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);
     }
 }
Example #2
0
        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;
            }
        }