Load_file_edt_to_Imput.sq

LOAD DATA INFILE '/mnt/dashboard/SFR_report_monitor_traffic_trunk-groups.txt'
INTO TABLE graf_log_trunks_sft -- (dt, n1, n2, n3, n4, n5, n6, n51, n52)
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES
(@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9)
set 
  dt= concat(STR_TO_DATE(trim(SUBSTRING(@c1,length(@c1)-11,13)), '%b %e %Y'), ' ', STR_TO_DATE(trim(SUBSTRING(@c1,1,5)), '%H:%i')),
  n1=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c2, '  ',' '), '  ',' '), ' ',','),',',3),
  n2=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c3, '  ',' '), '  ',' '), ' ',','),',',3),
  n3=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c4, '  ',' '), '  ',' '), ' ',','),',',3),
  n4=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c5, '  ',' '), '  ',' '), ' ',','),',',3),
  n5=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c6, '  ',' '), '  ',' '), ' ',','),',',3),
  n6=  SPLIT_STR(REPLACE(REPLACE(REPLACE(@c7, '  ',' '), '  ',' '), ' ',','),',',3),
  n51= SPLIT_STR(REPLACE(REPLACE(REPLACE(@c8, '  ',' '), '  ',' '), ' ',','),',',3),
  n52= SPLIT_STR(REPLACE(REPLACE(REPLACE(@c9, '  ',' '), '  ',' '), ' ',','),',',3)

Load_Mon_Trafic.sql.sql

Use Dashboards;

DROP TABLE IF EXISTS Mon_Trafic_Trunk_SFR2;

