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

    <select id="pickCount" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
        sum(iao.refundable_qty) 'pickC'
        FROM
        pick_manager_ship pms
        LEFT JOIN pick_manager pm ON pm.id = pms.pick_id
        LEFT JOIN invoice_application_order iao ON iao.id = pms.invoice_order_id
        LEFT JOIN invoice_application ia on iao.invoice_id = ia.id
        LEFT JOIN king_dee_material kdm on iao.material_id = kdm.id
        LEFT JOIN king_dee_category kdc on kdm.category_id = kdc.id
        join customer_product_type_rela cptr on iao.customer_wallet_id = cptr.customer_wallet_id
        where 1=1
        and ia.type != 2
        <if test="invoiceId != null and invoiceId != ''">
            and iao.invoice_id like concat('%',#{invoiceId},'%')
        </if>
        <if test="createBy != null and createBy != ''">
            and pm.create_by like concat('%',#{createBy},'%')
        </if>
        <if test="id != null and id != ''">
            and pm.id = #{id}
        </if>
        <if test="correspondId != null and correspondId != ''">
            and pm.correspond_id = #{correspondId}
        </if>
        <if test="takerId != null and takerId != ''">
            and pm.taker_id like concat('%',#{takerId},'%')
        </if>
        <if test="pickStartTime != null and pickStartTime != ''">
            and pm.pick_time BETWEEN #{pickStartTime} and #{pickEndTime}
        </if>
        <if test="startTime != null and endTime != ''">
            and pm.create_time BETWEEN #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and ia.customer_id = #{customerId}
        </if>
        <if test="customerName != null and customerName != ''">
            and ia.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and ia.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="status != null">
            and pms.status = #{status}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and cptr.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 iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY pms.id

    </select>


    <select id="saleOrderlistCount" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
            sum(refundable_qty) 'totalC',
            sum(price) 'unitPriceC',
            sum(pay_amount) 'orderAmountC'
        FROM
            sales_order so
        LEFT JOIN sales_order_item iao ON so.id = iao.sales_order_no
        where 1=1
        <if test="orderNo != null and orderNo != ''">
            and so.order_no like concat('%',#{orderNo},'%')
        </if>
        <if test="customerName != null and customerName != ''">
            and so.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="correspondId != null and correspondId != ''">
            and so.correspond_id = #{correspondId}
        </if>
        <if test="specification != null and specification != ''">
            and iao.specification like concat('%',#{specification},'%')
        </if>
        <if test="customerNumber != null and customerNumber != ''">
            and so.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="examineStatus != null and examineStatus != ''">
            and so.examine_status = #{examineStatus}
        </if>
        <if test="materialName != null and materialName != ''">
            and iao.material_name like concat('%',#{materialName},'%')
        </if>
        <if test="correspondName != null and correspondName != ''">
            and so.correspond_name like concat('%',#{correspondName},'%')
        </if>
        <if test="materialNumber != null and materialNumber != ''">
            and iao.material_code like concat('%',#{materialNumber},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            and so.order_time between #{startTime} and #{endTime}
        </if>
        <if test="customerId != null and customerId != ''">
            and so.customer_id = #{customerId}
        </if>
        <if test="serviceIds != null and serviceIds.size > 0">
            and iao.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 iao.k3_category_id in
            <foreach item="item" index="index" collection="k3CategoryIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        order by so.create_time desc
    </select>

    <select id="retailOrderListCount" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        select
            round(b.has_send_qty/b.qty*100,2) 'thjd',
            sum(price)'unitPriceC',
            sum(b.qty) 'totalC',
            sum(b.pay_amount) 'payAmountC',
            sum(b.pay_rebate_amount) 'rebateAmountC',
            sum(b.total_disc_amount) 'discAmountC',
            (b.refundable_qty - (SELECT IFNULL(SUM(iao.refundable_qty),0)
            FROM invoice_application ia
            LEFT JOIN invoice_application_order iao on ia.id = iao.invoice_id
            where ia.examine_status	in ('SAVE','WAIT')
            and iao.order_id =b.id)) as refundable_qty,
            sum(b.direct_transfer_qty) 'directC',
            sum(b.has_send_qty) 'deliverC'
        from retail_order a
        join retail_order_item b on a.id = b.retail_order_id
        <where>
            <if test="id != null and id !=''">
                and a.id like concat('%',#{id},'%')
            </if>
            <if test="serviceId != null and serviceId !=''">
                and b.service_id = #{serviceId}
            </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.create_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_by 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="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 b.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by a.create_time desc
    </select>


    <select id="enginOrderCount" resultType="com.zfire.mall.manager.bean.PoySaleCountBean">
        SELECT
        sum(b.qty) 'totalC',
        sum(b.price) 'unitPriceC',
        sum(b.disc_amount) 'discAmountC',
        sum(b.rebate_amount) 'rebateAmountC',
        sum(b.direct_transfer_qty) 'directC',
        sum(b.has_send_qty) 'sendQtyC',
        sum(b.pay_amount) 'payAmountC',
        (b.refundable_qty - (SELECT IFNULL(SUM(iao.refundable_qty),0) FROM invoice_application ia
        LEFT JOIN invoice_application_order iao on ia.id = iao.invoice_id
        where ia.examine_status	in ('SAVE','WAIT')
        and iao.order_id =b.id)) as refundable_qty,
        b.retired_qty
        FROM
        engin_order a JOIN engin_order_item b ON a.engin_order_id = b.parent_id
        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword},'%') OR a.customer_number LIKE CONCAT('%', #{customerKeyword},'%'))
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                AND a.engin_order_type = #{enginOrderType}
            </if>
            <!--            <if test="enginOrderNo != null and enginOrderNo != ''">-->
            <!--                AND a.engin_order_no = #{enginOrderNo}-->
            <!--            </if>-->
            <if test="enginOrderNo != null and enginOrderNo.size > 0">
                AND a.engin_order_no in
                <foreach item="item" index="index" collection="enginOrderNo" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo != ''">
                AND a.ref_engin_record_no = #{refEnginRecordNo}
            </if>
            <if test="refProjectName != null and refProjectName != ''">
                AND a.ref_project_name = #{refProjectName}
            </if>
            <if test="refUseUnit != null and refUseUnit != ''">
                AND a.ref_use_unit = #{refUseUnit}
            </if>
            <if test="startCreateTime != null and startCreateTime != ''">
                AND a.create_time <![CDATA[ >= ]]> #{startCreateTime}
            </if>
            <if test="endCreateTime != null and endCreateTime != ''">
                AND a.create_time <![CDATA[ <= ]]> #{endCreateTime}
            </if>
            <if test="saleTypeId != null and saleTypeId != ''">
                AND b.sale_type_id = #{saleTypeId}
            </if>
            <if test="createName != null and createName != ''">
                AND a.create_name LIKE CONCAT('%', #{createName},'%')
            </if>
            <if test="confirmName != null and confirmName != ''">
                AND a.confirm_name LIKE CONCAT('%', #{confirmName},'%')
            </if>
            <if test="serviceId != null and serviceId != ''">
                AND a.service_id = #{serviceId}
            </if>
            <if test="examineStatus != null and examineStatus.size > 0">
                AND a.examine_status in
                <foreach item="item" index="index" collection="examineStatus" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </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 b.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        <if test="examineStatus != null and examineStatus.size == 1 and examineStatus.contains(&quot;WAIT&quot;)">
            GROUP BY a.engin_order_id
        </if>
        ORDER BY a.create_time DESC, b.id DESC
    </select>

    <select id="queryKingDeeCustomer" resultType="com.zfire.mall.manager.plus.entity.KingDeeCustomer">
        select
            a.*
            from king_dee_customer a
        left join admin_user b on a.id = b.customer_id and b.is_master=1
        left join admin_user_websit_rela c on c.admin_user_id = b.admin_user_id
        left join admin_websit d on d.admin_websit_id = c.admin_websit_id
        <where>
          <if test="keyword != null and keyword !=''">
              and (a.name like concat('%',#{keyword},'%') or a.number like concat('%',#{keyword},'%'))
          </if>
          <if test="region != null and region != ''">
              and a.region like concat('%',#{region},'%')
          </if>
            <if test="jianXianType != null and jianXianType != ''">
                and a.jiaxian_type = #{jianXianType}
            </if>
          <if test="bindUser != null">
              and a.bind_user = #{bindUser}
          </if>
            <if test="notFilterCustomerId != null and notFilterCustomerId !=''">
                or a.id = #{notFilterCustomerId}
            </if>
          <if test="isGroupCompany != null">
              and a.is_group_company = #{isGroupCompany}
          </if>
          <if test="customerId != null and customerId != ''">
              and a.id = #{customerId}
          </if>
          <if test="websitNumber != null and websitNumber != ''">
              and d.websit_number=#{websitNumber}
          </if>
        </where>
        order by a.create_time desc,a.id desc
    </select>
    <select id="listV2" resultType="com.zfire.mall.manager.bean.supplier.KingDeeSupplierVO">
        SELECT
        a.*
        FROM
        king_dee_supplier a
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listCustomerV2" resultType="com.zfire.mall.manager.bean.listV2.OrderPlanCustomerVO">
        SELECT
        a.*
        FROM
        order_plan_customer a
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listLogisticsConfigurationV2"
            resultType="com.zfire.mall.manager.bean.listV2.LogisticsConfigurationVO">
        SELECT
        a.*
        FROM
        logistics_configuration a
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>

</mapper>