1.生产领料库位信息查询

南骏 池

2 天以前 b727409bf046acea9533065da35e8e3872cac783

 service/QC/IpqcService.cs

@@ -5,6 +5,9 @@
using NewPdaSqlServer.entity;
using NewPdaSqlServer.util;
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using static Azure.Core.HttpHeader;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

@@ -43,81 +46,57 @@
        return (pageList, totalCount);
    }

    public List<MesQaItemsDetectDetail5> GetItems(string? releaseNo,
    public List<dynamic> GetItems(string? releaseNo,
        string? id)
    {
        var parsedGuid = Guid.Empty;

        if (id != null)
        {
            var isValid = Guid.TryParse(id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误");
        }
        var sql = string.Format(@"SELECT [a].[guid]                                AS [Guid],
       [a].[parent_guid]                         AS [ParentGuid],
       [a].[release_no]                          AS [ReleaseNo],
       [a].[check_qyt]                           AS [CheckQyt],
       [a].[fac_level]                           AS [FacLevel],
       [a].[fcheck_item]                         AS [FcheckItem],
       [a].[fcheck_tool]                         AS [FcheckTool],
       [a].[fdown_allow]                         AS [FdownAllow],
       [a].[fcheck_level]                        AS [FcheckLevel],
       [a].[fstand]                              AS [Fstand],
       [a].[fup_allow]                           AS [FupAllow],
       [a].[sample_size_no]                      AS [SampleSizeNo],
       [a].[fspec_requ]                          AS [FspecRequ],
       [a].[fre_qty]                             AS [FreQty],
       N'1000'                                   AS [Factory],
       N'1000'                                   AS [Company],
       COUNT([b].[guid])                         AS [FenterQty],
       [a].[fcheck_resu]                         AS [FcheckResu],
       [a].[forder]                              AS [Order],
       [a].[ybsl]                                AS [Ybsl],
       [a].stationName,
       [a].IS_GWSCAN,
       ISNULL((SELECT COUNT(1)
               FROM MES_QA_ITEMS_DETECT_DETAIL13 C
               WHERE C.parent_guid = A.guid), 0) AS [YbslIn]
FROM [MES_QA_ITEMS_DETECT_DETAIL5] [a]
         Left JOIN [MES_QA_ITEMS_DETECT_DETAIL12] [b] ON ([a].[guid] = [b].[parent_guid])
WHERE ([a].[release_no] = N'{0}' AND IS_GWSCAN = 1)
GROUP BY [a].[guid], [a].[parent_guid], [a].[release_no], [a].[fac_level], [a].[fcheck_item], [a].[fcheck_tool],
         [a].[fdown_allow], [a].[fcheck_level], [a].[fstand], [a].[fup_allow], [a].[sample_size_no], [a].[fspec_requ],
         [a].[fre_qty], [a].[check_qyt], [a].[fcheck_resu], [a].[forder], [a].[ybsl], [a].stationName,
         [a].IS_GWSCAN
ORDER BY [a].[forder] ASC", releaseNo);

        var Ybsl_In = Db.Queryable<MesQaItemsDetectDetail5>()
            .Where(a => a.ParentGuid == parsedGuid)
            .Count();
        var Ybsl_In = Db.Ado.SqlQuery<dynamic>(sql).ToList();

        return Db.Queryable<MesQaItemsDetectDetail5, MesQaItemsDetectDetail12>(
                (a, b) =>
                    new JoinQueryInfos(JoinType.Left, a.Guid == b.ParentGuid))
            .Where((a, b) => a.ReleaseNo == releaseNo)
            .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
                (a, b) => a.Guid == parsedGuid)
            .GroupBy((a, b) => new
            {
                a.Guid,
                a.ParentGuid,
                a.ReleaseNo,
                a.FacLevel,
                a.FcheckItem,
                a.FcheckTool,
                a.FdownAllow,
                a.FcheckLevel,
                a.Fstand,
                a.FupAllow,
                a.SampleSizeNo,
                a.FspecRequ,
                a.FreQty,
                a.CheckQyt,
                a.FcheckResu,
                a.Order,
                a.Ybsl,
                a.ISRZXX,
                a.IPQCRZXX
            }).Select((a, b) => new MesQaItemsDetectDetail5
            {
                Guid = a.Guid,
                ParentGuid = a.ParentGuid,
                ReleaseNo = a.ReleaseNo,
                CheckQyt = a.CheckQyt,
                FacLevel = a.FacLevel,
                FcheckItem = a.FcheckItem,
                FcheckTool = a.FcheckTool,
                FdownAllow = a.FdownAllow,
                FcheckLevel = a.FcheckLevel,
                Fstand = a.Fstand,
                FupAllow = a.FupAllow,
                SampleSizeNo = a.SampleSizeNo,
                FspecRequ = a.FspecRequ,
                FreQty = a.FreQty,
                Factory = "1000",
                Company = "1000",
                FenterQty = SqlFunc.AggregateCount(b.Guid),
                FcheckResu = a.FcheckResu,
                Order = a.Order,
                Ybsl = a.Ybsl,
                YbslIn = Ybsl_In,
                ISRZXX = a.ISRZXX,
                IPQCRZXX = a.IPQCRZXX
            }).OrderBy(a => a.Order)
            .ToList();
        return Ybsl_In;
    }


    public (List<VIpqcXj> item, int TotalCount) getPageXj(XJPageResult queryObj)
    {



        var totalCount = 0;
        var parsedGuid = Guid.Empty;
        if (!queryObj.id.IsNullOrEmpty())
        {
@@ -125,43 +104,349 @@
            if (!isValid)
                throw new ApplicationException("GUID转换错误");
        }
        //var pageList = Db.Queryable<VIpqcXj>()
        //    .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
        //        a.ItemNo.Contains(queryObj.keyword) ||
        //        a.ItemName.Contains(queryObj.keyword) ||
        //        a.Daa001.Contains(queryObj.keyword) ||
        //        a.ReleaseNo.Contains(queryObj.keyword) ||
        //        a.DepartmentName.Contains(queryObj.keyword) ||
        //        a.xtName.Contains(queryObj.keyword)
        //    )
        //    .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
        //        a => a.Guid == parsedGuid.ToString())
        //    .Where(a => (a.FSubmit ?? 0) == 0)
        //    .OrderByDescending(a => a.CreateDate)
        //    .OrderBy(a => a.checkTimeSlot)
        //    .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);

        var sql = string.Format(@"SELECT [guid],
       [fsubmit],
       [MODIFY1_FLAG],
       [LOT_NO],
       [FNG_HANDLE],
       [FVERSION],
       [LOT_NO1],
       [FCOVERT_QTY],
       [FCHECK_RESU],
       [RELEASE_NO],
       [DAA001],
       [FBATCH_QTY],
       [ITEM_NO],
       [FCHECK_BY],
       [FNG_DESC],
       [CREATE_BY],
       [DAA013],
       [sj_xt],
       [departmentname],
       [name],
       [ITEM_NAME],
       [remarks],
       [DATE_01],
       [ITEM_MODEL],
       [CREATE_DATE],
       [line_id],
       [check_time_slot],
       ROW_NUMBER() OVER (ORDER BY [CREATE_DATE] DESC, [check_time_slot] ASC) AS RowIndex
FROM [V_IPQC_XJ]
WHERE
    ISNULL([fsubmit], 0) = 0
    AND (
        -- 处理22字符的标准时间段 (e.g. '2025-07-29 08:00-10:00')
        (
            LEN([check_time_slot]) = 22
            AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
            AND CAST(GETDATE() AS time) BETWEEN
                TRY_CAST(SUBSTRING([check_time_slot], 12, 5) + ':00' AS time)
                AND TRY_CAST(SUBSTRING([check_time_slot], 18, 5) + ':00' AS time)
        )
        OR
        -- 处理13字符的加班格式 (e.g. '2025-07-29 加班')
        (
            LEN([check_time_slot]) = 13
            AND [check_time_slot] LIKE '%加班'
            AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
            AND CAST(GETDATE() AS time) >= '17:00:00'
        )
    )
    {0}", 
    parsedGuid != Guid.Empty ? "AND guid = @parsedGuid" : "");  // 新增GUID过滤条件

        


        var totalCount = 0;

        var pageList = Db.Queryable<VIpqcXj>()
            .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
                a.ItemNo.Contains(queryObj.keyword) ||
                a.ItemName.Contains(queryObj.keyword) ||
                a.Daa001.Contains(queryObj.keyword) ||
                a.ReleaseNo.Contains(queryObj.keyword) ||
                a.DepartmentName.Contains(queryObj.keyword) ||
                a.xtName.Contains(queryObj.keyword)
            )
            .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
                a => a.Guid == parsedGuid.ToString())
            .Where(a => (a.FSubmit ?? 0) == 0)
            .OrderByDescending(a => a.CreateDate)
            .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
        var sqlParams = new List<SugarParameter> { 
            new("@parsedGuid", parsedGuid)  // 新增参数
        };

        var pageList = Db.Ado.SqlQuery<VIpqcXj>(sql,sqlParams).ToList();

        return (pageList, totalCount);
    }


    //更新不合格描述
    public int updateIpqcRzxx(LLJDto dto)
    public int updateIpqcRzxx(dynamic dto)
    {
        if (dto == null) throw new ArgumentNullException(nameof(dto), "参数对象不能为 null");

        // 参数校验（根据存储过程新增参数）
        if (string.IsNullOrEmpty(dto.pid?.ToString()))
            throw new ArgumentException("项目明细id存在问题，请联系管理员！", nameof(dto.pid));

        var withOracle = Db.Updateable<MesQaItemsDetectDetail5>()
                .SetColumns(s => s.IPQCRZXX == dto.inRzxxValue)
                .Where(s => s.Guid.ToString() == dto.pid)
                .ExecuteCommand();
        var sqlParams = new List<SugarParameter> {
            new("@inRzxxValue", dto.inRzxxValue),
            new("@pid", dto.pid)
        };

        return withOracle;
        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx = @inRzxxValue 
                    WHERE guid = @pid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }


    public dynamic CreateByWomdaa(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户账号不能为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.inOrderGuid1?.ToString()))
            throw new ArgumentException("检验单GUID不能为空", nameof(query.inOrderGuid1));

            if (string.IsNullOrEmpty(query.DAA001?.ToString()))
                throw new ArgumentException("工单号不能为空", nameof(query.inOrderGuid1));

            var _strMsg = "";
        var _status = -1;

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        using (var cmd = new SqlCommand("ipqc_createByWomdaa", conn))
        {
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = 
                {
                    new("@po_outMsg", SqlDbType.NVarChar, 150) { Direction = ParameterDirection.Output },
                    new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output },
                    new("@pi_user", SqlDbType.NVarChar, 150) { Value = query.userAccount },
                    new("@pi_OrderGuid1", SqlDbType.UniqueIdentifier) { Value = Guid.Parse(query.inOrderGuid1.ToString()) },
                    new("@pi_DAA001", SqlDbType.NVarChar, 150) { Value = query.DAA001.ToString() },
                    new("@pi_inP1", SqlDbType.NVarChar, 20) { Value = query.pi_inP1 ?? DBNull.Value },
                    new("@pi_inP2", SqlDbType.NVarChar, 20) { Value = query.pi_inP2 ?? DBNull.Value }
                };

                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();

                _strMsg = parameters[0].Value?.ToString() ?? "";
                _status = Convert.ToInt32(parameters[1].Value ?? -1);

                if (_status <= 0) throw new Exception(_strMsg);

                return new 
                {
                    message = _strMsg,
                    status = _status
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"检验单更新失败：{ex.Message}");
            }
        }
    }

    public int UpdateTableConfig(string selectedWater, string selectedFlow, string tableData, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@selectedWater", selectedWater),
            new("@selectedFlow", selectedFlow),
            new("@tableData", tableData),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_zrxn_sel1 = @selectedWater,
                        ipqc_zrxn_sel2 = @selectedFlow,
                        ipqc_zrxn_table = @tableData 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public dynamic GetTableConfig(string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@mxguid", mxguid)
        };

        var sql = @"SELECT ipqc_zrxn_sel1,ipqc_zrxn_sel2,ipqc_zrxn_table 
                    FROM MES_QA_ITEMS_DETECT_DETAIL5 
                    WHERE guid = @mxguid";

        var result = Db.Ado.SqlQuery<dynamic>(sql, sqlParams).FirstOrDefault();
        
        if (result == null)
            throw new Exception("配置信息查询结果为空");

        return result;
    }

    public List<dynamic> GetRzxxList(string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@mxguid", mxguid)
        };

        var sql = @"SELECT a.s_type, a.defect_name 
                    FROM [dbo].[MES_DEFECT_CODE] a 
                    WHERE a.s_type = '认证信息' 
                    AND a.pid = ( 
                        SELECT TOP 1 b.guid 
                        FROM [dbo].[MES_DEFECT_CODE] b 
                        INNER JOIN [dbo].[MES_QA_ITEMS_DETECT_DETAIL5] d 
                            ON b.defect_name = d.fcheck_item 
                        WHERE d.guid = @mxguid 
                        ORDER BY b.guid 
                    )";

        var result = Db.Ado.SqlQuery<dynamic>(sql, sqlParams);
        
        if (result == null || result.Count == 0)
            throw new Exception("未找到相关认证信息");

        return result;
    }

    public int UpdateScDate(string scDateValue, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@scDateValue", scDateValue),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx_date = @scDateValue 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public int UpdateRzxxNum(string ipqcrzxxNum, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@ipqcrzxxNum", ipqcrzxxNum),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_rzxx_num = @ipqcrzxxNum 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }

    public int UpdateCpscs(string ipqCpscs, string mxguid)
    {
        var sqlParams = new List<SugarParameter> {
            new("@ipqCpscs", ipqCpscs),
            new("@mxguid", mxguid)
        };

        var sql = @"UPDATE MES_QA_ITEMS_DETECT_DETAIL5 
                    SET ipqc_pscs = @ipqCpscs 
                    WHERE guid = @mxguid";

        return Db.Ado.ExecuteCommand(sql, sqlParams);
    }


    public dynamic GetIpqcXjDaa(dynamic unity)
    {

        var sqlParams = new List<SugarParameter> { };
        sqlParams.Add(new("@xt", unity.xt));

        var sql2 = new StringBuilder(@"
        SELECT '['+DAA001+']['+DAA021+']' AS daaInfo,DAA001,DAA021
        FROM WOMDAA
        WHERE DAA015 = @xt AND daa018 NOT IN ('W:完工', 'D:待开工')");

        if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
        {
            sqlParams.Add(new("@selectKey", unity.selectKey));
            sql2.Append(@"
            AND (DAA001 LIKE '%' + @selectKey + '%' 
            OR DAA021 LIKE '%' + @selectKey + '%')");
        }

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

        if (XcslItem == null)
        {
            throw new Exception("该条件下无对应工单信息，请重新输入！");
        }

        return XcslItem;
    }


    public dynamic SelIpqcItemsByGw(dynamic query)
    {
        if (query == null) throw new ArgumentNullException(nameof(query));

        if (string.IsNullOrEmpty(query.userAccount?.ToString()))
            throw new ArgumentException("用户名不允许为空", nameof(query.userAccount));

        if (string.IsNullOrEmpty(query.inOrderGuid1?.ToString()))
            throw new ArgumentException("检验单主表GUID不允许为空", nameof(query.inOrderGuid1));

        var _strMsg = "";
        var _status = -1;

        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        using (var cmd = new SqlCommand("prc_selIpqcItemsByGw", conn))
        {
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter[] parameters = 
                {
                    new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
                    new("@pi_gw", SqlDbType.NVarChar, 100) { Value = query.gw },
                    new("@pi_QaGuid1", SqlDbType.NVarChar, 100) { Value = query.inOrderGuid1 },
                    new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
                    new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
                };

                cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();

                _strMsg = parameters[3].Value?.ToString() ?? "";
                _status = Convert.ToInt32(parameters[4].Value ?? -1);

                if (_status <= 0) throw new Exception(_strMsg);

                return new 
                {
                    message = _strMsg,
                    status = _status,
                    qaGuid = query.inOrderGuid1,
                    gw = query.gw
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"工位扫描失败：{ex.Message}");
            }
        }
    }
}