public MES_NGAPIParam GetMesNGParam(QADetailSearch model, Machine_Station_CustomerDTO stationInfo)
{
var timeInterval = model.Time_interval.Split('-');
var startTime = timeInterval[0];
var endTime = timeInterval[1];
MES_NGAPIParam requestParam = new MES_NGAPIParam();
requestParam.startTime = Convert.ToDateTime(model.ProductDate + " " + startTime).AddHours(-0.5).ToString("yyyy-MM-dd HH:mm");
requestParam.endTime = Convert.ToDateTime(model.ProductDate + " " + endTime).AddHours(-0.5).ToString("yyyy-MM-dd HH:mm");
requestParam.PIS_Customer_Name = stationInfo.PIS_Customer_Name;
requestParam.MES_Customer_Name = stationInfo.MES_Customer_Name;
return(requestParam);
}
public List <QualityAssurance_ExceptionType> QueryQAExceptionTypeAPI(QADetailSearch searchData)
{
List <QualityAssurance_ExceptionType> result = new List <QualityAssurance_ExceptionType>();
try
{
string sqlWhere = "";
if (!string.IsNullOrEmpty(searchData.ShortName))
{
sqlWhere = string.Format(@" ExType.ShortName=N'{0}' ", searchData.ShortName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeName))
{
sqlWhere = sqlWhere + string.Format(@"AND ExType.TypeName=N'{0}' ", searchData.TypeName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeCode))
{
sqlWhere = sqlWhere + string.Format(@"AND ExType.Org_TypeCode='{0}' ", searchData.TypeCode.Replace("'", "''"));
}
if (searchData.FlowChart_Master_UID != 0)
{
sqlWhere = sqlWhere + string.Format(@"AND ExType.FlowChart_Master_UID={0} ", searchData.FlowChart_Master_UID);
}
if (sqlWhere.StartsWith("AND"))
{
sqlWhere = sqlWhere.Remove(0, 3);
}
string sql = string.Format(@"
DECLARE @TypeCode NVARCHAR(50)
SELECT @TypeCode=Org_TypeCode FROM dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
WHERE {0}
IF {1}
BEGIN
IF ISNULL(@TypeCode,'')=''
BEGIN
SET @TypeCode=''
END
ELSE
BEGIN
IF @TypeCode LIKE '%000000'
BEGIN
SELECT * FROM dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
WHERE ExType.EnableFlag=1 AND ExType.FlowChart_Master_UID={2} and ExType.Org_TypeCode LIKE SUBSTRING(@TypeCode,1,3)+'%000'
AND ( (TypeClassify=N'外观不良' AND Org_TypeCode NOT LIKE '%000000' AND Org_TypeCode NOT LIKE '%000')
OR (TypeClassify=N'尺寸不良' AND Org_TypeCode NOT LIKE '%000000'))
END
ELSE IF @TypeCode LIKE '%000'
BEGIN
SELECT * FROM dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
WHERE ExType.EnableFlag=1 AND ExType.FlowChart_Master_UID={2} and ExType.Org_TypeCode LIKE SUBSTRING(@TypeCode,1,6)+'%'
AND ( (TypeClassify=N'外观不良' AND Org_TypeCode NOT LIKE '%000000' AND Org_TypeCode NOT LIKE '%000')
OR (TypeClassify=N'尺寸不良' AND Org_TypeCode NOT LIKE '%000000'))
END
ELSE
BEGIN
SELECT * FROM dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
WHERE ExType.EnableFlag=1 and {0}
END
END
END", sqlWhere, searchData.IsContainsChild ? "1=1" : "1<>1", searchData.FlowChart_Master_UID);
var query = DataContext.Database.SqlQuery <QualityAssurance_ExceptionType>(sql).ToList();
result = query;
}
catch (Exception ex)
{
log.Error(ex);
}
return(result);
}
public List <QAInputDetailVM> QueryQAInputDetailVM(QADetailSearch searchData, bool searchHistoryFlag)
{
List <QAInputDetailVM> result = new List <QAInputDetailVM>();
try
{
string masterSql = "";
if (searchData.QAMaster_UID != 0)
{
masterSql = string.Format(" QaDetail.QualityAssurance_InputMaster_UID={0}", searchData.QAMaster_UID);
}
else
{
masterSql = string.Format(" QaMaster.FlowChart_Detail_UID={0}", searchData.FlowChart_Detail_UID);
}
#region --- where sql
string sqlWhere = "";
if (!string.IsNullOrEmpty(searchData.ShortName))
{
sqlWhere = sqlWhere + string.Format(@" AND ExType.ShortName=N'{0}'", searchData.ShortName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeName))
{
sqlWhere = sqlWhere + string.Format(@" AND ExType.TypeName=N'{0}'", searchData.TypeName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeCode))
{
sqlWhere = sqlWhere + string.Format(@" AND ExType.Org_TypeCode='{0}'", searchData.TypeCode.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.Project))
{
sqlWhere = sqlWhere + string.Format(@" AND ExType.FlowChart_Master_UID={0}", searchData.FlowChart_Master_UID);
}
if (sqlWhere.StartsWith(" AND"))
{
sqlWhere = sqlWhere.Remove(0, 4);
}
#endregion
string sql = @"
DECLARE @TypeCode NVARCHAR(50)
SELECT @TypeCode=Org_TypeCode FROM dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
WHERE {1}
IF {2}
BEGIN
DECLARE @levelOne NVARCHAR(3),
@levelTwo NVARCHAR(3),
@levelThree NVARCHAR(3)
IF ISNULL(@TypeCode,'')=''
BEGIN
SET @TypeCode=''
END
ELSE
BEGIN
SET @levelOne=SUBSTRING(@TypeCode,1,3)
SET @levelTwo=SUBSTRING(@TypeCode,4,3)
SET @levelThree=SUBSTRING(@TypeCode,7,3)
IF @levelThree<>'000'
BEGIN
SET @TypeCode = @levelOne + @levelTwo + @levelThree
END
ELSE IF @levelTwo <> '000'
BEGIN
SET @TypeCode = @levelOne + @levelTwo + '%'
END
ELSE
BEGIN
SET @TypeCode = @levelOne + '%'
END
END
END
SELECT ExType.TypeName AS ExceptionTypeName,
ExType.BadTypeCode as BadTypeCode,
ExType.BadTypeEnglishCode as BadTypeEnglishCode,
QaDetail.QualityAssurance_InputDetail_UID,
{4} AS QualityAssurance_InputMaster_UID,
QaDetail.ExceptionType_UID,
QaDetail.RepairNG_Qty,
QaDetail.NG_Qty,
QaDetail.SepcialAccept_Qty,
QaDetail.Repair_Qty,
QaDetail.IsDeleted,
QaDetail.Create_Date,
QaDetail.Creator_UID,
QaDetail.Modified_Date,
QaDetail.Modified_UID,
QaDetail.Displace_Qty FROM {0} QaDetail WITH(NOLOCK)
INNER JOIN dbo.QualityAssurance_ExceptionType ExType WITH(NOLOCK)
ON ExType.ExceptionType_UID = QaDetail.ExceptionType_UID
INNER JOIN {5} QaMaster WITH(NOLOCK)
ON QaMaster.QualityAssurance_InputMaster_UID = QaDetail.QualityAssurance_InputMaster_UID
WHERE {3} and ExType.Org_TypeCode LIKE @TypeCode
";
string sql1 = string.Format(sql, searchHistoryFlag? "dbo.QualityAssurance_InputDetail_History" : "dbo.QualityAssurance_InputDetail", sqlWhere, searchData.IsContainsChild ? "1=1" : "1<>1", masterSql, searchData.QAMaster_UID, searchHistoryFlag ? "dbo.QualityAssurance_InputMaster_History" : "dbo.QualityAssurance_InputMaster");
var query = DataContext.Database.SqlQuery <QAInputDetailVM>(sql1).ToList();
result = query.ToList();
}
catch (Exception ex)
{
log.Error(ex);
}
return(result);
}
public List <QAInputDetailVM> QueryQAInputDetail(QADetailSearch searchData)
{
List <QAInputDetailVM> result = new List <QAInputDetailVM>();
try
{
if (searchData.QAMaster_UID == 0 && ("白班小计,夜班小计,全天".Contains(searchData.Time_interval)))
{
return(QueryInputDetailForDaySum(searchData));
}
bool searchHistoryFlag = false;
TimeSpan t = DateTime.Now - Convert.ToDateTime(searchData.ProductDate);
if (t.Days >= 7)
{
searchHistoryFlag = true;
}
if (!string.IsNullOrEmpty(searchData.TypeCode) || !string.IsNullOrEmpty(searchData.TypeName) || !string.IsNullOrEmpty(searchData.ShortName))
{
return(QueryQAInputDetailVM(searchData, searchHistoryFlag));
}
string sql = string.Empty;
#region
sql = string.Format(@"
SELECT ExType.TypeName AS ExceptionTypeName ,
ExType.BadTypeCode as BadTypeCode,
ExType.BadTypeEnglishCode as BadTypeEnglishCode,
detail.QualityAssurance_InputDetail_UID ,
detail.QualityAssurance_InputMaster_UID ,
detail.ExceptionType_UID ,
detail.RepairNG_Qty ,
detail.NG_Qty ,
detail.SepcialAccept_Qty ,
detail.Repair_Qty,
detail.Displace_Qty
INTO #temp
FROM dbo.{3} detail WITH ( NOLOCK )
INNER JOIN dbo.QualityAssurance_ExceptionType Extype WITH ( NOLOCK ) ON Extype.ExceptionType_UID = detail.ExceptionType_UID
INNER JOIN dbo.{4} qaMa WITH ( NOLOCK ) ON qaMa.QualityAssurance_InputMaster_UID = detail.QualityAssurance_InputMaster_UID
WHERE qaMa.QualityAssurance_InputMaster_UID = {0}
AND IsDeleted = 0
IF EXISTS (SELECT TOP 1 1 FROM #temp where Repair_Qty IS not NULL AND SepcialAccept_Qty IS not NULL AND
RepairNG_Qty IS not NULL AND NG_Qty IS not NULL )
BEGIN
SELECT ExceptionTypeName ,
BadTypeCode,
BadTypeEnglishCode,
QualityAssurance_InputDetail_UID ,
QualityAssurance_InputMaster_UID ,
ExceptionType_UID ,
RepairNG_Qty ,
NG_Qty ,
SepcialAccept_Qty ,
Repair_Qty,
Displace_Qty
FROM #temp
ORDER BY ExceptionType_UID
END
ElSE
BEGIN
SELECT ExceptionTypeName ,
BadTypeCode,
BadTypeEnglishCode,
QualityAssurance_InputDetail_UID ,
QualityAssurance_InputMaster_UID ,
ExceptionType_UID ,
RepairNG_Qty ,
NG_Qty ,
SepcialAccept_Qty ,
Repair_Qty,
Displace_Qty
FROM #temp
UNION
SELECT EType.TypeName AS ExceptionTypeName ,
EType.BadTypeCode as BadTypeCode,
EType.BadTypeEnglishCode as BadTypeEnglishCode,
0 AS QualityAssurance_InputDetail_UID ,
{0} AS QualityAssurance_InputMaster_UID ,
EType.ExceptionType_UID ,
null AS RepairNG_Qty ,
null AS NG_Qty ,
null AS SepcialAccept_Qty ,
null AS Repair_Qty,
null AS Displace_Qty
FROM dbo.ExceptionTypeWithFlowchart EWF WITH ( NOLOCK )
INNER JOIN dbo.QualityAssurance_ExceptionType EType WITH ( NOLOCK ) ON EType.ExceptionType_UID = EWF.ExceptionType_UID
WHERE EWF.FlowChart_Detail_UID = {1}
AND EWF.FlowChart_Master_UID = {2}
AND EType.FlowChart_Master_UID={2}
AND ewf.ExceptionType_UID NOT IN ( SELECT ExceptionType_UID
FROM #temp )
END
DROP TABLE #temp", searchData.QAMaster_UID, searchData.FlowChart_Detail_UID, searchData.FlowChart_Master_UID, searchHistoryFlag ? "QualityAssurance_InputDetail_History" : "QualityAssurance_InputDetail",
searchHistoryFlag ? "QualityAssurance_InputMaster_History" : "QualityAssurance_InputMaster");
#endregion
var query = DataContext.Database.SqlQuery <QAInputDetailVM>(sql).ToList();
result = query.ToList();
}
catch (Exception ex)
{
log.Error(ex);
}
return(result);
}
public List <QAInputDetailVM> QueryInputDetailForDaySum(QADetailSearch searchData)
{
List <QAInputDetailVM> result = new List <QAInputDetailVM>();
try
{
string time_interval = string.Empty;
if (searchData.Time_interval.Contains("白班小计"))
{
time_interval = "Daily_Sum";
}
else if (searchData.Time_interval.Contains("夜班小计"))
{
time_interval = "Night_Sum";
}
else if (searchData.Time_interval.Contains("全天"))
{
time_interval = "ALL";
}
string sqlWhere = "";
if (!string.IsNullOrEmpty(searchData.ShortName))
{
sqlWhere = sqlWhere + string.Format(@" AND QAType.ShortName=N'{0}'", searchData.ShortName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeName))
{
sqlWhere = sqlWhere + string.Format(@" AND QAType.TypeName=N'{0}'", searchData.TypeName.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.TypeCode))
{
sqlWhere = sqlWhere + string.Format(@" AND QAType.Org_TypeCode='{0}'", searchData.TypeCode.Replace("'", "''"));
}
if (!string.IsNullOrEmpty(searchData.Project))
{
sqlWhere = sqlWhere + string.Format(@" AND QAType.FlowChart_Master_UID={0}", searchData.FlowChart_Master_UID);
}
bool searchHistoryFlag = false;
TimeSpan t = DateTime.Now - Convert.ToDateTime(searchData.ProductDate);
if (t.Days >= 7)
{
searchHistoryFlag = true;
}
string sql = string.Format(@"DECLARE @ProductDate Date,
@FlowChart_Detail_UID INT,
@SumType NVARCHAR(50),
@MaterialType NVARCHAR(50),
@Color NVARCHAR(50)
SET @ProductDate = N'{0}'
SET @FlowChart_Detail_UID = {1}
SET @SumType = N'{2}'
SET @MaterialType = N'{3}'
SET @Color = N'{4}'
DECLARE @TempQAMasterSum TABLE(
QualityAssurance_InputMaster_UID INT,
FlowChart_Detail_UID int,
Process NVARCHAR(50),
color NVARCHAR(50),
Time_Interval NVARCHAR(20),
Product_Date DATE,
Input INT,
FirstCheck_Qty INT,
FirstOK_Qty INT,
FirstRejectionRate DECIMAL,
NG_Qty INT,
SurfaceSA_Qty INT,
SizeSA_Qty INT,
RepairCheck_Qty INT,
RepairOK_Qty INT,
Shipment_Qty INT,
WIPForCheck_Qty INT,
NGFlag BIT,
FirstCheckFlag BIT,
Displace_Qty INT,
DisplaceFlag BIT)
INSERT INTO @TempQAMasterSum(
QualityAssurance_InputMaster_UID,
FlowChart_Detail_UID,
Process ,
color,
Time_Interval,
Product_Date ,
Input ,
FirstCheck_Qty ,
FirstOK_Qty ,
FirstRejectionRate ,
NG_Qty ,
SurfaceSA_Qty ,
SizeSA_Qty ,
RepairCheck_Qty ,
RepairOK_Qty ,
Shipment_Qty ,
WIPForCheck_Qty ,
NGFlag,
FirstCheckFlag,
Displace_Qty,
DisplaceFlag
)
EXEC usp_GetQAMasterDaySum @ProductDate,@FlowChart_Detail_UID,@SumType,@MaterialType,@Color
SELECT QAType.TypeName as ExceptionTypeName, QAType.BadTypeCode as BadTypeCode, QAType.BadTypeEnglishCode as BadTypeEnglishCode,QAType.ExceptionType_UID,0 as QualityAssurance_InputDetail_UID,0 AS QualityAssurance_InputMaster_UID ,SUM(QADetail.NG_Qty) AS NG_Qty,SUM(QADetail.RepairNG_Qty) AS RepairNG_Qty,SUM(QADetail.Repair_Qty) AS Repair_Qty,
SUM(QADetail.SepcialAccept_Qty) AS SepcialAccept_Qty,SUM(QADetail.Displace_Qty) AS Displace_Qty FROM @TempQAMasterSum QAMaster
INNER JOIN dbo.{5} QADetail ON QADetail.QualityAssurance_InputMaster_UID = QAMaster.QualityAssurance_InputMaster_UID
INNER JOIN dbo.QualityAssurance_ExceptionType QAType ON QAType.ExceptionType_UID = QADetail.ExceptionType_UID
WHERE QADetail.IsDeleted=0 {6}
GROUP BY QAType.TypeName,QAType.ExceptionType_UID , QAType.BadTypeCode,QAType.BadTypeEnglishCode
" , searchData.ProductDate, searchData.FlowChart_Detail_UID, time_interval, searchData.MaterialType, searchData.Color, searchHistoryFlag ? "QualityAssurance_InputDetail_History" : "QualityAssurance_InputDetail", sqlWhere);
var query = DataContext.Database.SqlQuery <QAInputDetailVM>(sql).ToList();
result = query.ToList();
}
catch (Exception ex)
{
log.Error(ex);
}
return(result);
}
