<?php

function rupiah1($angka){
	$hasil_rupiah = "Rp " . number_format($angka, 0, ".", ".");
	return $hasil_rupiah;
}
 
function rupiah2($angka){
	$hasil_rupiah = "Rp " . number_format($angka, 1, ",", ".");
	return $hasil_rupiah;
}
 
function rupiah3($angka){
	$hasil_rupiah = "Rp " . number_format($angka, 2, ".", ",");
	return $hasil_rupiah;
}
function anti_inject($data)
	{
		$filter_sql = stripslashes(strip_tags(htmlspecialchars($data,ENT_QUOTES)));
		return $filter_sql;
	}
function NamaBulan($bln) 
{
	if ($bln == 1)
		return "Januari";
	elseif ($bln == 2)
		return "Februari";		
	elseif ($bln == 3)
		return "Maret";		
	elseif ($bln == 4)
		return "April";		
	elseif ($bln == 5)
		return "Mei";
	elseif ($bln == 6)
		return "Juni";		
	elseif ($bln == 7)
		return "Juli";
	elseif ($bln == 8)
		return "Agustus";		
	elseif ($bln == 9)
		return "September";
	elseif ($bln == 10)
		return "Oktober";		
	elseif ($bln == 11)
		return "November";
	elseif ($bln == 12)
		return "Desember";		
}

function NamaHari($hari) 
{
	if ($hari == 1)
		return "Senin";
	elseif ($hari == 2)
		return "Selasa";		
	elseif ($hari == 3)
		return "Rabu";		
	elseif ($hari == 4)
		return "Kamis";		
	elseif ($hari == 5)
		return "Jumat";
	elseif ($hari == 6)
		return "Sabtu";
	elseif ($hari == 7)
		return "Minggu";
}
function provinsi($p)
	{
		$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
		$sqlprov = mysqli_query($conn, "SELECT * FROM `provinsi` WHERE id_prov = '$p'");
		$m = mysqli_fetch_assoc($sqlprov);

		return $m['nama'];
	}
function kabupaten($k)
{
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sqlkeb = mysqli_query($conn, "SELECT * FROM `kabupaten` WHERE id_kab = '$k'");
	$k = mysqli_fetch_assoc($sqlkeb);

	return $k['nama'];
}
function kecamatan($kc)
{
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sqlkec = mysqli_query($conn, "SELECT * FROM `kecamatan` WHERE id_kec = '$kc'");
	$kec = mysqli_fetch_assoc($sqlkec);

	return $kec['nama'];
}
function kelurahan($kl)
{
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sqlkel = mysqli_query($conn, "SELECT * FROM `kelurahan` WHERE id_kel = '$kl'");
	$kel = mysqli_fetch_assoc($sqlkel);

	return $kel['nama'];
}
function nama_toko_absen($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT tempat FROM tb_setting_absensi WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return $m['tempat'];
}
function pegawai($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT pegawai_nama FROM `pegawai`
                        WHERE pegawai_id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['pegawai_nama'];
}

