440 lines
16 KiB
PHP
440 lines
16 KiB
PHP
<?php
|
|
class Qc extends MY_Controller {
|
|
function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->resp = array(
|
|
"status" => "ERR",
|
|
"message" => ""
|
|
);
|
|
}
|
|
function now() {
|
|
return date("Y-m-d H:i:s");
|
|
}
|
|
function reply() {
|
|
echo json_encode($this->resp);
|
|
exit;
|
|
}
|
|
function get_data($from,$to,$m_qcID) {
|
|
$sql = "select
|
|
Nat_QcControlName,
|
|
Nat_QcControlLotNumber, Nat_QcControlExpired,
|
|
Nat_QcLevelName, Nat_InstrumentName, Nat_TestName,
|
|
M_QcResultDate, M_QcResultValue, ifnull(Nat_QcRuleCode,'') Marking,
|
|
Nat_QcMean,Nat_QcSd,Nat_QcTea,
|
|
M_QcIsStat, M_QcStatMean, M_QcStatSd
|
|
from m_qc_result
|
|
join m_qc
|
|
on M_QcResultM_QcID = M_QcID
|
|
and M_QcResultIsActive = 'Y'
|
|
and M_QcID = ?
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?
|
|
join nat_qc
|
|
on M_QcNat_QcID = Nat_QcID
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
join nat_qc_level on Nat_QcControlNat_QcLevelID = Nat_QcLevelID
|
|
join nat_instrument on Nat_QcNat_InstrumentID = Nat_InstrumentID
|
|
join nat_test on Nat_QcNat_TestID = Nat_TestID
|
|
left join nat_qc_rule
|
|
on M_QcResultNat_QcRuleID = Nat_QcRuleID
|
|
";
|
|
|
|
$qry = $this->db->query($sql, array($m_qcID, $from, $to));
|
|
//echo $this->db->last_query();
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$result = array();
|
|
foreach($rows as $idx => $r) {
|
|
if($idx == 0) {
|
|
$isStat = $r["M_QcIsStat"] == "Y";
|
|
$result = array(
|
|
"control" => $r["Nat_QcControlName"],
|
|
"level" => $r["Nat_QcLevelName"],
|
|
"lotNumber" => $r["Nat_QcControlLotNumber"],
|
|
"instrument" => $r["Nat_InstrumentName"],
|
|
"ed" => $r["Nat_QcControlExpired"],
|
|
"test" => $r["Nat_TestName"],
|
|
"mean" => $isStat ? $r["M_QcStatMean"] : $r["Nat_QcMean"],
|
|
"sd" => $isStat ? $r["M_QcStatSd"] : $r["Nat_QcSd"],
|
|
"tea" => $r["Nat_QcTea"],
|
|
"value" => array(),
|
|
"mark" => array(),
|
|
"date" => array()
|
|
);
|
|
}
|
|
$result["date"][] = $r["M_QcResultDate"];
|
|
$result["value"][] = $r["M_QcResultValue"];
|
|
$result["mark"][] = $r["Marking"];
|
|
}
|
|
return $result;
|
|
}
|
|
function data($from,$to,$p_ids) {
|
|
$result = array();
|
|
$ids = explode("-",$p_ids);
|
|
foreach($ids as $id) {
|
|
$result[] = $this->get_data($from,$to,$id);
|
|
}
|
|
$this->resp["status"] = "OK";
|
|
$this->resp["data"] = $result;
|
|
$this->reply();
|
|
}
|
|
|
|
function calc_multi($from,$to,$ids) {
|
|
$a_id = explode("-",$ids);
|
|
$ids = implode(",",$a_id);
|
|
$sql = "select
|
|
M_QcResultID,
|
|
M_QcResultDate,
|
|
M_QcResultValue,
|
|
if(M_QcIsStat = 'N', Nat_QcMean, M_QcStatMean ) Nat_QcMean,
|
|
if(M_QcIsStat = 'N', Nat_QcSd, M_QcStatSd ) Nat_QcSd,
|
|
Nat_QcTea
|
|
from
|
|
m_qc_result
|
|
join m_qc
|
|
on M_QcResultM_QcID = M_QcID
|
|
and M_QcIsActive = 'Y'
|
|
join nat_qc
|
|
on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
join nat_qc_control on Nat_QcNat_QcControlID = Nat_QcControlID
|
|
where M_QcResultM_QcID in ($ids)
|
|
and M_QcResultIsActive = 'Y'
|
|
and M_QcResultDate >= $from
|
|
and M_QcResultDate <= $to
|
|
order by M_QcResultDate, Nat_QcControlNat_QcLevelID";
|
|
$qry = $this->db->query($sql);
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
foreach($rows as $idx => $r) {
|
|
$mean = $r["Nat_QcMean"];
|
|
$sd = $r["Nat_QcSd"];
|
|
$TEa = $r["Nat_QcTea"];
|
|
$value = $r["M_QcResultValue"];
|
|
$id = $r["M_QcResultID"];
|
|
$ruleID = 0;
|
|
if ($ruleID == 0 && $idx > 0 ) {
|
|
if ($ruleID == 0 && $idx > 11) {
|
|
$ruleID = $this->is_12x($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 9) {
|
|
$ruleID = $this->is_10x($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 6) {
|
|
$ruleID = $this->is_7t($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 4) {
|
|
$ruleID = $this->is_4_1s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
|
|
$xruleID = $this->is_1_2s($idx,$value,$mean,$sd,$rows);
|
|
if ($xruleID > 0) {
|
|
$ruleID = $xruleID;
|
|
}
|
|
$xruleID = $this->is_2_2s($idx,$value,$mean,$sd,$rows);
|
|
if ($xruleID > 0) {
|
|
$ruleID = $xruleID;
|
|
}
|
|
|
|
if ($ruleID == 0 ) {
|
|
$ruleID = $this->is_r_4s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
|
|
} else {
|
|
$ruleID = $this->is_1_2s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
$sql = "update m_qc_result
|
|
set M_QcResultNat_QcRuleID = ?
|
|
where M_QcResultID = ?
|
|
and M_QcResultDate >= $from
|
|
and M_QcResultDate <= $to";
|
|
$qry = $this->db->query($sql,array($ruleID,$id));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
|
|
}
|
|
$this->resp["status"] = "OK";
|
|
$this->reply();
|
|
}
|
|
|
|
function calc($from= null,$to= null,$qcID= null) {
|
|
$COUNT_THRESHOLD = 90;
|
|
|
|
|
|
|
|
$sql = "select count(*) count,
|
|
avg(M_QcResultValue) mean,
|
|
STDDEV_SAMP(M_QcResultValue) sd
|
|
from
|
|
m_qc_result
|
|
where M_QcResultM_QcID = ?
|
|
and M_QcResultIsActive = 'Y'
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?";
|
|
$qry = $this->db->query($sql, array($qcID,$from,$to));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
$count_act = $rows[0]["count"];
|
|
$sd_act = $rows[0]["sd"];
|
|
$mean_act = $rows[0]["mean"];
|
|
//Sementara TEa di abaikan
|
|
if ($count_act > $COUNT_THRESHOLD) {
|
|
$sql = "update
|
|
m_qc
|
|
set M_QcIsStat = 'Y',
|
|
M_QcStatSd = ?, M_QcStatMean = ?
|
|
where M_QcID = ?
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?";
|
|
$this->db->query($sql, array($sd_act,$mean_act,$qcID,$from,$to));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
} else {
|
|
$sql = "update
|
|
m_qc
|
|
set M_QcIsStat = 'N'
|
|
where M_QcID = ?";
|
|
$this->db->query($sql, array($qcID));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
}
|
|
//reset M_QcResultNat_QcRuleID
|
|
$sql = "update m_qc_result set M_QcResultNat_QcRuleID = 0
|
|
where M_QcResultM_QcID = ?
|
|
and M_QcResultIsActive = 'Y'
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?
|
|
";
|
|
$qry = $this->db->query($sql, array($qcID,$from,$to));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR Reset Nat_QcRuleID : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
$sql = "select
|
|
M_QcResultID,
|
|
M_QcResultDate,
|
|
M_QcResultValue,
|
|
Nat_QcMean,
|
|
Nat_QcSd,
|
|
Nat_QcTea
|
|
from
|
|
m_qc_result
|
|
join m_qc
|
|
on M_QcResultM_QcID = M_QcID
|
|
and M_QcIsActive = 'Y'
|
|
join nat_qc
|
|
on M_QcNat_QcID = Nat_QcID
|
|
and Nat_QcIsActive = 'Y'
|
|
where M_QcResultM_QcID = ?
|
|
and M_QcResultIsActive = 'Y'
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?
|
|
order by M_QcResultDate";
|
|
$qry = $this->db->query($sql, array($qcID,$from,$to));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
$rows = $qry->result_array();
|
|
foreach($rows as $idx => $r) {
|
|
$mean = $r["Nat_QcMean"];
|
|
$sd = $r["Nat_QcSd"];
|
|
$TEa = $r["Nat_QcTea"];
|
|
if ($count_act > $COUNT_THRESHOLD) {
|
|
$mean = $mean_act;
|
|
$sd = $sd_act;
|
|
}
|
|
$value = $r["M_QcResultValue"];
|
|
$id = $r["M_QcResultID"];
|
|
$ruleID = 0;
|
|
if ($ruleID == 0 && $idx > 0 ) {
|
|
if ($ruleID == 0 && $idx > 11) {
|
|
$ruleID = $this->is_12x($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 9) {
|
|
$ruleID = $this->is_10x($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 6) {
|
|
$ruleID = $this->is_7t($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
if ($ruleID == 0 && $idx > 4) {
|
|
$ruleID = $this->is_4_1s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
|
|
$xruleID = $this->is_1_2s($idx,$value,$mean,$sd,$rows);
|
|
if ($xruleID > 0) {
|
|
$ruleID = $xruleID;
|
|
}
|
|
$xruleID = $this->is_2_2s($idx,$value,$mean,$sd,$rows);
|
|
if ($xruleID > 0) {
|
|
$ruleID = $xruleID;
|
|
}
|
|
|
|
if ($ruleID == 0 ) {
|
|
$ruleID = $this->is_r_4s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
|
|
} else {
|
|
$ruleID = $this->is_1_2s($idx,$value,$mean,$sd,$rows);
|
|
}
|
|
$sql = "update m_qc_result
|
|
set M_QcResultNat_QcRuleID = ?
|
|
where M_QcResultID = ?
|
|
and M_QcResultDate >= ?
|
|
and M_QcResultDate <= ?";
|
|
$qry = $this->db->query($sql,array($ruleID,$id,$from,$to));
|
|
if (!$qry) {
|
|
$this->resp["message"] = "{$this->now()} ERR : " . $this->db->error()["message"] . "\n"
|
|
. "|" . $this->db->last_query() . "\n";
|
|
$this->reply();
|
|
}
|
|
|
|
}
|
|
$this->resp["status"] = "OK";
|
|
$this->reply();
|
|
|
|
}
|
|
|
|
// 1 : 1 2s
|
|
function is_1_2s($idx,$value,$mean,$sd,$rows) {
|
|
//echo "$value | $mean | $sd \n";
|
|
if ($value < $mean - 2 * $sd || $value > $mean + 2*$sd) {
|
|
return 1;
|
|
}
|
|
return 0;
|
|
}
|
|
// 7 : 2 2s
|
|
function is_2_2s($idx,$value,$mean,$sd,$rows) {
|
|
$prev_value = $rows[$idx-1]["M_QcResultValue"];
|
|
$sd2 = $mean + 2 * $sd;
|
|
$sd2p = $mean - 2 * $sd;
|
|
if ( $value < $mean - 2 * $sd && $prev_value < $mean - 2*$sd) {
|
|
return 7;
|
|
}
|
|
if ( $value > $mean + 2 * $sd && $prev_value > $mean + 2*$sd) {
|
|
return 7;
|
|
}
|
|
return 0;
|
|
}
|
|
// 2 : R 4s
|
|
function is_R_4s($idx,$value,$mean,$sd,$rows) {
|
|
$prev_value = $rows[$idx-1]["M_QcResultValue"];
|
|
|
|
if ( $value < $mean - 2 * $sd && $prev_value > $mean + 2*$sd) {
|
|
return 2;
|
|
}
|
|
if ( $value > $mean + 2 * $sd && $prev_value < $mean - 2*$sd) {
|
|
return 2;
|
|
}
|
|
return 0;
|
|
}
|
|
// 3 : 4 1s
|
|
function is_4_1s($idx,$value,$mean,$sd,$rows) {
|
|
$value3 = $rows[$idx-1]["M_QcResultValue"];
|
|
$value2 = $rows[$idx-2]["M_QcResultValue"];
|
|
$value1 = $rows[$idx-3]["M_QcResultValue"];
|
|
$val_1sd = $mean + $sd;
|
|
$val_1sd_neg = $mean - $sd;
|
|
// echo "$value1 | $value2 | $value3 | $value | $val_1sd | $val_1sd_neg \n";
|
|
if ($value1 > $val_1sd && $value2 > $val_1sd && $value3 > $val_1sd && $value > $val_1sd ) {
|
|
return 3;
|
|
}
|
|
if ($value1 < $val_1sd_neg && $value2 < $val_1sd_neg && $value3 < $val_1sd_neg && $value < $val_1sd_neg ) {
|
|
return 3;
|
|
}
|
|
return 0;
|
|
}
|
|
// 6 : 7t
|
|
function is_7t($idx,$value,$mean,$sd,$rows) {
|
|
$value6 = $rows[$idx-1]["M_QcResultValue"];
|
|
$value5 = $rows[$idx-2]["M_QcResultValue"];
|
|
$value4 = $rows[$idx-3]["M_QcResultValue"];
|
|
$value3 = $rows[$idx-4]["M_QcResultValue"];
|
|
$value2 = $rows[$idx-5]["M_QcResultValue"];
|
|
$value1 = $rows[$idx-6]["M_QcResultValue"];
|
|
if ($value1 < $value2 && $value2 < $value3 && $value3 < $value4 && $value4 < $value5 &&
|
|
$value5 < $value6 && $value6 < $value ) {
|
|
return 6;
|
|
}
|
|
if ( $value1 > $value2 && $value2 > $value3 && $value3 > $value4 && $value4 > $value5 &&
|
|
$value5 > $value6 && $value6 > $value ) {
|
|
return 6;
|
|
}
|
|
return 0;
|
|
}
|
|
// 4 : 10x
|
|
function is_10x($idx,$value,$mean,$sd,$rows) {
|
|
$value9 = $rows[$idx-1]["M_QcResultValue"];
|
|
$value8 = $rows[$idx-2]["M_QcResultValue"];
|
|
$value7 = $rows[$idx-3]["M_QcResultValue"];
|
|
$value6 = $rows[$idx-4]["M_QcResultValue"];
|
|
$value5 = $rows[$idx-5]["M_QcResultValue"];
|
|
$value4 = $rows[$idx-6]["M_QcResultValue"];
|
|
$value3 = $rows[$idx-7]["M_QcResultValue"];
|
|
$value2 = $rows[$idx-8]["M_QcResultValue"];
|
|
$value1 = $rows[$idx-9]["M_QcResultValue"];
|
|
$val_1sd = $mean + $sd;
|
|
$val_1sd_neg = $mean - $sd;
|
|
if ($value1 < $mean && $value2 < $mean && $value3 < $mean && $value4 < $mean && $value5 < $mean &&
|
|
$value6 < $mean && $value7 < $mean && $value8 < $mean && $value9 < $mean && $value < $mean ) {
|
|
return 4;
|
|
}
|
|
if ($value1 > $mean && $value2 > $mean && $value3 > $mean && $value4 > $mean && $value5 > $mean &&
|
|
$value6 > $mean && $value7 > $mean && $value8 > $mean && $value9 > $mean && $value > $mean ) {
|
|
return 4;
|
|
}
|
|
return 0;
|
|
}
|
|
// 5 : 12x
|
|
function is_12x($idx,$value,$mean,$sd,$rows) {
|
|
$value11= $rows[$idx-1]["M_QcResultValue"];
|
|
$value10= $rows[$idx-2]["M_QcResultValue"];
|
|
$value9 = $rows[$idx-3]["M_QcResultValue"];
|
|
$value8 = $rows[$idx-4]["M_QcResultValue"];
|
|
$value7 = $rows[$idx-5]["M_QcResultValue"];
|
|
$value6 = $rows[$idx-6]["M_QcResultValue"];
|
|
$value5 = $rows[$idx-7]["M_QcResultValue"];
|
|
$value4 = $rows[$idx-8]["M_QcResultValue"];
|
|
$value3 = $rows[$idx-9]["M_QcResultValue"];
|
|
$value2 = $rows[$idx-10]["M_QcResultValue"];
|
|
$value1 = $rows[$idx-11]["M_QcResultValue"];
|
|
if ($value1 < $mean && $value2 < $mean && $value3 < $mean && $value4 < $mean && $value5 < $mean &&
|
|
$value6 < $mean && $value7 < $mean && $value8 < $mean && $value9 < $mean && $value10 < $mean &&
|
|
$value11 < $mean && $value < $mean ) {
|
|
return 5;
|
|
}
|
|
if ($value1 > $mean && $value2 > $mean && $value3 > $mean && $value4 > $mean && $value5 > $mean &&
|
|
$value6 > $mean && $value7 > $mean && $value8 > $mean && $value9 > $mean && $value10 > $mean &&
|
|
$value11 > $mean && $value > $mean ) {
|
|
return 5;
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
}
|
|
?>
|