<?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.StocktakeItemsMapper">

    <resultMap type="com.ruoyi.inventory.domain.TO.StocktakeItemsTo" id="StocktakeItemsResult">
        <result property="id"    column="id"    />
        <result property="stocktakeId"    column="stocktake_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchCode"    column="batch_code"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="locationId"    column="location_id"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="locationName"    column="location_name"    />
        <result property="systemQuantity"    column="system_quantity"    />
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="varianceQuantity"    column="variance_quantity"    />
        <result property="varianceAmount"    column="variance_amount"    />
        <result property="stocktakeStatus"    column="stocktake_status"    />
        <result property="countedBy"    column="counted_by"    />
        <result property="countedAt"    column="counted_at"    />
        <result property="adjusted"    column="adjusted"    />
        <result property="adjustmentReason"    column="adjustment_reason"    />
        <result property="adjustedBy"    column="adjusted_by"    />
        <result property="adjustedAt"    column="adjusted_at"    />
        <result property="startTime"    column="start_time"    />
        <result property="endTime"    column="end_time"    />
        <result property="materialRange"    column="material_range"    />
        <result property="locationRange"    column="location_range"    />
        <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"    />
        <result property="inventoryId"    column="inventory_id"    />
        <result property="unitPrice"    column="unit_price"    />
        <result property="adjustedType"    column="adjusted_type"    />
    </resultMap>

    <sql id="selectStocktakeItemsVo">
        select id, inventory_id,adjusted_type,unit_price, stocktake_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 from stocktake_items
    </sql>

    <select id="selectStocktakeItemsList" parameterType="StocktakeItems" resultMap="StocktakeItemsResult">
        <include refid="selectStocktakeItemsVo"/>
        <where>
            <if test="adjustedType != null  and adjustedType != ''"> and adjusted_type = #{adjustedType}</if>
            <if test="stocktakeId != null  and stocktakeId != ''"> and stocktake_id = #{stocktakeId}</if>
            <if test="materialId != null  and materialId != ''"> and material_id = #{materialId}</if>
            <if test="batchCode != null  and batchCode != ''"> and batch_code = #{batchCode}</if>
            <if test="warehouseId != null  and warehouseId != ''"> and warehouse_id = #{warehouseId}</if>
            <if test="locationId != null  and locationId != ''"> and location_id = #{locationId}</if>
            <if test="systemQuantity != null "> and system_quantity = #{systemQuantity}</if>
            <if test="actualQuantity != null "> and actual_quantity = #{actualQuantity}</if>
            <if test="varianceQuantity != null "> and variance_quantity = #{varianceQuantity}</if>
            <if test="varianceAmount != null "> and variance_amount = #{varianceAmount}</if>
            <if test="stocktakeStatus != null  and stocktakeStatus != ''"> and stocktake_status = #{stocktakeStatus}</if>
            <if test="countedBy != null  and countedBy != ''"> and counted_by = #{countedBy}</if>
            <if test="countedAt != null "> and counted_at = #{countedAt}</if>
            <if test="adjusted != null "> and adjusted = #{adjusted}</if>
            <if test="adjustmentReason != null  and adjustmentReason != ''"> and adjustment_reason = #{adjustmentReason}</if>
            <if test="adjustedBy != null  and adjustedBy != ''"> and adjusted_by = #{adjustedBy}</if>
            <if test="adjustedAt != null "> and adjusted_at = #{adjustedAt}</if>
            <if test="startTime != null "> and start_time = #{startTime}</if>
            <if test="endTime != null "> and end_time = #{endTime}</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="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>
            <if test="unitPrice != null  and unitPrice != ''"> and unit_price = #{unitPrice}</if>
        </where>
    </select>

    <select id="selectStocktakeItemsById" parameterType="String" resultMap="StocktakeItemsResult">
        <include refid="selectStocktakeItemsVo"/>
        where id = #{id}
    </select>

    <select id="selectStocktakeItemsByMain"  parameterType="java.util.Map" resultMap="StocktakeItemsResult">
        select st_it.id
             , st_it.inventory_id
             ,st_it.unit_price
             , st_it.stocktake_id
             , st_it.material_id
             , st_it.batch_code
             , st_it.warehouse_id
             , st_it.location_id
             , st_it.system_quantity
             , st_it.actual_quantity
             , st_it.variance_quantity
             , st_it.variance_amount
             , st_it.stocktake_status
             , st_it.counted_by
             , st_it.counted_at
             , st_it.adjusted
             , st_it.adjustment_reason
             , st_it.adjusted_by
             , st_it.adjusted_at
             , st_it.start_time
             , st_it.end_time
             , st_it.material_range
             , st_it.location_range
        ,(select w.warehouses_name from warehouses w where st_it.warehouse_id = w.id  and w.is_used = 1) as warehouse_name
        ,(select sl.location_name from storage_locations sl where st_it.location_id = sl.id  and sl.is_used = 1) as location_name
        ,st_it.adjusted_type
        from stocktake_items st_it
        where stocktake_id = #{stocktake_id} and is_used = 1
        <if test="adjusted != null  and adjusted != ''"> and adjusted = #{adjusted}</if>
    </select>
