<?php 

define("SITE_KEY", 'BismillahiAllahuAkbar');

function conn($db){
	$host = "localhost";
	$database = $db;
	$user = "berkahgl_root";
	$pass = "berkahgl_Sha1&Md5";
	$koneksi = new PDO("mysql:host=".$host.";dbname=".$database,$user,$pass);
	return $koneksi;
}

function koneksi($db){
	$host = "localhost";
	$database = $db;
	$user = "berkahgl_root";
	$pass = "berkahgl_Sha1&Md5";
	$con = mysqli_connect($host, $user, $pass, $database);

	return $con;
}

function tanggal($tanggal){
	# format tanggal 1 Jan 2018
	$tanggal_format = date('j-M-Y', strtotime($tanggal));
	return $tanggal_format;
}

function GetRangeAyat($urutan, $tanggal){
	$hari_ke = GetHariKe($tanggal); 
	$jml_hari_khatam = JmlHariKhatam($tanggal); 
	$maqro = GetMaqro($urutan, $tanggal, $hari_ke, $jml_hari_khatam);
	return $maqro;
}

function JmlHariKhatam($tanggal){
	$tgl_mulai1 = "20170707";
	$tgl_mulai2 = "20180118";
	//
	$tgl = explode('-',$tanggal);
	$tgl_cek = $tgl[0].$tgl[1].$tgl[2];

	# jika ada perubahan hari khatam maka edit disini
	if ($tgl_cek >= $tgl_mulai2) {
		$hari_khatam = 6;
	}
	elseif($tgl_cek >= $tgl_mulai1){
		$hari_khatam = 3;
	}

	return $hari_khatam;
}

function GetMaqro($no_urut, $tanggal, $hari_ke, $jml_hari_khatam){
	# jumlah ayat Al-Qur'an
	$jml_all_ayat = 6236;

	# jumlah ayat per hari
	$jml_part_ayat = floor($jml_all_ayat / $jml_hari_khatam);
	$sisa_jml_part_ayat = $jml_all_ayat % $jml_hari_khatam; 
	
	$dbh = conn('berkahgl_portal');
    $sql = "SELECT COUNT(*) FROM anggota_tadarus WHERE (tanggal_on<='$tanggal' AND tanggal_off>'$tanggal') OR (tanggal_on<='$tanggal' AND tanggal_off='0000-00-00') ";
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $jml_anggota = $stmt->fetchColumn();

	# cari jumlah ayat maqro
	if (($hari_ke == $jml_hari_khatam) AND ($no_urut == $jml_anggota)) {
		$ayat_tambahan = $sisa_jml_part_ayat;
	}else{
		$ayat_tambahan = 0;
	}

	$jml_ayat_maqro = floor($jml_part_ayat / $jml_anggota);
	$sisa_jml_ayat_maqro = $jml_part_ayat % $jml_anggota;

	if ($no_urut <= $sisa_jml_ayat_maqro) { 
		$jml_ayat_maqro = $jml_ayat_maqro + 1;
		$urutan_ayat_akhir = ($no_urut * $jml_ayat_maqro) + (($hari_ke - 1) * $jml_part_ayat);
		$urutan_ayat_awal = $urutan_ayat_akhir - $jml_ayat_maqro + 1;
	}else{ 
		$urutan_ayat_akhir = ($no_urut * $jml_ayat_maqro) + (($hari_ke - 1) * $jml_part_ayat) + $sisa_jml_ayat_maqro + $ayat_tambahan;
		$urutan_ayat_awal = ($urutan_ayat_akhir - $jml_ayat_maqro) + 1;		
	}
	
	$maqro = $urutan_ayat_awal . '-' .$urutan_ayat_akhir;
	return $maqro;
}


function GetHariKe($tanggal){
	# tanggal awal isi manual 
	$tanggal_awal = "2017-07-10";
	$start_date = new DateTime("2017-07-06");
	$end_date = new DateTime($tanggal);
	$interval = $start_date->diff($end_date);
	$selisih = $interval->days;	
	# cari jumlah hari khatam pada tanggal tersebut
	$jml_hari_khatam = JmlHariKhatam($tanggal);
	# cari jumlah hari minggu dan hari libur
//	$jml_minggu = jml_minggu($tanggal_awal, $tanggal);
//	$jml_libur_nasional = jml_libur_nasional($tanggal_awal, $tanggal);
	# selisih dikurangi dengan hari minggu dan hari libur
//	$selisih = $selisih - $jml_minggu - $jml_libur_nasional;
	# cari hari ke dari jumlah hari khatam
	$modulo = $selisih % $jml_hari_khatam;
	if ($modulo != 0) {
		$hari_ke = $modulo;
	}else{
		$hari_ke = $jml_hari_khatam;
	}

	return $hari_ke;
}


function GetAyat($maqro){
	$exp = explode('-',$maqro);
	$urutan_ayat_awal = $exp[0];
	$urutan_ayat_akhir = $exp[1];
	//echo $urutan_ayat_awal . ' ' .$urutan_ayat_akhir;

	$no = 1;	
	$db = conn('berkahgl_portal');
    $sql = "SELECT * FROM ayat_alquran WHERE no_urut>='$urutan_ayat_awal' AND no_urut<='$urutan_ayat_akhir' GROUP BY no_surat";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    while ( $data = $stmt->fetch() ) {
		# cari ayat awal dan akhir dari surat
	    $q_ayat_max = "SELECT MAX(ayat) AS ayat_akhir, MIN(ayat) AS ayat_awal FROM ayat_alquran WHERE no_urut>='$urutan_ayat_awal' AND no_urut<='$urutan_ayat_akhir' AND no_surat='$data[no_surat]'";
	    $d_stmt = $db->prepare($q_ayat_max);
	    $d_stmt->execute();
	    $d_ayat = $d_stmt->fetch();

		$arr_ayat[$no][1] = $data['no_surat'];
		$arr_ayat[$no][2] = $d_ayat['ayat_awal'];
		$arr_ayat[$no][3] = $d_ayat['ayat_akhir'];

		$no++;
    }

	for ($i=1; $i <= count($arr_ayat) ; $i++) { 
		$nama_surat = GetNamaSurat($arr_ayat[$i][1]);
		$ayat[] = 'QS. '. $nama_surat.' ('.$arr_ayat[$i][1].') : '.$arr_ayat[$i][2].'-'.$arr_ayat[$i][3];
	}
	# jika hanya dalam satu surat
	$ayat_baca = $ayat[0];
	# jika lebih dari satu surat
	if (count($ayat) > 1) {
		for ($i=1; $i < count($ayat); $i++) { 
			$ayat_baca = $ayat_baca . ' + '. $ayat[$i];
		}
	}
	
	return $ayat_baca;
}

function GetNamaSurat($no_surat){
	$db = conn('berkahgl_portal');
    $sql = "SELECT nama FROM surat_alquran WHERE no_surat='$no_surat' ";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $data = $stmt->fetch();

	return $data['nama'];
}


# fungsi warning sakit farm
/*function warning_kondisi_sakit(){    
	date_default_timezone_set('Asia/Jakarta');  
	$hasil = "Alhamdulillah .. Tidak ada ayam sakit 😍😍";  
	$hari=date("Y-m-d");
    $kmrn = strtotime('-1 day', strtotime($hari));
    $kemarin = date('Y-m-d', $kmrn);

	$con = koneksi('berkahgl_portal');
    $qunit = mysqli_query($con,"select db from berkahgl_portal.unit where berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BMM%' or  berkahgl_portal.unit.id_unit like 'BAS%' or  berkahgl_portal.unit.id_unit like 'BUS%' or  berkahgl_portal.unit.id_unit like 'BSB%' order by nm_unit asc");
    while($dUnit = mysqli_fetch_array($qunit)){
        $database="berkahgl_".strtolower($dUnit[db]);

        
        $unit=$dUnit[db];
        $qsakit=mysqli_query($con,"select * from berkahgl_portal.update_status_kondisi where tanggal='$hari' and unit='$unit' and status='Sakit' group by unit order by tanggal desc");
        $jsakit=mysqli_num_rows($qsakit);
        if ($jsakit==0){
            $qussakit=mysqli_query($con,"select populasi,hasil_status  from berkahgl_portal.update_status_kondisi where tanggal='$kemarin' and unit='$unit' and status='Sakit' group by unit order by tanggal desc");
        } else {
            $qussakit=mysqli_query($con,"select populasi,hasil_status from berkahgl_portal.update_status_kondisi where tanggal='$hari' and unit='$unit' and status='Sakit' group by unit order by tanggal desc");
        }
        while ($dussakit=mysqli_fetch_array($qussakit)){
            //farm sakit
            $totsakitpop=$totsakitpop+$dussakit['populasi'];
            $farmsakit=$farmsakit+$dussakit['hasil_status'];
            //farm sehat
            $qsehat=mysqli_query($con,"select * from berkahgl_portal.update_status_kondisi where tanggal='$hari' and unit='$unit' and status='Sehat' group by unit order by tanggal desc");
            $jsehat=mysqli_num_rows($qsehat);
            if ($jsehat==0){
                $qussehat=mysqli_query($con,"select hasil_status from berkahgl_portal.update_status_kondisi where tanggal='$kemarin' and unit='$unit' and status='Sehat' group by unit order by tanggal desc");
            } else {
                $qussehat=mysqli_query($con,"select hasil_status from berkahgl_portal.update_status_kondisi where tanggal='$hari' and unit='$unit' and status='Sehat' group by unit order by tanggal desc");
            }
            $dussehat=mysqli_fetch_array($qussehat);         
            $farmsehat=$farmsehat+$dussehat['hasil_status'];
            //persentase
            $totalfarmkondisi=$farmsehat+$farmsakit;
            $pfarmsakit=$farmsakit/$totalfarmkondisi*100;
        }
    }
    mysqli_close($con);
    if ($pfarmsakit > 3){
    	$hasil = "Sakit *".number_format($pfarmsakit)."*%\n";
        $hasil .= "Farm *".number_format($farmsakit)."*\n";
        $hasil .= "Populasi *".number_format($totsakitpop)."*\n\n";
        $hasil .= "~~~~~~~~~~~~~~~~~~~~~~~~~~~~";
        $hasil .= detail_farm_sakit();
    }

    return $hasil;

}*/

function detail_farm_sakit($unit){
	date_default_timezone_set('Asia/Jakarta');  
	$data_text = "";
	$hasil_reusume = "";
	$hasil = "";  
	$jml_farm = 0;
	$persen_sakit = 0;
	$total_populasi = 0;
	$hari=date("Y-m-d");
	$con = koneksi('berkahgl_portal');	
	$db="berkahgl_portal";

	if ($unit == 'allunit') {
		$qunit = mysqli_query($con,"select * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like 'BMM%' or berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BSB%' or berkahgl_portal.unit.id_unit like 'BUS%'  order by nm_unit asc");
	}else{
		$qunit = mysqli_query($con,"select * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like '$unit%'  order by nm_unit asc");
	}

	while($dUnit = mysqli_fetch_array($qunit)){
		$dtUnit = $dUnit['db'];
		$database="berkahgl_".strtolower($dtUnit);
		$con_unit = koneksi($database);
		$exp = explode(" ", $dUnit['nm_unit']);
		$nama_unit = "BGF ".strtoupper($exp[3]); 
		$data_hasil = FALSE;
		// $hasil .= "\n";
		// $hasil .= "🔸️ ".$nama_unit."\n";

		//$totalpopulasi=0;

		$querry=mysqli_query($con,"select berkahgl_portal.checkpoint.nik, $database.rhpp.id_produksi, $database.rhpp.panenbulan, $database.rhpp.id_farm from berkahgl_portal.checkpoint join $database.rhpp on berkahgl_portal.checkpoint.id_produksi = $database.rhpp.id_produksi where kondisi_ayam='Sakit' and berkahgl_portal.checkpoint.id_produksi not in (select id_produksi from berkahgl_portal.stok_habis) and  berkahgl_portal.checkpoint.id_produksi in (select $database.produksi.id_produksi from $database.produksi) and $database.rhpp.panenbulan='0000-00-00' group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc");

		while ($dataku=mysqli_fetch_array($querry)){
			// id proudksi
			$datprod=$dataku['id_produksi'];

			# cek kondisi terakhir input
			//menapilkan data terakhir yg diinput	

			$quer=mysqli_query($con,"select berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.tanggal_input from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$datprod' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			$dataa=mysqli_fetch_array($quer);
			$id_prod=$dataa['id_produksi'];
			$tgl_input=$dataa['tanggal_input'];

			$query=mysqli_query($con,"select berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.bb from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$id_prod' and berkahgl_portal.checkpoint.kondisi_ayam='sakit' and  berkahgl_portal.checkpoint.tanggal_input='$tgl_input' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");

			while ($data=mysqli_fetch_array($query)){
				$id_produksi=$data['id_produksi'];

				// cek dulu barangkali udah panen habis
				$qpp=mysqli_query($con,"select berkahgl_portal.pengajuan_panen.ket from berkahgl_portal.pengajuan_panen where id_produksi='$datprod' and status_pengajuan='Confirm' order by tanggal_pp desc");
				$dpp=mysqli_fetch_array($qpp);
			//	$jumlahpanen=$dpp['jumlah1']+$dpp['jumlah2']+$dpp['jumlah3'];
				$ket=$dpp['ket'];

				if ($ket != "Normal") {

					$nik=$dataku['nik'];
					//nama ts
					$kary=mysqli_query($con,"select nama from berkahgl_portal.karyawan where nik='$nik'");
					$dkary=mysqli_fetch_array($kary);
					$nm_ts=$dkary['nama'];		

					//farm nama
					$query2=mysqli_query($con_unit,"select $database.farm.nama, $database.farm.desa from $database.produksi join $database.farm on $database.produksi.id_farm = $database.farm.id_farm where $database.produksi.id_produksi='$datprod'");
					$data2=mysqli_fetch_array($query2);
					$nama=$data2['nama'];
					$desa=$data2['desa'];
						
					//populasi
					$qsap1 = mysqli_query($con_unit,"select $database.jual.tanggal, $database.jual.id_barang, $database.jual.id_produksi, $database.jual.id_barang, $database.jual.qty, $database.barang.nama from $database.jual join $database.barang on $database.barang.id_barang = $database.jual.id_barang where $database.jual.id_produksi='$datprod' and $database.jual.id_barang like '%D-%' order by tanggal");
					$jsap1 = mysqli_num_rows($qsap1);
					if ($jsap1 > 0) {
					    while ($dsap1 = mysqli_fetch_array($qsap1)) {
					        $tgl_sap = $dsap1['tanggal'];
					        $namadoc = $dsap1['nama'];
					        //cari retur DOC
					        $retdoc1 = mysqli_query($con_unit,"select $database.beban.qty from $database.beban where id_produksi='$dsap1[id_produksi]' and id_barang='$dsap1[id_barang]' order by tanggal desc ");
					        $retpopulasi1 = 0;
					        $populasi21 = 0;
					        while ($dretdoc1 = mysqli_fetch_assoc($retdoc1)) {
					            $tretdoc1 = $dretdoc1['qty'];
					            $retpopulasi1 = $retpopulasi1 + $tretdoc1;
					        }
					        //kematian ayam
					        $qmati = mysqli_query($con,"select berkahgl_portal.checkpoint.mortalitas from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$dsap1[id_produksi]' order by tanggal_input desc limit 1 ");
					        $matpopulasi = 0;
					        $populasimati = 0;
					        while ($matdoc1 = mysqli_fetch_assoc($qmati)) {
					            $matidoc1 = $matdoc1['mortalitas'];
					            $matpopulasi = $matpopulasi + $matidoc1;
					        }

					        //cari retur DOC
					        $tdoc1 = $dsap1['qty'];
					        $populasi21 = $populasi21 + ($tdoc1);
					     }
					}
				    $populasi = $populasi21 - $retpopulasi1;
					$pop = $populasi - $matpopulasi;

					if ($data_hasil == FALSE):
						$hasil .= "\n";
						$hasil .= "🔹️ ".$nama_unit."\n";

						$data_hasil = TRUE;
					endif;

					# umur hari
					$umurhi = ((abs(strtotime($hari) - strtotime($tgl_sap))) / (60 * 60 * 24));

					$hasil .= "*".$nama ."* (".number_format($populasi,0,',','.')." - ". number_format($matpopulasi,0,',','.')." - ". number_format($pop,0,',','.')." - ".$umurhi." - ".number_format($data['bb'],2,',','.').") _".$nm_ts."_\n";

					# ngitung total
					$jml_farm++;
					$total_populasi +=  $pop;
				}
			}
		}
	}

	$hasil_reusume .= "Farm *".$jml_farm."* \n";
	$hasil_reusume .= "Populasi *".number_format($total_populasi,0,',','.')."* \n\n";
	$hasil_reusume .= "-----------------------\n";

	$data_text = $hasil_reusume.$hasil;

	return $data_text;
}		

function getAbsensiSopir($tanggal){
	$db = conn('berkahgl_portal');
    $sql = "SELECT karyawan.nama, absensi.masuk, absensi.keluar FROM absensi JOIN karyawan ON absensi.nik=karyawan.nik WHERE tanggal='$tanggal' AND (absensi.id_jabatan='DRV' OR absensi.id_jabatan='KDT') ORDER BY nama ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    while ( $data = $stmt->fetch() ) {	
    	echo "`".substr($data['masuk'], 0, 5).' '.substr($data['keluar'], 0, 5)."`  ".strtoupper($data['nama'])."\n";
    }
}


