public override void Run() { //base.Run(); string sn = snObj.Value.ToString(); string wo = woObj.Value.ToString(); string runSql = $@" select sn.skuno,sn.workorderno,sn.sn,kp.value,kp.partno,kp.kp_name,kp.mpn,kp.scantype,kp.itemseq, kp.scanseq,kp.detailseq,kp.station,kp.valid_flag,kp.edit_time,kp.edit_emp from r_sn_kp kp, r_sn sn where kp.r_sn_id = sn.id and sn.valid_flag = '1' "; if (sn == "" && wo == "") { ReportAlart alart = new ReportAlart("Please input a sn or wo"); Outputs.Add(alart); return; } if (sn != "") { runSql = runSql + $@" and (sn.sn='{sn}' or sn.boxsn='{sn}') "; } if (wo != "") { runSql = runSql + $@" and sn.workorderno='{wo}' "; } OleExec sfcdb = DBPools["SFCDB"].Borrow(); DataTable dt = new DataTable(); try { dt = sfcdb.RunSelect(runSql).Tables[0]; if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } if (dt.Rows.Count == 0) { throw new Exception("NO data"); } ReportTable reportTable = new ReportTable(); reportTable.LoadData(dt, null); reportTable.Tittle = "SN keypart detail"; Outputs.Add(reportTable); } catch (Exception ex) { if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportAlart alart = new ReportAlart(ex.Message); Outputs.Add(alart); } }
public override void Run() { DateTime ft = (DateTime)fromDate.Value; var DB = this.DBPools["SFCDB"].Borrow(); try { string strSql = $@"SELECT rownum NO, SKU.CUST_PARTNO, SN.SN, S.LOT_NO, S.LOT_QTY, 'XXX' WORKTIME, 'QQQ' DAYS, S.SAMPLE_STATION FROM R_LOT_STATUS S INNER JOIN R_LOT_DETAIL D ON S.LOT_NO = D.LOT_ID INNER JOIN R_SN SN ON D.SN = SN.SN INNER JOIN C_SKU SKU ON SN.SKUNO = SKU.SKUNO WHERE S.LOT_NO LIKE 'LOT-%' AND S.AQL_TYPE IS NULL AND SN.VALID_FLAG = 1 AND D.STATUS = 0 AND S.EDIT_TIME >to_date('{ft.ToString("yyyy-MM-dd")}','yyyy-mm-dd')"; if (StockNo.Value.ToString() != "ALL") { strSql += $@" AND S.SAMPLE_STATION = '{StockNo.Value.ToString()}'"; } strSql += " order by s.lot_no"; var res = DB.RunSelect(strSql); var yearCodeMapping = Common.SNmaker.GetCodeMapping("2YEAR", DB); var MMCodeMapping = Common.SNmaker.GetCodeMapping("HWDSN_MM", DB); var DDCodeMapping = Common.SNmaker.GetCodeMapping("HWDSN_DD", DB); var now = DateTime.Now; for (int i = 0; i < res.Tables[0].Rows.Count; i++) { try { DataRow dr = res.Tables[0].Rows[i]; string SN = dr["SN"].ToString(); string yearCode = SN.Substring(6, 2); string MMCode = SN.Substring(8, 1); string DDCode = SN.Substring(9, 1); var year = yearCodeMapping.Find(T => T.CODEVALUE == yearCode).VALUE; var MM = MMCodeMapping.Find(T => T.CODEVALUE == MMCode).VALUE; var DD = DDCodeMapping.Find(T => T.CODEVALUE == DDCode).VALUE; dr["WORKTIME"] = year + MM + DD; dr["DAYS"] = ((int)(now - DateTime.Parse($@"{year}-{MM}-{DD}")).TotalDays).ToString(); } catch { } } ReportTable retTab = new ReportTable(); retTab.LoadData(res.Tables[0], null); retTab.Tittle = "OBA REPORT"; Outputs.Add(retTab); } catch (Exception e) { ReportAlart alart = new ReportAlart(e.Message); Outputs.Add(alart); } finally { DBPools["SFCDB"].Return(DB); } }
public override void Run() { if (Skuno.Value == null) { throw new Exception("SKUNO Can not be null"); } string skuno = Skuno.Value.ToString(); DataRow linkDataRow = null; string closeflag = CloseFlag.Value.ToString(); OleExec SFCDB = DBPools["SFCDB"].Borrow(); try { string Sqlsku = $@"select workorderno,wo_type,skuno,sku_ver,workorder_qty from r_wo_base where skuno ='{skuno}'"; if (closeflag == "Y") { Sqlsku = Sqlsku + " and CLOSED_FLAG = 1"; } else if (closeflag == "N") { Sqlsku = Sqlsku + " and CLOSED_FLAG = 0"; } DataTable dtsku = SFCDB.RunSelect(Sqlsku).Tables[0]; if (SFCDB != null) { DBPools["SFCDB"].Return(SFCDB); } if (dtsku.Rows.Count == 0) { ReportAlart alart = new ReportAlart("No Data!"); Outputs.Add(alart); return; } DataTable linkTable = new DataTable(); linkTable.Columns.Add("workorderno"); linkTable.Columns.Add("wo_type"); linkTable.Columns.Add("skuno"); linkTable.Columns.Add("sku_ver"); linkTable.Columns.Add("workorder_qty"); for (int i = 0; i < dtsku.Rows.Count; i++) { linkDataRow = linkTable.NewRow(); //跳轉的頁面鏈接 linkDataRow["workorderno"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.WoReport&RunFlag=1&WO=" + dtsku.Rows[i]["workorderno"].ToString() + "&EventName="; linkDataRow["wo_type"] = ""; linkDataRow["skuno"] = ""; linkDataRow["sku_ver"] = ""; linkDataRow["workorder_qty"] = ""; linkTable.Rows.Add(linkDataRow); } ReportTable retTab = new ReportTable(); retTab.LoadData(dtsku, linkTable); retTab.Tittle = "Skuno Report"; Outputs.Add(retTab); } catch (Exception exception) { if (SFCDB != null) { DBPools["SFCDB"].Return(SFCDB); } throw exception; } }
public override void Run() { string skuno = skuInput.Value?.ToString(); string line = lineInput.Value?.ToString(); string eventpoint = eventInput.Value?.ToString(); string from = fromDate.Value?.ToString().Replace("/", "-"); string to = toDate.Value?.ToString().Replace("/", "-"); from = from.Substring(0, from.Length - 3); to = to.Substring(0, to.Length - 3); if (string.IsNullOrEmpty(skuno)) { ReportAlart alart = new ReportAlart("請輸入機種!"); Outputs.Add(alart); return; } try { DataTable dt = GetUphRateDetail(skuno, line, eventpoint, from, to); if (dt.Rows.Count == 0) { ReportAlart alart = new ReportAlart("輸入內容無信息!"); Outputs.Add(alart); return; } #region 時間點去重 重複的重 去重后的 List 為 timeList var tempList = new List <string>(); var timeList = new ArrayList(); for (int i = 0; i < dt.Rows.Count; i++) { tempList.Add(dt.Rows[i]["HOURPERIOD"].ToString()); } for (int i = 0; i < tempList.Count; i++) { if (!timeList.Contains(tempList[i])) { timeList.Add(tempList[i]); } } #endregion #region 構建需要顯示的 DataTable => showDt 把時間點作列名 DataTable showDt = new DataTable(); showDt.Columns.Add("機種"); showDt.Columns.Add("採集點"); showDt.Columns.Add("區分類"); showDt.Columns.Add("UPM*10"); for (int i = 0; i < timeList.Count; i++) { showDt.Columns.Add(timeList[i].ToString()); } #endregion #region 構建內容行 : 機種 | 工站 | 區分類 | UPM*10 | 時:分的值 List <string> qualifyRowList = MakeContentRow("良品數", dt, showDt); //查詢出的所有工站的良品數行的List集合 List <string> unQualifyRowList = MakeContentRow("不良數", dt, showDt); //查詢出的所有工站的不良品數行的List集合 List <string> dataList = new List <string>(); //將良品數行,不良數行,損失率行,良品率行拼在一起的List集合 for (int i = 0; i < qualifyRowList.Count; i++) { dataList.Add(qualifyRowList[i]); dataList.Add(unQualifyRowList[i]); string[] qualifyRowArray = qualifyRowList[i].Split(','); //良品數行的數組 string[] unQualifyRowArray = unQualifyRowList[i].Split(','); //不良數行的數組 string qualifyRateRow = string.Empty; //良品率行 string unQualifyRateRow = string.Empty; //損失率行 //分別拼接良品率行與損失率行字符串 qualifyRateRow += qualifyRowList[i].Split(',')[0] + "," + qualifyRowList[i].Split(',')[1] + ",良品率," + qualifyRowList[i].Split(',')[3]; unQualifyRateRow += qualifyRowList[i].Split(',')[0] + "," + qualifyRowList[i].Split(',')[1] + ",損失率," + qualifyRowList[i].Split(',')[3]; for (int j = 4; j < qualifyRowList[i].Split(',').Length; j++) { double upm10 = Convert.ToDouble(qualifyRowList[i].Split(',')[3]); int total = Convert.ToInt32(qualifyRowList[i].Split(',')[j]) + Convert.ToInt32(unQualifyRowList[i].Split(',')[j]); int good = Convert.ToInt32(qualifyRowList[i].Split(',')[j]); string qualifyRate = (total == 0 ? 0 : (double)good / total).ToString("0.00%"); string unQualifyRate = (upm10 == 0 ? 0 : (double)(upm10 - total) / upm10).ToString("0.00%"); qualifyRateRow += "," + qualifyRate; unQualifyRateRow += "," + unQualifyRate; } dataList.Add(unQualifyRateRow); //先將損失率行Add進List dataList.Add(qualifyRateRow); //再講良品率行Add進List } #endregion #region 根據機種路由工站順序重新排列dataList集合使之呈現最終報表的樣子 => showList DataTable eventDt = GetEventSeqNo(skuno); List <string> showList = new List <string>(); for (int i = 0; i < eventDt.Rows.Count; i++) { for (int j = 0; j < dataList.Count; j++) { if (eventDt.Rows[i]["station_name"].ToString() == dataList[j].Split(',')[1]) { showList.Add(dataList[j]); } } } #endregion #region 循環 showList 填充 showDt 再顯示出來 for (int i = 0; i < showList.Count; i++) { DataRow dtRow = showDt.NewRow(); for (int j = 0; j < showDt.Columns.Count; j++) { dtRow[j] = showList[i].Split(',')[j]; } showDt.Rows.Add(dtRow); } ReportTable retTab = new ReportTable(); retTab.LoadData(showDt, null); retTab.Tittle = "ZERO DEFECT UPH RATE REPORT"; Outputs.Add(retTab); #endregion } catch (Exception ex) { Outputs.Add(new ReportAlart(ex.Message)); } }
public override void Run() { //base.Run(); string sn = snObj.Value.ToString(); string wo = woObj.Value.ToString(); string sqlNoLinkSN = ""; string sqlNoLinkWO = ""; string sqlLinkSN_1 = ""; string sqlLinkSN_2 = ""; string sqlLinkWO_1 = ""; string sqlLinkWO_2 = ""; string sqlNoLink = ""; string sqlLink = ""; if (sn == "" && wo == "") { ReportAlart alart = new ReportAlart("Please input a sn or wo"); Outputs.Add(alart); return; } if (sn != "") { sqlNoLinkSN = $@" and (a.sn='{sn}' or a.boxsn='{sn}') "; sqlLinkSN_1 = $@" and (a.sn='{sn}' or a.boxsn='{sn}') "; sqlLinkSN_2 = $@" and (d.sn='{sn}' or d.boxsn='{sn}') "; } if (wo != "") { sqlNoLinkWO = $@" and a.workorderno='{wo}' "; sqlLinkWO_1 = $@" and a.workorderno='{wo}' "; sqlLinkWO_2 = $@" and d.workorderno='{wo}' "; } sqlNoLink = $@"select a.sn,a.skuno,a.workorderno,a.completed_flag,a.completed_time,a.shipped_flag,a.shipdate,a.current_station,a.next_station,a.edit_time from r_sn a where 1=1 {sqlNoLinkSN} {sqlNoLinkWO} and a.valid_flag='1' and not exists (select * from r_sn_kp b where a.sn=b.value) and not exists (select * from r_sn_keypart_detail c where a.sn=c.keypart_sn) "; sqlLink = $@"select c.workorderno as wo,c.sn, a.workorderno as keypart_wo,a.sn as keypart_sn,b.station as link_station,b.edit_time as link_time,b.edit_emp as link_by from r_sn a,r_sn_kp b,r_sn c where a.sn=b.value and c.id=b.r_sn_id {sqlLinkSN_1} {sqlLinkWO_1} and a.valid_flag='1' and not exists (select * from r_sn_keypart_detail n where a.sn=n.keypart_sn) union select distinct f.workorderno as wo,f.sn, d.workorderno as keypart_wo,d.sn as keypart_sn,e.station_name as link_station,e.edit_time as link_time,e.edit_emp as link_by from r_sn d,r_sn_keypart_detail e,r_sn f where d.sn=e.keypart_sn and e.sn=f.sn and f.valid_flag='1' and d.valid_flag='1' {sqlLinkSN_2} {sqlLinkWO_2} "; OleExec sfcdb = DBPools["SFCDB"].Borrow(); DataTable dtLink = new DataTable(); DataTable dtNoLnk = new DataTable(); try { dtNoLnk = sfcdb.RunSelect(sqlNoLink).Tables[0]; dtLink = sfcdb.RunSelect(sqlLink).Tables[0]; if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportTable reportTable = new ReportTable(); reportTable.LoadData(dtNoLnk, null); reportTable.Tittle = "SN no link detail"; Outputs.Add(reportTable); ReportTable retTab2 = new ReportTable(); retTab2.LoadData(dtLink, null); retTab2.Tittle = "SN link detail"; Outputs.Add(retTab2); } catch (Exception ex) { if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportAlart alart = new ReportAlart(ex.Message); Outputs.Add(alart); } }
public override void Run() { DataTable dt = null; string pack_no = PackNo.Value.ToString(); OleExec sfcdb = DBPools["SFCDB"].Borrow(); DataRow linkDataRow = null; if (pack_no == "" || PackNo.Value == null) { ReportAlart alart = new ReportAlart("PackNo or SN Can not be null"); Outputs.Add(alart); return; } DataTable linkTable = new DataTable(); try { string packSql = $@"select * from r_packing where pack_no ='{pack_no}'"; dt = sfcdb.RunSelect(packSql).Tables[0]; if (dt.Rows.Count == 0) { packSql = $@"SELECT B.sn, B.skuno, B.workorderno, B.CARTON, rppk.pack_no PALLET, B.EDIT_TIME FROM(select rsn.sn, rsn.skuno, rsn.workorderno, rpk.parent_pack_id, rpk.pack_no CARTON, RPK.EDIT_TIME from r_sn rsn, R_SN_PACKING rsnp, r_packing rpk where rsn.SN = '{pack_no}' and rsn.id = rsnp.sn_id and rsnp.pack_id = rpk.id) B LEFT JOIN r_packing rppk ON B.parent_pack_id = rppk.id"; dt = sfcdb.RunSelect(packSql).Tables[0]; if (dt.Rows.Count == 0) { if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportAlart alart = new ReportAlart("No Data!"); Outputs.Add(alart); return; } } else if (dt.Rows[0]["PACK_TYPE"].ToString() == "PALLET") { packSql = $@"select rsn.sn, rsn.skuno, rsn.workorderno, rcp.pack_no carton, rp.pack_no pallet, rcp.edit_time from r_packing rp left join r_packing rcp on rp.id = rcp.parent_pack_id left join r_sn_packing rsp on rcp.id = rsp.pack_id left join r_sn rsn on rsp.sn_id = rsn.id where rp.pack_no = '{pack_no}' order by pallet,carton"; } else { packSql = $@"select b.sn, b.skuno, b.workorderno, b.carton, rppk.pack_no pallet, b.edit_time from (select rsn.sn, rsn.skuno, rsn.workorderno, rp.pack_no carton, rp.parent_pack_id, rp.edit_time from r_packing rp, r_sn_packing rpk, r_sn rsn where rp.pack_no = '{pack_no}' and rp.id = rpk.pack_id and rpk.sn_id = rsn.id) b left join r_packing rppk on b.parent_pack_id = rppk.id order by pallet, carton"; } //string packSql = $@"select r.sn,r.skuno,r.workorderno,rp.pack_no,rp.edit_time from r_sn r,r_sn_packing rsp,r_packing rp // where rp.id =rsp.pack_id and rsp.sn_id = r.id and rp.pack_no ='{pack_no}'"; dt = sfcdb.RunSelect(packSql).Tables[0]; if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } linkTable.Columns.Add("sn"); linkTable.Columns.Add("skuno"); linkTable.Columns.Add("workorderno"); linkTable.Columns.Add("carton"); linkTable.Columns.Add("pallet"); linkTable.Columns.Add("edit_time"); for (int i = 0; i < dt.Rows.Count; i++) { linkDataRow = linkTable.NewRow(); //跳轉的頁面鏈接 linkDataRow["sn"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + dt.Rows[i]["SN"].ToString(); linkDataRow["skuno"] = ""; linkDataRow["workorderno"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.WoReport&RunFlag=1&WO=" + dt.Rows[i]["workorderno"].ToString() + "&EventName="; linkDataRow["carton"] = ""; linkDataRow["pallet"] = ""; linkDataRow["edit_time"] = ""; linkTable.Rows.Add(linkDataRow); } ReportTable reportTable = new ReportTable(); reportTable.LoadData(dt, linkTable); reportTable.Tittle = "SN Packing detail"; Outputs.Add(reportTable); } catch (Exception ex) { if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportAlart alart = new ReportAlart(ex.Message); Outputs.Add(alart); } }
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() { DataTable dt = null; string lot_no = LotNo.Value.ToString(); OleExec sfcdb = DBPools["SFCDB"].Borrow(); DataRow linkDataRow = null; if (lot_no == "" || LotNo.Value == null) { ReportAlart alart = new ReportAlart("lot_no Can not be null"); Outputs.Add(alart); return; } DataTable linkTable = new DataTable(); try { string strSql = $@"select rld.sn, rld.workorderno, rpk.pack_no carton, rlp.packno pallet, decode(rld.status,'','未抽檢',1,'PASS',2,'FAIL') status, rls.lot_no, rls.edit_emp, rld.edit_time from r_lot_status rls, r_lot_pack rlp, r_lot_detail rld, r_sn rsn, r_sn_packing rspk, r_packing rpk where rls.id = rld.lot_id and rlp.lotno = rls.lot_no and rld.sn = rsn.sn and rsn.id = rspk.sn_id and rspk.pack_id = rpk.id and rls.lot_no = '{lot_no}' order by rld.edit_time"; dt = sfcdb.RunSelect(strSql).Tables[0]; if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } if (dt.Rows.Count == 0) { ReportAlart alart = new ReportAlart("No Data!"); Outputs.Add(alart); return; } linkTable.Columns.Add("sn"); linkTable.Columns.Add("skuno"); linkTable.Columns.Add("workorderno"); linkTable.Columns.Add("carton"); linkTable.Columns.Add("pallet"); linkTable.Columns.Add("status"); linkTable.Columns.Add("lotno"); linkTable.Columns.Add("edit_emp"); linkTable.Columns.Add("edit_time"); for (int i = 0; i < dt.Rows.Count; i++) { linkDataRow = linkTable.NewRow(); //跳轉的頁面鏈接 linkDataRow["sn"] = "Link#/FunctionPage/Report/Report.html?ClassName=MESReport.BaseReport.SNReport&RunFlag=1&SN=" + dt.Rows[i]["SN"].ToString(); linkDataRow["skuno"] = ""; linkDataRow["workorderno"] = ""; linkDataRow["carton"] = ""; linkDataRow["pallet"] = ""; linkDataRow["status"] = ""; linkDataRow["lotno"] = ""; linkDataRow["edit_emp"] = ""; linkDataRow["edit_time"] = ""; linkTable.Rows.Add(linkDataRow); } ReportTable reportTable = new ReportTable(); reportTable.LoadData(dt, linkTable); reportTable.Tittle = "OBASAMPLE DETAIL"; Outputs.Add(reportTable); } catch (Exception ex) { if (sfcdb != null) { DBPools["SFCDB"].Return(sfcdb); } ReportAlart alart = new ReportAlart(ex.Message); Outputs.Add(alart); return; } }