<?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.qmth.sop.business.mapper.TBDeviceDeliveryMapper">

    <select id="page" resultType="com.qmth.sop.business.bean.result.TBDeviceDeliveryResult">
        SELECT
            tbdd.id,
            tbs.name AS serviceName,
            tbdd.crm_no AS crmNo,
            tbdd.usage_type AS usageType,
            tbdd.delivery_type AS deliveryType,
            ss.name AS supplierName,
            sd.device_code AS deviceNo,
            sd.serial_no AS serialNo,
            sd.brand,
            sd.model,
            tbdd.mailing_address AS mailingAddress,
            tbdd.consignee,
            tbdd.consignee_phone AS consigneePhone,
            tbdd.express_no AS expressNo,
            tbdd.express_photo_path AS expressPhotoPath,
            tbdd.status,
            tbdd.deliver_time AS deliverTime,
            su.real_name AS deliverUserName,
            tbdd.receive_time AS receiveTime,
            tbdd.effect
        FROM
            t_b_device_delivery tbdd
        LEFT JOIN
            t_b_crm tbc ON tbc.crm_no = tbdd.crm_no
        LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
        LEFT JOIN
            sys_supplier ss ON ss.id = tbdd.supplier_id
        LEFT JOIN
            sys_device sd ON sd.serial_no = tbdd.serial_no
        LEFT JOIN
            sys_user su ON su.id = tbdd.deliver_user_id
        <where>
            AND tbdd.enable = 1 AND tbdd.status != 'CANCEL'
            <if test="serviceId != null and serviceId != ''">
                AND tbs.id = #{serviceId}
            </if>
            <if test="usageType != null">
                AND tbdd.usage_type = #{usageType}
            </if>
            <if test="crmNo != null and crmNo != ''">
                AND tbdd.crm_no LIKE CONCAT('%',#{crmNo},'%')
            </if>
            <if test="status != null">
                AND tbdd.status = #{status}
            </if>
            <if test="deliverUserId != null">
                AND tbdd.deliver_user_id = #{deliverUserId}
            </if>
            <if test="deliverStartTime != null">
                AND tbdd.deliver_time <![CDATA[ >= ]]> #{deliverStartTime}
            </if>
            <if test="deliverEndTime != null">
                AND tbdd.deliver_time <![CDATA[ <= ]]> #{deliverEndTime}
            </if>
            <if test="serialNo != null and serialNo != ''">
                AND tbdd.serial_no LIKE CONCAT('%',#{serialNo},'%')
            </if>
            <if test="deviceNo != null and deviceNo != ''">
                AND sd.device_code LIKE CONCAT('%',#{deviceNo},'%')
            </if>
            <if test="supplierId != null">
                AND tbdd.supplier_id = #{supplierId}
            </if>
            <if test="receiveStartTime != null and receiveStartTime != ''">
                AND tbdd.receive_time <![CDATA[ >= ]]> #{receiveStartTime}
            </if>
            <if test="receiveEndTime != null and receiveEndTime != ''">
                AND tbdd.receive_time <![CDATA[ <= ]]> #{receiveEndTime}
            </if>
            <if test="consignee != null and consignee != ''">
                AND tbdd.consignee LIKE CONCAT('%',#{consignee},'%')
            </if>
            <if test="mailingAddress != null and mailingAddress != ''">
                AND tbdd.mailing_address LIKE CONCAT('%',#{mailingAddress},'%')
            </if>
            <if test="deliveryType != null">
                AND tbdd.delivery_type = #{deliveryType}
            </if>
            <if test="brand != null and brand != ''">
                AND sd.brand LIKE CONCAT('%',#{brand},'%')
            </if>
            <if test="model != null and model != ''">
                AND sd.model LIKE CONCAT('%',#{model},'%')
            </if>
            <if test="expressNo != null and expressNo != ''">
                AND tbdd.express_no LIKE CONCAT('%',#{expressNo},'%')
            </if>
        </where>
        ORDER BY tbdd.update_time DESC
    </select>

    <select id="list" resultType="com.qmth.sop.business.bean.result.TBDeviceDeliveryResult">
        SELECT
            tbdd.id,
            IFNULL(tbs.name,'--') AS serviceName,
            IFNULL(tbdd.crm_no, '--') AS crmNo,
            tbdd.usage_type AS usageType,
            tbdd.delivery_type AS deliveryType,
            ss.name AS supplierName,
            sd.device_code AS deviceNo,
            sd.serial_no AS serialNo,
            sd.brand,
            sd.model,
            tbdd.mailing_address AS mailingAddress,
            tbdd.consignee,
            tbdd.consignee_phone AS consigneePhone,
            tbdd.express_no AS expressNo,
            tbdd.express_photo_path AS expressPhotoPath,
            tbdd.status,
            tbdd.deliver_time AS deliverTime,
            su.real_name AS deliverUserName,
            tbdd.receive_time AS receiveTime,
            tbdd.effect
        FROM
            t_b_device_delivery tbdd
        LEFT JOIN
            t_b_crm tbc ON tbc.crm_no = tbdd.crm_no
        LEFT JOIN
            t_b_service tbs ON tbc.service_id = tbs.id
        LEFT JOIN
            sys_supplier ss ON ss.id = tbdd.supplier_id
        LEFT JOIN
            sys_device sd ON sd.serial_no = tbdd.serial_no
        LEFT JOIN
            sys_user su ON su.id = tbdd.deliver_user_id
        <where>
            AND tbdd.enable = 1 AND tbdd.status != 'CANCEL'
            <if test="serviceId != null and serviceId != ''">
                AND tbs.id = #{serviceId}
            </if>
            <if test="usageType != null">
                AND tbdd.usage_type = #{usageType}
            </if>
            <if test="crmNo != null and crmNo != ''">
                AND tbdd.crm_no LIKE CONCAT('%',#{crmNo},'%')
            </if>
            <if test="status != null">
                AND tbdd.status = #{status}
            </if>
            <if test="deliverUserId != null">
                AND tbdd.deliver_user_id = #{deliverUserId}
            </if>
            <if test="deliverStartTime != null">
                AND tbdd.deliver_time <![CDATA[ >= ]]> #{deliverStartTime}
            </if>
            <if test="deliverEndTime != null">
                AND tbdd.deliver_time <![CDATA[ <= ]]> #{deliverEndTime}
            </if>
            <if test="serialNo != null and serialNo != ''">
                AND tbdd.serial_no LIKE CONCAT('%',#{serialNo},'%')
            </if>
            <if test="deviceNo != null and deviceNo != ''">
                AND sd.device_code LIKE CONCAT('%',#{deviceNo},'%')
            </if>
            <if test="supplierId != null">
                AND tbdd.supplier_id = #{supplierId}
            </if>
            <if test="receiveStartTime != null and receiveStartTime != ''">
                AND tbdd.receive_time <![CDATA[ >= ]]> #{receiveStartTime}
            </if>
            <if test="receiveEndTime != null and receiveEndTime != ''">
                AND tbdd.receive_time <![CDATA[ <= ]]> #{receiveEndTime}
            </if>
            <if test="consignee != null and consignee != ''">
                AND tbdd.consignee LIKE CONCAT('%',#{consignee},'%')
            </if>
            <if test="mailingAddress != null and mailingAddress != ''">
                AND tbdd.mailing_address LIKE CONCAT('%',#{mailingAddress},'%')
            </if>
            <if test="deliveryType != null">
                AND tbdd.delivery_type = #{deliveryType}
            </if>
            <if test="brand != null and brand != ''">
                AND sd.brand LIKE CONCAT('%',#{brand},'%')
            </if>
            <if test="model != null and model != ''">
                AND sd.model LIKE CONCAT('%',#{model},'%')
            </if>
            <if test="expressNo != null and expressNo != ''">
                AND tbdd.express_no LIKE CONCAT('%',#{expressNo},'%')
            </if>
        </where>
        ORDER BY tbdd.update_time DESC
    </select>

    <select id="listDeviceSign" resultType="com.qmth.sop.business.bean.result.DeviceSignResult">
        SELECT
            d.id,
            d.crm_no crmNo,
            d.serial_no serialNo,
            se.model,
            se.status deviceStatus,
            sp.NAME supplierName,
            d.express_no expressNo,
            d.STATUS
        FROM
            t_b_device_delivery d,
            sys_device se,
            sys_supplier sp
        WHERE
            d.serial_no = se.serial_no
          AND d.supplier_id = sp.id
          AND d.crm_no = #{crmNo}
          AND d.delivery_type = #{deliveryType}
          AND d.status in
        <foreach collection="statusList" item="status" open="(" separator="," close=")">
            #{status}
        </foreach>
        ORDER BY
            d.create_time DESC
    </select>

    <select id="listDevicePlace" resultType="com.qmth.sop.business.bean.result.DevicePlaceResult">
        SELECT
            d.id,
            d.serial_no serialNo,
            sd.model,
            sd.STATUS deviceStatus,
            sp.NAME supplierName,
            d.STATUS,
            d.remark,
            d.mail_type transferMode
        FROM
            t_b_device_delivery d,
            sys_device sd,
            sys_supplier sp
        WHERE
            d.serial_no = sd.serial_no
          AND d.supplier_id = sp.id
          AND d.crm_no = #{crmNo}
          AND d.delivery_type=#{deliveryType}
          AND d.status in
        <foreach collection="statusList" item="status" open="(" separator="," close=")">
            #{status}
        </foreach>
        ORDER BY
           d.create_time DESC
    </select>
</mapper>
