<?php
$db_function = new db_function();
if (empty($_GET['no_trail'])) {
$no_trail = $db_function->selectOnefield("select no_trail from debitur_trail where no_rekg_pinjaman='" . $_GET['id'] . "' order by no_trail desc limit 1");
header("location: col_trail.php?id=" . $_GET['id'] . "&no_trail=" . $no_trail);
exit;
}
$showInformasiJaminan = false;
$showInformasiAsuransiKerugian = false;
$showInformasiAsuransiJiwa = false;
$showInformasiFleksi = false;
$showInformasiOto = false;
$showDtLunas = false;
$showForm = true;
$showInformasiLain = true;
$showEmergencyKon = true;
$messageBox = "";
$pesanError = array();
$program_kd = "";
$cab_kd = "";
$lnc = "*";
//$messageBox = showMessage("<div>Data Telah di simpan</div>","notice");
$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;
function validasi_form($frm)
{
$pesanError = array();
$db_function = new db_function();
if (cleanstr($frm['namadebitur']) == "") {
array_push($pesanError, "Nama Debitur Harus diisi");
}
if (cleanstr($frm['tgl_pk']) == "") {
array_push($pesanError, "Tgl Perjanjian Kredit Harus diisi");
}
if (cleanstr($frm['jkw_kredit']) == "") {
array_push($pesanError, "Jangka Waktu Kredit Harus diisi");
}
if (cleanstr($frm['fixed_rate']) == "" && preg_match("/(griya|multiguna)/", strtolower($frm['produk']))) {
array_push($pesanError, "Masa fix rate Harus diisi");
}
if (cleanstr($frm['no_rekg_pinjaman']) == "") {
array_push($pesanError, "Norek Pinjaman Harus diisi");
}
//validasi no_rekg_pinjaman
$buf = $db_function->selectOnefield("select noaplikasi from debitur where no_rekg_pinjaman ='" . $frm['no_rekg_pinjaman'] . "'");
if (cleanstr($buf) != "") {
array_push($pesanError, "Norek Pinjaman sudah ada sebelum nya dengan no aplikasi " . $buf);
}
$skimPencairan = strtolower($frm['skim_pencairan']);
$skimPks = strtolower($frm['skim_pks']);
if ($skimPencairan == "partial drow down" && in_array($skimPks, array("kavling bangun", "indent"))) {
if ($frm['progress'] == "") {
array_push($pesanError, "Progress Pembangunan harus di isi untuk Partial drow down, skim pks kavling bangun/indent ");
} elseif ($frm["progress"] != "SELESAI" && !in_array($frm['tgl_cair_tahap_dok'], array("", "00-00-0000"))) {
array_push($pesanError, "tanggal cair tahap dok sudah di isi harap progress pembangunan = <b>selesai</b>");
}
}
return $pesanError;
}
function insertDebitur($data, $row, $type = "system")
{
$usercreate = $_SESSION['colateral']['npp'];
$userupdate = $usercreate;
$action = "now()";
$tgl_update = "now()";
if ($type == "system") {
//print_r($data);
$row;
$action = "'" . $data->val($row, 1) . "'";
$tgl_update = "'" . $data->val($row, 121) . "'";
$usercreate = "'" . $data->val($row, 155) . "'";
$userupdate = "'" . $data->val($row, 156) . "'";
}
if (cleanstr($data->val($row, 9)) != "") {
$pesan = "";
$db_function = new db_function();
$sql = "delete from debitur where no_rekg_pinjaman='" . $data->val($row, 9) . "'";
$pesan = $db_function->exec($sql);
if ($pesan != "") {
$countArray = count($_SESSION['colateral']['message_loop']) + 1;
$_SESSION['colateral']['message_loop'][$countArray] == "[del debitur]no_rekg_pinjaman->" . $data->val($row, 9) . ":" . $pesan;
}
/** // kalau insert manual hapus semua data debitur_trail
if($type!="system"){
$sql = "delete from debitur_trail where no_rekg_pinjaman='" . $data->val($row, 9) . "'";
$pesan.=$db_function->exec($sql);
}*/
$sql = "insert into debitur (action,input_date,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,jenis_sertifikat,jaminan,jml_jaminan,jenis_surat_tanah,alamat_collateral,luas_tanah,tgl_jt_surat_tanah,jenis_pengikatan,nilai_ht,no_covernote,no_covernote_n,tgl_covernote,jkw_covernote,tgl_jt_covernote,notaris,appraisal,jml_jaminan_n,no_ajb,no_ajb_n,no_surat_tanah,collateral_zipcode,luas_bangunan,nilai_taksasi,harga_tanah,harga_bangunan,harga_tanah_imb,harga_bangunan_imb,no_pengikatan,no_pengikatan_n,proses_pengikatan,tgl_pengikatan,tgl_penyerahan_berkas,developer,no_pks,skim_pks,status_imb,no_imb,nama_perumahan,kategori_proyek,jenis_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,asuransi_jiwa,no_polis_ass_jiwa,no_polis_ass_jiwa_n,premi_jiwa,nilai_pertanggungan_ass_jiwa,tgl_ass_jiwa,tgl_jt_ass_jiwa,berkas_assuransi_jiwa,asuransi_kerugian,no_polis_ass_kerugian,no_polis_ass_kerugian_n,premi_kerugian,nilai_pertanggungan_ass_kerugian,tgl_ass_kerugian,tgl_jt_ass_kerugian,berkas_asuransi_kerugian,jenis_kendaraan,no_bpkb,no_bpkb_n,no_rangka,nama_dealer,merk,no_mesin,no_polisi,status_rekg,tgl_pelunasan,tgl_serah,pelunasan_penerima,pelunasan_keterangan,memo,skdr,siup,siup_n,tdp,tdp_n,others,others_n,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_flag,kjpp,status,tgl_update_app,tgl_update_los,tgl_update_asc,skim_pencairan,no_jaminan_fleksi,no_jaminan_fleksi_n,jns_jaminan_fleksi,srt_pernyataan_fleksi,usercreate,userupdate) values(";
for ($col = 1; $col <= 156; $col++) {
$value = $data->val($row, $col);
if ($col == 1) {
$sql .= $action . ",";
} elseif ($col == 121) {
$sql .= $tgl_update . ",";
} elseif ($col == 155) {
$sql .= $usercreate . ",";
} elseif ($col == 156) {
$sql .= $userupdate . ",";
} else {
$sql .= "'" . $value . "',";
}
}
$sql = substr($sql, 0, strlen($sql) - 1);
$sql .= ")";
$pesan = $db_function->exec($sql);
if ($pesan != "") {
$_SESSION['colateral']['message_loop'][] = "[inst debitur]no_rekg_pinjaman->" . $data->val($row, 9) . ":" . $pesan;
}
if ($type == "manual") {
$sql = "select no_trail from debitur_trail where no_rekg_pinjaman='" . $data->val($row, 9) . "' order by no_trail desc limit 1";
$no_trail = $db_function->selectOnefield($sql);
$no_trail = $no_trail == "" ? "1" : $no_trail + 1;
$sql = "insert into debitur_trail (no_trail,insertfrom,input_date,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,jenis_sertifikat,jaminan,jml_jaminan,jenis_surat_tanah,alamat_collateral,luas_tanah,tgl_jt_surat_tanah,jenis_pengikatan,nilai_ht,no_covernote,no_covernote_n,tgl_covernote,jkw_covernote,tgl_jt_covernote,notaris,appraisal,jml_jaminan_n,no_ajb,no_ajb_n,no_surat_tanah,collateral_zipcode,luas_bangunan,nilai_taksasi,harga_tanah,harga_bangunan,harga_tanah_imb,harga_bangunan_imb,no_pengikatan,no_pengikatan_n,proses_pengikatan,tgl_pengikatan,tgl_penyerahan_berkas,developer,no_pks,skim_pks,status_imb,no_imb,nama_perumahan,kategori_proyek,jenis_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,asuransi_jiwa,no_polis_ass_jiwa,no_polis_ass_jiwa_n,premi_jiwa,nilai_pertanggungan_ass_jiwa,tgl_ass_jiwa,tgl_jt_ass_jiwa,berkas_assuransi_jiwa,asuransi_kerugian,no_polis_ass_kerugian,no_polis_ass_kerugian_n,premi_kerugian,nilai_pertanggungan_ass_kerugian,tgl_ass_kerugian,tgl_jt_ass_kerugian,berkas_asuransi_kerugian,jenis_kendaraan,no_bpkb,no_bpkb_n,no_rangka,nama_dealer,merk,no_mesin,no_polisi,status_rekg,tgl_pelunasan,tgl_serah,pelunasan_penerima,pelunasan_keterangan,memo,skdr,siup,siup_n,tdp,tdp_n,others,others_n,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_flag,kjpp,status,tgl_update_app,tgl_update_los,tgl_update_asc,skim_pencairan,no_jaminan_fleksi,no_jaminan_fleksi_n,jns_jaminan_fleksi,srt_pernyataan_fleksi,userupdate) values(";
$sql .= "'{$no_trail}','xls',";
for ($col = 1; $col <= 156; $col++) {
$value = $data->val($row, $col);
if ($col == 1) {
// $sql.=$action . ",";
} elseif ($col == 121) {
$sql .= $tgl_update . ",";
} elseif ($col == 155) {
// $sql.= $usercreate . ",";
} elseif ($col == 156) {
$sql .= $userupdate . ",";
} else {
$sql .= "'" . $value . "',";
}
}
$sql = substr($sql, 0, strlen($sql) - 1);
$sql .= ")";
// echo $sql;exit;
$pesan = $db_function->exec($sql);
if ($pesan != "") {
$_SESSION['colateral']['message_loop'][] = "[insr debitur trail]no_rekg_pinjaman->" . $data->val($row, 9) . ":" . $pesan;
}
}
}
}
<?php
include 'collateral_script/session_head.php';
include 'collateral_script/function.php';
include 'collateral_script/db_function.php';
$db_function = new db_function();
if (!empty($_POST)) {
$user = $_SESSION['colateral']['npp'];
$error = $db_function->initTrail('init', "1", "sama");
echo $error;
}
$row = $db_function->selectOnefield("select count(*)from debitur left join debitur_trail on debitur.noaplikasi = debitur_trail .noaplikasi where debitur_trail.noaplikasi is null");
?>
<!DOCTYPE html>
<html>
<head>
<?php
include 'collateral_script/head.php';
?>
</head>
<body>
<div style="margin:0px 50px;text-align: left;">
<h1 class="judulfrm">Inisialisasi Trail</h1>
<div style="margin:10px;">
<form method="POST">
<h3><?php
echo $row;
?>
data tidak ada auditrail </h3>
<input type="submit" value="Inisialisasi Trail" name="action" />
$query .= " where no_rekg_pinjaman='" . $_POST['frm']['no_rekg_pinjaman'] . "'";
$buf = cleanstr($db_function->exec($query));
if ($buf == "") {
//insert trail
$strKey = "";
$strVal = "'";
$frmTrail = $_POST['frm'];
foreach ($frmTrail as $key => $val) {
if (isDate($val)) {
$val = balikTgl($val);
}
$strKey .= $key . ",";
$strVal .= trim($val) . "','";
}
$query = "select no_trail from debitur_trail where no_rekg_pinjaman='" . $_POST['frm']['no_rekg_pinjaman'] . "' order by no_trail desc";
$lastOrder = $db_function->selectOnefield($query);
$lastOrder = $lastOrder == "" ? "0" : $lastOrder + 1;
$strKey = substr_replace($strKey, "", -1);
$strVal = substr_replace($strVal, "", -2);
$query = "insert into debitur_trail\n (no_trail,{$strKey},userupdate,tgl_update) \n values('{$lastOrder}',{$strVal},'{$userCreate}',now());";
// echo $query;exit;
$buf = cleanstr($db_function->exec($query));
// echo $buf;
// exit;
}
if ($buf != "") {
array_push($pesanError, $buf);
} else {
$_SESSION['colateral']['message'] = showMessage("Data telah ubah", "success", "-ses");
header("location:edit_data_debitur.php?id=" . $_POST['frm']['no_rekg_pinjaman']);
exit;
$listTypeLookup[$row["type"]]=$row["type"];
}*/
$listTypeLookup = array("asuransi_jiwa" => "Asuransi Jiwa", "asuransi_kerugian" => "Asuransi Kerugian", "daftar_kjpp" => "Daftar Kjpp", "developer" => "Developer", "jns_kendaraan" => "Jenis Kendaraan", "merk_kendaraan" => "Merek Kendaraan", "notaris" => "Notaris", "kendala" => "Kendala Pengikatan", "jns_pengikatan" => "Jenis Pengikatan", "jns_surat_tanah" => "Jenis Jaminan");
$listLookup = "";
$action = $_GET['action'] != null ? $_GET['action'] : "Simpan";
if (!empty($_POST)) {
if ($_POST['action'] == "Batal") {
header("location:col_lookup.php?type=" . $_GET['type']);
} elseif ($_POST['action'] == "Simpan") {
$frm = $_POST['frm'];
$sql = "insert into lookup(type,value,lnc)values('" . $frm['type'] . "','" . $frm['value'] . "','" . $frm['lnc'] . "')";
$db_function->exec($sql);
header("location:col_lookup.php?type=" . $_GET['type']);
} elseif ($_POST['action'] == "Edit") {
$frm = $_POST['frm'];
$db_function->exec("update lookup set type='" . $frm['type'] . "',value='" . $frm['value'] . "',lnc='" . $frm['lnc'] . "' " . "where type='" . $_GET['type'] . "' and value='" . $_GET['value'] . "'");
header("location:col_lookup.php?type=" . $_GET['type']);
}
}
if ($action == "delete") {
$db_function->exec("delete from lookup where type='" . $_GET['type'] . "' and value='" . $_GET['value'] . "'");
header("location:col_lookup.php?type=" . $_GET['type']);
} elseif ($action == "Edit" && empty($_POST)) {
$_POST['frm']['value'] = $_GET['value'];
$_POST['frm']['lnc'] = $db_function->selectOnefield("select lnc from lookup where type='" . $_GET['type'] . "' and value='" . $_GET['value'] . "'");
}
if ($_GET['type'] != null) {
$_POST['frm']['type'] = $_GET['type'];
$sql = "select * from lookup where type= '" . $_GET['type'] . "' order by value asc";
$listLookup = $db_function->selectAllRows($sql);
}
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;
}