CREATE TABLE IF NOT EXISTS Mon_Trafic_Trunk_SFR2 (
 col1 text,
 col2 text,
 col3 text,
 col4 text,
 col5 text,
 col6 text,
 col7 text,
 col8 text,
 col9 text,
 col10 text,
 col11 text,
 col12 text,
 col13 text,
 col14 text,
 col99 text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
delete from Mon_Trafic_Trunk_SFR2;
*/

LOAD DATA INFILE '/mnt/dashboard/SFR_report_monitor_traffic_trunk-groups.txt'
INTO TABLE Mon_Trafic_Trunk_SFR2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES;

select 
  trim(SUBSTRING(col1,1,5)),
  trim(SUBSTRING(col1,length(col1)-11,13)),
  col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13
  into 
  @hora, @dat,
  @col2, @col3, @col4, @col5, @col6, @col7, @col8, @col9, @col10, @col11, @col12, @col13
from Mon_Trafic_Trunk_SFR2;

select 
   concat(
          STR_TO_DATE(@dat, '%b %e %Y'), ' ', 
          STR_TO_DATE(@hora, '%H:%i')
         ) 
			into @intodatahora; 

insert into Mon_Trafic_Trunk_SFR(DT, Num, Size, Active, Q, W)
select
   STR_TO_DATE(@intodatahora, '%Y-%m-%d %H:%i:%s') as DT,
   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',1) as Num,
   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',2) as Size,
   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',3) as Active,
   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',4) as Q,
   SPLIT_STR( REPLACE(trunk, '  ',' '), ' ',5) as W
 from (
   select REPLACE( @col2, '  ',' ') as trunk union all
   select REPLACE( @col3, '  ',' ') as trunk union all
   select REPLACE( @col4, '  ',' ') as trunk union all
   select REPLACE( @col5, '  ',' ') as trunk union all
   select REPLACE( @col6, '  ',' ') as trunk union all
   select REPLACE( @col7, '  ',' ') as trunk union all
   select REPLACE( @col8, '  ',' ') as trunk union all
   select REPLACE( @col9, '  ',' ') as trunk union all
   select REPLACE(@col10, '  ',' ') as trunk union all
   select REPLACE(@col11, '  ',' ') as trunk union all
   select REPLACE(@col12, '  ',' ') as trunk union all
   select REPLACE(@col13, '  ',' ') as trunk 
  )linhas;

Load_Mon_Trafic_Hunt.sql

USE Dashboards;

DROP TABLE IF EXISTS Mon_Trafic_Hunt_SFR2;

CREATE TABLE Mon_Trafic_Hunt_SFR2 (
      c1 text,
      c2 text,    c3 text,   c4 text,   c5 text,   c6 text,   c7 text,
      c8 text,    c9 text,  c10 text,  c11 text,  c12 text,  c13 text,
      c14 text,  c15 text,  c16 text,  c17 text,  c18 text,  c19 text,
      c20 text,  c21 text,  c22 text,  c23 text,  c24 text,  c25 text,
      c26 text,  c27 text,  c28 text,  c29 text,  c30 text,  c31 text,
      c32 text,  c33 text,  c34 text,  c35 text,  c36 text,  c37 text,
      c38 text,  c39 text,  c40 text,  c41 text,  c42 text,  c43 text,
      c44 text,  c45 text,  c46 text,  c47 text,  c48 text,  c49 text,
      c50 text,  c51 text,  c52 text,  c53 text,  c54 text,  c55 text,
      c56 text,  c57 text,  c58 text,  c59 text,  c60 text,  c61 text,
      c62 text,  c63 text,  c64 text,  c65 text,  c66 text,  c67 text,
      c68 text,  c69 text,  c70 text,  c71 text,  c72 text,  c73 text,
      c74 text,  c75 text,  c76 text,  c77 text,  c78 text,  c79 text,
      c80 text,  c81 text,  c82 text,  c83 text,  c84 text,  c85 text,
      c86 text,  c87 text,  c88 text,  c89 text,  c90 text,  c91 text,
      c92 text,  c93 text,  c94 text,  c95 text,  c96 text,  c97 text,
      c98 text,  c99 text, c100 text, c101 text, c102 text, c103 text,
     c104 text, c105 text, c106 text, c107 text, c108 text, c109 text,
     c110 text, c111 text, c112 text, c113 text, c114 text, c115 text,
     c116 text, c117 text, c118 text, c119 text, c120 text, c121 text,
     c122 text, c123 text, c124 text, c125 text, c126 text, c127 text,
     c128 text, c129 text, c130 text, c131 text, c132 text, c133 text,
     c134 text, c135 text, c136 text, c137 text, c138 text, c139 text,
     c140 text, c141 text, c142 text, c143 text, c144 text, c145 text,
     c146 text, c147 text, c148 text, c149 text, c150 text, c151 text,
     c152 text, c153 text, c154 text, c155 text, c156 text, c157 text,
     c158 text, c159 text, c160 text, c161 text, c162 text, c163 text,
     c164 text, c165 text, c166 text, c167 text, c168 text, c169 text,
     c170 text, c171 text, c172 text, c173 text, c174 text, c175 text,
     c176 text, c177 text, c178 text, c179 text, c180 text, c181 text,
     c182 text, c183 text, c184 text, c185 text, c186 text, c187 text,
     c188 text, c189 text, c190 text, c191 text, c192 text, c193 text,
     c194 text, c195 text, c196 text, c197 text, c198 text, c199 text,
     c200 text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


LOAD DATA INFILE '/mnt/dashboard/SFR_report_monitor_traffic_hunt-group.txt'
INTO TABLE Mon_Trafic_Hunt_SFR2
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES;

-- select * from Mon_Trafic_Trunk_SFR where DT > '2016-09-00 18:09:00';

select 
  trim(SUBSTRING(c1,1,5)), trim(SUBSTRING(c1,length(c1)-11,13)),
  c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, 
  c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, 
  c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, 
  c47, c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, 
  c62, c63, c64, c65, c66, c67, c68, c69, c70, c71, c72, c73, c74, c75, c76, 
  c77, c78, c79, c80, c81, c82, c83, c84, c85, c86, c87, c88, c89, c90, c91, 
  c92, c93, c94, c95, c96, c97, c98, c99, c100, c101, c102, c103, c104, c105, 
  c106, c107, c108, c109, c110, c111, c112, c113, c114, c115, c116, c117, c118, 
  c119, c120, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, 
  c132, c133, c134, c135, c136, c137, c138, c139, c140, c141, c142, c143, c144, 
  c145, c146, c147, c148, c149, c150, c151, c152, c153, c154, c155, c156, c157, 
  c158, c159, c160, c161, c162, c163, c164, c165, c166, c167, c168, c169, c170, 
  c171, c172, c173, c174, c175, c176, c177, c178, c179, c180, c181, c182, c183, 
  c184, c185, c186, c187, c188, c189, c190, c191, c192, c193
  into @hora, @dat,
  @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, 
  @c17, @c18, @c19, @c20, @c21, @c22, @c23, @c24, @c25, @c26, @c27, @c28, @c29, @c30, @c31, 
  @c32, @c33, @c34, @c35, @c36, @c37, @c38, @c39, @c40, @c41, @c42, @c43, @c44, @c45, @c46, 
  @c47, @c48, @c49, @c50, @c51, @c52, @c53, @c54, @c55, @c56, @c57, @c58, @c59, @c60, @c61, 
  @c62, @c63, @c64, @c65, @c66, @c67, @c68, @c69, @c70, @c71, @c72, @c73, @c74, @c75, @c76, 
  @c77, @c78, @c79, @c80, @c81, @c82, @c83, @c84, @c85, @c86, @c87, @c88, @c89, @c90, @c91, 
  @c92, @c93, @c94, @c95, @c96, @c97, @c98, @c99, @c100, @c101, @c102, @c103, @c104, @c105, 
  @c106, @c107, @c108, @c109, @c110, @c111, @c112, @c113, @c114, @c115, @c116, @c117, @c118, 
  @c119, @c120, @c121, @c122, @c123, @c124, @c125, @c126, @c127, @c128, @c129, @c130, @c131, 
  @c132, @c133, @c134, @c135, @c136, @c137, @c138, @c139, @c140, @c141, @c142, @c143, @c144, 
  @c145, @c146, @c147, @c148, @c149, @c150, @c151, @c152, @c153, @c154, @c155, @c156, @c157, 
  @c158, @c159, @c160, @c161, @c162, @c163, @c164, @c165, @c166, @c167, @c168, @c169, @c170, 
  @c171, @c172, @c173, @c174, @c175, @c176, @c177, @c178, @c179, @c180, @c181, @c182, @c183, 
  @c184, @c185, @c186, @c187, @c188, @c189, @c190, @c191, @c192, @c193
from Mon_Trafic_Hunt_SFR2;

select 
   concat(
          STR_TO_DATE(@dat, '%b %e %Y'), ' ', 
          STR_TO_DATE(@hora, '%H:%i')
         ) 
			into @intodatahora; 
			
insert into Mon_Trafic_Hunt_SFR (DT, Num, Size, Active, W, LCIQ)

select 
	STR_TO_DATE(@intodatahora, '%Y-%m-%d %H:%i:%s') as DT,
	Num, Size, Active, W, LCIQ 
from (
	select 
	       @c2 as Num,
          	        @c3 as Size,   
					      @c4 as Active,    
								 @c6 as W,   
								        @c7 as LCIQ  
	union all 
	select   @c8,   @c9,  @c10,  @c12,  @c13 union all
	select  @c14,  @c15,  @c16,  @c18,  @c19 union all
	select  @c20,  @c21,  @c22,  @c24,  @c25 union all
	select  @c26,  @c27,  @c28,  @c30,  @c31 union all
	select  @c32,  @c33,  @c34,  @c36,  @c37 union all
	select  @c38,  @c39,  @c40,  @c42,  @c43 union all
	select  @c44,  @c45,  @c46,  @c48,  @c49 union all
	select  @c50,  @c51,  @c52,  @c54,  @c55 union all
	select  @c56,  @c57,  @c58,  @c60,  @c61 union all
	select  @c62,  @c63,  @c64,  @c66,  @c67 union all
	select  @c68,  @c69,  @c70,  @c72,  @c73 union all
	select  @c74,  @c75,  @c76,  @c78,  @c79 union all
	select  @c80,  @c81,  @c82,  @c84,  @c85 union all
	select  @c86,  @c87,  @c88,  @c90,  @c91 union all
	select  @c92,  @c93,  @c94,  @c96,  @c97 union all
	select  @c98,  @c99, @c100, @c102, @c103 union all
	select @c104, @c105, @c106, @c108, @c109 union all
	select @c110, @c111, @c112, @c114, @c115 union all
	select @c116, @c117, @c118, @c120, @c121 union all
	select @c122, @c123, @c124, @c126, @c127 union all
	select @c128, @c129, @c130, @c132, @c133 union all
	select @c134, @c135, @c136, @c138, @c139 union all
	select @c140, @c141, @c142, @c144, @c145 union all
	select @c146, @c147, @c148, @c150, @c151 union all
	select @c152, @c153, @c154, @c156, @c157 union all
	select @c158, @c159, @c160, @c162, @c163 union all
	select @c164, @c165, @c166, @c168, @c169 union all
	select @c170, @c171, @c172, @c174, @c175 union all
	select @c176, @c177, @c178, @c180, @c181 union all
	select @c182, @c183, @c184, @c186, @c187 union all
	select @c188, @c189, @c190, @c192, @c193
) as hunts
where 
    Size > 0;
	
	/* a coluna 6 não está a ser utilizada 
	   @c11, 
	   @c17, 
	   @c23, 
       @c29, 
       @c35, 
       @c41, 
       @c47, 
       @c53, 
       @c59, 
       @c65, 
       @c71, 
       @c77, 
       @c83, 
       @c89, 
       @c95, 
      @c101, 
      @c107, 
      @c113, 
      @c119, 
      @c125, 
      @c131, 
      @c137, 
      @c143, 
      @c149, 
      @c155, 
      @c161, 
      @c167, 
      @c173, 
      @c179, 
      @c185, 
      @c191, 
	  */