<?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.wallet.WalletCommonMapper">

    <select id="queryWalletByCustomerId" resultType="com.zfire.mall.manager.bean.wallet.CustomerWalletBean">
        (select
            a.customer_wallet_id,
            '' as 'customer_wallet_number',
            a.customer_wallet_name,
            a.wallet_rebate_id,
            a.type,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.amount,
            a.used_credit_amount,
            a.free_credit_amount,
            a.create_time,
            a.create_by,
            a.update_time,
            a.update_by,
            b.name,
            '' as 'serviceId',
            '' as 'serviceName',
            '' as 'isZr',
            '' as 'isRebate',
            '' AS 'walletNumber'
        from customer_wallet a
        join wallet_rebate b on a.wallet_rebate_id = b.wallet_rebate_id
        where  a.type='REBATE' and b.status=1
          <if test="customerId !=null and customerId !=''">
              and a.customer_id=#{customerId}
          </if>
            <if test="type != null and type !=''">
                and a.type=#{type}
            </if>
          order by b.create_time desc)
    union all
        select
            a.customer_wallet_id,
            b.number as 'customer_wallet_number',
            a.customer_wallet_name,
            a.wallet_rebate_id,
            a.type,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.amount,
            a.used_credit_amount,
            a.free_credit_amount,
            a.create_time,
            a.create_by,
            a.update_time,
            a.update_by,
            b.name,
            c.service_id as 'serviceId',
            c.service_name as 'serviceName',
            b.is_zr,
            b.is_rebate,
            b.number AS 'walletNumber'
        from customer_wallet a
		  join king_dee_wallet b on a.wallet_rebate_id = b.id
          left join customer_product_type_rela c on c.customer_wallet_id = a.customer_wallet_id
        where a.type!='REBATE' and b.forbid_status='A'
        <if test="customerId !=null and customerId !=''">
            and a.customer_id=#{customerId}
        </if>
        <if test="type != null and type !=''">
            and a.type=#{type}
        </if>
        <if test="minFreeAmount != null">
            and a.free_credit_amount &gt;= #{minFreeAmount}
        </if>
        <if test="maxFreeAmount != null">
            and a.free_credit_amount &lt;= #{maxFreeAmount}
        </if>
