<?php 
/* Cek apakah sudah memiliki data pelunasan */
$cek=pg_query("SELECT l.id 
FROM pekerjaan p 
INNER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
INNER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE p.id=$id");
 $row_cek = pg_fetch_row($cek);
 if ($row_cek[0] > 0) {
 
 	/* Identifikasi vr_pelunasan_id */
    $cek3=pg_query("SELECT l.id,l.status_npd 
	FROM pekerjaan p 
	INNER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
	INNER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
	WHERE p.id=$id");
    while($rcek3=pg_fetch_array($cek3)) {$vr_pelunasan_id=$rcek3[0];$status_npd=$rcek3[1];}
 
 	/* Cek data NPD(id_npd) rek baru */
 	$cek1=pg_query("SELECT COALESCE(MAX(id_npd),0) FROM vr_pelunasan l
 	INNER JOIN vr_realisasi r ON (r.id=l.vr_realisasi_id)
 	INNER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
 	WHERE p.sub_kegiatan_id=$sub_kegiatan_id");
 	while($rcek1=pg_fetch_array($cek1)) {$id_npd=$rcek1[0];$id_npd_baru=$id_npd+1;}
 
 	/* Cek data NPD(akumulasi+nilai_pelunasan) rek baru pada data terakhir */
 	/*$cek2=pg_query("SELECT COALESCE(akumulasi_npd,0)+COALESCE(nilai_pelunasan,0) FROM vr_pelunasan l
	INNER JOIN vr_realisasi r ON (r.id=l.vr_realisasi_id)
	INNER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
	WHERE p.sub_kegiatan_id=$sub_kegiatan_id
	AND l.id_npd=$id_npd");
 	while($rcek2=pg_fetch_array($cek2)) {$akumulasi_npd=$rcek2[0];}
 	*/
 
 	/* Update */
 	pg_query("UPDATE vr_pelunasan SET id_npd=$id_npd_baru,status_npd='v' WHERE id=$vr_pelunasan_id");
 
 	/* re-seting akumulasi NPD */
 	pg_query("UPDATE vr_pelunasan c
	SET akumulasi_npd = c2.cum_amt
	FROM 
	(
	SELECT c2.id_npd,c2.akumulasi_npd,c2.nilai_pelunasan,c2.status_npd,c2.id,
    CASE WHEN c2.status_npd IS NULL THEN NULL ELSE
	sum(c2.nilai_pelunasan) OVER (PARTITION BY p2.sub_kegiatan_id ORDER BY COALESCE(c2.id_npd,0))-c2.nilai_pelunasan END AS cum_amt
	FROM vr_pelunasan c2 
	INNER JOIN vr_realisasi r2 ON (r2.id=c2.vr_realisasi_id)
	INNER JOIN pekerjaan p2 ON (p2.id=r2.pekerjaan_id)
	WHERE p2.sub_kegiatan_id=$sub_kegiatan_id AND c2.npdman='x'
	) c2
	WHERE c2.id = c.id");
 
 	/* ===================================================================================================================== */
 
 	/* re-seting urutan NPD dari rekening sebelumnya */
 	pg_query("UPDATE vr_pelunasan c
	SET id_npd = c2.seqnum
	FROM 
	(
	SELECT c2.*, row_number() OVER (ORDER BY CAST(id_npd AS INT)) as seqnum 
	FROM vr_pelunasan c2 
	INNER JOIN vr_realisasi r2 ON (r2.id=c2.vr_realisasi_id)
	INNER JOIN pekerjaan p2 ON (p2.id=r2.pekerjaan_id)
	WHERE p2.sub_kegiatan_id=$sub_kegiatan_id_lama AND c2.status_npd='v'
	) c2
	WHERE c2.id = c.id");
 
 	/* re-seting akumulasi NPD dari rekening sebelumnya */
 	pg_query("UPDATE vr_pelunasan c
	SET akumulasi_npd = c2.cum_amt
	FROM 
	(
	SELECT c2.id_npd,c2.akumulasi_npd,c2.nilai_pelunasan,c2.status_npd,c2.id,
    CASE WHEN c2.status_npd IS NULL THEN NULL ELSE
	sum(c2.nilai_pelunasan) OVER (PARTITION BY p2.sub_kegiatan_id ORDER BY COALESCE(c2.id_npd,0))-c2.nilai_pelunasan END AS cum_amt
	FROM vr_pelunasan c2 
	INNER JOIN vr_realisasi r2 ON (r2.id=c2.vr_realisasi_id)
	INNER JOIN pekerjaan p2 ON (p2.id=r2.pekerjaan_id)
	WHERE p2.sub_kegiatan_id=$sub_kegiatan_id_lama AND c2.npdman='x'
	) c2
	WHERE c2.id = c.id");
 } else {}
?>