<?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.listV2.CommonCMapper">

    <select id="queryComList" resultType="com.zfire.mall.manager.bean.listV2.ComListVO">
          select
            *
          from
            com_list a
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="queryNoticeList" resultType="com.zfire.mall.manager.bean.listV2.NoticeRecordReadVO">
        select
            *
          from
            notice_record a
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="queryK3CutomerList" resultType="com.zfire.mall.manager.bean.listV2.K3CustomerVO">
      select
            a.*,
			b.`status`,
			b.nick_name
          from
            king_dee_customer a
			LEFT JOIN admin_user b on  a.number = b.customer_number
         ${ex.query}
         and a.jiaxian_type = 'KING'
         order by a.create_time desc
    </select>

    <select id="queryCustomerProductTypeRela" resultType="com.zfire.mall.manager.bean.listV2.CustomerProductTypeRelaVO">
        select
            a.*,
            b.name k3_org_name
        from
            customer_product_type_rela a
        left join king_dee_department b on a.k3_org_number=b.number
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="queryK3Material" resultType="com.zfire.mall.manager.bean.listV2.K3MaterialVO">
        select
            a.*
        from
            king_dee_material a
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="queryPolicy" resultType="com.zfire.mall.manager.bean.listV2.PolicyVO">
        select
        a.*
        from
        policy a
        ${ex.query}
        <if test="policyIds2 != null and policyIds2.size > 0">
            and a.id in
            <foreach item="item" index="index" collection="policyIds2" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="flag != null and (flag !=0 or (policyIds != null and policyIds.size > 0))">
            and (
                <if test="policyIds != null and policyIds.size > 0">
                    (a.id in
                    <foreach item="item" index="index" collection="policyIds" open="(" separator=","
                             close=")">
                        #{item}
                    </foreach>
                    and flag =0)
                </if>
                <if test="flag != 0">
                    <if test="policyIds != null and policyIds.size > 0">
                        or
                    </if>
                     a.flag = #{flag}
                </if>
            )
        </if>

        order by a.create_time desc
    </select>

    <select id="queryPolicyCustomer" resultType="com.zfire.mall.manager.bean.listV2.PolicyCustomerVO">
        select
            a.*,
            count(b.id) customerCount
        from
            policy a join policy_customer b on a.id=b.policy_id
         ${ex.query}
         group by a.id
         order by a.create_time desc
    </select>

    <select id="queryWalletRebate" resultType="com.zfire.mall.manager.bean.listV2.WalletRebateVO">
        select
            *
          from
            wallet_rebate a
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="querySaleType" resultType="com.zfire.mall.manager.bean.listV2.SaleTypeVO">
        select
            *
          from
            sale_type a
         ${ex.query}
         order by a.create_time desc
    </select>
    <select id="queryWalletSaleType" resultType="com.zfire.mall.manager.bean.listV2.WalletSaleTypeRelaVO">
        select
            a.* ,
            b.name,
            a.rebate_rate,
            c.sale_code ,
            c.sale_name
        from wallet_rebate_sale_type a
        join wallet_rebate b on a.wallet_rebate_id = b.wallet_rebate_id
        join sale_type c on c.id=a.sale_type_id
         ${ex.query}
         order by a.create_time desc
    </select>

    <select id="queryCustomerWalletCreditRecord" resultType="com.zfire.mall.manager.bean.listV2.CustomerWalletCreditRecordVO">
         select
            customer_wallet_id,
            customer_wallet_id as id,
            customer_id,
            customer_number,
            customer_name,
            customer_wallet_name,
            free_credit_amount 'amount'
        from customer_wallet a
            ${ex.query}
         and type='COMMONLY'
        order by create_time,customer_wallet_id desc

    </select>

    <select id="queryK3Receipt" resultType="com.zfire.mall.manager.bean.listV2.K3ReceiptVO">
        select
            a.*,
            b.name as 'wallet_name',
            c.`name` as 'customerName',
            d.name as 'wlCompanyName',
            e.service_name
          from
            king_dee_receipt a
            join king_dee_wallet b on a.wallet_id=b.id
            join king_dee_customer c on c.id = a.customer_id
            left join king_dee_customer d on d.id=a.wl_company_type
            join customer_product_type_rela e on e.wallet_id=a.wallet_id and e.customer_id=a.customer_id
         ${ex.query}
         <if test="customerId != null and customerId != ''">
             and a.customer_id = #{customerId}
         </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>
         order by a.create_time desc
    </select>

    <select id="queryReceivableItem" resultType="com.zfire.mall.manager.bean.listV2.ReceivableItemVO">
        SELECT
            a.*,
            b.id 'itemId',
            b.total_amount 'itemAmount',
            tax,
            tax_rate,
            b.remark 'itemRemark',
            c.name 'customerName',
            b.project_code,
            b.project_name,
            b.customer_wallet_name
        FROM
        receivable_order a
        JOIN receivable_order_item b ON a.id = b.receivable_order_id
        left join king_dee_customer c on c.number=a.customer_number

        ${ex.query}
        <if test="examineStatus != null and examineStatus != ''">
            and a.examine_status = #{examineStatus}
        </if>
        <if test="customerNumber != null and customerNumber !=''">
            and b.customer_number = #{customerNumber}
        </if>
        order by a.create_time desc
    </select>

    <select id="queryCustomerWalletTransfer" resultType="com.zfire.mall.manager.bean.listV2.CustomerWalletTransferVO">
        select
          a.*
        from
          customer_wallet_transfer a
          ${ex.query}
        <if test="examineStatus != null and examineStatus != ''">
            and a.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </if>
        order by a.create_time desc
    </select>

    <select id="queryRebateOrder" resultType="com.zfire.mall.manager.bean.listV2.RebateOrderVO">
        select
          a.*
        from
          rebate_order_item a
        left join admin_user b on a.customer_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
        ${ex.query}
        <if test="examineStatus != null and examineStatus != ''">
            and a.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
            and a.examine_status not in ('SAVE','WAIT')
        </if>
        <if test="isConfirm != null">
            and a.customer_is_confirm = #{isConfirm}
        </if>
        <if test="websitNumber != null and websitNumber !=''">
            and d.websit_number = #{websitNumber}
        </if>
        order by a.create_time desc
    </select>



    <sql id="retailExecSql">
        select
            b.material_old_number,
            a.create_time,
            a.the_time,
            a.id,
            a.customer_id,
            b.is_displace_order,
            a.customer_number as 'customer_number',
            a.customer_name,
            b.id as 'orderItemId',
            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,
            a.k3_service_name,
            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,
            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
    </sql>
    <select id="queryOrderExecRetail" resultType="com.zfire.mall.manager.bean.listV2.OrderExecRetailVO">
        <include refid="retailExecSql"/>
         ${ex.query} and a.type=1 and a.examine_status='OK'
         <if test="customerId != null and customerId !=''">
             and a.customer_id = #{customerId}
         </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>
        order by a.create_time desc
    </select>
    <select id="queryOrderExecRetailCount" resultType="java.lang.Integer">
        select
          count(1) total
        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
         ${ex.query} and a.type=1  and a.examine_status='OK'
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </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>
    </select>

    <select id="queryOrderExecPolicy" resultType="com.zfire.mall.manager.bean.listV2.OrderExecPolicyVO">
        <include refid="retailExecSql"/>
        ${ex.query} and a.type = 2  and a.examine_status='OK'
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </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>
        order by a.create_time desc
    </select>
    <select id="queryOrderExecPolicyCount" resultType="java.lang.Integer">
        select
          count(1) total
        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
        ${ex.query}  and a.type = 2  and a.examine_status='OK'
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </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>
    </select>


    <sql id="retailOrderSql">
        select
            a.*,
            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
    </sql>
    <select id="queryRetailOrder" resultType="com.zfire.mall.manager.bean.listV2.RetailOrderVO">
        <include refid="retailOrderSql"></include>
        ${ex.query}  and a.type = 1
        <if test="examineStatus != null and examineStatus !=''">
            and a.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </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>
        order by a.create_time desc
    </select>

    <select id="queryPolicyOrder" resultType="com.zfire.mall.manager.bean.listV2.RetailOrderPolicyVO">
        <include refid="retailOrderSql"></include>
        ${ex.query}  and a.type = 2
        <if test="examineStatus != null and examineStatus !=''">
            and a.examine_status = #{examineStatus}
        </if>
        <if test="customerId != null and customerId !=''">
            and a.customer_id = #{customerId}
        </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>
        order by a.create_time desc
    </select>

    <select id="queryEnginOrderCount" resultType="java.lang.Integer">
        select
          count(1) total
        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
        ${ex.query}   and a.examine_status='OK'
        <if test="enginOrderType != null and enginOrderType !=''">
            and a.engin_order_type = #{enginOrderType}
        </if>
        <if test="customerId != null and customerId != ''">
            and a.customer_id = #{customerId}
        </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>
    </select>
    <select id="queryEnginOrder" resultType="com.zfire.mall.manager.bean.listV2.OrderEnginVO">
        select
            b.material_old_number,
            a.create_time,
            a.order_date 'the_time',
            a.engin_order_no 'id',
            a.customer_id,
            b.is_displace_order,
            d.number as 'customer_number',
            a.customer_name,
            b.id as 'orderItemId',
            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,
            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
        ${ex.query}  and a.examine_status='OK'
        <if test="enginOrderType != null and enginOrderType !=''">
            and a.engin_order_type = #{enginOrderType}
        </if>
        <if test="customerId != null and customerId != ''">
            and a.customer_id = #{customerId}
        </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>
        order by a.create_time desc
    </select>



    <select id="queryCustomerWalletRecord" resultType="com.zfire.mall.manager.bean.listV2.CustomerWalletRecordVO">
        select
            *
          from
            customer_wallet_record a
         ${ex.query}
         order by a.create_time desc
    </select>
    <select id="stageListV2" resultType="com.zfire.mall.manager.bean.listV2.StageK3CustomerVO">
            select
            *
          from
            king_dee_customer a
         ${ex.query}
         and a.jiaxian_type in ('JIAXIAN','GONGCHENG')
        <if test="customerId != null and customerId != ''">
            and a.higher_prant_number = #{customerId}
        </if>
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
    </select>


    <select id="listPush" resultType="com.zfire.mall.manager.bean.front.CustomerPushBean">
 select
        *
        from
        king_dee_customer a
        where a.sync_status != 'OK'
        and a.forbid_status ='A'
        and ((a.jiaxian_type = 'JIAXIAN' and a.examine_status ='OK') or a.jiaxian_type='KING')
    </select>


    <select id="jiaXianV2" resultType="com.zfire.mall.manager.bean.listV2.K3CustomerJiaXianVO">
       select
       a.id,
            a.number,
			a.`name`

          from
			king_dee_customer a
         ${ex.query}
         order by a.create_time desc
    </select>
    <select id="queryListV3K3CutomerList" resultType="com.zfire.mall.manager.bean.listV2.K3CustomerVO">
         select
            a.*,
			b.`status`,
			b.nick_name
          from
            king_dee_customer a
			LEFT JOIN admin_user b on  a.number = b.user_name
         ${ex.query}
         order by a.create_time desc
    </select>


    <select id="queryMaterialReferPriceList" resultType="com.zfire.mall.manager.bean.listV2.MaterialReferPriceVO">
         select
            a.*,
            b.name,
            b.old_number,
            b.category_id,
            b.category_name,
            b.specification
          from
            material_refer_price a
			 JOIN king_dee_material b on a.material_number = b.number
         ${ex.query}
         order by a.create_time desc
    </select>


    <select id="queryCustomerWalletAmountList" resultType="com.zfire.mall.manager.bean.listV2.CustomerWalletAmountVO">
        select
            b.customer_wallet_id,
            b.customer_wallet_name,
            b.customer_number,
            b.customer_name,
            b.type,
            ifnull(a.balance_amount,0) balance_amount
        from (
            select
            id,
            customer_wallet_id,
            balance_amount,
            create_time,
            row_number() over(PARTITION by customer_wallet_id order by id desc) as row_num
            from customer_wallet_record where create_time &lt;= #{time}
        ) a
        right join customer_wallet b on a.customer_wallet_id = b.customer_wallet_id and a.row_num=1
        ${ex.query}
        order by b.customer_number,b.type
    </select>

</mapper>