function getAbsensiBerkah($tanggal){
	$db = conn('berkahgl_portal');
	$text = "";

    $sql = "SELECT id_unit, nm_unit from berkahgl_portal.unit where id_unit != 'BFD'  and id_unit != 'BBL'  and id_unit != 'BAS' and id_unit != 'BSB' and id_unit not like 'BUS' order by nm_unit ASC";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    while ( $data = $stmt->fetch() ) {	
    	$text .= "🔹 ".strtoupper($data['nm_unit'])."\n";

    	# mengambil jumlah karyawan
    	# Tidak dihitung karyawan: 900316090119 Diko HS, 090618060222 Internal Control, 810115110185 Saulan Sinaga
    	$sql4 = "SELECT COUNT(nik) as jml_karyawan FROM karyawan WHERE status_aktf='Aktif' AND id_unit='$data[id_unit]' AND nik!='900316090119' AND nik!='090618060222' AND nik!='810115110185' ";
	    $stmt4 = $db->prepare($sql4);
	    $stmt4->execute();
	    $data4 = $stmt4->fetch();
	    $jml_karyawan = $data4['jml_karyawan'];

	    # mengambil data perjalanan dinas
    	$sql5 = "SELECT COUNT(absensi.nik) as jml_dinas FROM absensi WHERE tanggal='$tanggal' AND absensi.absensi='H' AND absensi.id_unit='$data[id_unit]' AND ket like 'PD:%' ";
	    $stmt5 = $db->prepare($sql5);
	    $stmt5->execute();
	    $data5 = $stmt5->fetch();
	    $jml_dinas = $data5['jml_dinas'];	    

    	$sql2 = "SELECT COUNT(IF(absensi='H', nik, NULL)) as H, 
			COUNT(IF(absensi='I', nik, NULL)) as I, 
			COUNT(IF(absensi='S', nik, NULL)) as S,
			COUNT(IF(absensi='C', nik, NULL)) as C,
			COUNT(IF(absensi='TK', nik, NULL)) as TK 
			FROM absensi WHERE tanggal='$tanggal' AND id_unit='$data[id_unit]'";
	    $stmt2 = $db->prepare($sql2);
	    $stmt2->execute();
	    $data2 = $stmt2->fetch();
	    $total  = $data2['H'] + $data2['I'] + $data2['S'] + $data2['C'] + $data2['TK'];

	    $no_data = $jml_karyawan-$total;

	   	$text .= "👤 Karyawan `".$jml_karyawan."` \n";
	    $text .= "▪️ In `".$total."` \n";
	    $text .= "▪️ Out `".$no_data."` \n\n";
	    
	    $text .= "✅ Hadir `".$data2['H']."` \n";
	    $text .= "ℹ️ Ijin `".$data2['I']."` \n";
	    $text .= "🛌 Sakit `".$data2['S']."` \n";
	    $text .= "🏖 Cuti `".$data2['C']."` \n";
	    $text .= "❌ TK `".$data2['TK']."` \n";
	    if ($jml_dinas > 0) {
	    	$text .= "🚙 PD `".$jml_dinas."` \n";
	    }
	    $text .= "\n";
	    
	    

	    $sql3 = "SELECT karyawan.nama, absensi.* FROM absensi JOIN karyawan ON absensi.nik=karyawan.nik WHERE tanggal='$tanggal' AND absensi.absensi!='H'  AND absensi.id_unit='$data[id_unit]' ORDER BY nama ASC ";
	    $stmt3 = $db->prepare($sql3);
	    $stmt3->execute();

	    while ( $data3 = $stmt3->fetch() ) {	
	    	$text .= simbolAbsensi($data3['absensi'])." *".$data3['nama']."* _".strtolower($data3['ket'])."_\n";
	    }

	    if ($jml_dinas > 0) {
		    $sql6 = "SELECT karyawan.nama, absensi.* FROM absensi JOIN karyawan ON absensi.nik=karyawan.nik WHERE tanggal='$tanggal' AND absensi.absensi='H' AND absensi.id_unit='$data[id_unit]' AND absensi.ket like 'PD:%' ORDER BY nama ASC ";
		    $stmt6 = $db->prepare($sql6);
		    $stmt6->execute();

		   	while ($data6 = $stmt6->fetch()) {	
		    	$text .= "🚙 *".$data6['nama']."* _".$data6['ket']."_ \n";
		    }
		}

	    $sql7 = "SELECT karyawan.nama, absensi.* FROM absensi JOIN karyawan ON absensi.nik=karyawan.nik WHERE tanggal='$tanggal' AND absensi.absensi='H' AND absensi.id_unit='$data[id_unit]' AND absensi.masuk >= '07:01:00' ORDER BY nama ASC ";
	    $stmt7 = $db->prepare($sql7);
	    $stmt7->execute();

	   	while ($data7 = $stmt7->fetch()) {	
	    	$text .= "🔒 *".$data7['nama']."* `".$data7['masuk']."` _".strtolower($data7['ket'])."_\n";
	    }

	    $text .= "~~~~~~~~~~~~~~~\n\n";
    }



	$sql = "SELECT COUNT(IF(absensi='H', nik, NULL)) as H, 
			COUNT(IF(absensi='I', nik, NULL)) as I, 
			COUNT(IF(absensi='S', nik, NULL)) as S,
			COUNT(IF(absensi='C', nik, NULL)) as C,
			COUNT(IF(absensi='TK', nik, NULL)) as TK FROM absensi WHERE tanggal='$tanggal'";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $data = $stmt->fetch();
    $total  = $data['H'] + $data['I'] + $data['S'] + $data['TK'] + $data['C'];
    
    $text .= "~~~~~~~~~~~~~~~\n\n";
    $text .= "👤 TOTAL `".$total."` \n";
    $text .= "✅ Hadir `".$data['H']."` \n";
    $text .= "ℹ️ Ijin `".$data['I']."` \n";
    $text .= "🛌 Sakit `".$data['S']."` \n";
    $text .= "🏖 Cuti `".$data['C']."` \n";
    $text .= "❌ TK `".$data['TK']."` \n";
    
    return $text; 

    // $sql = "SELECT karyawan.nama, absensi.* FROM absensi JOIN karyawan ON absensi.nik=karyawan.nik WHERE tanggal='$tanggal' AND absensi.absensi!='H' ORDER BY nama ASC ";
    // $stmt = $db->prepare($sql);
    // $stmt->execute();

    // while ( $data = $stmt->fetch() ) {	
    // 	echo simbolAbsensi($data['absensi'])." *".$data['nama']."* _".$data['id_unit']."_ ".strtolower($data['ket'])."\n";
    // } 
}

function simbolAbsensi($absensi){
	if ($absensi == 'I') {
		$simbol = "ℹ️";
	}elseif ($absensi == 'S') {
		$simbol = "🛌";
	}elseif ($absensi == 'C') {
		$simbol = "🏖";
	}elseif ($absensi == 'TK') {
		$simbol = "❌";
	}else{
		$simbol = "📛";
	}

	return $simbol;
}

function angkaketgl($hr){
    $tgl=date('Y-m-d');
    $newdate = strtotime('-'.$hr.' day',strtotime($tgl));
    $newdate = date('Y-m-d', $newdate);
    return $newdate;
}

function getStokAyam($umur1, $umur2){
	date_default_timezone_set('Asia/Jakarta');  
	$tanggal_chickin_umur1 = angkaketgl($umur1);
	$tanggal_chickin_umur2 = angkaketgl($umur2);
	$data_text = "";
	$hasil_reusume = "";
	$hasil = "";  
	$jml_farm = 0;
	$total_populasi = 0;
	$hari=date("Y-m-d");
	$con = koneksi('berkahgl_portal');	
	$db="berkahgl_portal";

	$qunit = mysqli_query($con,"select * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like 'BMM%' or berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BSB%' or berkahgl_portal.unit.id_unit like 'BUS%'  order by nm_unit asc");

	while($dUnit = mysqli_fetch_array($qunit)){
		$dtUnit = $dUnit['db'];
		$database="berkahgl_".strtolower($dtUnit);
		$con_unit = koneksi($database);
		$exp = explode(" ", $dUnit['nm_unit']);
		$nama_unit = "BGF ".strtoupper($exp[3]); 
		$data_hasil = FALSE;

		$querry=mysqli_query($con,"SELECT $database.produksi.id_produksi, berkahgl_portal.checkpoint.nik, $database.farm.nama from $database.produksi join $database.farm on $database.farm.id_farm = $database.produksi.id_farm join berkahgl_portal.checkpoint on berkahgl_portal.checkpoint.id_produksi=$database.produksi.id_produksi where $database.produksi.id_produksi in (select id_produksi from $database.rhpp where panenbulan='0000-00-00') and $database.produksi.id_produksi not in (select id_produksi from berkahgl_portal.stok_habis) AND berkahgl_portal.checkpoint.tanggal_chickin <= '$tanggal_chickin_umur1' and berkahgl_portal.checkpoint.tanggal_chickin >= '$tanggal_chickin_umur2' GROUP BY $database.produksi.id_produksi ");

		while ($dataku=mysqli_fetch_array($querry)){
			// id proudksi
			$datprod=$dataku['id_produksi'];
		//	$hasil .= $datprod."\n";
			$nama = $dataku['nama'];

			// cek dulu barangkali udah panen habis
			$qpp=mysqli_query($con,"SELECT berkahgl_portal.pengajuan_panen.ket from berkahgl_portal.pengajuan_panen where id_produksi='$datprod' and status_pengajuan='Confirm' order by tanggal_pp desc");
			$dpp=mysqli_fetch_array($qpp);
			$ket=$dpp['ket'];

			if ($ket != "Normal") {

				$nik=$dataku['nik'];
				//nama ts
				$kary=mysqli_query($con,"select nama from berkahgl_portal.karyawan where nik='$nik'");
				$dkary=mysqli_fetch_array($kary);
				$nm_ts=$dkary['nama'];

				//populasi
				$qsap1 = mysqli_query($con_unit,"select $database.jual.tanggal, $database.jual.id_produksi, $database.jual.id_barang, $database.jual.qty, $database.barang.nama from $database.jual join $database.barang on $database.barang.id_barang = $database.jual.id_barang where $database.jual.id_produksi='$datprod' and $database.jual.id_barang like '%D-%' order by tanggal");
				$jsap1 = mysqli_num_rows($qsap1);
				if ($jsap1 > 0) {
				    while ($dsap1 = mysqli_fetch_array($qsap1)) {
				        $tgl_sap = $dsap1['tanggal'];
				        $namadoc = $dsap1['nama'];
				        //cari retur DOC
				        $retdoc1 = mysqli_query($con_unit,"select $database.beban.qty from $database.beban where id_produksi='$dsap1[id_produksi]' and id_barang='$dsap1[id_barang]' order by tanggal desc ");
				        $retpopulasi1 = 0;
				        $populasi21 = 0;
				        while ($dretdoc1 = mysqli_fetch_assoc($retdoc1)) {
				            $tretdoc1 = $dretdoc1['qty'];
				            $retpopulasi1 = $retpopulasi1 + $tretdoc1;
				        }
				        //kematian ayam
				        $qmati = mysqli_query($con,"select berkahgl_portal.checkpoint.mortalitas from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$dsap1[id_produksi]' order by tanggal_input desc limit 1 ");
				        $matpopulasi = 0;
				        $populasimati = 0;
				        while ($matdoc1 = mysqli_fetch_assoc($qmati)) {
				            $matidoc1 = $matdoc1['mortalitas'];
				            $matpopulasi = $matpopulasi + $matidoc1;
				        }

				        //cari retur DOC
				        $tdoc1 = $dsap1['qty'];
				        $populasi21 = $populasi21 + ($tdoc1);
				     }
				}
			    $populasi = $populasi21 - $retpopulasi1;
				$pop = $populasi - $matpopulasi;

				if ($data_hasil == FALSE):
					$hasil .= "\n";
					$hasil .= "🔹️ ".$nama_unit."\n";

					$data_hasil = TRUE;
				endif;

				# umur hari
				$umurhi = ((abs(strtotime($hari) - strtotime($tgl_sap))) / (60 * 60 * 24));

				$hasil .= "*".$nama ."* (".number_format($populasi,0,',','.')." - ". number_format($matpopulasi,0,',','.')." - ". number_format($pop,0,',','.')." - ".$umurhi.") _".$nm_ts."_\n";

				# ngitung total
				$jml_farm++;
				$total_populasi +=  $pop;
			}
		}

	}

	$hasil_reusume .= "Farm `".$jml_farm."` \n";
	$hasil_reusume .= "Populasi `".number_format($total_populasi,0,',','.')."` \n\n";
	$hasil_reusume .= "~~~~~~~~~~~~~~~\n";

	$data_text = $hasil_reusume.$hasil;

	return $data_text;
}

