refactor(database): 重构产品信息查询以使用关联表结构

liyong

3 天以前 bfda179b25eb3f121cc827485ae2b8be1fee3449

refactor(database): 重构产品信息查询以使用关联表结构

- 将销售台账产品表中的产品名称、型号和单位字段替换为通过产品模型表和产品表关联获取
- 在退货销售产品查询中添加产品模型和产品的左连接关联
- 更新销售退款金额订单查询以从产品关联表获取规格型号和产品名称
- 修改发货信息查询使用产品模型表的型号和单位字段
- 调整入库记录和出库记录查询使用产品模型表的规格型号字段
- 统一产品信息获取方式，提高数据一致性和查询准确性

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

@@ -18,9 +18,9 @@
    <select id="pageSalesRefundAmountOrderDto" resultType="com.ruoyi.account.bean.dto.SalesRefundAmountOrderDto">
        select sl.sales_contract_no,
        sl.customer_contract_no,
        slp.specification_model,
        slp.product_category as product_name,
        slp.unit,
        pm.model as specification_model,
        p.product_name ,
        pm.unit,
        sl.customer_name,
        rm.return_no as return_management_no,
        srao.*
@@ -28,6 +28,8 @@
        left join return_management rm on srao.return_management_id = rm.id
        left join return_sale_product rs on rm.id = rs.return_management_id
        left join sales_ledger_product slp on rs.return_sales_ledger_product_id = slp.id
        left join product_model pm on slp.product_model_id = pm.id
            left join product p on pm.product_id = p.id
        left join sales_ledger sl on slp.sales_ledger_id = sl.id
        <where>
            <if test="ew.salesContractNo != null and ew.salesContractNo !=''">

 src/main/resources/mapper/procurementrecord/ReturnSaleProductMapper.xml

@@ -11,9 +11,9 @@
        <result column="status" property="status" />
    </resultMap>
    <select id="listReturnSaleProductDto" resultType="com.ruoyi.procurementrecord.dto.ReturnSaleProductDto">
        SELECT slp.product_category                                         as product_name,
               slp.specification_model                                      as model,
               slp.unit                                      as unit,
        SELECT p.product_name                                         as product_name,
               pm.model                                     as model,
               pm.unit                                      as unit,
               rsp.*,
               GREATEST(slp.quantity - COALESCE(rs.total_return_num, 0), 0) AS un_quantity,
               COALESCE(rs.total_return_num, 0)                             AS total_return_num
@@ -21,6 +21,8 @@
                 LEFT JOIN return_management rm ON rm.id = rsp.return_management_id
                 LEFT JOIN shipping_info si ON si.id = rm.shipping_id
                 LEFT JOIN sales_ledger_product slp ON si.sales_ledger_product_id = slp.id and slp.type = 1
                left join product_model pm on slp.product_model_id = pm.id
                  LEFT JOIN product p on pm.product_id = p.id
                 LEFT JOIN (SELECT return_sales_ledger_product_id,

                                   SUM(num) AS total_return_num

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

@@ -6,7 +6,42 @@

    <select id="selectSalesLedgerProductList" resultType="com.ruoyi.sales.pojo.SalesLedgerProduct">
        SELECT
        T1.*,
        T1.id,
        T1.sales_ledger_id,
        T1.warn_num,
        T1.speculative_trading_name,
        T1.quantity,
        T1.min_stock,
        T1.tax_rate,
        T1.tax_inclusive_unit_price,
        T1.tax_inclusive_total_price,
        T1.tax_exclusive_total_price,
        T1.invoice_type,
        T1.type,
        T1.tickets_num,
        T1.tickets_amount,
        T1.future_tickets,
        T1.future_tickets_amount,
        T1.invoice_num,
        T1.no_invoice_num,
        T1.invoice_amount,
        T1.no_invoice_amount,
        T1.product_id,
        T1.product_model_id,
        T1.register,
        T1.register_date,
        T1.approve_status,
        T1.pending_invoice_total,
        T1.invoice_total,
        T1.pending_tickets_total,
        T1.tickets_total,
        T1.is_checked,
        T1.is_production,
        T1.create_user,
        T1.dept_id,
        p.product_name as product_category,
        pm.model as specification_model,
        pm.unit as unit,
        CASE
        WHEN (IFNULL(t2.qualitity, 0) - IFNULL(t2.locked_quantity, 0)) >0 THEN 1
        ELSE 0
@@ -29,6 +64,8 @@
        LEFT JOIN shipping_product_detail spd ON si.id = spd.shipping_info_id
        GROUP BY sales_ledger_product_id
        ) t3 ON t3.sales_ledger_product_id = T1.id
        left join product_model pm ON T1.product_model_id = pm.id
        left join product p ON pm.product_id = p.id
        <where>
            <if test="salesLedgerProduct.salesLedgerId != null">
                AND T1.sales_ledger_id = #{salesLedgerProduct.salesLedgerId}

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

@@ -19,7 +19,8 @@
        s.update_user,
        s.tenant_id,
        sl.sales_contract_no,
        slp.specification_model,
        pm.model as specification_model,
        pm.unit,
        p.product_name,
        sl.customer_name
        FROM shipping_info s

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

@@ -77,7 +77,7 @@
            pl.supplier_name,
            DATE(sir.create_time) AS inboundDate,
            p.product_name,
            slp.specification_model,
            pm.model as specification_model,
            sor.stock_in_num * slp.tax_inclusive_unit_price AS InboundAmount,
            pl.purchase_contract_number
            FROM stock_in_record sir

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

@@ -93,7 +93,7 @@
        sl.customer_name,
        s.shipping_date,
        p.product_name,
        slp.specification_model,
        pm.model as specification_model,
        sor.stock_out_num * slp.tax_inclusive_unit_price as outboundAmount,
        s.shipping_no,
        sl.sales_contract_no