<!--    查询处理统计-详情-->
    <select id="selectStocktakesCountInfo" resultType="com.ruoyi.inventory.domain.TO.StocktakesVo">
        select
        st_it.material_id as materialId
        ,ma.sap_no as sapNo
        ,date_format(st.planned_date,'%Y%m%d') as plannedDates
        ,w.warehouses_name  as warehousesName
        ,sl.location_name  as locationName
        ,st_it.warehouse_id  as warehousesId
        ,st_it.location_id  as locationID
        ,ma.ts_code as tsCode
        ,dict.dict_label  as hazardName
        ,IFNULL(st_it.variance_quantity, '0') as varianceQuantity
        ,IFNULL(st_it.variance_amount, '0') as varianceAmount
        ,st_it.adjustment_reason as adjustmentReason
        ,st_it.adjusted_type  as adjustedType
--         ,(select dict.dict_label from sys_dict_data dict where st_it.adjusted_type = dict.dict_sort  and dict.status =0 and dict.dict_type ='adjusted_type') 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'
        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="plannedDateArray  != null and plannedDateArray.length > 0">
            and date_format(st.planned_date,'%Y%m%d') in
            <foreach item="date" collection="plannedDateArray" open="(" separator="," close=")">
                #{date}
            </foreach>
        </if>
        <if test="warehouseIdArray != null and warehouseIdArray.length > 0">
          and st_it.warehouse_id in
            <foreach item="warehouse" collection="warehouseIdArray" open="(" separator="," close=")">
                #{warehouse}
            </foreach>
        </if>
        <if test="locationIdArray != null and locationIdArray.length > 0">
          and st_it.location_id in
            <foreach item="location" collection="locationIdArray" open="(" separator="," close=")">
                #{location}
            </foreach>
        </if>
        order by st.planned_date
    </select>