function getWajibPanenSakit(){
	date_default_timezone_set('Asia/Jakarta');  
	$data_text = "";
	$hasil_reusume = "";
	$hasil = "";  
	$jml_farm = 0;
	$persen_sakit = 0;
	$total_populasi = 0;
	$hari=date("Y-m-d");
	$con = koneksi('berkahgl_portal');	
	$db="berkahgl_portal";

	$qunit = mysqli_query($con,"select * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like 'BMM%' or berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BSB%' or berkahgl_portal.unit.id_unit like 'BUS%'  order by nm_unit asc");

	while($dUnit = mysqli_fetch_array($qunit)){
		$dtUnit = $dUnit['db'];
		$database="berkahgl_".strtolower($dtUnit);
		$con_unit = koneksi($database);
		$exp = explode(" ", $dUnit['nm_unit']);
		$nama_unit = "BGF ".strtoupper($exp[3]); 
		$data_hasil = FALSE;

		$querry=mysqli_query($con,"select berkahgl_portal.checkpoint.nik, $database.rhpp.id_produksi, $database.rhpp.panenbulan, $database.rhpp.id_farm from berkahgl_portal.checkpoint join $database.rhpp on berkahgl_portal.checkpoint.id_produksi = $database.rhpp.id_produksi where kondisi_ayam='Sakit' and berkahgl_portal.checkpoint.id_produksi not in (select id_produksi from berkahgl_portal.stok_habis) and  berkahgl_portal.checkpoint.id_produksi in (select $database.produksi.id_produksi from $database.produksi) and $database.rhpp.panenbulan='0000-00-00' group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc");

		while ($dataku=mysqli_fetch_array($querry)){
			// id proudksi
			$datprod=$dataku['id_produksi'];

			# cek kondisi terakhir input
			//menapilkan data terakhir yg diinput	

			$quer=mysqli_query($con,"select berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.tanggal_input from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$datprod' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			$dataa=mysqli_fetch_array($quer);
			$id_prod=$dataa['id_produksi'];
			$tgl_input=$dataa['tanggal_input'];

			$query=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.bb from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$id_prod' and berkahgl_portal.checkpoint.kondisi_ayam='sakit' and  berkahgl_portal.checkpoint.tanggal_input='$tgl_input' AND berkahgl_portal.checkpoint.bb>='1' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");

			while ($data=mysqli_fetch_array($query)){
				$id_produksi=$data['id_produksi'];

				// cek dulu barangkali udah panen habis
				$qpp=mysqli_query($con,"select berkahgl_portal.pengajuan_panen.ket from berkahgl_portal.pengajuan_panen where id_produksi='$datprod' and status_pengajuan='Confirm' order by tanggal_pp desc");
				$dpp=mysqli_fetch_array($qpp);
			//	$jumlahpanen=$dpp['jumlah1']+$dpp['jumlah2']+$dpp['jumlah3'];
				$ket=$dpp['ket'];

				if ($ket != "Normal") {

					$nik=$dataku['nik'];
					//nama ts
					$kary=mysqli_query($con,"select nama from berkahgl_portal.karyawan where nik='$nik'");
					$dkary=mysqli_fetch_array($kary);
					$nm_ts=$dkary['nama'];		

					//farm nama
					$query2=mysqli_query($con_unit,"select $database.farm.nama, $database.farm.desa from $database.produksi join $database.farm on $database.produksi.id_farm = $database.farm.id_farm where $database.produksi.id_produksi='$datprod'");
					$data2=mysqli_fetch_array($query2);
					$nama=$data2['nama'];
					$desa=$data2['desa'];
						
					//populasi
					$qsap1 = mysqli_query($con_unit,"select $database.jual.tanggal, $database.jual.id_barang, $database.jual.id_produksi, $database.jual.id_barang, $database.jual.qty, $database.barang.nama from $database.jual join $database.barang on $database.barang.id_barang = $database.jual.id_barang where $database.jual.id_produksi='$datprod' and $database.jual.id_barang like '%D-%' order by tanggal");
					$jsap1 = mysqli_num_rows($qsap1);
					if ($jsap1 > 0) {
					    while ($dsap1 = mysqli_fetch_array($qsap1)) {
					        $tgl_sap = $dsap1['tanggal'];
					        $namadoc = $dsap1['nama'];
					        //cari retur DOC
					        $retdoc1 = mysqli_query($con_unit,"select $database.beban.qty from $database.beban where id_produksi='$dsap1[id_produksi]' and id_barang='$dsap1[id_barang]' order by tanggal desc ");
					        $retpopulasi1 = 0;
					        $populasi21 = 0;
					        while ($dretdoc1 = mysqli_fetch_assoc($retdoc1)) {
					            $tretdoc1 = $dretdoc1['qty'];
					            $retpopulasi1 = $retpopulasi1 + $tretdoc1;
					        }
					        //kematian ayam
					        $qmati = mysqli_query($con,"select berkahgl_portal.checkpoint.mortalitas from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$dsap1[id_produksi]' order by tanggal_input desc limit 1 ");
					        $matpopulasi = 0;
					        $populasimati = 0;
					        while ($matdoc1 = mysqli_fetch_assoc($qmati)) {
					            $matidoc1 = $matdoc1['mortalitas'];
					            $matpopulasi = $matpopulasi + $matidoc1;
					        }

					        //cari retur DOC
					        $tdoc1 = $dsap1['qty'];
					        $populasi21 = $populasi21 + ($tdoc1);
					     }
					}
				    $populasi = $populasi21 - $retpopulasi1;
					$pop = $populasi - $matpopulasi;

					if ($data_hasil == FALSE):
						$hasil .= "\n";
						$hasil .= "🔻️ ".$nama_unit."\n";

						$data_hasil = TRUE;
					endif;

					# umur hari
					$umurhi = ((abs(strtotime($hari) - strtotime($tgl_sap))) / (60 * 60 * 24));

					$hasil .= "*".$nama ."* (".number_format($populasi,0,',','.')." - ". number_format($matpopulasi,0,',','.')." - ". number_format($pop,0,',','.')." - ".$umurhi." - ".number_format($data['bb'],2,',','.').") _".$nm_ts."_\n";

					# ngitung total
					$jml_farm++;
					$total_populasi +=  $pop;
				}
			}
		}
	}

	$hasil_reusume .= "Farm `".$jml_farm."` \n";
	$hasil_reusume .= "Populasi `".number_format($total_populasi,0,',','.')."` \n\n";
	$hasil_reusume .= "~~~~~~~~~~~~~~~\n";

	$data_text = $hasil_reusume.$hasil;

	return $data_text;
}

function getWajibPanenPoor(){
	date_default_timezone_set('Asia/Jakarta');  
	$data_text = "";
	$hasil_reusume = "";
	$hasil = "";  
	$jml_farm = 0;
	$persen_sakit = 0;
	$total_populasi = 0;
	$hari=date("Y-m-d");
	$con = koneksi('berkahgl_portal');	
	$db="berkahgl_portal";

	$qunit = mysqli_query($con,"SELECT * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like 'BMM%' or berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BSB%' or berkahgl_portal.unit.id_unit like 'BUS%'  order by nm_unit asc");

	while($dUnit = mysqli_fetch_array($qunit)){
		$dtUnit = $dUnit['db'];
		$database="berkahgl_".strtolower($dtUnit);
		$con_unit = koneksi($database);
		$exp = explode(" ", $dUnit['nm_unit']);
		$nama_unit = "BGF ".strtoupper($exp[3]); 
		$data_hasil = FALSE;

		$querry=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.nik, $database.rhpp.id_produksi, $database.rhpp.panenbulan, $database.rhpp.id_farm from berkahgl_portal.checkpoint join $database.rhpp on berkahgl_portal.checkpoint.id_produksi = $database.rhpp.id_produksi where  berkahgl_portal.checkpoint.id_produksi not in (select id_produksi from berkahgl_portal.stok_habis) and  berkahgl_portal.checkpoint.id_produksi in (select $database.produksi.id_produksi from $database.produksi) and $database.rhpp.panenbulan='0000-00-00' AND $db.checkpoint.adgpoint = 'Poor' and $db.checkpoint.bb >=1 group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc");

		while ($dataku=mysqli_fetch_array($querry)){
			// id proudksi
			$datprod=$dataku['id_produksi'];

			# cek kondisi terakhir input
			//menapilkan data terakhir yg diinput	
			$quer=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.tanggal_input from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$datprod' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			$dataa=mysqli_fetch_array($quer);
			$id_prod=$dataa['id_produksi'];
			$tgl_input=$dataa['tanggal_input'];

			$query=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.bb, berkahgl_portal.checkpoint.agepoint from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$id_prod' and  berkahgl_portal.checkpoint.tanggal_input='$tgl_input' AND $db.checkpoint.adgpoint = 'Poor' and $db.checkpoint.bb >=1 order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			while ($data=mysqli_fetch_array($query)){
				$id_produksi=$data['id_produksi'];
				$agepoint=$data['agepoint'];

				// cek dulu barangkali udah panen habis
				$qpp=mysqli_query($con,"select berkahgl_portal.pengajuan_panen.ket from berkahgl_portal.pengajuan_panen where id_produksi='$datprod' and status_pengajuan='Confirm' order by tanggal_pp desc");
				$dpp=mysqli_fetch_array($qpp);
			//	$jumlahpanen=$dpp['jumlah1']+$dpp['jumlah2']+$dpp['jumlah3'];
				$ket=$dpp['ket'];

				if ($ket != "Normal") {

					$nik=$dataku['nik'];
					//nama ts
					$kary=mysqli_query($con,"select nama from berkahgl_portal.karyawan where nik='$nik'");
					$dkary=mysqli_fetch_array($kary);
					$nm_ts=$dkary['nama'];		

					//farm nama
					$query2=mysqli_query($con_unit,"select $database.farm.nama, $database.farm.desa from $database.produksi join $database.farm on $database.produksi.id_farm = $database.farm.id_farm where $database.produksi.id_produksi='$datprod'");
					$data2=mysqli_fetch_array($query2);
					$nama=$data2['nama'];
					$desa=$data2['desa'];
						
					//populasi
					$qsap1 = mysqli_query($con_unit,"select $database.jual.tanggal, $database.jual.id_barang, $database.jual.id_produksi, $database.jual.id_barang, $database.jual.qty, $database.barang.nama from $database.jual join $database.barang on $database.barang.id_barang = $database.jual.id_barang where $database.jual.id_produksi='$datprod' and $database.jual.id_barang like '%D-%' order by tanggal");
					$jsap1 = mysqli_num_rows($qsap1);
					if ($jsap1 > 0) {
					    while ($dsap1 = mysqli_fetch_array($qsap1)) {
					        $tgl_sap = $dsap1['tanggal'];
					        $namadoc = $dsap1['nama'];
					        //cari retur DOC
					        $retdoc1 = mysqli_query($con_unit,"select $database.beban.qty from $database.beban where id_produksi='$dsap1[id_produksi]' and id_barang='$dsap1[id_barang]' order by tanggal desc ");
					        $retpopulasi1 = 0;
					        $populasi21 = 0;
					        while ($dretdoc1 = mysqli_fetch_assoc($retdoc1)) {
					            $tretdoc1 = $dretdoc1['qty'];
					            $retpopulasi1 = $retpopulasi1 + $tretdoc1;
					        }
					        //kematian ayam
					        $qmati = mysqli_query($con,"select berkahgl_portal.checkpoint.mortalitas from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$dsap1[id_produksi]' order by tanggal_input desc limit 1 ");
					        $matpopulasi = 0;
					        $populasimati = 0;
					        while ($matdoc1 = mysqli_fetch_assoc($qmati)) {
					            $matidoc1 = $matdoc1['mortalitas'];
					            $matpopulasi = $matpopulasi + $matidoc1;
					        }

					        //cari retur DOC
					        $tdoc1 = $dsap1['qty'];
					        $populasi21 = $populasi21 + ($tdoc1);
					     }
					}
				    $populasi = $populasi21 - $retpopulasi1;
					$pop = $populasi - $matpopulasi;

					
					$persen_mortalitas = ($matpopulasi / $populasi) * 100;
					$berat = $data['bb'];
					if (($berat<=1) && ($persen_mortalitas<=3)){
						$status="S";
					} else if ((($berat>1) && ($berat<1.5)) && ($persen_mortalitas<=4)){
						$status="S";
					} else if (($berat>=1.5) && ($persen_mortalitas<=5)){
						$status="S";
					} else {
						$status="T";
					}


					// cek status kondisi harus Awas
					$qkondisi=mysqli_query($con,"SELECT $db.status_kondisi.alert from $db.status_kondisi join $db.checkpoint on $db.status_kondisi.agepoint = $db.checkpoint.agepoint where $db.status_kondisi.agepoint='$agepoint' and $db.status_kondisi.adgpoint='Poor' and $db.status_kondisi.mort='$status' and $db.status_kondisi.alert='Awas' and $db.checkpoint.id_produksi = '$id_produksi' group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc") or die (mysql_error(0));
					$datakondisi=mysqli_fetch_array($qkondisi);
					$jkondisi=mysqli_num_rows($qkondisi);

					if ($jkondisi > 0) {
						if ($data_hasil == FALSE):
							$hasil .= "\n";
							$hasil .= "🔻️ ".$nama_unit."\n";

							$data_hasil = TRUE;
						endif;

						# umur hari
						$umurhi = ((abs(strtotime($hari) - strtotime($tgl_sap))) / (60 * 60 * 24));

						$hasil .= "*".$nama ."* (".number_format($populasi,0,',','.')." - ". number_format($matpopulasi,0,',','.')." - ". number_format($pop,0,',','.')." - ".$umurhi." - ".number_format($data['bb'],2,',','.').") _".$nm_ts."_\n";

						# ngitung total
						$jml_farm++;
						$total_populasi +=  $pop;
					}
				}
			}
		}
	}

	$hasil_reusume .= "Farm `".$jml_farm."` \n";
	$hasil_reusume .= "Populasi `".number_format($total_populasi,0,',','.')."` \n\n";
	$hasil_reusume .= "~~~~~~~~~~~~~~~\n";

	$data_text = $hasil_reusume.$hasil;

	return $data_text;
}
	
