using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.util;
|
using SqlSugar;
|
using System.Data;
|
|
namespace MES.Service.service.QC;
|
|
public class SJService
|
{
|
private readonly BaseService _baseService = new();
|
|
//获取最大单号
|
public string getMaxBillNo()
|
{
|
var db = SqlSugarHelper.GetInstance();
|
var date = DateTime.Now.ToString("yyyy-MM-dd");
|
var sql =
|
"select max(BILL_NO) from QS_ITEM_IPI_REQ where to_char(CREATE_TIME,'yyyy-mm-dd') = '" +
|
date + "'";
|
|
var maxBillNo = db.Ado.SqlQuerySingle<string>(sql);
|
|
var number = "0001";
|
if (maxBillNo != null)
|
{
|
maxBillNo = maxBillNo.Substring(12);
|
var no = Convert.ToInt32(maxBillNo);
|
no++;
|
number = no.ToString().PadLeft(4, '0');
|
}
|
|
return "SJ" + date.Replace("-", "") + number;
|
}
|
|
//获取所有数据分页
|
public (List<SJPageResult> item, int TotalCount) getPage(SJPageResult queryObj)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
string[]? lineNo = null;
|
|
if (StringUtil.IsNotNullOrEmpty(queryObj.StatusUser))
|
lineNo = _baseService.getUserLineNo(queryObj.StatusUser);
|
|
var query = db.Queryable<SJPageResult>()
|
.WhereIF(lineNo != null && lineNo.Length > 0,
|
a => lineNo.Contains(a.line))
|
.WhereIF(queryObj.Id != null, a => a.Id == queryObj.Id)
|
.WhereIF(StringUtil.IsNotNullOrEmpty(queryObj.BillNo),
|
a => a.BillNo == queryObj.BillNo)
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.Result) &&
|
"未完成".Equals(queryObj.Result),
|
a => a.Fsubmit == 0)
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.Result) &&
|
!"未完成".Equals(queryObj.Result),
|
a => a.Fsubmit == 1);
|
|
// 新增的动态搜索逻辑
|
if (!string.IsNullOrEmpty(queryObj.SearchValue) && !string.IsNullOrEmpty(queryObj.searchField))
|
{
|
switch (queryObj.searchField)
|
{
|
case "daa001": // 工单
|
query = query.Where(x => x.daa001.Contains(queryObj.SearchValue));
|
break;
|
case "billNo": // 检验单号
|
query = query.Where(x => x.BillNo.Contains(queryObj.SearchValue));
|
break;
|
case "line": // 产线
|
query = query.Where(x => x.line.Contains(queryObj.SearchValue));
|
break;
|
case "itemNo": // 物料编码
|
query = query.Where(x => x.ItemNo.Contains(queryObj.SearchValue));
|
break;
|
case "daa003": // 物料名称
|
query = query.Where(x => x.Daa003.Contains(queryObj.SearchValue));
|
break;
|
default:
|
// 如果没有指定字段或字段不匹配，使用原有的模糊查询逻辑作为兜底方案
|
query = query.Where(x =>
|
x.ItemNo.Contains(queryObj.SearchValue) ||
|
x.Daa003.Contains(queryObj.SearchValue) ||
|
x.daa001.Contains(queryObj.SearchValue) ||
|
x.BillNo.Contains(queryObj.SearchValue) ||
|
x.line.Contains(queryObj.SearchValue));
|
break;
|
}
|
}
|
// 为了兼容旧版本，如果没有传递 searchField，使用原来的查询逻辑
|
else if (string.IsNullOrEmpty(queryObj.searchField) && !string.IsNullOrEmpty(queryObj.SearchValue))
|
{
|
// 保持原有的多字段模糊查询逻辑
|
query = query.Where(x =>
|
x.ItemNo.Contains(queryObj.SearchValue) ||
|
x.Daa003.Contains(queryObj.SearchValue) ||
|
x.daa001.Contains(queryObj.SearchValue) ||
|
x.BillNo.Contains(queryObj.SearchValue) ||
|
x.line.Contains(queryObj.SearchValue));
|
}
|
|
var totalCount = 0;
|
var data = query.OrderBy(a => a.BillNo, OrderByType.Desc)
|
.ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
|
|
return (data, totalCount);
|
}
|
|
|
//SetQSItem
|
// public List<QsItemIpiItem> SetQSItems(string itemNo)
|
// {
|
// var db = SqlSugarHelper.GetInstance();
|
// return db
|
// .Queryable<MoidToItem, MesQualityStandard>((a, b) =>
|
// new JoinQueryInfos(JoinType.Inner, a.MoidNum == b.MoidNum))
|
// .Where((a, b) => b.QsType == "1" && a.Fsubmit == 1 && a.ItemNo == itemNo).Select(
|
// (a, b) => new QsItemIpiItem
|
// {
|
// ProjName = b.ProjName,
|
// ItemMod = b.ItemMod,
|
// InspectionMethod = b.InspectionMethod,
|
// UsingInstruments = b.UsingInstruments,
|
// LevelNum = SqlFunc.IsNull(
|
// SqlFunc.IsNull(b.LevelNum * b.InspectionLevel, 1),
|
// b.InspectionLevel),
|
// MaxValue = b.MaxValue,
|
// StandardValue = b.StandardValue,
|
// MinValue = b.MinValue,
|
// Notes = b.Notes,
|
// FcheckLevel = b.FcheckLevel,
|
// FacLevel = b.FacLevel,
|
// QsCode = b.QsCode,
|
// QsName = b.QsName,
|
// result = "未检测"
|
// }).ToList();
|
// }
|
public List<QsItemIpiItem> SetQSItems(string? itemNo)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
var count = db.Queryable<MesQa>().Where(s => s.QsType == "1"
|
&& s.ItemNo == itemNo && s.Fsubmit == 1).Count();
|
|
if (count <= 0) return [];
|
|
return db
|
.Queryable<MesQualityStandard>()
|
.Where(b => b.QsType == "1" && b.ItemNo == itemNo).Select(
|
b => new QsItemIpiItem
|
{
|
ProjName = b.ProjName,
|
ItemMod = b.ItemMod,
|
|
InspectionMethod = b.InspectionMethod,
|
UsingInstruments = b.UsingInstruments,
|
LevelNum = SqlFunc.IsNull(
|
SqlFunc.IsNull(b.LevelNum * b.InspectionLevel, 1),
|
b.InspectionLevel),
|
MaxValue = b.MaxValue,
|
StandardValue = b.StandardValue,
|
MinValue = b.MinValue,
|
Notes = b.Notes,
|
FcheckLevel = b.FcheckLevel,
|
FacLevel = b.FacLevel,
|
QsCode = b.QsCode,
|
QsName = b.QsName,
|
Picture = b.Picture,
|
Picturename = b.Picturename,
|
result = "未检测",
|
isCheck = 0
|
}).ToList();
|
}
|
|
public List<QsItemIpiItem> getQSItems(decimal? pid, decimal? id)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// 使用JOIN查询获取子表数据和主表的MNUM、DNUM字段
|
var qsItemIpiItems = db
|
.Queryable<QsItemIpiItem, QsItemIpiReq>((a, b) => new JoinQueryInfos(JoinType.Left, a.Pid == b.Id))
|
.WhereIF(pid > 0, (a, b) => a.Pid == pid)
|
.WhereIF(id > 0, (a, b) => a.Id == id)
|
.Select((a, b) => new QsItemIpiItem
|
{
|
Id = a.Id,
|
Pid = a.Pid,
|
ProjName = a.ProjName,
|
ItemMod = a.ItemMod,
|
InspectionMethod = a.InspectionMethod,
|
UsingInstruments = a.UsingInstruments,
|
LevelNum = a.LevelNum,
|
MaxValue = a.MaxValue,
|
StandardValue = a.StandardValue,
|
MinValue = a.MinValue,
|
Notes = a.Notes,
|
FcheckLevel = a.FcheckLevel,
|
FacLevel = a.FacLevel,
|
QsCode = a.QsCode,
|
QsName = a.QsName,
|
Picture = a.Picture,
|
Picturename = a.Picturename,
|
IsPass = a.IsPass,
|
ItemId = a.ItemId,
|
Mnum = b.Mnum, // 从主表获取MNUM
|
Dnum = b.Dnum, // 从子表获取DNUM
|
Snum = a.Snum, // 送检批数
|
Remarks = a.Remarks
|
}).ToList();
|
|
var array = qsItemIpiItems.Select(s => s.Id).ToArray();
|
var qsItemIpiItemDetails = db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => array.Contains(s.Pid) && s.FcheckResu != "/")
|
.GroupBy(s => s.Pid)
|
.Select(s => new
|
{
|
s.Pid,
|
count = SqlFunc.AggregateCount(s.Id)
|
}).ToList();
|
|
qsItemIpiItems.ForEach(s =>
|
{
|
var find = qsItemIpiItemDetails.Find(a => s.Id == a.Pid);
|
if (find == null)
|
{
|
s.isCheck = 0;
|
s.result = "未完成";
|
}
|
else
|
{
|
s.isCheck = find.count;
|
|
// 计算实际需要检验的数量（排除堵穴）
|
int actualRequiredCount = (int)(s.LevelNum ?? 0);
|
if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
|
{
|
// 如果有穴号信息，计算非堵穴的数量
|
actualRequiredCount = s.HoleNumbers.Count(h => !h.IsBlocked);
|
}
|
|
// 调试信息：结果判定
|
Console.WriteLine($"getQSItems: Pid={s.Id}, find.count={find.count}, actualRequiredCount={actualRequiredCount}, IsPass={s.IsPass}");
|
|
// 如果有检验记录，根据合格情况判断状态
|
if (find.count > 0)
|
{
|
// 获取合格数量
|
var passCount = db.Queryable<QsItemIpiItemDetail>()
|
.Where(a => a.Pid == s.Id && a.Fstand == "√" && a.FcheckResu != "/").Count();
|
|
// 获取不合格数量
|
var failCount = db.Queryable<QsItemIpiItemDetail>()
|
.Where(a => a.Pid == s.Id && a.Fstand == "×" && a.FcheckResu != "/").Count();
|
|
// 如果有不合格记录，直接显示不合格
|
if (failCount > 0)
|
{
|
s.result = "不合格";
|
}
|
// 如果全部合格且数量匹配，显示合格
|
else if (passCount == actualRequiredCount && find.count == actualRequiredCount)
|
{
|
s.result = "合格";
|
}
|
// 如果部分完成，显示进行中
|
else if (find.count < actualRequiredCount)
|
{
|
s.result = "进行中";
|
}
|
// 如果全部完成但合格数量不足，显示不合格
|
else if (find.count == actualRequiredCount && passCount < actualRequiredCount)
|
{
|
s.result = "不合格";
|
}
|
// 其他情况显示进行中
|
else
|
{
|
s.result = "进行中";
|
}
|
}
|
else
|
{
|
s.result = "未完成";
|
}
|
}
|
});
|
|
//使用linq表达式过滤出图片id不为空的数据
|
var itemIpiItems =
|
qsItemIpiItems.Where(s => s.Picture is { Length: > 0 })
|
.ToList();
|
if (itemIpiItems.Count > 0)
|
itemIpiItems.ForEach(s =>
|
{
|
var qsItemIpiItem = qsItemIpiItems.Find(t => t.Id == s.Id);
|
//将字节数组转换为Base64编码的字符串
|
qsItemIpiItem.imageData = Convert.ToBase64String(s.Picture);
|
});
|
|
//排序，未完成的排在前面
|
qsItemIpiItems = qsItemIpiItems.OrderBy(s => s.isCheck).ToList();
|
|
// 为每个检验项目生成穴号信息
|
qsItemIpiItems.ForEach(item =>
|
{
|
if (item.Mnum > 0 && item.Snum > 0)
|
{
|
// 使用SNUM作为送检批数来计算总编号数量
|
item.HoleNumbers = GenerateHoleNumbers(item.Mnum, item.Dnum, item.Snum);
|
}
|
});
|
|
return qsItemIpiItems;
|
}
|
|
public QsItem Save(QsItem item)
|
{
|
var qsItemIpiReq = item.From;
|
var qsItemIpiItems = item.Items;
|
SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
qsItemIpiReq.StatusDate = DateTime.Now;
|
var pid = db.Insertable(qsItemIpiReq)
|
.ExecuteReturnIdentity();
|
qsItemIpiReq.Id = pid;
|
|
item.gid = pid;
|
|
qsItemIpiItems.ForEach(s => s.Pid = pid);
|
|
return db.Insertable(qsItemIpiItems)
|
.ExecuteCommand();
|
});
|
item.Items = getQSItems(qsItemIpiReq.Id, null);
|
|
//没有上下限的检验项目自动盘点为合格
|
item.Items.ForEach(s =>
|
{
|
if (s.MaxValue != null || s.StandardValue != null ||
|
s.MinValue != null) return;
|
var detail = new QsItemIpiItemDetail();
|
detail.Pid = s.Id;
|
detail.Gid = item.gid;
|
detail.Fstand = "√";
|
detail.FcheckResu = "1";
|
detail.UpdateBy = item.From.StatusUser;
|
|
// 计算默认合格的数量：(检验数 * 开穴数) - (检验数 * 堵穴数)
|
if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
|
{
|
// 如果有穴号信息，计算开穴数和堵穴数
|
int openHoles = s.HoleNumbers.Count(h => !h.IsBlocked); // 开穴数
|
int blockedHoles = s.HoleNumbers.Count(h => h.IsBlocked); // 堵穴数
|
int checkCount = (int)(s.LevelNum ?? 1); // 检验数
|
|
// 计算：(检验数 * 开穴数) - (检验数 * 堵穴数)
|
detail.count = (checkCount * openHoles) - (checkCount * blockedHoles);
|
}
|
else
|
{
|
// 否则使用LevelNum
|
detail.count = (int?)s.LevelNum;
|
}
|
|
SetQSItemDetail(detail);
|
});
|
|
item.Items = getQSItems(qsItemIpiReq.Id, null);
|
var sjPageResult = new SJPageResult
|
{ Id = item.From.Id, Limit = 1, PageIndex = 1 };
|
item.Result = getPage(sjPageResult).item[0];
|
|
return item;
|
}
|
|
public QsItem SaveItem(QsItem item)
|
{
|
var qsItemIpiItems = item.Items;
|
SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
qsItemIpiItems.ForEach(s => s.Pid = item.gid);
|
|
return db.Insertable(qsItemIpiItems)
|
.ExecuteCommand();
|
});
|
item.Items = getQSItems(item.gid, null);
|
|
//没有上下限的检验项目自动盘点为合格
|
item.Items.ForEach(s =>
|
{
|
if (s.MaxValue != null || s.StandardValue != null ||
|
s.MinValue != null) return;
|
var detail = new QsItemIpiItemDetail();
|
detail.Pid = s.Id;
|
detail.Gid = item.gid;
|
detail.Fstand = "√";
|
detail.FcheckResu = "1";
|
detail.UpdateBy = item.StatusUser;
|
|
// 计算默认合格的数量：(检验数 * 开穴数) - (检验数 * 堵穴数)
|
if (s.HoleNumbers != null && s.HoleNumbers.Count > 0)
|
{
|
// 如果有穴号信息，计算开穴数和堵穴数
|
int openHoles = s.HoleNumbers.Count(h => !h.IsBlocked); // 开穴数
|
int blockedHoles = s.HoleNumbers.Count(h => h.IsBlocked); // 堵穴数
|
int checkCount = (int)(s.LevelNum ?? 1); // 检验数
|
|
// 计算：(检验数 * 开穴数) - (检验数 * 堵穴数)
|
detail.count = (checkCount * openHoles) - (checkCount * blockedHoles);
|
}
|
else
|
{
|
// 否则使用LevelNum
|
detail.count = (int?)s.LevelNum;
|
}
|
|
SetQSItemDetail(detail);
|
});
|
|
item.Items = getQSItems(item.gid, null);
|
|
return item;
|
}
|
|
public int SetQSItemDetail(QsItemIpiItemDetail detail)
|
{
|
var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
List<QsItemIpiItemDetail> result = new();
|
|
// 获取起始索引，如果没有指定则从0开始
|
int startIndex = detail.startIndex ?? 0;
|
|
for (var i = 0; i < detail.count; i++)
|
{
|
var item = new QsItemIpiItemDetail();
|
item.Gid = detail.Gid;
|
item.Pid = detail.Pid;
|
item.Fstand = detail.Fstand;
|
item.FcheckResu = detail.FcheckResu;
|
item.CreateBy = detail.UpdateBy;
|
item.CreateDate = DateTime.Now;
|
|
// 如果有起始索引，可以在这里设置相关的索引信息
|
// 注意：这里只是示例，实际可能需要根据业务需求调整
|
result.Add(item);
|
}
|
|
return db.Insertable(result).ExecuteCommand();
|
});
|
|
detail.CreateBy = detail.UpdateBy;
|
|
autoResult(detail);
|
|
return oracle;
|
}
|
|
public List<QsItemIpiItemDetail> getQSItemDetail(decimal? pid, decimal? gid)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => s.Pid == pid)
|
.WhereIF(gid > 0, s => s.Gid == gid)
|
.ToList();
|
}
|
|
|
//自动判定是否合格
|
private int autoResult(QsItemIpiItemDetail detail)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
|
var qsItemIpiItem = db.Queryable<QsItemIpiItem>()
|
.Single(s => s.Id == detail.Pid);
|
|
if (qsItemIpiItem == null) return 0;
|
|
var count = db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => s.Pid == detail.Pid && s.FcheckResu != "/").Count();
|
|
// 计算实际需要检验的数量（排除堵穴）
|
var actualRequiredCount = qsItemIpiItem.LevelNum;
|
if (qsItemIpiItem.HoleNumbers != null && qsItemIpiItem.HoleNumbers.Count > 0)
|
{
|
// 如果有穴号信息，计算非堵穴的数量
|
actualRequiredCount = qsItemIpiItem.HoleNumbers.Count(h => !h.IsBlocked);
|
}
|
|
var result = 0;
|
|
// 比较实际需要检验的数量和实际检验记录数量
|
if (actualRequiredCount != count)
|
{
|
// 调试信息：记录数量不匹配
|
Console.WriteLine($"autoResult: actualRequiredCount={actualRequiredCount}, count={count}, Pid={detail.Pid}");
|
// 即使数量不匹配，也要继续执行后续逻辑来更新IS_PASS字段
|
// return 0; // 注释掉这行，让方法继续执行
|
}
|
|
var passCount = db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => s.Pid == detail.Pid && s.Fstand == "√" && s.FcheckResu != "/").Count();
|
|
// 调试信息：统计信息
|
Console.WriteLine($"autoResult: count={count}, passCount={passCount}, Pid={detail.Pid}");
|
|
// 只有在数量匹配时才进行合格性判断
|
if (actualRequiredCount == count && count == passCount)
|
{
|
result = 1;
|
}
|
|
var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
var commit = 0;
|
commit += db.Updateable<QsItemIpiItem>()
|
.SetColumns(s => s.IsPass == result)
|
.Where(s => s.Id == detail.Pid)
|
.ExecuteCommand();
|
return commit;
|
});
|
|
|
//自动判定是否合格
|
//获取检验单的检验项目实际需要检验个数（排除堵穴）
|
var sum = 0;
|
var qsItems = db.Queryable<QsItemIpiItem>()
|
.Where(s => s.Pid == detail.Gid).ToList();
|
|
foreach (var item in qsItems)
|
{
|
if (item.HoleNumbers != null && item.HoleNumbers.Count > 0)
|
{
|
// 如果有穴号信息，计算非堵穴的数量
|
sum += item.HoleNumbers.Count(h => !h.IsBlocked);
|
}
|
else
|
{
|
// 否则使用LevelNum
|
sum += (int)(item.LevelNum ?? 0);
|
}
|
}
|
|
if (sum == 0) return 1;
|
|
//获取检验单下的检验项目实际个数（排除堵穴）
|
var icount = db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => s.Gid == detail.Gid && s.FcheckResu != "/").Count();
|
|
if (icount == 0) return 1;
|
|
//实际个数等于理论个数时对检验单进行判定
|
if (sum == icount)
|
{
|
result = 0;
|
|
//获取这个检验单下的所有合格的检验结果
|
passCount = db.Queryable<QsItemIpiItemDetail>()
|
.Where(s => s.Gid == detail.Gid && s.Fstand == "√").Count();
|
|
//合格的检验结果等于总检验数视为合格
|
if (icount == passCount) result = 1;
|
|
useTransactionWithOracle += SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
return db.Updateable<QsItemIpiReq>()
|
.SetColumns(s => s.IsPass == result)
|
.SetColumns(s => s.StatusUser == detail.CreateBy)
|
.SetColumns(s => s.CompleteTime == DateTime.Now)
|
.Where(s => s.Id == detail.Gid)
|
.ExecuteCommand();
|
});
|
}
|
|
return useTransactionWithOracle;
|
}
|
|
public int UpdateQSItemDetail(QsItemIpiItemDetail detail)
|
{
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemIpiItemDetail>()
|
.SetColumns(s => s.UpdateBy == detail.UpdateBy)
|
.SetColumns(s => s.UpdateDate == DateTime.Now)
|
.SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.Fstand),
|
s => s.Fstand == detail.Fstand)
|
.SetColumnsIF(StringUtil.IsNotNullOrEmpty(detail.FcheckResu),
|
s => s.FcheckResu == detail.FcheckResu)
|
.Where(s => s.Id == detail.Id)
|
.ExecuteCommand();
|
});
|
|
detail.CreateBy = detail.UpdateBy;
|
|
withOracle += autoResult(detail);
|
|
return withOracle;
|
}
|
|
|
//主表修改备注字段
|
public int saveRemarksGid(QsItem dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemIpiReq>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.gid)
|
.ExecuteCommand();
|
});
|
}
|
|
//子表修改备注字段
|
public int saveRemarksPid(QsItem dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemIpiItem>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.pid)
|
.ExecuteCommand();
|
});
|
}
|
|
//孙表修改备注字段
|
public int saveRemarksById(QsItem dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemIpiItemDetail>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.id)
|
.ExecuteCommand();
|
});
|
}
|
|
public int removeXJ(decimal? id)
|
{
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
var commit = 0;
|
//删除主表
|
commit += db.Deleteable<QsItemIpiReq>().Where(s => s.Id == id)
|
.ExecuteCommand();
|
//删除子表
|
commit += db.Deleteable<QsItemIpiItem>().Where(s => s.Pid == id)
|
.ExecuteCommand();
|
//删除孙表
|
commit += db.Deleteable<QsItemIpiItemDetail>()
|
.Where(s => s.Gid == id)
|
.ExecuteCommand();
|
return commit;
|
});
|
|
return withOracle;
|
}
|
//刷新检验项目
|
public (int result, string message) GenUpdate(decimal? id, string? no, string? user, decimal? mnum = null, string? dnum = null)
|
{
|
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_ID", id, System.Data.DbType.Decimal, ParameterDirection.Input),
|
new("P_NO", no, System.Data.DbType.String, ParameterDirection.Input),
|
new("P_USER", user, System.Data.DbType.String, ParameterDirection.Input),
|
new("P_MNUM", mnum ?? 1, System.Data.DbType.Decimal, ParameterDirection.Input),
|
new("P_DNUM", dnum ?? "", System.Data.DbType.String, ParameterDirection.Input),
|
outputResult,
|
outputMessage
|
};
|
|
var db = SqlSugarHelper.GetInstance();
|
db.Ado.ExecuteCommand(
|
"BEGIN PRC_GEN_UPDATE(:P_ID,:P_NO,:P_MNUM,:P_DNUM,:P_USER, :PO_RESULT, :PO_TEXT); END;",
|
parameters.ToArray());
|
|
int result = outputResult.Value == null ? -1 : Convert.ToInt32(outputResult.Value);
|
string message = outputMessage.Value?.ToString() ?? "";
|
|
return (result, message);
|
}
|
|
public bool SjSubmit(SJDto dto)
|
{
|
try
|
{
|
// 定义输出参数
|
var outputResult = new SugarParameter("c_res", null,
|
System.Data.DbType.Int32, ParameterDirection.Output,
|
4000);
|
|
var outputMessage = new SugarParameter("c_msg", null,
|
System.Data.DbType.String,
|
ParameterDirection.Output, 4000);
|
|
// 定义输入参数，固定FLAG为1（审核）
|
var parameters = new List<SugarParameter>
|
{
|
new("P_ID", dto.id, System.Data.DbType.Decimal, ParameterDirection.Input),
|
new("P_FLAG", 1, System.Data.DbType.Int32, ParameterDirection.Input),
|
new("P_USER", dto.userNo, System.Data.DbType.String, ParameterDirection.Input),
|
outputResult,
|
outputMessage
|
};
|
|
var db = SqlSugarHelper.GetInstance();
|
|
// 使用 SqlSugar 执行存储过程
|
db.Ado.ExecuteCommand(
|
"BEGIN PRC_WOMDAA_SJ_UPDATE_RES(:P_ID, :P_FLAG, :P_USER, :c_res, :c_msg); END;",
|
parameters.ToArray());
|
|
// 获取输出参数的值
|
var resultValue = outputResult.Value?.ToString();
|
var messageValue = outputMessage.Value?.ToString();
|
|
if ("1".Equals(resultValue)) throw new Exception(messageValue);
|
|
return true;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 获取附件信息
|
/// </summary>
|
/// <param name="itemNo">物料编码</param>
|
/// <returns>附件列表</returns>
|
public List<QamftpDto> GetAttachments(string itemNo, string projName = null)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
try
|
{
|
var query = db.Queryable<MesQamftp>()
|
.Where(x => x.ItemNo == itemNo)
|
.Where(x => x.Ftype == "首检");
|
|
// 如果传入了projName，则按Fversion过滤
|
if (!string.IsNullOrEmpty(projName))
|
{
|
query = query.Where(x => x.Fversion == projName);
|
}
|
|
return query.OrderBy(x => x.Fdate, OrderByType.Desc)
|
.Select(x => new QamftpDto
|
{
|
Id = x.Id,
|
itemNo = x.ItemNo,
|
Ftype = x.Ftype,
|
Fattach = x.Fattach,
|
Fversion = x.Fversion,
|
Fdate = x.Fdate,
|
CreateBy = x.CreateBy,
|
CreateDate = x.CreateDate,
|
Company = x.Company,
|
Factory = x.Factory,
|
F_type = x.F_type,
|
LastupdateBy = x.LastupdateBy,
|
LastupdateDate = x.LastupdateDate,
|
ItemId = x.ItemId,
|
Pid = x.Pid
|
}).ToList();
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"查询附件信息失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 从FTP服务器获取文件（首检使用OPC目录）
|
/// </summary>
|
/// <param name="itemNo">物料编码</param>
|
/// <param name="fileName">文件名</param>
|
/// <param name="ftpServer">FTP服务器地址</param>
|
/// <param name="projName">项目名称</param>
|
/// <returns>文件字节数组</returns>
|
public byte[] GetFtpFile(string itemNo, string fileName, string ftpServer, string projName = null)
|
{
|
// 参数验证
|
if (string.IsNullOrEmpty(itemNo) || string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(ftpServer))
|
{
|
throw new ArgumentException("参数不能为空: itemNo, fileName, ftpServer");
|
}
|
|
string ftpUser = "hm_ftp";
|
string ftpPwd = "dell_123";
|
|
// 标准化FTP服务器地址
|
string normalizedServer = NormalizeFtpServer(ftpServer);
|
|
// 构建FTP文件路径 - 首检使用OPC目录，如果传入了projName则使用新格式
|
string ftpPath;
|
if (!string.IsNullOrEmpty(projName))
|
{
|
ftpPath = $"{normalizedServer}/OPC/{itemNo}/{projName}/{fileName}";
|
}
|
else
|
{
|
ftpPath = $"{normalizedServer}/OPC/{itemNo}/{fileName}";
|
}
|
|
try
|
{
|
var request = (System.Net.FtpWebRequest)System.Net.WebRequest.Create(ftpPath);
|
request.Method = System.Net.WebRequestMethods.Ftp.DownloadFile;
|
request.Credentials = new System.Net.NetworkCredential(ftpUser, ftpPwd);
|
request.UseBinary = true;
|
request.UsePassive = false;
|
request.Timeout = 30000; // 30秒超时
|
request.ReadWriteTimeout = 30000;
|
|
using (var response = (System.Net.FtpWebResponse)request.GetResponse())
|
using (var ftpStream = response.GetResponseStream())
|
using (var ms = new System.IO.MemoryStream())
|
{
|
if (ftpStream == null)
|
{
|
throw new Exception("FTP响应流为空");
|
}
|
|
ftpStream.CopyTo(ms);
|
var fileBytes = ms.ToArray();
|
|
if (fileBytes.Length == 0)
|
{
|
return null; // 文件为空或不存在
|
}
|
|
return fileBytes;
|
}
|
}
|
catch (System.Net.WebException ex)
|
{
|
if (ex.Response is System.Net.FtpWebResponse ftpResponse)
|
{
|
switch (ftpResponse.StatusCode)
|
{
|
case System.Net.FtpStatusCode.ActionNotTakenFileUnavailable:
|
return null; // 文件不存在
|
case System.Net.FtpStatusCode.NotLoggedIn:
|
throw new Exception("FTP认证失败，请检查用户名和密码");
|
case System.Net.FtpStatusCode.ActionNotTakenFilenameNotAllowed:
|
throw new Exception("文件名不被允许或路径无效");
|
default:
|
throw new Exception($"FTP错误 ({ftpResponse.StatusCode}): {ftpResponse.StatusDescription}");
|
}
|
}
|
|
// 处理超时和网络错误
|
if (ex.Status == System.Net.WebExceptionStatus.Timeout)
|
{
|
throw new Exception("FTP连接超时，请稍后重试");
|
}
|
|
throw new Exception($"FTP连接失败: {ex.Message}");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"获取文件失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 标准化FTP服务器地址
|
/// </summary>
|
/// <param name="ftpServer">FTP服务器地址</param>
|
/// <returns>标准化后的FTP服务器地址</returns>
|
private string NormalizeFtpServer(string ftpServer)
|
{
|
if (string.IsNullOrEmpty(ftpServer))
|
{
|
throw new ArgumentException("FTP服务器地址不能为空");
|
}
|
|
// 确保以ftp://开头
|
string normalizedServer = ftpServer.StartsWith("ftp://") ? ftpServer : $"ftp://{ftpServer}";
|
|
// 特殊处理已知服务器地址
|
if (normalizedServer == "ftp://36.26.21.214")
|
{
|
normalizedServer = "ftp://36.26.21.214:21";
|
}
|
else if (!normalizedServer.Contains(":") && normalizedServer.StartsWith("ftp://"))
|
{
|
normalizedServer += ":21"; // 默认FTP端口
|
}
|
|
// 开发环境使用本地服务器
|
normalizedServer = "ftp://192.168.1.22:21";
|
|
return normalizedServer;
|
}
|
|
/// <summary>
|
/// 获取文件的内容类型
|
/// </summary>
|
/// <param name="fileName">文件名</param>
|
/// <returns>MIME类型</returns>
|
public string GetContentType(string fileName)
|
{
|
if (string.IsNullOrEmpty(fileName))
|
return "application/octet-stream";
|
|
var extension = System.IO.Path.GetExtension(fileName).ToLower();
|
|
return extension switch
|
{
|
// PDF文件
|
".pdf" => "application/pdf",
|
|
// 图片文件
|
".jpg" or ".jpeg" => "image/jpeg",
|
".png" => "image/png",
|
".gif" => "image/gif",
|
".bmp" => "image/bmp",
|
".webp" => "image/webp",
|
".svg" => "image/svg+xml",
|
".ico" => "image/x-icon",
|
|
// 文本文件
|
".txt" => "text/plain",
|
".log" => "text/plain",
|
".md" => "text/markdown",
|
".html" or ".htm" => "text/html",
|
".css" => "text/css",
|
".js" => "application/javascript",
|
".json" => "application/json",
|
".xml" => "application/xml",
|
|
// Office文档
|
".doc" => "application/msword",
|
".docx" => "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
|
".xls" => "application/vnd.ms-excel",
|
".xlsx" => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
".ppt" => "application/vnd.ms-powerpoint",
|
".pptx" => "application/vnd.openxmlformats-officedocument.presentationml.presentation",
|
|
// 其他常见格式
|
".csv" => "text/csv",
|
".zip" => "application/zip",
|
".rar" => "application/x-rar-compressed",
|
".7z" => "application/x-7z-compressed",
|
".tar" => "application/x-tar",
|
".gz" => "application/gzip",
|
|
// 默认
|
_ => "application/octet-stream"
|
};
|
}
|
|
/// <summary>
|
/// 生成穴号信息
|
/// </summary>
|
/// <param name="mnum">开穴总数</param>
|
/// <param name="dnum">堵穴号（逗号分隔）</param>
|
/// <param name="snum">送检批数</param>
|
/// <returns>穴号信息列表</returns>
|
private List<HoleNumberInfo> GenerateHoleNumbers(decimal? mnum, string? dnum, decimal? snum)
|
{
|
var result = new List<HoleNumberInfo>();
|
|
if (mnum == null || mnum <= 0 || snum == null || snum <= 0)
|
return result;
|
|
// 解析堵穴号
|
var blockedHoles = new HashSet<int>();
|
if (!string.IsNullOrEmpty(dnum))
|
{
|
var holeNumbers = dnum.Split(',', StringSplitOptions.RemoveEmptyEntries);
|
foreach (var holeStr in holeNumbers)
|
{
|
if (int.TryParse(holeStr.Trim(), out int holeNum))
|
{
|
blockedHoles.Add(holeNum);
|
}
|
}
|
}
|
|
int totalHoles = (int)mnum.Value;
|
int batchCount = (int)snum.Value; // 送检批数
|
|
// 计算总编号数量：开穴总数 × 送检批数
|
int totalIndexes = totalHoles * batchCount;
|
|
for (int i = 1; i <= totalIndexes; i++)
|
{
|
// 穴号循环：1-8, 1-8...
|
int holeNumber = ((i - 1) % totalHoles) + 1;
|
bool isBlocked = blockedHoles.Contains(holeNumber);
|
|
result.Add(new HoleNumberInfo
|
{
|
Index = i,
|
HoleNumber = holeNumber,
|
IsBlocked = isBlocked,
|
RecordValue = isBlocked ? "/" : null,
|
CheckResult = isBlocked ? "/" : null
|
});
|
}
|
|
// 排序：堵穴排最后优先级第一，然后穴号从小到大排，穴号相同的排一起
|
result = result.OrderBy(x => x.IsBlocked) // 堵穴排最后（false在前，true在后）
|
.ThenBy(x => x.HoleNumber) // 穴号从小到大
|
.ToList();
|
|
// 重新设置Index，保持连续编号
|
for (int i = 0; i < result.Count; i++)
|
{
|
result[i].Index = i + 1;
|
}
|
|
return result;
|
}
|
|
/// <summary>
|
/// 上传图片到检验项目的PICTURE字段
|
/// </summary>
|
/// <param name="id">检验项目ID</param>
|
/// <param name="imageBytes">图片字节数组</param>
|
/// <param name="fileName">原始文件名</param>
|
/// <param name="createBy">创建人</param>
|
/// <returns>操作结果</returns>
|
public (int status, string message) UploadImageToPicture(decimal id, byte[] imageBytes, string fileName, string createBy)
|
{
|
try
|
{
|
if (imageBytes == null || imageBytes.Length == 0)
|
{
|
return (1, "图片数据为空");
|
}
|
|
if (string.IsNullOrEmpty(fileName))
|
{
|
return (1, "文件名为空");
|
}
|
|
// 验证图片格式
|
var allowedExtensions = new[] { ".jpg", ".jpeg", ".png", ".gif", ".bmp", ".webp" };
|
var extension = System.IO.Path.GetExtension(fileName).ToLower();
|
if (!allowedExtensions.Contains(extension))
|
{
|
return (1, "不支持的图片格式，仅支持：jpg, jpeg, png, gif, bmp, webp");
|
}
|
|
// 验证图片大小（限制为5MB）
|
if (imageBytes.Length > 5 * 1024 * 1024)
|
{
|
return (1, "图片大小不能超过5MB");
|
}
|
|
// 生成时间戳文件名，格式：1746945271304.jpg
|
var timestamp = DateTimeOffset.Now.ToUnixTimeMilliseconds();
|
var timestampFileName = $"{timestamp}{extension}";
|
|
var result = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
// 检查检验项目是否存在
|
var exists = db.Queryable<QsItemIpiItem>()
|
.Where(s => s.Id == id)
|
.Any();
|
|
if (!exists)
|
{
|
throw new Exception("检验项目不存在");
|
}
|
|
// 更新PICTURE字段（LONG RAW类型）和PICTURENAME字段（时间戳文件名）
|
var updateResult = db.Updateable<QsItemIpiItem>()
|
.SetColumns(s => s.Picture == imageBytes)
|
.SetColumns(s => s.Picturename == timestampFileName)
|
.Where(s => s.Id == id)
|
.ExecuteCommand();
|
|
return updateResult;
|
});
|
|
if (result > 0)
|
{
|
return (0, "图片保存成功");
|
}
|
else
|
{
|
return (1, "图片保存失败，未找到对应的检验项目");
|
}
|
}
|
catch (Exception ex)
|
{
|
return (1, $"图片保存失败：{ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 删除检验项目的图片
|
/// </summary>
|
/// <param name="id">检验项目ID</param>
|
/// <returns>操作结果</returns>
|
public (int status, string message) DeleteImageFromPicture(decimal id)
|
{
|
try
|
{
|
var result = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
// 检查检验项目是否存在
|
var exists = db.Queryable<QsItemIpiItem>()
|
.Where(s => s.Id == id)
|
.Any();
|
|
if (!exists)
|
{
|
throw new Exception("检验项目不存在");
|
}
|
|
// 清空PICTURE字段和PICTURENAME字段
|
var updateResult = db.Updateable<QsItemIpiItem>()
|
.SetColumns(s => s.Picture == null)
|
.SetColumns(s => s.Picturename == null)
|
.Where(s => s.Id == id)
|
.ExecuteCommand();
|
|
return updateResult;
|
});
|
|
if (result > 0)
|
{
|
return (0, "图片删除成功");
|
}
|
else
|
{
|
return (1, "图片删除失败，未找到对应的检验项目");
|
}
|
}
|
catch (Exception ex)
|
{
|
return (1, $"图片删除失败：{ex.Message}");
|
}
|
}
|
}
|
