using System.Data;
|
using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.util;
|
using SqlSugar;
|
|
namespace MES.Service.service.QC;
|
|
public class CqjService
|
{
|
/// <summary>
|
/// 获取超期检验列表（分页）
|
/// </summary>
|
/// <param name="queryObj">查询对象</param>
|
/// <returns>分页数据</returns>
|
public (List<LtsCqj> item, int TotalCount) GetPage(XJPageResult queryObj)
|
{
|
if (string.IsNullOrEmpty(queryObj.createUser)) return ([], 0);
|
|
var db = SqlSugarHelper.GetInstance();
|
var id = Convert.ToDecimal(queryObj.id);
|
int totalCount = 0;
|
|
// 查询超期检验列表
|
var pageList = db.Queryable<LtsCqj>()
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
"未完成".Equals(queryObj.result),
|
a => a.FcheckResu == null)
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
!"未完成".Equals(queryObj.result),
|
a => a.FcheckResu != null)
|
.WhereIF(id > 0, a => a.Id == id)
|
// 搜索条件
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1, // 物料编号搜索
|
a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2, // 物料名称搜索
|
a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3, // 送检批次搜索
|
a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4, // 送检单号搜索
|
a => a.DeclarationNo != null && a.DeclarationNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5, // 检验单号搜索
|
a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6, // 物料规格搜索
|
a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.Select(a => new LtsCqj
|
{
|
Id = a.Id,
|
ReleaseNo = a.ReleaseNo,
|
ItemId = a.ItemId,
|
ItemNo = a.ItemNo,
|
ItemName = a.ItemName,
|
ItemModel = a.ItemModel,
|
LotNo = a.LotNo,
|
Fsubmit = a.Fsubmit,
|
CreateDate = a.CreateDate,
|
Cjr = a.Cjr,
|
FcheckResu = a.FcheckResu,
|
LotNo1 = a.LotNo1,
|
FngDesc = a.FngDesc,
|
FbatchQty = a.FbatchQty,
|
Tjr = a.Tjr,
|
Fnumber = a.Fnumber,
|
Sjr = a.Sjr,
|
DeclarationNo = a.DeclarationNo
|
})
|
.OrderBy(a => a.CreateDate, OrderByType.Desc)
|
.ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
|
|
// 批量获取检验项目数量
|
if (pageList.Any())
|
{
|
var releaseNos = pageList.Select(x => x.ReleaseNo).Distinct().ToList();
|
|
foreach (var item in pageList)
|
{
|
var count = db.Queryable<MesCqItemsDetectDetail5>()
|
.Where(x => x.ReleaseNo == item.ReleaseNo)
|
.Count();
|
item.InspectionItemCount = count;
|
}
|
}
|
|
// 计算总数
|
int allDataQuery = db.Queryable<LtsCqj>()
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
"未完成".Equals(queryObj.result),
|
a => a.FcheckResu == null)
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
!"未完成".Equals(queryObj.result),
|
a => a.FcheckResu != null)
|
.WhereIF(id > 0, a => a.Id == id)
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 1,
|
a => a.ItemNo != null && a.ItemNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 2,
|
a => a.ItemName != null && a.ItemName.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 3,
|
a => a.LotNo != null && a.LotNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 4,
|
a => a.DeclarationNo != null && a.DeclarationNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 5,
|
a => a.ReleaseNo != null && a.ReleaseNo.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.WhereIF(!string.IsNullOrEmpty(queryObj.SearchValue) && queryObj.selectedIndex == 6,
|
a => a.ItemModel != null && a.ItemModel.ToLower().Contains(queryObj.SearchValue.ToLower()))
|
.Select(a => a.ReleaseNo)
|
.Distinct()
|
.Count();
|
|
return (pageList, allDataQuery);
|
}
|
|
/// <summary>
|
/// 调用存储过程，重新获取检验项目
|
/// </summary>
|
/// <param name="itemNo">物料编号</param>
|
/// <param name="quantity">数量</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <returns>执行结果</returns>
|
public string[] SetItems(decimal itemNo, decimal quantity, string releaseNo)
|
{
|
// 定义输出参数
|
var outputResult = new SugarParameter("PO_RESULT", null,
|
System.Data.DbType.Int32, ParameterDirection.Output, 4000);
|
|
var outputMessage = new SugarParameter("PO_TEXT", null,
|
System.Data.DbType.String, ParameterDirection.Output, 4000);
|
|
// 定义输入参数
|
var parameters = new List<SugarParameter>
|
{
|
new("P_RELEASE_NO", releaseNo, System.Data.DbType.String, ParameterDirection.Input),
|
new("P_ITEM_ID", itemNo, System.Data.DbType.Decimal, ParameterDirection.Input),
|
outputResult,
|
outputMessage
|
};
|
|
var db = SqlSugarHelper.GetInstance();
|
|
// 使用 SqlSugar 执行存储过程
|
db.Ado.ExecuteCommand(
|
"BEGIN PRC_CQ_ITEM_INSERT_BTN(:P_RELEASE_NO, :P_ITEM_ID, :PO_RESULT, :PO_TEXT); END;",
|
parameters.ToArray());
|
|
// 获取输出参数的值
|
var resultValue = outputResult.Value?.ToString();
|
var messageValue = outputMessage.Value?.ToString();
|
|
string[] msg = new string[2];
|
msg[0] = resultValue;
|
msg[1] = messageValue;
|
return msg;
|
}
|
|
/// <summary>
|
/// 获取检验项目列表
|
/// </summary>
|
/// <param name="releaseNo">检验单号</param>
|
/// <param name="id">项目ID</param>
|
/// <returns>检验项目列表</returns>
|
public List<MesCqItemsDetectDetail5> GetItems(string? releaseNo, decimal? id)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
return db.Queryable<MesCqItemsDetectDetail5, MesCqItemsDetectDetail12>(
|
(a, b) => new JoinQueryInfos(JoinType.Left, a.Id == b.MainId))
|
.Where((a, b) => a.ReleaseNo == releaseNo)
|
.GroupBy((a, b) => new
|
{
|
a.Id,
|
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.FcheckItemDesc,
|
a.Funit,
|
a.Meom,
|
a.FngQty, // 添加不合格数
|
a.FacQty // 添加AC数
|
}).Select((a, b) => new MesCqItemsDetectDetail5
|
{
|
Id = a.Id,
|
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.Id), // 已录入数量
|
FngQty = a.FngQty, // 不合格数（从数据库读取，由 updateDetail5 更新）
|
FacQty = a.FacQty, // AC数
|
FcheckResu = a.FcheckResu,
|
FcheckItemDesc = a.FspecRequ,
|
Funit = a.Funit,
|
Meom = a.Meom
|
})
|
.OrderBy(a => SqlFunc.IIF(a.Fstand != null, 0, 1))
|
.OrderBy(a => a.FcheckItem, OrderByType.Desc)
|
.ToList();
|
}
|
|
/// <summary>
|
/// 删除超期检验单
|
/// </summary>
|
/// <param name="releaseNo">检验单号</param>
|
/// <returns>影响行数</returns>
|
public int RemoveCqj(string? releaseNo)
|
{
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
var commit = 0;
|
// 删除主表
|
commit += db.Deleteable<MesCqItemsDetect01>()
|
.Where(s => s.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
// 删除子表
|
commit += db.Deleteable<MesCqItemsDetectDetail5>()
|
.Where(s => s.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
// 删除孙表
|
commit += db.Deleteable<MesCqItemsDetectDetail12>()
|
.Where(s => s.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
return commit;
|
});
|
|
return withOracle;
|
}
|
|
/// <summary>
|
/// 保存不良描述到主表
|
/// </summary>
|
/// <param name="gid">主表ID</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <param name="fngDesc">不良描述</param>
|
/// <returns>影响行数</returns>
|
public int SaveRemarksGid(decimal gid, string? releaseNo, string? fngDesc)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Updateable<MesCqItemsDetect01>()
|
.SetColumns(it => new MesCqItemsDetect01
|
{
|
FngDesc = fngDesc // 使用FNG_DESC字段存储不良描述
|
})
|
.Where(it => it.Id == gid && it.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
}
|
|
/// <summary>
|
/// 保存备注到主表
|
/// </summary>
|
/// <param name="gid">主表ID</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <param name="lotNo1">备注内容</param>
|
/// <returns>影响行数</returns>
|
public int SaveLotNo1(decimal gid, string? releaseNo, string? lotNo1)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Updateable<MesCqItemsDetect01>()
|
.SetColumns(it => new MesCqItemsDetect01
|
{
|
LotNo1 = lotNo1 // 使用LOT_NO1字段存储备注
|
})
|
.Where(it => it.Id == gid && it.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
}
|
|
/// <summary>
|
/// 保存不良描述到子表
|
/// </summary>
|
/// <param name="pid">子表ID</param>
|
/// <param name="funit">不良描述</param>
|
/// <returns>影响行数</returns>
|
public int SaveRemarksPid(decimal pid, string? funit)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Updateable<MesCqItemsDetectDetail5>()
|
.SetColumns(it => new MesCqItemsDetectDetail5
|
{
|
Funit = funit
|
})
|
.Where(it => it.Id == pid)
|
.ExecuteCommand();
|
}
|
|
/// <summary>
|
/// 保存备注到子表
|
/// </summary>
|
/// <param name="pid">子表ID</param>
|
/// <param name="meom">备注</param>
|
/// <returns>影响行数</returns>
|
public int SaveMeom(decimal pid, string? meom)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Updateable<MesCqItemsDetectDetail5>()
|
.SetColumns(it => new MesCqItemsDetectDetail5
|
{
|
Meom = meom
|
})
|
.Where(it => it.Id == pid)
|
.ExecuteCommand();
|
}
|
|
/// <summary>
|
/// 新增检验详情记录（批量）
|
/// </summary>
|
/// <param name="detail">检验详情对象</param>
|
/// <returns>影响行数</returns>
|
public int SetQSItemDetail(MesCqItemsDetectDetail12 detail)
|
{
|
var dbd = SqlSugarHelper.GetInstance();
|
var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
List<MesCqItemsDetectDetail12> result = new();
|
for (var i = 0; i < detail.count; i++)
|
{
|
var item = new MesCqItemsDetectDetail12();
|
item.MainId = detail.MainId;
|
item.ReleaseNo = detail.ReleaseNo;
|
item.Fstand = detail.Fstand;
|
item.FcheckResu = detail.FcheckResu;
|
item.CreateBy = detail.LastupdateBy;
|
item.CreateDate = DateTime.Now;
|
item.Factory = "1000";
|
item.Company = "1000";
|
result.Add(item);
|
}
|
|
return db.Insertable(result).ExecuteCommand();
|
});
|
|
detail.CreateBy = detail.LastupdateBy;
|
|
updateDetail5(detail);
|
autoResult(detail);
|
|
return oracle;
|
}
|
|
//更新检验明细已检、不合格数量
|
private int updateDetail5(MesCqItemsDetectDetail12 detail)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
//查询这个检验项目下的检验数量
|
var count = db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(s => s.MainId == detail.MainId).Count();
|
|
//获取不合格数
|
var countNo = db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();
|
|
//更新检验明细已检数量
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<MesCqItemsDetectDetail5>()
|
.SetColumns(s => s.FenterQty == count)
|
.SetColumns(s => s.FngQty == countNo)
|
.Where(s => s.Id == detail.MainId)
|
.ExecuteCommand();
|
});
|
|
return withOracle;
|
}
|
|
private int autoResult(MesCqItemsDetectDetail12 detail)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
|
var QsItemOqcItem = db.Queryable<MesCqItemsDetectDetail5>()
|
.Single(s => s.Id == detail.MainId);
|
|
if (QsItemOqcItem == null) return 0;
|
|
//查询这个检验项目下的检验结果
|
var count = db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(s => s.MainId == detail.MainId).Count();
|
|
updateDetail5(detail);
|
var result = "";
|
|
//检验实际结果不等于应该检验的个数时直接退出
|
if (QsItemOqcItem.CheckQyt != count) return 0;
|
|
//合格的有多少个
|
var passCount = db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(s => s.MainId == detail.MainId && s.Fstand == "√").Count();
|
//不合格的有多少个
|
var noCount = db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(s => s.MainId == detail.MainId && s.Fstand == "×").Count();
|
|
if (count == passCount)
|
result = "合格";
|
else if (noCount >= QsItemOqcItem.FreQty)
|
result = "不合格";
|
var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
var commit = 0;
|
commit += db.Updateable<MesCqItemsDetectDetail5>()
|
.SetColumns(s => s.FcheckResu == result)
|
.SetColumns(s => s.FenterQty == count)
|
.Where(s => s.Id == detail.MainId)
|
.ExecuteCommand();
|
|
return commit;
|
});
|
|
return useTransactionWithOracle;
|
}
|
|
/// <summary>
|
/// 获取检验详情记录列表
|
/// </summary>
|
/// <param name="id">主检验项目ID</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <returns>检验详情列表</returns>
|
public List<MesCqItemsDetectDetail12> GetQSItemDetail(decimal id, string? releaseNo)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
return db.Queryable<MesCqItemsDetectDetail12>()
|
.Where(x => x.MainId == id && x.ReleaseNo == releaseNo)
|
.OrderBy(x => x.CreateDate, OrderByType.Asc)
|
.Select(x => new MesCqItemsDetectDetail12
|
{
|
Id = x.Id,
|
MainId = x.MainId,
|
ReleaseNo = x.ReleaseNo,
|
Fstand = x.Fstand,
|
FcheckResu = x.FcheckResu,
|
CreateBy = x.CreateBy,
|
CreateDate = x.CreateDate,
|
LastupdateBy = x.LastupdateBy,
|
Factory = x.Factory,
|
Company = x.Company
|
// 不包含 LastupdateDate 字段
|
})
|
.ToList();
|
}
|
|
/// <summary>
|
/// 更新检验详情记录
|
/// </summary>
|
/// <param name="id">详情记录ID</param>
|
/// <param name="mainId">主检验项目ID</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <param name="fstand">检验标准</param>
|
/// <param name="fcheckResu">检验结果</param>
|
/// <param name="updateBy">更新人</param>
|
/// <returns>影响行数</returns>
|
public int UpdateQSItemDetail(decimal id, decimal mainId, string? releaseNo, string? fstand, string? fcheckResu, string? updateBy)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// 先更新检验详情记录
|
var updateResult = db.Updateable<MesCqItemsDetectDetail12>()
|
.SetColumns(it => new MesCqItemsDetectDetail12
|
{
|
Fstand = fstand,
|
FcheckResu = fcheckResu,
|
LastupdateBy = updateBy
|
// 不设置 LastupdateDate，因为数据库中不存在此字段
|
})
|
.Where(it => it.Id == id && it.MainId == mainId && it.ReleaseNo == releaseNo)
|
.ExecuteCommand();
|
|
// 构造 detail 对象用于调用 autoResult
|
var detail = new MesCqItemsDetectDetail12
|
{
|
Id = id,
|
MainId = mainId,
|
ReleaseNo = releaseNo,
|
Fstand = fstand,
|
FcheckResu = fcheckResu,
|
LastupdateBy = updateBy,
|
CreateBy = updateBy
|
};
|
|
// 更新已检数量和不合格数量（在更新记录之后执行，确保能查询到最新的 Fstand）
|
updateDetail5(detail);
|
|
// 自动判定检验结果
|
autoResult(detail);
|
|
return updateResult;
|
}
|
|
/// <summary>
|
/// 检验提交
|
/// </summary>
|
/// <param name="userNo">用户账号</param>
|
/// <param name="releaseNo">检验单号</param>
|
/// <returns>执行结果</returns>
|
public string[] SubmitInspection(string? userNo, string? releaseNo)
|
{
|
try
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// 获取检验单信息（只查询需要的字段，避免实体映射错误）
|
var fngDesc = db.Queryable<MesCqItemsDetect01>()
|
.Where(x => x.ReleaseNo == releaseNo)
|
.Select(x => x.FngDesc)
|
.First();
|
|
// 检查检验单是否存在
|
var exists = db.Queryable<MesCqItemsDetect01>()
|
.Where(x => x.ReleaseNo == releaseNo)
|
.Any();
|
|
if (!exists)
|
{
|
return new[] { "1", "检验单不存在" };
|
}
|
|
// 定义输出参数
|
var outputResult = new SugarParameter("o_Result", null,
|
System.Data.DbType.Int32, ParameterDirection.Output, 4000);
|
|
var outputMessage = new SugarParameter("o_Msg", null,
|
System.Data.DbType.String, ParameterDirection.Output, 4000);
|
|
// 定义输入参数（参数顺序必须与存储过程定义完全一致）
|
var parameters = new List<SugarParameter>
|
{
|
new("PI_FACTORY", "1000", System.Data.DbType.String, ParameterDirection.Input, 50),
|
new("PI_COMPANY", "1000", System.Data.DbType.String, ParameterDirection.Input, 50),
|
new("p_Release_No", releaseNo ?? "", System.Data.DbType.String, ParameterDirection.Input, 50),
|
new("p_badReason", "", System.Data.DbType.String, ParameterDirection.Input, 200), // 空字符串而非NULL
|
new("p_iqcStatus", "", System.Data.DbType.String, ParameterDirection.Input, 200), // 空字符串而非NULL
|
new("p_workShop", "", System.Data.DbType.String, ParameterDirection.Input, 200), // 空字符串而非NULL
|
new("p_remark", fngDesc ?? "", System.Data.DbType.String, ParameterDirection.Input, 4000), // 不良描述
|
new("p_User", userNo ?? "", System.Data.DbType.String, ParameterDirection.Input, 50),
|
outputResult,
|
outputMessage
|
};
|
|
// 调用存储过程
|
db.Ado.ExecuteCommand(
|
"BEGIN Prc_Mes_CQJ_Qa_Submit(:PI_FACTORY, :PI_COMPANY, :p_Release_No, :p_badReason, :p_iqcStatus, :p_workShop, :p_remark, :p_User, :o_Result, :o_Msg); END;",
|
parameters.ToArray());
|
|
// 获取输出参数的值
|
var resultValue = outputResult.Value?.ToString() ?? "1";
|
var messageValue = outputMessage.Value?.ToString() ?? "提交失败";
|
|
return new[] { resultValue, messageValue };
|
}
|
catch (Exception ex)
|
{
|
return new[] { "1", $"提交异常：{ex.Message}" };
|
}
|
}
|
}
|
