//取得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; }
//Display private static String Struct2StationDisplayFieldsString(StationDisplayFieldInfo DisplayFieldsInfo) { String result = ""; if (DisplayFieldsInfo.QuantityDsp.ToLower() == "true") { result += "QuantityDsp=True,"; } else { result += "QuantityDsp=False,"; } if (DisplayFieldsInfo.DefectDsp.ToLower() == "true") { result += "DefectDsp=True,"; } else { result += "DefectDsp=False,"; } if (DisplayFieldsInfo.WIPDsp.ToLower() == "true") { result += "WIPDsp=True,"; } else { result += "WIPDsp=False,"; } if (DisplayFieldsInfo.YieldRateDsp.ToLower() == "true") { result += "YieldRateDsp=True"; } else { result += "YieldRateDsp=False"; } return result; }
public static String SaveDashboardWindowSetting(DashboardWindowInfo windowInfo) { String connectString = DatabaseUtil.GetConnectionString(); String windowId = GeneralUtil.Null2String( windowInfo.WindowId); String windowName=GeneralUtil.Null2String( windowInfo.WindowName); 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_Station_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.IsDnDisplay = windowInfo.IsDnDisplay; stageDisplayFieldInfo.IsFaInputDisplay = windowInfo.IsFaInputDisplay; stageDisplayFieldInfo.IsFaOutputDisplay = windowInfo.IsFaOutputDisplay; stageDisplayFieldInfo.IsPaInputDisplay = windowInfo.IsPaInputDisplay; stageDisplayFieldInfo.IsPaOutputDisplay = windowInfo.IsPaOutputDisplay; stageDisplayFieldInfo.IsGoalDisplay = windowInfo.IsGoalDisplay; stageDisplayFieldInfo.IsSaInputDisplay = windowInfo.IsSaInputDisplay; stageDisplayFieldInfo.IsSaOutputDisplay = windowInfo.IsSaOutputDisplay; 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 paramIsStationDsp = new ConditionValueSet(); paramIsStationDsp.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramIsStationDsp.DataType = "bit"; paramIsStationDsp.ParamValue = lineItem.StationDisplay; paramList.Add(paramIsStationDsp); 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] ([ID],[WindowsID],[Line],[OutputTarget],[FPYTarget],[FPYAlert],[StartWorkTime],[StopWorkTime],[IsStationDsp],[Order])") .Append("VALUES (") .Append(paramLineTargetId.ParamName).Append(",") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramLineId.ParamName).Append(",") .Append(paramOutputTarget.ParamName).Append(",") .Append(paramFPYTarget.ParamName).Append(",") .Append(paramFPYAlert.ParamName).Append(",") .Append(paramLineStartWorkTime.ParamName).Append(",") .Append(paramLineStopWorkTime.ParamName).Append(",") .Append(paramIsStationDsp.ParamName).Append(",") .Append(paramLineOrder.ParamName ) .Append(") "); //存盘时,相同的line的基本数据同时变 sqlLineUpdateAllStringBuilder.Append("UPDATE [Dashboard_Line_Target] SET [StartWorkTime] = ") .Append(paramLineStartWorkTime.ParamName) .Append(",[StopWorkTime] =") .Append(paramLineStopWorkTime.ParamName) .Append(",[OutputTarget] =") .Append(paramOutputTarget.ParamName) .Append(",[FPYTarget] =") .Append(paramFPYTarget.ParamName) .Append(",[FPYAlert] =") .Append(paramFPYAlert.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.WindowLineStationInfos.Count; j++) { WindowLineStationInfo stationItem= lineItem.WindowLineStationInfos[j]; ConditionValueSet paramStationTargetId = new ConditionValueSet(); paramStationTargetId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStationTargetId.DataType = "char(32)"; paramStationTargetId.ParamValue = DatabaseUtil.GetUUID(); paramList.Add(paramStationTargetId); ConditionValueSet paramStationId = new ConditionValueSet(); paramStationId.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStationId.DataType = "char(10)"; paramStationId.ParamValue = GeneralUtil.Null2String(stationItem.StationId); paramList.Add(paramStationId); ConditionValueSet paramYieldTarget = new ConditionValueSet(); paramYieldTarget.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramYieldTarget.DataType = "float"; paramYieldTarget.ParamValue = GeneralUtil.Null2String(stationItem.YieldTarget); paramList.Add(paramYieldTarget); //true 1 True都可 ConditionValueSet paramFactorOfFPY = new ConditionValueSet(); paramFactorOfFPY.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramFactorOfFPY.DataType = "bit"; paramFactorOfFPY.ParamValue = GeneralUtil.Null2String(stationItem.FactorOfFPY); paramList.Add(paramFactorOfFPY); StationDisplayFieldInfo stationDisplayFieldsInfo=new StationDisplayFieldInfo (); stationDisplayFieldsInfo.DefectDsp=stationItem.Defect; stationDisplayFieldsInfo.QuantityDsp=stationItem.Quantity; stationDisplayFieldsInfo.WIPDsp=stationItem.WIP; stationDisplayFieldsInfo.YieldRateDsp=stationItem.YieldRate; String stationDisplayFieldsInfoValue=Struct2StationDisplayFieldsString(stationDisplayFieldsInfo); ConditionValueSet paramStationDisplayFields = new ConditionValueSet(); paramStationDisplayFields.ParamName = "@param" + paramIndex; paramIndex = paramIndex + 1; paramStationDisplayFields.DataType = "varchar(100)"; paramStationDisplayFields.ParamValue = stationDisplayFieldsInfoValue; paramList.Add(paramStationDisplayFields); 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_Station_Target] ([ID],[WindowsID],[Line],[Station],[YieldTarget],[FactorOfFPY],[DisplayFields],[Order])") .Append("VALUES (") .Append(paramStationTargetId.ParamName).Append(",") .Append(paramItemWindowId.ParamName).Append(",") .Append(paramLineId.ParamName).Append(",") .Append(paramStationId.ParamName).Append(",") .Append(paramYieldTarget.ParamName).Append(",") .Append(paramFactorOfFPY.ParamName).Append(",") .Append(paramStationDisplayFields.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; }
//这部分字段顺序固定 //QuantityDsp //DefectDsp //WIPDsp //YieldRateDsp public static StationDisplayFieldInfo StationDisplayFieldsString2Struct(String DisplayFieldsString) { StationDisplayFieldInfo result = new StationDisplayFieldInfo(); //如 "Quantilt=true". 逗号分隔 String[] separator = { "," }; String[] values = DisplayFieldsString.Split(separator, StringSplitOptions.None); String[] separatorNext = { "=" }; String[] QuantityDspInfos = values[0].Split(separatorNext, StringSplitOptions.None); if (QuantityDspInfos[1].ToLower() == "true") { result.QuantityDsp = "True"; } else { result.QuantityDsp = "False"; } String[] DefectDspInfos = values[1].Split(separatorNext, StringSplitOptions.None); if (DefectDspInfos[1].ToLower() == "true") { result.DefectDsp = "True"; } else { result.DefectDsp = "False"; } String[] WIPDspDisplayInfos = values[2].Split(separatorNext, StringSplitOptions.None); if (WIPDspDisplayInfos[1].ToLower() == "true") { result.WIPDsp = "True"; } else { result.WIPDsp = "False"; } String[] YieldRateDspDisplayInfos = values[3].Split(separatorNext, StringSplitOptions.None); if (YieldRateDspDisplayInfos[1].ToLower() == "true") { result.YieldRateDsp = "True"; } else { result.YieldRateDsp = "False"; } return result; }
//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; }