<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.com.ty.lift.business.annualinspection.mapper.AnnualInspectionMapper" >
	<resultMap id="BaseResultMap" type="cn.com.ty.lift.business.annualinspection.dto.InspectionResponse" >
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="lift_id" property="liftId" jdbcType="BIGINT" />
		<result column="mt_company_id" property="mtCompanyId" jdbcType="BIGINT" />
		<result column="plan_date" property="planDate" jdbcType="TIMESTAMP" />
		<result column="is_check_governer" property="isCheckGoverner" jdbcType="TINYINT" />
		<result column="load_inspection_setting" property="loadInspectionSetting" jdbcType="TINYINT" />
		<result column="finish_time" property="finishTime" jdbcType="TIMESTAMP" />
		<result column="inspector" property="inspector" jdbcType="VARCHAR" />
		<result column="telephone" property="telephone" jdbcType="VARCHAR" />
		<result column="operator" property="operator" jdbcType="VARCHAR" />
		<result column="creator_id" property="creatorId" jdbcType="BIGINT" />
		<result column="create_date" property="createDate" jdbcType="TIMESTAMP" />
        <result column="update_id" property="updateId" jdbcType="BIGINT" />
        <result column="update_date" property="updateDate" jdbcType="TIMESTAMP" />
		<result column="status" property="status" jdbcType="TINYINT" />
		<result column="next_inspection_time" property="nextInspectionTime" jdbcType="TIMESTAMP" />
		<result column="selfcheck_report_img" property="selfcheckReportImg" jdbcType="VARCHAR" />
		<result column="selfcheck_date" property="selfcheckDate" jdbcType="TIMESTAMP" />
		<result column="check_result_img" property="checkResultImg" jdbcType="VARCHAR" />
		<result column="check_date" property="checkDate" jdbcType="TIMESTAMP" />
		<result column="annual_inspection_img" property="annualInspectionImg" jdbcType="VARCHAR" />
		<result column="step_status" property="stepStatus" jdbcType="INTEGER" />
        <result column="step_name" property="stepName" jdbcType="INTEGER" />

		<result column="area_name" property="areaName" jdbcType="VARCHAR" />
        <result column="project_id" property="projectId" jdbcType="BIGINT" />
		<result column="project_name" property="projectName" jdbcType="VARCHAR" />
		<result column="registration_code" property="registrationCode" jdbcType="VARCHAR" />
        <result column="use_company_code" property="useCompanyCode" jdbcType="VARCHAR" />
		<result column="device_position" property="devicePosition" jdbcType="VARCHAR" />
        <result column="worker_name" property="workerName" jdbcType="VARCHAR" />
        <result column="director_name" property="directorName" jdbcType="VARCHAR" />
	</resultMap>

    <select id="infoById" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest">
        SELECT
            t.*,
            ui.name AS worker_name
        FROM
            (SELECT
                ai.*,
                re.area_name,
                pr.id AS project_id,
                pr.project_name,
                li.registration_code,
                li.use_company_code,
                li.device_position,
                ui.`name` AS director_name,
                plr.worker_id
            FROM
                annual_inspection ai
            LEFT JOIN project_lift_relevance plr ON ai.lift_id = plr.lift_id AND ai.mt_company_id = plr.mt_company_id
            LEFT JOIN lift li ON ai.lift_id = li.id
            LEFT JOIN project pr ON pr.id = plr.project_id
            LEFT JOIN region re ON pr.region_id = re.id
            LEFT JOIN user_info ui ON ui.user_id = re.user_id
            WHERE ai.id = #{cond.id}
            <if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
                AND ai.mt_company_id = #{cond.mtCompanyId}
            </if>
            ) t
        LEFT JOIN user_info ui ON ui.user_id = t.worker_id
    </select>

    <!--根据状态查询总条数-->
    <select id="countByCondition" resultType="java.lang.Long" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest" >
        SELECT
            count(*)
        FROM
            annual_inspection ai
        <where>
            <if test="cond.status != null">
                AND ai.status = #{cond.status}
            </if>
            <if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
                AND ai.mt_company_id = #{cond.mtCompanyId}
            </if>
        </where>
    </select>

    <!--多条件分页查询年检记录-->
	<select id="pageByCondition" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest" >
        SELECT
            t.*,
            ui.name AS worker_name
        FROM
            (SELECT DISTINCT
                ai.*,
                re.area_name,
                pr.id AS project_id,
                pr.project_name,
                li.registration_code,
                li.use_company_code,
                li.device_position,
                ui.`name` AS director_name,
                plr.worker_id
            FROM
                annual_inspection ai
            LEFT JOIN platform_company_lift_relevance pclr
            ON ai.mt_company_id = pclr.mt_company_id AND ai.lift_id = pclr.lift_id AND pclr.lift_company_status != '1'
            LEFT JOIN project_lift_relevance plr ON ai.lift_id = plr.lift_id AND ai.mt_company_id = plr.mt_company_id
                      AND plr.delete_flag = '0'
            LEFT JOIN project_user pu ON pu.project_id = plr.project_id AND pu.mt_company_id = ai.mt_company_id
            LEFT JOIN lift li ON ai.lift_id = li.id
            LEFT JOIN project pr ON pr.id = plr.project_id
            LEFT JOIN region re ON pr.region_id = re.id
            LEFT JOIN user_info ui ON ui.user_id = re.user_id
            WHERE 1=1
                <if test="cond.status != null">
                    AND ai.status = #{cond.status}
                </if>
                <if test="cond.stepName != null">
                    AND ai.step_name = #{cond.stepName}
                </if>
                <if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
                    AND ai.mt_company_id = #{cond.mtCompanyId}
                </if>
                <if test="cond.info != null and cond.info != ''">
                    AND li.registration_code LIKE concat('%',#{cond.info},'%')
                </if>
                <if test="cond.regionId != null and cond.regionId > 0">
                    AND re.id = #{cond.regionId}
                </if>
                <if test="cond.projectId != null and cond.projectId > 0">
                    AND pr.id = #{cond.projectId}
                </if>
                <if test="cond.workerId !=null ">
                    <choose>
                        <when test="cond.currentRoleCode == 'REGION_DIRECTOR' or cond.currentRoleCode == 'CLERK' ">
                            AND pu.user_id = #{cond.userId,jdbcType=BIGINT}
                        </when>
                        <when test="cond.currentRoleCode == 'ROLEMTWORK' ">
                            AND plr.worker_id = #{cond.workerId}
                        </when>
                        <otherwise/>
                    </choose>
                </if>
            ) t
        LEFT JOIN user_info ui ON ui.user_id = t.worker_id
        ORDER BY plan_date,finish_time
	</select>

    <select id="pageByLift" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest" >
        SELECT
            ai.*,
            re.area_name,
            pr.id AS project_id,
            pr.project_name,
            li.registration_code,
            li.use_company_code,
            li.device_position,
            plr.worker_id
        FROM
            annual_inspection ai
        LEFT JOIN project_lift_relevance plr ON ai.lift_id = plr.lift_id AND ai.mt_company_id = plr.mt_company_id
        LEFT JOIN lift li ON ai.lift_id = li.id
        LEFT JOIN project pr ON pr.id = plr.project_id
        LEFT JOIN region re ON pr.region_id = re.id
        <where>
            <if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
                AND ai.mt_company_id = #{cond.mtCompanyId}
            </if>
            <if test="cond.liftId != null and cond.liftId > 0">
                AND ai.lift_id = #{cond.liftId}
            </if>
            <if test="cond.projectId != null and cond.projectId > 0">
                AND pr.id = #{cond.projectId}
            </if>
        </where>
    </select>

    <select id="listByIdList" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest" >
        SELECT
            t.*,
            ui.name AS worker_name
        FROM
        (SELECT
            ai.*,
            re.area_name,
            pr.id AS project_id,
            pr.project_name,
            li.registration_code,
            li.use_company_code,
            li.device_position,
            ui.`name` AS director_name,
            plr.worker_id
        FROM
            annual_inspection ai
        LEFT JOIN project_lift_relevance plr ON ai.lift_id = plr.lift_id AND ai.mt_company_id = plr.mt_company_id
        LEFT JOIN lift li ON ai.lift_id = li.id
        LEFT JOIN project pr ON pr.id = plr.project_id
        LEFT JOIN region re ON pr.region_id = re.id
        LEFT JOIN user_info ui ON ui.user_id = re.user_id
        <where>
            <if test="cond.ids != null and cond.ids.size > 0">
                AND ai.id IN
                <foreach collection="cond.ids" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        ) t
        LEFT JOIN user_info ui ON ui.user_id = t.worker_id
    </select>

    <!--多条件查询年检记录-->
    <select id="pageByUser" resultMap="BaseResultMap" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest" >
        SELECT
            t.*,
            ui.name AS worker_name
        FROM
            (SELECT
                ai.*,
                re.area_name,
                pr.id AS project_id,
                pr.project_name,
                li.registration_code,
                li.use_company_code,
                li.device_position,
                ui.`name` AS director_name,
                plr.worker_id
            FROM
                annual_inspection ai
            LEFT JOIN project_lift_relevance plr ON ai.lift_id = plr.lift_id AND ai.mt_company_id = plr.mt_company_id
            LEFT JOIN project_user pu ON pu.project_id = plr.project_id AND pu.mt_company_id = ai.mt_company_id
            LEFT JOIN lift li ON ai.lift_id = li.id
            LEFT JOIN project pr ON pr.id = plr.project_id
            LEFT JOIN region re ON pr.region_id = re.id
            LEFT JOIN user_info ui ON ui.user_id = re.user_id
            <where>
                <if test="cond.status != null">
                    AND ai.status = #{cond.status}
                </if>
                <if test="cond.mtCompanyId != null and cond.mtCompanyId > 0">
                    AND ai.mt_company_id = #{cond.mtCompanyId}
                </if>
                <if test="cond.userId != null and cond.userId > 0">
                    AND pu.user_id = #{cond.userId}
                </if>
            </where>
            ) t
        LEFT JOIN user_info ui ON ui.user_id = t.worker_id
    </select>

    <select id="countDoingByUser" resultType="java.lang.Long" parameterType="cn.com.ty.lift.business.common.CommonRequest">
        SELECT
            count(1)
        FROM
            annual_inspection ai
        LEFT JOIN platform_company_lift_relevance pc
                ON ai.lift_id = pc.lift_id AND pc.lift_company_status !=1 AND ai.mt_company_id = pc.mt_company_id
        <if test="cond.currentRoleCode == 'REGION_DIRECTOR' or cond.currentRoleCode == 'CLERK' or cond.currentRoleCode == 'ROLEMTWORK' ">
            LEFT JOIN project_lift_relevance plr ON plr.mt_company_id = pc.mt_company_id AND plr.lift_id = ai.lift_id AND plr.delete_flag = '0'
        </if>
        <if test="cond.currentRoleCode == 'REGION_DIRECTOR' or cond.currentRoleCode == 'CLERK' ">
            LEFT JOIN project_user pu ON plr.project_id = pu.project_id AND pu.mt_company_id = plr.mt_company_id
        </if>
        WHERE
            ai.`status` = 0
            AND ai.mt_company_id = #{cond.mtCompanyId}
        <choose>
            <when test="cond.currentRoleCode == 'REGION_DIRECTOR' or cond.currentRoleCode == 'CLERK' ">
                AND pu.user_id = #{cond.userId,jdbcType=BIGINT}
            </when>
            <when test="cond.currentRoleCode == 'ROLEMTWORK' ">
                AND plr.worker_id = #{cond.userId,jdbcType=BIGINT}
            </when>
            <otherwise/>
        </choose>
    </select>

    <!--app统计年检列表待确认总数 -->
    <select id="countInspectionConfirmByUserId" resultType="java.lang.Integer"
            parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest">
        SELECT COUNT(DISTINCT li.id) toConfirm
        FROM lift li
        LEFT JOIN project_lift_relevance plr ON li.id = plr.lift_id AND plr.delete_flag = '0'
        LEFT JOIN project_user pu ON pu.project_id = plr.project_id
        LEFT JOIN annual_inspection ai ON ai.lift_id = li.id AND ai.plan_date = li.annual_inspection_date
        AND ai.mt_company_id = plr.mt_company_id
        WHERE <![CDATA[li.annual_inspection_date <= date_add(curdate(), INTERVAL 45 DAY)]]>
        AND <![CDATA[li.annual_inspection_date >= curdate() AND isnull(ai.id)]]>
        AND plr.mt_company_id = #{request.mtCompanyId}
        <choose>
            <when test="request.currentRoleCode == 'REGION_DIRECTOR' or request.currentRoleCode == 'CLERK' ">
                AND pu.user_id = #{request.userId,jdbcType=BIGINT}
            </when>
            <when test="request.currentRoleCode == 'ROLEMTWORK' ">
                AND plr.worker_id = #{request.userId,jdbcType=BIGINT}
            </when>
            <otherwise/>
        </choose>
    </select>

    <!--app统计年检列表执行中和已完成总数 -->
    <select id="countInspectionByUserId" parameterType="cn.com.ty.lift.business.annualinspection.dto.InspectionRequest"
            resultType="cn.com.ty.lift.business.annualinspection.dto.InspectionCount">
        SELECT MAX(CASE t.status WHEN '0' THEN t.total ELSE 0 END) processing,
               MAX(CASE t.status WHEN '1' THEN t.total ELSE 0 END) complete
        FROM (
             SELECT count(1) total,
                    status
             FROM annual_inspection ai
                      LEFT JOIN platform_company_lift_relevance pc
                                ON ai.lift_id = pc.lift_id AND pc.lift_company_status !=1 AND ai.mt_company_id = pc.mt_company_id
             <if test="request.currentRoleCode == 'REGION_DIRECTOR' or request.currentRoleCode == 'CLERK' or request.currentRoleCode == 'ROLEMTWORK' ">
                 LEFT JOIN project_lift_relevance plr ON plr.mt_company_id = pc.mt_company_id AND plr.lift_id = ai.lift_id AND plr.delete_flag = '0'
             </if>
             <if test="request.currentRoleCode == 'REGION_DIRECTOR' or request.currentRoleCode == 'CLERK' ">
                 LEFT JOIN project_user pu ON plr.project_id = pu.project_id AND pu.mt_company_id = plr.mt_company_id
             </if>
             WHERE ai.mt_company_id = #{request.mtCompanyId}
            <choose>
                <when test="request.currentRoleCode == 'REGION_DIRECTOR' or request.currentRoleCode == 'CLERK' ">
                    AND pu.user_id = #{request.userId,jdbcType=BIGINT}
                </when>
                <when test="request.currentRoleCode == 'ROLEMTWORK' ">
                    AND plr.worker_id = #{request.userId,jdbcType=BIGINT}
                </when>
                <otherwise/>
            </choose>
     GROUP BY status) t
    </select>
</mapper>