<?php
    $host        = "host = 172.16.0.170";
    $port        = "port = 5432";
    //$dbname      = "dbname = inventory";
    $credentials = "user =postgres password=Sriwijaya36";
    //$db_inventory = pg_connect("$host $port $dbname $credentials");

    //$dbname      = "dbname = new_simfoni";
    //$db_simfoni = pg_connect("$host $port $dbname $credentials");
   
    //$dbname      = "dbname = mis";
   // $db_mis = pg_connect("$host $port $dbname $credentials");

    $dbname      = "dbname = new_harmoni";
    $db_hrm = pg_connect("$host $port $dbname $credentials");

    $datenow = date ('2023-06-02');//date('Y-m-d') // mengambil tanggal sekarang
	
	$yesterday = date("Y-m-d", strtotime("yesterday"));
	$querygetcuti = "select * from kalender where date like '%$datenow%' and lower(kategori_nm) like '%cuti%'";
	$allkaryawan = "select 
					karyawan.nik, karyawan.tlc, karyawan.nama
					from karyawan
					join (select tlc, max(CAST(id_status_karyawan AS INTEGER))id_status_karyawan from status_karyawan group by tlc)a on a.tlc = karyawan.tlc
					join status_karyawan on CAST(status_karyawan.id_status_karyawan AS INTEGER) = a.id_status_karyawan and upper(status) in ('TETAP','KONTRAK')";
	$result = pg_query($db_hrm, $querygetcuti);
	while($cuti=pg_fetch_assoc($result)){
		$result2 = pg_query($db_hrm, $allkaryawan);
		while($karyawan=pg_fetch_assoc($result2)){
			$qcekSeq = "select count(*)+1 jml  from cuti_hrd where (tlc='".$karyawan['tlc']."') and (CAST(tgl_update AS DATE) = '$yesterday' :: DATE)";
			$qcekId = "SELECT max(CAST(replace(id_cuti_hrd,'cuti_hrd','')as integer))+1 as last FROM cuti_hrd";
			$result3 = pg_query($db_hrm, $qcekSeq);
			$result4 = pg_query($db_hrm, $qcekId);
			$cekSeq=pg_fetch_assoc($result3);
			$cekId=pg_fetch_assoc($result4);
			$seq = sprintf('%02d', $cekSeq['jml']);
			$no_cuti = date("d", strtotime("yesterday")) . '.' . $seq . '/CT.ITS/' . $karyawan['tlc'] . '/' . date("m.y").'<br/>';
			$queryinsertcuti_hrd = "insert into cuti_hrd (tlc,jumlah_cuti,tgl_update,id_cuti_hrd,jenis,deskripsi,no_cuti)
			values('".$karyawan['tlc']."',-1,'".date('Y-m-d H:i:s')."','".$cekId['last']."','Pengurangan','".$cuti['info']."','".$no_cuti."')";
			$insert = pg_query($db_hrm, $queryinsertcuti_hrd);
		}
	}
?>
