public override void Init() { if (SelectBy.Value.ToString() == "日期段") { startTime.Value = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + " 08:00:00"; endTime.Value = DateTime.Now.ToString("yyyy-MM-dd") + " 08:00:00"; month.Value = ""; week.Value = ""; } else if (SelectBy.Value.ToString() == "月") { startTime.Value = ""; endTime.Value = ""; month.Value = DateTime.Now.Month.ToString(); week.Value = ""; } else if (SelectBy.Value.ToString() == "周") { startTime.Value = ""; endTime.Value = ""; month.Value = ""; week.Value = ConverDate.GetWeekFromDate(DateTime.Now); } }
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() { DateTime dateFrom = DateTime.Now; DateTime dateTO = DateTime.Now; DataTable dtFailCode = new DataTable(); DataTable loadTable = new DataTable(); DataTable linkTable = new DataTable(); DataRow loadTitleRow = null; DataRow loadDataRow = null; DataRow linkTitleRow = null; DataRow linkDataRow = null; string failCode = ""; int col = 0; 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 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.create_time = a.create_time inner join c_sku d on d.skuno = b.skuno where a.closed_flag = 1 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 { dtFailCode = SFCDB.RunSelect(sqlRun).Tables[0]; for (int top = 0; top <= 11; top++) { if (top == 0) { loadTable.Columns.Add("FAIL_CODE"); linkTable.Columns.Add("FAIL_CODE"); } else if (top == 1) { loadTable.Columns.Add("TOTAL"); linkTable.Columns.Add("TOTAL"); } else { loadTable.Columns.Add("TOP" + (top - 1).ToString()); linkTable.Columns.Add("TOP" + (top - 1).ToString()); } } for (int i = 0; i < dtFailCode.Rows.Count; i++) { if (failCode != dtFailCode.Rows[i]["fail_code"].ToString()) { failCode = dtFailCode.Rows[i]["fail_code"].ToString(); loadTitleRow = loadTable.NewRow(); loadDataRow = loadTable.NewRow(); linkTitleRow = linkTable.NewRow(); linkDataRow = linkTable.NewRow(); loadTitleRow["FAIL_CODE"] = failCode; col = 0; loadTable.Rows.Add(loadTitleRow); loadTable.Rows.Add(loadDataRow); linkTable.Rows.Add(linkTitleRow); linkTable.Rows.Add(linkDataRow); loadDataRow["TOTAL"] = 0; linkTitleRow["FAIL_CODE"] = "MESReport.BaseReport.RepairFailCodeDetail"; } col++; if (col > 10) { continue; } loadTitleRow[col + 1] = dtFailCode.Rows[i]["sku_name"].ToString(); loadDataRow[col + 1] = dtFailCode.Rows[i]["count"].ToString(); loadDataRow["TOTAL"] = (Int32.Parse(loadDataRow["TOTAL"].ToString())) + Int32.Parse(dtFailCode.Rows[i]["count"].ToString()); } ReportTable reportTable = new ReportTable(); reportTable.LoadData(loadTable, linkTable); reportTable.Tittle = "RepairTopByFailTable"; Outputs.Add(reportTable); DBPools["SFCDB"].Return(SFCDB); } catch (Exception exception) { DBPools["SFCDB"].Return(SFCDB); throw exception; } }