function getStatusAwas($unit){
	date_default_timezone_set('Asia/Jakarta');  
	$data_text = "";
	$hasil_reusume = "";
	$hasil = "";  
	$jml_farm = 0;
	$persen_sakit = 0;
	$total_populasi = 0;
	$hari=date("Y-m-d");
	$con = koneksi('berkahgl_portal');	
	$db="berkahgl_portal";

	//$qunit = mysqli_query($con,"SELECT * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like 'BMM%' or berkahgl_portal.unit.id_unit like 'BS1%' or  berkahgl_portal.unit.id_unit like 'BS2%' or berkahgl_portal.unit.id_unit like 'BSB%' or berkahgl_portal.unit.id_unit like 'BUS%'  order by nm_unit asc");
	$qunit = mysqli_query($con,"SELECT * from berkahgl_portal.unit where  berkahgl_portal.unit.id_unit like '$unit%' order by nm_unit asc");

	while($dUnit = mysqli_fetch_array($qunit)){
		$dtUnit = $dUnit['db'];
		$database="berkahgl_".strtolower($dtUnit);
		$con_unit = koneksi($database);
		$exp = explode(" ", $dUnit['nm_unit']);
		$nama_unit = "BGF ".strtoupper($exp[3]); 
		$data_hasil = FALSE;

		$querry=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.nik, $database.rhpp.id_produksi, $database.rhpp.panenbulan, $database.rhpp.id_farm from berkahgl_portal.checkpoint join $database.rhpp on berkahgl_portal.checkpoint.id_produksi = $database.rhpp.id_produksi where  berkahgl_portal.checkpoint.id_produksi not in (select id_produksi from berkahgl_portal.stok_habis) and  berkahgl_portal.checkpoint.id_produksi in (select $database.produksi.id_produksi from $database.produksi) and $database.rhpp.panenbulan='0000-00-00' group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc");

		while ($dataku=mysqli_fetch_array($querry)){
			// id proudksi
			$datprod=$dataku['id_produksi'];

			# cek kondisi terakhir input
			//menapilkan data terakhir yg diinput	
			$quer=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.tanggal_input from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$datprod' order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			$dataa=mysqli_fetch_array($quer);
			$id_prod=$dataa['id_produksi'];
			$tgl_input=$dataa['tanggal_input'];

			$query=mysqli_query($con,"SELECT berkahgl_portal.checkpoint.id_produksi, berkahgl_portal.checkpoint.bb, berkahgl_portal.checkpoint.agepoint from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$id_prod' and  berkahgl_portal.checkpoint.tanggal_input='$tgl_input'order by berkahgl_portal.checkpoint.tanggal_input desc limit 1");
			while ($data=mysqli_fetch_array($query)){
				$id_produksi=$data['id_produksi'];
				$agepoint=$data['agepoint'];

				// cek dulu barangkali udah panen habis
				$qpp=mysqli_query($con,"select berkahgl_portal.pengajuan_panen.ket from berkahgl_portal.pengajuan_panen where id_produksi='$datprod' and status_pengajuan='Confirm' order by tanggal_pp desc");
				$dpp=mysqli_fetch_array($qpp);
			//	$jumlahpanen=$dpp['jumlah1']+$dpp['jumlah2']+$dpp['jumlah3'];
				$ket=$dpp['ket'];

				if ($ket != "Normal") {

					$nik=$dataku['nik'];
					//nama ts
					$kary=mysqli_query($con,"select nama from berkahgl_portal.karyawan where nik='$nik'");
					$dkary=mysqli_fetch_array($kary);
					$nm_ts=$dkary['nama'];		

					//farm nama
					$query2=mysqli_query($con_unit,"select $database.farm.nama, $database.farm.desa from $database.produksi join $database.farm on $database.produksi.id_farm = $database.farm.id_farm where $database.produksi.id_produksi='$datprod'");
					$data2=mysqli_fetch_array($query2);
					$nama=$data2['nama'];
					$desa=$data2['desa'];
						
					//populasi
					$qsap1 = mysqli_query($con_unit,"select $database.jual.tanggal, $database.jual.id_barang, $database.jual.id_produksi, $database.jual.id_barang, $database.jual.qty, $database.barang.nama from $database.jual join $database.barang on $database.barang.id_barang = $database.jual.id_barang where $database.jual.id_produksi='$datprod' and $database.jual.id_barang like '%D-%' order by tanggal");
					$jsap1 = mysqli_num_rows($qsap1);
					if ($jsap1 > 0) {
					    while ($dsap1 = mysqli_fetch_array($qsap1)) {
					        $tgl_sap = $dsap1['tanggal'];
					        $namadoc = $dsap1['nama'];
					        //cari retur DOC
					        $retdoc1 = mysqli_query($con_unit,"select $database.beban.qty from $database.beban where id_produksi='$dsap1[id_produksi]' and id_barang='$dsap1[id_barang]' order by tanggal desc ");
					        $retpopulasi1 = 0;
					        $populasi21 = 0;
					        while ($dretdoc1 = mysqli_fetch_assoc($retdoc1)) {
					            $tretdoc1 = $dretdoc1['qty'];
					            $retpopulasi1 = $retpopulasi1 + $tretdoc1;
					        }
					        //kematian ayam
					        $qmati = mysqli_query($con,"select berkahgl_portal.checkpoint.mortalitas from berkahgl_portal.checkpoint where berkahgl_portal.checkpoint.id_produksi='$dsap1[id_produksi]' order by tanggal_input desc limit 1 ");
					        $matpopulasi = 0;
					        $populasimati = 0;
					        while ($matdoc1 = mysqli_fetch_assoc($qmati)) {
					            $matidoc1 = $matdoc1['mortalitas'];
					            $matpopulasi = $matpopulasi + $matidoc1;
					        }

					        //cari retur DOC
					        $tdoc1 = $dsap1['qty'];
					        $populasi21 = $populasi21 + ($tdoc1);
					     }
					}
				    $populasi = $populasi21 - $retpopulasi1;
					$pop = $populasi - $matpopulasi;

					
					$persen_mortalitas = ($matpopulasi / $populasi) * 100;
					$berat = $data['bb'];
					if (($berat<=1) && ($persen_mortalitas<=3)){
						$status="S";
					} else if ((($berat>1) && ($berat<1.5)) && ($persen_mortalitas<=4)){
						$status="S";
					} else if (($berat>=1.5) && ($persen_mortalitas<=5)){
						$status="S";
					} else {
						$status="T";
					}


					// cek status kondisi harus Awas
					$qkondisi=mysqli_query($con,"SELECT $db.status_kondisi.alert from $db.status_kondisi join $db.checkpoint on $db.status_kondisi.agepoint = $db.checkpoint.agepoint where $db.status_kondisi.agepoint='$agepoint' and $db.status_kondisi.mort='$status' and $db.status_kondisi.alert='Awas' and $db.checkpoint.id_produksi = '$id_produksi' group by berkahgl_portal.checkpoint.id_produksi order by berkahgl_portal.checkpoint.tanggal_input desc") or die (mysql_error(0));
					$datakondisi=mysqli_fetch_array($qkondisi);
					$jkondisi=mysqli_num_rows($qkondisi);

					if ($jkondisi > 0) {
						if ($data_hasil == FALSE):
							$hasil_reusume .= "🐥 ".$nama_unit."\n\n";

							$data_hasil = TRUE;
						endif;

						# umur hari
						$umurhi = ((abs(strtotime($hari) - strtotime($tgl_sap))) / (60 * 60 * 24));

						$hasil .= "*".$nama ."* (".number_format($populasi,0,',','.')." - ". number_format($matpopulasi,0,',','.')." - ". number_format($pop,0,',','.')." - ".$umurhi." - ".number_format($data['bb'],2,',','.').") _".$nm_ts."_\n";

						# ngitung total
						$jml_farm++;
						$total_populasi +=  $pop;
					}
				}
			}
		}
	}

	$hasil_reusume .= "Farm `".$jml_farm."` \n";
	$hasil_reusume .= "Populasi `".number_format($total_populasi,0,',','.')."` \n\n";
	$hasil_reusume .= "~~~~~~~~~~~~~~~\n";

	$data_text = $hasil_reusume.$hasil;

	return $data_text;
}

function getSudahTadarus($tanggal){
	$db = conn('berkahgl_portal');
	$text = "";

	$sql = "SELECT COUNT(IF(aksi='Baca', nik_tugas, NULL)) as total_baca FROM setoran_ayat WHERE tanggal='$tanggal'";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $data = $stmt->fetch();
    $total  = $data['total_baca'];
    $text .= "👳‍ Baca `".$total."`\n\n";


    $sql = "SELECT id_unit, nm_unit from berkahgl_portal.unit where id_unit != 'BFD' and id_unit != 'BCR' and id_unit != 'BBL' and id_unit != 'BTS' and id_unit not like 'BAS' order by nm_unit ASC";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    while ( $data = $stmt->fetch() ) {	
    	$data_hasil = FALSE;

	    $sql3 = "SELECT karyawan.nama FROM anggota_tadarus JOIN karyawan ON anggota_tadarus.id_anggota=karyawan.nik JOIN setoran_ayat ON anggota_tadarus.no_anggota=setoran_ayat.nik_tugas WHERE tanggal='$tanggal' AND aksi='Baca'  AND setoran_ayat.id_unit='$data[id_unit]' ORDER BY nama ASC ";
	    $stmt3 = $db->prepare($sql3);
	    $stmt3->execute();

	    while ( $data3 = $stmt3->fetch() ) {	
			if ($data_hasil == FALSE):
				$text .= "\n";
				$text .= "✅ ".strtoupper($data['nm_unit'])."\n";

				$data_hasil = TRUE;
			endif;

	    	$text .= "*".$data3['nama']."* \n";
	    }
    }
    
    return $text; 
}

