|
|
<?php
|
|
|
|
|
|
|
|
|
class Groupinfo_model extends CI_Model {
|
|
|
|
|
|
function __construct() {
|
|
|
parent::__construct();
|
|
|
$this->HT = $this->load->database('HT', TRUE);
|
|
|
}
|
|
|
|
|
|
//团的信息
|
|
|
function get_group_info($gri_sn) {
|
|
|
$sql = "
|
|
|
EXEC SP_Evaluation_GetGroupInfo 1,0,?,1
|
|
|
|
|
|
";
|
|
|
$query = $this->HT->select_stored_procedure($sql, array($gri_sn));
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
//生成汉特各项平均分
|
|
|
function SP_VendorQuality_SetMark($gri_sn, $vei_sn, $eoi_sn) {
|
|
|
$sql = "
|
|
|
EXEC SP_VendorQuality_SetMark ?,?,?
|
|
|
|
|
|
";
|
|
|
$query = $this->HT->select_stored_procedure($sql, array($gri_sn, $vei_sn, $eoi_sn));
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
//团队相关联的信息，地接等等
|
|
|
function get_eva_objectinfo($topnum, $eoi_sn, $gri_sn = false, $vei_sn = false, $cii_sn = false, $eoi_type = false, $eoi_fillworkers_sn = false, $eoi_date = false, $orderby = ' EOI_Date DESC ') {
|
|
|
if (empty($topnum)) {
|
|
|
$topnum = 1;
|
|
|
}
|
|
|
$sql = "
|
|
|
SELECT TOP $topnum EOI_SN,
|
|
|
EOI_GRI_SN,
|
|
|
EOI_VEI_SN,
|
|
|
EOI_ObjSN,
|
|
|
EOI_CII_SN,
|
|
|
EOI_Type,
|
|
|
EOI_Score,
|
|
|
EOI_GetDate,
|
|
|
EOI_Date,
|
|
|
EOI_SendEvaluation,
|
|
|
EOI_NotSendReason,
|
|
|
EOI_JD,
|
|
|
EOI_JDCheckDate,
|
|
|
EOI_Evaluation,
|
|
|
EOI_NotEvaluationReason,
|
|
|
EOI_CusService,
|
|
|
EOI_CusServiceCheckDate,
|
|
|
Checker,
|
|
|
Checked,
|
|
|
Creator,
|
|
|
CreateDate,
|
|
|
LastEidtUser,
|
|
|
LastEditTime,
|
|
|
EOI_Cancel,
|
|
|
EOI_GroupType,
|
|
|
EOI_AddCode,
|
|
|
EOI_DEI_SN,
|
|
|
EOI_OPI_SN,
|
|
|
EOI_FillWorkers_SN,
|
|
|
EOI_VerifyUser_SN,
|
|
|
(
|
|
|
SELECT TOP 1 vei2_companybn
|
|
|
FROM VendorInfo2
|
|
|
WHERE vei2_vei_sn = EOI_VEI_SN
|
|
|
AND VEI2_LGC = 2
|
|
|
) VendorName,
|
|
|
(
|
|
|
SELECT TOP 1 gi. GRI_No
|
|
|
FROM GRoupInfo gi
|
|
|
WHERE gi .GRI_SN = EOI_GRI_SN
|
|
|
) AS groupname
|
|
|
FROM Eva_ObjectInfo
|
|
|
WHERE 1=1
|
|
|
AND EOI_sendEvaluation = 1
|
|
|
AND EOI_Type = 1
|
|
|
AND (select COUNT(*) from GRoupInfo where isnull(GRI_OrderType,227001)=227001 and GRI_SN=eoi_gri_sn)>0
|
|
|
AND eoi_cancel = 0
|
|
|
AND year(EOI_Date)> '2010'
|
|
|
|
|
|
";
|
|
|
|
|
|
empty($eoi_sn) ? false : $sql.=' AND EOI_SN= ' . $this->HT->escape($eoi_sn);
|
|
|
empty($gri_sn) ? false : $sql.=' AND EOI_GRI_SN= ' . $this->HT->escape($gri_sn);
|
|
|
empty($vei_sn) ? false : $sql.=' AND EOI_VEI_SN= ' . $this->HT->escape($vei_sn);
|
|
|
empty($cii_sn) ? false : $sql.=' AND EOI_CII_SN= ' . $this->HT->escape($cii_sn);
|
|
|
empty($eoi_type) ? false : $sql.=' AND EOI_Type= ' . $this->HT->escape($eoi_type);
|
|
|
empty($eoi_date) ? false : $sql.=' AND ' . $eoi_date;
|
|
|
//查找没有添加反馈表的团
|
|
|
($eoi_fillworkers_sn !== null) ? false : $sql.=' AND EOI_FillWorkers_SN IS NULL AND EOI_VerifyUser_SN IS NULL AND (select COUNT(*) from InfoManager.dbo.tga_feedback_evaluation where group_id=EOI_GRI_SN and travel_agency_id=EOI_VEI_SN )=0';
|
|
|
|
|
|
$sql.=' ORDER BY ' . $orderby;
|
|
|
$query = $this->HT->query($sql);
|
|
|
//print_r($this->HT->queries);
|
|
|
if ($topnum == 1) {
|
|
|
if ($query->num_rows() > 0) {
|
|
|
$row = $query->row();
|
|
|
return $row;
|
|
|
} else {
|
|
|
return FALSE;
|
|
|
}
|
|
|
} else {
|
|
|
return $query->result();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//更新导游信息
|
|
|
function update_guide($eoi_sn, $eoi_objsn) {
|
|
|
$sql = "
|
|
|
UPDATE Eva_ObjectInfo
|
|
|
SET
|
|
|
EOI_ObjSN=?
|
|
|
WHERE EOI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($eoi_objsn, $eoi_sn));
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
function update_feedback_verify($eoi_sn, $type, $lmi_sn) {
|
|
|
switch ($type) {
|
|
|
case 'verifysave':
|
|
|
$sql = "
|
|
|
UPDATE Eva_ObjectInfo
|
|
|
SET
|
|
|
EOI_FillWorkers_SN=?,
|
|
|
EOI_VRequestVerify=0,
|
|
|
EOI_FWks_LastEditTime=GETDATE()
|
|
|
WHERE EOI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($lmi_sn, $eoi_sn));
|
|
|
break;
|
|
|
case 'verifygo':
|
|
|
$sql = "
|
|
|
UPDATE Eva_ObjectInfo
|
|
|
SET
|
|
|
EOI_FillWorkers_SN=?,
|
|
|
EOI_VRequestVerify=1,
|
|
|
EOI_FWks_LastEditTime=GETDATE()
|
|
|
WHERE EOI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($lmi_sn, $eoi_sn));
|
|
|
break;
|
|
|
case 'verifyok'://审核通过
|
|
|
$sql = "
|
|
|
UPDATE Eva_ObjectInfo
|
|
|
SET
|
|
|
EOI_VerifyUser_SN=?,
|
|
|
EOI_Verify_lastEditTime=GETDATE()
|
|
|
WHERE EOI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($lmi_sn, $eoi_sn));
|
|
|
break;
|
|
|
case 'verifyno': //审核不通过
|
|
|
$sql = "
|
|
|
UPDATE Eva_ObjectInfo
|
|
|
SET
|
|
|
EOI_VerifyUser_SN=null,
|
|
|
EOI_Verify_lastEditTime=null
|
|
|
WHERE EOI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($eoi_sn));
|
|
|
break;
|
|
|
default:
|
|
|
return false;
|
|
|
}
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
// 判断多家庭反馈表
|
|
|
function get_many_family($gri_sn){
|
|
|
$sql = "
|
|
|
SELECT cul_cui_sn ESI_MEI_SN,
|
|
|
(
|
|
|
SELECT TOP 1 mei_firstname + ' ' + mei_lastname
|
|
|
FROM MEmberInfo
|
|
|
WHERE mei_sn = cul_cui_sn
|
|
|
) esiname
|
|
|
FROM CustomerList
|
|
|
WHERE cul_coli_sn = (
|
|
|
SELECT TOP 1 coli_sn
|
|
|
FROM ConfirmLineInfo
|
|
|
WHERE coli_gri_sn = ?
|
|
|
)
|
|
|
AND CUL_SendEvaluation = 1
|
|
|
ORDER BY
|
|
|
cul_cui_sn
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn));
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query->result();
|
|
|
}
|
|
|
|
|
|
//反馈表分数
|
|
|
function get_service_item_info($eoi_sn, $gri_sn, $vei_sn,$mei_sn) {
|
|
|
$sql = "
|
|
|
SELECT ESI_SN,
|
|
|
ESI_VEI_SN,
|
|
|
ESI_GRI_SN,
|
|
|
ESI_Date,
|
|
|
ESI_LeaveCity,
|
|
|
ESI_ServiceCity,
|
|
|
ESI_ServiceType,
|
|
|
IsNull(ESI_ServiceSN,0) as ESI_ServiceSN,
|
|
|
ESI_ServiceSN2,
|
|
|
ESI_ServiceName,
|
|
|
ESI_Describe,
|
|
|
ESI_ServiceSNBack,
|
|
|
ESI_Order,
|
|
|
isnull(ESI_Rate,0) as ESI_Rate,
|
|
|
ESI_Comment,
|
|
|
ESI_NotCount,
|
|
|
ESI_NotCountReason,
|
|
|
ESI_EOI_SN,
|
|
|
ESI_CII_SN,
|
|
|
ESI_DayNo,
|
|
|
ESI_MEI_SN,
|
|
|
ESI_SYC_SN,
|
|
|
ESI_LocalAgencySaid,
|
|
|
ESI_LastEditDate,
|
|
|
ESI_ServeDate
|
|
|
FROM Eva_ServiceItemInfo
|
|
|
WHERE (esi_eoi_sn = ? AND esi_gri_sn = ?)
|
|
|
AND esi_vei_sn = ?
|
|
|
AND esi_mei_sn = ?
|
|
|
AND ESI_ServiceType IN ('A', 'C', '7','Y','W')
|
|
|
ORDER BY
|
|
|
(
|
|
|
CASE ESI_ServiceType
|
|
|
WHEN 'A' THEN 2
|
|
|
WHEN '4' THEN 3
|
|
|
WHEN 'w' THEN 4
|
|
|
WHEN 'Y' THEN 5
|
|
|
ELSE 1
|
|
|
END
|
|
|
),
|
|
|
ESI_ServeDate,
|
|
|
ESI_Order,
|
|
|
ESI_ServiceSN
|
|
|
|
|
|
";
|
|
|
|
|
|
$query = $this->HT->query($sql, array($eoi_sn, $gri_sn, $vei_sn,$mei_sn));
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query->result();
|
|
|
}
|
|
|
|
|
|
//生成反馈项目
|
|
|
public function create_service_item_info($eoi_sn, $gri_sn, $vei_sn) {
|
|
|
$lgc = $this->get_group_lgc($gri_sn);
|
|
|
// 多家庭反馈表生成
|
|
|
$customer_list = $this->get_customer($gri_sn);
|
|
|
if ($customer_list) {
|
|
|
foreach ($customer_list as $item_cus) {
|
|
|
$mei_sn = $item_cus->cul_cui_sn;
|
|
|
$sql = " EXEC SP_VendorQuality_CreateServiceItemTest 0,?,?,0,?,?,? "; //@Debug,@GRI_SN,@VEI_SN,@VType,@LGC,@EOI_SN,@MEI_SN
|
|
|
$query = $this->HT->select_stored_procedure($sql, array($gri_sn, $vei_sn, $lgc, $eoi_sn, $mei_sn));
|
|
|
//echo $gri_sn.','.$vei_sn.','.$lgc.','.$eoi_sn;
|
|
|
}
|
|
|
} else {
|
|
|
$mei_sn = 0;
|
|
|
$sql = " EXEC SP_VendorQuality_CreateServiceItemTest 0,?,?,0,?,?,? "; //@Debug,@GRI_SN,@VEI_SN,@VType,@LGC,@EOI_SN,@MEI_SN
|
|
|
$query = $this->HT->select_stored_procedure($sql, array($gri_sn, $vei_sn, $lgc, $eoi_sn, $mei_sn));
|
|
|
}
|
|
|
return true;
|
|
|
}
|
|
|
|
|
|
//查询团队的客人SN
|
|
|
public function get_customer($gri_sn) {
|
|
|
$sql = "
|
|
|
SELECT cul_cui_sn
|
|
|
FROM CustomerList
|
|
|
WHERE cul_coli_sn = (
|
|
|
SELECT TOP 1 coli_sn
|
|
|
FROM ConfirmLineInfo
|
|
|
WHERE coli_gri_sn = ?
|
|
|
AND CUL_SendEvaluation = 1
|
|
|
)
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn));
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query->result();
|
|
|
}
|
|
|
|
|
|
//查询团队的语种，默认1英文
|
|
|
public function get_group_lgc($gri_sn) {
|
|
|
$sql = " SELECT ISNULL(
|
|
|
(
|
|
|
SELECT DEI_LGC
|
|
|
FROM DepartmentInfo
|
|
|
WHERE DEI_SN = (
|
|
|
SELECT TOP 1 coli_department
|
|
|
FROM ConfirmLineInfo
|
|
|
WHERE coli_gri_sn = ?
|
|
|
)
|
|
|
),
|
|
|
1
|
|
|
) lgc
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn));
|
|
|
if ($query->result()) {
|
|
|
$row = $query->row();
|
|
|
return $row->lgc;
|
|
|
} else {
|
|
|
return 1;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public function get_group_service($eoi_sn, $vei_sn) {
|
|
|
$sql = " SP_Evaloation_GetHotelAndElse 0,?,?,1,0 ";
|
|
|
$query = $this->HT->select_stored_procedure($sql, array($eoi_sn, $vei_sn));
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
public function get_eva_feedback($gri_sn, $vei_sn, $eoi_sn, $eef_type) {
|
|
|
$sql = "
|
|
|
SELECT TOP 1 eefb.EEF_SN,
|
|
|
eefb.EEF_GRI_SN,
|
|
|
eefb.EEF_VEI_SN,
|
|
|
eefb.EEF_MEI_SN,
|
|
|
eefb.EEF_CII_SN,
|
|
|
eefb.EEF_EOI_SN,
|
|
|
eefb.EEF_Type,
|
|
|
eefb.EEF_ID,
|
|
|
eefb.EEF_Content,
|
|
|
eefb.EEF_Date,
|
|
|
eefb.EEF_Creator
|
|
|
FROM Eva_ElseFeedBack eefb
|
|
|
WHERE eefb.EEF_GRI_SN = ?
|
|
|
AND eefb.EEF_VEI_SN = ?
|
|
|
AND eefb.EEF_EOI_SN = ?
|
|
|
AND eefb.EEF_Type = ?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn, $vei_sn, $eoi_sn, $eef_type));
|
|
|
//print_r($this->HT->queries);
|
|
|
if ($query->result()) {
|
|
|
$row = $query->row();
|
|
|
return $row;
|
|
|
} else {
|
|
|
return FALSE;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//更新分数
|
|
|
public function update_esi_rate($esi_sn, $rate) {
|
|
|
$sql = "
|
|
|
UPDATE Eva_ServiceItemInfo
|
|
|
SET
|
|
|
ESI_Rate = ?
|
|
|
WHERE ESI_SN=?
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($rate, $esi_sn));
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
//更新问题的答案
|
|
|
public function update_eva_answer($gri_sn, $eoi_sn, $vei_sn, $eef_type, $eef_content, $eef_id) {
|
|
|
//根据内容来查询答案是否已经有记录
|
|
|
$sql = "
|
|
|
|
|
|
SELECT TOP 1 eefb.EEF_SN
|
|
|
FROM Eva_ElseFeedBack eefb
|
|
|
WHERE eefb.EEF_GRI_SN = ?
|
|
|
AND eefb.EEF_EOI_SN=?
|
|
|
AND eefb.EEF_VEI_SN = ?
|
|
|
AND eefb.EEF_Type = ?
|
|
|
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn, $eoi_sn, $vei_sn, $eef_type));
|
|
|
if ($query->result()) {
|
|
|
$row = $query->row();
|
|
|
|
|
|
$sql = "
|
|
|
|
|
|
UPDATE Eva_ElseFeedBack
|
|
|
SET
|
|
|
EEF_Content = ?,
|
|
|
EEF_ID=?,
|
|
|
EEF_Date = GETDATE()
|
|
|
WHERE EEF_SN = ?
|
|
|
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($eef_content, $eef_id, $row->EEF_SN));
|
|
|
} else {
|
|
|
$sql = "
|
|
|
|
|
|
INSERT INTO Eva_ElseFeedBack
|
|
|
(
|
|
|
EEF_GRI_SN,
|
|
|
EEF_EOI_SN,
|
|
|
EEF_VEI_SN,
|
|
|
EEF_Type,
|
|
|
EEF_Content,
|
|
|
EEF_Date,
|
|
|
EEF_ID
|
|
|
)
|
|
|
VALUES
|
|
|
(
|
|
|
?,?,?,?,?,GETDATE(),?
|
|
|
)
|
|
|
|
|
|
";
|
|
|
$query = $this->HT->query($sql, array($gri_sn, $eoi_sn, $vei_sn, $eef_type, $eef_content, $eef_id));
|
|
|
}
|
|
|
|
|
|
//print_r($this->HT->queries);
|
|
|
return $query;
|
|
|
}
|
|
|
|
|
|
//判断反馈表是否已经由小助手那边提交
|
|
|
function CheckXzsSubmit($grisn,$veisn){
|
|
|
$sql = "select top 1 * from InfoManager.dbo.tga_feedback_evaluation
|
|
|
where group_id=? and travel_agency_id=? ";
|
|
|
$query = $this->HT->query($sql,array($grisn,$veisn));
|
|
|
if ($query->result()){
|
|
|
return true;
|
|
|
}else{
|
|
|
return false;
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|