using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.Modes.QcIssueResult;
|
using MES.Service.service.QcIssueResult;
|
using MES.Service.util;
|
using SqlSugar;
|
using System.Linq;
|
using System.Data;
|
using System.Net;
|
using System.Web;
|
|
namespace MES.Service.service.QC;
|
|
public class RKJService
|
{
|
private readonly BaseService _baseService = new();
|
|
//手动执行sql
|
//生成检验单号
|
public string getMaxBillNo()
|
{
|
var db = SqlSugarHelper.GetInstance();
|
var date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
try
|
{
|
// 查询当天所有以RKJY开头的检验单号
|
var sql = @"
|
SELECT RELEASE_NO
|
FROM QS_ITEM_OQC_REQ
|
WHERE RELEASE_NO LIKE 'RKJY%'
|
AND TO_CHAR(CREATE_DATE,'yyyy-mm-dd') = @date
|
ORDER BY RELEASE_NO DESC";
|
|
var existingNumbers = db.Ado.SqlQuery<string>(sql, new { date });
|
|
var number = "0001";
|
if (existingNumbers != null && existingNumbers.Count > 0)
|
{
|
// 找到最大的编号
|
var maxNumber = existingNumbers
|
.Where(x => x != null && x.Length >= 16) // 确保长度足够
|
.Select(x => x.Substring(12, 4))
|
.Where(x => int.TryParse(x, out _))
|
.Select(x => int.Parse(x))
|
.DefaultIfEmpty(0)
|
.Max();
|
|
number = (maxNumber + 1).ToString().PadLeft(4, '0');
|
}
|
|
var result = "RKJY" + date.Replace("-", "") + number;
|
|
// 添加日志记录（可选）
|
Console.WriteLine($"Generated RKJ number: {result}, Date: {date}, Existing count: {existingNumbers?.Count ?? 0}");
|
|
return result;
|
}
|
catch (Exception ex)
|
{
|
// 如果出现异常，使用时间戳作为备选方案
|
var timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
|
var fallbackNumber = "RKJY" + date.Replace("-", "") + timestamp.Substring(8, 4);
|
Console.WriteLine($"Error generating RKJ number, using fallback: {fallbackNumber}, Error: {ex.Message}");
|
return fallbackNumber;
|
}
|
}
|
|
//选择产线
|
public List<MesLine> getLineNo()
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Queryable<MesLine>()
|
.OrderBy(t => t.LineNo)
|
.Select(t => new MesLine
|
{
|
LineNo = t.LineNo,
|
LineName = t.LineName
|
})
|
.ToList();
|
}
|
|
/// <summary>
|
/// 获取有线体的部门列表（生产车间）
|
/// </summary>
|
/// <returns>部门列表</returns>
|
public List<dynamic> GetDepartmentsWithLines()
|
{
|
var db = SqlSugarHelper.GetInstance();
|
var sql = @"
|
SELECT DISTINCT
|
d.DEPARTMENTID as departmentid,
|
d.DEPARTMENTNAME as departmentname
|
FROM SYS_DEPARTMENT d
|
INNER JOIN MES_LINE l ON d.DEPARTMENTID = l.DEPOT_ID
|
WHERE L.LINE_NO IS NOT NULL
|
ORDER BY d.DEPARTMENTNAME";
|
|
return db.Ado.SqlQuery<dynamic>(sql);
|
}
|
|
/// <summary>
|
/// 根据部门ID获取该部门下的线体列表
|
/// </summary>
|
/// <param name="departmentId">部门ID</param>
|
/// <returns>线体列表</returns>
|
public List<dynamic> GetLinesByDepartment(string departmentId)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
var sql = @"
|
SELECT
|
l.LINE_NO as lineNo,
|
l.LINE_NAME as lineName
|
FROM MES_LINE l
|
WHERE l.DEPOT_ID = @departmentId
|
ORDER BY l.LINE_NAME";
|
|
return db.Ado.SqlQuery<dynamic>(sql, new { departmentId });
|
}
|
|
/// <summary>
|
/// 保存部门选择
|
/// </summary>
|
/// <param name="id">检验单ID</param>
|
/// <param name="departmentId">部门ID</param>
|
/// <param name="departmentName">部门名称</param>
|
/// <returns>操作结果</returns>
|
public bool SaveDepartmentSelection(decimal id, string departmentId, string departmentName)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
var sql = @"
|
UPDATE QS_ITEM_OQC_REQ
|
SET WORK_SHOP = @departmentName, DEPARTMENT_ID = @departmentId
|
WHERE ID = @id";
|
|
return db.Ado.ExecuteCommand(sql, new { id, departmentId, departmentName }) > 0;
|
}
|
|
|
//获取工单号
|
public List<Womdaa> GetDaa001s(string lineNo)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Queryable<Womdaa, MesItems>((w, m) => new JoinQueryInfos(
|
JoinType.Left, w.Daa002 == m.Id.ToString()))
|
.Where((w, m) => w.Daa015 == lineNo)
|
.OrderBy((w, m) => w.Daa001, OrderByType.Desc)
|
.Select((w, m) => new Womdaa
|
{
|
Id = w.Id,
|
Daa001 = w.Daa001, // 工单号
|
Daa002 = m.ItemNo, // 产品编码（从MesItems表获取）
|
Daa003 = w.Daa003, // 产品名称
|
Daa004 = w.Daa004, // 产品规格
|
Daa008 = w.Daa008, // 工单数量
|
Daa015 = w.Daa015, // 生产线别
|
// 添加物料ID字段
|
ItemId = m.Id // 物料ID
|
})
|
.ToList();
|
}
|
|
//根据检验标准来计算检验个数
|
public List<QsItemOqcItem> SetItems(string itemNo, decimal quantity)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
var count = db.Queryable<MesQa>().Where(s => s.QsType == "3"
|
&& s.ItemNo == itemNo && s.Fsubmit == 1).Count();
|
|
if (count <= 0) return [];
|
|
var qsItemOqcItems = db
|
.Queryable<MesQualityStandard>()
|
.Where(b => b.QsType == "3"
|
&& b.ItemNo == itemNo).Select(
|
b => new QsItemOqcItem
|
{
|
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 = "未检测",
|
isCheck = 0,
|
Picture = b.Picture,
|
Picturename = b.Picturename
|
}).ToList();
|
|
qsItemOqcItems.ForEach(item =>
|
{
|
string LEV = null;
|
switch (item.FcheckLevel)
|
{
|
case string s when s.Contains("S1"):
|
LEV = "B.FLEVEL_S1";
|
break;
|
case string s when s.Contains("S2"):
|
LEV = "B.FLEVEL_S2";
|
break;
|
case string s when s.Contains("S3"):
|
LEV = "B.FLEVEL_S3";
|
break;
|
case string s when s.Contains("S4"):
|
LEV = "B.FLEVEL_S4";
|
break;
|
case string s when s.Contains("(I)"):
|
LEV = "B.FLEVEL_I";
|
break;
|
case string s when s.Contains("(II)"):
|
LEV = "B.FLEVEL_II";
|
break;
|
case string s when s.Contains("(III)"):
|
LEV = "B.FLEVEL_III";
|
break;
|
default:
|
LEV = ""; // 默认值
|
break;
|
}
|
|
var sql =
|
"SELECT " + LEV +
|
" FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL2 B ON B.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO='" +
|
item.QsCode + "' AND B.LOT_FROM<= " + quantity + " AND " +
|
quantity + "<=B.LOT_TO";
|
|
var maxBillNo = db.Ado.SqlQuerySingle<string>(sql);
|
|
|
var result = ExtractSubstring(item.FacLevel, '(', ')');
|
|
|
sql = "SELECT FSAMPLE_SIZE_WORD, " + result +
|
" Result FROM MES_QM_AQL1 A LEFT JOIN MES_QM_AQL3 C ON C.AQL1_ID=A.ID WHERE A.SAMPLE_SIZE_NO= '" +
|
item.QsCode + "' AND SAMPLE_SIZE_WORD= '" + maxBillNo + "'";
|
var resultClass = db.Ado.SqlQuerySingle<ResultClass>(sql);
|
|
item.LevelNum = resultClass.FSAMPLE_SIZE_WORD;
|
item.FreQty = resultClass.Result;
|
});
|
|
return qsItemOqcItems;
|
}
|
|
private string ExtractSubstring(string input, char startChar, char endChar)
|
{
|
var startIndex = input.IndexOf(startChar);
|
var endIndex = input.IndexOf(endChar, startIndex);
|
|
if (startIndex == -1 || endIndex == -1)
|
// 如果未找到起始字符或结束字符，则返回空字符串或者抛出异常，根据实际情况选择
|
return string.Empty;
|
// 或者抛出异常
|
// throw new ArgumentException("Start or end character not found");
|
// 提取子字符串
|
var length = endIndex - startIndex - 1;
|
return input.Substring(startIndex + 1, length);
|
}
|
|
public List<QsItemOqcItem> GetItems(decimal? pid, decimal? id)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
return db.Queryable<QsItemOqcItem, QsItemOqcItemDetail>((a, b) =>
|
new JoinQueryInfos(JoinType.Left, a.Id == b.Pid))
|
.WhereIF(pid > 0, (a, b) => a.Pid == pid)
|
.WhereIF(id > 0, (a, b) => a.Id == id)
|
.GroupBy((a, b) => new
|
{
|
a.Id,
|
a.ProjName,
|
a.ItemMod,
|
a.InspectionMethod,
|
a.UsingInstruments,
|
a.LevelNum,
|
a.MaxValue,
|
a.StandardValue,
|
a.MinValue,
|
a.Notes,
|
a.IsPass,
|
a.FcheckLevel,
|
a.FacLevel,
|
a.QsCode,
|
a.QsName
|
}).Select((a, b) => new QsItemOqcItem
|
{
|
ProjName = a.ProjName,
|
Id = a.Id,
|
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,
|
isCheck = SqlFunc.AggregateCount(b.Id),
|
Result = a.IsPass == 1 &&
|
a.LevelNum == SqlFunc.AggregateCount(b.Id) ? "合格"
|
: a.IsPass == 0 &&
|
a.LevelNum == SqlFunc.AggregateCount(b.Id) ? "不合格"
|
: "未完成"
|
}).OrderBy("result desc").ToList();
|
}
|
|
public dynamic save(RKJDto rkjDto)
|
{
|
var xj = rkjDto.from;
|
var items = rkjDto.items;
|
var userNo = rkjDto.userNo;
|
|
SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
var commit = 0;
|
|
xj.CreateBy = userNo;
|
xj.CreateDate = DateTime.Now;
|
|
// 确保检验单号被正确设置
|
if (string.IsNullOrEmpty(xj.ReleaseNo))
|
{
|
xj.ReleaseNo = getMaxBillNo();
|
}
|
|
// 确保生产线编号被正确设置
|
if (string.IsNullOrEmpty(xj.LineNo) && !string.IsNullOrEmpty(xj.Daa015))
|
{
|
xj.LineNo = xj.Daa015;
|
}
|
|
// 确保工单号被正确设置
|
if (string.IsNullOrEmpty(xj.BillNo) && !string.IsNullOrEmpty(xj.RbillNo))
|
{
|
xj.BillNo = xj.RbillNo;
|
}
|
|
// 确保物料ID被正确设置（如果为空，尝试从物料编码获取）
|
if (xj.ItemId == null && !string.IsNullOrEmpty(xj.ItemNo))
|
{
|
// 从物料编码获取物料ID
|
var itemId = db.Queryable<MesItems>()
|
.Where(m => m.ItemNo == xj.ItemNo)
|
.Select(m => m.Id)
|
.First();
|
|
if (itemId > 0)
|
{
|
xj.ItemId = itemId;
|
}
|
}
|
|
// 新增：保存送检批次
|
if (!string.IsNullOrEmpty(rkjDto.RbillNo))
|
xj.RbillNo = rkjDto.RbillNo;
|
|
// 确保Quantity字段被正确设置
|
if (xj.Quantity == null && rkjDto.quantity != null)
|
{
|
xj.Quantity = rkjDto.quantity;
|
}
|
|
// 确保提交状态被正确设置（默认为未提交）
|
if (xj.Fsubmit == null)
|
{
|
xj.Fsubmit = 0;
|
}
|
|
var pid = db.Insertable(xj).ExecuteReturnIdentity();
|
|
xj.Id = pid;
|
|
rkjDto.gid = pid;
|
|
foreach (var item in items) item.Pid = pid;
|
|
commit += db.Insertable(items).ExecuteCommand();
|
|
return commit;
|
});
|
|
|
// using (var connection =
|
// new OracleConnection(OracleSQLHelper.ConnectionString)) // connection.Open();
|
//
|
// var transaction = connection.BeginTransaction();
|
//
|
// try
|
// {
|
// using (var command = new OracleCommand())
|
// {
|
// command.Connection = connection;
|
// command.Transaction = transaction;
|
// command.CommandText = "insert_and_update_picture_RKJ";
|
// command.CommandType = CommandType.StoredProcedure;
|
//
|
// // Add parameters
|
// command.Parameters.Add("p_QS_TYPE", OracleDbType.Varchar2)
|
// .Value = "3";
|
// command.Parameters.Add("p_MOID_NUM", OracleDbType.Varchar2)
|
// .Value = rkjDto.moidNum;
|
// command.Parameters.Add("p_pid", OracleDbType.Int32).Value =
|
// xj.Id;
|
//
|
// command.ExecuteNonQuery();
|
// }
|
//
|
// transaction.Commit();
|
// }
|
// catch (Exception)
|
// {
|
// transaction.Rollback();
|
// throw;
|
// }
|
// }
|
|
rkjDto.items = GetItems(xj.Id, null);
|
|
rkjDto.items.ForEach(s =>
|
{
|
if (s.MaxValue != null || s.StandardValue != null ||
|
s.MinValue != null) return;
|
var detail = new QsItemOqcItemDetail();
|
detail.Pid = s.Id;
|
detail.Gid = rkjDto.gid;
|
detail.Fstand = "√";
|
detail.FcheckResu = "1";
|
detail.UpdateBy = rkjDto.userNo;
|
detail.count = (int?)s.LevelNum;
|
SetQSItemDetail(detail);
|
});
|
|
return Convert.ToInt32(xj.Id);
|
}
|
|
public int SetQSItemDetail(QsItemOqcItemDetail detail)
|
{
|
var oracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
List<QsItemOqcItemDetail> result = new();
|
for (var i = 0; i < detail.count; i++)
|
{
|
var item = new QsItemOqcItemDetail();
|
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;
|
}
|
|
private int autoResult(QsItemOqcItemDetail detail)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
// Single 没有查询到结果时返回Null，如果结果大于1条会抛出错误
|
var QsItemOqcItem = db.Queryable<QsItemOqcItem>()
|
.Single(s => s.Id == detail.Pid);
|
|
if (QsItemOqcItem == null) return 0;
|
|
//查询这个检验项目下的检验结果
|
var count = db.Queryable<QsItemOqcItemDetail>()
|
.Where(s => s.Pid == detail.Pid).Count();
|
|
|
var result = 0;
|
|
//检验实际结果不等于应该检验的个数时直接推出
|
if (QsItemOqcItem.LevelNum != count) return 0;
|
|
//合格的有多少个
|
var passCount = db.Queryable<QsItemOqcItemDetail>()
|
.Where(s => s.Pid == detail.Pid && s.Fstand == "√").Count();
|
|
if (count == passCount)
|
result = 1;
|
else if (count - passCount < QsItemOqcItem.FreQty) result = 1;
|
|
var useTransactionWithOracle = SqlSugarHelper.UseTransactionWithOracle(
|
db =>
|
{
|
var commit = 0;
|
commit += db.Updateable<QsItemOqcItem>()
|
.SetColumns(s => s.IsPass == result)
|
.Where(s => s.Id == detail.Pid)
|
.ExecuteCommand();
|
|
return commit;
|
});
|
|
var isNull = db.Queryable<QsItemOqcItem>()
|
.Where(s => s.Pid == detail.Gid && s.IsPass == null).Count();
|
|
if (isNull > 0) return 1;
|
|
|
//获取检验单的检验项目的个数
|
var sum = db.Queryable<QsItemOqcItem>()
|
.Where(s => s.Pid == detail.Gid).Count();
|
|
if (sum == 0) return 1;
|
|
//获取检验单下的合格的检验项目个数
|
var icount = db.Queryable<QsItemOqcItem>()
|
.Where(s => s.Pid == detail.Gid && s.IsPass == 1).Count();
|
|
var FcheckResu = "不合格";
|
|
//实际个数等于理论个数时对检验单进行判定
|
if (sum == icount)
|
//合格的检验结果等于总检验数视为合格
|
FcheckResu = "合格";
|
|
SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcReq>()
|
.SetColumns(s => s.FcheckResu == FcheckResu)
|
.SetColumns(s => s.FcheckDate == DateTime.Now)
|
.SetColumns(s => s.FcheckBy == detail.CreateBy)
|
.SetColumns(s => s.LastupdateBy == detail.CreateBy)
|
.SetColumns(s => s.LastupdateDate == DateTime.Now)
|
.Where(s => s.Id == detail.Gid)
|
.ExecuteCommand();
|
});
|
|
if (FcheckResu.Equals("不合格"))
|
//自动生成入库检异常对策
|
saveDetect02(detail.Gid, detail.CreateBy);
|
|
return useTransactionWithOracle;
|
}
|
|
public int saveDetect02(decimal? gid, string? createBy)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
var qsItemOqcReq =
|
db.Queryable<QsItemOqcReq>().Single(s => s.Id == gid);
|
|
var mesInvItemIns = db.Queryable<MesInvItemIns>()
|
.Single(s => s.BillNo == qsItemOqcReq.BillNo);
|
|
var mesSchemeResult = db.Queryable<Womdaa>()
|
.Single(s => s.Daa001 == mesInvItemIns.CbillNo);
|
|
var entity = new MesQaItemsDetect02();
|
// entity.ItemNo = mesSchemeResult.BoardItem;
|
// entity.BoardItem = mesSchemeResult.BoardItem;
|
entity.LineNo = mesSchemeResult.Daa015;
|
// entity.Aufnr = mesSchemeResult.TaskNo;
|
entity.ReleaseNo = qsItemOqcReq.ReleaseNo;
|
entity.LotNo = qsItemOqcReq.BillNo;
|
entity.FcheckDate = qsItemOqcReq.FcheckDate;
|
entity.FcheckMemo = qsItemOqcReq.Remarks;
|
entity.Gid = qsItemOqcReq.Id;
|
// entity.PlanQty = qsItemOqcReq.FcheckDate;
|
|
|
entity.FcheckResu = "不合格";
|
entity.FcheckLevel = "严重";
|
entity.CreateDate = DateTime.Now;
|
entity.Factory = "10000";
|
entity.Company = "1000";
|
entity.Ftype = "4";
|
entity.Fversion = 0;
|
entity.Modify1Flag = 0;
|
entity.IpqcStatus = 0;
|
entity.Fsubmit = 1;
|
entity.CreateBy = createBy;
|
entity.FcheckBy = createBy;
|
|
return SqlSugarHelper.UseTransactionWithOracle(db => db
|
.Insertable(entity)
|
.ExecuteCommand());
|
}
|
|
|
public List<QsItemOqcReq> getPage(XJPageResult queryObj)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
|
var id = Convert.ToDecimal(queryObj.id);
|
|
string[]? lineNo = null;
|
|
if (StringUtil.IsNotNullOrEmpty(queryObj.createUser))
|
lineNo = _baseService.getUserLineNo(queryObj.createUser);
|
|
return db
|
.Queryable<QsItemOqcReq, Womdaa, MesItems>((a, da, b) =>
|
new JoinQueryInfos(
|
JoinType.Left, da.Daa001 == a.BillNo,
|
JoinType.Left, a.ItemId == b.Id
|
))
|
.WhereIF(lineNo != null && lineNo.Length > 0,
|
(a, da, b) => lineNo.Contains(da.Daa015))
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
"未完成".Equals(queryObj.result),
|
(a, da, b) => a.FcheckResu == null || a.FcheckResu == "")
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.result) &&
|
!"未完成".Equals(queryObj.result),
|
(a, da, b) => a.FcheckResu != null && a.FcheckResu != "")
|
.WhereIF(id > 0, (a, da, b) => a.Id == id)
|
// 添加fsubmit字段过滤逻辑
|
.WhereIF(
|
StringUtil.IsNotNullOrEmpty(queryObj.fsubmit) && queryObj.fsubmit == "1",
|
(a, da, b) => a.Fsubmit == 1)
|
.WhereIF(
|
StringUtil.IsNullOrEmpty(queryObj.fsubmit) || queryObj.fsubmit == "0",
|
(a, da, b) => a.Fsubmit == 0 || a.Fsubmit == null)
|
.Select((a, da, b) => new QsItemOqcReq
|
{
|
Id = a.Id,
|
ReleaseNo = a.ReleaseNo,
|
CreateBy = a.CreateBy,
|
CreateDate = a.CreateDate,
|
BillNo = a.BillNo,
|
RbillNo = a.RbillNo,
|
ItemNo = a.ItemNo,
|
ItemId = a.ItemId,
|
LineNo = a.LineNo,
|
FcheckResu = a.FcheckResu,
|
FcheckBy = a.FcheckBy,
|
FcheckDate = a.FcheckDate,
|
Remarks = a.Remarks,
|
WorkShop = a.WorkShop,
|
DepartmentId = a.DepartmentId,
|
Fsubmit = a.Fsubmit, // 添加fsubmit字段到返回结果
|
Blyy = a.Blyy, // 添加不良原因字段
|
Pszt = a.Pszt, // 添加评审状态字段
|
Sscj = a.Sscj, // 添加所属车间字段
|
FngDesc = a.FngDesc, // 添加不良描述字段
|
// 从关联表获取的字段
|
ItemName = b.ItemName,
|
ItemModel = b.ItemModel,
|
Daa015 = da.Daa015,
|
Quantity = a.Quantity
|
}).OrderBy(a => a.CreateDate, OrderByType.Desc)
|
.ToPageList(queryObj.PageIndex, queryObj.Limit);
|
}
|
|
//删除主表并且连级删除子表和孙表
|
public int removeXJ(decimal? id)
|
{
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
var commit = 0;
|
//删除主表
|
commit += db.Deleteable<QsItemOqcReq>().Where(s => s.Id == id)
|
.ExecuteCommand();
|
//删除子表
|
commit += db.Deleteable<QsItemOqcItem>().Where(s => s.Pid == id)
|
.ExecuteCommand();
|
//删除孙表
|
commit += db.Deleteable<QsItemOqcItemDetail>()
|
.Where(s => s.Gid == id)
|
.ExecuteCommand();
|
return commit;
|
});
|
|
return withOracle;
|
}
|
|
|
public RKJDto getXjDetail02ById(decimal? id)
|
{
|
var rkjDto = new RKJDto();
|
|
var db = SqlSugarHelper.GetInstance();
|
var qsItemOqcItem =
|
db.Queryable<QsItemOqcItem>().Single(s => s.Id == id);
|
|
if (qsItemOqcItem.IsPass == 0)
|
qsItemOqcItem.Result = "不合格";
|
else if (qsItemOqcItem.IsPass == 1)
|
qsItemOqcItem.Result = "合格";
|
else
|
qsItemOqcItem.Result = "未完成";
|
|
if (qsItemOqcItem.Picture is { Length: > 0 })
|
qsItemOqcItem.imageData =
|
Convert.ToBase64String(qsItemOqcItem.Picture);
|
|
//获取不合格数
|
var count = db.Queryable<QsItemOqcItemDetail>()
|
.Where(s => s.Fstand == "×" && s.Pid == id).Count();
|
|
qsItemOqcItem.Unqualified = count;
|
|
rkjDto.ItemXj01 = qsItemOqcItem;
|
|
|
rkjDto.ItemXj02s = db.Queryable<QsItemOqcItemDetail>()
|
.Where(s => s.Pid == id)
|
.ToList();
|
|
return rkjDto;
|
}
|
|
public int UpdateQSItemDetail(QsItemOqcItemDetail detail)
|
{
|
var withOracle = SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcItemDetail>()
|
.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(RKJDto dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcReq>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.gid)
|
.ExecuteCommand();
|
});
|
}
|
|
//子表修改备注字段
|
public int saveRemarksPid(RKJDto dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcItem>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.pid)
|
.ExecuteCommand();
|
});
|
}
|
|
//孙表修改备注字段
|
public int saveRemarksById(RKJDto dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcItemDetail>()
|
.SetColumns(it =>
|
it.Remarks == dto.Remarks) //SetColumns是可以叠加的 写2个就2个字段赋值
|
.Where(it => it.Id == dto.id)
|
.ExecuteCommand();
|
});
|
}
|
|
public int saveItem(RKJDto rkjDto)
|
{
|
var items = rkjDto.items;
|
var userNo = rkjDto.userNo;
|
|
SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
foreach (var item in items) item.Pid = rkjDto.gid;
|
|
return db.Insertable(items).ExecuteCommand();
|
});
|
|
rkjDto.items = GetItems(rkjDto.gid, null);
|
|
rkjDto.items.ForEach(s =>
|
{
|
if (s.MaxValue != null || s.StandardValue != null ||
|
s.MinValue != null) return;
|
var detail = new QsItemOqcItemDetail();
|
detail.Pid = s.Id;
|
detail.Gid = rkjDto.gid;
|
detail.Fstand = "√";
|
detail.FcheckResu = "1";
|
detail.UpdateBy = rkjDto.userNo;
|
detail.count = (int?)s.LevelNum;
|
SetQSItemDetail(detail);
|
});
|
|
return Convert.ToInt32(rkjDto.gid);
|
}
|
|
/// <summary>
|
/// 提交检验单
|
/// </summary>
|
/// <param name="id">检验单ID</param>
|
/// <param name="userNo">提交用户</param>
|
/// <returns>更新结果</returns>
|
public int SubmitInspection(decimal id, string userNo)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
// 获取检验单详细信息
|
var inspectionData = db.Queryable<QsItemOqcReq>()
|
.Where(it => it.Id == id)
|
.First();
|
|
if (inspectionData == null)
|
{
|
throw new Exception("检验单不存在");
|
}
|
|
// 检查钉钉推送条件：PSZT为待判，且不良原因、不良描述、所属车间不为空
|
bool shouldPushToDingTalk = inspectionData.Pszt == "待判" &&
|
!string.IsNullOrEmpty(inspectionData.Blyy) &&
|
!string.IsNullOrEmpty(inspectionData.FngDesc) &&
|
!string.IsNullOrEmpty(inspectionData.Sscj);
|
|
// 如果满足推送条件，则推送到钉钉
|
if (shouldPushToDingTalk)
|
{
|
try
|
{
|
// 构建钉钉推送数据
|
var dingTalkData = new GetQcIssueResultDetail
|
{
|
FbatchQty = inspectionData.Quantity?.ToString(), // 送检数量
|
ItemName = inspectionData.ItemName,
|
ItemNo = inspectionData.ItemNo,
|
SuppName = "生产入库", // 固定值
|
AppicationReason = inspectionData.FngDesc, // 不良描述
|
BadReason = inspectionData.Blyy, // 不良原因
|
remark = inspectionData.Remarks,
|
ReleaseNo = inspectionData.ReleaseNo,
|
StaffNo = userNo,
|
IqcStatus = inspectionData.Pszt, // 评审状态
|
Department = inspectionData.Sscj, // 使用所属车间作为部门
|
WorkShop = inspectionData.Sscj, // 所属车间
|
EMERGENCY = "0" // 固定值为0
|
};
|
|
// 调用钉钉推送服务
|
var qcIssueResultManager = new QcIssueResultManager();
|
string dingTalkResult = qcIssueResultManager.GetProcessNo(dingTalkData, "RKJ");
|
|
// 解析钉钉返回结果，获取流程实例ID
|
if (dingTalkResult.Contains("钉钉工作流实例启动成功"))
|
{
|
// 提取instanceId
|
int startIndex = dingTalkResult.IndexOf("\"InstanceId\":\"") + 14;
|
int endIndex = dingTalkResult.IndexOf("\"", startIndex);
|
if (startIndex > 13 && endIndex > startIndex)
|
{
|
string instanceId = dingTalkResult.Substring(startIndex, endIndex - startIndex);
|
|
// 保存钉钉流程信息
|
qcIssueResultManager.SaveProcessNo(
|
inspectionData.ReleaseNo,
|
instanceId,
|
userNo,
|
inspectionData.Sscj,
|
inspectionData.Pszt
|
);
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
// 钉钉推送失败不影响检验提交，只记录日志
|
Console.WriteLine($"钉钉推送失败: {ex.Message}");
|
}
|
}
|
|
// 更新检验单状态
|
return db.Updateable<QsItemOqcReq>()
|
.SetColumns(it => it.Fsubmit == 1) // 设置提交状态为1（已提交）
|
.SetColumns(it => it.FcheckBy == userNo) // 设置检验人
|
.SetColumns(it => it.FcheckDate == DateTime.Now) // 设置检验时间
|
.Where(it => it.Id == id)
|
.ExecuteCommand();
|
});
|
}
|
|
/// <summary>
|
/// 刷新检验项目 - 调用存储过程
|
/// </summary>
|
/// <param name="id">检验单ID</param>
|
/// <param name="no">检验单号</param>
|
/// <param name="user">用户</param>
|
/// <returns>刷新结果</returns>
|
public (int result, string message) GenUpdate(decimal? id, string? no, string? user)
|
{
|
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),
|
outputResult,
|
outputMessage
|
};
|
|
var db = SqlSugarHelper.GetInstance();
|
db.Ado.ExecuteCommand(
|
"BEGIN PRC_GEN_UPDATE3(:P_ID, :P_NO, :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);
|
}
|
|
/// <summary>
|
/// 获取附件信息
|
/// </summary>
|
/// <param name="ItemNo">物料编码</param>
|
/// <returns>附件列表</returns>
|
public List<QamftpDto> GetAttachments(string ItemNo)
|
{
|
var db = SqlSugarHelper.GetInstance();
|
try
|
{
|
return db.Queryable<MesQamftp>()
|
.Where(x => x.ItemNo == ItemNo)
|
.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
|
}).ToList();
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"查询附件信息失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 从FTP服务器获取文件
|
/// </summary>
|
/// <param name="itemNo">物料编码</param>
|
/// <param name="fileName">文件名</param>
|
/// <param name="ftpServer">FTP服务器地址</param>
|
/// <returns>文件字节数组</returns>
|
public byte[] GetFtpFile(string itemNo, string fileName, string ftpServer)
|
{
|
// 参数验证
|
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文件路径 - RKJ使用FQC文件夹
|
string ftpPath = $"{normalizedServer}/FQC/{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($"获取FTP文件失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 获取文件内容类型
|
/// </summary>
|
/// <param name="fileName">文件名</param>
|
/// <returns>内容类型</returns>
|
public string GetContentType(string fileName)
|
{
|
if (string.IsNullOrEmpty(fileName))
|
return "application/octet-stream";
|
|
var extension = System.IO.Path.GetExtension(fileName).ToLowerInvariant();
|
return extension switch
|
{
|
".pdf" => "application/pdf",
|
".jpg" or ".jpeg" => "image/jpeg",
|
".png" => "image/png",
|
".gif" => "image/gif",
|
".bmp" => "image/bmp",
|
".txt" => "text/plain",
|
".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",
|
_ => "application/octet-stream"
|
};
|
}
|
|
/// <summary>
|
/// 标准化FTP服务器地址
|
/// </summary>
|
/// <param name="ftpServer">FTP服务器地址</param>
|
/// <returns>标准化后的地址</returns>
|
private string NormalizeFtpServer(string ftpServer)
|
{
|
if (string.IsNullOrEmpty(ftpServer))
|
throw new ArgumentException("FTP服务器地址不能为空");
|
|
// 移除可能的协议前缀
|
var normalized = ftpServer.Trim();
|
if (normalized.StartsWith("ftp://", StringComparison.OrdinalIgnoreCase))
|
{
|
normalized = normalized.Substring(6);
|
}
|
else if (normalized.StartsWith("ftps://", StringComparison.OrdinalIgnoreCase))
|
{
|
normalized = normalized.Substring(7);
|
}
|
|
// 确保以ftp://开头
|
if (!normalized.StartsWith("ftp://", StringComparison.OrdinalIgnoreCase))
|
{
|
normalized = "ftp://" + normalized;
|
}
|
|
return normalized;
|
}
|
|
/// <summary>
|
/// 保存下拉框字段（不良原因、评审状态、所属车间）
|
/// </summary>
|
/// <param name="dto">包含字段数据的DTO</param>
|
/// <returns>更新结果</returns>
|
public int SaveDropdownFields(RKJDto dto)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcReq>()
|
.SetColumns(it => it.Blyy == dto.BLYY)
|
.SetColumns(it => it.Pszt == dto.PSZT)
|
.SetColumns(it => it.Sscj == dto.SSCJ)
|
.Where(it => it.ReleaseNo == dto.releaseNo)
|
.ExecuteCommand();
|
});
|
}
|
|
/// <summary>
|
/// 保存不良描述
|
/// </summary>
|
/// <param name="gid">检验单ID</param>
|
/// <param name="fngDesc">不良描述</param>
|
/// <returns>更新结果</returns>
|
public int SaveFngDesc(decimal gid, string fngDesc)
|
{
|
return SqlSugarHelper.UseTransactionWithOracle(db =>
|
{
|
return db.Updateable<QsItemOqcReq>()
|
.SetColumns(it => it.FngDesc == fngDesc)
|
.Where(it => it.Id == gid)
|
.ExecuteCommand();
|
});
|
}
|
}
|
