<?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.retail.RetailOrderCommonMapper">

    <select id="queryRetailOrderList" resultType="com.zfire.mall.manager.bean.retail.RetailOrderList">
        select
            a.*,
            round(b.has_send_qty/b.qty*100,2) 'thjd',
            b.id as retailId,
            b.sale_type_id,
            b.sale_type_name,
            b.material_id,
            b.material_code,
            b.material_name,
            b.specification,
            b.unit,
            b.qty,
            b.price,
            b.total_amount as 'item_total_amount',
            b.customer_wallet_name,
            b.customer_wallet_name2,
            b.rebate_rate,
            b.rebate_amount,
            b.pay_amount,
            b.pay_rebate_amount,
            b.disc_amount,
            b.total_disc_amount,
            b.is_direct_transfer,
        (b.refundable_qty -  b.withhold ) as refundable_qty,
            b.retired_qty,
            b.direct_transfer_qty,
            b.has_send_qty,
            b.service_id,
            b.service_name,
            b.remark as 'itemRemark',
            b.material_old_number,
            b.k3_category_name,
        kdm.material_group_name
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        join customer_product_type_rela c on c.customer_wallet_id = b.customer_wallet_id
        join king_dee_material kdm on b.material_id = kdm.id
        <include refid="retailWhere"></include>
        order by a.create_time desc
    </select>



    <select id="queryRetailOrderCount" resultType="java.lang.Integer">
        select
            count(distinct b.id)
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        join customer_product_type_rela c on c.customer_wallet_id = b.customer_wallet_id
        join king_dee_material kdm on b.material_id = kdm.id
        LEFT JOIN invoice_application_order iao on iao.order_id = b.id and iao.refundable_qty > 0 and iao.invoice_examine_status in ('SAVE','WAIT')
        <include refid="retailWhere"></include>
    </select>


    <sql id="retailWhere">
        <where>
            <if test="id != null and id !=''">
                and a.id like concat('%',#{id},'%')
            </if>
            <if test="customerWalletId != null and customerWalletId !=''">
                and b.wallet_id1 =#{customerWalletId}
            </if>
            <if test="saleTypeId != null and saleTypeId !=''">
                and b.sale_type_id =#{saleTypeId}
            </if>
            <if test="policyCode != null and policyCode !=''">
                and a.policy_code like concat('%',#{policyCode},'%')
            </if>
            <if test="policyRemark != null and policyRemark !=''">
                and a.policy_remark like concat('%',#{policyRemark},'%')
            </if>
            <if test="serviceId != null and serviceId !=''">
                and b.service_id = #{serviceId}
            </if>
            <if test="k3ServiceId != null and k3ServiceId !=''">
                and a.k3_service_id = #{k3ServiceId}
            </if>
            <if test="type != null">
                and a.type = #{type}
            </if>
            <if test="examineStatus != null and examineStatus !=''">
                and a.examine_status = #{examineStatus}
            </if>
            <if test="materialName != null and materialName !=''">
                and b.material_name like concat(#{materialName},'%')
            </if>
            <if test="specification != null and specification !=''">
                and b.specification like  concat('%',#{specification},'%')
            </if>
            <if test="customer != null and customer !=''">
                and (a.customer_id like concat('%',#{customer},'%') or a.customer_name like concat('%',#{customer},'%'))
            </if>
            <if test="customerId != null and customerId !=''">
                and a.customer_id = #{customerId}
            </if>
            <if test="startTime != null and startTime !='' and endTime != null and endTime !=''">
                and a.the_time between #{startTime} and #{endTime}
            </if>
            <if test="createBy != null and createBy != ''">
                and a.create_by like concat('%',#{createBy},'%')
            </if>
            <if test="examineBy != null and examineBy != ''">
                and a.examine_name like concat('%',#{examineBy},'%')
            </if>
            <if test="isDirectTransfer != null">
                and b.is_direct_transfer = #{isDirectTransfer}
            </if>
            <if test="materialOldNumber != null and materialOldNumber !=''">
                and b.material_old_number = #{materialOldNumber}
            </if>
            <if test="materialCode != null and materialCode !=''">
                and b.material_code = #{materialCode}
            </if>
            <if test="k3CategoryNumber != null and k3CategoryNumber !=''">
                and b.k3_category_number=#{k3CategoryNumber}
            </if>
            <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="serviceIds != null and serviceIds.size > 0">
                and c.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </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>
        </where>
    </sql>




    <select id="queryProductList" resultType="com.zfire.mall.manager.bean.retail.ProductPriceBean">
        select
            b.id as 'sale_id',
            b.sale_code,
            b.sale_name,
            c.number,
            c.name,
            c.specification,
            c.base_unit_id 'base_unit',
            c.tax_rate_id 'tax_rate',
            a.*
        from product_price a
        join sale_type b on a.sale_type_id = b.id
        join king_dee_material c on c.id=a.material_id
        where a.is_revoke = 0 and now() between a.start_date and a.end_date
        <if test="saleId != null and saleId !=''">
            and a.sale_type_id =#{saleId}
        </if>
        <if test="materialNumber != null and materialNumber !=''">
            and c.number =#{materialNumber}
        </if>
        <if test="materialName != null and materialName !=''">
            and c.name like concat('%',#{materialName},'%')
        </if>
        <if test="specification != null and specification !=''">
            and c.specification like concat('%',#{specification},'%')
        </if>
        <if test="price1 != null and price2 != null">
            and a.batch_price between #{price1} and #{price2}
        </if>
        <if test="billType != null and billType != ''">
            and a.bill_type = #{billType}
        </if>
        <if test="oldNumbers != null and oldNumbers.size > 0">
            and c.old_number in
            <foreach item="item" index="index" collection="oldNumbers" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by a.create_time desc
    </select>



    <select id="queryDirectTransfer" resultType="com.zfire.mall.manager.bean.direct.DirectTransferList">
        select
            a.id as 'deliverNo',
            a.deliver_date,
            c.`name` 'correspondName',
            a.order_type,
            a.order_no,
            a.customer_name,
            b.material_name,
            b.specification,
            b.unit,
            b.qty,
            b.price,
            b.after_tax_price 'afterTaxPrice',
            b.must_total_amount 'mustTotalAmount',
            b.must_total_amount - b.must_total_amount * b.tax_rate AS 'noTaxTotalAmount',
            b.single_deposit_amount,
            b.total_deposit_amount,
            b.remark,
            a.create_by,
            a.create_time
        from direct_transfer_order a
        join direct_transfer_order_item b on a.id = b.direct_transfer_order_id
        join king_dee_stock c on a.stock_id = c.id
        <where>
            <if test="deliverNo != null and deliverNo !=''">
                and a.id like concat('%',#{deliverNo},'%')
            </if>
            <if test="orderNo != null and orderNo !=''">
                and a.order_no like concat('%',#{orderNo},'%')
            </if>
            <if test="enginRecordNo != null and enginRecordNo !=''">
                and a.engin_record_no like concat('%',#{enginRecordNo},'%')
            </if>
            <if test="orderType != null and orderType !='' ">
                and a.order_type = #{orderType}
            </if>
            <if test="customerName != null and customerName !=''">
                and a.customer_name = #{customerName}
            </if>
            <if test="startTime != null and endTime !=null and startTime !='' and endTime != ''">
                and a.order_date between #{startTime} and #{endTime}
            </if>

        </where>
        order by a.create_time desc
    </select>

    <select id="queryPolicyOrderNums" resultType="java.lang.Integer">
        select
          <if test="materialId != null and materialId !=''">
            sum(b.qty) counts
          </if>
          <if test="materialId == null or materialId == ''">
              count(distinct  a.id) counts
          </if>
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        where a.customer_id=#{customerId}
        and type=2  and a.policy_id=#{policyId}

        <if test="examineStatus != null and examineStatus != ''">
            and a.examine_status=#{examineStatus}
        </if>
        <if test="materialId != null and materialId !=''">
            and b.material_id = #{materialId}
        </if>

    </select>

</mapper>