<?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.zfire.mall.manager.commonmapper.manage.product.ProductUpdPriceBillReserveMapper">

    <resultMap id="listDetailResultMap" type="com.zfire.mall.manager.bean.manage.product.price.ProductUpdPriceBillBean">
        <result column="upd_price_bill_id" jdbcType="VARCHAR" property="updPriceBillId" />
        <result column="admin_company_id" jdbcType="VARCHAR" property="adminCompanyId" />
        <result column="admin_websit_id" jdbcType="VARCHAR" property="adminWebsitId" />
        <result column="admin_websit_name" jdbcType="VARCHAR" property="adminWebsitName" />
        <result column="bill_type" jdbcType="VARCHAR" property="billType" />
        <result column="service_id" jdbcType="VARCHAR" property="serviceId" />
        <result column="service_name" jdbcType="VARCHAR" property="serviceName" />
        <result column="examine_status" jdbcType="VARCHAR" property="examineStatus" />
        <result column="remark" jdbcType="VARCHAR" property="remark" />
        <result column="confirm_by" jdbcType="VARCHAR" property="confirmBy" />
        <result column="confirm_name" jdbcType="VARCHAR" property="confirmName" />
        <result column="confirm_time" jdbcType="TIMESTAMP" property="confirmTime" />
        <result column="create_by" jdbcType="VARCHAR" property="createBy" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <collection property="items" resultMap="itemResultList" />
    </resultMap>
    
    <resultMap id="itemResultList" type="com.zfire.mall.manager.bean.manage.product.price.ProductUpdPriceBillItemBean">
        <result column="item_id" jdbcType="VARCHAR" property="id" />
        <result column="upd_price_bill_id" jdbcType="VARCHAR" property="updPriceBillId" />
        <result column="sale_type_id" jdbcType="VARCHAR" property="saleTypeId" />
        <result column="sale_type_code" jdbcType="VARCHAR" property="saleTypeCode" />
        <result column="sale_type_name" jdbcType="VARCHAR" property="saleTypeName" />
        <result column="material_id" jdbcType="VARCHAR" property="materialId" />
        <result column="material_name" jdbcType="VARCHAR" property="materialName" />
        <result column="material_number" jdbcType="VARCHAR" property="materialNumber" />
        <result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber" />
        <result column="specification" jdbcType="VARCHAR" property="specification" />
        <result column="unit" jdbcType="VARCHAR" property="unit" />
        <result column="batch_price" jdbcType="DECIMAL" property="batchPrice" />
        <result column="disc_amount" jdbcType="DECIMAL" property="discAmount" />
        <result column="start_date" jdbcType="DATE" property="startDate" />
        <result column="end_date" jdbcType="DATE" property="endDate" />
        <result column="is_promote" jdbcType="BOOLEAN" property="isPromote" />
        <result column="qty" jdbcType="DECIMAL" property="qty" />
        <result column="is_publish" jdbcType="BOOLEAN" property="isPublish" />
        <result column="main_id" jdbcType="VARCHAR" property="mainId" />
        <result column="main_name" jdbcType="VARCHAR" property="mainName" />
        <!--<collection property="wallets" ofType="com.zfire.mall.manager.plus.entity.ProductUpdPriceBillItemWallet">-->
            <!--<result column="walletItemId" jdbcType="VARCHAR" property="id" />-->
            <!--<result column="parent_item_id" jdbcType="VARCHAR" property="parentItemId" />-->
            <!--<result column="wallet_id" jdbcType="VARCHAR" property="walletId" />-->
        <!--</collection>-->
    </resultMap>

    <select id="listPage" resultType="com.zfire.mall.manager.bean.manage.product.price.ProductUpdPriceBillListBean">
        SELECT
            a.*,
            b.id as 'item_id',
            b.material_id,
            b.material_name,
            b.material_number,
            b.material_old_number,
            b.specification,
            b.unit,
            b.batch_price,
            b.retail_price,
            b.disc_amount,
            b.start_date,
            b.end_date,
            b.is_promote,
            b.qty,
            b.is_publish,
            b.sale_type_id,
            b.sale_type_code,
            b.sale_type_name,
            b.main_name
        FROM
            product_upd_price_bill a
                LEFT JOIN product_upd_price_bill_item b ON a.upd_price_bill_id = b.upd_price_bill_id
        <where>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="materialNumber != null and materialNumber != ''">
                AND b.material_number = #{materialNumber}
            </if>
            <if test="materialOldNumber != null and materialOldNumber !=''">
                and b.material_old_number = #{materialOldNumber}
            </if>
            <if test="materialName != null and materialName != ''">
                AND b.material_name like concat('%',#{materialName},'%')
            </if>
            <if test="remark != null and remark !=''">
                and a.remark like concat('%',#{remark},'%')
            </if>
            <if test="mainId != null and mainId !=''">
                and b.main_id =#{mainId}
            </if>
            <if test="saleTypeId != null and saleTypeId !=''">
                and b.sale_type_id =#{saleTypeId}
            </if>
            <if test="startDate != null and startDate !=''">
                AND b.start_date = #{startDate}
            </if>
            <if test="specification != null and specification !=''">
                and b.specification like concat('%',#{specification},'%')
            </if>
            <if test="billType != null and billType !=''">
                and a.bill_type = #{billType}
            </if>
            <if test="k3CategoryNumber != null and k3CategoryNumber != ''">
                and b.k3_category_number = #{k3CategoryNumber}
            </if>
            <if test="k3CategoryIds != null and k3CategoryIds.size > 0">
                AND b.k3_category_id in
                <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator="," close=")">
                  #{item}
                </foreach>
            </if>
            AND a.del = 0
        </where>
        ORDER BY a.create_time desc, a.upd_price_bill_id DESC
    </select>
    <select id="detail" resultMap="listDetailResultMap">
        SELECT
            a.*,
            b.id AS item_id,
            b.sale_type_id,
            b.sale_type_code,
            b.sale_type_name,
            b.material_id,
            b.material_name,
            b.material_number,
            b.material_old_number,
            b.specification,
            b.unit,
            b.wallet_rebate_id,
            b.wallet_rebate_name,
            b.rebate_rate,
            b.batch_price,
            b.retail_price,
            b.disc_amount,
            b.start_date,
            b.end_date,
            b.is_promote,
            b.qty,
            b.is_publish,
            c.id AS walletItemId,
            c.upd_price_bill_item_id,
            c.wallet_id,
            b.main_id,
            b.main_name,
            a.service_id,
            a.service_name
        FROM
            product_upd_price_bill a
                 JOIN product_upd_price_bill_item b ON a.upd_price_bill_id = b.upd_price_bill_id
                 JOIN product_upd_price_bill_item_wallet c ON a.upd_price_bill_id = c.upd_price_bill_id AND b.id = c.upd_price_bill_item_id
        <where>
            a.upd_price_bill_id = #{id}
            <!--<if test="adminCompanyIds != null and adminCompanyIds.size > 0">-->
                <!--AND a.admin_company_id in-->
                <!--<foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","-->
                         <!--close=")">-->
                    <!--#{item}-->
                <!--</foreach>-->
            <!--</if>-->
            AND a.del = 0
        </where>
        order by a.create_time desc
    </select>

    <select id="queryWallet" resultType="com.zfire.mall.manager.bean.manage.product.price.ProductWalletBean">
        	select
                a.upd_price_bill_item_id,
                b.id as 'wallet_id',
                b.name as 'wallet_name',
                a.type as 'type'
            from product_upd_price_bill_item_wallet a
        join king_dee_wallet b on a.wallet_id=b.id
            where a.type='COMMONLY' and a.upd_price_bill_item_id in
            <foreach item="item" index="index" collection="updPriceBillItemIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
    union
        select
                a.upd_price_bill_item_id,
                b.wallet_rebate_id as 'wallet_id',
                b.name as 'wallet_name',
                a.type  as 'type'
            from product_upd_price_bill_item_wallet a
        join wallet_rebate b on a.wallet_id=b.wallet_rebate_id
        where a.type='REBATE' and a.upd_price_bill_item_id in
        <foreach item="item" index="index" collection="updPriceBillItemIds" open="(" separator=","
                 close=")">
            #{item}
        </foreach>
    </select>

    <select id="queryMain" resultType="com.zfire.mall.manager.bean.MainBean">
        select
            a.product_category_id AS 'main_id',
            d.product_category_name AS 'main_name'
        from product_category_rela a
        join king_dee_category b on b.id=a.king_dee_category_id
        join king_dee_material c on c.category_id = b.id
        join product_category d on d.product_category_number = a.product_category_id
        where c.id=#{materialId}
    </select>

    <select id="v2ListPage" resultType="com.zfire.mall.manager.bean.manage.v2.product.price.ProductUpdPriceBillVO">
        SELECT
            a.*,
            b.id as 'item_id',
            b.material_id,
            b.material_name,
            b.material_number,
            b.material_old_number,
            b.specification,
            b.unit,
            b.batch_price,
            b.retail_price,
            b.disc_amount,
            b.start_date,
            b.end_date,
            b.is_promote,
            b.qty,
            b.is_publish,
            b.sale_type_id,
            b.sale_type_code,
            b.sale_type_name,
            b.main_name
        FROM
            product_upd_price_bill a
                LEFT JOIN product_upd_price_bill_item b ON a.upd_price_bill_id = b.upd_price_bill_id
        ${ex.query}
        AND a.del = 0
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY a.create_time DESC
        </if>
        ${ex.orderBy}
    </select>


</mapper>