F0LNVzEs

1Use Dashboards;
2
3DROP TABLE IF EXISTS Mon_Trafic_Trunk_SFR2;
4
5CREATE TABLE IF NOT EXISTS Mon_Trafic_Trunk_SFR2 (
6 col1 text,
7 col2 text,
8 col3 text,
9 col4 text,
10 col5 text,
11 col6 text,
12 col7 text,
13 col8 text,
14 col9 text,
15 col10 text,
16 col11 text,
17 col12 text,
18 col13 text,
19 col14 text,
20 col99 text
21) ENGINE=InnoDB DEFAULT CHARSET=latin1;
22
23/*
24delete from Mon_Trafic_Trunk_SFR2;
25*/
26
27LOAD DATA INFILE '/mnt/dashboard/SFR_report_monitor_traffic_trunk-groups.txt'
28INTO TABLE Mon_Trafic_Trunk_SFR2
29FIELDS TERMINATED BY ','
30ENCLOSED BY '"'
31LINES TERMINATED BY '\r\n'
32IGNORE 3 LINES;
33
34select 
35  trim(SUBSTRING(col1,1,5)),
36  trim(SUBSTRING(col1,length(col1)-11,13)),
37  col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13
38  into 
39  @hora, @dat,
40  @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10, @col11, @col12, @col13
41from Mon_Trafic_Trunk_SFR2;
42
43select 
44   concat(
45          STR_TO_DATE(@dat, '%b %e %Y'), ' ', 
46          STR_TO_DATE(@hora, '%H:%i')
47         ) 
48			into @intodatahora; 
49
50insert into Mon_Trafic_Trunk_SFR(DT, Num, Size, Active, Q, W)
51select
52   STR_TO_DATE(@intodatahora, '%Y-%m-%d %H:%i:%s') as DT,
53   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',1) as Num,
54   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',2) as Size,
55   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',3) as Active,
56   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',4) as Q,
57   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',5) as W
58 from (
59   select REPLACE( @col2, '  ',' ') as trunk union all
60   select REPLACE( @col3, '  ',' ') as trunk union all
61   select REPLACE( @col4, '  ',' ') as trunk union all
62   select REPLACE( @col5, '  ',' ') as trunk union all
63   select REPLACE( @col6, '  ',' ') as trunk union all
64   select REPLACE( @col7, '  ',' ') as trunk union all
65   select REPLACE( @col8, '  ',' ') as trunk union all
66   select REPLACE( @col9, '  ',' ') as trunk union all
67   select REPLACE(@col10, '  ',' ') as trunk union all
68   select REPLACE(@col11, '  ',' ') as trunk union all
69   select REPLACE(@col12, '  ',' ') as trunk union all
70   select REPLACE(@col13, '  ',' ') as trunk 
71  )linhas;