<?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.czg.service.product.mapper.ConsStockFlowMapper">

    <select id="getConsStatistics" resultType="com.czg.product.vo.ConsStatisticsVo">
        SELECT
        sum( CASE WHEN t1.in_out_type = 'in' then t1.in_out_number end) AS inSumTotal,
        sum( CASE WHEN t1.in_out_item = 'win-in' THEN t1.in_out_number END ) AS winInNum,
        sum( CASE WHEN t1.in_out_item = 'manual-in' THEN t1.in_out_number END ) AS stockInNum,
        sum( CASE WHEN t1.in_out_type = 'out' then abs(t1.in_out_number) end) AS outSumTotal,
        sum( CASE WHEN t1.in_out_item = 'loss-out' THEN abs(t1.in_out_number) END ) AS lossOutNum,
        abs(sum( CASE WHEN t1.in_out_item = 'order-out' THEN t1.in_out_number END )) AS consumeNum,
        sum( CASE WHEN t1.in_out_item = 'damage-out' THEN abs(t1.in_out_number) END ) AS damageNum,
        sum( CASE WHEN t1.in_out_item = 'manual-out' THEN abs(t1.in_out_number) END ) AS stockOutNum
        FROM
        tb_cons_stock_flow t1
        LEFT JOIN tb_cons_info t2 on t1.con_id = t2.id
        where t1.shop_id = #{shopId}
        <if test="id != null">
            and t1.con_id = #{id}
        </if>
        <if test="consGroupId != null">
            and t2.cons_group_id = #{consGroupId}
        </if>
        <if test="conName != null and conName != ''">
            and t1.con_name like concat('%', #{conName}, '%')
        </if>
        <if test="beginTime != null and beginTime != ''">
            and t1.create_time >= str_to_date(#{beginTime}, '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="endTime != null and endTime != ''">
            <![CDATA[
            and t1.create_time <= str_to_date(#{endTime}, '%Y-%m-%d %H:%i:%s')
            ]]>
        </if>
    </select>
    <select id="selectByVendorId" resultType="com.czg.product.vo.ShopVendorBillRecordVO">
        SELECT tb_cons_stock_flow.id,
        tb_cons_stock_flow.con_name,
        tb_cons_stock_flow.purchase_price,
        tb_cons_stock_flow.in_out_number,
        tb_cons_stock_flow.amount_payable,
        tb_cons_stock_flow.actual_payment_amount,
        tb_cons_stock_flow.remark,
        tb_cons_stock_flow.create_time,
        (tb_cons_stock_flow.amount_payable - tb_cons_stock_flow.actual_payment_amount) as unPaidAmount
        FROM tb_cons_stock_flow
        left join tb_shop_vendor on tb_cons_stock_flow.vendor_id = tb_shop_vendor.id
        WHERE tb_cons_stock_flow.shop_id = #{shopId}
        and tb_cons_stock_flow.vendor_id = #{vendorId}
        <if test="key != null and key != ''">
            and tb_shop_vendor.`name` like concat('%', #{key}, '%')
        </if>
    </select>

    <select id="selectUnPaid" resultType="com.czg.product.entity.ConsStockFlow">
        select * from tb_cons_stock_flow where amount_payable > actual_payment_amount and shop_id=#{shopId}
        <foreach collection="vendorIds" item="id" open="and id in (" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="findConsStockFlowPage" resultType="com.czg.product.dto.ConsStockFlowDTO">
        select a.*, b.name as productName, c.`name` as vendorName from tb_cons_stock_flow as a
        left join tb_shop_vendor as c on c.id=a.vendor_id
        left join tb_product as b on a.product_id = b.id

                 where a.shop_id=#{shopId}
        <if test="inOutType != null and inOutType != ''">
            and a.in_out_type = #{inOutType}
        </if>
        <if test="inOutItem != null and inOutItem != ''">
            and a.in_out_item = #{inOutItem}
        </if>
        <if test="conId != null">
            and a.con_id = #{conId}
        </if>
        order by a.id desc
    </select>
</mapper>