function kategori_jenis_pekerjaan($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `kategori_jenis_pekerjaan`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_pengerjaan($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `item_pengerjaan`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_pt($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `item_pembayaran`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}

function barang($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_barang`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function supplier($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_supplier`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function marketing($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_marketing`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function tukang($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_tukang`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function bank($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_bank`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function jabatan($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_jabatan`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function jabatan_pegawai($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sqlp = mysqli_query($conn,"SELECT pegawai_jabatan FROM `pegawai`
                        WHERE pegawai_id = '$id'") or die(mysqli_error());
    $mp = mysqli_fetch_assoc($sqlp);
    
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_jabatan`
                        WHERE id = '$mp[pegawai_jabatan]'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_retensi($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_pembayaran`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_opname($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_item_opname`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function detail_item_bayar($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT item FROM `tb_detail_biaya`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return item_biaya($m['item']);
}
function detail_item_opname($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT id_unit FROM `tb_detail_opname_unit`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  rumah($m['id_unit']);
}
function item_biaya($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_biaya_lain`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_detail_pesan($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_detail`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function unit($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
    mysqli_set_charset($conn, "utf8mb4");
    $sql = mysqli_query($conn,"SELECT nama FROM `unit`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);
    return htmlspecialchars($m['nama'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    //return  $m['nama'];
}
function progres_unit($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql_kondisi = mysqli_query($conn,"SELECT * FROM tb_timeline_unit WHERE id_pesan='$id'");
	$tot=0;	                            		
    while($k = mysqli_fetch_assoc($sql_kondisi))
    {		
    	$sql = mysqli_query($conn,"SELECT bobot FROM `tb_unit`
                        WHERE id = '$k[timeline]'") or die(mysqli_error());
   		$m = mysqli_fetch_assoc($sql);

    	$tot += $m['bobot'];
    }

    return  $tot;
}

function kategori_kantor($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_kategori_ca_op`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function kategori_proyek($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_kategori`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function kategori_biaya_proyek($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_kategori_biaya_proyek`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}

function item_ca_kantor($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_item_ca_kantor`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_ca_proyek($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_item_ca_proyek`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function item_biaya_proyek($id)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");  
    $sql = mysqli_query($conn,"SELECT nama FROM `tb_item_biaya_proyek`
                        WHERE id = '$id'") or die(mysqli_error());
    $m = mysqli_fetch_assoc($sql);

    return  $m['nama'];
}
function rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `nama` FROM `tb_rumah` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function type_rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `id_type` FROM `tb_rumah` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	$sql1 = mysqli_query($conn,"SELECT `nama` FROM `tb_type` WHERE `id` = '$m[id_type]'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	return $m1['nama'];
}
function luas_rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `id_type` FROM `tb_rumah` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	$sql1 = mysqli_query($conn,"SELECT `luas_bangunan` FROM `tb_type` WHERE `id` = '$m[id_type]'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	return $m1['luas_bangunan'];
}
function rumah_book($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM `pesanan_tmp` WHERE `id_rumah` = '$id' ") or die(mysqli_error());
	$m = mysqli_num_rows($sql);
    if($m > 0 ){
        $status = "ada";
    }else{
        $status = "kosong";
    }
    $statusx = "kosong";
	return $statusx;
}
function status_rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `status` FROM `tb_pesan` WHERE `id_rumah` = '$id' ORDER BY id DESC LIMIT 1") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['status'];
}
function status_wawancara($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$query = "SELECT `id`, `status`, `pembayaran` FROM `tb_pesan` WHERE `id_rumah` = '$id' ORDER BY id DESC LIMIT 1";
			$sql_kul = mysqli_query($conn,$query);	
			$ps = mysqli_fetch_assoc($sql_kul);
			if($ps['pembayaran'] == "Kredit"){
			    $sql = mysqli_query($conn,"SELECT `status_wawancara` FROM `tb_kpr` WHERE `id_rumah` = '$id' ORDER BY id DESC LIMIT 1") or die(mysqli_error());
	            $m = mysqli_fetch_assoc($sql);
	            $hasil =$m['status_wawancara'];
			}else{
			    if($ps['status']=="ACC"){
			        $hasil = "Approve";
			    }else{
			         $hasil = "Pending";
			    }
			}
	

	return $hasil;
}
function wawancara($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$query = "SELECT `id`, `status`, `pembayaran` FROM `tb_pesan` WHERE `id_rumah` = '$id' ORDER BY id DESC LIMIT 1";
	$sql_kul = mysqli_query($conn,$query);	
	$ps = mysqli_fetch_assoc($sql_kul);
	if($ps['pembayaran'] == "Kredit"){
    	$sql = mysqli_query($conn,"SELECT `status_wawancara` FROM `tb_kpr` WHERE `id_rumah` = '$id' ORDER BY id DESC LIMIT 1") or die(mysqli_error());
    	$m = mysqli_num_rows($sql);
	}else{
	    if($ps['status']=="ACC"){
	        $m = 1;
	    }else{
	         $m = 0;
	    }
	}

	return $m;
}
function status_pesanan($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM `tb_pesan_proses` WHERE `id_rumah` = '$id'") or die(mysqli_error());
	$m = mysqli_num_rows($sql);

	return $m;
}
function detail_pesanan($id,$idpesan)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM `tb_pesan_termasuk` WHERE `id_detail` = '$id' AND `idpesan` = '$idpesan'") or die(mysqli_error());
	$m = mysqli_num_rows($sql);
    if($m > 0 ){
        $status = "checked";
    }else{
        $status = "";
    }
    $statusx = "kosong";
	return $status;
}
function stok_barang($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql2 = mysqli_query($conn,"SELECT sum(`qty`) as total FROM `tb_gudang` WHERE `idbarang` = '$id' ") or die(mysqli_error());
	$m2 = mysqli_fetch_assoc($sql2);
	$rmsk = $m2['total'];
	$sql1 = mysqli_query($conn,"SELECT sum(`qty`) as total FROM `tb_detail_logistik_item` WHERE `iditem` = '$id' ") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);
	$rqty = $m1['total'];
	$sisas = $rmsk - $rqty;
	if ($sisas < 0 ) {
		$sisa = 0;
	}else{
		$sisa = $sisas;
	}

	return $sisas;
}
function stok_barang_qa($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql2 = mysqli_query($conn,"SELECT sum(`qty`) as total FROM `tb_gudang_qa` WHERE `idbarang` = '$id' ") or die(mysqli_error());
	$m2 = mysqli_fetch_assoc($sql2);
	$rmsk = $m2['total'];
	$sql1 = mysqli_query($conn,"SELECT sum(`qty`) as total FROM `tb_detail_logistik_qa` WHERE `idbarang` = '$id' ") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);
	$rqty = $m1['total'];
	$sisas = $rmsk - $rqty;
	if ($sisas < 0 ) {
		$sisa = 0;
	}else{
		$sisa = $sisas;
	}

	return $sisas;
}
function kontraktor($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `nama` FROM `tb_kontraktor` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function data_legal($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `nama` FROM `tb_data_legal` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function cluster($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT `nama` FROM `tb_cluster` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function total_biaya_lain($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_detail_biaya` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_opname($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$query = "SELECT * FROM `tb_detail_opname_item2` WHERE `id_opname`= '$id'";
	$sql_kul = mysqli_query($conn,$query);	
	$jx = mysqli_num_rows($sql_kul);
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
	    $sum = $obt['nominal'];
		$amt += $sum;
	    
	}
    
	return $amt;
}
function total_logistik($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$query = "SELECT * FROM `tb_detail_logistik_item` WHERE `id_logistik`= '$id'";
	$sql_kul = mysqli_query($conn,$query);	
	$jx = mysqli_num_rows($sql_kul);
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
	    $sum = $obt['qty'] ;
		$amt += $sum;
	    
	}
    
	return $amt;
}
function pembayaran_opname_nominal($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_detail_opbame_bayar` WHERE `id_opname` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}

function pembayaran_opname_persen($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`persen`) as jumlah FROM `tb_detail_opbame_bayar` WHERE `id_opname` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_opname_item($id,$item)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT* FROM `tb_detail_opname_item2` WHERE `id_opname` = '$id' AND `id` = '$item'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	$tot =  $m['nominal'];
    
	return $tot;
}
function pembayaran_opname_persen_item($id,$item)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`persen`) as jumlah FROM `tb_detail_opbame_bayar` WHERE `id_opname` = '$id' AND `item` = '$item'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_retensi($id,$iditem)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT sum(`nominal`) as total FROM `tb_retensi_dana` WHERE `id_pesan` = '$id' AND `id_item` = '$iditem'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['total'];

	return $hasil;
}
function total_retensi_cair($id,$iditem)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT sum(`nominal`) as total FROM `tb_retensi_cair` WHERE `id_pesan` = '$id' AND `id_item` = '$iditem'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['total'];

	return $hasil;
}
function total_retensi_kpr($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT `retensi` FROM `tb_kpr` WHERE `id_pesan` = '$id' AND `status_wawancara` = 'Approve'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['retensi'];

	return $hasil;
}
function total_retensi_rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT sum(`nominal`) as total FROM `tb_retensi_dana` WHERE `id_pesan` = '$id'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['total'];

	return $hasil;
}
function total_retensi_cair_rumah($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT sum(`nominal`) as total FROM `tb_retensi_cair` WHERE `id_pesan` = '$id'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['total'];

	return $hasil;
}
function total_biaya_proyek()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$sql1 = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`status` = 'ACC'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['jumlah'];

	return $hasil;
}
function total_perunit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT COUNT(a.`id`) as jumlah
							FROM `tb_rumah` as a 
							JOIN `tb_blok` as b ON a.`id_blok` = b.`id` 
							WHERE b.`id_cluster` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	$sql1 = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			WHERE b.`status` = 'ACC'") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);

	$hasil = $m1['jumlah']/$m['jumlah'];

	return $hasil;
}
function jumlah_kpr($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM `tb_pesan` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $totkel = $m['kelebihan']*$m['harga_lebih'];
	$totharju = $m['harga_jual']+$totkel;
	$tothad = $totharju-$m['diskon'];
	
	return $tothad;
}
function total_cash_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_detail_cash` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_kpr_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_detail_cicilan` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_kpr($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_detail_cicilan` WHERE `kode_pesanan` = '$id' AND pembayaran like '%Pembayaran UM%'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_bayar_cash($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Cash'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}

function total_bayar_kpr($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'UM'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_bayar_tum($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'TUM'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_timeline($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' ") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_bayar_lain($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_bayar_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_detail_um` WHERE `id_um` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_bayar_pt($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as jumlah FROM `tb_bayar_pt` WHERE `id_pesan` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['jumlah'];
}
function total_progress($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	$query1 = "SELECT * FROM `tb_pesan` WHERE `id` = '$id' ";
	$sql_kul1 = mysqli_query($conn,$query1);	
	$c = mysqli_fetch_assoc($sql_kul1);
	if($c['pembayaran']=="Cash"){
	    $sql = mysqli_query($conn,"SELECT * FROM `tb_detail_cash` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	    $m = mysqli_num_rows($sql);
	    $sql2 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Cash'") or die(mysqli_error());
	    $m2 = mysqli_num_rows($sql2);
	}else{
	    $sql = mysqli_query($conn,"SELECT * FROM `tb_detail_cicilan` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	    $m = mysqli_num_rows($sql);
	    $sql2 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'UM'") or die(mysqli_error());
	    $m2 = mysqli_num_rows($sql2);
	}
	

	$sql1 = mysqli_query($conn,"SELECT * FROM `tb_detail_biaya` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	$m1 = mysqli_num_rows($sql1);

	

	$sql3 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$m3 = mysqli_num_rows($sql3);

    $tot = $m+$m1;
    $totb = $m2+$m3;

    $pr = ($totb!=0)?($totb/$tot) * 100:0; 
    $hsl = number_format($pr);

	return $hsl;
}
function total_progress_history($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	$query1 = "SELECT * FROM `tb_pesan_history` WHERE `id` = '$id' ";
	$sql_kul1 = mysqli_query($conn,$query1);	
	$c = mysqli_fetch_assoc($sql_kul1);
	if($c['pembayaran']=="Cash"){
	    $sql = mysqli_query($conn,"SELECT * FROM `tb_detail_cash` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	    $m = mysqli_num_rows($sql);
	    $sql2 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Cash'") or die(mysqli_error());
	    $m2 = mysqli_num_rows($sql2);
	}else{
	    $sql = mysqli_query($conn,"SELECT * FROM `tb_detail_cicilan` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	    $m = mysqli_num_rows($sql);
	    $sql2 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'UM'") or die(mysqli_error());
	    $m2 = mysqli_num_rows($sql2);
	}
	

	$sql1 = mysqli_query($conn,"SELECT * FROM `tb_detail_biaya` WHERE `kode_pesanan` = '$id'") or die(mysqli_error());
	$m1 = mysqli_num_rows($sql1);

	

	$sql3 = mysqli_query($conn,"SELECT a.* FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`id_pesan` = '$id' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$m3 = mysqli_num_rows($sql3);

    $tot = $m+$m1;
    $totb = $m2+$m3;

    $pr = ($totb!=0)?($totb/$tot) * 100:0; 
    $hsl = number_format($pr);

	return $hsl;
}
function total_tf_kpr()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'KPR'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_topup_kpr()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`kpr`) as jumlah FROM `tb_kpr` WHERE `status_wawancara` = 'Approve'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    
	return $m['jumlah'] ;
}
function total_tf_sbum()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'sbum'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_topup_retensi()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`retensi`) as jumlah FROM `tb_kpr` WHERE `status_wawancara` = 'Approve'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    
	return $m['jumlah'] ;
}
function total_tf_retensi()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'retensi'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	
	$sql1 = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_dana`") or die(mysqli_error());
	$m1 = mysqli_fetch_assoc($sql1);
    
	return $m['jumlah'] + $m1['jumlah'];
}
function total_topup_sbum()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`sbum`) as jumlah FROM `tb_pesan` WHERE `status` = 'ACC'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    
	return $m['jumlah'] ;
}
function total_tf_brt()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'BRT'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_topup_brt()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_brt` ") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'BRT' AND  b.`jenis` <> 'Lain'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);


	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'BRT'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);
    
    $sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'BRT' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
	$sqllr = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_cair` WHERE `pembayaran` = 'BRT'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
	return $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mln['jumlah'] + $mlr['jumlah'];
}
function total_brt_out()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			WHERE b.`pembayaran` = 'BRT' AND  b.`status` = 'ACC' ") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	
	
	$sqltf = mysqli_query($conn,"SELECT SUM(a.`fee`) as jumlah FROM `tb_detail_pembayaran_kontraktor` as a JOIN `tb_pembayaran_kontraktor` as b ON a.`id_pembayaran` = b.`id` WHERE b.`pembayaran` = 'BRT' ") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlpo = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah 
			FROM `tb_detail_opbame_bayar` as a 
			JOIN `tb_opname_mandor` AS b ON a.`id_opname` = b.`id`
			WHERE a.`jenis` = 'BRT' AND  b.`status` = 'ACC' ") or die(mysqli_error());
	$mpo = mysqli_fetch_assoc($sqlpo);

	$sqlrf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_refund` WHERE `pembayaran` = 'BRT'") or die(mysqli_error());
	$mrf = mysqli_fetch_assoc($sqlrf);
	
	$amt = 0;
	$query = "SELECT * FROM `tb_ca_proyek` WHERE `status` = 'ACC' AND `pembayaran` = 'Lunas' AND `jenis` = 'BRT'";
	$sql_kul = mysqli_query($conn,$query);	
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
		$tot = total_ca_project($obt['id']);
		$amt += $tot;
	}
    
    $sqlrcp = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_ca_proyek` as a 
									JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
									WHERE b.`pembayaran` = 'Hutang' AND a.`pembayaran` = 'BRT' AND  b.`status` = 'ACC'") or die(mysqli_error());
	$mrcp = mysqli_fetch_assoc($sqlrcp);
	
	$amtqa = 0;
	$queryqa = "SELECT * FROM `tb_qa` WHERE `jenis` = 'Lunas' AND `pembayaran` = 'BRT'";
	$sql_kulqa = mysqli_query($conn,$queryqa);	
	while ($obtq = mysqli_fetch_assoc($sql_kulqa)) {	
		$totqa = total_quotation($obtq['id']);
		$amtqa += $totqa;
	}
	$sqlrcp1 = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_pembayaran_qa` WHERE `pembayaran` = 'BRT'") or die(mysqli_error());
	$mrcp1 = mysqli_fetch_assoc($sqlrcp1);
	
    $tf = total_tf_brt();
	return $m['jumlah'] + $tf + $mtf['jumlah'] + $mpo['jumlah'] + $mrf['jumlah'] + $mrcp['jumlah'] + $amt + $amtqa + $mrcp1['jumlah'];
}
function total_tf_bank_all()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Bank' ") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_tf_bank($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Bank' AND `id_bank_from` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_topup_bank_all()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_bank` ") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND  b.`jenis` <> 'Lain'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Bank' ") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);


    $sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND  b.`jenis` = 'Lain' ") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
	
	$sqllr = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_cair` WHERE `pembayaran` = 'Transfer'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
    
	return $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mln['jumlah'] + $mlr['jumlah'];
}
function total_topup_bank($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_bank` WHERE `id_bank` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` <> 'Lain'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Bank' AND `id_bank_to` = '$id'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlpi = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_piutang` as a 
								JOin `tb_piutang` AS b on a.`id_piutang` = b.`id`
								WHERE b.`bank` = '$id'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);

	$sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
	
	$sqllr = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_retensi_cair` as a 
                                                    JOIN `tb_retensi_dana` as b ON a.`id_bank` = b.`id`
                                                    WHERE b.id_bank = '$id'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
    
	return $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mpi['jumlah'] + $mln['jumlah'] + $mlr['jumlah'];
}
function total_bank_out($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`pembayaran` = 'Transfer' AND  b.`status` = 'ACC' AND b.`id_bank` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    $sqltf = mysqli_query($conn,"SELECT SUM(a.`fee`) as jumlah FROM `tb_detail_pembayaran_kontraktor` as a JOIN `tb_pembayaran_kontraktor` as b ON a.`id_pembayaran` = b.`id` WHERE b.`pembayaran` = 'Transfer' AND b.`id_bank` = '$id'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);
	$sqlpi = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_piutang` WHERE `bank` = '$id' AND `status` = 'ACC'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);
	$sqlpo = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah 
			FROM `tb_detail_opbame_bayar` as a 
			JOIN `tb_opname_mandor` AS b ON a.`id_opname` = b.`id`
			WHERE a.`jenis` = 'Transfer' AND  b.`status` = 'ACC' AND a.`id_bank` = '$id'") or die(mysqli_error());
	$mpo = mysqli_fetch_assoc($sqlpo);

	$sqlrf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_refund` WHERE `id_bank` = '$id'") or die(mysqli_error());
	$mrf = mysqli_fetch_assoc($sqlrf);
    
    $sqlrcp = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_ca_proyek` as a 
													JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
													WHERE a.`pembayaran` = 'Transfer' AND b.`pembayaran` = 'Hutang'  AND a.`id_bank` = '$id' AND  b.`status` = 'ACC'") or die(mysqli_error());
	$mrcp = mysqli_fetch_assoc($sqlrcp);
	
	$amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * 
			FROM `tb_detail_ca_proyek` as a 
			JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
			WHERE b.`jenis` = 'Transfer' AND b.`id_bank` = '$id' AND b.`pembayaran` = 'Lunas' AND  b.`status` = 'ACC'") or die(mysqli_error());
    while ($obt = mysqli_fetch_assoc($sql1)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    }
	
	$amtqa = 0;
	$queryqa = "SELECT * FROM `tb_qa` WHERE `jenis` = 'Lunas' AND `pembayaran` = 'Transfer' AND `id_bank` = '$id'";
	$sql_kulqa = mysqli_query($conn,$queryqa);	
	while ($obtqa = mysqli_fetch_assoc($sql_kulqa)) {	
		$totqa = total_quotation($obtqa['id']);
		$amtqa += $totqa;
	}
	
	$sqlrcp1 = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_qa` as a 
													JOIN `tb_qa` AS b ON a.`id_qa` = b.`id`
													WHERE b.`jenis` = 'Hutang' AND a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id'") or die(mysqli_error());
	$mrcp1 = mysqli_fetch_assoc($sqlrcp1);
	
    $tf = total_tf_bank($id);
    
    $sqllr = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_dana` WHERE id_bank = '$id'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
	
	return $m['jumlah'] + $tf + $mtf['jumlah']+ $mpi['jumlah'] + $mpo['jumlah'] + $mrf['jumlah'] + $mrcp['jumlah'] + $amtqa + $amt + $mrcp1['jumlah'] + $mlr['jumlah'] ;
}
function total_tf_cash()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Cash'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}

