611 lines
18 KiB
PHP
611 lines
18 KiB
PHP
<?php
|
|
|
|
class Email_on_hold_multi extends MY_Controller
|
|
{
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->response = [
|
|
"status" => "ERR",
|
|
"message" => "",
|
|
];
|
|
}
|
|
public function reply()
|
|
{
|
|
echo json_encode($this->response);
|
|
exit();
|
|
}
|
|
public function get_config()
|
|
{
|
|
$sql = "select *
|
|
from conf_on_hold
|
|
where ConfOnHoldIsActive = 'Y'
|
|
limit 0,1";
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Get Config On Hold Email | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
if (count($rows) == 0) {
|
|
$this->response["message"] =
|
|
"Error Get Config On Hold Email | Configuration On Hold not found!";
|
|
$this->reply();
|
|
}
|
|
$config = $rows[0];
|
|
$sql = "select M_CityName
|
|
from m_branch
|
|
join m_city on M_BranchM_CityID = M_CityID
|
|
where M_BranchIsActive = 'Y' and M_BranchIsDefault = 'Y'";
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Get City Branch | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
if (count($rows) == 0) {
|
|
$this->response["message"] = "Error Get City Branch not found!";
|
|
$this->reply();
|
|
}
|
|
$config["M_CityName"] = $rows[0]["M_CityName"];
|
|
return $config;
|
|
}
|
|
|
|
public function compose_email($reminder, $config, $r)
|
|
{
|
|
$tpl = $config["ConfOnHoldTemplateHtml"];
|
|
if ($reminder == 2) {
|
|
$tpl = $config["ConfOnHoldTemplateHtml02"];
|
|
}
|
|
if ($reminder == 3) {
|
|
$tpl = $config["ConfOnHoldTemplateHtml03"];
|
|
}
|
|
$emailMessage = str_replace(
|
|
"{{SENDER_NAME}}",
|
|
$config["ConfOnHoldSenderName"],
|
|
$tpl
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{SENDER_TITLE}}",
|
|
$config["ConfOnHoldSenderTitle"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{SENDER_TITLE_EN}}",
|
|
$config["ConfOnHoldSenderTitleEng"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{LETTER_CITY}}",
|
|
$config["M_CityName"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{LETTER_DATE}}",
|
|
date("d M Y"),
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{LETTER_NUMBER}}",
|
|
$r["F_BillIssueRefNumber"] . "/RE/01",
|
|
$emailMessage
|
|
);
|
|
|
|
$emailMessage = str_replace(
|
|
"{{PIC_NAME}}",
|
|
$r["F_BillIssuePIC"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{COMPANY_NAME}}",
|
|
$r["M_CompanyName"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{COMPANY_ADDRESS}}",
|
|
$r["M_CompanyAddress"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{COMPANY_CITY}}",
|
|
$r["M_CityName"],
|
|
$emailMessage
|
|
);
|
|
|
|
$periode =
|
|
$this->xdate($r["StartOrderDate"]) .
|
|
" - " .
|
|
$this->xdate($r["EndOrderDate"]);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_PERIODE}}",
|
|
$periode,
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_DUEDATE}}",
|
|
$this->xdate($r["DueDate"]),
|
|
$emailMessage
|
|
);
|
|
$inv_amount =
|
|
"<b>Rp " .
|
|
number_format(intVal($r["TotalAmount"]), 0, ".", ",") .
|
|
",-<b>";
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_AMOUNT}}",
|
|
$inv_amount,
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_NUMBER}}",
|
|
$r["F_BillIssueRefNumber"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_ALPHABET_ID}}",
|
|
$r["Terbilang_id"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_ALPHABET_EN}}",
|
|
$r["Terbilang_en"],
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_ALPHABET_EN}}",
|
|
$r["Terbilang_en"],
|
|
$emailMessage
|
|
);
|
|
|
|
$emailMessage = str_replace(
|
|
"{{LETTER_ATTACHMENT}}",
|
|
$r["F_BillIssueImg"],
|
|
$emailMessage
|
|
);
|
|
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_REMINDERDATE}}",
|
|
$this->xdate($r["ReminderDate"]),
|
|
$emailMessage
|
|
);
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_FINALDATE}}",
|
|
$this->xdate($r["OnHoldDate"]),
|
|
$emailMessage
|
|
);
|
|
|
|
$emailMessage = str_replace(
|
|
"{{LETTER_ATTACHMENT_SEND}}",
|
|
$r["F_BillIssueImgSend"],
|
|
$emailMessage
|
|
);
|
|
|
|
$emailMessage = str_replace(
|
|
"{{INVOICE_RECEIVEDATE}}",
|
|
$this->xdate($r["F_BillIssueReceiveDate"]),
|
|
$emailMessage
|
|
);
|
|
|
|
return $emailMessage;
|
|
}
|
|
//TODO:
|
|
public function first_reminder()
|
|
{
|
|
$config = $this->get_config();
|
|
$sql = "select
|
|
F_BillIssueID,
|
|
F_BillNo,
|
|
F_BillIssueNumber,
|
|
F_BillIssueRefNumber,
|
|
F_BillIssueDate,
|
|
F_BillIssueReceiveDate,
|
|
min(date(T_OrderHeaderDate)) StartOrderDate,
|
|
max(date(T_OrderHeaderDate)) EndOrderDate,
|
|
(F_BillTotal) TotalAmount,
|
|
F_BillDueDate DueDate,
|
|
M_CompanyName,
|
|
M_CompanyAddress,
|
|
M_CityName,
|
|
F_BillIssuePIC,
|
|
F_BillIssueImg,
|
|
F_BillIssuePICEmail,
|
|
ifnull(KeuReminderStatus , 'N') KeuReminderStatus,
|
|
if( date(KeuReminderCreated) = date(now()) , 'Y' , 'N') today ,
|
|
fn_IntegerToWords((F_BillTotal)) Terbilang_en,
|
|
f_terbilang((F_BillTotal)) Terbilang_id,
|
|
F_BillDueDate + interval 7 day as OnHoldDate,
|
|
F_BillDueDate - interval 7 day as ReminderDate
|
|
from
|
|
f_bill_issue
|
|
join f_bill on F_BillIssueF_BillID = F_BillID
|
|
and F_BillIsActive = 'Y'
|
|
and F_BillIssueIsAllMou = 'Y'
|
|
join f_bill_detail on F_BillIssueF_BillID = F_BillDetailF_BillID
|
|
and F_BillDetailIsActive = 'Y'
|
|
join t_orderheader on F_BillDetailT_OrderHeaderID = T_OrderHeaderID
|
|
and T_OrderHeaderIsActive = 'Y'
|
|
join m_company on M_CompanyID = F_BillIssueM_CompanyID
|
|
join m_city on M_CompanyM_CityID = M_CityID
|
|
join m_mou on F_BillIssueM_MouID = M_MouID and M_MouM_BillTypeID <> 1
|
|
left join keu_reminder
|
|
on F_BillIssueID = KeuReminderF_BillIssueID
|
|
and KeuReminderType = '01'
|
|
where
|
|
F_BillIssueIsActive = 'Y'
|
|
and F_BillIssueIsLunas = 'N'
|
|
and F_BillIssueIsReceive = 'Y'
|
|
and F_BillDueDate - interval 7 DAY = date(now())
|
|
and F_BillIssueExtendDueDate is null
|
|
group by F_BillIssueID
|
|
";
|
|
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Get On Hold Email | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$result = [];
|
|
foreach ($rows as $r) {
|
|
if ($r["KeuReminderStatus"] != "N") {
|
|
continue;
|
|
}
|
|
if ($r["today"] == "Y" && $r["KeuReminderStatus"] != "N") {
|
|
continue;
|
|
}
|
|
|
|
if ($r["F_BillIssuePICEmail"] == "") {
|
|
$result[] = [
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"],
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"ErrorMessage" => "Invalid M_MouFinanceEmail",
|
|
"KeuReminderID" => 0,
|
|
];
|
|
continue;
|
|
}
|
|
$emailMessage = $this->compose_email(1, $config, $r);
|
|
$img = [];
|
|
if ($r["F_BillIssueImg"] != "") {
|
|
$img[] =
|
|
"http://localhost/one-media/one-photo/" .
|
|
$r["F_BillIssueImg"];
|
|
}
|
|
$keuReminderID = $this->create_log(
|
|
$r["F_BillIssueID"],
|
|
$r["F_BillIssuePICEmail"]
|
|
);
|
|
$result[] = [
|
|
"ErrorMessage" => "",
|
|
"KeuReminderID" => $keuReminderID,
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"] . "/RE/01",
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"email" => $r["F_BillIssuePICEmail"],
|
|
"message" => $emailMessage,
|
|
"attachment" => $img,
|
|
];
|
|
}
|
|
$this->response["status"] = "OK";
|
|
$this->response["data"] = $result;
|
|
$this->response["config"] = $config;
|
|
$this->reply();
|
|
}
|
|
public function xdate($str)
|
|
{
|
|
return date("d M Y", strtotime($str));
|
|
}
|
|
//TODO:
|
|
public function second_reminder()
|
|
{
|
|
$config = $this->get_config();
|
|
$sql = "select
|
|
F_BillIssueID,
|
|
F_BillNo,
|
|
F_BillIssueNumber,
|
|
F_BillIssueRefNumber,
|
|
F_BillIssueDate,
|
|
F_BillIssueReceiveDate,
|
|
min(date(T_OrderHeaderDate)) StartOrderDate,
|
|
max(date(T_OrderHeaderDate)) EndOrderDate,
|
|
(F_BillTotal) TotalAmount,
|
|
date(F_BillIssueReceiveDate + interval F_BillIssueAgingDay day) DueDate,
|
|
M_CompanyName,
|
|
M_CompanyAddress,
|
|
M_CityName,
|
|
F_BillIssuePIC,
|
|
F_BillIssueImg,
|
|
F_BillIssuePICEmail,
|
|
ifnull(KeuReminderStatus , 'N') KeuReminderStatus,
|
|
if( date(KeuReminderCreated) = date(now()) , 'Y' , 'N') today,
|
|
fn_IntegerToWords((F_BillTotal)) Terbilang_en,
|
|
f_terbilang((F_BillTotal)) Terbilang_id,
|
|
F_BillDueDate + interval 7 day as OnHoldDate,
|
|
F_BillDueDate - interval 7 day as ReminderDate
|
|
from
|
|
f_bill_issue
|
|
join f_bill on F_BillIssueF_BillID = F_BillID
|
|
and F_BillIsActive = 'Y'
|
|
and F_BillIssueIsAllMou = 'Y'
|
|
join f_bill_detail on F_BillIssueF_BillID = F_BillDetailF_BillID
|
|
and F_BillDetailIsActive = 'Y'
|
|
join t_orderheader on F_BillDetailT_OrderHeaderID = T_OrderHeaderID
|
|
and T_OrderHeaderIsActive = 'Y'
|
|
join m_company on M_CompanyID = F_BillIssueM_CompanyID
|
|
join m_city on M_CompanyM_CityID = M_CityID
|
|
join f_bill_mou on F_BillID = F_BillMouF_BillID
|
|
and F_BillMouIsActive = 'Y'
|
|
join m_mou on F_BillMouM_MouID = M_MouID
|
|
and M_MouM_BillTypeID <> 1
|
|
left join keu_reminder
|
|
on F_BillIssueID = KeuReminderF_BillIssueID
|
|
and KeuReminderType = '02'
|
|
where
|
|
F_BillIssueIsActive = 'Y'
|
|
and F_BillIssueIsLunas = 'N'
|
|
and F_BillIssueIsReceive = 'Y'
|
|
and F_BillDueDate = date(now())
|
|
and F_BillIssueExtendDueDate is null
|
|
group by F_BillIssueID
|
|
";
|
|
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Get On Hold Email | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$result = [];
|
|
foreach ($rows as $r) {
|
|
if ($r["KeuReminderStatus"] != "N") {
|
|
continue;
|
|
}
|
|
if ($r["today"] == "Y" && $r["KeuReminderStatus"] != "N") {
|
|
continue;
|
|
}
|
|
|
|
if ($r["F_BillIssuePICEmail"] == "") {
|
|
$result[] = [
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"],
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"ErrorMessage" => "Invalid M_MouFinanceEmail",
|
|
"KeuReminderID" => 0,
|
|
];
|
|
continue;
|
|
}
|
|
$emailMessage = $this->compose_email(2, $config, $r);
|
|
$img = [];
|
|
if ($r["F_BillIssueImg"] != "") {
|
|
$img[] =
|
|
"http://localhost/one-media/one-photo/" .
|
|
$r["F_BillIssueImg"];
|
|
}
|
|
$keuReminderID = $this->create_log(
|
|
$r["F_BillIssueID"],
|
|
$r["F_BillIssuePICEmail"],
|
|
"02"
|
|
);
|
|
$result[] = [
|
|
"ErrorMessage" => "",
|
|
"KeuReminderID" => $keuReminderID,
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"] . "/RE/01",
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"email" => $r["F_BillIssuePICEmail"],
|
|
"message" => $emailMessage,
|
|
"attachment" => $img,
|
|
];
|
|
}
|
|
$this->response["status"] = "OK";
|
|
$this->response["data"] = $result;
|
|
$this->response["config"] = $config;
|
|
$this->reply();
|
|
}
|
|
|
|
//TODO:
|
|
public function tagihan_awal()
|
|
{
|
|
$config = $this->get_config();
|
|
$sql = "select
|
|
F_BillIssueID,
|
|
F_BillNo,
|
|
F_BillIssueNumber,
|
|
F_BillIssueRefNumber,
|
|
F_BillIssueDate,
|
|
F_BillIssueReceiveDate,
|
|
min(date(T_OrderHeaderDate)) StartOrderDate,
|
|
max(date(T_OrderHeaderDate)) EndOrderDate,
|
|
(F_BillTotal) TotalAmount,
|
|
F_BillDueDate DueDate,
|
|
M_CompanyName,
|
|
M_CompanyAddress,
|
|
M_CityName,
|
|
F_BillIssuePIC,
|
|
F_BillIssueImg,
|
|
F_BillIssuePICEmail,
|
|
ifnull(KeuReminderStatus , 'N') KeuReminderStatus,
|
|
if( date(KeuReminderCreated) = date(now()) , 'Y' , 'N') today,
|
|
fn_IntegerToWords((F_BillTotal)) Terbilang_en,
|
|
f_terbilang((F_BillTotal)) Terbilang_id,
|
|
F_BillIssueReceiveDate + interval (F_BillIssueAgingDay + 7 ) day as OnHoldDate,
|
|
F_BillIssueReceiveDate + interval (F_BillIssueAgingDay - 7 ) day as ReminderDate,
|
|
F_BillIssueImgSend,
|
|
F_BillIssueReceiveDate
|
|
from
|
|
f_bill_issue
|
|
join f_bill on F_BillIssueF_BillID = F_BillID
|
|
and F_BillIsActive = 'Y'
|
|
and F_BillIssueIsAllMou = 'Y'
|
|
join f_bill_detail on F_BillIssueF_BillID = F_BillDetailF_BillID
|
|
and F_BillDetailIsActive = 'Y'
|
|
join t_orderheader on F_BillDetailT_OrderHeaderID = T_OrderHeaderID
|
|
and T_OrderHeaderIsActive = 'Y'
|
|
join m_company on M_CompanyID = F_BillIssueM_CompanyID
|
|
join m_city on M_CompanyM_CityID = M_CityID
|
|
join m_mou on F_BillIssueM_MouID = M_MouID and M_MouM_BillTypeID <> 1
|
|
left join keu_reminder
|
|
on F_BillIssueID = KeuReminderF_BillIssueID
|
|
and KeuReminderType = '03'
|
|
where
|
|
F_BillIssueIsActive = 'Y'
|
|
and F_BillIssueIsNotif = 'Y'
|
|
group by F_BillIssueID
|
|
";
|
|
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Get On Hold Email | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$result = [];
|
|
foreach ($rows as $r) {
|
|
if ($r["KeuReminderStatus"] != "N") {
|
|
continue;
|
|
}
|
|
if ($r["F_BillIssuePICEmail"] == "") {
|
|
$result[] = [
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"],
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"ErrorMessage" => "Invalid M_MouFinanceEmail",
|
|
"KeuReminderID" => 0,
|
|
];
|
|
continue;
|
|
}
|
|
$emailMessage = $this->compose_email(3, $config, $r);
|
|
$img = [];
|
|
if ($r["F_BillIssueImgSend"] != "") {
|
|
$img[] =
|
|
"http://localhost/one-media/one-photo/" .
|
|
$r["F_BillIssueImgSend"];
|
|
}
|
|
if ($r["F_BillIssueImg"] != "") {
|
|
$img[] =
|
|
"http://localhost/one-media/one-photo/" .
|
|
$r["F_BillIssueImg"];
|
|
}
|
|
$keuReminderID = $this->create_log(
|
|
$r["F_BillIssueID"],
|
|
$r["F_BillIssuePICEmail"],
|
|
"03"
|
|
);
|
|
$result[] = [
|
|
"ErrorMessage" => "",
|
|
"KeuReminderID" => $keuReminderID,
|
|
"F_BillIssueID" => $r["F_BillIssueID"],
|
|
"F_BillNo" => $r["F_BillNo"] . "/RE/01",
|
|
"M_CompanyName" => $r["M_CompanyName"],
|
|
"email" => $r["F_BillIssuePICEmail"],
|
|
"message" => $emailMessage,
|
|
"attachment" => $img,
|
|
];
|
|
}
|
|
$this->response["status"] = "OK";
|
|
$this->response["data"] = $result;
|
|
$this->response["config"] = $config;
|
|
$this->reply();
|
|
}
|
|
|
|
public function log()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$sql = "update keu_reminder
|
|
set KeuReminderStatus =? ,
|
|
KeuReminderNote = ? ,
|
|
KeuReminderSendDate = now()
|
|
where KeuReminderID = ?";
|
|
$qry = $this->db->query($sql, [
|
|
$prm["status"],
|
|
$prm["note"],
|
|
$prm["id"],
|
|
]);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Log Keu Reminder | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$this->response["status"] = "OK";
|
|
$this->response["query"] = $this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
|
|
public function log_awal()
|
|
{
|
|
$prm = $this->sys_input;
|
|
$sql = "update f_bill_issue
|
|
set F_BillIssueIsNotif= ?
|
|
where F_BillIssueID = ? ";
|
|
$qry = $this->db->query($sql, [$prm["status"], $prm["id"]]);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Log Keu Reminder | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$this->response["status"] = "OK";
|
|
$this->response["query"] = $this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
|
|
public function create_log($billIssueID, $email, $type = "01")
|
|
{
|
|
$sql = "select KeuReminderID
|
|
from keu_reminder
|
|
where KeuReminderF_BillIssueID=? and KeuReminderStatus = 'N'
|
|
and KeuReminderType=?";
|
|
$qry = $this->db->query($sql, [$billIssueID, $type]);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Check Log Keu Reminder $type | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$total = 0;
|
|
if (count($rows) > 0) {
|
|
return $rows[0]["KeuReminderID"];
|
|
}
|
|
$sql = "insert into keu_reminder (KeuReminderF_BillIssueID, KeuReminderEmail,
|
|
KeuReminderStatus, KeuReminderNote,KeuReminderType)
|
|
values (?,?,'N','New Request',?)";
|
|
$qry = $this->db->query($sql, [$billIssueID, $email, $type]);
|
|
if (!$qry) {
|
|
$this->response["message"] =
|
|
"Error Create Log Keu Reminder | " .
|
|
$this->db->error()["message"] .
|
|
"|" .
|
|
$this->db->last_query();
|
|
$this->reply();
|
|
}
|
|
return $this->db->insert_id();
|
|
}
|
|
}
|