<?php
include('../koneksi.php');
$key=$_POST['key'];
if ($key=='edit_kode_panggil') {
$id=$_POST['pekerjaan_id'];
$pekerjaan_id=$_POST['pekerjaan_id'];
$sub_kegiatan_id=$_POST['sub_kegiatan_id'];

$skl=pg_query("SELECT sub_kegiatan_id FROM pekerjaan WHERE id=$id"); 
while($rskl=pg_fetch_array($skl)) {$sub_kegiatan_id_lama=$rskl[0];}

pg_query("UPDATE pekerjaan SET sub_kegiatan_id='$sub_kegiatan_id' WHERE id='$pekerjaan_id'");
include('../reset_npd.php');
} else
if ($key=='kendali_cari') {
$no_sp=strtolower($_POST['no_sp']);
$urut_switch=$_POST['urut_switch'];
	if ($urut_switch=='off') {
    	$d=pg_query("SELECT p.id,p.sub_kegiatan_id,s.kd_panggil,p.no_sp FROM pekerjaan p 
		INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
		WHERE LOWER(p.no_sp)='$no_sp'");
		while($r=pg_fetch_array($d)) {$pekerjaan_id=$r[0];$sub_kegiatan_id=$r[1];$kd_panggil=$r[2];$no_spa=$r[3];}
		echo $pekerjaan_id.'$%^'.$sub_kegiatan_id.'$%^'.$kd_panggil.'$%^'.$no_spa;
    } else {
		$d=pg_query("SELECT p.id,p.sub_kegiatan_id,s.kd_panggil,v.nomor,p.no_sp FROM pekerjaan p 
		INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id) 
		INNER JOIN no_verif v ON (p.id=v.pekerjaan_id)
		WHERE LOWER(p.no_sp)='$no_sp'");
		while($r=pg_fetch_array($d)) {$pekerjaan_id=$r[0];$sub_kegiatan_id=$r[1];$kd_panggil=$r[2];$no_verif=$r[3];$no_spa=$r[4];}
		echo $pekerjaan_id.'$%^'.$sub_kegiatan_id.'$%^'.$kd_panggil.'$%^'.$no_verif.'$%^'.$no_spa;
    }
} else
if ($key=='cari_kp') {
  $thn=$_POST['thn'];
  $kd_panggil=strtolower($_POST['kd_panggil']);
  $d=pg_query("SELECT k.id,p.inisial FROM sub_kegiatan k 
  LEFT OUTER JOIN ppkom o ON (o.id=k.ppkom_id) 
  LEFT OUTER JOIN kpa p ON (p.id=k.kpa_id)
  WHERE LOWER(k.kd_panggil)='$kd_panggil' AND k.thn='$thn'");
  while($r=pg_fetch_array($d)) {$id=$r[0];$kpa=$r[1];}
  echo $id.'$%^'.$kpa;
} 
else
if ($key=='cek_subsidi') {
  $sub_kegiatan_id=$_POST['sub_kegiatan_id'];
  $d=pg_query("SELECT LEFT(kegiatan,2) FROM sub_kegiatan WHERE id='$sub_kegiatan_id'");
  while($rd=pg_fetch_array($d)) {
    $subs=$rd[0];
  }
  if ($subs=='**') {
    echo "0";
  } else {
    echo "1";
  }
}
else

if ($key=='simpan_input') {

  $id=$_POST['id'];
  $sub_kegiatan_id=$_POST['sub_kegiatan_id'];
  $rekanan_id=$_POST['rekanan_id'];
  $no_sp=$_POST['no_sp'];
  
  $tgl_mulai_sp=$_POST['tgl_mulai_sp'];
  $tgl_mulai_sp=str_replace('/','-',$tgl_mulai_sp);
  $tm=date('Y',strtotime($tgl_mulai_sp));
  $bm=date('m',strtotime($tgl_mulai_sp));
  $hm=date('d',strtotime($tgl_mulai_sp));

  $tgl_selesai_sp=$_POST['tgl_selesai_sp'];
  $tgl_selesai_sp=str_replace('/','-',$tgl_selesai_sp);
  $ts=date('Y',strtotime($tgl_selesai_sp));
  $bs=date('m',strtotime($tgl_selesai_sp));
  $hs=date('d',strtotime($tgl_selesai_sp));

  $kegiatan_pengadaan=$_POST['kegiatan_pengadaan'];

  $nilai_sp=$_POST['nilai_sp'];
  $nilai_sp=str_replace('.','',$nilai_sp);
  $nilai_sp=str_replace(',','',$nilai_sp);

  $jenis_rek=$_POST['jenis_rek'];
  if ($jenis_rek=='SUBSIDI') {$nota_dinas_id=0;} else {$nota_dinas_id=1;}

  $jenis_kegiatan_id=$_POST['jenis_kegiatan_id'];
  $user_nama=$_POST['cek_nampeg'];
  

  $kp=pg_query("SELECT k.inisial FROM kpa k INNER JOIN sub_kegiatan s ON (s.kpa_id=k.id) WHERE s.id='$sub_kegiatan_id'");
  while($rkp=pg_fetch_array($kp)) {$spkpa=$rkp[0];}

  $cek_add=pg_query("SELECT id FROM pekerjaan WHERE id='$id'");
  $row_add = pg_fetch_row($cek_add);
  if ($row_add[0] > 0) {  
  	$upd_add = pg_query("UPDATE pekerjaan SET
    sub_kegiatan_id='$sub_kegiatan_id', 
    rekanan_id='$rekanan_id',
    tgl_mulai_sp='$tm-$bm-$hm',
    tgl_selesai_sp='$ts-$bs-$hs',
    kegiatan_pengadaan='$kegiatan_pengadaan',
    nilai_sp='$nilai_sp',
    tgl_input=NOW(),
    jenis_kegiatan_id='$jenis_kegiatan_id',
    nilai_realisasi='$nilai_sp',
    nota_dinas_id='$nota_dinas_id',
    user_nama='$user_nama',
    no_sp='$no_sp'
    WHERE id='$id'"); 
    
    $cek_ada=pg_query("SELECT l.id FROM vr_realisasi r 
    INNER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id) WHERE r.pekerjaan_id='$id'");
  	$row_ada = pg_fetch_row($cek_ada);
    if ($row_ada[0] > 0) {
        $cek_ada3=pg_query("SELECT l.id,l.ppn,l.pph_22,l.pph_23 FROM vr_realisasi r 
    	INNER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id) WHERE r.pekerjaan_id='$id'");
    	while($rad=pg_fetch_array($cek_ada3)) {$vr_pelunasan_id=$rad[0];$ppn=$rad[1];$pph_22=$rad[2];$pph_23=$rad[3];}
		$up=pg_query("UPDATE vr_pelunasan SET nilai_pelunasan='$nilai_sp' WHERE id='$vr_pelunasan_id'");
    } else {}
    echo $id;
  } else {
   $urut_switch=$_POST['urut_switch'];
   if ($urut_switch=='off') {
   		$no_sp=$_POST['no_sp'];
   		$ins_add = pg_query("INSERT into pekerjaan (
    	sub_kegiatan_id,
    	rekanan_id,
    	no_sp,
    	tgl_mulai_sp,
    	tgl_selesai_sp,
    	kegiatan_pengadaan,
    	nilai_sp,
    	tgl_input,
    	status,
    	jenis_kegiatan_id,
    	nilai_realisasi,
    	nota_dinas_id,
    	user_nama,
    	user_satker,
        urut
    	) values (
    	'$sub_kegiatan_id',
    	'$rekanan_id',
    	'$no_sp',
    	'$tm-$bm-$hm',
    	'$ts-$bs-$hs',
    	'$kegiatan_pengadaan',
    	'$nilai_sp',
    	NOW(),
    	'Penyerapan',
    	'$jenis_kegiatan_id',
    	'$nilai_sp',
    	'$nota_dinas_id',
    	'$user_nama',
    	'AKUNTANSI',
        'off'
     	)
  		");
        echo 'baru$%^'.$no_sp;
   } else {
  	$bulan = array(
                '1' => 'Jan',
                '2' => 'Feb',
                '3' => 'Mar',
                '4' => 'Apr',
                '5' => 'Mei',
                '6' => 'Jun',
                '7' => 'Jul',
                '8' => 'Ags',
                '9' => 'Sep',
                '10' => 'Okt',
                '11' => 'Nop',
                '12' => 'Des',
        );
	$thn=$_POST['thn'];
	$bln=$_POST['bulan'];
	$th=substr($thn,2);
	$d=pg_query("SELECT COALESCE(MAX(nomor),0) FROM no_verif WHERE thn='$thn'");
	while($r=pg_fetch_array($d)) {$no_verif=$r[0]+1;}

	$no_pinjam=(int) substr($no_verif,0,5);
	$no_verif=sprintf("%05s",$no_pinjam);

	$no_sp=$no_verif.' '.$bulan[$bln].' '.$th.' '.$spkpa;  
  
	$ins_add = pg_query("INSERT into pekerjaan (
    sub_kegiatan_id,
    rekanan_id,
    no_sp,
    tgl_mulai_sp,
    tgl_selesai_sp,
    kegiatan_pengadaan,
    nilai_sp,
    tgl_input,
    status,
    jenis_kegiatan_id,
    nilai_realisasi,
    nota_dinas_id,
    user_nama,
    user_satker
    ) values (
    '$sub_kegiatan_id',
    '$rekanan_id',
    '$no_sp',
    '$tm-$bm-$hm',
    '$ts-$bs-$hs',
    '$kegiatan_pengadaan',
    '$nilai_sp',
    NOW(),
    'Penyerapan',
    '$jenis_kegiatan_id',
    '$nilai_sp',
    '$nota_dinas_id',
    '$user_nama',
    'AKUNTANSI'
     )
  	");
  
  	$asu=pg_query("SELECT id FROM pekerjaan WHERE 
  	sub_kegiatan_id='$sub_kegiatan_id' AND
  	rekanan_id='$rekanan_id' AND
  	no_sp='$no_sp' AND
  	tgl_mulai_sp='$tm-$bm-$hm' AND
  	tgl_selesai_sp='$ts-$bs-$hs' AND
  	kegiatan_pengadaan='$kegiatan_pengadaan' AND
  	nilai_sp='$nilai_sp' AND
  	status='Penyerapan' AND
  	jenis_kegiatan_id='$jenis_kegiatan_id' AND
  	nilai_realisasi='$nilai_sp' AND
  	nota_dinas_id='$nota_dinas_id'
  	");
  	while($rasu=pg_fetch_array($asu)) {$pekerjaan_id=$rasu[0];}
    
  	pg_query("INSERT INTO no_verif (thn,nomor,pekerjaan_id) VALUES ('$thn','$no_verif','$pekerjaan_id')");
  	echo 'baru$%^'.$no_sp;
   }
  }
} else
if ($key=='hapus_input') {
  $pekerjaan_id=$_POST['pekerjaan_id'];
  $c=pg_query("SELECT id FROM vr_realisasi WHERE pekerjaan_id='$pekerjaan_id'");
  while($x=pg_fetch_array($c)) {$vr_realisasi_id=$x[0];}

  $c=pg_query("SELECT id FROM vr_pelunasan WHERE vr_realisasi_id='$vr_realisasi_id'");
  while($x=pg_fetch_array($c)) {$vr_pelunasan_id=$x[0];}
  pg_query("DELETE FROM pengerjaan_npd WHERE vr_pelunasan_id='$id'");
  pg_query("DELETE FROM vr_ba WHERE vr_pelunasan_id='$vr_pelunasan_id'");
  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("DELETE FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM scan_pekerjaan WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM pengerjaan_ba WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM pengerjaan_adminers WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM list_relasi WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM no_verif WHERE pekerjaan_id='$pekerjaan_id'");
  pg_query("DELETE FROM pekerjaan WHERE id='$pekerjaan_id'");
} else
if ($key=='cek_no_verif') {
$bulan = array(
                '1' => 'Jan',
                '2' => 'Feb',
                '3' => 'Mar',
                '4' => 'Apr',
                '5' => 'Mei',
                '6' => 'Jun',
                '7' => 'Jul',
                '8' => 'Ags',
                '9' => 'Sep',
                '10' => 'Okt',
                '11' => 'Nop',
                '12' => 'Des',
        );

$thn=$_POST['thn'];
$bln=$_POST['bulan'];
$th=substr($thn,2);
$d=pg_query("SELECT COALESCE(MAX(nomor),0) FROM no_verif WHERE thn='$thn'");
while($r=pg_fetch_array($d)) {$abc=$r[0]+1;}

$no_pinjam=(int) substr($abc,0,5);
$no_verif=sprintf("%05s",$no_pinjam);

echo $no_verif.' '.$bulan[$bln].' '.$th.'$%^'.$abc;
}
?>