239 lines
8.2 KiB
PHP
239 lines
8.2 KiB
PHP
<?php
|
|
class Api_jpa extends MY_Controller
|
|
{
|
|
function get_branch()
|
|
{
|
|
$sql = "select M_BranchCode from m_branch where M_BranchIsActive ='Y' and M_BranchIsDefault = 'Y'";
|
|
$resp = $this->get_row($sql);
|
|
if ($resp["status"] != 1) {
|
|
echo "No Default Branch";
|
|
exit();
|
|
}
|
|
return $resp["data"]["M_BranchCode"];
|
|
}
|
|
function jpa_doctor($startDate = "", $endDate = "", $format = "html")
|
|
{
|
|
if (trim($startDate) === "") {
|
|
$startDate = date("Y-m-d");
|
|
}
|
|
if (trim($endDate) === "") {
|
|
$endDate = date("Y-m-d");
|
|
}
|
|
$branchCode = $this->get_branch();
|
|
$generatedJpaDoctor = $this->generate_jpa_doctor($startDate, $endDate);
|
|
$result = [];
|
|
$total = 0;
|
|
foreach ($generatedJpaDoctor as $jpa) {
|
|
$total += intval($jpa["amount"]);
|
|
$result[] = [
|
|
"StartDate" => $startDate,
|
|
"EndDate" => $endDate,
|
|
"DoctorID" => $jpa["M_DoctorID"],
|
|
"BranchCode" => $branchCode,
|
|
"DoctorName" => $jpa["M_DoctorName"],
|
|
"DoctorNameWithDegree" => $jpa["fullname"],
|
|
"DoctorCode" => $jpa["M_DoctorCode"],
|
|
"Amount" => $jpa["amount"]
|
|
];
|
|
}
|
|
$result[] = [
|
|
"StartDate" => $startDate,
|
|
"EndDate" => $endDate,
|
|
"DoctorID" => "",
|
|
"BranchCode" => $branchCode,
|
|
"DoctorName" => "Total",
|
|
"DoctorNameWithDegree" => "",
|
|
"DoctorCode" => "",
|
|
"Amount" => $total
|
|
];
|
|
if ($format == "html") {
|
|
$this->print_table_style();
|
|
$this->print_table($result, array_keys($result[0]));
|
|
exit();
|
|
}
|
|
if ($format == "json") {
|
|
$this->sys_ok($result);
|
|
} else {
|
|
$this->print_csv($result, array_keys($result[0]), "jpa_doctor.csv");
|
|
}
|
|
}
|
|
function generate_jpa_doctor($startDate, $endDate)
|
|
{
|
|
$sql = "SELECT sum(T_OrderDetailTotal * Nat_JPADetailDiscount) as amount,
|
|
M_DoctorID,
|
|
M_DoctorCode,
|
|
M_DoctorName,
|
|
CONCAT(
|
|
CASE WHEN M_DoctorPrefix IS NOT NULL AND NULLIF(M_DoctorPrefix, '') IS NOT NULL
|
|
THEN CONCAT(M_DoctorPrefix, ' ')
|
|
ELSE ''
|
|
END,
|
|
CASE WHEN M_DoctorPrefix2 IS NOT NULL AND NULLIF(M_DoctorPrefix2, '') IS NOT NULL
|
|
THEN CONCAT(M_DoctorPrefix2, ' ')
|
|
ELSE ''
|
|
END,
|
|
CONCAT(M_DoctorName, ' '),
|
|
CASE WHEN M_DoctorSufix IS NOT NULL AND NULLIF(M_DoctorSufix, '') IS NOT NULL
|
|
THEN CONCAT(M_DoctorSufix, ' ')
|
|
ELSE ''
|
|
END,
|
|
CASE WHEN M_DoctorSufix2 IS NOT NULL AND NULLIF(M_DoctorSufix2, '') IS NOT NULL
|
|
THEN CONCAT(M_DoctorSufix2, ' ')
|
|
ELSE ''
|
|
END,
|
|
CASE WHEN M_DoctorSufix3 IS NOT NULL AND NULLIF(M_DoctorSufix3, '') IS NOT NULL
|
|
THEN CONCAT(M_DoctorSufix3, ' ')
|
|
ELSE ''
|
|
END
|
|
) AS fullname
|
|
FROM t_orderheader
|
|
JOIN t_orderdetail ON T_OrderHeaderID = T_OrderDetailT_OrderHeaderID
|
|
AND T_OrderHeaderDate BETWEEN '{$startDate} 00:00:00' AND '{$endDate} 23:59:59'
|
|
AND T_OrderDetailIsActive = 'Y'
|
|
JOIN t_test ON T_OrderDetailT_TestID = T_TestID
|
|
AND T_TestIsActive = 'Y'
|
|
JOIN m_doctoraddress ON T_OrderHeaderSenderM_DoctorAddressID = M_DoctorAddressID
|
|
AND M_DoctorAddressIsActive = 'Y'
|
|
JOIN m_doctor ON M_DoctorAddressM_DoctorID = M_DoctorID
|
|
AND M_DoctorIsActive = 'Y' AND M_DoctorName != '-'
|
|
JOIN nat_test ON T_TestCode = Nat_TestCode
|
|
AND Nat_TestIsActive = 'Y'
|
|
JOIN jpa_test ON Nat_TestID = JPA_TestNat_TestID
|
|
AND JPA_TestIsActive = 'Y'
|
|
JOIN nat_jpagroup ON JPA_TestNat_JpaGroupID = Nat_JPAGroupID
|
|
AND Nat_JPAGroupIsActive = 'Y'
|
|
JOIN nat_jpadetail ON Nat_JPAGroupID = Nat_JPADetailNat_JPAGroupID
|
|
AND Nat_JPADetailIsActive = 'Y'
|
|
WHERE T_OrderHeaderIsActive = 'Y'
|
|
GROUP BY M_DoctorName, M_DoctorCode";
|
|
$resp = $this->get_rows($sql);
|
|
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["amount"];
|
|
$r["amount"] = $r["amount"];
|
|
$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 "
|
|
<style>
|
|
th, td {
|
|
padding: 15px;
|
|
text-align: left;
|
|
}
|
|
tr:nth-child(even) {background-color: #f2f2f2;}
|
|
table {
|
|
border: solid 1px ;
|
|
min-width:600px;
|
|
}
|
|
</style>
|
|
";
|
|
}
|
|
function xformat($inp)
|
|
{
|
|
return number_format($inp, 0, "", "");
|
|
}
|
|
public function print_table($rows, $keys, $title = false)
|
|
{
|
|
echo "<table>";
|
|
if ($title) {
|
|
$col_span = count($keys);
|
|
echo "<tr>";
|
|
echo "<th colspan=$col_span>$title</th>";
|
|
echo "</tr>";
|
|
}
|
|
echo "<tr>";
|
|
foreach ($keys as $k) {
|
|
$k = str_replace("|R", "", $k);
|
|
echo "<td>$k</td>";
|
|
}
|
|
echo "</tr>\n";
|
|
foreach ($rows as $r) {
|
|
echo "<tr>";
|
|
foreach ($keys as $k) {
|
|
if (in_array($k, ["Debit", "Debit", "Kredit"])) {
|
|
echo "<td style='text-align:right' >" .
|
|
$this->xformat($r[$k]) .
|
|
"</td>";
|
|
} else {
|
|
echo "<td>" . $r[$k] . "</td>";
|
|
}
|
|
}
|
|
echo "</tr>";
|
|
}
|
|
echo "</table>";
|
|
}
|
|
function print_csv($rows, $header, $file_name = "download.csv")
|
|
{
|
|
//header("Content-type: text/csv");
|
|
//header('Content-Disposition: inline; filename="' . $file_name . '"');
|
|
echo "<pre>";
|
|
$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, ["Amount"])) {
|
|
$line .= $this->xformat($r[$h]);
|
|
} else {
|
|
$line .= $r[$h];
|
|
}
|
|
} else {
|
|
$line .= "\"{$r[$h]}\"";
|
|
}
|
|
}
|
|
echo $line . "\n";
|
|
}
|
|
}
|
|
}
|