function total_topup_cash()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_cash`") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	$sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Cash' AND  b.`jenis` <> 'Lain'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Cash'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Cash' AND  b.`jenis` = 'Lain'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
    
	return $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mln['jumlah'];
}
function total_cash_out()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`pembayaran` = 'Cash' AND  b.`status` = 'ACC'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	
	$amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * 
			FROM `tb_detail_ca_proyek` as a 
			JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
			WHERE b.`jenis` = 'Cash' AND b.`pembayaran` = 'Lunas' AND  b.`status` = 'ACC'") or die(mysqli_error());
    while ($obt = mysqli_fetch_assoc($sql1)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    }
	
    $tf = total_tf_cash();
	return $m['jumlah'] + $tf + $amt;
}
function saldo_cash_in($tgl)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
    if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	    $mxa = 0;
	}else{
	    $thn    =date('Y',  $dt);
	    
        $sqlxa = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `saldo_awal_tahun` WHERE `id_bank` = '0' AND `tahun` = '$thn'") or die(mysqli_error());
    	$mx = mysqli_fetch_assoc($sqlxa);
    	$mxa = $mx['jumlah'];
	}
    
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_cash` WHERE MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	
	$sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Cash' AND  b.`jenis` <> 'Lain' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Cash' AND MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Cash' AND  b.`jenis` = 'Lain' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
    
	return $mxa + $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mln['jumlah'];
}
function saldo_cash_out($tgl)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
    if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	}else{
	    $thn    =date('Y',  $dt);
	}
    
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`pembayaran` = 'Cash' AND  b.`status` = 'ACC' AND MONTH(b.`tanggal`) <= '$bln'  AND YEAR(b.`tanggal`) <= '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	
	$amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * 
			FROM `tb_detail_ca_proyek` as a 
			JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
			WHERE b.`jenis` = 'Cash' AND b.`pembayaran` = 'Lunas' AND  b.`status` = 'ACC' AND MONTH(b.`tanggal`) <= '$bln'  AND YEAR(b.`tanggal`) <= '$thn'") or die(mysqli_error());
    while ($obt = mysqli_fetch_assoc($sql1)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    }
    
	$sqlx = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Cash' AND MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'") or die(mysqli_error());
	$mx = mysqli_fetch_assoc($sqlx);
	
    
	return $m['jumlah'] + $tf + $amt + $mx['jumlah'] ;
}
function saldo_bank_in_loop($id,$tgl)
{
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
	
	if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	    $mxa = 0;
	}else{
	    $thn    =date('Y',  $dt);
	    
        $sqlxa = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `saldo_awal_tahun` WHERE `id_bank` = '$id' AND `tahun` = '$thn'") or die(mysqli_error());
    	$mx = mysqli_fetch_assoc($sqlxa);
    	$mxa = $mx['jumlah'];
	}
    
    
    $sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_bank` WHERE `id_bank` = '$id' AND MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` <= 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` <> 'Lain' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Bank' AND `id_bank_to` = '$id' AND MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlpi = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_piutang` as a 
								JOin `tb_piutang` AS b on a.`id_piutang` = b.`id`
								WHERE b.`bank` = '$id' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);

	$sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` = 'Lain' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
	
	$sqllr = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_retensi_cair` as a 
                                                    JOIN `tb_retensi_dana` as b ON a.`id_bank` = b.`id`
                                                    WHERE b.id_bank = '$id' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
    
	return $mxa + $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mpi['jumlah'] + $mln['jumlah'] + $mlr['jumlah'];
}
function saldo_bank_out_loop($id,$tgl)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
    if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	}else{
	    $thn    =date('Y',  $dt);
	}
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`pembayaran` = 'Transfer' AND  b.`status` = 'ACC' AND b.`id_bank` = '$id' AND MONTH(b.`tanggal`) <= '$bln'  AND YEAR(b.`tanggal`) <= '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    $sqltf = mysqli_query($conn,"SELECT SUM(a.`fee`) as jumlah FROM `tb_detail_pembayaran_kontraktor` as a JOIN `tb_pembayaran_kontraktor` as b ON a.`id_pembayaran` = b.`id` WHERE b.`pembayaran` = 'Transfer' AND b.`id_bank` = '$id' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);
	$sqlpi = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_piutang` WHERE `bank` = '$id' AND `status` = 'ACC' AND MONTH(`tanggal`) <= '$bln'  AND YEAR(`tanggal`) <= '$thn'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);
	$sqlpo = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah 
			FROM `tb_detail_opbame_bayar` as a 
			JOIN `tb_opname_mandor` AS b ON a.`id_opname` = b.`id`
			WHERE a.`jenis` = 'Transfer' AND  b.`status` = 'ACC' AND a.`id_bank` = '$id' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mpo = mysqli_fetch_assoc($sqlpo);

	$sqlrf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_refund` WHERE `id_bank` = '$id' AND MONTH(`tanggal`) <= '$bln'  AND YEAR(`tanggal`) <= '$thn'") or die(mysqli_error());
	$mrf = mysqli_fetch_assoc($sqlrf);
    
    $sqlrcp = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_ca_proyek` as a 
													JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
													WHERE a.`pembayaran` = 'Transfer' AND b.`pembayaran` = 'Hutang'  AND a.`id_bank` = '$id' AND  b.`status` = 'ACC' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mrcp = mysqli_fetch_assoc($sqlrcp);
	
	$amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * 
			FROM `tb_detail_ca_proyek` as a 
			JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
			WHERE b.`jenis` = 'Transfer' AND b.`id_bank` = '$id' AND b.`pembayaran` = 'Lunas' AND  b.`status` = 'ACC' AND MONTH(b.`tanggal`) <= '$bln'  AND YEAR(b.`tanggal`) <= '$thn'") or die(mysqli_error());
    while ($obt = mysqli_fetch_assoc($sql1)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    }
	
	$amtqa = 0;
	$queryqa = "SELECT * FROM `tb_qa` WHERE `jenis` = 'Lunas' AND `pembayaran` = 'Transfer' AND `id_bank` = '$id' AND MONTH(`tgl_mulai`) <= '$bln'  AND YEAR(`tgl_mulai`) <= '$thn'";
	$sql_kulqa = mysqli_query($conn,$queryqa);	
	while ($obtqa = mysqli_fetch_assoc($sql_kulqa)) {	
		$totqa = total_quotation($obtqa['id']);
		$amtqa += $totqa;
	}
	
	$sqlrcp1 = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_qa` as a 
													JOIN `tb_qa` AS b ON a.`id_qa` = b.`id`
													WHERE b.`jenis` = 'Hutang' AND a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND MONTH(a.`tanggal`) <= '$bln'  AND YEAR(a.`tanggal`) <= '$thn'") or die(mysqli_error());
	$mrcp1 = mysqli_fetch_assoc($sqlrcp1);
	
    $queryt1 = "SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Bank' AND `id_bank_from` = '$id' AND MONTH(`date`) <= '$bln'  AND YEAR(`date`) <= '$thn'";
	$sql_kult1 = mysqli_query($conn,$queryt1);
	$tfmlr = mysqli_fetch_assoc($sql_kult1);
    
    $sqllr = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_dana` WHERE id_bank = '$id' AND MONTH(`tanggal`) <= '$bln'  AND YEAR(`tanggal`) <= '$thn'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
	
	return $m['jumlah'] + $tfmlr['jumlah']  + $mtf['jumlah']+ $mpi['jumlah'] + $mpo['jumlah'] + $mrf['jumlah'] + $mrcp['jumlah'] + $amtqa + $amt + $mrcp1['jumlah'] + $mlr['jumlah'] ;
}
function saldo_bank_in($id,$tgl)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
    $thn    =date('Y',  $dt);
	if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	}else{
	    $thn    =date('Y',  $dt);
	}
	$sql = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_topup_bank` WHERE `id_bank` = '$id' AND MONTH(`date`) = '$bln'  AND YEAR(`date`) = '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
    $sqlc = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` <> 'Lain' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mc = mysqli_fetch_assoc($sqlc);

	$sqltf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `to` = 'Bank' AND `id_bank_to` = '$id' AND MONTH(`date`) = '$bln'  AND YEAR(`date`) = '$thn'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);

	$sqlpi = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_piutang` as a 
								JOin `tb_piutang` AS b on a.`id_piutang` = b.`id`
								WHERE b.`bank` = '$id' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);

	$sqlln = mysqli_query($conn,"SELECT SUM(b.`jumlah`) as jumlah FROM `tb_bayar_uang_muka` as a JOIN `tb_detail_um` as b ON a.`id` = b.`id_um` WHERE a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND  b.`jenis` = 'Lain' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mln = mysqli_fetch_assoc($sqlln);
	
	$sqllr = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_retensi_cair` as a 
                                                    JOIN `tb_retensi_dana` as b ON a.`id_bank` = b.`id`
                                                    WHERE b.id_bank = '$id' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
    
	return $m['jumlah'] + $mc['jumlah'] + $mtf['jumlah'] + $mpi['jumlah'] + $mln['jumlah'] + $mlr['jumlah'];
}
function saldo_bank_out($id,$tgl)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");
	
	$dt = strtotime($tgl);
	$bln    =date('m', strtotime("-1 month", $dt));
    $thn    =date('Y',  $dt);
    if($bln == "12"){
	    $thn    =date('Y', strtotime("-1 year", $dt));
	}else{
	    $thn    =date('Y',  $dt);
	}
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE b.`pembayaran` = 'Transfer' AND  b.`status` = 'ACC' AND b.`id_bank` = '$id' AND MONTH(b.`tanggal`) = '$bln'  AND YEAR(b.`tanggal`) = '$thn'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    $sqltf = mysqli_query($conn,"SELECT SUM(a.`fee`) as jumlah FROM `tb_detail_pembayaran_kontraktor` as a JOIN `tb_pembayaran_kontraktor` as b ON a.`id_pembayaran` = b.`id` WHERE b.`pembayaran` = 'Transfer' AND b.`id_bank` = '$id' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mtf = mysqli_fetch_assoc($sqltf);
	$sqlpi = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_piutang` WHERE `bank` = '$id' AND `status` = 'ACC' AND MONTH(`tanggal`) = '$bln'  AND YEAR(`tanggal`) = '$thn'") or die(mysqli_error());
	$mpi = mysqli_fetch_assoc($sqlpi);
	$sqlpo = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah 
			FROM `tb_detail_opbame_bayar` as a 
			JOIN `tb_opname_mandor` AS b ON a.`id_opname` = b.`id`
			WHERE a.`jenis` = 'Transfer' AND  b.`status` = 'ACC' AND a.`id_bank` = '$id' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mpo = mysqli_fetch_assoc($sqlpo);

	$sqlrf = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_refund` WHERE `id_bank` = '$id' AND MONTH(`tanggal`) = '$bln'  AND YEAR(`tanggal`) = '$thn'") or die(mysqli_error());
	$mrf = mysqli_fetch_assoc($sqlrf);
    
    $sqlrcp = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_ca_proyek` as a 
													JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
													WHERE a.`pembayaran` = 'Transfer' AND b.`pembayaran` = 'Hutang'  AND a.`id_bank` = '$id' AND  b.`status` = 'ACC' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mrcp = mysqli_fetch_assoc($sqlrcp);
	
	$amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * 
			FROM `tb_detail_ca_proyek` as a 
			JOIN `tb_ca_proyek` AS b ON a.`id_ca` = b.`id`
			WHERE b.`jenis` = 'Transfer' AND b.`id_bank` = '$id' AND b.`pembayaran` = 'Lunas' AND  b.`status` = 'ACC' AND MONTH(b.`tanggal`) = '$bln'  AND YEAR(b.`tanggal`) = '$thn'") or die(mysqli_error());
    while ($obt = mysqli_fetch_assoc($sql1)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    }
	
	$amtqa = 0;
	$queryqa = "SELECT * FROM `tb_qa` WHERE `jenis` = 'Lunas' AND `pembayaran` = 'Transfer' AND `id_bank` = '$id' AND MONTH(`tgl_mulai`) = '$bln'  AND YEAR(`tgl_mulai`) = '$thn'";
	$sql_kulqa = mysqli_query($conn,$queryqa);	
	while ($obtqa = mysqli_fetch_assoc($sql_kulqa)) {	
		$totqa = total_quotation($obtqa['id']);
		$amtqa += $totqa;
	}
	
	$sqlrcp1 = mysqli_query($conn,"SELECT SUM(a.`nominal`) as jumlah FROM `tb_pembayaran_qa` as a 
													JOIN `tb_qa` AS b ON a.`id_qa` = b.`id`
													WHERE b.`jenis` = 'Hutang' AND a.`pembayaran` = 'Transfer' AND a.`id_bank` = '$id' AND MONTH(a.`tanggal`) = '$bln'  AND YEAR(a.`tanggal`) = '$thn'") or die(mysqli_error());
	$mrcp1 = mysqli_fetch_assoc($sqlrcp1);
	
    $queryt1 = "SELECT SUM(`nominal`) as jumlah FROM `tb_transfer_saldo` WHERE `dari` = 'Bank' AND `id_bank_from` = '$id' AND MONTH(`date`) = '$bln'  AND YEAR(`date`) = '$thn'";
	$sql_kult1 = mysqli_query($conn,$queryt1);
	$tfmlr = mysqli_fetch_assoc($sql_kult1);
    
    $sqllr = mysqli_query($conn,"SELECT SUM(`nominal`) as jumlah FROM `tb_retensi_dana` WHERE id_bank = '$id' AND MONTH(`tanggal`) = '$bln'  AND YEAR(`tanggal`) = '$thn'") or die(mysqli_error());
	$mlr = mysqli_fetch_assoc($sqllr);
	
	return $m['jumlah'] + $tfmlr['jumlah']  + $mtf['jumlah']+ $mpi['jumlah'] + $mpo['jumlah'] + $mrf['jumlah'] + $mrcp['jumlah'] + $amtqa + $amt + $mrcp1['jumlah'] + $mlr['jumlah'] ;
}

function total_ca_project($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

    $amt = 0;

    	$query = "SELECT * FROM `tb_detail_ca_proyek` WHERE `id_ca` = '$id'  ORDER BY id asc";
    	$sql_kul = mysqli_query($conn,$query);	
    	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
    		$tot = $obt['qty'] * $obt['harga'];
    		$amt += $tot;
    	}
	return $amt;
}
function biaya_rincian_proyek($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(a.`harga`) as jumlah 
			FROM `tb_detail_ca_op` as a 
			JOIN `tb_ca_op` AS b ON a.`id_ca` = b.`id`
			JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
			WHERE a.`id_item` = '$id' AND  b.`status` = 'ACC'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
    
	return $m['jumlah'];
}
function total_rumah_spk($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

	$amt = 0;
	$query = "SELECT * FROM `tb_detail_pembayaran_kontraktor` WHERE `id_rumah` = '$id'  ORDER BY id asc";
	$sql_kul = mysqli_query($conn,$query);	
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
		$tot = $obt['fee'];
		$amt += $tot;
	}

	return $amt;
}
function total_bayar_spk($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	

    $amt = 0;
	$sql1 = mysqli_query($conn,"SELECT * FROM `tb_pembayaran_kontraktor` WHERE `id_spk` = '$id'") or die(mysqli_error());
	while ($m1 = mysqli_fetch_assoc($sql1)){

    	$query = "SELECT * FROM `tb_detail_pembayaran_kontraktor` WHERE `id_pembayaran` = '$m1[id]'  ORDER BY id asc";
    	$sql_kul = mysqli_query($conn,$query);	
    	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
    		$tot = $obt['fee'];
    		$amt += $tot;
    	}
	}
	return $amt;
}
function pesanan($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM `tb_pesan` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	$jm = mysqli_num_rows($sql);
	if($jm == 0){
	    $sqlm = mysqli_query($conn,"SELECT * FROM `tb_pesan_history` WHERE `id` = '$id'") or die(mysqli_error());
	    $m1 = mysqli_fetch_assoc($sqlm);
	    $nama = $m1['nama_pesan'];
	    $rumah = rumah($m1['id_rumah']);
	    $no = $m1['kode_pesan'];
	}else{
	    $nama = $m['nama_pesan'];
	    $rumah = rumah($m['id_rumah']);
	    $no = $m['kode_pesan'];
	}
	

	return $nama.' - '.$rumah;
}
function pesanan_his($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT nama_pesan FROM `tb_pesan_history` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama_pesan'];
}
function total_ruamh($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT COUNT(`marketing`) as total FROM `tb_pesan` WHERE `status` ='ACC' AND `marketing` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['total'];
}

