1372 lines
46 KiB
PHP
1372 lines
46 KiB
PHP
<?php
|
|
namespace App\Models\v2;
|
|
|
|
use App\Models\v2\BaseV2Model;
|
|
|
|
class M713Model extends BaseV2Model
|
|
{
|
|
|
|
public function getTotalCount($data)
|
|
{
|
|
$sql = "SELECT
|
|
COUNT(*) AS cnt
|
|
FROM v2_article_info a
|
|
JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq AND b.rgbk_confirm = true
|
|
JOIN v2_modify_info c ON a.vr_sq = c.vr_sq
|
|
JOIN v2_article_info_etc c1 ON c1.vr_sq = a.vr_sq
|
|
LEFT JOIN region_codes f ON a.address_code = f.region_cd
|
|
LEFT JOIN v2_chg_stat d ON a.vr_sq = d.vr_sq AND d.stat_cd = '35'
|
|
LEFT JOIN v2_chg_stat e ON a.vr_sq = e.vr_sq AND e.stat_cd = '60'
|
|
LEFT JOIN codes g ON b.stat_cd = g.cd AND g.category = 'STEP_VERIFICATION'
|
|
LEFT JOIN codes h ON b.vrfc_type = h.cd AND h.category = 'VRFCREQ_WAY'
|
|
LEFT JOIN users i ON a.charger = i.usr_id
|
|
LEFT JOIN users j ON a.reg_charger = j.usr_id
|
|
LEFT JOIN v2_article_info_etc k ON a.vr_sq = k.vr_sq
|
|
LEFT JOIN v2_chg_stat d2 ON d2.vr_sq = a.vr_sq AND d2.stat_cd = '39'
|
|
WHERE
|
|
b.insert_tm >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
|
|
AND b.vrfc_type = 'O' ";
|
|
|
|
// 매물번호
|
|
if (!empty($data['atcl_no'])) {
|
|
$sql .= "AND a.atcl = '{$data['atcl_no']}' ";
|
|
} else {
|
|
// 현재상태
|
|
if (!empty($data['stat_cd'])) {
|
|
$sql .= "AND b.stat_cd = '{$data['stat_cd']}' ";
|
|
}
|
|
|
|
// 매물종류
|
|
if (!empty($data['rlet_type_cd'])) {
|
|
$sql .= "AND a.rlet_type_cd = '{$data['rlet_type_cd']}' ";
|
|
}
|
|
|
|
// 접수기간
|
|
if (!empty($data['receipt_sdate'])) {
|
|
$sql .= "AND b.insert_tm >= '{$data['receipt_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['receipt_edate'])) {
|
|
$sql .= "AND b.insert_tm <= '{$data['receipt_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 완료기간
|
|
if (!empty($data['complete_sdate'])) {
|
|
$sql .= "AND e.insert_tm >= '{$data['complete_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['complete_edate'])) {
|
|
$sql .= "AND e.insert_tm <= '{$data['complete_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 검증방식
|
|
if (!empty($data['vrfc_type_sub'])) {
|
|
$sql .= "AND a.vrfc_type_sub = '{$data['vrfc_type_sub']}' ";
|
|
} else {
|
|
if (!empty($data['vrfcreq_way'])) {
|
|
$sql .= "AND b.vrfc_type = '{$data['vrfcreq_way']}' ";
|
|
}
|
|
}
|
|
|
|
// 매체사
|
|
if (!empty($data['rcpt_cpid'])) {
|
|
$sql .= "AND a.cpid = '{$data['rcpt_cpid']}' ";
|
|
}
|
|
|
|
// 면적여부
|
|
if ($data['chk_spc_yn'] == "Y") {
|
|
$sql .= "AND a.dong_ho_chk = 'N' AND a.hscplqry_lv = '3' ";
|
|
} else if ($data['chk_spc_yn'] == "N") {
|
|
$sql .= "AND a.rlet_type_cd IN ('A01', 'A02','A03') ";
|
|
$sql .= "AND a.dong_ho_chk = 'Y' AND a.hscplqry_lv != '3' AND a.dong_ho_Chk IS NOT NULL ";
|
|
}
|
|
|
|
// 지역구분
|
|
if (!empty($data['srcDong'])) {
|
|
$sql .= "AND a.address_code = '{$data['srcDong']}' ";
|
|
} else {
|
|
if (!empty($data['srcGugun'])) {
|
|
$str_gugun = substr($data['srcGugun'], '0', '2');
|
|
if ($str_gugun == '36') { //세종시는 군구가 없고 바로 동이라서 예외
|
|
$sql .= "AND a.address_code = '{$data['srcGugun']}' ";
|
|
} else {
|
|
$gugunPrefix = substr($data['srcGugun'], '0', '5');
|
|
$sql .= "AND a.address_code LIKE '{$gugunPrefix}%' ";
|
|
}
|
|
} else {
|
|
if (!empty($data['srcSido'])) {
|
|
$sidoPrefix = substr($data['srcSido'], '0', '2');
|
|
$sql .= "AND a.address_code LIKE '{$sidoPrefix}%' ";
|
|
}
|
|
}
|
|
}
|
|
|
|
// 담당자
|
|
if (!empty($data['damdang'])) {
|
|
|
|
switch ($data['charger_gbn']) {
|
|
case "1":
|
|
$sql .= "a.charger = '{$data['damdang']}' ";
|
|
break;
|
|
case "2":
|
|
$sql .= "a.reg_charger = '{$data['damdang']}' ";
|
|
break;
|
|
}
|
|
|
|
} else {
|
|
// 배정여부
|
|
if ($data['assign_yn'] !== "A") {
|
|
switch ($data['charger_gbn'] . $data['assign_yn']) {
|
|
case "1Y": // 전화/서류 담당자
|
|
$sql .= "a.charger != '' ";
|
|
break;
|
|
case "1N": // 전화/서류 담당자
|
|
$sql .= "a.charger = '' ";
|
|
break;
|
|
case "2Y": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger != '' ";
|
|
break;
|
|
case "2N": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger IS NULL ";
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 법인
|
|
if (!empty($data['corp_own'])) {
|
|
$sql .= "AND k.corp_own = '{$data['a.corp_own']}' ";
|
|
}
|
|
|
|
// 참고파일
|
|
if (!empty($data['reference_file_url_yn'])) {
|
|
$sql .= "AND a.reference_file_url_yn = '{$data['a.reference_file_url_yn']}' ";
|
|
}
|
|
|
|
}
|
|
|
|
// print ($sql);
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getRow()->cnt;
|
|
}
|
|
|
|
public function getResultList($start, $end, $data)
|
|
{
|
|
$sql = "SELECT
|
|
a.vr_sq,
|
|
a.dong_ho_chk,
|
|
a.hscplqry_lv,
|
|
a.atcl_no,
|
|
a.cpid,
|
|
a.cp_atcl_id,
|
|
a.rlet_type_cd,
|
|
a.address1,
|
|
a.sise,
|
|
a.rdate,
|
|
a.seller_tel_no,
|
|
a.seller_nm,
|
|
a.realtor_nm,
|
|
a.realtor_tel_no,
|
|
a.charger,
|
|
b.insert_tm,
|
|
b.stat_cd,
|
|
c.bild_nm,
|
|
b.vrfc_type,
|
|
c.rm_no,
|
|
c.floor,
|
|
c.address_code,
|
|
c.address2,
|
|
IFNULL(c1.address2a, '') AS address2a,
|
|
IFNULL(c1.address2b, '') AS address2b,
|
|
IFNULL(c.address3, '') AS address3,
|
|
c.trade_type,
|
|
c.deal_amt,
|
|
c.wrrnt_amt,
|
|
c.lease_amt,
|
|
c.isale_amt,
|
|
c.prem_amt,
|
|
c.sply_spc,
|
|
c.excls_spc,
|
|
c.tot_spc,
|
|
c.grnd_spc,
|
|
c.bldg_spc,
|
|
c.hscp_no,
|
|
c.ptp_no,
|
|
d.insert_tm as update_res_tm,
|
|
e.insert_tm as result_tm,
|
|
f.region_nm,
|
|
g.cd_nm as pre_stat,
|
|
h.cd_nm as vrfc_type,
|
|
i.usr_nm,
|
|
j.usr_nm as reg_charger,
|
|
d2.insert_tm as stat_39_tm,
|
|
a.vrfc_type_sub,
|
|
k.corp_own,
|
|
a.reference_file_url_yn
|
|
FROM v2_article_info a
|
|
JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq AND b.rgbk_confirm = true
|
|
JOIN v2_modify_info c ON a.vr_sq = c.vr_sq
|
|
JOIN v2_article_info_etc c1 ON c1.vr_sq = a.vr_sq
|
|
LEFT JOIN region_codes f ON a.address_code = f.region_cd
|
|
LEFT JOIN v2_chg_stat d ON a.vr_sq = d.vr_sq AND d.stat_cd = '35'
|
|
LEFT JOIN v2_chg_stat e ON a.vr_sq = e.vr_sq AND e.stat_cd = '60'
|
|
LEFT JOIN codes g ON b.stat_cd = g.cd AND g.category = 'STEP_VERIFICATION'
|
|
LEFT JOIN codes h ON b.vrfc_type = h.cd AND h.category = 'VRFCREQ_WAY'
|
|
LEFT JOIN users i ON a.charger = i.usr_id
|
|
LEFT JOIN users j ON a.reg_charger = j.usr_id
|
|
LEFT JOIN v2_article_info_etc k ON a.vr_sq = k.vr_sq
|
|
LEFT JOIN v2_chg_stat d2 ON d2.vr_sq = a.vr_sq AND d2.stat_cd = '39'
|
|
WHERE
|
|
b.insert_tm >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
|
|
AND b.vrfc_type = 'O' ";
|
|
|
|
// 매물번호
|
|
if (!empty($data['atcl_no'])) {
|
|
$sql .= "AND a.atcl = '{$data['atcl_no']}' ";
|
|
} else {
|
|
// 현재상태
|
|
if (!empty($data['stat_cd'])) {
|
|
$sql .= "AND b.stat_cd = '{$data['stat_cd']}' ";
|
|
}
|
|
|
|
// 매물종류
|
|
if (!empty($data['rlet_type_cd'])) {
|
|
$sql .= "AND a.rlet_type_cd = '{$data['rlet_type_cd']}' ";
|
|
}
|
|
|
|
// 접수기간
|
|
if (!empty($data['receipt_sdate'])) {
|
|
$sql .= "AND b.insert_tm >= '{$data['receipt_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['receipt_edate'])) {
|
|
$sql .= "AND b.insert_tm <= '{$data['receipt_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 완료기간
|
|
if (!empty($data['complete_sdate'])) {
|
|
$sql .= "AND e.insert_tm >= '{$data['complete_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['complete_edate'])) {
|
|
$sql .= "AND e.insert_tm <= '{$data['complete_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 검증방식
|
|
if (!empty($data['vrfc_type_sub'])) {
|
|
$sql .= "AND a.vrfc_type_sub = '{$data['vrfc_type_sub']}' ";
|
|
} else {
|
|
if (!empty($data['vrfcreq_way'])) {
|
|
$sql .= "AND b.vrfc_type = '{$data['vrfcreq_way']}' ";
|
|
}
|
|
}
|
|
|
|
// 매체사
|
|
if (!empty($data['rcpt_cpid'])) {
|
|
$sql .= "AND a.cpid = '{$data['rcpt_cpid']}' ";
|
|
}
|
|
|
|
// 면적여부
|
|
if ($data['chk_spc_yn'] == "Y") {
|
|
$sql .= "AND a.dong_ho_chk = 'N' AND a.hscplqry_lv = '3' ";
|
|
} else if ($data['chk_spc_yn'] == "N") {
|
|
$sql .= "AND a.rlet_type_cd IN ('A01', 'A02','A03') ";
|
|
$sql .= "AND a.dong_ho_chk = 'Y' AND a.hscplqry_lv != '3' AND a.dong_ho_Chk IS NOT NULL ";
|
|
}
|
|
|
|
// 지역구분
|
|
if (!empty($data['srcDong'])) {
|
|
$sql .= "AND a.address_code = '{$data['srcDong']}' ";
|
|
} else {
|
|
if (!empty($data['srcGugun'])) {
|
|
$str_gugun = substr($data['srcGugun'], '0', '2');
|
|
if ($str_gugun == '36') { //세종시는 군구가 없고 바로 동이라서 예외
|
|
$sql .= "AND a.address_code = '{$data['srcGugun']}' ";
|
|
} else {
|
|
$gugunPrefix = substr($data['srcGugun'], '0', '5');
|
|
$sql .= "AND a.address_code LIKE '{$gugunPrefix}%' ";
|
|
}
|
|
} else {
|
|
if (!empty($data['srcSido'])) {
|
|
$sidoPrefix = substr($data['srcSido'], '0', '2');
|
|
$sql .= "AND a.address_code LIKE '{$sidoPrefix}%' ";
|
|
}
|
|
}
|
|
}
|
|
|
|
// 담당자
|
|
if (!empty($data['damdang'])) {
|
|
|
|
switch ($data['charger_gbn']) {
|
|
case "1":
|
|
$sql .= "a.charger = '{$data['damdang']}' ";
|
|
break;
|
|
case "2":
|
|
$sql .= "a.reg_charger = '{$data['damdang']}' ";
|
|
break;
|
|
}
|
|
|
|
} else {
|
|
// 배정여부
|
|
if ($data['assign_yn'] !== "A") {
|
|
switch ($data['charger_gbn'] . $data['assign_yn']) {
|
|
case "1Y": // 전화/서류 담당자
|
|
$sql .= "a.charger != '' ";
|
|
break;
|
|
case "1N": // 전화/서류 담당자
|
|
$sql .= "a.charger = '' ";
|
|
break;
|
|
case "2Y": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger != '' ";
|
|
break;
|
|
case "2N": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger IS NULL ";
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 법인
|
|
if (!empty($data['corp_own'])) {
|
|
$sql .= "AND k.corp_own = '{$data['a.corp_own']}' ";
|
|
}
|
|
|
|
// 참고파일
|
|
if (!empty($data['reference_file_url_yn'])) {
|
|
$sql .= "AND a.reference_file_url_yn = '{$data['a.reference_file_url_yn']}' ";
|
|
}
|
|
|
|
}
|
|
|
|
$sql .= "ORDER BY b.vr_sq DESC , b.insert_tm DESC ";
|
|
$sql .= "LIMIT {$start}, {$end}";
|
|
|
|
// print ($sql);
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getResultArray();
|
|
}
|
|
|
|
public function getExcelList($data)
|
|
{
|
|
$sql = "SELECT
|
|
a.atcl_no AS '매물번호',
|
|
g.cd_nm AS '진행상태',
|
|
b.insert_tm AS '접수시간',
|
|
(SELECT cd_nm FROM codes d WHERE d.cd = b.vrfc_type AND d.category = 'VRFCREQ_WAY' ) AS '검증방식',
|
|
CONCAT(f.region_nm, ' ', c.rm_no) AS '주소',
|
|
CONCAT ( (SELECT region_nm FROM region_codes r WHERE a.address_code = r.region_cd ) , ' ' , ifnull(c.rm_no,'') ) AS address ,
|
|
(
|
|
case IFNULL(c1.address2b , '')
|
|
when '' then CONCAT(ifnull(a.address2,'') , ' ' , ifnull(a.address3,''))
|
|
ELSE CONCAT(IFNULL(c1.address2b,'') , ' ' , ifnull(a.address3,''))
|
|
END
|
|
|
|
) AS '상세주소',
|
|
a.cpid AS '매체사',
|
|
a.realtor_nm AS '중개소',
|
|
IFNULL(d.insert_tm, d2.insert_tm) AS '서류/전화 확인시간',
|
|
i.usr_nm AS '담당자',
|
|
e.insert_tm AS '검증완료 일시'
|
|
FROM v2_article_info a
|
|
JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq AND b.rgbk_confirm = true
|
|
JOIN v2_modify_info c ON a.vr_sq = c.vr_sq
|
|
JOIN v2_article_info_etc c1 ON c1.vr_sq = a.vr_sq
|
|
LEFT JOIN region_codes f ON a.address_code = f.region_cd
|
|
LEFT JOIN v2_chg_stat d ON a.vr_sq = d.vr_sq AND d.stat_cd = '35'
|
|
LEFT JOIN v2_chg_stat e ON a.vr_sq = e.vr_sq AND e.stat_cd = '60'
|
|
LEFT JOIN codes g ON b.stat_cd = g.cd AND g.category = 'STEP_VERIFICATION'
|
|
LEFT JOIN codes h ON b.vrfc_type = h.cd AND h.category = 'VRFCREQ_WAY'
|
|
LEFT JOIN users i ON a.charger = i.usr_id
|
|
LEFT JOIN users j ON a.reg_charger = j.usr_id
|
|
LEFT JOIN v2_article_info_etc k ON a.vr_sq = k.vr_sq
|
|
LEFT JOIN v2_chg_stat d2 ON d2.vr_sq = a.vr_sq AND d2.stat_cd = '39'
|
|
WHERE
|
|
b.insert_tm >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
|
|
AND b.vrfc_type = 'O' ";
|
|
|
|
// 매물번호
|
|
if (!empty($data['atcl_no'])) {
|
|
$sql .= "AND a.atcl = '{$data['atcl_no']}' ";
|
|
} else {
|
|
// 현재상태
|
|
if (!empty($data['stat_cd'])) {
|
|
$sql .= "AND b.stat_cd = '{$data['stat_cd']}' ";
|
|
}
|
|
|
|
// 매물종류
|
|
if (!empty($data['rlet_type_cd'])) {
|
|
$sql .= "AND a.rlet_type_cd = '{$data['rlet_type_cd']}' ";
|
|
}
|
|
|
|
// 접수기간
|
|
if (!empty($data['receipt_sdate'])) {
|
|
$sql .= "AND b.insert_tm >= '{$data['receipt_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['receipt_edate'])) {
|
|
$sql .= "AND b.insert_tm <= '{$data['receipt_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 완료기간
|
|
if (!empty($data['complete_sdate'])) {
|
|
$sql .= "AND e.insert_tm >= '{$data['complete_sdate']} 00:00:00' ";
|
|
}
|
|
|
|
if (!empty($data['complete_edate'])) {
|
|
$sql .= "AND e.insert_tm <= '{$data['complete_edate']} 23:59:59' ";
|
|
}
|
|
|
|
// 검증방식
|
|
if (!empty($data['vrfc_type_sub'])) {
|
|
$sql .= "AND a.vrfc_type_sub = '{$data['vrfc_type_sub']}' ";
|
|
} else {
|
|
if (!empty($data['vrfcreq_way'])) {
|
|
$sql .= "AND b.vrfc_type = '{$data['vrfcreq_way']}' ";
|
|
}
|
|
}
|
|
|
|
// 매체사
|
|
if (!empty($data['rcpt_cpid'])) {
|
|
$sql .= "AND a.cpid = '{$data['rcpt_cpid']}' ";
|
|
}
|
|
|
|
// 면적여부
|
|
if ($data['chk_spc_yn'] == "Y") {
|
|
$sql .= "AND a.dong_ho_chk = 'N' AND a.hscplqry_lv = '3' ";
|
|
} else if ($data['chk_spc_yn'] == "N") {
|
|
$sql .= "AND a.rlet_type_cd IN ('A01', 'A02','A03') ";
|
|
$sql .= "AND a.dong_ho_chk = 'Y' AND a.hscplqry_lv != '3' AND a.dong_ho_Chk IS NOT NULL ";
|
|
}
|
|
|
|
// 지역구분
|
|
if (!empty($data['srcDong'])) {
|
|
$sql .= "AND a.address_code = '{$data['srcDong']}' ";
|
|
} else {
|
|
if (!empty($data['srcGugun'])) {
|
|
$str_gugun = substr($data['srcGugun'], '0', '2');
|
|
if ($str_gugun == '36') { //세종시는 군구가 없고 바로 동이라서 예외
|
|
$sql .= "AND a.address_code = '{$data['srcGugun']}' ";
|
|
} else {
|
|
$gugunPrefix = substr($data['srcGugun'], '0', '5');
|
|
$sql .= "AND a.address_code LIKE '{$gugunPrefix}%' ";
|
|
}
|
|
} else {
|
|
if (!empty($data['srcSido'])) {
|
|
$sidoPrefix = substr($data['srcSido'], '0', '2');
|
|
$sql .= "AND a.address_code LIKE '{$sidoPrefix}%' ";
|
|
}
|
|
}
|
|
}
|
|
|
|
// 담당자
|
|
if (!empty($data['damdang'])) {
|
|
|
|
switch ($data['charger_gbn']) {
|
|
case "1":
|
|
$sql .= "a.charger = '{$data['damdang']}' ";
|
|
break;
|
|
case "2":
|
|
$sql .= "a.reg_charger = '{$data['damdang']}' ";
|
|
break;
|
|
}
|
|
|
|
} else {
|
|
// 배정여부
|
|
if ($data['assign_yn'] !== "A") {
|
|
switch ($data['charger_gbn'] . $data['assign_yn']) {
|
|
case "1Y": // 전화/서류 담당자
|
|
$sql .= "a.charger != '' ";
|
|
break;
|
|
case "1N": // 전화/서류 담당자
|
|
$sql .= "a.charger = '' ";
|
|
break;
|
|
case "2Y": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger != '' ";
|
|
break;
|
|
case "2N": // 등기부등본 담당자
|
|
$sql .= "a.reg_charger IS NULL ";
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 법인
|
|
if (!empty($data['corp_own'])) {
|
|
$sql .= "AND k.corp_own = '{$data['a.corp_own']}' ";
|
|
}
|
|
|
|
// 참고파일
|
|
if (!empty($data['reference_file_url_yn'])) {
|
|
$sql .= "AND a.reference_file_url_yn = '{$data['a.reference_file_url_yn']}' ";
|
|
}
|
|
|
|
}
|
|
|
|
$sql .= "ORDER BY b.vr_sq DESC , b.insert_tm DESC ";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getResultArray();
|
|
}
|
|
|
|
|
|
// 상세화면
|
|
public function getDetail($vr_sq)
|
|
{
|
|
$sql = "SELECT
|
|
a.vr_sq,
|
|
a.dong_ho_chk,
|
|
a.reg_status,
|
|
a.hscplqry_lv,
|
|
a.atcl_no,
|
|
b.stat_cd,
|
|
a.cpid,
|
|
a.cp_atcl_id,
|
|
a.rlet_type_cd,
|
|
a.address1,
|
|
a.sise,
|
|
a.rdate,
|
|
a.hscp_no as chk_hscp_no,
|
|
b.try_cnt,
|
|
a.seller_tel_no,
|
|
a.seller_nm,
|
|
a.realtor_nm,
|
|
a.realtor_tel_no,
|
|
a.charger,
|
|
a.ownerNm,
|
|
a.ownerTelNo,
|
|
b.reg_try_cnt,
|
|
b.insert_tm,
|
|
a.reg_charger,
|
|
i2.usr_nm as reg_charger_nm,
|
|
c.bild_nm,
|
|
b.vrfc_type as vrfc_type_cd,
|
|
c.rm_no,
|
|
c.floor,
|
|
c.floor2,
|
|
c.address_code,
|
|
c.address2,
|
|
c1.address2a,
|
|
c1.address2b,
|
|
c.address3,
|
|
c.address4,
|
|
c.trade_type as trade_type_cd,
|
|
c.deal_amt,
|
|
c.wrrnt_amt,
|
|
c.lease_amt,
|
|
c.isale_amt,
|
|
c.prem_amt,
|
|
c.sply_spc,
|
|
c.excls_spc,
|
|
c.tot_spc,
|
|
c.grnd_spc,
|
|
c.bldg_spc,
|
|
c.hscp_no,
|
|
c.ptp_no,
|
|
d.insert_tm as update_res_tm,
|
|
e.insert_tm as result_tm,
|
|
f.region_nm,
|
|
g.cd_nm as pre_stat,
|
|
g.cd as pre_stat_cd,
|
|
h.cd_nm as vrfc_type,
|
|
i.usr_nm,
|
|
j.cd_nm as trade_type,
|
|
j.cd as trade_type_cd,
|
|
c.hscp_nm,
|
|
c.ptp_nm,
|
|
l.success,
|
|
k.cd_nm as atcl_nm,
|
|
m.code as result_d11,
|
|
m.comment,
|
|
n.code as fax_conf_yn_2,
|
|
o.code as fax_conf_yn_3,
|
|
p.code as fax_conf_yn_4,
|
|
n.comment as fax_conf_yn_info_2,
|
|
o.comment as fax_conf_yn_info_3,
|
|
p.comment as fax_conf_yn_info_4,
|
|
v.success AS tel_suc,
|
|
r.code AS tel_agree,
|
|
s.code AS tel_conf_yn_2,
|
|
t.code AS tel_conf_yn_3,
|
|
u.code AS tel_conf_yn_4,
|
|
s.comment AS tel_conf_yn_info_2,
|
|
t.comment AS tel_conf_yn_info_3,
|
|
u.comment AS tel_conf_yn_info_4,
|
|
w.success AS reg_conf_yn_1,
|
|
x.code AS reg_conf_yn_2,
|
|
y.code AS reg_conf_yn_3,
|
|
x.comment AS reg_conf_yn_info_2,
|
|
y.comment AS reg_conf_yn_info_3,
|
|
b.rgbk_confirm,
|
|
a.redvlp_area_nm,
|
|
a.biz_stp_desc,
|
|
a.cert_register,
|
|
a.confirm_doc_img_url,
|
|
a.cert_register_save_yn,
|
|
a.confirm_doc_img_url_save_yn,
|
|
b.confirm_doc_owner_check_yn,
|
|
a.owner_birth,
|
|
a.vrfc_type_sub,
|
|
b.owner_verifiable,
|
|
a.reference_file_url,
|
|
a.reference_file_url_save_yn,
|
|
a.reference_file_url_yn,
|
|
z.corp_own,
|
|
c1.vir_addr_yn,
|
|
c1.cert_uncnfrm_status,
|
|
c1.noRgbkVrfcReqYn,
|
|
c1.areaByBdbkVrfcReqYn,
|
|
sm.sm_apporval_date ,
|
|
sm.sm_end_date,
|
|
sm.sm_seq,
|
|
a.registerBookUniqueNumber
|
|
FROM v2_article_info a
|
|
JOIN v2_vrfc_req b ON a.vr_sq = b.vr_sq
|
|
JOIN v2_modify_info c ON a.vr_sq = c.vr_sq
|
|
JOIN v2_article_info_etc c1 ON c1.vr_sq = a.vr_sq
|
|
LEFT JOIN region_codes f ON a.address_code = f.region_cd
|
|
LEFT JOIN v2_chg_stat d ON a.vr_sq = d.vr_sq AND d.stat_cd = '35'
|
|
LEFT JOIN v2_chg_stat e ON a.vr_sq = e.vr_sq AND e.stat_cd = '60'
|
|
LEFT JOIN codes g ON b.stat_cd = g.cd AND g.category = 'STEP_VERIFICATION'
|
|
LEFT JOIN codes h ON b.vrfc_type = h.cd AND h.category = 'VRFCREQ_WAY'
|
|
LEFT JOIN codes j ON c.trade_type = j.cd AND j.category = 'TRADE_TYPE'
|
|
LEFT JOIN codes k ON a.rlet_type_cd = k.cd AND k.category = 'ARTICLE_TYPE'
|
|
LEFT JOIN v2_confirm l ON a.vr_sq = l.vr_sq AND l.vrfc_type = 'D'
|
|
LEFT JOIN v2_check_list m ON a.vr_sq = m.vr_sq AND m.type = 'D11'
|
|
LEFT JOIN v2_check_list n ON a.vr_sq = n.vr_sq AND n.type = 'D12'
|
|
LEFT JOIN v2_check_list o ON a.vr_sq = o.vr_sq AND o.type = 'D13'
|
|
LEFT JOIN v2_check_list p ON a.vr_sq = p.vr_sq AND p.type = 'D14'
|
|
LEFT JOIN v2_confirm v ON a.vr_sq = v.vr_sq AND v.vrfc_type = 'T'
|
|
LEFT JOIN v2_check_list r ON a.vr_sq = r.vr_sq AND r.type = 'T11'
|
|
LEFT JOIN v2_check_list s ON a.vr_sq = s.vr_sq AND s.type = 'T12'
|
|
LEFT JOIN v2_check_list t ON a.vr_sq = t.vr_sq AND t.type = 'T13'
|
|
LEFT JOIN v2_check_list u ON a.vr_sq = u.vr_sq AND u.type = 'T14'
|
|
LEFT JOIN v2_confirm w ON a.vr_sq = w.vr_sq AND w.vrfc_type = 'R'
|
|
LEFT JOIN v2_check_list x ON a.vr_sq = x.vr_sq AND x.type = '21'
|
|
LEFT JOIN v2_check_list y ON a.vr_sq = y.vr_sq AND y.type = '22'
|
|
LEFT JOIN users i ON a.charger = i.usr_id
|
|
LEFT JOIN users i2 ON a.reg_charger = i2.usr_id
|
|
LEFT JOIN v2_article_info_etc z ON a.vr_sq = z.vr_sq
|
|
LEFT JOIN scomplex_manage sm ON a.hscp_no = sm.sm_code
|
|
|
|
WHERE
|
|
a.vr_sq = ?";
|
|
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
// 메모
|
|
public function getMemo($vr_sq)
|
|
{
|
|
$sql = "SELECT memo FROM v2_vrfc_req where vr_sq = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
public function getRecordInfo($vr_sq, $file_type)
|
|
{
|
|
$sql = "SELECT seq, vr_sq, use_yn, file_type, view_odr, file_path, file_name, file_ext, file_size, img_width, img_height, meta_data, insert_user, insert_tm , cloud_upload_yn " .
|
|
" FROM v2_files" .
|
|
" WHERE vr_sq = ?" .
|
|
" AND use_yn = 'Y'" .
|
|
" AND file_type = ?" .
|
|
" ORDER BY seq DESC";
|
|
$data = [
|
|
$vr_sq,
|
|
$file_type
|
|
];
|
|
|
|
$query = $this->db->query($sql, [$vr_sq, $file_type]);
|
|
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
public function getDisplay($menu_position)
|
|
{
|
|
|
|
$sql = "SELECT display_yn " .
|
|
"FROM page_display " .
|
|
"WHERE menu_position = ? ";
|
|
$data = [$menu_position];
|
|
$query = $this->db->query($sql, $data);
|
|
$row = $query->getRowArray();
|
|
|
|
return $row;
|
|
}
|
|
|
|
|
|
// 법인매물저장
|
|
public function saveCorpOwn($vr_sq, $atcl_no)
|
|
{
|
|
$sql = "INSERT INTO v2_article_info_etc
|
|
(vr_sq, atcl_no, corp_own)
|
|
VALUES
|
|
(?, ?, 'Y')
|
|
ON DUPLICATE KEY UPDATE corp_own = 'Y'
|
|
";
|
|
|
|
if ($this->db->query($sql, [$vr_sq, $atcl_no]) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '저장 실패',
|
|
];
|
|
}
|
|
|
|
return [
|
|
'success' => true,
|
|
];
|
|
}
|
|
|
|
// 파일업로드
|
|
public function saveFileInfo($data)
|
|
{
|
|
$this->db->transStart();
|
|
|
|
$usr_id = session('usr_id');
|
|
|
|
// 기존파일 확인후 업데이트
|
|
$sql = "SELECT seq FROM v2_files WHERE vr_sq = {$data['vr_sq']} AND use_yn = 'Y' AND file_type = '2'";
|
|
$query = $this->db->query($sql);
|
|
$row = $query->getNumRows();
|
|
|
|
if ($row > 0) {
|
|
|
|
$sql = "UPDATE v2_files SET use_yn = 'N' WHERE vr_sq = {$data['vr_sq']} AND use_yn = 'Y' AND file_type '2'";
|
|
|
|
$this->db->query($sql);
|
|
|
|
$sql = "INSERT INTO v2_files
|
|
(vr_sq, file_type, view_odr, file_path, file_name, file_ext, file_size, insert_user, insert_tm, cloud_upload_yn)
|
|
VALUES
|
|
(?, '2', 0, ?, ?, ?, ?, ?, NOW(), 'Y')
|
|
";
|
|
|
|
$param = [
|
|
$data['vr_sq'],
|
|
$data['file_path'],
|
|
$data['new_name'],
|
|
$data['ext'],
|
|
$data['size'],
|
|
$data['usr_id'],
|
|
];
|
|
|
|
|
|
if ($this->db->query($sql, $param)) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '파일정보 저장 실패',
|
|
];
|
|
}
|
|
|
|
$row = $this->getDetail($data['vr_sq']);
|
|
$this->saveChangedHistory($data['vr_sq'], $row['stat_cd'], 'C28', $usr_id, '이미지파일 업로드');
|
|
|
|
}
|
|
|
|
$this->db->transComplete();
|
|
|
|
return [
|
|
'success' => true
|
|
];
|
|
}
|
|
|
|
|
|
// 다음매물
|
|
public function getNextInfo($vr_sq)
|
|
{
|
|
$usr_id = session('usr_id');
|
|
|
|
$sql = "SELECT b.vr_sq
|
|
FROM v2_article_info b
|
|
INNER JOIN v2_vrfc_req a ON a.vr_sq = b.vr_sq AND a.vr_sq != ? AND a.stat_cd BETWEEN '35' AND '49' AND a.stat_cd NOT IN ('39','45')
|
|
LEFT JOIN v2_chg_stat c ON c.vr_sq = b.vr_sq AND c.stat_cd = '40'
|
|
WHERE a.insert_tm < DATE_FORMAT(curdate(), '%Y%m%d172959')
|
|
AND (b.reg_charger IS NULL OR b.reg_charger = '')
|
|
AND a.vrfc_type = 'N'
|
|
ORDER BY CASE a.vrfc_type WHEN 'N' THEN 1 ELSE 2 END, a.vr_sq
|
|
LIMIT 1
|
|
for update skip locked ";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
$row = $query->getRowArray();
|
|
|
|
if (!empty($row)) {
|
|
|
|
$this->db->transStart();
|
|
|
|
$sql = "UPDATE v2_article_info SET reg_charger = ? WHERE vr_sq = ?";
|
|
|
|
if ($this->db->query($sql, [$usr_id, $vr_sq]) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '저장 실패',
|
|
];
|
|
}
|
|
|
|
$this->db->transComplete();
|
|
|
|
return $row;
|
|
|
|
} else {
|
|
return [];
|
|
}
|
|
}
|
|
|
|
/* 현재 상태 확인 */
|
|
public function chkStat($vr_sq)
|
|
{
|
|
$sql = "SELECT stat_cd FROM v2_vrfc_req WHERE vr_sq = ?";
|
|
$data = array(
|
|
$vr_sq
|
|
);
|
|
$query = $this->db->query($sql, $data);
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
public function chkTryCnt($atcl_vr_sq)
|
|
{
|
|
// 1차 검증인지 2차 검증인지 확인.
|
|
$sql = "SELECT a.try_cnt FROM v2_vrfc_req a WHERE vr_sq = ?";
|
|
$query = $this->db->query($sql, [$atcl_vr_sq]);
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
public function chkRegiTryCnt($atcl_vr_sq)
|
|
{
|
|
$sql = "SELECT a.reg_try_cnt FROM v2_vrfc_req a WHERE vr_sq = ?";
|
|
$query = $this->db->query($sql, [$atcl_vr_sq]);
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
public function chkConfirm($atcl_vr_sq, $reg_yn)
|
|
{
|
|
// 검증결과 table에 있는지 확인.
|
|
$builder = $this->db->table('v2_confirm a');
|
|
$builder->select('a.vr_sq');
|
|
$builder->where('vr_sq', $atcl_vr_sq);
|
|
|
|
if ($reg_yn) {
|
|
$builder->where('vrfc_type', $reg_yn); // 등기부등본 정보 확인시
|
|
}
|
|
|
|
$query = $builder->get();
|
|
$result = $query->getNumRows();
|
|
|
|
return $result;
|
|
}
|
|
|
|
public function getUpdateFailTime($vr_sq)
|
|
{
|
|
$sql = "select vr_sq, stat_cd, insert_user, insert_tm " .
|
|
"from v2_chg_stat " .
|
|
"where vr_sq = ? and stat_cd = '49' " .
|
|
"order by insert_tm desc " .
|
|
"limit 1";
|
|
$data = array($vr_sq);
|
|
$query = $this->db->query($sql, $data);
|
|
$row = $query->getRowArray();
|
|
|
|
return $row;
|
|
}
|
|
|
|
public function insertConfirm($vr_sq, $vrfc_type, $success, $type)
|
|
{ //v2_confirm INSERT
|
|
|
|
$usr_sq = session('usr_sq');
|
|
$usr_id = session('usr_id');
|
|
|
|
$sql = "INSERT INTO v2_confirm" .
|
|
"(vr_sq, vrfc_type, success, type, charger, date, insert_user, insert_tm, update_user, update_tm)" .
|
|
"VALUES" .
|
|
"(?, ?, ?, ?, ?, '" . date('YmdHis') . "', ?, NOW(), ?, NOW())";
|
|
|
|
$data = array(
|
|
$vr_sq,
|
|
$vrfc_type,
|
|
$success,
|
|
$type,
|
|
$usr_id,
|
|
$usr_sq,
|
|
$usr_sq
|
|
);
|
|
|
|
$res = $this->db->query($sql, $data);
|
|
}
|
|
|
|
public function updateConfirm($vr_sq, $vrfc_type, $success): void
|
|
{
|
|
$data = [
|
|
'success' => $success
|
|
];
|
|
|
|
$builder = $this->db->table('v2_confirm');
|
|
$builder->where('vr_sq', $vr_sq);
|
|
$builder->where('vrfc_type', $vrfc_type);
|
|
$builder->update($data);
|
|
}
|
|
|
|
public function InsResChar($atcl_vr_sq)
|
|
{
|
|
$usr_id = session('usr_id');
|
|
|
|
$sql = "UPDATE v2_article_info" .
|
|
" SET reg_charger = ?" .
|
|
" WHERE vr_sq = ?";
|
|
|
|
$data = array(
|
|
$usr_id,
|
|
$atcl_vr_sq
|
|
);
|
|
//echo $this->db->last_query();
|
|
$res = $this->db->query($sql, $data);
|
|
$log_v = $this->db->getLastQuery();
|
|
log_message('debug', $log_v);
|
|
}
|
|
|
|
public function add_cert_uncnfrm_status($vr_sq, $cert_uncnfrm_status)
|
|
{
|
|
$sql = "UPDATE v2_article_info_etc" .
|
|
" SET cert_uncnfrm_status = ?" .
|
|
" WHERE vr_sq = ?";
|
|
$data = array(
|
|
$cert_uncnfrm_status,
|
|
$vr_sq
|
|
);
|
|
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
public function insertChkList($vr_sq, $vrfc_type, $type, $code, $comment)
|
|
{ //v2_check_list INSERT
|
|
|
|
$usr_sq = session('usr_sq');
|
|
|
|
$sql = "INSERT INTO v2_check_list" .
|
|
"(vr_sq, vrfc_type, type, code, comment, insert_user, insert_tm)" .
|
|
"VALUES" .
|
|
"(?, ?, ?, ?, ?, ?, NOW())" .
|
|
" ON DUPLICATE KEY UPDATE" .
|
|
" vr_sq=values(vr_sq), vrfc_type=values(vrfc_type), type=values(type), code=values(code), comment=values(comment), insert_user=values(insert_user), insert_tm=values(insert_tm)";
|
|
|
|
$data = array(
|
|
$vr_sq,
|
|
$vrfc_type,
|
|
$type,
|
|
$code,
|
|
$comment,
|
|
$usr_sq
|
|
);
|
|
|
|
|
|
$res = $this->db->query($sql, $data);
|
|
}
|
|
|
|
public function update_owner_verifiable($vr_sq, $owner_verifiable)
|
|
{
|
|
$sql = "UPDATE v2_vrfc_req" .
|
|
" SET owner_verifiable = ?" .
|
|
" WHERE vr_sq = ?";
|
|
$data = array(
|
|
$owner_verifiable,
|
|
$vr_sq
|
|
);
|
|
|
|
$this->db->query($sql, $data);
|
|
$s = $this->db->getLastQuery();
|
|
log_message('debug', "====update_owner_verifiable====" . $s);
|
|
}
|
|
|
|
/* 등기부등본 API 호출*/
|
|
public function getDatacertAPI($vr_sq, $vrfc_type = '')
|
|
{
|
|
// 요청정보
|
|
$sql = "SELECT vr_sq, atcl_no, step, cpid, cp_atcl_id, trade_type, realtor_nm, realtor_tel_no, seller_tel_no, vrfc_type, rgbk_confirm, req_type, rdate, stat_cd, try_cnt, insert_user, insert_tm, owner_verifiable" .
|
|
" FROM v2_vrfc_req WHERE vr_sq = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
$row_vrfc_req = $query->getRowArray();
|
|
|
|
if (!empty($vrfc_type)) {
|
|
$row_vrfc_req['vrfc_type'] = $vrfc_type;
|
|
}
|
|
|
|
log_message('debug', 'getDatacertApi_query => ' . $this->db->getLastQuery());
|
|
log_message('debug', 'getDatacertApi_result => ' . json_encode($row_vrfc_req));
|
|
|
|
// 매물정보
|
|
$sql = "SELECT vr_sq, atcl_no, cpid, cp_atcl_id, rlet_type_cd, trade_type, address_code, address1, address2, address3, sply_spc, excls_spc, tot_spc, grnd_spc, bldg_spc, deal_amt, wrrnt_amt, lease_amt, isale_amt, prem_amt, sise, floor, rdate, seller_tel_no, seller_nm, realtor_nm, realtor_tel_no, hscp_no, hscp_nm, ptp_no, ptp_nm, charger, req_price_yn, reg_charger, dept1_sq, dept2_sq, reg_dept1_sq, reg_dept2_sq, floor2, vrfc_type_sub" .
|
|
" FROM v2_article_info WHERE vr_sq = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
$row_article_info = $query->getRowArray();
|
|
|
|
log_message('debug', $this->db->getLastQuery());
|
|
|
|
// 수정정보
|
|
$sql = "SELECT vr_sq, bild_nm, rm_no, floor, address_code, address2, address3, trade_type, deal_amt, wrrnt_amt, lease_amt, isale_amt, prem_amt, sply_spc, excls_spc, tot_spc, grnd_spc, bldg_spc, hscp_no, hscp_nm, ptp_no, ptp_nm, modify_yn, floor2" .
|
|
" FROM v2_modify_info WHERE vr_sq = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq]);
|
|
$row_modify_info = $query->getRowArray();
|
|
|
|
log_message('debug', $this->db->getLastQuery());
|
|
|
|
// 검증결과
|
|
$sql = "SELECT vr_sq, vrfc_type, success, type, charger, date, insert_user, insert_tm, update_user, update_tm, work_type" .
|
|
" FROM v2_confirm WHERE vr_sq = ? AND vrfc_type = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq, $row_vrfc_req['vrfc_type']]);
|
|
$row_confirm = $query->getRowArray();
|
|
|
|
log_message('debug', $this->db->getLastQuery());
|
|
|
|
// 확인정보
|
|
$sql = "SELECT a.type, a.code, a.comment, b.ownerNm, b.owner_birth" .
|
|
" FROM v2_check_list a" .
|
|
" INNER JOIN v2_article_info b ON a.vr_sq = b.vr_sq" .
|
|
" WHERE a.vr_sq = ? AND a.vrfc_type = ?";
|
|
|
|
$query = $this->db->query($sql, [$vr_sq, $row_vrfc_req['vrfc_type']]);
|
|
$res_check_list = $query->getResultArray();
|
|
|
|
log_message('debug', $this->db->getLastQuery());
|
|
|
|
log_message('debug', implode(', ', $row_confirm ?? []));
|
|
|
|
$atclNo = $row_vrfc_req['atcl_no'];
|
|
$type = $row_vrfc_req['try_cnt'];
|
|
$success = empty($row_confirm['success']) ? false : true;
|
|
$charger = $row_confirm['charger'] ?? '';
|
|
$date = $row_confirm['date'] ?? '';
|
|
|
|
$ownerVerifiable = match ($row_vrfc_req['owner_verifiable']) {
|
|
"1", "true" => true,
|
|
default => false,
|
|
};
|
|
|
|
$checkList = [];
|
|
foreach ($res_check_list as $row) {
|
|
$checkList[] = [
|
|
'type' => $row['type'],
|
|
'code' => $row['code'],
|
|
'comment' => $row['comment'],
|
|
'ownerNm' => $row['ownerNm'],
|
|
'ownerBirth' => $row['owner_birth'],
|
|
];
|
|
}
|
|
|
|
$modifyInfo = [];
|
|
if ($row_modify_info['modify_yn'] == 'Y') {
|
|
if (!empty($row_modify_info['hscp_no'])) {
|
|
// 공동주택
|
|
$modifyInfo = [
|
|
'hscpNo' => $row_modify_info['hscp_no'],
|
|
'ptpNo' => $row_modify_info['ptp_no'],
|
|
'bildNm' => $row_modify_info['address2'],
|
|
'rmNo' => $row_modify_info['address3'],
|
|
'tradeType' => $row_modify_info['trade_type'],
|
|
'dealAmt' => $row_modify_info['deal_amt'],
|
|
'wrrntAmt' => $row_modify_info['wrrnt_amt'],
|
|
'leaseAmt' => $row_modify_info['lease_amt'],
|
|
'isaleAmt' => $row_modify_info['isale_amt'],
|
|
'premAmt' => $row_modify_info['prem_amt'],
|
|
'floor' => $row_modify_info['floor'],
|
|
];
|
|
} else {
|
|
// 비공동주택
|
|
$modifyInfo = [
|
|
'addressCode' => $row_modify_info['address_code'],
|
|
'address2' => $row_modify_info['address2'],
|
|
'address3' => $row_modify_info['address3'],
|
|
'tradeType' => $row_modify_info['trade_type'],
|
|
'dealAmt' => $row_modify_info['deal_amt'],
|
|
'wrrntAmt' => $row_modify_info['wrrnt_amt'],
|
|
'leaseAmt' => $row_modify_info['lease_amt'],
|
|
'splySpc' => $row_modify_info['sply_spc'],
|
|
'exclsSpc' => $row_modify_info['excls_spc'],
|
|
'totSpc' => $row_modify_info['tot_spc'],
|
|
'grndSpc' => $row_modify_info['grnd_spc'],
|
|
'bldgSpc' => $row_modify_info['bldg_spc'],
|
|
'floor' => $row_modify_info['floor'],
|
|
'floor2' => $row_modify_info['floor2'],
|
|
];
|
|
}
|
|
|
|
$return_data = [
|
|
'atclNo' => $atclNo,
|
|
'type' => $type,
|
|
'success' => $success,
|
|
'checkList' => $checkList,
|
|
'charger' => $charger,
|
|
'modifyInfo' => $modifyInfo,
|
|
'date' => $date,
|
|
'vrfcType' => $row_article_info['vrfc_type_sub'],
|
|
'ownerVerifiable' => $ownerVerifiable,
|
|
];
|
|
|
|
log_message('debug', "713 getDatacertAPI_1 {$atclNo} ::: " . json_encode($return_data) . PHP_EOL);
|
|
|
|
return $return_data;
|
|
} else {
|
|
$return_data = [
|
|
'atclNo' => $atclNo,
|
|
'type' => $type,
|
|
'success' => $success,
|
|
'checkList' => $checkList,
|
|
'charger' => $charger,
|
|
'date' => $date,
|
|
'vrfcType' => $row_article_info['vrfc_type_sub'],
|
|
'ownerVerifiable' => $ownerVerifiable,
|
|
];
|
|
|
|
log_message('debug', "713 getDatacertAPI_2 {$atclNo} ::: " . json_encode($return_data) . PHP_EOL);
|
|
|
|
return $return_data;
|
|
}
|
|
}
|
|
|
|
public function chgStat($vr_sq, $stat_cd, $insert_tm)
|
|
{
|
|
// 상태변경 TABLE INSERT
|
|
$usr_sq = session('usr_sq');
|
|
$usr_id = session('usr_id');
|
|
|
|
$list = $this->getDetail($vr_sq);
|
|
|
|
$sql = "INSERT INTO v2_chg_stat (vr_sq, stat_cd, insert_user, insert_tm)" .
|
|
"VALUES (?, ?, ?, ?)" .
|
|
"ON DUPLICATE KEY " .
|
|
"UPDATE vr_sq=VALUES(vr_sq), stat_cd=VALUES(stat_cd), insert_user=VALUES(insert_user), insert_tm=VALUES(insert_tm)";
|
|
|
|
$data = [
|
|
$vr_sq,
|
|
$stat_cd,
|
|
$usr_sq,
|
|
$insert_tm
|
|
];
|
|
|
|
$this->db->transStart();
|
|
|
|
$this->db->query($sql, $data);
|
|
|
|
$error = $this->db->error();
|
|
$return = [
|
|
'error_number' => $error['code'],
|
|
'error_message' => $error['message']
|
|
];
|
|
|
|
if (empty($return['error_number'])) {
|
|
// 로그를 남긴다.
|
|
if ($this->db->affectedRows() > 0) {
|
|
// $changed = $this->what_is_changed($list, $data, '');
|
|
// if (!empty($changed)) {
|
|
$this->saveChangedHistory($list['vr_sq'], $stat_cd, 'C9', $usr_id, "상태변경 저장");
|
|
// }
|
|
}
|
|
}
|
|
|
|
$this->db->transComplete();
|
|
|
|
return $return;
|
|
}
|
|
|
|
public function chgStatVrfc($vr_sq, $stat_cd)
|
|
{
|
|
$data = ['stat_cd' => $stat_cd];
|
|
|
|
$builder = $this->db->table('v2_vrfc_req');
|
|
$builder->where('vr_sq', $vr_sq);
|
|
$builder->update($data);
|
|
|
|
$error = $this->db->error();
|
|
return [
|
|
'error_number' => $error['code'],
|
|
'error_message' => $error['message']
|
|
];
|
|
}
|
|
|
|
public function chgStatFax($vr_sq, $stat_cd)
|
|
{
|
|
$data = ['stat_cd' => $stat_cd];
|
|
|
|
$builder = $this->db->table('fax_imgs');
|
|
$builder->where('vr_sq', $vr_sq);
|
|
$builder->update($data);
|
|
|
|
$error = $this->db->error();
|
|
return [
|
|
'error_number' => $error['code'],
|
|
'error_message' => $error['message']
|
|
];
|
|
}
|
|
|
|
public function updateStat($vr_sq, $reg_status)
|
|
{
|
|
$data = ['reg_status' => $reg_status];
|
|
|
|
$builder = $this->db->table('v2_article_info');
|
|
$builder->where('vr_sq', $vr_sq);
|
|
$builder->update($data);
|
|
|
|
$error = $this->db->error();
|
|
return [
|
|
'error' => [
|
|
'code' => $error['code'],
|
|
'message' => $error['message']
|
|
]
|
|
];
|
|
}
|
|
|
|
public function chgRegiTryCnt($vr_sq, $try_cnt)
|
|
{ //v2_vrfc_req try_cnt 값 변경.
|
|
$sql = "UPDATE v2_vrfc_req" .
|
|
" SET reg_try_cnt = ?" .
|
|
" WHERE vr_sq = ?";
|
|
|
|
$data = array(
|
|
$try_cnt,
|
|
$vr_sq
|
|
);
|
|
|
|
$this->db->query($sql, $data);
|
|
}
|
|
|
|
public function modify_info($vr_sq, $owner_birth, $address2, $address2a, $address2b, $address3, $address4, $address_code, $hscp_no, $hscp_nm)
|
|
{
|
|
$this->db->transStart();
|
|
$usr_id = session('usr_id');
|
|
|
|
$sql = "UPDATE v2_modify_info
|
|
SET address2 = ?
|
|
,address2a = ?
|
|
,address2b = ?
|
|
,address3 = ?
|
|
,address4 = ?
|
|
,address_code = ?
|
|
,hscp_no = ?
|
|
,hscp_nm = ?
|
|
,modify_yn = 'Y'
|
|
WHERE vr_sq = ?";
|
|
$data = array(
|
|
$address2,
|
|
$address2a,
|
|
$address2b,
|
|
$address3,
|
|
$address4,
|
|
$address_code,
|
|
$hscp_no,
|
|
$hscp_nm,
|
|
$vr_sq
|
|
);
|
|
|
|
if ($this->db->query($sql, $data) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '저장 실패',
|
|
];
|
|
}
|
|
|
|
$sql2 = "UPDATE v2_article_info
|
|
SET owner_birth = ?
|
|
,address2 = ?
|
|
,address3 = ?
|
|
,address_code = ?
|
|
,hscp_no = ?
|
|
,hscp_nm = ?
|
|
WHERE vr_sq = ?";
|
|
$data2 = array(
|
|
$owner_birth,
|
|
$address2,
|
|
$address3,
|
|
$address_code,
|
|
$hscp_no,
|
|
$hscp_nm,
|
|
$vr_sq
|
|
);
|
|
|
|
if ($this->db->query($sql2, $data2) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '저장 실패',
|
|
];
|
|
}
|
|
|
|
$sql3 = "UPDATE v2_article_info_etc
|
|
SET address2a = ?
|
|
,address2b = ?
|
|
WHERE vr_sq = ?";
|
|
$data3 = array(
|
|
$address2a,
|
|
$address2b,
|
|
$vr_sq
|
|
);
|
|
|
|
if ($this->db->query($sql3, $data) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '저장 실패',
|
|
];
|
|
}
|
|
|
|
$this->saveChangedHistory($vr_sq, '40', 'C34', $usr_id, '생년월일 정보 변경');
|
|
|
|
$this->db->transComplete();
|
|
return [
|
|
'success' => true,
|
|
];
|
|
}
|
|
|
|
// 메모저장
|
|
public function saveMemo($data)
|
|
{
|
|
$usr_id = session('usr_id');
|
|
|
|
$sql = "UPDATE v2_vrfc_req SET
|
|
memo = ?
|
|
WHERE vr_sq = ?";
|
|
|
|
if ($this->db->query($sql, $data) === false) {
|
|
return [
|
|
'success' => false,
|
|
'msg' => '파일정보 저장 실패',
|
|
];
|
|
}
|
|
|
|
$row = $this->getDetail($data[1]);
|
|
|
|
$memo = "메모변경 : " . $row['memo'] . " => " . $data[0];
|
|
$this->saveChangedHistory($data[1], $row['pre_stat_cd'], 'C19', $usr_id, $memo);
|
|
|
|
return [
|
|
'success' => true
|
|
];
|
|
}
|
|
|
|
// 정보변경 이력 저장
|
|
public function saveChangedHistory($vr_sq, $stat_cd, $chg_type, $usr_id, $memo)
|
|
{
|
|
$sql = "INSERT INTO v2_chg_history" .
|
|
" (vr_sq, stat_cd, chg_type, insert_id, insert_tm, memo)" .
|
|
" VALUES" .
|
|
" (?, ?, ?, ?, now(), ?)";
|
|
|
|
$data = [
|
|
$vr_sq,
|
|
$stat_cd,
|
|
$chg_type,
|
|
$usr_id,
|
|
$memo
|
|
];
|
|
|
|
$res = $this->db->query($sql, $data);
|
|
}
|
|
} |