public OQC_InputMasterDTO QueryOQCRecordData(QAReportSearchVM searchModel)
{
OQC_InputMasterDTO result = new OQC_InputMasterDTO();
#region
int uid = 0;
if (searchModel.FlowChart_Detail_UID != 0)
{
uid = searchModel.FlowChart_Detail_UID;
}
else
{
uid = GetDetailUID(searchModel.ProjectName, searchModel.Process, searchModel.ProductDate);
}
if (searchModel.Tab_Select_Text == "Night_Sum" || searchModel.Tab_Select_Text == "Daily_Sum" || searchModel.Tab_Select_Text == "ALL")
{
try
{
bool searchHistory = false;
TimeSpan tSpan = DateTime.Now.Date - searchModel.ProductDate;
if (tSpan.Days >= 7)
{
searchHistory = true;
}
string sql = string.Format(@"
DECLARE @SumReworkQty INT ,
@SumNGQTy INT ,
@DailyEndTimeIndex INT ,
@NightEndTimeIndex INT ,
@StartIndex INT ,
@EndIndex INT ,
@TimeType VARCHAR(50)
SET @TimeType = N'{6}'
SELECT @DailyEndTimeIndex = CONVERT(INT, EN.Enum_Name)
FROM dbo.Enumeration EN WITH ( NOLOCK )
INNER JOIN dbo.Enumeration E WITH ( NOLOCK ) ON E.Enum_Value = EN.Enum_Value
WHERE En.Enum_Type = 'Time_InterVal_OP1'
AND E.Enum_Type = 'Sum_TimeInterval'
AND E.Enum_Name = 'Daily_Sum'
SELECT @NightEndTimeIndex = CONVERT(INT, EN.Enum_Name)
FROM dbo.Enumeration EN WITH ( NOLOCK )
INNER JOIN dbo.Enumeration E WITH ( NOLOCK ) ON E.Enum_Value = EN.Enum_Value
WHERE En.Enum_Type = 'Time_InterVal_OP1'
AND E.Enum_Type = 'Sum_TimeInterval'
AND E.Enum_Name = 'Night_Sum'
IF @TimeType = N'ALL'
BEGIN
SET @StartIndex = 1
SET @EndIndex = @NightEndTimeIndex
END
ELSE
IF @TimeType = N'Daily_Sum'
BEGIN
SET @StartIndex = 1
SET @EndIndex = @DailyEndTimeIndex
END
ELSE
IF @TimeType = N'Night_Sum'
BEGIN
SET @StartIndex = @DailyEndTimeIndex + 1
SET @EndIndex = @NightEndTimeIndex
END
SELECT @SumReworkQty = SUM(QaDetails.Qty)
FROM dbo.{5} QaDetails WITH ( NOLOCK )
INNER JOIN dbo.{0} OQCMa WITH ( NOLOCK ) ON OQCMa.OQCMater_UID = QaDetails.OQCMater_UID
INNER JOIN dbo.Enumeration en ON OQCMa.Time_Interval = en.Enum_Value
WHERE FlowChart_Detail_UID = {1}
AND QaDetails.TypeClassify = N'返修明细'
AND OQCMa.Color = N'{2}'
AND OQCMa.MaterialType = N'{3}'
AND ProductDate = N'{4}'
AND en.Enum_Type = N'Time_InterVal_OP1'
AND CONVERT(INT, Enum_Name) <= @EndIndex
AND CONVERT(INT, Enum_Name) >= @StartIndex
SELECT @SumNGQTy = SUM(QaDetails.Qty)
FROM dbo.{5} QaDetails WITH ( NOLOCK )
INNER JOIN dbo.{0} OQCMa WITH ( NOLOCK ) ON OQCMa.OQCMater_UID = QaDetails.OQCMater_UID
INNER JOIN dbo.Enumeration en ON OQCMa.Time_Interval = en.Enum_Value
WHERE FlowChart_Detail_UID = {1}
AND QaDetails.TypeClassify = N'不良明细'
AND OQCMa.Color = N'{2}'
AND OQCMa.MaterialType = N'{3}'
AND ProductDate = N'{4}'
AND en.Enum_Type = N'Time_InterVal_OP1'
AND CONVERT(INT, Enum_Name) <= @EndIndex
AND CONVERT(INT, Enum_Name) >= @StartIndex
SELECT OQCMa.FlowChart_Detail_UID ,
OQCMa.MaterialType ,
OQCMa.Color ,
OQCMa.Project_UID ,
SUM(OQCMa.Input) AS Input ,
SUM(OQCMa.GoodParts_Qty) AS GoodParts_Qty ,
SUM(OQCMa.NGParts_Qty) AS NGParts_Qty ,
SUM(OQCMa.Rework) AS Rework ,
SUM(OQCMa.ReworkQtyFromOQC) AS ReworkQtyFromOQC,
SUM(OQCMa.ProductLineRework) AS ProductLineRework ,
SUM(OQCMa.ReworkQtyFromAssemble) AS ReworkQtyFromAssemble ,
SUM(OQCMa.RepairNG_Qty) AS RepairNG_Qty ,
SUM(OQCMa.NG_Qty) AS NG_Qty ,
@SumReworkQty * 1.00 / ( SUM(OQCMa.GoodParts_Qty)
+ SUM(OQCMa.NGParts_Qty)
+ SUM(OQCMa.Rework)
+ SUM(OQCMa.ProductLineRework) ) * 1.00 AS RepairNG_Yield ,
@SumNGQTy * 1.00 / ( SUM(OQCMa.GoodParts_Qty)
+ SUM(OQCMa.NGParts_Qty) ) * 1.00 AS NG_Yield ,
CASE WHEN ( SUM(OQCMa.GoodParts_Qty) + SUM(OQCMa.NGParts_Qty)
+ SUM(OQCMa.Rework) - SUM(OQCMa.ReworkQtyFromOQC) ) > 0
THEN (SUM(OQCMa.GoodParts_Qty)-SUM(OQCMa.ReworkQtyFromOQC)) * 1.00
/ ( SUM(OQCMa.GoodParts_Qty) + SUM(OQCMa.NGParts_Qty)
+ SUM(OQCMa.Rework) - SUM(OQCMa.ReworkQtyFromOQC) )
* 1.00
ELSE 0
END AS FirstYieldRate ,
CASE WHEN ( SUM(OQCMa.GoodParts_Qty) + SUM(OQCMa.NGParts_Qty) ) > 0
THEN SUM(OQCMa.GoodParts_Qty) * 1.00
/ ( SUM(OQCMa.GoodParts_Qty) + SUM(OQCMa.NGParts_Qty) )
* 1.00
ELSE 0
END AS SecondYieldRate ,
SUM(OQCMa.Storage_Qty) AS Storage_Qty ,
SUM(OQCMa.Storage_Qty) AS Storage_Qty ,
SUM(OQCMa.WaitStorage_Qty) AS WaitStorage_Qty ,
0 AS WIP
FROM dbo.{0} OQCMa WITH ( NOLOCK )
INNER JOIN dbo.Enumeration EN WITH ( NOLOCK ) ON OQCMa.Time_Interval = en.Enum_Value
WHERE FlowChart_Detail_UID = {1}
AND OQCMa.Color = N'{2}'
AND OQCMa.MaterialType = N'{3}'
AND ProductDate = N'{4}'
AND en.Enum_Type = N'Time_InterVal_OP1'
AND CONVERT(INT, Enum_Name) <= @EndIndex
AND CONVERT(INT, Enum_Name) >= @StartIndex
GROUP BY OQCMa.FlowChart_Detail_UID ,
OQCMa.MaterialType ,
OQCMa.Project_UID ,
OQCMa.Color
", !searchHistory ? "OQC_InputMaster" : "OQC_InputMaster_History", uid, searchModel.Color, searchModel.MaterialType, searchModel.ProductDate.ToShortDateString(),
!searchHistory ? "OQC_InputDetail" : "OQC_InputDetail_History", searchModel.Tab_Select_Text);
var query = DataContext.Database.SqlQuery <OQC_InputMasterDTO>(sql).ToList();
if (query.Count == 0)
{
return(result);
}
else
{
result = query[0];
}
}
catch (Exception ex)
{
log.Error(ex);
}
}
else
{
try
{
bool searchHistory = false;
TimeSpan tSpan = DateTime.Now.Date - searchModel.ProductDate;
if (tSpan.Days >= 7)
{
searchHistory = true;
}
string sql = string.Format(@"
SELECT OQCMa.*
FROM dbo.{0} OQCMa WITH ( NOLOCK )
WHERE FlowChart_Detail_UID = {1}
AND OQCMa.Color = N'{2}'
AND OQCMa.MaterialType = N'{3}'
AND ProductDate = N'{4}'
AND Time_interval = N'{5}'
", !searchHistory ? "OQC_InputMaster" : "OQC_InputMaster_History", uid, searchModel.Color, searchModel.MaterialType, searchModel.ProductDate.ToShortDateString(), searchModel.Tab_Select_Text);
var query = DataContext.Database.SqlQuery <OQC_InputMasterDTO>(sql).ToList();
if (query.Count == 0)
{
return(result);
}
else
{
result = query[0];
}
}
catch (Exception ex)
{
log.Error(ex);
}
}
#endregion
return(result);
}
public OQC_InputMasterDTO QueryOQCMasterData(QAReportSearchVM searchModel)
{
OQC_InputMasterDTO result = new OQC_InputMasterDTO();
try
{
bool searchHistory = false;
TimeSpan tSpan = DateTime.Now.Date - searchModel.ProductDate;
if (tSpan.Days >= 7)
{
searchHistory = true;
}
string sql = string.Format(@"
SELECT OQCMa.*
FROM dbo.{0} OQCMa WITH ( NOLOCK )
INNER JOIN dbo.System_Project SP WITH ( NOLOCK ) ON SP.Project_UID = OQCMa.Project_UID
WHERE FlowChart_Detail_UID = {1}
AND SP.Project_Name = N'{2}'
AND OQCMa.Color = N'{3}'
AND OQCMa.MaterialType = N'{4}'
AND ProductDate = N'{5}'
AND Time_interval = N'{6}'
", !searchHistory ? "OQC_InputMaster" : "OQC_InputMaster_History", searchModel.FlowChart_Detail_UID,
searchModel.ProjectName, searchModel.Color, searchModel.MaterialType, searchModel.ProductDate.ToShortDateString(), searchModel.Time_interval);
var query = DataContext.Database.SqlQuery <OQC_InputMasterDTO>(sql).ToList();
if (query.Count == 0)
{
var query1 = from QAinterface in DataContext.PPForQAInterface
join flowDetails in DataContext.FlowChart_Detail on QAinterface.FlowChart_Detail_UID equals flowDetails.FlowChart_Detail_UID
join flowMaster in DataContext.FlowChart_Master on flowDetails.FlowChart_Master_UID equals flowMaster.FlowChart_Master_UID
where QAinterface.Color == searchModel.Color &&
QAinterface.Product_Date == searchModel.ProductDate &&
QAinterface.Time_Interval == searchModel.Time_interval && QAinterface.MaterielType == searchModel.MaterialType &&
QAinterface.QAUsedFlag == false && QAinterface.FlowChart_Detail_UID == searchModel.FlowChart_Detail_UID &&
flowDetails.FlowChart_Version == flowMaster.FlowChart_Version
select new OQC_InputMasterDTO
{
Input = QAinterface.Input_Qty,
GoodParts_Qty = QAinterface.Good_Qty,
NGParts_Qty = QAinterface.NG_Qty,
Rework = QAinterface.ReWorkQty,
ProductDate = searchModel.ProductDate,
Time_interval = searchModel.Time_interval,
FlowChart_Detail_UID = searchModel.FlowChart_Detail_UID,
Color = searchModel.Color,
MaterialType = searchModel.MaterialType,
Project_UID = flowMaster.Project_UID
};
if (query1.Count() > 0)
{
result = query1.ToList()[0];
}
else
{
var query2 = from flowDetails in DataContext.FlowChart_Detail
join flowMaster in DataContext.FlowChart_Master on flowDetails.FlowChart_Master_UID equals flowMaster.FlowChart_Master_UID
where flowDetails.FlowChart_Detail_UID == searchModel.FlowChart_Detail_UID &&
flowDetails.FlowChart_Version == flowMaster.FlowChart_Version
select new OQC_InputMasterDTO
{
ProductDate = searchModel.ProductDate,
Time_interval = searchModel.Time_interval,
FlowChart_Detail_UID = searchModel.FlowChart_Detail_UID,
Color = searchModel.Color,
MaterialType = searchModel.MaterialType,
Project_UID = flowMaster.Project_UID
};
if (query2.Count() > 0)
{
result = query2.ToList()[0];
}
}
}
else
{
result = query[0];
}
}
catch (Exception ex)
{
log.Error(ex);
}
return(result);
}