function total_jual_bersih($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$amt=0;
	$query = "SELECT * FROM `tb_pesan` WHERE `marketing` = '$id' AND `status` ='ACC'";
	$sql_kul = mysqli_query($conn,$query);	
	$i=1;
	while ($m = mysqli_fetch_assoc($sql_kul)) {	
	    $c2 = "SELECT * FROM `tb_detail_cicilan` WHERE `pembayaran` = 'Booking Kavling' AND `kode_pesanan` = '$m[id]'";
  		$d2 = mysqli_query($conn,$c2);	
  		$d = mysqli_fetch_assoc($d2);
  		
	    $c1 = "SELECT * FROM `tb_fee_marketing` WHERE `id_pegawai` = '$id' AND `id_pesan` = '$m[id]'";
  		$d1 = mysqli_query($conn,$c1);						  
  		$je1 = mysqli_num_rows($d1);
  		if ($je1==0) {
  			$notaris = "0";
  			$real = "0";
  		}else{
  			$c = mysqli_fetch_assoc($d1);
  			$notaris = $c['notaris'];
  			$real = $c['real'];
  		}
  		
		$kpr = $m['kpr'];
		$rcair = $kpr - $notaris - $real;
		$tot = (2.5 / 100)*$rcair;
		$htot = $tot + $d['jumlah'];
		$amt += $htot;
	}

	return $amt;
}

