get_row($sql);
if ($resp["status"] != 1) {
echo "No Default Branch";
exit();
}
return $resp["data"]["M_BranchCode"];
}
function test_db()
{
$dbx = $this->load_db();
$qry = $dbx->query("select * from test");
if (!$qry) {
echo $dbx->error()["message"];
exit();
}
$rows = $qry->result_array();
$this->print_table_style();
$this->print_table($rows, array_keys($rows[0]));
}
function ar_db($result)
{
if (count($result) == 0) {
echo "No Records";
return;
}
$date = $result[0]["Date"];
$dbx = $this->load_db();
$sql = "select count(*) total from ar where arDate = ?";
$qry = $dbx->query($sql, [$date]);
if (!$qry) {
echo "Error : " . $dbx->error()["message"];
return;
}
$rows = $qry->result_array();
if (count($rows) > 0 && $rows[0]["total"] > 0) {
echo "Error : AR $date sudah di Posting ";
return;
}
$dbx->trans_begin();
$count = 0;
foreach ($result as $r) {
$data = [];
foreach ($r as $k => $v) {
$data["ar" . $k] = $v;
}
$qry = $dbx->insert("ar", $data);
$count++;
if (!$qry) {
echo "Error : " . $dbx->error()["message"];
$dbx->trans_rollback();
return;
}
}
echo "ar data , inserted $count rows";
$dbx->trans_commit();
}
function jurnal_db($result)
{
if (count($result) == 0) {
echo "No Records";
return;
}
$date = $result[0]["Date"];
$dbx = $this->load_db();
$sql = "select count(*) total from jurnal where jurnalDate = ?";
$qry = $dbx->query($sql, [$date]);
if (!$qry) {
echo "Error : " . $dbx->error()["message"];
return;
}
$rows = $qry->result_array();
if (count($rows) > 0 && $rows[0]["total"] > 0) {
echo "Error : Jurnal $date sudah di Posting ";
return;
}
$dbx->trans_begin();
$count = 0;
foreach ($result as $r) {
$data = [];
foreach ($r as $k => $v) {
$data["jurnal" . $k] = $v;
}
$qry = $dbx->insert("jurnal", $data);
$count++;
if (!$qry) {
echo "Error : " . $dbx->error()["message"];
$dbx->trans_rollback();
return;
}
}
echo "Jurnal data , inserted $count rows";
$dbx->trans_commit();
}
function xformat($inp)
{
return number_format($inp, 0, "", "");
}
function receive_payment($date = "", $format = "html")
{
if ($date == "") {
$date = date("Y-m-d");
}
$branchCode = $this->get_branch();
$r92c = $this->r_092_c($date);
$r92c1 = $this->r_092_c1($date);
$r92c2 = $this->r_092_c2($date);
$result = [];
$tot_db = 0;
$tot_cr = 0;
foreach ($r92c as $r) {
$k = $r["M_BankAccountNo"];
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $k,
"Deskripsi" => $r["M_OmzetTypeName"] . "\t| " . $r["M_PaymentTypeName"],
//"Deskripsi" => $r["M_CompanyName"],
"Debit" => $r["total|R"],
"Kredit" => "",
];
$tot_db += $r["total|R"];
}
foreach ($r92c as $r) {
$k = "AR|" . $r["TxNo"];
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $k,
//"Deskripsi" => $r["M_OmzetTypeName"] . "|" . $r["M_CompanyName"],
"Deskripsi" => $r["M_CompanyName"],
"Debit" => "",
"Kredit" => $r["total|R"],
];
$tot_cr += $r["total|R"];
}
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => "",
"Deskripsi" => "Total",
"Debit" => $tot_db,
"Kredit" => $tot_cr,
];
if ($format == "html") {
$this->print_table_style();
$this->print_table($result, array_keys($result[0]));
exit();
}
$this->print_csv(
$result,
array_keys($result[0]),
"receive_payment.csv"
);
}
function ar($date = "", $format = "html")
{
if ($date == "") {
$date = date("Y-m-d");
}
$branchCode = $this->get_branch();
$r_092b = $this->r_092_b($date);
$r_092b1 = $this->r_092_b1($date);
$r_092b2 = $this->r_092_b2($date);
$r_092b3 = $this->r_092_b3($date);
$result = [];
$tot_db = 0;
$tot_cr = 0;
foreach ($r_092b as $r) {
$k = "AR|" . $r["TxNo"];
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $k,
"Deskripsi" =>
$r["M_CompanyName"],
"Debit" => $r["total|R"],
"ProductDescA" => "",
"Product" => "",
"Kredit" => "",
];
$code_omz = $r["M_OmzetTypeName"];
switch (strtolower($code_omz)) {
case "perusahaan":
$code_omz = "RKN";
break;
case "aps mandiri":
$code_omz = "APS";
break;
case "rujukan":
$code_omz = "RJK";
break;
case "dokter":
$code_omz = "KLN";
break;
case "penelitian":
$code_omz = "PNT";
break;
case "anak perusahaan":
$code_omz = "ANP";
break;
}
$tot_db += $r["total|R"];
$last_idx = count($result) - 1;
$is_first = true;
foreach ($r_092b1 as $r1) {
if ($r["TxNo"] != $r1["TxNo"]) {
continue;
}
if ($is_first) {
$result[$last_idx]["Product"] =
$code_omz . "|" .
sprintf("%02d", $r1["Nat_GroupID"]) .
"|" .
sprintf("%02d", $r1["groupTestId"]);
$result[$last_idx]["Kredit"] = $r1["bruto|R"];
$result[$last_idx]["ProductDesc"] =
$r1["id"] . "|" . $r1["grouptest"];
} else {
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $k,
"Deskripsi" => "",
"Debit" => "",
"ProductDesc" => $r1["id"] . "|" . $r1["grouptest"],
"Product" =>
$code_omz . "|" . sprintf("%02d", $r1["Nat_GroupID"]) .
"|" .
sprintf("%02d", $r1["groupTestId"]),
"Kredit" => $r1["bruto|R"],
];
}
$is_first = false;
$tot_cr += $r1["bruto|R"];
}
foreach ($r_092b3 as $r1) {
if ($r["TxNo"] != $r1["TxNo"]) {
continue;
}
if ($r1["diskon|R"] == 0) {
continue;
}
//$ref_no = "Diskon | " . $r1["TxNo"];
$ref_no = "AR|" . $r1["TxNo"];
$xkre = -1 * $r1["diskon|R"];
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $ref_no,
"Deskripsi" => $r1["M_CompanyName"],
"Debit" => "",
"Product" => "Diskon",
"ProductDesc" => "Diskon",
"Kredit" => $xkre,
];
$tot_db += $r1["diskon|R"];
}
}
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => "",
"Deskripsi" => "Total",
"Debit" => $tot_db,
"Product" => "",
"Kredit" => $tot_cr,
];
if ($format == "html") {
$this->print_table_style();
$this->print_table($result, array_keys($result[0]));
exit();
}
if ($format == "db") {
$this->ar_db($result);
exit();
}
$this->print_csv($result, array_keys($result[0]), "ar.csv");
}
function print_csv($rows, $header, $file_name = "download.csv")
{
//header("Content-type: text/csv");
//header('Content-Disposition: inline; filename="' . $file_name . '"');
echo "";
$line = "";
foreach ($header as $h) {
if ($line != "") {
$line .= ",";
}
if (strpos(",", $h) === false) {
$line .= $h;
} else {
$line .= "\"$h\"";
}
}
echo $line . "\n";
foreach ($rows as $r) {
$line = "";
foreach ($header as $h) {
if ($line != "") {
$line .= ",";
}
if (strpos(",", $r[$h]) === false) {
if (in_array($h, ["Debit", "Kredit"])) {
$line .= $this->xformat($r[$h]);
} else {
$line .= $r[$h];
}
} else {
$line .= "\"{$r[$h]}\"";
}
}
echo $line . "\n";
}
}
function jurnal($date = "", $format = "html", $debug = "")
{
if ($date == "") {
$date = date("Y-m-d");
}
$branchCode = $this->get_branch();
$r92x = $this->r_092x($date);
$r92xa1 = $this->r_092x_a1($date);
$r92xa2 = $this->r_092x_a2($date);
$r92xa3 = $this->r_092x_a3($date);
$r92xa4 = $this->r_092x_a4($date);
$r92xa5 = $this->r_092x_a5($date);
$tot_db = 0;
$tot_cr = 0;
if ($debug != "") {
echo "r92x\n";
print_r($r92x);
echo "r92xa1\n";
print_r($r92xa1);
echo "r92xa2\n";
print_r($r92xa2);
}
$result_pendapatan = [];
foreach ($r92x as $r) {
$result_pendapatan[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $r["M_PaymentTypeName"],
// "Ref" =>
// $r["BankAccountNo"] == ""
// ? $r["M_PaymentTypeName"]
// : $r["BankAccountNo"],
"Note" => $r["M_PaymentTypeName"],
"Debit" => $r["total|R"],
"Kredit" => "",
];
$tot_db += $r["total|R"];
}
$result_sales = [];
foreach ($r92xa1 as $r) {
$ref_no =
"Sales|" .
$r["M_OmzetTypeName"] .
"|" .
sprintf("%02d", $r["Nat_GroupID"]) .
"|" .
sprintf("%02d", $r["groupTestId"]);
$result_sales[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => $ref_no,
"Note" =>
$r["M_OmzetTypeName"] .
"|" .
$r["id"] .
"|" .
$r["grouptest"],
"Debit" => "",
"Kredit" => $r["bruto|R"],
];
$tot_cr += $r["bruto|R"];
}
$result_diskon = [];
$result_diskon = [];
foreach ($r92xa5 as $r) {
$ref_no = "";
$result_sales[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => "Diskon",
"Note" => "Diskon",
"Debit" => $r["diskon|R"],
"Kredit" => "",
];
$tot_db += $r["diskon|R"];
}
$result_round = [];
foreach ($r92xa2 as $r) {
$ref_no = "";
$result_sales[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => "Round",
"Note" => "Round",
"Debit" => $r["round|R"],
"Kredit" => "",
];
$tot_db += $r["round|R"];
}
$result = array_merge(
$result_pendapatan,
$result_sales,
$result_diskon,
$result_round
);
$result[] = [
"Date" => $date,
"BranchCode" => $branchCode,
"Ref" => "Total",
"Note" => "Total",
"Debit" => $tot_db,
"Kredit" => $tot_cr,
];
if ($format == "html") {
$this->print_table_style();
$this->print_table($result, array_keys($result[0]));
exit();
}
if ($format == "csv") {
$this->print_csv($result, array_keys($result[0]), "jurnal.csv");
exit();
}
if ($format == "db") {
$this->jurnal_db($result);
}
}
function check_receive_payment($date = "")
{
if ($date == "") {
$date = date("Y-m-d");
}
$this->print_table_style();
$rows = $this->r_092_c($date);
$this->print_table($rows, array_keys($rows[0]), "r_092_c");
echo "
";
$rows = $this->r_092_c1($date);
$this->print_table($rows, array_keys($rows[0]), "r_092_c1");
echo "
";
$rows = $this->r_092_c2($date);
$this->print_table($rows, array_keys($rows[0]), "r_092_c2");
echo "
";
}
function r_092_c2($date)
{
$sql = "select
sum(SsPiutangPaymentAmount) as `total|R` ,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
M_PaymentTypeName,
M_OmzetTypeName
-- M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'B2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by M_OmzetTypeID , TxNo
order by M_OmzetTypeID , TxNo";
$sql = " select
sum(total)as`total|R` ,
TxNo,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID
from (
select
SsPiutangPayment as total,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = '2022-12-22'
and SsPiutangType IN ('B2','A3') and T_OrderHeaderAddOnIsKaPus = 'N' and SsPiutangIsActive = 'Y'
and M_OmzetTypeID <> 7
group by SsPiutangID,T_OrderHeaderID ) as x
group by M_OmzetTypeID , TxNo
order by M_OmzetTypeID , TxNo
";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["total|R"];
$r["total|R"] = $r["total|R"];
$result[] = $r;
}
$result[] = [
"total|R" => $total,
"M_PaymentTypeName" => "",
"M_OmzetTypeName" => "",
"TxNo" => "SUM",
];
return $result;
}
function r_092_c1($date)
{
$sql = "select
-- SsPiutangT_OrderHeaderID as id,
concat(date(T_OrderHeaderDate),'-',M_CompanyName) as TxNo,
sum( MOD(SsPiutangPaymentAmount, 500)) as `round|R` ,
M_PaymentTypeName,
M_OmzetTypeName
-- M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'B2'
and M_PaymentTypeID = '1' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by M_OmzetTypeID,TxNo
order by M_OmzetTypeID,TxNo
";
$sql = "
select
-- SsPiutangT_OrderHeaderID as id,
concat(date(T_OrderHeaderDate),'-',M_CompanyName) as TxNo,
sum( MOD(SsPiutangPayment , 500)) as `round|R` ,
M_PaymentTypeName,
M_OmzetTypeName
-- M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType IN ('B2','A3') and T_OrderHeaderAddOnIsKaPus = 'N' and SsPiutangIsActive = 'Y'
and M_PaymentTypeID = '1'
and M_OmzetTypeID <> 7
group by M_OmzetTypeID,TxNo
order by M_OmzetTypeID,TxNo
";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["round|R"];
$r["round|R"] = $r["round|R"];
$result[] = $r;
}
return $result;
}
function r_092_c($date)
{
$sql = "select
-- SsPiutangT_OrderHeaderID as id,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
sum(SsPiutangPaymentAmount - if(M_PaymentTypeID = '1' , MOD(SsPiutangPaymentAmount, 500) ,'')) as `total|R` ,
concat(M_PaymentTypeName,'|' , ifnull(Nat_BankCode,''), ' ' , ifnull(M_BankAccountNo,'')) as M_PaymentTypeName,
M_OmzetTypeName, M_BankAccountNo, M_CompanyName
-- M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join m_bank_account on SsPiutangPaymentM_BankAccountID = M_BankAccountID
left join nat_bank on Nat_BankID = M_BankAccountNat_BankID
where SsPiutangDate = ?
and SsPiutangType = 'B2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by M_OmzetTypeID,M_PaymentTypeID, TxNo, M_PaymentTypeID, Nat_BankID ,M_BankAccountID
order by M_OmzetTypeID,M_PaymentTypeID
";
$sql = "select
sum(total)as`total|R` ,
TxNo,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID, M_CompanyName
from (
select
SsPiutangPayment as total,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
concat(M_PaymentTypeName,'|' , M_CompanyName , '|', ifnull(Nat_BankCode,''), ' ' , ifnull(M_BankAccountNo,'')) as M_PaymentTypeName,
-- M_PaymentTypeName,
M_OmzetTypeName, M_CompanyName,
M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join m_bank_account on SsPiutangPaymentM_BankAccountID = M_BankAccountID
left join nat_bank on Nat_BankID = M_BankAccountNat_BankID
where SsPiutangDate = ?
and SsPiutangType IN ('B2','A3') and T_OrderHeaderAddOnIsKaPus = 'N' and SsPiutangIsActive = 'Y'
and M_OmzetTypeID <> 7
group by SsPiutangID,T_OrderHeaderID ) as x
group by M_OmzetTypeID , TxNo
order by M_OmzetTypeID , TxNo ";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["total|R"];
$r["total|R"] = $r["total|R"];
$result[] = $r;
}
return $result;
}
function check_ar($date = "")
{
if ($date == "") {
$date = date("Y-m-d");
}
$this->print_table_style();
$rows = $this->r_092_b($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092_b | total piutang per omzet"
);
echo "
";
$rows = $this->r_092_b1($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092_b1 | 28 test group per omzet"
);
echo "
";
$rows = $this->r_092_b2($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092_b2 | total 28 test group per omzet"
);
echo "
";
$rows = $this->r_092_b3($date);
$this->print_table($rows, array_keys($rows[0]), "r_092_b3 | diskon");
echo "
";
}
function r_092_b3($date)
{
$sql = "select
-- SsPiutangT_OrderHeaderID as id,
-- M_OmzetTypeID ,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
M_OmzetTypeName, M_CompanyName,
sum(ifnull(SsPiutangDiscount,0)) as `diskon|R`
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
-- left join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
-- left join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID
where SsPiutangDate = ?
and SsPiutangType IN ('A1','A3') and T_OrderHeaderAddOnIsKaPus = 'N'
-- and (F_PaymentDetailM_PaymentTypeID <> 6 or T_OnlineOrderID is null)
and ( T_OnlineOrderID is null)
and M_OmzetTypeID <> 7
group by TxNo, M_OmzetTypeID
order by TxNo, M_OmzetTypeID";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["diskon|R"];
$r["diskon|R"] = $r["diskon|R"];
$result[] = $r;
}
return $result;
}
function r_092_b2($date)
{
$sql = "select
-- SsPiutangT_OrderHeaderID as id,
sum(SsPiutangTestPrice) `price|R` ,
M_OmzetTypeName
-- M_OmzetTypeID
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
-- left join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
-- left join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID
where SsPiutangDate = ?
and SsPiutangType IN ('A1','A3') and T_OrderHeaderAddOnIsKaPus = 'N'
-- and (F_PaymentDetailM_PaymentTypeID <> 6 or T_OnlineOrderID is null)
and (T_OnlineOrderID is null)
and M_OmzetTypeID <> 7
group by M_OmzetTypeID
order by M_OmzetTypeID,Nat_SubGroupID";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["price|R"];
$r["price|R"] = $r["price|R"];
$result[] = $r;
}
return $result;
}
function r_092_b1($date)
{
$sql = "select
Nat_GroupName as id,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
Nat_GroupID,
sum(SsPiutangTestPrice) as `bruto|R`,
M_OmzetTypeName,
-- M_OmzetTypeID,
Nat_SubGroupName as grouptest,
Nat_SubGroupID as groupTestId
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join nat_group on T_TestNat_GroupID = Nat_GroupID
left join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
-- left join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
-- left join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID
where SsPiutangDate = ?
and SsPiutangType IN ('A1','A3') and T_OrderHeaderAddOnIsKaPus = 'N'
-- and (F_PaymentDetailM_PaymentTypeID <> 6 or T_OnlineOrderID is null)
and ( T_OnlineOrderID is null)
and M_OmzetTypeID <> 7
group by TxNo,Nat_GroupID,Nat_SubGroupID,M_OmzetTypeName
order by TxNo,M_OmzetTypeName, Nat_GroupID,Nat_SubGroupID";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["bruto|R"];
$r["bruto|R"] = $r["bruto|R"];
$result[] = $r;
}
return $result;
}
function r_092_b($date)
{
$sql = "select
M_OmzetTypeName,
concat(date(T_OrderHeaderDate),'|',M_CompanyNumber) as TxNo,
-- M_OmzetTypeID,
-- SsPiutangT_OrderHeaderID as id,
sum(SsPiutangTotal) as `total|R` ,
sum(SsPiutangTotal) + sum(SsPiutangDiscount) as `grandTotal|R`,
M_CompanyName
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
-- left join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
-- left join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID
where SsPiutangDate = ?
and SsPiutangType IN ('A1','A3') and T_OrderHeaderAddOnIsKaPus = 'N'
-- and (F_PaymentDetailM_PaymentTypeID <> 6 or T_OnlineOrderID is null)
and (T_OnlineOrderID is null)
and M_OmzetTypeID <> 7
group by M_OmzetTypeID,TxNo
order by M_OmzetTypeID,TxNo ";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
$grandTotal = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["total|R"];
$grandTotal += $r["grandTotal|R"];
$r["total|R"] = $r["total|R"];
$r["grandTotal|R"] = $r["grandTotal|R"];
$result[] = $r;
}
return $result;
}
function check_jurnal_entry($date = "")
{
if ($date == "") {
$date = date("Y-m-d");
}
$this->print_table_style();
$rows = $this->r_092x($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092x | Tipe Pembayaran"
);
echo "
";
$rows = $this->r_092x_a1($date);
$this->print_table($rows, array_keys($rows[0]), "r_092x_a1 | 28 test");
echo "
";
$rows = $this->r_092x_a2($date);
$this->print_table($rows, array_keys($rows[0]), "r_092x_a2 | Rounding");
echo "
";
$rows = $this->r_092x_a3($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092x_a3 | total pembayaran - round - diskon"
);
echo "
";
$rows = $this->r_092x_a4($date);
$this->print_table(
$rows,
array_keys($rows[0]),
"r_092x_a4 | total 28 test"
);
echo "
";
$rows = $this->r_092x_a5($date);
$this->print_table($rows, array_keys($rows[0]), "r_092x_a5 | diskon");
}
function r_092x_a5($date = "")
{
$sql = "
select
sum(diskon) as `diskon|R`
from (
select
SsPiutangT_OrderHeaderID as id,
M_OmzetTypeID ,M_OmzetTypeName,
sum(SsPiutangDiscount) as diskon
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
union all
select
SsPiutangT_OrderHeaderID as id,
M_OmzetTypeID ,M_OmzetTypeName,
sum(SsPiutangDiscount) as diskon
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID and F_PaymentDetailM_PaymentTypeID = 6
left join m_paymenttype on F_PaymentDetailM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
where SsPiutangDate =?
and SsPiutangType = 'A1'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
) as x
";
$xsql = "select
sum(SsPiutangDiscount) as `diskon|R`
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2'
and M_OmzetTypeID <> 7
and T_OrderHeaderAddOnIsKaPus = 'N' ";
$resp = $this->get_rows($sql, [$date, $date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
foreach ($resp["data"] as $idx => $r) {
$r["diskon|R"] = $r["diskon|R"];
$result[] = $r;
}
return $result;
}
function r_092x_a4($date = "")
{
$sql = "
select
sum(a) `total|R`
from
(
select
SsPiutangT_OrderHeaderID as id,
sum(SsPiutangTestPrice) as a ,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
union all
select
SsPiutangT_OrderHeaderID as id,
sum(SsPiutangTestPrice) as a,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID and T_OnlineOrderIsActive = 'Y'
join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID and F_PaymentDetailM_PaymentTypeID = 6
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
where SsPiutangDate =?
and SsPiutangType = 'A1'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
) as x
";
$xsql = "select
sum(SsPiutangTestPrice) `total|R`
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
order by Nat_SubGroupID ";
$resp = $this->get_rows($sql, [$date, $date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
foreach ($resp["data"] as $idx => $r) {
$r["total|R"] = $r["total|R"];
$result[] = $r;
}
return $result;
}
function r_092x_a3($date = "")
{
$sql = "select
sum(bayar) + sum(SsPiutangDiscount) as `total|R`
from
(
select
sum(SsPiutangPaymentAmount) as bayar,
SsPiutangDiscount,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by T_OrderHeaderID
union
select
sum(F_PaymentDetailAmount) as bayar,
SsPiutangDiscount,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID and F_PaymentDetailM_PaymentTypeID = 6
left join m_paymenttype on F_PaymentDetailM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
where SsPiutangDate = ?
and SsPiutangType = 'A1'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
group by T_OrderHeaderID
) as x";
$resp = $this->get_rows($sql, [$date, $date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
foreach ($resp["data"] as $idx => $r) {
$r["total|R"] = $r["total|R"];
$result[] = $r;
}
return $result;
}
function r_092x_a2($date = "")
{
$sql = "select
SsPiutangT_OrderHeaderID as id,
sum( MOD(SsPiutangPaymentAmount, 500)) as 'round|R' ,
M_PaymentTypeName,
M_OmzetTypeName,
M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
and M_PaymentTypeID = '1' and T_OrderHeaderAddOnIsKaPus = 'N' ";
$xsql = "select
-- SsPiutangT_OrderHeaderID as id,
sum( MOD(SsPiutangPaymentAmount, 500)) as `round|R`
-- M_PaymentTypeName,
-- M_OmzetTypeName,
-- M_OmzetTypeID
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
where SsPiutangDate = ?
and SsPiutangType = 'A2'
and M_PaymentTypeID = '1' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
";
$resp = $this->get_rows($sql, [$date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$prevCompanyName = "";
$total = 0;
$prevPaymentType = "";
$sub_pay_total = 0;
foreach ($resp["data"] as $idx => $r) {
$r["round|R"] = $r["round|R"];
$result[] = $r;
}
return $result;
}
function r_092x_a1($date = "")
{
$sql = " select
Nat_GroupID,
Nat_GroupName as id,
sum(SsPiutangTestPrice) as `bruto|R` ,
M_OmzetTypeName,
-- M_OmzetTypeID,
Nat_SubGroupName as grouptest,
Nat_SubGroupID as groupTestId
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID and Nat_SubGroupIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join nat_group on T_TestNat_GroupID = Nat_GroupID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by Nat_GroupID,Nat_SubGroupID,M_OmzetTypeName
union
select
Nat_GroupID,
Nat_GroupName as id,
sum(SsPiutangTestPrice) as bruto ,
M_OmzetTypeName,
-- M_OmzetTypeID,
Nat_SubGroupName as grouptest,
Nat_SubGroupID as groupTestId
from ss_piutang
join ss_piutang_test on SsPiutangTestSsPiutangID = SsPiutangID
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID and T_OnlineOrderIsActive = 'Y'
join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID and F_PaymentDetailM_PaymentTypeID = 6
left join t_test on SsPiutangTestT_TestID = T_TestID
left join nat_subgroup on T_TestNat_SubgroupID = Nat_SubGroupID and Nat_SubGroupIsActive = 'Y'
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join nat_group on T_TestNat_GroupID = Nat_GroupID
where SsPiutangDate = ?
and SsPiutangType = 'A1'
and M_OmzetTypeID <> '7' and M_OmzetTypeIsActive = 'Y'
group by Nat_GroupID,Nat_SubGroupID,M_OmzetTypeName
order by M_OmzetTypeName, Nat_GroupID ";
$resp = $this->get_rows($sql, [$date, $date]);
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["bruto|R"];
$r["bruto|R"] = $r["bruto|R"];
$result[] = $r;
}
/*
$result[] = [
"Nat_GroupID" => "",
"id" => "Total",
"bruto|R" => number_format($total, 0, "", " "),
"M_OmzetTypeName" => "",
"M_OmzetTypeID" => "",
"grouptest" => "",
];
*/
return $result;
}
function r_092x($date = "")
{
if (true) {
$sql = "select
-- SsPiutangT_OrderHeaderID as id,
ifnull(M_BankAccountNo,'') BankAccountNo,
case when M_PaymentTypeName='RK' then concat(M_PaymentTypeName,'|', fn_get_asal_rk(SsPiutangT_OrderHeaderID) )
else
concat(M_PaymentTypeName,'|' , ifnull(Nat_BankCode,''), ' ' , ifnull(M_BankAccountNo,''))
end as M_PaymentTypeName,
sum(SsPiutangPaymentAmount - if(M_PaymentTypeID = '1' , MOD(SsPiutangPaymentAmount, 500) ,'')) as total
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join m_company on T_OrderHeaderM_CompanyID = M_CompanyID
left join ss_piutang_payment on SsPiutangPaymentSsPiutangID = SsPiutangID
left join m_paymenttype on SsPiutangPaymentM_PaymentTypeID = M_PaymentTypeID
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join t_orderheaderaddon on T_OrderHeaderAddOnT_OrderHeaderID =T_OrderHeaderID
left join m_bank_account on SsPiutangPaymentM_BankAccountID = M_BankAccountID
left join nat_bank on Nat_BankID = M_BankAccountNat_BankID
where SsPiutangDate = ?
and SsPiutangType = 'A2' and T_OrderHeaderAddOnIsKaPus = 'N'
and M_OmzetTypeID <> 7
group by M_PaymentTypeName, Nat_BankID ,M_BankAccountID
union
select
-- SsPiutangT_OrderHeaderID as id,
ifnull(M_BankAccountNo,'') BankAccountNo,
concat('DP ', M_PaymentTypeName,'|' , ifnull(Nat_BankCode,''), ' ' , ifnull(M_BankAccountNo,'')) as M_PaymentTypeName,
sum(F_PaymentDetailAmount ) as total
from ss_piutang
join t_orderheader on SsPiutangT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y'
join t_onlineorder on T_OrderHeaderID = T_OnlineOrderT_OrderHeaderID
and T_OnlineOrderIsActive = 'Y'
join f_payment on F_PaymentT_OrderHeaderID = T_OrderHeaderID and F_PaymentIsActive = 'Y'
join f_paymentdetail on F_PaymentID = F_PaymentDetailF_PaymentID and F_PaymentDetailM_PaymentTypeID = 6
left join m_paymenttype on F_PaymentDetailM_PaymentTypeID = M_PaymentTypeID
left join m_company on T_OrderHeaderM_CompanyID = M_CompanyID and M_CompanyIsActive = 'Y'
left join m_companytype on M_CompanyM_CompanyTypeID = M_CompanyTypeID and M_CompanyTypeIsActive = 'Y'
left join m_mou on T_OrderHeaderM_MouID = M_MouID
left join m_omzettype on M_MouM_OmzetTypeID = M_OmzetTypeID
left join m_bank_account on F_PaymentDetailM_BankAccountID = M_BankAccountID
left join nat_bank on Nat_BankID = M_BankAccountNat_BankID
where SsPiutangDate = ?
and SsPiutangType = 'A1'
group by M_PaymentTypeName, Nat_BankID ,M_BankAccountID
order by M_PaymentTypeName";
$resp = $this->get_rows($sql, [$date, $date]);
}
if ($resp["status"] == -1) {
echo "Error: " . $resp["message"];
exit();
}
if (count($resp["data"]) == 0) {
return [];
}
$result = [];
$prevCompanyName = "";
$total = 0;
$prevPaymentType = "";
$sub_pay_total = 0;
foreach ($resp["data"] as $idx => $r) {
$total += $r["total"];
$r["total|R"] = $r["total"];
unset($r["total"]);
$prevCompanyName = $r["M_CompanyName"];
$prevPaymentType = $r["M_PaymentTypeName"];
$result[] = $r;
}
return $result;
}
function get_rows($sql, $param = false)
{
if ($param) {
$qry = $this->db->query($sql, $param);
} else {
$qry = $this->db->query($sql);
}
if (!$qry) {
return [
"status" => -1,
"message" =>
$this->db->last_query() .
"|" .
$this->db->error()["message"],
];
}
return ["status" => 0, "data" => $qry->result_array()];
}
function get_row($sql, $param = false)
{
$resp = $this->get_rows($sql, $param);
if ($resp["status"] == -1) {
return $resp;
}
if (count($resp["data"]) == 0) {
return ["status" => 0, "message" => "Not found."];
}
return ["status" => 1, "data" => $resp["data"][0]];
}
public function print_table_style()
{
echo "
";
}
public function print_table($rows, $keys, $title = false)
{
echo "";
if ($title) {
$col_span = count($keys);
echo "";
echo "| $title | ";
echo "
";
}
echo "";
foreach ($keys as $k) {
$k = str_replace("|R", "", $k);
echo "| $k | ";
}
echo "
\n";
foreach ($rows as $r) {
echo "";
foreach ($keys as $k) {
if (in_array($k, ["Debit", "Debit", "Kredit"])) {
echo "| " .
$this->xformat($r[$k]) .
" | ";
} else {
echo "" . $r[$k] . " | ";
}
}
echo "
";
}
echo "
";
}
function load_db()
{
$config = [
"dsn" => "",
"hostname" => "192.168.55.227",
"username" => "xone",
"password" => "xone!xx123",
"database" => "autocon_v2",
"dbdriver" => "mysqli",
"dbprefix" => "",
"pconnect" => false,
"db_debug" => false,
"cache_on" => false,
"cachedir" => "",
"char_set" => "utf8",
"dbcollat" => "utf8_general_ci",
"swap_pre" => "",
"encrypt" => false,
"compress" => false,
"stricton" => false,
"failover" => [],
"save_queries" => true,
];
return $this->load->database($config, true);
}
}
/*
create table jurnal (
jurnalID int not null auto_increment primary key,
jurnalDate date,
jurnalBranchCode varchar(3),
jurnalRef varchar(100),
jurnalNote varchar(100),
jurnalDebit decimal(15),
jurnalKredit decimal(15),
jurnalCreated datetime default current_timestamp(),
key(jurnalDate),
key(jurnalBranchCode),
key(jurnalRef)
);
"Date" => $date,
"BranchCode" => $branchCode ,
"Ref" => $k,
"Deskripsi" => $r["M_OmzetTypeName"] . "/" . $r["M_CompanyName"] ,
"Debit" => $r["total|R"],
"ProductDesc" => "",
"Product" => "",
"Kredit" => ""
create table ar(
arID int not null auto_increment primary key,
arDate date,
arBranchCode varchar(3),
arRef varchar(100),
arDeskripsi varchar(100),
arDebit decimal(10),
arProductDesc varchar(100),
arProduct varchar(20),
arKredit decimal(10),
arCreatedDate datetime default current_timestamp(),
key(arDate),
key(arBranchCode)
);
)
*/