function getBelumTadarus($tanggal){
	$db = conn('berkahgl_portal');
	$text = "";

	# hitung jumlah anggota klaim
	$sql = "SELECT COUNT(IF(aksi='Klaim', nik_tugas, NULL)) as total_baca FROM setoran_ayat WHERE tanggal='$tanggal'";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $data = $stmt->fetch();
    $total  = $data['total_baca'];

    # hitung jumlah anggota no klaim
    $sql = "SELECT COUNT(no_anggota) AS total_no_klaim FROM anggota_tadarus WHERE anggota_tadarus.no_anggota not in (select nik_tugas FROM setoran_ayat WHERE tanggal='$tanggal') AND tanggal_off='0000-00-00' ";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $data = $stmt->fetch();
    $total_no_klaim  = $data['total_no_klaim'];

    $text .= "👳‍ Klaim `".$total."`\n";
    $text .= "✖️ No Klaim `".$total_no_klaim."`\n\n";


    $sql = "SELECT id_unit, nm_unit from berkahgl_portal.unit where id_unit != 'BFD' and id_unit != 'BCR' and id_unit != 'BBL' and id_unit != 'BTS' and id_unit not like 'BAS' order by nm_unit ASC";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    while ( $data = $stmt->fetch() ) {	
    	$data_hasil = FALSE;

	    $sql3 = "SELECT karyawan.nama FROM anggota_tadarus JOIN karyawan ON anggota_tadarus.id_anggota=karyawan.nik JOIN setoran_ayat ON anggota_tadarus.no_anggota=setoran_ayat.nik_tugas WHERE tanggal='$tanggal' AND aksi='Klaim'  AND setoran_ayat.id_unit='$data[id_unit]' ORDER BY nama ASC ";
	    $stmt3 = $db->prepare($sql3);
	    $stmt3->execute();

	    while ( $data3 = $stmt3->fetch() ) {	
			if ($data_hasil == FALSE):
				$text .= "\n";
				$text .= "❎ ".strtoupper($data['nm_unit'])."\n";

				$data_hasil = TRUE;
			endif;

	    	$text .= "*".$data3['nama']."* \n";
	    }

	    $sql2 = "SELECT karyawan.nama FROM anggota_tadarus JOIN karyawan ON anggota_tadarus.id_anggota=karyawan.nik WHERE anggota_tadarus.no_anggota not in (select nik_tugas FROM setoran_ayat WHERE tanggal='$tanggal' AND setoran_ayat.id_unit='$data[id_unit]') AND tanggal_off='0000-00-00' AND karyawan.id_unit='$data[id_unit]' ORDER BY nama ASC ";
	    $stmt2 = $db->prepare($sql2);
	    $stmt2->execute();

	   	while ($data2 = $stmt2->fetch()) {	
	    	$text .= "*".$data2['nama']."* \*\n";
	    }
    }
    
    return $text; 
}

function getAbsensiAm($tanggal){

	$host = "103.27.206.20";
	$database = "almultaz_nizam";
	$user = "almultaz";
	$pass = "nizam@99";
	$db = new PDO("mysql:host=".$host.";dbname=".$database,$user,$pass);

	$sql = "SELECT COUNT(*) as jml_karyawan FROM pegawai WHERE id_sekolah='6'";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $jml_pegawai = $stmt->fetchColumn();

    $sql = "SELECT COUNT(*) as jml_hadir FROM absensi JOIN pegawai ON absensi.user_id=pegawai.nrp WHERE date='$tanggal' AND pegawai.id_sekolah='6' ORDER BY absensi.time_in ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $jml_hadir = $stmt->fetchColumn();

    $sql = "SELECT pegawai.nama, absensi.time_in, absensi.time_out FROM absensi JOIN pegawai ON absensi.user_id=pegawai.nrp WHERE date='$tanggal' AND pegawai.id_sekolah='6' ORDER BY absensi.time_in ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    $tidak_hadir = $jml_pegawai-$jml_hadir;

    echo "✅ Hadir `".$jml_hadir."` \n";
    echo "👤 Total `".$jml_pegawai." (-".$tidak_hadir.")` \n";
    echo "=============================== \n";


    while ( $data = $stmt->fetch() ) {	
    	echo "`".substr($data['time_in'], 0, 5).' '.substr($data['time_out'], 0, 5)."`  ".strtoupper($data['nama'])."\n";
    }
}


function getAbsensiMusyrifah($tanggal){

	// $host = "103.27.206.20";
	// $database = "almultaz_nizam";
	// $user = "almultaz";
	// $pass = "nizam@99";
	// $db = new PDO("mysql:host=".$host.";dbname=".$database,$user,$pass);
	$db = conn('berkahgl_demo');

	$sql = "SELECT COUNT(*) as jml_karyawan FROM pegawai WHERE id_jabatan='21'";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $jml_pegawai = $stmt->fetchColumn();

    $sql = "SELECT COUNT(*) as jml_hadir FROM absensi_musyrifah JOIN pegawai ON absensi_musyrifah.user_id=pegawai.nrp WHERE date='$tanggal' AND absensi_musyrifah.time_in2!='00:00:00' ORDER BY absensi_musyrifah.time_in2 ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $jml_hadir = $stmt->fetchColumn();

    $sql = "SELECT pegawai.nama, absensi_musyrifah.time_in2 FROM absensi_musyrifah JOIN pegawai ON absensi_musyrifah.user_id=pegawai.nrp WHERE date='$tanggal' AND absensi_musyrifah.time_in2!='00:00:00' ORDER BY absensi_musyrifah.time_in2 ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();

    $tidak_hadir = $jml_pegawai-$jml_hadir;

    $text = "✅ Hadir `".$jml_hadir."` \n";
    $text .= "👤 Total `".$jml_pegawai." (-".$tidak_hadir.")` \n";
    $text .= "=============================== \n";


    while ( $data = $stmt->fetch() ) {	
    	$text .= "`".substr($data['time_in2'], 0, 5)."`  ".strtoupper($data['nama'])."\n";
    }

    $text .= "\n=============================== \n";
    $sql = "SELECT pegawai.nama FROM pegawai WHERE id_jabatan='21' AND pegawai.nrp NOT IN (SELECT user_id FROM absensi_musyrifah WHERE date='$tanggal' AND absensi_musyrifah.time_in2!='00:00:00')  ORDER BY pegawai.nama ASC ";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    while ( $data = $stmt->fetch() ) {	
    	$text .= strtoupper($data['nama'])."\n";
    }

    return $text;
}


function simpanIdTelegramHK($nik, $id_telegram){
	
	$dbh = conn('berkahgl_husnul_khotimah');
    $sql = "INSERT INTO abs_id_telegram (nik, id_telegram) VALUE ($nik, $id_telegram) ";
    $stmt = $dbh->prepare($sql);
    $result = $stmt->execute();
    return $result;
    
 }


 # ======================
 # FUNGSI API ANDROI BERKAH GO
 # ======================

/* API key encryption */
function apiToken($session_uid)
{
	$key=md5(SITE_KEY.$session_uid);
	return hash('sha256', $key);
}

function getPengajuanTransferDO($dari, $ke, $jenis){
	$con = koneksi('berkahgl_trd');

	$data = array();

	if($jenis=="all"){
	  $q = mysqli_query($con, "SELECT * FROM berkahgl_trd.v_transdata v,berkahgl_trd.b_stts_transfer t where v.id_rencana=t.id_stts_transfer and tgl_pengajuan <= '$ke' and tgl_pengajuan >= '$dari' order by tanggal DESC");
	}else if($jenis=="paid"){
	  $q = mysqli_query($con, "SELECT * FROM berkahgl_trd.v_transdata v,berkahgl_trd.b_stts_transfer t where v.id_rencana=t.id_stts_transfer and tgl_pengajuan <= '$ke' and tgl_pengajuan >= '$dari' and app_lvl2='1' and app_lvl1='1' and app_drt='1' order by tanggal DESC");
	}else if($jenis=="unpaid"){
	  $q = mysqli_query($con, "SELECT * FROM berkahgl_trd.v_transdata v,berkahgl_trd.b_stts_transfer t where v.id_rencana=t.id_stts_transfer and tgl_pengajuan <= '$ke' and tgl_pengajuan >= '$dari' and app_lvl1='1' and app_drt='1' and app_lvl2='0' order by tanggal DESC");
	}else{
	  $q = mysqli_query($con, "SELECT * FROM berkahgl_trd.v_transdata v,berkahgl_trd.b_stts_transfer t where v.id_rencana=t.id_stts_transfer and tgl_pengajuan <= '$ke' and tgl_pengajuan >= '$dari' and app_drt='0' and app_lvl1='1' order by tanggal DESC");
	}

	    $ix = 0;
	    while ($d = mysqli_fetch_array($q)) {
	        $kon=  mysqli_query($con, "SELECT * FROM berkahgl_trd.b_transaksi where id_rencana='D-$d[id_rencana]'");
	        $dkon=  mysqli_fetch_array($kon);
	        $nkon=  mysqli_num_rows($kon);
	        if($nkon != 0){
	            $konsumen=$dkon['id_konsumen'];
	            $via=  "Trader ".ceknamesup($dkon['idmarketing']);
	            if($konsumen =="DIRECT"){
	                $dkonsumen="Konf. Trader ".ceknamesup($dkon['idmarketing']);
	            }else{
	                $dkonsumen=$dkon['id_konsumen'];
	            }
	        }else{
	            $dkonsumen=$d['id_konsumen'];
	            $via="Regular";
	        }

	//        cek real atau rencana
	        if($d['real_ton'] == 0){
	            $ton=$d['rencana_ton'];
	            $hb=$d['rencana_harga_beli'];
	            $hj=$d['rencana_harga_jual'];
	            $ekor=$d['rencana_ekor'];
	        }else{
	            $ton=$d['real_ton'];
	            $hb=$d['real_harga_beli'];
	            $hj=$d['real_harga_jual'];
	            $ekor=$d['real_ekor'];
	        }
	        $totton=$totton+$ton;
	        $totekor=$totekor+$ekor;
	        $totHb=$totHb+$hb;

	        if($d['status']!="DOC"){
				$jaditon=number_format($ton,2);
		        $totrenbeli = ($ton * $hb);
		        $totrenjual = ($ton * $hj);
			}else{
				$jaditon="*DOC";
				$totrenbeli = ($ekor * $hb);
				$totrenjual = ($ekor * $hj);
			}

	    	$totBeli=$totBeli+$totrenbeli;

			$data[$ix]['kd_transfer'] = $d['kd_transfer'];
        	$data[$ix]['id_transaksi'] = $d['id_transaksi'];
        	$data[$ix]['tanggal'] = substr($d['tanggal'], 0, 10);
        	$data[$ix]['id_supplier'] = $d['id_supplier'];
        	$data[$ix]['nama_supplier'] = strtoupper(ceknamesup($d['id_supplier']));
        	$data[$ix]['via'] = $via;
        	$data[$ix]['id_konsumen'] = $dkonsumen;
        	$data[$ix]['nama_konsumen'] = strtoupper(ceknamesup($dkonsumen));
        	$data[$ix]['tonase'] = $jaditon;
        	$data[$ix]['ekor'] = $ekor;
        	$data[$ix]['beli'] = $hb;
        	$data[$ix]['total'] = $totrenbeli;
        	$data[$ix]['pengajuan'] = $d['jml'];
        	$ren_bruto = $totrenjual - $totrenbeli;
        	$data[$ix]['bruto'] = $ren_bruto;
        	$data[$ix]['trader'] = ceknamesup($d[idmarketing]);

        	if($d['app_drt'] == "0"){
        		$transfer = 'Unpaid';
        		$color = 'dark';
        	}else if($d['app_drt'] == "1") {
				if($d['ket'] == ""){
            		$transfer = 'Diajukan';
            		$color = 'warning';
            	}else{
              		$transfer = 'Paid';
              		$color = 'success';
            	}
            } 

            $data[$ix]['isChecked'] = false;
        	$data[$ix]['transfer'] = $transfer;
        	$data[$ix]['colorStatus'] = $color;

        	$ix++;
	    }

	return $data;

}

function ceknamesup($id) {
	$con = koneksi('berkahgl_trd');
    $qsup = mysqli_query($con, "SELECT * FROM berkahgl_trd.b_masterdata where id_konsumen='$id'");
    $dsup = mysqli_fetch_array($qsup);
    $nsup = mysqli_num_rows($qsup);
    if ($nsup != 0) {
        $namasup = explode("-", $dsup['nama']);
        $namasupx = $namasup[1];
    } else {
        $qsup1 = mysqli_query($con, "select * from berkahgl_trd.b_trader where nip='$id'");
        $dsup1 = mysqli_fetch_array($qsup1);
        $namasup1 = explode(" ", $dsup1['nama_trader']);
        $namasupx = $namasup1[0] . " " . $namasup1[1];
    }
    return $namasupx;
}	

function approveTransferDO($kd_transfers, $app_drt){
	$con = koneksi('berkahgl_trd');

	foreach ($kd_transfers as $key => $kd_transfer) {
		mysqli_query($con, "UPDATE berkahgl_trd.b_stts_transfer set app_drt='$app_drt' where kd_transfer='$kd_transfer'");
	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';		
	}

	return $data;
}

