SELECT DISTINCT
C.ID AS ID,
C.IS_LOCK AS IS_LOCK,
C.UPDATE_BY AS UPDATE_BY,
C.REGIST_NO AS REGIST_NO,
P.POLICY_NUMBER AS POLICY_NUMBER,
C.USER_NAME AS USER_NAME,
C.LICENSE_NO AS LICENSE_NO,
O.DESCRIPTION AS ORGNAME,
C.REGIST_DATE AS REGIST_DATE,
C.DAMAGE_DATE AS DAMAGE_DATE,
CO.DESCRIPTION AS COMPENSATIONORG,
C.COMPENSATIONCODE AS COMPENSATIONCODE,
C.BEHALFTHE AS BEHALFTHE,
yy.SURVEY_DATE AS SURVEY_DATE,
yy.UPDATE_TIME,
CASE
WHEN yy1.CI IS NOT NULL THEN
1
ELSE
0
END AS ISAPPOINTDATE,
C.reportType,
C.internetType,
C.delegateMode,
C.isautodispatch,
C. LEVEL,
C.CONSULTATION_TXT
FROM
sd_case_info C
INNER JOIN (
SELECT
D.CASE_INFO_ID,
min(D.SURVEY_DATE) AS SURVEY_DATE,
max(D.UPDATE_TIME) AS UPDATE_TIME
FROM
sd_dispatch_item D
WHERE
D.CASE_INFO_ID IS NOT NULL
AND (
D.DISPATCH_TYPE = 0
OR D.DISPATCH_TYPE = 1
OR D.DISPATCH_TYPE = 2
)
GROUP BY
D.CASE_INFO_ID
) yy ON C.ID = yy.CASE_INFO_ID
LEFT JOIN (
SELECT
DD.CASE_INFO_ID AS CI
FROM
sd_dispatch_item DD
WHERE
DD.CASE_INFO_ID IS NOT NULL
AND DD.DISPATCH_TYPE = 4
AND TIMESTAMPDIFF(
MINUTE,
DD.SURVEY_DATE,
now()
) >- 120 = 1
AND DD. STATUS = 1
GROUP BY
DD.CASE_INFO_ID
) yy1 ON C.ID = yy1.CI
LEFT JOIN (
SELECT
max(POLICY_NUMBER) AS POLICY_NUMBER,
CASE_INFO_ID,
INSURANCE_CODE
FROM
sd_policy_item pp
GROUP BY
CASE_INFO_ID
) P ON C.ID = P.CASE_INFO_ID
LEFT JOIN sd_org_info O ON C.ORG_ID = O.ID
LEFT JOIN sd_org_info CO ON C.COMPENSATIONORG = CO.ORGCODE
WHERE
1 = 1
AND C. STATUS != 99
AND C.IS_DISPATCHED != 0
AND C.CaseVersion = '1'
ORDER BY
C.REGIST_DATE DESC
LIMIT 10,
10