<?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.cftech.common.promotionstock.dao.PromotionStockMapper">

    <resultMap id="resultMap" type="com.cftech.common.promotionstock.model.PromotionStock">
        <id column="id" property="id"/>
        <result column="item_id" property="itemId"/>
        <result column="change_num" property="changeNum"/>
        <result column="create_by" property="createBy"/>
        <result column="create_time" property="createTime"/>
        <result column="after_change_stock" property="afterChangeStock"/>
        <result column="type" property="type"/>
        <result column="status" property="status"/>
        <result column="form_id" property="formId"/>
    </resultMap>

    <sql id="sqlWhere">
        <if test="conds!=null">
            <trim prefix="WHERE" prefixOverrides="AND|OR">
                <foreach collection="conds.conds" index="index" item="cond">
                    ${cond.linkType}
                    <if test="cond.condType == 'EQUAL'">${cond.param} = #{cond.value}</if>
                    <if test="cond.condType == 'NOTEQUAL'">${cond.param} &lt;&gt; #{cond.value}</if>
                    <if test="cond.condType == 'GREATEQUAL'">${cond.param} &gt;= #{cond.value}</if>
                    <if test="cond.condType == 'GREATTHAN'">${cond.param} &gt; #{cond.value}</if>
                    <if test="cond.condType == 'LESSEQUAL'">${cond.param} &lt;= #{cond.value}</if>
                    <if test="cond.condType == 'LESSTHAN'">${cond.param} &lt; #{cond.value}</if>
                    <if test="cond.condType == 'BETWEEN'">${cond.param} BETWEEN #{cond.startValue} AND
                        #{cond.endValue}
                    </if>
                    <if test="cond.condType == 'ISNULL'">${cond.param} IS NULL</if>
                    <if test="cond.condType == 'NOTNULL'">${cond.param} IS NOT NULL</if>
                    <if test="cond.condType == 'LIKE'">${cond.param} LIKE #{cond.value}</if>
                    <if test="cond.condType == 'IN'">${cond.param} IN
                        <foreach item="item" index="index" collection="cond.value" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </if>
                </foreach>
            </trim>
        </if>
    </sql>

    <sql id="sqlColumns">
                                    id,
                                                item_id,
                                                change_num,
                                                create_by,
                                                create_time,
                                                after_change_stock,
                                                type,status,form_id
                        </sql>


    <insert id="save" parameterType="com.cftech.common.marketing.model.Marketing" useGeneratedKeys="true"
            keyProperty="id">
        insert into wx_mp_common_stock
        (
        <include refid="sqlColumns"/>
        )
        values
        (
        #{id, jdbcType=BIGINT},
        #{itemId, jdbcType=BIGINT},
        #{changeNum, jdbcType=INTEGER},
        #{createBy,jdbcType=BIGINT},
        now(),
        #{afterChangeStock, jdbcType=INTEGER},
        #{type, jdbcType=INTEGER},
        #{status,jdbcType=INTEGER},
        #{formId,jdbcType=VARCHAR}
        )
    </insert>

    <select id="fetchById" parameterType="java.lang.Long" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM wx_mp_common_stock t
        WHERE t.id=#{id}
    </select>

    <select id="count" parameterType="java.util.Map" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM wx_mp_common_stock
        <include refid="sqlWhere"/>

    </select>

    <select id="fetchSearchByPage" parameterType="java.util.Map" resultMap="resultMap">
        SELECT
        <include refid="sqlColumns"/>
        FROM wx_mp_common_stock
        <include refid="sqlWhere"/>
        <if test="sort!=null">ORDER BY ${sort.param} ${sort.type}</if>
        <if test="limit>0">limit #{offset},#{limit}</if>
    </select>

    <select id="fetchPStockStatistical" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.PStockStatisticalDto">
        select grantItems.price,grantItems.itemName,
        stock.item_id itemId,
        stock.change_num changeNum,
        stock.after_change_stock afterChangeStock,
        stock.type,stock.id stockId,appform.dis_logistics_price disLogisticsPrice,appform.id appformId,
        stock.status
        from wx_mp_common_grantitems grantItems
        inner join wx_mp_common_stock stock on grantItems.appitem = stock.item_id
        inner join wx_mp_common_appform appform on appform.id = grantItems.appform
        where grantItems.grant_time &gt;= #{startDate} and grantItems.grant_time &lt;=#{endDate}
<!--        <if test="isManager!=null and isManager=='1'.toString()">-->
<!--            and grantItems.manager_grant_time &gt;= #{startDate} and grantItems.manager_grant_time &lt;=#{endDate}-->
<!--            and (appform.issuing_state=2 or appform.issuing_state = 3)-->
<!--        </if>-->
<!--        <if test="isManager!=null and isManager=='0'.toString()">-->
<!--            and grantItems.dis_grant_time &gt;= #{startDate} and grantItems.dis_grant_time &lt;=#{endDate}-->
<!--            and (appform.issuing_state=1 or appform.issuing_state = 3)-->
<!--        </if>-->
        order by stock.create_time desc;
    </select>