function approveFarmBaru($ids, $app_drt){

	$con = koneksi('berkahgl_portal');
	array_unique($ids);

	foreach ($ids as $key => $id) {

		if ($app_drt == 'Approve') {

			$query = mysqli_query($con, "SELECT * FROM berkahgl_portal.farm where app_drt='Proses' and app_pusat='Approve' and app_manager='Approve' and app_ts='Approve' AND id='$id' ");
			while ($s = mysqli_fetch_array($query,MYSQLI_ASSOC)){ 
				$id=$s[id];
				$unt = $s['unit'];
				$database="berkahgl_".strtolower($unt);
				$j++;
				
				$sp="select * from $database.perusahaan";
				$rp=mysqli_query($con, $sp) or die (mysqli_error(0));
				$dp = mysqli_fetch_assoc($rp);
				
				$s1 = mysqli_query($con, "select id_farm from $database.farm order by id_farm DESC limit 0,1") or die (mysqli_error());
				$j1 = mysqli_fetch_assoc($s1);
				$no_terakhir = substr($j1['id_farm'],12,3);
				$no_terakhir1=$no_terakhir+1;
				//menambahkan angka nol
				if($no_terakhir1 < 10){
					$notrans_t1 = '00'.$no_terakhir1;
				}else if($no_terakhir1 < 100) {
					$notrans_t1 = '0'.$no_terakhir1;
				}else{
					$notrans_t1 = $no_terakhir1;
				}
				
				
				$id_kandang="02/$dp[id_perusahaan]/FARM/".$notrans_t1;   
				$nama = $s['nama'];
				$tempat_lahir = $s['tempat_lahir']; 
				$tgl_lahir = $s['tgl_lahir'];
				$jenis_kelamin = $s['jenis_kelamin'];
				$desa_peternak = $s['desa_peternak'];
				$kec_peternak = $s['kec_peternak'];
				$kota_peternak = $s['kota_peternak'];
				$telp = $s['telp'];
				$agama = $s['agama'];
				$desa = $s['desa'];
				$kec = $s['kec'];
				$kota = $s['kota'];
				$populasi = $s['populasi'];
				$lokasi = $s['lokasi'];
				$arah_kandang = $s['arah_kandang'];
				$letak_kandang = $s['letak_kandang'];
				$lk = $s['lk'];
				$kualitas_jalan = $s['kualitas_jalan'];
				$besar_jalan = $s['besar_jalan'];
				$sistem_kandang = $s['sistem_kandang'];
				$lantai_kandang = $s['lantai_kandang'];
				$jumlah_lantai = $s['jumlah_lantai'];
				$atap_kandang = $s['atap_kandang'];
				$alas_kandang = $s['alas_kandang'];
				$jk = $s['jk'];
				$pl1 = $s['pl1'];
				$l1 = $s['l1'];
				$pl2 = $s['pl2'];
				$l2 = $s['l2'];
				$pl3 = $s['pl3'];
				$l3 = $s['l3'];
				$pl4 = $s['pl4'];
				$l4 = $s['l4'];
				$pl5 = $s['pl5'];
				$l5 = $s['l5'];
				$pl6 = $s['pl6'];
				$l6 = $s['l6'];
				$pl7 = $s['pl7'];
				$l7 = $s['l7'];
				$pl8 = $s['pl8'];
				$l8 = $s['l8'];
				$pl9 = $s['pl9'];
				$l9 = $s['l9'];
				$pl10 = $s['pl10'];
				$l10 = $s['l10'];
				$suhu_kandang_siang = $s['suhu_kandang_siang'];
				$suhu_kandang_malam = $s['suhu_kandang_malam'];
				$brooder = $s['brooder'];
				$pemanas = $s['pemanas'];
				$tp = $s['tp'];
				$jum_tp = $s['jum_tp'];
				$tm = $s['tm'];
				$jum_tm = $s['jum_tm'];
				$dinding = $s['dinding'];
				$tembok = $s['tembok'];
				$tiang = $s['tiang'];
				$anak_kandang = $s['anak_kandang'];
				$penyakit = $s['penyakit'];
				$sk = $s['sk'];
				$ts = $s['TS'];
				$periode = $s['periode'];
				$saldo_awal = $s['saldo_awal'];
				$saldo_awal_tab = $s['saldo_awal_tab'];
				$gambar = $s['gambarkandang'];
				$peta = $s['petakandang'];
				$saldo_awal_prod = $s['saldo_awal_prod'];
				$saldo_awal_tab_ijr = $s['saldo_awal_tab_ijr'];
				//$unit = $s['unit'];
				$tanggal = $s['tanggal'];
			
			
			$query = mysqli_query($con, "INSERT INTO $database.farm VALUES ('$id_kandang','$nama','$tempat_lahir','$tgl_lahir','$jenis_kelamin','$desa_peternak','$kec_peternak','$kota_peternak','$telp','$agama','$desa','$kec','$kota','$populasi','$lokasi','$arah_kandang','$letak_kandang','$lk','$kualitas_jalan','$besar_jalan','$sistem_kandang','$lantai_kandang','$jumlah_lantai','$atap_kandang','$alas_kandang','$jk','$pl1','$l1','$pl2','$l2','$pl3','$l3','$pl4','$l4','$pl5','$l5','$pl6','$l6','$pl7','$l7','$pl8','$l8','$pl9','$l9','$pl10','$l10','$suhu_kandang_siang','$suhu_kandang_malam','$brooder','$pemanas','$tp','$jum_tp','$tm','$jum_tm','$dinding','$tembok','$tiang','$anak_kandang','$penyakit','$sk','$ts','$periode','$saldo_awal','$saldo_awal_tab','$gambar','$peta','$saldo_awal_prod','$saldo_awal_tab_ijr')") or die (mysqli_error());
				
				
			$updatesttus = mysqli_query($con, "update berkahgl_portal.farm set app_drt='Approve', farm = '$id_kandang' where id='$id'");
			
			}

		}else{

			$updatesttus = mysqli_query($con, "update berkahgl_portal.farm set app_drt='Batal' where id='$id'");
		}

	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';		
	}

	return $data;
	
}

function getPengajuanFarmOff($idupdate){
	$con = koneksi('berkahgl_portal');
	$db="berkahgl_portal";

	$data = array();
	$ix = 0;

	$sql=mysqli_query($con, "SELECT * from berkahgl_portal.update_ts where app_man = 'Approve' and ket = 'Approve' and app_drt='Proses' and idupdate='$idupdate' ");

    while ($dFarm = mysqli_fetch_array($sql)) {
		$nik=$dFarm['nik'];
		$kriteria=$dFarm['kriteria'];
        $query = mysqli_query($con, "SELECT * from $db.karyawan where nik='$nik' ") or die(mysqli_error(0));
        $dquery = mysqli_fetch_array($query);
        $namats =$dquery['nama'];
		$unitt=strtoupper($dFarm['unit']); 
		$database="berkahgl_".strtolower($dFarm['unit']);
		
		$nm = $dFarm['nama'];
		$almt = $dFarm['desa'].'-'.$dFarm['kec'].'-'.$dFarm['kota'];
		$p = number_format($dFarm['populasi']);
		$pop = $dFarm['populasi'];
		
		$iprata2=number_format(cekIPrata($database, $dFarm['id_farm']),2);
		//$grade = cekGrade(cekIPrata($database, $dFarm['id_farm']));	
		$grade = cekGrade($iprata2);	

		if ($dFarm['status']==1){
			$status="ON";
		} else if ($dFarm['status']==2){
			$status="REST";
		} else {
			$status="OFF";
		}
		
		
		//asal data
		$qfarmasal=mysqli_query($con, "select * from $database.farm where id_farm='$dFarm[id_farm]'");
		$dfarmasal=mysqli_fetch_array($qfarmasal);
		
		$qts=mysqli_query($con, "select * from berkahgl_portal.update_ts where id_farm='$dFarm[id_farm]' order by tanggal desc limit 1,2");
		$dts=mysqli_fetch_array($qts);
		$nikts=$dts['nik'];
		
		$qkary=mysqli_query($con, "select * from berkahgl_portal.karyawan where nik='$nikts'");
		$dkary=mysqli_fetch_array($qkary);
		
		$qfarmstatus=mysqli_query($con, "select * from $database.farm_update where id_farm='$dFarm[id_farm]'");
		$dfarmstatus=mysqli_fetch_array($qfarmstatus);
		$status_farmasal=$dfarmstatus['status'];
		if ($status_farmasal==1){
			$stts="ON";
		} else if ($status_farmasal==2){
			$stts="REST";
		} else if ($status_farmasal==3){
			$stts="OFF";
		}
		
		 $q= mysqli_query($con, "SELECT $database.produksi.id_farm, $database.produksi.id_kontrak,  $database.produksi.ts  FROM  $database.produksi where id_farm = '$dFarm[id_farm]' order by id_produksi desc limit 1") or die(mysqli_error(0));
        $dquery = mysqli_fetch_array($q);
		//$ts = $dquery['ts'];
		$id_kntrk = $dquery['id_kontrak'];
		
		
		$qp= mysqli_query($con, "SELECT $database.produksi.id_farm, $database.produksi.id_kontrak,  $database.produksi.ts  FROM  $database.produksi where id_farm = '$dFarm[id_farm]' order by id_produksi desc") or die(mysqli_error(0));
		$jp=mysqli_num_rows($qp);
        

        $data[$ix]['id_farm'] = $dFarm['id_farm'];
        $data[$ix]['nama'] = $dFarm['nama'];
        $data[$ix]['alamat'] = 'Desa ' . $dFarm['desa'] . ' Kec. ' . $dFarm['kec'] . ' Kab. ' .  $dFarm['kota'];
        $data[$ix]['iprata2'] = $iprata2;
        $data[$ix]['grade'] = $grade;
        $data[$ix]['namats'] = $namats;
        $data[$ix]['kriteria'] = $kriteria;
        $data[$ix]['populasi'] = $dFarm['populasi'];
        $data[$ix]['status'] = $status;
        $data[$ix]['id_kontrak'] = $dFarm['id_kontrak'];
        $data[$ix]['periode'] = $jp;
        $data[$ix]['tanggal'] = $dFarm['tanggal'];

		$qip=mysqli_query($con, "select * from $database.rhpp where id_farm='$dFarm[id_farm]' order by id_produksi desc limit 2");
		$qep=mysqli_query($con, "Select * from $database.rhpp where id_farm='$dFarm[id_farm]' order by id_produksi desc limit 2 ") or die (mysqli_error());
		$jip=mysqli_num_rows($qip);
		if ($jip==0){

			$data[$ix]['ipterakhir1'] = '-';
			$data[$ix]['ipterakhir2'] = '-';
			$data[$ix]['epterakhir1'] = '-';
			$data[$ix]['epterakhir2'] = '-';

		} else if ($jip==1){
			while ($dip=mysqli_fetch_array($qip)){
				$ip=$dip['ip'];  
				$data[$ix]['ipterakhir1'] = number_format($ip,2);
			}
			$data[$ix]['ipterakhir2'] = '-';

			while ($dipp=mysqli_fetch_array($qep)){ 
				//ep
				$qpja = mysqli_query($con, "select * from $database.pja where id_produksi='$dipp[id_produksi]' order by tanggal") or die(mysqli_error());
				$Tkg=0;
				while ($dpja = mysqli_fetch_assoc($qpja)){
				    $Tkg = $Tkg + $dpja['tonase_real'];
				}
			    $query_rhpp = mysqli_query($con, "select * from $database.rhpp where id_produksi='$dipp[id_produksi]'");
			   	$drhpp = mysqli_fetch_assoc($query_rhpp);
			    $pakanterpakai = $drhpp['pakan_total'];
			    $efisiensi_pakan = substr(($Tkg / $pakanterpakai) * 100, 0, 5);
			    $data[$ix]['epterakhir1'] = number_format($efisiensi_pakan,2);
				$data[$ix]['epterakhir2'] = '-';
			}
		} else {
			while ($dip=mysqli_fetch_array($qip)){
				$ip=$dip['ip']; 

				if (isset($data[$ix]['ipterakhir1'])) {
					$data[$ix]['ipterakhir2'] = number_format($ip,2);
				}else{
					$data[$ix]['ipterakhir1'] = number_format($ip,2);
				}

			}

			while ($dep=mysqli_fetch_array($qep)) { 
				//ep
				$qpja = mysqli_query($con, "select * from $database.pja where id_produksi='$dep[id_produksi]' order by tanggal") or die(mysqli_error());
				$Tkg=0;
				while ($dpja = mysqli_fetch_assoc($qpja)){
				    $Tkg = $Tkg + $dpja['tonase_real'];
				}

			    $query_rhpp = mysqli_query($con, "select * from $database.rhpp where id_produksi='$dep[id_produksi]'");
			   	$drhpp = mysqli_fetch_assoc($query_rhpp);
			    $pakanterpakai = $drhpp['pakan_total'];
			    $efisiensi_pakan = substr(($Tkg / $pakanterpakai) * 100, 0, 5);

				if (isset($data[$ix]['epterakhir1'])) {
					$data[$ix]['epterakhir2'] = number_format($efisiensi_pakan,2);
				}else{
					$data[$ix]['epterakhir1'] = number_format($efisiensi_pakan,2);
				}
			}

		}

		$data[$ix]['alasan'] = $dFarm['alasan'];
		$data[$ix]['unit'] = $unitt;

		$ix++;
	}

	return $data;
}

function cekIPrata($db, $id_farm)
{
	$con = koneksi('berkahgl_portal');

    $qip=  mysqli_query($con, "SELECT * FROM $db.rhpp where id_farm='$id_farm' and panenbulan != '0000-00-00'");
    while($dip=  mysqli_fetch_array($qip)){
        $populasi = getPopulasi($db, $dip['id_produksi']);
        $cekip = $populasi * $dip['ip'];
        $kaliip = $kaliip + $cekip;
        $totpopulasi = $totpopulasi + $populasi;
    }
    $nilaikaliip=$kaliip;
    $iprata=$nilaikaliip / $totpopulasi;
    return $iprata;
}

function getPopulasi(string $db, string $id_produksi) : int
{
	$con = koneksi('berkahgl_portal');
      //populasi
    $qpop=mysqli_query($con, "SELECT sum(qty) as qty from $db.jual where id_produksi='$id_produksi' and id_barang like '%D-%'");
    $dpop=mysqli_fetch_array($qpop);
    $popawal=$dpop[qty];

      //beban
    $subTotalQtyBeban=0;
    $qbeban=mysqli_query($con, "SELECT sum(qty) as qty from $db.beban where id_produksi='$id_produksi'");
    $dbeban=mysqli_fetch_array($qbeban);
    $returndoc=$dbeban[qty];

    $realpopulasi = $popawal - $returndoc;
    //  if ($realpopulasi==""){
    //     $populasi=0;
    // } else {
    //     $populasi=$realpopulasi;
    // }
    return $realpopulasi;
}

function cekGrade($IPrata){
    $rataIP = number_format($IPrata,2);
    if($rataIP > 330){
        $grade = 'A+';
    }elseif (($rataIP >= 300) and ($rataIP <= 330)) {
        $grade = 'A';
    }elseif (($rataIP >= 270) and ($rataIP < 300)) {
        $grade = 'B';
    }elseif (($rataIP >= 250) and ($rataIP < 270)) {
        $grade = 'C+';
    }else{
        $grade = 'C';
    }

    return $grade;
}


