/// <summary>
/// 刪除TPEM數據SQL
/// </summary>
/// <param name="oFilter"></param>
/// <returns></returns>
public string DeleteTPEMDataSQL(TransitionFilter oFilter)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSqlNrst = new StringBuilder();
StringBuilder strSqlNpst = new StringBuilder();
StringBuilder strSqlJobt = new StringBuilder();
StringBuilder strSqlOee1 = new StringBuilder();
strSqlNrst.AppendLine("Delete From TPEMNRST Where 1=1 ");
strSqlNpst.AppendLine("Delete From TPEMNPST Where 1=1 ");
strSqlJobt.AppendLine("Delete From TPEMJOBT Where 1=1 ");
strSqlOee1.AppendLine("Delete From TPEMOEE1 Where 1=1 ");
if (oFilter.dDate != DateTime.MinValue)
{
strSqlNrst.AppendLine("And nrst_dDate=Convert(DateTime,'" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "',120)");
strSqlNpst.AppendLine("And NPST_dDate=Convert(DateTime,'" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "',120)");
strSqlJobt.AppendLine("And Jobt_dDate=Convert(DateTime,'" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "',120)");
strSqlOee1.AppendLine("And OEE1_DDATE=Convert(DateTime,'" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "',120)");
}
if (oFilter.cShift != string.Empty)
{
strSqlNrst.AppendLine("And NRST_fTime=N'" + oFilter.cShift + "'");
strSqlNpst.AppendLine("And NPST_fTime=N'" + oFilter.cShift + "'");
strSqlJobt.AppendLine("And Jobt_fTime=N'" + oFilter.cShift + "'");
strSqlOee1.AppendLine("And OEE1_FTIME=N'" + oFilter.cShift + "'");
}
//if (oFilter.cMachine != ConstantsVar.StringNull)
//{
// strSqlNrst.AppendLine("And NRST_cMNum=N'" + oFilter.cMachine + "'");
// strSqlNpst.AppendLine("And NPST_CMNum=N'" + oFilter.cMachine + "'");
// strSqlJobt.AppendLine("And Jobt_cMNum=N'" + oFilter.cMachine + "'");
// strSqlOee1.AppendLine("And OEE1_CMNUM=N'" + oFilter.cMachine + "'");
//}
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSqlNrst.AppendLine("And NRST_cMNum In(" + oFilter.cMachineFilter + ")");
strSqlNpst.AppendLine("And NPST_CMNum In(" + oFilter.cMachineFilter + ")");
strSqlJobt.AppendLine("And Jobt_cMNum In(" + oFilter.cMachineFilter + ")");
strSqlOee1.AppendLine("And OEE1_CMNUM In(" + oFilter.cMachineFilter + ")");
}
strSql.AppendLine(strSqlNrst.ToString().Trim());
strSql.AppendLine(strSqlNpst.ToString().Trim());
strSql.AppendLine(strSqlJobt.ToString().Trim());
strSql.AppendLine(strSqlOee1.ToString().Trim());
return strSql.ToString().Trim();
}
/// <summary>
/// 填充條件
/// </summary>
/// <param name="cGroup"></param>
/// <param name="dDate"></param>
/// <param name="cShift"></param>
/// <param name="cUnit"></param>
/// <param name="cArea"></param>
/// <param name="cMachine"></param>
/// <returns></returns>
TransitionFilter FillFilter(string cGroupCD, DateTime dDate, string cShift, string cUnit, string cArea, string cMachine)
{
TransitionFilter oReturn = new TransitionFilter();
switch (cGroupCD)
{
case "LAMIMS":
oReturn.cGroup = "過膠科";
break;
case "SILKIMS":
oReturn.cGroup = "絲印科";
break;
case "HOTIMS":
oReturn.cGroup = "燙金科";
break;
}
if (cGroupCD != string.Empty)
oReturn.cGroupCD = cGroupCD;
if (dDate != DateTime.MinValue)
oReturn.dDate = DateTime.Parse(dDate.ToString("yyyy-MM-dd"));
if (cShift != string.Empty)
oReturn.cShift = cShift;
if (cUnit != string.Empty)
oReturn.cUnit = cUnit;
if (cArea != string.Empty)
oReturn.cArea = cArea;
if (cMachine != string.Empty)
oReturn.cMachine = cMachine;
oReturn.cMachineList = _TPEMTransitionDA.GetMachineCode(oReturn);
oReturn.cMachineFilter = ConversitionMachineCode(oReturn.cMachineList);
return oReturn;
}
//, decimal iSpeed1, decimal iSpeed2)
/// <summary>
/// 獲取報表數據SQL
/// </summary>
/// <param name="oFilter"></param>
///// <param name="iSpeed1"></param>
///// <param name="iSpeed2"></param>
/// <returns></returns>
string GetReportSql(TransitionFilter oFilter)
{
StringBuilder strSql = new StringBuilder();
#region 舊心電圖數據轉換SQL
//string cShift = string.Empty;
//if (oFilter.cShift != string.Empty)
// cShift = oFilter.cShift == "1" ? "日班" : "夜班";
//strSql.AppendLine("--主記錄臨時表");
//strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpMain') IS NOT NULL ");
//strSql.AppendLine("Begin");
//strSql.AppendLine("Drop Table #TmpMain");
//strSql.AppendLine("End");
//strSql.AppendLine("Create Table #TmpMain(iRowID int,");
//strSql.AppendLine("cUnit Nvarchar(100),cGroup Nvarchar(100),dDate datetime,cShift Nvarchar(20),iShift int,cMachineCode varchar(50),");
//strSql.AppendLine("cProject Nvarchar(100),cProduct Nvarchar(100),cSONO Nvarchar(100),cProjection Nvarchar(100),dProjectStart datetime,dProjectCover datetime,");
//strSql.AppendLine("dprojectEnd datetime,iRealQty int,iDefectiveQty int,iWasteQty int ,iOperationTime int,iMealTime int,iDownTime int,");
//strSql.AppendLine("iCoversionTime int,iIdealSpeed float,iIdealSpeed1 int,iIdealSpeed2 int,");
//strSql.AppendLine("iUnit int,cUnitCD varchar(50),cArea Nvarchar(100),dAddDate datetime,cAdd varchar(50),");
//strSql.AppendLine("dLastDate datetime,cLast varchar(50),ID int,dShiftBegin datetime,dShiftEnd datetime,iShiftOperation int,iShiftMeal int, iShiftDown int,iShiftCoversion int,lIsProject bit)");
//strSql.AppendLine("--主記錄匯總臨時表");
//strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpSumMain') IS NOT NULL ");
//strSql.AppendLine("Begin");
//strSql.AppendLine("Drop Table #TmpSumMain");
//strSql.AppendLine("End");
//strSql.AppendLine("Create Table #TmpSumMain(");
//strSql.AppendLine("dDate datetime,cShift Nvarchar(100),cMachineCode Nvarchar(100),iTotalOperation int,iTotalMeal int,iTotalDown int,iTotlaCoversion int,");
//strSql.AppendLine("iIdealSpeed1 int,iIdealSpeed2 int, lIsExist bit)");
//strSql.AppendLine("--*************************************************");
//strSql.AppendLine("--停機記錄臨時表");
//strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpDown') IS NOT NULL ");
//strSql.AppendLine("Begin");
//strSql.AppendLine("Drop Table #TmpDown");
//strSql.AppendLine("End");
//strSql.AppendLine("Create Table #TmpDown(iRowID int,");
//strSql.AppendLine("iShiftID int,cShift Nvarchar(20),cMachineCode varchar(20),dDate datetime,dBeginTime datetime,");
//strSql.AppendLine("dEndTime datetime,iTime int,cStopType varchar(20),cStopCode varchar(20),ID int,iActualTime int,iTime1 int,iSeq int)");
//strSql.AppendLine("--*************************************************");
//strSql.AppendLine("--就餐記錄臨時表");
//strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpMeal') IS NOT NULL ");
//strSql.AppendLine("Begin");
//strSql.AppendLine("Drop Table #TmpMeal");
//strSql.AppendLine("End");
//strSql.AppendLine("Create Table #TmpMeal(iRowID int,");
//strSql.AppendLine("iShiftID int,cShift Nvarchar(20),cMachineCode varchar(20),dDate datetime,dBeginTime datetime,");
//strSql.AppendLine("dEndTime datetime,iTime int)");
//strSql.AppendLine("--就餐記錄匯總臨時表");
//strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpSumMeal') IS NOT NULL ");
//strSql.AppendLine("Begin");
//strSql.AppendLine("Drop Table #TmpSumMeal");
//strSql.AppendLine("End");
//strSql.AppendLine("Create Table #TmpSumMeal(iRowID int,");
//strSql.AppendLine("iShiftID int,cShift Nvarchar(20),cMachineCode varchar(20),dDate datetime,iSumTime int)");
//strSql.AppendLine("--*************************************************");
//strSql.AppendLine("insert into #TmpMain");
//strSql.AppendLine("select ROW_NUMBER() over(order by iShift) As RowID,* from (");
//strSql.AppendLine("/*空閒狀態*/");
//strSql.AppendLine("Select dcma_cDescription As cUnit,'" + oFilter.cGroup + "' As cGroup");
//strSql.AppendLine(",shft_dProductionDate As dDate,shft_cShiftName As cShift,isrd_iShiftID As iShift");
//strSql.AppendLine(",dcmp_cMachineKey As cMachineCode,istp_nStateName As cProject,istp_nStateName As cProduct,istp_nStateName As cSONO,istp_nStateName As cProjection");
//strSql.AppendLine(",isrd_dStateBeginTime As dProjectStart,getdate() As dProjectCover, isrd_dStateEndTime As dProjectEnd");
//strSql.AppendLine(",0 As iRealQty,0 As iDefectiveQty,0 As iWasteQty");
//strSql.AppendLine(",Case When datediff(minute,isrd_dStateBeginTime,isrd_dStateEndTime)>0 Then Datediff(minute,isrd_dStateBeginTime,isrd_dStateEndTime) Else 0 End As iOperationTime");
//strSql.AppendLine(",0 As iMealTime_A,Case When datediff(minute,isrd_dStateBeginTime,isrd_dStateEndTime)>0 Then Datediff(minute,isrd_dStateBeginTime,isrd_dStateEndTime) Else 0 End As iDownTime_A");
//strSql.AppendLine(",0 As iCoversionTime,0 As iIdealSpeed,0 As iIdealSpeed1,0 As iIdealSpeed2");
//strSql.AppendLine(",dcma_iDCMAID As cUnitID,dcma_cAreaCode As cUnitCD,dcma_cAreaName As cArea,Getdate() As dAddDate,'system' As cAdd,Getdate() As dLastDate, 'system' As cLast");
//strSql.AppendLine(",isrd_iISRDID,shft_dBeginTime,shft_dEndTime");
//strSql.AppendLine(",datediff(minute,shft_dBeginTime,shft_dEndTime) As iShiftOperation,0 As iShiftMeal,0 As iShiftDown,0 As iShiftCoversion,0 As lIsProject");
//strSql.AppendLine("From IdleStateRecord_isrd");
//strSql.AppendLine("Inner Join IdleStateProfile_istp On isrd_iISTPID=istp_iISTPID And isrd_lIsDeleted=0 And istp_lIsDeleted=0");
//strSql.AppendLine("Inner Join DCMProfile_dcmp On isrd_iMachineID=dcmp_iDCMPID And dcmp_lIsDeleted=0");
//strSql.AppendLine("Inner Join DCMAreaProfile_dcma On dcmp_iAreaID=dcma_iDCMAID And dcma_lIsDeleted=0");
//strSql.AppendLine("Inner Join Shift_shft On shft_iSTID=isrd_iShiftID And shft_lIsDeleted=0");
//strSql.AppendLine("Where 1=1 ");
//if (oFilter.dDate != DateTime.MinValue)
// strSql.AppendLine("And shft_dProductionDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
//if (cShift != string.Empty)
// strSql.AppendLine("And shft_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And dcma_cDescription='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
//if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
//{
// strSql.AppendLine("And dcmp_cMachineKey In(" + oFilter.cMachineFilter + ")");
//}
//strSql.AppendLine("union all");
//strSql.AppendLine("Select dcma_cDescription As cUnit,'" + oFilter.cGroup + "' As cGroup, shft_dProductionDate As dDate");
//strSql.AppendLine(",shft_cShiftName As cShift,shft_iSTID As iShift, dcmp_cMachineKey As cMachineCode");
//strSql.AppendLine(",T1.cProject,T1.cProduct,T1.cSONO,T1.cProjection");
//strSql.AppendLine(",pjsf_dBeginTime as dProjectStart,pjsf_dProductBeginTime As dProjectCover, pjsf_dEndTime as dProjectEnd");
//strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
////strSql.AppendLine("pjsf_iAccountEligibleQty*oppj_iUnitLength/100 ");
//strSql.AppendLine("round((cast((pjsf_iAccountEligibleQty*oppj_iUnitLength) as float)/1000)+0.5,0)");
//strSql.AppendLine("else");
//strSql.AppendLine("pjsf_iAccountEligibleQty ");
//strSql.AppendLine("end As iRealQty");
//strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
////strSql.AppendLine("pjsf_iAccountDefectiveQty*oppj_iUnitLength/100 ");
//strSql.AppendLine("round((cast((pjsf_iAccountDefectiveQty*oppj_iUnitLength) as float)/1000)+0.5,0)");
//strSql.AppendLine("else");
//strSql.AppendLine("pjsf_iAccountDefectiveQty");
//strSql.AppendLine("end As iDefectiveQty ");
//strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
////strSql.AppendLine("pjsf_iAccountWasteQty*oppj_iUnitLength/100 ");
//strSql.AppendLine("round((cast((pjsf_iAccountWasteQty*oppj_iUnitLength) as float)/1000)+0.5,0)");
//strSql.AppendLine("else");
//strSql.AppendLine("pjsf_iAccountWasteQty");
//strSql.AppendLine("end As iWasteQty ");
//strSql.AppendLine(",case when datediff(minute,pjsf_dBeginTime,pjsf_dEndTime)>0 then");
//strSql.AppendLine("datediff(minute,pjsf_dBeginTime,pjsf_dEndTime) ");
//strSql.AppendLine("else");
//strSql.AppendLine("0 end As iOperationTime");
//strSql.AppendLine(",0 As iMealTime_A,0 As iDownTime_A,datediff(minute,pjsf_dBeginTime,pjsf_dProductBeginTime) As iCoversionTime");
//strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
//strSql.AppendLine("case when datediff(minute,pjsf_dBeginTime,pjsf_dEndTime)>0 then");
//strSql.AppendLine("round(cast((pjsf_iAccountEligibleQty*oppj_iUnitLength/100) as float)/datediff(minute,pjsf_dBeginTime,pjsf_dEndTime),2)");
//strSql.AppendLine("else 0 end");
//strSql.AppendLine("else");
//strSql.AppendLine("case when datediff(minute,pjsf_dBeginTime,pjsf_dEndTime)>0 then");
//strSql.AppendLine("round(cast( pjsf_iAccountEligibleQty as float)/datediff(minute,pjsf_dBeginTime,pjsf_dEndTime),2)");
//strSql.AppendLine("else 0 end");
//strSql.AppendLine("end As iIdealSpeed");
//strSql.AppendLine(",0 As iIdealSpeed1,0 As iIdealSpeed2");
//strSql.AppendLine(",dcma_iDCMAID As cUnitID,dcma_cAreaCode As cUnitCD,dcma_cAreaName As cArea,Getdate() As dAddDate,'system' As cAdd,Getdate() As dLastDate, 'system' As cLast");
//strSql.AppendLine(",pjsf_iPJSFID,shft_dBeginTime,shft_dEndTime");
//strSql.AppendLine(",datediff(minute,shft_dBeginTime,shft_dEndTime) As iShiftOperation,0 As iShiftMeal,0 As iShiftDown,0 As iShiftCoversion,1 As lIsProject");
//strSql.AppendLine("from ProjectInShift_pjsf With(Nolock)");
//strSql.AppendLine("inner join OPProject_oppj With(Nolock) on pjsf_iOPPJID=oppj_iOPPJID And oppj_iProjectStatus In(4,5)");
//strSql.AppendLine("inner join Shift_shft With(Nolock) on pjsf_iSTID=shft_iSTID and shft_lIsDeleted=0");
//strSql.AppendLine("inner join (");
//strSql.AppendLine("select dcxm_iPPCTaskID As TaskID,dcxm_cSONO As cProject,dcxm_cProgramDesc As cProduct,dcxm_cDCSheetNo As cSONO");
//strSql.AppendLine(",dcxm_cProductDesc As cProjection");
//strSql.AppendLine("from DCXScheduleMiddle_dcxm With(Nolock)");
//strSql.AppendLine("Union");
//strSql.AppendLine("select dcxe_iPPCTaskID As TaskID,dcxa_cSONO As cProject,dcxa_cProgramDesc As cProduct,dcxa_cDCSheetNo As cSONO");
//strSql.AppendLine(",dcxa_cProductDesc As cProjection ");
//strSql.AppendLine("from dcxSchedule_DCXE With(Nolock)");
//strSql.AppendLine("inner join DCXJob_dcxd With(Nolock) on dcxe_iDCJobID=dcxd_iDCJobID");
//strSql.AppendLine("inner join DCXSheetMaster_dcxa With(Nolock) on dcxd_iDCSheetID=dcxa_iDCSheetID");
//strSql.AppendLine(") T1 on oppj_iPPCTaskID=T1.TaskID");
//strSql.AppendLine("inner join DCMProfile_dcmp With(Nolock)");
//strSql.AppendLine("on pjsf_iDCMPID=dcmp_iDCMPID and dcmp_lIsDeleted=0");
//strSql.AppendLine("inner join DCMAreaProfile_dcma With(Nolock)");
//strSql.AppendLine("on dcmp_iAreaID=dcma_iDCMAID and dcma_lIsDeleted=0");
//strSql.AppendLine("Where 1=1 ");
////if (oFilter.cGroup != ConstantsVar.StringNull)
//// strSql.AppendLine("");
//if (oFilter.dDate != DateTime.MinValue)
// strSql.AppendLine("And shft_dProductionDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
//if (cShift != string.Empty)
// strSql.AppendLine("And shft_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And dcma_cDescription='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
//if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
//{
// strSql.AppendLine("And dcmp_cMachineKey In(" + oFilter.cMachineFilter + ")");
//}
//strSql.AppendLine(") Project order by Project.cMachineCode,Project.dProjectStart");
//strSql.AppendLine("/*******************************************************************/");
//strSql.AppendLine("insert into #TmpMeal");
//strSql.AppendLine("/*就餐記錄*/");
//strSql.AppendLine("Select ROW_NUMBER() over(order by dpst_iShiftID) As RowID, dpst_iShiftID,shft_cShiftName,dcmp_cMachineKey");
//strSql.AppendLine(",shft_dProductionDate,dpst_dBeginTime,dpst_dEndTime,datediff(minute,dpst_dBeginTime,dpst_dEndTime) As iMealTime");
//strSql.AppendLine("from DinningPauseShift_dpst");
//strSql.AppendLine("Inner Join DCMProfile_dcmp On dpst_iDCMPID=dcmp_iDCMPID And dpst_iUpdateTime>0 --And dpst_lIsDeleted=0 And dcmp_lIsDeleted=0 ");
//strSql.AppendLine("Inner Join DCMAreaProfile_dcma On dcmp_iAreaID=dcma_iDCMAID And dcma_lIsDeleted=0");
//strSql.AppendLine("Inner Join Shift_shft On shft_iSTID=dpst_iShiftID And shft_lIsDeleted=0");
//strSql.AppendLine("Where 1=1 ");
////if (oFilter.cGroup != ConstantsVar.StringNull)
//// strSql.AppendLine("");
//if (oFilter.dDate != DateTime.MinValue)
// strSql.AppendLine("And shft_dProductionDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
//if (cShift != string.Empty)
// strSql.AppendLine("And shft_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And dcma_cDescription='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
//if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
//{
// strSql.AppendLine("And dcmp_cMachineKey In(" + oFilter.cMachineFilter + ")");
//}
//strSql.AppendLine("/*就餐時間匯總*/");
////--select * from #TmpMeal
//strSql.AppendLine("insert into #TmpSumMeal");
//strSql.AppendLine("select ROW_NUMBER() over(order by dpst_iShiftID) As RowID,dpst_iShiftID,shft_cShiftName,dcmp_cMachineKey,shft_dProductionDate,sum(iMealTime) As iSumMealTime from (");
//strSql.AppendLine("Select dpst_iShiftID,shft_cShiftName,dcmp_cMachineKey,shft_dProductionDate");
//strSql.AppendLine(",datediff(minute,dpst_dBeginTime,dpst_dEndTime) As iMealTime from DinningPauseShift_dpst");
//strSql.AppendLine("Inner Join DCMProfile_dcmp On dpst_iDCMPID=dcmp_iDCMPID And dpst_iUpdateTime>0 --And dpst_lIsDeleted=0 And dcmp_lIsDeleted=0 ");
//strSql.AppendLine("Inner Join DCMAreaProfile_dcma On dcmp_iAreaID=dcma_iDCMAID And dcma_lIsDeleted=0");
//strSql.AppendLine("Inner Join Shift_shft On shft_iSTID=dpst_iShiftID And shft_lIsDeleted=0");
//strSql.AppendLine("Where 1=1 ");
////if (oFilter.cGroup != ConstantsVar.StringNull)
//// strSql.AppendLine("");
//if (oFilter.dDate != DateTime.MinValue)
// strSql.AppendLine("And shft_dProductionDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
//if (cShift != string.Empty)
// strSql.AppendLine("And shft_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And dcma_cDescription='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
//if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
//{
// strSql.AppendLine("And dcmp_cMachineKey In(" + oFilter.cMachineFilter + ")");
//}
//strSql.AppendLine(") AA group by dpst_iShiftID,shft_cShiftName,dcmp_cMachineKey,shft_dProductionDate");
//strSql.AppendLine("/******************************************************************/");
//strSql.AppendLine("/*工程停機記錄*/");
//strSql.AppendLine("insert into #TmpDown");
//strSql.AppendLine("select * from (");
//strSql.AppendLine("select ROW_NUMBER() over(order by shft_iSTID) As RowID, shft_iSTID,shft_cShiftName,dcmp_cMachineKey,shft_dProductionDate");
//strSql.AppendLine(",psrd_dBeginTime,psrd_dEndTime,srpf_iPredictedTime ");
///*Modify by leoth lunlin 2013-11-01 start*/
////strSql.AppendLine(",case when srpf_cReasonCode like 'A%' then 'PLAN'");
////strSql.AppendLine("when srpf_cReasonCode like 'C%' then 'NPLAN'");
////strSql.AppendLine("when srpf_cReasonCode like 'D%' then 'NRECORD'");
//strSql.AppendLine(",case when cdnm_cName like 'A%' then 'PLAN'");
//strSql.AppendLine("when cdnm_cName like 'C%' then 'NPLAN'");
//strSql.AppendLine("when cdnm_cName like 'D%' then 'NRECORD'");
///*Modify by leoth lunlin 2013-11-01 end*/
////strSql.AppendLine("else '' end As StopType,srpf_cReasonCode,pjsf_iPJSFID");
//strSql.AppendLine("else '' end As StopType,cdnm_cName,pjsf_iPJSFID");
//strSql.AppendLine(",datediff(minute,psrd_dBeginTime,psrd_dEndTime) As iActualTime,srpf_iPredictedTime as iTime1");
//strSql.AppendLine(",psrd_iPSRDID");
//strSql.AppendLine("from ProjectStopRecord_psrd With(Nolock)");
//strSql.AppendLine("inner join ProjectInShift_pjsf With(Nolock) on psrd_iPJSFID=pjsf_iPJSFID And psrd_lIsDeleted=0 And pjsf_lIsDeleted=0");
//strSql.AppendLine("inner join Shift_shft With(Nolock) on pjsf_iSTID=shft_iSTID and shft_lIsDeleted=0");
//strSql.AppendLine("inner join DCMProfile_dcmp With(Nolock) on pjsf_iDCMPID=dcmp_iDCMPID and dcmp_lIsDeleted=0");
//strSql.AppendLine("inner join DCMAreaProfile_dcma With(Nolock) on dcmp_iAreaID=dcma_iDCMAID and dcma_lIsDeleted=0");
//strSql.AppendLine("inner join ProjectStopReason_psrn With(Nolock) on psrd_iPSRDID=psrn_iPSRDID And psrn_lIsDeleted=0");
//strSql.AppendLine("inner join StopReasonProfile_srpf With(Nolock) on psrn_iSRPFID=srpf_iSRPFID And srpf_lIsDeleted=0 ");
//strSql.AppendLine("left join CodeName_cdnm With(Nolock) on srpf_iReasonType=cdnm_iValue And cdnm_iDictionaryType=5");
//strSql.AppendLine("Where 1=1 ");
////if (oFilter.cGroup != ConstantsVar.StringNull)
//// strSql.AppendLine("");
//if (oFilter.dDate != DateTime.MinValue)
// strSql.AppendLine("And shft_dProductionDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
//if (oFilter.cShift != string.Empty)
// strSql.AppendLine("And shft_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And dcma_cDescription='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
//if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
//{
// strSql.AppendLine("And dcmp_cMachineKey In(" + oFilter.cMachineFilter + ")");
//}
//strSql.AppendLine(") AA where AA.StopType!=''");
//strSql.AppendLine("/******************************************************************/");
//strSql.AppendLine("/*處理停機類型並運算停機時間*/");
//strSql.AppendLine("Declare @iShiftDown int ,@cMachineDown varchar(10),@dDateDown datetime,@dBeginDown datetime,@dEndDown datetime,@cCodeType varchar(10)");
//strSql.AppendLine("Declare @numDown int ,@tagDown int,@sumNormDown int,@normDown int,@iActualTimeDown int,@iSeqDowm int");
//strSql.AppendLine("Declare @iShiftMeal0 int ,@cMachineMeal0 varchar(10),@dDateMeal0 datetime ,@iTimeMeal0 int,@dBeginMeal0 datetime,@dEndMeal0 datetime");
//strSql.AppendLine("Declare @numMeal0 int ,@tagMeal0 int ");
//strSql.AppendLine("Set @tagDown=1");
//strSql.AppendLine("select @numDown=Max(iRowID) from #TmpDown");
//strSql.AppendLine("if(@numDown>0)");
//strSql.AppendLine("begin ");
//strSql.AppendLine("");
//strSql.AppendLine("while(@tagDown<=@numDown)");
//strSql.AppendLine("begin");
//strSql.AppendLine("--select * from #TmpDown");
//strSql.AppendLine("select @iShiftDown=(select iShiftID from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @cMachineDown=(select cMachineCode from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dDateDown=(select dDate from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dBeginDown=(select dBeginTime from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dEndDown=(select dEndTime from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @cCodeType=(select cStopType from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @iSeqDowm=(select iSeq from #TmpDown where iRowID=@tagDown )");
//strSql.AppendLine("if @cCodeType='PLAN'");
//strSql.AppendLine("Update #TmpDown set cStopType='PLAN' where iShiftID=@iShiftDown And cMachineCode=@cMachineDown And dDate=@dDateDown And dBeginTime=@dBeginDown And dEndTime=@dEndDown and iSeq=@iSeqDowm");
//strSql.AppendLine("");
//strSql.AppendLine("select @tagDown=@tagDown+1");
//strSql.AppendLine("end");
//strSql.AppendLine("select @tagDown=1");
//strSql.AppendLine("while(@tagDown<=@numDown)");
//strSql.AppendLine("begin");
//strSql.AppendLine("--select * from #TmpDown");
//strSql.AppendLine("select @iShiftDown=(select iShiftID from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @cMachineDown=(select cMachineCode from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dDateDown=(select dDate from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dBeginDown=(select dBeginTime from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @dEndDown=(select dEndTime from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @cCodeType=(select cStopType from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine("select @iSeqDowm=(select iSeq from #TmpDown where iRowID=@tagDown )");
//strSql.AppendLine("select @sumNormDown=Sum(iTime1) from #TmpDown where iShiftID=@iShiftDown And cMachineCode=@cMachineDown And dDate=@dDateDown And dBeginTime=@dBeginDown And dEndTime=@dEndDown and iSeq=@iSeqDowm");
//strSql.AppendLine("select @normDown=(select iTime1 from #TmpDown where iRowID=@tagDown) ");
//strSql.AppendLine("select @iActualTimeDown=(select iActualTime from #TmpDown where iRowID=@tagDown)");
//strSql.AppendLine(" /*檢查停機記錄是否包含就餐*/");
//strSql.AppendLine("set @tagMeal0=1");
//strSql.AppendLine("select @numMeal0=Max(iRowID) from #TmpMeal");
//strSql.AppendLine("if(@numMeal0>0)");
//strSql.AppendLine("begin");
//strSql.AppendLine("while @tagMeal0<=@numMeal0");
//strSql.AppendLine("begin ");
//strSql.AppendLine("select @iShiftMeal0=(select iShiftID from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine("select @cMachineMeal0=(select cMachineCode from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine("select @dDateMeal0=(select dDate from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine("select @iTimeMeal0=(select iTime from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine("select @dBeginMeal0=(select dBeginTime from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine("select @dEndMeal0=(select dEndTime from #TmpMeal where iRowID=@tagMeal0)");
//strSql.AppendLine(" ");
//strSql.AppendLine("--select @iTimeMeal0");
//strSql.AppendLine("if @iShiftDown=@iShiftMeal0 and @cMachineDown=@cMachineMeal0 and @dDateDown=@dDateMeal0");
//strSql.AppendLine("and @dBeginDown<=@dBeginMeal0 and @dEndDown>=@dEndMeal0 ");
//strSql.AppendLine("begin");
//strSql.AppendLine("update #TmpDown set iActualTime=iActualTime-@iTimeMeal0 where iRowID=@tagDown");
//strSql.AppendLine(" ");
//strSql.AppendLine("end");
//strSql.AppendLine("select @tagMeal0=@tagMeal0+1");
//strSql.AppendLine("end");
//strSql.AppendLine("end");
//strSql.AppendLine("/*檢查停機記錄是否包含就餐*/");
//strSql.AppendLine("if @cCodeType<>'PLAN'");
//strSql.AppendLine("update #TmpDown set iTime=round(cast(@normDown*iActualTime as float) /@sumNormDown,0) where iRowID=@tagDown");
//strSql.AppendLine("if @cCodeType='PLAN'");
//strSql.AppendLine("update #TmpDown set iTime=iActualTime where iRowID=@tagDown");
//strSql.AppendLine("select @tagDown=@tagDown+1");
//strSql.AppendLine("end");
//strSql.AppendLine("end");
//strSql.AppendLine("/*處理班次就餐時間*/");
//strSql.AppendLine("Declare @iShiftSumMeal int ,@cMachineSumMeal varchar(10),@dDateSumMeal datetime ,@iTimeSumMeal int ");
//strSql.AppendLine("Declare @numSumMeal int ,@tagSumMeal int");
//strSql.AppendLine("set @tagSumMeal=1");
//strSql.AppendLine("select @numSumMeal=Max(iRowID) from #TmpSumMeal");
//strSql.AppendLine("if(@numSumMeal>0)");
//strSql.AppendLine("begin");
//strSql.AppendLine("while @tagSumMeal<=@numSumMeal");
//strSql.AppendLine("begin");
//strSql.AppendLine("select @iShiftSumMeal=(select iShiftID from #TmpSumMeal where iRowID=@tagSumMeal)");
//strSql.AppendLine("select @cMachineSumMeal=(select cMachineCode from #TmpSumMeal where iRowID=@tagSumMeal)");
//strSql.AppendLine("select @dDateSumMeal=(select dDate from #TmpSumMeal where iRowID=@tagSumMeal)");
//strSql.AppendLine("select @iTimeSumMeal=(select iSumTime from #TmpSumMeal where iRowID=@tagSumMeal)");
//strSql.AppendLine(" ");
//strSql.AppendLine("Update #TmpMain set iShiftMeal=@iTimeSumMeal where iShift=@iShiftSumMeal And cMachineCode=@cMachineSumMeal And dDate=@dDateSumMeal");
//strSql.AppendLine(" ");
//strSql.AppendLine("select @tagSumMeal=@tagSumMeal+1");
//strSql.AppendLine("end");
//strSql.AppendLine("end");
//#region /*處理班次內工程就餐時間*/
//strSql.AppendLine("/*處理班次內工程就餐時間*/");
//strSql.AppendLine("Declare @iShiftMain int ,@cMachineMain varchar(10),@dDateMain datetime ,@dBeginMain datetime,@dBeginCoverMain datetime, @dEndMain datetime,@iMealMain int,@iCoverMain int,@iDownAddMain int,@iDownMinusMain int,@lIsProjectMain bit");
//strSql.AppendLine("Declare @numMain int ,@tagMain int ");
//strSql.AppendLine("Declare @iShiftMeal int ,@cMachineMeal varchar(10),@dDateMeal datetime ,@iTimeMeal int,@dBeginMeal datetime,@dEndMeal datetime");
//strSql.AppendLine("Declare @numMeal int ,@tagMeal int ");
//strSql.AppendLine("Declare @iShiftDown1 int ,@cMachineDown1 varchar(10),@dDateDown1 datetime ,@iTimeDown1 int,@dBeginDown1 datetime,@dEndDown1 datetime,@cTypeDown1 varchar(10)");
//strSql.AppendLine("Declare @numDown1 int ,@tagDown1 int , @SumNum int");
//strSql.AppendLine("");
//strSql.AppendLine("set @tagMain=1");
//strSql.AppendLine("select @numMain=Max(iRowID) from #TmpMain");
//strSql.AppendLine("if(@numMain>0)");
//strSql.AppendLine("begin");
//strSql.AppendLine("while @tagMain<=@numMain");
//strSql.AppendLine("begin ");
//strSql.AppendLine("select @iMealMain=0");
//strSql.AppendLine("select @iCoverMain=0");
//strSql.AppendLine(" ");
//strSql.AppendLine("select @iShiftMain=(select iShift from #TmpMain where iRowID=@tagMain)");
//strSql.AppendLine("select @cMachineMain=(select cMachineCode from #TmpMain where iRowID=@tagMain)");
//strSql.AppendLine("select @dDateMain=(select dDate from #TmpMain where iRowID=@tagMain)");
//strSql.AppendLine("select @dBeginMain=(select dProjectStart from #TmpMain where iRowID=@tagMain)");
//strSql.AppendLine("select @dBeginCoverMain=(select dProjectCover from #TmpMain where iRowID=@tagMain) ");
//strSql.AppendLine("select @dEndMain=(select dProjectEnd from #TmpMain where iRowID=@tagMain) ");
//strSql.AppendLine("select @lIsProjectMain=(select lIsproject from #TmpMain where iRowID=@tagMain)");
//strSql.AppendLine("");
//strSql.AppendLine("set @tagMeal=1");
//strSql.AppendLine("/*工程是否包含就餐*/");
//strSql.AppendLine("select @numMeal=Max(iRowID) from #TmpMeal");
//strSql.AppendLine("if(@numMeal>0)");
//strSql.AppendLine("begin");
//strSql.AppendLine("while @tagMeal<=@numMeal");
//strSql.AppendLine("begin");
//strSql.AppendLine("select @iDownAddMain=0");
//strSql.AppendLine("select @iDownMinusMain=0");
//strSql.AppendLine(" ");
//strSql.AppendLine("select @iShiftMeal=(select iShiftID from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine("select @cMachineMeal=(select cMachineCode from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine("select @dDateMeal=(select dDate from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine("select @iTimeMeal=(select iTime from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine("select @dBeginMeal=(select dBeginTime from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine("select @dEndMeal=(select dEndTime from #TmpMeal where iRowID=@tagMeal)");
//strSql.AppendLine(" ");
//strSql.AppendLine("if @dBeginMain<=@dBeginMeal and @dEndMain>=@dEndMeal and @iShiftMain=@iShiftMeal and @cMachineMain=@cMachineMeal and @dDateMain=@dDateMeal");
//strSql.AppendLine("select @iMealMain=@iMealMain+Isnull(@iTimeMeal,0)");
//strSql.AppendLine(" ");
//strSql.AppendLine("if @lIsProjectMain=1 and @dBeginMain<=@dBeginMeal and @dBeginCoverMain>=@dEndMeal and @iShiftMain=@iShiftMeal and @cMachineMain=@cMachineMeal and @dDateMain=@dDateMeal");
//strSql.AppendLine("select @iCoverMain=@iCoverMain+Isnull(@iTimeMeal,0)");
//strSql.AppendLine(" ");
//strSql.AppendLine("");
//strSql.AppendLine("select @tagMeal=@tagMeal+1");
//strSql.AppendLine("end");
//strSql.AppendLine("update #TmpMain set --(case when iDownTime+@iDownAddMain-@iDownMinusMain>0 then iDownTime+@iDownAddMain-@iDownMinusMain else 0 end)");
//strSql.AppendLine("iCoversionTime=iCoversionTime-@iCoverMain--(case when iCoversionTime-@iCoverMain>0 then iCoversionTime-@iCoverMain else 0 end)");
//strSql.AppendLine(",iMealTime=@iMealMain ");
//strSql.AppendLine("where iRowID=@tagMain");
//strSql.AppendLine("end ");
//strSql.AppendLine(" ");
//strSql.AppendLine("select @tagMain=@tagMain+1");
//strSql.AppendLine("end");
//strSql.AppendLine("end");
//strSql.AppendLine("");
//#endregion
//strSql.AppendLine("insert into #TmpSumMain");
//strSql.AppendLine("select dDate,cShift,cMachineCode,Max(iShiftOperation) As TotalOperatino,Max(iShiftMeal) As TotalMeal,0 As TotalDown,");
//strSql.AppendLine("Sum(iCoversionTime) As TotalCoversion,Max(iIdealSpeed1) As iIdealSpeed1 ,Max(iIdealSpeed2) As iIdealSpeed2,0 As IsExist");
//strSql.AppendLine("from #TmpMain With(Nolock)");
//strSql.AppendLine("group by dDate,cShift,cMachineCode,datediff(minute,dShiftBegin,dShiftEnd)");
//strSql.AppendLine("");
//strSql.AppendLine("select * from #TmpSumMain");
//strSql.AppendLine("select *,0 As lIsExist from #TmpMain ");
//strSql.AppendLine("select *,0 As lIsExist from #TmpDown ");
//strSql.AppendLine("select * from #TmpMeal ");
//strSql.AppendLine("select distinct iShiftID,cShift,cMachineCode,dDate,dBeginTime,dEndTime,iTime,cStopType,ID,iActualTime from #tmpDown");
//strSql.AppendLine("");
//strSql.AppendLine("drop table #TmpMain");
//strSql.AppendLine("drop table #TmpMeal");
//strSql.AppendLine("drop table #TmpDown");
//strSql.AppendLine("drop table #TmpSumMeal");
#endregion
string cShift = oFilter.cShift == "1" ? "日班" : "夜班";
strSql.AppendLine("--主記錄臨時表");
strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpMain') IS NOT NULL ");
strSql.AppendLine("Begin");
strSql.AppendLine("Drop Table #TmpMain");
strSql.AppendLine("End");
strSql.AppendLine("Create Table #TmpMain(iRowID int,");
strSql.AppendLine("cUnit Nvarchar(100),cGroup Nvarchar(100),dDate datetime,cShift Nvarchar(20),iShift Nvarchar(36),cMachineCode varchar(50),");
strSql.AppendLine("cProject Nvarchar(100),cProduct Nvarchar(100),cSONO Nvarchar(100),cProjection Nvarchar(100),dProjectStart datetime,dProjectCover datetime,");
strSql.AppendLine("dprojectEnd datetime,iRealQty int,iDefectiveQty int,iWasteQty int ,iOperationTime int,iMealTime int,iDownTime int,");
strSql.AppendLine("iCoversionTime int,iIdealSpeed float,iIdealSpeed1 int,iIdealSpeed2 int,");
strSql.AppendLine("iUnit Nvarchar(36),cUnitCD varchar(50),cArea Nvarchar(100),dAddDate datetime,cAdd varchar(50),");
strSql.AppendLine("dLastDate datetime,cLast varchar(50),MainID Nvarchar(36),dShiftBegin datetime,dShiftEnd datetime,iShiftOperation int,iShiftMeal int, iShiftDown int,iShiftCoversion int,lIsProject bit)");
strSql.AppendLine("/************************************************************************************************************************************************/");
strSql.AppendLine("--停機記錄臨時表");
strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpDown') IS NOT NULL ");
strSql.AppendLine("Begin");
strSql.AppendLine("Drop Table #TmpDown");
strSql.AppendLine("End");
strSql.AppendLine("Create Table #TmpDown(iRowID int,");
strSql.AppendLine("iShiftID Nvarchar(36),cShift Nvarchar(20),cMachineCode varchar(20),dDate datetime,dBeginTime datetime,");
strSql.AppendLine("dEndTime datetime,iTime int,cStopType varchar(20),cStopCode varchar(20),DownID Nvarchar(36),DownSubID Nvarchar(36),iActualTime int,iTime1 int)--,iSeq int)");
strSql.AppendLine("/************************************************************************************************************************************************/");
strSql.AppendLine("--就餐記錄臨時表");
strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpMeal') IS NOT NULL ");
strSql.AppendLine("Begin");
strSql.AppendLine("Drop Table #TmpMeal");
strSql.AppendLine("End");
strSql.AppendLine("Create Table #TmpMeal(iRowID int,");
strSql.AppendLine("iShiftID Nvarchar(36),cShift Nvarchar(20),cMachineCode varchar(20),dDate datetime,dBeginTime datetime,");
strSql.AppendLine("dEndTime datetime,iTime int,iActualTime int,MealID Nvarchar(36))");
strSql.AppendLine("/************************************************************************************************************************************************/");
strSql.AppendLine("");
strSql.AppendLine("Insert Into #TmpMain");
strSql.AppendLine("/*空閒記錄*/");
strSql.AppendLine("select ROW_NUMBER() over(order by cMachineCode) As RowID,* From(");
strSql.AppendLine("select amr_cPublicInstitution As cUnit,'" + oFilter.cGroup + "' As cGroup,sifo_dProdDate As dDate,stm_cShiftName As cShift,sifo_RecordID As iShift,mmt_cMachineName As cMachineCode,sst_cStatusName As cProject");
strSql.AppendLine(",sst_cStatusName As cProduct,sst_cStatusName As cSONO,sst_cStatusName As cProjection,sst_dBeginTime As dProjectStart,getdate() As dProjectCover,sst_dEndTime As dProjectEnd");
strSql.AppendLine(",0 As iRealQty,0 As iDefectiveQty,0 As iWasteQty,Case When datediff(second,sst_dBeginTime,sst_dEndTime)>0 Then Datediff(second,sst_dBeginTime,sst_dEndTime) Else 0 End As iOperationTime");
strSql.AppendLine(",0 As iMealTime_A,Case When datediff(minute,sst_dBeginTime,sst_dEndTime)>0 Then Datediff(second,sst_dBeginTime,sst_dEndTime) Else 0 End As iDownTime_A");
strSql.AppendLine(",0 As iCoversionTime,0 As iIdealSpeed,0 As iIdealSpeed1,0 As iIdealSpeed2");
strSql.AppendLine(",amr_cRecordID As cUnitID,amr_cAreaCode As cUnitCD,amr_cAreaName As cArea,Getdate() As dAddDate,'system' As cAdd,Getdate() As dLastDate, 'system' As cLast");
strSql.AppendLine(",sst_RecordID,sifo_dBeginTime,sifo_dEndTime,datediff(second,sifo_dBeginTime,sifo_dEndTime) As iShiftOperation,0 As iShiftMeal,0 As iShiftDown,0 As iShiftCoversion,0 As lIsProject");
strSql.AppendLine("--,* ");
strSql.AppendLine("from SpareStatus_sst With(Nolock)");
strSql.AppendLine("Inner Join MachineMaster_mmt With(Nolock) On mmt_cMachineID=sst_cMachineNO");
strSql.AppendLine("Inner Join AreaMaster_amr With(Nolock) On mmt_cAreaID=amr_cRecordID");
strSql.AppendLine("Inner Join ShiftInfo_sifo With(Nolock) On sst_iSIFOID=sifo_RecordID");
strSql.AppendLine("Inner Join ShiftTypeMaster_stm With(Nolock) On stm_iRecordID=sifo_iSTMID");
strSql.AppendLine("Where 1=1 ");
if (oFilter.dDate != DateTime.MinValue)
strSql.AppendLine("And sifo_dProdDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
if (oFilter.cShift != string.Empty)
strSql.AppendLine("And stm_cShiftName='" + cShift + "'");
if (oFilter.cUnit != string.Empty)
strSql.AppendLine("And amr_cPublicInstitution='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSql.AppendLine("And mmt_cMachineID In(" + oFilter.cMachineFilter + ")");
}
//strSql.AppendLine("--And sifo_dProdDate='2014-3-13'");
//strSql.AppendLine("--And mmt_cMachineID In('F-034-013','F-034-018','F-034-036','F-034-017','F-034-014','F-034-011','F-034-015','F-034-016','F-034-010','F-034-001','F-034-002','-')");
//strSql.AppendLine("--and mmt_cMachineName='D-SWT-003' ");
strSql.AppendLine("/*空閒記錄*/");
strSql.AppendLine("Union All");
strSql.AppendLine("/*工程記錄*/");
strSql.AppendLine("select amr_cPublicInstitution As cUnit,'" + oFilter.cGroup + "' As cGroup,sifo_dProdDate As dDate,stm_cShiftName As cShift,sifo_RecordID As iShift,mmt_cMachineName As cMachineCode,swl_cSONO As cProject");
strSql.AppendLine(",swl_cItemName As cProduct,swl_cItemNO As cSONO,swl_cProdName As cProjection,spl_dBeginTime As dProjectStart,spl_dProdBegin As dProjectCover,spl_dEndTime As dProjectEnd");
strSql.AppendLine("--,spl_iQTY-spl_iDefectiveQty-spl_iWastQTY As iRealQty,spl_iDefectiveQty As iDefectiveQty,spl_iWastQTY As iWasteQty");
strSql.AppendLine(" ,case when '" + oFilter.cGroup + "'='過膠科' then");
strSql.AppendLine("round((cast(((spl_iQTY-spl_iDefectiveQty-spl_iWastQTY)*pai_iUnitLength) as float)/100)+0.5,0)");
strSql.AppendLine("else");
strSql.AppendLine("spl_iQTY-spl_iDefectiveQty-spl_iWastQTY");
strSql.AppendLine("end As iRealQty");
strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
strSql.AppendLine("round((cast((spl_iDefectiveQty*pai_iUnitLength) as float)/100)+0.5,0)");
strSql.AppendLine("else");
strSql.AppendLine("spl_iDefectiveQty");
strSql.AppendLine("end As iDefectiveQty ");
strSql.AppendLine(",case when '" + oFilter.cGroup + "'='過膠科' then");
strSql.AppendLine("round((cast((spl_iWastQTY*pai_iUnitLength) as float)/100)+0.5,0)");
strSql.AppendLine("else");
strSql.AppendLine("spl_iWastQTY");
strSql.AppendLine("end As iWasteQty");
strSql.AppendLine(",Case When datediff(second,spl_dBeginTime,spl_dEndTime)>0 Then Datediff(second,spl_dBeginTime,spl_dEndTime) Else 0 End As iOperationTime,0 As iMealTime_A,0 As iDownTime_A,datediff(second,spl_dBeginTime,spl_dProdBegin) As iCoversionTime");
strSql.AppendLine(",case when datediff(second,spl_dBeginTime,spl_dEndTime)>0 then");
strSql.AppendLine("round(cast( spl_iCalQTY as float)/datediff(second,spl_dBeginTime,spl_dEndTime),2)");
strSql.AppendLine("else 0 end As iIdealSpeed,0 As iIdealSpeed1,0 As iIdealSpeed2");
strSql.AppendLine(",amr_cRecordID As cUnitID,amr_cAreaCode As cUnitCD,amr_cAreaName As cArea,Getdate() As dAddDate,'system' As cAdd,Getdate() As dLastDate, 'system' As cLast");
strSql.AppendLine(",spl_RecordID,sifo_dBeginTime,sifo_dEndTime,datediff(second,sifo_dBeginTime,sifo_dEndTime) As iShiftOperation,0 As iShiftMeal,0 As iShiftDown,0 As iShiftCoversion,1 As lIsProject");
strSql.AppendLine("--,* ");
strSql.AppendLine("from ShiftProjList_spl With(Nolock)");
strSql.AppendLine("Inner Join PrintProject_ppj With(Nolock) On spl_PPJID=ppj_RecordID");
strSql.AppendLine("Inner Join ProjectAdditionalInformation_pai With(Nolock) On ppj_RecordID=pai_PPJRecordID");
strSql.AppendLine("Inner Join ScheduleProjList_swl With(Nolock) On ppj_SWLID=swl_RecordID And swl_cProjStatus In('FINISH','STOP')");
strSql.AppendLine("Inner Join ShiftInfo_sifo With(Nolock) On spl_SIFOID=sifo_RecordID");
strSql.AppendLine("Inner Join ShiftTypeMaster_stm With(Nolock) On stm_iRecordID=sifo_iSTMID");
strSql.AppendLine("Inner Join MachineMaster_mmt With(Nolock) On mmt_cMachineID=swl_cMachineNO");
strSql.AppendLine("Inner Join AreaMaster_amr With(Nolock) On mmt_cAreaID=amr_cRecordID");
strSql.AppendLine("Where 1=1 ");
if (oFilter.dDate != DateTime.MinValue)
strSql.AppendLine("And sifo_dProdDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
if (oFilter.cShift != string.Empty)
strSql.AppendLine("And stm_cShiftName='" + cShift + "'");
if (oFilter.cUnit != string.Empty)
strSql.AppendLine("And amr_cPublicInstitution='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSql.AppendLine("And mmt_cMachineID In(" + oFilter.cMachineFilter + ")");
}
//strSql.AppendLine("--And sifo_dProdDate='2014-3-13'");
//strSql.AppendLine("--And mmt_cMachineID In('F-034-013','F-034-018','F-034-036','F-034-017','F-034-014','F-034-011','F-034-015','F-034-016','F-034-010','F-034-001','F-034-002','-')");
strSql.AppendLine(") MainData --where MainData.cMachineCode='D-SWT-003' ");
strSql.AppendLine("Order By MainData.cMachineCode");
strSql.AppendLine("/*工程記錄*/");
strSql.AppendLine("/*就餐記錄*/");
strSql.AppendLine("Insert Into #TmpMeal");
strSql.AppendLine("select ROW_NUMBER() over(order by cMachineCode) As RowID,* From(");
strSql.AppendLine("select ");
strSql.AppendLine("--psrd_RecordID As iRowID,");
strSql.AppendLine("sifo_RecordID As iShiftID,stm_cShiftName As cShift,mmt_cMachineName As cMachineCode,sifo_dProdDate As dDate");
strSql.AppendLine(",psrd_dBeginTime As dBeginTime,psrd_dEndTime As dEndTime,psrd_iPredictStopTime As iTime");
strSql.AppendLine("--,'MEAL' As cStopType,srm_cReasonCode As cStopCode");
strSql.AppendLine(",psrd_iActualStopTime As iActualTime,spl_RecordID As ID--,srm_iPredictedTime*60 As iTime1,'' As iSeq--,'' As lIsExist");
strSql.AppendLine("--,* ");
strSql.AppendLine("from ProjectStopRecord_psrd With(Nolock)");
strSql.AppendLine("Inner Join ShiftProjList_spl With(Nolock) On psrd_SPLID=spl_RecordID");
strSql.AppendLine("Inner Join ProjectStopReason_pjsr With(Nolock) On pjsr_PSRDID=psrd_RecordID");
strSql.AppendLine("Inner Join StopReasonMaster_srm With(Nolock) On pjsr_iSRMID=srm_iRecordID");
strSql.AppendLine("");
strSql.AppendLine("Inner Join PrintProject_ppj With(Nolock) On spl_PPJID=ppj_RecordID");
strSql.AppendLine("Inner Join ScheduleProjList_swl With(Nolock) On ppj_SWLID=swl_RecordID And swl_cProjStatus In('FINISH','STOP')");
strSql.AppendLine("Inner Join ShiftInfo_sifo With(Nolock) On spl_SIFOID=sifo_RecordID");
strSql.AppendLine("Inner Join ShiftTypeMaster_stm With(Nolock) On stm_iRecordID=sifo_iSTMID");
strSql.AppendLine("Inner Join MachineMaster_mmt With(Nolock) On mmt_cMachineID=swl_cMachineNO");
strSql.AppendLine("Where 1=1 ");
if (oFilter.dDate != DateTime.MinValue)
strSql.AppendLine("And sifo_dProdDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
if (oFilter.cShift != string.Empty)
strSql.AppendLine("And stm_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And amr_cPublicInstitution='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSql.AppendLine("And mmt_cMachineID In(" + oFilter.cMachineFilter + ")");
}
//strSql.AppendLine("--And sifo_dProdDate='2014-3-13' ");
strSql.AppendLine("And srm_cPlanType='DINNER'");
strSql.AppendLine(") MealDate --where MealDate.cMachineCode='D-SWT-003' ");
strSql.AppendLine("Order by MealDate.cMachineCode");
strSql.AppendLine("/*就餐記錄*/");
strSql.AppendLine("/*停機記錄*/");
strSql.AppendLine("Insert Into #TmpDown");
strSql.AppendLine("select ROW_NUMBER() over(order by cMachineCode) As RowID,* From(");
strSql.AppendLine("select ");
strSql.AppendLine("--psrd_RecordID As iRowID,");
strSql.AppendLine("sifo_RecordID As iShiftID,stm_cShiftName As cShift,mmt_cMachineName As cMachineCode,sifo_dProdDate As dDate");
strSql.AppendLine(",psrd_dBeginTime As dBeginTime,psrd_dEndTime As dEndTime,psrd_iPredictStopTime As iTime,");
strSql.AppendLine("case when srm_cPlanType = 'A' then 'PLAN'");
strSql.AppendLine("when srm_cPlanType = 'C' then 'NPLAN'");
strSql.AppendLine("when srm_cPlanType = 'D' then 'NRECORD'");
strSql.AppendLine("--when srm_cPlanType like 'M%' then 'MEAL'");
strSql.AppendLine("else '' end As cStopType,srm_cReasonCode As cStopCode");
strSql.AppendLine(",spl_RecordID As DownID,psrd_RecordID As SubID,psrd_iActualStopTime As iActualTime,srm_iPredictedTime*60 As iTime1--,'' As iSeq--,'' As lIsExist");
strSql.AppendLine("--,* ");
strSql.AppendLine("From ProjectStopRecord_psrd With(Nolock)");
strSql.AppendLine("Inner Join ShiftProjList_spl With(Nolock) On psrd_SPLID=spl_RecordID");
strSql.AppendLine("Inner Join ProjectStopReason_pjsr With(Nolock) On pjsr_PSRDID=psrd_RecordID");
strSql.AppendLine("Inner Join StopReasonMaster_srm With(Nolock) On pjsr_iSRMID=srm_iRecordID");
strSql.AppendLine("Inner Join PrintProject_ppj With(Nolock) On spl_PPJID=ppj_RecordID");
strSql.AppendLine("Inner Join ScheduleProjList_swl With(Nolock) On ppj_SWLID=swl_RecordID And swl_cProjStatus In('FINISH','STOP')");
strSql.AppendLine("Inner Join ShiftInfo_sifo With(Nolock) On spl_SIFOID=sifo_RecordID");
strSql.AppendLine("Inner Join ShiftTypeMaster_stm With(Nolock) On stm_iRecordID=sifo_iSTMID");
strSql.AppendLine("Inner Join MachineMaster_mmt With(Nolock) On mmt_cMachineID=swl_cMachineNO");
strSql.AppendLine("Where 1=1 ");
if (oFilter.dDate != DateTime.MinValue)
strSql.AppendLine("And sifo_dProdDate='" + oFilter.dDate.ToString("yyyy-MM-dd", DateTimeFormatInfo.InvariantInfo) + "'");
if (oFilter.cShift != string.Empty)
strSql.AppendLine("And stm_cShiftName='" + cShift + "'");
//if (oFilter.cUnit != string.Empty)
// strSql.AppendLine("And amr_cPublicInstitution='" + oFilter.cUnit + "'");
//if (oFilter.cArea != string.Empty)
// strSql.AppendLine("And dcma_cAreaCode='" + oFilter.cArea + "'");
////if (oFilter.cMachine != ConstantsVar.StringNull)
//// strSql.AppendLine("And dcmp_cMachineKey='" + oFilter.cMachine + "'");
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSql.AppendLine("And mmt_cMachineID In(" + oFilter.cMachineFilter + ")");
}
//strSql.AppendLine("--And sifo_dProdDate='2014-3-13'");
strSql.AppendLine(") DownData Where DownData.cStopType<>'' --and DownData.cMachineCode='D-SWT-003' ");
strSql.AppendLine("Order By cMachineCode");
strSql.AppendLine("");
strSql.AppendLine("--select sum(iTime),sum(iActualTime) from (");
strSql.AppendLine("--select distinct iTime,DownID,iActualTime from #TmpDown) qq");
strSql.AppendLine("--select distinct iTime,DownID,iActualTime from #TmpDown");
strSql.AppendLine("/*停機記錄*/");
strSql.AppendLine("/*將就餐時間填充到主數據表*/");
strSql.AppendLine("Declare @MainID Nvarchar(36),@MainTag int,@MainNum int");
strSql.AppendLine("Set @MainTag=1");
strSql.AppendLine("--Set @MealTag=1");
strSql.AppendLine("Select @MainNum=Max(iRowID) From #TmpMain");
strSql.AppendLine("if(@MainNum>0)");
strSql.AppendLine("begin");
strSql.AppendLine("while(@MainTag<=@MainNum)");
strSql.AppendLine("begin");
strSql.AppendLine("Select @MainID=(select MainID from #TmpMain where iRowID=@MainTag)");
strSql.AppendLine(" ");
strSql.AppendLine("Update #TmpMain Set iMealTime=isnull((Select Sum(iActualTime) From #TmpMeal Where MealID=@MainID),0) Where MainID=@MainID");
strSql.AppendLine("Select @MainTag=@MainTag+1");
strSql.AppendLine("end");
strSql.AppendLine("end");
strSql.AppendLine("");
strSql.AppendLine("");
strSql.AppendLine("/*將就餐時間填充到主數據表*/");
strSql.AppendLine("/*處理停機記錄*/");
strSql.AppendLine("--select * from #TmpDown");
strSql.AppendLine("Declare @DownID Nvarchar(36),@DownSubID Nvarchar(36),@iDownTime int,@DownTag int,@DownNum int,@DownType varchar(20),@SumTime int");
strSql.AppendLine("Set @DownTag=1");
strSql.AppendLine("Select @DownNum=Max(iRowID) From #TmpDown");
strSql.AppendLine("if(@DownNum>0)");
strSql.AppendLine("begin");
strSql.AppendLine("while(@DownTag<=@DownNum)");
strSql.AppendLine("begin");
strSql.AppendLine("select @DownID=(select DownID from #TmpDown where iRowID=@DownTag)");
strSql.AppendLine("select @DownSubID=(select DownSubID from #TmpDown where iRowID=@DownTag)");
strSql.AppendLine("select @DownType=(select cStopType from #TmpDown where iRowID=@DownTag)");
strSql.AppendLine(" ");
strSql.AppendLine("if(@DownType='PLAN')");
strSql.AppendLine("begin");
strSql.AppendLine("Update #TmpDown Set cStopType='PLAN' Where DownID=@DownID And DownSubID=@DownSubID");
strSql.AppendLine("Update #TmpDown Set iTime=iActualTime Where DownID=@DownID And DownSubID=@DownSubID");
strSql.AppendLine("end");
strSql.AppendLine(" ");
strSql.AppendLine("select @iDownTime=(select iTime from #TmpDown where iRowID=@DownTag)");
strSql.AppendLine("select @SumTime=sum(iTime1) from #TmpDown where DownID=@DownID And DownSubID=@DownSubID");
strSql.AppendLine(" ");
strSql.AppendLine("if(@DownType<>'PLAN')");
strSql.AppendLine("begin");
strSql.AppendLine("Update #TmpDown Set iTime=round(cast(iTime1*iActualTime as float) /@SumTime,0) Where iRowID=@DownTag");
strSql.AppendLine("end");
strSql.AppendLine("Select @DownTag=@DownTag+1");
strSql.AppendLine("end");
strSql.AppendLine("Set @DownTag=1");
strSql.AppendLine(" ");
strSql.AppendLine("while(@DownTag<=@DownNum)");
strSql.AppendLine("begin");
strSql.AppendLine("select @DownID=(select DownID from #TmpDown where iRowID=@DownTag)");
strSql.AppendLine("Update #TmpMain Set iDownTime=(Select Sum(iTime) From (select distinct iTime,cStopType,DownID,iActualTime from #TmpDown where cStopType='PLAN') D Where D.DownID=@DownID) Where MainID=@DownID ");
strSql.AppendLine("Select @DownTag=@DownTag+1");
strSql.AppendLine("end");
strSql.AppendLine("end");
strSql.AppendLine("/*處理停機記錄*/");
strSql.AppendLine("");
strSql.AppendLine("select dDate,cShift,cMachineCode,Max(iShiftOperation) As TotalOperatino,Isnull(sum(iMealTime),0) As TotalMeal,Isnull(sum(iDownTime),0) As TotalDown,");
strSql.AppendLine("Isnull(Sum(iCoversionTime),0) As TotalCoversion,Max(iIdealSpeed1) As iIdealSpeed1 ,Max(iIdealSpeed2) As iIdealSpeed2,0 As IsExist");
strSql.AppendLine("from #TmpMain With(Nolock)");
strSql.AppendLine("--where cMachineCode='D-024-003'");
strSql.AppendLine("group by dDate,cShift,cMachineCode--,datediff(minute,dShiftBegin,dShiftEnd)");
strSql.AppendLine("");
strSql.AppendLine("select * from #TmpMain --where cMachineCode='D-024-003'");
strSql.AppendLine("select * from #TmpMeal");
strSql.AppendLine("select * from #TmpDown where cStopType<>'PLAN'");
strSql.AppendLine("");
strSql.AppendLine("drop table #TmpMain");
strSql.AppendLine("drop table #TmpMeal");
strSql.AppendLine("drop table #TmpDown");
return strSql.ToString().Trim();
}
DataSet GetReportPPCData(TransitionFilter oFilter)
{
DataSet objReturn = new DataSet();
string strSql = GetReportPPCDataSQL(oFilter);
if (strSql != "")
{
//Database db = DatabaseFactory.CreateDatabase(oFilter.cGroupCD);
//DbCommand dbCommand = db.GetSqlStringCommand(strSql);
//objReturn = db.ExecuteDataSet(dbCommand);
//foreach (DataRow item in objReturn.Tables[0].Rows)
//{
// if (dicSpeed.ContainsKey(item["cMachineCode"].ToString().Trim()))
// {
// item["iIdealSpeed1"] = dicSpeed[item["cMachineCode"].ToString().Trim()][0];
// item["iIdealSpeed2"] = dicSpeed[item["cMachineCode"].ToString().Trim()][1];
// item["lIsExist"] = 1;
// }
//}
//foreach (DataRow item in objReturn.Tables[1].Rows)
//{
// if (dicSpeed.ContainsKey(item["cMachineCode"].ToString().Trim()))
// {
// item["iIdealSpeed1"] = dicSpeed[item["cMachineCode"].ToString().Trim()][0];
// item["iIdealSpeed2"] = dicSpeed[item["cMachineCode"].ToString().Trim()][1];
// item["lIsExist"] = 1;
// }
//}
////MainDBDataContext db = new MainDBDataContext();
////db.Connection.Open();
////StringBuilder str = new StringBuilder();
////str.AppendLine("select convert(decimal,'1') as fSpeed,convert(decimal,'2') as fSpeed2,'3' as cMachineCode");
////str.AppendLine("select convert(decimal,'4') as fSpeed,convert(decimal,'5') as fSpeed2,'6' as cMachineCode");
////System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(
//// str.ToString(), (System.Data.SqlClient.SqlConnection)db.Connection);
////using (System.Data.Common.DbDataReader reader = command.ExecuteReader())
////{
//// List<_TPEMMachineSpeed> W;
//// while (reader.Read())
//// {
//// W = new List<_TPEMMachineSpeed>();
//// _TPEMMachineSpeed a = new _TPEMMachineSpeed()
//// {
//// fSpeed = Convert.ToDecimal(reader[0].ToString()),
//// fSpeed2 = Convert.ToDecimal(reader[1].ToString()),
//// cMachineCode = reader[2].ToString()
//// };
//// }
//// reader.NextResult();
//// var b = db.Translate<_TPEMMachineSpeed>(reader);
//// List<_TPEMMachineSpeed> E = b.ToList();
////}
}
return objReturn;
}
void GetReportPPCData(TransitionFilter oFilter, ref List<_TPEMCollect> collectList, ref List<_TPEMMain> mainList, ref List<_TPEMDown> downList)
{
string strSql = GetReportPPCDataSQL(oFilter);
MainDBDataContext db = new MainDBDataContext(strConnectionString);
db.Connection.Open();
//foreach (DataRow item in objReturn.Tables[0].Rows)
//{
// if (dicSpeed.ContainsKey(item["cMachineCode"].ToString().Trim()))
// {
// item["iIdealSpeed1"] = dicSpeed[item["cMachineCode"].ToString().Trim()][0];
// item["iIdealSpeed2"] = dicSpeed[item["cMachineCode"].ToString().Trim()][1];
// item["lIsExist"] = 1;
// }
//}
//foreach (DataRow item in objReturn.Tables[1].Rows)
//{
// if (dicSpeed.ContainsKey(item["cMachineCode"].ToString().Trim()))
// {
// item["iIdealSpeed1"] = dicSpeed[item["cMachineCode"].ToString().Trim()][0];
// item["iIdealSpeed2"] = dicSpeed[item["cMachineCode"].ToString().Trim()][1];
// item["lIsExist"] = 1;
// }
//}
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(
strSql.Trim().ToString(), (System.Data.SqlClient.SqlConnection)db.Connection);
using (System.Data.Common.DbDataReader reader = command.ExecuteReader())
{
collectList = new List<_TPEMCollect>();
while (reader.Read())
{
_TPEMCollect tmp = new _TPEMCollect()
{
//fSpeed = Convert.ToDecimal(reader[0].ToString()),
//fSpeed2 = Convert.ToDecimal(reader[1].ToString()),
//cMachineCode = reader[2].ToString()
dDate = Convert.ToDateTime(reader[0].ToString()),
cShift = reader[1].ToString(),
cMachineCode = reader[2].ToString(),
iTotalOperation = Convert.ToInt32(reader[3].ToString()),
iTotalMeal = Convert.ToInt32(reader[4].ToString()),
iTotalDown = Convert.ToInt32(reader[5].ToString()),
iTotlaCoversion = Convert.ToInt32(reader[6].ToString()),
iIdealSpeed1 = Convert.ToDecimal(reader[7].ToString()),
iIdealSpeed2 = Convert.ToDecimal(reader[8].ToString()),
lIsExist = Convert.ToBoolean(reader[9])
};
if (dicSpeed.ContainsKey(tmp.cMachineCode.ToString().Trim()))
{
tmp.iIdealSpeed1 = dicSpeed[tmp.cMachineCode.ToString().Trim()][0];
tmp.iIdealSpeed2 = dicSpeed[tmp.cMachineCode.ToString().Trim()][1];
tmp.lIsExist = true;
}
collectList.Add(tmp);
}
reader.NextResult();
mainList = new List<_TPEMMain>();
while (reader.Read())
{
_TPEMMain tmp = new _TPEMMain()
{
iRowID = reader.IsDBNull(0) ? 0 : Convert.ToInt32(reader[0].ToString()),
cUnit = reader.IsDBNull(1) ? "" : reader[1].ToString(),
cGroup = reader.IsDBNull(2) ? "" : reader[2].ToString(),
dDate = reader.IsDBNull(3) ? DateTime.MinValue : Convert.ToDateTime(reader[3].ToString()),
cShift = reader.IsDBNull(4) ? "" : reader[4].ToString(),
iShift = reader.IsDBNull(5) ? "" : reader[5].ToString(),
cMachineCode = reader.IsDBNull(6) ? "" : reader[6].ToString(),
cProject = reader.IsDBNull(7) ? "" : reader[7].ToString(),
cProduct = reader.IsDBNull(8) ? "" : reader[8].ToString(),
cSONO = reader.IsDBNull(9) ? "" : reader[9].ToString(),
cProjection = reader.IsDBNull(10) ? "" : reader[10].ToString(),
dProjectStart = reader.IsDBNull(11) ? DateTime.MinValue : Convert.ToDateTime(reader[11].ToString()),
dProjectCover = reader.IsDBNull(12) ? DateTime.MinValue : Convert.ToDateTime(reader[12].ToString()),
dprojectEnd = reader.IsDBNull(13) ? DateTime.MinValue : Convert.ToDateTime(reader[13].ToString()),
iRealQty = reader.IsDBNull(14) ? 0 : Convert.ToInt32(reader[14].ToString()),
iDefectiveQty = reader.IsDBNull(15) ? 0 : Convert.ToInt32(reader[15].ToString()),
iWasteQty = reader.IsDBNull(16) ? 0 : Convert.ToInt32(reader[16].ToString()),
iOperationTime = reader.IsDBNull(17) ? 0 : Convert.ToInt32(reader[17].ToString()),
iMealTime = reader.IsDBNull(18) ? 0 : Convert.ToInt32(reader[18].ToString()),
iDownTime = reader.IsDBNull(19) ? 0 : Convert.ToInt32(reader[19].ToString()),
iCoversionTime = reader.IsDBNull(20) ? 0 : Convert.ToInt32(reader[20].ToString()),
iIdealSpeed = reader.IsDBNull(21) ? 0 : float.Parse(reader[21].ToString()),
iIdealSpeed1 = reader.IsDBNull(22) ? 0 : Convert.ToInt32(reader[22].ToString()),
iIdealSpeed2 = reader.IsDBNull(23) ? 0 : Convert.ToInt32(reader[23].ToString()),
iUnit = reader.IsDBNull(24) ? "" : reader[24].ToString(),
cUnitCD = reader.IsDBNull(25) ? "" : reader[25].ToString(),
cArea = reader.IsDBNull(26) ? "" : reader[26].ToString(),
dAddDate = reader.IsDBNull(27) ? DateTime.MinValue : Convert.ToDateTime(reader[27].ToString()),
cAdd = reader.IsDBNull(28) ? "" : reader[28].ToString(),
dLastDate = reader.IsDBNull(29) ? DateTime.MinValue : Convert.ToDateTime(reader[29].ToString()),
cLast = reader.IsDBNull(30) ? "" : reader[30].ToString(),
ID = reader.IsDBNull(31) ? "" : reader[31].ToString(),
dShiftBegin = reader.IsDBNull(32) ? DateTime.MinValue : Convert.ToDateTime(reader[32].ToString()),
dShiftEnd = reader.IsDBNull(33) ? DateTime.MinValue : Convert.ToDateTime(reader[33].ToString()),
iShiftOperation = reader.IsDBNull(34) ? 0 : Convert.ToInt32(reader[34].ToString()),
iShiftMeal = reader.IsDBNull(35) ? 0 : Convert.ToInt32(reader[35].ToString()),
iShiftDown = reader.IsDBNull(36) ? 0 : Convert.ToInt32(reader[36].ToString()),
iShiftCoversion = reader.IsDBNull(37) ? 0 : Convert.ToInt32(reader[37].ToString()),
lIsProject = Convert.ToBoolean(reader[38])
};
if (dicSpeed.ContainsKey(tmp.cMachineCode.ToString().Trim()))
{
tmp.iIdealSpeed1 = dicSpeed[tmp.cMachineCode.ToString().Trim()][0];
tmp.iIdealSpeed2 = dicSpeed[tmp.cMachineCode.ToString().Trim()][1];
tmp.lIsExist = true;
}
mainList.Add(tmp);
}
//mainList = b.ToList();
reader.NextResult();
reader.NextResult();
var b = db.Translate<_TPEMDown>(reader);
downList = b.ToList();
}
}
/// <summary>
/// 無事務插入數據SQL
/// </summary>
/// <returns></returns>
string GetInsertTPEMSql(List<TPEMMainObject> TPEMObjectList, bool IsSql, TransitionFilter objFilter)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("/*Sql開始*/");
strSql.AppendLine("SET NOCOUNT ON");
strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpAll') IS NOT NULL ");
strSql.AppendLine("Begin");
strSql.AppendLine("Drop Table #TmpAll");
strSql.AppendLine("End");
//strSql.AppendLine("Else");
//strSql.AppendLine("Begin");
strSql.AppendLine("create table #TmpAll (");
strSql.AppendLine("cMachine Nvarchar (50),");
strSql.AppendLine("cShift Nvarchar (20),");
strSql.AppendLine("dDate Nvarchar (20),");
strSql.AppendLine("MyCorrect Nvarchar (100),");
strSql.AppendLine("MyError Nvarchar (100),");
strSql.AppendLine("MyErrorMessage Nvarchar (2000)");
strSql.AppendLine(")");
//***********************************************************
strSql.AppendLine("IF OBJECT_ID('tempdb..#TmpData') IS NOT NULL ");
strSql.AppendLine("Begin");
strSql.AppendLine("Drop Table #TmpData");
strSql.AppendLine("End");
//strSql.AppendLine("Else");
//strSql.AppendLine("Begin");
strSql.AppendLine("create table #TmpData (");
strSql.AppendLine("cGroup Nvarchar (20),dData Nvarchar (20),cShift Nvarchar (20),cMachine Nvarchar (20),cProject Nvarchar (20),cProduct Nvarchar (20),");
strSql.AppendLine("cSONO Nvarchar (20),cProjection Nvarchar (20),dProjectStart Nvarchar(20),dProjectEnd Nvarchar (20),iRealQty Nvarchar(20),");
strSql.AppendLine("iDefectiveQty Nvarchar (20),iWasteQty Nvarchar (20),iOperationTime Nvarchar (20),iMealTime Nvarchar (20),iDownTime Nvarchar (20),iCoversionTime Nvarchar (20),");
strSql.AppendLine("iIdealSpeed1 Nvarchar (20),iIdealSpeed2 Nvarchar (20),cUnit Nvarchar (20),cArea Nvarchar (20)");
strSql.AppendLine(",iOperationTime1 Nvarchar (20),iMealTime1 Nvarchar (20),iDownTime1 Nvarchar (20),iCoversionTime1 Nvarchar (20)");
strSql.AppendLine(")");
strSql.AppendLine("Declare @iSeq int");
strSql.AppendLine("Set @iSeq=0");
//strSql.AppendLine("End");
foreach (TPEMMainObject TPEMObject in TPEMObjectList)
{
objFilter.cMachineList.Remove(TPEMObject.cMachineCode.Trim());
#region 班次備註
string cShift = TPEMObject.cShift;
TPEMObject.cShift = cShift == "日班" ? "1" : "2";
//string cShift = TPEMObject.cShift == "1" ? "日班" : "夜班";
#endregion
string strKey = TPEMObject.cMachineCode.Trim().Replace("-", "_") + "_" + TPEMObject.dDate.ToString("yyyy_MM_dd") + "_" + TPEMObject.cShift;
string MyCorrect = "@Correct" + TPEMObject.cMachineCode.Trim().Replace("-", "_") + "_" + TPEMObject.dDate.ToString("yyyy_MM_dd") + "_" + TPEMObject.cShift;
string MyError = "@Error" + TPEMObject.cMachineCode.Trim().Replace("-", "_") + "_" + TPEMObject.dDate.ToString("yyyy_MM_dd") + "_" + TPEMObject.cShift;
string MyErrorMessage = "@ErrorMessage" + TPEMObject.cMachineCode.Trim().Replace("-", "_") + "_" + TPEMObject.dDate.ToString("yyyy_MM_dd") + "_" + TPEMObject.cShift;
strSql.AppendLine("Declare " + MyCorrect + " int");
strSql.AppendLine("Declare " + MyError + " int");
strSql.AppendLine("Declare " + MyErrorMessage + " Nvarchar(2000)");
strSql.AppendLine("Set " + MyCorrect + "=0");
strSql.AppendLine("Set " + MyError + "=0");
strSql.AppendLine("Set " + MyErrorMessage + "=''");
if (TPEMObject.iIsExist)
{
strSql.AppendLine("/*工程主表插入(" + strKey + ")*/");
strSql.AppendLine("Begin Try");
//strSql.AppendLine(GetInsertSql(TPEMObject, IsSql));
#region 獲取無事務插入數據的內部SQL
/*主表*/
strSql.AppendLine("Insert Into tpemoee1 ");
strSql.AppendLine("(oee1_cMNum,oee1_dDate,oee1_fTime,oee1_fTotTm,oee1_fEatTm,oee1_fPStpTm,oee1_fSwTm,oee1_fSpeed,oee1_fSpeed2,oee1_dAddDt,oee1_cAddBy,oee1_dLastDt,oee1_cLastBy,oee1_fPlanMaintenanceTime,oee1_fMeetingTime,oee1_fSampleTime,oee1_fNoTaskTime,oee1_fOtherApprovedTime,oee1_cRemark)");
strSql.AppendLine("Values");
strSql.AppendLine("(N'" + TPEMObject.cMachineCode + "',N'" + TPEMObject.dDate.ToString("yyyy-MM-dd").Trim() + "',N'" + TPEMObject.cShift.ToString().Trim() + "','" + TPEMObject.iOperationTotalTime.ToString().Trim() + "','" + TPEMObject.iMealTotalTime.ToString().Trim() + "','" + TPEMObject.iDownTotalTime.ToString().Trim() + "','" + TPEMObject.iCoversionTotalTime.ToString().Trim() + "','" + TPEMObject.iIdealSpeed1.ToString().Trim() + "','" + TPEMObject.iIdealSpeed2.ToString().Trim() + "',Getdate(),N'system',Getdate(),N'system',0,0,0,0,0,'')");//,0,0,0,0,0,''雅圖仕修改表結構後添加字段
if (IsSql && TPEMObject.JobList.Count <= 0)
{
strSql.AppendLine("Insert Into #TmpData Values(");
strSql.AppendLine("'',N'" + TPEMObject.dDate.ToString("yyyy-MM-dd") + "',N'" + cShift + "',");
strSql.AppendLine("N'" + TPEMObject.cMachineCode + "',");
strSql.AppendLine("'','','','','','','','','',");
strSql.AppendLine("N'" + TPEMObject.iOperationTotalTime + "',N'" + TPEMObject.iMealTotalTime + "',N'" + TPEMObject.iDownTotalTime + "',N'" + TPEMObject.iCoversionTotalTime + "',N'" + TPEMObject.iIdealSpeed1 + "',N'" + TPEMObject.iIdealSpeed2 + "','','','" + TPEMObject.iOperationTotalTime.ToString().Trim() + "','" + TPEMObject.iMealTotalTime.ToString().Trim() + "','" + TPEMObject.iDownTotalTime.ToString().Trim() + "','" + TPEMObject.iCoversionTotalTime.ToString().Trim() + "'");
strSql.AppendLine(")");
}
/*工程單*/
strSql.AppendLine("/*工程子表插入(" + strKey + ")*/");
foreach (TPEMJobObject itemJob in TPEMObject.JobList)
{
itemJob.cShift = itemJob.cShift == "日班" ? "1" : "2";
//************************************************************
strSql.AppendLine("Begin Try");
//************************************************************
strSql.AppendLine("Insert Into TPEMJOBT ");
strSql.AppendLine("(jobt_cMNum,jobt_dDate,jobt_fTime,jobt_cNum,jobt_cTitle,jobt_fQty,jobt_fTotTm,jobt_fEatTm,jobt_fPStpTm,jobt_fSwTm,jobt_fSwTm2,jobt_fSpeed,jobt_fWtspd,jobt_fWtspd2,jobt_fWstQty,jobt_dAddDt,jobt_cAddBy,jobt_dLastDt,jobt_cLastBy,jobt_cSeq,jobt_iTaskID,jobt_cTaskStatus,jobt_dJStTm,Jobt_iReadyATM)");//,jobt_fEatTms,jobt_fTotTms,jobt_fSwTms,jobt_fSwTm2s,JobT_fPStpTms,jobt_fSpeeds,jobt_iReadyATMs)");
strSql.AppendLine("Values");
strSql.AppendLine("(N'" + itemJob.cMachineCode + "',N'" + itemJob.dDate.ToString("yyyy-MM-dd").Trim() + "',N'" + itemJob.cShift + "',N'" + itemJob.cProject + "',N'" + itemJob.cProduct + "','" + itemJob.iRealQty + "','" + itemJob.iOperationTime + "','" + itemJob.iMealTime + "','" + itemJob.iDownTime + "','" + itemJob.iCoversionTime + "','0','" + itemJob.iIdealSpeed + "','" + itemJob.iIdealSpeed1 + "','" + itemJob.iIdealSpeed2 + "','" + itemJob.iDefectiveQty + "',Getdate(),'system',Getdate(),'system','','','',null,0)");//,0,0,0,0,0,0,0)");
strSql.AppendLine(" Set @iSeq = (Select Max(jobt_iSeq) From TPEMJOBT)");
if (IsSql)
{
strSql.AppendLine("Insert Into #TmpData Values(");
strSql.AppendLine("N'" + itemJob.cGroup + "',N'" + TPEMObject.dDate.ToString("yyyy-MM-dd") + "',N'" + cShift + "',");
strSql.AppendLine("N'" + TPEMObject.cMachineCode + "',");
strSql.AppendLine("N'" + itemJob.cProject + "',N'" + itemJob.cProduct + "',N'" + itemJob.cSONO + "',N'" + itemJob.cProjection + "',N'" + itemJob.dProjectStart.ToString("yyyy-MM-dd HH:mm:ss") + "',N'" + itemJob.dProjectEnd.ToString("yyyy-MM-dd HH:mm:ss") + "','" + itemJob.iRealQty + "',N'" + itemJob.iDefectiveQty + "',N'" + itemJob.iWasteQty + "',");
//strSql.AppendLine("N'" + TPEMObject.iOperationTotalTime + "',N'" + TPEMObject.iMealTotalTime + "',N'" + TPEMObject.iDownTotalTime + "',N'" + TPEMObject.iCoversionTotalTime + "',N'" + TPEMObject.iIdealSpeed1 + "',N'" + TPEMObject.iIdealSpeed2 + "',N'" + itemJob.cUnit + "',N'" + itemJob.cArea + "'");
strSql.AppendLine("N'" + itemJob.iOperationTime + "',N'" + itemJob.iMealTime + "',N'" + itemJob.iDownTime + "',N'" + itemJob.iCoversionTime + "',N'" + itemJob.iIdealSpeed1 + "',N'" + itemJob.iIdealSpeed2 + "',N'" + itemJob.cUnit + "',N'" + itemJob.cArea + "','" + TPEMObject.iOperationTotalTime.ToString().Trim() + "','" + TPEMObject.iMealTotalTime.ToString().Trim() + "','" + TPEMObject.iDownTotalTime.ToString().Trim() + "','" + TPEMObject.iCoversionTotalTime.ToString().Trim() + "'");
strSql.AppendLine(")");
}
//************************************************************
strSql.AppendLine("Set " + MyCorrect + "=" + MyCorrect + "+1");
strSql.AppendLine("Set " + MyErrorMessage + "=N'轉換成功'");
strSql.AppendLine("End Try");
strSql.AppendLine("Begin Catch");
strSql.AppendLine("Set " + MyError + "=" + MyError + "+1");
strSql.AppendLine("Set " + MyErrorMessage + "=isnull(ERROR_MESSAGE() ,'')");
strSql.AppendLine("End Catch");
//************************************************************
/*停機計畫*/
strSql.AppendLine("/*工程停機內容插入(" + strKey + ")*/");
foreach (TPEMDowantimeObject itemDowantime in itemJob.DowantimeList)
{
itemDowantime.cShift = itemDowantime.cShift == "日班" ? "1" : "2";
if (itemDowantime.cRecordType == "PLAN")
continue;
if (itemDowantime.cRecordType == "NPLAN")
{
strSql.AppendLine("Insert Into TPEMNPST ");
strSql.AppendLine("(npst_cMNum,npst_dDate,npst_fTime,npst_cCode,npst_cNum,npst_iSeq,npst_fTM,npst_dAddDt,npst_cAddBy,npst_dLastDt,npst_cLastBy)");//,npst_fTMs)");
strSql.AppendLine("Values");
}
if (itemDowantime.cRecordType == "NRECORD")
{
strSql.AppendLine("Insert Into TPEMNRST ");
strSql.AppendLine("(nrst_cMNum,nrst_dDate,nrst_fTime,nrst_cCode,nrst_cNum,nrst_iSeq,nrst_fTM,nrst_dAddDt,nrst_cAddBy,nrst_dLastDt,nrst_cLastBy)");//,NRST_fTMs)");
strSql.AppendLine("Values");
}
strSql.AppendLine("(N'" + itemDowantime.cMachineCode + "',N'" + itemDowantime.dDate.ToString("yyyy-MM-dd") + "',N'" + itemDowantime.cShift + "',N'" + itemDowantime.cReasonCode + "',N'" + itemJob.cProject + "',@iSeq,'" + itemDowantime.iTime + "',Getdate(),'system',Getdate(),'system')");//,'" + itemDowantime.iTimes + "')");
}
}
#endregion
//strSql.AppendLine("Set " + MyCorrect + "=" + MyCorrect + "+1");
//strSql.AppendLine("Set " + MyErrorMessage + "=N'轉換成功'");
strSql.AppendLine("End Try");
strSql.AppendLine("Begin Catch");
strSql.AppendLine("Set " + MyError + "=" + MyError + "+1");
strSql.AppendLine("Set " + MyErrorMessage + "=isnull(ERROR_MESSAGE() ,'')");
strSql.AppendLine("End Catch");
strSql.AppendLine("Insert Into #TmpAll Values(N'" + TPEMObject.cMachineCode + "',N'" + cShift + "',N'" + TPEMObject.dDate.ToString("yyyy-MM-dd").Trim() + "'," + MyCorrect + "," + MyError + "," + MyErrorMessage + ")");
}
else
{
strSql.AppendLine("Insert Into #TmpAll Values(N'" + TPEMObject.cMachineCode + "',N'" + cShift + "',N'" + TPEMObject.dDate.ToString("yyyy-MM-dd").Trim() + "',0,1,N'機台身份證編號不存在於[TPEM系統]')");
}
}
//if (objFilter != null)
//{
// string cShift = objFilter.cShift == "1" ? "日班" : "夜班";
// strSql.AppendLine("Insert Into #TmpAll Values(N'" + objFilter.cMachine + "',N'" + cShift + "',N'" + objFilter.dDate.ToString("yyyy-MM-dd").Trim() + "',0,1,N'機台身份證編號不存在於[TPEM系統]')");
//}
if (objFilter != null && objFilter.cMachineList.Count > 0)
{
string cShift = objFilter.cShift == "1" ? "日班" : "夜班";
foreach (string strItem in objFilter.cMachineList)
{
if (dicSpeed.ContainsKey(strItem))
strSql.AppendLine("Insert Into #TmpAll Values(N'" + strItem + "',N'" + cShift + "',N'" + objFilter.dDate.ToString("yyyy-MM-dd").Trim() + "',0,0,N'該機台當前班次沒有數據')");
else
strSql.AppendLine("Insert Into #TmpAll Values(N'" + strItem + "',N'" + cShift + "',N'" + objFilter.dDate.ToString("yyyy-MM-dd").Trim() + "',0,1,N'機台身份證編號不存在於[TPEM系統]')");
}
}
strSql.AppendLine("Select * from #TmpAll");
if (IsSql)
strSql.AppendLine("Select * from #TmpData");
strSql.AppendLine("SET NOCOUNT OFF");
strSql.AppendLine("Drop Table #TmpAll");
strSql.AppendLine("Drop Table #TmpData");
return strSql.ToString().Trim();
}
/// <summary>
/// 執行刪除TPEM操作
/// </summary>
/// <param name="oFilter"></param>
/// <returns></returns>
ReturnValueInfo DeleteTPEMData(TransitionFilter oFilter)
{
ReturnValueInfo objReturn = new ReturnValueInfo();
StringBuilder strSql = new StringBuilder();
strSql.AppendLine(DeleteTPEMDataSQL(oFilter));
try
{
using (TPEMDBDataContext db = new TPEMDBDataContext())
{
int iDel = db.ExecuteCommand(strSql.ToString(), new object[] { });
objReturn.boolValue = true;
}
}
catch (Exception Ex)
{
objReturn.boolValue = false;
objReturn.isError = true;
objReturn.messageText = Ex.Message;
}
return objReturn;
}
public string InsertTPEMDataSQL(TransitionFilter oFilter)
{
StringBuilder strSql = new StringBuilder();
if (!DeleteTPEMData(oFilter).boolValue)
{
return strSql.ToString().Trim();
}
m_CollectList = new List<_TPEMCollect>();
m_MainList = new List<_TPEMMain>();
m_DownList = new List<_TPEMDown>();
GetReportPPCData(oFilter, ref m_CollectList, ref m_MainList, ref m_DownList);
if (m_CollectList.Count > 0)
{
List<TPEMMainObject> TPEMObjectList = ConversitionObject();
strSql.AppendLine(GetInsertTPEMSql(TPEMObjectList, true, oFilter));
return strSql.ToString().Trim();
}
else
{
strSql.AppendLine(GetInsertTPEMSql(new List<TPEMMainObject>(), true, oFilter));
return strSql.ToString().Trim();
}
}
/// <summary>
/// 獲取PPCDataSQL
/// </summary>
/// <param name="oFilter"></param>
/// <returns></returns>
public string GetReportPPCDataSQL(TransitionFilter oFilter)
{
StringBuilder strSql = new StringBuilder();
dicSpeed = new Dictionary<string, decimal[]>();
DataSet objReturn = new DataSet();
if (GetTPEMAllMachine(oFilter, ref dicSpeed))
{
strSql.AppendLine(GetReportSql(oFilter));
}
return strSql.ToString().Trim();
}
/// <summary>
/// 根據傳入的Key 獲取TPEM系統機台速度1和速度2
/// </summary>
/// <param name="cMachineKey"></param>
/// <param name="iSpeed1"></param>
/// <param name="iSpeed2"></param>
/// <returns></returns>
bool GetTPEMAllMachine(TransitionFilter oFilter, ref Dictionary<string, decimal[]> dicSpeed)
{
bool retValue = false;
StringBuilder strSql = new StringBuilder();
strSql.AppendLine("SELECT A.MCHN_CCODE As cMachineCode,A.MCHN_CNAME,B.DEPT_CNAME,A.MCHN_CLCT,A.MCHN_CGRP,");
strSql.AppendLine("CONVERT(char(10),A.MCHN_DTPEMDT,103),A.MCHN_CCAP,A.MCHN_fSpeed As fSpeed,A.MCHN_fSpeed2 As fSpeed2,A.MCHN_CCAT,");
strSql.AppendLine("CONVERT(char(11),A.MCHN_DADDDT,103)+RIGHT(CONVERT(char(20),A.MCHN_DADDDT,109),8)+' '+RIGHT(CONVERT(char(26),A.MCHN_DADDDT,109),2),");
strSql.AppendLine("A.MCHN_CADDBY,CONVERT(char(11),A.MCHN_DLASTDT,103)+RIGHT(CONVERT(char(20),A.MCHN_DLASTDT,109),8)+' '+RIGHT(CONVERT(char(26),A.MCHN_DLASTDT,109),2),");
strSql.AppendLine("A.MCHN_CLASTBY ");
strSql.AppendLine("FROM TPEMMCHN A ");
strSql.AppendLine("LEFT JOIN TPEMDEPT B ON A.MCHN_CDEPT=B.DEPT_CCODE ");
strSql.AppendLine("Where 1=1 And A.MCHN_CCAT<>'D-024'");
if (oFilter.cMachineList.Count > 0 && oFilter.cMachineFilter != string.Empty)
{
strSql.AppendLine("And mchn_ccode In(" + oFilter.cMachineFilter + ")");
}
using (TPEMDBDataContext db = new TPEMDBDataContext())
{
var result = db.ExecuteQuery<_TPEMMachineSpeed>(strSql.ToString(), new object[] { });
if (result != null)
{
List<_TPEMMachineSpeed> objRet = result.ToList();
foreach (_TPEMMachineSpeed objItem in objRet)
{
decimal[] speed = { objItem.fSpeed, objItem.fSpeed2 };
dicSpeed.Add(objItem.cMachineCode.ToString().Trim(), speed);
}
retValue = true;
}
}
return retValue;
}
/// <summary>
/// 獲取機台Code
/// </summary>
/// <param name="oFilter"></param>
/// <returns></returns>
public List<string> GetMachineCode(TransitionFilter oFilter)
{
StringBuilder strSql = new StringBuilder();
List<string> objReturn = new List<string>();
strSql.AppendLine(GetAllMachineSQL());
if (oFilter.cUnit != string.Empty)
strSql.AppendLine("And amr_cPublicInstitution='" + oFilter.cUnit + "'");
if (oFilter.cArea != string.Empty)
strSql.AppendLine("And amr_cRecordID='" + oFilter.cArea + "'");
if (oFilter.cMachine != string.Empty)
strSql.AppendLine("And mmt_cMachineID='" + oFilter.cMachine + "'");
try
{
using (MainDBDataContext db = new MainDBDataContext(strConnectionString))
{
var result = db.ExecuteQuery<_HBIMSMachine>(strSql.ToString(), new object[] { });
if (result != null)
{
List<_HBIMSMachine> objRet = result.ToList();
//objReturn = objRet;
foreach (_HBIMSMachine item in objRet)
{
objReturn.Add(item.mmt_cMachineID);
}
}
}
}
catch (Exception Ex)
{
objReturn = new List<string>();
}
return objReturn;
}
