public List <MDataGrapPDMeeting> GetDataPDMeeting(string line, int Month, int Year) { int Target = 0; SqlCommand sqlDataLog = new SqlCommand(); DateTime startDate = new DateTime(Year, Month, 1); DateTime endDate = new DateTime(Year, Month, DateTime.DaysInMonth(Year, Month)); string LineCode = ""; if (line.ToUpper() == "MAIN") { LineCode = "3AMA5"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '301' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "MECHA") { LineCode = "3AME5"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '302' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "3101") { LineCode = "3101"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '304' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "3102") { LineCode = "3102"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '305' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "3103") { LineCode = "3103"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '306' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "3104") { LineCode = "3104"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '307' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } else if (line.ToUpper() == "3105") { LineCode = "3105"; sqlDataLog.CommandText = "SELECT MAX(DailyPlan) AS TargetPlan FROM [DataLog] WHERE BoardId = '308' AND" + " LogTime >= '" + startDate.ToString("yyyy-MM-dd") + "'" + " AND LogTime <= '" + endDate.ToString("yyyy-MM-dd") + "'"; } DataTable dtTarget = oConnPDB.Query(sqlDataLog); if (dtTarget.Rows.Count > 0) { Target = 1300;// Convert.ToInt32(dtTarget.Rows[0]["TargetPlan"].ToString()); } List <MDataGrapPDMeeting> oListPDAch = new List <MDataGrapPDMeeting>(); //------------- Get Data PD Achieve Line Main Fac 3 ---------------- SqlCommand sqlPDAch = new SqlCommand(); SqlCommand sqlPDAchLastMonth = new SqlCommand(); SqlCommand sqlPDAchNextMonth = new SqlCommand(); sqlPDAch.CommandText = String.Format("SELECT * FROM [PD_Achievement] WHERE ProductionLine = '{0}' AND DataMonth='{1}' AND DataYear={2} ", LineCode, startDate.Month, startDate.Year); sqlPDAchLastMonth.CommandText = String.Format("SELECT * FROM [PD_Achievement] WHERE ProductionLine = '{0}' AND DataMonth='{1}' AND DataYear={2} ", LineCode, startDate.AddMonths(-1).Month, startDate.AddMonths(-1).Year); sqlPDAchNextMonth.CommandText = String.Format("SELECT * FROM [PD_Achievement] WHERE ProductionLine = '{0}' AND DataMonth='{1}' AND DataYear={2} ", LineCode, startDate.AddMonths(1).Month, startDate.AddMonths(1).Year); DataTable dtAch = oConn.Query(sqlPDAch); //DataTable dtAchLastMonth = oConn.Query(sqlPDAchLastMonth); //DataTable dtAchNextMonth = oConn.Query(sqlPDAchNextMonth); for (int i = 0; i < DateTime.DaysInMonth(Year, Month); i++) { DateTime dateShow = startDate.AddDays(i); string DayName = "", NightName = ""; bool shiftDay = true; int day = 0, night = 0; int CheckMonth = 0; int CheckYear = 0; day = dateShow.Day; DayName = dateShow.Day.ToString() + " (D)"; night = dateShow.Day; NightName = dateShow.Day.ToString() + " (N)"; CheckMonth = dateShow.Month; CheckYear = dateShow.Year; //------------- Shift Day --------------- MDataGrapPDMeeting oMPdAchDay = new MDataGrapPDMeeting(); oMPdAchDay.Day = DayName; oMPdAchDay.Target = Target; if (dtAch.Rows.Count > 0) { oMPdAchDay.PlanD = dtAch.Rows[0]["Day" + day + "D_Plan"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Plan"].ToString()); oMPdAchDay.ActualD = dtAch.Rows[0]["Day" + day + "D_Actual"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Actual"].ToString()); } oListPDAch.Add(oMPdAchDay); //------------- Shift Night --------------- MDataGrapPDMeeting oMPdAchNight = new MDataGrapPDMeeting(); oMPdAchNight.Day = NightName; oMPdAchNight.Target = Target; if (dtAch.Rows.Count > 0) { oMPdAchNight.PlanD = dtAch.Rows[0]["Day" + day + "N_Plan"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Plan"].ToString()); oMPdAchNight.ActualD = dtAch.Rows[0]["Day" + day + "N_Actual"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Actual"].ToString()); } oListPDAch.Add(oMPdAchNight); #region Old //if (dtAch.Rows.Count > 0) //{ // if (CheckYear == DateTime.Now.Year) // { // if (CheckMonth < DateTime.Now.Month) // { // oMPdAch.PlanD = dtAchLastMonth.Rows[0]["Day" + day + "D_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "D_Plan"].ToString()); // oMPdAch.PlanN = dtAchLastMonth.Rows[0]["Day" + day + "N_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "N_Plan"].ToString()); // oMPdAch.ActualD = dtAchLastMonth.Rows[0]["Day" + day + "D_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "D_Actual"].ToString()); // oMPdAch.ActualN = dtAchLastMonth.Rows[0]["Day" + day + "N_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "N_Actual"].ToString()); // } // else if (CheckMonth > DateTime.Now.Month) // { // oMPdAch.PlanD = dtAchNextMonth.Rows[0]["Day" + day + "D_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "D_Plan"].ToString()); // oMPdAch.PlanN = dtAchNextMonth.Rows[0]["Day" + day + "N_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "N_Plan"].ToString()); // oMPdAch.ActualD = dtAchNextMonth.Rows[0]["Day" + day + "D_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "D_Actual"].ToString()); // oMPdAch.ActualN = dtAchNextMonth.Rows[0]["Day" + day + "N_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "N_Actual"].ToString()); // } // else // { // oMPdAch.PlanD = dtAch.Rows[0]["Day" + day + "D_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Plan"].ToString()); // oMPdAch.PlanN = dtAch.Rows[0]["Day" + day + "N_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Plan"].ToString()); // oMPdAch.ActualD = dtAch.Rows[0]["Day" + day + "D_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Actual"].ToString()); // oMPdAch.ActualN = dtAch.Rows[0]["Day" + day + "N_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Actual"].ToString()); // } // } // else if (CheckYear > DateTime.Now.Year) // { // oMPdAch.PlanD = dtAchNextMonth.Rows[0]["Day" + day + "D_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "D_Plan"].ToString()); // oMPdAch.PlanN = dtAchNextMonth.Rows[0]["Day" + day + "N_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "N_Plan"].ToString()); // oMPdAch.ActualD = dtAchNextMonth.Rows[0]["Day" + day + "D_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "D_Actual"].ToString()); // oMPdAch.ActualN = dtAchNextMonth.Rows[0]["Day" + day + "N_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchNextMonth.Rows[0]["Day" + day + "N_Actual"].ToString()); // } // else // { // oMPdAch.PlanD = dtAchLastMonth.Rows[0]["Day" + day + "D_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "D_Plan"].ToString()); // oMPdAch.PlanN = dtAchLastMonth.Rows[0]["Day" + day + "N_Plan"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "N_Plan"].ToString()); // oMPdAch.ActualD = dtAchLastMonth.Rows[0]["Day" + day + "D_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "D_Actual"].ToString()); // oMPdAch.ActualN = dtAchLastMonth.Rows[0]["Day" + day + "N_Actual"] == // DBNull.Value ? 0 : Convert.ToInt32(dtAchLastMonth.Rows[0]["Day" + day + "N_Actual"].ToString()); // } // oMPdAch.PlanD = oMPdAch.PlanD; // oMPdAch.ActualD = oMPdAch.ActualD; // if (!shiftDay) // { // oMPdAch.PlanD = oMPdAch.PlanN; // oMPdAch.ActualD = oMPdAch.ActualN; // } // if ((oMPdAch.PlanD + oMPdAch.PlanN) > 0) // { // oMPdAch.Achieve = ((oMPdAch.ActualD + oMPdAch.ActualN) * 100) / (oMPdAch.PlanD + oMPdAch.PlanN); // } // else if ((oMPdAch.PlanD + oMPdAch.PlanN) <= 0 && (oMPdAch.ActualD + oMPdAch.ActualN) <= 0) // { // oMPdAch.Achieve = 0; // } // else // { // oMPdAch.Achieve = 0; // } //} #endregion //oListPDAch.Add(oMPdAch); } return(oListPDAch); }
public List <MDataGrapPDMeeting> GetDataPDMeeting(string line) { string LineCode = ""; if (line == "Main") { LineCode = "3AMA5"; } List <MDataGrapPDMeeting> oListPDAch = new List <MDataGrapPDMeeting>(); //------------- Get Data PD Achieve Line Main Fac 3 ---------------- SqlCommand sqlPDAch = new SqlCommand(); sqlPDAch.CommandText = "SELECT * FROM [PD_Achievement] WHERE ProductionLine = '" + LineCode + "' AND DataMonth = '" + DateTime.Now.Month + "' AND DataYear = '" + DateTime.Now.Year + "'"; DataTable dtAch = oConn.Query(sqlPDAch); for (int i = 1; i <= 14; i++) { string DayName = ""; bool shiftDay = true; int day = 0; switch (i) { case 1: day = DateTime.Now.AddDays(-3).Day; DayName = DateTime.Now.AddDays(-3).Day.ToString() + " (D)"; break; case 2: day = DateTime.Now.AddDays(-3).Day; DayName = DateTime.Now.AddDays(-3).Day.ToString() + " (N)"; shiftDay = false; break; case 3: day = DateTime.Now.AddDays(-2).Day; DayName = DateTime.Now.AddDays(-2).Day.ToString() + " (D)"; break; case 4: day = DateTime.Now.AddDays(-2).Day; DayName = DateTime.Now.AddDays(-2).Day.ToString() + " (N)"; shiftDay = false; break; case 5: day = DateTime.Now.AddDays(-1).Day; DayName = DateTime.Now.AddDays(-1).Day.ToString() + " (D)"; break; case 6: day = DateTime.Now.AddDays(-1).Day; DayName = DateTime.Now.AddDays(-1).Day.ToString() + " (N)"; shiftDay = false; break; case 7: day = DateTime.Now.Day; DayName = DateTime.Now.Day.ToString() + " (D)"; break; case 8: day = DateTime.Now.Day; DayName = DateTime.Now.Day.ToString() + " (N)"; shiftDay = false; break; case 9: day = DateTime.Now.AddDays(1).Day; DayName = DateTime.Now.AddDays(1).Day.ToString() + " (D)"; break; case 10: day = DateTime.Now.AddDays(1).Day; DayName = DateTime.Now.AddDays(1).Day.ToString() + " (N)"; shiftDay = false; break; case 11: day = DateTime.Now.AddDays(2).Day; DayName = DateTime.Now.AddDays(2).Day.ToString() + " (D)"; break; case 12: day = DateTime.Now.AddDays(2).Day; DayName = DateTime.Now.AddDays(2).Day.ToString() + " (N)"; shiftDay = false; break; case 13: day = DateTime.Now.AddDays(3).Day; DayName = DateTime.Now.AddDays(3).Day.ToString() + " (D)"; break; case 14: day = DateTime.Now.AddDays(3).Day; DayName = DateTime.Now.AddDays(3).Day.ToString() + " (N)"; shiftDay = false; break; } MDataGrapPDMeeting oMPdAch = new MDataGrapPDMeeting(); oMPdAch.Day = DayName; oMPdAch.Target = 92; //if (day > DateTime.Now.Day) //{ // //oMPdAch.PlanD = null; // //oMPdAch.PlanN = null; // //oMPdAch.ActualD = null; // //oMPdAch.ActualN = null; //} //else //{ if (dtAch.Rows.Count > 0) { oMPdAch.PlanD = dtAch.Rows[0]["Day" + day + "D_Plan"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Plan"].ToString()); oMPdAch.PlanN = dtAch.Rows[0]["Day" + day + "N_Plan"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Plan"].ToString()); oMPdAch.PlanD = oMPdAch.PlanD; oMPdAch.ActualD = dtAch.Rows[0]["Day" + day + "D_Actual"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "D_Actual"].ToString()); oMPdAch.ActualN = dtAch.Rows[0]["Day" + day + "N_Actual"] == DBNull.Value ? 0 : Convert.ToInt32(dtAch.Rows[0]["Day" + day + "N_Actual"].ToString()); oMPdAch.ActualD = oMPdAch.ActualD; if (!shiftDay) { oMPdAch.PlanD = oMPdAch.PlanN; oMPdAch.ActualD = oMPdAch.ActualN; } if ((oMPdAch.PlanD + oMPdAch.PlanN) > 0) { oMPdAch.Achieve = ((oMPdAch.ActualD + oMPdAch.ActualN) * 100) / (oMPdAch.PlanD + oMPdAch.PlanN); //oMPdAch.Achieve = 100; } else if ((oMPdAch.PlanD + oMPdAch.PlanN) <= 0 && (oMPdAch.ActualD + oMPdAch.ActualN) <= 0) { oMPdAch.Achieve = 0; } else { oMPdAch.Achieve = 0; } } //} oListPDAch.Add(oMPdAch); } return(oListPDAch); }