db = $this->load->database("default", true);
$this->db->query("use one_dash");
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$userID = $this->sys_user['M_UserID'];
$sql_cek_token = "SELECT M_UserActiveToken
FROM m_user
WHERE M_UserID = ?
AND M_UserActiveToken IS NOT NULL";
$qry_token = $this->db->query($sql_cek_token, [$userID]);
if (!$qry_token) {
$this->sys_error('Invalid token');
exit;
}
$rows_token = $qry_token->result_array();
if (count($rows_token) == 0) {
$this->sys_error('Invalid token');
exit;
}
}
function corss()
{
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST');
header("Access-Control-Allow-Headers: X-Requested-With");
//for preflight
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, PUT, POST, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept');
//for disable cached
header('Last-Modified: ' . gmdate("D, d M Y H:i:s") . ' GMT');
header('Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0');
header('Pragma: no-cache');
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// global $_SERVER;
// header("Access-Control-Allow-Private-Network: true");
// header("Access-Control-Allow-Credentials : true");
// if (isset($_SERVER["HTTP_ORIGIN"])) {
// header("Access-Control-Allow-Origin: " . $_SERVER["HTTP_ORIGIN"]);
// } else {
// header("Access-Control-Allow-Origin: */*");
// }
// header("Access-Control-Allow-Methods: GET, PUT, POST, DELETE, OPTIONS");
// header(
// "Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept, Authorization"
// );
if (
isset($_SERVER["REQUEST_METHOD"]) &&
$_SERVER["REQUEST_METHOD"] == "OPTIONS"
) {
http_response_code(200);
echo json_encode("OK");
exit();
}
}
public function getClassificationNumber($number_param_x)
{
$this->corss();
// $number_fix = 0;
// if ($number_param < 0) {
// $number_fix = $number_param * -1;
// } else {
// $number_fix = $number_param;
// }
$number_param = abs($number_param_x);
$string = "Puluhan";
if ($number_param >= 1000000000) {
// echo "Angka ini dalam miliar";
$number_format = $number_param / 1000000000;
$formatted_number_fix = number_format($number_format, 2);
$string = ($number_param_x >= 0 ? '' : '-') . $formatted_number_fix . " M";
} elseif ($number_param >= 1000000) {
// echo "Angka ini dalam juta";
$number_format = $number_param / 1000000;
$formatted_number_fix = number_format($number_format, 2);
$string = ($number_param_x >= 0 ? '' : '-') . $formatted_number_fix . " Juta";
} elseif ($number_param >= 1000) {
// echo "Angka ini dalam ribuan";
$number_format = $number_param / 1000;
$formatted_number_fix = number_format($number_format, 2);
$string = ($number_param_x >= 0 ? '' : '-') . $formatted_number_fix . " Ribu";
} elseif ($number_param >= 100) {
$number_format = $number_param / 100;
$formatted_number_fix = number_format($number_format, 2);
$string = ($number_param_x >= 0 ? '' : '-') . $formatted_number_fix . " Ratus";
} else {
// echo "Angka ini dalam ratusan";
$number_format = $number_param / 10;
$formatted_number_fix = number_format($number_format, 2);
$string = ($number_param_x >= 0 ? '' : '-') . $formatted_number_fix . " Puluh";
}
return $string;
}
public function list_color_bar_chart($index, $list_scope)
{
$this->corss();
// diurutkan S_RegionalName ASC
$listColor = [
"#D00000",
"#FFBA08",
"#CBFF8C",
"#1B998B",
"#3185FC",
"#5D2E8C",
"#FF7B9C",
"#4D86A5",
"#CF0BF1",
"#12E2F1",
"#DDA1BA",
"#706997",
"#C3C4E9",
"#1A3D84",
"#98B29D",
"#333333",
"#104E53",
"#BC9E68",
"#654E1B",
"#6E1653",
"#F6D67F",
"#FADFDB",
"#FF9B85",
"#8FE388",
"#46237A",
];
// define default color grey jika list scope != listColor
if ($list_scope != count($listColor)) {
if ($list_scope > count($listColor)) {
$greyCount = count($list_scope) - count($listColor);
$greyColors = array_fill(0, $greyCount, "#888888");
$resultColor = array_merge($listColor, $greyColors);
$listColor = $resultColor;
} else {
$listColor[$index];
}
} else {
$listColor[$index];
}
return $listColor[$index];
}
public function list_color_pie_chart($index, $tingkat, $list_data)
{
$this->corss();
// Tingkat 1 diurutkan Lab dan Non Lab
// Tingkat 2 diurutkan Nat_GroupName ASC
// Tingkat 3 diurutkan Nat_SubGroupName ASC
$listColorLabNonLab = [
"#F9D8CC", // lab
"#DF4836", // nonlab
];
$listColorNatGroup = [
"#0E713E", //[Laboratorium] =>
"#49CDF5", // [Elektromedis] =>
"#F9BE42", // [Radiologi] =>
"#930907" //[Layanan Medis lainnya] =>
];
$listColorNatSubGroup = [
"#1BD875", //[Hematologi] =>
"#17B562", //[Urinalisis] =>
"#15A75B", //[Faeces & Parasitologi] =>
"#139A54", //[Analisa Klinik Rutin] =>
"#118D4D", //[Kimia Klinik] =>
"#107F45", //[Immunologi] =>
"#0E713E", //[Mikrobiologi] =>
"#0C6437", //[Molekuler Diagnostik] =>
"#0A5730", //[Pathologi Anatomi] =>
"#094929", //[Pemeriksaan Lab Lainnya] =>
"#052E1A", //[Riset] =>
"#92DAF2", //[Pemeriksaan Syaraf] =>
"#73CFED", //[Elektromedis Jantung & Pembuluh Darah] =>
"#53C5EA", //[Fungsi Pendengaran] =>
"#41BFE7", //[Fungsi Paru-paru] =>
'#1BABDA', //[Otot] =>
"#F4DBA4", //[Foto Polos] =>
"#EEC877", //[Panoramic & Dental] =>
"#EBBF60", //[Foto Kontras] =>
"#E6AD33", //[Bonedensitometri] =>
"#DEA11B", //[CT Scan] =>
"#B68416", //[USG] =>
"#916912", //[MRI] =>
"#ED7F6E", //[Layanan Klinik] =>
"#E95F49", //[Pemeriksaan K3] =>
"#E43F25", //[Vaksin] =>
"#CC3219", //[Layanan Alat & Farmasi] =>
"#912312", //[Layanan Jasa] =>
"#64180C", //[Dental Lab] =>
];
// define default color grey jika list scope != listColor
// nonlab & lab
if (($list_data != count($listColorLabNonLab))) {
if (($list_data > count($listColorLabNonLab))) {
$greyCount = $list_data - count($listColorLabNonLab);
$greyColors = array_fill(0, $greyCount, "#888888");
$resultColor = array_merge($listColorLabNonLab, $greyColors);
$listColorLabNonLab = $resultColor;
}
}
// nat group
if (($list_data != count($listColorNatGroup))) {
if (($list_data > count($listColorNatGroup))) {
$greyCount = $list_data - count($listColorNatGroup);
$greyColors = array_fill(0, $greyCount, "#888888");
$resultColor = array_merge($listColorNatGroup, $greyColors);
$listColorNatGroup = $resultColor;
}
}
// nat sub group
if (($list_data != count($listColorNatSubGroup))) {
if (($list_data > count($listColorNatSubGroup))) {
$greyCount = $list_data - count($listColorNatSubGroup);
$greyColors = array_fill(0, $greyCount, "#888888");
$resultColor = array_merge($listColorNatSubGroup, $greyColors);
$listColorNatSubGroup = $resultColor;
}
}
// if (
// ($list_data != count($listColorLabNonLab)) ||
// ($list_data != count($listColorNatGroup)) ||
// ($list_data != count($listColorNatSubGroup))
// ) {
// if (($list_data > count($listColorLabNonLab)) ||
// ($list_data > count($listColorNatGroup)) ||
// ($list_data > count($listColorNatSubGroup))
// ) {
// $greyCount = $list_data - count($listColorLabNonLab);
// $greyColors = array_fill(0, $greyCount, "#888888");
// $resultColor = array_merge($listColorLabNonLab, $greyColors);
// $listColorLabNonLab = $resultColor;
// $listColorNatGroup = $resultColor;
// $listColorNatSubGroup = $resultColor;
// }
// }
if ($tingkat == 'tingkat1') {
return $listColorLabNonLab[$index];
} else {
if ($tingkat == 'tingkat2') {
return $listColorNatGroup[$index];
} else {
if ($tingkat == 'tingkat3') {
return $listColorNatSubGroup[$index];
}
}
}
}
public function formatAngkaJuta($angka)
{
return "Rp " . number_format($angka / 1000000, 2, ',', '.') . ' Juta';
}
public function formatAngkaJutaTanpaAkhiran($angka)
{
// return "Rp " . number_format($angka / 1000000, 2, ',', '.');
$formattedAngka = number_format($angka, 2, ',', '.');
// Mengganti koma menjadi titik untuk memisahkan ribuan
// $formattedAngka = str_replace(',', '.', $formattedAngka);
return "Rp " . $formattedAngka;
}
public function formatAngkaPemeriksaanPieChart($angka)
{
if ($angka >= 1000000000) {
// Lebih dari satu miliar
return number_format($angka / 1000000000, 2, ',', '.') . ' Miliar';
} elseif ($angka >= 1000000) {
// Lebih dari satu juta
return number_format($angka / 1000000, 2, ',', '.') . ' Juta';
} elseif ($angka >= 1000) {
// Lebih dari seribu
return number_format($angka / 1000, 2, ',', '.') . ' Ribu';
} else {
// Kurang dari seribu
return number_format($angka, 2, ',', '.');
}
}
public function formatAngkaPemeriksaanPieChartTanpaAkhiran($angka)
{
if ($angka >= 1000000000) {
// Lebih dari satu miliar
// return number_format($angka / 1000000000, 2, ',', '.');
return number_format($angka, 2, ',', '.');
} elseif ($angka >= 1000000) {
// Lebih dari satu juta
// return number_format($angka / 1000000, 2, ',', '.');
return number_format($angka, 2, ',', '.');
} elseif ($angka >= 1000) {
// Lebih dari seribu
// return number_format($angka / 1000, 2, ',', '.');
return number_format($angka, 2, ',', '.');
} else {
// Kurang dari seribu
return number_format($angka, 2, ',', '.');
}
}
public function getQuarterByFilter($waktu)
{
if ($waktu == "Q1") {
$quarterResult = "1";
} elseif ($waktu == "Q2") {
$quarterResult = "2";
} elseif ($waktu == "Q3") {
$quarterResult = "3";
} elseif ($waktu == "Q4") {
$quarterResult = "4";
} else {
$quarterResult = "";
}
$result = $quarterResult;
return $result;
}
// national start
public function nat_total_count_order_xold1($debug = "")
{
$this->corss();
// total order = count nat_sales
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
$end_date_year_min_1 = date('Y-m-d', strtotime($date_now . ' -1 year'));
// $param = $this->sys_input;
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(count(*),0) AS total_order_tahun_berjalan
FROM `nat_sales`
WHERE `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_tahun_berjalan = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_tahun_berjalan;
// }
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
// tahun min 1
// sebagai b
$sql_year_min_1 = "
SELECT ifnull(count(*),0) AS total_order_year_min_1
FROM `nat_sales`
WHERE `Nat_SalesYear` = year('$date_now') - 1
AND Nat_SalesDay <= '$end_date_year_min_1'
AND Nat_SalesIsActive = 'Y'
";
$qry_year_min_1 = $this->db->query($sql_year_min_1);
if (!$qry_year_min_1) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_year_min_1 = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_year_min_1;
// }
$rst_year_min_1 = $qry_year_min_1->result_array();
// diff dalam persen
// diff sales = a - b
$diff_total_order = ($rst_tahun_berjalan[0]['total_order_tahun_berjalan']) - ($rst_year_min_1[0]['total_order_year_min_1']);
$diff_persen = 0.0;
$keterangan = "-";
$formatted_number = $this->getClassificationNumber($diff_total_order);
// kalau tahun lalu 0 maka 100%
if (($rst_year_min_1[0]['total_order_year_min_1']) == 0) {
$diff_persen = 100;
} else {
// $persentase = (($tahun_sekarang - $tahun_lalu) / $tahun_lalu) * 100;
$diff_persen_calculate = ($diff_total_order / ($rst_year_min_1[0]['total_order_year_min_1'])) * 100;
$diff_persen = number_format($diff_persen_calculate, 0);
}
// set keterangan & prefix
if (($rst_year_min_1[0]['total_order_year_min_1']) < 1) {
$keterangan = "-";
} else {
if (($rst_tahun_berjalan[0]['total_order_tahun_berjalan']) > ($rst_year_min_1[0]['total_order_year_min_1'])) {
$keterangan = "up";
} else {
if (($rst_year_min_1[0]['total_order_year_min_1']) > ($rst_tahun_berjalan[0]['total_order_tahun_berjalan'])) {
$keterangan = "down";
}
}
}
$total_count_order = $formatted_number;
$result = array(
"total" => $total_count_order,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan
);
if ($debug != "") {
$result = array(
"total_asli" => $diff_total_order,
"total" => $total_count_order,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total_xold1($debug = "")
{
$this->corss();
// total pendapatan nat_sales
// *) di tambah dengan tahun - 1
// di berikan diff dalam persen , jika sebelumnya 0 ( belum ada data ) di kasih 100
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
$end_date_year_min_1 = date('Y-m-d', strtotime($date_now . ' -1 year'));
// $param = $this->sys_input;
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan
FROM `nat_sales`
WHERE `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_tahun_berjalan = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_tahun_berjalan;
// }
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
// tahun min 1
// sebagai b
$sql_year_min_1 = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_year_min_1
FROM `nat_sales`
WHERE `Nat_SalesYear` = year('$date_now') - 1
AND Nat_SalesDay <= '$end_date_year_min_1'
AND Nat_SalesIsActive = 'Y'
";
$qry_year_min_1 = $this->db->query($sql_year_min_1);
if (!$qry_year_min_1) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_year_min_1 = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_year_min_1;
// }
$rst_year_min_1 = $qry_year_min_1->result_array();
// diff dalam persen
// diff sales = a - b
$diff_total_sales = ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) - ($rst_year_min_1[0]['total_sales_year_min_1']);
$diff_persen = 0.0;
$keterangan = "-";
$formated_number = $this->getClassificationNumber($diff_total_sales);
// kalau tahun lalu 0 maka 100%
if (($rst_year_min_1[0]['total_sales_year_min_1']) == 0) {
$diff_persen = 100;
} else {
// $persentase = (($tahun_sekarang - $tahun_lalu) / $tahun_lalu) * 100;
$diff_persen_calculate = ($diff_total_sales / ($rst_year_min_1[0]['total_sales_year_min_1'])) * 100;
$diff_persen = number_format($diff_persen_calculate, 0);
}
// set keterangan & prefix
if (($rst_year_min_1[0]['total_sales_year_min_1']) < 1) {
$keterangan = "-";
} else {
if (($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) > ($rst_year_min_1[0]['total_sales_year_min_1'])) {
$keterangan = "up";
} else {
if (($rst_year_min_1[0]['total_sales_year_min_1']) > ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan'])) {
$keterangan = "down";
}
}
}
$total_pendapatan_fix = "Rp. " . $formated_number;
$result = array(
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan
);
if ($debug != "") {
$result = array(
"total_reformat" => $diff_total_sales,
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_nat_subgroup_xold1($date_now)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
// NAT_SUBGROUP
$sql_natsubgroup = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
CONCAT(Nat_SubGroupNat_GroupID,Nat_SubGroupCode) as code,
Nat_SubGroupID, Nat_SubGroupName, '' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
join nat_subgroup
ON Nat_SalesNat_SubGroupID = Nat_SubGroupID
AND Nat_SubGroupIsActive = 'Y'
GROUP BY Nat_SalesNat_SubGroupID
order by Nat_GroupCode ASC, Nat_SubGroupCode ASC";
$qry_natsubgroup = $this->db->query($sql_natsubgroup);
if (!$qry_natsubgroup) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_natsubgroup = $qry_natsubgroup->result_array();
$last_qry_natsubgroup = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_natsubgroup = 0.0;
// calculate value persen
if (count($rst_natsubgroup) > 0) {
foreach ($rst_natsubgroup as $key => $value) {
$sum_natsubgroup += $value['total_sales_tahun_berjalan'];
}
}
$total_value = $sum_natsubgroup;
if (count($rst_natsubgroup) > 0) {
foreach ($rst_natsubgroup as $key => $value) {
$persen_natsubgroup = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$result[] = [
"code" => $value['code'],
"name" => $value['Nat_SubGroupName'],
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_natsubgroup . ' %',
"rst_natsubgroup" => $rst_natsubgroup,
"last_qry_natsubgroup" => $last_qry_natsubgroup,
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_nat_group_xold1($date_now)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
// NAT_GROUP
$sql_natgroup = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
Nat_GroupCode,
Nat_GroupID, Nat_GroupName, '' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
GROUP BY Nat_SalesNat_GroupID
order by Nat_GroupCode ASC";
$qry_natgroup = $this->db->query($sql_natgroup);
if (!$qry_natgroup) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_natgroup = $qry_natgroup->result_array();
$last_qry_natgroup = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_natgroup = 0.0;
// calculate value persen
if (count($rst_natgroup) > 0) {
foreach ($rst_natgroup as $key => $value) {
$sum_natgroup += $value['total_sales_tahun_berjalan'];
}
}
$total_value = $sum_natgroup;
if (count($rst_natgroup) > 0) {
foreach ($rst_natgroup as $key => $value) {
$persen_natgroup = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$result[] = [
"code" => $value['Nat_GroupCode'],
"name" => $value['Nat_GroupName'],
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_natgroup . ' %',
"rst_natgroup" => $rst_natgroup,
"last_qry_natgroup" => $last_qry_natgroup,
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_lab_nonlab_xold1($date_now)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
// LAB
$sql_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
'' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
AND Nat_SalesNat_GroupID IN (1)";
$qry_lab = $this->db->query($sql_lab);
if (!$qry_lab) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_lab = $qry_lab->result_array();
$last_qry_lab = $this->db->last_query();
// NON LAB
$sql_non_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
'' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
AND Nat_SalesNat_GroupID NOT IN (1)";
$qry_non_lab = $this->db->query($sql_non_lab);
if (!$qry_non_lab) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_non_lab = $qry_non_lab->result_array();
$last_qry_non_lab = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_lab = 0.0;
$sum_non_lab = 0.0;
// calculate value persen
if (count($rst_lab) > 0) {
foreach ($rst_lab as $key => $value) {
$sum_lab += $value['total_sales_tahun_berjalan'];
}
}
if (count($rst_non_lab) > 0) {
foreach ($rst_non_lab as $key => $value) {
$sum_non_lab += $value['total_sales_tahun_berjalan'];
}
}
$total_value = $sum_lab + $sum_non_lab;
if (count($rst_lab) > 0) {
foreach ($rst_lab as $key => $value) {
$persen_lab = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$result[] = [
"code" => "1",
"name" => "Laboratorium",
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_lab . ' %',
"rst_lab" => $rst_lab,
"last_qry_lab" => $last_qry_lab,
];
}
}
if (count($rst_non_lab) > 0) {
foreach ($rst_non_lab as $key => $value) {
$persen_non_lab = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$result[] = [
"code" => "2",
"name" => "Klinik",
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_non_lab . ' %',
"rst_non_lab" => $rst_non_lab,
"last_qry_non_lab" => $last_qry_non_lab
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total_count_order($debug = "", $filter_scope_debug = "", $filter_waktu_debug = "")
{
$this->corss();
// total order = count nat_sales
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$filter_scope = "";
$filter_waktu = "";
// untuk debug
if ($debug != "") {
// $filter_scope = $filter_scope_debug;
$filter_scope = ($filter_scope_debug == "0" || $filter_scope_debug == 0) ? "-" : $filter_scope_debug;
$filter_waktu = $filter_waktu_debug;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
$date_now = date('Y-m-d');
$end_date_year_min_1 = date('Y-m-d', strtotime($date_now . ' -1 year'));
$where_tahun_berjalan = " `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y' ";
$where_year_min_1 = " `Nat_SalesYear` = year('$date_now') - 1
AND Nat_SalesDay <= '$end_date_year_min_1'
AND Nat_SalesIsActive = 'Y' ";
$join_filter_tahun_berjalan = "";
$join_filter_end_date_year_min_1 = "";
// filter start
if ($filter_scope != "-") {
$join_filter_tahun_berjalan = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
$join_filter_end_date_year_min_1 = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
// menentukan apakah filter Quarter ?
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_tahun_berjalan .= " AND Nat_SalesQuarter = ($quarterResult)";
$where_year_min_1 .= " AND Nat_SalesQuarter = ($quarterResult)";
}
}
} else {
if ($filter_waktu == 'YTD') {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
$end_date_a = date('Y-01-01', strtotime('-1 year'));
$end_date_b = date('Y-m-d', strtotime('-1 year'));
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_tahun_berjalan = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')
";
// end date year - 1
// Nat_SalesDay >= '2022-01-01' AND Nat_SalesDay <= '2022-10-04'
$where_year_min_1 = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now') - 1
AND (`Nat_SalesDay` >= '$end_date_a' AND `Nat_SalesDay` <= DATE(NOW() - INTERVAL 1 YEAR))";
}
}
}
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(count(*),0) AS total_order_tahun_berjalan
FROM `nat_sales`
$join_filter_tahun_berjalan
WHERE $where_tahun_berjalan
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_tahun_berjalan = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_tahun_berjalan;
// }
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
// tahun min 1
// sebagai b
$sql_year_min_1 = "
SELECT ifnull(count(*),0) AS total_order_year_min_1
FROM `nat_sales`
$join_filter_end_date_year_min_1
WHERE $where_year_min_1
";
$qry_year_min_1 = $this->db->query($sql_year_min_1);
if (!$qry_year_min_1) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_year_min_1 = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_year_min_1;
// }
$rst_year_min_1 = $qry_year_min_1->result_array();
// diff dalam persen
// diff sales = a - b
$diff_total_order = ($rst_tahun_berjalan[0]['total_order_tahun_berjalan']) - ($rst_year_min_1[0]['total_order_year_min_1']);
$diff_persen = 0.0;
$keterangan = "-";
// $formatted_number = $this->getClassificationNumber($diff_total_order);
$formatted_number = $this->getClassificationNumber($rst_tahun_berjalan[0]['total_order_tahun_berjalan']);
// kalau tahun lalu 0 maka 100%
if (($rst_year_min_1[0]['total_order_year_min_1']) == 0) {
$diff_persen = 100;
} else {
// $persentase = (($tahun_sekarang - $tahun_lalu) / $tahun_lalu) * 100;
$diff_persen_calculate = ($diff_total_order / ($rst_year_min_1[0]['total_order_year_min_1'])) * 100;
$diff_persen = number_format($diff_persen_calculate, 0);
}
// set keterangan & prefix
if (($rst_year_min_1[0]['total_order_year_min_1']) < 1) {
$keterangan = "-";
} else {
if (($rst_tahun_berjalan[0]['total_order_tahun_berjalan']) > ($rst_year_min_1[0]['total_order_year_min_1'])) {
$keterangan = "up";
} else {
if (($rst_year_min_1[0]['total_order_year_min_1']) > ($rst_tahun_berjalan[0]['total_order_tahun_berjalan'])) {
$keterangan = "down";
}
}
}
$total_count_order = $formatted_number;
$result = array(
"total" => $total_count_order,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan
);
if ($debug != "") {
$result = array(
"total_asli" => $rst_tahun_berjalan[0]['total_order_tahun_berjalan'],
"total" => $total_count_order,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total_oldv1($debug = "", $filter_scope_debug = "", $filter_waktu_debug = "")
{
$this->corss();
// total pendapatan nat_sales
// *) di tambah dengan tahun - 1
// di berikan diff dalam persen , jika sebelumnya 0 ( belum ada data ) di kasih 100
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$filter_scope = "";
$filter_waktu = "";
// untuk debug
if ($debug != "") {
// $filter_scope = $filter_scope_debug;
$filter_scope = ($filter_scope_debug == "0" || $filter_scope_debug == 0) ? "-" : $filter_scope_debug;
$filter_waktu = $filter_waktu_debug;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
$date_now = date('Y-m-d');
$end_date_year_min_1 = date('Y-m-d', strtotime($date_now . ' -1 year'));
$where_tahun_berjalan = " `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y' ";
$where_year_min_1 = " `Nat_SalesYear` = year('$date_now') - 1
AND Nat_SalesDay <= '$end_date_year_min_1'
AND Nat_SalesIsActive = 'Y' ";
$join_filter_tahun_berjalan = "";
$join_filter_end_date_year_min_1 = "";
// filter start
if ($filter_scope != "-") {
$join_filter_tahun_berjalan = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
$join_filter_end_date_year_min_1 = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_tahun_berjalan .= " AND Nat_SalesQuarter = ($quarterResult)";
$where_year_min_1 .= " AND Nat_SalesQuarter = ($quarterResult)";
}
}
} else {
if ($filter_waktu == 'YTD') {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
$end_date_a = date('Y-01-01', strtotime('-1 year'));
$end_date_b = date('Y-m-d', strtotime('-1 year'));
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_tahun_berjalan = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')
";
// end date year - 1
// Nat_SalesDay >= '2022-01-01' AND Nat_SalesDay <= '2022-10-04'
$where_year_min_1 = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now') - 1
AND (`Nat_SalesDay` >= '$end_date_a' AND `Nat_SalesDay` <= DATE(NOW() - INTERVAL 1 YEAR))";
}
}
}
// $param = $this->sys_input;
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan
FROM `nat_sales`
$join_filter_tahun_berjalan
WHERE $where_tahun_berjalan
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_tahun_berjalan = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_tahun_berjalan;
// }
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
// tahun min 1
// sebagai b
$sql_year_min_1 = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_year_min_1
FROM `nat_sales`
$join_filter_end_date_year_min_1
WHERE $where_year_min_1
";
$qry_year_min_1 = $this->db->query($sql_year_min_1);
if (!$qry_year_min_1) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_year_min_1 = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_year_min_1;
// }
$rst_year_min_1 = $qry_year_min_1->result_array();
// diff dalam persen
// diff sales = a - b
$diff_total_sales = ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) - ($rst_year_min_1[0]['total_sales_year_min_1']);
$diff_persen = 0.0;
$keterangan = "-";
// $formated_number = $this->getClassificationNumber($diff_total_sales);
$formated_number = $this->getClassificationNumber($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']);
// kalau tahun lalu 0 maka 100%
if (($rst_year_min_1[0]['total_sales_year_min_1']) == 0) {
$diff_persen = 100;
} else {
// $persentase = (($tahun_sekarang - $tahun_lalu) / $tahun_lalu) * 100;
$diff_persen_calculate = ($diff_total_sales / ($rst_year_min_1[0]['total_sales_year_min_1'])) * 100;
$diff_persen = number_format($diff_persen_calculate, 0);
}
// set keterangan & prefix
if (($rst_year_min_1[0]['total_sales_year_min_1']) < 1) {
$keterangan = "-";
} else {
if (($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) > ($rst_year_min_1[0]['total_sales_year_min_1'])) {
$keterangan = "up";
} else {
if (($rst_year_min_1[0]['total_sales_year_min_1']) > ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan'])) {
$keterangan = "down";
}
}
}
$total_pendapatan_fix = "Rp. " . $formated_number;
$result = array(
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
if ($debug != "") {
$result = array(
"total_reformat" => $rst_tahun_berjalan[0]['total_sales_tahun_berjalan'],
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total($debug = "", $filter_scope_debug = "", $filter_waktu_debug = "")
{
$this->corss();
// total pendapatan nat_sales
// *) di tambah dengan tahun - 1
// di berikan diff dalam persen , jika sebelumnya 0 ( belum ada data ) di kasih 100
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$filter_scope = "";
$filter_waktu = "";
// untuk debug
if ($debug != "") {
// $filter_scope = $filter_scope_debug;
$filter_scope = ($filter_scope_debug == "0" || $filter_scope_debug == 0) ? "-" : $filter_scope_debug;
$filter_waktu = $filter_waktu_debug;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
$date_now = date('Y-m-d');
$end_date_year_min_1 = date('Y-m-d', strtotime($date_now . ' -1 year'));
$where_tahun_berjalan = " `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y' ";
$where_year_min_1 = " `Nat_SalesYear` = year('$date_now') - 1
AND Nat_SalesDay <= '$end_date_year_min_1'
AND Nat_SalesIsActive = 'Y' ";
$join_filter_tahun_berjalan = "
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
";
$join_filter_end_date_year_min_1 = "
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
";
// filter start
if ($filter_scope != "-") {
$join_filter_tahun_berjalan = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
$join_filter_end_date_year_min_1 = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_tahun_berjalan .= " AND Nat_SalesQuarter = ($quarterResult)";
$where_year_min_1 .= " AND Nat_SalesQuarter = ($quarterResult)";
}
}
} else {
if ($filter_waktu == 'YTD') {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
$end_date_a = date('Y-01-01', strtotime('-1 year'));
$end_date_b = date('Y-m-d', strtotime('-1 year'));
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_tahun_berjalan = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')
";
// end date year - 1
// Nat_SalesDay >= '2022-01-01' AND Nat_SalesDay <= '2022-10-04'
$where_year_min_1 = " `Nat_SalesIsActive` = 'Y'
AND `Nat_SalesYear` = year('$date_now') - 1
AND (`Nat_SalesDay` >= '$end_date_a' AND `Nat_SalesDay` <= DATE(NOW() - INTERVAL 1 YEAR))";
}
}
}
// $param = $this->sys_input;
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan
FROM `nat_sales`
$join_filter_tahun_berjalan
WHERE $where_tahun_berjalan
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_tahun_berjalan = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_tahun_berjalan;
// }
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
// tahun min 1
// sebagai b
$sql_year_min_1 = "
SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_year_min_1
FROM `nat_sales`
$join_filter_end_date_year_min_1
WHERE $where_year_min_1
";
$qry_year_min_1 = $this->db->query($sql_year_min_1);
if (!$qry_year_min_1) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry_year_min_1 = $this->db->last_query();
// if($debug != ""){
// echo $last_qry_year_min_1;
// }
$rst_year_min_1 = $qry_year_min_1->result_array();
// diff dalam persen
// diff sales = a - b
$diff_total_sales = ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) - ($rst_year_min_1[0]['total_sales_year_min_1']);
$diff_persen = 0.0;
$keterangan = "-";
// $formated_number = $this->getClassificationNumber($diff_total_sales);
$formated_number = $this->getClassificationNumber($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']);
// kalau tahun lalu 0 maka 100%
if (($rst_year_min_1[0]['total_sales_year_min_1']) == 0) {
$diff_persen = 100;
} else {
// $persentase = (($tahun_sekarang - $tahun_lalu) / $tahun_lalu) * 100;
$diff_persen_calculate = ($diff_total_sales / ($rst_year_min_1[0]['total_sales_year_min_1'])) * 100;
$diff_persen = number_format($diff_persen_calculate, 0);
}
// set keterangan & prefix
if (($rst_year_min_1[0]['total_sales_year_min_1']) < 1) {
$keterangan = "-";
} else {
if (($rst_tahun_berjalan[0]['total_sales_tahun_berjalan']) > ($rst_year_min_1[0]['total_sales_year_min_1'])) {
$keterangan = "up";
} else {
if (($rst_year_min_1[0]['total_sales_year_min_1']) > ($rst_tahun_berjalan[0]['total_sales_tahun_berjalan'])) {
$keterangan = "down";
}
}
}
$total_pendapatan_fix = "Rp. " . $formated_number;
$result = array(
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
if ($debug != "") {
$result = array(
"total_reformat" => $rst_tahun_berjalan[0]['total_sales_tahun_berjalan'],
"total" => $total_pendapatan_fix,
"persen" => $diff_persen . " %",
"keterangan" => $keterangan,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
"last_qry_year_min_1" => $last_qry_year_min_1
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total_pendapatan_bar_chart_no_group_oldv1($debug = "", $filter_scope_debug = "", $filter_waktu_debug = "")
{
$this->corss();
// total pendapatan nat_sales
// *) di tambah dengan tahun - 1
// di berikan diff dalam persen , jika sebelumnya 0 ( belum ada data ) di kasih 100
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
// $param = $this->sys_input;
$filter_scope = "";
$filter_waktu = "";
$where_filter_scope = "";
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = YEAR('$date_now')";
// untuk debug
if ($debug != "") {
// $filter_scope = $filter_scope_debug;
$filter_scope = ($filter_scope_debug == "0" || $filter_scope_debug == 0) ? "-" : $filter_scope_debug;
$filter_waktu = $filter_waktu_debug;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
// filter start
if ($filter_scope !== "-") {
$where_filter_scope = " AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND Nat_SalesQuarter = ($quarterResult)
AND `Nat_SalesYear` = year('$date_now')";
}
}
} else {
if ($filter_waktu == "YTD") {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
}
}
}
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(SUM(`Nat_SalesTotal`), 0) AS total_sales_tahun_berjalan,
S_RegionalID, S_RegionalName
FROM `nat_sales`
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
$where_tahun_berjalan
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
$where_filter_scope
GROUP BY S_RegionalID
UNION ALL
SELECT 0 AS total_sales_tahun_berjalan,
S_RegionalID, S_RegionalName
FROM s_regional
WHERE S_RegionalIsActive = 'Y'
AND S_RegionalID NOT IN (
SELECT
S_RegionalID
FROM `nat_sales`
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
$where_tahun_berjalan
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
$where_filter_scope
GROUP BY S_RegionalID
)
ORDER BY S_RegionalName ASC
";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
$last_qry_tahun_berjalan = $this->db->last_query();
$list_scope = [];
$value_of_scope = [];
$result = [];
$list_color = [];
if (count($rst_tahun_berjalan) > 0) {
for ($i = 0; $i < count($rst_tahun_berjalan); $i++) {
$value_of_scope[] = $rst_tahun_berjalan[$i]['total_sales_tahun_berjalan'];
$list_scope[] = $rst_tahun_berjalan[$i]['S_RegionalName'];
$list_color[] = $this->list_color_bar_chart($i, count($list_scope));
}
// // TESTING COLOR DEFAULT
// $x1 = [
// 'Bali Raya',
// 'Balikpapan Raya',
// 'Bandung Raya',
// 'Cirebon Raya',
// 'Jakarta Raya',
// 'Madiun Raya',
// 'Magelang Raya',
// 'Makassar Raya',
// 'Manado Raya',
// 'Medan Raya',
// 'Padang Raya',
// 'Palembang Raya',
// 'Pekanbaru Raya',
// 'Pontianak Raya',
// 'Prospek',
// 'Salatiga Raya',
// 'Semarang Raya',
// 'Sima Jember',
// 'Sima Kediri',
// 'Sima Malang',
// 'Surabaya Raya',
// 'Tasik Raya',
// 'Tegal Raya',
// 'Yogyaraya',
// 'Bali Raya',
// 'Balikpapan Raya',
// 'Bandung Raya',
// 'Cirebon Raya',
// 'Jakarta Raya',
// 'Madiun Raya',
// 'Magelang Raya',
// 'Makassar Raya',
// 'Manado Raya',
// 'Medan Raya',
// 'Padang Raya',
// 'Palembang Raya',
// 'Pekanbaru Raya',
// 'Pontianak Raya',
// 'Prospek',
// 'Salatiga Raya',
// 'Semarang Raya',
// 'Sima Jember',
// 'Sima Kediri',
// 'Sima Malang',
// 'Surabaya Raya',
// 'Tasik Raya',
// 'Tegal Raya',
// 'Yogyaraya',
// ];
// for ($i = 0; $i < count($x1); $i++) {
// // $value_of_scope[] = $rst_tahun_berjalan[$i]['total_sales_tahun_berjalan'];
// // $list_scope[] = $rst_tahun_berjalan[$i]['S_RegionalName'];
// $list_color[] = $this->list_color_bar_chart($i, count($x1));
// }
}
$result = array(
"value_of_scope" => $value_of_scope,
"list_scope" => $list_scope,
"bar_chart_color" => $list_color,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
);
if ($debug != "") {
$result = array(
"value_of_scope" => $value_of_scope,
"list_scope" => $list_scope,
"bar_chart_color" => $list_color,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_total_pendapatan_bar_chart_no_group($debug = "", $filter_scope_debug = "", $filter_waktu_debug = "")
{
$this->corss();
// total pendapatan nat_sales
// *) di tambah dengan tahun - 1
// di berikan diff dalam persen , jika sebelumnya 0 ( belum ada data ) di kasih 100
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
// $param = $this->sys_input;
$filter_scope = "";
$filter_waktu = "";
$where_filter_scope = "";
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = YEAR('$date_now')";
// untuk debug
if ($debug != "") {
// $filter_scope = $filter_scope_debug;
$filter_scope = ($filter_scope_debug == "0" || $filter_scope_debug == 0) ? "-" : $filter_scope_debug;
$filter_waktu = $filter_waktu_debug;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
// filter start
if ($filter_scope !== "-") {
$where_filter_scope = " AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND Nat_SalesQuarter = ($quarterResult)
AND `Nat_SalesYear` = year('$date_now')";
}
}
} else {
if ($filter_waktu == "YTD") {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_tahun_berjalan = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
}
}
}
// tahun berjalan
// sebagai a
$sql_tahun_berjalan = "
SELECT ifnull(SUM(`Nat_SalesTotal`), 0) AS total_sales_tahun_berjalan,
S_RegionalID, S_RegionalName
FROM `nat_sales`
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
$where_tahun_berjalan
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
$where_filter_scope
GROUP BY S_RegionalID
UNION ALL
SELECT 0 AS total_sales_tahun_berjalan,
S_RegionalID, S_RegionalName
FROM s_regional
WHERE S_RegionalIsActive = 'Y'
AND S_RegionalID NOT IN (
SELECT
S_RegionalID
FROM `nat_sales`
JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
$where_tahun_berjalan
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
$where_filter_scope
GROUP BY S_RegionalID
)
ORDER BY S_RegionalName ASC
";
// $sql_tahun_berjalan = "
// SELECT ifnull(SUM(`Nat_SalesTotal`), 0) AS total_sales_tahun_berjalan,
// M_BranchID, M_BranchName,
// S_RegionalName, S_RegionalID
// FROM `nat_sales`
// JOIN m_branch
// ON Nat_SalesM_BranchID = M_BranchID
// AND M_BranchIsActive = 'Y'
// $where_tahun_berjalan
// JOIN s_regional
// ON M_BranchS_RegionalID = S_RegionalID
// AND S_RegionalIsActive = 'Y'
// $where_filter_scope
// GROUP BY M_BranchID
// ORDER BY M_BranchName ASC
// ";
$qry_tahun_berjalan = $this->db->query($sql_tahun_berjalan);
if (!$qry_tahun_berjalan) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_tahun_berjalan = $qry_tahun_berjalan->result_array();
$last_qry_tahun_berjalan = $this->db->last_query();
$list_scope = [];
$value_of_scope = [];
$result = [];
$list_color = [];
if (count($rst_tahun_berjalan) > 0) {
for ($i = 0; $i < count($rst_tahun_berjalan); $i++) {
$value_of_scope[] = $rst_tahun_berjalan[$i]['total_sales_tahun_berjalan'];
$list_scope[] = $rst_tahun_berjalan[$i]['S_RegionalName'];
$list_color[] = $this->list_color_bar_chart($i, count($list_scope));
}
// // TESTING COLOR DEFAULT
// $x1 = [
// 'Bali Raya',
// 'Balikpapan Raya',
// 'Bandung Raya',
// 'Cirebon Raya',
// 'Jakarta Raya',
// 'Madiun Raya',
// 'Magelang Raya',
// 'Makassar Raya',
// 'Manado Raya',
// 'Medan Raya',
// 'Padang Raya',
// 'Palembang Raya',
// 'Pekanbaru Raya',
// 'Pontianak Raya',
// 'Prospek',
// 'Salatiga Raya',
// 'Semarang Raya',
// 'Sima Jember',
// 'Sima Kediri',
// 'Sima Malang',
// 'Surabaya Raya',
// 'Tasik Raya',
// 'Tegal Raya',
// 'Yogyaraya',
// 'Bali Raya',
// 'Balikpapan Raya',
// 'Bandung Raya',
// 'Cirebon Raya',
// 'Jakarta Raya',
// 'Madiun Raya',
// 'Magelang Raya',
// 'Makassar Raya',
// 'Manado Raya',
// 'Medan Raya',
// 'Padang Raya',
// 'Palembang Raya',
// 'Pekanbaru Raya',
// 'Pontianak Raya',
// 'Prospek',
// 'Salatiga Raya',
// 'Semarang Raya',
// 'Sima Jember',
// 'Sima Kediri',
// 'Sima Malang',
// 'Surabaya Raya',
// 'Tasik Raya',
// 'Tegal Raya',
// 'Yogyaraya',
// ];
// for ($i = 0; $i < count($x1); $i++) {
// // $value_of_scope[] = $rst_tahun_berjalan[$i]['total_sales_tahun_berjalan'];
// // $list_scope[] = $rst_tahun_berjalan[$i]['S_RegionalName'];
// $list_color[] = $this->list_color_bar_chart($i, count($x1));
// }
}
$result = array(
"value_of_scope" => $value_of_scope,
"list_scope" => $list_scope,
"bar_chart_color" => $list_color,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
);
if ($debug != "") {
$result = array(
"value_of_scope" => $value_of_scope,
"list_scope" => $list_scope,
"bar_chart_color" => $list_color,
"last_qry_tahun_berjalan" => $last_qry_tahun_berjalan,
);
echo "";
print_r($result);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// analisa produk
public function nat_analisa_produk_nat_subgroup($date_now, $filter_scope, $filter_waktu)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
$join_filter_natsubgroup = "";
$where_nat_subgroup = "";
// filter start
if ($filter_scope != "-") {
$join_filter_natsubgroup = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_nat_subgroup = " AND Nat_SalesQuarter = ($quarterResult)";
}
}
} else {
if ($filter_waktu == "YTD") {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_nat_subgroup = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
}
}
}
// NAT_SUBGROUP
// $sql_natsubgroup = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
// CONCAT(Nat_SubGroupNat_GroupID,Nat_SubGroupCode) as code,
// Nat_SubGroupID, Nat_SubGroupName, '' as color
// FROM `nat_sales`
// join nat_group
// ON Nat_SalesNat_GroupID = Nat_GroupID
// AND Nat_GroupIsActive = 'Y'
// AND `Nat_SalesYear` = year('$date_now')
// AND Nat_SalesIsActive = 'Y'
// $where_nat_subgroup
// join nat_subgroup
// ON Nat_SalesNat_SubGroupID = Nat_SubGroupID
// AND Nat_SubGroupIsActive = 'Y'
// $join_filter_natsubgroup
// GROUP BY Nat_SalesNat_SubGroupID
// order by Nat_GroupCode ASC, Nat_SubGroupCode ASC";
$sql_natsubgroup = "SELECT
ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
ifnull(SUM(`Nat_SalesCountPx`),0) AS total_count_pemeriksaan,
CONCAT(Nat_SubGroupNat_GroupID,Nat_SubGroupCode) as code,
Nat_SubGroupID, Nat_SubGroupName, '' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND Nat_SalesIsActive = 'Y'
$where_nat_subgroup
join nat_subgroup
ON Nat_SalesNat_SubGroupID = Nat_SubGroupID
AND Nat_SubGroupIsActive = 'Y'
$join_filter_natsubgroup
GROUP BY Nat_SalesNat_SubGroupID
order by Nat_GroupCode ASC, Nat_SubGroupCode ASC";
$qry_natsubgroup = $this->db->query($sql_natsubgroup);
if (!$qry_natsubgroup) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_natsubgroup = $qry_natsubgroup->result_array();
$last_qry_natsubgroup = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_natsubgroup = 0.0;
// Count Pemeriksaan
$total_value_pemeriksaan = 0.0;
$sum_natsubgroup_pemeriksaan = 0.0;
// calculate value persen
if (count($rst_natsubgroup) > 0) {
foreach ($rst_natsubgroup as $key => $value) {
$sum_natsubgroup += $value['total_sales_tahun_berjalan'];
$sum_natsubgroup_pemeriksaan += $value['total_count_pemeriksaan'];
}
}
$total_value = $sum_natsubgroup;
$total_value_pemeriksaan = $sum_natsubgroup_pemeriksaan;
if (count($rst_natsubgroup) > 0) {
foreach ($rst_natsubgroup as $key => $value) {
$persen_natsubgroup = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$persen_natsubgroup_pemeriksaan = round($value['total_count_pemeriksaan'] / $total_value_pemeriksaan * 100, 0);
$result[] = [
"code" => $value['code'],
"name" => $value['Nat_SubGroupName'],
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_format_rupiah_v2" => $this->formatAngkaJutaTanpaAkhiran($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_natsubgroup . ' %',
"count_pemeriksaan" => $value['total_count_pemeriksaan'],
"count_pemeriksaan_format" => $this->formatAngkaPemeriksaanPieChart($value['total_count_pemeriksaan']),
"count_pemeriksaan_format_v2" => $this->formatAngkaPemeriksaanPieChartTanpaAkhiran($value['total_count_pemeriksaan']),
"count_pemeriksaan_persen" => $persen_natsubgroup_pemeriksaan . ' %',
"rst_natsubgroup" => $rst_natsubgroup,
"last_qry_natsubgroup" => $last_qry_natsubgroup,
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_nat_group($date_now, $filter_scope, $filter_waktu)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
$join_filter_natgroup = "";
$where_filter_natgroup = "";
// filter start
if ($filter_scope != "-") {
$join_filter_natgroup = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_filter_natgroup = " AND Nat_SalesQuarter = ($quarterResult)
AND `Nat_SalesYear` = year('$date_now')";
}
}
} else {
if ($filter_waktu == "YTD") {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_filter_natgroup = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
}
}
}
// NAT_GROUP
// $sql_natgroup = "SELECT
// ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
// Nat_GroupCode,
// Nat_GroupID, Nat_GroupName, '' as color
// FROM `nat_sales`
// join nat_group
// ON Nat_SalesNat_GroupID = Nat_GroupID
// AND Nat_GroupIsActive = 'Y'
// AND Nat_SalesIsActive = 'Y'
// $where_filter_natgroup
// $join_filter_natgroup
// GROUP BY Nat_SalesNat_GroupID
// order by Nat_GroupCode ASC";
$sql_natgroup = "SELECT
ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
ifnull(SUM(`Nat_SalesCountPx`),0) AS total_count_pemeriksaan,
Nat_GroupCode,
Nat_GroupID, Nat_GroupName, '' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND Nat_SalesIsActive = 'Y'
$where_filter_natgroup
$join_filter_natgroup
GROUP BY Nat_SalesNat_GroupID
order by Nat_GroupCode ASC";
$qry_natgroup = $this->db->query($sql_natgroup);
if (!$qry_natgroup) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_natgroup = $qry_natgroup->result_array();
$last_qry_natgroup = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_natgroup = 0.0;
// Count Pemeriksaan
$total_value_pemeriksaan = 0.0;
$sum_natgroup_pemeriksaan = 0.0;
// calculate value persen
if (count($rst_natgroup) > 0) {
foreach ($rst_natgroup as $key => $value) {
$sum_natgroup += $value['total_sales_tahun_berjalan'];
$sum_natgroup_pemeriksaan += $value['total_count_pemeriksaan'];
}
}
$total_value = $sum_natgroup;
$total_value_pemeriksaan = $sum_natgroup_pemeriksaan;
if (count($rst_natgroup) > 0) {
foreach ($rst_natgroup as $key => $value) {
$persen_natgroup = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$persen_natgroup_pemeriksaan = round($value['total_count_pemeriksaan'] / $total_value_pemeriksaan * 100, 0);
$result[] = [
"code" => $value['Nat_GroupCode'],
"name" => $value['Nat_GroupName'],
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_format_rupiah_v2" => $this->formatAngkaJutaTanpaAkhiran($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_natgroup . ' %',
"count_pemeriksaan" => $value['total_count_pemeriksaan'],
"count_pemeriksaan_format" => $this->formatAngkaPemeriksaanPieChart($value['total_count_pemeriksaan']),
"count_pemeriksaan_format_v2" => $this->formatAngkaPemeriksaanPieChartTanpaAkhiran($value['total_count_pemeriksaan']),
"count_pemeriksaan_persen" => $persen_natgroup_pemeriksaan . ' %',
"rst_natgroup" => $rst_natgroup,
"last_qry_natgroup" => $last_qry_natgroup,
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_lab_nonlab($date_now, $filter_scope, $filter_waktu)
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$date_now = date('Y-m-d');
$join_filter_lab = "";
$join_filter_nonlab = "";
$where_lab = "";
$where_nonlab = "";
// filter start
if ($filter_scope != "-") {
$join_filter_lab = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
$join_filter_nonlab = "JOIN m_branch
ON Nat_SalesM_BranchID = M_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional
ON M_BranchS_RegionalID = S_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = $filter_scope";
}
if ($filter_waktu != "") {
if ($filter_waktu != 'YTD') {
// bukan Year To Date
if (strpos($filter_waktu, 'Q') !== false || strpos($filter_waktu, 'q') !== false) {
$quarterResult = $this->getQuarterByFilter($filter_waktu);
if ($quarterResult != '-') {
$where_lab = " AND Nat_SalesQuarter = ($quarterResult)
AND `Nat_SalesYear` = year('$date_now')";
$where_nonlab = " AND Nat_SalesQuarter = ($quarterResult)
AND `Nat_SalesYear` = year('$date_now')";
}
}
} else {
if ($filter_waktu == "YTD") {
// jika Year To Date
// klu berjalan, 1 januari 2023 - 4 oktober 2023 ( date now )
// klu end date year - 1 , 1 januari 2022 - (date now - 1)
$berjalan_a = date('Y-01-01');
// berjalan
// Nat_SalesDay >= '2023-01-01' AND Nat_SalesDay <= '2023-10-04'
// Nat_SalesYear` = year('$date_now')
// AND Nat_SalesDay >= '$berjalan_a' AND Nat_SalesDay <= '$date_now'
$where_lab = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
$where_nonlab = " AND Nat_SalesIsActive = 'Y'
AND `Nat_SalesYear` = year('$date_now')
AND (`Nat_SalesDay` >= '$berjalan_a' AND `Nat_SalesDay` <= '$date_now')";
}
}
}
// LAB
// $sql_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
// '' as color
// FROM `nat_sales`
// join nat_group
// ON Nat_SalesNat_GroupID = Nat_GroupID
// AND Nat_GroupIsActive = 'Y'
// AND Nat_SalesIsActive = 'Y'
// AND Nat_SalesNat_GroupID IN (1)
// $where_lab
// $join_filter_lab";
$sql_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
ifnull(SUM(`Nat_SalesCountPx`),0) AS total_count_pemeriksaan,
'' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND Nat_SalesIsActive = 'Y'
AND Nat_SalesNat_GroupID IN (1)
$where_lab
$join_filter_lab";
$qry_lab = $this->db->query($sql_lab);
if (!$qry_lab) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_lab = $qry_lab->result_array();
$last_qry_lab = $this->db->last_query();
// NON LAB
// $sql_non_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
// '' as color
// FROM `nat_sales`
// join nat_group
// ON Nat_SalesNat_GroupID = Nat_GroupID
// AND Nat_GroupIsActive = 'Y'
// AND Nat_SalesIsActive = 'Y'
// AND Nat_SalesNat_GroupID NOT IN (1)
// $where_nonlab
// $join_filter_nonlab";
$sql_non_lab = "SELECT ifnull(SUM(`Nat_SalesTotal`),0) AS total_sales_tahun_berjalan,
ifnull(SUM(`Nat_SalesCountPx`),0) AS total_count_pemeriksaan,
'' as color
FROM `nat_sales`
join nat_group
ON Nat_SalesNat_GroupID = Nat_GroupID
AND Nat_GroupIsActive = 'Y'
AND Nat_SalesIsActive = 'Y'
AND Nat_SalesNat_GroupID NOT IN (1)
$where_nonlab
$join_filter_nonlab";
$qry_non_lab = $this->db->query($sql_non_lab);
if (!$qry_non_lab) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rst_non_lab = $qry_non_lab->result_array();
$last_qry_non_lab = $this->db->last_query();
$result = [];
$total_value = 0.0;
$sum_lab = 0.0;
$sum_non_lab = 0.0;
// Count pemeriksaan
$total_value_pemeriksaan = 0.0;
$sum_lab_pemeriksaan = 0.0;
$sum_non_lab_pemeriksaan = 0.0;
// calculate value persen
if (count($rst_lab) > 0) {
foreach ($rst_lab as $key => $value) {
$sum_lab += $value['total_sales_tahun_berjalan'];
$sum_lab_pemeriksaan += $value['total_count_pemeriksaan'];
}
}
if (count($rst_non_lab) > 0) {
foreach ($rst_non_lab as $key => $value) {
$sum_non_lab += $value['total_sales_tahun_berjalan'];
$sum_non_lab_pemeriksaan += $value['total_count_pemeriksaan'];
}
}
$total_value = $sum_lab + $sum_non_lab;
$total_value_pemeriksaan = $sum_lab_pemeriksaan + $sum_non_lab_pemeriksaan;
if (count($rst_lab) > 0) {
foreach ($rst_lab as $key => $value) {
$persen_lab = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$persen_lab_pemeriksaan = round($value['total_count_pemeriksaan'] / $total_value_pemeriksaan * 100, 0);
$result[] = [
"code" => "1",
"name" => "Laboratorium",
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_format_rupiah_v2" => $this->formatAngkaJutaTanpaAkhiran($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_lab . ' %',
"count_pemeriksaan" => $value['total_count_pemeriksaan'],
"count_pemeriksaan_format" => $this->formatAngkaPemeriksaanPieChart($value['total_count_pemeriksaan']),
"count_pemeriksaan_format_v2" => $this->formatAngkaPemeriksaanPieChartTanpaAkhiran($value['total_count_pemeriksaan']),
"count_pemeriksaan_persen" => $persen_lab_pemeriksaan . ' %',
"rst_lab" => $rst_lab,
"last_qry_lab" => $last_qry_lab,
];
}
}
if (count($rst_non_lab) > 0) {
foreach ($rst_non_lab as $key => $value) {
$persen_non_lab = round($value['total_sales_tahun_berjalan'] / $total_value * 100, 0);
$persen_non_lab_pemeriksaan = round($value['total_count_pemeriksaan'] / $total_value_pemeriksaan * 100, 0);
$result[] = [
"code" => "2",
"name" => "Klinik",
"value" => $value['total_sales_tahun_berjalan'],
"color" => "",
"value_format_rupiah" => $this->formatAngkaJuta($value['total_sales_tahun_berjalan']),
"value_format_rupiah_v2" => $this->formatAngkaJutaTanpaAkhiran($value['total_sales_tahun_berjalan']),
"value_persen" => $persen_non_lab . ' %',
"count_pemeriksaan" => $value['total_count_pemeriksaan'],
"count_pemeriksaan_format" => $this->formatAngkaPemeriksaanPieChart($value['total_count_pemeriksaan']),
"count_pemeriksaan_format_v2" => $this->formatAngkaPemeriksaanPieChartTanpaAkhiran($value['total_count_pemeriksaan']),
"count_pemeriksaan_persen" => $persen_non_lab_pemeriksaan . ' %',
"rst_non_lab" => $rst_non_lab,
"last_qry_non_lab" => $last_qry_non_lab
];
}
}
// $result = array(
// "lab" => $rst_lab,
// "last_qry_lab" => $last_qry_lab,
// "non_lab" => $rst_non_lab,
// "last_qry_non_lab" => $last_qry_non_lab
// );
return $result;
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_analisa_produk_pie_chart($debug = "", $filter_scope_debug_prm = "", $filter_waktu_debug_prm = "")
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$filter_scope = "";
$filter_waktu = "";
// untuk debug
if ($debug != "") {
$filter_scope = $filter_scope_debug_prm;
$filter_waktu = $filter_waktu_debug_prm;
}
// PAKE POST DARI FLUTTER
$prm = $this->sys_input;
// if (isset($prm['filter_scope'])) {
// $filter_scope = $prm["filter_scope"];
// }
if (isset($prm['filter_scope'])) {
$filter_scope = ($prm["filter_scope"] == "0" || $prm["filter_scope"] == 0) ? "-" : $prm["filter_scope"];
}
if (isset($prm['filter_waktu'])) {
$filter_waktu = $prm["filter_waktu"];
}
$date_now = date('Y-m-d');
$result = [];
$data_lab_non_lab = [];
$data_natgroup = [];
$data_natsubgroup = [];
// lab dan nonlab - tingkat 1
$analisaProdukLabNonLab = $this->nat_analisa_produk_lab_nonlab($date_now, $filter_scope, $filter_waktu);
// nat group - tingkat 2
$analisaProdukNatGroup = $this->nat_analisa_produk_nat_group($date_now, $filter_scope, $filter_waktu);
// nat subgroup - tingkat 3
$analisaProdukNatSubGroup = $this->nat_analisa_produk_nat_subgroup($date_now, $filter_scope, $filter_waktu);
// klu data kosong set array kosong
if (count($analisaProdukLabNonLab) < 1) {
$analisaProdukLabNonLab = [];
}
if (count($analisaProdukNatGroup) < 1) {
$analisaProdukNatGroup = [];
}
if (count($analisaProdukNatSubGroup) < 1) {
$analisaProdukNatSubGroup = [];
}
// analisa produk lab dan nonlab
for ($i = 0; $i < count($analisaProdukLabNonLab); $i++) {
$data_lab_non_lab[$i]["level"] = "Level 1";
$data_lab_non_lab[$i]["code"] = $analisaProdukLabNonLab[$i]["code"];
$data_lab_non_lab[$i]["name"] = $analisaProdukLabNonLab[$i]["name"];
$data_lab_non_lab[$i]["value"] = $analisaProdukLabNonLab[$i]["value"];
$data_lab_non_lab[$i]['color'] = $this->list_color_pie_chart($i, 'tingkat1', count($analisaProdukLabNonLab));
$data_lab_non_lab[$i]["value_format_rupiah"] = $analisaProdukLabNonLab[$i]["value_format_rupiah"];
$data_lab_non_lab[$i]["value_format_rupiah_v2"] = $analisaProdukLabNonLab[$i]["value_format_rupiah_v2"];
$data_lab_non_lab[$i]["value_persen"] = $analisaProdukLabNonLab[$i]["value_persen"];
$data_lab_non_lab[$i]["count_pemeriksaan"] = $analisaProdukLabNonLab[$i]["count_pemeriksaan"];
$data_lab_non_lab[$i]["count_pemeriksaan_format"] = $analisaProdukLabNonLab[$i]["count_pemeriksaan_format"];
$data_lab_non_lab[$i]["count_pemeriksaan_format_v2"] = $analisaProdukLabNonLab[$i]["count_pemeriksaan_format_v2"];
$data_lab_non_lab[$i]["count_pemeriksaan_persen"] = $analisaProdukLabNonLab[$i]["count_pemeriksaan_persen"];
}
// analisa produk nat group
for ($i = 0; $i < count($analisaProdukNatGroup); $i++) {
$data_natgroup[$i]["level"] = "Level 2";
$data_natgroup[$i]["code"] = $analisaProdukNatGroup[$i]["code"];
$data_natgroup[$i]["name"] = $analisaProdukNatGroup[$i]["name"];
$data_natgroup[$i]["value"] = $analisaProdukNatGroup[$i]["value"];
$data_natgroup[$i]['color'] = $this->list_color_pie_chart($i, 'tingkat2', count($analisaProdukNatGroup));
$data_natgroup[$i]["value_format_rupiah"] = $analisaProdukNatGroup[$i]["value_format_rupiah"];
$data_natgroup[$i]["value_format_rupiah_v2"] = $analisaProdukNatGroup[$i]["value_format_rupiah_v2"];
$data_natgroup[$i]["value_persen"] = $analisaProdukNatGroup[$i]["value_persen"];
$data_natgroup[$i]["count_pemeriksaan"] = $analisaProdukNatGroup[$i]["count_pemeriksaan"];
$data_natgroup[$i]["count_pemeriksaan_format"] = $analisaProdukNatGroup[$i]["count_pemeriksaan_format"];
$data_natgroup[$i]["count_pemeriksaan_format_v2"] = $analisaProdukNatGroup[$i]["count_pemeriksaan_format_v2"];
$data_natgroup[$i]["count_pemeriksaan_persen"] = $analisaProdukNatGroup[$i]["count_pemeriksaan_persen"];
}
// analisa produk nat_subgroup
for ($i = 0; $i < count($analisaProdukNatSubGroup); $i++) {
$data_natsubgroup[$i]["level"] = "Level 3";
$data_natsubgroup[$i]["code"] = $analisaProdukNatSubGroup[$i]["code"];
$data_natsubgroup[$i]["name"] = $analisaProdukNatSubGroup[$i]["name"];
$data_natsubgroup[$i]["value"] = $analisaProdukNatSubGroup[$i]["value"];
$data_natsubgroup[$i]['color'] = $this->list_color_pie_chart($i, 'tingkat3', count($analisaProdukNatSubGroup));
$data_natsubgroup[$i]["value_format_rupiah"] = $analisaProdukNatSubGroup[$i]["value_format_rupiah"];
$data_natsubgroup[$i]["value_format_rupiah_v2"] = $analisaProdukNatSubGroup[$i]["value_format_rupiah_v2"];
$data_natsubgroup[$i]["value_persen"] = $analisaProdukNatSubGroup[$i]["value_persen"];
$data_natsubgroup[$i]["count_pemeriksaan"] = $analisaProdukNatSubGroup[$i]["count_pemeriksaan"];
$data_natsubgroup[$i]["count_pemeriksaan_format"] = $analisaProdukNatSubGroup[$i]["count_pemeriksaan_format"];
$data_natsubgroup[$i]["count_pemeriksaan_format_v2"] = $analisaProdukNatSubGroup[$i]["count_pemeriksaan_format_v2"];
$data_natsubgroup[$i]["count_pemeriksaan_persen"] = $analisaProdukNatSubGroup[$i]["count_pemeriksaan_persen"];
}
$result = [
"analisaProdukLabNonLab" => $data_lab_non_lab,
"analisaProdukNatGroup" => $data_natgroup,
"analisaProdukNatSubGroup" => $data_natsubgroup,
"last_qry_lab" => $analisaProdukLabNonLab[0]['last_qry_lab'],
"last_qry_non_lab" => $analisaProdukLabNonLab[1]['last_qry_non_lab'],
"last_qry_natgroup" => $analisaProdukNatGroup[0]['last_qry_natgroup'],
"last_qry_natsubgroup" => $analisaProdukNatSubGroup[0]['last_qry_natsubgroup']
];
if ($debug != "") {
$result_x = array(
"analisaProdukLabNonLab" => $analisaProdukLabNonLab,
"analisaProdukNatGroup" => $analisaProdukNatGroup,
"analisaProdukNatSubGroup" => $data_natsubgroup,
"last_qry_lab" => $analisaProdukLabNonLab[0]['last_qry_lab'],
"last_qry_non_lab" => $analisaProdukLabNonLab[1]['last_qry_non_lab'],
"last_qry_natgroup" => $analisaProdukNatGroup[0]['last_qry_natgroup'],
"last_qry_natsubgroup" => $analisaProdukNatSubGroup[0]['last_qry_natsubgroup']
);
echo "";
print_r($result_x);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// analisa produk end
// filter chart start - 02-10-2023
public function filter_list_scope($debug = "")
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$sql_scope = "SELECT
0 as S_RegionalID,
'Nasional' as S_RegionalName
UNION
SELECT
S_RegionalID,
S_RegionalName
FROM s_regional
WHERE S_RegionalIsActive = 'Y'";
$qry_scope = $this->db->query($sql_scope, []);
if (!$qry_scope) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$rows_scope = $qry_scope->result_array();
$last_qry = $this->db->last_query();
$result = $rows_scope;
if ($debug != "") {
$result_x = array(
"list_scope_filter" => $rows_scope,
"last_qry_scope_filter" => $last_qry
);
echo "";
print_r($result_x);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function filter_waktu($debug = "")
{
$this->corss();
try {
// if (!$this->isLogin) {
// $this->sys_error("Invalid Token");
// exit;
// }
$filter_waktu = [
["text" => "Year To Date", "value" => "YTD"],
["text" => "Quarter 1", "value" => "Q1"],
["text" => "Quarter 2", "value" => "Q2"],
["text" => "Quarter 3", "value" => "Q3"],
["text" => "Quarter 4", "value" => "Q4"]
];
$result = $filter_waktu;
if ($debug != "") {
$result_x = array(
"list_waktu_filter" => $filter_waktu,
);
echo "";
print_r($result_x);
echo "";
exit;
}
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
// filter chart end - 02-10-2023
// national end
public function reg_total()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$regID = "0";
if (isset($prm['reg_id'])) {
$regID = $prm["reg_id"];
}
$sql = "SELECT SUM(`Nat_SalesTotal`) AS total_sales
FROM `nat_sales`
JOIN m_branch ON M_BranchID = Nat_SalesM_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional ON S_RegionalID = M_BranchS_RegionalID
AND S_RegionalIsActive = 'Y'
AND S_RegionalID = ?
WHERE `Nat_SalesYear` = year(curdate())
AND Nat_SalesIsActive = 'Y'";
$qry = $this->db->query($sql, [$regID]);
if (!$qry) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$rst = $qry->result_array();
$result = array(
"total_sales" => $rst[0]['total_sales'],
"last_qry" => $last_qry
);
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function branch_total()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$branchID = "0";
if (isset($prm['branch_id'])) {
$branchID = $prm["branch_id"];
}
$sql = "SELECT SUM(`Nat_SalesTotal`) AS total_sales
FROM `nat_sales`
JOIN m_branch ON M_BranchID = Nat_SalesM_BranchID
AND M_BranchIsActive = 'Y'
AND M_BranchID = ?
WHERE `Nat_SalesYear` = year(curdate())
AND Nat_SalesIsActive = 'Y'";
$qry = $this->db->query($sql, [$branchID]);
if (!$qry) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$rst = $qry->result_array();
$result = array(
"total_sales" => $rst[0]['total_sales'],
"last_qry" => $last_qry
);
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function nat_3_month_sales()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$month = "0";
if (isset($prm['month'])) {
$month = explode(",", $prm["month"]);
}
$sql_reg = "SELECT
S_RegionalID,
S_RegionalName
FROM s_regional
WHERE S_RegionalIsActive = 'Y'";
$qry_reg = $this->db->query($sql_reg, []);
if (!$qry_reg) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$arr_reg = $qry_reg->result_array();
$sql_sales = "SELECT S_RegionalID ,
SUM(`Nat_SalesTotal`) AS total_sales,
DATE_FORMAT(Nat_SalesDay, '%Y-%m' ) as bulan,
DATE_FORMAT(Nat_SalesDay, '%m' ) as month
FROM `nat_sales`
JOIN m_branch ON M_BranchID = Nat_SalesM_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional ON S_RegionalID = M_BranchS_RegionalID
AND S_RegionalIsActive = 'Y'
AND Nat_SalesDay + interval 3 month > curdate()
AND Nat_SalesIsActive = 'Y'
GROUP BY S_RegionalID, bulan";
$qry_sales = $this->db->query($sql_sales, []);
if (!$qry_sales) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$arr_sales = $qry_sales->result_array();
$arr_month = [
date("m", strtotime("-3 Months")),
date("m", strtotime("-2 Months")),
date("m", strtotime("-1 Months"))
];
$perregional = array();
for ($i = 0; $i < count($arr_reg); $i++) {
$regional = $arr_reg[$i];
$perbulan = array();
for ($j = 0; $j < count($arr_month); $j++) {
$a = array();
for ($k = 0; $k < count($arr_sales); $k++) {
$monthName = date('M', mktime(0, 0, 0, $month[$j], 10));
$a = array(
"regional_id" => $regional['S_RegionalID'],
"month" => $arr_month[$j],
"bulan" => $monthName,
"value" => "0"
);
}
array_push($perbulan, $a);
}
$z = array(
"regional_id" => $regional['S_RegionalID'],
"regional_name" => $regional['S_RegionalName'],
"dataChart" => $perbulan,
);
array_push($perregional, $z);
}
for ($i = 0; $i < count($perregional); $i++) {
$regional = $perregional[$i];
for ($j = 0; $j < count($regional); $j++) {
$perbulan = $regional['dataChart'];
for ($k = 0; $k < count($perbulan); $k++) {
$data = $perbulan[$k];
for ($l = 0; $l < count($arr_sales); $l++) {
$sales = $arr_sales[$l];
if (
$data['regional_id'] == $sales['S_RegionalID']
&& intval($data['month']) == intval($sales['month'])
) {
// $perregional[$i]['dataChart'][$k]['total_sales'] = "a";
$perregional[$i]['dataChart'][$k]['value'] = $sales['total_sales'];
}
}
}
}
}
$this->sys_ok($perregional);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function reg_3_month_sales()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$month = "0";
if (isset($prm['month'])) {
$month = explode(",", $prm["month"]);
}
$regID = "0";
if (isset($prm['reg_id'])) {
$regID = $prm["reg_id"];
}
$sqlbranch = "SELECT M_BranchID,
M_BranchName
FROM m_branch
WHERE M_BranchIsActive = 'Y'";
$qrybranch = $this->db->query($sqlbranch, []);
if (!$qrybranch) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$arrbranch = $qrybranch->result_array();
$sql_sales = "SELECT M_BranchID ,
SUM(`Nat_SalesTotal`) AS total_sales,
DATE_FORMAT(Nat_SalesDay, '%Y-%m' ) as bulan,
DATE_FORMAT(Nat_SalesDay, '%m' ) as month
FROM `nat_sales`
JOIN m_branch ON M_BranchID = Nat_SalesM_BranchID
AND M_BranchIsActive = 'Y'
JOIN s_regional ON S_RegionalID = M_BranchS_RegionalID
AND S_RegionalIsActive = 'Y'
AND Nat_SalesDay + interval 3 month > curdate()
AND Nat_SalesIsActive = 'Y'
GROUP BY M_BranchID, bulan";
$qry_sales = $this->db->query($sql_sales, []);
if (!$qry_sales) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$arr_sales = $qry_sales->result_array();
$perbranch = array();
$arr_month = [
date("m", strtotime("-3 Months")),
date("m", strtotime("-2 Months")),
date("m", strtotime("-1 Months"))
];
for ($i = 0; $i < count($arrbranch); $i++) {
$branch = $arrbranch[$i];
$perbulan = array();
for ($j = 0; $j < count($arr_month); $j++) {
$a = array();
for ($k = 0; $k < count($arr_sales); $k++) {
$monthName = date('M', mktime(0, 0, 0, $month[$j], 10));
$a = array(
"branch_id" => $branch['M_BranchID'],
"month" => $arr_month[$j],
"bulan" => $monthName,
"value" => "0"
);
}
array_push($perbulan, $a);
}
$z = array(
"branch_id" => $branch['M_BranchID'],
"branch_name" => $branch['M_BranchName'],
"dataChart" => $perbulan,
);
array_push($perbranch, $z);
}
for ($i = 0; $i < count($perbranch); $i++) {
$branch = $perbranch[$i];
for ($j = 0; $j < count($branch); $j++) {
$perbulan = $branch['dataChart'];
for ($k = 0; $k < count($perbulan); $k++) {
$data = $perbulan[$k];
for ($l = 0; $l < count($arr_sales); $l++) {
$sales = $arr_sales[$l];
if (
$data['branch_id'] == $sales['M_BranchID']
&& intval($data['month']) == intval($sales['month'])
) {
// $perbranch[$i]['dataChart'][$k]['total_sales'] = "a";
$perbranch[$i]['dataChart'][$k]['value'] = $sales['total_sales'];
}
}
}
}
}
// for ($i = 0; $i < count($arrbranch); $i++) {
// $perbulan = array();
// for ($j = 0; $j < count($month); $j++) {
// $sql = "SELECT SUM(`Nat_SalesTotal`) AS total_sales,
// IFNULL(SUM(`Nat_SalesTotal`), 'N') AS total_flag,
// S_RegionalID,
// S_RegionalName,
// M_BranchID ,
// M_BranchName,
// Nat_SalesMonth
// FROM m_branch
// LEFT JOIN s_regional ON S_RegionalID = M_BranchS_RegionalID
// AND S_RegionalIsActive = 'Y'
// LEFT JOIN nat_sales ON Nat_SalesM_BranchID = M_BranchID
// AND `Nat_SalesYear` = year(curdate())
// AND Nat_SalesIsActive = 'Y'
// AND Nat_SalesMonth = ?
// WHERE M_BranchIsActive = 'Y'
// AND M_BranchID = ?";
// $qry = $this->db->query($sql, [$month[$j], $arrbranch[$i]['M_BranchID']]);
// if (!$qry) {
// $error = array(
// "message" => $this->db->error()["message"],
// "sql" => $this->db->last_query()
// );
// $this->sys_error_db($error);
// exit;
// }
// $last_qry = $this->db->last_query();
// $rst = $qry->result_array();
// $a = array();
// if ($rst[0]['total_flag'] != "N") {
// # code...
// $a = array(
// "month" => $month[$j],
// "total_sales" => $rst[0]['total_sales'],
// );
// } else {
// $a = array(
// "month" => $month[$j],
// "total_sales" => "0",
// );
// }
// array_push($perbulan, $a);
// }
// $z = array(
// "branch_id" => $arrbranch[$i]['M_BranchID'],
// "branch_name" => $arrbranch[$i]['M_BranchName'],
// "data" => $perbulan
// );
// array_push($perbranch, $z);
// }
$result = array(
"arr month" => $arr_month,
"last_qry" => $perbranch
);
$this->sys_ok($result);
// $this->sys_ok($perbranch);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
public function branch_3_month_sales()
{
try {
if (!$this->isLogin) {
$this->sys_error("Invalid Token");
exit;
}
$prm = $this->sys_input;
$month = "0";
if (isset($prm['month'])) {
$month = explode(",", $prm["month"]);
}
$branchID = "0";
if (isset($prm['branch_id'])) {
$branchID = $prm["branch_id"];
}
$sql = "SELECT SUM(`Nat_SalesTotal`) AS total_sales
FROM `nat_sales`
JOIN m_branch ON M_BranchID = Nat_SalesM_BranchID
AND M_BranchIsActive = 'Y'
AND M_BranchID = ?
WHERE `Nat_SalesYear` = year(curdate())
AND Nat_SalesMonth IN ?
AND Nat_SalesIsActive = 'Y'";
$qry = $this->db->query($sql, [$branchID, $month]);
if (!$qry) {
$error = array(
"message" => $this->db->error()["message"],
"sql" => $this->db->last_query()
);
$this->sys_error_db($error);
exit;
}
$last_qry = $this->db->last_query();
$rst = $qry->result_array();
$result = array(
"total_sales" => $rst[0]['total_sales'],
"last_qry" => $last_qry
);
$this->sys_ok($result);
} catch (Exception $exc) {
$message = $exc->getMessage();
$this->sys_error($message);
}
}
}