/// <summary>
/// Loads the data that will be used in the KPI sections for data calculations
/// </summary>
public void LoadKPITables(AccessInfo.MainTables country)
{
prsOnPOsDt = new DataTable();
posRecCompDt = new DataTable();
pr2ndLvlRelDateDt = new DataTable();
AllDt = new DataTable();
try
{
using (OleDbCommand cmd = new OleDbCommand()
{
Connection = PRPO_DB_Utils.DatabaseConnection
})
{
using (OleDbDataAdapter da = new OleDbDataAdapter())
{
cmd.CommandText = PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.AllPOs] + Filters.FilterQuery;
da.SelectCommand = cmd;
da.Fill(prsOnPOsDt);
cmd.CommandText = PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.POLinesRecComplete] + Filters.FilterQuery;
da.SelectCommand = cmd;
da.Fill(posRecCompDt);
cmd.CommandText = PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.PR_2ndLvlRel] + Filters.FilterQuery;
da.SelectCommand = cmd;
da.Fill(pr2ndLvlRelDateDt);
if (Filters.FilterQuery == string.Empty)
{
cmd.CommandText = PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.AllData];
}
else
{
cmd.CommandText = PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.AllData] + " WHERE " + Filters.SecondaryFilterQuery;
}
da.SelectCommand = cmd;
da.Fill(AllDt);
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
PRPO_DB_Utils.KPITablesLoaded = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI Data Load");
}
}
/// <summary>
/// Loads the data for the specific KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR 2nd Level Release vs PO Creation
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
pr2ndLvlRelVsPOCreation.data.Total = Overall.prsOnPOsDt.Rows.Count;
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPOLineCreateDate = (dr["PO Line Creat#DT"].ToString()).Split('/');
int poCreateDtYear = int.Parse(strPOLineCreateDate[2]);
int poCreateDtMonth = int.Parse(strPOLineCreateDate[0].TrimStart('0'));
int poCreateDtDay = int.Parse(strPOLineCreateDate[1].TrimStart('0'));
DateTime poLineCreateDate = new DateTime(poCreateDtYear, poCreateDtMonth, poCreateDtDay);
string[] strPR2ndLvlRelDate = (dr["PR 2° Rel# Date"].ToString()).Split('/');
int secLvlRelYear = int.Parse(strPR2ndLvlRelDate[2]);
int secLvlRelMonth = int.Parse(strPR2ndLvlRelDate[0].TrimStart('0'));
int secLvlRelDay = int.Parse(strPR2ndLvlRelDate[1].TrimStart('0'));
DateTime pr2ndLvlRelDate = new DateTime(secLvlRelYear, secLvlRelMonth, secLvlRelDay);
double elapsedDays = (poLineCreateDate - pr2ndLvlRelDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
pr2ndLvlRelVsPOCreation.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
pr2ndLvlRelVsPOCreation.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
pr2ndLvlRelVsPOCreation.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
pr2ndLvlRelVsPOCreation.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
pr2ndLvlRelVsPOCreation.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
pr2ndLvlRelVsPOCreation.data.TwentyTwo_TwentyEight++;
}
else if (elapsedDays >= 29 && elapsedDays <= 35)
{
pr2ndLvlRelVsPOCreation.data.TwentyNine_ThirtyFive++;
}
else if (elapsedDays >= 36 && elapsedDays <= 42)
{
pr2ndLvlRelVsPOCreation.data.ThirtySix_FourtyTwo++;
}
else if (elapsedDays >= 43 && elapsedDays <= 49)
{
pr2ndLvlRelVsPOCreation.data.FourtyThree_FourtyNine++;
}
else if (elapsedDays >= 50)
{
pr2ndLvlRelVsPOCreation.data.greaterThanEqualFifty++;
}
}
try
{
pr2ndLvlRelVsPOCreation.data.Average = Math.Round(totalDays / pr2ndLvlRelVsPOCreation.data.Total, 2);
}
catch (DivideByZeroException)
{
pr2ndLvlRelVsPOCreation.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PO Creation vs PO Release
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPOLine1stRelDt = (dr["PO Line 1st Rel Dt"].ToString()).Split('/');
int poLine1stRelDtYear = int.Parse(strPOLine1stRelDt[2]);
int poLine1stRelDtMonth = int.Parse(strPOLine1stRelDt[0]);
int poLine1stRelDtDay = int.Parse(strPOLine1stRelDt[1]);
if (poLine1stRelDtYear == 0 && poLine1stRelDtMonth == 0 && poLine1stRelDtDay == 0)
{
continue;
}
else
{
poCreationVsPORel.data.Total++;
poLine1stRelDtYear = int.Parse(strPOLine1stRelDt[2]);
poLine1stRelDtMonth = int.Parse(strPOLine1stRelDt[0].TrimStart('0'));
poLine1stRelDtDay = int.Parse(strPOLine1stRelDt[1].TrimStart('0'));
}
DateTime poLine1stRelDate = new DateTime(poLine1stRelDtYear, poLine1stRelDtMonth, poLine1stRelDtDay);
string[] strPOLineCreateDt = (dr["PO Line Creat#DT"].ToString()).Split('/');
int poOLineCreateDtYear = int.Parse(strPOLineCreateDt[2]);
int poOLineCreateDtMonth = int.Parse(strPOLineCreateDt[0].TrimStart('0'));
int poOLineCreateDtDay = int.Parse(strPOLineCreateDt[1].TrimStart('0'));
DateTime poCreateDate = new DateTime(poOLineCreateDtYear, poOLineCreateDtMonth, poOLineCreateDtDay);
double elapsedDays = (poLine1stRelDate - poCreateDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
poCreationVsPORel.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
poCreationVsPORel.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
poCreationVsPORel.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
poCreationVsPORel.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
poCreationVsPORel.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
poCreationVsPORel.data.TwentyTwo_TwentyEight++;
}
else if (elapsedDays >= 29 && elapsedDays <= 35)
{
poCreationVsPORel.data.TwentyNine_ThirtyFive++;
}
else if (elapsedDays >= 36 && elapsedDays <= 42)
{
poCreationVsPORel.data.ThirtySix_FourtyTwo++;
}
else if (elapsedDays >= 43 && elapsedDays <= 49)
{
poCreationVsPORel.data.FourtyThree_FourtyNine++;
}
else if (elapsedDays >= 50)
{
poCreationVsPORel.data.greaterThanEqualFifty++;
}
}
try
{
poCreationVsPORel.data.Average = Math.Round(totalDays / poCreationVsPORel.data.Total, 2);
}
catch (DivideByZeroException)
{
poCreationVsPORel.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PO Release vs PO Confirm
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPOLineFirstRelDate = (dr["PO Line 1st Rel Dt"].ToString()).Split('/');
int poLineFirstRelDateYear = int.Parse(strPOLineFirstRelDate[2]);
int poLineFirstRelDateMonth = int.Parse(strPOLineFirstRelDate[0]);
int poLineFirstRelDateDay = int.Parse(strPOLineFirstRelDate[1]);
if (poLineFirstRelDateYear == 0 && poLineFirstRelDateMonth == 0 && poLineFirstRelDateDay == 0)
{
continue;
}
else
{
poLineFirstRelDateYear = int.Parse(strPOLineFirstRelDate[2]);
poLineFirstRelDateMonth = int.Parse(strPOLineFirstRelDate[0].TrimStart('0'));
poLineFirstRelDateDay = int.Parse(strPOLineFirstRelDate[1].TrimStart('0'));
}
DateTime poLineFirstRelDate = new DateTime(poLineFirstRelDateYear, poLineFirstRelDateMonth, poLineFirstRelDateDay);
string[] strPOLineFirstConfCreateDate = (dr["1st Conf Creation Da"].ToString()).Split('/');
int poLineFirstConfCreateYear = int.Parse(strPOLineFirstConfCreateDate[2]);
int poLineFirstConfCreateMonth = int.Parse(strPOLineFirstConfCreateDate[0]);
int poLineFirstConfCreateDay = int.Parse(strPOLineFirstConfCreateDate[1]);
if (poLineFirstConfCreateYear == 0 && poLineFirstConfCreateMonth == 0 && poLineFirstConfCreateDay == 0)
{
totalUnconf++;
continue;
}
else
{
poLineFirstConfCreateYear = int.Parse(strPOLineFirstConfCreateDate[2]);
poLineFirstConfCreateMonth = int.Parse(strPOLineFirstConfCreateDate[0].TrimStart('0'));
poLineFirstConfCreateDay = int.Parse(strPOLineFirstConfCreateDate[1].TrimStart('0'));
}
DateTime poLineFirstConfCreateDt = new DateTime(poLineFirstConfCreateYear, poLineFirstConfCreateMonth, poLineFirstConfCreateDay);
double elapsedDays = (poLineFirstConfCreateDt - poLineFirstRelDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
poRelVsPOConf.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
poRelVsPOConf.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
poRelVsPOConf.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
poRelVsPOConf.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
poRelVsPOConf.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
poRelVsPOConf.data.TwentyTwo_TwentyEight++;
}
else if (elapsedDays >= 29 && elapsedDays <= 35)
{
poRelVsPOConf.data.TwentyNine_ThirtyFive++;
}
else if (elapsedDays >= 36 && elapsedDays <= 42)
{
poRelVsPOConf.data.ThirtySix_FourtyTwo++;
}
else if (elapsedDays >= 43 && elapsedDays <= 49)
{
poRelVsPOConf.data.FourtyThree_FourtyNine++;
}
else if (elapsedDays >= 50)
{
poRelVsPOConf.data.greaterThanEqualFifty++;
}
}
poRelVsPOConf.data.Total = Overall.prsOnPOsDt.Rows.Count - (int)totalUnconf;
try
{
poRelVsPOConf.data.Average = Math.Round(totalDays / poRelVsPOConf.data.Total, 2);
}
catch (DivideByZeroException)
{
poRelVsPOConf.data.Average = 0;
}
try
{
poRelVsPOConf.data.PercentUnconf = (int)((totalUnconf / poRelVsPOConf.data.Total) * 100);
}
catch (DivideByZeroException)
{
poRelVsPOConf.data.PercentUnconf = 0;
}
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace, "KPI -> Purch Two Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data of a specific KPA
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PRs Aging (Not Released)
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DataTable dt = new DataTable();
OleDbCommand cmd;
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_Plan_PRsAgingNotRel] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_Plan_PRsAgingNotRel] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
// The user wants to filter by PR date range
if (Filters.PrDateRangeFilterAdded)
{
string[] strPrDate = (dr["Requisn Date"].ToString().Split('/'));
int prYear = int.Parse(strPrDate[2]);
int prMonth = int.Parse(strPrDate[0].TrimStart('0'));
int prDay = int.Parse(strPrDate[1].TrimStart('0'));
DateTime prDate = new DateTime(prYear, prMonth, prDay);
if (DateTime.Compare(prDate, Filters.PrFromDate) < 0)
{
continue; // The PR date is not within the filtered PR date range
}
else
{
if (DateTime.Compare(prDate, Filters.PrToDate) > 0)
{
continue; // The PR date is not within the filtered PR date range
}
}
}
// The user wants to filter by PO date range
if (Filters.PoDateRangeFilterAdded)
{
string[] strPoDate = (dr["PO Date"].ToString().Split('/'));
int poYear = int.Parse(strPoDate[2]);
int poMonth = int.Parse(strPoDate[0]);
int poDay = int.Parse(strPoDate[1]);
if (poYear == 0 && poMonth == 0 && poDay == 0)
{
continue;
}
else
{
poYear = int.Parse(strPoDate[2]);
poMonth = int.Parse(strPoDate[0].TrimStart('0'));
poDay = int.Parse(strPoDate[1].TrimStart('0'));
}
DateTime poDate = new DateTime(poYear, poMonth, poDay);
if (DateTime.Compare(poDate, Filters.PoFromDate) < 0)
{
continue; // The PR date is not within the filtered PR date range
}
else
{
if (DateTime.Compare(poDate, Filters.PoToDate) > 0)
{
continue; // The PR date is not within the filtered PR date range
}
}
}
prsAgingNotRel.data.Total++;
string[] reqCreationDate = (dr["Requisn Date"].ToString()).Split('/');
int year = int.Parse(reqCreationDate[2]);
int month = int.Parse(reqCreationDate[0].TrimStart('0'));
int day = int.Parse(reqCreationDate[1].TrimStart('0'));
DateTime reqDate = new DateTime(year, month, day);
DateTime today = DateTime.Now.Date;
double elapsedDays = (today - reqDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
prsAgingNotRel.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
prsAgingNotRel.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
prsAgingNotRel.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
prsAgingNotRel.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
prsAgingNotRel.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
prsAgingNotRel.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
prsAgingNotRel.data.TwentyNinePlus++;
}
}
try
{
prsAgingNotRel.data.Average = Math.Round(totalDays / prsAgingNotRel.data.Total, 2);
}
catch (DivideByZeroException)
{
prsAgingNotRel.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Material Due
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
dt = new DataTable();
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_Plan_MaterialDue] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_Plan_MaterialDue] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
da = new OleDbDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
// The user wants to filter by PR date range
if (Filters.PrDateRangeFilterAdded)
{
string[] strPrDate = (dr["Requisn Date"].ToString().Split('/'));
int prYear = int.Parse(strPrDate[2]);
int prMonth = int.Parse(strPrDate[0].TrimStart('0'));
int prDay = int.Parse(strPrDate[1].TrimStart('0'));
DateTime prDate = new DateTime(prYear, prMonth, prDay);
if (DateTime.Compare(prDate, Filters.PrFromDate) < 0)
{
continue; // The PR date is not within the filtered PR date range
}
else
{
if (DateTime.Compare(prDate, Filters.PrToDate) > 0)
{
continue; // The PR date is not within the filtered PR date range
}
}
}
// The user wants to filter by PO date range
if (Filters.PoDateRangeFilterAdded)
{
string[] strPoDate = (dr["PO Date"].ToString().Split('/'));
int poYear = int.Parse(strPoDate[2]);
int poMonth = int.Parse(strPoDate[0]);
int poDay = int.Parse(strPoDate[1]);
if (poYear == 0 && poMonth == 0 && poDay == 0)
{
continue;
}
else
{
poYear = int.Parse(strPoDate[2]);
poMonth = int.Parse(strPoDate[0].TrimStart('0'));
poDay = int.Parse(strPoDate[1].TrimStart('0'));
}
DateTime poDate = new DateTime(poYear, poMonth, poDay);
if (DateTime.Compare(poDate, Filters.PoFromDate) < 0)
{
continue; // The PR date is not within the filtered PR date range
}
else
{
if (DateTime.Compare(poDate, Filters.PoToDate) > 0)
{
continue; // The PR date is not within the filtered PR date range
}
}
}
matDueDate.data.Total++;
string[] strCurrReqDate = (dr["PR Delivery Date"].ToString()).Split('/');
int year = int.Parse(strCurrReqDate[2]);
int month = int.Parse(strCurrReqDate[0].TrimStart('0'));
int day = int.Parse(strCurrReqDate[1].TrimStart('0'));
DateTime currReqDate = new DateTime(year, month, day);
DateTime today = DateTime.Now.Date;
double elapsedDays = (currReqDate - today).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
matDueDate.data.LessThanZero++;
}
else if (elapsedDays <= 3)
{
matDueDate.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
matDueDate.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
matDueDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
matDueDate.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
matDueDate.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
matDueDate.data.TwentyNinePlus++;
}
}
try
{
matDueDate.data.Average = Math.Round(totalDays / matDueDate.data.Total, 2);
}
catch (DivideByZeroException)
{
matDueDate.data.Average = 0;
}
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPA -> Plan Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data for the specific KPA
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR Release to PO Release
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DataTable dt = new DataTable();
OleDbCommand cmd;
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_PurchSub_PRReleasePORelease] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_PurchSub_PRReleasePORelease] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
prRelToPORel.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strDate = (dr["PR 2° Rel# Date"].ToString()).Split('/');
int year = int.Parse(strDate[2]);
int month = int.Parse(strDate[0].TrimStart('0'));
int day = int.Parse(strDate[1].TrimStart('0'));
DateTime date = new DateTime(year, month, day);
DateTime today = DateTime.Now.Date;
double elapsedDays = (today - date).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
prRelToPORel.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
prRelToPORel.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
prRelToPORel.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
prRelToPORel.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
prRelToPORel.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
prRelToPORel.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
prRelToPORel.data.TwentyNinePlus++;
}
}
try
{
prRelToPORel.data.Average = Math.Round(totalDays / prRelToPORel.data.Total, 2);
}
catch (DivideByZeroException)
{
prRelToPORel.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PO Creation Date to Confirmation Entry
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
dt = new DataTable();
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_PurchSub_POCreationCOnfEntry] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_PurchSub_POCreationCOnfEntry] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
da = new OleDbDataAdapter(cmd);
da.Fill(dt);
POCreatToConfEntry.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strDate = (dr["PO Line Creat#DT"].ToString()).Split('/');
int year = int.Parse(strDate[2]);
int month = int.Parse(strDate[0].TrimStart('0'));
int day = int.Parse(strDate[1].TrimStart('0'));
DateTime date = new DateTime(year, month, day);
DateTime today = DateTime.Now.Date;
double elapsedDays = (today - date).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
POCreatToConfEntry.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
POCreatToConfEntry.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
POCreatToConfEntry.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
POCreatToConfEntry.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
POCreatToConfEntry.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
POCreatToConfEntry.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
POCreatToConfEntry.data.TwentyNinePlus++;
}
}
try
{
POCreatToConfEntry.data.Average = Math.Round(totalDays / POCreatToConfEntry.data.Total, 2);
}
catch (DivideByZeroException)
{
POCreatToConfEntry.data.Average = 0;
}
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPA -> Purch Sub Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data for the specific KPA
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Confirmed vs Plan Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DataTable dt = new DataTable();
OleDbCommand cmd;
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_FollowUp_ConfPlanDate] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_FollowUp_ConfPlanDate] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
confDateVsPlanDate.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strCurrConfDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int delConfYear = int.Parse(strCurrConfDate[2]);
int delConfMonth = int.Parse(strCurrConfDate[0].TrimStart('0'));
int delConfDay = int.Parse(strCurrConfDate[1].TrimStart('0'));
DateTime delConfDate = new DateTime(delConfYear, delConfMonth, delConfDay);
string[] strCurrPlanDate = (dr["Rescheduling date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrPlanDate[2]);
int currConfMonth = int.Parse(strCurrPlanDate[0]);
int currConfDay = int.Parse(strCurrPlanDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
string[] strNewCurrConfDate = (dr["Delivery Date"].ToString()).Split('/');
currConfYear = int.Parse(strNewCurrConfDate[2]);
currConfMonth = int.Parse(strNewCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strNewCurrConfDate[1].TrimStart('0'));
}
else
{
currConfYear = int.Parse(strCurrPlanDate[2]);
currConfMonth = int.Parse(strCurrPlanDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrPlanDate[1].TrimStart('0'));
}
DateTime currPlanDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (delConfDate - currPlanDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
confDateVsPlanDate.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
confDateVsPlanDate.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
confDateVsPlanDate.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
confDateVsPlanDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
confDateVsPlanDate.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
confDateVsPlanDate.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
confDateVsPlanDate.data.TwentyNinePlus++;
}
}
try
{
confDateVsPlanDate.data.Average = Math.Round(totalDays / confDateVsPlanDate.data.Total, 2);
}
catch (DivideByZeroException)
{
confDateVsPlanDate.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Confirmed Date for Upcoing Deliveries
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
dt = new DataTable();
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_FollowUp_ConfDateUpcomingDel] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_FollowUp_ConfDateUpcomingDel] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ConfDateForUpcomingDel.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int year = int.Parse(strDate[2]);
int month = int.Parse(strDate[0].TrimStart('0'));
int day = int.Parse(strDate[1].TrimStart('0'));
DateTime date = new DateTime(year, month, day);
DateTime today = DateTime.Now.Date;
double elapsedDays = (date - today).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
ConfDateForUpcomingDel.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
ConfDateForUpcomingDel.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
ConfDateForUpcomingDel.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
ConfDateForUpcomingDel.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
ConfDateForUpcomingDel.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
ConfDateForUpcomingDel.data.TwentyTwo_TwentyEight++;
}
else // 29+
{
ConfDateForUpcomingDel.data.TwentyNinePlus++;
}
}
try
{
ConfDateForUpcomingDel.data.Average = Math.Round(totalDays / ConfDateForUpcomingDel.data.Total, 2);
}
catch (DivideByZeroException)
{
ConfDateForUpcomingDel.data.Average = 0;
}
totalDays = 0;
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPA -> Follow Up Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data of a specific KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Initial Confirmation vs Current Confirmation
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.posRecCompDt.Rows)
{
string[] strFirstConfDate = (dr["1st Conf Date"].ToString()).Split('/');
int firstConfYear = int.Parse(strFirstConfDate[2]);
int firstConfMonth = int.Parse(strFirstConfDate[0]);
int firstConfDay = int.Parse(strFirstConfDate[1]);
if (firstConfYear == 0 && firstConfMonth == 0 && firstConfDay == 0)
{
totalUnconfPOs++;
continue;
}
else
{
firstConfYear = int.Parse(strFirstConfDate[2]);
firstConfMonth = int.Parse(strFirstConfDate[0].TrimStart('0'));
firstConfDay = int.Parse(strFirstConfDate[1].TrimStart('0'));
}
DateTime firstConfDate = new DateTime(firstConfYear, firstConfMonth, firstConfDay);
string[] delConfDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int delConfYear = int.Parse(delConfDate[2]);
int delConfMonth = int.Parse(delConfDate[0].TrimStart('0'));
int delConfDay = int.Parse(delConfDate[1].TrimStart('0'));
DateTime prPlanDate = new DateTime(delConfYear, delConfMonth, delConfDay);
double elapsedDays = (firstConfDate - prPlanDate).TotalDays;
totalDays += elapsedDays;
if (elapsedDays <= (-22))
{
initConfVsCurrConf.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
initConfVsCurrConf.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
initConfVsCurrConf.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
initConfVsCurrConf.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
initConfVsCurrConf.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
initConfVsCurrConf.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
initConfVsCurrConf.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
initConfVsCurrConf.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
initConfVsCurrConf.data.TwentyTwo++;
}
}
initConfVsCurrConf.data.Total = Overall.posRecCompDt.Rows.Count - (int)totalUnconfPOs;
try
{
initConfVsCurrConf.data.PercentUnconf = (int)((totalUnconfPOs / Overall.posRecCompDt.Rows.Count) * 100);
}
catch (DivideByZeroException)
{
initConfVsCurrConf.data.PercentUnconf = 0;
}
try
{
initConfVsCurrConf.data.Average = Math.Round(totalDays / initConfVsCurrConf.data.Total, 2);
}
catch (DivideByZeroException)
{
initConfVsCurrConf.data.Average = 0;
}
totalDays = 0;
totalUnconfPOs = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Final Confirmation Date vs Final Planned Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
finalConfDateVsFinalPlan.data.Total = Overall.posRecCompDt.Rows.Count;
foreach (DataRow dr in Overall.posRecCompDt.Rows)
{
string[] strDelConfDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int firstConfYear = int.Parse(strDelConfDate[2]);
int firstConfMonth = int.Parse(strDelConfDate[0]);
int firstConfDay = int.Parse(strDelConfDate[1]);
if (firstConfYear == 0 && firstConfMonth == 0 && firstConfDay == 0)
{
totalUnconfPOs++;
continue;
}
else
{
firstConfYear = int.Parse(strDelConfDate[2]);
firstConfMonth = int.Parse(strDelConfDate[0].TrimStart('0'));
firstConfDay = int.Parse(strDelConfDate[1].TrimStart('0'));
}
DateTime firstConfDate = new DateTime(firstConfYear, firstConfMonth, firstConfDay);
string[] strPrDelDate = (dr["PR Delivery Date"].ToString()).Split('/');
int delConfYear = int.Parse(strPrDelDate[2]);
int delConfMonth = int.Parse(strPrDelDate[0].TrimStart('0'));
int delConfDay = int.Parse(strPrDelDate[1].TrimStart('0'));
DateTime prPlanDate = new DateTime(delConfYear, delConfMonth, delConfDay);
double elapsedDays = (firstConfDate - prPlanDate).TotalDays;
totalDays += elapsedDays;
if (elapsedDays <= (-22))
{
finalConfDateVsFinalPlan.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
finalConfDateVsFinalPlan.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
finalConfDateVsFinalPlan.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
finalConfDateVsFinalPlan.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
finalConfDateVsFinalPlan.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
finalConfDateVsFinalPlan.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
finalConfDateVsFinalPlan.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
finalConfDateVsFinalPlan.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
finalConfDateVsFinalPlan.data.TwentyTwo++;
}
}
try
{
finalConfDateVsFinalPlan.data.Average = Math.Round(totalDays / finalConfDateVsFinalPlan.data.Total, 2);
}
catch (DivideByZeroException)
{
finalConfDateVsFinalPlan.data.Average = 0;
}
try
{
finalConfDateVsFinalPlan.data.PercentUnconf = (int)((totalUnconfPOs / finalConfDateVsFinalPlan.data.Total) * 100);
}
catch (DivideByZeroException)
{
finalConfDateVsFinalPlan.data.PercentUnconf = 0;
}
totalDays = 0;
totalUnconfPOs = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Receipt Date vs Current Planned Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
receiptDateVsCurrPlanDate.data.Total = Overall.posRecCompDt.Rows.Count;
foreach (DataRow dr in Overall.posRecCompDt.Rows)
{
string[] strLastPORecDate = (dr["Last PO Rec#Date"].ToString()).Split('/');
int lastPORecDtYear = int.Parse(strLastPORecDate[2]);
int lastPORecDtMonth = int.Parse(strLastPORecDate[0]);
int lastPORecDtDay = int.Parse(strLastPORecDate[1]);
DateTime lastPORecDate = new DateTime(lastPORecDtYear, lastPORecDtMonth, lastPORecDtDay);
string[] strCurrPlanDate = (dr["Rescheduling date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrPlanDate[2]);
int currConfMonth = int.Parse(strCurrPlanDate[0]);
int currConfDay = int.Parse(strCurrPlanDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
string[] strNewCurrConfDate = (dr["Delivery Date"].ToString()).Split('/');
currConfYear = int.Parse(strNewCurrConfDate[2]);
currConfMonth = int.Parse(strNewCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strNewCurrConfDate[1].TrimStart('0'));
}
else
{
currConfYear = int.Parse(strCurrPlanDate[2]);
currConfMonth = int.Parse(strCurrPlanDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrPlanDate[1].TrimStart('0'));
}
DateTime currPlanDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (lastPORecDate - currPlanDate).TotalDays;
totalDays += elapsedDays;
if (elapsedDays <= (-22))
{
receiptDateVsCurrPlanDate.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
receiptDateVsCurrPlanDate.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
receiptDateVsCurrPlanDate.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
receiptDateVsCurrPlanDate.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
receiptDateVsCurrPlanDate.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
receiptDateVsCurrPlanDate.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
receiptDateVsCurrPlanDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
receiptDateVsCurrPlanDate.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
receiptDateVsCurrPlanDate.data.TwentyTwo++;
}
}
try
{
receiptDateVsCurrPlanDate.data.Average = Math.Round(totalDays / receiptDateVsCurrPlanDate.data.Total, 2);
}
catch (DivideByZeroException)
{
receiptDateVsCurrConfDate.data.Average = 0;
}
totalDays = 0;
totalUnconfPOs = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Receipt Date vs Original Confirmed Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
receiptDateVsOrigConfDate.data.Total = Overall.posRecCompDt.Rows.Count;
foreach (DataRow dr in Overall.posRecCompDt.Rows)
{
string[] strLastPORecDate = (dr["Last PO Rec#Date"].ToString()).Split('/');
int lastPORecDtYear = int.Parse(strLastPORecDate[2]);
int lastPORecDtMonth = int.Parse(strLastPORecDate[0]);
int lastPORecDtDay = int.Parse(strLastPORecDate[1]);
DateTime lastPORecDate = new DateTime(lastPORecDtYear, lastPORecDtMonth, lastPORecDtDay);
string[] strFirstConfDate = (dr["1st Conf Date"].ToString()).Split('/');
int firstConfYear = int.Parse(strFirstConfDate[2]);
int firstConfMonth = int.Parse(strFirstConfDate[0]);
int firstConfDay = int.Parse(strFirstConfDate[1]);
if (firstConfYear == 0 && firstConfMonth == 0 && firstConfDay == 0)
{
totalUnconfPOs++;
continue;
}
else
{
firstConfYear = int.Parse(strFirstConfDate[2]);
firstConfMonth = int.Parse(strFirstConfDate[0].TrimStart('0'));
firstConfDay = int.Parse(strFirstConfDate[1].TrimStart('0'));
}
DateTime firstConfDate = new DateTime(firstConfYear, firstConfMonth, firstConfDay);
double elapsedDays = (lastPORecDate - firstConfDate).TotalDays;
totalDays += elapsedDays;
if (elapsedDays <= (-22))
{
receiptDateVsOrigConfDate.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
receiptDateVsOrigConfDate.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
receiptDateVsOrigConfDate.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
receiptDateVsOrigConfDate.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
receiptDateVsOrigConfDate.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
receiptDateVsOrigConfDate.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
receiptDateVsOrigConfDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
receiptDateVsOrigConfDate.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
receiptDateVsOrigConfDate.data.TwentyTwo++;
}
}
try
{
receiptDateVsOrigConfDate.data.Average = Math.Round(totalDays / receiptDateVsOrigConfDate.data.Total, 2);
}
catch (DivideByZeroException)
{
receiptDateVsOrigConfDate.data.Average = 0;
}
try
{
receiptDateVsOrigConfDate.data.PercentUnconf = (int)((totalUnconfPOs / receiptDateVsOrigConfDate.data.Total) * 100);
}
catch (DivideByZeroException)
{
receiptDateVsOrigConfDate.data.PercentUnconf = 0;
}
totalDays = 0;
totalUnconfPOs = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Receipt Date vs Current Confirmed Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
receiptDateVsCurrConfDate.data.Total = Overall.posRecCompDt.Rows.Count;
foreach (DataRow dr in Overall.posRecCompDt.Rows)
{
string[] strLastPORecDate = (dr["Last PO Rec#Date"].ToString()).Split('/');
int lastPORecDtYear = int.Parse(strLastPORecDate[2]);
int lastPORecDtMonth = int.Parse(strLastPORecDate[0]);
int lastPORecDtDay = int.Parse(strLastPORecDate[1]);
DateTime lastPORecDate = new DateTime(lastPORecDtYear, lastPORecDtMonth, lastPORecDtDay);
string[] strCurrConfDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrConfDate[2]);
int currConfMonth = int.Parse(strCurrConfDate[0]);
int currConfDay = int.Parse(strCurrConfDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
totalUnconfPOs++;
continue;
}
else
{
currConfYear = int.Parse(strCurrConfDate[2]);
currConfMonth = int.Parse(strCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrConfDate[1].TrimStart('0'));
}
DateTime currConfDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (lastPORecDate - currConfDate).TotalDays;
totalDays += elapsedDays;
if (elapsedDays <= (-22))
{
receiptDateVsCurrConfDate.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
receiptDateVsCurrConfDate.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
receiptDateVsCurrConfDate.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
receiptDateVsCurrConfDate.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
receiptDateVsCurrConfDate.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
receiptDateVsCurrConfDate.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
receiptDateVsCurrConfDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
receiptDateVsCurrConfDate.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
receiptDateVsCurrConfDate.data.TwentyTwo++;
}
}
try
{
receiptDateVsCurrConfDate.data.Average = Math.Round(totalDays / receiptDateVsCurrConfDate.data.Total, 2);
}
catch (DivideByZeroException)
{
receiptDateVsCurrConfDate.data.Average = 0;
}
try
{
receiptDateVsCurrConfDate.data.PercentUnconf = (int)((totalUnconfPOs / receiptDateVsCurrConfDate.data.Total) * 100);
}
catch (DivideByZeroException)
{
receiptDateVsCurrConfDate.data.PercentUnconf = 0;
}
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI -> Follow Up Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data of a specific KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR Plan Date vs Current Plan Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
prPlanDateVsCurrPlan.data.Total = Overall.prsOnPOsDt.Rows.Count;
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPrPlanDate = (dr["PR Delivery Date"].ToString()).Split('/');
int delConfYear = int.Parse(strPrPlanDate[2]);
int delConfMonth = int.Parse(strPrPlanDate[0].TrimStart('0'));
int delConfDay = int.Parse(strPrPlanDate[1].TrimStart('0'));
DateTime prPlanDate = new DateTime(delConfYear, delConfMonth, delConfDay);
string[] strCurrPlanDate = (dr["Rescheduling date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrPlanDate[2]);
int currConfMonth = int.Parse(strCurrPlanDate[0]);
int currConfDay = int.Parse(strCurrPlanDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
string[] strNewCurrConfDate = (dr["Delivery Date"].ToString()).Split('/');
currConfYear = int.Parse(strNewCurrConfDate[2]);
currConfMonth = int.Parse(strNewCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strNewCurrConfDate[1].TrimStart('0'));
}
else
{
currConfYear = int.Parse(strCurrPlanDate[2]);
currConfMonth = int.Parse(strCurrPlanDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrPlanDate[1].TrimStart('0'));
}
DateTime reqDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (reqDate - prPlanDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= (-22))
{
prPlanDateVsCurrPlan.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
prPlanDateVsCurrPlan.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
prPlanDateVsCurrPlan.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
prPlanDateVsCurrPlan.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
prPlanDateVsCurrPlan.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
prPlanDateVsCurrPlan.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
prPlanDateVsCurrPlan.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
prPlanDateVsCurrPlan.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
prPlanDateVsCurrPlan.data.TwentyTwo++;
}
}
try
{
prPlanDateVsCurrPlan.data.Average = Math.Round(totalDays / prPlanDateVsCurrPlan.data.Total, 2);
}
catch (DivideByZeroException)
{
prPlanDateVsCurrPlan.data.Average = 0;
}
totalDays = 0;
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI -> Plan Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data of a specific KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR Created
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.AllDt.Rows)
{
string[] strReqDate = (dr["Requisn Date"].ToString()).Split('/');
int reqDateYear = int.Parse(strReqDate[2]);
int reqDateMonth = int.Parse(strReqDate[0].TrimStart('0'));
int reqDateDay = int.Parse(strReqDate[1].TrimStart('0'));
DateTime requiDate = new DateTime(reqDateYear, reqDateMonth, reqDateDay);
totalValue += decimal.Parse(dr["PR Pos#Value"].ToString());
DateTime today = DateTime.Now.Date;
double elapsedDays = (requiDate - today).TotalDays;
double weeks = Math.Floor(elapsedDays / 7);
if (weeks == 0)
{
prsCreated.data.Zero++;
}
else if (weeks < 0 && weeks >= (-1))
{
prsCreated.data.LessOneWeek++;
}
else if (weeks < (-1) && weeks >= (-2))
{
prsCreated.data.LessTwoWeeks++;
}
else if (weeks < (-2) && weeks >= (-3))
{
prsCreated.data.LessThreeWeeks++;
}
else if (weeks < (-3) && weeks >= (-4))
{
prsCreated.data.LessFourWeeks++;
}
else if (weeks < (-4) && weeks >= (-5))
{
prsCreated.data.LessFiveWeeks++;
}
else if (weeks < (-5) && weeks >= (-6))
{
prsCreated.data.LessSixWeeks++;
}
else if (weeks < (-6) && weeks >= (-7))
{
prsCreated.data.LessSevenWeeks++;
}
else if (weeks < (-7) && weeks >= (-8))
{
prsCreated.data.LessEightWeeks++;
}
else if (weeks <= (-9))
{
prsCreated.data.LessNinePlusWeeks++;
}
}
prsCreated.data.TotalValue = totalValue;
prsCreated.data.Total = Overall.AllDt.Rows.Count;
totalValue = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PRs Released
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.pr2ndLvlRelDateDt.Rows)
{
string[] strPr2ndLvlRelDt = (dr["PR 2° Rel# Date"].ToString()).Split('/');
int pr2ndLvlRelDtYear = int.Parse(strPr2ndLvlRelDt[2]);
int pr2ndLvlRelDtMonth = int.Parse(strPr2ndLvlRelDt[0].TrimStart('0'));
int pr2ndLvlRelDtDay = int.Parse(strPr2ndLvlRelDt[1].TrimStart('0'));
DateTime pr2ndLvlRelDt = new DateTime(pr2ndLvlRelDtYear, pr2ndLvlRelDtMonth, pr2ndLvlRelDtDay);
totalValue += decimal.Parse(dr["PR Pos#Value"].ToString());
DateTime today = DateTime.Now.Date;
double elapsedDays = (pr2ndLvlRelDt - today).TotalDays;
double weeks = Math.Floor(elapsedDays / 7);
if (weeks == 0)
{
prsReleased.data.Zero++;
}
else if (weeks < 0 && weeks >= (-1))
{
prsReleased.data.LessOneWeek++;
}
else if (weeks < (-1) && weeks >= (-2))
{
prsReleased.data.LessTwoWeeks++;
}
else if (weeks < (-2) && weeks >= (-3))
{
prsReleased.data.LessThreeWeeks++;
}
else if (weeks < (-3) && weeks >= (-4))
{
prsReleased.data.LessFourWeeks++;
}
else if (weeks < (-4) && weeks >= (-5))
{
prsReleased.data.LessFiveWeeks++;
}
else if (weeks < (-5) && weeks >= (-6))
{
prsReleased.data.LessSixWeeks++;
}
else if (weeks < (-6) && weeks >= (-7))
{
prsReleased.data.LessSevenWeeks++;
}
else if (weeks < (-7) && weeks >= (-8))
{
prsReleased.data.LessEightWeeks++;
}
else if (weeks <= (-9))
{
prsReleased.data.LessNinePlusWeeks++;
}
}
prsReleased.data.TotalValue = totalValue;
prsReleased.data.Total = Overall.pr2ndLvlRelDateDt.Rows.Count;
totalValue = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Total Spend
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPoCreateDt = (dr["PO Line Creat#DT"].ToString()).Split('/');
int poCreateDtYear = int.Parse(strPoCreateDt[2]);
int poCreateDtMonth = int.Parse(strPoCreateDt[0].TrimStart('0'));
int poCreateDtDay = int.Parse(strPoCreateDt[1].TrimStart('0'));
DateTime poCreateDate = new DateTime(poCreateDtYear, poCreateDtMonth, poCreateDtDay);
totalValue += decimal.Parse(dr["PO Value"].ToString());
DateTime today = DateTime.Now.Date;
double elapsedDays = (poCreateDate - today).TotalDays;
double weeks = Math.Floor(elapsedDays / 7);
if (weeks == 0)
{
totalSpend.data.Zero++;
}
else if (weeks < 0 && weeks >= (-1))
{
totalSpend.data.LessOneWeek++;
}
else if (weeks < (-1) && weeks >= (-2))
{
totalSpend.data.LessTwoWeeks++;
}
else if (weeks < (-2) && weeks >= (-3))
{
totalSpend.data.LessThreeWeeks++;
}
else if (weeks < (-3) && weeks >= (-4))
{
totalSpend.data.LessFourWeeks++;
}
else if (weeks < (-4) && weeks >= (-5))
{
totalSpend.data.LessFiveWeeks++;
}
else if (weeks < (-5) && weeks >= (-6))
{
totalSpend.data.LessSixWeeks++;
}
else if (weeks < (-6) && weeks >= (-7))
{
totalSpend.data.LessSevenWeeks++;
}
else if (weeks < (-7) && weeks >= (-8))
{
totalSpend.data.LessEightWeeks++;
}
else if (weeks <= (-9))
{
totalSpend.data.LessNinePlusWeeks++;
}
}
totalSpend.data.TotalValue = totalValue;
totalSpend.data.Total = Overall.prsOnPOsDt.Rows.Count;
totalValue = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR vs PO Value
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strPoCreateDt = (dr["PO Line Creat#DT"].ToString()).Split('/');
int poCreateDtYear = int.Parse(strPoCreateDt[2]);
int poCreateDtMonth = int.Parse(strPoCreateDt[0].TrimStart('0'));
int poCreateDtDay = int.Parse(strPoCreateDt[1].TrimStart('0'));
DateTime poCreateDate = new DateTime(poCreateDtYear, poCreateDtMonth, poCreateDtDay);
totalValue += decimal.Parse(dr["PO Value"].ToString()) - decimal.Parse(dr["PR Pos#Value"].ToString());
DateTime today = DateTime.Now.Date;
double elapsedDays = (poCreateDate - today).TotalDays;
double weeks = Math.Floor(elapsedDays / 7);
if (weeks == 0)
{
prVsPOValue.data.Zero++;
}
else if (weeks < 0 && weeks >= (-1))
{
prVsPOValue.data.LessOneWeek++;
}
else if (weeks < (-1) && weeks >= (-2))
{
prVsPOValue.data.LessTwoWeeks++;
}
else if (weeks < (-2) && weeks >= (-3))
{
prVsPOValue.data.LessThreeWeeks++;
}
else if (weeks < (-3) && weeks >= (-4))
{
prVsPOValue.data.LessFourWeeks++;
}
else if (weeks < (-4) && weeks >= (-5))
{
prVsPOValue.data.LessFiveWeeks++;
}
else if (weeks < (-5) && weeks >= (-6))
{
prVsPOValue.data.LessSixWeeks++;
}
else if (weeks < (-6) && weeks >= (-7))
{
prVsPOValue.data.LessSevenWeeks++;
}
else if (weeks < (-7) && weeks >= (-8))
{
prVsPOValue.data.LessEightWeeks++;
}
else if (weeks <= (-9))
{
prVsPOValue.data.LessNinePlusWeeks++;
}
}
totalValue = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Hot Job PRs
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.AllDt.Rows)
{
if (dr["Purch# Group"].ToString() != "UHJ")
{
continue;
}
string[] strPrReqDt = (dr["Requisn Date"].ToString()).Split('/');
int reqDateYear = int.Parse(strPrReqDt[2]);
int reqDateMonth = int.Parse(strPrReqDt[0].TrimStart('0'));
int reqDateDay = int.Parse(strPrReqDt[1].TrimStart('0'));
DateTime prReqDate = new DateTime(reqDateYear, reqDateMonth, reqDateDay);
totalValue += decimal.Parse(dr["PR Pos#Value"].ToString());
DateTime today = DateTime.Now.Date;
double elapsedDays = (prReqDate - today).TotalDays;
double weeks = Math.Floor(elapsedDays / 7);
if (weeks == 0)
{
hotJobPrs.data.Zero++;
}
else if (weeks < 0 && weeks >= (-1))
{
hotJobPrs.data.LessOneWeek++;
}
else if (weeks < (-1) && weeks >= (-2))
{
hotJobPrs.data.LessTwoWeeks++;
}
else if (weeks < (-2) && weeks >= (-3))
{
hotJobPrs.data.LessThreeWeeks++;
}
else if (weeks < (-3) && weeks >= (-4))
{
hotJobPrs.data.LessFourWeeks++;
}
else if (weeks < (-4) && weeks >= (-5))
{
hotJobPrs.data.LessFiveWeeks++;
}
else if (weeks < (-5) && weeks >= (-6))
{
hotJobPrs.data.LessSixWeeks++;
}
else if (weeks < (-6) && weeks >= (-7))
{
hotJobPrs.data.LessSevenWeeks++;
}
else if (weeks < (-7) && weeks >= (-8))
{
hotJobPrs.data.LessEightWeeks++;
}
else if (weeks <= (-9))
{
hotJobPrs.data.LessNinePlusWeeks++;
}
}
totalValue = 0;
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI -> Other Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data of the specific KPA.
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Current Planned Date vs Current Confirmation Date (Open POs)
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DataTable dt = new DataTable();
OleDbCommand cmd;
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_CurrPlanActual_CurrPlanDateCurrConfDateOpenPO] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_CurrPlanActual_CurrPlanDateCurrConfDateOpenPO] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
currPlanDateCurrConfDate.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int year = int.Parse(strDate[2]);
int month = int.Parse(strDate[0].TrimStart('0'));
int day = int.Parse(strDate[1].TrimStart('0'));
DateTime confDate = new DateTime(year, month, day);
string[] strCurrPlanDate = (dr["Rescheduling date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrPlanDate[2]);
int currConfMonth = int.Parse(strCurrPlanDate[0]);
int currConfDay = int.Parse(strCurrPlanDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
string[] strNewCurrConfDate = (dr["Delivery Date"].ToString()).Split('/');
currConfYear = int.Parse(strNewCurrConfDate[2]);
currConfMonth = int.Parse(strNewCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strNewCurrConfDate[1].TrimStart('0'));
}
else
{
currConfYear = int.Parse(strCurrPlanDate[2]);
currConfMonth = int.Parse(strCurrPlanDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrPlanDate[1].TrimStart('0'));
}
DateTime currPlanDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (confDate - currPlanDate).TotalDays; // keep this a double so we can calculate an accurate average
totalWeeks += elapsedDays / 7;
elapsedDays = (int)elapsedDays; // we can now convert to a whole number
int weeks = 0;
if (elapsedDays < 0)
{
weeks = (int)Math.Floor(elapsedDays / 7);
}
else
{
weeks = (int)Math.Ceiling(elapsedDays / 7);
}
if (weeks <= (-4))
{
currPlanDateCurrConfDate.data.LessThanMinusFourWeeks++;
}
else if (weeks > (-4) && weeks <= (-3))
{
currPlanDateCurrConfDate.data.LessThanMinusThreeWeeks++;
}
else if (weeks > (-3) && weeks <= (-2))
{
currPlanDateCurrConfDate.data.LessThanMinusTwoWeeks++;
}
else if (weeks > (-2) && weeks <= (-1))
{
currPlanDateCurrConfDate.data.LessThanMinusOneWeeks++;
}
else if (weeks == 0)
{
currPlanDateCurrConfDate.data.ZeroWeeks++;
}
else if (weeks > 0 && weeks <= 1)
{
currPlanDateCurrConfDate.data.OneWeek++;
}
else if (weeks > 1 && weeks <= 2)
{
currPlanDateCurrConfDate.data.TwoWeeks++;
}
else if (weeks > 2 && weeks <= 3)
{
currPlanDateCurrConfDate.data.ThreeWeeks++;
}
else // 4 Weeks+
{
currPlanDateCurrConfDate.data.FourWeeksPlus++;
}
}
try
{
currPlanDateCurrConfDate.data.Average = Math.Round(totalWeeks / currPlanDateCurrConfDate.data.Total, 2);
}
catch (DivideByZeroException)
{
currPlanDateCurrConfDate.data.Average = 0;
}
totalWeeks = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Current Planned Date vs Current Confirmation Date (Open POs)
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
dt = new DataTable();
if (Overall.SelectedCountry == AccessInfo.MainTables.US_PRPO)
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_CurrPlanActual_CurrPlanDateCurrConfDateOpenPOHotJobs] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
else
{
cmd = new OleDbCommand(PRPOCommands.Queries[(int)PRPOCommands.DatabaseTables.TableNames.KPA_CurrPlanActual_CurrPlanDateCurrConfDateOpenPOHotJobs] + Filters.FilterQuery, PRPO_DB_Utils.DatabaseConnection);
}
da = new OleDbDataAdapter(cmd);
da.Fill(dt);
currPlanDateCurrConfDateHotJobs.data.Total = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
string[] strDate = (dr["Del#Conf#Date"].ToString()).Split('/');
int year = int.Parse(strDate[2]);
int month = int.Parse(strDate[0].TrimStart('0'));
int day = int.Parse(strDate[1].TrimStart('0'));
DateTime confDate = new DateTime(year, month, day);
string[] strCurrPlanDate = (dr["Rescheduling date"].ToString()).Split('/');
int currConfYear = int.Parse(strCurrPlanDate[2]);
int currConfMonth = int.Parse(strCurrPlanDate[0]);
int currConfDay = int.Parse(strCurrPlanDate[1]);
if (currConfYear == 0 && currConfMonth == 0 && currConfDay == 0)
{
string[] strNewCurrConfDate = (dr["Delivery Date"].ToString()).Split('/');
currConfYear = int.Parse(strNewCurrConfDate[2]);
currConfMonth = int.Parse(strNewCurrConfDate[0].TrimStart('0'));
currConfDay = int.Parse(strNewCurrConfDate[1].TrimStart('0'));
}
else
{
currConfYear = int.Parse(strCurrPlanDate[2]);
currConfMonth = int.Parse(strCurrPlanDate[0].TrimStart('0'));
currConfDay = int.Parse(strCurrPlanDate[1].TrimStart('0'));
}
DateTime currPlanDate = new DateTime(currConfYear, currConfMonth, currConfDay);
double elapsedDays = (confDate - currPlanDate).TotalDays; // keep this a double so we can calculate an acccurate average
totalWeeks += elapsedDays / 7;
elapsedDays = (int)elapsedDays; // we can now convert this to a whole number.
int weeks = 0;
if (elapsedDays < 0)
{
weeks = (int)Math.Floor(elapsedDays / 7);
}
else
{
weeks = (int)Math.Ceiling(elapsedDays / 7);
}
if (weeks <= (-4))
{
currPlanDateCurrConfDateHotJobs.data.LessThanMinusFourWeeks++;
}
else if (weeks > (-4) && weeks <= (-3))
{
currPlanDateCurrConfDateHotJobs.data.LessThanMinusThreeWeeks++;
}
else if (weeks > (-3) && weeks <= (-2))
{
currPlanDateCurrConfDateHotJobs.data.LessThanMinusTwoWeeks++;
}
else if (weeks > (-2) && weeks <= (-1))
{
currPlanDateCurrConfDateHotJobs.data.LessThanMinusOneWeeks++;
}
else if (weeks == 0)
{
currPlanDateCurrConfDateHotJobs.data.ZeroWeeks++;
}
else if (weeks > 0 && weeks <= 1)
{
currPlanDateCurrConfDateHotJobs.data.OneWeek++;
}
else if (weeks > 1 && weeks <= 2)
{
currPlanDateCurrConfDateHotJobs.data.TwoWeeks++;
}
else if (weeks > 2 && weeks <= 3)
{
currPlanDateCurrConfDateHotJobs.data.ThreeWeeks++;
}
else // 4 Weeks+
{
currPlanDateCurrConfDateHotJobs.data.FourWeeksPlus++;
}
}
try
{
currPlanDateCurrConfDateHotJobs.data.Average = Math.Round(totalWeeks / currPlanDateCurrConfDateHotJobs.data.Total);
}
catch (DivideByZeroException)
{
currPlanDateCurrConfDateHotJobs.data.Average = 0;
}
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPA -> Follow Up Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data for the specifc KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PO Release vs PR Delivery Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
strPoLineFirstRelDate = (dr["PO Line 1st Rel Dt"].ToString()).Split('/');
int poLineFirstRelYear = int.Parse(strPoLineFirstRelDate[2]);
int poLineFirstRelMonth = int.Parse(strPoLineFirstRelDate[0]);
int poLineFirstRelDay = int.Parse(strPoLineFirstRelDate[1]);
if (poLineFirstRelYear == 0 && poLineFirstRelMonth == 0 && poLineFirstRelDay == 0)
{
continue;
}
else
{
poRelVsPRDelDate.data.Total++;
poLineFirstRelYear = int.Parse(strPoLineFirstRelDate[2]);
poLineFirstRelMonth = int.Parse(strPoLineFirstRelDate[0].TrimStart('0'));
poLineFirstRelDay = int.Parse(strPoLineFirstRelDate[1].TrimStart('0'));
}
DateTime poLineFirstRelDate = new DateTime(poLineFirstRelYear, poLineFirstRelMonth, poLineFirstRelDay);
string[] strPRDelDate = (dr["PR Delivery Date"].ToString()).Split('/');
int prDelYear = int.Parse(strPRDelDate[2]);
int prDelMonth = int.Parse(strPRDelDate[0].TrimStart('0'));
int prDelDay = int.Parse(strPRDelDate[1].TrimStart('0'));
DateTime prDelDate = new DateTime(prDelYear, prDelMonth, prDelDay);
double elapsedDays = (prDelDate - poLineFirstRelDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
poRelVsPRDelDate.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
poRelVsPRDelDate.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
poRelVsPRDelDate.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
poRelVsPRDelDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
poRelVsPRDelDate.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
poRelVsPRDelDate.data.TwentyTwo_TwentyEight++;
}
else if (elapsedDays >= 29 && elapsedDays <= 35)
{
poRelVsPRDelDate.data.TwentyNine_ThirtyFive++;
}
else if (elapsedDays >= 36 && elapsedDays <= 42)
{
poRelVsPRDelDate.data.ThirtySix_FourtyTwo++;
}
else if (elapsedDays >= 43 && elapsedDays <= 49)
{
poRelVsPRDelDate.data.FourtyThree_FourtyNine++;
}
else if (elapsedDays >= 50)
{
poRelVsPRDelDate.data.greaterThanEqualFifty++;
}
}
try
{
poRelVsPRDelDate.data.Average = Math.Round(totalDays / poRelVsPRDelDate.data.Total, 2);
}
catch (DivideByZeroException)
{
poRelVsPRDelDate.data.Average = 0;
}
totalDays = 0;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// PR 2nd Lvl Release to Original Planned Delivery Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.pr2ndLvlRelDateDt.Rows)
{
string[] strPR2ndLvlRelDate = (dr["PR 2° Rel# Date"].ToString()).Split('/');
int pr2ndLvlRelYear = int.Parse(strPR2ndLvlRelDate[2]);
int pr2ndLvlRelMonth = int.Parse(strPR2ndLvlRelDate[0]);
int pr2ndLvlRelDay = int.Parse(strPR2ndLvlRelDate[1]);
if (pr2ndLvlRelYear == 0 && pr2ndLvlRelMonth == 0 && pr2ndLvlRelDay == 0)
{
continue;
}
else
{
pr2ndLvlRelOrigPlanDelDate.data.Total++;
pr2ndLvlRelYear = int.Parse(strPR2ndLvlRelDate[2]);
pr2ndLvlRelMonth = int.Parse(strPR2ndLvlRelDate[0].TrimStart('0'));
pr2ndLvlRelDay = int.Parse(strPR2ndLvlRelDate[1].TrimStart('0'));
}
DateTime pr2ndLvlRelDate = new DateTime(pr2ndLvlRelYear, pr2ndLvlRelMonth, pr2ndLvlRelDay);
string[] strPRDelDate = (dr["PR Delivery Date"].ToString()).Split('/');
int prDelYear = int.Parse(strPRDelDate[2]);
int prDelMonth = int.Parse(strPRDelDate[0].TrimStart('0'));
int prDelDay = int.Parse(strPRDelDate[1].TrimStart('0'));
DateTime prDelDate = new DateTime(prDelYear, prDelMonth, prDelDay);
double elapsedDays = (prDelDate - pr2ndLvlRelDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= 0)
{
pr2ndLvlRelOrigPlanDelDate.data.LessThanZero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 3)
{
pr2ndLvlRelOrigPlanDelDate.data.One_Three++;
}
else if (elapsedDays >= 4 && elapsedDays <= 7)
{
pr2ndLvlRelOrigPlanDelDate.data.Four_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
pr2ndLvlRelOrigPlanDelDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
pr2ndLvlRelOrigPlanDelDate.data.Fifteen_TwentyOne++;
}
else if (elapsedDays >= 22 && elapsedDays <= 28)
{
pr2ndLvlRelOrigPlanDelDate.data.TwentyTwo_TwentyEight++;
}
else if (elapsedDays >= 29 && elapsedDays <= 35)
{
pr2ndLvlRelOrigPlanDelDate.data.TwentyNine_ThirtyFive++;
}
else if (elapsedDays >= 36 && elapsedDays <= 42)
{
pr2ndLvlRelOrigPlanDelDate.data.ThirtySix_FourtyTwo++;
}
else if (elapsedDays >= 43 && elapsedDays <= 49)
{
pr2ndLvlRelOrigPlanDelDate.data.FourtyThree_FourtyNine++;
}
else if (elapsedDays >= 50)
{
pr2ndLvlRelOrigPlanDelDate.data.greaterThanEqualFifty++;
}
}
try
{
pr2ndLvlRelOrigPlanDelDate.data.Average = Math.Round(totalDays / pr2ndLvlRelOrigPlanDelDate.data.Total, 2);
}
catch (DivideByZeroException)
{
pr2ndLvlRelOrigPlanDelDate.data.Average = 0;
}
totalDays = 0;
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI -> Purch Plan Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads the data for the specific KPI
/// </summary>
/// <param name="Overall.SelectedCountry"></param>
public void LoadData()
{
try
{
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Initial Confirmation vs PR Planned Date
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow dr in Overall.prsOnPOsDt.Rows)
{
string[] strFirstConfDate = (dr["1st Conf Date"].ToString()).Split('/');
int firstConfYear = int.Parse(strFirstConfDate[2]);
int firstConfMonth = int.Parse(strFirstConfDate[0]);
int firstConfDay = int.Parse(strFirstConfDate[1]);
if (firstConfYear == 0 && firstConfMonth == 0 && firstConfDay == 0)
{
totalPOsUnconf++;
continue;
}
else
{
initConfVsPRPlanDate.data.Total++;
firstConfYear = int.Parse(strFirstConfDate[2]);
firstConfMonth = int.Parse(strFirstConfDate[0].TrimStart('0'));
firstConfDay = int.Parse(strFirstConfDate[1].TrimStart('0'));
}
DateTime firstConfDate = new DateTime(firstConfYear, firstConfMonth, firstConfDay);
string[] strPRPlanDate = (dr["PR Delivery Date"].ToString()).Split('/');
int prDelYear = int.Parse(strPRPlanDate[2]);
int prDelMonth = int.Parse(strPRPlanDate[0].TrimStart('0'));
int prDelDay = int.Parse(strPRPlanDate[1].TrimStart('0'));
DateTime prPlanDate = new DateTime(prDelYear, prDelMonth, prDelDay);
double elapsedDays = (firstConfDate - prPlanDate).TotalDays;
totalDays += elapsedDays;
elapsedDays = (int)elapsedDays;
if (elapsedDays <= (-22))
{
initConfVsPRPlanDate.data.Minus_TwentyTwo++;
}
else if (elapsedDays > (-22) && elapsedDays <= (-15))
{
initConfVsPRPlanDate.data.Minus_Fifteen_TwentyOne++;
}
else if (elapsedDays > (-14) && elapsedDays <= (-8))
{
initConfVsPRPlanDate.data.Minus_Eight_Fourteen++;
}
else if (elapsedDays > (-7) && elapsedDays <= (-1))
{
initConfVsPRPlanDate.data.Minus_One_Seven++;
}
else if (elapsedDays == 0)
{
initConfVsPRPlanDate.data.Zero++;
}
else if (elapsedDays >= 1 && elapsedDays <= 7)
{
initConfVsPRPlanDate.data.One_Seven++;
}
else if (elapsedDays >= 8 && elapsedDays <= 14)
{
initConfVsPRPlanDate.data.Eight_Fourteen++;
}
else if (elapsedDays >= 15 && elapsedDays <= 21)
{
initConfVsPRPlanDate.data.Fifteen_TwentyOne++;
}
else // 22 Days or greater
{
initConfVsPRPlanDate.data.TwentyTwo++;
}
}
try
{
initConfVsPRPlanDate.data.Average = Math.Round(totalDays / initConfVsPRPlanDate.data.Total, 2);
}
catch (DivideByZeroException)
{
initConfVsPRPlanDate.data.Average = 0;
}
try
{
initConfVsPRPlanDate.data.PercentUnconf = (int)((totalPOsUnconf / initConfVsPRPlanDate.data.Total) * 100);
}
catch (DivideByZeroException)
{
initConfVsPRPlanDate.data.PercentUnconf = 0;
}
initConfVsPRPlanDate.data.Total = Overall.prsOnPOsDt.Rows.Count - (int)totalPOsUnconf;
totalDays = 0;
PRPO_DB_Utils.CompletedDataLoads++;
MethodInvoker del = delegate
{
PRPO_DB_Utils.UpdateDataLoadProgress();
};
del.Invoke();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "KPI -> Purch Calculation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}