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