v_exp_inspection_activity

select `i`.`id` AS `ID Inspeksi`,`i`.`judul_inspeksi` AS `Judul Inspeksi`,`location`.`location_name` AS `Lokasi Inspeksi`,`sub_location`.`name` AS `Sublokasi Inspeksi`,(case when (`i`.`sarana_id` is not null) then `sarana`.`type` when (`i`.`prasarana_id` is not null) then `prasarana`.`type` when (`i`.`peralatan_id` is not null) then `peralatan`.`type` when (`i`.`instalasi_id` is not null) then `instalasi`.`type` end) AS `Jenis Objek Inspeksi`,(case when (`i`.`sarana_id` is not null) then `sarana`.`no_lambung` when (`i`.`prasarana_id` is not null) then `prasarana`.`no_prasarana` when (`i`.`peralatan_id` is not null) then `peralatan`.`no_peralatan` when (`i`.`instalasi_id` is not null) then `instalasi`.`no_instalasi` end) AS `Nama Objek Inspeksi`,count(0) AS `Jumlah Temuan`,sum(if((`nc_all`.`status` = 'END'),1,0)) AS `Jumlah Close`,(case when (count(0) = sum(if((`nc_all`.`status` = 'END'),1,0))) then 'END' else 'OPEN' end) AS `Status Inspeksi`,`form`.`form_title` AS `Nama Form Inspeksi`,`i`.`createdAt` AS `Tanggal Inspeksi`,`pju`.`name` AS `Nama PJU`,`pju`.`nik` AS `NIK PJU`,`pju`.`position` AS `Posisi PJU`,`pju`.`department` AS `Departemen PJU`,`pju`.`company` AS `Perusahaan PJU`,`pelapor`.`nik` AS `Pelapor NIK`,`pelapor`.`isafe_no` AS `Pelapor Isafe ID`,`pelapor`.`name` AS `Pelapor Nama`,`pelapor`.`company` AS `Pelapor Perusahaan` from ((((((((((`dsis_hse_assessments_inspeksi` `i` join `dsis_hse_simper` `pju` on((`i`.`AssignTo` = `pju`.`isafe_no`))) join `dsis_master_location` `location` on((`location`.`id` = `i`.`location_id`))) join `dsis_master_sub_lokasi` `sub_location` on((`sub_location`.`id` = `i`.`sublocation_id`))) join `dsis_master_form` `form` on((`form`.`id` = `i`.`form_id`))) left join `dsis_master_unit` `sarana` on(((`sarana`.`id` = `i`.`sarana_id`) and (`i`.`asset_type_id` = 1)))) left join `dsis_master_unit_prasarana` `prasarana` on(((`prasarana`.`id` = `i`.`prasarana_id`) and (`i`.`asset_type_id` = 2)))) left join `dsis_master_unit_instalasi` `instalasi` on(((`instalasi`.`id` = `i`.`instalasi_id`) and (`i`.`asset_type_id` = 3)))) left join `dsis_master_unit_peralatan` `peralatan` on(((`peralatan`.`id` = `i`.`peralatan_id`) and (`i`.`asset_type_id` = 4)))) left join `dsis_hse_nc` `nc_all` on((`nc_all`.`inspeksi_id` = `i`.`id`))) left join `dsis_hse_simper` `pelapor` on((`pelapor`.`id` = `i`.`created_by`))) where (not((`i`.`judul_inspeksi` like '%Test]%'))) group by `i`.`judul_inspeksi`,`location`.`location_name`,(case when (`i`.`sarana_id` is not null) then `sarana`.`type` when (`i`.`prasarana_id` is not null) then `prasarana`.`type` when (`i`.`peralatan_id` is not null) then `peralatan`.`type` when (`i`.`instalasi_id` is not null) then `instalasi`.`type` end),(case when (`i`.`sarana_id` is not null) then `sarana`.`no_lambung` when (`i`.`prasarana_id` is not null) then `prasarana`.`no_prasarana` when (`i`.`peralatan_id` is not null) then `peralatan`.`no_peralatan` when (`i`.`instalasi_id` is not null) then `instalasi`.`no_instalasi` end),`sub_location`.`name`,`form`.`form_title`,`i`.`createdAt`,`pju`.`name`,`pju`.`nik`,`pju`.`position`,`pju`.`department`,`pju`.`company`