Untitled

  WITH query_pengendalian_rincian AS (
	select
	     c.suretyship,
			 a.id_transaksi_suretyship,
			nama_principal,
			nomor_sk,
			CASE	
				when d.id_mt_suretyship = 1 THEN 'Mitra Surety Bond'
				when d.id_mt_suretyship = 2 THEN 'PT Bank DKI'
				when d.id_mt_suretyship = 3 THEN 'Mitra Custom Bond'
			else
					nama_bank
			END mitra,
 
        --count(distinct d.nomor_sk) jumlah_sertifikat,
	    --count(distinct a.id_transaksi_suretyship) jumlah_nasabah,
    	a.nilai_bond total_bond,
    	COALESCE(nilai_penjaminan, nilai_bond) total_penjaminan,
    	--sum((coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0))*(100-coalesce(a.persen_reasuransi,0))/100) as outstanding_or,
			--sum(case when p.status_kolektibilitas =1 then (coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)) *((100-coalesce(a.persen_reasuransi,0))/100) else 0 end) as outstanding_or,
			 --sum(coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)) as outstanding,
	    --sum(case when p.status_kolektibilitas =1 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as outstanding,
  			sum(case when p.status_kolektibilitas =1 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as lancar,
  	    sum(case when p.status_kolektibilitas =2 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as dpk,
  	    sum(case when p.status_kolektibilitas =3 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as kl,
  	    sum(case when p.status_kolektibilitas =4 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as diragukan,
  	    sum(case when p.status_kolektibilitas =5 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end) as macet,
			  sum(case when p.status_kolektibilitas =1 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end)*(100-coalesce(a.persen_reasuransi,0))/100 as lancar_or,
  	    sum(case when p.status_kolektibilitas =2 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end)*(100-coalesce(a.persen_reasuransi,0))/100 as dpk_or,
				sum(case when p.status_kolektibilitas =3 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end)*(100-coalesce(a.persen_reasuransi,0))/100 as kl_or,
				sum(case when p.status_kolektibilitas =4 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end)*(100-coalesce(a.persen_reasuransi,0))/100 as diragukan_or,
				sum(case when p.status_kolektibilitas =5 then coalesce(p.tunggakan_pokok,0)+ coalesce(p.tunggakan_hutang,0)+ coalesce(p.bunga,0)+ coalesce(p.denda,0)else 0 end)*(100-coalesce(a.persen_reasuransi,0))/100 as macet_or
	    from
    	    tr_transaksi_suretyship a
    	    inner join mt_jenis_suretyship b on a.id_jenis_suretyship = b.id_jenis_suretyship
    	    inner join mt_suretyship c on b.id_mt_suretyship = c.id_mt_suretyship
    	    inner join tr_sertifikat_penjaminan d on a.id_sertifikat = d.id_sertifikat
    	    inner join mt_principal e on a.id_principal = e.id_principal
					left JOIN mt_bank f on f.id_mt_bank = d.id_mt_bank
    	    left join 
    	    (
		select 
			id_transaksi_suretyship,
			max(id_pengendalian) id_pengendalian 
		from 
			tr_pengendalian
		where 
			tanggal_angsuran <='$date2'
		group by 
			id_transaksi_suretyship
    	    )pengendalian on a.id_transaksi_suretyship = pengendalian.id_transaksi_suretyship
    	    left join tr_pengendalian p on p.id_pengendalian=pengendalian.id_pengendalian
		where --a.flag_deletion is null and --b.id_mt_suretyship not in (1,2,3) and

		 flag_deletion is null and 		
		 flag_delete is null and 
		( 
			( tgl_sk >='$tgl1' and  tgl_sk <='$tgl2'  and 
				( tgl_sk_cetak is null or ( tgl_sk_cetak is not null and d.id_reference is null) ) 
			) OR  
			
			( tgl_sk_cetak >='$tgl1' and  tgl_sk_cetak <='$tgl2' and d.id_reference is not null)
		)
		{$sql}
 		GROUP BY nama_principal,
		nomor_sk, mitra, d.id_mt_bank , nilai_penjaminan, nilai_bond, persen_reasuransi, a.id_transaksi_suretyship, c.suretyship
)

SELECT 
 suretyship,
 count( DISTINCT nomor_sk) jumlah_sertifikat, 
 count( DISTINCT id_transaksi_suretyship) jumlah_nasabah,
 sum(total_bond) total_bond, 
 sum(total_penjaminan) total_penjaminan,
 sum(lancar+dpk+kl+diragukan+macet) outstanding,
 sum(lancar_or+dpk_or+kl_or+diragukan_or+macet_or) outstanding_or,
 sum(lancar_or) lancar_or, 
 sum(dpk_or) dpk_or,
 sum(kl_or) kl_or, 
 sum(diragukan_or) diragukan_or, 
 sum(macet_or) macet_or
 
 from query_pengendalian_rincian  where (lancar+dpk+kl+diragukan+macet) > 0  and id_transaksi_suretyship NOT IN (SELECT id_transaksi_suretyship from tr_klaim a
join tr_keputusan_klaim b ON a.id_klaim = b.id_klaim
where flag_keputusan != 2 and flag_pengendalian ISNULL) 
 group by
	       suretyship