$strKey = substr_replace($strKey, "", -1);
$strVal = substr_replace($strVal, "", -2);
$query = "insert into debitur\r\n ({$strKey},usercreate,userupdate,action,tgl_update) \r\n values({$strVal},'{$userCreate}','{$userCreate}','{$datenow}','{$datenow}');";
$buf = cleanstr($db_function->exec($query));
if ($buf == "") {
//--insert trail
$strKey = "";
$strVal = "'";
$frmTrail = $_POST['frm'];
$nonTrail = array('usercreate', 'action');
foreach ($nonTrail as $row) {
unset($frmTrail[$row]);
}
foreach ($frmTrail as $key => $val) {
if (isDate($val)) {
$val = balikTgl($val);
}
$strKey .= $key . ",";
$strVal .= trim($val) . "','";
}
$strKey = substr_replace($strKey, "", -1);
$strVal = substr_replace($strVal, "", -2);
$query = "insert into debitur_trail\r\n (no_trail,{$strKey},userupdate,tgl_update) \r\n values('1',{$strVal},'{$userCreate}',now());";
$buf = cleanstr($db_function->exec($query));
if ($buf != "") {
cleanstr($db_function->exec("delete from debitur where noaplikasi='" . $_POST['frm']['noaplikasi'] . "'"));
}
}
//------------
if ($buf != "") {
array_push($pesanError, $buf);
}
$row++;
foreach ($dataArray as $data) {
foreach ($judulArray as $col => $judul) {
$kata = $data[$col];
if (strlen($kata) > 1000) {
$kata = substr($kata, 0, 1000);
}
xlsWriteLabel($row, $col, $kata);
}
$row++;
}
//insert trail
$judulArrayTrail = array("LNC", "NOAPLIKASI", "NAMADEBITUR", "TEMPATLAHIR", "TGLLAHIR", "CIF", "no_rekg_pinjaman", "afiliasi", "instansi", "produk", "maksimum_kredit", "no_pk", "tgl_pk", "jkw_kredit", "fixed_rate", "tgl_jt_pk", "tgl_jt_fixed_rate", "lokasi_dokumen_asli", "amplop_asli", "amplopasli", "lokasi_dokumen_copy", "amplop_copy", "amplopcopy", "jaminan", "jml_jaminan", "jenis_surat_tanah", "alamat_collateral", "luas_tanah", "tgl_jt_surat_tanah", "jenis_pengikatan", "nilai_ht", "jkw_covernote", "notaris", "appraisal", "no_ajb", "no_surat_tanah", "collateral_zipcode", "luas_bangunan", "nilai_taksasi", "harga_tanah", "harga_bangunan", "harga_tanah_imb", "harga_bangunan_imb", "no_pengikatan", "tgl_covernote", "tgl_jt_covernote", "developer", "skim_pks", "no_imb", "status_imb", "nama_perumahan", "asuransi_jiwa", "no_polis_ass_jiwa", "premi_jiwa", "nilai_pertanggungan_ass_jiwa", "tgl_ass_jiwa", "tgl_jt_ass_jiwa", "asuransi_kerugian", "no_polis_ass_kerugian", "premi_kerugian", "nilai_pertanggungan_ass_kerugian", "tgl_ass_kerugian", "tgl_jt_ass_kerugian", "jenis_kendaraan", "no_bpkb", "no_rangka", "nama_dealer", "merk", "no_mesin", "no_polisi", "status_rekg", "tgl_pelunasan", "memo", "skdr", "siup", "tdp", "others", "serah", "kendala", "tgl_update", "bunga", "program", "agama", "npwp", "kelamin", "tgl_imb", "penilai", "tgl_taksasi", "tinggal", "cabang", "no_ktp", "ibu_kandung", "jabatan", "memo_appraisal", "plafond_dimohon", "nama_emergency", "telp_emergency", "alamat_kantor", "hubungan", "progress", "sales", "hp_sales", "kjpp", "status", "tgl_update_app", "tgl_update_los", "tgl_update_asc", "skim_pencairan", "input_date", "no_covernote", "no_covernote_n", "no_pengikatan_n", "tgl_penyerahan_berkas", "proses_pengikatan", "jenis_sertifikat", "jenis_proyek", "kategori_proyek", "total_unitdibangun", "penguasaan_sertifikat", "no_rek_escrow", "cair_tahap_fondasi", "tgl_cair_tahap_fondasi", "ket_cair_tahap_fondasi", "cair_tahap_topping", "tgl_cair_tahap_topping", "ket_cair_tahap_topping", "cair_tahap_bast", "tgl_cair_tahap_bast", "ket_cair_tahap_bast", "cair_tahap_dok", "tgl_cair_tahap_dok", "ket_cair_tahap_dok", "proses_agunan", "no_polis_ass_kerugian_n", "berkas_asuransi_kerugian", "no_polis_ass_jiwa_n", "berkas_assuransi_jiwa", "no_jaminan_fleksi", "no_jaminan_fleksi_n", "jns_jaminan_fleksi", "srt_pernyataan_fleksi", "no_bpkb_n", "tgl_serah", "pelunasan_penerima", "pelunasan_keterangan", "siup_n", "tdp_n", "no_pks", "tgl_pengikatan", "others_n", "kjpp_flag", "no_ajb_n", "jml_jaminan_n", "userupdate", "insertfrom", "no_trail");
$sql = "select LNC,NOAPLIKASI,NAMADEBITUR,TEMPATLAHIR,TGLLAHIR,CIF,no_rekg_pinjaman,afiliasi,instansi,produk,maksimum_kredit,no_pk,tgl_pk,jkw_kredit,fixed_rate,tgl_jt_pk,tgl_jt_fixed_rate,lokasi_dokumen_asli,amplop_asli,amplopasli,lokasi_dokumen_copy,amplop_copy,amplopcopy,jaminan,jml_jaminan,jenis_surat_tanah,alamat_collateral,luas_tanah,tgl_jt_surat_tanah,jenis_pengikatan,nilai_ht,jkw_covernote,notaris,appraisal,no_ajb,no_surat_tanah,collateral_zipcode,luas_bangunan,nilai_taksasi,harga_tanah,harga_bangunan,harga_tanah_imb,harga_bangunan_imb,no_pengikatan,tgl_covernote,tgl_jt_covernote,developer,skim_pks,no_imb,status_imb,nama_perumahan,asuransi_jiwa,no_polis_ass_jiwa,premi_jiwa,nilai_pertanggungan_ass_jiwa,tgl_ass_jiwa,tgl_jt_ass_jiwa,asuransi_kerugian,no_polis_ass_kerugian,premi_kerugian,nilai_pertanggungan_ass_kerugian,tgl_ass_kerugian,tgl_jt_ass_kerugian,jenis_kendaraan,no_bpkb,no_rangka,nama_dealer,merk,no_mesin,no_polisi,status_rekg,tgl_pelunasan,memo,skdr,siup,tdp,others,serah,kendala,tgl_update,bunga,program,agama,npwp,kelamin,tgl_imb,penilai,tgl_taksasi,tinggal,cabang,no_ktp,ibu_kandung,jabatan,memo_appraisal,plafond_dimohon,nama_emergency,telp_emergency,alamat_kantor,hubungan,progress,sales,hp_sales,kjpp,status,tgl_update_app,tgl_update_los,tgl_update_asc,skim_pencairan,input_date,no_covernote,no_covernote_n,no_pengikatan_n,tgl_penyerahan_berkas,proses_pengikatan,jenis_sertifikat,jenis_proyek,kategori_proyek,total_unitdibangun,penguasaan_sertifikat,no_rek_escrow,cair_tahap_fondasi,tgl_cair_tahap_fondasi,ket_cair_tahap_fondasi,cair_tahap_topping,tgl_cair_tahap_topping,ket_cair_tahap_topping,cair_tahap_bast,tgl_cair_tahap_bast,ket_cair_tahap_bast,cair_tahap_dok,tgl_cair_tahap_dok,ket_cair_tahap_dok,proses_agunan,no_polis_ass_kerugian_n,berkas_asuransi_kerugian,no_polis_ass_jiwa_n,berkas_assuransi_jiwa,no_jaminan_fleksi,no_jaminan_fleksi_n,jns_jaminan_fleksi,srt_pernyataan_fleksi,no_bpkb_n,tgl_serah,pelunasan_penerima,pelunasan_keterangan,siup_n,tdp_n,no_pks,tgl_pengikatan,others_n,kjpp_flag,no_ajb_n,jml_jaminan_n,userupdate,insertfrom,no_trail from debitur_trail ";
$sql .= "where tgl_update>= '" . balikTgl($_POST["frm"]["tgl_awal"]) . " 00:00:00' and tgl_update<='" . balikTgl($_POST["frm"]["tgl_akhir"]) . " 23:59:59'";
$dataArray = $db_function->selectAllRows($sql);
$row++;
xlsWriteLabel($row, 0, "[debitur_trail]");
$row++;
foreach ($judulArrayTrail as $col => $data) {
xlsWriteLabel($row, $col, $data);
}
$row++;
foreach ($dataArray as $data) {
foreach ($judulArrayTrail as $col => $judul) {
$kata = $data[$col];
if (strlen($kata) > 1000) {
$kata = substr($kata, 0, 1000);
}
xlsWriteLabel($row, $col, $kata);
</td>
</tr>
<tr>
<td width='100px'>user create</td>
<td><?php
echo $usersCreate['NPP'] . ", " . $usersCreate['NAMA'];
?>
</td>
</tr>
<tr>
<td >date create</td>
<td><?php
$buf = explode(" ", $trailSekarang['tgl_update']);
if (isset($buf[1])) {
echo balikTgl($buf[0]) . " " . $buf[1];
}
?>
</td>
</tr>
</table>
<?php
echo $messageBox;
?>
<?php
if (isset($_SESSION['colateral']['message'])) {
echo $_SESSION['colateral']['message'];
unset($_SESSION['colateral']['message']);
}
$no_trail = $_GET['no_trail'];
$_POST['frm']['no_trail'] = $no_trail;
$trailSekarang;
$trailSebelum;
$buf = $db_function->selectOneRows("select * from debitur_trail where no_rekg_pinjaman='" . $_GET['id'] . "' and no_trail='" . $_GET['no_trail'] . "'");
foreach ($buf as $key => $val) {
if (!is_int($key)) {
$trailSekarang[strtolower($key)] = isDateDB($val) ? balikTgl($val) : $val;
}
}
$usersCreate = $db_function->selectOneRows("select * from pegawai where npp='" . $trailSekarang['userupdate'] . "'");
if ($_GET['no_trail'] > 1) {
$buf = $db_function->selectOneRows("select * from debitur_trail where no_rekg_pinjaman='" . $_GET['id'] . "' and no_trail='" . ($_GET['no_trail'] - 1) . "'");
foreach ($buf as $key => $val) {
if (!is_int($key)) {
$trailSebelum[strtolower($key)] = isDateDB($val) ? balikTgl($val) : $val;
}
}
}
$cekTrail = new CekTrail($trailSekarang, $trailSebelum);
$buf = $db_function->selectAllRows("select distinct no_trail from debitur_trail where no_rekg_pinjaman='" . $_GET['id'] . "' order by no_trail asc");
$ListNoTrail = array();
foreach ($buf as $row) {
$ListNoTrail[$row['no_trail']] = $row['no_trail'];
}
$produk_nm = trim(strtoupper($_POST['frm']["produk"]));
//echo $produk_nm;exit;
if (in_array($produk_nm, array("BNI GRIYA", "BNI OTO", "BNI GRIYA MULTIGUNA", "BNI FLEKSI", "BNI CERDAS", "BNI WIRAUSAHA", "UMG"))) {
// kalau pilihan nya ada didatabase baru pakek kondisi
//cuma Griya/Multiguna/BWU yg bisa
if (in_array($produk_nm, array("BNI GRIYA", "BNI GRIYA MULTIGUNA", "BNI WIRAUSAHA"))) {
// echo "test->".$sqlAJB;exit;
$countBPKB = pecahData($db_function->selectAllRows($sqlBPKB));
$countAJB = pecahData($db_function->selectAllRows($sqlAJB));
$countSHT = pecahData($db_function->selectAllRows($sqlSHT));
$countAssJiwa = pecahData($db_function->selectAllRows($sqlPolisAssJiwa));
$countKerugian = pecahData($db_function->selectAllRows($sqlPolisAssRugi));
$countTotalDebitur = pecahData($db_function->selectAllRows($sqlTotalDebitur));
// print_r($dataAJB);
$showtable = true;
}
} else {
if ($_GET['jns_pencarian'] == "point") {
$data = $db_function->selectAllRows("select distinct tanggal from summery_pending order by tanggal desc");
if (!empty($data)) {
foreach ($data as $row) {
$ddl_tglpoint[$row['tanggal']] = balikTgl($row['tanggal']);
}
}
$tgl_point = "";
if (isset($_GET['tgl_point'])) {
$tgl_point = $_GET['tgl_point'];
$setTgl = $tgl_point;
$_POST['frm']['tgl_point'] = $tgl_point;
$dataCount = array();
$data = $db_function->selectAllRows("select * from summery_pending where tanggal='" . $tgl_point . "'");
if (!empty($data)) {
foreach ($data as $row) {
$dataCount[$row['jenis']][strtoupper($row['lnc'])] = $row['jumlah'];
}
}
$countBPKB = $dataCount['bpkb'];
if ($submn == "input") {
$cssInput = $cssTabActive;
$cssEdit = "";
} else {
$cssEdit = $cssTabActive;
$cssInput = "";
}
$db_function = new db_function();
$dataTBL = array();
$listLNC = array();
$listLNC[''] = "semua";
$data = $db_function->selectAllRows("select singkatan from master_cab order by singkatan asc");
foreach ($data as $row) {
$listLNC[$row["singkatan"]] = $row["singkatan"];
}
if (!empty($_POST)) {
$tgl1 = balikTgl($_POST['frm']['tgl1']);
$tgl2 = balikTgl($_POST['frm']['tgl2']);
$sql = "select lnc,npp,nama,jml from pegawai right join\n (select lnc,userupdate,count(no_rekg_pinjaman) jml from debitur_trail \n where 1=1 :whereLNC: and :whereInput: and tgl_update>'{$tgl1}' and tgl_update<'{$tgl2} 23:59:59'\n group by userupdate,lnc) sumjml on pegawai.npp=sumjml.userupdate order by lnc,npp";
if ($_POST['frm']['lnc'] == "") {
$sql = str_replace(":whereLNC:", "", $sql);
} else {
$sql = str_replace(":whereLNC:", "and lnc='" . $_POST['frm']['lnc'] . "'", $sql);
}
if ($submn == "input") {
$sql = str_replace(":whereInput:", "no_trail=1", $sql);
} else {
$sql = str_replace(":whereInput:", "no_trail<>1", $sql);
}
$dataTBL = $db_function->selectAllRows($sql);
}
<th width="110px" style="text-align: center">JUMLAH DEBITUR</th>
</tr>
</thead>
<?php
$row = 1;
if ($showtable) {
?>
<tbody>
<?php
if (!$_GET['jns_pencarian'] != "point") {
unset($_SESSION['colateral']['summery_pending']);
}
$tgl = date("Y-m-d");
if ($_GET['jns_pencarian'] == "tgl") {
$tgl = balikTgl($_POST['frm']['tgl_update']);
}
$sumBPKB = 0;
$sumAJB = 0;
$sumSHT = 0;
$sumJIWA = 0;
$sumRUGI;
$sumTOTAL = 0;
$sumDEBITUR = 0;
foreach ($dataLNC as $lnc) {
$total = intval($countBPKB[$lnc['singkatan']]) + intval($countAJB[$lnc['singkatan']]) + intval($countSHT[$lnc['singkatan']]) + intval($countAssJiwa[$lnc['singkatan']]) + intval($countKerugian[$lnc['singkatan']]);
$sumBPKB += $countBPKB[$lnc['singkatan']];
$sumAJB += $countAJB[$lnc['singkatan']];
$sumSHT += $countSHT[$lnc['singkatan']];
$sumJIWA += $countAssJiwa[$lnc['singkatan']];
$sumRUGI += $countKerugian[$lnc['singkatan']];
});
});
</script>
</head>
<body>
<div style="margin:0px 50px;text-align: left;">
<h1 class="judulfrm">Detail <?php
echo $_GET["jenis"];
?>
</h1>
<table style="margin:5px;">
<tr><td width="200px">Per Tanggal</td><td width="5px">:</td><td><?php
echo balikTgl($_GET["tgl"]);
?>
</td></tr>
<tr><td>LNC</td><td>:</td><td><?php
echo $_GET["lnc"];
?>
</td></tr>
<tr><td>
<select id="searchBy" style="width:150px">
<option value="noaplikasi">No Aplikasi</option>
<option value="namadebitur">Nama Debitur</option>
<option value="no_rekg_pinjaman">No Rek Pinjaman</option>
</select>
</td><td>:</td><td>
function json_sumDetCair()
{
$db_function = new db_function();
$lnc = $_GET['lnc'];
$jns = $_GET['jns'];
$page = $_GET['page'];
$limit = $_GET['rows'];
$sidx = $_GET['sidx'];
$sord = $_GET['sord'];
$tgl = $_GET['tgl'];
if ($limit == "") {
$limit = 10;
}
$tgl_update = $tgl . " 23:59:59";
$sql = "select trail.noaplikasi,trail.namadebitur,trail.no_rekg_pinjaman,trail.tgl_pk ,trail.tgl_cair_tahap_fondasi,trail.tgl_cair_tahap_topping,trail.tgl_cair_tahap_bast,trail.tgl_cair_tahap_dok \n from debitur join debitur_trail trail on debitur.no_rekg_pinjaman=trail.no_rekg_pinjaman\n join (\n select max(tgl_update) tgl_update,no_rekg_pinjaman from debitur_trail where tgl_update <= '{$tgl_update}' group by no_rekg_pinjaman) bb\n on trail.no_rekg_pinjaman=bb.no_rekg_pinjaman and trail.tgl_update=bb.tgl_update\n where 1=1 and trail.skim_pencairan='PARTIAL DROW DOWN' and trail.skim_pks in('KAVLING BANGUN','INDENT') :paramwhere: group by trail.lnc";
if (isset($_GET['searchValue']) && trim($_GET['searchValue']) != "") {
$sql .= " and " . $_GET['searchBy'] . " like '%" . $_GET['searchValue'] . "%' ";
}
switch ($jns) {
case "debitur":
$sql = str_replace(":paramwhere:", "and trail.progress <>'' ", $sql);
break;
case "pondasi":
$sql = str_replace(":paramwhere:", "and trail.progress='BELUM SELESAI' and trail.tgl_cair_tahap_fondasi in(null,'','0000-00-00') ", $sql);
break;
case "topping":
$sql = str_replace(":paramwhere:", "and trail.progress='BELUM SELESAI' and trail.tgl_cair_tahap_fondasi >'0000-00-00' and trail.tgl_cair_tahap_topping in(null,'','0000-00-00') ", $sql);
break;
case "bast":
$sql = str_replace(":paramwhere:", "and trail.progress='BELUM SELESAI' and trail.tgl_cair_tahap_topping >'0000-00-00' and trail.tgl_cair_tahap_bast in(null,'','0000-00-00') ", $sql);
break;
case "dokumen":
$sql = str_replace(":paramwhere:", "and trail.progress='BELUM SELESAI' and trail.tgl_cair_tahap_bast >'0000-00-00' and trail.tgl_cair_tahap_dok in(null,'','0000-00-00') ", $sql);
break;
case "inprogress":
$sql = str_replace(":paramwhere:", "and trail.progress='SELESAI' ", $sql);
break;
case "selesai":
$sql .= "AND progress='SELESAI'";
break;
}
$sqlCount = "select count(*) from ({$sql}) test";
$count = $db_function->selectOnefield($sqlCount);
if ($count > 0 && $limit > 0) {
$total_pages = ceil($count / $limit);
} else {
$total_pages = 0;
}
if ($page > $total_pages) {
$page = $total_pages;
}
$start = $limit * $page - $limit;
if ($start < 0) {
$start = 0;
}
$sqlDt = "select * from ({$sql})temp LIMIT {$start} , {$limit}";
$query = $db_function->selectAllRows($sqlDt);
$responce['page'] = $page;
$responce['total'] = $total_pages;
$responce['records'] = $count;
$i = 0;
foreach ($query as $row) {
$row['tgl_pk'] = balikTgl($row['tgl_pk']);
$row['tgl_cair_tahap_fondasi'] = cleanDate(balikTgl($row['tgl_cair_tahap_fondasi']));
$row['tgl_cair_tahap_topping'] = cleanDate(balikTgl($row['tgl_cair_tahap_topping']));
$row['tgl_cair_tahap_bast'] = cleanDate(balikTgl($row['tgl_cair_tahap_bast']));
$row['tgl_cair_tahap_dok'] = cleanDate(balikTgl($row['tgl_cair_tahap_dok']));
$responce['rows'][$i]['id'] = $i;
$responce['rows'][$i]['cell'] = $row;
$i++;
}
echo json_encode($responce);
exit;
}
