198 lines
8.4 KiB
PHP
198 lines
8.4 KiB
PHP
<?php
|
|
|
|
namespace App\Models\Home;
|
|
|
|
use App\Models\common\CodeModel;
|
|
use CodeIgniter\Model;
|
|
|
|
class HomeModel extends Model
|
|
{
|
|
|
|
public function getHomeStatistics($sdate, $edate)
|
|
{
|
|
$status = $status2 = $status3 = $reserve = $assign = $personal = [];
|
|
$usr_sq = session('usr_sq');
|
|
$usr_level = session('usr_level');
|
|
|
|
if (in_array($usr_level, ['1', '2', '3'], true)) {
|
|
|
|
$codeModel = new CodeModel();
|
|
$codes = $codeModel->getCodeList('RECEIPT_STATUS1');
|
|
helper('string');
|
|
|
|
$columns = '';
|
|
foreach ($codes as $row) {
|
|
$cd = sqlstr_quotes($row['cd']);
|
|
$cdnm = sqlstr_quotes($row['cd_nm']);
|
|
|
|
$columns .= ", SUM(CASE WHEN SUBSTR(b.rcpt_stat, 1, 2) = '" . $cd . "' THEN 1 ELSE 0 END) \"" . $cdnm . "\"";
|
|
}
|
|
$columns = substr($columns, 1);
|
|
|
|
// 상태별 개수
|
|
$sql = "SELECT " . $columns .
|
|
" FROM receipt b" .
|
|
" where b.rcpt_tm BETWEEN ? AND ? ";
|
|
|
|
$sdate .= ' 00:00:00';
|
|
$edate .= ' 23:59:59';
|
|
|
|
$data = [$sdate, $edate];
|
|
$query = $this->db->query($sql, $data);
|
|
$status = $query->getResultArray();
|
|
|
|
// 녹취필요 5개
|
|
$sql = "SELECT b.rcpt_key, b.rcpt_tm, a.photo_save_dt, IFNULL(DATEDIFF(NOW(), a.photo_save_dt),0) elapsed_dt" .
|
|
" FROM result a" .
|
|
" INNER JOIN receipt b ON b.rcpt_sq = a.rcpt_sq and b.rcpt_tm between ? and ?" .
|
|
" WHERE a.req_rec_yn = 'Y'" .
|
|
" AND a.rec_yn != 'Y'" .
|
|
" AND a.result_cd1 != '90'" .
|
|
" ORDER BY 3" .
|
|
" LIMIT 5";
|
|
$data = [$sdate, $edate];
|
|
$query = $this->db->query($sql, $data);
|
|
$status2 = $query->getResultArray();
|
|
|
|
// 동의서없음 5개
|
|
$sql = "SELECT b.rcpt_key, b.rcpt_tm, a.photo_save_dt, IFNULL(DATEDIFF(NOW(), a.photo_save_dt),0) elapsed_dt" .
|
|
" FROM result a" .
|
|
" INNER JOIN receipt b ON b.rcpt_sq = a.rcpt_sq AND b.rcpt_stat = '701000' and b.rcpt_tm between ? and ?" .
|
|
" AND a.result_cd1 != '90'" .
|
|
" ORDER BY 3" .
|
|
" LIMIT 5";
|
|
$data = array($sdate, $edate);
|
|
$query = $this->db->query($sql, $data);
|
|
$status3 = $query->getResultArray();
|
|
}
|
|
|
|
|
|
if (in_array($usr_level, ['1', '2', '3'], true)) { // 시스템관리자, 관리자, 상담원
|
|
$sql = "SELECT a.rcpt_key, a.rcpt_tm, IFNULL(b.rsrv_date, a.rsrv_date) rsrv_date, c.cd_nm rsrv_tm_ap" .
|
|
" FROM receipt a" .
|
|
" LEFT JOIN result b ON b.rcpt_sq = a.rcpt_sq AND b.use_yn = 'Y'" .
|
|
" LEFT JOIN codes c ON c.category = 'RESERVED_APM' AND c.cd = IFNULL(b.rsrv_tm_ap, a.rsrv_tm_ap)" .
|
|
" WHERE a.rcpt_stat <= '20'" .
|
|
" and a.rcpt_tm between ? and ?" .
|
|
" LIMIT 5";
|
|
$data = array($sdate, $edate);
|
|
$query = $this->db->query($sql, $data);
|
|
$reserve = $query->getResultArray();
|
|
|
|
}
|
|
|
|
if (in_array($usr_level, ['4'], true)) { // 현장조사원
|
|
$sql = "SELECT b.rcpt_key, b.rcpt_tm, IFNULL(a.rsrv_date, b.rsrv_date) rsrv_date, c.cd_nm rsrv_tm_ap" .
|
|
" FROM result a" .
|
|
" INNER JOIN receipt b ON b.rcpt_sq = a.rcpt_sq AND b.rcpt_stat = '30' AND b.rcpt_tm between ? and ?" .
|
|
" INNER JOIN codes c ON c.category = 'RESERVED_APM' AND c.cd = IFNULL(a.rsrv_tm_ap, b.rsrv_tm_ap)" .
|
|
" WHERE a.usr_sq = ?" .
|
|
" AND a.use_yn = 'Y'" .
|
|
" LIMIT 5";
|
|
$data = array($sdate, $edate, $usr_sq);
|
|
$query = $this->db->query($sql, $data);
|
|
$assign = $query->getResultArray();
|
|
|
|
|
|
|
|
$sql = "SELECT COUNT(*) assign_count" .
|
|
", IFNULL(SUM(CASE WHEN b.rcpt_stat IN ('39', '49') THEN 1 ELSE 0 END),0) cancel_count" .
|
|
", IFNULL(SUM(CASE b.rcpt_stat WHEN '50' THEN 1 ELSE 0 END),0) complete_count" .
|
|
" FROM result a" .
|
|
" INNER JOIN receipt b ON b.rcpt_sq = a.rcpt_sq AND b.rcpt_tm between ? and ?" .
|
|
" WHERE a.usr_sq = ?" .
|
|
" AND a.assign_save_dt BETWEEN DATE_FORMAT(NOW(),'%Y-%m-01') AND DATE_FORMAT(NOW(),'%Y-%m-%d')" .
|
|
" AND a.use_yn = 'Y'";
|
|
|
|
|
|
$sql = "SELECT " .
|
|
" SUM( case when a.result_cd2 = '2000' then 1 ELSE null END ) as assign_count" .
|
|
", SUM( case when a.result_cd2 = '9030' then 1 ELSE null END ) as cancel_count" .
|
|
", SUM( case when a.result_cd2 = '6000' then 1 ELSE null END ) as complete_count" .
|
|
" FROM result a" .
|
|
" INNER JOIN receipt b ON b.rcpt_sq = a.rcpt_sq AND b.rcpt_tm between ? and ?" .
|
|
" WHERE a.usr_sq = ?" .
|
|
" AND a.rsrv_date BETWEEN DATE_FORMAT(NOW(),'%Y-%m-01') AND DATE_FORMAT(NOW(),'%Y-%m-%d')" .
|
|
" AND a.use_yn = 'Y'";
|
|
$data = array($sdate, $edate, $usr_sq);
|
|
$query = $this->db->query($sql, $data);
|
|
$personal = $query->getRowArray();
|
|
|
|
}
|
|
|
|
return ['status' => $status, 'status2' => $status2, 'status3' => $status3, 'reserve' => $reserve, 'assign' => $assign, 'personal' => $personal];
|
|
|
|
}
|
|
|
|
// 메인 공지사항 조회
|
|
public function getNoticeList()
|
|
{
|
|
$sql = "SELECT a.bbs_sq, a.subject, a.depth, a.hit, a.update_usr, a.update_nm, DATE_FORMAT(a.update_tm, '%Y-%m-%d') AS update_tm
|
|
FROM bbs_main_notice AS a
|
|
WHERE
|
|
a.use_yn = 'Y'
|
|
ORDER BY bbs_sq DESC
|
|
LIMIT 5";
|
|
|
|
$query = $this->db->query($sql);
|
|
$rows = $query->getResultArray();
|
|
$total = $query->getNumRows();
|
|
|
|
/*
|
|
$data = [
|
|
'noticeList' => $rows,
|
|
'total' => $total,
|
|
];
|
|
*/
|
|
|
|
return $rows;
|
|
}
|
|
|
|
// 실적 조회
|
|
public function viewStatData($param)
|
|
{
|
|
$sql = "select a1.*
|
|
from(
|
|
SELECT
|
|
CASE a.insert_user WHEN 0 THEN 1 ELSE a.insert_user END insert_user,
|
|
COUNT( DISTINCT CASE WHEN b.vrfc_type = 'T' AND a.stat_cd = '38' THEN a.vr_sq ELSE NULL END ) phone_cnt ,
|
|
COUNT( DISTINCT CASE WHEN b.vrfc_type = 'D' AND (a.stat_cd = '35' or a.stat_cd = '39') THEN a.vr_sq ELSE NULL END ) paper_cnt ,
|
|
COUNT( CASE WHEN (a.stat_cd = '77') THEN a.vr_sq ELSE NULL END ) + COUNT( DISTINCT CASE WHEN b.stat_cd = '60' AND a.stat_cd = '76' THEN a.vr_sq ELSE NULL END ) AS reg_open_cnt,
|
|
COUNT( CASE WHEN (a.stat_cd = '85') THEN a.vr_sq ELSE NULL END ) + COUNT( DISTINCT CASE WHEN b.stat_cd = '60' AND a.stat_cd = '80' THEN a.vr_sq ELSE NULL END ) AS reg_tempOpen_cnt,
|
|
COUNT( DISTINCT CASE WHEN a.stat_cd = '86' or a.stat_cd = '88' THEN a.vr_sq ELSE NULL END ) AS real_top_R,
|
|
COUNT( DISTINCT CASE WHEN a.stat_cd = '87' or a.stat_cd = '89' THEN a.vr_sq ELSE NULL END ) AS real_top_G,
|
|
IFNULL((select count(distinct f1.vr_sq)
|
|
from v2_files f1
|
|
INNER JOIN v2_vrfc_req b ON b.vr_sq = f1.vr_sq and b.vrfc_type = 'M'
|
|
where f1.insert_tm between concat(DATE_FORMAT({$param['s_date']}, '%Y-%m-%d'),' 00:00:00') AND concat(DATE_FORMAT({$param['s_date']}, '%Y-%m-%d'),' 23:59:59')
|
|
and f1.file_type = '2'
|
|
and f1.use_yn = 'Y'
|
|
AND f1.insert_user = a.insert_user) ,0) AS mobile_cnt
|
|
FROM v2_chg_stat_stats a
|
|
INNER JOIN v2_vrfc_req b ON b.vr_sq = a.vr_sq
|
|
INNER JOIN v2_article_info c ON c.vr_sq = a.vr_sq
|
|
WHERE 1=1
|
|
and a.insert_user = {$param['usr_sq']}
|
|
and a.insert_tm between concat(DATE_FORMAT({$param['s_date']}, '%Y-%m-%d'),' 00:00:00') AND concat(DATE_FORMAT({$param['s_date']}, '%Y-%m-%d'),' 23:59:59')
|
|
) a1 ";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
return $query->getRowArray();
|
|
}
|
|
|
|
// 팩스 건수 조회
|
|
public function getHomeFaxCount()
|
|
{
|
|
$sql = "SELECT
|
|
date_format(now(), '%H') as base_time
|
|
,(SELECT count(*) FROM uds_tiff where save_time >= concat(date_format(now(), '%Y-%m-%d %H'), ':00:00')) as enfax_count
|
|
,(SELECT COUNT(*) FROM FC_RECV_TRAN where TR_RECVTIME >= concat(date_format(now(), '%Y%m%d%H'), '0000')) as lgfax_count ";
|
|
|
|
$query = $this->db->query($sql);
|
|
|
|
|
|
return $query->getRowArray();
|
|
|
|
}
|
|
} |