<!--    本月 盘盈金额 和 盘亏金额-->
    <select id="selectstocktakeVarianceAmount" parameterType="StocktakeItems" resultType="java.util.Map">
        select
            SUM(CASE WHEN variance_amount &gt;= 0 THEN variance_amount ELSE 0 END) AS positiveTotal,
            SUM(CASE WHEN variance_amount &lt; 0 THEN ABS(variance_amount) ELSE 0 END) AS negativeAbsTotal
        from stocktake_items
        where
        is_used = 1 and IFNULL(variance_quantity, 0) != 0
        AND date_format(create_time,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
        AND date_format(create_time,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
    </select>

<!--    本月 本月差异分析-->
    <select id="selectItemsVarianceAnalysis" parameterType="StocktakeItems" resultType="java.util.Map">
        select
            COUNT(*) AS totalCount,  -- 修改总记录数
            ROUND(IFNULL(COUNT(CASE WHEN adjusted_type = 1 THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 0), 2) AS type1Ratio,
            ROUND(IFNULL(COUNT(CASE WHEN adjusted_type = 2 THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 0), 2) AS type2Ratio,
            ROUND(IFNULL(COUNT(CASE WHEN adjusted_type = 3 THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 0), 2) AS type3Ratio
        FROM stocktake_items
        where
            is_used = 1 and adjusted_type != '0'
        AND date_format(create_time,'%Y%m%d') &gt;= date_format(#{params.beginTime},'%Y%m%d')
        AND date_format(create_time,'%Y%m%d') &lt;= date_format(#{params.endTime},'%Y%m%d')
    </select>

    <insert id="insertStocktakeItems" parameterType="StocktakeItems">
        insert into stocktake_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="adjustedType != null">adjusted_type,</if>
            <if test="stocktakeId != null">stocktake_id,</if>
            <if test="inventoryId != null">inventory_id,</if>
            <if test="materialId != null">material_id,</if>
            <if test="batchCode != null">batch_code,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="locationId != null">location_id,</if>
            <if test="systemQuantity != null">system_quantity,</if>
            <if test="actualQuantity != null">actual_quantity,</if>
            <if test="varianceQuantity != null">variance_quantity,</if>
            <if test="varianceAmount != null">variance_amount,</if>
            <if test="stocktakeStatus != null">stocktake_status,</if>
            <if test="countedBy != null">counted_by,</if>
            <if test="countedAt != null">counted_at,</if>
            <if test="adjusted != null">adjusted,</if>
            <if test="adjustmentReason != null">adjustment_reason,</if>
            <if test="adjustedBy != null">adjusted_by,</if>
            <if test="adjustedAt != null">adjusted_at,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="materialRange != null">material_range,</if>
            <if test="locationRange != null">location_range,</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>
            <if test="unitPrice != null"> unit_price,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="adjustedType != null">#{adjustedType},</if>
            <if test="stocktakeId != null">#{stocktakeId},</if>
            <if test="inventoryId != null">#{inventoryId},</if>
            <if test="materialId != null">#{materialId},</if>
            <if test="batchCode != null">#{batchCode},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="locationId != null">#{locationId},</if>
            <if test="systemQuantity != null">#{systemQuantity},</if>
            <if test="actualQuantity != null">#{actualQuantity},</if>
            <if test="varianceQuantity != null">#{varianceQuantity},</if>
            <if test="varianceAmount != null">#{varianceAmount},</if>
            <if test="stocktakeStatus != null">#{stocktakeStatus},</if>
            <if test="countedBy != null">#{countedBy},</if>
            <if test="countedAt != null">#{countedAt},</if>
            <if test="adjusted != null">#{adjusted},</if>
            <if test="adjustmentReason != null">#{adjustmentReason},</if>
            <if test="adjustedBy != null">#{adjustedBy},</if>
            <if test="adjustedAt != null">#{adjustedAt},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="materialRange != null">#{materialRange},</if>
            <if test="locationRange != null">#{locationRange},</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>
            <if test="unitPrice != null"> #{unitPrice},</if>
         </trim>
    </insert>

    <update id="updateStocktakeItems" parameterType="StocktakeItems">
        update stocktake_items
        <trim prefix="SET" suffixOverrides=",">
            <if test="adjustedType != null">adjusted_type = #{adjustedType},</if>
            <if test="stocktakeId != null">stocktake_id = #{stocktakeId},</if>
            <if test="inventoryId != null">inventory_id = #{inventoryId},</if>
            <if test="materialId != null">material_id = #{materialId},</if>
            <if test="batchCode != null">batch_code = #{batchCode},</if>
            <if test="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="locationId != null">location_id = #{locationId},</if>
            <if test="systemQuantity != null">system_quantity = #{systemQuantity},</if>
            <if test="actualQuantity != null">actual_quantity = #{actualQuantity},</if>
            <if test="varianceQuantity != null">variance_quantity = #{varianceQuantity},</if>
            <if test="varianceAmount != null">variance_amount = #{varianceAmount},</if>
            <if test="stocktakeStatus != null">stocktake_status = #{stocktakeStatus},</if>
            <if test="countedBy != null">counted_by = #{countedBy},</if>
            <if test="countedAt != null">counted_at = #{countedAt},</if>
            <if test="adjusted != null">adjusted = #{adjusted},</if>
            <if test="adjustmentReason != null">adjustment_reason = #{adjustmentReason},</if>
            <if test="adjustedBy != null">adjusted_by = #{adjustedBy},</if>
            <if test="adjustedAt != null">adjusted_at = #{adjustedAt},</if>
            <if test="startTime != null">start_time = #{startTime},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="materialRange != null">material_range = #{materialRange},</if>
            <if test="locationRange != null">location_range = #{locationRange},</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>
            <if test="unitPrice != null"> unit_price = #{unitPrice},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteStocktakeItemsById" parameterType="String">
        update stocktake_items
        set is_used = 0
        where id = #{id}
    </update>

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