function bayar_kpr($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT pembayaran FROM `tb_detail_cicilan` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['pembayaran'];
}
function bayar_cash($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT pembayaran FROM `tb_detail_cash` WHERE `id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['pembayaran'];
}

function total_spk($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$amt = 0;
	$query = "SELECT * FROM `tb_detail_spk` WHERE `id_spk` = '$id'  ORDER BY id asc";
	$sql_kul = mysqli_query($conn,$query);	
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
		$tot = $obt['fee'];
		$amt += $tot;
	}

	return $amt;
}
function total_spk_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT id_rumah FROM `tb_detail_spk` WHERE `id_spk` = '$id'") or die(mysqli_error());
	$m = mysqli_num_rows($sql);

	return $m;
}
function total_order_bangun_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT id_rumah FROM `tb_ob_unit` WHERE `id_ob` = '$id'") or die(mysqli_error());
	$m = mysqli_num_rows($sql);

	return $m;
}
function total_pembayaran_kontraktor_unit($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT id_rumah FROM `tb_detail_pembayaran_kontraktor` WHERE `id_pembayaran` = '$id'") or die(mysqli_error());
	$m = mysqli_num_rows($sql);

	return $m;
}
function total_pembayaran_kontraktor($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$amt = 0;
	$query = "SELECT * FROM `tb_detail_pembayaran_kontraktor` WHERE `id_pembayaran` = '$id'  ORDER BY id asc";
	$sql_kul = mysqli_query($conn,$query);	
	while ($obt = mysqli_fetch_assoc($sql_kul)) {	
		$tot =  $obt['fee'];
		$amt += $tot;
	}

	return $amt;
}
function cluster_rumah($id)
{
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT  b.`id_cluster`
							FROM `tb_rumah` as a 
							JOIN `tb_blok` as b ON a.`id_blok` = b.`id` 
							WHERE a.`id` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['id_cluster'];

}
function total_profiling($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT SUM(`jumlah`) as fee FROM `tb_profiling` WHERE `id_rumah` = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	$id_cluster = cluster_rumah($id);
	$tot_unit = total_perunit($id_cluster);

	return $m['fee']+$tot_unit;
}
function total_biaya_kat($id,$idcluster)
{   
    $conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	 
    
    $querym = "SELECT * FROM `tb_item_biaya_proyek` WHERE `id_kategori` = '$id' ORDER BY `id` ASC ";
	$sql_kulm = mysqli_query($conn,$querym);	
	$jml = 0;
	while ($m = mysqli_fetch_assoc($sql_kulm)) {
	    $queryc = mysqli_query($conn,"SELECT * FROM tb_rincian_proyek WHERE id_item = '$m[id]' AND id_cluster = '$idcluster'");
		$c = mysqli_fetch_assoc($queryc);
		$jml += $c['total'];
	}	

    return  $jml;
}
function total_ca_op($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
    $amt = 0;
	$querym = "SELECT a.*
                FROM `tb_detail_ca_op` as a  
                JOIN `tb_kategori_ca_op` as c ON a.`id_kategori` = c.`id`
                WHERE a.`id_ca` = '$id' ";
	$sql_kulm = mysqli_query($conn,$querym);	
	$jml = 0;
	while ($obt = mysqli_fetch_assoc($sql_kulm)) {
	    $tot =  $obt['harga'];
	    $amt += $tot;
	}	

    return  $amt;
}
function total_ca_proyek($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
    $amt = 0;
	$querym = "SELECT * FROM `tb_detail_ca_proyek` WHERE `id_ca` = '$id' ";
	$sql_kulm = mysqli_query($conn,$querym);	
	$jml = 0;
	while ($obt = mysqli_fetch_assoc($sql_kulm)) {
	    $tot =  $obt['qty'] * $obt['harga'];
	    $amt += $tot;
	}	

    return  $amt;
}
function total_quotation($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
    $amt = 0;
	$querym = "SELECT * FROM `tb_detail_qa` WHERE `id_qa` = '$id' ";
	$sql_kulm = mysqli_query($conn,$querym);	
	$jml = 0;
	while ($obt = mysqli_fetch_assoc($sql_kulm)) {
	    $tot =  $obt['qty'] * $obt['harga'];
	    $amt += $tot;
	}	

    return  $amt;
}
function total_penjualan()
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$query = "SELECT * FROM `tb_pesan` WHERE `status` = 'ACC' ";
	$sql_kul = mysqli_query($conn,$query);	
	$sum=0;
	while ($tra = mysqli_fetch_assoc($sql_kul)) {
	    if($tra['pembayaran']=="Kredit"){
	        
    	    $c1 = "SELECT `status_wawancara` FROM `tb_kpr` WHERE `id_pesan` = '$tra[id]'";
      		$d1 = mysqli_query($conn,$c1);						  
      		$c = mysqli_fetch_assoc($d1);
      		if($c['status_wawancara']=="Approve"){
      		    $totkel = $tra['kelebihan']*$tra['harga_lebih'];
            	$totharju = $tra['harga_jual']+$totkel;
            	$tothad = $totharju-$tra['diskon'];
            	$sum += $tothad;
      		}
	    }else{
	        $totkel = $tra['kelebihan']*$tra['harga_lebih'];
            	$totharju = $tra['harga_jual']+$totkel;
            	$tothad = $totharju-$tra['diskon'];
            	$sum += $tothad;
	    }
		

	}

	return $sum;
}
function akun($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT *FROM `tb_akun`
						WHERE id = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return  $m['pos_akun'].$m['kode_akun']." - ".$m['nama_akun'];
}
function nama_petugas($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	


	$sql = mysqli_query($conn,"SELECT * FROM users WHERE user_id = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);
	$sqlgrpx = mysqli_query($conn,"SELECT * FROM `tb_group` WHERE `kode_group` = '$m[level]'");
	$grpx = mysqli_fetch_assoc($sqlgrpx);
	$sql_akunx = mysqli_query($conn,"SELECT `pegawai_id` as id,`pegawai_nama` as nama FROM `pegawai` WHERE pegawai_id = '$m[nama_lengkap]'");
	$gx = mysqli_fetch_assoc($sql_akunx);
	

	return $gx['nama'];
}
function group($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT nama FROM tb_group
						WHERE kode_group = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function nama_toko($id)
{	
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
	$sql = mysqli_query($conn,"SELECT * FROM tb_identitas WHERE id = '$id'") or die(mysqli_error());
	$m = mysqli_fetch_assoc($sql);

	return $m['nama'];
}
function get_client_ip() {
    $ipaddress = '';
    if (getenv('HTTP_CLIENT_IP'))
        $ipaddress = getenv('HTTP_CLIENT_IP');
    else if(getenv('HTTP_X_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_X_FORWARDED_FOR');
    else if(getenv('HTTP_X_FORWARDED'))
        $ipaddress = getenv('HTTP_X_FORWARDED');
    else if(getenv('HTTP_FORWARDED_FOR'))
        $ipaddress = getenv('HTTP_FORWARDED_FOR');
    else if(getenv('HTTP_FORWARDED'))
       $ipaddress = getenv('HTTP_FORWARDED');
    else if(getenv('REMOTE_ADDR'))
        $ipaddress = getenv('REMOTE_ADDR');
    else
        $ipaddress = 'IP tidak dikenali';
    return $ipaddress;
}
 
//menampilkan ip address menggunakan function $_SERVER
function get_client_ip_2() {
    $ipaddress = '';
    if (isset($_SERVER['HTTP_CLIENT_IP']))
        $ipaddress = $_SERVER['HTTP_CLIENT_IP'];
    else if(isset($_SERVER['HTTP_X_FORWARDED_FOR']))
        $ipaddress = $_SERVER['HTTP_X_FORWARDED_FOR'];
    else if(isset($_SERVER['HTTP_X_FORWARDED']))
        $ipaddress = $_SERVER['HTTP_X_FORWARDED'];
    else if(isset($_SERVER['HTTP_FORWARDED_FOR']))
        $ipaddress = $_SERVER['HTTP_FORWARDED_FOR'];
    else if(isset($_SERVER['HTTP_FORWARDED']))
        $ipaddress = $_SERVER['HTTP_FORWARDED'];
    else if(isset($_SERVER['REMOTE_ADDR']))
        $ipaddress = $_SERVER['REMOTE_ADDR'];
    else
        $ipaddress = 'IP tidak dikenali';
    return $ipaddress;
}
 
 //menampilkan jenis web browser pengunjung
function get_client_browser() {
    $browser = '';
    if(strpos($_SERVER['HTTP_USER_AGENT'], 'Netscape'))
        $browser = 'Netscape';
    else if (strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox'))
        $browser = 'Firefox';
    else if (strpos($_SERVER['HTTP_USER_AGENT'], 'Chrome'))
        $browser = 'Chrome';
    else if (strpos($_SERVER['HTTP_USER_AGENT'], 'Opera'))
        $browser = 'Opera';
    else if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE'))
        $browser = 'Internet Explorer';
    else
        $browser = 'Other';
    return $browser;
}
function log_insert($kode,$table,$ket,$petugas) {
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
  	$info = "IP :". get_client_ip()."<br> Browser : ".get_client_browser()."<br> Sistem Operasi : ".$_SERVER['HTTP_USER_AGENT'];
  	mysqli_query($conn,"INSERT INTO `tb_log`(`kode`, `table`, `deskripsi`, `aksi`, `petugas`,`info`) VALUES ('$kode','$table','$ket','Insert','$petugas','$info')") or die(mysqli_error());

}
function log_update($kode,$table,$ket,$petugas) {
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
  	$info = "IP :". get_client_ip()."<br> Browser : ".get_client_browser()."<br> Sistem Operasi : ".$_SERVER['HTTP_USER_AGENT'];
  	mysqli_query($conn,"INSERT INTO `tb_log`(`kode`, `table`, `deskripsi`, `aksi`, `petugas`,`info`) VALUES ('$kode','$table','$ket','Update','$petugas','$info')") or die(mysqli_error());

}
function log_delete($kode,$table,$ket,$petugas) {
	$conn = mysqli_connect("localhost", "patraray_siteplan", "fQ}w}(9@6Uz2ADB=", "patraray_siteplan");	
  	$info = "IP :". get_client_ip()."<br> Browser : ".get_client_browser()."<br> Sistem Operasi : ".$_SERVER['HTTP_USER_AGENT'];
  	mysqli_query($conn,"INSERT INTO `tb_log`(`kode`, `table`, `deskripsi`, `aksi`, `petugas`,`info`) VALUES ('$kode','$table','$ket','Delete','$petugas','$info')") or die(mysqli_error());

}

function seo_title($s){
  $c = array (' ');
  $d = array ('-','/','\\',',','.','#',':',';','\'','"','[',']','{','}',')','(','|','`','~','!','@','%','$','^','&','*','=','?','+');
  $s = str_replace($d, '', $s);
  $s = strtolower(str_replace($c, '-', $s));
  return $s;
}

