$hapus1=mysqli_query("truncate table tb_nec_plan");
$hapus1=mysqli_query("truncate table tb_nec_progres_status");



q0="insert into tb_nec_plan
select 'TREG7' AS `TREG`,'PUMA' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_puma`.`site_id_order` AS `site_id_order`,`tb_nec_plan_puma`.`site_name_order` AS `site_name_order`,`tb_nec_plan_puma`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_puma`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_puma`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_puma`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_puma`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_puma`.`hop_id` AS `hop_id`,`tb_nec_plan_puma`.`final_bw` AS `final_bw`,`tb_nec_plan_puma`.`remark_detail_sow_sp1` AS `sow`,`tb_nec_plan_puma`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_puma` where (`tb_nec_plan_puma`.`remark_detail_sow_sp1` > '') union all select 'TREG1' AS `TREG`,'SUMBAGSEL' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_sumbagsel`.`site_id_order` AS `site_id_order`,`tb_nec_plan_sumbagsel`.`site_name_order` AS `site_name_order`,`tb_nec_plan_sumbagsel`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_sumbagsel`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_sumbagsel`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_sumbagsel`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_sumbagsel`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_sumbagsel`.`hop_id` AS `hop_id`,`tb_nec_plan_sumbagsel`.`final_bw` AS `final_bw`,`tb_nec_plan_sumbagsel`.`remark_detail_sow_sp1` AS `sow`,`tb_nec_plan_sumbagsel`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_sumbagsel` where (`tb_nec_plan_sumbagsel`.`remark_detail_sow_sp1` not in ('-','')) union all select 'TREG7' AS `TREG`,'SULAWESI' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_sulawesi`.`site_id_order` AS `site_id_order`,`tb_nec_plan_sulawesi`.`site_name_order` AS `site_name_order`,`tb_nec_plan_sulawesi`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_sulawesi`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_sulawesi`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_sulawesi`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_sulawesi`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_sulawesi`.`hop_id` AS `hop_id`,`tb_nec_plan_sulawesi`.`final_bw` AS `final_bw`,`tb_nec_plan_sulawesi`.`remark_detail_sow_sp1` AS `sow`,`tb_nec_plan_sulawesi`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_sulawesi` where (`tb_nec_plan_sulawesi`.`remark_detail_sow_sp1` > '') union all select 'TREG6' AS `TREG`,'KALIMANTAN' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_kalimantan`.`site_id_order` AS `site_id_order`,`tb_nec_plan_kalimantan`.`site_name_order` AS `site_name_order`,`tb_nec_plan_kalimantan`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_kalimantan`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_kalimantan`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_kalimantan`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_kalimantan`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_kalimantan`.`hop_id` AS `hop_id`,`tb_nec_plan_kalimantan`.`final_bw` AS `final_bw`,`tb_nec_plan_kalimantan`.`remark_detail_sow_sp1` AS `sow`,`tb_nec_plan_kalimantan`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_kalimantan` where (`tb_nec_plan_kalimantan`.`remark_detail_sow_sp1` > '') union all select 'TREG5' AS `TREG`,'BALNUS' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_balnus`.`site_id_order` AS `site_id_order`,`tb_nec_plan_balnus`.`site_name_order` AS `site_name_order`,`tb_nec_plan_balnus`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_balnus`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_balnus`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_balnus`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_balnus`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_balnus`.`hop_id` AS `hop_id`,`tb_nec_plan_balnus`.`final_bw` AS `final_bw`,`tb_nec_plan_balnus`.`remark_detail_sow_sp1` AS `sow`,`tb_nec_plan_balnus`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_balnus` where (`tb_nec_plan_balnus`.`remark_detail_sow_sp1` > '') union all select 'TREG7' AS `TREG`,'PUMA' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_puma`.`site_id_order` AS `site_id_order`,`tb_nec_plan_puma`.`site_name_order` AS `site_name_order`,`tb_nec_plan_puma`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_puma`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_puma`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_puma`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_puma`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_puma`.`hop_id` AS `hop_id`,`tb_nec_plan_puma`.`final_bw` AS `final_bw`,`tb_nec_plan_puma`.`plan_sow_fix` AS `plan_sow_fix`,`tb_nec_plan_puma`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_puma` where (`tb_nec_plan_puma`.`plan_sow_fix` > '') union all select 'TREG1' AS `TREG`,'SUMBAGSEL' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_sumbagsel`.`site_id_order` AS `site_id_order`,`tb_nec_plan_sumbagsel`.`site_name_order` AS `site_name_order`,`tb_nec_plan_sumbagsel`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_sumbagsel`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_sumbagsel`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_sumbagsel`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_sumbagsel`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_sumbagsel`.`hop_id` AS `hop_id`,`tb_nec_plan_sumbagsel`.`final_bw` AS `final_bw`,`tb_nec_plan_sumbagsel`.`plan_sow_fix` AS `plan_sow_fix`,`tb_nec_plan_sumbagsel`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_sumbagsel` where (`tb_nec_plan_sumbagsel`.`plan_sow_fix` > '') union all select 'TREG7' AS `TREG`,'SULAWESI' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_sulawesi`.`site_id_order` AS `site_id_order`,`tb_nec_plan_sulawesi`.`site_name_order` AS `site_name_order`,`tb_nec_plan_sulawesi`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_sulawesi`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_sulawesi`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_sulawesi`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_sulawesi`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_sulawesi`.`hop_id` AS `hop_id`,`tb_nec_plan_sulawesi`.`final_bw` AS `final_bw`,`tb_nec_plan_sulawesi`.`plan_sow_fix` AS `plan_sow_fix`,`tb_nec_plan_sulawesi`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_sulawesi` where (`tb_nec_plan_sulawesi`.`plan_sow_fix` > '') union all select 'TREG6' AS `TREG`,'KALIMANTAN' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_kalimantan`.`site_id_order` AS `site_id_order`,`tb_nec_plan_kalimantan`.`site_name_order` AS `site_name_order`,`tb_nec_plan_kalimantan`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_kalimantan`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_kalimantan`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_kalimantan`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_kalimantan`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_kalimantan`.`hop_id` AS `hop_id`,`tb_nec_plan_kalimantan`.`final_bw` AS `final_bw`,`tb_nec_plan_kalimantan`.`plan_sow_fix` AS `plan_sow_fix`,`tb_nec_plan_kalimantan`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_kalimantan` where (`tb_nec_plan_kalimantan`.`plan_sow_fix` > '') union all select 'TREG5' AS `TREG`,'BALNUS' AS `TSEL`,'NEC' AS `mitra`,`tb_nec_plan_balnus`.`site_id_order` AS `site_id_order`,`tb_nec_plan_balnus`.`site_name_order` AS `site_name_order`,`tb_nec_plan_balnus`.`countifd_site_id_order` AS `countifd_site_id_order`,`tb_nec_plan_balnus`.`site_id_ne` AS `site_id_ne`,`tb_nec_plan_balnus`.`site_name_ne` AS `site_name_ne`,`tb_nec_plan_balnus`.`site_id_fe` AS `site_id_fe`,`tb_nec_plan_balnus`.`site_name_fe` AS `site_name_fe`,`tb_nec_plan_balnus`.`hop_id` AS `hop_id`,`tb_nec_plan_balnus`.`final_bw` AS `final_bw`,`tb_nec_plan_balnus`.`plan_sow_fix` AS `plan_sow_fix`,`tb_nec_plan_balnus`.`plan_alpro_fix` AS `plan_alpro_fix` from `tb_nec_plan_balnus` where (`tb_nec_plan_balnus`.`plan_sow_fix` > '')

";

$act_q0=mysql_query($q0);


// table tb_nec_progres_status
q1="insert into tb_nec_progres_status
$q1="select `t8`.`TREG` AS `TREG`,`t8`.`TSEL` AS `TSEL`,`t8`.`mitra` AS `mitra`,`t8`.`site_id_order` AS `site_id_order`,`t8`.`site_name_order` AS `site_name_order`,`t8`.`countifd_site_id_order` AS `countifd_site_id_order`,`t8`.`site_id_ne` AS `site_id_ne`,`t8`.`site_name_ne` AS `site_name_ne`,`t8`.`site_id_fe` AS `site_id_fe`,`t8`.`site_name_fe` AS `site_name_fe`,`t8`.`hop_id` AS `hop_id`,`t8`.`final_bw` AS `final_bw`,`t8`.`sow` AS `sow`,`t8`.`plan_alpro_fix` AS `plan_alpro_fix`,(case when (`t8`.`sow` = 'No Activity') then 'RFS' else `t9`.`gabung` end) AS `gabung` from (((select tb_nec_plan.`TREG` AS `TREG`,tb_nec_plan.`TSEL` AS `TSEL`,tb_nec_plan.`mitra` AS `mitra`,tb_nec_plan.`site_id_order` AS `site_id_order`,tb_nec_plan.`site_name_order` AS `site_name_order`,tb_nec_plan.`countifd_site_id_order` AS `countifd_site_id_order`,tb_nec_plan.`site_id_ne` AS `site_id_ne`,tb_nec_plan.`site_name_ne` AS `site_name_ne`,tb_nec_plan.`site_id_fe` AS `site_id_fe`,tb_nec_plan.`site_name_fe` AS `site_name_fe`,tb_nec_plan.`hop_id` AS `hop_id`,tb_nec_plan.`final_bw` AS `final_bw`,tb_nec_plan.`sow` AS `sow`,tb_nec_plan.`plan_alpro_fix` AS `plan_alpro_fix` from tb_nec_plan)) `t8` left join (select `t7`.`hop_id` AS `hop_id`,min(`t7`.`cat_gabung`) AS `gabung` from (select `t1`.`hop_id` AS `hop_id`,`t1`.`implementation_category` AS `implementation_category`,`t2`.`cat_gabung` AS `cat_gabung` from (((select `tb_nec_plan_progres`.`hop_id` AS `hop_id`,`tb_nec_plan_progres`.`implementation_category` AS `implementation_category` from `tb_nec_plan_progres`)) `t1` left join (select `tb_nec_plan_progres_cat`.`implementation_category` AS `implementation_category`,`tb_nec_plan_progres_cat`.`cat_gabung` AS `cat_gabung` from `tb_nec_plan_progres_cat`) `t2` on((`t1`.`implementation_category` = `t2`.`implementation_category`)))) `t7` group by `t7`.`hop_id`) `t9` on((`t8`.`hop_id` = `t9`.`hop_id`)))";

$act_q1=mysql_query($q1);




q2="insert into table tb_nec_order_status

select `t2`.`TREG` AS `TREG`,`t2`.`TSEL` AS `TSEL`,`t2`.`site_id_order` AS `site_id_order`,`t2`.`jml_hop` AS `jml_hop`,`t2`.`jml_RFS` AS `jml_RFS`,`t2`.`jml_OGP` AS `jml_OGP`,`t2`.`jml_PREP` AS `jml_PREP`,`t2`.`NOT_YET` AS `NOT_YET`,(case when (`t2`.`jml_hop` = `t2`.`jml_RFS`) then 'RFS' when ((`t2`.`NOT_YET` > 0) and (`t2`.`jml_RFS` >= 0) and (`t2`.`jml_OGP` >= 0) and (`t2`.`jml_PREP` >= 0)) then 'NOT YET' when ((`t2`.`NOT_YET` = 0) and (`t2`.`jml_RFS` >= 0) and (`t2`.`jml_OGP` >= 0) and (`t2`.`jml_PREP` > 0)) then 'PREP' else 'OGP' end) AS `final_status`,(case when (`t2`.`jml_hop` = `t2`.`jml_RFS`) then 'P0' when ((`t2`.`NOT_YET` > 0) and (`t2`.`jml_RFS` >= 0) and (`t2`.`jml_OGP` >= 0) and (`t2`.`jml_PREP` >= 0)) then 'P3' when ((`t2`.`NOT_YET` = 0) and (`t2`.`jml_RFS` >= 0) and (`t2`.`jml_OGP` >= 0) and (`t2`.`jml_PREP` > 0)) then 'P2' else 'P1' end) AS `prioritas` from (select `t1`.`TREG` AS `TREG`,`t1`.`TSEL` AS `TSEL`,`t1`.`site_id_order` AS `site_id_order`,sum(if((`t1`.`site_id_order` > ''),1,0)) AS `jml_hop`,sum(if((`t1`.`status_gab` = 'RFS'),1,0)) AS `jml_RFS`,sum(if((`t1`.`status_gab` = 'ONGO'),1,0)) AS `jml_OGP`,sum(if((`t1`.`status_gab` = 'MATPREP'),1,0)) AS `jml_PREP`,sum(if(isnull(`t1`.`status_gab`),1,0)) AS `NOT_YET` from (select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`site_id_order` AS `site_id_order`,min(`gabung`) AS `status_gab`,`hop_id` AS `hop_id` from tb_nec_progres_status where (`plan_alpro_fix` = 'Radio IP') group by `TREG`,`TSEL`,`site_id_order`,`hop_id`) `t1` group by `t1`.`TREG`,`t1`.`TSEL`,`t1`.`site_id_order`) `t2`
";

$act_q2=mysql_query($q2);



$q3="insert into table tb_nec_hop_order1 as
select distinct `t1`.`TREG` AS `TREG`,`t1`.`TSEL` AS `TSEL`,`t1`.`site_id_order` AS `site_id_order`,`t1`.`hop_id` AS `hop_id`,`t2`.`prioritas` AS `prioritas`,`t1`.`status` AS `status` from ((select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`site_id_order` AS `site_id_order`,`hop_id` AS `hop_id`,min(`gabung`) AS `status` from tb_nec_progres_status where (`plan_alpro_fix` = 'radio IP') group by `TREG`,`TSEL`,`site_id_order`,`hop_id`) `t1` join (select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`site_id_order` AS `site_id_order`,`jml_hop` AS `jml_hop`,`jml_RFS` AS `jml_RFS`,`jml_OGP` AS `jml_OGP`,`jml_PREP` AS `jml_PREP`,`NOT_YET` AS `NOT_YET`,`final_status` AS `final_status`,`prioritas` AS `prioritas` from tb_nec_order_status) `t2` on((`t1`.`site_id_order` = `t2`.`site_id_order`))) order by `t1`.`hop_id`"
$act_q3=mysql_query($q3);



$q4="insert into tb_nec_prioritas_summary
select `TREG` AS `treg`,`TSEL` AS `tsel`,`prioritas` AS `prioritas`,count(0) AS `jml_hop`,sum(if((`status` = 'RFS'),1,0)) AS `RFS`,sum(if((`status` = 'ONGO'),1,0)) AS `OGP`,sum(if((`status` = 'MATPREP'),1,0)) AS `PREP`,sum(if(isnull(`status`),1,0)) AS `NY` from tb_nec_hop_order1 group by `TREG`,`TSEL`,`prioritas` order by `TREG`";
$act_q4=mysql_query($q4);



$q5="
INSERT INTO tb_nec_prioritas_order
select distinct TREG,TSEL,prioritas,sum(if(site_id_order>'',1,0)) as jml_order from
(SELECT distinct TREG,TSEL,prioritas,site_id_order FROM `tb_nec_hop_order1`) t1
GROUP BY TREG,TSEL,prioritas";
$act_q5=mysql_query($q5);

$q6="
INSERT INTO tb_nec_prioritas 
select `t1`.`TREG` AS `TREG`,`t1`.`TSEL` AS `TSEL`,`t1`.`prioritas` AS `prioritas`,`t1`.`jml_order` AS `jml_order`,`t2`.`jml_hop` AS `jml_hop`,`t2`.`RFS` AS `rfs`,`t2`.`OGP` AS `ogp`,`t2`.`PREP` AS `prep`,`t2`.`NY` AS `ny` from ((select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`prioritas` AS `prioritas`,`jml_order` AS `jml_order` from `tb_nec_prioritas_order`) `t1` left join (select tb_nec_prioritas_summary.`treg` AS `treg`,tb_nec_prioritas_summary.`tsel` AS `tsel`,tb_nec_prioritas_summary.`prioritas` AS `prioritas`,tb_nec_prioritas_summary.`jml_hop` AS `jml_hop`,tb_nec_prioritas_summary.`RFS` AS `RFS`,tb_nec_prioritas_summary.`OGP` AS `OGP`,tb_nec_prioritas_summary.`PREP` AS `PREP`,tb_nec_prioritas_summary.`NY` AS `NY` from tb_nec_prioritas_summary) `t2` on(((`t1`.`TREG` = `t2`.`treg`) and (`t1`.`TSEL` = `t2`.`tsel`) and (`t1`.`prioritas` = `t2`.`prioritas`))))";
$act_q6=mysql_query($q6);


$q7="insert into tb_nec_prioritas_all
select `TREG` AS `TREG`,`TSEL` AS `TSEL`,sum(if((`prioritas` = 'P0'),`jml_order`,0)) AS `p0_order`,sum(if((`prioritas` = 'P0'),`jml_hop`,0)) AS `p0_hop`,sum(if((`prioritas` = 'P0'),`rfs`,0)) AS `p0_rfs`,sum(if((`prioritas` = 'P0'),`ogp`,0)) AS `p0_ogp`,sum(if((`prioritas` = 'P0'),`prep`,0)) AS `p0_prep`,sum(if((`prioritas` = 'P0'),`ny`,0)) AS `p0_ny`,sum(if((`prioritas` = 'P1'),`jml_order`,0)) AS `p1_order`,sum(if((`prioritas` = 'P1'),`jml_hop`,0)) AS `p1_hop`,sum(if((`prioritas` = 'P1'),`rfs`,0)) AS `p1_rfs`,sum(if((`prioritas` = 'P1'),`ogp`,0)) AS `p1_ogp`,sum(if((`prioritas` = 'P1'),`prep`,0)) AS `p1_prep`,sum(if((`prioritas` = 'P1'),`ny`,0)) AS `p1_ny`,sum(if((`prioritas` = 'P2'),`jml_order`,0)) AS `p2_order`,sum(if((`prioritas` = 'P2'),`jml_hop`,0)) AS `p2_hop`,sum(if((`prioritas` = 'P2'),`rfs`,0)) AS `p2_rfs`,sum(if((`prioritas` = 'P2'),`ogp`,0)) AS `p2_ogp`,sum(if((`prioritas` = 'P2'),`prep`,0)) AS `p2_prep`,sum(if((`prioritas` = 'P2'),`ny`,0)) AS `p2_ny`,sum(if((`prioritas` = 'P3'),`jml_order`,0)) AS `p3_order`,sum(if((`prioritas` = 'P3'),`jml_hop`,0)) AS `p3_hop`,sum(if((`prioritas` = 'P3'),`rfs`,0)) AS `p3_rfs`,sum(if((`prioritas` = 'P3'),`ogp`,0)) AS `p3_ogp`,sum(if((`prioritas` = 'P3'),`prep`,0)) AS `p3_prep`,sum(if((`prioritas` = 'P3'),`ny`,0)) AS `p3_ny` from tb_nec_prioritas group by `TREG`,`TSEL` union all select 'total' AS `total`,'total' AS `total`,sum(if((`prioritas` = 'P0'),`jml_order`,0)) AS `p0_order`,sum(if((`prioritas` = 'P0'),`jml_hop`,0)) AS `p0_hop`,sum(if((`prioritas` = 'P0'),`rfs`,0)) AS `p0_rfs`,sum(if((`prioritas` = 'P0'),`ogp`,0)) AS `p0_ogp`,sum(if((`prioritas` = 'P0'),`prep`,0)) AS `p0_prep`,sum(if((`prioritas` = 'P0'),`ny`,0)) AS `p0_ny`,sum(if((`prioritas` = 'P1'),`jml_order`,0)) AS `p1_order`,sum(if((`prioritas` = 'P1'),`jml_hop`,0)) AS `p1_hop`,sum(if((`prioritas` = 'P1'),`rfs`,0)) AS `p1_rfs`,sum(if((`prioritas` = 'P1'),`ogp`,0)) AS `p1_ogp`,sum(if((`prioritas` = 'P1'),`prep`,0)) AS `p1_prep`,sum(if((`prioritas` = 'P1'),`ny`,0)) AS `p1_ny`,sum(if((`prioritas` = 'P2'),`jml_order`,0)) AS `p2_order`,sum(if((`prioritas` = 'P2'),`jml_hop`,0)) AS `p2_hop`,sum(if((`prioritas` = 'P2'),`rfs`,0)) AS `p2_rfs`,sum(if((`prioritas` = 'P2'),`ogp`,0)) AS `p2_ogp`,sum(if((`prioritas` = 'P2'),`prep`,0)) AS `p2_prep`,sum(if((`prioritas` = 'P2'),`ny`,0)) AS `p2_ny`,sum(if((`prioritas` = 'P3'),`jml_order`,0)) AS `p3_order`,sum(if((`prioritas` = 'P3'),`jml_hop`,0)) AS `p3_hop`,sum(if((`prioritas` = 'P3'),`rfs`,0)) AS `p3_rfs`,sum(if((`prioritas` = 'P3'),`ogp`,0)) AS `p3_ogp`,sum(if((`prioritas` = 'P3'),`prep`,0)) AS `p3_prep`,sum(if((`prioritas` = 'P3'),`ny`,0)) AS `p3_ny` from tb_nec_prioritas";

$act_q7=mysql_query($q7);


$q8="insert into tb_nec_dashboard
select `a`.`TREG` AS `TREG`,`a`.`TSEL` AS `TSEL`,`a`.`jml_ORDER` AS `jml_ORDER`,`a`.`order_RFS` AS `order_RFS`,`a`.`order_OGP` AS `order_OGP`,`a`.`order_PREP` AS `order_PREP`,`a`.`order_NY` AS `order_NY`,`b`.`jml_hop` AS `jml_LINK`,`b`.`jml_RFS` AS `link_RFS`,`b`.`jml_OGP` AS `link_OGP`,`b`.`jml_PREP` AS `link_PREP`,`b`.`NOT_YET` AS `link_NY` from ((select `t1`.`TREG` AS `TREG`,`t1`.`TSEL` AS `TSEL`,sum(if((`t1`.`site_id_order` > ''),1,0)) AS `jml_ORDER`,sum(if((`t1`.`final_status` = 'RFS'),1,0)) AS `order_RFS`,sum(if((`t1`.`final_status` = 'OGP'),1,0)) AS `order_OGP`,sum(if((`t1`.`final_status` = 'PREP'),1,0)) AS `order_PREP`,sum(if((`t1`.`final_status` = 'NOT YET'),1,0)) AS `order_NY` from tb_nec_order_status `t1` group by `t1`.`TREG`,`t1`.`TSEL`) `a` left join (select `t1`.`TREG` AS `TREG`,`t1`.`TSEL` AS `TSEL`,sum(if((`t1`.`hop_id` > ''),1,0)) AS `jml_hop`,sum(if((`t1`.`status_gab` = 'RFS'),1,0)) AS `jml_RFS`,sum(if((`t1`.`status_gab` = 'ONGO'),1,0)) AS `jml_OGP`,sum(if((`t1`.`status_gab` = 'MATPREP'),1,0)) AS `jml_PREP`,sum(if(isnull(`t1`.`status_gab`),1,0)) AS `NOT_YET` from (select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`hop_id` AS `hop_id`,min(`gabung`) AS `status_gab` from tb_nec_progres_status where (`plan_alpro_fix` = 'Radio IP') group by `TREG`,`TSEL`,`hop_id`) `t1` group by `t1`.`TREG`,`t1`.`TSEL`) `b` on(((`a`.`TSEL` = `b`.`TSEL`) and (`a`.`TREG` = `b`.`TREG`))))";
$act_q8=mysql_query($q8);




$q9="insert into tb_nec_dashboard_all
select `TREG` AS `TREG`,`TSEL` AS `TSEL`,`jml_ORDER` AS `jml_ORDER`,`order_RFS` AS `order_RFS`,`order_OGP` AS `order_OGP`,`order_PREP` AS `order_PREP`,`order_NY` AS `order_NY`,`jml_LINK` AS `jml_LINK`,`link_RFS` AS `link_RFS`,`link_OGP` AS `link_OGP`,`link_PREP` AS `link_PREP`,`link_NY` AS `link_NY` from tb_nec_dashboard union all select 'TOTAL' AS `total`,'TOTAL' AS `total2`,sum(`jml_ORDER`) AS `sum(jml_order)`,sum(`order_RFS`) AS `SUM(order_RFS)`,sum(`order_OGP`) AS `sum(order_OGP)`,sum(`order_PREP`) AS `sum(order_PREP)`,sum(`order_NY`) AS `sum(order_NY)`,sum(`jml_LINK`) AS `sum(jml_LINK)`,sum(`link_RFS`) AS `sum(link_RFS)`,sum(`link_OGP`) AS `sum(link_OGP)`,sum(`link_PREP`) AS `sum(link_PREP)`,sum(`link_NY`) AS `sum(link_NY)` from tb_nec_dashboard";
$act_q9=mysql_query($q9);