//Labor Productivity Charts
private void loadChartP01(int indice, string clase, string sFilter)
{
try
{
//Week range, from current week - 13 to current week
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
semana = semana - 1;
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
string dtFrom = st.AddDays(-91).ToShortDateString();
string dtTo = st.ToShortDateString();
WebChartControl1.Series[0].Points.Clear();
WebChartControl1.Series[1].Points.Clear();
WebChartControl1.Series[2].Points.Clear();
WebChartControl1.Series[3].Points.Clear();
WebChartControl1.SeriesSorting = SortingMode.None;
WebChartControl1.SeriesTemplate.SeriesPointsSorting = SortingMode.None;
string qry = "", query = "", qryBaseline = "";
string colName = "";
string xClass = "", prefix = "";
string sTblName = "", cTblName = "", aTblName = "", vTblName = "";
//Selects filter according to user's selection, dafult filter is by week
if (sFilter == "Week")
{
sTblName = " FROM [vw_labor_productivity_by_site_wkly] WHERE [NP_LstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "'";
cTblName = " FROM [vw_labor_productivity_by_cell_wkly] WHERE [NP_LstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
aTblName = " FROM [vw_labor_productivity_by_area_wkly] WHERE [NP_LstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
vTblName = " FROM [vw_labor_productivity_by_vsm_wkly] WHERE [NP_LstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
colName = "NP_Week";
prefix = "Wk";
}
else
{
sTblName = ", [NP_LstWkDay] = (SELECT TOP 1 [NP_LstWkDay] FROM tblLaborProductivity WHERE [NP_Month] = [vw_labor_productivity_by_site_mntly].[NP_Month] AND " +
"[NP_Year] = [vw_labor_productivity_by_site_mntly].[NP_Year] Order by [NP_LstWkDay] desc) " +
"FROM [vw_labor_productivity_by_site_mntly]";
cTblName = ", [NP_LstWkDay] = (SELECT TOP 1 [NP_LstWkDay] FROM tblLaborProductivity WHERE [NP_Month] = [vw_labor_productivity_by_cell_mntly].[NP_Month] AND[NP_Year] = [vw_labor_productivity_by_cell_mntly].[NP_Year] " +
" Order by[NP_LstWkDay] desc) FROM [vw_labor_productivity_by_cell_mntly] WHERE ";
aTblName = ", [NP_LstWkDay] = (SELECT TOP 1 [NP_LstWkDay] FROM tblLaborProductivity WHERE [NP_Month] = [vw_labor_productivity_by_area_mntly].[NP_Month] AND[NP_Year] = [vw_labor_productivity_by_area_mntly].[NP_Year]" +
" Order by[NP_LstWkDay] desc) FROM [vw_labor_productivity_by_area_mntly] WHERE ";
vTblName = ", [NP_LstWkDay] = (SELECT TOP 1 [NP_LstWkDay] FROM tblLaborProductivity WHERE [NP_Month] = [vw_labor_productivity_by_vsm_mntly].[NP_Month] AND[NP_Year] = [vw_labor_productivity_by_vsm_mntly].[NP_Year]" +
" Order by[NP_LstWkDay] desc) FROM [vw_labor_productivity_by_vsm_mntly] WHERE ";
colName = "NP_Month";
prefix = "";
}
//Validates filter level by site/area/cell, default filter is by site
switch (indice)
{
case 1:
xClass = clase;
query = "SELECT TOP 12 * " + vTblName + " [NP_Group] = '" + xClass +
"' ORDER BY [NP_LstWkDay] desc, [NP_Group]";
qry = "select * from (" + query + ") q1 order by [NP_LstWkDay] asc";
qryBaseline = "SELECT * FROM [tblBaseProductivity] WHERE [TBP_Name] LIKE '" + xClass + "'";
break;
case 2:
xClass = clase;
query = "SELECT TOP 12 * " + aTblName + " [NP_Area] = '" + xClass +
"' ORDER BY [NP_LstWkDay] desc, [NP_Area]";
qry = "select * from (" + query + ") q1 order by [NP_LstWkDay] asc";
qryBaseline = "SELECT * FROM [tblBaseProductivity] WHERE [TBP_Name] LIKE '" + xClass + "'";
break;
case 3:
xClass = clase;
query = "SELECT TOP 12 * " + cTblName + " [NP_Celda] = '" + xClass +
"' ORDER BY [NP_LstWkDay] desc, [NP_Celda]";
qry = "select * from (" + query + ") q1 order by [NP_LstWkDay] asc";
qryBaseline = "SELECT * FROM [tblBaseProductivity] WHERE [TBP_Name] LIKE '" + xClass + "'";
break;
default:
query = "SELECT TOP 12 * " + sTblName +
" ORDER BY [NP_LstWkDay] desc";
qry = "select * from (" + query + ") q1 order by [NP_LstWkDay] asc";
qryBaseline = "SELECT * FROM [tblBaseProductivity] WHERE [TBP_Name] LIKE 'Site'";
break;
}
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dtPareto = dBHelper.QryManager(qry);
DataTable dtBaseline = dBHelper.GetBaseLine(qryBaseline);
double xGoal = 0;
if (dtBaseline.Rows.Count > 0)
{
Double.TryParse(dtBaseline.Rows[0]["TBP_Base"].ToString(), out xGoal);
}
xGoal = xGoal / 100;
foreach (DataRow dr1 in dtPareto.Rows)
{
double eHrs = Convert.ToDouble(dr1["NP_EarnedHrs"].ToString());
double tHrs = Convert.ToDouble(dr1["NP_TotalHrs"].ToString());
double prod = tHrs == 0 ? 0 : (eHrs / tHrs);
WebChartControl1.Series[0].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), tHrs));
WebChartControl1.Series[1].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), eHrs));
WebChartControl1.Series[2].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), prod));
WebChartControl1.Series[3].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), xGoal));
WebChartControl1.Series[0].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
WebChartControl1.Series[1].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
WebChartControl1.Series[2].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
WebChartControl1.Series[3].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
//HttpContext.Current.Items.Add("Exception", ex);
HttpContext.Current.Session.Add("Exception", ex);
if (ex is SqlException)
{
// Handle more specific SqlException exception here.
SqlException odbcExc = (SqlException)ex;
errNum = odbcExc.Number;
errDesc = odbcExc.Message;
}
else
{
// Handle generic ones here.
errDesc = ex.Message;
}
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=productivity.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartP01(int indice, string clase, string sFilter)
{
try
{
//Week range, from current week - 13 to current week
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
semana = semana - 1;
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
string dtFrom = st.AddDays(-91).ToShortDateString();
string dtTo = st.ToShortDateString();
WebChartControl1.Series[0].Points.Clear();
WebChartControl1.Series[1].Points.Clear();
WebChartControl1.SeriesSorting = SortingMode.None;
WebChartControl1.SeriesTemplate.SeriesPointsSorting = SortingMode.None;
string query = "", qry = "", qryBaseline = "";
string colName = "", prefix = "", qFilter = "";
string xClass = "";
string sTblName = "", cTblName = "", aTblName = "", vTblName = "";
//Selects filter according to user's selection, dafult filter is by week
if (sFilter == "Week")
{
sTblName = " FROM [vw_scrap_weekly_by_site]";
aTblName = " FROM [vw_scrap_weekly_by_area] WHERE ";
cTblName = " FROM [vw_scrap_weekly_by_cell] WHERE ";
vTblName = " FROM [vw_scrap_weekly_by_vsm] WHERE ";
colName = "TCS_Week";
prefix = "Wk";
qFilter = "Weekly";
}
else
{
sTblName = " FROM [vw_scrap_monthly_by_site]";
aTblName = " FROM [vw_scrap_monthly_by_area] WHERE ";
cTblName = " FROM [vw_scrap_monthly_by_cell] WHERE ";
vTblName = " FROM [vw_scrap_monthly_by_vsm] WHERE ";
colName = "sMontName";
prefix = "";
qFilter = "Monthly";
}
//Validates filter level by site/area/cell, default filter is by site
switch (indice)
{
case 1:
xClass = clase;
query = "SELECT TOP 12 * " + vTblName + " [TCS_Group] = '" + xClass +
"' ORDER BY [sLstWkDay] desc, [TCS_Group]";
qry = "select * from (" + query + ") q1 order by [sLstWkDay] asc";
qryBaseline = "SELECT * FROM [sta_nivel2] WHERE [sMetric] = 'Scrap' AND [sFilter] = 'VSM' and [sType] = '" + qFilter + "' and [sClass] = '" + xClass + "'";
break;
case 2:
xClass = clase;
query = "SELECT TOP 12 * " + aTblName + " [TCS_Area] = '" + xClass +
"' ORDER BY [sLstWkDay] desc, [TCS_Area]";
qry = "select * from (" + query + ") q1 order by [sLstWkDay] asc";
qryBaseline = "SELECT * FROM [sta_nivel2] WHERE [sMetric] = 'Scrap' AND [sFilter] = 'VSM' and [sType] = '" + qFilter + "' and [sClass] = '" + xClass + "'";
break;
case 3:
xClass = clase;
query = "SELECT TOP 12 * " + cTblName + " [TCS_Cell] = '" + xClass +
"' ORDER BY [sLstWkDay] desc, [TCS_Cell]";
qry = "select * from (" + query + ") q1 order by [sLstWkDay] asc";
qryBaseline = "SELECT * FROM [sta_nivel2] WHERE [sMetric] = 'Scrap' AND [sFilter] = 'Cell' and [sType] = '" + qFilter + "' and [sClass] = '" + xClass + "'";
break;
default:
query = "SELECT TOP 12 * " + sTblName +
" ORDER BY [sLstWkDay] desc";
qry = "select * from (" + query + ") q1 order by [sLstWkDay] asc";
qryBaseline = "SELECT * FROM [sta_nivel2] WHERE [sMetric] = 'Scrap' AND [sFilter] = 'Site' and [sType] = '" + qFilter + "'";
break;
}
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dtPareto = dBHelper.QryManager(qry);
DataTable dtBaseline = dBHelper.QryManager(qryBaseline);
double xGoal = 0;
if (dtBaseline.Rows.Count > 0)
{
Double.TryParse(dtBaseline.Rows[0]["fGoal"].ToString(), out xGoal);
}
var maxVal = 0;
if (dtPareto.Rows.Count > 0)
{
maxVal = Convert.ToInt32(dtPareto.Compute("Max([TCS_Amount])", string.Empty));
}
AxisLabel lbaxisY = ((XYDiagram)WebChartControl1.Diagram).AxisY.Label;
string lbFmt = "";
int div = 0;
if (maxVal > 999999)
{
lbFmt = "{V:c2}" + " M";
WebChartControl1.Series[0].CrosshairLabelPattern = lbFmt;
WebChartControl1.Series[0].Label.TextPattern = lbFmt;
WebChartControl1.Series[1].CrosshairLabelPattern = lbFmt;
WebChartControl1.Series[1].Label.TextPattern = lbFmt;
lbaxisY.TextPattern = lbFmt;
div = 1000000;
}
else if (maxVal <= 999999)
{
lbFmt = "{V:c2}" + " K";
WebChartControl1.Series[0].CrosshairLabelPattern = lbFmt;
WebChartControl1.Series[0].Label.TextPattern = lbFmt;
WebChartControl1.Series[1].CrosshairLabelPattern = lbFmt;
WebChartControl1.Series[1].Label.TextPattern = lbFmt;
lbaxisY.TextPattern = lbFmt;
div = 1000;
}
xGoal = xGoal / div;
foreach (DataRow dr1 in dtPareto.Rows)
{
double tScrap = Convert.ToDouble(dr1["TCS_Amount"].ToString());
tScrap = tScrap / div;
WebChartControl1.Series[0].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), tScrap));
WebChartControl1.Series[1].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), xGoal));
WebChartControl1.Series[0].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
WebChartControl1.Series[1].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
//HttpContext.Current.Items.Add("Exception", ex);
HttpContext.Current.Session.Add("Exception", ex);
if (ex is SqlException)
{
// Handle more specific SqlException exception here.
SqlException odbcExc = (SqlException)ex;
errNum = odbcExc.Number;
errDesc = odbcExc.Message;
}
else
{
// Handle generic ones here.
errDesc = ex.Message;
}
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=Scrap.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartP01(int indice, string clase, string sFilter)
{
try
{
//Week range, from current week - 13 to current week
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
semana = semana - 1;
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
WebChartControl1.Series[0].Points.Clear();
WebChartControl1.Series[1].Points.Clear();
WebChartControl1.Series[2].Points.Clear();
WebChartControl1.Series[3].Points.Clear();
WebChartControl1.SeriesSorting = DevExpress.XtraCharts.SortingMode.None;
WebChartControl1.SeriesTemplate.SeriesPointsSorting = DevExpress.XtraCharts.SortingMode.None;
string qry = "", qryBaseline = "";
string colName = "", prefix = "";
string xClass = "";
string sTblName = "", cTblName = "", aTblName = "", vTblName = "";
//Selects filter according to user's selection, dafult filter is by week
if (sFilter == "Week")
{
sTblName = "[vw_utilization_by_site_wkly] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "'";
cTblName = "[vw_utilization_by_cell_wkly] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
aTblName = "[vw_utilization_by_area_wkly] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
vTblName = "[vw_utilization_by_vsm_wkly] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
colName = "TU_Week";
prefix = "Wk";
}
else
{
sTblName = "[vw_utilization_by_site_mntly_wd] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-180).ToShortDateString() + "' AND '" + st.ToShortDateString() + "'";
cTblName = "[vw_utilization_by_cell_mntly_wd] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-180).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
aTblName = "[vw_utilization_by_area_mntly_wd] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-180).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
vTblName = "[vw_utilization_by_vsm_mntly_wd] WHERE [TU_LstWkDay] BETWEEN '" + st.AddDays(-180).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' AND";
colName = "TU_Month";
prefix = "";
}
//Validates filter level by site/area/cell, default filter is by site
switch (indice)
{
case 1:
xClass = clase;
qry = "SELECT * FROM " + vTblName + " [TU_Group] = '" + xClass +
"' ORDER BY [TU_LstWkDay]";
qryBaseline = "SELECT Top 1 [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'Utilization' and [sClass] LIKE 'All'";
break;
case 2:
xClass = clase;
qry = "SELECT * FROM " + aTblName + " [TU_Area] = '" + xClass +
"' ORDER BY [TU_LstWkDay]";
qryBaseline = "SELECT Top 1 [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'Utilization' and [sClass] LIKE '" + xClass + "'";
break;
case 3:
xClass = clase;
qry = "SELECT * FROM " + cTblName + " [TU_Celda] = '" + xClass +
"' ORDER BY [TU_LstWkDay]";
qryBaseline = "SELECT Top 1 [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'Utilization' and [sClass] LIKE '" + xClass + "'";
break;
default:
qry = "SELECT * FROM " + sTblName +
" ORDER BY [TU_LstWkDay]";
qryBaseline = "SELECT Top 1 [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'Utilization' and [sClass] LIKE 'All'";
break;
}
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dtPareto = dBHelper.QryManager(qry);
DataTable dtBaseline = dBHelper.QryManager(qryBaseline);
double xGoal = 0;
if (dtBaseline.Rows.Count > 0)
{
Double.TryParse(dtBaseline.Rows[0]["fGoal"].ToString(), out xGoal);
}
xGoal = xGoal / 100;
foreach (DataRow dr1 in dtPareto.Rows)
{
double eHrs = Convert.ToDouble(dr1["TU_DirectHrs"].ToString());
double tHrs = Convert.ToDouble(dr1["TU_TotHrs"].ToString());
double prod = (eHrs / tHrs);
WebChartControl1.Series[0].Points.Add(new DevExpress.XtraCharts.SeriesPoint(prefix + dr1[colName].ToString(), tHrs));
WebChartControl1.Series[1].Points.Add(new DevExpress.XtraCharts.SeriesPoint(prefix + dr1[colName].ToString(), eHrs));
WebChartControl1.Series[2].Points.Add(new DevExpress.XtraCharts.SeriesPoint(prefix + dr1[colName].ToString(), prod));
WebChartControl1.Series[3].Points.Add(new DevExpress.XtraCharts.SeriesPoint(prefix + dr1[colName].ToString(), xGoal));
WebChartControl1.Series[0].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series[1].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series[2].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
WebChartControl1.Series[3].Label.ResolveOverlappingMode = DevExpress.XtraCharts.ResolveOverlappingMode.Default;
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
//HttpContext.Current.Items.Add("Exception", ex);
HttpContext.Current.Session.Add("Exception", ex);
if (ex is SqlException)
{
// Handle more specific SqlException exception here.
SqlException odbcExc = (SqlException)ex;
errNum = odbcExc.Number;
errDesc = odbcExc.Message;
}
else
{
// Handle generic ones here.
errDesc = ex.Message;
}
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=Utilization.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
