public override void Run()
{
//base.Run();
string wo = inputWo.Value.ToString();
string eventName = inputEventName.Value.ToString().ToUpper();
string sqlRun = string.Empty;
DataTable snListTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow linkRow = null;
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
if (eventName.Equals("REPAIRWIP"))
{
sqlRun = $@"select distinct sn,next_station as station,edit_time from r_sn where REPAIR_FAILED_FLAG = 1 and workorderno ='{wo}'";
}
else if (eventName.Equals("MRB"))
{
sqlRun = $@"select distinct sn,'MRB' as station,edit_time from r_mrb where workorderno = '{wo}' and rework_wo is null";
}
else
{
//sqlRun = $@"select sn,next_station as station,edit_time from r_sn where workorderno='{wo}' and next_station='{eventName}'";
//sqlRun = $@"select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a,r_panel_sn b where a.workorderno='{wo}' and a.next_station='{eventName}' and a.sn=b.sn";
sqlRun = $@" select a.sn,a.next_station as station,a.edit_time,b.panel from r_sn a left join r_panel_sn b on a.sn=b.sn where a.workorderno='{wo}' and a.next_station='{eventName}' ";
}
RunSqls.Add(sqlRun);
//OleExec SFCDB = DBPools["SFCDB"].Borrow();
//try
//{
snListTable = SFCDB.RunSelect(sqlRun).Tables[0];
DBPools["SFCDB"].Return(SFCDB);
linkTable.Columns.Add("SN");
linkTable.Columns.Add("STATION");
linkTable.Columns.Add("EDIT_TIME");
linkTable.Columns.Add("PANEL");
for (int i = 0; i < snListTable.Rows.Count; i++)
{
linkRow = linkTable.NewRow();
linkRow["SN"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + snListTable.Rows[i]["SN"].ToString();
linkRow["STATION"] = "";
linkRow["EDIT_TIME"] = "";
linkRow["PANEL"] = "";
linkTable.Rows.Add(linkRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(snListTable, linkTable);
reportTable.Tittle = "SNList";
//reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
string sqlCode = "";
string sqlRun = "";
if (errorCode.Value != null && !string.IsNullOrEmpty(errorCode.Value.ToString()))
{
sqlCode = $@" and error_code ='{errorCode.Value.ToString()}' ";
}
sqlRun = Sqls["SqlErrorCode"] + sqlCode;
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsUresymptom = SFCDB.RunSelect(sqlRun);
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dsUresymptom.Tables[0], null);
reportTable.Tittle = "ErrorCodeTable";
reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
if (SN.Value == null || SN.Value.ToString().Trim().Length <= 0)
{
throw new Exception("SN Can not be null");
}
// string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString());
string runSql = "select * from r_sn where sn=:snno ";
string strSn = SN.Value.ToString().Trim();
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
OleDbParameter[] paramet = new OleDbParameter[] {
new OleDbParameter(":snno", strSn)
};
DataTable res = SFCDB.ExecuteDataTable(runSql, CommandType.Text, paramet);
ReportTable retTab = new ReportTable();
retTab.LoadData(res, null);
retTab.Tittle = "SN RMA Message";
retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
}
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 stime = Convert.ToDateTime(StartTime.Value);
DateTime etime = Convert.ToDateTime(EndTime.Value);
string svalue = stime.ToString("yyyy/MM/dd HH:mm:ss");
string evalue = etime.ToString("yyyy/MM/dd HH:mm:ss");
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
//string runSql = $@"SELECT a.sn, a.fail_line, b.EDIT_TIME repair_date, b.fail_code, B.FAIL_LOCATION
// FROM R_REPAIR_MAIN a, R_REPAIR_FAILCODE b, R_REPAIR_ACTION c
// WHERE a.id = b.repair_main_id(+) AND B.ID = C.REPAIR_FAILCODE_ID(+)
// AND a.CREATE_TIME BETWEEN TO_DATE ('{svalue}','YYYY/MM/DD HH24:MI:SS')
// AND TO_DATE ('{evalue}', 'YYYY/MM/DD HH24:MI:SS') ";
string runSql = $@"select rrm.sn,rrm.workorderno,rrm.skuno,rrm.fail_station,rrf.fail_code,
rra.action_code,cec.chinese_description,rra.fail_location,rra.kp_no,
rra.date_code,rra.lot_code,rra.process,rra.repair_emp,rra.repair_time
from r_repair_main rrm,r_repair_failcode rrf,
c_error_code cec,r_repair_action rra
where rrm.sn = rrf.sn and cec.error_code =rrf.fail_code(+)
and rrm.CREATE_TIME BETWEEN TO_DATE ('{svalue}','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE ('{evalue}', 'YYYY/MM/DD HH24:MI:SS') and rrf.sn = rra.sn ";
if (SN.Value.ToString() != "ALL" && SN.Value.ToString() != string.Empty)
{
runSql += $@" and rrm.SN = '{ SN.Value.ToString()}'";
}
if (SkuNo.Value.ToString() != "ALL" && SkuNo.Value.ToString() != string.Empty)
{
runSql += $@" and rrm.skuno = '{SkuNo.Value.ToString()}'";
}
runSql += $@" order by rrm.sn ,rrm.workorderno,rra.repair_time";
RunSqls.Add(runSql);
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "Repair Report";
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
throw ee;
}
}
public override void Run()
{
DateTime startDT = (DateTime)startTime.Value;
DateTime endDT = (DateTime)endTime.Value;
string dateFrom = $@"to_date('{startDT.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string dateTO = $@"to_date('{endDT.ToString("yyyy-MM-dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string sqlTaskNo = "";
string sqlHWPN = "";
string sqlProductLine = "";
string sqlRun = "";
if (taskNo.Value != null && !string.IsNullOrEmpty(taskNo.Value.ToString()))
{
sqlTaskNo = $@" and TASK_NO ='{taskNo.Value.ToString()}' ";
}
if (item.Value != null && !string.IsNullOrEmpty(item.Value.ToString()))
{
sqlHWPN = $@" AND ITEM = '{item.Value.ToString()}' ";
}
if (productLine.Value.ToString() != "ALL" && !string.IsNullOrEmpty(productLine.Value.ToString()))
{
sqlProductLine = $@" and PRODUCT_LINE = '{productLine.Value.ToString()}'";
}
//現在數據庫中沒有r_7b5_xml_t這個表,故直接DB Link 舊的數據庫
sqlRun = $@"select * from r_7b5_xml_t@hwd where 1=1 and lasteditdt between {dateFrom} and {dateTO} {sqlTaskNo} {sqlHWPN} {sqlProductLine}
order by TASK_NO,lasteditdt";
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet ds7B5 = SFCDB.RunSelect(sqlRun);
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(ds7B5.Tables[0], null);
reportTable.Tittle = "7B5Table";
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
DateTime stime = Convert.ToDateTime(StartTime.Value);
DateTime etime = Convert.ToDateTime(EndTime.Value);
string svalue = stime.ToString("yyyy/MM/dd HH:mm:ss");
string evalue = etime.ToString("yyyy/MM/dd HH:mm:ss");
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string runSql = $@"SELECT SKUNO,FAIL_STATION,COUNT(*) QTY
FROM R_REPAIR_MAIN WHERE closed_flag = 0
AND CREATE_TIME BETWEEN TO_DATE ('{svalue}',
'YYYY/MM/DD HH24:MI:SS') AND TO_DATE ('{evalue}',
'YYYY/MM/DD HH24:MI:SS') ";
if (Event.Value.ToString() != "ALL")
{
runSql += $@"and FAIL_STATION = '{ Event.Value.ToString()}'";
}
if (SkuNo.Value.ToString() != "ALL")
{
runSql += $@"and FAIL_STATION = '{SkuNo.Value.ToString()}'";
}
runSql = runSql + " GROUP BY SKUNO,FAIL_STATION";
RunSqls.Add(runSql);
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "Repair wip";
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
throw ee;
}
}
private string[] GetSkunoArray()
{
List <string> listSkuno = new List <string>();
RunSqls.Add(Sqls["SqlGetSkuno"]);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsSkuno = SFCDB.RunSelect(Sqls["SqlGetSkuno"]);
foreach (DataRow row in dsSkuno.Tables[0].Rows)
{
listSkuno.Add(row[0].ToString());
}
return(listSkuno.ToArray());
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
}
public override void Run()
{
DateTime stime = Convert.ToDateTime(StartTime.Value);
DateTime etime = Convert.ToDateTime(EndTime.Value);
string svalue = stime.ToString("yyyy/MM/dd HH:mm:ss");
string evalue = etime.ToString("yyyy/MM/dd HH:mm:ss");
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
string runSql = $@"SELECT a.sn, a.fail_line, b.EDIT_TIME repair_date, b.fail_code, B.FAIL_LOCATION
FROM R_REPAIR_MAIN a, R_REPAIR_FAILCODE b, R_REPAIR_ACTION c
WHERE a.id = b.repair_main_id(+) AND B.ID = C.REPAIR_FAILCODE_ID(+)
AND a.CREATE_TIME BETWEEN TO_DATE ('{svalue}','YYYY/MM/DD HH24:MI:SS')
AND TO_DATE ('{evalue}', 'YYYY/MM/DD HH24:MI:SS') ";
if (WO.Value.ToString() != "ALL")
{
runSql += $@"and a.wo = '{ WO.Value.ToString()}'";
}
if (SkuNo.Value.ToString() != "ALL")
{
runSql += $@"and skuno = '{SkuNo.Value.ToString()}'";
}
RunSqls.Add(runSql);
DataSet res = SFCDB.RunSelect(runSql);
ReportTable retTab = new ReportTable();
retTab.LoadData(res.Tables[0], null);
retTab.Tittle = "Repair Report";
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
throw ee;
}
}
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()
{
DateTime dateFrom = (DateTime)startTime.Value;
DateTime dateTO = (DateTime)endTime.Value;
DataTable dtLineFailTop = new DataTable();
DataTable loadTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow loadTitleRow = null;
DataRow loadDataRow = null;
DataRow linkTitleRow = null;
DataRow linkDataRow = null;
int col = 0;
string line = "";
string sqlRun = "";
//if (SelectBy.Value.ToString() == "日期段" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// dateFrom = (DateTime)startTime.Value;
// dateTO = (DateTime)endTime.Value;
//}
//else if (SelectBy.Value.ToString() == "月" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// if (string.IsNullOrEmpty(month.Value.ToString()))
// {
// throw new Exception("Please input month");
// }
// dateFrom = DateTime.Parse(DateTime.Now.Year.ToString() + "-" + month.Value + "-" + "01 08:00:00");
// dateTO = DateTime.Parse(DateTime.Now.Year.ToString() + "-" + month.Value + "-" + "01 08:00:00").AddMonths(1);
//}
//else if (SelectBy.Value.ToString() == "周" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// if (string.IsNullOrEmpty(week.Value.ToString()))
// {
// throw new Exception("Please input week");
// }
// dateFrom = ConverDate.GetWeekStartDate(Convert.ToInt32(week.Value.ToString()));
// dateTO = dateFrom.AddDays(7);
//}
sqlRun = $@"select e.line , c.fail_code , 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.create_time = a.create_time
inner join c_sku d
on d.skuno = b.skuno
inner join r_sn_station_detail e
on e.sn = a.sn
where a.closed_flag = 1
and e.current_station = 'AOI1'
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 e.line , c.fail_code
order by e.line , count(1) desc";
#region 原報表查詢語句
//select e.productionline , c.failcode , 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
// inner join mfsysevent e
// on e.sysserialno = a.sysserialno
// where a.repaired = 1
// and e.eventname = 'AOI1'
// 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 e.productionline , c.failcode
// order by e.productionline , count(1) desc
#endregion
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
dtLineFailTop = SFCDB.RunSelect(sqlRun).Tables[0];
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
for (int top = 0; top <= 10; top++)
{
if (top == 0)
{
loadTable.Columns.Add("LINE");
linkTable.Columns.Add("LINE");
}
else
{
loadTable.Columns.Add("TOP" + top.ToString());
linkTable.Columns.Add("TOP" + top.ToString());
}
}
for (int i = 0; i < dtLineFailTop.Rows.Count; i++)
{
if (line != dtLineFailTop.Rows[i]["line"].ToString())
{
line = dtLineFailTop.Rows[i]["line"].ToString();
loadTitleRow = loadTable.NewRow();
loadDataRow = loadTable.NewRow();
linkTitleRow = linkTable.NewRow();
linkDataRow = linkTable.NewRow();
loadTitleRow["line"] = line;
col = 0;
loadTable.Rows.Add(loadTitleRow);
loadTable.Rows.Add(loadDataRow);
linkTable.Rows.Add(linkTitleRow);
linkTable.Rows.Add(linkDataRow);
}
col++;
if (col > 10)
{
continue;
}
loadTitleRow[col] = dtLineFailTop.Rows[i]["fail_code"].ToString();
loadDataRow[col] = dtLineFailTop.Rows[i]["count"].ToString();
linkTitleRow[col] = "MESReport.BaseReport.RepairFailCodeDetail";
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(loadTable, linkTable);
reportTable.Tittle = "LineFailTopTable";
Outputs.Add(reportTable);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
string dateFrom = $@"to_date('{startTime.Value.ToString()}', 'yyyy-MM-dd hh24:mi:ss')";
string dateTO = $@"to_date('{endTime.Value}', 'yyyy-MM-dd hh24:mi:ss')";
string sqlFailCode = "";
string sqlSkuno = "";
string sqlLine = "";
string sqlWO = "";
string sqlRun = "";
if (type.Value.ToString() != "ALL" && !string.IsNullOrEmpty(type.Value.ToString()))
{
if (type.Value.ToString() == "功能")
{
sqlFailCode = " and j.fail_code not like 'SMT%' ";
}
else
{
sqlFailCode = " and j.fail_code like 'SMT%' ";
}
}
if (skuno.Value.ToString() != "ALL" && !string.IsNullOrEmpty(skuno.Value.ToString()))
{
sqlSkuno = $@" and b.skuno = '{skuno.Value.ToString()}'";
}
if (line.Value.ToString() != "ALL" && !string.IsNullOrEmpty(line.Value.ToString()))
{
sqlLine = $@" and c.line = '{line.Value.ToString()}'";
}
if (wo.Value != null && !string.IsNullOrEmpty(wo.Value.ToString()))
{
sqlWO = $@" and b.workorderno = '{wo.Value.ToString()}'";
}
sqlRun = $@"select workorderno 工單,skuno 料號,line 線別,workorder_qty 工單數量,failcount 不良數量,
decode(failcount/workorder_qty,0,'0',to_char(round(failcount/workorder_qty * 100, 2),'fm9999990.9999')) || '%' as 不良率,
fail_location 不良位置,chinese_description 不良原因,count(sn) 數量,fail_code 不良代碼
from (select b.workorderno,d.workorder_qty,i.skuno,g.failcount,
c.line,a.sn,a.create_time,a.edit_time,a.fail_code,h.chinese_description,
a.fail_location from r_repair_failcode a
inner join r_sn b
on a.sn = b.sn
left join r_sn_station_detail c
on a.sn = c.sn
inner join r_wo_base d
on b.workorderno = d.workorderno
inner join (select f.workorderno, count(1) failcount
from r_repair_failcode e
inner join r_sn f
on e.sn = f.sn
where e.edit_time between {dateFrom} and {dateTO}
and e.fail_category = 'DEFECT'
group by f.workorderno) g
on g.workorderno = b.workorderno
inner join c_error_code h
on a.fail_code = h.error_code
inner join r_wo_base i
on b.workorderno = i.workorderno
inner join r_repair_failcode j
on j.sn = a.sn and j.create_time = a.create_time
where a.fail_category = 'DEFECT'
and j.fail_category = 'SYMPTOM'
and c.current_station = 'AOI1'
and a.edit_time between {dateFrom} and {dateTO} {sqlFailCode} {sqlSkuno} {sqlLine} {sqlWO}
order by b.workorderno,c.line,a.fail_location,a.fail_code)
group by workorderno,workorder_qty,failcount,line,fail_location,fail_code,
chinese_description,skuno order by line, workorderno, count(1) desc";
#region 原報表查詢語句
//select workorderno 工單,
// skuno 料號,
// productionline 線別,
// workorderqty 工單數量,
// failcount 不良數量,
// round((failcount / workorderqty) * 100, 2) || '%' 不良率,
// faillocation 不良位置,
// symptomdesc1 不良原因,
// count(sysserialno) 數量 ,failcode
// from (select b.workorderno,
// d.workorderqty,
// i.skuno,
// g.failcount,
// c.productionline,
// a.sysserialno,
// a.createdate,
// a.lasteditdt,
// a.failcode,
// h.symptomdesc1,
// a.faillocation
// from sfcrepairfailcode a
// inner join mfworkstatus b
// on a.sysserialno = b.sysserialno
// left join mfsysevent c
// on a.sysserialno = c.sysserialno
// inner join mfworkorder d
// on b.workorderno = d.workorderno
// inner join (select f.workorderno, count(1) failcount
// from sfcrepairfailcode e
// inner join mfworkstatus f
// on e.sysserialno = f.sysserialno
// where e.lasteditdt between
// to_date('', 'yyyy-MM-dd hh24:mi:ss') and
// to_date('', 'yyyy-MM-dd hh24:mi:ss')
// and e.failcategory = 'DEFECT'
// group by f.workorderno) g
// on g.workorderno = b.workorderno
// inner join SFCCONFIG.SFCFAILURESYMPTOMINFO h
// on a.failcode = h.symptomname
// inner join mfworkorder i
// on b.workorderno = i.workorderno
// inner join sfcrepairfailcode j
// on j.sysserialno = a.sysserialno and j.createdate = a.createdate
// where a.failcategory = 'DEFECT'
// and j.failcategory = 'SYMPTOM'
// and c.eventname = 'AOI1'
// and a.lasteditdt between
// to_date('', 'yyyy-MM-dd hh24:mi:ss') and
// to_date('', 'yyyy-MM-dd hh24:mi:ss')
// and j.failcode not like 'SMT%'
// and b.skuno = ''
// and c.productionline = ''
// and b.workorderno = ''
// order by b.workorderno,
// c.productionline,
// a.faillocation,
// a.failcode)
// group by workorderno,
// workorderqty,
// failcount,
// productionline,
// faillocation,
// failcode,
// symptomdesc1,
// skuno
// order by productionline, workorderno, count(1) desc
#endregion
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsRepair = SFCDB.RunSelect(sqlRun);
ReportTable repairReportTable = new ReportTable();
repairReportTable.LoadData(dsRepair.Tables[0], null);
repairReportTable.Tittle = "RepairReportTable";
Outputs.Add(repairReportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
//base.Run();
string lotNo = inputLotNo.Value.ToString().Trim();
string sqlRun = string.Empty;
DataTable snListTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow linkRow = null;
sqlRun = $@"select ls.lot_no,ld.sn,ld.workorderno,ld.sampling,ld.status,ld.fail_code,ld.fail_location,ld.description,
ld.carton_no,ld.pallet_no,ld.edit_emp,ld.create_date from r_lot_detail ld,r_lot_status ls
where ld.lot_id=ls.id and ls.lot_no='{lotNo}'";
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
snListTable = SFCDB.RunSelect(sqlRun).Tables[0];
DBPools["SFCDB"].Return(SFCDB);
linkTable.Columns.Add("LOT_NO");
linkTable.Columns.Add("SN");
linkTable.Columns.Add("WORKORDERNO");
linkTable.Columns.Add("SAMPLING");
linkTable.Columns.Add("STATUS");
linkTable.Columns.Add("FAIL_CODE");
linkTable.Columns.Add("FAIL_LOCATION");
linkTable.Columns.Add("DESCRIPTION");
linkTable.Columns.Add("CARTON_NO");
linkTable.Columns.Add("PALLET_NO");
linkTable.Columns.Add("EDIT_EMP");
linkTable.Columns.Add("CREATE_DATE");
for (int i = 0; i < snListTable.Rows.Count; i++)
{
linkRow = linkTable.NewRow();
linkRow["LOT_NO"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SmtFqcByLotReport&RunFlag=1&LOTNO=" + snListTable.Rows[i]["LOT_NO"].ToString();
linkRow["SN"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SmtFqcBySnReport&RunFlag=1&WO=" + snListTable.Rows[i]["WORKORDERNO"].ToString()
+ "&SN=" + snListTable.Rows[i]["SN"].ToString();
//+ "$STATUS=ALL&From=" + snListTable.Rows[i]["CREATE_DATE"].ToString()
//+ "&To=" + snListTable.Rows[i]["CREATE_DATE"].ToString();
linkRow["WORKORDERNO"] = "";
linkRow["SAMPLING"] = "";
linkRow["FAIL_CODE"] = "";
linkRow["FAIL_LOCATION"] = "";
linkRow["DESCRIPTION"] = "";
linkRow["CARTON_NO"] = "";
linkRow["PALLET_NO"] = "";
linkRow["EDIT_EMP"] = "";
linkRow["CREATE_DATE"] = "";
linkTable.Rows.Add(linkRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(snListTable, linkTable);
reportTable.Tittle = "SNList";
//reportTable.ColNames.RemoveAt(0);
Outputs.Add(reportTable);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
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()
{
DateTime dateFrom = (DateTime)startTime.Value;
DateTime dateTO = (DateTime)endTime.Value;
DataTable dtRepairTop = new DataTable();
DataTable loadTable = new DataTable();
DataTable linkTable = new DataTable();
DataRow loadTitleRow = null;
DataRow loadDataRow = null;
DataRow linkTitleRow = null;
DataRow linkDataRow = null;
int col = 0;
string skuno = "";
string sqlRun = "";
//if (SelectBy.Value.ToString() == "日期段" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// dateFrom = (DateTime)startTime.Value;
// dateTO = (DateTime)endTime.Value;
//}
//else if (SelectBy.Value.ToString() == "月" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// if (string.IsNullOrEmpty(month.Value.ToString()))
// {
// throw new Exception("Please input month");
// }
// dateFrom = DateTime.Parse(DateTime.Now.Year.ToString() + "-" + month.Value + "-" + "01 08:00:00");
// dateTO = DateTime.Parse(DateTime.Now.Year.ToString() + "-" + month.Value + "-" + "01 08:00:00").AddMonths(1);
//}
//else if (SelectBy.Value.ToString() == "周" && !string.IsNullOrEmpty(SelectBy.Value.ToString()))
//{
// if (string.IsNullOrEmpty(week.Value.ToString()))
// {
// throw new Exception("Please input week");
// }
// dateFrom = ConverDate.GetWeekStartDate(Convert.ToInt32(week.Value.ToString()));
// dateTO = dateFrom.AddDays(7);
//}
sqlRun = $@"select d.sku_name ,c.fail_code, 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 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 d.sku_name, 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];
for (int top = 0; top <= 10; top++)
{
if (top == 0)
{
loadTable.Columns.Add("SKU_NAME");
linkTable.Columns.Add("SKU_NAME");
}
else
{
loadTable.Columns.Add("TOP" + top.ToString());
linkTable.Columns.Add("TOP" + top.ToString());
}
}
for (int i = 0; i < dtRepairTop.Rows.Count; i++)
{
//if (skuno != dtRepairTop.Rows[i]["sku_name"].ToString())
//{
// skuno = dtRepairTop.Rows[i]["sku_name"].ToString();
// loadTitleRow = loadTable.NewRow();
// loadDataRow = loadTable.NewRow();
// linkTitleRow = linkTable.NewRow();
// linkDataRow = linkTable.NewRow();
// loadTitleRow["sku_name"] = skuno;
// linkTitleRow["sku_name"] = "MESReport.BaseReport.RepairTopPieBySku";
// col = 0;
// loadTable.Rows.Add(loadTitleRow);
// loadTable.Rows.Add(loadDataRow);
// linkTable.Rows.Add(linkTitleRow);
// linkTable.Rows.Add(linkDataRow);
//}
//col++;
//if (col > 10)
//{
// continue;
//}
//loadTitleRow[col] = dtRepairTop.Rows[i]["fail_code"].ToString();
//loadDataRow[col] = dtRepairTop.Rows[i]["count"].ToString();
//linkTitleRow[col] = "MESReport.BaseReport.RepairFailCodeDetail";
if (skuno != dtRepairTop.Rows[i]["sku_name"].ToString())
{
skuno = dtRepairTop.Rows[i]["sku_name"].ToString();
loadTitleRow = loadTable.NewRow();
loadDataRow = loadTable.NewRow();
linkTitleRow = linkTable.NewRow();
linkDataRow = linkTable.NewRow();
loadTitleRow["sku_name"] = skuno;
linkTitleRow["sku_name"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.RepairTopPieBySku&RunFlag=1&SkuName=" + skuno + "&StartTime="
+ dateFrom.ToString("yyyy-MM-dd HH:mm:ss") + "&EndTime=" + dateTO.ToString("yyyy-MM-dd HH:mm:ss");
col = 0;
loadTable.Rows.Add(loadTitleRow);
loadTable.Rows.Add(loadDataRow);
linkTable.Rows.Add(linkTitleRow);
linkTable.Rows.Add(linkDataRow);
DataRow [] rows = dtRepairTop.Select("sku_name='" + skuno + "'", "count desc");
for (int j = 0; j < rows.Length; j++)
{
col++;
if (col > 10)
{
continue;
}
loadTitleRow[col] = rows[j]["fail_code"].ToString();
loadDataRow[col] = rows[j]["count"].ToString();
linkTitleRow[col] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.RepairFailCodeDetail&RunFlag=1&ErrorCode=" + rows[j]["fail_code"].ToString();
}
}
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(loadTable, linkTable);
reportTable.Tittle = "RepairTopTable";
Outputs.Add(reportTable);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
//base.Run();
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now;
string sn = inputSN.Value.ToString();
string station = inputStationName.Value.ToString();
string state = inputStateType.Value.ToString();
string runSql = $@" select skuno,sn,state,station,cell,operator,error_code,createtime from r_test_detail_vertiv where 1=1 ";
if (inputStartDate.Value.ToString() != "")
{
startDate = (DateTime)inputStartDate.Value;
}
if (inputStartDate.Value.ToString() != "")
{
endDate = (DateTime)inputEndDate.Value;
}
if (inputStartDate.Value.ToString() != "" && inputStartDate.Value.ToString() != "")
{
runSql = runSql + $@"createtime between to_date('{startDate.ToString("yyyy-MM-dd HH-mm-ss")}','yyyy/mm/dd hh24:mi:ss')
and to_date('{endDate.ToString("yyyy-MM-dd HH-mm-ss")}','yyyy/mm/dd hh24:mi:ss') ";
}
if (sn != "")
{
runSql = runSql + $@" and sn='{sn}' ";
}
if (station != "ALL")
{
runSql = runSql + $@" and station='{station}' ";
}
if (state != "ALL")
{
runSql = runSql + $@" and state='{state}' ";
}
RunSqls.Add(runSql);
DataTable dtTestReport = SFCDB.RunSelect(runSql).Tables[0];
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
DataTable linkTable = new DataTable();
DataRow linkRow = null;
linkTable.Columns.Add("SKUNO");
linkTable.Columns.Add("SN");
linkTable.Columns.Add("STATE");
linkTable.Columns.Add("STATION");
linkTable.Columns.Add("CELL");
linkTable.Columns.Add("OPERATOR");
linkTable.Columns.Add("ERROR_CODE");
linkTable.Columns.Add("CREATETIME");
for (int i = 0; i < dtTestReport.Rows.Count; i++)
{
linkRow = linkTable.NewRow();
linkRow["SN"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + dtTestReport.Rows[i]["SN"].ToString();
linkRow["SKUNO"] = "";
linkRow["STATE"] = "";
linkRow["STATION"] = "";
linkRow["CELL"] = "";
linkRow["OPERATOR"] = "";
linkRow["ERROR_CODE"] = "";
linkRow["CREATETIME"] = "";
linkTable.Rows.Add(linkRow);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dtTestReport, linkTable);
reportTable.Tittle = "SN TEST REPORT";
Outputs.Add(reportTable);
}
catch (Exception exception)
{
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
throw exception;
}
}
public override void Run()
{
string runSql = "select * from r_mrb ";
string strSn = (SN.Value == null)?"": SN.Value.ToString().Trim();
string strOldWO = (OldWO.Value == null) ? "" : OldWO.Value.ToString().Trim();
string strNewWO = (NewWO.Value == null) ? "" : NewWO.Value.ToString().Trim();
string strFromStorage = (FromStorage.Value == null) ? "" : FromStorage.Value.ToString().Trim();
string strToStorage = (ToStorage.Value == null) ? "" : ToStorage.Value.ToString().Trim();
bool isContainWhere = false;
OleDbParameter[] paramet = null;
List <OleDbParameter> parametList = new List <OleDbParameter>();
if (strSn.Length > 0)
{
runSql = runSql + $@" where sn=:sn ";
OleDbParameter SNParamet = new OleDbParameter(":sn", strSn);
parametList.Add(SNParamet);
isContainWhere = true;
}
if (strOldWO.Length > 0)
{
if (!isContainWhere)
{
runSql = runSql + $@" where workorderno=:wono ";
isContainWhere = true;
}
else
{
runSql = runSql + $@" and workorderno=:wono ";
}
OleDbParameter WOParamet = new OleDbParameter(":wono", strOldWO);
parametList.Add(WOParamet);
}
if (strNewWO.Length > 0)
{
if (!isContainWhere)
{
runSql = runSql + $@" where rework_wo=:rewono ";
isContainWhere = true;
}
else
{
runSql = runSql + $@" and rework_wo=:rewono ";
}
OleDbParameter REWOParamet = new OleDbParameter(":rewono", strNewWO);
parametList.Add(REWOParamet);
}
if (strFromStorage.Length > 0)
{
if (!isContainWhere)
{
runSql = runSql + $@" where from_storage=:FromStorage ";
isContainWhere = true;
}
else
{
runSql = runSql + $@" and from_storage=:FromStorage ";
}
OleDbParameter FromStorageParamet = new OleDbParameter(":FromStorage", strFromStorage);
parametList.Add(FromStorageParamet);
}
if (strToStorage.Length > 0)
{
if (!isContainWhere)
{
runSql = runSql + $@" where to_storage=:ToStorage ";
isContainWhere = true;
}
else
{
runSql = runSql + $@" and to_storage=:ToStorage ";
}
OleDbParameter ToStorageParamet = new OleDbParameter(":ToStorage", strToStorage);
parametList.Add(ToStorageParamet);
}
if (parametList.Count <= 0)
{
//throw new MESReturnMessage(MESReturnMessage.GetMESReturnMessage("MES00000141"}));
throw new Exception("請輸入查詢條件");
}
else
{
paramet = new OleDbParameter[parametList.Count];
for (int i = 0; i < parametList.Count; i++)
{
paramet[i] = parametList[i];
}
}
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataTable res = SFCDB.ExecuteDataTable(runSql, CommandType.Text, paramet);
ReportTable retTab = new ReportTable();
retTab.LoadData(res, null);
retTab.Tittle = "MRB List";
retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
}
public override void Run()
{
DateTime startDT = (DateTime)startTime.Value;
DateTime endDT = (DateTime)endTime.Value;
string dateFrom = $@"to_date('{startDT.ToString("yyyy/MM/dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string dateTO = $@"to_date('{endDT.ToString("yyyy/MM/dd HH:mm:ss")}', 'yyyy-MM-dd hh24:mi:ss')";
string sqlRun = $@"select line ,skuno 料號,input 投入, fail 不良總數,decode(failrate,0,'0',to_char(round(failrate * 100, 2),'fm9999990.9999')) ||'%' as 不良率
from (select d.line,d.skuno,count(distinct d.sn) input,count(distinct f.sn) fail,
count(distinct f.sn) / count(distinct d.sn) failrate from (
select a.sn, a.skuno, b.line from r_sn_station_detail a
inner join r_sn_station_detail b on a.sn = b.sn
inner join c_sku c on a.skuno = c.skuno
where a.edit_time between {dateFrom} and {dateTO} and a.current_station = 'BIP' and b.current_station = 'AOI1') d
left join (select sn from r_repair_main e where e.create_time between {dateFrom} and {dateTO}) f
on d.sn = f.sn group by d.skuno, d.line ) order by line , FAILRATE desc";
#region 原報表查詢語句
//select line 線別, skuno 料號, input 投入, fail 不良, substr(FAILRATE, 1, 4) || '%' 不良率
// from(select d.line,
// d.skuno,
// count(distinct d.sn) input,
// count(distinct f.sn) fail,
// count(distinct f.sn) / count(distinct d.sn) * 100 FAILRATE
// from(select a.sysserialno SN, a.skuno SKUNO, b.productionline LINE
// from mfsysevent a
// inner
// join mfsysevent b
// on a.sysserialno = b.sysserialno
// inner
// join sfccodelike c
// on a.skuno = c.skuno
// where a.scandatetime between
// to_date('', 'yyyy/mm/dd hh24:mi:ss') and
// to_date('', 'yyyy/mm/dd hh24:mi:ss')
// and a.eventname = 'BIP'
// and b.eventname = 'AOI1') d
// left join(select e.sysserialno SN
// from sfcrepairmain e
// where e.createdate between
// to_date('',
// 'yyyy/mm/dd hh24:mi:ss') and
// to_date('',
// 'yyyy/mm/dd hh24:mi:ss')) f
// on d.sn = f.sn
// group by d.skuno, d.line)
// order by line , FAILRATE desc;
#endregion
RunSqls.Add(sqlRun);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet dsLineFial = SFCDB.RunSelect(sqlRun);
if (SFCDB != null)
{
DBPools["SFCDB"].Return(SFCDB);
}
ReportTable reportTable = new ReportTable();
reportTable.LoadData(dsLineFial.Tables[0], null);
reportTable.Tittle = "LineFailTable";
Outputs.Add(reportTable);
if (dsLineFial.Tables[0].Rows.Count > 0)
{
Outputs.Add(GetChartDataSourse(startTime.Value.ToString(), endTime.Value.ToString(), dsLineFial.Tables[0]));
}
}
catch (Exception exception)
{
DBPools["SFCDB"].Return(SFCDB);
throw exception;
}
}
public override void Run()
{
string strYear = "";
int IntWeek = 0;
string strSkuno = "";
DateTime FromDate = DateTime.Now;
string strFromDate = "";
DateTime ToDate = DateTime.Now;
string strToDate = "";
if (Year.Value == null || Year.Value.ToString().Trim().Length <= 0)
{
strYear = DateTime.Now.Year.ToString();
Year.Value = strYear;
}
else
{
strYear = Year.Value.ToString();
try
{
if (Convert.ToDateTime(strYear + "/03/09 00:00:00").Year.ToString() != strYear)
{
throw new Exception("請填寫正確的Year");
}
}
catch
{
throw new Exception("請填寫正確的Year");
}
}
if (Week.Value == null || Week.Value.ToString().Trim().Length <= 0)
{
throw new Exception("Week 不能為空");
}
else
{
try
{
IntWeek = Convert.ToInt32(Week.Value.ToString().Trim());
if (IntWeek <= 0)
{
throw new Exception("Week 必須是大於0數字");
}
}
catch
{
throw new Exception("Week 必須是數字");
}
}
if (SkuNo.Value == null || SkuNo.Value.ToString().Trim().Length <= 0)
{
throw new Exception("SkuNo 不能為空");
}
else
{
strSkuno = SkuNo.Value.ToString().Trim();
if (strSkuno.ToUpper() == "ALL")
{
strSkuno = "select distinct(skuno) from c_sku";
}
else
{
strSkuno = "'" + strSkuno + "'";
}
}
FromDate = ConverDate.GetWeekStartDate(strYear, IntWeek);
strFromDate = FromDate.Year.ToString() + "-" + FromDate.Month.ToString() + "-" + FromDate.Day.ToString() + " ";
strFromDate = strFromDate + FromDate.Hour.ToString() + ":" + FromDate.Minute.ToString() + ":" + FromDate.Second.ToString();
ToDate = FromDate.AddDays(6);
strToDate = ToDate.Year.ToString() + "-" + ToDate.Month.ToString() + "-" + ToDate.Day.ToString() + " ";
strToDate = strToDate + ToDate.Hour.ToString() + ":" + ToDate.Minute.ToString() + ":" + ToDate.Second.ToString();
string runSql = $@"select skuno, datestr, sum(totalfail) as totalfail, sum(duplicatefail) as duplicatefail from(
select b.skuno, TO_CHAR(A.CREATE_TIME - 7.5 / 24, 'YYYY-MM-DD') as datestr, count(SN) as totalfail,
(case
when(count(Sn)) = 1 then 0
when(count(Sn)) > 1 then count(Sn) - 1
else 999
end
) as duplicatefail
from r_repair_main a, r_wo_base b
where a.workorderno = b.workorderno and b.skuno in({strSkuno})
and a.CREATE_TIME >= TO_DATE('{strFromDate}', 'YYYY-MM-DD HH24:MI:SS')
and a.CREATE_TIME <= to_date('{strToDate}', 'YYYY-MM-DD HH24:MI:SS')
group by b.skuno, TO_CHAR(A.CREATE_TIME - 7.5 / 24, 'YYYY-MM-DD'), sn
)
group by skuno,datestr
order by skuno, datestr";
RunSqls.Add(runSql);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataTable res = SFCDB.ExecuteDataTable(runSql, CommandType.Text);
ReportTable retTab = new ReportTable();
retTab.LoadData(res, null);
retTab.Tittle = "MultipleFailRate";
retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ex)
{
DBPools["SFCDB"].Return(SFCDB);
throw ex;
}
}
public override void Run()
{
if (SN.Value == null)
{
throw new Exception("SN Can not be null");
}
string runSql = string.Format(Sqls["strGetSN"], SN.Value.ToString());
string runSql1 = string.Format(Sqls["strGetSnDetail"], SN.Value.ToString());
string runSql2 = string.Format(Sqls["strGetSnKeypart"], SN.Value.ToString());
RunSqls.Add(runSql);
RunSqls.Add(runSql1);
RunSqls.Add(runSql2);
OleExec SFCDB = DBPools["SFCDB"].Borrow();
try
{
DataSet res = SFCDB.RunSelect(runSql);
DataSet res1 = SFCDB.RunSelect(runSql1);
DataSet res2 = SFCDB.RunSelect(runSql2);
ReportTable retTab = new ReportTable();
DataTable dt = res.Tables[0].Copy();
ReportLink link;
DataTable linkTable = new DataTable();
DataRow linkRow;
foreach (DataColumn column in res.Tables[0].Columns)
{
linkTable.Columns.Add(column.ColumnName);
}
foreach (DataRow row in res.Tables[0].Rows)
{
string linkURL = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.WoReport&RunFlag=1&=ALL&WO=" + row["workorderno"].ToString();
linkRow = linkTable.NewRow();
foreach (DataColumn dc in linkTable.Columns)
{
if (dc.ColumnName.ToString().ToUpper() == "WORKORDERNO")
{
linkRow[dc.ColumnName] = linkURL;
}
else
{
linkRow[dc.ColumnName] = "";
}
}
linkTable.Rows.Add(linkRow);
}
foreach (DataRow dr in dt.Rows)
{
// ClassName: 'sdsdsdw',Data: { Wo: 'sdsd',Station: '' }
//link = new ReportLink();
//link.ClassName = "MESReport.BaseReport.WoReport";
//link.Data.Add("WO", dr["workorderno"].ToString());
//link.Data.Add("CloseFlag","ALL");
//dr["WORKORDERNO"] = link;
//linkTable.Rows.Add(dr);
}
retTab.LoadData(res.Tables[0], linkTable);
retTab.Tittle = "OSN";
//retTab.ColNames.RemoveAt(0);
Outputs.Add(retTab);
ReportTable retTab1 = new ReportTable();
retTab1.LoadData(res1.Tables[0], null);
retTab1.Tittle = "SN DETAIL";
//retTab1.ColNames.RemoveAt(0);
Outputs.Add(retTab1);
ReportTable retTab2 = new ReportTable();
retTab2.LoadData(res2.Tables[0], null);
retTab2.Tittle = "SN KEYPARDT";
retTab2.ColNames.RemoveAt(0);
Outputs.Add(retTab2);
DBPools["SFCDB"].Return(SFCDB);
}
catch (Exception ee)
{
DBPools["SFCDB"].Return(SFCDB);
}
}