<?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.TBDeviceInOutMapper">

    <select id="findDeviceInOutPageBySop" resultType="com.qmth.sop.business.bean.result.DeviceInOutResult">
        SELECT
            tbdio.id AS inOutId,
            tbdio.service_id AS serviceUnitId,
            IFNULL(tbs.name,'--') AS serviceUnitName,
            tbdio.usage_type AS usageType,
            sd.device_code AS deviceNo,
            sd.id AS deviceId,
            sd.brand AS deviceBrand,
            sd.model AS deviceModel,
            tbdio.status AS deviceStatus,
            tbdio.in_out_time AS inOutTime,
            su.id AS userId,
            su.real_name AS userName,
            IFNULL(tbdio.custom_name,'--') AS customName,
            tbdio.type AS inOutType,
            tbdio.location AS location,
            tbdio.address AS address,
            tbdio.address_arr AS addressArrTemp,
            tbdio.serial_no AS serialNo,
            tbdio.base_photo_path AS basePhotoPath,
            tbdio.device_serial_no AS deviceSerialNo,
            ss.id AS supplierId,
            ss.name AS supplierName
        FROM
            t_b_device_in_out tbdio
                LEFT JOIN
            t_b_service tbs ON tbdio.service_id = tbs.id
                LEFT JOIN
            sys_device sd ON tbdio.device_serial_no = sd.serial_no
                LEFT JOIN
            sys_user su ON tbdio.user_id = su.id
                LEFT JOIN
            sys_supplier ss ON sd.supplier_id = ss.id
        <where>
            AND (tbs.status = 'PUBLISH' OR tbdio.service_id IS NULL)
            <if test="serviceUnitId != null">
                AND tbs.id = #{serviceUnitId}
            </if>
            <if test="usageType != null">
                AND tbdio.usage_type = #{usageType}
            </if>
            <if test="userId != null">
                AND su.id = #{userId}
            </if>
            <if test="deviceStatus != null">
                AND tbdio.status = #{deviceStatus}
            </if>
            <if test="inOutTimeStart != null">
                AND tbdio.in_out_time >= #{inOutTimeStart}
            </if>
            <if test="inOutTimeEnd != null">
                AND #{inOutTimeEnd} > tbdio.in_out_time
            </if>
            <if test="deviceNo != null and deviceNo != ''">
                AND sd.device_code LIKE CONCAT('%',#{deviceNo},'%')
            </if>
            <if test="deviceSerialNo != null and deviceSerialNo != ''">
                AND sd.serial_no LIKE CONCAT('%',#{deviceSerialNo},'%')
            </if>
            <if test="customName != null and customName != ''">
                AND tbdio.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="location != null and location != ''">
                AND tbdio.location LIKE CONCAT('%',#{location},'%')
            </if>
            <if test="address != null and address != ''">
                AND tbdio.address LIKE CONCAT('%',#{address},'%')
            </if>
            <if test="serialNo != null and serialNo != ''">
                AND tbdio.serial_no LIKE CONCAT('%',#{serialNo},'%')
            </if>
            <if test="inOutType != null">
                AND tbdio.type = #{inOutType}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbc.crm_no = tbdio.crm_no AND tbc.lead_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL)
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbc.crm_no = tbdio.crm_no AND tbc.region_coordinator_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL)
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY tbdio.serial_no DESC
    </select>

    <select id="findDeviceInOutPageBySource" resultType="com.qmth.sop.business.bean.result.DeviceInOutResult">
        SELECT
            tbdio.id AS inOutId,
            tbdio.service_id AS serviceUnitId,
            IFNULL(tbs.name,'--') AS serviceUnitName,
            tbdio.usage_type AS usageType,
            sd.device_code AS deviceNo,
            sd.id AS deviceId,
            sd.brand AS deviceBrand,
            sd.model AS deviceModel,
            tbdio.status AS deviceStatus,
            tbdio.in_out_time AS inOutTime,
            su.id AS userId,
            su.real_name AS userName,
            IFNULL(tbdio.custom_name,'--') AS customName,
            tbdio.type AS inOutType,
            tbdio.location AS location,
            tbdio.address AS address,
            tbdio.address_arr AS addressArrTemp,
            tbdio.serial_no AS serialNo,
            tbdio.base_photo_path AS basePhotoPath,
            tbdio.device_serial_no AS deviceSerialNo,
            ss.id AS supplierId,
            ss.name AS supplierName,
            sd.scan_count AS scanCount
        FROM
            t_b_device_in_out tbdio
                LEFT JOIN
            t_b_service tbs ON tbdio.service_id = tbs.id
                LEFT JOIN
            sys_device sd ON tbdio.device_serial_no = sd.serial_no
                LEFT JOIN
            sys_user su ON tbdio.user_id = su.id
                LEFT JOIN
            sys_supplier ss ON sd.supplier_id = ss.id
        <where>
            AND (tbs.status = 'PUBLISH' OR tbdio.service_id IS NULL)
            <if test="serviceUnitId != null">
                AND tbs.id = #{serviceUnitId}
            </if>
            <if test="usageType != null">
                AND tbdio.usage_type = #{usageType}
            </if>
            <if test="userId != null">
                AND su.id = #{userId}
            </if>
            <if test="deviceStatus != null">
                AND tbdio.status = #{deviceStatus}
            </if>
            <if test="inOutTimeStart != null">
                AND tbdio.in_out_time >= #{inOutTimeStart}
            </if>
            <if test="inOutTimeEnd != null">
                AND #{inOutTimeEnd} > tbdio.in_out_time
            </if>
            <if test="deviceNo != null and deviceNo != ''">
                AND sd.device_code LIKE CONCAT('%',#{deviceNo},'%')
            </if>
            <if test="deviceSerialNo != null and deviceSerialNo != ''">
                AND sd.serial_no LIKE CONCAT('%',#{deviceSerialNo},'%')
            </if>
            <if test="customName != null and customName != ''">
                AND tbdio.custom_name LIKE CONCAT('%',#{customName},'%')
            </if>
            <if test="location != null and location != ''">
                AND tbdio.location LIKE CONCAT('%',#{location},'%')
            </if>
            <if test="address != null and address != ''">
                AND tbdio.address LIKE CONCAT('%',#{address},'%')
            </if>
            <if test="serialNo != null and serialNo != ''">
                AND tbdio.serial_no LIKE CONCAT('%',#{serialNo},'%')
            </if>
            <if test="inOutType != null">
                AND tbdio.type = #{inOutType}
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbc.crm_no = tbdio.crm_no AND tbc.lead_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL)
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND EXISTS(SELECT 1 FROM t_b_crm tbc WHERE tbc.crm_no = tbdio.crm_no AND tbc.region_coordinator_id = #{dpr.requestUserId} OR tbdio.service_id IS NULL)
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY tbdio.serial_no DESC
    </select>

    <select id="findDeviceCanOutInfo" resultType="com.qmth.sop.business.bean.result.DeviceInOutResult">
        SELECT
            sd.serial_no AS serialNo,
            sd.brand AS deviceBrand,
            sd.model AS deviceModel,
            ss.name AS supplierName,
            sd.status AS deviceStatus,
            sd.scan_count AS scanCount,
            sd.device_code AS deviceNo,
            sd.location,
            sd.location_arr AS locationArrTemp
        FROM
            sys_device sd
                LEFT JOIN
            sys_supplier ss ON sd.supplier_id = ss.id
        <where>
            AND sd.enable
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="status != null">
                AND sd.status = #{status}
            </if>
            <if test="inOutType != null">
                AND sd.bound = #{inOutType}
            </if>
        </where>
        ORDER BY ss.id,sd.brand,sd.serial_no
    </select>
    <select id="findDeviceCanInInfo" resultType="com.qmth.sop.business.bean.result.DeviceInOutResult">
        SELECT
            sd.serial_no AS serialNo,
            sd.brand AS deviceBrand,
            sd.model AS deviceModel,
            ss.name AS supplierName,
            sd.status AS deviceStatus,
            sd.scan_count AS scanCount,
            sd.device_code AS deviceNo,
            sd.location,
            sd.location_arr AS locationArrTemp,
            ss.name AS address,
            tbdd.express_no AS expressNo
        FROM
            sys_device sd
        LEFT JOIN
            sys_supplier ss ON sd.supplier_id = ss.id
        LEFT JOIN
            t_b_device_delivery tbdd ON tbdd.serial_no = sd.serial_no AND tbdd.effect
        <where>
            IF(tbdd.id IS NULL,
                TRUE,
                tbdd.effect
                AND tbdd.status = 'RECEIVE')
            AND sd.enable
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="inOutType != null">
                AND sd.bound = #{inOutType}
            </if>
            <choose>
                <when test="sopNo != null and sopNo != ''">
                    AND EXISTS(SELECT 1 FROM t_b_device_in_out tbdio WHERE tbdio.device_serial_no = sd.serial_no AND tbdio.sop_no = #{sopNo})
                </when>
                <otherwise>
                    AND EXISTS(SELECT 1 FROM t_b_device_in_out tbdio WHERE tbdio.device_serial_no = sd.serial_no AND tbdio.service_id IS NULL)
                </otherwise>
            </choose>
        </where>
        ORDER BY ss.id,sd.brand,sd.serial_no
    </select>
</mapper>