<!--    <sql id="managerStartAndEndTime">-->
<!--        and ((t.manager_grant_time &gt;=#{startDate} and t.manager_grant_time &lt;= #{endDate}))-->
<!--    </sql>-->
<!--    <sql id="supplierStartAndEndTime">-->
<!--        and t.dis_grant_time &gt;=#{startDate} and t.dis_grant_time &lt;= #{endDate}-->
<!--    </sql>-->

    <select id="managerGrantDetail" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.GrantDetailDto">
        -- 用于计算管理员发货明细及发货金额
        select sum(t.manager_grant_count) grantCount,itemname,sum(t.manager_grant_count)*price totalPrice from wx_mp_common_grantitems t

        where t.manager_grant_count is not null
        and t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}
        group by t.appitem;
    </select>

    <select id="managerAfterStock" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.AfterStockDto">
        -- 用于统计管理员剩余库存明细及库存金额
        select appitem.name itemname,stock.after_change_stock afterChangeStock,appitem.price*after_change_stock totalPrice from wx_mp_common_stock stock
        left join (select DISTINCT appform,delivery_time from wx_mp_common_grantitems) g on g.appform = stock.form_id
        inner join wx_mp_common_appitems appitem on stock.item_id = appitem.id
        where type =  10 and stock.create_time &lt;= #{endDate} and (g.delivery_time &lt;=#{endDate} or g.delivery_time is null)
        group by stock.item_id
        order by g.delivery_time  desc
    </select>

    <select id="supplierGrantDetail" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.GrantDetailDto">
        -- 用于统计供应发货明细及发货金额
        select sum(t.dis_grant_count) grantCount,t.itemname,sum(t.dis_grant_count)*price totalPrice from wx_mp_common_grantitems t
        where t.dis_grant_count is not null
        and t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}

        group by appitem;
    </select>

    <select id="supplierLogisticsPrice" parameterType="java.util.Map" resultType="java.math.BigDecimal">
        -- 供应商物流金额
        select sum(dis_logistics_price) from (
        select dis_logistics_price from wx_mp_common_grantitems t
        left join wx_mp_common_appform appform on appform.id = t.appform
        where dis_grant_count is not null
         and t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}
        group by appform) a
    </select>

    <select id="deptGrantDetails" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.GrantDetailDto">
     select t.itemname,sum(t.reality_grant_count) grantCount,
        appform.dname,
        ROUND(sum(
        t.reality_grant_count*t.price
        ),2)
         totalPrice
        from wx_mp_common_appform appform
        inner join wx_mp_common_grantitems t on t.appform = appform.id
        WHERE
        t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}
        GROUP BY appform.dname,t.appitem
    </select>

    <select id="useGrantDetails" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.GrantDetailDto">
         select t.itemname,sum(t.reality_grant_count) grantCount,
        appform.uname,
        ROUND(sum(
        t.reality_grant_count*t.price
        ),2)
         totalPrice
        from wx_mp_common_appform appform
        inner join wx_mp_common_grantitems t on t.appform = appform.id
        where
        t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}
        GROUP BY appform.uname,t.appitem
    </select>

    <select id="managerEvaluate" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.EvalityNumberDto">
        select count(1) count
        ,avg(manager_all_evaluate) AllEvaluateCount,
        avg(manager_quality_evaluate) QualityEvaluateCount,
        avg(manager_logistics_evaluate) LogisticsEvaluateCount
        from wx_mp_common_appform appform
        where appform.manager_quality_time &gt;= #{startDate} and appform.manager_quality_time &lt;= #{endDate}
    </select>

    <select id="applyEvaluate" parameterType="java.util.Map" resultType="com.cftech.common.promotionstock.model.EvalityNumberDto">
        select count(1) count
        ,avg(apply_all_evaluate) AllEvaluateCount,
        avg(apply_quality_evaluate) QualityEvaluateCount,
        avg(apply_logistics_evaluate) LogisticsEvaluateCount
        from wx_mp_common_appform appform
        where appform.manager_quality_time &gt;= #{startDate} and appform.manager_quality_time &lt;=#{endDate}
    </select>

    <select id="managerConfirm" parameterType="java.util.Map" resultType="java.lang.Integer">
        select count(1) from wx_mp_common_appform
        where manager_confirm_time &gt;= #{startDate} and manager_confirm_time &lt;=#{endDate}
    </select>

    <select id="fetchSearchByPageByAppform" parameterType="java.util.Map"
            resultMap="com.cftech.common.typeappform.dao.TypeAppFormMapper.resultMap">
       SELECT
        appform.*
        FROM wx_mp_common_appform appform
        inner join wx_mp_common_grantitems t on appform.id = t.appform
        <include refid="sqlWhere"/>
        and t.delivery_time &gt;=#{startDate} and t.delivery_time &lt;= #{endDate}
        group by appform.id
    </select>


    <select id="getSupplierStockStatistics" parameterType="java.util.Map"
            resultType="com.cftech.common.promotionstock.model.SupplierStockDto">
        select wmca.name,wmca.dis_stock disStock,
        IFNULL((select sum(change_num) from wx_mp_common_stock as wmcs
        where wmcs.type = 20
        and wmcs.change_num &gt;0
        and year(wmcs.create_time) = DATE_FORMAT(NOW(), '%Y')
        and wmcs.item_id = wmca.id
        ),0) as yearAddStock,
        IFNULL((select sum(ABS(change_num)) from wx_mp_common_stock as wmcs
        where wmcs.type = 20
        and wmcs.change_num &lt;0
        and wmcs.item_id = wmca.id
        ),0) as grantCount
        from wx_mp_common_appitems as wmca
        where wmca.del_flag = 0 and (wmca.is_online = 1 or  (wmca.is_online = 0 and dis_stock >0))
        order by number asc
    </select>


</mapper>