<!--        <if test="hasRecord != null and hasRecord == true">-->
<!--            and a.customer_wallet_id in (-->
<!--                select distinct customer_wallet_id from customer_wallet_record-->
<!--            )-->
<!--        </if>-->
    </select>



    <select id="queryWalletDetailByCustomerId" resultType="com.zfire.mall.manager.bean.wallet.CustomerWalletBean">
        (select
            a.customer_wallet_id,
            '' as 'customer_wallet_number',
            a.customer_wallet_name,
            a.wallet_rebate_id,
            a.type,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.amount,
            a.used_credit_amount,
            a.free_credit_amount,
            a.create_time,
            a.create_by,
            a.update_time,
            a.update_by,
            b.name
        from customer_wallet a
        join wallet_rebate b on a.wallet_rebate_id = b.wallet_rebate_id
        where a.customer_wallet_id=#{customerWalletId} and a.type='REBATE'
          order by b.update_time desc)
    union all
        select
            a.customer_wallet_id,
            b.number as 'customer_wallet_number',
            a.customer_wallet_name,
            a.wallet_rebate_id,
            a.type,
            a.customer_id,
            a.customer_number,
            a.customer_name,
            a.amount,
            a.used_credit_amount,
            a.free_credit_amount,
            a.create_time,
            a.create_by,
            a.update_time,
            a.update_by,
            b.name
        from customer_wallet a
				join king_dee_wallet b on a.wallet_rebate_id = b.id
        where a.customer_wallet_id=#{customerWalletId} and a.type!='REBATE'
    </select>


    <update id="reloadCustomerWalletCert">
        update customer_wallet a join (
            select customer_wallet_id,sum(amount) as 'total_amount' from customer_wallet_credit_record
            where now() between start_time and end_time group by customer_wallet_id
        ) b on a.customer_wallet_id = b.customer_wallet_id
        set a.free_credit_amount = b.total_amount - a.used_credit_amount
        <where>
              <if test="customerId != null and customerId !=''">
                  and a.customer_id=#{customerId}
              </if>
        </where>
    </update>



    <select id="queryTotalAmount" resultType="java.util.Map">
        select
            a.customer_id as 'customerId',
            a.customer_wallet_name as 'name',
            sum(amount) 'totalAmount',
            sum(free_credit_amount) 'freezeCreditAmount',
            sum(used_credit_amount) 'usedCreditAmount',
            now() as 'theTime'
        from customer_wallet a
        join king_dee_customer c on c.id = a.customer_id
        left join admin_user au  on  c.id = au.customer_id and au.is_master=1
        left join admin_user_websit_rela auwr  on  auwr.admin_user_id = au.admin_user_id
        where 1=1
        <if test="type != null and type !=''">
            and a.type = #{type}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            and auwr.admin_websit_id in
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        group by a.wallet_rebate_id
        order by a.type
    </select>


    <select id="queryCustomerTotalAmount" resultType="java.util.Map">
        select
            a.customer_id as 'customerId',
            c.number as 'customerNumber',
            c.name as 'customerName',
            sum(free_credit_amount) 'freezeCreditAmount',
            sum(used_credit_amount) 'usedCreditAmount',
            sum(amount) - sum(used_credit_amount) total,
            now() as 'theTime'
        from customer_wallet a
        join king_dee_wallet b on a.wallet_rebate_id = b.id
        join king_dee_customer c on c.id = a.customer_id
        left join admin_user au  on  c.id = au.customer_id and au.is_master=true
        left join admin_user_websit_rela auwr  on  auwr.admin_user_id = au.admin_user_id
        where a.type='COMMONLY'
        <if test="customerNumber != null and customerNumber !=''">
            and c.number like concat('%',#{customerNumber},'%')
        </if>
        <if test="customerName != null and customerName !=''">
            and c.name like concat('%',#{customerName},'%')
        </if>
        <if test="walletId != null and walletId !=''">
            and a.wallet_rebate_id = #{walletId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            and auwr.admin_websit_id in
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>

        group by a.customer_id
    </select>

    <select id="queryCustomerTotalAmountExport" resultType="com.zfire.mall.manager.bean.wallet.CustomerTotalAmountExportBean">
        select
            b.customer_number,
            b.customer_name,
            b.type,
            b.customer_wallet_name,
            c.service_name,
            sum(if(a.amount_type='IN' and flag=1,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=1,a.amount,0)) 'amount1',
            sum(if(a.amount_type='IN' and flag=2,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=2,a.amount,0)) 'amount2',
            sum(if(a.amount_type='IN' and flag=3,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=3,a.amount,0)) 'amount3',
            sum(if(a.amount_type='IN' and flag=4,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=4,a.amount,0)) 'amount4',
            sum(if(a.amount_type='IN' and flag=5,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=5,a.amount,0)) 'amount5',
            sum(if(a.amount_type='IN' and flag=6,a.amount,0)) - sum(if(a.amount_type='OUT' and flag=6,a.amount,0)) 'amount6',
            b.amount - b.used_credit_amount 'amount'
        from customer_wallet_record a
        join customer_wallet b on a.customer_wallet_id=b.customer_wallet_id
        left join customer_product_type_rela c on c.customer_id=b.customer_id and c.customer_wallet_id = b.customer_wallet_id
        join admin_user au  on  b.customer_id = au.customer_id and au.is_master=1
        join admin_user_websit_rela auwr  on  auwr.admin_user_id = au.admin_user_id
        where a.type='COMMONLY' and a.del=0
        <if test="customerNumber != null and customerNumber !=''">
            and b.customer_number like concat('%',#{customerNumber},'%')
        </if>
        <if test="customerName != null and customerName !=''">
            and b.customer_name like concat('%',#{customerName},'%')
        </if>
        <if test="walletId != null and walletId !=''">
            and b.wallet_rebate_id = #{walletId}
        </if>
        <if test="adminWebsitIds != null and adminWebsitIds.size > 0">
            and auwr.admin_websit_id in
            <foreach item="item" index="index" collection="adminWebsitIds" open="(" separator=","
                     close=")">
                #{item}
            </foreach>
        </if>
        <if test="byCustomer == null or byCustomer == false">
            group by a.customer_wallet_id
        </if>
        <if test="byCustomer !=null and byCustomer == true">
            group by b.customer_id
        </if>
    </select>


    <select id="queryRebateWalletSaleTypeList" resultType="com.zfire.mall.manager.bean.wallet.RebateWalletSaleBean">
        select
            a.* ,
            b.name,
            a.rebate_rate,
            c.sale_code as 'sale_type_code',
            c.sale_name as 'sale_type_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
      <where>
          <if test="name != null and name != ''">
              and b.name like concat('%',#{name},'%')
          </if>
          <if test="saleTypeCode != null and saleTypeCode != ''">
              and c.sale_code like concat('%',#{saleTypeCode},'%')
          </if>
          <if test="saleTypeName != null and saleTypeName != ''">
              and c.sale_name like concat('%',#{saleTypeName},'%')
          </if>
          <if test="status != null">
              and a.status = #{status}
          </if>
      </where>
      order by a.create_time desc

    </select>

    <insert id="initCustomerWallet">
        insert into customer_wallet(customer_wallet_id,customer_id,customer_number,customer_name,wallet_rebate_id,customer_wallet_name,type)
        select
        concat(b.id,a.id),
        b.id,
        b.number,
        b.name,
        a.id,
        a.name,
        'COMMONLY'
        from king_dee_wallet a join king_dee_customer b
        left join customer_wallet c on c.wallet_rebate_id = a.id and c.customer_id=b.id
        where c.customer_wallet_id is null
        <if test="customerId != null and customerId !=''">
            and b.id=#{customerId}
        </if>

        union

        select
        concat(b.id,a.wallet_rebate_id),
        b.id,
        b.number,
        b.name,
        a.wallet_rebate_id,
        a.name,
        'REBATE'
        from wallet_rebate a join king_dee_customer b
        left join customer_wallet c on c.wallet_rebate_id = a.wallet_rebate_id and c.customer_id=b.id
        where c.customer_wallet_id is null
        <if test="customerId != null and customerId !=''">
            and b.id=#{customerId}
        </if>
    </insert>


    <insert id="initCustomerWallet2">
        insert into customer_wallet(customer_wallet_id,customer_id,customer_number,customer_name,wallet_rebate_id,customer_wallet_name,type)
    select
        concat(b.id,a.wallet_rebate_id),
        b.id,
        b.number,
        b.name,
        a.wallet_rebate_id,
        a.name,
        'REBATE'
    from wallet_rebate a join king_dee_customer b
    left join customer_wallet c on c.wallet_rebate_id = a.wallet_rebate_id and c.customer_id=b.id
    where c.customer_wallet_id is null
    </insert>


    <select id="amountCount" resultType="com.zfire.mall.manager.bean.wallet.AmountCountBean">
        select
            preInCount + preOutCount as 'preCount',
            orderInCount + orderOutCount as 'orderCOunt',
            payInCount + payOutCount as 'payCount',
            orderInDate + orderOutDate as 'orderDate',
            projectInCount + projectOutCount as 'projectCount',
            otherInCount + otherOutCount as 'otherCount',
            (
              select sum(amount) - sum(used_credit_amount) from customer_wallet where type='COMMONLY'

                    <if test="customerId != null and customerId != ''">
                        and customer_id = #{customerId}
                    </if>

                    <if test="customerNumber != null and customerNumber != ''">
                        and customer_number like concat('%',#{customerNumber},'%')
                    </if>

                    <if test="customerName != null and customerName != ''">
                        and customer_name like concat('%',#{customerName},'%')
                    </if>
                    <if test="customerWalletId != null and customerWalletId != ''">
                        and customer_wallet_id = #{customerWalletId}
                    </if>

                    <if test="walletRebateId != null and walletRebateId != ''">
                        and wallet_rebate_id = #{customerWalletId}
                    </if>

            ) 'totalBalanceAmount',
            inCount,
            outCount
        from  (
                select
                    sum(if(flag=1 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'preInCount',
                    sum(if(flag=2 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'orderInCount',
                    sum(if(flag=3 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'payInCount',
                    sum(if(flag=4 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'orderInDate',
                    sum(if(flag=5 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'projectInCount',
                    sum(if(flag=6 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'otherInCount',
                    sum(if(flag=1 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'preOutCount',
                    sum(if(flag=2 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'orderOutCount',
                    sum(if(flag=3 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'payOutCount',
                    sum(if(flag=4 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'orderOutDate',
                    sum(if(flag=5 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'projectOutCount',
                    sum(if(flag=6 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'otherOutCount',
                    sum(if(type='REBATE' and amount_type = 'IN',amount,0)) 'inCount',
                    sum(if(type='REBATE' and amount_type = 'OUT',-amount,0)) 'outCount'
                from
                customer_wallet_record where del=0

                <if test="customerId != null and customerId != ''">
                    and customer_id = #{customerId}
                </if>

                <if test="customerNumber != null and customerNumber != ''">
                    and customer_code like concat('%',#{customerNumber},'%')
                </if>

                <if test="customerName != null and customerName != ''">
                    and customer_name like concat('%',#{customerName},'%')
                </if>

                <if test="type != null and type != ''">
                    and `type` = #{type}
                 </if>

                <if test="isCustomer != null and isCustomer != '' and isCustomer == true ">
                    and customer_id = #{customerId}
                </if>

                <if test="billType != null and billType != ''">
                    and bill_type = #{billType}
                </if>

                <if test="customerWalletId != null and customerWalletId != ''">
                    and customer_wallet_id = #{customerWalletId}
                </if>

                <if test="walletRebateId != null and walletRebateId != ''">
                    and wallet_rebate_id = #{customerWalletId}
                </if>

                <if test="isReconciliation != null ">
                    and is_reconciliation = #{isReconciliation}
                </if>

                <if test="billNo != null and billNo != ''">
                    and bill_no like concat('%',#{billNo},'%')
                </if>

                <if test="startTime != null and startTime !='' and endTime != null and endTime !=''">
                    and create_time between #{startTime} and #{endTime}
                </if>
        ) t

    </select>


    <select id="queryWalletByCustomerIdRange" resultType="com.zfire.mall.manager.bean.wallet.WalletCountBean">
        select
        *
        from
        ( select
                a.type,
                a.customer_id,
                a.customer_number,
                a.customer_wallet_name 'walletName',
                a.customer_name,
                a.amount,
                a.used_credit_amount,
                a.free_credit_amount
                from customer_wallet a
                join wallet_rebate b on a.wallet_rebate_id = b.wallet_rebate_id
                where a.type='REBATE' and b.status=1
            <if test="startCustomerId !=null and startCustomerId !=''">
                and a.customer_number <![CDATA[ >=  ]]>
                #{startCustomerId}
            </if>
            <if test="endCustomerId !=null and endCustomerId !=''">
                and a.customer_number <![CDATA[ <=  ]]> #{endCustomerId}
            </if>
            union all
            select
                a.type,
                a.customer_id,
                a.customer_number,
                a.customer_wallet_name 'walletName',
                a.customer_name,
                a.amount,
                a.used_credit_amount,
                a.free_credit_amount
            from customer_wallet a
            join king_dee_wallet b on a.wallet_rebate_id = b.id
            left join customer_product_type_rela c on c.customer_wallet_id = a.customer_wallet_id
            where a.type!='REBATE' and b.forbid_status='A'
            <if test="startCustomerId !=null and startCustomerId !=''">
                and a.customer_number <![CDATA[ >=  ]]> #{startCustomerId}
            </if>

            <if test="endCustomerId !=null and endCustomerId !=''">
                and a.customer_number <![CDATA[ <=  ]]> #{endCustomerId}
            </if>
            ) t
             order by t.customer_number,t.type

    </select>


    <select id="queryWalletByCustomerIdRangeCount" resultType="com.zfire.mall.manager.bean.wallet.WalletCountBean">
        select
        a.type,
        a.customer_wallet_name 'walletName',
        a.customer_number 'customerNumber',
        a.customer_name 'customerName',
        sum(a.amount) 'amountC',
        sum(a.used_credit_amount) 'useCreditC',
        sum(a.free_credit_amount) 'freeUseCreditC'
        from customer_wallet a
        join wallet_rebate b on a.wallet_rebate_id = b.wallet_rebate_id
        where  a.type='REBATE' and b.status=1
        <if test="startCustomerId !=null and startCustomerId !=''">
            and a.customer_number <![CDATA[ >=  ]]> #{startCustomerId}
        </if>

        <if test="endCustomerId !=null and endCustomerId !=''">
            and a.customer_number <![CDATA[ <=  ]]> #{endCustomerId}
        </if>
        group by a.customer_number
        union all
        select
        a.type,
        a.customer_wallet_name 'walletName',
        a.customer_number 'customerNumber',
        a.customer_name 'customerName',
        sum(a.amount) 'amountC',
        sum(a.used_credit_amount) 'useCreditC',
        sum(a.free_credit_amount) 'freeuseCreditC'
        from customer_wallet a
        join king_dee_wallet b on a.wallet_rebate_id = b.id
        left join customer_product_type_rela c on c.customer_wallet_id = a.customer_wallet_id
        where a.type!='REBATE' and b.forbid_status='A'
        <if test="startCustomerId !=null and startCustomerId !=''">
            and a.customer_number <![CDATA[ >=  ]]> #{startCustomerId}
        </if>

        <if test="endCustomerId !=null and endCustomerId !=''">
            and a.customer_number <![CDATA[ <=  ]]> #{endCustomerId}
        </if>
        group by a.customer_number
    </select>


    <select id="queryCommonlyMonthCount" resultType="com.zfire.mall.manager.bean.wallet.CustomerCountMonthBean">
        select
            month,customer_id,customer_name,customer_code 'customerNumber',
            preInCount + preOutCount as 'preCount',
            orderInCount + orderOutCount as 'orderCOunt',
            payInCount + payOutCount as 'payCount',
            orderInDate + orderOutDate as 'orderDate',
            projectInCount + projectOutCount as 'projectCount',
            otherInCount + otherOutCount as 'otherCount'
        from  (
        select
            month,customer_id,customer_name,customer_code,
            sum(if(flag=1 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'preInCount',
            sum(if(flag=2 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'orderInCount',
            sum(if(flag=3 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'payInCount',
            sum(if(flag=4 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'orderInDate',
            sum(if(flag=5 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'projectInCount',
            sum(if(flag=6 and type='COMMONLY'  and amount_type ='IN',amount,0)) 'otherInCount',
            sum(if(flag=1 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'preOutCount',
            sum(if(flag=2 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'orderOutCount',
            sum(if(flag=3 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'payOutCount',
            sum(if(flag=4 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'orderOutDate',
            sum(if(flag=5 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'projectOutCount',
            sum(if(flag=6 and type='COMMONLY'  and amount_type ='OUT',-amount,0)) 'otherOutCount'
        from
            customer_wallet_record
        <where>
            <if test="month != null and month != ''">
                and month =#{month}
            </if>
        </where>
        group by customer_id,month
        ) t

    </select>


    <select id="queryrebateMonthCount" resultType="com.zfire.mall.manager.bean.wallet.CustomerCountMonthBean">
        select
            month,customer_id,customer_name,customer_code,
            sum(if(type='REBATE' and amount_type = 'IN',amount,0)) 'inCount',
            sum(if(type='REBATE' and amount_type = 'OUT',-amount,0)) 'outCount',
            sum(if(type='REBATE' ,allowance_amount,0)) 'allowanceCount',
            sum(if(type='REBATE' ,withhold_amount,0)) 'withholdCount'
        from
            customer_wallet_record
        <where>
            <if test="month != null and month != ''">
                and month =#{month}
            </if>
        </where>
        group by customer_id,month

    </select>




</mapper>