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