fix:groupBy

liding

6 小时以前 dc22724f61e0b792d327fa812895f6dc4fcf129d

fix:groupBy

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

@@ -53,7 +53,7 @@
        <if test="dateQueryDto.entryDateEnd != null and dateQueryDto.entryDateEnd != '' ">
            AND expense_date &lt;= DATE_FORMAT(#{dateQueryDto.entryDateEnd},'%Y-%m-%d')
        </if>
        group by expense_type
        group by sdd.dict_label, ae.expense_type
    </select>

    <select id="report1" resultType="java.math.BigDecimal">

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

@@ -45,16 +45,15 @@
        sdd.dict_label typeName,
        ifnull(sum(income_money),0) account
        FROM account_income ai
        left join sys_dict_data sdd on ai.income_type = sdd.dict_value and  sdd.dict_type='income_types'
        where
        1=1
        LEFT JOIN sys_dict_data sdd ON ai.income_type = sdd.dict_value AND sdd.dict_type = 'income_types'
        WHERE 1=1
        <if test="dateQueryDto.entryDateStart != null and dateQueryDto.entryDateStart != '' ">
            AND income_date &gt;= DATE_FORMAT(#{dateQueryDto.entryDateStart},'%Y-%m-%d')
        </if>
        <if test="dateQueryDto.entryDateEnd != null and dateQueryDto.entryDateEnd != '' ">
            AND income_date &lt;= DATE_FORMAT(#{dateQueryDto.entryDateEnd},'%Y-%m-%d')
        </if>
        group by income_type
        GROUP BY sdd.dict_label, ai.income_type
    </select>
    <select id="report1" resultType="java.math.BigDecimal">
        SELECT

 src/main/resources/mapper/production/SalesLedgerProductionAccountingMapper.xml

@@ -82,10 +82,8 @@
                and slpa.scheduling_date >= #{ew.entryDateStart}
                and slpa.scheduling_date &lt; DATE_ADD(DATE(#{ew.entryDateEnd}), INTERVAL 1 DAY)
            </if>

        </where>
        GROUP BY slpa.scheduling_user_name

        GROUP BY slpa.scheduling_user_id, slpa.scheduling_user_name
    </select>

    <select id="selectDailyWagesStats" resultType="java.util.Map">

 src/main/resources/mapper/purchase/PaymentRegistrationMapper.xml

@@ -197,27 +197,27 @@
                T1.supplier_name LIKE CONCAT ('%',#{req.supplierName},'%')
            </if>
        </where>
        GROUP BY T1.supplier_name
        GROUP BY T1.supplier_id, T1.supplier_name
    </select>

    <select id="supplierNameListPageDetails" resultType="com.ruoyi.purchase.dto.PaymentRegistrationDto">
        SELECT
        T1.supplier_id,
        T1.supplier_name,
        SUM(contract_amount) AS invoiceAmount,
        IFNULL( SUM(T2.current_payment_amount) , 0 ) AS paymentAmount,
        IFNULL((IFNULL(SUM(contract_amount),0)  - IFNULL(SUM(T2.current_payment_amount),0)),0) AS payableAmount,
        T1.purchase_contract_number,
        T2.payment_date
        T2.payment_date,
        SUM(T1.contract_amount) AS invoiceAmount,
        IFNULL(SUM(T2.current_payment_amount), 0) AS paymentAmount,
        IFNULL((IFNULL(SUM(T1.contract_amount), 0) - IFNULL(SUM(T2.current_payment_amount), 0)), 0) AS payableAmount
        FROM purchase_ledger T1
        INNER JOIN payment_registration T2 ON T1.id = T2.purchase_ledger_id
        <where>
            T1.supplier_id = #{req.supplierId}
            <if test="req.supplierName != null and req.supplierName != '' ">
                T1.supplier_name LIKE CONCAT ('%',#{req.supplierName},'%')
                AND T1.supplier_name LIKE CONCAT ('%',#{req.supplierName},'%')
            </if>
        </where>
        GROUP BY  T1.purchase_contract_number,T2.payment_date
        GROUP BY T1.supplier_id, T1.supplier_name, T1.purchase_contract_number, T2.payment_date
    </select>

</mapper>

 src/main/resources/mapper/sales/SalesLedgerProductMapper.xml

@@ -111,29 +111,22 @@
        SUM(slp.quantity) AS purchaseNum,
        SUM(slp.tax_inclusive_total_price) AS purchaseAmount,
        COUNT(DISTINCT slp.sales_ledger_id) AS purchaseTimes,
        <!-- 平均单价 = 总采购金额/总采购数量，保留2位小数，避免除0 -->
        ROUND(IF(SUM(slp.quantity) = 0, 0, SUM(slp.tax_inclusive_total_price) / SUM(slp.quantity)), 2) AS averagePrice,
        <!-- 该产品大类下最后一个录入日期（取台账主表的entry_date） -->
        MAX(sl.entry_date) AS entryDate,
        COALESCE(NULLIF(SUM(t1.return_quantity), 0), 0) AS return_quantity,
        COALESCE(SUM(t2.total_amount), 0) AS return_amount
        FROM sales_ledger_product slp
        <!-- 关联台账主表：获取录入日期entry_date -->
        LEFT JOIN purchase_ledger sl ON slp.sales_ledger_id = sl.id
        left join purchase_return_order_products as t1 on t1.sales_ledger_product_id = slp.id
        left join purchase_return_orders as t2 on t2.id = t1.purchase_return_order_id
        WHERE slp.type = 2 <!-- 固定筛选：采购台账（type=2） -->
        <!-- 采购日期筛选：可选条件 -->
        LEFT JOIN purchase_return_order_products t1 ON t1.sales_ledger_product_id = slp.id
        LEFT JOIN purchase_return_orders t2 ON t2.id = t1.purchase_return_order_id
        WHERE slp.type = 2
        <if test="req.entryDateStart != null and req.entryDateEnd != null">
            AND sl.entry_date BETWEEN #{req.entryDateStart} AND #{req.entryDateEnd} <!-- 时间范围：非空有效 -->
            AND sl.entry_date BETWEEN #{req.entryDateStart} AND #{req.entryDateEnd}
        </if>
        <!-- 产品大类筛选：可选条件 -->
        <if test="req.productCategory != null and req.productCategory != ''">
            AND slp.product_category = #{req.productCategory}
        </if>
        <!-- 按产品大类分组聚合 -->
        GROUP BY slp.product_category
        <!-- 按产品大类排序 -->
        GROUP BY slp.product_category, slp.specification_model, sl.supplier_name
        ORDER BY slp.product_category
    </select>
    <select id="selectProductBomStructure" resultType="com.ruoyi.sales.dto.LossProductModelDto">

 src/main/resources/mapper/staff/PersonalShiftMapper.xml

@@ -14,7 +14,7 @@
    <select id="performanceShiftPage" resultType="com.ruoyi.staff.dto.PerformanceShiftMapDto">
         SELECT
        u.staff_name name,
        GROUP_CONCAT(s.work_time, '：', IFNULL(palc.shift, ''), '：', s.id order by s.work_time SEPARATOR ';') AS shift_time,
        GROUP_CONCAT(s.work_time, '：', IFNULL(palc.shift, ''), '：', s.id ORDER BY s.work_time SEPARATOR ';') AS shift_time,
                u.id user_id
        FROM personal_shift s
        LEFT JOIN staff_on_job u ON u.id = s.staff_on_job_id
@@ -30,8 +30,8 @@
                and u.staff_name like concat('%', #{userName}, '%')
            </if>
        </where>
        GROUP BY u.id
        order by s.create_time
        GROUP BY u.id, u.staff_name
        ORDER BY MAX(s.create_time)
    </select>
   
    <select id="performanceShiftYear" resultType="java.util.Map">