function approveFarmOff($id_farms, $app_drt){

	$con = koneksi('berkahgl_portal');
	array_unique($id_farms);

	foreach ($id_farms as $key => $id_farm) {

		if ($app_drt == 'Approve') {

			$sql=mysqli_query($con, "SELECT * from berkahgl_portal.update_ts where app_man='Approve' and ket='Approve' and app_drt='Proses' and id_farm='$id_farm' order by tanggal desc limit 1");
			$s = mysqli_fetch_array($sql);
			$status = $s['status'];
			$unt = $s['unit'];
			$database="berkahgl_" . strtolower($unt);

			$hariini = date("Y-m-d");
			$tahun = substr($hariini, 2, 2);
			$bulan = substr($hariini, 6, 2);
					
			$ceksttsfarm = mysqli_query($con, "SELECT * from $database.farm_update where idfarm='$id_farm'");
			$csttsfarm=mysqli_num_rows($ceksttsfarm);

			if ($csttsfarm!=0){ 
				$ecek = mysqli_query($con, "UPDATE $database.farm_update set blnupdate='$bulan$tahun',status='$status' where idfarm='$id_farm'");
			} else {
				$ecek = mysqli_query($con, "INSERT into $database.farm_update values ('$id_farm','$bulan$tahun','1')");
			} 

			$updatesttus = mysqli_query( $con,"UPDATE berkahgl_portal.update_ts set app_drt='Approve' where id_farm='$id_farm'");

		}else{

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.update_ts set app_drt='Batal' where id_farm='$id_farm'");
		}
	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';	
	}


	return $data;
	
}

# RAK UNIT
function approveRAKunit($ids, $app_drt){

	$con = koneksi('berkahgl_portal');
	array_unique($ids);
	$hariini = date("Y-m-d");

	foreach ($ids as $key => $id) {

		$qbb = mysqli_query($con, "SELECT * FROM berkahgl_portal.ajuan_rak_unit where id_rak='$id' ");
		$dbb = mysqli_fetch_array($qbb);
		$id_rak = $dbb['id_rak'];
		$total = $dbb['nominal'];

		$qk = mysqli_query($con, "SELECT * from berkahgl_portal.karyawan where status_aktf='Aktif' and id_jabatan='FINTRD'") or die (mysqli_error());
		$dk = mysqli_fetch_array($qk);

		if ($app_drt == 'Approve') {

			$ex = explode("+", $dbb[command]);
			$rak = $ex[0];
			$sistem = $ex[1];
			$uraian = $dbb[alasan];

			$unit_asal = db_unit($dbb[unit_asal]);
			$unit_tujuan = db_unit($dbb[unit_tujuan]); 

			if ($dbb[unit_asal]=="BCO"){
				$no_transaksi=strtoupper(no_transaksi_bco("berkahgl_bco", $unit_asal));
			} else if ($dbb[unit_asal]=="KOP"){
				$no_transaksi=strtoupper(no_transaksi_kop());
			} else if ($dbb[unit_asal]=="PST"){
				$no_transaksi=strtoupper(NoTransaksiMGT());
			} else if ($dbb[unit_asal]=="TRD"){ 
				$no_transaksi=strtoupper(getJurnalNoTRD());
				$notran=getJurnalNo();
			} else { 
				$no_transaksi=strtoupper(NoTransaksi($dbb[unit_asal]));
			}

			if ($dbb[unit_tujuan]=="BCO"){
				$no_transaksi1=strtoupper(no_transaksi_bco("berkahgl_bco", $unit_tujuan));
			} else if ($dbb[unit_tujuan]=="KOP"){
				$no_transaksi1=strtoupper(no_transaksi_kop());
			} else if ($dbb[unit_tujuan]=="PST"){
				$no_transaksi1=strtoupper(NoTransaksiMGT());
			} else if ($dbb[unit_tujuan]=="TRD"){ 
				$no_transaksi1=strtoupper(getJurnalNoTRD());
				$notrans=getJurnalNo();
			} else { 
				$no_transaksi1=strtoupper(NoTransaksi($dbb[unit_tujuan]));
			}
			$database_asal="berkahgl_".$unit_asal;
			$database_tujuan="berkahgl_".$unit_tujuan;
			
			$rak_asal=rak_unit($dbb[unit_asal]);
			$rak_tujuan=rak_unit($dbb[unit_tujuan]); // echo "$no_transaksi $no_transaksi1 $unit_asal $unit_tujuan<br>";

			#masuk ke jurnal unit asal
			if ($dbb[unit_asal]=="PST"){ 
				$qj_unit = mysqli_query($con, "INSERT INTO berkahgl_portal.jurnal_mgt VALUES('$hariini','$no_transaksi','$rak_tujuan','$uraian','$total','0','$sistem','$uraian','0','$total', 'Approve', 'Approve', '')")or die (mysqli_error());
			} else {
				$qj = mysqli_query($con, "INSERT INTO berkahgl_portal.jurnal VALUES('$hariini','$no_transaksi','$rak_tujuan','$uraian','$total','0','$sistem','$uraian','0','$total','$dbb[unit_asal]','Approve','Approve','Approve','jurnal harian','')") or die(mysqli_error());
				if ($dbb[unit_asal]=="TRD"){ 
					$jurnal_unit=mysqli_query($con, "INSERT into berkahgl_trd.b_jurnal values ('$hariini','$notran','$rak_tujuan','$uraian','$total','0','$sistem','$uraian','0','$total', 'jrhn', '$dk[nik]', '', '-', '1', '0')")or die(mysqli_error());
				} else {
					$qj_unit = mysqli_query($con, "INSERT INTO ".$database_asal.".jurnal VALUES('$hariini','$no_transaksi','$rak_tujuan','$uraian','$total','0','$sistem','$uraian','0','$total')")or die (mysqli_error());
				}
			}

			#masuk ke jurnal unit tujuan
			if ($dbb[unit_tujuan]=="PST"){ 
				//$jurnal_unit=mysql_query("insert into berkahgl_portal.jurnal_mgt values ('$hariini','$no_transaksi1','$sistem','$uraian','$total','0','$rak_asal','$uraian','0','$total', 'Approve', 'Approve', '')")or die(mysql_error());6.3.1.01
				$jurnal_unit=mysqli_query($con, "INSERT into berkahgl_portal.jurnal_mgt values ('$hariini','$no_transaksi1','6.3.1.01','$uraian','$total','0','$rak_asal','$uraian','0','$total', 'Approve', 'Approve', '')")or die(mysqli_error());
			} else {
				$jurnal=mysqli_query($con, "INSERT into berkahgl_portal.jurnal values ('$hariini','$no_transaksi1','$sistem','$uraian','$total','0','$rak_asal','$uraian','0','$total','$dbb[unit_tujuan]','Approve','Approve','Approve','jurnal harian','')")or die(mysqli_error());
				if ($dbb[unit_tujuan]=="TRD"){ 
					$jurnal_unit=mysqli_query($con, "INSERT into berkahgl_trd.b_jurnal values ('$hariini','$notrans','$sistem','$uraian','$total','0','$rak_asal','$uraian','0','$total', 'jrhn', '$dk[nik]', '', '-', '1', '0')")or die(mysqli_error());
				} else{ 
					$jurnal_unit=mysqli_query($con, "INSERT into ".$database_tujuan.".jurnal values ('$hariini','$no_transaksi1','$sistem','$uraian','$total','0','$rak_asal','$uraian','0','$total')")or die(mysqli_error());
				}
			}
				

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_rak_unit set app_drt='Approve', tgl_app_drt='$hariini' where id_rak='$id_rak'")or die (mysqli_error());
		}else{

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_rak_unit set app_drt='Batal', tgl_app_drt='$hariini' where id_rak='$id_rak'");
		}

	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';		
	}

	return $data;
	
}

function db_unit($unit){
	$con = koneksi('berkahgl_portal');
    $qu = mysqli_query($con, "SELECT * from berkahgl_portal.unit where id_unit='$unit'") or die (mysqli_error());
    $du = mysqli_fetch_array($qu);

    return $du[db];
}

