delete FROM `tb_nec_order_status_simulasi` where site_id_order in (select site_id_order from tb_nec_progres_simulasi where hop_id='')
insert into tb_nec_order_status_simulasi 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_simulasi where (`plan_alpro_fix` = 'Radio IP') and site_id_order in( select site_id_order from tb_nec_progres_simulasi where hop_id='') group by `TREG`,`TSEL`,`site_id_order`,`hop_id`) `t1` group by `t1`.`TREG`,`t1`.`TSEL`,`t1`.`site_id_order`) `t2`
delete FROM `tb_nec_hop_order1_simulasi` where site_id_order in (select site_id_order from tb_nec_progres_simulasi where hop_id='')
insert into `tb_nec_hop_order1_simulasi` 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_simulasi where site_id_order in( select site_id_order from tb_nec_progres_simulasi where hop_id='') ) `t2` on((`t1`.`site_id_order` = `t2`.`site_id_order`))) order by `t1`.`hop_id`
insert into tb_nec_prioritas_summary_simulasi 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_simulasi group by `TREG`,`TSEL`,`prioritas` order by `TREG`
INSERT INTO tb_nec_prioritas_order_simulasi 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_simulasi`) t1 GROUP BY TREG,TSEL,prioritas
INSERT INTO tb_nec_prioritas_simulasi 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_simulasi`) `t1` left join (select `treg` AS `treg`,`tsel` AS `tsel`,`prioritas` AS `prioritas`,`jml_hop` AS `jml_hop`,`RFS` AS `RFS`,`OGP` AS `OGP`,`PREP` AS `PREP`,`NY` AS `NY` from tb_nec_prioritas_summary_simulasi) `t2` on(((`t1`.`TREG` = `t2`.`treg`) and (`t1`.`TSEL` = `t2`.`tsel`) and (`t1`.`prioritas` = `t2`.`prioritas`))))
insert into tb_nec_prioritas_all_simulasi 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_simulasi 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_simulasi