public static String GetStageNameByType(String type) { String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT [Stage] FROM [Dashboard_Stage] WHERE [Type]=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "int"; param1.ParamName = "@param1"; param1.ParamValue = GeneralUtil.Null2String(type); paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (result.Rows.Count <= 0) { return ""; } String stage = GeneralUtil.Null2String(result.Rows[0][0]); return stage; }
public static DataTable GetLineListByStage(String stageId) { //genutil.TLog<TabDef>.DebugInfo("GetPublishListInIntegrationFile", dashboardTab); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT RTRIM(LTRIM(Line)) AS selectId, RTRIM(LTRIM(Line)) As selectValue FROM Line WHERE RTRIM(LTRIM(Stage))=@param1 ORDER BY selectValue"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "char(10)"; param1.ParamName = "@param1"; param1.ParamValue = GeneralUtil.Null2String(stageId); paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
public static WindowLineInfo GetLineBaseSettingInfo(String lineId,Boolean isSaStage) { WindowLineInfo result = new WindowLineInfo(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT top 1 [StartWorkTime],[StopWorkTime],[Shift],[FmlDspField] FROM [Dashboard_Line_Target] WHERE [Line]=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(10)"; param1.ParamValue = lineId; paramList.Add(param1); DataTable sqlResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (sqlResult.Rows.Count > 0) { result.Shift = GeneralUtil.Null2String(sqlResult.Rows[0]["Shift"]); String FmlDspField = GeneralUtil.Null2String(sqlResult.Rows[0]["FmlDspField"]); if (isSaStage == true) { SaStationDisplayFieldInfo displayFieldInfos = new SaStationDisplayFieldInfo(); if (FmlDspField != "") { displayFieldInfos = SaStationDisplayFieldsString2Struct(FmlDspField); result.IsDefectDsp = displayFieldInfos.IsDefectDsp; result.IsICTDefectDsp = displayFieldInfos.IsICTDefectDsp; result.IsICTInputDsp = displayFieldInfos.IsICTInputDsp; result.IsICTYieldRateDsp = displayFieldInfos.IsICTYieldRateDsp; result.IsInputDsp = displayFieldInfos.IsInputDsp; result.IsYieldRateDsp = displayFieldInfos.IsYieldRateDsp; } } else { SmtStationDisplayFieldInfo displayFieldInfos = new SmtStationDisplayFieldInfo(); if (FmlDspField != "") { displayFieldInfos = SmtStationDisplayFieldsString2Struct(FmlDspField); result.IsDefectDsp = displayFieldInfos.IsDefectDsp; result.IsAOIDefectDsp = displayFieldInfos.IsAOIDefectDsp; result.IsAOIOutputDsp = displayFieldInfos.IsAOIOutputDsp; result.IsInputDsp = displayFieldInfos.IsInputDsp; result.IsYieldRateDsp = displayFieldInfos.IsYieldRateDsp; } } String StartWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StartWorkTime"]); String StopWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StopWorkTime"]); TimeInfo startWorkInfos = new TimeInfo(); if (StartWorkTime != "") { startWorkInfos = TimeString2Struct(StartWorkTime); } TimeInfo stopWorkInfos = new TimeInfo(); if (StopWorkTime != "") { stopWorkInfos = TimeString2Struct(StopWorkTime); } result.StartWorkHour = startWorkInfos.Hour; result.StartWorkMinute = startWorkInfos.Minute; result.StopWorkHour = stopWorkInfos.Hour; result.StopWorkMinute = stopWorkInfos.Minute; } return result; }
public static List<TimeInfo> GetStageWorkTimeInfo(String stageId) { List<TimeInfo> result = new List<TimeInfo>(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT top 1 [StartWorkTime],[StopWorkTime] FROM [Dashboard_Stage_Target] WHERE [Stage] = @param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(10)"; param1.ParamValue = stageId; paramList.Add(param1); DataTable sqlResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (sqlResult.Rows.Count > 0) { String StartWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StartWorkTime"]); String StopWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StopWorkTime"]); TimeInfo startWorkInfos = new TimeInfo(); if (StartWorkTime != "") { startWorkInfos = TimeString2Struct(StartWorkTime); } TimeInfo stopWorkInfos = new TimeInfo(); if (StopWorkTime != "") { stopWorkInfos = TimeString2Struct(StopWorkTime); } result.Add(startWorkInfos); result.Add(stopWorkInfos); } return result; }
public static WindowLineInfo GetLineBaseSettingInfo(String lineId) { WindowLineInfo result = new WindowLineInfo(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT top 1 Dashboard_Line_Target.FPYTarget AS FPYTarget,Dashboard_Line_Target.FPYAlert AS FPYAlert, Dashboard_Line_Target.OutputTarget AS OutputTarget,[StartWorkTime],[StopWorkTime] FROM [Dashboard_Line_Target] WHERE [Line]=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(10)"; param1.ParamValue = lineId; paramList.Add(param1); DataTable sqlResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (sqlResult.Rows.Count > 0) { result.OutputTarget = GeneralUtil.Null2String(sqlResult.Rows[0]["OutputTarget"]); result.FPYAlert = GeneralUtil.Null2String(sqlResult.Rows[0]["FPYAlert"]); result.FPYTarget = GeneralUtil.Null2String(sqlResult.Rows[0]["FPYTarget"]); String StartWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StartWorkTime"]); String StopWorkTime = GeneralUtil.Null2String(sqlResult.Rows[0]["StopWorkTime"]); TimeInfo startWorkInfos = new TimeInfo(); if (StartWorkTime != "") { startWorkInfos = TimeString2Struct(StartWorkTime); } TimeInfo stopWorkInfos = new TimeInfo(); if (StopWorkTime != "") { stopWorkInfos = TimeString2Struct(StopWorkTime); } result.StartWorkHour = startWorkInfos.Hour; result.StartWorkMinute = startWorkInfos.Minute; result.StopWorkHour = stopWorkInfos.Hour; result.StopWorkMinute = stopWorkInfos.Minute; } return result; }
public static int GetStageType(String windowId) { //genutil.TLog<TabDef>.DebugInfo("GetPublishListInIntegrationFile", dashboardTab); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT [Type] FROM [Dashboard_Stage] INNER JOIN [Dashboard_Stage_Target] ON [Dashboard_Stage].Stage=Dashboard_Stage_Target.Stage WHERE [Dashboard_Stage_Target].[WindowID]=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "char(32)"; param1.ParamName = "@param1"; param1.ParamValue = GeneralUtil.Null2String(windowId); paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (result.Rows.Count <= 0) { ExceptionManager.Throw("The specified item has been removed."); } String stageType = GeneralUtil.Null2String(result.Rows[0][0]); int resultType=0; try { resultType = Int32.Parse(stageType); } catch { ExceptionManager.Throw("The stage type is not right."); } return resultType; }
//参数Id: window id ok private static List<LineShowingInfo> GetShowInfoLineListByWinId(String windowId, String LineDataTableName, String StationDataTableName, DateTime NowTime, String IsSaStage) { List<LineShowingInfo> result = new List<LineShowingInfo>(); DataTable LineResult = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = ""; StringBuilder sqlStringBuilder = new StringBuilder(); sqlStringBuilder.Append("SELECT [Dashboard_Line_Target].[Line] As LineId,[Line].Descr As Line ") .Append(",[ID] ") .Append(",[WindowsID] ") .Append(",RTRIM(ISNULL(Shift,''))+' '+SUBSTRING(CONVERT(varchar,[StartWorkTime],120),12,5)+'-'+SUBSTRING(CONVERT(varchar,[StopWorkTime],120),12,5) AS WorkTime ") .Append(",ISNULL(LineData.[Plan],0) AS [Plan] ") .Append(",ISNULL(LineData.[Output],0) AS [Output] ") .Append(",[StartWorkTime] ") .Append(",[StopWorkTime] ") .Append(",[FmlDspField] ") .Append(",[Order] ") .Append("FROM [Dashboard_Line_Target] INNER JOIN [Line] ON [Line].Line=[Dashboard_Line_Target].Line ") .Append("LEFT OUTER JOIN ") .Append("(SELECT [Dashboard_Family_Target].[Line], ") .Append("SUM([Dashboard_Family_Target].[Plan]) AS [Plan], ") .Append("SUM([Dashboard_Family_Data].[Output]) AS [Output] ") .Append("FROM [Dashboard_Family_Target] ") .Append("LEFT OUTER JOIN ") .Append(StationDataTableName) .Append(" AS [Dashboard_Family_Data] ") .Append("ON [Dashboard_Family_Target].Line=[Dashboard_Family_Data].Line ") .Append("AND [Dashboard_Family_Target].Family=[Dashboard_Family_Data].Family ") .Append("AND [Dashboard_Family_Target].Series=[Dashboard_Family_Data].Series ") .Append("WHERE [Dashboard_Family_Target].[WindowsID]=@param1 ") .Append("GROUP BY [Dashboard_Family_Target].[Line]) AS LineData ") .Append("ON [Dashboard_Line_Target].[Line]= LineData.Line ") .Append("WHERE [WindowsID]=@param1 ORDER BY Dashboard_Line_Target.[Order]"); sqlString = sqlStringBuilder.ToString(); //LineDataTableName List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = windowId; paramList.Add(param1); LineResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); for (int i = 0; i < LineResult.Rows.Count; i++) { LineShowingInfo lineInfo = new LineShowingInfo(); String Plan = GeneralUtil.Null2String(LineResult.Rows[i]["Plan"]); String Output = GeneralUtil.Null2String(LineResult.Rows[i]["Output"]); lineInfo.FmlDspField = GeneralUtil.Null2String(LineResult.Rows[i]["FmlDspField"]); lineInfo.LineName = GeneralUtil.Null2String(LineResult.Rows[i]["Line"]); lineInfo.WorkTime = GeneralUtil.Null2String(LineResult.Rows[i]["WorkTime"]); lineInfo.Plan =Plan; lineInfo.Output = Output; String LineId = GeneralUtil.Null2String(LineResult.Rows[i]["LineId"]); String StartWork = GeneralUtil.Null2String(LineResult.Rows[i]["StartWorkTime"]); String StopWork = GeneralUtil.Null2String(LineResult.Rows[i]["StopWorkTime"]); //不在工作时间内的内容在这里不拿掉,使得客户端计算当前显示行简便 if (IsOnWorktime(StartWork, StopWork, NowTime) == true) { lineInfo.IsInWorkTime = "True"; } else { lineInfo.IsInWorkTime = "False"; } //问题: 如果OutputTarget=0, 当前的rate算是0还是1,询问后定为100% int Rate = 100; try { double PlanQty = double.Parse(Plan); if (PlanQty != 0) { Rate = (int)(double.Parse(Output) * 100 / PlanQty); } } catch { //不需要处理, Rate为0 } int TargetRate = GetTargetRate(StartWork, StopWork, NowTime); if (Rate >= TargetRate) { lineInfo.IsRateOk = "True"; } else { lineInfo.IsRateOk = "False"; } lineInfo.Rate = Rate.ToString(); lineInfo.TargetRate = TargetRate.ToString(); List<FamilyShowingInfo> StationResult = GetShowInfoStationListByLineAndWinId(windowId, LineId, StationDataTableName, lineInfo.FmlDspField, IsSaStage); lineInfo.FamilyShowingInfos = StationResult; result.Add(lineInfo); } return result; }
private static DataTable GetDataDatabaseInfoByWinId(String id) { DataTable result = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Window.ID AS WindowId, Dashboard_Window.DataSourceType AS DataSourceType FROM Dashboard_Window WHERE Dashboard_Window.ID=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = id; paramList.Add(param1); result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
public static String SaveDashboardWindowSetting(DashboardWindowInfo windowInfo, String stageType) { String connectString = DatabaseUtil.GetConnectionString(); String windowId = GeneralUtil.Null2String(windowInfo.WindowId); String windowName = GeneralUtil.Null2String(windowInfo.WindowName); //String stage=GeneralUtil.Null2String(windowInfo.StageId); String sqlString = ""; StringBuilder sqlStringBuilder = new StringBuilder(); //同时更新全部相同的stage的start work和stop work的所有SQL StringBuilder sqlStageUpdateAllStringBuilder = new StringBuilder(); //同时更新全部相同的line的start work和stop work的所有SQL StringBuilder sqlLineUpdateAllStringBuilder = new StringBuilder(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); int paramIndex = 0; //如果原先有数据,先删除 if (windowId != "") { CheckWindowSettingExist(windowId); ConditionValueSet paramItem = new ConditionValueSet(); String paramName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramItem.ParamName = paramName; paramItem.DataType = "char(32)"; paramItem.ParamValue = windowId; paramList.Add(paramItem); sqlStringBuilder.Append("DELETE FROM [Dashboard_Family_Target] WHERE WindowsID=").Append(paramName).Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Line_Target] WHERE WindowsID=").Append(paramName).Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Stage_Target] WHERE WindowID=").Append(paramName).Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Window] WHERE ID=").Append(paramName).Append(" "); } else { //检查名称是否重复 windowId = DatabaseUtil.GetUUID(); } CheckWindowNameSame(windowName, windowId); ConditionValueSet paramItemWindowId = new ConditionValueSet(); paramItemWindowId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramItemWindowId.DataType = "char(32)"; paramItemWindowId.ParamValue = windowId; paramList.Add(paramItemWindowId); ConditionValueSet paramWindowName = new ConditionValueSet(); paramWindowName.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramWindowName.DataType = "nvarchar(128)"; paramWindowName.ParamValue = windowName; paramList.Add(paramWindowName); ConditionValueSet paramDisplayName = new ConditionValueSet(); paramDisplayName.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramDisplayName.DataType = "nvarchar(128)"; paramDisplayName.ParamValue = GeneralUtil.Null2String(windowInfo.DisplayName); paramList.Add(paramDisplayName); ConditionValueSet paramAlertMessage = new ConditionValueSet(); paramAlertMessage.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramAlertMessage.DataType = "nvarchar(512)"; paramAlertMessage.ParamValue = GeneralUtil.Null2String(windowInfo.AlertMessage); paramList.Add(paramAlertMessage); TimeInfo timeInfos = new TimeInfo(); timeInfos.Hour = windowInfo.Hour; timeInfos.Minute = windowInfo.Minute; timeInfos.Second = windowInfo.Second; int refreshTime = Struct2TimeInt(timeInfos); ConditionValueSet paramRefreshTime = new ConditionValueSet(); paramRefreshTime.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramRefreshTime.DataType = "bigint"; paramRefreshTime.ParamValue = refreshTime.ToString(); paramList.Add(paramRefreshTime); ConditionValueSet paramDataSourceType = new ConditionValueSet(); paramDataSourceType.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramDataSourceType.DataType = "int"; paramDataSourceType.ParamValue = GeneralUtil.Null2String(windowInfo.DataSourceType); paramList.Add(paramDataSourceType); ConditionValueSet paramIsStageDsp = new ConditionValueSet(); paramIsStageDsp.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramIsStageDsp.DataType = "bit"; paramIsStageDsp.ParamValue = GeneralUtil.Null2String(windowInfo.IsStageDisplay); paramList.Add(paramIsStageDsp); ConditionValueSet paramCdt = new ConditionValueSet(); //新建时 if (windowInfo.Cdt != "") { paramCdt.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramCdt.DataType = "datetime"; paramCdt.ParamValue = GeneralUtil.Null2String(windowInfo.Cdt); paramList.Add(paramCdt); } ConditionValueSet paramEditor = new ConditionValueSet(); paramEditor.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramEditor.DataType = "nvarchar(100)"; paramEditor.ParamValue = GeneralUtil.Null2String(windowInfo.Editor); paramList.Add(paramEditor); //create time由于没有地方修改,而保留了原来的create time sqlStringBuilder.Append("INSERT INTO [Dashboard_Window] ([ID],[WindowName],[DisplayName],[AlertMessage],[RefreshTime]") .Append(",[DataSourceType],[IsStageDsp],[Cdt],[Udt],[Editor]) VALUES(") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramWindowName.ParamName).Append(",") .Append(paramDisplayName.ParamName).Append(",") .Append(paramAlertMessage.ParamName).Append(",") .Append(paramRefreshTime.ParamName).Append(",") .Append(paramDataSourceType.ParamName).Append(",") .Append(paramIsStageDsp.ParamName).Append(","); if (windowInfo.Cdt != "") { sqlStringBuilder.Append(paramCdt.ParamName).Append(","); } else { sqlStringBuilder.Append("getdate()").Append(","); } sqlStringBuilder.Append("getdate()").Append(",") .Append(paramEditor.ParamName) .Append(") "); ConditionValueSet paramStageTargetId = new ConditionValueSet(); paramStageTargetId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStageTargetId.DataType = "char(32)"; paramStageTargetId.ParamValue = DatabaseUtil.GetUUID(); paramList.Add(paramStageTargetId); ConditionValueSet paramStageId = new ConditionValueSet(); paramStageId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStageId.DataType = "char(10)"; paramStageId.ParamValue = GeneralUtil.Null2String(windowInfo.StageId); paramList.Add(paramStageId); //window id见上面 TimeInfo stageStartWorkTimeInfo = new TimeInfo(); stageStartWorkTimeInfo.Hour = windowInfo.StartWorkTimeHour; stageStartWorkTimeInfo.Minute = windowInfo.StartWorkTimeMinute; String stageStartWorkTime = Struct2TimeString(stageStartWorkTimeInfo); ConditionValueSet paramStartWorkTime = new ConditionValueSet(); paramStartWorkTime.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStartWorkTime.DataType = "datetime"; paramStartWorkTime.ParamValue = stageStartWorkTime; paramList.Add(paramStartWorkTime); TimeInfo stageStopWorkTimeInfo = new TimeInfo(); stageStopWorkTimeInfo.Hour = windowInfo.StopWorkTimeHour; stageStopWorkTimeInfo.Minute = windowInfo.StopWorkTimeMinute; String stageStopWorkTime = Struct2TimeString(stageStopWorkTimeInfo); ConditionValueSet paramStopWorkTime = new ConditionValueSet(); paramStopWorkTime.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStopWorkTime.DataType = "datetime"; paramStopWorkTime.ParamValue = stageStopWorkTime; paramList.Add(paramStopWorkTime); StageDisplayFieldInfo stageDisplayFieldInfo = new StageDisplayFieldInfo(); stageDisplayFieldInfo.IsGoalDisplay = windowInfo.IsGoalDisplay; stageDisplayFieldInfo.IsInputDisplay = windowInfo.IsInputDisplay; stageDisplayFieldInfo.IsOutputDisplay = windowInfo.IsOutputDisplay; stageDisplayFieldInfo.IsRateDisplay = windowInfo.IsRateDisplay; String stageDisplayFieldValue = Struct2StageDisplayFieldsString(stageDisplayFieldInfo); ConditionValueSet paramDisplayFields = new ConditionValueSet(); paramDisplayFields.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramDisplayFields.DataType = "varchar(100)"; paramDisplayFields.ParamValue = stageDisplayFieldValue; paramList.Add(paramDisplayFields); StringBuilder sqlStageStringBuilder = new StringBuilder(); sqlStageStringBuilder.Append("INSERT INTO [Dashboard_Stage_Target] ([ID],[Stage],[WindowID],[StartWorkTime],[StopWorkTime],[DisplayFields]) ") .Append("VALUES(") .Append(paramStageTargetId.ParamName).Append(",") .Append(paramStageId.ParamName).Append(",") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramStartWorkTime.ParamName).Append(",") .Append(paramStopWorkTime.ParamName).Append(",") .Append(paramDisplayFields.ParamName) .Append(") "); sqlStageUpdateAllStringBuilder.Append("UPDATE [Dashboard_Stage_Target] SET [StartWorkTime] = ") .Append(paramStartWorkTime.ParamName).Append(",") .Append("[StopWorkTime] =") .Append(paramStopWorkTime.ParamName) .Append(" WHERE [Stage]=") .Append(paramStageId.ParamName).Append(" "); StringBuilder sqlLineStringBuilder = new StringBuilder(); StringBuilder sqlStationStringBuilder = new StringBuilder(); //判断windowId,如果不为空,先删除对应的station,line, stage window,再加入 for (int i = 0; i < windowInfo.WindowLineInfos.Count; i++) { WindowLineInfo lineItem = windowInfo.WindowLineInfos[i]; ConditionValueSet paramLineTargetId = new ConditionValueSet(); paramLineTargetId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramLineTargetId.DataType = "char(32)"; paramLineTargetId.ParamValue = DatabaseUtil.GetUUID(); paramList.Add(paramLineTargetId); ConditionValueSet paramLineId = new ConditionValueSet(); paramLineId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramLineId.DataType = "char(10)"; paramLineId.ParamValue = GeneralUtil.Null2String(lineItem.LineID); paramList.Add(paramLineId); //ConditionValueSet paramOutputTarget = new ConditionValueSet(); //paramOutputTarget.ParamName = "@param" + paramIndex; //paramIndex = paramIndex + 1; //paramOutputTarget.DataType = "int"; //paramOutputTarget.ParamValue = GeneralUtil.Null2String(lineItem.OutputTarget); //paramList.Add(paramOutputTarget); //ConditionValueSet paramFPYTarget = new ConditionValueSet(); //paramFPYTarget.ParamName = "@param" + paramIndex; //paramIndex = paramIndex + 1; //paramFPYTarget.DataType = "float"; //paramFPYTarget.ParamValue = GeneralUtil.Null2String(lineItem.FPYTarget); //paramList.Add(paramFPYTarget); //ConditionValueSet paramFPYAlert = new ConditionValueSet(); //paramFPYAlert.ParamName = "@param" + paramIndex; //paramIndex = paramIndex + 1; //paramFPYAlert.DataType = "float"; //paramFPYAlert.ParamValue = GeneralUtil.Null2String(lineItem.FPYAlert); //paramList.Add(paramFPYAlert); TimeInfo lineStartWorkTimeInfo = new TimeInfo(); lineStartWorkTimeInfo.Hour = lineItem.StartWorkHour; lineStartWorkTimeInfo.Minute = lineItem.StartWorkMinute; String lineStartWorkTime = Struct2TimeString(lineStartWorkTimeInfo); ConditionValueSet paramLineStartWorkTime = new ConditionValueSet(); paramLineStartWorkTime.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramLineStartWorkTime.DataType = "datetime"; paramLineStartWorkTime.ParamValue = lineStartWorkTime; paramList.Add(paramLineStartWorkTime); TimeInfo lineStopWorkTimeInfo = new TimeInfo(); lineStopWorkTimeInfo.Hour = lineItem.StopWorkHour; lineStopWorkTimeInfo.Minute = lineItem.StopWorkMinute; String lineStopWorkTime = Struct2TimeString(lineStopWorkTimeInfo); ConditionValueSet paramLineStopWorkTime = new ConditionValueSet(); paramLineStopWorkTime.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramLineStopWorkTime.DataType = "datetime"; paramLineStopWorkTime.ParamValue = lineStopWorkTime; paramList.Add(paramLineStopWorkTime); ConditionValueSet paramShift = new ConditionValueSet(); paramShift.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramShift.DataType = "varchar"; paramShift.ParamValue = lineItem.Shift; paramList.Add(paramShift); String fmlDspField=""; if (stageType ==Constants.SA_STAGE.ToString()) { SaStationDisplayFieldInfo DisplayFieldsInfo=new SaStationDisplayFieldInfo(); DisplayFieldsInfo.IsDefectDsp =lineItem.IsDefectDsp; DisplayFieldsInfo.IsICTDefectDsp =lineItem.IsICTDefectDsp; DisplayFieldsInfo.IsICTInputDsp =lineItem.IsICTInputDsp; DisplayFieldsInfo.IsICTYieldRateDsp =lineItem.IsICTYieldRateDsp; DisplayFieldsInfo.IsInputDsp =lineItem.IsInputDsp; DisplayFieldsInfo.IsYieldRateDsp =lineItem.IsYieldRateDsp; fmlDspField=Struct2SaStationDisplayFieldsString(DisplayFieldsInfo); } else { SmtStationDisplayFieldInfo DisplayFieldsInfo=new SmtStationDisplayFieldInfo(); DisplayFieldsInfo.IsDefectDsp =lineItem.IsDefectDsp; DisplayFieldsInfo.IsInputDsp =lineItem.IsInputDsp; DisplayFieldsInfo.IsYieldRateDsp =lineItem.IsYieldRateDsp; DisplayFieldsInfo.IsAOIDefectDsp =lineItem.IsAOIDefectDsp; DisplayFieldsInfo.IsAOIOutputDsp =lineItem.IsAOIOutputDsp; fmlDspField=Struct2SmtStationDisplayFieldsString(DisplayFieldsInfo); } ConditionValueSet paramFmlDspField = new ConditionValueSet(); paramFmlDspField.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramFmlDspField.DataType = "varchar"; paramFmlDspField.ParamValue = fmlDspField; paramList.Add(paramFmlDspField); ConditionValueSet paramLineOrder = new ConditionValueSet(); paramLineOrder.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramLineOrder.DataType = "int"; paramLineOrder.ParamValue = i.ToString(); paramList.Add(paramLineOrder); sqlLineStringBuilder.Append("INSERT INTO [Dashboard_Line_Target] ") .Append("([Line]") .Append(",[ID]") .Append(",[WindowsID]") .Append(",[StartWorkTime]") .Append(",[StopWorkTime]") .Append(",[Order]") .Append(",[Shift]") .Append(",[FmlDspField]) ") .Append("VALUES (") .Append(paramLineId.ParamName).Append(",") .Append(paramLineTargetId.ParamName).Append(",") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramLineStartWorkTime.ParamName).Append(",") .Append(paramLineStopWorkTime.ParamName).Append(",") .Append(paramLineOrder.ParamName).Append(",") .Append(paramShift.ParamName).Append(",") .Append(paramFmlDspField.ParamName) .Append(") "); //存盘时,相同的line的基本数据同时变 sqlLineUpdateAllStringBuilder.Append("UPDATE [Dashboard_Line_Target] SET [StartWorkTime] = ") .Append(paramLineStartWorkTime.ParamName) .Append(",[StopWorkTime] =") .Append(paramLineStopWorkTime.ParamName) .Append(",[Shift] =") .Append(paramShift.ParamName) .Append(" WHERE [Line]=") .Append(paramLineId.ParamName).Append(" "); //存SA的目标量 // String SA = ConfigurationManager.AppSettings["SA"].ToString(); // ConditionValueSet paramSaStage = new ConditionValueSet(); // paramSaStage.ParamName = "@param" + paramIndex; // paramIndex = paramIndex + 1; // paramSaStage.DataType = "char"; // paramSaStage.ParamValue = SA; // paramList.Add(paramSaStage); // sqlLineUpdateAllStringBuilder.Append("DELETE FROM [Dashboard_StageGoal] WHERE Stage=") //.Append(paramSaStage.ParamName).Append(" ") //.Append("INSERT INTO [Dashboard_StageGoal] ([Stage],[Goal]) ") //.Append("SELECT a.Stage, SUM(a.OutputTarget) as GoalNum ") //.Append("FROM (SELECT DISTINCT [Dashboard_Line_Target].[Line] ") //.Append(",[Dashboard_Line_Target].[OutputTarget],[Line].Stage ") //.Append("FROM [Dashboard_Line_Target] INNER JOIN [Line] ") //.Append("ON [Dashboard_Line_Target].[Line]=[Line].[Line] ") //.Append("WHERE [Line].Stage=") //.Append(paramSaStage.ParamName).Append(" ) AS a GROUP BY a.Stage "); for (int j = 0; j < lineItem.WindowLineFamilyInfos.Count; j++) { WindowLineFamilyInfo stationItem = lineItem.WindowLineFamilyInfos[j]; ConditionValueSet paramFamilyTargetId = new ConditionValueSet(); paramFamilyTargetId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramFamilyTargetId.DataType = "char(32)"; paramFamilyTargetId.ParamValue = DatabaseUtil.GetUUID(); paramList.Add(paramFamilyTargetId); ConditionValueSet paramFamilyId = new ConditionValueSet(); paramFamilyId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramFamilyId.DataType = "varchar"; paramFamilyId.ParamValue = GeneralUtil.Null2String(stationItem.Family); paramList.Add(paramFamilyId); ConditionValueSet paramSeries = new ConditionValueSet(); paramSeries.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramSeries.DataType = "varchar"; paramSeries.ParamValue = GeneralUtil.Null2String(stationItem.Series); paramList.Add(paramSeries); ConditionValueSet paramPlan = new ConditionValueSet(); paramPlan.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramPlan.DataType = "int"; paramPlan.ParamValue = stationItem.PlanQty; paramList.Add(paramPlan); ConditionValueSet paramStationOrder = new ConditionValueSet(); paramStationOrder.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStationOrder.DataType = "int"; paramStationOrder.ParamValue = j.ToString(); paramList.Add(paramStationOrder); sqlStationStringBuilder.Append("INSERT INTO [Dashboard_Family_Target] ") .Append("([ID]") .Append(",[Line]") .Append(",[WindowsID]") .Append(",[Family]") .Append(",[Series]") .Append(",[Plan]") .Append(",[Order]) ") .Append("VALUES (") .Append(paramFamilyTargetId.ParamName).Append(",") .Append(paramLineId.ParamName).Append(",") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramFamilyId.ParamName).Append(",") .Append(paramSeries.ParamName).Append(",") .Append(paramPlan.ParamName).Append(",") .Append(paramStationOrder.ParamName) .Append(") "); } } sqlString = sqlStringBuilder.ToString() + sqlStageStringBuilder.ToString() + sqlLineStringBuilder.ToString() + sqlStationStringBuilder.ToString() + sqlStageUpdateAllStringBuilder.ToString() + sqlLineUpdateAllStringBuilder.ToString(); DatabaseUtil.ExecSqlNonQueryWithParam(sqlString, connectString, paramList); return windowId; }
//winId: window id //lineId: LineID,用户Line 表 的id ok private static List<WindowLineFamilyInfo> GetEditStationListByLineAndWinId(String WindowId, String lineId) { List<WindowLineFamilyInfo> result = new List<WindowLineFamilyInfo>(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = new StringBuilder() .Append("SELECT [Dashboard_Family_Target].[ID] AS FamilyTargetId") .Append(",RTRIM(LTRIM([Line].Descr)) AS LineName") .Append(",[Dashboard_Family_Target].[WindowsID] AS [WindowsID]") .Append(",RTRIM(LTRIM([Dashboard_Family_Target].[Family])) AS [Family]") .Append(",[Dashboard_Family_Target].[Series] AS [Series]") .Append(",[Dashboard_Family_Target].[Plan] AS [Plan]") .Append(",[Dashboard_Family_Target].[Order] AS SortOrder ") .Append("FROM [Dashboard_Family_Target] ") .Append("INNER JOIN Line ON Dashboard_Family_Target.Line = Line.Line ") .Append("where [Dashboard_Family_Target].[WindowsID]=@param1 AND RTRIM(LTRIM([Dashboard_Family_Target].[Line]))=@param2 ") .Append("ORDER BY SortOrder").ToString(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = WindowId; paramList.Add(param1); //!!varchar(32) ConditionValueSet param2 = new ConditionValueSet(); param2.ParamName = "@param2"; param2.DataType = "char(10)"; param2.ParamValue = lineId; paramList.Add(param2); DataTable StationResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); for (int i = 0; i < StationResult.Rows.Count; i++) { WindowLineFamilyInfo StationInfo = new WindowLineFamilyInfo(); StationInfo.FamilyTargetId = GeneralUtil.Null2String(StationResult.Rows[i]["FamilyTargetId"]); StationInfo.Line = GeneralUtil.Null2String(StationResult.Rows[i]["LineName"]); StationInfo.Family = GeneralUtil.Null2String(StationResult.Rows[i]["Family"]); StationInfo.Series = GeneralUtil.Null2String(StationResult.Rows[i]["Series"]); StationInfo.PlanQty = GeneralUtil.Null2String(StationResult.Rows[i]["Plan"]); StationInfo.Order = GeneralUtil.Null2String(StationResult.Rows[i]["SortOrder"]); result.Add(StationInfo); } return result; }
//取Stage下line信息 //Id: window id private static DataTable GetEditLineListByWinId(String WindowId) { DataTable result = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Line_Target.ID AS LineTargetId, RTRIM(LTRIM(Dashboard_Line_Target.Line)) AS LineId, RTRIM(LTRIM([Dashboard_Stage].Stage)) AS SatgeName, RTRIM(LTRIM(Line.Descr)) AS LineName, Dashboard_Line_Target.FPYTarget AS FPYTarget,Dashboard_Line_Target.FPYAlert AS FPYAlert, Dashboard_Line_Target.OutputTarget AS OutputTarget, Dashboard_Line_Target.StartWorkTime AS StartWorkTime, Dashboard_Line_Target.StopWorkTime AS StopWorkTime, Dashboard_Line_Target.IsStationDsp AS IsStationDsp,Dashboard_Line_Target.[Shift],Dashboard_Line_Target.[FmlDspField], Dashboard_Line_Target.[Order] AS SortOrder FROM [Dashboard_Stage] INNER JOIN Dashboard_Stage_Target ON [Dashboard_Stage].Stage = Dashboard_Stage_Target.Stage INNER JOIN Dashboard_Window ON Dashboard_Stage_Target.WindowID = Dashboard_Window.ID INNER JOIN Dashboard_Line_Target ON Dashboard_Window.ID = Dashboard_Line_Target.WindowsID LEFT OUTER JOIN Line ON Dashboard_Line_Target.Line = Line.Line where Dashboard_Window.ID=@param1 order by SortOrder"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = WindowId; paramList.Add(param1); result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
//ok private static DataTable GetEditDashboardMainByWinId(String windowId) { DataTable result = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = new StringBuilder().Append("SELECT Dashboard_Window.ID AS ID, Dashboard_Window.WindowName AS WindowName, Dashboard_Window.DisplayName AS DisplayName,") .Append("Dashboard_Window.AlertMessage AS AlertMessage, Dashboard_Window.RefreshTime AS RefreshTime,") .Append("Dashboard_Window.DataSourceType AS DataSourceType, Dashboard_Stage_Target.ID AS StageTargetId, RTRIM(LTRIM([Dashboard_Stage].Stage)) AS StageId,") .Append("RTRIM(LTRIM([Dashboard_Stage].Stage)) AS StageName, Dashboard_Window.IsStageDsp AS IsStageDsp,") .Append("Dashboard_Stage_Target.StartWorkTime AS StartWorkTime, Dashboard_Stage_Target.StopWorkTime AS StopWorkTime,") .Append("Dashboard_Stage_Target.DisplayFields AS DisplayFields, Dashboard_Window.Cdt AS Cdt,") .Append("Dashboard_Window.Udt AS Udt, Dashboard_Window.Editor AS Editor FROM Dashboard_Window ") .Append("INNER JOIN Dashboard_Stage_Target ON Dashboard_Window.ID = Dashboard_Stage_Target.WindowID ") .Append("LEFT OUTER JOIN Dashboard_Stage ON Dashboard_Stage_Target.Stage = [Dashboard_Stage].Stage where Dashboard_Window.ID=@param1") .ToString(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = windowId; paramList.Add(param1); result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
//取得Line相关的Station数据显示列表,按order排序 //参数winId: window id //lineId: LineID,用户Line 表 的id private static List<StationShowingInfo> GetShowInfoStationListByLineAndWinId(String winId, String lineId, String stationDataTableName, ref double lineFpy) { List<StationShowingInfo> result = new List<StationShowingInfo>(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Window.ID, RTRIM(LTRIM(Dashboard_Line_Target.Line)) AS Line, RTRIM(LTRIM(Dashboard_Station_Target.Station))+' '+RTRIM(LTRIM(ISNULL(Station.Name,''))) AS Station, Dashboard_Station_Target.YieldTarget, Dashboard_Station_Target.FactorOfFPY, Dashboard_Station_Target.DisplayFields, Dashboard_Station_Target.[Order], ISNULL(Dashboard_Station_Data.Quantity,0) AS Quantity, ISNULL(Dashboard_Station_Data.Defect,0) AS Defect, ISNULL(Dashboard_Station_Data.WIP,0) AS WIP FROM Dashboard_Window INNER JOIN Dashboard_Line_Target ON Dashboard_Window.ID = Dashboard_Line_Target.WindowsID INNER JOIN Dashboard_Station_Target ON Dashboard_Window.ID = Dashboard_Station_Target.WindowsID AND Dashboard_Line_Target.Line = Dashboard_Station_Target.Line INNER JOIN Station ON Station.Station=Dashboard_Station_Target.Station LEFT OUTER JOIN " + stationDataTableName + " AS Dashboard_Station_Data ON Dashboard_Station_Target.Station = Dashboard_Station_Data.Station AND Dashboard_Station_Target.Line = Dashboard_Station_Data.Line WHERE Dashboard_Window.ID=@param1 AND RTRIM(LTRIM(Dashboard_Line_Target.Line))=@param2 ORDER BY Dashboard_Station_Target.[Order] "; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = winId; paramList.Add(param1); //!!varchar(32) ConditionValueSet param2 = new ConditionValueSet(); param2.ParamName = "@param2"; param2.DataType = "char(10)"; param2.ParamValue = lineId; paramList.Add(param2); DataTable StationResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); for (int i = 0; i < StationResult.Rows.Count; i++) { StationShowingInfo StationInfo = new StationShowingInfo(); String DisplayFields = StationResult.Rows[i]["DisplayFields"].ToString(); String YieldTarget = StationResult.Rows[i]["YieldTarget"].ToString(); String Quantity = StationResult.Rows[i]["Quantity"].ToString(); String Defect = StationResult.Rows[i]["Defect"].ToString(); String WIP = StationResult.Rows[i]["WIP"].ToString(); String FactorOfFPY = StationResult.Rows[i]["FactorOfFPY"].ToString(); StationInfo.StationName = StationResult.Rows[i]["Station"].ToString(); StationDisplayFieldInfo displayFieldInfos = new StationDisplayFieldInfo(); if (DisplayFields != "") { displayFieldInfos = DashboardManager.StationDisplayFieldsString2Struct(DisplayFields); } StationInfo.IsQuantityDsp = displayFieldInfos.QuantityDsp; StationInfo.IsDefectDsp = displayFieldInfos.DefectDsp; StationInfo.IsWIPDsp = displayFieldInfos.WIPDsp; StationInfo.IsYieldRateDsp = displayFieldInfos.YieldRateDsp; StationInfo.Defect = Defect; StationInfo.Quantity = Quantity; StationInfo.WIP = WIP; double YieldRate = 100; //用以判断产量为0 double tmpValueQuantity = 0; try { tmpValueQuantity = double.Parse(Quantity); if (tmpValueQuantity != 0) { YieldRate = (1 - (double.Parse(Defect) / tmpValueQuantity)) * 100; } } catch { //不需要处理, Rate为0 } //!!! need check if (FactorOfFPY == "True" && tmpValueQuantity != 0) { lineFpy = YieldRate * lineFpy / 100; } StationInfo.YieldRate=YieldRate.ToString("0.00"); String YieldTargetformat = "0.00"; try { YieldTargetformat = double.Parse(YieldTarget).ToString("0.00"); } catch { //不需要处理 YieldTargetforma="0.00"; } //产量为0时,stationShowingInfo.yieldRate字串为空字串,不显示文字 if (double.Parse(StationInfo.YieldRate) >= double.Parse(YieldTargetformat)) { ////产量为0时,stationShowingInfo.yieldRate字串为空字串,不显示文字 //if (tmpValueQuantity == 0) //{ // StationInfo.YieldRate = ""; //} StationInfo.IsYieldRateOk = "True"; } else { StationInfo.IsYieldRateOk = "False"; } result.Add(StationInfo); } return result; }
//参数Id: window id private static List<LineShowingInfo> GetShowInfoLineListByWinId(String windowId,String LineDataTableName,String StationDataTableName,DateTime NowTime) { List<LineShowingInfo> result = new List<LineShowingInfo>(); DataTable LineResult = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); //String sqlString = "SELECT Dashboard_Window.ID, ISNULL(Dashboard_Line_Target.FPYTarget,0) AS FPYTarget, ISNULL(Dashboard_Line_Target.OutputTarget,0) AS OutputTarget, Dashboard_Line_Target.StartWorkTime, Dashboard_Line_Target.IsStationDsp, Dashboard_Line_Target.StopWorkTime, Dashboard_Line_Target.[Order] AS [Order], RTRIM(LTRIM(Dashboard_Line_Target.Line)) AS Line, ISNULL(LineData.Input,0) AS Input, ISNULL(LineData.Output,0) AS Output, ISNULL(LineData.DefQty,0) AS DefQty FROM Dashboard_Window INNER JOIN Dashboard_Line_Target ON Dashboard_Window.ID = Dashboard_Line_Target.WindowsID LEFT OUTER JOIN (SELECT RTRIM(LTRIM(Line.Line)) AS Line, Dashboard_Line_Data.Input, Dashboard_Line_Data.Output, Dashboard_Line_Data.DefQty FROM Line INNER JOIN " String sqlString = "SELECT Dashboard_Window.ID, ISNULL(Dashboard_Line_Target.FPYTarget,0) AS FPYTarget,ISNULL(Dashboard_Line_Target.FPYAlert,0) AS FPYAlert, ISNULL(Dashboard_Line_Target.OutputTarget,0) AS OutputTarget, Dashboard_Line_Target.StartWorkTime, Dashboard_Line_Target.IsStationDsp, Dashboard_Line_Target.StopWorkTime, Dashboard_Line_Target.[Order] AS [Order], RTRIM(LTRIM(Dashboard_Line_Target.Line)) AS LineId, RTRIM(LTRIM(Line.Descr)) AS Line, ISNULL(LineData.Input,0) AS Input, ISNULL(LineData.Output,0) AS Output FROM Dashboard_Window INNER JOIN Dashboard_Line_Target ON Dashboard_Window.ID = Dashboard_Line_Target.WindowsID INNER JOIN [Line] ON [Line].Line=[Dashboard_Line_Target].Line LEFT OUTER JOIN (SELECT RTRIM(LTRIM(Line.Line)) AS Line, Dashboard_Line_Data.Input, Dashboard_Line_Data.Output FROM Line INNER JOIN " + LineDataTableName + " AS Dashboard_Line_Data ON Dashboard_Line_Data.Line = Line.Line) AS LineData ON Dashboard_Line_Target.Line=LineData.Line WHERE Dashboard_Window.ID=@param1 ORDER BY Dashboard_Line_Target.[Order]"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = windowId; paramList.Add(param1); LineResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); for (int i = 0; i < LineResult.Rows.Count; i++) { LineShowingInfo lineInfo = new LineShowingInfo(); String FPYTarget= GeneralUtil.Null2String(LineResult.Rows[i]["FPYTarget"]); String FPYAlert = GeneralUtil.Null2String(LineResult.Rows[i]["FPYAlert"]); String OutputTarget= GeneralUtil.Null2String(LineResult.Rows[i]["OutputTarget"]); String StartWork = GeneralUtil.Null2String(LineResult.Rows[i]["StartWorkTime"]); String StopWork = GeneralUtil.Null2String(LineResult.Rows[i]["StopWorkTime"]); String Line=GeneralUtil.Null2String(LineResult.Rows[i]["Line"]); String Input=GeneralUtil.Null2String(LineResult.Rows[i]["Input"]); String Output=GeneralUtil.Null2String(LineResult.Rows[i]["Output"]); //String DefQty=GeneralUtil.Null2String(LineResult.Rows[i]["DefQty"]); String LineId = GeneralUtil.Null2String(LineResult.Rows[i]["LineId"]); lineInfo.LineName=Line; lineInfo.IsStationDsp=GeneralUtil.Null2String(LineResult.Rows[i]["IsStationDsp"]); //不在工作时间内的内容在这里不拿掉,使得客户端计算当前显示行简便 if (IsOnWorktime(StartWork, StopWork, NowTime) == true) { lineInfo.IsInWorkTime = "True"; } else { lineInfo.IsInWorkTime = "False"; } double FPY = 1; //!!! need modify //try //{ // float tmpValueOutput = float.Parse(Output); // if (tmpValueOutput != 0) // { // FPY = (1 - (float.Parse(DefQty) / tmpValueOutput)) * 100; // } //} //catch //{ // //不需要处理,FPY=0 //} List<StationShowingInfo> StationResult = GetShowInfoStationListByLineAndWinId(windowId, LineId, StationDataTableName, ref FPY); FPY = FPY * 100; lineInfo.FPY = FPY.ToString("0.00"); String FPYTargetformat = double.Parse(FPYTarget).ToString("0.00"); String FPYAlertformat = double.Parse(FPYAlert).ToString("0.00"); if (double.Parse(lineInfo.FPY) >= double.Parse(FPYTargetformat)) { lineInfo.IsFPYOk="True"; } else if (double.Parse(lineInfo.FPY) < double.Parse(FPYTargetformat) && double.Parse(lineInfo.FPY) >= double.Parse(FPYAlertformat)) { //合格 lineInfo.IsFPYOk="JustPass"; } else { lineInfo.IsFPYOk="False"; } //问题: 如果OutputTarget=0, 当前的rate算是0还是1,询问后定为100% int Rate = 100; try { double tmpValueOutputTarget = double.Parse(OutputTarget); if (tmpValueOutputTarget != 0) { Rate = (int)(double.Parse(Output) * 100 / tmpValueOutputTarget); } } catch { //不需要处理, Rate为0 } int TargetRate=GetTargetRate(StartWork, StopWork, NowTime); if(Rate>=TargetRate) { lineInfo.IsRateOk="True"; } else { lineInfo.IsRateOk="False"; } lineInfo.Rate=Rate.ToString(); lineInfo.TargetRate=TargetRate.ToString(); lineInfo.Output=Output; lineInfo.Target=OutputTarget; lineInfo .StationShowingInfos=StationResult; result.Add(lineInfo); } return result; }
public static DataTable GetStationListByLineEclipse(String lineId, List<String> existStations) { String connectString = DatabaseUtil.GetConnectionString(); StringBuilder sqlStringBuilder = new StringBuilder(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "char(10)"; param1.ParamName = "@param0"; param1.ParamValue = GeneralUtil.Null2String(lineId); paramList.Add(param1); sqlStringBuilder.Append("SELECT DISTINCT RTRIM(LTRIM(Station.Station)) AS selectId, RTRIM(LTRIM(Station.Station))+' '+RTRIM(LTRIM(ISNULL(Station.Name,''))) AS selectValue FROM Line_Station INNER JOIN Station ON Line_Station.Station = Station.Station WHERE RTRIM(LTRIM(Line_Station.Line))=@param0 "); int paramIndex = 1; for (int i = 0; i < existStations.Count; i++) { ConditionValueSet paramItem = new ConditionValueSet(); String paramName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramItem.ParamName = paramName; paramItem.DataType = "char(10)"; paramItem.ParamValue = GeneralUtil.Null2String(existStations[i]); paramList.Add(paramItem); if (i == 0) { sqlStringBuilder.Append("AND RTRIM(LTRIM(Station.Station)) NOT IN (") .Append(paramName); } else { sqlStringBuilder.Append(",").Append(paramName); } if (i == existStations.Count - 1) { sqlStringBuilder.Append(")"); } } sqlStringBuilder.Append("ORDER BY selectValue"); String sqlString = sqlStringBuilder.ToString(); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
public static DataTable GetStationListByLine(String lineId) { String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT DISTINCT RTRIM(LTRIM(Station.Station)) AS selectId, RTRIM(LTRIM(Station.Station))+' '+RTRIM(LTRIM(ISNULL(Station.Name,''))) AS selectValue FROM Line_Station INNER JOIN Station ON Line_Station.Station = Station.Station WHERE RTRIM(LTRIM(Line_Station.Line))=@param1 ORDER BY selectValue"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "char(10)"; param1.ParamName = "@param1"; param1.ParamValue = GeneralUtil.Null2String(lineId); paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
public static DataTable GetLineListByStageEclipse(String stageType, List<String> existLines) { //genutil.TLog<TabDef>.DebugInfo("GetPublishListInIntegrationFile", dashboardTab); String connectString = DatabaseUtil.GetConnectionString(); StringBuilder sqlStringBuilder = new StringBuilder(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); //ConditionValueSet param1 = new ConditionValueSet(); //param1.DataType = "char(10)"; //param1.ParamName = "@param0"; //param1.ParamValue = GeneralUtil.Null2String(stageId); //paramList.Add(param1); if (stageType == Constants.SA_STAGE.ToString()) { sqlStringBuilder.Append("SELECT DISTINCT RTRIM(LTRIM(Line)) AS selectId, RTRIM(LTRIM(Descr)) As selectValue ") .Append("FROM Line ") .Append("INNER JOIN Dashboard_Stage_Base ON Line.Stage=Dashboard_Stage_Base.Stage ") .Append("WHERE Dashboard_Stage_Base.Stage_Type=3 "); } else if (stageType == Constants.SMT_STAGE.ToString() ) { sqlStringBuilder.Append("SELECT DISTINCT RTRIM(LTRIM(Line)) AS selectId, RTRIM(LTRIM(Descr)) As selectValue ") .Append("FROM Line ") .Append("INNER JOIN Dashboard_Stage_Base ON Line.Stage=Dashboard_Stage_Base.Stage ") .Append("WHERE Dashboard_Stage_Base.Stage_Type=4 "); } int paramIndex = 1; for (int i = 0; i < existLines.Count; i++) { ConditionValueSet paramItem = new ConditionValueSet(); String paramName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramItem.ParamName = paramName; paramItem.DataType = "char(10)"; paramItem.ParamValue = GeneralUtil.Null2String(existLines[i]); paramList.Add(paramItem); if (i == 0) { sqlStringBuilder.Append("AND RTRIM(LTRIM(Line)) NOT IN (") .Append(paramName); } else { sqlStringBuilder.Append(",").Append(paramName); } if (i == existLines.Count - 1) { sqlStringBuilder.Append(")"); } } sqlStringBuilder.Append("ORDER BY selectValue"); String sqlString = sqlStringBuilder.ToString(); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
//检查dashobard是否被移除 ok private static Boolean CheckWindowSettingExist(String windowId) { String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Window.ID AS ID, Dashboard_Window.WindowName AS WindowName FROM Dashboard_Window where Dashboard_Window.ID=@param1"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = windowId; paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (result.Rows.Count <= 0) { ExceptionManager.Throw("This edit item has been removed."); } return true; }
//参数Id: window id //内部取得当前时间Now ok private static DataTable GetShowInfoDashboardMainByWinId(String id, String StageDataTableName) { DataTable result = new DataTable(); String connectString = DatabaseUtil.GetConnectionString(); StringBuilder sqlStringBuilder = new StringBuilder(); sqlStringBuilder.Append("SELECT Dashboard_Window.ID AS WindowId,") .Append("Dashboard_Window.WindowName AS WindowName,") .Append("Dashboard_Window.DisplayName AS DisplayName,") .Append("Dashboard_Window.AlertMessage AS AlertMessage,") .Append("Dashboard_Window.RefreshTime AS RefreshTime,") .Append("Dashboard_Window.Udt AS Udt,") .Append("Dashboard_Stage_Target.Stage AS StageId,") .Append("Dashboard_Window.DataSourceType AS DataSourceType,") .Append("Dashboard_Window.IsStageDsp AS IsStageDsp,") .Append("Dashboard_Stage_Target.DisplayFields AS DisplayFields,") .Append("ISNULL(Dashboard_Stage_Data.SMTInput,0) AS SMTInput,") .Append("ISNULL(Dashboard_Stage_Data.SMTOutput,0) AS SMTOutput,") .Append("ISNULL(Dashboard_Stage_Data.SAInput,0) AS SAInput,") .Append("ISNULL(Dashboard_Stage_Data.SAOutput,0) AS SAOutput,") .Append("ISNULL(Dashboard_Family_Target.Goal,0) AS Goal,") .Append("Dashboard_Stage_Target.StartWorkTime AS StartWorkTime,") .Append("Dashboard_Stage_Target.StopWorkTime AS StopWorkTime,") .Append("[Dashboard_Stage].[Type] AS StageType ") .Append("FROM Dashboard_Window ") .Append("INNER JOIN Dashboard_Stage_Target ON Dashboard_Window.ID = Dashboard_Stage_Target.WindowID ") .Append("INNER JOIN [Dashboard_Stage] ON Dashboard_Stage_Target.Stage=[Dashboard_Stage].Stage ") .Append("Left OUTER JOIN ") .Append(StageDataTableName) .Append(" AS Dashboard_Stage_Data ON Dashboard_Stage_Target.Stage = Dashboard_Stage_Data.Stage ") .Append("Left OUTER JOIN ") .Append("(SELECT SUM([Plan]) AS Goal, WindowsID FROM Dashboard_Family_Target WHERE WindowsID = @param1 GROUP BY WindowsID) AS Dashboard_Family_Target ") .Append("ON Dashboard_Window.ID=Dashboard_Family_Target.WindowsID ") .Append("WHERE Dashboard_Window.ID=@param1 "); String sqlString = sqlStringBuilder.ToString(); List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = id; paramList.Add(param1); result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
//检查window的名称是否重复 ok private static Boolean CheckWindowNameSame(String windowName, String windowId) { String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Window.ID AS ID, Dashboard_Window.WindowName AS WindowName FROM Dashboard_Window where Dashboard_Window.ID <>@param1 and Dashboard_Window.WindowName=@param2"; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = windowId; paramList.Add(param1); ConditionValueSet param2 = new ConditionValueSet(); param2.ParamName = "@param2"; param2.DataType = "nvarchar(128)"; param2.ParamValue = windowName; paramList.Add(param2); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); if (result.Rows.Count > 0) { ExceptionManager.Throw("The window name already exists."); } return true; }
//取得Line相关的Station数据显示列表,按order排序 ok //参数winId: window id //lineId: LineID,用户Line 表 的id private static List<FamilyShowingInfo> GetShowInfoStationListByLineAndWinId(String winId, String lineId, String stationDataTableName, String fmlDspField, String IsSaStage) { List<FamilyShowingInfo> result = new List<FamilyShowingInfo>(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = ""; StringBuilder sqlStringBuilder = new StringBuilder(); sqlStringBuilder.Append("SELECT Dashboard_Family_Target.WindowsID, ") .Append("RTRIM(LTRIM(Dashboard_Family_Target.Line)) AS Line, ") .Append("RTRIM(LTRIM(Dashboard_Family_Target.Family)) AS Family, ") .Append("RTRIM(LTRIM(Dashboard_Family_Target.Series)) AS Series, ") .Append("ISNULL(Dashboard_Family_Target.[Plan],0) AS [Plan], ") .Append("ISNULL(Dashboard_Family_Data.Output,0) AS Output, ") .Append("ISNULL(Dashboard_Family_Data.DefectQty,0) AS DefectQty, ") .Append("ISNULL(Dashboard_Family_Data.Input,0) AS Input, ") .Append("ISNULL(Dashboard_Family_Data.AOI_Output,0) AS AOI_Output, ") .Append("ISNULL(Dashboard_Family_Data.AOI_Defect,0) AS AOI_Defect, ") .Append("ISNULL(Dashboard_Family_Data.ICT_Input,0) AS ICT_Input, ") .Append("ISNULL(Dashboard_Family_Data.ICT_Defect,0) AS ICT_Defect ") .Append("FROM Dashboard_Family_Target ") .Append("LEFT OUTER JOIN ") .Append(stationDataTableName) .Append(" AS [Dashboard_Family_Data] ") .Append("ON [Dashboard_Family_Target].Line=[Dashboard_Family_Data].Line ") .Append("AND [Dashboard_Family_Target].Family=[Dashboard_Family_Data].Family ") .Append("AND [Dashboard_Family_Target].Series=[Dashboard_Family_Data].Series ") .Append("WHERE Dashboard_Family_Target.WindowsID=@param1 AND ") .Append("RTRIM(LTRIM(Dashboard_Family_Target.Line))=@param2 ") .Append("ORDER BY Dashboard_Family_Target.[Order]"); sqlString = sqlStringBuilder.ToString(); //stationDataTableNam List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = winId; paramList.Add(param1); //!!varchar(32) ConditionValueSet param2 = new ConditionValueSet(); param2.ParamName = "@param2"; param2.DataType = "char(10)"; param2.ParamValue = lineId; paramList.Add(param2); DataTable StationResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); int sumPlan = 0; int sumOutput = 0; int sumDefect = 0; int sumInput = 0; int sumICTInput = 0; int sumICTDefect = 0; int sumAOIOutput = 0; int sumAOIDefect = 0; SaStationDisplayFieldInfo displayFieldInfosSa = new SaStationDisplayFieldInfo(); SmtStationDisplayFieldInfo displayFieldInfosSmt = new SmtStationDisplayFieldInfo(); if (IsSaStage == "True") { if (fmlDspField != "") { displayFieldInfosSa = DashboardManager.SaStationDisplayFieldsString2Struct(fmlDspField); } } else //Smt { if (fmlDspField != "") { displayFieldInfosSmt = DashboardManager.SmtStationDisplayFieldsString2Struct(fmlDspField); } } for (int i = 0; i < StationResult.Rows.Count; i++) { FamilyShowingInfo StationInfo = new FamilyShowingInfo(); String Family = GeneralUtil.Null2String(StationResult.Rows[i]["Family"]); String Series = GeneralUtil.Null2String(StationResult.Rows[i]["Series"]); String Plan = GeneralUtil.Null2String(StationResult.Rows[i]["Plan"]); String Output = GeneralUtil.Null2String(StationResult.Rows[i]["Output"]); String DefectQty = GeneralUtil.Null2String(StationResult.Rows[i]["DefectQty"]); String Input = GeneralUtil.Null2String(StationResult.Rows[i]["Input"]); String AOI_Output = GeneralUtil.Null2String(StationResult.Rows[i]["AOI_Output"]); String AOI_Defect = GeneralUtil.Null2String(StationResult.Rows[i]["AOI_Defect"]); String ICT_Input = GeneralUtil.Null2String(StationResult.Rows[i]["ICT_Input"]); String ICT_Defect = GeneralUtil.Null2String(StationResult.Rows[i]["ICT_Defect"]); StationInfo.AOIDefect = AOI_Defect; StationInfo.AOIOutput = AOI_Output; StationInfo.Defect = DefectQty; StationInfo.Family = Family; StationInfo.ICTDefect = ICT_Defect; StationInfo.ICTInput = ICT_Input; //StationInfo.ICTYield= StationInfo.Input = Input; StationInfo.Output = Output; StationInfo.Plan = Plan; //StationInfo.Progress StationInfo.Series = Series; //StationInfo.YieldRate sumPlan += Int32.Parse(Plan); sumOutput += Int32.Parse(Output); sumDefect += Int32.Parse(DefectQty); sumInput += Int32.Parse(Input); sumICTInput += Int32.Parse(ICT_Input); sumICTDefect += Int32.Parse(ICT_Defect); sumAOIOutput += Int32.Parse(AOI_Output); sumAOIDefect += Int32.Parse(AOI_Defect); if (IsSaStage == "True") { StationInfo.IsDefectDsp = displayFieldInfosSa.IsDefectDsp; StationInfo.IsICTDefectDsp = displayFieldInfosSa.IsICTDefectDsp; StationInfo.IsICTInputDsp = displayFieldInfosSa.IsICTInputDsp; StationInfo.IsICTYieldRateDsp = displayFieldInfosSa.IsICTYieldRateDsp; StationInfo.IsInputDsp = displayFieldInfosSa.IsInputDsp; StationInfo.IsYieldRateDsp = displayFieldInfosSa.IsYieldRateDsp; } else //Smt { StationInfo.IsDefectDsp = displayFieldInfosSmt.IsDefectDsp; StationInfo.IsAOIDefectDsp = displayFieldInfosSmt.IsAOIDefectDsp; StationInfo.IsAOIOutputDsp = displayFieldInfosSmt.IsAOIOutputDsp; StationInfo.IsInputDsp = displayFieldInfosSmt.IsInputDsp; StationInfo.IsYieldRateDsp = displayFieldInfosSmt.IsYieldRateDsp; } string ICTYield = GetYieldRate(ICT_Input, ICT_Defect); StationInfo.ICTYield = ICTYield; string YieldRate = GetYieldRate(Output, DefectQty); StationInfo.YieldRate = YieldRate; //算Progress string Progress = GetProgress(Plan, Output); StationInfo.Progress = Progress; result.Add(StationInfo); } FamilyShowingInfo StationSumInfo = new FamilyShowingInfo(); if (IsSaStage == "True") { StationSumInfo.IsDefectDsp = displayFieldInfosSa.IsDefectDsp; StationSumInfo.IsICTDefectDsp = displayFieldInfosSa.IsICTDefectDsp; StationSumInfo.IsICTInputDsp = displayFieldInfosSa.IsICTInputDsp; StationSumInfo.IsICTYieldRateDsp = displayFieldInfosSa.IsICTYieldRateDsp; StationSumInfo.IsInputDsp = displayFieldInfosSa.IsInputDsp; StationSumInfo.IsYieldRateDsp = displayFieldInfosSa.IsYieldRateDsp; } else //Smt { StationSumInfo.IsDefectDsp = displayFieldInfosSmt.IsDefectDsp; StationSumInfo.IsAOIDefectDsp = displayFieldInfosSmt.IsAOIDefectDsp; StationSumInfo.IsAOIOutputDsp = displayFieldInfosSmt.IsAOIOutputDsp; StationSumInfo.IsInputDsp = displayFieldInfosSmt.IsInputDsp; StationSumInfo.IsYieldRateDsp = displayFieldInfosSmt.IsYieldRateDsp; } if (StationResult.Rows.Count > 0) { StationSumInfo.Plan = sumPlan.ToString(); StationSumInfo.Output = sumOutput.ToString(); StationSumInfo.Defect = sumDefect.ToString(); StationSumInfo.Input = sumInput.ToString(); StationSumInfo.ICTInput = sumICTInput.ToString(); StationSumInfo.ICTDefect = sumICTDefect.ToString(); StationSumInfo.AOIOutput = sumAOIOutput.ToString(); StationSumInfo.AOIDefect = sumAOIDefect.ToString(); StationSumInfo.ICTYield = GetYieldRate(StationSumInfo.ICTInput, StationSumInfo.ICTDefect); StationSumInfo.YieldRate = GetYieldRate(StationSumInfo.Output, StationSumInfo.Defect); StationSumInfo.Progress = GetProgress(StationSumInfo.Plan, StationSumInfo.Output); StationSumInfo.Family = "Total"; StationSumInfo.Series = " "; } else { StationSumInfo.Plan = "0"; StationSumInfo.Output = "0"; StationSumInfo.Defect = "0"; StationSumInfo.Input = "0"; StationSumInfo.ICTInput = "0"; StationSumInfo.ICTDefect = "0"; StationSumInfo.AOIOutput = "0"; StationSumInfo.AOIDefect = "0"; StationSumInfo.ICTYield = ""; StationSumInfo.YieldRate = ""; StationSumInfo.Progress = ""; StationSumInfo.Family = "Total"; StationSumInfo.Series = " "; } result.Add(StationSumInfo); return result; }
public static DataTable GetSeriesListByFamily(String familyId, List<String> FamilyList, List<String> SeriesList) { //List<String> FamilyList=new List<string> (); //List<String> SeriesList = new List<string>(); String connectString = DatabaseUtil.GetConnectionString(); StringBuilder sqlStringBuilder = new StringBuilder() .Append("SELECT DISTINCT a.Series AS selectId,") .Append("a.Series As selectValue ") .Append("FROM ") .Append("(SELECT RTRIM(LTRIM([Family])) AS [Family]") .Append(",RTRIM(LTRIM([Series])) AS [Series] ") .Append("FROM ") .Append(DashboardCommon.FamilySeries).Append(" "); //使用的类似view, 可能取得数据的地方需要修改 List<ConditionValueSet> paramList = new List<ConditionValueSet>(); int paramIndex = 0; for (int i = 0; i < FamilyList.Count; i++) { ConditionValueSet paramFamily = new ConditionValueSet(); paramFamily.ParamName = "@param1" + paramIndex; paramFamily.DataType = "varchar"; paramFamily.ParamValue = FamilyList[i]; paramList.Add(paramFamily); ConditionValueSet paramSeries = new ConditionValueSet(); paramSeries.ParamName = "@param2" + paramIndex; paramSeries.DataType = "varchar"; paramSeries.ParamValue = SeriesList[i]; paramList.Add(paramSeries); paramIndex = paramIndex + 1; if (i == 0) { sqlStringBuilder.Append("WHERE ([Family]<>").Append(paramFamily.ParamName).Append(" OR [Series]<>").Append(paramSeries.ParamName).Append(") "); } else { sqlStringBuilder.Append("AND ([Family]<>").Append(paramFamily.ParamName).Append(" OR [Series]<>").Append(paramSeries.ParamName).Append(") "); } } sqlStringBuilder.Append(") AS a ") .Append("WHERE a.[Family]=@param1 ") .Append("ORDER BY selectValue "); String sqlString = sqlStringBuilder.ToString(); ConditionValueSet param1 = new ConditionValueSet(); param1.DataType = "varchar"; param1.ParamName = "@param1"; param1.ParamValue = GeneralUtil.Null2String(familyId); paramList.Add(param1); DataTable result = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); return result; }
public static void DeleteDashboardWindowSetting(String windowId) { String connectString = DatabaseUtil.GetConnectionString(); StringBuilder sqlStringBuilder = new StringBuilder(); windowId = GeneralUtil.Null2String(windowId); if (windowId != "") { List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet paramItem = new ConditionValueSet(); paramItem.ParamName = "@param"; paramItem.DataType = "char(32)"; paramItem.ParamValue = windowId; paramList.Add(paramItem); sqlStringBuilder.Append("DELETE FROM [Dashboard_Family_Target] WHERE WindowsID=").Append("@param").Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Line_Target] WHERE WindowsID=").Append("@param").Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Stage_Target] WHERE WindowID=").Append("@param").Append(" "); sqlStringBuilder.Append("DELETE FROM [Dashboard_Window] WHERE ID=").Append("@param").Append(" "); String sqlString = sqlStringBuilder.ToString(); DatabaseUtil.ExecSqlNonQueryWithParam(sqlString, connectString, paramList); } }
//winId: window id //lineId: LineID,用户Line 表 的id private static List<WindowLineStationInfo> GetEditStationListByLineAndWinId(String WindowId, String lineId) { List<WindowLineStationInfo> result = new List<WindowLineStationInfo>(); String connectString = DatabaseUtil.GetConnectionString(); String sqlString = "SELECT Dashboard_Station_Target.ID AS StationTargetId, RTRIM(LTRIM(Line.Descr)) AS LineName, RTRIM(LTRIM(Station.Station)) AS StationId, RTRIM(LTRIM(Station.Station))+' '+RTRIM(LTRIM(ISNULL(Station.Name,''))) AS StationName, Dashboard_Station_Target.DisplayFields AS DisplayFields, Dashboard_Station_Target.YieldTarget AS YieldTarget, Dashboard_Station_Target.FactorOfFPY AS FactorOfFPY, Dashboard_Station_Target.[Order] AS SortOrder FROM Dashboard_Window INNER JOIN Dashboard_Line_Target ON Dashboard_Window.ID = Dashboard_Line_Target.WindowsID INNER JOIN Line ON Dashboard_Line_Target.Line = Line.Line INNER JOIN Dashboard_Station_Target ON Dashboard_Window.ID = Dashboard_Station_Target.WindowsID AND Dashboard_Line_Target.Line = Dashboard_Station_Target.Line INNER JOIN Station ON Dashboard_Station_Target.Station = Station.Station where Dashboard_Window.ID=@param1 AND RTRIM(LTRIM(Dashboard_Line_Target.Line))=@param2 ORDER BY SortOrder "; List<ConditionValueSet> paramList = new List<ConditionValueSet>(); ConditionValueSet param1 = new ConditionValueSet(); param1.ParamName = "@param1"; param1.DataType = "char(32)"; param1.ParamValue = WindowId; paramList.Add(param1); //!!varchar(32) ConditionValueSet param2 = new ConditionValueSet(); param2.ParamName = "@param2"; param2.DataType = "char(10)"; param2.ParamValue = lineId; paramList.Add(param2); DataTable StationResult = DatabaseUtil.GetResultBySQL(sqlString, connectString, paramList); for (int i = 0; i < StationResult.Rows.Count; i++) { WindowLineStationInfo StationInfo = new WindowLineStationInfo(); StationInfo.StationTargetId = StationResult.Rows[i]["StationTargetId"].ToString(); StationInfo.Line = StationResult.Rows[i]["LineName"].ToString(); StationInfo.StationId = StationResult.Rows[i]["StationId"].ToString(); StationInfo.Station = StationResult.Rows[i]["StationName"].ToString(); StationInfo.DisplayFields = StationResult.Rows[i]["DisplayFields"].ToString(); StationDisplayFieldInfo displayFieldInfos = new StationDisplayFieldInfo(); if (StationInfo.DisplayFields != "") { displayFieldInfos = StationDisplayFieldsString2Struct(StationInfo.DisplayFields); } StationInfo.Quantity = displayFieldInfos.QuantityDsp; StationInfo.Defect = displayFieldInfos.DefectDsp; StationInfo.WIP = displayFieldInfos.WIPDsp; StationInfo.YieldRate = displayFieldInfos.YieldRateDsp; StationInfo.YieldTarget = StationResult.Rows[i]["YieldTarget"].ToString(); StationInfo.FactorOfFPY = StationResult.Rows[i]["FactorOfFPY"].ToString(); StationInfo.Order = StationResult.Rows[i]["SortOrder"].ToString(); result.Add(StationInfo); } return result; }