function no_transaksi_bco($db, $unit){
	$con = koneksi('berkahgl_portal');

	$qprod=mysqli_query($con, "SELECT * from berkahgl_bco.produk_bco where db='$db' order by nama asc") or die (mysqli_error());
	$dprod=mysqli_fetch_array($qprod);
	$j=$dprod[id];

	$kd_unit = nm_cabang($unit);
	$comp = "$kd_unit-$j"; 
	

	$s = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	$d = mysqli_num_rows($s);
	if ($d != 0){
		$s1 = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	} else {
		$s1 = mysqli_query($con, "SELECT no_transaksi from $berkahgl_bco.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	}
	
	
	$j1 = mysqli_fetch_assoc($s1);
	$no_trans_terakhir = substr($j1['no_transaksi'],24,5);
	$mtrans_b4 = substr($j1['no_transaksi'],21,2); 
	$ytrans_b4 = substr($j1['no_transaksi'],18,2); 
	$todayn = date("Y-m-d");
	$tahunn = substr($todayn,2,2);
	$blnn = substr($todayn,5,2);
	if($tahunn > $ytrans_b4){
		$no_trans_terakhir1=1;
	}else if(($tahunn == $ytrans_b4)and($blnn > $mtrans_b4)){
		$no_trans_terakhir1=1;
	}else if(($tahunn == $ytrans_b4)and($blnn == $mtrans_b4)) {
		$no_trans_terakhir1=$no_trans_terakhir+1;
	}
	//menambahkan angka nol
	if($no_trans_terakhir1 < 10) {
		$notrans_t1 = '0000'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 100)	{
		$notrans_t1 = '000'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 1000){
		$notrans_t1 = '00'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 10000){
		$notrans_t1 = '0'.$no_trans_terakhir1;
	}else {
		$notrans_t1 = $no_trans_terakhir1;
	} 
	$no_transaksi="04/$comp-04/$tahunn/$blnn/$notrans_t1"; 
	return $no_transaksi;
}

function nm_cabang($id_unit) {
	$con = koneksi('berkahgl_bco');

    $qcabang = mysqli_query($con, "SELECT * from berkahgl_bco.data_unit where id_unit='$id_unit'");
	$dcabang = mysqli_fetch_array($qcabang);
	
	return $dcabang[nama];
}


function no_transaksi_kop(){
	$con = koneksi('berkahgl_portal');
	$comp = "KOP";

	$s = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	$d=mysqli_num_rows($s);
	if ($d != 0){
		$s1 = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	} else {
		$s1 = mysqli_query($con, "SELECT no_transaksi from berkahgl_koperasi.jurnal where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	}
			
	$j1 = mysqli_fetch_assoc($s1);
	$no_trans_terakhir = substr($j1['no_transaksi'],16,5);
	$mtrans_b4 = substr($j1['no_transaksi'],13,2); 
	$ytrans_b4 = substr($j1['no_transaksi'],10,2); 
	$todayn = date("Y-m-d");
	$tahunn = substr($todayn,2,2);
	$blnn = substr($todayn,5,2);
	if($tahunn > $ytrans_b4){
		$no_trans_terakhir1=1;
	}else if(($tahunn == $ytrans_b4)and($blnn > $mtrans_b4)){
		$no_trans_terakhir1=1;
	}else if(($tahunn == $ytrans_b4)and($blnn == $mtrans_b4)) {
		$no_trans_terakhir1=$no_trans_terakhir+1;
	}
	//menambahkan angka nol
	if($no_trans_terakhir1 < 10) {
		$notrans_t1 = '0000'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 100)	{
		$notrans_t1 = '000'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 1000){
		$notrans_t1 = '00'.$no_trans_terakhir1;
	}else if($no_trans_terakhir1 < 10000){
		$notrans_t1 = '0'.$no_trans_terakhir1;
	}else {
		$notrans_t1 = $no_trans_terakhir1;
	}
	$no_transaksi="04/$comp-04/$tahunn/$blnn/$notrans_t1";
	return $no_transaksi;
}

function NoTransaksiMGT(){
	$con = koneksi('berkahgl_portal');

    $comp = "PST";
    $s1 = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal_mgt where no_transaksi like '04/$comp-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
    
    $j1 = mysqli_fetch_assoc($s1);
    $no_trans_terakhir = substr($j1['no_transaksi'],16,5);
    $mtrans_b4 = substr($j1['no_transaksi'],13,2);
    $ytrans_b4 = substr($j1['no_transaksi'],10,2);
    // echo "<div class='alt-reg' center><label>No.Transaksi Terakhir ==> <b>$j1[no_transaksi]</font></label></div>";
    $todayn = date("Y-m-d");
    $tahunn = substr($todayn,2,2);
    $blnn = substr($todayn,5,2);
    if($tahunn > $ytrans_b4){
        $no_trans_terakhir1=1;
    }else if(($tahunn == $ytrans_b4)and($blnn > $mtrans_b4)){
        $no_trans_terakhir1=1;
    }else if(($tahunn == $ytrans_b4)and($blnn == $mtrans_b4)) {
        $no_trans_terakhir1=$no_trans_terakhir+1;
    }
    //menambahkan angka nol
    if($no_trans_terakhir1 < 10) {
        $notrans_t1 = '0000'.$no_trans_terakhir1;
    }else if($no_trans_terakhir1 < 100) {
        $notrans_t1 = '000'.$no_trans_terakhir1;
    }else if($no_trans_terakhir1 < 1000){
        $notrans_t1 = '00'.$no_trans_terakhir1;
    }else if($no_trans_terakhir1 < 10000){
        $notrans_t1 = '0'.$no_trans_terakhir1;
    }else {
        $notrans_t1 = $no_trans_terakhir1;
    }
    
    $no_transaksi="04/$comp-04/$tahunn/$blnn/$notrans_t1";
    
    return $no_transaksi;
}


function getJurnalNoTRD(){
	$con = koneksi('berkahgl_portal');
	  $bln = date('y/m');

	  $s = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/PAY/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	  $d1 = mysqli_num_rows($s);
	  if ($d1 != 0){ 
	      $q = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/PAY/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	  } else { 
	      $q = mysqli_query($con, "SELECT no_transaksi from berkahgl_trd.b_jurnal where no_transaksi like '04/PAY/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
	  }

	 // $q = mysql_query("SELECT * from berkahgl_trd.b_jurnal where substring(`no_transaksi`,8,5)='$bln' order by no_transaksi DESC limit 0,1");
	  $d = mysqli_fetch_array($q);
	  $numsql = mysqli_num_rows($q);
	  $notrx = substr($d['no_transaksi'], -4);
	  if ($numsql != 0) {
	    $idtrx = $notrx;
	  } else {
	    $idtrx = 0;
	  }
	  $num = $idtrx + 1;
	  $idcut = sprintf("%04d", $num);
	  $id = "04/PAY/" . $bln . "/" . $idcut;
	  return $id;
}

function getJurnalNo(){
	$con = koneksi('berkahgl_trd');
	$bln = date('y/m');
	$q = mysqli_query($con, "SELECT * from berkahgl_trd.b_jurnal where substring(`no_transaksi`,8,5)='$bln' order by no_transaksi DESC limit 0,1");
	$d = mysqli_fetch_array($q);
	$numsql = mysqli_num_rows($q);
	$notrx = substr($d['no_transaksi'], -4);
	if ($numsql != 0) {
		$idtrx = $notrx;
	} else {
		$idtrx = 0;
	}
	$num = $idtrx + 1;
	$idcut = sprintf("%04d", $num);
	$id = "04/PAY/" . $bln . "/" . $idcut;
	return $id;
}

function NoTransaksi($id_unit){
	$con = koneksi('berkahgl_portal');
    // cari id unit
    //$id_unit = $_SESSION['unit'];
    if (($id_unit=="BS1") or ($id_unit == "bs1")){
        $unit = "BSM";
        $unt = "bsm1";
    } else if (($id_unit=="BS2") or ($id_unit=="bs2")){
        $unit = "BS2";
        $unt = "bsm2";
    } else {
        $unit = strtoupper($id_unit);
        $unt = strtolower($id_unit);
    }
    $db ='berkahgl_'.strtolower($unt);

    $s = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$unit-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
    $d = mysqli_num_rows($s);
    if ($d != 0){ 
        $s1 = mysqli_query($con, "SELECT no_transaksi from berkahgl_portal.jurnal where no_transaksi like '04/$unit-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
    } else { 
        $s1 = mysqli_query($con, "SELECT no_transaksi from $db.jurnal where no_transaksi like '04/$unit-04/%' order by no_transaksi DESC limit 0,1") or die (mysqli_error());
    }
    $j1 = mysqli_fetch_assoc($s1);
    $no_trans_terakhir =(int) substr($j1['no_transaksi'],16,5);
    $mtrans = substr($j1['no_transaksi'],13,2);
    $ytrans = substr($j1['no_transaksi'],10,2);
    // cari bulan dan tahun
    $todayn = date("Y-m-d");
    $tahun = substr($todayn,2,2);
    $bln = substr($todayn,5,2);
    //buat no transaksi terakhir
    
    if($tahun > $ytrans){
        $no_trans_terakhir=1;
    }else if(($tahun == $ytrans)and($bln > $mtrans)){
        $no_trans_terakhir=1;
    }elseif(($tahun == $ytrans)and($bln == $mtrans)){
        $no_trans_terakhir++;
    } 
    //buat nomor transkasi baru
    $no_transaksi_baru ='04/'.$unit.'-04/'.$tahun.'/'.$bln.'/'.sprintf("%05s", $no_trans_terakhir);

    return $no_transaksi_baru;
}

#RAK UNIT
function rak_unit($unit){
    if ($unit=="BAS"){
        $rak="2.1.1.02";
    } else if ($unit=="BBD"){
        $rak="2.1.1.22";
    } else if ($unit=="BCO"){
        $rak="2.1.1.10";
    } else if ($unit=="BDP"){
        $rak="2.1.1.21";
    } else if ($unit=="BGT"){
        $rak="2.1.1.11";
    } else if ($unit=="BKN"){
        $rak="2.1.1.23";
    } else if ($unit=="BMM"){
        $rak="2.1.1.09";
    } else if ($unit=="BS1"){
        $rak="2.1.1.06";
    } else if ($unit=="BS2"){
        $rak="2.1.1.07";
    } else if ($unit=="BSB"){
        $rak="2.1.1.05";
    } else if ($unit=="BUS"){
        $rak="2.1.1.08";
    } else if ($unit=="KOP"){
        $rak="2.1.1.15";
    } else if ($unit=="PST"){ 
        $rak="2.1.1.12";
    } else if ($unit=="RND"){
        $rak="2.1.1.13";
    } else if ($unit=="TRD"){
        $rak="2.1.1.14";
    } else {
        $rak="";
    }

    return $rak;
}


function approveBayarBGT($ids, $app_drt){

	$con = koneksi('berkahgl_portal');
	array_unique($ids);
	$hariini=date("Y-m-d");

	foreach ($ids as $key => $id) {

		$qbb = mysqli_query($con, "SELECT * FROM berkahgl_portal.ajuan_bayar_bgt where id_bayar_bgt='$id' ");
		$dbb=mysqli_fetch_array($qbb);
		$id_bayar_bgt=$dbb['id_bayar_bgt'];
		$nominalasli=$dbb['nominal'];
		$sistem=$dbb['sistem'];

		if ($app_drt == 'Approve') {

			$ex=explode("+", $dbb[command]);
			$mkt=$ex[0];
			$dari=$ex[1];
			$uraianlkp=$ex[2];
			$nmsistem=$ex[3];

			$notran=getJurnalNo();
			$ref = "REF-" . $notran;
			
			$no_transaksi1=strtoupper(NoTransaksi("BGT"));

			#masuk ke jurnal trd
			$jtrd=mysqli_query($con, "INSERT into berkahgl_trd.b_jurnal values('$hariini','$notran','4.1.1.04','$uraianlkp','$nominalasli','0','$sistem','$uraianlkp','0','$nominalasli','$ref','$dbb[nik_ajuan]','$mkt','$dari','1','0')");
		 	$rb=mysqli_query($con, "INSERT into berkahgl_trd.ref_bayar values('$ref','','tanpa DO','$nominalasli')");

			#masuk ke jurnal unit tujuan
			$jurnal=mysqli_query($con, "INSERT into berkahgl_portal.jurnal values ('$hariini','$no_transaksi1','$sistem','$uraianlkp','$nominalasli','0','1.1.2.01','$uraianlkp','0','$nominalasli','bgt','Approve','Approve','Approve','pembayaran konsumen BGT','')")or die(mysqli_error()); 
			$jurnal_unit=mysqli_query($con, "INSERT into berkahgl_bgt.jurnal values ('$hariini','$no_transaksi1','$sistem','$uraianlkp','$nominalasli','0','1.1.2.01','$uraianlkp','0','$nominalasli')")or die(mysqli_error());
			$qpm = mysqli_query($con, "INSERT into berkahgl_bgt.pembayaran_konsumen (id_konsumen, tgl_bayar, jml_bayar, jml_potongan, jml_retur, ket_potongan, sistem, no_transaksi1, ket) values ('BGT-005','$hariini','$nominalasli','0','0','','$nmsistem','$no_transaksi1','$uraianlkp')") or die (mysqli_error(0));

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_bayar_bgt set app_drt='Approve', tgl_app_drt='$hariini' where id_bayar_bgt='$id_bayar_bgt'")or die (mysqli_error());

		}else{

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_bayar_bgt set app_drt='Batal', tgl_app_drt='$hariini' where id_bayar_bgt='$id_bayar_bgt'")or die (mysqli_error());
			
		}

	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';		
	}

	return $data;
	
}


function approveBaharBakar($ids, $app_drt){

	$con = koneksi('berkahgl_portal');
	array_unique($ids);
	$hariini=date("Y-m-d");

	foreach ($ids as $key => $id) {

		$qbb = mysqli_query($con, "SELECT * FROM berkahgl_portal.ajuan_bahan_bakar where id_bahanbakar='$id' ");
		$dbb=mysqli_fetch_array($qbb);
		$id_bahanbakar=$dbb['id_bahanbakar'];
		$no_transaksi=NoTransaksi("BGT");
		
		if ($app_drt == 'Approve') {

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_bahan_bakar set app_drt='Approve', tgl_app_drt='$hariini' where id_bahanbakar='$id_bahanbakar'")or die (mysqli_error());

			//$command="$nopol+$liter+$total+$no_pengadaan+$supir+$id_bbm+$tanggal+$debet+$kredit";
			$command=$dbb['command'];
			$ex=explode("+", $command);
			$nopol=$ex[0];
			$liter=$ex[1];
			$total=$ex[2];
			$no_pengadaan=$ex[3];
			$supir=$ex[4];
			$id_bbm=$ex[5];
			$tanggal=$ex[6];
			$debet=$ex[7];
			$kredit=$ex[8];
			$npol=$ex[8];
			$operasionalbbm="$no_pengadaan+$supir+$liter+$id_bbm+$tanggal";
			$uraian1="Bahan Bakar $nopol : $liter L $npol";

			#masuk ke jurnal BGT
			$qj = mysqli_query($con, "INSERT INTO berkahgl_portal.jurnal VALUES('$tanggal','$no_transaksi','$debet','Bahan Bakar $nopol : $liter L $npol','$total','','$kredit','Bahan Bakar $nopol : $liter L','','$total','$unit','Approve','Approve','Approve','operasional bbm','$operasionalbbm')")  or die (mysqli_error());
			$qpm = mysqli_query($con, "INSERT into berkahgl_bgt.konsumsi_bbm values ('','$tanggal','$no_pengadaan','$supir','$liter','$id_bbm','$no_transaksi')") or die (mysqli_error());
			$qj_unit = mysqli_query($con, "INSERT INTO berkahgl_bgt.jurnal VALUES('$tanggal','$no_transaksi','$debet','$uraian1','$total','','$kredit','Bahan Bakar $nopol : $liter L','','$total')")or die (mysqli_error());

			#masuk ke jurnal unit
			if ($dbb[unit_tujuan]=="TRD"){
				$qk=mysqli_query($con, "SELECT * from berkahgl_portal.karyawan where status_aktf='Aktif' and id_jabatan='FINTRD'") or die (mysqli_error());
				$dk=mysqli_fetch_array($qk);
				$qc=mysqli_query($con, "SELECT * from berkahgl_portal.conv_trading where nik_baru='$supir'") or die(mysqli_error()); 
				$dc=mysqli_fetch_array($qc);
				$no_transaksi1=getJurnalNoTRD();
				$notran=getJurnalNo();
				$jurnal=mysqli_query($con, "INSERT into berkahgl_portal.jurnal values ('$tanggal','$no_transaksi1','2.1.1.11','Bahan Bakar $nopol : $liter L','0','$total','6.2.1.01','Bahan Bakar $nopol : $liter L','$total','0','trd','Approve','Approve','Approve','jurnal harian trd','')")or die(mysqli_error());
				$jurnal_unit=mysqli_query($con, "INSERT into berkahgl_trd.b_jurnal values ('$tanggal','$notran','2.1.1.11','Bahan Bakar $nopol : $liter L','0','$total','6.2.1.01','Bahan Bakar $nopol : $liter L','$total','0', 'jrhn', '$dk[nik]', '$dc[nik_awal]', '-', '1', '0')")or die(mysqli_error());
				$beban_mrt=mysqli_query($con, "INSERT into berkahgl_trd.b_operasional values ('','$notran','$dc[nik_awal]')")or die(mysqli_error());
			} else{
				$unt=$dbb[unit_tujuan];
				if ($unt=="BS1"){
					$unit = "bsm1";

				}else if ($unt=="BS2"){
					$unit = "bsm2";
				}else {
					$unit = strtolower($unt);
				}
				//$no_transaksi1=NoTransaksi($unt);

				if ($dbb[unit_tujuan]=="BCO"){
					$no_transaksi1=strtoupper(no_transaksi_bco("berkahgl_bco", $unit_tujuan));
				} else if ($dbb[unit_tujuan]=="KOP"){
					$no_transaksi1=strtoupper(no_transaksi_kop());
				} else if ($dbb[unit_tujuan]=="PST"){
					$no_transaksi1=strtoupper(NoTransaksiMGT());
				} else if ($dbb[unit_tujuan]=="TRD"){ 
					$no_transaksi1=strtoupper(getJurnalNoTRD());
					$notrans=getJurnalNo();
				} else { 
					$no_transaksi1=strtoupper(NoTransaksi($dbb[unit_tujuan]));
				}
				$database="berkahgl_".$unit;

				if ($unt=="PST"){
					$jurnal_unit = mysqli_query($con, "INSERT INTO berkahgl_portal.jurnal_mgt VALUES('$tanggal','$no_transaksi1','2.1.1.11','Bahan Bakar $nopol : $liter L','0','$total','6.2.1.01','Bahan Bakar $nopol : $liter L','$total','0', 'Approve', 'Approve', '')")or die (mysqli_error());
				} else {
					$jurnal=mysqli_query($con, "INSERT into berkahgl_portal.jurnal values ('$tanggal','$no_transaksi1','2.1.1.11','Bahan Bakar $nopol : $liter L','0','$total','6.2.1.01','Bahan Bakar $nopol : $liter L','$total','0','trd','Approve','Approve','Approve','jurnal harian trd','')")or die(mysqli_error());
					$jurnal_unit=mysqli_query($con, "INSERT into $database.jurnal values ('$tanggal','$no_transaksi1','2.1.1.11','Bahan Bakar $nopol : $liter L','0','$total','6.2.1.01','Bahan Bakar $nopol : $liter L','$total','0')")or die(mysqli_error());
				}
				
			}		

		}else{

			$updatesttus = mysqli_query($con, "UPDATE berkahgl_portal.ajuan_bahan_bakar set app_drt='Batal', tgl_app_drt='$hariini' where id_bahanbakar='$id_bahanbakar'")or die (mysqli_error());
			
		}

	}

	if (mysqli_affected_rows($con) > 0) {
		$data['status'] = 'success';
		$data['data'] = '1';
	}else{
		$data['status'] = 'failed';
		$data['data'] = '0';		
	}

	return $data;
	
}


 ?>