private AndonDataLog GetActual(string boardId, DateTime startDate, DateTime endDate) { AndonDataLog dataLog = new AndonDataLog(); DataTable dtLine = new DataTable(); SqlCommand sqlSelect = new SqlCommand(); sqlSelect.CommandText = "SELECT TOP 1 BoardId, DailyPlan, Actual, Diff FROM DataLog WHERE BoardId = @BoardId AND Shift != '-' AND LogTime BETWEEN @StartDate AND @EndDate ORDER BY LogTime DESC"; sqlSelect.CommandTimeout = 180; sqlSelect.Parameters.Add(new SqlParameter("@BoardId", boardId)); sqlSelect.Parameters.Add(new SqlParameter("@StartDate", startDate)); sqlSelect.Parameters.Add(new SqlParameter("@EndDate", endDate)); dtLine = conPDB.Query(sqlSelect); if (dtLine.Rows.Count > 0) { dataLog.Actual = Convert.ToDecimal(dtLine.Rows[0]["Actual"].ToString()); dataLog.BoardId = dtLine.Rows[0]["BoardId"].ToString(); dataLog.DailyPlan = Convert.ToDecimal(dtLine.Rows[0]["DailyPlan"].ToString()); dataLog.Diff = Convert.ToDecimal(dtLine.Rows[0]["Diff"].ToString()); } return(dataLog); }
protected void btnSearch_Click(object sender, EventArgs e) { selectDate = DateTime.ParseExact(txtDate.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture); DateTime startDateDay = new DateTime(); DateTime endDateDay = new DateTime(); DateTime startDateNight = new DateTime(); DateTime endDateNight = new DateTime(); startDateDay = Convert.ToDateTime(selectDate.ToString("yyyy-MM-dd") + " 08:00:00.000"); endDateDay = Convert.ToDateTime(selectDate.ToString("yyyy-MM-dd") + " 19:59:59.000"); startDateNight = Convert.ToDateTime(selectDate.ToString("yyyy-MM-dd") + " 20:00:00.000"); endDateNight = Convert.ToDateTime(selectDate.AddDays(1).ToString("yyyy-MM-dd") + " 07:59:00.000"); // Load DataTable of Fac 3 Line DataTable dtLine = new DataTable(); SqlCommand sqlSelect = new SqlCommand(); sqlSelect.CommandText = "SELECT Line.LineId, Line.LineCode, Line.LineName, board.BoardId FROM PD_LineMstr AS Line INNER JOIN BoardData AS board ON Line.BoardId = board.BoardId WHERE Line.Factory ='3' ORDER BY board.BoardId ASC"; sqlSelect.CommandTimeout = 180; dtLine = conPDB.Query(sqlSelect); if (dtLine.Rows.Count > 0) { foreach (DataRow row in dtLine.Rows) { // Get BoardId string boardId = row["BoardId"].ToString(); // Get ACTUAL AndonDataLog dataLogDay = GetActual(boardId, startDateDay, endDateDay); AndonDataLog dataLogNight = GetActual(boardId, startDateNight, endDateNight); PdLineProductInfo product = new PdLineProductInfo(); product.BoardId = boardId; product.LineCode = row["LineCode"].ToString(); product.LineName = row["LineName"].ToString(); product.DataDate = startDateDay; // DAY product.ActualDay = dataLogDay.Actual; product.DiffDay = dataLogDay.Diff; product.PlanDay = dataLogDay.DailyPlan; // NIGHT product.ActualNight = dataLogNight.Actual; product.DiffNight = dataLogNight.Diff; product.PlanNight = dataLogNight.DailyPlan; if (boardId == "301") { product.TotalNgDay = GetTotalMainAssyNg(startDateDay, endDateDay); product.TotalNgNight = GetTotalMainAssyNg(startDateNight, endDateNight); } else { product.TotalNgDay = 0; product.TotalNgNight = 0; } products.Add(product); } } if (products.Count > 0) { rptData.DataSource = products; rptData.DataBind(); } else { rptData.DataSource = null; rptData.DataBind(); } }