<?php 
include("../koneksi.php");
$key=$_POST['key'];
if ($key=='setor') {
$thn=$_POST['thn'];
$nama_verifikator=$_POST['nama_verifikator'];
$a=strtoupper($_POST['a']);
$b=strtoupper($_POST['b']);
$c=strtoupper($_POST['c']);
$d=pg_query("SELECT l.id,l.tanggal_setor
                                    FROM pekerjaan p
                                    INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
                                    INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
                                    INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
                                    INNER JOIN rekanan n ON (n.id=p.rekanan_id)
                                    WHERE s.thn='$thn' AND l.nama_verfikator='$nama_verifikator' AND l.status_berkas='FIX' AND l.tanggal_lunas IS NULL
                                    ".($a == 'ALL' ? "" : "
                                        ".($a == 'FUNGSIONAL' ? "AND LEFT(s.kegiatan,2) NOT IN ('**')" : "AND LEFT(s.kegiatan,2) IN ('**')")."
                                        ")."
                                    ".($b == 'ALL' ? "" : "
                                        ".($b == 'NON UANG MUKA' ? "AND l.status_um='Non Uang Muka'" : "AND l.status_um='Uang Muka'")."
                                        ")."
                                    ".($c == 'ALL' ? "" : "AND l.tanggal_setor IS NULL")."
                                    ");
                                    while(($r=pg_fetch_array($d)) !== FALSE) {
                                    	$id=$r[0];
                                        $tanggal_setor=$r[1];
                                        if ($tanggal_setor==NULL) {
                                    	   pg_query("UPDATE vr_pelunasan SET tanggal_setor=NOW() WHERE id='$id'");
                                        } else {}
                                    }
} else
if ($key=='verifikasi') {
$bulan = array(
                '01' => 'Jan',
                '02' => 'Feb',
                '03' => 'Mar',
                '04' => 'Apr',
                '05' => 'Mei',
                '06' => 'Jun',
                '07' => 'Jul',
                '08' => 'Ags',
                '09' => 'Sep',
                '10' => 'Okt',
                '11' => 'Nov',
                '12' => 'Des',
        );
$bulan[date('m')];

$thn=$_POST['thn'];
$user_nama=$_POST['nama_verifikator'];
$a=strtoupper($_POST['a']);
$b=strtoupper($_POST['b']);
$c=strtoupper($_POST['c']);
$tm1=$_POST['m'];
$tm1=str_replace('/','-',$tm1);
$tt1=date('Y',strtotime($tm1));
$bb1=date('m',strtotime($tm1));
$hh1=date('d',strtotime($tm1));

$ts1=$_POST['s'];
$ts1=str_replace('/','-',$ts1);
$tt2=date('Y',strtotime($ts1));
$bb2=date('m',strtotime($ts1));
$hh2=date('d',strtotime($ts1));

/*
$sub_kegiatan_id=$_POST['sub_kegiatan_id'];

$d3=pg_query("SELECT kegiatan FROM sub_kegiatan WHERE id='$sub_kegiatan_id'");
while($r3=pg_fetch_array($d3)) {$kegiatan_pengadaan=$r3[0];}
*/

if ($a=='SUBSIDI') {$nota_dinas_id=0;} else {$nota_dinas_id=1;}
if ($b=='RAMPUNG') {$status_um='Non Uang Muka';} else {$status_um='Uang Muka';}


$a=$_POST['sp_peg_id'];
$pecah=explode(',',$a);
foreach($pecah as $selected) {


/* ===================================== awal =================================== */

$d=pg_query("SELECT g.id,g.tgl_setor_verif,g.status,p.nama,u.sub_kegiatan_id,k2.kegiatan
FROM sp_kwi k
INNER JOIN sp_rek u ON (k.sp_rek_id=u.id)
INNER JOIN sub_kegiatan k2 ON (k2.id=u.sub_kegiatan_id)
LEFT OUTER JOIN sp_spt t ON (t.sp_kwi_id=k.id)
LEFT OUTER JOIN sp_peg g ON (g.sp_spt_id=t.id)
LEFT OUTER JOIN data_pegawai p ON (g.data_pegawai_id=p.id)
LEFT OUTER JOIN satuan_kerja s ON (p.satuan_kerja=s.id)
WHERE g.id=$selected
");

while(($r=pg_fetch_array($d)) !== FALSE) {
 $sub_kegiatan_id=$r['sub_kegiatan_id'];
 $kegiatan_pengadaan=$r['kegiatan'];
 $cad1=$r['nama'];

 $tv1=$r['tgl_setor_verif'];
 $tv1=str_replace('/','-',$tv1);
 $ttv2=date('Y',strtotime($tv1));
 $bbv2=date('m',strtotime($tv1));
 $hhv2=date('d',strtotime($tv1));

 if ($r['status']=='v') {echo "0";} else {
 
 	$cek_nosppd=pg_query("SELECT id FROM no_sppd WHERE thn='$thn' AND pekerjaan_id=NULL");
 	$row_nosppd = pg_fetch_row($cek_nosppd);
    if ($row_nosppd[0] > 0) {	
    	$dxx=pg_query("SELECT MIN(nomor) FROM no_sppd WHERE thn='$thn' AND pekerjaan_id=NULL");
		while($rxx=pg_fetch_array($dxx)) {$no_sppdxx=$rxx[0];}
    } else {
 		$dxx=pg_query("SELECT COALESCE(MAX(nomor),0) FROM no_sppd WHERE thn='$thn'");
		while($rxx=pg_fetch_array($dxx)) {$no_sppdxx=$rxx[0]+1;}
    }
 
    $no_pinjam=(int) substr($no_sppdxx,0,5);
    $urut_sppd=sprintf("%05s",$no_pinjam);
    $no_sp='SPPD '.$urut_sppd.' '.$bulan[date('m')].' '.substr($thn,2);
 
	$total_um=0;$total_realisasi=0;$b0=0;$tr=0;$pn=0;$bi=0;
	$sp_peg_id=$r['id']; 
	$t0=pg_query("SELECT COALESCE((
	(uang_harian*lama)+
	(representasi*lama)+
	(penginapan_br*lama_inap_br)+
	sewa_kendaraan+
	bbm+
	tol+
	(bantrans*lama_bt)+
	trans_br+
	pcr
	),0) FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='B'");
	while($r0=pg_fetch_array($t0)) {$b0=$r0[0];}

	$t1=pg_query("SELECT COALESCE(SUM(harga),0) FROM sp_transportasi WHERE sp_peg_id='$sp_peg_id'");
	while($r1=pg_fetch_array($t1)) {$tr=$r1[0];}

	$t2=pg_query("SELECT COALESCE((penginapan*lama),0) FROM sp_penginapan WHERE sp_peg_id='$sp_peg_id'");
	while($r2=pg_fetch_array($t2)) {$pn=$r2[0];}

	$t3=pg_query("SELECT COALESCE(((uang_harian*lama)+(representasi*lama)+sewa_kendaraan+bbm+tol+(bantrans*lama_bt)+pcr),0) FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='P'");
	while($r3=pg_fetch_array($t3)) {$bi=$r3[0];}

	$total_um=$b0; 
	$total_realisasi=$tr+$pn+$bi;
 
  	$nilai_sp=$total_realisasi;
  	$nilai_sp=str_replace('.','',$nilai_sp);
  	$nilai_sp=str_replace(',','',$nilai_sp);

  	$nilai_realisasi=$total_realisasi;
  	$nilai_realisasi=str_replace('.','',$nilai_realisasi);
  	$nilai_realisasi=str_replace(',','',$nilai_realisasi);
 
    $ins_add = pg_query("INSERT into pekerjaan (
    sub_kegiatan_id,
    no_sp,
    kegiatan_pengadaan,
    nilai_sp,
    tgl_input,
    status,
    nilai_realisasi,
    nota_dinas_id,
    user_nama,
    user_satker,
    cad1,
    cad2
    ) values (
    '$sub_kegiatan_id',
    '$no_sp',
    '$kegiatan_pengadaan',
    '$nilai_sp',
    NOW(),
    'Penyerapan',
    '$nilai_sp',
    '$nota_dinas_id',
    '$user_nama',
    'PERBEND',
    '$cad1',
    '$cad2'
    )
    ");

    $e=pg_query("SELECT id FROM pekerjaan WHERE
    sub_kegiatan_id='$sub_kegiatan_id' AND
    no_sp='$no_sp' AND
    kegiatan_pengadaan='$kegiatan_pengadaan' AND
    nilai_sp='$nilai_sp' AND
    status='Penyerapan' AND
    nilai_realisasi='$nilai_sp' AND
    nota_dinas_id='$nota_dinas_id' AND
    user_nama='$user_nama' AND
    user_satker='PERBEND' AND
    cad1='$cad1' AND
    cad2='$cad2'
    ");
    while($s=pg_fetch_array($e)) {$pekerjaan_id=$s[0];}
	
 	$insno=pg_query("INSERT INTO no_sppd (thn,nomor,pekerjaan_id) VALUES ('$thn','$no_sppdxx','$pekerjaan_id')");
 
    $ins_re=pg_query("INSERT INTO vr_realisasi (pekerjaan_id,st_buku,st_termin) VALUES ('$pekerjaan_id','non-buku','non-termin')");

    $f=pg_query("SELECT id FROM vr_realisasi WHERE 
        pekerjaan_id='$pekerjaan_id' AND
        st_buku='non-buku' AND
        st_termin='non-termin'
    ");
    while($t=pg_fetch_array($f)) {$vr_realisasi_id=$t[0];}

    $ins_lu=pg_query("INSERT INTO vr_pelunasan (
        vr_realisasi_id,
        tanggal_input,
        nilai_pelunasan,
        kwitansi,
        faktur_penjualan,
        nota_dinas,
        faktur_pajak,
        ssp_ppn,
        ssp_pph,
        ba_hp_admin,
        ba_st,
        sp_spk,
        status_berkas,
        tanggal_setor,
        status_um,
        ba_denda,
        ceklist,
        nama_verfikator,
        data_ke,
  		honor,
  		hadir,
  		tor,
  		spt,
  		pendukung,
  		ba_um,
  		ba_ujicoba,
  		ba_pembayaran,
  		ba_kemajuan,
        st_setor_verif,
        tgl_terima_verif
    ) VALUES (
        '$vr_realisasi_id',
        NOW(),
        '$nilai_sp',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        'FIX',
        '$ttv2-$bbv2-$hhv2',
        '$status_um',
        't',
        't',
        '$user_nama',
        '1',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        't',
        'SETOR',
        NOW()
    )");
 
    $upd_peg=pg_query("UPDATE sp_peg SET status='v',pekerjaan_id='$pekerjaan_id' WHERE id='$sp_peg_id'");
 
 	$de=pg_query("SELECT id FROM vr_pelunasan WHERE vr_realisasi_id='$vr_realisasi_id' AND nilai_pelunasan='$nilai_sp' 
    AND status_berkas='FIX' AND tanggal_setor='$ttv2-$bbv2-$hhv2' AND status_um='$status_um' AND nama_verfikator='$user_nama'
    ");
 	while($se=pg_fetch_array($de)) {$vr_pelunasan_id=$se[0];}
 
 	$ins_ba=pg_query("INSERT INTO riwayat_ba (pekerjaan_id,berita_acara_id,tanggal_ba,nomor_ba,nilai_ba,vr_pelunasan_id) VALUES (
    '$pekerjaan_id',2,'$ttv2-$bbv2-$hhv2','$no_sp','$nilai_sp','$vr_pelunasan_id')"); 
 }

}

/* ===================================== akhr =================================== */

}
} else
if ($key=='batal') {
	$id=$_POST['id'];
	$ceklunas=pg_query("SELECT no_kwi_bend FROM vr_pelunasan WHERE vr_realisasi_id=(SELECT id FROM vr_realisasi WHERE pekerjaan_id=(SELECT pekerjaan_id FROM sp_peg WHERE id=$id))");
	while($rceklunas=pg_fetch_array($ceklunas)) {$no_kwi_bend=$rceklunas[0];}
	if ($no_kwi_bend==NULL) {
		$d=pg_query("SELECT pekerjaan_id FROM sp_peg WHERE id=$id");
		while($r=pg_fetch_array($d)) {$pekerjaan_id=$r[0];}
		// pg_query("DELETE FROM no_sppd WHERE pekerjaan_id=$pekerjaan_id");
		pg_query("UPDATE no_sppd SET pekerjaan_id=NULL WHERE pekerjaan_id=$pekerjaan_id");
		pg_query("DELETE FROM riwayat_ba WHERE pekerjaan_id=$pekerjaan_id");

		$d1=pg_query("SELECT id FROM vr_realisasi WHERE pekerjaan_id=$pekerjaan_id");
		while($r1=pg_fetch_array($d1)) {$vr_realisasi_id=$r1[0];}

		pg_query("DELETE FROM vr_pelunasan WHERE vr_realisasi_id=$vr_realisasi_id");
		pg_query("DELETE FROM vr_realisasi WHERE pekerjaan_id=$pekerjaan_id");
		pg_query("UPDATE sp_peg SET status=NULL,pekerjaan_id=NULL WHERE id=$id");
		pg_query("DELETE FROM pekerjaan WHERE id=$pekerjaan_id");
    } else {
    	echo "gakisobatal";
    }
}
?>