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