601 lines
24 KiB
PHP
601 lines
24 KiB
PHP
<?php
|
|
namespace App\Models\results;
|
|
|
|
use CodeIgniter\Model;
|
|
|
|
class M415Model extends Model
|
|
{
|
|
|
|
public function getTotalCount($data)
|
|
{
|
|
$sql = "SELECT COUNT(*) AS cnt FROM ( ";
|
|
$sql .= "SELECT
|
|
*
|
|
FROM (
|
|
SELECT
|
|
stan_date,
|
|
'M' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type IN ('M','O')
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'T' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'T'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'D' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'D'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'N' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'N'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
|
|
) summary
|
|
GROUP BY stan_date ) AS t ";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getRow()->cnt;
|
|
}
|
|
|
|
public function getResultList($data)
|
|
{
|
|
$sql = "SELECT
|
|
stan_date,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'M' THEN doc ELSE NULL END)),1,8) m_doc,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'M' THEN cert ELSE NULL END)),1,8) m_cert,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'M' THEN tot ELSE NULL END)),1,8) m_tot,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'T' THEN doc ELSE NULL END)),1,8) t_doc,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'T' THEN cert ELSE NULL END)),1,8) t_cert,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'T' THEN tot ELSE NULL END)),1,8) t_tot,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'D' THEN doc ELSE NULL END)),1,8) d_doc,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'D' THEN cert ELSE NULL END)),1,8) d_cert,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'D' THEN tot ELSE NULL END)),1,8) d_tot,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'N' THEN doc ELSE NULL END)),1,8) n_doc,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'N' THEN cert ELSE NULL END)),1,8) n_cert,
|
|
SUBSTR(SEC_TO_TIME(AVG(CASE vrfc_type WHEN 'N' THEN tot ELSE NULL END)),1,8) n_tot
|
|
FROM (
|
|
SELECT
|
|
stan_date,
|
|
'M' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type IN ('M','O')
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'T' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'T'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'D' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'D'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
stan_date,
|
|
'N' vrfc_type,
|
|
AVG((TIME_TO_SEC(conf_required_tm))) AS doc,
|
|
AVG((TIME_TO_SEC(cert_required_tm))) AS cert,
|
|
AVG((TIME_TO_SEC(tot_required_tm))) AS tot
|
|
FROM v2_time_required
|
|
WHERE stan_date BETWEEN '{$data['sdate']}' AND '{$data['edate']}'
|
|
AND vrfc_type = 'N'
|
|
AND tot_required_tm IS NOT NULL
|
|
GROUP BY stan_date
|
|
|
|
|
|
) summary
|
|
GROUP BY stan_date ";
|
|
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getResultArray();
|
|
}
|
|
|
|
|
|
// 팩스 인입시간
|
|
public function getFaxSaveTime($vr_sq)
|
|
{
|
|
$sql = "SELECT recv_time from fax_imgs" .
|
|
" WHERE vr_sq = ?" .
|
|
" ORDER BY fax_sq DESC" .
|
|
" LIMIT 1";
|
|
$data = [
|
|
$vr_sq
|
|
];
|
|
$query = $this->db->query($sql, $data);
|
|
|
|
$res = $query->getRowArray();
|
|
return $res;
|
|
}
|
|
|
|
// 서류/전화 불일치 시간
|
|
public function getFaxFailTimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '39'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = [
|
|
$vr_sq
|
|
];
|
|
$query = $this->db->query($sql, $data);
|
|
// echo $this->db->last_query().'<br>';
|
|
$res = $query->row_array();
|
|
return $res;
|
|
}
|
|
|
|
// 서류/전화 확인완료 시간 = 등기부등본 확인중 시간
|
|
public function getConfTimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '35'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
// 검증완료 시간
|
|
public function get_60_ForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '60'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
// 등기부등본 확인중 시간, 서류에서 등기로 넘어갈때 간혹 서류전화 확인 완료가 안찍히는 건들이 있다
|
|
public function get_cert_ing_TimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '40'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
// echo $this->db->last_query().'<br>';
|
|
$res = $query->row_array();
|
|
|
|
return $res;
|
|
}
|
|
|
|
//서류전화 (검증실패/검증완료)
|
|
public function insert_v2_time_required_Conf_Done($atcl_no, $cpid, $vrfc_type, $insert_tm, $tel_doc_conf_dt, $finishTime)
|
|
{
|
|
if (substr($insert_tm, 0, 10) == substr($tel_doc_conf_dt, 0, 10)) {
|
|
switch ($vrfc_type) {
|
|
case 'D':
|
|
if (('12:00:00' < substr($insert_tm, -8)) && (substr($insert_tm, -8) < '13:00:00')) {//접수시간이 12~13시 사이면 13시로 해준다
|
|
if (substr($tel_doc_conf_dt, -8) > '13:00:00') { //검증완료가 13시 '이후'에 끝나면 접수시간을 13시로 변경해준다
|
|
$insert_tm = date("Y-m-d", time()) . " 13:00:00";
|
|
}
|
|
} else if (
|
|
(substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') && //접수와 확인완료시간이 모두 17:30:00 ~ 09:00:00 사이가 아니면
|
|
(substr($tel_doc_conf_dt, -8) < '17:30:00' || substr($tel_doc_conf_dt, -8) < '08:59:59')
|
|
) {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
;
|
|
break;
|
|
|
|
case 'T':
|
|
if (('13:00:00' < substr($insert_tm, -8)) && (substr($insert_tm, -8) < '14:00:00')) {//접수시간이 13~14시 사이면 14시로 해준다
|
|
if (substr($tel_doc_conf_dt, -8) > '14:00:00') { //검증완료가 14시 '이후'에 끝나면 접수시간을 14시로 변경해준다
|
|
$insert_tm = date("Y-m-d", time()) . " 14:00:00";
|
|
}
|
|
} else if (
|
|
(substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') &&
|
|
(substr($tel_doc_conf_dt, -8) < '17:30:00' || substr($tel_doc_conf_dt, -8) < '08:59:59')
|
|
) { //접수와 확인완료시간이 모두 17:30:00 ~ 09:00:00 사이면
|
|
$insert_tm = date("Y-m-d", time()) . " 09:30:00";
|
|
}
|
|
;
|
|
break;
|
|
}
|
|
} else {
|
|
if ($vrfc_type == 'D') { //홍보
|
|
if (substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
} else { //전화
|
|
if (substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:30:00";
|
|
}
|
|
}
|
|
}
|
|
$stan_date = substr($insert_tm, 0, 10);
|
|
|
|
$sql = "INSERT INTO v2_time_required(stan_date, atcl_no, cpid, vrfc_type, insert_tm, tel_doc_conf_dt,conf_required_tm,tot_required_tm)" .
|
|
" VALUES(?, ?, ?, ?, ?, ?, TIMEDIFF(?,?), TIMEDIFF(?,?)) " .
|
|
" ON DUPLICATE KEY UPDATE insert_tm = VALUES(insert_tm), tel_doc_conf_dt=VALUES(tel_doc_conf_dt),conf_required_tm = VALUES(conf_required_tm),tot_required_tm = VALUES(tot_required_tm) ";
|
|
$data = [
|
|
$stan_date,
|
|
$atcl_no,
|
|
$cpid,
|
|
$vrfc_type,
|
|
$insert_tm,
|
|
$tel_doc_conf_dt,
|
|
$tel_doc_conf_dt,
|
|
$insert_tm,
|
|
$tel_doc_conf_dt,
|
|
$insert_tm
|
|
];
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
//서류전화 확인완료(일치/불일치)
|
|
//서류전화 (검증실패/검증완료)
|
|
public function insert_v2_time_required_Conf($atcl_no, $cpid, $vrfc_type, $insert_tm, $tel_doc_conf_dt, $finishTime)
|
|
{
|
|
if (substr($insert_tm, 0, 10) == substr($tel_doc_conf_dt, 0, 10)) {
|
|
switch ($vrfc_type) {
|
|
case 'D':
|
|
if (('12:00:00' < substr($insert_tm, -8)) && (substr($insert_tm, -8) < '13:00:00')) {//접수시간이 12~13시 사이면 13시로 해준다
|
|
if (substr($tel_doc_conf_dt, -8) > '13:00:00') { //검증완료가 13시 '이후'에 끝나면 접수시간을 13시로 변경해준다
|
|
$insert_tm = date("Y-m-d", time()) . " 13:00:00";
|
|
}
|
|
} else if (
|
|
(substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') && //접수와 확인완료시간이 모두 17:30:00 ~ 09:00:00 사이가 아니면
|
|
(substr($tel_doc_conf_dt, -8) < '17:30:00' || substr($tel_doc_conf_dt, -8) < '08:59:59')
|
|
) {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
;
|
|
break;
|
|
|
|
case 'T':
|
|
if (('13:00:00' < substr($insert_tm, -8)) && (substr($insert_tm, -8) < '14:00:00')) {//접수시간이 13~14시 사이면 14시로 해준다
|
|
if (substr($tel_doc_conf_dt, -8) > '14:00:00') { //검증완료가 14시 '이후'에 끝나면 접수시간을 14시로 변경해준다
|
|
$insert_tm = date("Y-m-d", time()) . " 14:00:00";
|
|
}
|
|
} else if (
|
|
(substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') &&
|
|
(substr($tel_doc_conf_dt, -8) < '17:30:00' || substr($tel_doc_conf_dt, -8) < '08:59:59')
|
|
) { //접수와 확인완료시간이 모두 17:30:00 ~ 09:00:00 사이면
|
|
$insert_tm = date("Y-m-d", time()) . " 09:30:00";
|
|
}
|
|
;
|
|
break;
|
|
}
|
|
} else {
|
|
if ($vrfc_type == 'D') { //홍보
|
|
if (substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
} else { //전화
|
|
if (substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:30:00";
|
|
}
|
|
}
|
|
}
|
|
$stan_date = substr($insert_tm, 0, 10);
|
|
|
|
$sql = "INSERT INTO v2_time_required(stan_date, atcl_no, cpid, vrfc_type, insert_tm, tel_doc_conf_dt,conf_required_tm,tot_required_tm)" .
|
|
" VALUES(?, ?, ?, ?, ?, ?, TIMEDIFF(?,?), TIMEDIFF(?,?)) " .
|
|
" ON DUPLICATE KEY UPDATE insert_tm = VALUES(insert_tm), tel_doc_conf_dt=VALUES(tel_doc_conf_dt),conf_required_tm = VALUES(conf_required_tm),tot_required_tm = VALUES(tot_required_tm) ";
|
|
$data = array(
|
|
$stan_date,
|
|
$atcl_no,
|
|
$cpid,
|
|
$vrfc_type,
|
|
$insert_tm,
|
|
$tel_doc_conf_dt,
|
|
$tel_doc_conf_dt,
|
|
$insert_tm,
|
|
$tel_doc_conf_dt,
|
|
$insert_tm
|
|
);
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
//전화확인 - 통화실패 카운트
|
|
public function getCallfailForHistory($vr_sq)
|
|
{
|
|
$sql = "SELECT 'x' from v2_chg_history" .
|
|
" WHERE vr_sq = ?" .
|
|
" AND chg_type= 'C29'" .
|
|
" ORDER BY seq desc" .
|
|
" LIMIT 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
// 서류/전화 인입시간
|
|
public function getSaveTimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '30'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
|
|
$res = $query->getRowArray();
|
|
return $res;
|
|
}
|
|
|
|
// 등기부등본 확인실패 시간
|
|
public function get_cert_failTimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '49'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
//등기부등본 확인완료(모바일)
|
|
public function insert_v2_time_required_M($atcl_no, $cpid, $vrfc_type, $insert_tm, $cert_comple_dt, $finishTime)
|
|
{
|
|
if (substr($insert_tm, 0, 10) == substr($cert_comple_dt, 0, 10)) {
|
|
if (('12:00:00' < substr($insert_tm, -8)) && (substr($insert_tm, -8) < '13:00:00')) {//접수시간이 12~13시 사이면 13시로 해준다
|
|
if ($cert_comple_dt > '13:00:00') { //검증완료가 13시 '이후'에 끝나면 접수시간을 13시로 변경해준다
|
|
$insert_tm = date("Y-m-d", time()) . " 13:00:00";
|
|
}
|
|
} else if (
|
|
(substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') && //접수와 확인완료시간이 모두 17:30:00 ~ 09:00:00 사이가 아니면
|
|
(substr($cert_comple_dt, -8) < '17:30:00' || substr($cert_comple_dt, -8) < '08:59:59')
|
|
) {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
} else {
|
|
if (substr($insert_tm, -8) > '17:30:00' || substr($insert_tm, -8) < '08:59:59') {
|
|
$insert_tm = date("Y-m-d", time()) . " 09:00:00";
|
|
}
|
|
}
|
|
$stan_date = substr($insert_tm, 0, 10);
|
|
|
|
$sql = "INSERT INTO v2_time_required(stan_date, atcl_no, cpid, vrfc_type, insert_tm, cert_comple_dt, cert_required_tm, tot_required_tm)" .
|
|
" VALUES(?, ?, ?, ?, ?, ?, TIMEDIFF(?,?), TIMEDIFF(?,?)) " .
|
|
" ON DUPLICATE KEY UPDATE insert_tm = VALUES(insert_tm), tel_doc_conf_dt=VALUES(tel_doc_conf_dt),conf_required_tm = VALUES(conf_required_tm),tot_required_tm = VALUES(tot_required_tm) ";
|
|
$data = array(
|
|
$stan_date,
|
|
$atcl_no,
|
|
$cpid,
|
|
$vrfc_type,
|
|
$insert_tm,
|
|
$cert_comple_dt,
|
|
$cert_comple_dt,
|
|
$insert_tm,
|
|
$cert_comple_dt,
|
|
$insert_tm
|
|
);
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
//등기부등본 확인완료(불일치)
|
|
public function update_v2_time_required_Conf($atcl_no, $cpid, $vrfc_type, $tel_doc_conf_dt, $cert_comple_dt, $sf)
|
|
{
|
|
if ($sf == 'F') {
|
|
if (substr($tel_doc_conf_dt, 0, 10) == substr($cert_comple_dt, 0, 10)) {
|
|
if (('12:00:00' < substr($tel_doc_conf_dt, -8)) && (substr($tel_doc_conf_dt, -8) < '13:00:00')) {//전서확인시간이 12~13시 사이면 13시로 해준다
|
|
// if (substr($finishTime, -8) > '13:00:00'){ //등본완료가 13시 '이후'에 끝나면 전서확인시간을 13시로 변경해준다
|
|
// $tel_doc_conf_dt = date("Y-m-d",time())." 13:00:00";
|
|
// }
|
|
}
|
|
}
|
|
}
|
|
$sql = "UPDATE v2_time_required " .
|
|
" SET cert_comple_dt = ?" .
|
|
" ,cert_required_tm = TIMEDIFF(?,?)" .//등본 소요시간 = 등본완료시간-전/서확인완료시간
|
|
" WHERE atcl_no = ?";
|
|
$data = array(
|
|
$cert_comple_dt,
|
|
$cert_comple_dt,
|
|
$tel_doc_conf_dt,
|
|
$atcl_no
|
|
);
|
|
$this->db->query($sql, $data);
|
|
// echo $this->db->last_query().'<br>';
|
|
}
|
|
|
|
// 최초! 등기부등본 확인중 시간-모바일용
|
|
public function get_cert_M_timeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '40'" .
|
|
" and chg_type= 'C9'" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
// 등기부등본 확인완료 시간
|
|
public function get_cert_confTimeForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '45'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
// 검증실패 시간
|
|
public function get_69_ForHistory($vr_sq)
|
|
{
|
|
$sql = "select insert_tm from v2_chg_history" .
|
|
" where vr_sq = ?" .
|
|
" and stat_cd = '69'" .
|
|
" and chg_type= 'C9'" .
|
|
" order by seq desc" .
|
|
" limit 1";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
|
|
//등기부등본 확인완료(일치/불일치), 검증완료/실패까지 갈때만!
|
|
public function update_v2_time_required_Conf_Done($atcl_no, $cpid, $vrfc_type, $tel_doc_conf_dt, $cert_comple_dt)
|
|
{
|
|
//1.검증소요시간 가져온다
|
|
$sql = "SELECT conf_required_tm" .
|
|
" FROM v2_time_required" .
|
|
" WHERE atcl_no = ?";
|
|
$data = array(
|
|
$atcl_no
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
|
|
$res = $query->row_array();
|
|
|
|
if (substr($tel_doc_conf_dt, 0, 10) == substr($cert_comple_dt, 0, 10)) {
|
|
if (('12:00:00' < substr($tel_doc_conf_dt, -8)) && (substr($tel_doc_conf_dt, -8) < '13:00:00')) {//전서확인시간이 12~13시 사이면 13시로 해준다
|
|
if (substr($cert_comple_dt, -8) > '13:00:00') { //등본완료가 13시 '이후'에 끝나면 전서확인시간을 13시로 변경해준다
|
|
$tel_doc_conf_dt = date("Y-m-d", time()) . " 13:00:00";
|
|
}
|
|
}
|
|
}
|
|
//2.등본소요시간, 등본완료시간, 총소요시간을 넣는다
|
|
$sql = "UPDATE v2_time_required " .
|
|
" SET cert_comple_dt = ?" .
|
|
" ,cert_required_tm = TIMEDIFF(?,?)" . //등본 소요시간 = 등본완료시간-전/서확인완료시간
|
|
" ,tot_required_tm = ADDTIME(?,TIMEDIFF(?,?))" .//총소요시간 = 검증소요시간+등본소요시간
|
|
" WHERE atcl_no = ?";
|
|
$data = array(
|
|
$cert_comple_dt,
|
|
$cert_comple_dt,
|
|
$tel_doc_conf_dt,
|
|
$res['conf_required_tm'],
|
|
$cert_comple_dt,
|
|
$tel_doc_conf_dt,
|
|
$atcl_no
|
|
);
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
public function get_rlet_type_cd($vr_sq)
|
|
{
|
|
$sql = "SELECT rlet_type_cd
|
|
FROM v2_article_info
|
|
WHERE vr_sq = ?";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
$res = $query->getRowArray();
|
|
|
|
return $res;
|
|
}
|
|
} |