function filterurl($s){
  $c = array (' ');
  $d = array ('?sw=error','?sw=success','?sw=success?sw=success','?sw=error','?sw=error','?sw=success?sw=error','?sw=error?sw=success');
  $s = str_replace($d, '', $s);
  //$s = strtolower(str_replace($c, '-', $s));
  return $s;
}

function UploadImage($fupload_name = null, $folder = null, array $quality = NULL){
  /**
   * Upload image and compress them.
   * @since 1.0.0
   */
  // debug
  $debug = false;

  $vdir_upload = "../../../content/upload/$folder/";

  $vfile_upload = $vdir_upload . $fupload_name;

  // set
  //set_error_handler("custom_error");

  // check directory writeable
  if(!is_writeable(root_uri().str_replace('../../../','', $vdir_upload))){
    // // set error return
    // $return = [
    //   "error" => [
    //     "code"    => "permission_denied",
    //     "message" => __('Failed while uploading image, permission denied.')
    //   ]
    // ];
  }else{
    // move uploaded
    if(!@move_uploaded_file($_FILES["fupload"]["tmp_name"], $vfile_upload)){
      // // give error space dude
      // $error_msg = __('Failed while processing image.');
      // // set error return
      // $return = [
      //   "error" => [
      //     "code"    => "failed_moving_image",
      //     "message" => __('Failed while moving image to specifict directory.')
      //   ]
      // ];
      // new result function
      /*
      result(array(
        'status' => 'error',
        'title' => lg('Error'),
        'msg' => lg('Failed to processing uploaded image. Contact adminisitrator.'),
        'msg_ses' => 'failed_processing',
        'red' => ''
      ), 'j');*/
    }else{
      // compress to all size
      if(empty($quality)){
        // compress
        // 1280px ~ 90%
        resize(1280,$vdir_upload . "hd-" . $fupload_name,$vfile_upload, 90);
        // 300px ~ 80%
        resize(300,$vdir_upload . "md-" . $fupload_name,$vfile_upload, 80);
        // 100px  ~ 40%
        resize(100,$vdir_upload . "sm-" . $fupload_name,$vfile_upload, 40);
        // 50px ~ 30%
        resize(50,$vdir_upload . "xs-" . $fupload_name,$vfile_upload, 30);
      }else{
        // compress to spesific size only
        if(in_array('hd', $quality)){
          // 1280px ~ 90%
          resize(1280,$vdir_upload . "hd-" . $fupload_name,$vfile_upload, 90);
        }
        if(in_array('md', $quality)){
          // 300px ~ 80%
          resize(300,$vdir_upload . "md-" . $fupload_name,$vfile_upload, 80);
        }
        if(in_array('sm', $quality)){
          // 100px  ~ 40%
          resize(100,$vdir_upload . "sm-" . $fupload_name,$vfile_upload, 40);
        }
        if(in_array('xs', $quality)){
          // 50px ~ 30%
          resize(50,$vdir_upload . "xs-" . $fupload_name,$vfile_upload, 30);
        }
      }
    }
  }

  if($debug){
    var_dump(@$return['error']['message'].' '.sprintf(__('Filename: %s, Uplaod Path: %s,  Module: %s, Compression: %s, Uploaded: %s'), @$fupload_name, @$vfile_upload, @$mod, var_dump(@$quality), var_dump(@$ok)));
    exit;
  }else{
    return $return;
  }
}

 function getExtension($str) {
         $i = strrpos($str,".");
         if (!$i) { return ""; }
         $l = strlen($str) - $i;
         $ext = substr($str,$i+1,$l);
         return $ext;
 }
 
function resize($newWidth, $targetFile, $originalFile, $quality) {
    $info = getimagesize($originalFile);
    $mime = $info['mime'];
    switch ($mime) {
            case 'image/jpeg':
                    $image_create_func = 'imagecreatefromjpeg';
                    $image_save_func = 'imagejpeg';
                    $new_image_ext = 'jpg';
                    break;

            case 'image/png':
                    $image_create_func = 'imagecreatefrompng';
                    $image_save_func = 'imagepng';
                    $new_image_ext = 'png';
                    break;

            case 'image/gif':
                    $image_create_func = 'imagecreatefromgif';
                    $image_save_func = 'imagegif';
                    $new_image_ext = 'gif';
                    break;

            default:
                    throw Exception('Unknown image type.');
    }

    $img = $image_create_func($originalFile);
    list($width, $height) = getimagesize($originalFile);

    $newHeight = ($height / $width) * $newWidth;
    $tmp = imagecreatetruecolor($newWidth, $newHeight);
    imagecopyresampled($tmp, $img, 0, 0, 0, 0, $newWidth, $newHeight, $width, $height);

    if (file_exists($targetFile)) {
            unlink($targetFile);
    }
    $image_save_func($tmp, "$targetFile", $quality);
}
?>