【接】多表联查(左联)计算多个子查询的Count数作为属性输出
优化点:1、尽量不用goto语句跳转,用if-else if-else if -else替代。
2、避免穷举的方式获取显示数据,尽量用循环(for、while等)
3、用switch-case优化过多的if判断
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using MESCore;
using MESCore.ListRoutines;
using DevExpress.XtraCharts;
namespace FDCCore
{
public partial class frmFDCViewStatusSummary : BaseForm, IBaseFormFunc
{
public frmFDCViewStatusSummary()
{
InitializeComponent();
}
public frmFDCViewStatusSummary(string FormName, string[] TableKeys)
{
InitializeComponent();
this.saTableKeys = TableKeys;//saTableKeys[0]: Factory; saTableKeys[1]: Type; saTableKeys[3]: Condition
this.Text = FormName;
}
#region Variable
private bool mbLoadFlag = false;
private string[] saTableKeys;
const int LINE_COL = 2;
const int AREA_COL = 3;
const int MODEL_COL = 4;
const int RES_COL = 5;
#endregion
#region Function
public Control GetFisrtFocusItem()
{
try
{
return this.gdcSummary;
}
catch (Exception ex)
{
CommonFunction.ShowMsgBox(ex.Message);
return null;
}
}
public override void RefreshForm()
{
BindGV();
}
private void InitControl()
{
SetBtnNewEnable(false);
if (saTableKeys == null)
{
saTableKeys = new string[] { GlobalVariable.gsFactory, "FAB", "" };
}
}
private void CreateSeries(DataTable dt, int iNameColumn, int iStartColumn, ViewType viewType, int iEndColumn = 0)
{
if (iStartColumn >= dt.Columns.Count || iEndColumn >= dt.Columns.Count)
{
return;
}
if(iEndColumn == 0)
{
iEndColumn = dt.Columns.Count;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
Series[] series = new Series[iEndColumn - iStartColumn];
for (int j = iStartColumn; j < iEndColumn; j++)
{
try
{
series[j - iStartColumn] = new Series(dt.Rows[i][iNameColumn].ToString() + " - " + dt.Columns[j].ColumnName, viewType);
series[j - iStartColumn].Points.Add(new SeriesPoint(dt.Rows[i][iNameColumn].ToString(), Convert.ToInt32(dt.Rows[i][j])));
series[j - iStartColumn].LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;//显示标注标签
((SideBySideStackedBarSeriesView)series[j - iStartColumn].View).StackedGroup = i;//将需要显示的多个series绑定到同一个柱状图
}
catch (Exception ex)
{
CommonFunction.ShowMsgBox(ex.Message);
series[j - iStartColumn].Points.Clear();
return;
}
}
chtSummary.Series.AddRange(series);
}
}
private DataTable InitViewDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("SITE");
dt.Columns.Add("FAB");
dt.Columns.Add("LINE");
dt.Columns.Add("AREA");
dt.Columns.Add("MODEL_ID");
dt.Columns.Add("RES_ID");
dt.Columns.Add("RES_CONNECTED");
dt.Columns.Add("RES_ACTIVATED");
dt.Columns.Add("MODULE_CONNECTED");
dt.Columns.Add("TRACE_PARA_COLLECTED");
dt.Columns.Add("EVENT_PARA_COLLECTED");
dt.Columns.Add("RECIPE_MODELED");
dt.Columns.Add("TRACE_SUM_PARAS");
dt.Columns.Add("EVENT_SUM_PARAS");
dt.Columns.Add("TRACE_PARA_MODELED");
dt.Columns.Add("EVENT_COUNT");
return dt;
}
private bool BindGV()
{
DataTable dt;
switch (saTableKeys[1])
{
case "FAB":
if(gluCondition_1.EditValue == null)
{
return false;
}
string sCondition_1 = gluCondition_1.EditValue.ToString();
dt = ListRoutineFDC.ViewFDCStatusReportByRes('1', sCondition_1);
if (dt != null)
{
chtSummary.Series.Clear();
CreateSeries(dt, LINE_COL, 6, ViewType.SideBySideStackedBar, 9);
}
else
{
dt = InitViewDataTable();
}
DevGridControlHelper.BindData(gdcSummary, dt, new int[] { 1, 1, 1, -1, -1, -1, 1, 1, 1, -1, -1, -1, -1, -1, -1, -1 });
break;
case "LINE":
dt = ListRoutineFDC.ViewFDCStatusReportByRes('2', saTableKeys[2]);
if (dt != null)
{
chtSummary.Series.Clear();
CreateSeries(dt, AREA_COL, 6, ViewType.SideBySideStackedBar);
}
else
{
dt = InitViewDataTable();
}
DevGridControlHelper.BindData(gdcSummary, dt, new int[] { -1, -1, -1, 1, -1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 });
break;
case "AREA":
dt = ListRoutineFDC.ViewFDCStatusReportByRes('3', saTableKeys[2]);
if (dt != null)
{
chtSummary.Series.Clear();
CreateSeries(dt, MODEL_COL, 6, ViewType.SideBySideStackedBar);
}
else
{
dt = InitViewDataTable();
}
DevGridControlHelper.BindData(gdcSummary, dt, new int[] { -1, -1, -1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 });
break;
case "MODEL":
dt = ListRoutineFDC.ViewFDCStatusReportByRes('4', saTableKeys[2]);
if (dt != null)
{
chtSummary.Series.Clear();
CreateSeries(dt, RES_COL, 8, ViewType.SideBySideStackedBar);
}
else
{
dt = InitViewDataTable();
}
DevGridControlHelper.BindData(gdcSummary, dt, new int[] { -1, -1, -1, -1, -1, 1, -1, -1, 1, 1, 1, 1, 1, 1, 1, 1 });
break;
case "RES":
dt = ListRoutineFDC.ViewFDCStatusReportByRes('5', saTableKeys[2]);
if (dt != null)
{
chtSummary.Series.Clear();
CreateSeries(dt, RES_COL, 9, ViewType.SideBySideStackedBar);
}
else
{
dt = InitViewDataTable();
}
DevGridControlHelper.BindData(gdcSummary, dt, new int[] { -1, -1, -1, -1, -1, 1, -1, -1, -1, 1, 1, 1, 1, 1, 1, 1 });
break;
default:
return false;
}
return true;
}
private void BindCondition_1()
{
DataTable dt = new DataTable();
dt = ListRoutineFDC.ViewFDCStatusReportByRes('B');
if (dt != null)
{
DataRow dr = dt.NewRow();
dr[0] = "";
dt.Rows.InsertAt(dr, 0);
DevGridLookupHelper.InitPopup(this.repositoryItemGridLookUpEdit1, new string[] { "","选择FAB" }, dt,1,1);
}
else
{
DataRow dr = dt.NewRow();
dt.Columns.Add(" ");
dt.Rows.Add("");
DevGridLookupHelper.InitPopup(this.repositoryItemGridLookUpEdit1, new string[] { "选择FAB" }, dt);
}
}
private string GetNextLevel(string sCurLevel)
{
string sResult = "";
switch(sCurLevel)
{
case "FAB":
sResult = "LINE";
break;
case "LINE":
sResult = "AREA";
break;
case "AREA":
sResult = "MODEL";
break;
case "MODEL":
sResult = "RES";
break;
}
return sResult;
}
#endregion
#region Control Event Function
protected override void ExecNewEvent()
{
;
}
protected override void ExecRefreshEvent()
{
BindGV();
}
#endregion
#region Control Event
private void frmFDCViewStatusSummary_Load(object sender, EventArgs e)
{
try
{
InitControl();
BindCondition_1();
if (mbLoadFlag == false && BindGV() == true)
{
mbLoadFlag = true;
}
if (saTableKeys != null && saTableKeys[1] != "FAB")
{
ribbonPageGroup1.Visible = false;
}
}
catch (Exception ex)
{
CommonFunction.ShowMsgBox(ex.Message);
}
}
//模拟行双击事件
private void gdvSummary_DoubleClick(object sender, EventArgs e)
{
MouseEventArgs arg = e as MouseEventArgs;
if (arg == null || arg.Button != MouseButtons.Left)
{
return;
}
DevExpress.XtraGrid.Views.Grid.ViewInfo.GridHitInfo hitInfo = gdvSummary.CalcHitInfo(new Point(arg.X, arg.Y));//获取坐标点
if (hitInfo.RowHandle >= 0)
{
string[] saTableKeys2 = new string[3];
saTableKeys2[0] = saTableKeys[0];
saTableKeys2[1] = GetNextLevel(saTableKeys[1]);
if (saTableKeys2[1] == "")
{
return;
}
if(saTableKeys[1]=="FAB")
{
saTableKeys2[2] = gdvSummary.GetRowCellValue(hitInfo.RowHandle, gdvSummary.Columns["LINE"]).ToString();
}
else
{
saTableKeys2[2] = gdvSummary.GetRowCellValue(hitInfo.RowHandle, gdvSummary.VisibleColumns[0]).ToString();
}
CommonFunction.OpenForm(CommonFunction.GetMenuTag(GlobalConstant.FORMID_FDC_VIEW_STATUS_SUMMARY),
"View Res Status Summary - " + saTableKeys2[2], saTableKeys2);
//CommonFunction.OpenForm(mTag, "Privilege Group - " + nodeName, new string[] { GlobalVariable.gsFactory, nodeID });
}
}
#endregion
private void btnSearch_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
BindGV();
}
}
}
============前台调用服务接收数据
#region FDC_View_Status_Report_By_Res 根据设备查看统计数据
public static DataTable ViewFDCStatusReportByRes(char Step, string sCondition_1 = " ")
{
try
{
DataTable dtResult = new DataTable();
dtResult.Columns.Add("SITE");
dtResult.Columns.Add("FAB");
dtResult.Columns.Add("LINE");
dtResult.Columns.Add("AREA");
dtResult.Columns.Add("MODEL_ID");
dtResult.Columns.Add("RES_ID");
dtResult.Columns.Add("RES_CONNECTED");
dtResult.Columns.Add("RES_ACTIVATED");
dtResult.Columns.Add("MODULE_CONNECTED");
dtResult.Columns.Add("TRACE_PARA_COLLECTED");
dtResult.Columns.Add("EVENT_PARA_COLLECTED");
dtResult.Columns.Add("RECIPE_MODELED");
dtResult.Columns.Add("TRACE_SUM_PARAS");
dtResult.Columns.Add("EVENT_SUM_PARAS");
dtResult.Columns.Add("TRACE_PARA_MODELED");
dtResult.Columns.Add("EVENT_COUNT");
ArrayList a_list = new ArrayList();
TRSNode in_node = new TRSNode("VIEW_LIST_IN");
TRSNode out_node;
CommonRoutine.SetInMsg(in_node);
in_node.ProcStep = Step;
in_node.AddString("CONDITION_1", sCondition_1);
do
{
out_node = new TRSNode("VIEW_LIST_OUT");
if (CommonRoutine.CallService("FDC", "FDC_View_Status_Report_By_Res", in_node, ref out_node) == false)
{
return null;
}
a_list.Add(out_node);
in_node.SetString("NEXT_EVENT_ID", out_node.GetString("NEXT_EVENT_ID"));
} while (in_node.GetString("NEXT_EVENT_ID") != "");
foreach (object obj in a_list)
{
out_node = null;
out_node = (TRSNode)obj;
for (int i = 0; i < out_node.GetList(0).Count; i++)
{
dtResult.Rows.Add(
out_node.GetList(0)[i].GetString("SITE"),
out_node.GetList(0)[i].GetString("FAB"),
out_node.GetList(0)[i].GetString("LINE"),
out_node.GetList(0)[i].GetString("AREA"),
out_node.GetList(0)[i].GetString("MODEL_ID"),
out_node.GetList(0)[i].GetString("MODULE_ID"),
out_node.GetList(0)[i].GetInt("RES_CONNECTED"),
out_node.GetList(0)[i].GetInt("RES_ACTIVATED"),
out_node.GetList(0)[i].GetInt("MODULE_CONNECTED"),
out_node.GetList(0)[i].GetInt("TRACE_PARA_COLLECTED"),
out_node.GetList(0)[i].GetInt("EVENT_PARA_COLLECTED"),
out_node.GetList(0)[i].GetInt("RECIPE_MODELED"),
out_node.GetList(0)[i].GetInt("TRACE_SUM_PARAS"),
out_node.GetList(0)[i].GetInt("EVENT_SUM_PARAS"),
out_node.GetList(0)[i].GetInt("TRACE_PARA_MODELED"),
out_node.GetList(0)[i].GetInt("EVENT_COUNT"));
}
}
dtResult.AcceptChanges();//保存更改,便于界面判断是否有修改
return dtResult;
}
catch (Exception ex)
{
CommonFunction.ShowMsgBox(ex.Message);
return null;
}
}
#endregion
===============后台实现服务提供数据
#region FDC_View_Status_Report_By_Res 根据设备查看统计数据
public static int FDC_View_Status_Report_By_Res(ref FDC_View_Status_Report_By_Res_In_Tag InTag, ref FDC_View_Status_Report_By_Res_Out_Tag OutTag)
{
#region Variable Define
string sMsgCode = "";
ModelContext ctx = new ModelContext();
string sFactory = InTag._cmn_in._factory;
string sCondition1 = InTag.condition_1;
string sCondition2 = InTag.condition_2;
string sCondition3 = InTag.condition_3;
int index;
#endregion
try
{
#region Validation
OutTag._cmn_out._status_value = GlobalConstant.RESULT_SUCCESS;
if (InTag._cmn_in._factory == "")
{
sMsgCode = "FDC-0004";
OutTag._cmn_out._field_msg = "FACTORY = " + sFactory;
return GlobalConstant.FAIL;
}
#endregion
#region Main
if (InTag._cmn_in._proc_step == "1")// Select UQcmcoatemplate List by CoaId
{
var query1 = ctx.Fdclocation
.Where(t => t.Factory == sFactory && t.Fab == sCondition1)
.Select(t => new { t.Site,t.Line,t.Fab })
.Distinct();
var query2 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.Fab == sCondition1 && t.r.ResLevel == 0)
.GroupBy(t => t.l.Line)
.Select(g => new { Line = g.Key, RES_CONNECTED = g.Count() });
var query3 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Join(ctx.Fdcdcp, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory && t.l2.l.Fab == sCondition1 && t.l2.r.ResLevel == 0 && t.r2.DcpState == "ACTIVE")
.GroupBy(t => t.l2.l.Line)
.Select(g => new { Line = g.Key, RES_ACTIVATED = g.Count() });
var query4 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.Fab == sCondition1 && t.r.ResLevel > 0)
.GroupBy(t => t.l.Line)
.Select(g => new { Line = g.Key, MODULE_CONNECTED = g.Count() });
var query = query1
.GroupJoin(query2, l => l.Line, r => r.Line, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, b.RES_CONNECTED })
.GroupJoin(query3, l => l.Line, r => r.Line, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, a.l.RES_CONNECTED, b.RES_ACTIVATED })
.GroupJoin(query4, l => l.Line, r => r.Line, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Line = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.Site, a.l.Fab, a.l.Line, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
.Select(t => new { t.Fab,t.Line,t.Site, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED })
.ToList();
int iRowCount = query.Count;
index = 0;
#region Output
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < query.Count; index++)
{
if (index >= GlobalVariable.giMaxRecordsRows)
{
//OutTag.status_report_list[index].location_id = query[index].LocationId;
break;
}
//OutTag.next_coa_id = uQcmcoatemplateList[index].CoaTemplateId;
OutTag.status_report_list[index].site = query[index].Site;
OutTag.status_report_list[index].fab = query[index].Fab;
OutTag.status_report_list[index].line = query[index].Line;
OutTag.status_report_list[index].area ="AREA";
OutTag.status_report_list[index].model_id ="MODEL_ID";
OutTag.status_report_list[index].module_id ="MODULE_ID";
OutTag.status_report_list[index].res_connected = query[index].RES_CONNECTED;
OutTag.status_report_list[index].res_activated = query[index].RES_ACTIVATED;
OutTag.status_report_list[index].module_connected = query[index].MODULE_CONNECTED;
OutTag.status_report_list[index].trace_para_collected = 0;
OutTag.status_report_list[index].event_para_collected =0;
OutTag.status_report_list[index].recipe_modeled = 0;
OutTag.status_report_list[index].trace_sum_paras = 0;
OutTag.status_report_list[index].event_sum_paras = 0;
OutTag.status_report_list[index].trace_para_modeled = 0;
OutTag.status_report_list[index].event_count = 0;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
if (InTag._cmn_in._proc_step == "2")// Select UQcmcoatemplate List by CoaId
{
var query1 = ctx.Fdclocation
.Where(t => t.Factory == sFactory && t.Line == sCondition1)
.Select(t => new { t.Area })
.Distinct();
var query2 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.Line == sCondition1 && t.r.ResLevel == 0)
.GroupBy(t => t.l.Area)
.Select(g => new { Area = g.Key, RES_CONNECTED = g.Count() });
var query3 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Join(ctx.Fdcdcp, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory && t.l2.l.Line == sCondition1 && t.l2.r.ResLevel == 0 && t.r2.DcpState == "ACTIVE")
.GroupBy(t => t.l2.l.Area)
.Select(g => new { Area = g.Key, RES_ACTIVATED = g.Count() });
var query4 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.Line == sCondition1 && t.r.ResLevel > 0)
.GroupBy(t => t.l.Area)
.Select(g => new { Area = g.Key, MODULE_CONNECTED = g.Count() });
var query5 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Join(ctx.Fdcdcptracepara, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory && t.l2.l.Line == sCondition1)
.GroupBy(t => t.l2.l.Area)
.Select(g => new { Area = g.Key, TRACE_PARA_COLLECTED = g.Count() });
var query6 = ctx.Fdclocation
.Join(ctx.Fdcres, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Join(ctx.Fdcdcptracepara, l2 => new { l2.l.Factory, l2.r.ResId }, r2 => new { r2.Factory, r2.ResId }, (l2, r2) => new { l2, r2 })
.Join(ctx.Fdctracespec, l3 => new { l3.l2.l.Factory, l3.r2.ParaDisplayName }, r3 => new { r3.Factory, r3.ParaDisplayName }, (l3, r3) => new { l3, r3 })
.Where(t => t.l3.l2.l.Factory == sFactory && t.l3.l2.l.Line == sCondition1)
.GroupBy(t => t.l3.l2.l.Area)
.Select(g => new { Area = g.Key, TRACE_PARA_MODELED = g.Count() });
var query = query1
.GroupJoin(query2, l => l.Area, r => r.Area, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.Area, b.RES_CONNECTED })
.GroupJoin(query3, l => l.Area, r => r.Area, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, b.RES_ACTIVATED })
.GroupJoin(query4, l => l.Area, r => r.Area, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
.GroupJoin(query5, l => l.Area, r => r.Area, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
.GroupJoin(query6, l => l.Area, r => r.Area, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { Area = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.Area, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
.Select(t => new { t.Area, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
.ToList();
int iRowCount = query.Count();
index = 0;
#region Output
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < query.Count; index++)
{
if (index >= GlobalVariable.giMaxRecordsRows)
{
//OutTag.status_report_list[index].location_id = query[index].LocationId;
break;
}
OutTag.status_report_list[index].site ="SITE";
OutTag.status_report_list[index].fab ="FAB";
OutTag.status_report_list[index].line = "LINE";
OutTag.status_report_list[index].area = query[index].Area;
OutTag.status_report_list[index].res_connected = query[index].RES_CONNECTED;
OutTag.status_report_list[index].res_activated = query[index].RES_ACTIVATED;
OutTag.status_report_list[index].module_connected = query[index].MODULE_CONNECTED;
OutTag.status_report_list[index].trace_para_collected = query[index].TRACE_PARA_COLLECTED;
OutTag.status_report_list[index].event_para_collected = query[index].EVENT_PARA_COLLECTED;
OutTag.status_report_list[index].recipe_modeled = query[index].RECIPE_MODELED;
OutTag.status_report_list[index].trace_sum_paras = query[index].TRACE_SUM_PARAS;
OutTag.status_report_list[index].event_sum_paras = query[index].EVENT_SUM_PARAS;
OutTag.status_report_list[index].trace_para_modeled = query[index].TRACE_PARA_MODELED;
OutTag.status_report_list[index].event_count = query[index].EVENT_COUNT;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
if (InTag._cmn_in._proc_step == "3")// Select UQcmcoatemplate List by CoaId
{
var query1 = ctx.Fdclocation
.Join(ctx.Fdcresmodel, l => new { l.Factory, l.LocationId }, r => new { r.Factory, r.LocationId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.Area == sCondition1)
.Select(t => new { t.r.ModelId })
.Distinct();
var query2 = ctx.Fdcres
.Where(t => t.Factory == sFactory && t.ResLevel == 0)
.GroupBy(t => t.ResModelId)
.Select(g => new { ModelId = g.Key, RES_CONNECTED = g.Count() });
var query3 = ctx.Fdcres
.Join(ctx.Fdcdcp, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory && t.l.ResLevel == 0 && t.r.DcpState == "ACTIVE")
.GroupBy(t => t.l.ResModelId)
.Select(g => new { ModelId = g.Key, RES_ACTIVATED = g.Count() });
var query4 = ctx.Fdcres
.Where(t => t.Factory == sFactory && t.ResLevel > 0)
.GroupBy(t => t.ResModelId)
.Select(g => new { ModelId = g.Key, MODULE_CONNECTED = g.Count() });
var query5 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory)
.GroupBy(t => t.l.ResModelId)
.Select(g => new { ModelId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
var query6 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory)
.GroupBy(t => t.l2.l.ResModelId)
.Select(g => new { ModelId = g.Key, TRACE_PARA_MODELED = g.Count() });
var query = query1
.GroupJoin(query2, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", RES_CONNECTED = 0 }), (a, b) => new { a.l.ModelId, b.RES_CONNECTED })
.GroupJoin(query3, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", RES_ACTIVATED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, b.RES_ACTIVATED })
.GroupJoin(query4, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, b.MODULE_CONNECTED })
.GroupJoin(query5, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
.GroupJoin(query6, l => l.ModelId, r => r.ModelId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ModelId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ModelId, a.l.RES_CONNECTED, a.l.RES_ACTIVATED, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
.Select(t => new { t.ModelId, t.RES_CONNECTED, t.RES_ACTIVATED, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
.ToList();
int iRowCount = query.Count;
index = 0;
#region Output
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < query.Count; index++)
{
if (index >= GlobalVariable.giMaxRecordsRows)
{
//OutTag.status_report_list[index].location_id = query[index].LocationId;
break;
}
//OutTag.next_coa_id = uQcmcoatemplateList[index].CoaTemplateId;
OutTag.status_report_list[index].site = "SITE";
OutTag.status_report_list[index].fab = "FAB";
OutTag.status_report_list[index].line = "LINE";
OutTag.status_report_list[index].area = "AREA";
OutTag.status_report_list[index].model_id = query[index].ModelId;
OutTag.status_report_list[index].module_id = "MODULE_ID";
OutTag.status_report_list[index].res_connected = query[index].RES_CONNECTED;
OutTag.status_report_list[index].res_activated = query[index].RES_ACTIVATED;
OutTag.status_report_list[index].module_connected = query[index].MODULE_CONNECTED;
OutTag.status_report_list[index].trace_para_collected = query[index].TRACE_PARA_COLLECTED;
OutTag.status_report_list[index].event_para_collected = query[index].EVENT_PARA_COLLECTED;
OutTag.status_report_list[index].recipe_modeled = query[index].RECIPE_MODELED;
OutTag.status_report_list[index].trace_sum_paras = query[index].TRACE_SUM_PARAS;
OutTag.status_report_list[index].event_sum_paras = query[index].EVENT_SUM_PARAS;
OutTag.status_report_list[index].trace_para_modeled = query[index].TRACE_PARA_MODELED;
OutTag.status_report_list[index].event_count = query[index].EVENT_COUNT;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
if (InTag._cmn_in._proc_step == "4")// Select UQcmcoatemplate List by CoaId
{
var query1 = ctx.Fdcres
.Where(t => t.Factory == sFactory && t.ResModelId == sCondition1 && t.ResLevel == 0)
.Select(t => new { t.ResId })
.Distinct();
var query2 = ctx.Fdcres
.Where(t => t.Factory == sFactory && t.ResLevel > 0)
.GroupBy(t => t.BaseResId)
.Select(g => new { ResId = g.Key, MODULE_CONNECTED = g.Count() });
var query3 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory)
.GroupBy(t => t.l.ResId)
.Select(g => new { ResId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
var query4 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory)
.GroupBy(t => t.l2.l.ResId)
.Select(g => new { ResId = g.Key, TRACE_PARA_MODELED = g.Count() });
var query = query1
.GroupJoin(query2, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", MODULE_CONNECTED = 0 }), (a, b) => new { a.l.ResId, b.MODULE_CONNECTED })
.GroupJoin(query3, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ResId, a.l.MODULE_CONNECTED, b.TRACE_PARA_COLLECTED })
.GroupJoin(query4, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ResId, a.l.MODULE_CONNECTED, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
.Select(t => new { t.ResId, t.MODULE_CONNECTED, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
.ToList();
int iRowCount = query.Count;
index = 0;
#region Output
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < query.Count; index++)
{
if (index >= GlobalVariable.giMaxRecordsRows)
{
//OutTag.status_report_list[index].location_id = query[index].LocationId;
break;
}
//OutTag.next_coa_id = uQcmcoatemplateList[index].CoaTemplateId;
OutTag.status_report_list[index].site = "SITE";
OutTag.status_report_list[index].fab = "FAB";
OutTag.status_report_list[index].line = "LINE";
OutTag.status_report_list[index].area = "AREA";
OutTag.status_report_list[index].model_id = "MODEL_ID";
OutTag.status_report_list[index].module_id = query[index].ResId;
OutTag.status_report_list[index].res_connected =0;
OutTag.status_report_list[index].res_activated = 0;
OutTag.status_report_list[index].module_connected = query[index].MODULE_CONNECTED;
OutTag.status_report_list[index].trace_para_collected = query[index].TRACE_PARA_COLLECTED;
OutTag.status_report_list[index].event_para_collected = query[index].EVENT_PARA_COLLECTED;
OutTag.status_report_list[index].recipe_modeled = query[index].RECIPE_MODELED;
OutTag.status_report_list[index].trace_sum_paras = query[index].TRACE_SUM_PARAS;
OutTag.status_report_list[index].event_sum_paras = query[index].EVENT_SUM_PARAS;
OutTag.status_report_list[index].trace_para_modeled = query[index].TRACE_PARA_MODELED;
OutTag.status_report_list[index].event_count = query[index].EVENT_COUNT;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
if (InTag._cmn_in._proc_step == "5")// Select UQcmcoatemplate List by CoaId
{
var query1 = ctx.Fdcres
.Where(t => t.Factory == sFactory && t.BaseResId == sCondition1)
.Select(t => new { t.ResId })
.Distinct();
var query2 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Where(t => t.l.Factory == sFactory)
.GroupBy(t => t.l.ResId)
.Select(g => new { ResId = g.Key, TRACE_PARA_COLLECTED = g.Count() });
var query3 = ctx.Fdcres
.Join(ctx.Fdcdcptracepara, l => new { l.Factory, l.ResId }, r => new { r.Factory, r.ResId }, (l, r) => new { l, r })
.Join(ctx.Fdctracespec, l2 => new { l2.l.Factory, l2.r.ParaDisplayName }, r2 => new { r2.Factory, r2.ParaDisplayName }, (l2, r2) => new { l2, r2 })
.Where(t => t.l2.l.Factory == sFactory)
.GroupBy(t => t.l2.l.ResId)
.Select(g => new { ResId = g.Key, TRACE_PARA_MODELED = g.Count() });
var query = query1
.GroupJoin(query2, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_COLLECTED = 0 }), (a, b) => new { a.l.ResId, b.TRACE_PARA_COLLECTED })
.GroupJoin(query3, l => l.ResId, r => r.ResId, (l, r) => new { l, r })
.SelectMany(lr => lr.r.DefaultIfEmpty(new { ResId = "", TRACE_PARA_MODELED = 0 }), (a, b) => new { a.l.ResId, a.l.TRACE_PARA_COLLECTED, b.TRACE_PARA_MODELED })
.Select(t => new { t.ResId, t.TRACE_PARA_COLLECTED, EVENT_PARA_COLLECTED = 0, RECIPE_MODELED = 0, TRACE_SUM_PARAS = 0, EVENT_SUM_PARAS = 0, t.TRACE_PARA_MODELED, EVENT_COUNT = 0 })
.ToList();
int iRowCount = query.Count;
index = 0;
#region Output
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < query.Count; index++)
{
if (index >= GlobalVariable.giMaxRecordsRows)
{
//OutTag.status_report_list[index].location_id = query[index].LocationId;
break;
}
//OutTag.next_coa_id = uQcmcoatemplateList[index].CoaTemplateId;
OutTag.status_report_list[index].site = "SITE";
OutTag.status_report_list[index].fab = "FAB";
OutTag.status_report_list[index].line = "LINE";
OutTag.status_report_list[index].area = "AREA";
OutTag.status_report_list[index].model_id = "MODEL_ID";
OutTag.status_report_list[index].module_id = query[index].ResId;
OutTag.status_report_list[index].res_connected = 0;
OutTag.status_report_list[index].res_activated = 0;
OutTag.status_report_list[index].module_connected = 0;
OutTag.status_report_list[index].trace_para_collected = query[index].TRACE_PARA_COLLECTED;
OutTag.status_report_list[index].event_para_collected = query[index].EVENT_PARA_COLLECTED;
OutTag.status_report_list[index].recipe_modeled = query[index].RECIPE_MODELED;
OutTag.status_report_list[index].trace_sum_paras = query[index].TRACE_SUM_PARAS;
OutTag.status_report_list[index].event_sum_paras = query[index].EVENT_SUM_PARAS;
OutTag.status_report_list[index].trace_para_modeled = query[index].TRACE_PARA_MODELED;
OutTag.status_report_list[index].event_count = query[index].EVENT_COUNT;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
#region
if (InTag._cmn_in._proc_step == "B")// Select UQcmcoatemplate List by CoaId
{
index = 0;
var fdclocationlist = (new int[] { 1 }).Select(x => new
{
Fab = "empty",
}).ToList() ;
fdclocationlist = ctx.Fdclocation.Where(t => t.Factory == sFactory).Select(x => new {
x.Fab,
}).Distinct().ToList();
int iRowCount = fdclocationlist.Count;
Array.Resize(ref OutTag.status_report_list, iRowCount > GlobalVariable.giMaxRecordsRows ? GlobalVariable.giMaxRecordsRows : iRowCount);
for (index = 0; index < fdclocationlist.Count; index++)
{
OutTag.status_report_list[index].fab = fdclocationlist[index].Fab;
}
OutTag.status_report_count = index;
OutTag._size_status_report_list = index;
}
#endregion
}
#endregion
catch (DbUpdateException ex)
{
sMsgCode = "FDC-0004";
OutTag._cmn_out._db_err_msg = ex.InnerException.Message;
OutTag._cmn_out._field_msg = "FACTORY = " + sFactory;
OutTag._cmn_out._status_value = GlobalConstant.RESULT_ERROR;
return GlobalConstant.FAIL;
}
catch (Exception ex)
{
sMsgCode = "FDC-0004";
OutTag._cmn_out._status_value = GlobalConstant.RESULT_ERROR;
OutTag._cmn_out._msg = ex.Message;
return GlobalConstant.FAIL;
}
finally
{
if (sMsgCode != "")
{
OutTag._cmn_out._msg_code = sMsgCode;
CommonFunction.COM_GetErrorMsg(InTag._cmn_in._language, sMsgCode, ref OutTag._cmn_out._msg);
GlobalVariable.gLog.AddLog(sMsgCode, OutTag._cmn_out._msg);
GlobalVariable.gLog.AddLog("FIELD_MSG", OutTag._cmn_out._field_msg);
GlobalVariable.gLog.LogWrite("ERROR " + CommonFunction.gCOM_Msg_Title, "E");
}
else
{
sMsgCode = "CMN-0000";
CommonFunction.COM_GetErrorMsg(InTag._cmn_in._language, sMsgCode, ref OutTag._cmn_out._msg);
}
}
return GlobalConstant.SUCCESS;
}
#endregion