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

南骏 池

21 小时以前 b727409bf046acea9533065da35e8e3872cac783

 service/QC/IpqcService.cs

@@ -7,6 +7,7 @@
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using static Azure.Core.HttpHeader;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

@@ -45,89 +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,
                a.IPQCRZXXDate,
                a.IPQCRZXXNum,
                a.IPQCpscs,
                a.FcheckType,
            }).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,
                IPQCRZXXDate = a.IPQCRZXXDate.ToString(),
                IPQCRZXXNum = a.IPQCRZXXNum,
                IPQCpscs = a.IPQCpscs,
                FcheckType =a.FcheckType,
            }).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())
        {
@@ -135,24 +104,83 @@
            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);
    }
@@ -337,4 +365,88 @@

        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}");
            }
        }
    }
}