fix: 录入人回显姓名,产品出入库数据分析查询错误

gongchunyi

2 天以前 235721b4a24fd6cc5037bba78c60723dcf200741

fix: 录入人回显姓名,产品出入库数据分析查询错误

 src/main/resources/mapper/account/AccountIncomeMapper.xml

@@ -4,32 +4,56 @@

    <select id="accountIncomeListPage" resultType="com.ruoyi.account.pojo.AccountIncome">
        SELECT
        *
        FROM account_income
        ai.id,
        ai.business_id,
        ai.business_type,
        ai.income_date,
        ai.income_type,
        ai.customer_name,
        ai.income_money,
        ai.income_described,
        ai.income_method,
        ai.invoice_number,
        ai.note,
        CASE
            WHEN TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
                THEN COALESCE(ui.nick_name, ai.input_user)
            ELSE ai.input_user
        END AS input_user,
        ai.input_time,
        ai.create_time,
        ai.create_user,
        ai.update_time,
        ai.update_user,
        ai.tenant_id
        FROM account_income ai
        LEFT JOIN sys_user ui ON TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
            AND ui.user_id = CAST(TRIM(ai.input_user) AS UNSIGNED)
            AND ui.del_flag = '0'
        where
        1=1
        <if test="accountIncome.entryDateStart != null and accountIncome.entryDateStart != '' ">
            AND income_date &gt;= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
            AND ai.income_date &gt;= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
        </if>
        <if test="accountIncome.entryDateEnd != null and accountIncome.entryDateEnd != '' ">
            AND income_date &lt;= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
            AND ai.income_date &lt;= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
        </if>
        <if test="accountIncome.incomeType != null and accountIncome.incomeType != '' ">
            AND income_type = #{accountIncome.incomeType}
            AND ai.income_type = #{accountIncome.incomeType}
        </if>
        <if test="accountIncome.incomeMethodLabel != null and accountIncome.incomeMethodLabel != ''">
            AND (
            (
            business_type = 1
            AND income_method in
            ai.business_type = 1
            AND ai.income_method in
            <foreach collection="accountIncome.receiptPaymentMethodList" item="method" open="(" separator="," close=")">
                #{method}
            </foreach>
            )
            OR
            (
            business_type is null
            AND income_method in
            ai.business_type is null
            AND ai.income_method in
            <foreach collection="accountIncome.paymentMethodList" item="method" open="(" separator="," close=")">
                #{method}
            </foreach>
@@ -37,26 +61,50 @@
            )
        </if>
        <if test="(accountIncome.incomeMethodLabel == null or accountIncome.incomeMethodLabel == '') and accountIncome.incomeMethod != null and accountIncome.incomeMethod != '' ">
            AND income_method = #{accountIncome.incomeMethod}
            AND ai.income_method = #{accountIncome.incomeMethod}
        </if>
    </select>
    <select id="accountIncomeExport" resultType="com.ruoyi.account.pojo.AccountIncome">
        SELECT
        *
        FROM account_income
        ai.id,
        ai.business_id,
        ai.business_type,
        ai.income_date,
        ai.income_type,
        ai.customer_name,
        ai.income_money,
        ai.income_described,
        ai.income_method,
        ai.invoice_number,
        ai.note,
        CASE
            WHEN TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
                THEN COALESCE(ui.nick_name, ai.input_user)
            ELSE ai.input_user
        END AS input_user,
        ai.input_time,
        ai.create_time,
        ai.create_user,
        ai.update_time,
        ai.update_user,
        ai.tenant_id
        FROM account_income ai
        LEFT JOIN sys_user ui ON TRIM(IFNULL(ai.input_user, '')) REGEXP '^[0-9]+$'
            AND ui.user_id = CAST(TRIM(ai.input_user) AS UNSIGNED)
            AND ui.del_flag = '0'
        where
        1=1
        <if test="accountIncome.entryDateStart != null and accountIncome.entryDateStart != '' ">
            AND income_date &gt;= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
            AND ai.income_date &gt;= DATE_FORMAT(#{accountIncome.entryDateStart},'%Y-%m-%d')
        </if>
        <if test="accountIncome.entryDateEnd != null and accountIncome.entryDateEnd != '' ">
            AND income_date &lt;= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
            AND ai.income_date &lt;= DATE_FORMAT(#{accountIncome.entryDateEnd},'%Y-%m-%d')
        </if>
        <if test="accountIncome.incomeType != null and accountIncome.incomeType != '' ">
            AND income_type = #{accountIncome.incomeType}
            AND ai.income_type = #{accountIncome.incomeType}
        </if>
        <if test="accountIncome.incomeMethod != null and accountIncome.incomeMethod != '' ">
            AND income_method = #{accountIncome.incomeMethod}
            AND ai.income_method = #{accountIncome.incomeMethod}
        </if>
    </select>
    <select id="report" resultType="com.ruoyi.account.dto.AccountDto2">
@@ -101,4 +149,16 @@

    </select>

    <update id="syncReceiptIncomeDatesFromReceiptPayment">
        UPDATE account_income ai
            INNER JOIN receipt_payment rp ON ai.business_id = rp.id AND ai.business_type = 1
        SET ai.income_date  = DATE(rp.receipt_payment_date),
            ai.input_time   = DATE(rp.receipt_payment_date),
            ai.create_time  = DATE(rp.receipt_payment_date),
            ai.update_time  = DATE(rp.receipt_payment_date)
        WHERE rp.receipt_payment_date IS NOT NULL
          AND ai.income_type = '3'
          AND ai.business_type = 1
    </update>

</mapper>

 src/main/resources/mapper/stock/StockInventoryMapper.xml

@@ -380,12 +380,19 @@
    </select>

    <select id="selectDailyStockInCounts" resultType="java.util.Map">
        WITH RECURSIVE product_subtree AS (
            SELECT id FROM product WHERE id = #{rootCategoryId}
            UNION ALL
            SELECT pr.id
            FROM product pr
                     INNER JOIN product_subtree ps ON pr.parent_id = ps.id
        )
        SELECT DATE(sir.create_time) AS date,
               SUM(sir.stock_in_num) AS count
        FROM stock_in_record sir
                 JOIN product_model pm ON sir.product_model_id = pm.id
                 JOIN product p ON pm.product_id = p.id
        WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
        WHERE p.id IN (SELECT id FROM product_subtree)
          AND sir.create_time &gt;= #{startDate}
          AND sir.create_time &lt;= #{endDate}
        GROUP BY DATE(sir.create_time)
@@ -393,12 +400,19 @@
    </select>

    <select id="selectDailyStockOutCounts" resultType="java.util.Map">
        WITH RECURSIVE product_subtree AS (
            SELECT id FROM product WHERE id = #{rootCategoryId}
            UNION ALL
            SELECT pr.id
            FROM product pr
                     INNER JOIN product_subtree ps ON pr.parent_id = ps.id
        )
        SELECT DATE(sor.create_time)  AS date,
               SUM(sor.stock_out_num) AS count
        FROM stock_out_record sor
                 JOIN product_model pm ON sor.product_model_id = pm.id
                 JOIN product p ON pm.product_id = p.id
        WHERE (p.parent_id = #{rootCategoryId} OR p.id = #{rootCategoryId})
        WHERE p.id IN (SELECT id FROM product_subtree)
          AND sor.create_time &gt;= #{startDate}
          AND sor.create_time &lt;= #{endDate}
        GROUP BY DATE(sor.create_time)