2382 lines
136 KiB
C#
Executable File
2382 lines
136 KiB
C#
Executable File
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Microsoft.Extensions.DependencyInjection;
|
|
using Opsoft.Data;
|
|
using Opsoft.NetCore.O3App.Domain;
|
|
using Opsoft.NetCore.O3App.Domain.Interface;
|
|
using Opsoft.NetCore.O3App.Domain.UnitOfWork.IPadReport.IRepositories;
|
|
using Opsoft.NetCore.O3App.Shared;
|
|
using System.Data;
|
|
using Opsoft.Data.Model;
|
|
using System.Linq;
|
|
using System.Collections.Generic;
|
|
using Opsoft.NetCore.O3App.Domain.UnitOfWork.IPadReport.Model;
|
|
using System;
|
|
using System.Collections;
|
|
using Microsoft.AspNetCore.Http;
|
|
|
|
namespace Opsoft.NetCore.O3App.Repositories.IPadReport
|
|
{
|
|
/// <summary>
|
|
/// 工序首检
|
|
/// </summary>
|
|
[MapTo(typeof(IInspectionRepository), ServiceLifetime.Transient)]
|
|
public class InspectionRepository : RepositoryBase<BaseEntity>, IInspectionRepository
|
|
{
|
|
public InspectionRepository() : base()
|
|
{
|
|
|
|
}
|
|
/// <summary>
|
|
/// 工序首检列表接口(状态查询:已完成、未完成
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> GetFirstInspections(GetFirstInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//已检、未检状态
|
|
string status = SData.Status.ToStringEx();
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
string sqlWhere = string.Empty;
|
|
object returnData = new { };
|
|
#endregion
|
|
|
|
if (status == "0")
|
|
{
|
|
//未首检
|
|
sqlWhere += " AND (T1.FTESTRESULT='' OR T1.FTESTRESULT IS NULL) ";
|
|
}
|
|
else if (status == "1")
|
|
{
|
|
//已首检
|
|
sqlWhere += " AND (T1.FTESTRESULT!='' AND T1.FTESTRESULT IS NOT NULL) ";
|
|
}
|
|
//资源NO
|
|
if (resource != null && resource.Count > 0)
|
|
{
|
|
sqlWhere += $@" AND T5.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"SELECT TOP {size} X.*,(SELECT COUNT(1) FROM T_SFC_FIRSTINPECTION T1 LEFT JOIN
|
|
(
|
|
SELECT DISTINCT
|
|
FINTERID,
|
|
FRESOURCESID,
|
|
FPROCESSID,
|
|
FCODE
|
|
FROM T_SFC_REPORT
|
|
WHERE ISNULL(FCODE, '') <> ''
|
|
) A
|
|
ON A.FCODE = T1.FCODE AND T1.FREPORTID=A.FINTERID
|
|
LEFT JOIN T_ENG_RESOURCE T5
|
|
ON T5.FINTERID = A.FRESOURCESID WHERE FWORKTYPE = 3 {sqlWhere}) AS TOTALCOUNT FROM
|
|
(
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY T1.FDATE DESC, T1.C_YMD DESC) AS ROWNO,
|
|
T1.FDATE AS FDate,
|
|
T1.FINTERID AS FInterID,
|
|
T4.FBILLNO AS FICMONO, --任务单号
|
|
T1.FFLOWCARDBILLNO AS FFlowCardNo, --流转卡号
|
|
T1.FCODE AS FCode, --工序识别码
|
|
A.FBILLNO AS FReportNo, --汇报编号
|
|
T2.FNUMBER AS FMaterialNo, --产品编码
|
|
T2.FNAME AS FMaterialName, --产品名称
|
|
T5.FNUMBER AS FResourceNo, --资源代码
|
|
T7.FNUMBER AS FProcessNo,
|
|
T7.FNAME AS FProcessName, --工序名称
|
|
CONVERT(VARCHAR,T1.C_YMD,120) AS CreateTime, --创建时间
|
|
T1.FTESTRESULT AS FTestResult, --检验结果 0=合格,1=不合格
|
|
T1.FSTATUS AS FStatus, --数据状态
|
|
T6.FNAME AS FStatusName, --状态名称
|
|
T1.FTESTTYPE AS FTestType, --检验方式 1=全检;2=抽检
|
|
T1.FQTY AS FQty, --数量
|
|
T1.FCOUNT AS FCount, --送检数量
|
|
T1.FACCEPTQTY AS FAcceptQty, --合格数
|
|
T1.FUNACCEPTQTY AS FUnAcceptQty --不合格数
|
|
FROM T_SFC_FIRSTINPECTION T1
|
|
LEFT JOIN T_BD_MATERIAL T2 ON T2.FINTERID=T1.FMATERIALID
|
|
LEFT JOIN T_SFC_FLOWCARD T3 ON T3.FINTERID=T1.FFLOWCARDID
|
|
LEFT JOIN T_PRD_MO T4 ON T4.FINTERID=T3.FMOID
|
|
LEFT JOIN (SELECT DISTINCT FINTERID,FBILLNO,FRESOURCESID,FPROCESSID,FCODE FROM T_SFC_REPORT WHERE ISNULL(FCODE,'')<>'') A ON A.FCODE = T1.FCODE AND T1.FREPORTID=A.FINTERID
|
|
LEFT JOIN T_ENG_RESOURCE T5 ON T5.FINTERID=A.FRESOURCESID
|
|
LEFT JOIN T_SFC_PROCESS T7 ON T1.FPROCESSID=T7.FINTERID
|
|
LEFT JOIN SYS_STATUS T6 ON T6.FITEMID=T1.FSTATUS
|
|
WHERE T1.FWORKTYPE=3 {sqlWhere}
|
|
) X WHERE ROWNO > {size * (page - 1)} ORDER BY ROWNO ";
|
|
//获取数据
|
|
DataTable dtData = context.Database.SqlQuery(sqlSearch);
|
|
//FInterID集合
|
|
List<string> finteridList = dtData.Select().AsEnumerable<DataRow>().Select(o => (o["FInterID"].ToStringEx())).ToList();
|
|
List<string> listsql = new List<string>();
|
|
//子项数据查询
|
|
//不良原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
FINTERID AS FInterID,
|
|
FNUMBER AS FNumber,
|
|
FNAME AS FName
|
|
FROM T_QM_BADREASONS
|
|
WHERE FSTATUS=40 AND FDELETED=0");
|
|
//样品一组检验方案
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
'' AS FDetailID,
|
|
0 AS FEntryID,
|
|
T3.FINTERID AS FTestProjectID, --检验项目内码
|
|
T3.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T3.FNAME AS FTestProjectName, --检验项目名称
|
|
T2.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T2.FVALUE AS TargetValue, --目标值
|
|
T2.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T2.FLWERLIMIT AS FLwerLimit, --下限值
|
|
0 AS FTestValue, --检验值(定量)
|
|
0 AS FTestResult, --检验结果
|
|
T2.FTESTWAY AS FTestWayID, --检验方法
|
|
T2.FTESTSTANDARD AS FTestStandardID, --质量标准
|
|
T2.FTESTINSTRUMENT AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_FIRSTINPECTION T1
|
|
LEFT JOIN T_QM_INSPECTSOLUTIONENTRY T2 ON T2.FINTERID=T1.FTESTWAYID
|
|
LEFT JOIN T_QM_INSPECTITEM T3 ON T3.FINTERID=T2.FINSPECTITEMID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FInterID");
|
|
//已经检验的数据
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T2.FINTERID AS FTestProjectID, --检验项目内码
|
|
T2.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T2.FNAME AS FTestProjectName, --检验项目名称
|
|
T1.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T1.FVALUE AS TargetValue, --目标值
|
|
T1.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T1.FLWERLIMIT AS FLwerLimit, --下限值
|
|
T1.FQUANTESTVALUE AS FTestValue, --检验值(定量)
|
|
T1.FTESTRESULT AS FTestResult, --检验结果
|
|
T1.FTESTWAYID AS FTestWayID, --检验方法
|
|
T1.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T1.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_FIRSTINPECTIONENTRY T1
|
|
LEFT JOIN T_QM_INSPECTITEM T2 ON T1.FTESTPROJECTID=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID");
|
|
//已经提交的不合格原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T1.FFAULTREASON AS FFaultReasonID,
|
|
T2.FNAME AS FFaultReasonName,
|
|
T1.FFAULTQTY AS FFaultQty
|
|
FROM T_SFC_FIRSTINPECTIONENTRY1 T1
|
|
LEFT JOIN T_QM_BADREASONS T2 ON T1.FFAULTREASON=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID
|
|
");
|
|
//获取数据
|
|
DataSet dsAll = context.Database.SqlQuery(listsql);
|
|
//数据集合
|
|
List<object> listFirstInspection = new List<object>();
|
|
//数据表体结合
|
|
DataTable dtTestWay = new DataTable();
|
|
DataTable dtTestItemData = new DataTable();
|
|
DataTable dtBadReasonsData = new DataTable();
|
|
DataRow[] dtTemp = null;
|
|
int count = 0;
|
|
foreach (DataRow item in dtData.Rows)
|
|
{
|
|
count = item["TOTALCOUNT"].ToInt();
|
|
//表体数据抓取
|
|
//检验方案
|
|
dtTemp = dsAll.Tables[1].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestWay = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestWay = new DataTable();
|
|
}
|
|
//检验项目明细
|
|
dtTemp = dsAll.Tables[2].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestItemData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestItemData = new DataTable();
|
|
}
|
|
//不合格原因
|
|
dtTemp = dsAll.Tables[3].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtBadReasonsData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtBadReasonsData = new DataTable();
|
|
}
|
|
listFirstInspection.Add(
|
|
new
|
|
{
|
|
FDate = item["FDate"].ToStringEx(),
|
|
FInterID = item["FInterID"].ToStringEx(),
|
|
FICMONO = item["FICMONO"].ToStringEx(),
|
|
FFlowCardNo = item["FFlowCardNo"].ToStringEx(),
|
|
FCode = item["FCode"].ToStringEx(),
|
|
FReportNo = item["FReportNo"].ToStringEx(),
|
|
FMaterialNo = item["FMaterialNo"].ToStringEx(),
|
|
FMaterialName = item["FMaterialName"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProcessNo = item["FProcessNo"].ToStringEx(),
|
|
FProcessName = item["FProcessName"].ToStringEx(),
|
|
CreateTime = item["CreateTime"].ToStringEx(),
|
|
FTestResult = item["FTestResult"].ToStringEx(),
|
|
FStatus = item["FStatus"].ToStringEx(),
|
|
FStatusName = item["FStatusName"].ToStringEx(),
|
|
FTestType = item["FTestType"].ToStringEx(),
|
|
FQty = item["FQty"].ToDecimal(),
|
|
FCount = item["FCount"].ToDecimal(),
|
|
FAcceptQty = item["FAcceptQty"].ToDecimal(),
|
|
FUnAcceptQty = item["FUnAcceptQty"].ToDecimal(),
|
|
BadReasons = dsAll.Tables[0],
|
|
TestWay = dtTestWay,
|
|
TestItemData = dtTestItemData,
|
|
BadReasonsData = dtBadReasonsData
|
|
});
|
|
}
|
|
returnData = new
|
|
{
|
|
firstInspection = listFirstInspection,
|
|
toltalCount = count
|
|
};
|
|
return await Task.Run(() => returnData);
|
|
|
|
}
|
|
/// <summary>
|
|
/// 工序首检列表接口(状态查询:已完成、未完成
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<List<object>> GetFirstInspection(GetFirstInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//已检、未检状态
|
|
string status = SData.Status.ToStringEx();
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
string sqlWhere = string.Empty;
|
|
object returnData = new { };
|
|
#endregion
|
|
|
|
if (status == "0")
|
|
{
|
|
//未首检
|
|
sqlWhere += " AND (T1.FTESTRESULT='' OR T1.FTESTRESULT IS NULL) ";
|
|
}
|
|
else if (status == "1")
|
|
{
|
|
//已首检
|
|
sqlWhere += " AND (T1.FTESTRESULT!='' AND T1.FTESTRESULT IS NOT NULL) ";
|
|
}
|
|
//资源NO
|
|
if (resource != null && resource.Count > 0)
|
|
{
|
|
sqlWhere += $@" AND T5.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"SELECT TOP {size} X.* FROM
|
|
(
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY T1.FDATE DESC, T1.C_YMD DESC) AS ROWNO,
|
|
T1.FDATE AS FDate,
|
|
T1.FINTERID AS FInterID,
|
|
T4.FBILLNO AS FICMONO, --任务单号
|
|
T1.FFLOWCARDBILLNO AS FFlowCardNo, --流转卡号
|
|
T2.FNUMBER AS FMaterialNo, --产品编码
|
|
T2.FNAME AS FMaterialName, --产品名称
|
|
T5.FNUMBER AS FResourceNo, --资源代码
|
|
T7.FNUMBER AS FProcessNo,
|
|
CONVERT(VARCHAR,T1.C_YMD,120) AS CreateTime, --创建时间
|
|
T1.FTESTRESULT AS FTestResult, --检验结果 0=合格,1=不合格
|
|
T1.FSTATUS AS FStatus, --数据状态
|
|
T6.FNAME AS FStatusName, --状态名称
|
|
T1.FTESTTYPE AS FTestType, --检验方式 1=全检;2=抽检
|
|
T1.FQTY AS FQty, --数量
|
|
T1.FCOUNT AS FCount, --送检数量
|
|
T1.FACCEPTQTY AS FAcceptQty, --合格数
|
|
T1.FUNACCEPTQTY AS FUnAcceptQty --不合格数
|
|
FROM T_SFC_FIRSTINPECTION T1
|
|
LEFT JOIN T_BD_MATERIAL T2 ON T2.FINTERID=T1.FMATERIALID
|
|
LEFT JOIN T_SFC_FLOWCARD T3 ON T3.FINTERID=T1.FFLOWCARDID
|
|
LEFT JOIN T_PRD_MO T4 ON T4.FINTERID=T3.FMOID
|
|
LEFT JOIN (SELECT DISTINCT FINTERID,FBILLNO,FRESOURCESID,FPROCESSID,FCODE FROM T_SFC_REPORT WHERE ISNULL(FCODE,'')<>'') A ON A.FCODE = T1.FCODE AND T1.FREPORTID=A.FINTERID
|
|
LEFT JOIN T_ENG_RESOURCE T5 ON T5.FINTERID=A.FRESOURCESID
|
|
LEFT JOIN T_SFC_PROCESS T7 ON T1.FPROCESSID=T7.FINTERID
|
|
LEFT JOIN SYS_STATUS T6 ON T6.FITEMID=T1.FSTATUS
|
|
WHERE T1.FWORKTYPE=3 {sqlWhere}
|
|
) X WHERE ROWNO > {size * (page - 1)} ORDER BY ROWNO ";
|
|
//获取数据
|
|
DataTable dtData = context.Database.SqlQuery(sqlSearch);
|
|
//FInterID集合
|
|
List<string> finteridList = dtData.Select().AsEnumerable<DataRow>().Select(o => (o["FInterID"].ToStringEx())).ToList();
|
|
List<string> listsql = new List<string>();
|
|
//子项数据查询
|
|
//不良原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
FINTERID AS FInterID,
|
|
FNUMBER AS FNumber,
|
|
FNAME AS FName
|
|
FROM T_QM_BADREASONS
|
|
WHERE FSTATUS=40 AND FDELETED=0");
|
|
//样品一组检验方案
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
'' AS FDetailID,
|
|
0 AS FEntryID,
|
|
T3.FINTERID AS FTestProjectID, --检验项目内码
|
|
T3.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T3.FNAME AS FTestProjectName, --检验项目名称
|
|
T2.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T2.FVALUE AS TargetValue, --目标值
|
|
T2.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T2.FLWERLIMIT AS FLwerLimit, --下限值
|
|
0 AS FTestValue, --检验值(定量)
|
|
0 AS FTestResult, --检验结果
|
|
T2.FTESTWAY AS FTestWayID, --检验方法
|
|
T2.FTESTSTANDARD AS FTestStandardID, --质量标准
|
|
T2.FTESTINSTRUMENT AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_FIRSTINPECTION T1
|
|
LEFT JOIN T_QM_INSPECTSOLUTIONENTRY T2 ON T2.FINTERID=T1.FTESTWAYID
|
|
LEFT JOIN T_QM_INSPECTITEM T3 ON T3.FINTERID=T2.FINSPECTITEMID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FInterID");
|
|
//已经检验的数据
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T2.FINTERID AS FTestProjectID, --检验项目内码
|
|
T2.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T2.FNAME AS FTestProjectName, --检验项目名称
|
|
T1.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T1.FVALUE AS TargetValue, --目标值
|
|
T1.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T1.FLWERLIMIT AS FLwerLimit, --下限值
|
|
T1.FQUANTESTVALUE AS FTestValue, --检验值(定量)
|
|
T1.FTESTRESULT AS FTestResult, --检验结果
|
|
T1.FTESTWAYID AS FTestWayID, --检验方法
|
|
T1.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T1.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_FIRSTINPECTIONENTRY T1
|
|
LEFT JOIN T_QM_INSPECTITEM T2 ON T1.FTESTPROJECTID=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID");
|
|
//已经提交的不合格原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T1.FFAULTREASON AS FFaultReasonID,
|
|
T2.FNAME AS FFaultReasonName,
|
|
T1.FFAULTQTY AS FFaultQty
|
|
FROM T_SFC_FIRSTINPECTIONENTRY1 T1
|
|
LEFT JOIN T_QM_BADREASONS T2 ON T1.FFAULTREASON=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID
|
|
");
|
|
//获取数据
|
|
DataSet dsAll = context.Database.SqlQuery(listsql);
|
|
//数据集合
|
|
List<object> listFirstInspection = new List<object>();
|
|
//数据表体结合
|
|
DataTable dtTestWay = new DataTable();
|
|
DataTable dtTestItemData = new DataTable();
|
|
DataTable dtBadReasonsData = new DataTable();
|
|
DataRow[] dtTemp = null;
|
|
|
|
foreach (DataRow item in dtData.Rows)
|
|
{
|
|
//表体数据抓取
|
|
//检验方案
|
|
dtTemp = dsAll.Tables[1].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestWay = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestWay = new DataTable();
|
|
}
|
|
//检验项目明细
|
|
dtTemp = dsAll.Tables[2].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestItemData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestItemData = new DataTable();
|
|
}
|
|
//不合格原因
|
|
dtTemp = dsAll.Tables[3].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtBadReasonsData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtBadReasonsData = new DataTable();
|
|
}
|
|
listFirstInspection.Add(
|
|
new
|
|
{
|
|
FDate = item["FDate"].ToStringEx(),
|
|
FInterID = item["FInterID"].ToStringEx(),
|
|
FICMONO = item["FICMONO"].ToStringEx(),
|
|
FFlowCardNo = item["FFlowCardNo"].ToStringEx(),
|
|
FMaterialNo = item["FMaterialNo"].ToStringEx(),
|
|
FMaterialName = item["FMaterialName"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProcessNo = item["FProcessNo"].ToStringEx(),
|
|
CreateTime = item["CreateTime"].ToStringEx(),
|
|
FTestResult = item["FTestResult"].ToStringEx(),
|
|
FStatus = item["FStatus"].ToStringEx(),
|
|
FStatusName = item["FStatusName"].ToStringEx(),
|
|
FTestType = item["FTestType"].ToStringEx(),
|
|
FQty = item["FQty"].ToDecimal(),
|
|
FCount = item["FCount"].ToDecimal(),
|
|
FAcceptQty = item["FAcceptQty"].ToDecimal(),
|
|
FUnAcceptQty = item["FUnAcceptQty"].ToDecimal(),
|
|
BadReasons = dsAll.Tables[0],
|
|
TestWay = dtTestWay,
|
|
TestItemData = dtTestItemData,
|
|
BadReasonsData = dtBadReasonsData
|
|
});
|
|
}
|
|
returnData = new
|
|
{
|
|
firstInspection = listFirstInspection,
|
|
toltalCount = listFirstInspection.Count
|
|
};
|
|
return await Task.Run(() => listFirstInspection);
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 首检数据保存
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<string> SaveFirstInspection(SaveFirstInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
string retMsg = string.Empty;
|
|
List<Dictionary<string, object>> testItemData = SData.testItemData;
|
|
List<Dictionary<string, object>> badReasonsData = SData.badReasonsData;
|
|
var sumFaultQty = badReasonsData.Select(s => s["FFaultQty"].ToDecimal()).Sum();
|
|
if (SData.FUnAcceptQty != sumFaultQty)
|
|
{
|
|
throw new O3AppException("登记的不合格品数合计与不合格数量不相等!!");
|
|
}
|
|
#endregion
|
|
try
|
|
{
|
|
//开启事务
|
|
context.Database.BeginTransaction();
|
|
|
|
//先更新主表数据
|
|
string mySql = $@" UPDATE T_SFC_FIRSTINPECTION
|
|
SET FTESTTYPE={SData.FTestType.ToInt()},FCOUNT={SData.FCount.ToDecimal()}
|
|
,FQTY={SData.FQty.ToDecimal()},FACCEPTQTY={SData.FAcceptQty.ToDecimal()}
|
|
,FUNACCEPTQTY={SData.FUnAcceptQty.ToDecimal()}, FTESTRESULT='{SData.FTestResult.ToStringEx()}',
|
|
FSTATUS=40, FCHECKERID='{LoginInfo.UserID}', FCHECKDATE=GETDATE()
|
|
WHERE FINTERID='{SData.FInterID.ToStringEx()}'";
|
|
if (await context.Database.SqlNonQueryAsync(mySql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
|
|
//判断此首检单对应的是否为汇报最新的首检单数据
|
|
string reportInterid = context.Database.SqlQueryString($@"SELECT FREPORTID FROM T_SFC_FIRSTINPECTION
|
|
WHERE FINTERID='{SData.FInterID.ToStringEx()}'");
|
|
if (!string.IsNullOrEmpty(reportInterid))
|
|
{
|
|
DataTable dtLastData = context.Database.SqlQuery($@"SELECT TOP 1 FINTERID, FBILLNO,FTESTRESULT,FSTATUS
|
|
FROM T_SFC_FIRSTINPECTION
|
|
WHERE FREPORTID='{reportInterid}'
|
|
ORDER BY C_YMD DESC");
|
|
if (dtLastData.Rows[0]["FINTERID"].ToStringEx() == SData.FInterID.ToStringEx())
|
|
{
|
|
//可以更新汇报状态
|
|
mySql = $@"UPDATE S SET S.FTYPEID=3, S.FRUNTYPE=4, S.FDATE=GETDATE() FROM T_SFC_REPORT S WHERE S.FINTERID='{reportInterid}'";
|
|
if (await context.Database.SqlNonQueryAsync(mySql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
}
|
|
}
|
|
#region 检验项目表体
|
|
//清除已经删除的数据
|
|
DataTable dtTestHaveData = context.Database.SqlQuery($@"SELECT
|
|
FDETAILID,
|
|
FENTRYID
|
|
FROM T_SFC_FIRSTINPECTIONENTRY
|
|
WHERE FINTERID ='{SData.FInterID.ToStringEx()}'");
|
|
//需要进行删除的sql集合
|
|
List<string> delTestList = new List<string>();
|
|
foreach (DataRow itemDel in dtTestHaveData.Rows)
|
|
{
|
|
if (testItemData.Count > 0)
|
|
{
|
|
//判断原数据是否还存在
|
|
if (testItemData.Where(o => o["FDetailID"].ToStringEx() == itemDel["FDETAILID"].ToStringEx()).ToList().Count < 1)
|
|
{
|
|
delTestList.Add($@" DELETE FROM T_SFC_FIRSTINPECTIONENTRY WHERE FDETAILID='{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
delTestList.Add($@" DELETE FROM T_SFC_FIRSTINPECTIONENTRY WHERE FDETAILID = '{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
if (delTestList.Count > 0)
|
|
{
|
|
//执行删除操作
|
|
foreach (string itemSql in delTestList)
|
|
{
|
|
if (await context.Database.SqlNonQueryAsync(itemSql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
}
|
|
}
|
|
|
|
//循环【首检项目】表体数据
|
|
//操作首检项目
|
|
foreach (var item in testItemData)
|
|
{
|
|
DataTable ODKGXSJTestProject = context.Database.SqlQuery($@"SELECT * FROM T_SFC_FIRSTINPECTIONENTRY
|
|
WHERE FINTERID='{SData.FInterID.ToStringEx()}'
|
|
AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
if (ODKGXSJTestProject.Rows.Count > 0)
|
|
{
|
|
#region 更新操作
|
|
string updataData = string.Empty;
|
|
if (item["FAnalysisWay"].ToInt() == 1)
|
|
{
|
|
//定性分析
|
|
updataData += $@" ,FQUALTESTVALUE='{item["FTestValue"].ToStringEx()}',FTESTRESULT={item["FTestResult"].ToInt()}";
|
|
}
|
|
else if (item["FAnalysisWay"].ToInt() == 2)
|
|
{
|
|
//定量
|
|
updataData += $@" ,FQUANTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
//定量分析时。检验结果可以根据检验值自动判断 1=合格,2=不合格
|
|
if (item["FTestValue"].ToDecimal() >= item["FLwerLimit"].ToDecimal() && item["FTestValue"].ToDecimal() <= item["FUpperLimit"].ToDecimal())
|
|
{
|
|
updataData += $@" ,FTESTRESULT=1";
|
|
}
|
|
else
|
|
{
|
|
updataData += $@" ,FTESTRESULT=2";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//其它
|
|
updataData += $@" ,FOTHERTESTVALUE={item["FTestValue"].ToDecimal()},FTESTRESULT={item["FTestResult"].ToInt()}";
|
|
}
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
UPDATE T_SFC_FIRSTINPECTIONENTRY
|
|
SET
|
|
FENTRYID={item["FEntryID"].ToInt()},FVALUE={item["TargetValue"].ToDecimal()}
|
|
,FUPPERLIMIT={item["FUpperLimit"].ToDecimal()},FLWERLIMIT={item["FLwerLimit"].ToDecimal()}
|
|
,FANALYSISWAY='{item["FAnalysisWay"].ToInt()}'
|
|
{updataData}
|
|
WHERE FINTERID='{SData.FInterID}' AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 插入操作
|
|
string fDetailID = Guid.NewGuid().ToStringEx();
|
|
string updataData = string.Empty;
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
INSERT INTO T_SFC_FIRSTINPECTIONENTRY
|
|
(FINTERID,FDETAILID,FENTRYID,FTESTPROJECTID,FTESTWAYID
|
|
,FTESTSTANDARDID,FTESTINSTRUMENTID,FVALUE,FUPPERLIMIT
|
|
,FLWERLIMIT,FTESTRESULT,FANALYSISWAY,UID)
|
|
VALUES('{SData.FInterID.ToStringEx()}','{fDetailID}'
|
|
,{item["FEntryID"].ToInt()},'{item["FTestProjectID"].ToStringEx()}'
|
|
,'{item["FTestWayID"].ToStringEx()}','{item["FTestStandardID"].ToStringEx()}'
|
|
,'{item["FTestInstrumentID"].ToStringEx()}',{item["TargetValue"].ToDecimal()}
|
|
,{item["FUpperLimit"].ToDecimal()},{item["FLwerLimit"].ToDecimal()}
|
|
,{item["FTestResult"].ToInt()},{item["FAnalysisWay"].ToInt()}
|
|
,'{Guid.NewGuid().ToStringEx()}')");
|
|
if (item["FAnalysisWay"].ToInt() == 1)
|
|
{
|
|
//定性分析
|
|
updataData += $@" FQUALTESTVALUE='{item["FTestValue"].ToStringEx()}'";
|
|
}
|
|
else if (item["FAnalysisWay"].ToInt() == 2)
|
|
{
|
|
//定量
|
|
updataData += $@" FQUANTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
//定量分析时。检验结果可以根据检验值自动判断 1=合格,2=不合格
|
|
if (item["FTestValue"].ToDecimal() >= item["FLwerLimit"].ToDecimal() && item["FTestValue"].ToDecimal() <= item["FUpperLimit"].ToDecimal())
|
|
{
|
|
updataData += $@" ,FTESTRESULT={1}";
|
|
}
|
|
else
|
|
{
|
|
updataData += $@" ,FTESTRESULT={2}";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//其它
|
|
updataData += $@" FOTHERTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
}
|
|
await context.Database.SqlNonQueryAsync($@"UPDATE T_SFC_FIRSTINPECTIONENTRY SET {updataData}
|
|
WHERE FDETAILID='{fDetailID}'");
|
|
#endregion
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 不合格原因表体
|
|
//清除已经删除的数据
|
|
DataTable dtBadHaveData = context.Database.SqlQuery($@"SELECT
|
|
FDETAILID,
|
|
FENTRYID
|
|
FROM T_SFC_FIRSTINPECTIONENTRY1
|
|
WHERE FINTERID ='{SData.FInterID.ToStringEx()}'");
|
|
//需要进行删除的sql集合
|
|
List<string> delBadList = new List<string>();
|
|
foreach (DataRow itemDel in dtBadHaveData.Rows)
|
|
{
|
|
if (badReasonsData.Count > 0)
|
|
{
|
|
//判断原数据是否还存在
|
|
if (badReasonsData.Where(o => o["FDetailID"].ToStringEx() == itemDel["FDETAILID"].ToStringEx()).ToList().Count < 1)
|
|
{
|
|
delBadList.Add($@" DELETE FROM T_SFC_FIRSTINPECTIONENTRY1 WHERE FDETAILID='{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
delBadList.Add($@" DELETE FROM T_SFC_FIRSTINPECTIONENTRY1 WHERE FDETAILID = '{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
if (delBadList.Count > 0)
|
|
{
|
|
//执行删除操作
|
|
foreach (string itemSql in delBadList)
|
|
{
|
|
if (await context.Database.SqlNonQueryAsync(itemSql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//循环【首检项目】表体数据
|
|
//操作首检项目
|
|
foreach (var item in badReasonsData)
|
|
{
|
|
DataTable ODKGXSJFault = context.Database.SqlQuery($@"SELECT * FROM T_SFC_FIRSTINPECTIONENTRY1
|
|
WHERE FINTERID='{SData.FInterID.ToStringEx()}'
|
|
AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
if (ODKGXSJFault.Rows.Count > 0)
|
|
{
|
|
#region 更新操作
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
UPDATE T_SFC_FIRSTINPECTIONENTRY1
|
|
SET
|
|
FENTRYID={item["FEntryID"].ToInt()},FFAULTREASON='{item["FFaultReasonID"].ToStringEx()}'
|
|
,FFAULTQTY={item["FFaultQty"].ToDecimal()}
|
|
WHERE FINTERID='{SData.FInterID}' AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 插入操作
|
|
string fDetailID = Guid.NewGuid().ToStringEx();
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
INSERT INTO T_SFC_FIRSTINPECTIONENTRY1
|
|
(FINTERID,FDETAILID,FENTRYID,FFAULTREASON,FFAULTQTY,UID)
|
|
VALUES('{SData.FInterID.ToStringEx()}','{fDetailID}'
|
|
,{item["FEntryID"].ToInt()},'{item["FFaultReasonID"].ToStringEx()}'
|
|
,{item["FFaultQty"].ToDecimal()},'{Guid.NewGuid().ToStringEx()}')");
|
|
#endregion
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
//提交事务
|
|
context.Database.CommitTransaction();
|
|
|
|
retMsg = "保存成功!!";
|
|
return await Task.Run(() => retMsg);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
if (context.Database.CurrentTransaction != null)
|
|
{
|
|
context.Database.RollbackTransaction();
|
|
}
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 工序巡检列表接口(状态查询:已完成、未完成)
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<List<object>> GetProcessInspection(GetFirstInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//已检、未检状态
|
|
string status = SData.Status.ToStringEx();
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
string sqlWhere = string.Empty;
|
|
object returnData = new { };
|
|
#endregion
|
|
|
|
if (status == "0")
|
|
{
|
|
//未首检
|
|
sqlWhere += " AND (T1.FTESTRESULT='' OR T1.FTESTRESULT IS NULL) ";
|
|
}
|
|
else if (status == "1")
|
|
{
|
|
//已首检
|
|
sqlWhere += " AND (T1.FTESTRESULT!='' AND T1.FTESTRESULT IS NOT NULL) ";
|
|
}
|
|
//资源NO
|
|
if (resource != null && resource.Count > 0)
|
|
{
|
|
sqlWhere += $@" AND T5.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"SELECT TOP {size} X.* FROM
|
|
(
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY T1.FDATE DESC, T1.C_YMD DESC) AS ROWNO,
|
|
T1.FDATE AS FDate,
|
|
T1.FINTERID AS FInterID,
|
|
T4.FBILLNO AS FICMONO, --任务单号
|
|
T7.FBILLNO AS FFlowCardNo, --流转卡号
|
|
T2.FNUMBER AS FMaterialNo, --产品编码
|
|
T2.FNAME AS FMaterialName, --产品名称
|
|
T5.FNUMBER AS FResourceNo, --资源代码
|
|
T8.FNUMBER AS FProcessNo,
|
|
CONVERT(VARCHAR,T1.C_YMD,120) AS CreateTime, --创建时间
|
|
CASE T1.FTESTRESULT WHEN '1' THEN '0' WHEN '2' THEN '1' ELSE '' END AS FTestResult, --检验结果 1=合格,2=不合格
|
|
T1.FSTATUS AS FStatus, --数据状态
|
|
T6.FNAME AS FStatusName, --状态名称
|
|
T1.FTESTTYPE AS FTestType, --检验方式 1=全检;2=抽检
|
|
T1.FQTY AS FQty, --数量
|
|
T1.FCOUNT AS FCount, --送检数量
|
|
T1.FACCEPTQTY AS FAcceptQty, --合格数
|
|
T1.FUNACCEPTQTY AS FUnAcceptQty --不合格数
|
|
,T1.FREPORTID AS FReportId
|
|
FROM T_SFC_PROCESSINPECTION T1
|
|
LEFT JOIN T_BD_MATERIAL T2 ON T2.FINTERID=T1.FMATERIALID
|
|
LEFT JOIN T_SFC_FLOWCARD T3 ON T3.FINTERID=T1.FFLOWCARDID
|
|
LEFT JOIN T_PRD_MO T4 ON T4.FINTERID=T3.FMOID
|
|
LEFT JOIN (SELECT DISTINCT FINTERID,FRESOURCESID,FCODE FROM T_SFC_REPORT WHERE ISNULL(FCODE,'')<>'') A ON A.FCODE = T1.FCODE AND T1.FREPORTID=A.FINTERID
|
|
LEFT JOIN T_ENG_RESOURCE T5 ON T5.FINTERID=A.FRESOURCESID
|
|
LEFT JOIN T_SFC_PROCESS T8 ON T1.FPROCESSID=T8.FINTERID
|
|
LEFT JOIN SYS_STATUS T6 ON T6.FITEMID=T1.FSTATUS
|
|
LEFT JOIN T_SFC_FLOWCARD T7 ON T7.FINTERID=T1.FFLOWCARDID
|
|
WHERE T1.FWORKTYPE=2 {sqlWhere}
|
|
) X WHERE ROWNO > {size * (page - 1)} ORDER BY ROWNO ";
|
|
//获取数据
|
|
DataTable dtData = context.Database.SqlQuery(sqlSearch);
|
|
//FInterID集合
|
|
List<string> finteridList = dtData.Select().AsEnumerable<DataRow>().Select(o => (o["FInterID"].ToStringEx())).ToList();
|
|
List<string> listsql = new List<string>();
|
|
//子项数据查询
|
|
//不良原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
FINTERID AS FInterID,
|
|
FNUMBER AS FNumber,
|
|
FNAME AS FName
|
|
FROM T_QM_BADREASONS
|
|
WHERE FSTATUS=40 AND FDELETED=0");
|
|
//样品一组检验方案
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
'' AS FDetailID,
|
|
0 AS FEntryID,
|
|
T3.FINTERID AS FTestProjectID, --检验项目内码
|
|
T3.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T3.FNAME AS FTestProjectName, --检验项目名称
|
|
T2.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T2.FVALUE AS TargetValue, --目标值
|
|
T2.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T2.FLWERLIMIT AS FLwerLimit, --下限值
|
|
0 AS FTestValue, --检验值(定量)
|
|
0 AS FTestResult, --检验结果
|
|
T2.FTESTWAYID AS FTestWayID, --检验方法
|
|
T2.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T2.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_PROCESSINPECTION T1
|
|
LEFT JOIN T_SFC_PROCESSINPECTIONENTRY T2 ON T2.FINTERID=T1.FTESTWAYID
|
|
LEFT JOIN T_QM_INSPECTITEM T3 ON T3.FINTERID=T2.FTESTPROJECTID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID");
|
|
//已经检验的数据
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T2.FINTERID AS FTestProjectID, --检验项目内码
|
|
T2.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T2.FNAME AS FTestProjectName, --检验项目名称
|
|
T1.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T1.FVALUE AS TargetValue, --目标值
|
|
T1.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T1.FLWERLIMIT AS FLwerLimit, --下限值
|
|
T1.FQUANTESTVALUE AS FTestValue, --检验值(定量)
|
|
T1.FTESTRESULT AS FTestResult, --检验结果
|
|
T1.FTESTWAYID AS FTestWayID, --检验方法
|
|
T1.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T1.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_PROCESSINPECTIONENTRY T1
|
|
LEFT JOIN T_QM_INSPECTITEM T2 ON T1.FTESTPROJECTID=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID");
|
|
//已经提交的不合格原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T1.FFAULTREASON AS FFaultReasonID,
|
|
T2.FNAME AS FFaultReasonName,
|
|
T1.FFAULTQTY AS FFaultQty
|
|
FROM T_SFC_PROCESSINPECTIONENTRY1 T1
|
|
LEFT JOIN T_QM_BADREASONS T2 ON T1.FFAULTREASON=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID
|
|
");
|
|
//获取数据
|
|
DataSet dsAll = context.Database.SqlQuery(listsql);
|
|
//数据集合
|
|
List<object> listProcessInspection = new List<object>();
|
|
//数据表体结合
|
|
DataTable dtTestWay = new DataTable();
|
|
DataTable dtTestItemData = new DataTable();
|
|
DataTable dtBadReasonsData = new DataTable();
|
|
DataRow[] dtTemp = null;
|
|
|
|
foreach (DataRow item in dtData.Rows)
|
|
{
|
|
//表体数据抓取
|
|
//检验方案
|
|
dtTemp = dsAll.Tables[1].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestWay = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestWay = new DataTable();
|
|
}
|
|
//检验项目明细
|
|
dtTemp = dsAll.Tables[2].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestItemData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestItemData = new DataTable();
|
|
}
|
|
//不合格原因
|
|
dtTemp = dsAll.Tables[3].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtBadReasonsData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtBadReasonsData = new DataTable();
|
|
}
|
|
listProcessInspection.Add(
|
|
new
|
|
{
|
|
FDate = item["FDate"].ToStringEx(),
|
|
FInterID = item["FInterID"].ToStringEx(),
|
|
FICMONO = item["FICMONO"].ToStringEx(),
|
|
FFlowCardNo = item["FFlowCardNo"].ToStringEx(),
|
|
FMaterialNo = item["FMaterialNo"].ToStringEx(),
|
|
FMaterialName = item["FMaterialName"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProcessNo = item["FProcessNo"].ToStringEx(),
|
|
CreateTime = item["CreateTime"].ToStringEx(),
|
|
FTestResult = item["FTestResult"].ToStringEx(),
|
|
FStatus = item["FStatus"].ToStringEx(),
|
|
FStatusName = item["FStatusName"].ToStringEx(),
|
|
FTestType = item["FTestType"].ToStringEx(),
|
|
FQty = item["FQty"].ToDecimal(),
|
|
FCount = item["FCount"].ToDecimal(),
|
|
FAcceptQty = item["FAcceptQty"].ToDecimal(),
|
|
FUnAcceptQty = item["FUnAcceptQty"].ToDecimal(),
|
|
FReportId = item["FReportId"].ToStringEx(),
|
|
BadReasons = dsAll.Tables[0],
|
|
TestWay = dtTestWay,
|
|
TestItemData = dtTestItemData,
|
|
BadReasonsData = dtBadReasonsData
|
|
});
|
|
}
|
|
returnData = new
|
|
{
|
|
firstInspection = listProcessInspection
|
|
};
|
|
return await Task.Run(() => listProcessInspection);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 工序巡检列表接口(状态查询:已完成、未完成)
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> GetProcessInspections(GetFirstInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//已检、未检状态
|
|
string status = SData.Status.ToStringEx();
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
string sqlWhere = string.Empty;
|
|
object returnData = new { };
|
|
#endregion
|
|
|
|
if (status == "0")
|
|
{
|
|
//未首检
|
|
sqlWhere += " AND (T1.FTESTRESULT='' OR T1.FTESTRESULT IS NULL) ";
|
|
}
|
|
else if (status == "1")
|
|
{
|
|
//已首检
|
|
sqlWhere += " AND (T1.FTESTRESULT!='' AND T1.FTESTRESULT IS NOT NULL) ";
|
|
}
|
|
//资源NO
|
|
if (resource != null && resource.Count > 0)
|
|
{
|
|
sqlWhere += $@" AND T5.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"SELECT TOP {size} X.*,(SELECT COUNT(1) FROM T_SFC_PROCESSINPECTION T1 LEFT JOIN
|
|
(
|
|
SELECT DISTINCT
|
|
FRESOURCESID,
|
|
FCODE
|
|
FROM T_SFC_REPORT
|
|
WHERE ISNULL(FCODE, '') <> ''
|
|
) A
|
|
ON A.FCODE = T1.FCODE
|
|
LEFT JOIN T_ENG_RESOURCE T5
|
|
ON T5.FINTERID = A.FRESOURCESID WHERE FWORKTYPE = 2 {sqlWhere}) AS TOTALCOUNT FROM
|
|
(
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY T1.FDATE DESC, T1.C_YMD DESC) AS ROWNO,
|
|
T1.FDATE AS FDate,
|
|
T1.FINTERID AS FInterID,
|
|
T4.FBILLNO AS FICMONO, --任务单号
|
|
T7.FBILLNO AS FFlowCardNo, --流转卡号
|
|
T2.FNUMBER AS FMaterialNo, --产品编码
|
|
T2.FNAME AS FMaterialName, --产品名称
|
|
T5.FNUMBER AS FResourceNo, --资源代码
|
|
T8.FNUMBER AS FProcessNo,
|
|
CONVERT(VARCHAR,T1.C_YMD,120) AS CreateTime, --创建时间
|
|
CASE T1.FTESTRESULT WHEN '1' THEN '0' WHEN '2' THEN '1' ELSE '' END AS FTestResult, --检验结果 1=合格,2=不合格
|
|
T1.FSTATUS AS FStatus, --数据状态
|
|
T6.FNAME AS FStatusName, --状态名称
|
|
T1.FTESTTYPE AS FTestType, --检验方式 1=全检;2=抽检
|
|
T1.FQTY AS FQty, --数量
|
|
T1.FCOUNT AS FCount, --送检数量
|
|
T1.FACCEPTQTY AS FAcceptQty, --合格数
|
|
T1.FUNACCEPTQTY AS FUnAcceptQty --不合格数
|
|
,T1.FREPORTID AS FReportId
|
|
FROM T_SFC_PROCESSINPECTION T1
|
|
LEFT JOIN T_BD_MATERIAL T2 ON T2.FINTERID=T1.FMATERIALID
|
|
LEFT JOIN T_SFC_FLOWCARD T3 ON T3.FINTERID=T1.FFLOWCARDID
|
|
LEFT JOIN T_PRD_MO T4 ON T4.FINTERID=T3.FMOID
|
|
LEFT JOIN (SELECT DISTINCT FINTERID,FRESOURCESID,FCODE FROM T_SFC_REPORT WHERE ISNULL(FCODE,'')<>'') A ON A.FCODE = T1.FCODE AND T1.FREPORTID=A.FINTERID
|
|
LEFT JOIN T_ENG_RESOURCE T5 ON T5.FINTERID=A.FRESOURCESID
|
|
LEFT JOIN T_SFC_PROCESS T8 ON T1.FPROCESSID=T8.FINTERID
|
|
LEFT JOIN SYS_STATUS T6 ON T6.FITEMID=T1.FSTATUS
|
|
LEFT JOIN T_SFC_FLOWCARD T7 ON T7.FINTERID=T1.FFLOWCARDID
|
|
WHERE T1.FWORKTYPE=2 {sqlWhere}
|
|
) X WHERE ROWNO > {size * (page - 1)} ORDER BY ROWNO ";
|
|
//获取数据
|
|
DataTable dtData = context.Database.SqlQuery(sqlSearch);
|
|
//FInterID集合
|
|
List<string> finteridList = dtData.Select().AsEnumerable<DataRow>().Select(o => (o["FInterID"].ToStringEx())).ToList();
|
|
List<string> listsql = new List<string>();
|
|
//子项数据查询
|
|
//不良原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
FINTERID AS FInterID,
|
|
FNUMBER AS FNumber,
|
|
FNAME AS FName
|
|
FROM T_QM_BADREASONS
|
|
WHERE FSTATUS=40 AND FDELETED=0");
|
|
//样品一组检验方案
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
'' AS FDetailID,
|
|
0 AS FEntryID,
|
|
T3.FINTERID AS FTestProjectID, --检验项目内码
|
|
T3.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T3.FNAME AS FTestProjectName, --检验项目名称
|
|
T2.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T2.FVALUE AS TargetValue, --目标值
|
|
T2.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T2.FLWERLIMIT AS FLwerLimit, --下限值
|
|
0 AS FTestValue, --检验值(定量)
|
|
0 AS FTestResult, --检验结果
|
|
T2.FTESTWAYID AS FTestWayID, --检验方法
|
|
T2.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T2.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_PROCESSINPECTION T1
|
|
LEFT JOIN T_SFC_PROCESSINPECTIONENTRY T2 ON T2.FINTERID=T1.FTESTWAYID
|
|
LEFT JOIN T_QM_INSPECTITEM T3 ON T3.FINTERID=T2.FTESTPROJECTID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID");
|
|
//已经检验的数据
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T2.FINTERID AS FTestProjectID, --检验项目内码
|
|
T2.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T2.FNAME AS FTestProjectName, --检验项目名称
|
|
T1.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T1.FVALUE AS TargetValue, --目标值
|
|
T1.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T1.FLWERLIMIT AS FLwerLimit, --下限值
|
|
T1.FQUANTESTVALUE AS FTestValue, --检验值(定量)
|
|
T1.FTESTRESULT AS FTestResult, --检验结果
|
|
T1.FTESTWAYID AS FTestWayID, --检验方法
|
|
T1.FTESTSTANDARDID AS FTestStandardID, --质量标准
|
|
T1.FTESTINSTRUMENTID AS FTestInstrumentID --质量标准
|
|
FROM T_SFC_PROCESSINPECTIONENTRY T1
|
|
LEFT JOIN T_QM_INSPECTITEM T2 ON T1.FTESTPROJECTID=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID");
|
|
//已经提交的不合格原因
|
|
listsql.Add($@"
|
|
SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDETAILID AS FDetailID,
|
|
T1.FENTRYID AS FEntryID,
|
|
T1.FFAULTREASON AS FFaultReasonID,
|
|
T2.FNAME AS FFaultReasonName,
|
|
T1.FFAULTQTY AS FFaultQty
|
|
FROM T_SFC_PROCESSINPECTIONENTRY1 T1
|
|
LEFT JOIN T_QM_BADREASONS T2 ON T1.FFAULTREASON=T2.FINTERID
|
|
WHERE T1.FINTERID IN ('{String.Join("','", finteridList)}') ORDER BY T1.FINTERID,T1.FENTRYID
|
|
");
|
|
//获取数据
|
|
DataSet dsAll = context.Database.SqlQuery(listsql);
|
|
//数据集合
|
|
List<object> listProcessInspection = new List<object>();
|
|
//数据表体结合
|
|
DataTable dtTestWay = new DataTable();
|
|
DataTable dtTestItemData = new DataTable();
|
|
DataTable dtBadReasonsData = new DataTable();
|
|
DataRow[] dtTemp = null;
|
|
int count = 0;
|
|
foreach (DataRow item in dtData.Rows)
|
|
{
|
|
count = item["TOTALCOUNT"].ToInt();
|
|
//表体数据抓取
|
|
//检验方案
|
|
dtTemp = dsAll.Tables[1].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestWay = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestWay = new DataTable();
|
|
}
|
|
//检验项目明细
|
|
dtTemp = dsAll.Tables[2].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtTestItemData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtTestItemData = new DataTable();
|
|
}
|
|
//不合格原因
|
|
dtTemp = dsAll.Tables[3].Select($@"FInterID='{item["FInterID"].ToStringEx()}'");
|
|
if (dtTemp.Length > 0)
|
|
{
|
|
dtBadReasonsData = dtTemp.CopyToDataTable<DataRow>();
|
|
}
|
|
else
|
|
{
|
|
dtBadReasonsData = new DataTable();
|
|
}
|
|
listProcessInspection.Add(
|
|
new
|
|
{
|
|
FDate = item["FDate"].ToStringEx(),
|
|
FInterID = item["FInterID"].ToStringEx(),
|
|
FICMONO = item["FICMONO"].ToStringEx(),
|
|
FFlowCardNo = item["FFlowCardNo"].ToStringEx(),
|
|
FMaterialNo = item["FMaterialNo"].ToStringEx(),
|
|
FMaterialName = item["FMaterialName"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProcessNo = item["FProcessNo"].ToStringEx(),
|
|
CreateTime = item["CreateTime"].ToStringEx(),
|
|
FTestResult = item["FTestResult"].ToStringEx(),
|
|
FStatus = item["FStatus"].ToStringEx(),
|
|
FStatusName = item["FStatusName"].ToStringEx(),
|
|
FTestType = item["FTestType"].ToStringEx(),
|
|
FQty = item["FQty"].ToDecimal(),
|
|
FCount = item["FCount"].ToDecimal(),
|
|
FAcceptQty = item["FAcceptQty"].ToDecimal(),
|
|
FUnAcceptQty = item["FUnAcceptQty"].ToDecimal(),
|
|
FReportId = item["FReportId"].ToStringEx(),
|
|
BadReasons = dsAll.Tables[0],
|
|
TestWay = dtTestWay,
|
|
TestItemData = dtTestItemData,
|
|
BadReasonsData = dtBadReasonsData
|
|
});
|
|
}
|
|
returnData = new
|
|
{
|
|
processInspection = listProcessInspection,
|
|
totalCount = count
|
|
};
|
|
return await Task.Run(() => returnData);
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 巡检条码扫入验证获取数据
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> ScanProcessCode(ScanProcessCode SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
|
|
object returnData = new { };
|
|
|
|
string strSql = string.Format(@"SELECT FCODE AS FNUMBER, 1 AS FTYPEID, '工序流转卡' AS FTYPENAME FROM T_SFC_FLOWCARDENTRY WHERE FCODE = '{0}'
|
|
UNION
|
|
SELECT FSNCODE AS FNUMBER, 2 AS FTYPEID, 'SN序列号' AS FTYPENAME FROM T_SFC_BARCODESN WHERE FSNCODE = '{0}'
|
|
UNION
|
|
SELECT FTCCODE AS FNUMBER, 3 AS FTYPEID, '周转码' AS FTYPENAME FROM T_SFC_BARCODETC WHERE FTCCODE = '{0}'
|
|
UNION
|
|
SELECT FWIPCODE AS FNUMBER, 4 AS FTYPEID, '在制品条码' AS FTYPENAME FROM T_SFC_BARCODEWIP WHERE FWIPCODE = '{0}'", SData.Barcode);
|
|
DataTable dtBarType = context.Database.SqlQuery(strSql);
|
|
if (dtBarType == null || dtBarType.Rows.Count == 0)
|
|
{
|
|
throw new O3AppException("该条码不存在或不符合条件,不可检验!!");
|
|
}
|
|
|
|
//条码类型
|
|
int codeType = dtBarType.Rows[0]["FTYPEID"].ToInt();
|
|
|
|
//在制品条码
|
|
string strTableName = "";
|
|
string strStatus = "";
|
|
//过程表数据
|
|
DataTable dtResult = new DataTable();
|
|
DataTable Headdata = new DataTable();
|
|
#region 单据主体信息
|
|
Headdata = context.Database.SqlQuery($@"SELECT
|
|
T1.FINTERID AS FInterID,
|
|
T1.FDATE AS FDate,
|
|
T4.FBILLNO AS FICMONO, --任务单号
|
|
T1.FFLOWCARDDETAILID AS FFlowCardDetailID, --工序识别内码
|
|
T1.FFLOWCARDID AS FFlowCardID, --流转卡内码
|
|
T3.FBILLNO AS FFlowCardNo, --流转卡号
|
|
T1.FMATERIALID AS FMaterialID, --产品内码
|
|
T2.FNUMBER AS FMaterialNo, --产品编码
|
|
T2.FNAME AS FMaterialName, --产品名称
|
|
T1.FUNITID AS FUnitID, --单位内码
|
|
T1.FPROCESSID AS FProcessID, --工序内码
|
|
T5.FNUMBER AS FProcessNO, --工序号
|
|
T1.FINTERID AS FResourceNo, --资源代码
|
|
CONVERT(VARCHAR,T1.C_YMD,120) AS CreateTime, --创建时间
|
|
T1.FTESTRESULT AS FTestResult, --检验结果 0=合格,1=不合格
|
|
T1.FTESTWAYID AS FTestWayID, --检验方案
|
|
T1.FTESTTYPE AS FTestType, --检验方式 1=全检;2=抽检
|
|
T1.FQTY AS FQty, --数量
|
|
T1.FCOUNT AS FCount, --送检数量
|
|
T1.FACCEPTQTY AS FAcceptQty, --合格数
|
|
T1.FUNACCEPTQTY AS FUnAcceptQty, --不合格数
|
|
T1.FREPORTID AS FReportId
|
|
FROM T_SFC_PROCESSINPECTION T1
|
|
LEFT JOIN T_BD_MATERIAL T2 ON T1.FMATERIALID=T2.FINTERID
|
|
LEFT JOIN T_SFC_FLOWCARD T3 ON T3.FINTERID=T1.FFLOWCARDID
|
|
LEFT JOIN T_PRD_MO T4 ON T4.FINTERID=T3.FMOID
|
|
LEFT JOIN T_SFC_PROCESS T5 ON T5.FINTERID=T1.FPROCESSID
|
|
WHERE 1=0 ");
|
|
#endregion
|
|
//查询条码主体信息sql文
|
|
string strSQL = string.Empty;
|
|
//工序识别码
|
|
if (codeType == 1)
|
|
{
|
|
#region 根据FC工序流转码查询生产信息
|
|
|
|
strSQL = $@"
|
|
SELECT TOP 1
|
|
T1.FAUXQTY AS FAuxQty,
|
|
T1.FPRODUCTID AS FProductID,
|
|
T1.FBATCHNO AS FBatchNO,
|
|
T1.FINTERID AS FFlowCardID,
|
|
T1.FBILLNO AS FFlowCardBillNo,
|
|
OI.FBILLNO AS FICMONO,
|
|
T2.FDETAILID AS FFlowCardDetailID,
|
|
T2.FCODE AS FCode,
|
|
T2.FPROCESSID AS FProcessID,
|
|
T3.FNUMBER AS FProcessNumber,
|
|
T3.FNAME AS FProcessName,
|
|
T4.FNUMBER AS FItemNumber,
|
|
T4.FNAME AS FItemName,
|
|
T4.FSPECIFICATION,
|
|
--是否首检
|
|
T2.FISHEADQC AS FIsHeadQC,
|
|
T2.FINSPECTIONCYCLE AS FInspectionCycle,
|
|
T6.FINTERID AS FQCTypeID,
|
|
T6.FNUMBER AS FQCTypeNumber,
|
|
T6.FNAME AS FQCTypeName,
|
|
T7.FINTERID AS FUnitID,
|
|
T7.FNUMBER AS FUnitNumber,
|
|
T7.FNAME AS FUnitName,
|
|
T8.FNUMBER AS FResourceNo,
|
|
A.FINTERID AS FReportId
|
|
FROM T_SFC_FLOWCARDENTRY T2
|
|
INNER JOIN T_SFC_FLOWCARD T1 ON T1.FINTERID=T2.FINTERID
|
|
LEFT JOIN T_PRD_MO OI ON OI.FINTERID=T1.FMOID
|
|
LEFT JOIN T_SFC_PROCESS T3 ON T2.FPROCESSID=T3.FINTERID
|
|
LEFT JOIN T_BD_MATERIAL T4 ON T1.FPRODUCTID=T4.FINTERID
|
|
LEFT JOIN T_QM_INSPECTSOLUTION T6 ON T6.FINTERID=T2.FCOURSEQCSCHEMEID
|
|
LEFT JOIN T_BD_UNIT T7 ON T1.FUNITID=T7.FINTERID
|
|
LEFT JOIN (SELECT DISTINCT FINTERID,FRESOURCESID,FCODE,FDATE FROM T_SFC_REPORT WHERE ISNULL(FCODE,'')<>'') A ON A.FCODE = T2.FCODE
|
|
LEFT JOIN T_ENG_RESOURCE T8 ON T8.FINTERID=A.FRESOURCESID
|
|
WHERE T2.FCODE='{SData.Barcode.ToStringEx()}' AND T2.FISCOURSEQC = 1
|
|
ORDER BY A.FDATE DESC";
|
|
|
|
//查询数据
|
|
dtResult = context.Database.SqlQuery(strSQL);
|
|
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 条码类型 表名、状态区分
|
|
//主流条码类型,区分表名
|
|
string fNumber = string.Empty;
|
|
if (codeType == 2)
|
|
{
|
|
strTableName = "T_SFC_BARCODESN";
|
|
strStatus = " AND T10.FSNSTATUS = 4 ";
|
|
fNumber = "FSNCODE";
|
|
}
|
|
else if (codeType == 3)
|
|
{
|
|
strTableName = "T_SFC_BARCODETC";
|
|
strStatus = " AND T10.FTCSTATUS = 4 ";
|
|
fNumber = "FTCCODE";
|
|
}
|
|
else if (codeType == 4)
|
|
{
|
|
strTableName = "T_SFC_BARCODEWIP";
|
|
strStatus = " AND T10.FWIPSTATUS = 3 ";
|
|
fNumber = "FWIPCODE";
|
|
}
|
|
else
|
|
{
|
|
//查询符合条件的表名
|
|
string strSQLTableName =$@"
|
|
SELECT 'T_SFC_BARCODEWIP' TableName FROM T_SFC_BARCODEWIP WHERE FWIPCODE = '{SData.Barcode}'
|
|
UNION
|
|
SELECT 'T_SFC_BARCODESN' TableName FROM T_SFC_BARCODESN WHERE FSNCODE = '{SData.Barcode}'";
|
|
DataTable dtTableName = context.Database.SqlQuery(strSQLTableName);
|
|
if (dtTableName.Rows.Count>0)
|
|
{
|
|
//表名
|
|
strTableName = dtTableName.Rows[0]["TableName"].ToStringEx();
|
|
if (strTableName.Trim() == "T_SFC_BARCODEWIP")
|
|
{
|
|
strStatus = " AND T10.FWIPSTATUS = 3 ";
|
|
fNumber = "FWIPCODE";
|
|
}
|
|
else
|
|
{
|
|
strStatus = " AND T10.FSNSTATUS = 4 ";
|
|
fNumber = "FSNCODE";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
throw new O3AppException("该条码不存在或不符合条件,不可检验!");
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 除FC工序流转卡之外的条码,查询生产信息
|
|
strSQL = $@"
|
|
DECLARE @FCheckBadQty DECIMAL;
|
|
|
|
SELECT @FCheckBadQty = ISNULL(SUM(FBADQTY), 0)
|
|
FROM T_SFC_REPORTENTRY5
|
|
WHERE FBODYID = (SELECT FReportDetailID FROM {strTableName} WHERE {fNumber} = '{SData.Barcode.ToStringEx()}')
|
|
|
|
SELECT TOP 1
|
|
T10.{fNumber} FSNBarcode ,
|
|
T2.FPROCESSID AS FProcessID ,
|
|
T8.FISCOURSEQC AS FIsCourseQC ,
|
|
T2.FSDTIME AS FSdtime ,
|
|
T2.FEDTIME AS FEdtime,
|
|
T2.FTOTALSEC AS FTotalSec,
|
|
T2.FWORKINGHOURSEC AS FWorkingHourSec,
|
|
T2.FNOWORKINGHOURSEC AS FNOWorkingHourSec,
|
|
T2.FFLOWCARDDETAILID AS FFlowCardDetailID,
|
|
T2.FQTY AS FAuxQty,
|
|
T2.FQUAQTY - T2.FCHECKQTY
|
|
+ ( SELECT ISNULL(SUM(FBADQTY), 0)
|
|
FROM T_SFC_REPORTENTRY5
|
|
WHERE FBODYID = T2.FDETAILID
|
|
AND FBADSOURCETYPE = 1
|
|
) FUnCheckQty ,
|
|
T10.FQTY AS FQty ,
|
|
T2.FBADQTY AS FBadQty,
|
|
T2.FDETAILID AS FReportDetailID ,
|
|
T2.FFLOWCARDID AS FFlowCardID,
|
|
T3.* ,
|
|
T3.FINTERID AS FReportId,
|
|
T6.FNUMBER AS FItemNumber ,
|
|
T6.FNAME AS FItemName ,
|
|
T6.FSPECIFICATION,
|
|
T4.FNUMBER AS FProcessNumber ,
|
|
T4.FNAME AS FProcessName ,
|
|
T5.FPRODUCTID AS FProductID,
|
|
T5.FBILLNO AS FFlowCardBillNo ,
|
|
T5.FBATCHNO AS FBatchNO,
|
|
OI.FBILLNO AS FICMONO,
|
|
--T5.FAUXQTY AS FAuxQty,
|
|
T7.FINTERID AS FUnitID,
|
|
T7.FNUMBER AS FUnitNumber ,
|
|
T7.FNAME AS FUnitName ,
|
|
T9.FINTERID AS FQCTypeID ,
|
|
T9.FNUMBER AS FQCTypeNumber ,
|
|
T8.FSTARTMODE AS FStartMode,
|
|
--是否首检
|
|
T8.FISHEADQC AS FIsHeadQC,
|
|
T8.FINSPECTIONCYCLE AS FInspectionCycle,
|
|
T9.FNAME AS FQCTypeName,
|
|
T11.FNUMBER AS FResourceNo
|
|
FROM T_SFC_REPORTENTRY T2
|
|
INNER JOIN T_SFC_REPORT T3 ON T2.FINTERID = T3.FINTERID
|
|
LEFT JOIN T_SFC_PROCESS T4 ON T3.FPROCESSID = T4.FINTERID
|
|
LEFT JOIN T_SFC_FLOWCARD T5 ON T2.FFLOWCARDID = T5.FINTERID
|
|
LEFT JOIN T_PRD_MO OI ON OI.FINTERID=T5.FMOID
|
|
LEFT JOIN T_BD_MATERIAL T6 ON T5.FPRODUCTID = T6.FINTERID
|
|
LEFT JOIN T_BD_UNIT T7 ON T5.FUNITID = T7.FINTERID
|
|
LEFT JOIN T_SFC_FLOWCARDENTRY T8 ON T2.FFLOWCARDDETAILID = T8.FDETAILID
|
|
LEFT JOIN T_QM_INSPECTSOLUTION T9 ON T9.FINTERID = T8.FCOURSEQCSCHEMEID
|
|
LEFT JOIN {strTableName} T10 ON T10.FREPORTDETAILID = T2.FDETAILID OR ((T10.FREPORTDETAILID IS NULL OR T10.FREPORTDETAILID='') AND T2.FBARCODE='{SData.Barcode.ToStringEx()}')
|
|
LEFT JOIN T_ENG_RESOURCE T11 ON T11.FINTERID=T3.FRESOURCESID
|
|
WHERE T10.{fNumber} = '{SData.Barcode.ToStringEx()}'
|
|
AND T8.FIsCourseQC = 1
|
|
AND T2.FQUAQTY > 0
|
|
AND (T2.FDATATYPE = 'E' OR T2.FDATATYPE = 'J')
|
|
AND T10.FQualityStatus = 0
|
|
AND T10.FIsCheck = 0
|
|
AND T10.FIsDeplete = 0
|
|
--{strStatus}
|
|
AND T2.FQUAQTY > T2.FCHECKQTY
|
|
ORDER BY T2.FEDTIME DESC";
|
|
//FC条码外的条码信息查询
|
|
dtResult = context.Database.SqlQuery(strSQL);
|
|
if (dtResult.Rows.Count==0)
|
|
{
|
|
throw new O3AppException("该条码不存在或不符合条件,不可检验!");
|
|
}
|
|
#endregion
|
|
}
|
|
|
|
#region 生成表头数据
|
|
if (dtResult.Rows.Count > 0)
|
|
{
|
|
if (codeType == 1)
|
|
{
|
|
//查询此工序识别码对应最新的汇报信息
|
|
DataTable dtReport = context.Database.SqlQuery($@"SELECT TOP 1 FINTERID,FBILLNO,FRUNTYPE
|
|
FROM T_SFC_REPORT
|
|
WHERE FCODE = '{SData.Barcode.ToStringEx()}'
|
|
ORDER BY FDATE DESC ");
|
|
if (dtReport.Rows.Count == 0)
|
|
{
|
|
throw new O3AppException("该条码对应的工序还没有开工,不能创建巡检单!");
|
|
}
|
|
|
|
//获取汇报数据
|
|
DataTable dtReportEntry = context.Database.SqlQuery($@"SELECT FDETAILID,FDATATYPE
|
|
FROM T_SFC_REPORTENTRY
|
|
WHERE FINTERID = '{dtReport.Rows[0]["FINTERID"].ToStringEx()}'");
|
|
//判断必须首检的情况下是否已经首检
|
|
// if (dtResult.Rows[0]["FIsHeadQC"].ToBoolean())
|
|
// {
|
|
// string objValue = context.Database.SqlQueryString($@"
|
|
// SELECT T01.FBILLNO
|
|
// FROM T_SFC_FIRSTINPECTION T01
|
|
//LEFT JOIN T_SFC_REPORT T02 ON T02.FINTERID=T01.FREPORTID
|
|
// WHERE T01.FSTATUS = 40
|
|
// AND T02.FBILLNO = '{dtReport.Rows[0]["FBILLNO"].ToStringEx()}'
|
|
// AND FTESTRESULT = 0");
|
|
// if (objValue.IsNullOrEmpty())
|
|
// {
|
|
// throw new O3AppException("该条码还没有做首检,不能创建巡检单!");
|
|
// }
|
|
// }
|
|
// else
|
|
// {
|
|
// //不需要首检的工序,判断汇报是否已经换型
|
|
// if (dtReportEntry.Rows.Count==0 || dtReportEntry.Select("FDATATYPE='A'").Length < 1)
|
|
// {
|
|
// throw new O3AppException("该条码对应的工序还未换型,不能创建巡检单!");
|
|
// }
|
|
// }
|
|
|
|
//巡检周期为0时,不做巡检,创建的为过程检,过程检需要验证当前工序是否已经汇报
|
|
if (dtResult.Rows[0]["FInspectionCycle"].ToDecimal() == 0)
|
|
{
|
|
if (dtReportEntry.Rows.Count == 0 || dtReportEntry.Select("FDATATYPE='E'").Length < 1)
|
|
{
|
|
throw new O3AppException("该条码对应的工序还未汇报,不能创建巡检单!");
|
|
}
|
|
}
|
|
}
|
|
|
|
DataRow dr = Headdata.NewRow(); //定义新行
|
|
dr["FInterID"] = string.Empty;
|
|
dr["FTestResult"] = string.Empty;
|
|
dr["FTestType"] = 0;
|
|
dr["FICMONO"] = dtResult.Rows[0]["FICMONO"]; //任务单号
|
|
dr["FFlowCardID"] = dtResult.Rows[0]["FFlowCardID"]; //流转卡内码
|
|
dr["FFlowCardNo"] = dtResult.Rows[0]["FFlowCardBillNo"]; //流转卡号
|
|
dr["FProcessID"] = dtResult.Rows[0]["FProcessID"]; //工序内码
|
|
dr["FProcessNO"] = dtResult.Rows[0]["FProcessNumber"]; //工序号
|
|
dr["FFlowCardDetailID"] = dtResult.Rows[0]["FFlowCardDetailID"]; //工序识别内码
|
|
dr["FMaterialID"] = dtResult.Rows[0]["FProductID"]; //物料内码
|
|
dr["FMaterialNo"] = dtResult.Rows[0]["FItemNumber"]; //物料编码
|
|
dr["FMaterialName"] = dtResult.Rows[0]["FItemName"]; //物料名称
|
|
dr["FUnitID"] = dtResult.Rows[0]["FUnitID"]; //单位ID
|
|
dr["FAcceptQty"] = 0; //合格数
|
|
dr["FUnAcceptQty"] = 0; //不合格数
|
|
dr["FQty"] = 0; //数量
|
|
dr["FCount"] = dtResult.Rows[0]["FAuxQty"]; //送检数量
|
|
dr["FTestWayID"] = dtResult.Rows[0]["FQCTypeID"]; //检验方案
|
|
dr["FResourceNo"] = dtResult.Rows[0]["FResourceNo"]; //资源代码
|
|
dr["FReportId"] = dtResult.Rows[0]["FReportId"];
|
|
Headdata.Rows.Add(dr);
|
|
}
|
|
else
|
|
{
|
|
throw new O3AppException("该条码不存在或不符合条件,不可检验!!");
|
|
}
|
|
#endregion
|
|
|
|
#region 获取表体使用数据
|
|
List<string> strBody = new List<string>();
|
|
strBody.Add($@"
|
|
--不良原因
|
|
SELECT
|
|
FINTERID AS FInterID,
|
|
FNUMBER AS FNumber,
|
|
FNAME AS FName
|
|
FROM T_QM_BADREASONS
|
|
WHERE FSTATUS=40 AND FDELETED=0");
|
|
strBody.Add($@"
|
|
--样品一组检验方案
|
|
SELECT
|
|
'' AS FInterID,
|
|
'' AS FDetailID,
|
|
0 AS FEntryID,
|
|
T3.FINTERID AS FTestProjectID, --检验项目内码
|
|
T3.FNUMBER AS FTestProjectNumber, --检验项目编码
|
|
T3.FNAME AS FTestProjectName, --检验项目名称
|
|
T2.FANALYSISWAY AS FAnalysisWay, --分析方法
|
|
T2.FVALUE AS TargetValue, --目标值
|
|
T2.FUPPERLIMIT AS FUpperLimit, --上限值
|
|
T2.FLWERLIMIT AS FLwerLimit, --下限值
|
|
0 AS FTestValue, --检验值(定量)
|
|
0 AS FTestResult, --检验结果
|
|
T2.FTESTWAY AS FTestWayID, --检验方法
|
|
T2.FTESTSTANDARD AS FTestStandardID, --检验标准
|
|
T2.FTESTINSTRUMENT AS FTestInstrumentID --检验仪器
|
|
FROM T_QM_INSPECTSOLUTIONENTRY T2
|
|
LEFT JOIN T_QM_INSPECTITEM T3 ON T3.FINTERID = T2.FINSPECTITEMID
|
|
WHERE T2.FINTERID = '{Headdata.Rows[0]["FTestWayID"].ToStringEx()}'");
|
|
//获取数据
|
|
DataSet dsAll = context.Database.SqlQuery(strBody);
|
|
#endregion
|
|
|
|
returnData = new
|
|
{
|
|
ProcessInspection = Headdata,
|
|
badReasons = dsAll.Tables[0],
|
|
testWay = dsAll.Tables[1]
|
|
};
|
|
|
|
return await Task.Run(() => returnData);
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 巡检数据保存
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> SaveProcessInspection(SaveProcessInspection SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义变量AND传参
|
|
string retMsg = string.Empty;
|
|
string mySql = string.Empty;
|
|
object returnData = new { };
|
|
List<Dictionary<string, object>> testItemData = SData.testItemData;
|
|
List<Dictionary<string, object>> badReasonsData = SData.badReasonsData;
|
|
#endregion
|
|
|
|
try
|
|
{
|
|
//开启事务
|
|
context.Database.BeginTransaction();
|
|
|
|
//单据内码
|
|
string FInterID = !string.IsNullOrEmpty(SData.FInterID.ToStringEx()) ? SData.FInterID.ToStringEx() : Guid.NewGuid().ToString();
|
|
|
|
string strSql = string.Format(@"SELECT FCODE AS FNUMBER, 1 AS FTYPEID, '工序流转卡' AS FTYPENAME FROM T_SFC_FLOWCARDENTRY WHERE FCODE = '{0}'
|
|
UNION
|
|
SELECT FSNCODE AS FNUMBER, 2 AS FTYPEID, 'SN序列号' AS FTYPENAME FROM T_SFC_BARCODESN WHERE FSNCODE = '{0}'
|
|
UNION
|
|
SELECT FTCCODE AS FNUMBER, 3 AS FTYPEID, '周转码' AS FTYPENAME FROM T_SFC_BARCODETC WHERE FTCCODE = '{0}'
|
|
UNION
|
|
SELECT FWIPCODE AS FNUMBER, 4 AS FTYPEID, '在制品条码' AS FTYPENAME FROM T_SFC_BARCODEWIP WHERE FWIPCODE = '{0}'", SData.FCode);
|
|
DataTable dtBarType = context.Database.SqlQuery(strSql);
|
|
if (dtBarType == null || dtBarType.Rows.Count == 0)
|
|
{
|
|
throw new O3AppException("该条码不存在或不符合条件,不可检验!!");
|
|
}
|
|
|
|
//条码类型
|
|
int codeType = dtBarType.Rows[0]["FTYPEID"].ToInt();
|
|
string strTableName = "";
|
|
string fNumber = string.Empty;
|
|
if (codeType != 1)
|
|
{
|
|
if (codeType == 2)
|
|
{
|
|
strTableName = "T_SFC_BARCODESN";
|
|
fNumber = "FSNCODE";
|
|
}
|
|
else if (codeType == 3)
|
|
{
|
|
strTableName = "T_SFC_BARCODETC";
|
|
fNumber = "FTCCODE";
|
|
}
|
|
else if (codeType == 4)
|
|
{
|
|
strTableName = "T_SFC_BARCODEWIP";
|
|
fNumber = "FWIPCODE";
|
|
}
|
|
if (!string.IsNullOrEmpty(SData.FTestResult.ToStringEx()))
|
|
{
|
|
mySql = $@"UPDATE {strTableName} SET FISCHECK=1,FQUALITYSTATUS={(SData.FTestResult == "0" ? 0 : 1)} WHERE {fNumber}='{SData.FCode}'";
|
|
}
|
|
//更新操作
|
|
if (await context.Database.SqlNonQueryAsync(mySql) < 1)
|
|
{
|
|
throw new O3AppException("更新条码出错!");
|
|
}
|
|
}
|
|
|
|
//根据单据内码判断是新增还是保存
|
|
if (SData.FInterID.ToStringEx() != "")
|
|
{
|
|
#region 更新主表操作(单据已有)
|
|
//先更新主表数据
|
|
//状态值
|
|
int FStatus = string.IsNullOrEmpty(SData.FTestResult.ToStringEx()) ? 0 : 40;
|
|
mySql = $@" UPDATE T_SFC_PROCESSINPECTION
|
|
SET FTESTTYPE={SData.FTestType.ToInt()},FCOUNT={SData.FCount.ToDecimal()}
|
|
,FQTY={SData.FQty.ToDecimal()},FACCEPTQTY={SData.FAcceptQty.ToDecimal()}
|
|
,FUNACCEPTQTY={SData.FUnAcceptQty.ToDecimal()}, FTESTRESULT='{(SData.FTestResult == "0" ? "1" : "2")}'
|
|
,FUNITID='{SData.FUnitID.ToStringEx()}', FSTATUS={FStatus}
|
|
,FCHECKERID='{LoginInfo.UserID}', FCHECKDATE=GETDATE()
|
|
WHERE FINTERID='{FInterID}' ";
|
|
//更新操作
|
|
if (await context.Database.SqlNonQueryAsync(mySql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!");
|
|
}
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 新增主表操作
|
|
string fBillNO = CreateBilllNO(context, JsonOperator.JsonSerialize(new { FINSPECTDATE = DateTime.Now }), "SFC_ProcessInpection", "T_SFC_ProcessInpection", "FBILLNO", "", "");
|
|
int fStatus = string.IsNullOrEmpty(SData.FTestResult.ToStringEx()) ? 0 : 40;
|
|
string fCode = context.Database.SqlQueryString($@"SELECT FCODE FROM T_SFC_FLOWCARDENTRY WHERE FDETAILID='{SData.FFlowCardDetailID.ToStringEx()}'").ToStringEx();
|
|
mySql = $@"INSERT INTO T_SFC_PROCESSINPECTION
|
|
(FINTERID,FBILLNO,FWORKTYPE,FDATE,FTESTWAYID,FMATERIALID
|
|
,FPROCESSID,FFLOWCARDID,FFLOWCARDDETAILID,FCODE,FUNITID
|
|
,FTESTTYPE,FQTY,FCOUNT,FAcceptQty,FUNACCEPTQTY,FTESTRESULT
|
|
,FSTATUS,FCHECKERID,FCHECKDATE,UID,C_USER,C_YMD,FREPORTID)
|
|
VALUES('{FInterID}','{fBillNO}',{2},'{DateTime.Now}','{SData.FTestWayID.ToStringEx()}'
|
|
,'{SData.FMaterialID.ToStringEx()}','{SData.FProcessID.ToStringEx()}'
|
|
,'{SData.FFlowCardID.ToStringEx()}','{SData.FFlowCardDetailID.ToStringEx()}'
|
|
,'{fCode}','{SData.FUnitID.ToStringEx()}'
|
|
,{SData.FTestType.ToInt()},{SData.FQty.ToDecimal()},{SData.FCount.ToDecimal()}
|
|
,{SData.FAcceptQty.ToDecimal()},{SData.FUnAcceptQty.ToDecimal()},'{(SData.FTestResult == "0" ? "1" : "2")}'
|
|
,{fStatus},'{LoginInfo.UserID.ToStringEx()}','{DateTime.Now}','{Guid.NewGuid().ToStringEx()}','{LoginInfo.UserID.ToStringEx()}','{DateTime.Now}','{SData.FReportId.ToStringEx()}')";
|
|
//更新操作
|
|
if (await context.Database.SqlNonQueryAsync(mySql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!");
|
|
}
|
|
#endregion
|
|
}
|
|
|
|
if (codeType != 1)
|
|
{
|
|
|
|
}
|
|
#region 统一操作表体数据
|
|
|
|
#region 检验项目表体
|
|
//清除已经删除的数据
|
|
DataTable dtTestHaveData = context.Database.SqlQuery($@"SELECT
|
|
FDETAILID,
|
|
FENTRYID
|
|
FROM T_SFC_PROCESSINPECTIONENTRY
|
|
WHERE FINTERID ='{FInterID}'");
|
|
//需要进行删除的sql集合
|
|
List<string> delTestList = new List<string>();
|
|
foreach (DataRow itemDel in dtTestHaveData.Rows)
|
|
{
|
|
if (testItemData.Count > 0)
|
|
{
|
|
//判断原数据是否还存在
|
|
if (testItemData.Where(o => o["FDetailID"].ToStringEx() == itemDel["FDETAILID"].ToStringEx()).ToList().Count < 1)
|
|
{
|
|
delTestList.Add($@" DELETE FROM T_SFC_PROCESSINPECTIONENTRY WHERE FDETAILID='{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
delTestList.Add($@" DELETE FROM T_SFC_PROCESSINPECTIONENTRY WHERE FDETAILID = '{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
if (delTestList.Count > 0)
|
|
{
|
|
//执行删除操作
|
|
foreach (string itemSql in delTestList)
|
|
{
|
|
if (await context.Database.SqlNonQueryAsync(itemSql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
}
|
|
}
|
|
|
|
//循环【巡检项目】表体数据
|
|
//操作巡检项目
|
|
foreach (var item in testItemData)
|
|
{
|
|
DataTable ODKGXSJTestProject = context.Database.SqlQuery($@"SELECT * FROM T_SFC_PROCESSINPECTIONENTRY
|
|
WHERE FINTERID='{FInterID}'
|
|
AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
if (ODKGXSJTestProject.Rows.Count > 0)
|
|
{
|
|
#region 更新操作
|
|
string updataData = string.Empty;
|
|
if (item["FAnalysisWay"].ToInt() == 1)
|
|
{
|
|
//定性分析
|
|
updataData += $@" ,FQUALTESTVALUE='{item["FTestValue"].ToStringEx()}',FTESTRESULT={item["FTestResult"].ToInt()}";
|
|
}
|
|
else if (item["FAnalysisWay"].ToInt() == 2)
|
|
{
|
|
//定量
|
|
updataData += $@" ,FQUANTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
//定量分析时。检验结果可以根据检验值自动判断 1=合格,2=不合格
|
|
if (item["FTestValue"].ToDecimal() >= item["FLwerLimit"].ToDecimal() && item["FTestValue"].ToDecimal() <= item["FUpperLimit"].ToDecimal())
|
|
{
|
|
updataData += $@" ,FTESTRESULT=1";
|
|
}
|
|
else
|
|
{
|
|
updataData += $@" ,FTESTRESULT=2";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//其它
|
|
updataData += $@" ,FOTHERTESTVALUE={item["FTestValue"].ToDecimal()},FTESTRESULT={item["FTestResult"].ToInt()}";
|
|
}
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
UPDATE T_SFC_PROCESSINPECTIONENTRY
|
|
SET
|
|
FENTRYID={item["FEntryID"].ToInt()},FVALUE={item["TargetValue"].ToDecimal()}
|
|
,FUPPERLIMIT={item["FUpperLimit"].ToDecimal()},FLWERLIMIT={item["FLwerLimit"].ToDecimal()}
|
|
,FANALYSISWAY='{item["FAnalysisWay"].ToInt()}'
|
|
{updataData}
|
|
WHERE FINTERID='{FInterID}' AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 插入操作
|
|
string fDetailID = Guid.NewGuid().ToStringEx();
|
|
string updataData = string.Empty;
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
INSERT INTO T_SFC_PROCESSINPECTIONENTRY
|
|
(FINTERID,FDETAILID,FENTRYID,FTESTPROJECTID,FTESTWAYID
|
|
,FTESTSTANDARDID,FTESTINSTRUMENTID,FVALUE,FUPPERLIMIT
|
|
,FLWERLIMIT,FTESTRESULT,FANALYSISWAY,UID)
|
|
VALUES('{FInterID}','{fDetailID}'
|
|
,{item["FEntryID"].ToInt()},'{item["FTestProjectID"].ToStringEx()}'
|
|
,'{item["FTestWayID"].ToStringEx()}','{item["FTestStandardID"].ToStringEx()}'
|
|
,'{item["FTestInstrumentID"].ToStringEx()}',{item["TargetValue"].ToDecimal()}
|
|
,{item["FUpperLimit"].ToDecimal()},{item["FLwerLimit"].ToDecimal()}
|
|
,{item["FTestResult"].ToInt()},{item["FAnalysisWay"].ToInt()}
|
|
,'{Guid.NewGuid().ToStringEx()}')");
|
|
if (item["FAnalysisWay"].ToInt() == 1)
|
|
{
|
|
//定性分析
|
|
updataData += $@" FQUALTESTVALUE='{item["FTestValue"].ToStringEx()}'";
|
|
}
|
|
else if (item["FAnalysisWay"].ToInt() == 2)
|
|
{
|
|
//定量
|
|
updataData += $@" FQUANTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
//定量分析时。检验结果可以根据检验值自动判断 1=合格,2=不合格
|
|
if (item["FTestValue"].ToDecimal() >= item["FLwerLimit"].ToDecimal() && item["FTestValue"].ToDecimal() <= item["FUpperLimit"].ToDecimal())
|
|
{
|
|
updataData += $@" ,FTESTRESULT={1}";
|
|
}
|
|
else
|
|
{
|
|
updataData += $@" ,FTESTRESULT={2}";
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//其它
|
|
updataData += $@" FOTHERTESTVALUE={item["FTestValue"].ToDecimal()}";
|
|
}
|
|
await context.Database.SqlNonQueryAsync($@"UPDATE T_SFC_PROCESSINPECTIONENTRY SET {updataData}
|
|
WHERE FDETAILID='{fDetailID}'");
|
|
#endregion
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 不合格原因表体
|
|
//清除已经删除的数据
|
|
DataTable dtBadHaveData = context.Database.SqlQuery($@"SELECT
|
|
FDETAILID,
|
|
FENTRYID
|
|
FROM T_SFC_PROCESSINPECTIONENTRY1
|
|
WHERE FINTERID ='{FInterID}'");
|
|
//需要进行删除的sql集合
|
|
List<string> delBadList = new List<string>();
|
|
foreach (DataRow itemDel in dtBadHaveData.Rows)
|
|
{
|
|
if (badReasonsData.Count > 0)
|
|
{
|
|
//判断原数据是否还存在
|
|
if (badReasonsData.Where(o => o["FDetailID"].ToStringEx() == itemDel["FDETAILID"].ToStringEx()).ToList().Count < 1)
|
|
{
|
|
delBadList.Add($@" DELETE FROM T_SFC_PROCESSINPECTIONENTRY1 WHERE FDETAILID='{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
delBadList.Add($@" DELETE FROM T_SFC_PROCESSINPECTIONENTRY1 WHERE FDETAILID = '{itemDel["FDETAILID"].ToStringEx()}'; ");
|
|
}
|
|
}
|
|
if (delBadList.Count > 0)
|
|
{
|
|
//执行删除操作
|
|
foreach (string itemSql in delBadList)
|
|
{
|
|
if (await context.Database.SqlNonQueryAsync(itemSql) < 1)
|
|
{
|
|
throw new O3AppException("保存出错!!");
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//循环【首检项目】表体数据
|
|
//操作首检项目
|
|
foreach (var item in badReasonsData)
|
|
{
|
|
DataTable ODKGXSJFault = context.Database.SqlQuery($@"SELECT * FROM T_SFC_PROCESSINPECTIONENTRY1
|
|
WHERE FINTERID='{FInterID}'
|
|
AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
if (ODKGXSJFault.Rows.Count > 0)
|
|
{
|
|
#region 更新操作
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
UPDATE T_SFC_PROCESSINPECTIONENTRY1
|
|
SET
|
|
FENTRYID={item["FEntryID"].ToInt()},FFAULTREASON='{item["FFaultReasonID"].ToStringEx()}'
|
|
,FFAULTQTY={item["FFaultQty"].ToDecimal()}
|
|
WHERE FINTERID='{FInterID}' AND FDETAILID='{item["FDetailID"].ToStringEx()}'");
|
|
#endregion
|
|
}
|
|
else
|
|
{
|
|
#region 插入操作
|
|
string fDetailID = Guid.NewGuid().ToStringEx();
|
|
await context.Database.SqlNonQueryAsync($@"
|
|
INSERT INTO T_SFC_PROCESSINPECTIONENTRY1
|
|
(FINTERID,FDETAILID,FENTRYID,FFAULTREASON,FFAULTQTY,UID)
|
|
VALUES('{FInterID}','{fDetailID}'
|
|
,{item["FEntryID"].ToInt()},'{item["FFaultReasonID"].ToStringEx()}'
|
|
,{item["FFaultQty"].ToDecimal()},'{Guid.NewGuid().ToStringEx()}')");
|
|
#endregion
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#endregion
|
|
//更新流转卡合格数量
|
|
DataTable flowCardEntry = context.Database.SqlQuery($@"SELECT FQUAQTY FROM T_SFC_FLOWCARDENTRY WHERE FDETAILID='{SData.FFlowCardDetailID.ToStringEx()}'");
|
|
if (flowCardEntry.Rows[0]["FQUAQTY"].ToDecimal() - SData.FUnAcceptQty.ToDecimal() >= 0)
|
|
{
|
|
string updateFlowCardEntry = $@"UPDATE T_SFC_FLOWCARDENTRY SET FQUAQTY=FQUAQTY-{SData.FUnAcceptQty.ToDecimal()} WHERE FDETAILID='{SData.FFlowCardDetailID.ToStringEx()}'";
|
|
if (await context.Database.SqlNonQueryAsync(updateFlowCardEntry) < 1)
|
|
{
|
|
throw new O3AppException("更新工序流转卡出错!!");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
throw new O3AppException("不合格数已超过工序流转卡的合格数,提交失败!!");
|
|
}
|
|
|
|
//提交事务
|
|
context.Database.CommitTransaction();
|
|
|
|
returnData = new { retMsg = "保存成功!", FInterID = FInterID };
|
|
return await Task.Run(() => returnData);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
if (context.Database.CurrentTransaction != null)
|
|
{
|
|
context.Database.RollbackTransaction();
|
|
}
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 文档查看功能
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> GetAssignDocument(GetAssignDocument SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
|
|
#region 自定义AND传参
|
|
object returnData = new { };
|
|
//工序内码
|
|
string FProcessID = SData.FProcessID.ToStringEx();
|
|
//产品内码
|
|
string FItemID = SData.FItemID.ToStringEx();
|
|
//文件名
|
|
string FName = SData.FName.ToStringEx();
|
|
//查询条件
|
|
string sqlWhere = string.Empty;
|
|
#endregion
|
|
|
|
if (!string.IsNullOrEmpty(FName))
|
|
{
|
|
sqlWhere += $@" AND FNAME LIKE '%{FName}%' ";
|
|
}
|
|
//获取系统配置
|
|
Hashtable PBCursor = context.PBValue("WFMUpload,C,;");
|
|
//上传服务器IP地址
|
|
string serverIP = context.Database.SqlQueryString("SELECT FVALUE FROM SYS_DEPARAMETERS WHERE FNUMBER = 'FileServerUrl' ");
|
|
if (serverIP.IsNullOrEmpty())
|
|
{
|
|
serverIP = "http://127.0.0.1";
|
|
}
|
|
//文件所在文件夹路径
|
|
string filePath = PBCursor["WFMUpload"].ToStringEx();
|
|
string fileCut = filePath.Substring(filePath.LastIndexOf(@"\") + 1);
|
|
string fileLink = serverIP + "/" + fileCut + "/";
|
|
Encoding ed = Encoding.GetEncoding("gb2312");
|
|
|
|
string sqlSearch = $@"SELECT
|
|
FINTERID,
|
|
'{serverIP}' AS ServerIP,
|
|
'{filePath}' + FBILLNO + '\' + FFILENAME AS FilePath,
|
|
C_YMD AS FDate,
|
|
C_USER AS FUploader,
|
|
FNAME,
|
|
FBILLNO,
|
|
FFILENAME,
|
|
FTYPE,
|
|
CASE WHEN FTYPE='1' THEN '过程标准文件'
|
|
WHEN FTYPE='2' THEN '控制计划'
|
|
WHEN FTYPE='3' THEN '异常控制文件'
|
|
END AS FTypeName
|
|
FROM T_SFC_WORKFILEMANAGE
|
|
WHERE FFILENAME !='' AND FDELETED=0 AND FMATERIALID='{FItemID}' AND FPROCESSID='{FProcessID}'
|
|
AND (FEFFECTIVEDATE IS NULL OR FEFFECTIVEDATE<=GETDATE())
|
|
AND (FEXPIRYDATE IS NULL OR FEXPIRYDATE > GETDATE()) {sqlWhere} ORDER BY C_YMD DESC";
|
|
//获取数据
|
|
DataTable dt = context.Database.SqlQuery(sqlSearch);
|
|
dt.Columns.Add("FileLink", typeof(string));
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
string finalLink = fileLink + dt.Rows[i]["FBILLNO"] + '/' + System.Web.HttpUtility.UrlEncode(dt.Rows[i]["FFILENAME"].ToStringEx(), ed);
|
|
dt.Rows[i]["FileLink"] = finalLink;
|
|
}
|
|
returnData = new
|
|
{
|
|
proStandardFile = dt.Select("FTYPE='1'").Length > 0 ? dt.Select("FTYPE='1'").CopyToDataTable<DataRow>() : new DataTable(),
|
|
controlPlanFile = dt.Select("FTYPE='2'").Length > 0 ? dt.Select("FTYPE='2'").CopyToDataTable<DataRow>() : new DataTable(),
|
|
abnormalControlFile = dt.Select("FTYPE='3'").Length > 0 ? dt.Select("FTYPE='3'").CopyToDataTable<DataRow>() : new DataTable()
|
|
};
|
|
|
|
return await Task.Run(() => returnData);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 安灯异常呼叫列表接口
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> GetAbnormalCall(GetAbnormalCall SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义AND传参
|
|
object returnData = new { };
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//资源编码列表
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
//查询条件
|
|
string sqlWhere = string.Empty;
|
|
#endregion
|
|
|
|
//资源NO
|
|
if (resource.Count>0)
|
|
{
|
|
sqlWhere += $@" AND T2.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"
|
|
SELECT XX.ROWNO,X.* FROM
|
|
(
|
|
SELECT
|
|
T1.FINTERID,
|
|
T2.FNUMBER AS FResourceNo,
|
|
T3.FNUMBER AS FProductNumber,
|
|
T5.FBILLNO AS FFlowCardBillNO,
|
|
T1.FPROCESSID,
|
|
T6.FNAME AS FProcessName,
|
|
ISNULL(T7.USER_NAME,ISNULL(T8.FNAME,'')) AS FCallName,
|
|
T9.FNAME AS FTypeName,
|
|
T1.FCALLTIME,
|
|
DATEDIFF(MINUTE, T1.FCALLTIME, ISNULL(T1.FRESPONSETIME,GETDATE())) AS FCallTimeLengh,
|
|
ISNULL(T10.USER_NAME,ISNULL(T11.FNAME,'')) as FRespondName,
|
|
T1.FRESPONSETIME,
|
|
T1.FDULESTATUS,
|
|
CASE T1.FDULESTATUS WHEN 2 THEN '异常呼叫'
|
|
WHEN 3 THEN '处理中'
|
|
WHEN 4 THEN '呼叫关闭'
|
|
WHEN 5 THEN '处理完成'
|
|
END AS FDuleStatusName
|
|
FROM T_SFC_ANDONWARNING T1
|
|
LEFT JOIN T_ENG_RESOURCE T2 on T2.FINTERID=T1.FRESOURCEID --资源
|
|
LEFT JOIN T_BD_MATERIAL T3 ON T3.FINTERID=T1.FPRODUCTID --产品
|
|
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T4.FDETAILID=T1.FWORKFLOWCARDDETAILID
|
|
LEFT JOIN T_SFC_FLOWCARD T5 ON T5.FINTERID=T4.FINTERID --流转卡
|
|
LEFT JOIN T_SFC_PROCESS T6 on T6.FINTERID=T1.FPROCESSID --工序
|
|
LEFT JOIN SYS_LOGINUSER T7 on T7.USER_ID=T1.FCALLID --呼叫人
|
|
LEFT JOIN T_HR_EMPINFO T8 on T8.FINTERID=T1.FCALLID
|
|
LEFT JOIN T_AS_WARNINGEVENT T9 on T9.FINTERID=T1.FEVENTID --呼叫事件
|
|
LEFT JOIN SYS_LOGINUSER T10 on T10.USER_ID=T1.FRESPONSEID --响应人
|
|
LEFT JOIN T_HR_EMPINFO T11 on T11.FINTERID=T1.FRESPONSEID
|
|
WHERE T1.FDULESTATUS NOT IN (4,5) {sqlWhere}
|
|
) X
|
|
INNER JOIN
|
|
(
|
|
SELECT TOP {size} A.* FROM
|
|
(
|
|
SELECT
|
|
ROW_NUMBER() OVER (ORDER BY MAX(T1.FCALLTIME) DESC,T2.FNumber,T3.FNumber,T5.FBillNO,T1.FPROCESSID) AS ROWNO,
|
|
T2.FNUMBER AS FResourceNo,
|
|
T3.FNUMBER AS FProductNumber,
|
|
T5.FBILLNO AS FFlowCardBillNO,
|
|
T1.FPROCESSID
|
|
FROM T_SFC_ANDONWARNING T1
|
|
LEFT JOIN T_ENG_RESOURCE T2 on T2.FINTERID=T1.FRESOURCEID --资源
|
|
LEFT JOIN T_BD_MATERIAL T3 ON T3.FINTERID=T1.FPRODUCTID --产品
|
|
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T4.FDETAILID=T1.FWORKFLOWCARDDETAILID
|
|
LEFT JOIN T_SFC_FLOWCARD T5 ON T5.FINTERID=T4.FINTERID --流转卡
|
|
WHERE T1.FDULESTATUS NOT IN (4,5) {sqlWhere}
|
|
GROUP BY T2.FNUMBER,T3.FNUMBER,T5.FBILLNO,T1.FPROCESSID
|
|
) A WHERE ROWNO > {size * (page - 1)} ORDER BY A.ROWNO
|
|
) XX ON XX.FResourceNo=X.FResourceNo AND XX.FProductNumber=X.FProductNumber
|
|
AND (XX.FFlowCardBillNO=X.FFlowCardBillNO OR (XX.FFlowCardBillNO IS NULL AND X.FFlowCardBillNO IS NULL)) AND XX.FPROCESSID=X.FPROCESSID
|
|
ORDER BY XX.ROWNO ";
|
|
//获取数据
|
|
DataTable dt = context.Database.SqlQuery(sqlSearch);
|
|
//数据集合
|
|
List<object> listAbnormalCall = new List<object>();
|
|
//添加数据
|
|
//分组key
|
|
string groupKey = string.Empty;
|
|
//单个对象
|
|
object itemAbnormalCall = null;
|
|
//对象中的对象
|
|
foreach (DataRow item in dt.Rows)
|
|
{
|
|
if (groupKey != item["ROWNO"].ToStringEx())
|
|
{
|
|
itemAbnormalCall = new
|
|
{
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProductNumber = item["FProductNumber"].ToStringEx(),
|
|
FFlowCardBillNO = item["FFlowCardBillNO"].ToStringEx(),
|
|
FProcessID = item["FProcessID"].ToStringEx(),
|
|
FProcessName = item["FProcessName"].ToStringEx(),
|
|
DetailCall = dt.Select("ROWNO=" + item["ROWNO"].ToStringEx()).CopyToDataTable<DataRow>()
|
|
};
|
|
listAbnormalCall.Add(itemAbnormalCall);
|
|
}
|
|
groupKey = item["ROWNO"].ToStringEx();
|
|
}
|
|
returnData = new { abnormalCall = listAbnormalCall };
|
|
return await Task.Run(() => returnData);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 安灯异常呼叫列表接口
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<object> GetAbnormalCalls(GetAbnormalCall SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义AND传参
|
|
object returnData = new { };
|
|
//分页查询使用
|
|
int page = SData.Page.ToInt();
|
|
int size = SData.Size.ToInt();
|
|
//资源编码列表
|
|
List<string> resource = JsonOperator.JsonDeserialize<List<string>>(SData.Resource.ToStringEx());
|
|
//查询条件
|
|
string sqlWhere = string.Empty;
|
|
#endregion
|
|
|
|
//资源NO
|
|
if (resource.Count > 0)
|
|
{
|
|
sqlWhere += $@" AND T2.FNUMBER IN ('{string.Join("','", resource)}') ";
|
|
}
|
|
string sqlSearch = $@"
|
|
SELECT TOP {size} X.ROWNO,X.*,(SELECT COUNT(1) FROM T_SFC_ANDONWARNING T1
|
|
INNER JOIN dbo.T_ENG_RESOURCE T2 ON T1.FRESOURCEID=T2.FINTERID
|
|
WHERE T1.FDULESTATUS NOT IN (4,5) {sqlWhere}) AS TOTALCOUNT FROM
|
|
(
|
|
SELECT
|
|
T1.FINTERID,ROW_NUMBER() OVER (ORDER BY T1.FCALLTIME DESC, T1.C_YMD DESC) AS ROWNO,
|
|
T1.FRESOURCEID as FResourceId,
|
|
T2.FNUMBER AS FResourceNo,
|
|
T3.FNUMBER AS FProductNumber,
|
|
T5.FBILLNO AS FFlowCardBillNO,
|
|
T1.FPROCESSID,
|
|
T6.FNAME AS FProcessName,
|
|
ISNULL(T7.USER_NAME,ISNULL(T8.FNAME,'')) AS FCallName,
|
|
T9.FNAME AS FTypeName,
|
|
T1.FCALLTIME,
|
|
DATEDIFF(MINUTE, T1.FCALLTIME, ISNULL(T1.FRESPONSETIME,GETDATE())) AS FCallTimeLengh,
|
|
ISNULL(T10.USER_NAME,ISNULL(T11.FNAME,'')) as FRespondName,
|
|
T1.FRESPONSETIME,
|
|
T1.FDULESTATUS,
|
|
CASE T1.FDULESTATUS WHEN 2 THEN '异常呼叫'
|
|
WHEN 3 THEN '处理中'
|
|
WHEN 4 THEN '呼叫关闭'
|
|
WHEN 5 THEN '处理完成'
|
|
END AS FDuleStatusName,
|
|
T12.FINTERID AS FReportId
|
|
FROM T_SFC_ANDONWARNING T1
|
|
LEFT JOIN T_ENG_RESOURCE T2 on T2.FINTERID=T1.FRESOURCEID --资源
|
|
LEFT JOIN T_BD_MATERIAL T3 ON T3.FINTERID=T1.FPRODUCTID --产品
|
|
LEFT JOIN T_SFC_FLOWCARDENTRY T4 ON T4.FDETAILID=T1.FWORKFLOWCARDDETAILID
|
|
LEFT JOIN T_SFC_FLOWCARD T5 ON T5.FINTERID=T4.FINTERID --流转卡
|
|
LEFT JOIN T_SFC_PROCESS T6 on T6.FINTERID=T1.FPROCESSID --工序
|
|
LEFT JOIN SYS_LOGINUSER T7 on T7.USER_ID=T1.FCALLID --呼叫人
|
|
LEFT JOIN T_HR_EMPINFO T8 on T8.FINTERID=T1.FCALLID
|
|
LEFT JOIN T_AS_WARNINGEVENT T9 on T9.FINTERID=T1.FEVENTID --呼叫事件
|
|
LEFT JOIN SYS_LOGINUSER T10 on T10.USER_ID=T1.FRESPONSEID --响应人
|
|
LEFT JOIN T_HR_EMPINFO T11 on T11.FINTERID=T1.FRESPONSEID
|
|
LEFT JOIN T_SFC_REPORT T12 ON T1.FREBILLNO=T12.FBILLNO
|
|
WHERE T1.FDULESTATUS NOT IN (4,5) {sqlWhere}
|
|
) X WHERE ROWNO > {size * (page - 1)}
|
|
ORDER BY X.ROWNO ";
|
|
//获取数据
|
|
DataTable dt = context.Database.SqlQuery(sqlSearch);
|
|
//数据集合
|
|
List<object> listAbnormalCall = new List<object>();
|
|
//添加数据
|
|
//分组key
|
|
string groupKey = string.Empty;
|
|
List<object> listTotalCall = new List<object>();
|
|
//对象中的对象
|
|
int count = 0;
|
|
foreach (DataRow item in dt.Rows)
|
|
{
|
|
count = item["TOTALCOUNT"].ToInt();
|
|
listTotalCall.Add(new
|
|
{
|
|
FINTERID = item["FINTERID"].ToStringEx(),
|
|
FResourceId = item["FResourceId"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProductNumber = item["FProductNumber"].ToStringEx(),
|
|
FFlowCardBillNO = item["FFlowCardBillNO"].ToStringEx(),
|
|
FProcessID = item["FProcessID"].ToStringEx(),
|
|
FProcessName = item["FProcessName"].ToStringEx(),
|
|
FCallName = item["FCallName"].ToStringEx(),
|
|
FTypeName = item["FTypeName"].ToStringEx(),
|
|
FCALLTIME = item["FCALLTIME"].ToStringEx(),
|
|
FCallTimeLengh = item["FCallTimeLengh"].ToStringEx(),
|
|
FRespondName = item["FRespondName"].ToStringEx(),
|
|
FRESPONSETIME = item["FRESPONSETIME"].ToStringEx(),
|
|
FDULESTATUS = item["FDULESTATUS"].ToStringEx(),
|
|
FDuleStatusName = item["FDuleStatusName"].ToStringEx(),
|
|
FReportId = item["FReportId"].ToStringEx()
|
|
});
|
|
|
|
}
|
|
|
|
returnData = new { abnormalCall = listTotalCall, totalCount = count };
|
|
return await Task.Run(() => returnData);
|
|
}
|
|
|
|
public async Task<string> DoAbnormalCall(DoAbnormalCall SData)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
#region 自定义AND传参
|
|
//返回消息
|
|
string retMsg = string.Empty;
|
|
//操作模式
|
|
string doType = SData.DoType.ToStringEx();
|
|
//安灯内码列表
|
|
List<string> listFInterID = SData.ListFInterID;
|
|
|
|
try
|
|
{
|
|
//开启事务
|
|
context.Database.BeginTransaction();
|
|
if (doType == "1")
|
|
{
|
|
//响应
|
|
for (int i = 0; i < listFInterID.Count; i++)
|
|
{
|
|
await context.Database.SqlNonQueryAsync($@"UPDATE T_SFC_ANDONWARNING
|
|
SET FRESPONSEID='{LoginInfo.UserID}',FRESPONSETIME='{DateTime.Now}'
|
|
,FDULESTATUS={3}
|
|
WHERE FINTERID='{listFInterID[i].ToStringEx()}'");
|
|
}
|
|
}
|
|
else if (doType == "2")
|
|
{
|
|
//关闭
|
|
for (int i = 0; i < listFInterID.Count; i++)
|
|
{
|
|
string updateSql = string.Empty;
|
|
if ((SData.IsDule.ToStringEx()).ToBoolean())
|
|
{
|
|
string userID = SData.DuleUserID.ToStringEx().IsNullOrEmpty() ? LoginInfo.UserID : SData.DuleUserID.ToStringEx();
|
|
updateSql += $@",FDULEID='{userID}',FDULETIME='{SData.DuleTime.ToDateTime()}'
|
|
,FDULERESULT='{SData.DuleResult.ToStringEx()}',FDULESTATUS={5}";
|
|
}
|
|
else
|
|
{
|
|
updateSql += $@",FDULESTATUS={4}";
|
|
}
|
|
DataTable abnormalCallBatch = context.Database.SqlQuery($@"SELECT * FROM T_SFC_ANDONWARNING
|
|
WHERE FINTERID='{listFInterID[i].ToStringEx()}'");
|
|
//判断响应人、响应时间是否需要更新
|
|
if (abnormalCallBatch.Rows.Count > 0)
|
|
{
|
|
if (abnormalCallBatch.Rows[0]["FRESPONSEID"].ToStringEx().IsNullOrEmpty())
|
|
{
|
|
//响应人、响应时间
|
|
updateSql += $@",FRESPONSEID='{LoginInfo.UserID}',FRESPONSETIME='{DateTime.Now}'";
|
|
}
|
|
}
|
|
await context.Database.SqlNonQueryAsync($@"UPDATE T_SFC_ANDONWARNING SET FCLOSEID='{LoginInfo.UserID}',FCLOSETIME='{DateTime.Now}'
|
|
{updateSql} WHERE FINTERID='{listFInterID[i]}'");
|
|
//解除停工造成的数据状态
|
|
DataTable callEvent = context.Database.SqlQuery($@"SELECT A.FRESOURCEID,C.FNUMBER,D.FREPORTID,D.FDATASTATUS FROM T_SFC_ANDONWARNING A
|
|
LEFT JOIN T_AS_WARNINGEVENT B on B.FINTERID=A.FEVENTID
|
|
LEFT JOIN T_BAS_ASSISTANTDATAENTRY C ON C.FINTERID = B.FTYPEID
|
|
LEFT JOIN T_SFC_RESOURCEWORKSTATE D ON A.FRESOURCEID=D.FINTERID
|
|
WHERE A.FINTERID='{listFInterID[i]}'");
|
|
if (callEvent.Rows.Count > 0)
|
|
{
|
|
if (callEvent.Rows[0]["FREPORTID"].ToStringEx().Equals(SData.reportID))
|
|
{
|
|
if ((callEvent.Rows[0]["FNUMBER"].ToStringEx().Equals("ES") && callEvent.Rows[0]["FDATASTATUS"].ToStringEx().Equals("K"))
|
|
|| (callEvent.Rows[0]["FNUMBER"].ToStringEx().Equals("MS") && callEvent.Rows[0]["FDATASTATUS"].ToStringEx().Equals("F"))
|
|
|| (callEvent.Rows[0]["FNUMBER"].ToStringEx().Equals("OS") && callEvent.Rows[0]["FDATASTATUS"].ToStringEx().Equals("L"))
|
|
|| (callEvent.Rows[0]["FNUMBER"].ToStringEx().Equals("QS") && callEvent.Rows[0]["FDATASTATUS"].ToStringEx().Equals("H")))
|
|
{
|
|
await context.Database.SqlNonQueryAsync($@"UPDATE T_SFC_RESOURCEWORKSTATE SET FDATASTATUS=FPREDATASTATUS
|
|
WHERE FINTERID='{callEvent.Rows[0]["FRESOURCEID"].ToStringEx()}'");
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
//提交事务
|
|
context.Database.CommitTransaction();
|
|
|
|
retMsg = "保存成功!!";
|
|
return await Task.Run(() => retMsg);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
if (context.Database.CurrentTransaction != null)
|
|
{
|
|
context.Database.RollbackTransaction();
|
|
}
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 安灯异常呼叫列表接口
|
|
/// </summary>
|
|
/// <param name="SData"></param>
|
|
/// <returns></returns>
|
|
public async Task<List<object>> GetAbnormalCallResource(string resourceID)
|
|
{
|
|
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
|
|
string sqlSearch = $@"
|
|
SELECT X.* FROM(SELECT
|
|
S.FINTERID,S.FRESOURCEID,ROW_NUMBER() OVER (ORDER BY S.FCALLTIME DESC, S.C_YMD DESC) AS ROWNO,
|
|
S1.FNUMBER AS FResourceNo,
|
|
S2.FNUMBER AS FProductNumber,
|
|
S4.FBILLNO AS FFlowCardBillNO,
|
|
S.FPROCESSID,
|
|
S5.FNAME AS FProcessName,
|
|
ISNULL(S6.USER_NAME,ISNULL(S7.FNAME,'')) AS FCallName,
|
|
S8.FNAME AS FTypeName,
|
|
S.FCALLTIME,
|
|
DATEDIFF(MINUTE, S.FCALLTIME, ISNULL(S.FRESPONSETIME,GETDATE())) AS FCallTimeLengh,
|
|
ISNULL(S9.USER_NAME,ISNULL(S10.FNAME,'')) as FRespondName,
|
|
S.FRESPONSETIME,
|
|
S.FDULESTATUS,
|
|
CASE S.FDULESTATUS WHEN 2 THEN '异常呼叫'
|
|
WHEN 3 THEN '处理中'
|
|
WHEN 4 THEN '呼叫关闭'
|
|
WHEN 5 THEN '处理完成'
|
|
END AS FDuleStatusName
|
|
FROM T_SFC_ANDONWARNING S
|
|
LEFT JOIN T_ENG_RESOURCE S1 on S1.FINTERID=S.FRESOURCEID --资源
|
|
LEFT JOIN T_BD_MATERIAL S2 ON S2.FINTERID=S.FPRODUCTID --产品
|
|
LEFT JOIN T_SFC_FLOWCARDENTRY S3 ON S3.FDETAILID=S.FWORKFLOWCARDDETAILID
|
|
LEFT JOIN T_SFC_FLOWCARD S4 ON S4.FINTERID=S3.FINTERID --流转卡
|
|
LEFT JOIN T_SFC_PROCESS S5 on S5.FINTERID=S.FPROCESSID --工序
|
|
LEFT JOIN SYS_LOGINUSER S6 on S6.USER_ID=S.FCALLID --呼叫人
|
|
LEFT JOIN T_HR_EMPINFO S7 on S7.FINTERID=S.FCALLID
|
|
LEFT JOIN T_AS_WARNINGEVENT S8 on S8.FINTERID=S.FEVENTID --呼叫事件
|
|
LEFT JOIN SYS_LOGINUSER S9 on S9.USER_ID=S.FRESPONSEID --响应人
|
|
LEFT JOIN T_HR_EMPINFO S10 on S10.FINTERID=S.FRESPONSEID
|
|
WHERE S.FDULESTATUS NOT IN (4,5) AND S.FRESOURCEID='{resourceID}')X
|
|
ORDER BY X.ROWNO";
|
|
//获取数据
|
|
DataTable dt = context.Database.SqlQuery(sqlSearch);
|
|
//数据集合
|
|
List<object> listAbnormalCall = new List<object>();
|
|
//添加数据
|
|
//单个对象
|
|
object itemAbnormalCall = null;
|
|
//对象中的对象
|
|
foreach (DataRow item in dt.Rows)
|
|
{
|
|
itemAbnormalCall = new
|
|
{
|
|
FINTERID = item["FINTERID"].ToStringEx(),
|
|
FResourceNo = item["FResourceNo"].ToStringEx(),
|
|
FProductNumber = item["FProductNumber"].ToStringEx(),
|
|
FFlowCardBillNO = item["FFlowCardBillNO"].ToStringEx(),
|
|
FProcessID = item["FProcessID"].ToStringEx(),
|
|
FProcessName = item["FProcessName"].ToStringEx(),
|
|
FCallName = item["FCallName"].ToStringEx(),
|
|
FTypeName = item["FTypeName"].ToStringEx(),
|
|
FCALLTIME = item["FCALLTIME"].ToStringEx(),
|
|
FCallTimeLengh = item["FCallTimeLengh"].ToStringEx(),
|
|
FRespondName = item["FRespondName"].ToStringEx(),
|
|
FRESPONSETIME = item["FRESPONSETIME"].ToStringEx(),
|
|
FDULESTATUS = item["FDULESTATUS"].ToStringEx(),
|
|
FDuleStatusName = item["FDuleStatusName"].ToStringEx()
|
|
};
|
|
listAbnormalCall.Add(itemAbnormalCall);
|
|
}
|
|
return await Task.Run(() => listAbnormalCall);
|
|
}
|
|
}
|
|
}
|