<?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.pluss.platform.mapper.DeviceStockMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.pluss.platform.entity.DeviceStock">
        <id column="id" property="id"/>
        <result column="snNo" property="snNo"/>
        <result column="orderNo" property="orderNo"/>
        <result column="price" property="price"/>
        <result column="type" property="type"/>
        <result column="groupNo" property="groupNo"/>
        <result column="buyMercName" property="buyMercName"/>
        <result column="buyMercId" property="buyMercId"/>
        <result column="actMercName" property="actMercName"/>
        <result column="actMercId" property="actMercId"/>
        <result column="status" property="status"/>
        <result column="createTime" property="createTime"/>
        <result column="createBy" property="createBy"/>
        <result column="delFlag" property="delFlag"/>
        <result column="remarks" property="remarks"/>
    </resultMap>

    <select id="selectStockInfoList" resultType="java.util.Map">
        SELECT dd.code, ds.groupNo, dd.size, count(1) count FROM tb_pluss_device_stock ds
        LEFT JOIN tb_pluss_device_detail dd ON ds.groupNo = dd.groupNo AND dd.`code` = ds.`code`
        WHERE ds.groupNo IN
        <foreach collection="groupNoList" item="groupNo" close=")" separator="," open="(">
            #{groupNo}
        </foreach>
        GROUP BY dd.groupNo, dd.code, dd.size;
    </select>

    <select id="selectSoldStockList" resultType="java.util.Map">
        SELECT doi.groupNo, SUM(doi.num * dd.size) soldCount FROM tb_pluss_device_order_info doi
        LEFT JOIN tb_pluss_device_detail dd ON doi.groupNo = dd.groupNo AND dd.`code` = doi.`code`
        WHERE dd.groupNo IN
        <foreach collection="groupNoList" item="groupNo" close=")" separator="," open="(">
            #{groupNo}
        </foreach>
        GROUP BY groupNo;
    </select>

    <select id="getStockTypeNumList" resultType="cn.pluss.platform.vo.DeviceStockVO"
            parameterType="cn.pluss.platform.dto.DeviceStockDTO">
        SELECT
        dt.`code`,
        dt.`name` as deviceName,
        IFNULL(ds.totalNum,0) as totalNum,
        IFNULL(ds.bindNum,0) as bindNum,
        IFNULL(ds.unBindNum,0) as unBindNum,
        dg.deviceLogo
        FROM tb_pluss_device_type dt
        LEFT JOIN tb_pluss_device_goods dg ON dt.`code` = dg.`code`
        LEFT JOIN (
        SELECT
        count(1) as totalNum,
        `code`,
        SUM(IF( `status` = 3, 1, 0 )) AS bindNum,
        SUM(IF( `status` != 3 or `status` is null, 1, 0)) AS unBindNum
        FROM
        tb_pluss_device_stock
        WHERE
        <choose>
            <when test='pType == "2"'>
                belongUserId = #{userId} or belongUserId in (select userId from tb_pluss_user_app where pidArr like
                CONCAT('%,',#{userId}))
            </when>
            <otherwise>
                belongUserId = #{userId}
            </otherwise>
        </choose>

        GROUP BY `code` ) ds
        ON dt.`code` = ds.`code`
        WHERE dt.showFlag = 1
    </select>

    <select id="pageInfo" parameterType="cn.pluss.platform.dto.DeviceStockDTO"
            resultType="cn.pluss.platform.vo.DeviceStockVO">
        SELECT ds.*, mbi.userId actUserId, mbi2.userId buyUserId,
        dt.name as deviceName,
        dg.deviceLogo,
        ue.userName as extractName,
        ub.userName as belongName
        FROM tb_pluss_device_stock ds
        left join tb_pluss_device_type dt on ds.`code` = dt.`code`
        left join tb_pluss_device_goods dg on ds.`code` = dg.`code`
        left join tb_pluss_user_app ue on ds.extractUserId = ue.userId
        left join tb_pluss_user_app ub on ds.belongUserId = ub.userId
        LEFT JOIN tb_pluss_merchant_base_info mbi ON mbi.id = ds.actMercId
        LEFT JOIN tb_pluss_merchant_base_info mbi2 ON mbi2.id = ds.buyMercId
        <where>
            <if test="dto.code != null and dto.code != ''">
                and ds.`code` = #{dto.code}
            </if>
            <if test="dto.userId != null">
                <choose>
                    <when test="dto.pType != null">
                        <choose>
                            <when test='dto.pType == "2"'>
                                and (belongUserId = #{dto.userId} or belongUserId in (select userId from tb_pluss_user_app
                                where pidArr like CONCAT('%,',#{dto.userId})))
                            </when>
                            <otherwise>
                                and belongUserId = #{dto.userId}
                            </otherwise>
                        </choose>
                    </when>
                    <otherwise>
                        and ds.belongUserId = #{dto.userId}
                    </otherwise>
                </choose>
            </if>
            <if test="dto.extractUserId != null">
                and ds.extractUserId = #{dto.extractUserId}
            </if>
            <if test="dto.startDate != null and dto.startDate != ''">
                and DATE_FORMAT(ds.inStockTime,'%Y-%m-%d') <![CDATA[ >= ]]> #{dto.startDate}
            </if>
            <if test="dto.endDate != null and dto.endDate != ''">
                and DATE_FORMAT(ds.inStockTime,'%Y-%m-%d') <![CDATA[ <= ]]> #{dto.endDate}
            </if>

            <if test="dto.startSnNo != null and dto.startSnNo != ''">
                and ds.deviceNo <![CDATA[ >= ]]> #{dto.startSnNo}
            </if>
            <if test="dto.endSnNo != null and dto.endSnNo != ''">
                and ds.deviceNo <![CDATA[ <= ]]> #{dto.endSnNo}
            </if>
            <if test="dto.startDeviceNo != null and dto.startDeviceNo != ''">
                and ds.snNo <![CDATA[ >= ]]> #{dto.startDeviceNo}
            </if>
            <if test="dto.endDeviceNo != null and dto.endDeviceNo != ''">
                and ds.snNo <![CDATA[ <= ]]> #{dto.endDeviceNo}
            </if>

            <if test="dto.type != null and dto.type != ''">
                and ds.type = #{dto.type}
            </if>

            <if test="dto.buyMercName != null and dto.buyMercName != ''">
                and ds.buyMercName = #{dto.buyMercName}
            </if>

            <if test="dto.actMercName != null and dto.actMercName != ''">
                and ds.actMercName = #{dto.actMercName}
            </if>

            <if test="dto.actUserId != null and dto.actUserId != ''">
                and mbi.userId = #{dto.actUserId}
            </if>

            <if test="dto.deviceNo != null and dto.deviceNo != ''">
                and ds.deviceNo = #{dto.deviceNo}
            </if>
            <if test="dto.snNo != null and dto.snNo != ''">
                and (ds.snNo like CONCAT('%',#{dto.snNo},'%') or ds.deviceNo like CONCAT('%',#{dto.snNo},'%') )
            </if>
            <if test="dto.bindStatus != '-2'">
                <choose>
                    <when test="dto.bindStatus == null or dto.bindStatus == '' or dto.bindStatus == '-1'">
                        and ds.status in ('1','2')
                    </when>
                    <otherwise>
                        and ds.status = '3'
                    </otherwise>
                </choose>
            </if>
        </where>
        order by ds.id desc
    </select>

    <select id="detail" parameterType="cn.pluss.platform.dto.DeviceStockDTO"
            resultType="cn.pluss.platform.vo.DeviceStockVO">
        SELECT ds.*,
        ua1.userName as belongName,
        ui1.phone as belongPhone,
        ua2.userName as extractName,
        ui2.phone as extractPhone
        FROM tb_pluss_device_stock ds
        left join tb_pluss_user_app ua1 on ds.belongUserId = ua1.userId
        left join tb_pluss_user_info ui1 on ds.belongUserId = ui1.id

        left join tb_pluss_user_app ua2 on ds.extractUserId = ua2.userId
        left join tb_pluss_user_info ui2 on ds.extractUserId = ui2.id
        <where>
            ds.`snNo` = #{snNo}
        </where>
    </select>

    <select id="getMercInfo" parameterType="cn.pluss.platform.dto.DeviceStockDTO"
            resultType="cn.pluss.platform.vo.DeviceStockVO">
        SELECT ds.actMercName,
        ui.phone as mercPhone,
        IFNULL(ds.bindTime,ds.updateTime) as bindTime
        FROM tb_pluss_device_stock ds
        left join tb_pluss_merchant_base_info mbi on ds.actMercId = mbi.id
        left join tb_pluss_user_info ui on mbi.userId = ui.id
        <where>
            ds.`snNo` = #{snNo}
        </where>
    </select>

    <select id="pageDeviceStock" resultType="cn.pluss.platform.vo.DeviceStockVO">
        SELECT ds.*, dt.`name` deviceName, spec.specName typeDesc, dg.deviceLogo, dt.payCodeFlag, mcp.name cashPlaceName
        FROM tb_pluss_device_stock ds
        LEFT JOIN tb_pluss_merchant_base_info mbi ON ds.actMercId = mbi.id
        LEFT JOIN tb_pluss_device_type dt ON dt.`code` = ds.`code`
        LEFT JOIN tb_pluss_device_spec spec ON ds.`code` = spec.`code` AND ds.type = spec.spec
        LEFT JOIN tb_pluss_merchant_cash_pay_code mcpc ON mcpc.payCode = ds.snNo
        LEFT JOIN tb_pluss_merchant_cash_place mcp ON mcpc.cashPlaceId = mcp.id
        LEFT JOIN tb_pluss_device_goods dg ON dg.`code` = ds.`code`
        <where>
            <if test="userId != null">
                AND mbi.userId = #{userId}
            </if>
            <if test="cashPlaceId != null">
                AND mcpc.cashPlaceId = #{cashPlaceId}
            </if>
        </where>
    </select>

</mapper>
