1.现场收料优化

南骏 池

2025-05-08 b38ef27a088410208e4643c3ab34de433fcf4849

 service/Wom/WomdaaManager.cs

@@ -109,6 +109,7 @@

        var _strMsg = "";
        var _intSum = "";
        var _cfBar = "";//拆分后条码
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_SCLL_CF]", conn))
@@ -119,8 +120,9 @@
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outMsg", SqlDbType.NVarChar, 2000),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@outCfBar", SqlDbType.NVarChar, 300),
                        new("@c_User", query.userName),
                        new("@p_biLL_no", query.daa001),
                        new("@p_item_barcode", query.barcode),
@@ -128,11 +130,13 @@
                    };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    parameters[2].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();
                    _cfBar = parameters[2].Value.ToString();


                    var result = Convert.ToInt32(_intSum);
@@ -141,7 +145,8 @@
                    var dto = new ProductionPickDto
                    {
                        daa001 = query.daa001,
                        barcode = query.barcode
                        barcode = query.barcode,//原条码
                        cfBarcode = _cfBar//拆分后条码
                    };

                    return dto;
@@ -615,13 +620,8 @@
        var sqlParams = new List<SugarParameter> { new("@daa001", query.daa001) };

        var sql1 = @"SELECT A.item_out_no,
       SUM(B.QUANTITY) AS BL_Num,
       (SELECT ISNULL(SUM(B.QUANTITY), 0)
        FROM MES_INV_ITEM_OUTS A
                 LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
        WHERE out_type = '生产领料'
          AND A.task_no = @daa001
          AND ISNULL(IS_XCSL, '0') = 1) AS JS_Num,
       SUM(B.QUANTITY)         AS BL_Num,
       ISNULL(Sub.JS_Sum, 0) AS JS_Num, -- 使用LEFT JOIN子查询的结果
       C.item_no,
       C.item_name,
       D.USER_NAME
@@ -629,9 +629,24 @@
         LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B ON A.GUID = B.ITEM_OUT_ID
         LEFT JOIN MES_ITEMS C ON B.ITEM_ID = C.item_id
         LEFT JOIN SYS_USER D ON A.create_by = D.ACCOUNT
WHERE out_type = '生产领料'
-- 新增LEFT JOIN聚合子查询
         LEFT JOIN (SELECT B1.ITEM_ID,
                           SUM(B1.QUANTITY) AS JS_Sum
                    FROM MES_INV_ITEM_OUTS A1
                             LEFT JOIN MES_INV_ITEM_OUT_C_DETAILS B1 ON A1.GUID = B1.ITEM_OUT_ID
                    WHERE A1.out_type = '生产领料'
                      AND A1.task_no = @daa001
                      AND ISNULL(B1.IS_XCSL, '0') = 1
                    GROUP BY B1.ITEM_ID -- 按ITEM_ID提前聚合
) AS Sub ON Sub.ITEM_ID = C.item_id
WHERE A.out_type = '生产领料'
  AND A.task_no = @daa001
GROUP BY item_out_no, C.item_no, C.item_name, D.USER_NAME";
GROUP BY A.item_out_no,
         C.item_no,
         C.item_name,
         D.USER_NAME,
         C.item_id,
         ISNULL(Sub.JS_Sum, 0);";

        var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams);