SELECT
ci.CASE_NO AS caseNo,
ci.POLICY_NO AS policyNo,
ci.REPORT_DATE AS reportDate,
pi.ADDRESS AS address,
ci.DANGER_ADDRESS AS dangerAddress,
ci.REPORTER_NAME AS reporterName,
ci.REPORTER_PHONE AS reporterPhone,
ci.DANGER_DATE AS dangerDate,
u.USER_NAME AS userName,
unit.UNIT_NAME AS unitName,
unit.UNIT_CODE AS unitCode,
unit.PARENT_UNIT_ID AS parentUnitId,
unit.UNIT_LEVEL AS userLevel,
task.STAFF_ID AS staffId,
task.STATUS AS status,
task.POINT AS point,
task.INSERT_TIME AS insertTime,
task.UPDATE_TIME AS staffEndTime,
'青铜' AS adjustingClassify
FROM USER u
LEFT JOIN USER_ROLE_RELEVANCY rcy ON rcy.USER_ACCOUNT = u.ID
LEFT JOIN ROLE r ON r.ROLE_ID = rcy.ROLE_INFO_ID
LEFT JOIN AC_UNIT unit ON u.COMPANY_CODE = unit.UNIT_CODE
LEFT JOIN HT_CLAIM_CASE_TASK_INFO task ON task.APP_RECEIVER_CODE = u.HR_CODE
LEFT JOIN HT_CLAIM_CASE_INFO ci ON task.CASE_NO = ci.CASE_NO
LEFT JOIN HT_POLICY_INSURED pi ON ci.POLICY_NO= pi.POLICY_NO
WHERE task.STATUS IN ('5','6','8','9')
AND r.ROLECODE = 'app_chuli'
AND u.IS_DELETE IS NULL
AND u.USER_STATUS = '1'
<if test="unitLevel eq '0'">
AND unit.UNIT_LEVEL IN(0,1,2,3)
</if>
<if test="unitLevel eq '1'">
AND unit.UNIT_LEVEL IN(1,2,3)
</if>
<if test="unitLevel eq '2'">
AND unit.UNIT_LEVEL IN(2,3)
</if>
<if test="unitLevel eq '3'">
AND unit.UNIT_LEVEL IN(3)
</if>
<if test="unitCode != null and unitCode != ''">
AND unit.UNIT_CODE LIKE CONCAT(#{unitCode},'%')
</if>
<if test="startTime != null">
<![CDATA[
and DATE_FORMAT(task.INSERT_TIME, '%Y-%m-%d')>= DATE_FORMAT(#{startTime}, '%Y-%m-%d')
]]>
</if>
<if test="endTime != null">
<![CDATA[
and DATE_FORMAT(task.INSERT_TIME, '%Y-%m-%d')<= DATE_FORMAT(#{endTime}, '%Y-%m-%d')
]]>
</if>
ORDER BY task.INSERT_TIME DESC