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


    <sql id="retailSql">
        select
            b.full_cost_time,
            b.material_old_number,
            a.create_time,
            a.the_time,
            a.id,
            b.is_cost,
            null as ref_use_unit,
            null as ref_project_note,
            a.customer_id,
            b.is_displace_order,
            a.customer_number as 'customer_number',
            a.customer_name,
            b.id as 'orderItemId',
            b.sale_type_code,
            b.sale_type_name,
            b.material_name,
            b.material_code,
            b.material_id,
            b.specification,
            (b.refundable_qty ) as refundable_qty,
            b.price,
            b.qty+b.refund_product_qty as qty,
            b.retired_qty,
            if(0 > b.refund_product_qty,((b.qty+b.refund_product_qty) * b.single_pay_price),b.pay_amount) as pay_amount,
            (b.qty+b.refund_product_qty) * b.single_rebate_amount as pay_rebate_amount,
            (b.qty+b.refund_product_qty) *b.disc_amount as total_disc_amount,
            b.single_pay_price * has_send_qty as 'hasSendAmount',
            has_send_qty,
            direct_transfer_qty,
            b.retired_qty*b.single_pay_price as 'retiredAmount',
            DATE_FORMAT(a.the_time,'%m') as 'month',
            a.remark,
            b.remark as 'itemRemark',
            '' as 'project_name',
            a.k3_service_id as 'serviceId',
            a.k3_service_name as 'serviceName',
            b.service_id as 'itemServiceId',
            b.service_name as 'itemServiceName',
            e.service_id as 'newServiceId',
            e.service_name as 'newServiceName',
            b.customer_wallet_name AS 'walletName',
            b.customer_wallet_name2 AS 'rebateWalletName',
            b.k3_category_name as 'k3CategoryName',
            b.policy_id as 'policy_code',
            a.policy_title as 'policy_title',
            a.policy_remark as 'policy_remark',
            b.price_type,
            b.org_price,
            '' as ref_engin_record_no,
            '' as ref_project_no,
            '' as file_no,
            '' as ref_machine_type,
            '' as ref_project_name,
            b.refund_product_qty,
            b.refund_product_amount,
            b.single_rebate_amount,
            b.disc_amount,
            f.name as 'k3OrgName',
            a.examine_time,
            a.examine_name,
            a.examine_status,
            j.name as 'area',
            if(b.is_direct_transfer=0,'非直调',if(b.direct_transfer_status=0,'直调中','已完结')) as 'direct_transfer_status_name'
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        left join customer_product_type_rela e on e.customer_wallet_id = b.customer_wallet_id
        left join king_dee_department f on f.number = e.k3_org_number
        left join admin_user g on g.admin_user_id = e.service_id
        left join admin_user_websit_rela h on h.admin_user_id = g.admin_user_id
        left join admin_websit j on j.admin_websit_id = h.admin_websit_id
        <include refid="retailSqlWhere"></include>
        order by a.create_time desc
    </sql>

    <sql id="retailSqlWhere">
        where a.examine_status ='OK'
        <if test="costStartTime !=null and costStartTime !=''">
            and b.full_cost_time between #{costStartTime} and #{costEndTime}
        </if>
        <if test="isCost !=null and isCost !=''">
            and b.is_cost = #{isCost}
        </if>
        <if test="materialOldNumber !=null and materialOldNumber !=''">
            and b.material_old_number like concat('%',#{materialOldNumber},'%')
        </if>
        <if test="id !=null and id !=''">
            and a.id like concat('%',#{id},'%')
        </if>
        <if test="customerName !=null and customerName !=''">
            and a.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="materialName !=null and materialName !=''">
            and b.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="orderStartTime !=null and orderStartTime !='' and orderEndTime !=null and orderEndTime !=''">
            and a.the_time between #{orderStartTime} and #{orderEndTime}
        </if>
        <if test="startTime !=null and startTime !='' and endTime !=null and endTime !='' ">
            and a.create_time between #{startTime} and #{endTime}
        </if>
        <if test="serviceName !=null and serviceName !=''">
            and a.k3_service_name like concat('%',#{serviceName},'%')
        </if>
        <if test="itemServiceName !=null and itemServiceName !=''">
            and b.service_name like concat('%',#{itemServiceName},'%')
        </if>
        <if test="newServiceName != null and newServiceName != ''">
            and e.service_name like concat('%',#{newServiceName},'%')
        </if>
        <if test="type != null and type !=''">
            and a.type = #{type}
        </if>
        <if test="k3CategoryNumber != null and k3CategoryNumber.size > 0">
            and b.k3_category_number in
            <foreach item="item" index="index" collection="k3CategoryNumber" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </if>
        <if test="specification != null and specification !=''">
            and b.specification like concat('%',#{specification},'%')
        </if>
        <if test="policyCode != null and policyCode !=''">
            and a.policy_code like concat('%',#{policyCode},'%')
        </if>
        <if test="isOver != null and isOver == true">
            and b.refundable_qty = 0
        </if>
        <if test="isOver != null and isOver == false">
            and b.refundable_qty > 0
        </if>
        <if test="policyRemark != null and policyRemark !=''">
            and a.policy_remark like concat('%',#{policyRemark},'%')
        </if>
        <if test="isDisplaceOrder != null">
            and b.is_displace_order = #{isDisplaceOrder}
        </if>
        <if test="priceType != null and priceType !=''">
            and b.price_type = #{priceType}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and e.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>
        <if test="walletIds1 != null and walletIds1.size > 0">
            and b.wallet_id1 in
            <foreach item="item" index="index" collection="walletIds1" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="walletIds2 != null and walletIds2.size > 0">
            and b.wallet_id2 in
            <foreach item="item" index="index" collection="walletIds2" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

    </sql>

    <select id="queryRetailOrderExecCount" resultType="java.lang.Integer">
        select
            count(1)
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        left join customer_product_type_rela e on e.customer_wallet_id = b.customer_wallet_id
        <include refid="retailSqlWhere"></include>
    </select>



    <sql id="enginSql">
        select
            b.full_cost_time,
            b.material_old_number,
            a.create_time,
            a.order_date 'the_time',
            a.engin_order_no 'id',
            b.is_cost,
            a.ref_use_unit,
            a.ref_project_note,
            a.customer_id,
            b.is_displace_order,
            d.number as 'customer_number',
            a.customer_name,
            b.id as 'orderItemId',
            b.sale_type_code,
            b.sale_type_name,
            b.material_name,
            b.material_number as 'material_code',
            b.material_id,
            b.specification,
            (b.refundable_qty ) as refundable_qty,
            b.price,
            b.qty+b.refund_product_qty as qty,
            b.retired_qty,
            if(0 > b.refund_product_qty ,((b.qty+b.refund_product_qty) * b.single_pay_price),b.pay_amount) as pay_amount,
             (b.qty+b.refund_product_qty) * b.single_rebate_amount as pay_rebate_amount,
            (b.qty+b.refund_product_qty) *b.disc_amount as total_disc_amount,
            b.single_pay_price * has_send_qty as 'hasSendAmount',
            has_send_qty,
            direct_transfer_qty,
            b.retired_qty*b.single_pay_price as 'retiredAmount',
            DATE_FORMAT(a.order_date,'%m') as 'month',
            a.remark,
            b.remark as 'itemRemark',
            a.ref_project_name 'project_name',
            a.service_id as 'serviceId',
            a.service_name as 'serviceName',
            b.service_id as 'itemServiceId',
            b.service_name as 'itemServiceName',
            e.service_id as 'newServiceId',
            e.service_name as 'newServiceName',
            b.customer_wallet_name AS 'walletName',
            b.customer_wallet_name2 AS 'rebateWalletName',
            b.k3_category_name as 'k3CategoryName',
            '' as 'policy_code',
            '' as 'policy_title',
            '' as 'policy_remark',
            '' as 'price_type',
            0  as 'org_price',
            a.ref_engin_record_no,
            a.ref_project_no,
            a.file_no,
            a.ref_machine_type,
            a.ref_project_name,
            b.refund_product_qty,
            b.refund_product_amount,
            b.single_rebate_amount,
            b.disc_amount,
            f.name as 'k3OrgName',
            a.confirm_time as 'examine_time',
            a.confirm_name as 'examine_name',
            a.examine_status,
            j.name as 'area',
            if(b.is_direct_transfer=0,'非直调',if(b.direct_transfer_status=0,'直调中','已完结')) as 'direct_transfer_status_name'
        from engin_order a
        join engin_order_item b on a.engin_order_id = b.parent_id
        join admin_user c on c.admin_user_id=b.service_id
        join king_dee_customer d on d.id=a.customer_id
        left join customer_product_type_rela e on e.customer_wallet_id = b.customer_wallet_id
        left join king_dee_department f on f.number = e.k3_org_number
        left join admin_user_websit_rela h on h.admin_user_id = c.admin_user_id
        left join admin_websit j on j.admin_websit_id = h.admin_websit_id
        <include refid="enginSqlWhere"></include>
        order by a.create_time desc
    </sql>

    <sql id="enginSqlWhere">
        where a.examine_status ='OK'
        <if test="costStartTime !=null and costStartTime !=''">
            and b.full_cost_time between #{costStartTime} and #{costEndTime}
        </if>
        <if test="materialOldNumber !=null and materialOldNumber !=''">
            and b.material_old_number like concat('%',#{materialOldNumber},'%')
        </if>
        <if test="isCost !=null and isCost !=''">
            and b.is_cost = #{isCost}
        </if>
        <if test="refUseUnit !=null and refUseUnit !=''">
            and a.ref_use_unit like concat('%',#{refUseUnit},'%')
        </if>
        <if test="id !=null and id !=''">
            and a.engin_order_no like concat('%',#{id},'%')
        </if>
        <if test="customerName !=null and customerName !=''">
            and a.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="materialName !=null and materialName !=''">
            and b.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="startTime !=null and startTime !='' and endTime !=null and endTime !=''">
            and a.create_time between #{startTime} and #{endTime}
        </if>
        <if test="orderStartTime !=null and orderStartTime !='' and orderEndTime !=null and orderEndTime !=''">
            and a.order_date between #{orderStartTime} and #{orderEndTime}
        </if>
        <if test="serviceName !=null and serviceName !=''">
            and a.service_name like concat('%',#{serviceName},'%')
        </if>
        <if test="itemServiceName !=null and itemServiceName !=''">
            and c.nick_name like concat('%',#{itemServiceName},'%')
        </if>
        <if test="newServiceName != null and newServiceName != ''">
            and e.service_name like concat('%',#{newServiceName},'%')
        </if>
        <if test="type != null and type !=''">
            and a.engin_order_type = #{type}
        </if>
        <if test="projectName !=null and projectName !=''">
            and a.ref_project_name like concat('%',#{projectName},'%')
        </if>
        <if test="enginRecordNo != null and enginRecordNo !=''">
            and a.ref_engin_record_no like concat('%',#{enginRecordNo},'%')
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </if>
        <if test="specification != null and specification !=''">
            and b.specification like concat('%',#{specification},'%')
        </if>
        <if test="isDisplaceOrder != null">
            and b.is_displace_order = #{isDisplaceOrder}
        </if>
        <if test="k3CategoryNumber != null and k3CategoryNumber.size > 0">
            and b.k3_category_number in
            <foreach item="item" index="index" collection="k3CategoryNumber" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="isOver != null and isOver == true">
            and b.refundable_qty = 0
        </if>
        <if test="isOver != null and isOver == false">
            and b.refundable_qty > 0
        </if>
        <if test="priceType != null and priceType !=''">
            and b.price_type = #{priceType}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and e.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>
        <if test="walletIds1 != null and walletIds1.size > 0">
            and b.wallet_id1 in
            <foreach item="item" index="index" collection="walletIds1" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="walletIds2 != null and walletIds2.size > 0">
            and b.wallet_id2 in
            <foreach item="item" index="index" collection="walletIds2" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

    </sql>

    <select id="queryEnginOrderExecCount" resultType="java.lang.Integer">
        select
          count(1)
        from engin_order a
        join engin_order_item b on a.engin_order_id = b.parent_id
        join admin_user c on c.admin_user_id=b.service_id
        join king_dee_customer d on d.id=a.customer_id
        left join customer_product_type_rela e on e.customer_wallet_id = b.customer_wallet_id
        <include refid="enginSqlWhere"></include>
    </select>




    <select id="queryRetailOrderExec" resultType="com.zfire.mall.manager.bean.OrderExecBean">
          <include refid="retailSql"></include>
    </select>

    <select id="queryEnginOrderExec" resultType="com.zfire.mall.manager.bean.OrderExecBean">
          <include refid="enginSql"></include>
    </select>

    <select id="queryOrderExecTotal" resultType="com.zfire.mall.manager.bean.OrderExecBean">
        <if test=" total > 0 and total2 > 0">
            (
        </if>
      <if test=" total > 0">
        <include refid="retailSql"></include>
      </if>
        <if test=" total > 0 and total2 > 0">
            )
        </if>
        <if test=" total > 0 and total2 > 0">
          union
      </if>
        <if test=" total > 0 and total2 > 0">
            (
        </if>
      <if test="total2 > 0">
        <include refid="enginSql"></include>
      </if>
        <if test=" total > 0 and total2 > 0">
            )
        </if>
    </select>

</mapper>