<?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="com.zehong.system.mapper.TWorkOrderMapper">
    
    <resultMap type="TWorkOrder" id="TWorkOrderResult">
        <result property="workId"    column="work_id"    />
        <result property="workTitle"    column="work_title"    />
        <result property="workType"    column="work_type"    />
        <result property="workContent"    column="work_content"    />
        <result property="workCreateEnterpriseName"    column="work_create_enterprise_name"    />
        <result property="workCreateEnterpriseId"    column="work_create_enterprise_id"    />
        <result property="workAssignEnterproseName"    column="work_assign_enterprose_name"    />
        <result property="workAssignEnterproseId"    column="work_assign_enterprose_id"    />
        <result property="workAssignManId"    column="work_assign_man_id"    />
        <result property="workAssignMan"    column="work_assign_man"    />
        <result property="workStatus"    column="work_status"    />
        <result property="createType"    column="create_type"    />
        <result property="inspectionDate"    column="inspection_date"    />
        <result property="inspectionRoute"    column="inspection_route"    />
        <result property="problemDescription"    column="problem_description"    />
        <result property="iconUrl"    column="icon_url"    />
        <result property="rectificationPlan"    column="rectification_plan"    />
        <result property="rectificationResult"    column="rectification_result"    />
        <result property="responsibleUnit"    column="responsible_unit"    />
        <result property="responsiblePerson"    column="responsible_person"    />
        <result property="expiryDate"    column="expiry_date"    />
        <result property="createBy"    column="create_by"    />
        <result property="jiedanTime" column="jiedan_time"/>
        <result property="shangbaoTime" column="shangbao_time"/>
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="isDel"    column="is_del"    />
        <result property="remarks"    column="remarks"    />
    </resultMap>

    <sql id="selectTWorkOrderVo">
        SELECT
            workOrder.work_id,workOrder.create_type,
            workOrder.work_title,
            workOrder.work_type,
            workOrder.work_content,
            workOrder.work_create_enterprise_name,
            workOrder.work_create_enterprise_id,
            info.enterprise_name AS work_assign_enterprose_name,
            workOrder.work_assign_enterprose_id,
            workOrder.work_assign_man_id,
            usr.nick_name AS work_assign_man,
            workOrder.work_status,
            workOrder.inspection_date,
            workOrder.inspection_route,
            workOrder.problem_description,
            workOrder.icon_url,
            workOrder.rectification_plan,
            workOrder.rectification_result,
            workOrder.responsible_unit,
            workOrder.responsible_person,
            workOrder.expiry_date,
            workOrder.create_by,
            workOrder.create_time,
            workOrder.update_by,
            workOrder.update_time,
            workOrder.is_del,
            workOrder.remarks,
            workOrder.shangbao_time,
            workOrder.jiedan_time
        FROM
            t_work_order workOrder
        LEFT JOIN sys_user usr ON usr.user_id = workOrder.work_assign_man_id
        LEFT JOIN t_enterprise_info info ON info.enterprise_id = workOrder.work_assign_enterprose_id

    </sql>

    <select id="selectTWorkOrderList" parameterType="TWorkOrder" resultMap="TWorkOrderResult">
        <include refid="selectTWorkOrderVo"/>
        <where>
            workOrder.is_del = '0'
            <if test="workTitle != null  and workTitle != ''"> and workOrder.work_title like concat('%', #{workTitle}, '%')</if>
            <if test="workType != null  and workType != ''"> and workOrder.work_type = #{workType}</if>
            <if test="workContent != null  and workContent != ''"> and workOrder.work_content = #{workContent}</if>
            <if test="workCreateEnterpriseName != null  and workCreateEnterpriseName != ''"> and workOrder.work_create_enterprise_name like concat('%', #{workCreateEnterpriseName}, '%')</if>
            <if test="workCreateEnterpriseId != null  and workCreateEnterpriseId != ''"> and workOrder.work_create_enterprise_id = #{workCreateEnterpriseId}</if>
            <if test="workAssignEnterproseName != null  and workAssignEnterproseName != ''"> and info.work_assign_enterprose_name like concat('%', #{workAssignEnterproseName}, '%')</if>
            <if test="workAssignEnterproseId != null "> and workOrder.work_assign_enterprose_id = #{workAssignEnterproseId}</if>
            <if test="workAssignManId != null "> and workOrder.work_assign_man_id = #{workAssignManId}</if>
            <if test="workAssignMan != null  and workAssignMan != ''"> and user.work_assign_man = #{workAssignMan}</if>
            <if test="workStatus != null  and workStatus != ''"> and workOrder.work_status = #{workStatus}</if>
            <if test="inspectionDate != null "> and workOrder.inspection_date = #{inspectionDate}</if>
            <if test="inspectionRoute != null  and inspectionRoute != ''"> and workOrder.inspection_route = #{inspectionRoute}</if>
            <if test="problemDescription != null  and problemDescription != ''"> and workOrder.problem_description = #{problemDescription}</if>
            <if test="iconUrl != null  and iconUrl != ''"> and workOrder.icon_url = #{iconUrl}</if>
            <if test="rectificationPlan != null  and rectificationPlan != ''"> and workOrder.rectification_plan = #{rectificationPlan}</if>
            <if test="rectificationResult != null  and rectificationResult != ''"> and workOrder.rectification_result = #{rectificationResult}</if>
            <if test="responsibleUnit != null  and responsibleUnit != ''"> and workOrder.responsible_unit = #{responsibleUnit}</if>
            <if test="responsiblePerson != null  and responsiblePerson != ''"> and workOrder.responsible_person = #{responsiblePerson}</if>
            <if test="expiryDate != null "> and workOrder.expiry_date = #{expiryDate}</if>
            <if test="isDel != null  and isDel != ''"> and workOrder.is_del = #{isDel}</if>
            <if test="createType!=null"> and workOrder.create_type = #{createType}</if>
            <if test="remarks != null  and remarks != ''"> and workOrder.remarks = #{remarks}</if>
        </where>
        ORDER BY workOrder.create_time DESC
    </select>
    <select id="selectTuserList" parameterType="TWorkOrder" resultType="com.zehong.system.domain.TUserLocation" >
        SELECT * from t_user_location
        WHERE user_id = #{workAssignManId}
        and create_time &gt; #{jiedanTime}
        and create_time &lt;#{shangbaoTime}
    </select>
    <select id="selectTWorkOrderListCount" parameterType="TWorkOrder" resultType="java.util.HashMap">
        SELECT
        <if test="timeType==null or timeType==1">
            DATE_FORMAT(create_time, "%Y-%m-%d") AS `timeType`,
        </if>
        <if test="timeType==2">
            DATE_FORMAT(create_time, "%Y-%u") AS `timeType`,
        </if>
        <if test="timeType==3">
            DATE_FORMAT(create_time, "%Y-%m") AS `timeType`,
        </if>
        <if test="timeType==4">
            DATE_FORMAT(create_time, "%Y") AS `timeType`,
        </if>
        COUNT(*) AS countNum

        FROM t_work_order workOrder

        <where>
            workOrder.is_del = '0'
            <if test="workTitle != null  and workTitle != ''"> and workOrder.work_title like concat('%', #{workTitle}, '%')</if>
            <if test="workType != null  and workType != ''"> and workOrder.work_type = #{workType}</if>
            <if test="expiryDate != null "> and workOrder.expiry_date = #{expiryDate}</if>
            <if test="createType!=null"> and workOrder.create_type = #{createType}</if>
            <if test="workAssignEnterproseName != null  and workAssignEnterproseName != ''"> and info.work_assign_enterprose_name like concat('%', #{workAssignEnterproseName}, '%')</if>
            <if test="workAssignEnterproseId != null "> and workOrder.work_assign_enterprose_id = #{workAssignEnterproseId}</if>
            <if test="workAssignManId != null "> and workOrder.work_assign_man_id = #{workAssignManId}</if>
            <if test="workAssignMan != null  and workAssignMan != ''"> and user.work_assign_man = #{workAssignMan}</if>
        </where>
        GROUP BY `timeType`;
    </select>
    <select id="selectTWorkOrderById" parameterType="Long" resultMap="TWorkOrderResult">
        <include refid="selectTWorkOrderVo"/>
        where workOrder.work_id = #{workId}
    </select>

    <!--<select id="countTWorkOrder" resultType="HomepageVo">
        select COUNT(t.work_id) AS taskTotal,
			   SUM(CASE t.work_status WHEN '2' THEN 1 ELSE 0 END) AS taskFeedbackTotal
        from (select * from t_work_order where is_del = '0') t
    </select>-->
    <select id="countTWorkOrder" resultType="HomepageVo">
        SELECT
                IFNULL(SUM(CASE task_type WHEN '1' THEN 1 ELSE 0 END),0) AS taskTotal,
                IFNULL(SUM(CASE task_type WHEN '2' THEN 1 ELSE 0 END),0) AS taskFeedbackTotal
        FROM
            t_work_task
        WHERE is_del = '0' and is_report = '1'
    </select>
    <insert id="insertTWorkOrder" parameterType="TWorkOrder" useGeneratedKeys="true" keyProperty="workId">
        insert into t_work_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="workTitle != null">work_title,</if>
            <if test="workType != null">work_type,</if>
            <if test="workContent != null">work_content,</if>
            <if test="workCreateEnterpriseName != null">work_create_enterprise_name,</if>
            <if test="workCreateEnterpriseId != null">work_create_enterprise_id,</if>
            <if test="workAssignEnterproseName != null">work_assign_enterprose_name,</if>
            <if test="workAssignEnterproseId != null">work_assign_enterprose_id,</if>
            <if test="workAssignManId != null">work_assign_man_id,</if>
            <if test="workAssignMan != null">work_assign_man,</if>
            <if test="workStatus != null">work_status,</if>
            <if test="inspectionDate != null">inspection_date,</if>
            <if test="inspectionRoute != null">inspection_route,</if>
            <if test="problemDescription != null">problem_description,</if>
            <if test="iconUrl != null">icon_url,</if>
            <if test="rectificationPlan != null">rectification_plan,</if>
            <if test="rectificationResult != null">rectification_result,</if>
            <if test="responsibleUnit != null">responsible_unit,</if>
            <if test="responsiblePerson != null">responsible_person,</if>
            <if test="expiryDate != null">expiry_date,</if>
            <if test="jiedanTime != null">jiedan_time,</if>
            <if test="shangbaoTime != null">shangbao_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="isDel != null">is_del,</if>
            <if test="remarks != null">remarks,</if>
            <if test="createType != null">create_type,</if>

         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="workTitle != null">#{workTitle},</if>
            <if test="workType != null">#{workType},</if>
            <if test="workContent != null">#{workContent},</if>
            <if test="workCreateEnterpriseName != null">#{workCreateEnterpriseName},</if>
            <if test="workCreateEnterpriseId != null">#{workCreateEnterpriseId},</if>
            <if test="workAssignEnterproseName != null">#{workAssignEnterproseName},</if>
            <if test="workAssignEnterproseId != null">#{workAssignEnterproseId},</if>
            <if test="workAssignManId != null">#{workAssignManId},</if>
            <if test="workAssignMan != null">#{workAssignMan},</if>
            <if test="workStatus != null">#{workStatus},</if>
            <if test="inspectionDate != null">#{inspectionDate},</if>
            <if test="inspectionRoute != null">#{inspectionRoute},</if>
            <if test="problemDescription != null">#{problemDescription},</if>
            <if test="iconUrl != null">#{iconUrl},</if>
            <if test="rectificationPlan != null">#{rectificationPlan},</if>
            <if test="rectificationResult != null">#{rectificationResult},</if>
            <if test="responsibleUnit != null">#{responsibleUnit},</if>
            <if test="responsiblePerson != null">#{responsiblePerson},</if>
            <if test="expiryDate != null">#{expiryDate},</if>
            <if test="jiedanTime != null">#{jiedanTime},</if>
            <if test="shangbaoTime != null">#{shangbaoTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="isDel != null">#{isDel},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="createType != null">#{createType},</if>
         </trim>
    </insert>
    <insert id="insertTUserLocationList">
        INSERT INTO t_user_location (user_id,longitude,latitude,create_time)
        VALUES
        <foreach collection="list" index="index" separator="," item="item">
            (#{item.userId},
            #{item.longitude},
            #{item.latitude},
            #{item.createTime})
        </foreach>
    </insert>
    <update id="updateTWorkOrder" parameterType="TWorkOrder">
        update t_work_order
        <trim prefix="SET" suffixOverrides=",">
            <if test="workTitle != null">work_title = #{workTitle},</if>
            <if test="workType != null">work_type = #{workType},</if>
            <if test="workContent != null">work_content = #{workContent},</if>
            <if test="workCreateEnterpriseName != null">work_create_enterprise_name = #{workCreateEnterpriseName},</if>
            <if test="workCreateEnterpriseId != null">work_create_enterprise_id = #{workCreateEnterpriseId},</if>
            <if test="workAssignEnterproseName != null">work_assign_enterprose_name = #{workAssignEnterproseName},</if>
            <if test="workAssignEnterproseId != null">work_assign_enterprose_id = #{workAssignEnterproseId},</if>
            <if test="workAssignManId == null and workAssignMan == ''">work_assign_man_id = null,</if>
            <if test="workAssignManId != null">work_assign_man_id = #{workAssignManId},</if>
            <if test="workAssignMan != null">work_assign_man = #{workAssignMan},</if>
            <if test="workStatus != null">work_status = #{workStatus},</if>
            <if test="inspectionDate != null">inspection_date = #{inspectionDate},</if>
            <if test="inspectionRoute != null">inspection_route = #{inspectionRoute},</if>
            <if test="problemDescription != null">problem_description = #{problemDescription},</if>
            <if test="iconUrl != null">icon_url = #{iconUrl},</if>
            <if test="rectificationPlan != null">rectification_plan = #{rectificationPlan},</if>
            <if test="rectificationResult != null">rectification_result = #{rectificationResult},</if>
            <if test="responsibleUnit != null">responsible_unit = #{responsibleUnit},</if>
            <if test="responsiblePerson != null">responsible_person = #{responsiblePerson},</if>
            <if test="expiryDate != null">expiry_date = #{expiryDate},</if>
            <if test="jiedanTime != null">jiedan_time = #{jiedanTime},</if>
            <if test="shangbaoTime != null">shangbao_time = #{shangbaoTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDel != null">is_del = #{isDel},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="createType != null">create_type = #{createType},</if>
        </trim>
        where work_id = #{workId}
    </update>

    <delete id="deleteTWorkOrderById" parameterType="Long">
        delete from t_work_order where work_id = #{workId}
    </delete>

    <delete id="deleteTWorkOrderByIds" parameterType="String">
        delete from t_work_order where work_id in 
        <foreach item="workId" collection="array" open="(" separator="," close=")">
            #{workId}
        </foreach>
    </delete>
    <!--<select id="selectWorkOrderNum" resultType="java.util.HashMap">
        SELECT  IFNULL(SUM(IF(work_status=3,1,0)),0) AS typeOne,
        IFNULL(SUM(IF(work_status!=3,1,0)),0) AS typeTwo,
        (SELECT COUNT(employed_people_id)  FROM t_employed_people_info WHERE is_del = 0
            AND beyond_enterprise_id IN
            <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                #{enterpriseId}
            </foreach>
        ) AS peopleNum,
        (SELECT CAST(SUM(pipe_length) AS  DECIMAL(20,0))  FROM t_pipe_info WHERE  is_del = 0
                AND beyond_enterprise_id IN
                <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                    #{enterpriseId}
                </foreach>
        )AS allPipeLength
        FROM t_work_order WHERE  is_del = 0
            AND work_assign_enterprose_id IN
            <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                #{enterpriseId}
            </foreach>
    </select>-->

    <select id="selectWorkOrderNum" resultType="java.util.HashMap">
        SELECT
            IFNULL(SUM(CASE work_status WHEN 3 THEN 1 ELSE 0 END),0) AS typeOne,
            IFNULL(SUM(CASE WHEN work_status &lt; 3 THEN 1 ELSE 0 END),0) AS typeTwo,
            (
                SELECT COUNT(employed_people_id)  FROM t_employed_people_info WHERE is_del = 0
                AND beyond_enterprise_id IN
                <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                    #{enterpriseId}
                </foreach>
            ) AS peopleNum,
            (
                SELECT CAST(SUM(pipe_length) AS  DECIMAL(20,0))  FROM t_pipe_info WHERE  is_del = 0
                AND beyond_enterprise_id IN
                <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                    #{enterpriseId}
                </foreach>
            )AS allPipeLength,
            (
                select count(1) from t_hidden_danger_standing_book where remediation = '1' and is_del = '0'
                AND beyondEnterprise_id IN
                <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                    #{enterpriseId}
                </foreach>
            )AS hiddenBookFinish,
            (
                select count(1) from t_hidden_danger_standing_book where remediation = '2' and is_del = '0'
                AND beyondEnterprise_id IN
                <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
                    #{enterpriseId}
                </foreach>
            )AS hiddenBookUnFinish
        FROM t_work_order WHERE  is_del = 0
        AND work_assign_enterprose_id IN
        <foreach item="enterpriseId" collection="enterpriseIds" open="(" separator="," close=")">
            #{enterpriseId}
        </foreach>
    </select>
</mapper>