<?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.ruoyi.inventory.mapper.StocktakesMapper">

    <resultMap type="Stocktakes" id="StocktakesResult">
        <result property="id"    column="id"    />
        <result property="stocktakeId"    column="stocktake_id"    />
        <result property="stocktakeType"    column="stocktake_type"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="stocktakeStatus"    column="stocktake_status"    />
        <result property="plannedDate"    column="planned_date"    />
        <result property="materialRange"    column="material_range"    />
        <result property="locationRange"    column="location_range"    />
        <result property="totalVarianceAmount"    column="total_variance_amount"    />
        <result property="remark"    column="remark"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
    </resultMap>



    <sql id="selectStocktakesVo">
        select id, stocktake_id, stocktake_type, warehouse_id, stocktake_status, planned_date, material_range, location_range, total_variance_amount, remark, is_used, sort_no, create_time, create_user_code, update_time, update_user_code from stocktakes
    </sql>

    <select id="selectStocktakesList" parameterType="Stocktakes" resultMap="StocktakesResult">
        <include refid="selectStocktakesVo"/>
        where is_used = 1
            <if test="stocktakeId != null  and stocktakeId != ''"> and stocktake_id = #{stocktakeId}</if>
            <if test="stocktakeType != null  and stocktakeType != ''"> and stocktake_type = #{stocktakeType}</if>
            <if test="warehouseId != null  and warehouseId != ''"> and warehouse_id = #{warehouseId}</if>
            <if test="stocktakeStatus != null  and stocktakeStatus != ''"> and stocktake_status = #{stocktakeStatus}</if>

            <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
                AND date_format(planned_date,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
            </if>
            <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
                AND date_format(planned_date,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
            </if>
            <if test="materialRange != null  and materialRange != ''"> and material_range = #{materialRange}</if>
            <if test="locationRange != null  and locationRange != ''"> and location_range = #{locationRange}</if>
            <if test="totalVarianceAmount != null "> and total_variance_amount = #{totalVarianceAmount}</if>
            <if test="isUsed != null "> and is_used = #{isUsed}</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="createUserCode != null  and createUserCode != ''"> and create_user_code = #{createUserCode}</if>
            <if test="updateUserCode != null  and updateUserCode != ''"> and update_user_code = #{updateUserCode}</if>
             order by create_time,planned_date,update_time desc
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <select id="selectStocktakesById" parameterType="String" resultMap="StocktakesResult">
        select id, stocktake_id, stocktake_type, warehouse_id, stocktake_status, planned_date, material_range, location_range
             , total_variance_amount, remark, is_used, sort_no, create_time, create_user_code, update_time, update_user_code
        from stocktakes
        where id = #{id}
    </select>
<!--    查询处理统计-->
    <select id="selectStocktakesListCount" resultType="com.ruoyi.inventory.domain.TO.StocktakesVo">
        select
        st_it.material_id as materialId
        ,ma.sap_no as sapNo
        ,GROUP_CONCAT(DISTINCT date_format(st.planned_date,'%Y%m%d') SEPARATOR ',') as plannedDates
        ,GROUP_CONCAT(DISTINCT w.warehouses_name SEPARATOR ',')  as warehousesName
        ,GROUP_CONCAT(DISTINCT sl.location_name SEPARATOR ',')  as locationName
        ,GROUP_CONCAT(DISTINCT st_it.warehouse_id SEPARATOR ',')  as warehousesId
        ,GROUP_CONCAT(DISTINCT st_it.location_id SEPARATOR ',')  as locationId
        ,ma.ts_code as tsCode
        ,GROUP_CONCAT(DISTINCT dict.dict_label SEPARATOR ',')  as hazardName
        ,IFNULL(sum(st_it.variance_quantity), '0') as countQuantity
        ,IFNULL(sum(st_it.variance_amount), '0') as countAmount
        ,GROUP_CONCAT(DISTINCT dict1.dict_label SEPARATOR ',')  as adjustedType
        from  stocktakes st
        left join stocktake_items st_it on st.id = st_it.stocktake_id and st_it.is_used = 1
        left join materials ma on st_it.material_id = ma.id  and ma.is_used = 1
        left join warehouses w on st_it.warehouse_id = w.id  and w.is_used = 1
        left join storage_locations sl on st_it.location_id = sl.id  and sl.is_used = 1
        left join sys_dict_data dict on ma.hazard_id = dict.dict_sort  and dict.status =0 and dict.dict_type ='danger_type'
        left join sys_dict_data dict1 on st_it.adjusted_type = dict1.dict_sort  and dict1.status =0 and dict1.dict_type ='adjusted_type'
        where
        st.is_used = 1 and IFNULL(st_it.variance_quantity, 0) != 0
        <if test="materialId != null "> and st_it.material_id = #{materialId}</if>
        <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
            AND date_format(st.planned_date,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
        </if>
        <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
            AND date_format(st.planned_date,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
        </if>
        <if test="warehouseId != null "> and st_it.warehouse_id = #{warehouseId}</if>
        <if test="locationId != null "> and st_it.location_id = #{locationId}</if>
        group by st_it.material_id
    </select>
<!--    首页-本月盘点次数-->
    <select id="selectStocktakesCountByMonth" parameterType="Stocktakes"  resultType="java.lang.Integer">
        select count(*) from stocktakes
        where is_used = 1
          AND date_format(planned_date,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
          AND date_format(planned_date,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
    </select>


    <insert id="insertStocktakes" parameterType="Stocktakes">
        insert into stocktakes
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="stocktakeId != null">stocktake_id,</if>
            <if test="stocktakeType != null">stocktake_type,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="stocktakeStatus != null">stocktake_status,</if>
            <if test="plannedDate != null">planned_date,</if>
            <if test="materialRange != null">material_range,</if>
            <if test="locationRange != null">location_range,</if>
            <if test="totalVarianceAmount != null">total_variance_amount,</if>
            <if test="remark != null">remark,</if>
            <if test="isUsed != null">is_used,</if>
            <if test="sortNo != null">sort_no,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createUserCode != null">create_user_code,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateUserCode != null">update_user_code,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="stocktakeId != null">#{stocktakeId},</if>
            <if test="stocktakeType != null">#{stocktakeType},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="stocktakeStatus != null">#{stocktakeStatus},</if>
            <if test="plannedDate != null">#{plannedDate},</if>
            <if test="materialRange != null">#{materialRange},</if>
            <if test="locationRange != null">#{locationRange},</if>
            <if test="totalVarianceAmount != null">#{totalVarianceAmount},</if>
            <if test="remark != null">#{remark},</if>
            <if test="isUsed != null">#{isUsed},</if>
            <if test="sortNo != null">#{sortNo},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createUserCode != null">#{createUserCode},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateUserCode != null">#{updateUserCode},</if>
         </trim>
    </insert>

    <update id="updateStocktakes" parameterType="Stocktakes">
        update stocktakes
        <trim prefix="SET" suffixOverrides=",">
            <if test="stocktakeId != null">stocktake_id = #{stocktakeId},</if>
            <if test="stocktakeType != null">stocktake_type = #{stocktakeType},</if>
            <if test="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="stocktakeStatus != null">stocktake_status = #{stocktakeStatus},</if>
            <if test="plannedDate != null">planned_date = #{plannedDate},</if>
            <if test="materialRange != null">material_range = #{materialRange},</if>
            <if test="locationRange != null">location_range = #{locationRange},</if>
            <if test="totalVarianceAmount != null">total_variance_amount = #{totalVarianceAmount},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="isUsed != null">is_used = #{isUsed},</if>
            <if test="sortNo != null">sort_no = #{sortNo},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createUserCode != null">create_user_code = #{createUserCode},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateUserCode != null">update_user_code = #{updateUserCode},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteStocktakesById" parameterType="String">
        delete from stocktakes where id = #{id}
    </delete>

    <update id="deleteStocktakesByIds" parameterType="String">
        update stocktakes
        set is_used = 0
        where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <update id="deleteStocktakeItemsByStocktakeIds" parameterType="String">
        update stocktake_items
        set is_used = 0
        where stocktake_id in
        <foreach item="stocktakeId" collection="array" open="(" separator="," close=")">
            #{stocktakeId}
        </foreach>
    </update>

    <delete id="deleteStocktakeItemsByStocktakeId" parameterType="String">
        delete from stocktake_items where stocktake_id = #{stocktakeId}
    </delete>

    <insert id="batchStocktakeItems">
        insert into stocktake_items( id, stocktake_id, inventory_id, material_id, batch_code, warehouse_id, location_id, system_quantity, actual_quantity, variance_quantity, variance_amount, stocktake_status, counted_by, counted_at, adjusted, adjustment_reason, adjusted_by, adjusted_at, start_time, end_time, material_range, location_range, remark, is_used, sort_no, create_time, create_user_code, update_time, update_user_code) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.stocktakeId}, #{item.inventoryId}, #{item.materialId}, #{item.batchCode}, #{item.warehouseId}, #{item.locationId}, #{item.systemQuantity}, #{item.actualQuantity}, #{item.varianceQuantity}, #{item.varianceAmount}, #{item.stocktakeStatus}, #{item.countedBy}, #{item.countedAt}, #{item.adjusted}, #{item.adjustmentReason}, #{item.adjustedBy}, #{item.adjustedAt}, #{item.startTime}, #{item.endTime}, #{item.materialRange}, #{item.locationRange}, #{item.remark}, #{item.isUsed}, #{item.sortNo}, #{item.createTime}, #{item.createUserCode}, #{item.updateTime}, #{item.updateUserCode})
        </foreach>
    </insert>
</mapper>