public override void Run()
{
if (WO.Value == null)
{
throw new Exception("WO Can not be null");
}
string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString());
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "SN List";
retTab.ColNames.RemoveAt(0);
// piechart
pieChart retChart_pie = new pieChart();
retChart_pie.GetSample();
Outputs.Add(retChart_pie);
//linechart
LineChart retChart_line = new LineChart();
retChart_line.GetSample1();
LineChart retChart_spline = new LineChart();
retChart_spline.GetSample2();
LineChart retChart_area = new LineChart();
retChart_area.GetSample3();
//columnChart
columnChart retChart_column = new columnChart();
retChart_column.GetSample1();
Outputs.Add(retChart_column);
Outputs.Add(retChart_line);
Outputs.Add(retChart_spline);
Outputs.Add(retChart_area);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}
public override void Run()
{
DateTime dateFrom = DateTime.Now.AddDays(-730);
DateTime dateTO = DateTime.Now;
DataTable dtRepairTop = new DataTable();
string _skuName = "";
string sqlRun = "";
int col;
//dateFrom = (DateTime)startTime.Value;
//dateTO = (DateTime)endTime.Value;
if (startTime.Value == null || string.IsNullOrEmpty(startTime.Value.ToString()))
{
throw new Exception("Start time can not be null!");
}
else
{
dateFrom = Convert.ToDateTime(startTime.Value);
}
if (endTime.Value == null || string.IsNullOrEmpty(endTime.Value.ToString()))
{
throw new Exception("End time can not be null!");
}
else
{
dateTO = (DateTime)endTime.Value;
}
if (skuName.Value == null || string.IsNullOrEmpty(skuName.Value.ToString()))
{
throw new Exception("Sku name can not be null!");
}
else
{
_skuName = skuName.Value.ToString();
}
sqlRun = $@"select c.fail_code, d.sku_name , count(1) count
from r_repair_main a
inner join r_sn b
on a.sn = b.sn
inner join r_repair_failcode c
on c.sn = a.sn
and c.edit_time = a.edit_time
inner join c_sku d
on d.skuno = b.skuno
where a.closed_flag = 0
and d.sku_name='{_skuName}'
and a.edit_time between
to_date('{dateFrom.ToString("yyyy-MM-dd HH-mm-ss")}', 'yyyy-MM-dd hh24:mi:ss') and
to_date('{dateTO.ToString("yyyy-MM-dd HH-mm-ss")}', 'yyyy-MM-dd hh24:mi:ss')
--and c.fail_category = 'DEFECT'
group by d.sku_name, c.fail_code
order by c.fail_code, count(1) desc";
#region 原報表查詢語句
//select c.failcode, d.codename , count(1) count
// from sfcrepairmain A
// inner join mfworkstatus B
// on a.sysserialno = b.sysserialno
// inner join sfcrepairfailcode C
// on C.SYSSERIALNO = a.sysserialno
// and c.createdate = a.createdate
// inner join sfccodelike d
// on d.skuno = b.skuno
// where a.repaired = 1
// and a.lasteditdt between
// to_date('', 'yyyy-MM-dd hh24:mi:ss') and
// to_date('', 'yyyy-MM-dd hh24:mi:ss')
// and C.failcategory = 'DEFECT'
// group by d.codename, c.failcode
// order by c.failcode, count(1) desc;
#endregion
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
dtRepairTop = SFCDB.RunSelect(sqlRun).Tables[0];
if (dtRepairTop.Rows.Count > 0)
{
DataRow[] rows = dtRepairTop.Select("sku_name='" + _skuName + "'", "count desc");
List <object> objList = new List <object>();
pieChart pie = new pieChart();
//pie.Tittle = dateFrom.ToString("yyyy-MM-dd HH:mm:ss") + "至" + dateTO.ToString("yyyy-MM-dd HH:mm:ss") + " " + _skuName + "不良TOP10餅狀圖";
pie.Tittle = dateFrom.ToString("f").Replace(":", "時") + "分至" + dateTO.ToString("f").Replace(":", "時") + "分" + _skuName + "不良TOP10餅狀圖";
pie.ChartTitle = "主標題";
pie.ChartSubTitle = "副標題";
ChartData chartData = new ChartData();
chartData.name = _skuName;
chartData.type = ChartType.pie.ToString();
col = 0;
for (int j = 0; j < rows.Length; j++)
{
col++;
if (col < 11)
{
objList.Add(new List <object> {
rows[j]["fail_code"].ToString(), Convert.ToInt64(rows[j]["count"].ToString())
});
}
}
chartData.data = objList;
chartData.colorByPoint = true;
List <ChartData> _ChartDatas = new List <ChartData> {
chartData
};
pie.ChartDatas = _ChartDatas;
Outputs.Add(pie);
}
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
if (WO.Value == null)
{
throw new Exception("WO Can not be null");
}
// string runSql = string.Format(Sqls["strGetSN"], WO.Value.ToString());
// RunSqls.Add(runSql);
string wo = WO.Value.ToString();
string columnName = "";
string closeflag = CloseFlag.Value.ToString();
string linkURL = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNListByWo&RunFlag=1&WO=" + wo + "&EventName=";
;
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string Sqlwo = $@"SELECT workorderno ,skuno,ROUTE_ID,WORKORDER_QTY , trunc ( sysdate - DOWNLOAD_DATE) DATS,INPUT_QTY,FINISHED_QTY FROM R_WO_BASE where
WORKORDERNO = '{wo}' ";
if (closeflag == "Y")
{
Sqlwo = Sqlwo + " and CLOSED_FLAG = 1";
}
else if (closeflag == "N")
{
Sqlwo = Sqlwo + " and CLOSED_FLAG = 0";
}
DataTable dtwo = SFCDB.RunSelect(Sqlwo).Tables[0];
RunSqls.Add(Sqlwo);
if (dtwo.Rows.Count == 0)
{
ReportAlart alart = new ReportAlart("No Data!");
Outputs.Add(alart);
return;
}
//string SqlRoute = $@"select * from C_ROUTE_DETAIL where route_id='{dtwo.Rows[0]["route_id"].ToString()}' order by seq_no";
string SqlRoute = $@"select * from C_ROUTE_DETAIL where route_id='{dtwo.Rows[0]["route_id"].ToString()}' order by seq_no";
DataTable dtroute = SFCDB.RunSelect(SqlRoute).Tables[0];
RunSqls.Add(SqlRoute);
//string SqlStationRoute = $@" SELECT distinct next_station FROM r_sn where REPAIR_FAILED_FLAG <> 1 and(COMPLETED_FLAG = 0 or NEXT_STATION = 'JOBFINISH') and workorderno = '{wo}'
// MINUS
// select STATION_NAME from c_route_detail where ROUTE_ID='{dtwo.Rows[0]["route_id"].ToString()}' ";
string SqlStationRoute = $@" SELECT distinct next_station FROM r_sn where REPAIR_FAILED_FLAG <> 1 and workorderno = '{wo}'
MINUS
select STATION_NAME from c_route_detail where ROUTE_ID='{dtwo.Rows[0]["route_id"].ToString()}' ";
DataTable dtstationroute = SFCDB.RunSelect(SqlStationRoute).Tables[0];
RunSqls.Add(SqlStationRoute);
DataTable resdt = new DataTable();
DataTable linkTable = new DataTable();
resdt.Columns.Add("WorkOrderNo");
resdt.Columns.Add("Skuno");
resdt.Columns.Add("DATS");
resdt.Columns.Add("QTY");
linkTable.Columns.Add("WorkOrderNo");
linkTable.Columns.Add("Skuno");
linkTable.Columns.Add("DATS");
linkTable.Columns.Add("QTY");
for (int i = 0; i < dtroute.Rows.Count; i++)
{
resdt.Columns.Add(dtroute.Rows[i]["STATION_NAME"].ToString());
linkTable.Columns.Add(dtroute.Rows[i]["STATION_NAME"].ToString());
}
for (int i = 0; i < dtstationroute.Rows.Count; i++)
{
if (dtstationroute.Rows[i]["next_station"].ToString().Equals("JOBFINISH"))
{
continue;
}
resdt.Columns.Add(dtstationroute.Rows[i]["next_station"].ToString());
linkTable.Columns.Add(dtstationroute.Rows[i]["next_station"].ToString());
}
// resdt.Columns.Add("STOCKIN");
// resdt.Columns.Add("JOBFINISH");
//resdt.Columns.Add("NA");
resdt.Columns.Add("RepairWip");
resdt.Columns.Add("MRB");
//resdt.Columns.Add("REWORK");
resdt.Columns.Add("JOBFINISH");
//linkTable.Columns.Add("NA");
linkTable.Columns.Add("RepairWip");
linkTable.Columns.Add("MRB");
//linkTable.Columns.Add("REWORK");
linkTable.Columns.Add("JOBFINISH");
DataRow drd = resdt.NewRow();
DataRow linkDataRow = linkTable.NewRow();
drd["WorkOrderNo"] = wo;
drd["Skuno"] = dtwo.Rows[0]["Skuno"].ToString();
drd["DATS"] = dtwo.Rows[0]["DATS"].ToString();
drd["QTY"] = dtwo.Rows[0]["WORKORDER_QTY"].ToString();
// drd["STOCKIN"]= dtwo.Rows[0]["FINISHED_QTY"].ToString();
//string Sqlsncount =$@" select NEXT_STATION, count(NEXT_STATION)c from r_sn where (REPAIR_FAILED_FLAG <> 1 or REPAIR_FAILED_FLAG is null)
// and(COMPLETED_FLAG = 0 or NEXT_STATION = 'JOBFINISH')
// and workorderno = '{wo}' group by NEXT_STATION";
//
string Sqlsncount = $@" select NEXT_STATION, count(NEXT_STATION)c from r_sn where (REPAIR_FAILED_FLAG <> 1 or REPAIR_FAILED_FLAG is null)
and workorderno = '{wo}' and NEXT_STATION <>'REWORK' group by NEXT_STATION";
long loadingNum = 0;
long mrbNum = 0;
DataTable dtsncont = SFCDB.RunSelect(Sqlsncount).Tables[0];
RunSqls.Add(Sqlsncount);
for (int i = 0; i < dtsncont.Rows.Count; i++)
{
drd[dtsncont.Rows[i]["NEXT_STATION"].ToString()] = dtsncont.Rows[i]["c"].ToString();
linkDataRow[dtsncont.Rows[i]["NEXT_STATION"].ToString()] = (dtsncont.Rows[i]["c"].ToString() != "0") ? (linkURL + dtsncont.Rows[i]["NEXT_STATION"].ToString()) : "";
loadingNum = loadingNum + Convert.ToInt64(dtsncont.Rows[i]["c"].ToString());
}
string SqlRepairCount = $@" select count(1) repaircount from r_sn where REPAIR_FAILED_FLAG = 1 and workorderno = '{wo}'";
DataTable dtrepaircont = SFCDB.RunSelect(SqlRepairCount).Tables[0];
RunSqls.Add(SqlRepairCount);
drd["RepairWip"] = dtrepaircont.Rows[0]["repaircount"].ToString();
linkDataRow["RepairWip"] = (dtrepaircont.Rows[0]["repaircount"].ToString() != "0") ? (linkURL + "RepairWip") : "";
//string SqlMrbCount = $@"select count(1) mrbcount from r_mrb where workorderno = '{wo}' and rework_wo is null";
string SqlMrbCount = $@"select count(1) mrbcount from r_mrb where workorderno = '{wo}' ";
DataTable dtmrbcont = SFCDB.RunSelect(SqlMrbCount).Tables[0];
RunSqls.Add(SqlMrbCount);
drd["MRB"] = dtmrbcont.Rows[0]["mrbcount"].ToString();
mrbNum = Convert.ToInt64(dtmrbcont.Rows[0]["mrbcount"].ToString());
linkDataRow["MRB"] = (dtmrbcont.Rows[0]["mrbcount"].ToString() != "0") ? (linkURL + "MRB") : "";
//loadingNum = loadingNum + Convert.ToInt64(dtrepaircont.Rows[0]["repaircount"].ToString()) + Convert.ToInt64(dtmrbcont.Rows[0]["mrbcount"].ToString());
foreach (DataColumn dc in resdt.Columns)
{
if (dc.ColumnName.ToString().ToUpper().IndexOf("LOADING") > 0 || dc.ColumnName.ToString().ToUpper().IndexOf("LINK") > -1)
{
drd[dc.ColumnName.ToString()] = Convert.ToInt64(dtwo.Rows[0]["WORKORDER_QTY"].ToString()) - loadingNum - mrbNum;
}
}
resdt.Rows.Add(drd);
linkTable.Rows.Add(linkDataRow);
ReportTable retTab = new ReportTable();
retTab.LoadData(resdt, linkTable);
retTab.Tittle = "WO WIP";
//retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
if (resdt.Rows.Count > 0)
{
List <object> objList = new List <object>();
pieChart pie = new pieChart();
pie.Tittle = "工單" + wo + "WIP分佈餅狀圖";
pie.ChartTitle = "主標題";
pie.ChartSubTitle = "副標題";
ChartData chartData = new ChartData();
chartData.name = "WOLIST";
chartData.type = ChartType.pie.ToString();
for (int j = 0; j < resdt.Rows.Count; j++)
{
foreach (DataColumn column in resdt.Columns)
{
columnName = column.ColumnName.ToString().ToUpper();
if (columnName != "WORKORDERNO" && columnName != "SKUNO" && columnName != "DATS" && columnName != "QTY" && resdt.Rows[j][columnName].ToString() != "" && resdt.Rows[j][columnName].ToString() != "0")
{
objList.Add(new List <object> {
columnName, Convert.ToInt64(resdt.Rows[j][columnName].ToString())
});
}
}
}
chartData.data = objList;
chartData.colorByPoint = true;
List <ChartData> _ChartDatas = new List <ChartData> {
chartData
};
pie.ChartDatas = _ChartDatas;
Outputs.Add(pie);
}
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}
public override void Run()
{
if (wo.Value == null || wo.Value.ToString().Equals(""))
{
throw new Exception("WO Can not be null");
}
OleExec sfcdb = DBPools["SFCDB"].Borrow();
//string workorderno = wo.Value.ToString();
//string ClosedFlag = CloseFlag.Value.ToString();
T_R_SN t = new T_R_SN(sfcdb, MESDataObject.DB_TYPE_ENUM.Oracle);
//DataTable dt = t.GetSNByWo(workorderno,sfcdb);
//ReportTable retTab = new ReportTable();
//retTab.LoadData(dt, null);
//retTab.Tittle = "ASSY Manufacture Report";
//Outputs.Add(retTab);
#region 折綫圖
LineChart lChart = new LineChart();
lChart.GetSample1();
//lChart.Tittle = "SampleChart_折線圖";
//lChart.ChartTitle = "主标题";
//lChart.ChartSubTitle = "副标题";
//XAxis _XAxis = new XAxis();
//_XAxis.Title = "X轴标题";
//_XAxis.XAxisType = XAxisType.datetime;
//lChart.XAxis = _XAxis;
//lChart.Tooltip = "%";
//Yaxis _YAxis = new Yaxis();
//_YAxis.Title = "Y轴标题";
//lChart.YAxis = _YAxis;
//ChartData lChartData = new ChartData();
//lChartData.name = "B32S1";
//lChartData.type = ChartType.line.ToString();
//PlotOptions PlotOptions = new PlotOptions();
//PlotOptions.type = PlotType.datetime;
////PlotOptions.pointStartDateTime = DateTime.Parse("2018-08-20 12:00:00");
////PlotOptions.pointInterval = 3600000;
//lChart.Plot = PlotOptions;
//lChartData.data = new List<object> { new List<object>{ "2018-08-20 12:00:00" ,new string[] { "11","22","33"} },
//new List<object>{ "2018-08-20 13:00:00" ,new string[] { "111","222","333"} }} ;
//List<ChartData> lChartDatas = new List<ChartData> { lChartData };
//lChart.ChartDatas = lChartDatas;
Outputs.Add(lChart);
#endregion
#region 曲綫圖
LineChart spLineChart = new LineChart();
spLineChart.Tittle = "SampleChart_曲線圖";
spLineChart.ChartTitle = "主标题";
spLineChart.ChartSubTitle = "副标题";
XAxis _spXAxis = new XAxis();
_spXAxis.Title = "X轴标题";
_spXAxis.XAxisType = XAxisType.datetime;
spLineChart.XAxis = _spXAxis;
//spLineChart.Tooltip = "%";
Yaxis _spYAxis = new Yaxis();
_spYAxis.Title = "Y轴标题";
spLineChart.YAxis = _spYAxis;
ChartData spChartData = new ChartData();
spChartData.name = "B32S1";
spChartData.type = ChartType.spline.ToString();
PlotOptions spPlotOptions = new PlotOptions();
spPlotOptions.type = PlotType.datetime;
spLineChart.Plot = spPlotOptions;
//日期會顯示在當鼠標經過圖標的時候
spChartData.data = new List <object> {
new List <object> {
"2018-05-29 10:00:00", 64
}, new List <object> {
"2018-05-29 11:00:00", 78
}, new List <object> {
"2018-05-29 12:00:00", 35
}, new List <object> {
"2018-05-29 14:00:00", 235
}, new List <object> {
"2018-05-29 15:00:00", 135
}, new List <object> {
"2018-05-29 16:00:00", 85
}, new List <object> {
"2018-05-29 17:00:00", 56
}, new List <object> {
"2018-05-29 18:00:00", 15
}, new List <object> {
"2018-05-29 19:00:00", 133
}
};
List <ChartData> _spChartDatas = new List <ChartData> {
spChartData
};
spLineChart.ChartDatas = _spChartDatas;
Outputs.Add(spLineChart);
#endregion
#region 面積圖
LineChart aLineChart = new LineChart();
aLineChart.Tittle = "SampleChart_面積圖";
aLineChart.ChartTitle = "主标题";
aLineChart.ChartSubTitle = "副标题";
XAxis _aXAxis = new XAxis();
_aXAxis.Title = "2018/08/20 降雨分佈";
//_aXAxis.XAxisType = XAxisType.BarChart;
//_aXAxis.Categories = new string[] { "12","13","14","15","16","17","18" };
aLineChart.XAxis = _aXAxis;
aLineChart.Tooltip = "%";
Yaxis _aYAxis = new Yaxis();
_aYAxis.Title = "降雨量";
aLineChart.YAxis = _aYAxis;
ChartData aChartData = new ChartData();
aChartData.name = "降雨圖";
aChartData.type = ChartType.area.ToString();
PlotOptions aPlotOptions = new PlotOptions();
aPlotOptions.type = PlotType.intdata;
aPlotOptions.pointStartIntdata = 8;
//aPlotOptions.pointInterval = 2;
aLineChart.Plot = aPlotOptions;
aChartData.data = new List <object> {
198, 384, 64, 78, 35, 235, 135
};
List <ChartData> _aChartDatas = new List <ChartData> {
aChartData
};
aLineChart.ChartDatas = _aChartDatas;
Outputs.Add(aLineChart);
#endregion
#region 餅狀圖
pieChart pChart = new pieChart();
pChart.Tittle = "餅狀圖測試 ZGJ";
pChart.ChartTitle = "餅狀圖主標題";
pChart.ChartSubTitle = "餅狀圖副標題";
ChartData pChartData = new ChartData();
pChartData.name = "餅狀圖數據";
pChartData.type = ChartType.pie.ToString();
pChartData.data = t.GetPieChartTestData(sfcdb);
pChartData.colorByPoint = true;
List <ChartData> pChartDatas = new List <ChartData> {
pChartData
};
pChart.ChartDatas = pChartDatas;
Outputs.Add(pChart);
#endregion
#region 柱狀圖
columnChart cChart = new columnChart();
cChart.Tittle = "SampleChart_柱狀圖";
cChart.ChartTitle = "主标题";
cChart.ChartSubTitle = "副标题";
XAxis _cXAxis = new XAxis();
_cXAxis.Title = "X轴标题";
_cXAxis.XAxisType = XAxisType.BarChart;
//不可以省略，表示X軸上面顯示的内容，如果沒有設定的話，就會變成 0，1，2，3，4 這些毫無意義的數據
_cXAxis.Categories = new string[] { "苹果", "橘子", "梨", "葡萄", "香蕉" };
cChart.XAxis = _cXAxis;
cChart.Tooltip = "%";
Yaxis _cYAxis = new Yaxis();
_cYAxis.Title = "Y轴标题";
cChart.YAxis = _cYAxis;
ChartData cChartData = new ChartData();
cChartData.name = "HWD 各線別產出";
cChartData.type = ChartType.column.ToString();
cChartData.colorByPoint = true;
cChartData.data = new List <object> {
new columnData()
{
name = "苹果", y = 10
},
new columnData()
{
name = "橘子", y = 2
},
new columnData()
{
name = "梨", y = -3
},
new columnData()
{
name = "葡萄", y = 4
},
new columnData()
{
name = "香蕉", y = 0.0
}
};
List <ChartData> _cChartDatas = new List <ChartData> {
cChartData
};
cChart.ChartDatas = _cChartDatas;
Outputs.Add(cChart);
#endregion
#region 指針式儀表圖
//EChartBase Chart1 = new EChartBase();
//Chart1.title = new { text = "測試指針式儀表數據" };
//for (int i = 0; i < 5; i++)
//{
// GAUGE_Chart_Data GCD = new GAUGE_Chart_Data();
// GCD.min = 80;
// GCD.max = 100;
// GCD.splitNumber = 2;
// GCD.endAngle = -45;
// GCD.radius = "50%";
// GCD.name = "第" + i + "小時";
// GCD.axisLine = new
// {
// lineStyle = new
// {
// width = 8,
// color = new List<Object>()
// { new List<object>() { 0.5, "#FF0000" }, new List<object>() { 1, "#00FF00" }}
// }
// };
// int j = i + 1;
// GCD.center = new List<string>() { $@"{j * 10 + 5}%", $@"50%" };
// GCD.data = new List<object> { new { value = 80 + i * 5, name = GCD.name } };
// Chart1.series.Add(GCD);
//}
//Chart1.Zone_ID = "TopZone";
//Outputs.Add(Chart1);
#endregion
if (sfcdb != null)
{
DBPools["SFCDB"].Return(sfcdb);
}
}
