857 lines
35 KiB
PHP
857 lines
35 KiB
PHP
<?php
|
|
class Search extends MY_Controller
|
|
{
|
|
var $db;
|
|
function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->db = $this->load->database("onedev", true);
|
|
}
|
|
function index()
|
|
{
|
|
echo "Api: Training Playground";
|
|
}
|
|
|
|
function instrument_byname()
|
|
{
|
|
try {
|
|
//# cek token valid
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$param = $this->sys_input;
|
|
$page = $param["page"];
|
|
|
|
// check input parameter
|
|
$control = "";
|
|
if (isset($param['control'])) {
|
|
$control = trim($param["control"]);
|
|
if ($control != "") {
|
|
$control = '%' . $param['control'] . '%';
|
|
}
|
|
}
|
|
$test = "";
|
|
if (isset($param['test'])) {
|
|
$test = trim($param["test"]);
|
|
if ($test != "") {
|
|
$test = '%' . $param['test'] . '%';
|
|
}
|
|
}
|
|
$instrument = trim($param['instrument']);
|
|
$instrument = "%" . $instrument . "%";
|
|
|
|
// hitung start_offset
|
|
$ROW_PER_PAGE = 10;
|
|
$start_offset = 0;
|
|
if (isset($param["page"])) {
|
|
if (is_numeric((int)$param["page"]) && $param["page"] > 0) {
|
|
$start_offset = ($page - 1) * $ROW_PER_PAGE;
|
|
}
|
|
}
|
|
//query
|
|
if (
|
|
$control == "" && $test == ""
|
|
) {
|
|
$sql = "select distinct Nat_InstrumentID, Nat_InstrumentName
|
|
from
|
|
nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y' and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$instrument, $start_offset]);
|
|
$last_qry = $this->db->last_query();
|
|
$count = "select count(distinct Nat_InstrumentID) as total
|
|
from
|
|
nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y' and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'";
|
|
$qry_total_filter = $this->db->query($count, [$instrument]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
} else if (
|
|
$control != "" && $test == ""
|
|
) {
|
|
$sql = "select distinct Nat_InstrumentID, Nat_InstrumentName
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
and Nat_QcControlIsActive = 'Y'
|
|
and Nat_QcControlName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$instrument, $control, $start_offset]);
|
|
$last_qry = $this->db->last_query();
|
|
$count = "select count(distinct Nat_InstrumentID) as total
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
and Nat_QcControlIsActive = 'Y'
|
|
and Nat_QcControlName like ?
|
|
";
|
|
$qry_total_filter = $this->db->query($count, [$instrument, $control]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
} else if (
|
|
$control == "" && $test != ""
|
|
) {
|
|
$sql = "select distinct Nat_InstrumentID, Nat_InstrumentName
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_test on Nat_TestID = Nat_QcNat_TestID
|
|
and Nat_TestName like ?
|
|
limit 10 offset ?";
|
|
$qry =
|
|
$this->db->query($sql, [$instrument, $test, $start_offset]);
|
|
$last_qry = $this->db->last_query();
|
|
$count = "select count(distinct Nat_InstrumentID) as total
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_test on Nat_TestID = Nat_QcNat_TestID
|
|
and Nat_TestName like ?
|
|
";
|
|
$qry_total_filter = $this->db->query($count, [$instrument, $test]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
} else {
|
|
$sql = "select distinct Nat_InstrumentID, Nat_InstrumentName
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_test on Nat_TestID = Nat_QcNat_TestID
|
|
and Nat_TestName like ?
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
and Nat_QcControlIsActive = 'Y'
|
|
and Nat_QcControlName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$instrument, $test, $control, $start_offset]);
|
|
$last_qry = $this->db->last_query();
|
|
$count = "select count(distinct Nat_InstrumentID) as total
|
|
from nat_instrument
|
|
join t_instrument_local on Nat_InstrumentID = T_InstrumentLocalNat_InstrumentID
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
and T_InstrumentLocalIsActive = 'Y'
|
|
and Nat_InstrumentName like ?
|
|
join nat_qc on Nat_instrumentID = Nat_QcNat_InstrumentID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_test on Nat_TestID = Nat_QcNat_TestID
|
|
and Nat_TestName like ?
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
and Nat_QcControlIsActive = 'Y'
|
|
and Nat_QcControlName like ?
|
|
";
|
|
|
|
$qry_total_filter = $this->db->query($count, [$instrument, $test, $control]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
}
|
|
if (!$qry) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
if (!$qry_total_filter) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry_total_filter
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
$rows = $qry->result_array();
|
|
$total_filter = (int)$qry_total_filter->result_array()[0]["total"];
|
|
|
|
$total = ceil($total_filter / $ROW_PER_PAGE);
|
|
$result = array(
|
|
"total" => $total,
|
|
"total_filter" => $total_filter,
|
|
"records" => $rows,
|
|
"sql" => $last_qry,
|
|
"count" => $last_qry_total_filter
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
function qc_byname()
|
|
{
|
|
try {
|
|
//# cek token valid
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$param = $this->sys_input;
|
|
|
|
$page = $param["page"];
|
|
$control = "%%";
|
|
if (isset($param['control'])) {
|
|
$control = trim($param["control"]);
|
|
|
|
if ($control != "") {
|
|
$control = '%' . $param['control'] . '%';
|
|
}
|
|
}
|
|
$instrument = "%%";
|
|
if (isset($param['instrument'])) {
|
|
$instrument = trim($param["instrument"]);
|
|
if ($instrument != "") {
|
|
$instrument = '%' . $param['instrument'] . '%';
|
|
}
|
|
}
|
|
$test = "%%";
|
|
if (isset($param['test'])) {
|
|
$test = trim($param["test"]);
|
|
if ($test != "") {
|
|
$test = '%' . $param['test'] . '%';
|
|
}
|
|
}
|
|
$sdate = "";
|
|
if (isset($param['sdate'])) {
|
|
$sdate = trim($param["sdate"]);
|
|
if ($sdate != "") {
|
|
$sdate = $param['sdate'];
|
|
}
|
|
}
|
|
$ROW_PER_PAGE = 10;
|
|
$start_offset = 0;
|
|
if (isset($param["page"])) {
|
|
if (
|
|
is_numeric($param["page"]) && $param["page"] > 0
|
|
) {
|
|
$start_offset = ($page - 1) * $ROW_PER_PAGE;
|
|
}
|
|
}
|
|
$sql = "select distinct M_QcID, M_QcStatMean, M_QcStatSd, M_QcStatTea,
|
|
M_QcResultID, M_QcResultValue, M_QcResultIsInstrument , M_QcResultDate,
|
|
Nat_QcControlID , Nat_QcControlName , Nat_InstrumentID, Nat_InstrumentName, Nat_TestID , Nat_TestName
|
|
from m_qc_result
|
|
join m_qc on M_QcResultM_QcID = M_QcID
|
|
and M_QcResultDate = ?
|
|
and M_QcResultIsActive ='Y'
|
|
join nat_qc on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID =Nat_QcControlID
|
|
and Nat_QcControlName like ?
|
|
and Nat_QcControlIsActive = 'Y'
|
|
join nat_instrument on Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
and Nat_InstrumentName like ?
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
join nat_test on Nat_QcNat_TestID = Nat_TestID
|
|
and Nat_TestName like ?
|
|
and Nat_TestIsActive = 'Y' limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [
|
|
$sdate, $control, $instrument, $test, $start_offset
|
|
]);
|
|
$last_qry = $this->db->last_query();
|
|
$count =
|
|
"select count(distinct M_QcID) as total
|
|
from m_qc_result
|
|
join m_qc on M_QcResultM_QcID = M_QcID
|
|
and M_QcResultDate = ?
|
|
and M_QcResultIsActive ='Y'
|
|
join nat_qc on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID =Nat_QcControlID
|
|
and Nat_QcControlName like ?
|
|
and Nat_QcControlIsActive = 'Y'
|
|
join nat_instrument on Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
and Nat_InstrumentName like ?
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
join nat_test on Nat_QcNat_TestID = Nat_TestID
|
|
and Nat_TestName like ?
|
|
and Nat_TestIsActive = 'Y' ";
|
|
$qry_total_filter =
|
|
$this->db->query($count, [
|
|
$sdate, $control, $instrument, $test
|
|
]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
if ($sdate == "" or $test == "") {
|
|
echo json_encode([
|
|
"status" => "ERR", "message" => 'test and sdate is mandatory',
|
|
|
|
]);
|
|
} else {
|
|
if (!$qry) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
if (!$qry_total_filter) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry_total_filter
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
$rows = $qry->result_array();
|
|
$total_filter = (int)$qry_total_filter->result_array()[0]["total"];
|
|
|
|
$total = ceil($total_filter / $ROW_PER_PAGE);
|
|
$result = array(
|
|
"total" => $total,
|
|
"total_filter" => $total_filter,
|
|
"records" => $rows,
|
|
"sql" => $last_qry,
|
|
"count" => $last_qry_total_filter
|
|
);
|
|
$this->sys_ok($result);
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
function qc_byid()
|
|
{
|
|
try {
|
|
//# cek token valid
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$param = $this->sys_input;
|
|
$page = $param["page"];
|
|
$controlid = "";
|
|
$instrumentid = "";
|
|
$ROW_PER_PAGE = 10;
|
|
$start_offset = 0;
|
|
if (isset($param["page"])) {
|
|
if (is_numeric($param["page"]) && $param["page"] > 0) {
|
|
$start_offset = ($page - 1) * $ROW_PER_PAGE;
|
|
}
|
|
}
|
|
if (isset($param["controlid"])) {
|
|
if (is_numeric($param["controlid"])) {
|
|
$controlid = $param["controlid"];
|
|
}
|
|
}
|
|
if (isset($param["instrumentid"])) {
|
|
if (is_numeric($param["instrumentid"])) {
|
|
$instrumentid = $param["instrumentid"];
|
|
}
|
|
}
|
|
$sdate = "";
|
|
if (isset($param['sdate'])) {
|
|
$sdate = trim($param["sdate"]);
|
|
if ($sdate != "") {
|
|
$sdate = $param['sdate'];
|
|
}
|
|
}
|
|
$test = "%%";
|
|
if (isset($param['test'])) {
|
|
$test = trim($param["test"]);
|
|
if ($test != "") {
|
|
$test = '%' . $param['test'] . '%';
|
|
}
|
|
}
|
|
$sql = "select distinct M_QcID, M_QcStatMean, M_QcStatSd, M_QcStatTea,
|
|
M_QcResultID, M_QcResultValue, M_QcResultIsInstrument , M_QcResultDate,
|
|
Nat_QcControlID , Nat_QcControlName , Nat_InstrumentID, Nat_InstrumentName, Nat_TestID , Nat_TestName
|
|
from m_qc_result
|
|
join m_qc on M_QcResultM_QcID = M_QcID
|
|
and M_QcResultDate = ? and M_QcResultIsActive ='Y'
|
|
join nat_qc on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID =Nat_QcControlID
|
|
and Nat_QcControlID = ?
|
|
and Nat_QcControlIsActive = 'Y'
|
|
join nat_instrument on Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
and Nat_InstrumentID = ?
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
join nat_test on Nat_QcNat_TestID = Nat_TestID
|
|
and Nat_TestName like ?
|
|
and Nat_TestIsActive = 'Y' limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$sdate, $controlid, $instrumentid, $test, $start_offset]);
|
|
$last_qry = $this->db->last_query();
|
|
$count = "select count(distinct M_QcID) as total
|
|
from m_qc_result
|
|
join m_qc on M_QcResultM_QcID = M_QcID
|
|
and M_QcResultDate = ? and M_QcResultIsActive ='Y'
|
|
join nat_qc on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID =Nat_QcControlID
|
|
and Nat_QcControlID = ?
|
|
and Nat_QcControlIsActive = 'Y'
|
|
join nat_instrument on Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
and Nat_InstrumentID = ?
|
|
and Nat_InstrumentIsActive = 'Y'
|
|
join nat_test on Nat_QcNat_TestID = Nat_TestID
|
|
and Nat_TestName like ?
|
|
and Nat_TestIsActive = 'Y'";
|
|
$qry_total_filter = $this->db->query($count, [$sdate, $controlid, $instrumentid, $test]);
|
|
$last_qry_total_filter = $this->db->last_query();
|
|
if ($controlid == "" or $instrumentid == "" or $sdate == "") {
|
|
echo json_encode([
|
|
"status" => "ERR", "message" => 'control, instrument, and sdate is mandatory',
|
|
]);
|
|
} else {
|
|
if (!$qry) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
if (!$qry_total_filter) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_qry_total_filter
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
$rows = $qry->result_array();
|
|
$total_filter = (int)$qry_total_filter->result_array()[0]["total"];
|
|
|
|
$total = ceil($total_filter / $ROW_PER_PAGE);
|
|
$result = array(
|
|
"total" => $total,
|
|
"total_filter" => $total_filter,
|
|
"records" => $rows,
|
|
"sql" => $last_qry,
|
|
"count" => $last_qry_total_filter
|
|
);
|
|
$this->sys_ok($result);
|
|
}
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
function control_byname()
|
|
{
|
|
try {
|
|
//# cek token valid
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$param = $this->sys_input;
|
|
// $page = 0;
|
|
$page = $param['page'];
|
|
|
|
// check input parameter
|
|
|
|
// mandatory
|
|
$control = trim($param['control']);
|
|
$control = "%" . $control . "%";
|
|
|
|
// tidak mandatory
|
|
$instrument = "";
|
|
if (isset($param['instrument'])) {
|
|
$instrument = trim($param["instrument"]);
|
|
if ($instrument != "") {
|
|
$instrument = '%' . $param['instrument'] . '%';
|
|
}
|
|
}
|
|
$test = "";
|
|
if (isset($param['test'])) {
|
|
$test = trim($param["test"]);
|
|
if ($test != "") {
|
|
$test = '%' . $param['test'] . '%';
|
|
}
|
|
}
|
|
|
|
// hitung start_offset
|
|
$ROW_PER_PAGE = 10;
|
|
$start_offset = 0;
|
|
if (isset($param["page"])) {
|
|
if (is_numeric($param["page"]) && $param['page'] > 0) {
|
|
$start_offset = ($page - 1) * $ROW_PER_PAGE;
|
|
}
|
|
}
|
|
|
|
// query
|
|
if ($instrument == "" && $test == "") {
|
|
$sql = "select
|
|
distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$control, $start_offset]);
|
|
$last_query = $this->db->last_query();
|
|
|
|
$sql_total_filter = "select
|
|
count(distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName) as total_filter
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?";
|
|
$qry_total_filter = $this->db->query($sql_total_filter, [$control]);
|
|
$last_query_total_filter = $this->db->last_query();
|
|
} else if ($instrument !== "" && $test == "") {
|
|
// instrument terisi dan tes kosong
|
|
$sql = "select
|
|
distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$control, $instrument, $start_offset]);
|
|
$last_query = $this->db->last_query();
|
|
|
|
$sql_total_filter = "select
|
|
count(distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName) as total_filter
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentName like ?";
|
|
$qry_total_filter = $this->db->query($sql_total_filter, [$control, $instrument]);
|
|
$last_query_total_filter = $this->db->last_query();
|
|
} else if ($instrument == "" && $test !== "") {
|
|
// instrument kosong dan tes terisi
|
|
$sql = "select
|
|
distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
AND Nat_TestName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$control, $test, $start_offset]);
|
|
$last_query = $this->db->last_query();
|
|
|
|
$sql_total_filter = "select
|
|
count(distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName) as total_filter
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
AND Nat_TestName like ?";
|
|
$qry_total_filter = $this->db->query($sql_total_filter, [$control, $test]);
|
|
$last_query_total_filter = $this->db->last_query();
|
|
} else {
|
|
// instrument dan test terisi
|
|
$sql = "select
|
|
distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentName like ?
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
AND Nat_TestName like ?
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$control, $instrument, $test, $start_offset]);
|
|
$last_query = $this->db->last_query();
|
|
|
|
$sql_total_filter = "select
|
|
count(distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName) as total_filter
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
AND Nat_QcControlName like ?
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentName like ?
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
AND Nat_TestName like ?";
|
|
$qry_total_filter = $this->db->query($sql_total_filter, [$control, $instrument, $test]);
|
|
$last_query_total_filter = $this->db->last_query();
|
|
}
|
|
|
|
if (!$qry) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_query
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
if (!$qry_total_filter) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_query_total_filter
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
$rows = $qry->result_array();
|
|
$rows_total_filter = $qry_total_filter->result_array();
|
|
$total = ceil($rows_total_filter[0]["total_filter"] / $ROW_PER_PAGE);
|
|
$result = array(
|
|
"total" => $total,
|
|
"total_filter" => (int)$rows_total_filter[0]["total_filter"],
|
|
"records" => $rows,
|
|
"sql" => $this->db->last_query()
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
function control_byid()
|
|
{
|
|
try {
|
|
//# cek token valid
|
|
if (!$this->isLogin) {
|
|
$this->sys_error("Invalid Token");
|
|
exit;
|
|
}
|
|
$param = $this->sys_input;
|
|
$page = $param['page'];
|
|
|
|
// check input parameter
|
|
|
|
// mandatory
|
|
$id = 0;
|
|
if (isset($param['id'])) {
|
|
if (is_numeric($param['id'])) {
|
|
$id = $param['id'];
|
|
}
|
|
}
|
|
|
|
// hitung start_offset
|
|
$ROW_PER_PAGE = 10;
|
|
$start_offset = 0;
|
|
if (isset($param["page"])) {
|
|
if (is_numeric($param["page"]) && $param['page'] > 0) {
|
|
$start_offset = ($page - 1) * $ROW_PER_PAGE;
|
|
}
|
|
}
|
|
|
|
|
|
$sql = "select
|
|
distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName,
|
|
Nat_InstrumentID,
|
|
Nat_InstrumentName,
|
|
Nat_TestID,
|
|
Nat_TestName
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentID = ?
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'
|
|
limit 10 offset ?";
|
|
$qry = $this->db->query($sql, [$id, $start_offset]);
|
|
$last_query = $this->db->last_query();
|
|
|
|
$sql_total_filter = "select
|
|
count(distinct Nat_QcControlID,
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber,
|
|
Nat_QcControlExpired,
|
|
Nat_QcLevelID,
|
|
Nat_QcLevelName,
|
|
Nat_InstrumentID,
|
|
Nat_InstrumentName,
|
|
Nat_TestID,
|
|
Nat_TestName) as total_filter
|
|
from nat_qc_control
|
|
join nat_qc_level
|
|
ON Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
AND Nat_QcControlIsActive = 'Y'
|
|
AND Nat_QcLevelIsActive = 'Y'
|
|
join nat_qc
|
|
ON Nat_QcControlID = Nat_QcNat_QcControlID
|
|
AND Nat_QcIsActive = 'Y'
|
|
join nat_instrument
|
|
ON Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
AND Nat_InstrumentIsActive = 'Y'
|
|
AND Nat_InstrumentID = ?
|
|
join nat_test
|
|
ON Nat_QcNat_TestID = Nat_TestID
|
|
AND Nat_TestIsActive = 'Y'";
|
|
$qry_total_filter = $this->db->query($sql_total_filter, [$id]);
|
|
$last_query_total_filter = $this->db->last_query();
|
|
|
|
if (!$qry) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_query
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
if (!$qry_total_filter) {
|
|
$this->db->trans_rollback();
|
|
$error = array(
|
|
"message" => $this->db->error()["message"],
|
|
"sql" => $last_query_total_filter
|
|
);
|
|
$this->sys_error_db($error);
|
|
exit;
|
|
}
|
|
|
|
$rows = $qry->result_array();
|
|
$rows_total_filter = $qry_total_filter->result_array();
|
|
$total = ceil($rows_total_filter[0]["total_filter"] / $ROW_PER_PAGE);
|
|
|
|
$result = array(
|
|
"total" => $total,
|
|
"total_filter" => (int)$rows_total_filter[0]["total_filter"],
|
|
"records" => $rows,
|
|
"sql" => $this->db->last_query()
|
|
);
|
|
$this->sys_ok($result);
|
|
} catch (Exception $exc) {
|
|
$message = $exc->getMessage();
|
|
$this->sys_error($message);
|
|
}
|
|
}
|
|
}
|