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;
}
