public void llenarDatos_I(int indice)
{
try
{
//must use 'B' img because metric have to be low
double actual = 0;
double aop = 0;
string imagen = "goodB";
string qry = "select * from [sta_nivel1] where smetric = 'inventory' order by id";
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
//string myCnStr1 = Properties.Settings.Default.db_1033_dashboard;
//SqlConnection conn1 = new SqlConnection(myCnStr1);
//SqlCommand cmd1 = new SqlCommand("select * from [sta_nivel1] where smetric = 'inventory' order by id", conn1);
//SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
//DataTable dt1 = new DataTable();
//da1.Fill(dt1);
foreach (DataRow dr1 in dt1.Rows)
{
actual = Convert.ToDouble(dr1["factual"].ToString());
aop = Convert.ToDouble(dr1["fgoal"].ToString());
}
if (actual > aop)
{
imagen = "badB";
}
imgInventory.ImageUrl = "~/img/" + imagen + ".png";
inventoryActual.Text = actual + "";
inventoryAOP.Text = aop + "";
loadChartI(indice);
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n1_Default.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
protected string[] loadYearly(string xMetric)
{
string[] xValores = { "0", "0" };
try
{
//int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
int iVSM = ASPxComboBoxVF.SelectedIndex;
string sVSM = ASPxComboBoxVF.SelectedItem.ToString();
string query = "SELECT top 1 * FROM [DB_1033_Dashboard].[dbo].[sta_nivel2] where smetric = '" + xMetric + "' and sfilter = 'site' and stype = 'yearly' order by id desc";
string qry = "select * from (" + query + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
xValores[0] = dr1["factual"].ToString();
xValores[1] = dr1["fgoal"].ToString();
}
if (iVSM > 6)
{
string query2 = "SELECT top 1 * FROM [DB_1033_Dashboard].[dbo].[sta_nivel2] where smetric = '" + xMetric + "' and sfilter = 'VSM' and stype = 'yearly' and sclass = '" + sVSM + "' order by id desc";
string qry2 = "select * from (" + query2 + ") q1 order by id";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
xValores[0] = dr2["factual"].ToString();
xValores[1] = dr2["fgoal"].ToString();
}
}
}
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=Dashboard.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
return(xValores);
}
protected string[] loadMonthly(string xMetric)
{
string[] xValores = { "0", "0" };
try
{
int iVSM = ASPxComboBoxVF.SelectedIndex;
string sVSM = ASPxComboBoxVF.SelectedItem.ToString();
string query = "SELECT top 1 * FROM [DB_1033_Dashboard].[dbo].[sta_nivel2] where smetric = '" + xMetric + "' and sfilter = 'site' and stype = 'monthly' order by id desc";
string qry = "select * from (" + query + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
xValores[0] = dr1["factual"].ToString();
xValores[1] = dr1["fgoal"].ToString();
}
if (iVSM > 6 || xMetric == "AGED WIP" || xMetric == "ESCAPES" || xMetric == "INCIDENTS" || xMetric == "INTERNAL ESCAPES" || xMetric == "LABOR PRODUCTIVITY" || xMetric == "OTTR" || xMetric == "PASTDUE" || xMetric == "PPMS" || xMetric == "SCRAP" || xMetric == "UTILIZATION")
{
string query2 = "SELECT top 1 * FROM [DB_1033_Dashboard].[dbo].[sta_nivel2] where smetric = '" + xMetric + "' and sfilter = 'VSM' and stype = 'monthly' and sclass = '" + sVSM + "' order by id desc";
string qry2 = "select * from (" + query2 + ") q1 order by id";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
xValores[0] = dr2["factual"].ToString();
xValores[1] = dr2["fgoal"].ToString();
}
}
}
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=Dashboard.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
return(xValores);
}
//Net Productivity Data
public void llenarDatos_P02(int indice, string _sClass)
{
try
{
double actual = 0;
double aop = 0;
string imagen = "good";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
string qry = "";
qry = "select TOP 1 * from [sta_nivel2] where smetric = 'net productivity' and sClass = '" + _sClass + "' order by id desc";
DataTable dtPareto = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dtPareto.Rows)
{
actual = Convert.ToDouble(dr1["factual"].ToString());
aop = Convert.ToDouble(dr1["fgoal"].ToString());
}
if (actual < aop)
{
imagen = "bad";
}
imgP02.ImageUrl = "~/img/" + imagen + ".png";
P02Actual.Text = actual + "%";
P02AOP.Text = aop + "%";
//loadChartP02(indice, "");
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Productivity.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
public void llenarDatos_D(int indice)
{
try
{
double actual = 0;
double aop = 0;
string imagen = "good";
string qry = "select * from [sta_nivel1] where smetric = 'delivery' order by id";
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
actual = Convert.ToDouble(dr1["factual"].ToString());
aop = Convert.ToDouble(dr1["fgoal"].ToString());
}
if (actual < aop)
{
imagen = "bad";
}
imgDelivery.ImageUrl = "~/img/" + imagen + ".png";
deliveryActual.Text = actual + "";
deliveryAOP.Text = aop + "";
loadChartD(indice);
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n1_Default.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
public void llenarDatos_D03(int indice)
{
try
{
double actual = 0;
double aop = 0;
string imagen = "goodB";
string xClass = "All";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
string qry = "select TOP 1 * from [sta_nivel2] where smetric = 'pastdue' and sClass = '" + xClass + "' order by id desc";
DataTable dtPareto = dBHelper.QryManager(qry);
if (dtPareto.Rows.Count > 0)
{
actual = Convert.ToDouble(dtPareto.Rows[0]["factual"].ToString());
aop = Convert.ToDouble(dtPareto.Rows[0]["fgoal"].ToString());
ASPxLabelD3.Text = "Last update: " + dtPareto.Rows[0]["sLstWkDay"].ToString().Substring(0, 10);
}
if (actual > aop)
{
imagen = "badB";
}
imgD03.ImageUrl = "~/img/" + imagen + ".png";
D03Actual.Text = (actual / 1000).ToString("C2") + "K";
D03AOP.Text = (aop / 1000).ToString("C2") + "K";
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Delivery.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
public void llenarDatos_D01(int indice)
{
try
{
double actual = 0;
double aop = 0;
string imagen = "good";
string xClass = "All";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
//string qry = "SELECT TOP 1 * FROM vw_ottr_by_wk ORDER BY [TO_Yr] desc, [TO_Wk] desc, [TO_Month] desc";
string qry = "select TOP 1 * from [sta_nivel2] where smetric = 'OTTR' and sClass = '" + xClass + "' order by id desc";
DataTable dtPareto = dBHelper.QryManager(qry);
if (dtPareto.Rows.Count > 0)
{
actual = Convert.ToDouble(dtPareto.Rows[0]["factual"].ToString());
aop = Convert.ToDouble(dtPareto.Rows[0]["fgoal"].ToString());
}
if (actual < aop)
{
imagen = "bad";
}
imgD01.ImageUrl = "~/img/" + imagen + ".png";
D01Actual.Text = actual + "%";
D01AOP.Text = aop + "%";
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Delivery.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
public void llenarDatos_S04(int indice)
{
try
{
double actual = 0;
double aop = 0;
string imagen = "good";
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
string query1 = "select * from [sta_nivel2] where smetric = 'meps' and sfilter = 'SITE' and sdesc = '" + (semana - 1) + "' order by id"; //porque si existe semana actual
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(query1);
foreach (DataRow dr1 in dt1.Rows)
{
actual = Convert.ToDouble(dr1["factual"].ToString());
aop = Convert.ToDouble(dr1["fgoal"].ToString());
ASPxLabelS04.Text = "Last update: " + dr1["sLstWkDay"].ToString().Substring(0, 10);
}
if (actual < aop)
{
imagen = "bad";
}
ImgS04.ImageUrl = "~/img/" + imagen + ".png";
S04Actual.Text = (actual).ToString();
S04AOP.Text = (aop).ToString();
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Safety.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
protected string loadUpdates(string xMetric)
{
string xDescripcion = "";
try
{
//int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
string query = "SELECT TOP 1 * FROM tbl_metricsUpdates WHERE reportName = '" + xMetric + "'";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(query);
foreach (DataRow dr1 in dt1.Rows)
{
string[] dates = dr1["lastUpdateText"].ToString().Split(' ');
xDescripcion = dates[2] + "/2021";
//xDescripcion = dr1["lastUpdateDate"].ToString();
}
}
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=Dashboard.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
return(xDescripcion);
}
protected string loadHighlights(string xMetric)
{
string xDescripcion = "";
try
{
//int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
string query = "SELECT TOP 1 * FROM tbl_highlights WHERE smetrico = '" + xMetric + "' order by highlight_id desc";
string qry = "select * from (" + query + ") q1 order by highlight_id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
xDescripcion = dr1["descripcion"].ToString();
}
}
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=Dashboard.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
return(xDescripcion.Replace("\n", "<br />"));
}
private void loadUpdate()
{
try
{
string qry = "SELECT * FROM [tbl_metricsUpdates] WHERE [reportName] = 'ottr'";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt.Rows)
{
lbLUpd.Text = dr1["lastUpdateText"].ToString();
}
}
catch (SqlException ex)
{
//https ://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc645603(v=sql.105)?redirectedfrom=MSDN
int errNum = ex.Number;
//HttpContext.Current.Items.Add("Exception", ex);
HttpContext.Current.Session.Add("Exception", ex);
string errDesc = ex.Message;
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=OTTR.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
protected int regresValueType(string xMetric)
{
int xTipo = 1;
try
{
string qry = "select sValueType from tbl_settings where stype = 'dashboard' and svalue = '" + xMetric + "'";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
xTipo = Convert.ToInt32(dr1["sValueType"].ToString());
}
}
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=Dashboard.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
return(xTipo);
}
private void loadChartE01(int tipo, string clase, string filtro, int xGraph)
{
try
{
chartE01.Series["Series1"].Points.Clear();
chartE01.Series["Series2"].Points.Clear();
chartE01.Series["Series3"].Points.Clear();
string xTipo = "weekly";
if (tipo < 2)
{
xTipo = "WEEKLY";
}
if (tipo == 2)
{
xTipo = "MONTHLY";
}
if (tipo == 3)
{
xTipo = "QUARTERLY";
}
if (tipo == 4)
{
xTipo = "YEARLY";
}
if (xGraph < 2)
{
string query1 = "select top 13 * from [sta_nivel2] where smetric = 'ppms' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' order by id desc";
string qry1 = "select * from (" + query1 + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry1);
foreach (DataRow dr1 in dt1.Rows)
{
double xActual = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
chartE01.Series["Series1"].Points.AddXY(dr1["sdesc"].ToString(), xActual);
chartE01.Series["Series2"].Points.AddXY(dr1["sdesc"].ToString(), xGoal);
chartE01.Series["Series3"].Points.AddXY(dr1["sdesc"].ToString(), "0");
}
}
if (xGraph == 2)
{
string query2 = "select top 10 * from [sta_nivel2p] where smetric = 'ppms' and stype = 'causes' order by id";
string qry2 = "select * from (" + query2 + ") q1 order by id";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["factual"].ToString());
double xGoal = Convert.ToDouble(dr2["fsum"].ToString());
chartE01.Series["Series1"].Points.AddXY(dr2["scause"].ToString(), xActual);
chartE01.Series["Series2"].Points.AddXY(dr2["scause"].ToString(), xGoal);
chartE01.Series["Series1"].ToolTip = "#VALX";
}
}
if (xGraph == 3)
{
string query2 = "select top 6 * from [sta_nivel2f] where smetric = 'ppms' and stype = '" + xTipo + "' order by id desc";
string qry2 = "select * from (" + query2 + ") q1 order by id";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["factual"].ToString());
double xGoal = Convert.ToDouble(dr2["fsum"].ToString());
chartE01.Series["Series1"].Points.AddXY(dr2["scause"].ToString(), xActual);
chartE01.Series["Series2"].Points.AddXY(dr2["scause"].ToString(), xGoal);
chartE01.Series["Series1"].ToolTip = "#VALX";
}
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=sc_ppms.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
//Labor Productivity Charts
private void loadChartP01(int indice, string clase, string sFilter, int mcView)
{
try
{
//Week range, from current week - 13 to current week
int yr = DateTime.Now.Year;
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
string dtFrom = st.AddDays(-91).ToShortDateString();
string dtTo = st.ToShortDateString();
string dtFrom2 = st.AddDays(-300).ToShortDateString();
string mnth = st.AddDays(-7).ToString("MMM", CultureInfo.InvariantCulture);
if (semana == 53 || semana == 1)
{
semana = 52;
yr = yr - 1;
}
else
{
semana = semana - 1;
}
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;
WebChartControl2.Series[0].Points.Clear();
WebChartControl2.SeriesSorting = SortingMode.None;
WebChartControl2.SeriesTemplate.SeriesPointsSorting = SortingMode.None;
string qry = "", qry2 = "", qryBaseline = "";
string colName = "";
string xClass = "", prefix = "";
string sTblName = "", cTblName = "", aTblName = "", mTblName = "", aaTblName = "", acTblName = "", amTblName = "", smTbl = "", cmTbl = "", amTbl = "", mmTbl = "", asmTbl = "", acmTbl = "", aamTbl = "", ammTbl = "", vTblName = "", avTblName = "", vmTbl = "", vmmTbl = "";
string qryOrder = "", fBy = "";
switch (indice)
{
case 1:
fBy = "VSM";
break;
case 2:
fBy = "Area";
break;
case 3:
fBy = "Cell";
break;
case 4:
fBy = "MRP";
break;
default:
clase = "All";
fBy = "Site";
break;
}
//Selects filter according to user's selection, dafult filter is by week
if (sFilter == "Week")
{
sTblName = "[vw_ottr_by_site_wkly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "'";
cTblName = "[vw_ottr_by_cell_wkly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
aTblName = "[vw_ottr_by_area_wkly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
mTblName = "[vw_ottr_by_mrp_wkly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
vTblName = "[vw_ottr_by_vsm_wkly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
//qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = 'All' AND [sType] = 'Weekly' AND [sDesc] = [TO_Wk] AND [sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "'";
qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = '" + clase + "' AND [sFilter] = '" + fBy + "' AND [sType] = 'Weekly' AND [sDesc] = [TO_Wk] AND [sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "'";
qryOrder = "[TO_Wk]";
colName = "TO_Wk";
prefix = "Wk";
aaTblName = "[vw_ottr_by_area_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
acTblName = "[vw_ottr_by_cell_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
amTblName = "[vw_ottr_by_mrp_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
avTblName = "[vw_ottr_by_vsm_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
smTbl = "[vw_ottr_misses_by_site_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
cmTbl = "[vw_ottr_misses_by_cell_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
amTbl = "[vw_ottr_misses_by_area_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
mmTbl = "[vw_ottr_misses_by_mrp_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
vmTbl = "[vw_ottr_misses_by_vsm_wkly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
asmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Wk FROM " + smTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr,TO_Wk";
acmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Wk FROM " + cmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr,TO_Wk";
aamTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Wk FROM " + amTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr,TO_Wk";
ammTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Wk FROM " + mmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr,TO_Wk";
vmmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Wk FROM " + vmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr,TO_Wk";
}
if (sFilter == "Month")
{
sTblName = "[vw_ottr_by_site_mntly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "'";
cTblName = "[vw_ottr_by_cell_mntly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "' AND";
aTblName = "[vw_ottr_by_area_mntly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "' AND";
mTblName = "[vw_ottr_by_mrp_mntly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "' AND";
vTblName = "[vw_ottr_by_vsm_mntly] WHERE [TO_sLstWkDay] BETWEEN '" + dtFrom + "' AND '" + dtTo + "' AND";
//qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = 'All' AND [sType] = 'Monthly' AND " +
// "[sDesc] = [TO_Month] AND [sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "'";
qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = '" + clase + "' AND [sFilter] = '" + fBy + "' AND [sType] = 'Monthly' AND " +
"[sDesc] = [TO_Month] AND [sLstWkDay] BETWEEN '" + dtFrom2 + "' AND '" + dtTo + "'";
qryOrder = "[TO_sLstWkDay]";
colName = "TO_Month";
aaTblName = "[vw_ottr_by_area_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
acTblName = "[vw_ottr_by_cell_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
amTblName = "[vw_ottr_by_mrp_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
avTblName = "[vw_ottr_by_vsm_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
smTbl = "[vw_ottr_misses_by_site_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
cmTbl = "[vw_ottr_misses_by_cell_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
amTbl = "[vw_ottr_misses_by_area_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
mmTbl = "[vw_ottr_misses_by_mrp_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Month] = '" + mnth + "'";
vmTbl = "[vw_ottr_misses_by_vsm_mntly] WHERE [TO_Yr] = " + yr + " AND [TO_Wk] = " + semana;
asmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Month FROM " + smTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr, TO_Month";
acmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Month FROM " + cmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr, TO_Month";
aamTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Month FROM " + amTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr, TO_Month";
ammTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Month FROM " + mmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr, TO_Month";
vmmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr, TO_Month FROM " + vmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr, TO_Month";
}
if (sFilter == "Year")
{
sTblName = "[vw_ottr_by_site_yrly] WHERE [TO_Yr] BETWEEN " + (yr - 1) + " AND " + yr;
cTblName = "[vw_ottr_by_cell_yrly] WHERE [TO_Yr] BETWEEN " + (yr - 1) + " AND " + yr + " AND ";
aTblName = "[vw_ottr_by_area_yrly] WHERE [TO_Yr] BETWEEN " + (yr - 1) + " AND " + yr + " AND ";
mTblName = "[vw_ottr_by_mrp_yrly] WHERE [TO_Yr] BETWEEN " + (yr - 1) + " AND " + yr + " AND ";
vTblName = "[vw_ottr_by_vsm_yrly] WHERE [TO_Yr] BETWEEN " + (yr - 1) + " AND " + yr + " AND ";
//qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = 'All' AND [sType] = 'Yearly' AND " +
// "[sDesc] = [TO_Yr] AND [sDesc] BETWEEN '" + (yr - 1) + "' AND '" + yr + "'";
qryBaseline = "SELECT [fGoal] FROM [sta_nivel2] WHERE [sMetric] = 'OTTR' AND [sClass] = '" + clase + "' AND [sFilter] = '" + fBy + "' AND [sType] = 'Yearly' AND " +
"[sDesc] = [TO_Yr] AND [sDesc] BETWEEN '" + (yr - 1) + "' AND '" + yr + "'";
qryOrder = "[TO_sLstWkDay]";
colName = "TO_Yr";
aaTblName = "[vw_ottr_by_area_yrly] WHERE [TO_Yr] = " + yr;
acTblName = "[vw_ottr_by_cell_yrly] WHERE [TO_Yr] = " + yr;
amTblName = "[vw_ottr_by_mrp_yrly] WHERE [TO_Yr] = " + yr;
avTblName = "[vw_ottr_by_vsm_yrly] WHERE [TO_Yr] = " + yr;
smTbl = "[vw_ottr_misses_by_site_yrly] WHERE [TO_Yr] = " + yr;
cmTbl = "[vw_ottr_misses_by_cell_yrly] WHERE [TO_Yr] = " + yr;
amTbl = "[vw_ottr_misses_by_area_yrly] WHERE [TO_Yr] = " + yr;
mmTbl = "[vw_ottr_misses_by_mrp_yrly] WHERE [TO_Yr] = " + yr;
vmTbl = "[vw_ottr_misses_by_vsm_yrly] WHERE [TO_Yr] = " + yr;
asmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr FROM " + smTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr";
acmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr FROM " + cmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr";
aamTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr FROM " + amTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr";
ammTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr FROM " + mmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr";
vmmTbl = "SELECT TO_DlyCode, TO_CodeCat, SUM(Qty) as Qty, TO_Yr FROM " + vmTbl + " Group By TO_DlyCode,TO_CodeCat,TO_Yr";
}
if (mcView == 0)
{
//Validates filter level by site/area/cell, default filter is by site
switch (indice)
{
case 1:
xClass = clase;
qry = "SELECT *, [AOP] = ISNULL((" + qryBaseline + "),0) FROM " + vTblName + " [TO_Group] = '" + xClass + "' Order by [TO_Yr], " + qryOrder + ", [TO_Group]";
qry2 = "SELECT * FROM " + amTbl + " AND [TO_rArea] = '" + xClass + "' Order by [TO_Yr], [TO_rArea], [Qty] desc";
break;
case 2:
xClass = clase;
qry = "SELECT *, [AOP] = ISNULL((" + qryBaseline + "),0) FROM " + aTblName + " [TO_rArea] = '" + xClass + "' Order by [TO_Yr], " + qryOrder + ", [TO_rArea]";
qry2 = "SELECT * FROM " + amTbl + " AND [TO_rArea] = '" + xClass + "' Order by [TO_Yr], [TO_rArea], [Qty] desc";
break;
case 3:
xClass = clase;
qry = "SELECT *, [AOP] = ISNULL((" + qryBaseline + "),0) FROM " + cTblName + " [TO_Cell] = '" + xClass + "' Order by [TO_Yr], " + qryOrder + ", [TO_Cell]";
qry2 = "SELECT * FROM " + cmTbl + " AND [TO_Cell] = '" + xClass + "' Order by [TO_Yr], [TO_Cell], [Qty] desc";
break;
case 4:
xClass = clase;
qry = "SELECT *, [AOP] = ISNULL((" + qryBaseline + "),0) FROM " + mTblName + " [TO_MRP] = '" + xClass + "' Order by [TO_Yr], " + qryOrder + ", [TO_MRP]";
qry2 = "SELECT * FROM " + mmTbl + " AND [TO_MRP] = '" + xClass + "' Order by [TO_Yr], [TO_MRP], [Qty] desc";
break;
default:
qry = "SELECT *, [AOP] = ISNULL((" + qryBaseline + "),0) FROM " + sTblName + " Order by [TO_Yr], " + qryOrder;
qry2 = "SELECT * FROM " + smTbl + " Order by [TO_Yr], [Qty] desc";
break;
}
}
else
{
prefix = "";
if (mcView == 1)
{
qry = "SELECT * FROM " + avTblName + " Order by [TO_Yr], [TO_Group]";
qry2 = aamTbl + " Order by [TO_Yr], [Qty] desc";
colName = "TO_Group";
}
if (mcView == 2)
{
qry = "SELECT * FROM " + aaTblName + " Order by [TO_Yr], [TO_rArea]";
qry2 = aamTbl + " Order by [TO_Yr], [Qty] desc";
colName = "TO_rArea";
}
if (mcView == 3)
{
qry = "SELECT * FROM " + acTblName + " Order by [TO_Yr], [TO_Cell]";
qry2 = acmTbl + " Order by [TO_Yr], [Qty] desc";
colName = "TO_Cell";
}
if (mcView == 4)
{
qry = "SELECT * FROM " + amTblName + " Order by [TO_Yr], [TO_MRP]";
qry2 = ammTbl + " Order by [TO_Yr], [Qty] desc";
colName = "TO_MRP";
}
}
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dtPareto = dBHelper.QryManager(qry);
DataTable dtPatero2 = dBHelper.QryManager(qry2);
if (mcView == 0)
{
foreach (DataRow dr1 in dtPareto.Rows)
{
double xHit = Convert.ToDouble(dr1["HIT"].ToString());
double xTotHrs = Convert.ToDouble(dr1["TotalOrdrs"].ToString());
double perc = (xHit / xTotHrs);
double xGoal = Convert.ToDouble(dr1["AOP"].ToString()) / 100;
WebChartControl1.Series[0].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), xHit));
WebChartControl1.Series[1].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), xTotHrs));
WebChartControl1.Series[2].Points.Add(new SeriesPoint(prefix + dr1[colName].ToString(), perc));
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;
}
foreach (DataRow dr1 in dtPatero2.Rows)
{
double xHit = Convert.ToDouble(dr1["Qty"].ToString());
WebChartControl2.Series[0].Points.Add(new SeriesPoint(dr1["TO_DlyCode"].ToString(), xHit));
WebChartControl2.Series[0].Label.ResolveOverlappingMode = ResolveOverlappingMode.Default;
}
}
else
{
foreach (DataRow dr1 in dtPareto.Rows)
{
double xHit = Convert.ToDouble(dr1["HIT"].ToString());
double xTotHrs = Convert.ToDouble(dr1["TotalOrdrs"].ToString());
double perc = (xHit / xTotHrs);
double xGoal = 1;
WebChartControl1.Series[0].Points.Add(new SeriesPoint(dr1[colName].ToString(), xHit));
WebChartControl1.Series[1].Points.Add(new SeriesPoint(dr1[colName].ToString(), xTotHrs));
WebChartControl1.Series[2].Points.Add(new SeriesPoint(dr1[colName].ToString(), perc));
WebChartControl1.Series[3].Points.Add(new SeriesPoint(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;
}
foreach (DataRow dr1 in dtPatero2.Rows)
{
double xHit = Convert.ToDouble(dr1["Qty"].ToString());
WebChartControl2.Series[0].Points.Add(new SeriesPoint(dr1["TO_DlyCode"].ToString(), xHit));
WebChartControl2.Series[0].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;
}
//Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=OTTR.aspx&msg=" + errNum + "&errDesc=" + errDesc, true);
Response.Redirect("~\\CustomErrors\\Errors.aspx?handler=OTTR.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartP(int indice)
{
try
{
chartP.Series["Series1"].Points.Clear();
chartP.Series["Series2"].Points.Clear();
string tipo = "monthly";
if (indice == 4)
{
tipo = "quarterly";
}
if (indice == 5)
{
tipo = "yearly";
}
string query1 = "select top 6 * from [sta_nivel1] where smetric = 'productivity' and stype = '" + tipo + "' order by id desc";
string qry = "select * from (" + query1 + ") q1 order by id";
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper.QryManager(qry);
foreach (DataRow dr2 in dt2.Rows)
{
chartP.Series["Series1"].Points.AddXY(dr2["sdesc"].ToString(), dr2["factual"].ToString());
chartP.Series["Series2"].Points.AddXY(dr2["sdesc"].ToString(), dr2["fgoal"].ToString());
ASPxLabelP.Text = "Last update: " + dr2["sLstWkDay"].ToString().Substring(0, 10);
}
chartPp.Series["Series1"].Points.Clear();
chartPp.Series["Series2"].Points.Clear();
chartPp.Series["Series3"].Points.Clear();
qry = "select * from [sta_nivel1p] where smetric = 'productivity' and stype = '" + tipo + "' order by id";
DataTable dt3 = dBHelper.QryManager(qry);
foreach (DataRow dr3 in dt3.Rows)
{
chartPp.Series["Series1"].Points.AddXY(dr3["sdesc"].ToString(), dr3["factual"].ToString());
chartPp.Series["Series2"].Points.AddXY(dr3["sdesc"].ToString(), dr3["fgoal"].ToString());
chartPp.Series["Series3"].Points.AddXY(dr3["sdesc"].ToString(), "0");
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n1_Default.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartI(int indice)
{
try
{
chartI.Series["Series1"].Points.Clear();
chartI.Series["Series2"].Points.Clear();
string tipo = "monthly";
if (indice == 4)
{
tipo = "quarterly";
}
if (indice == 5)
{
tipo = "yearly";
}
string query1 = "select top 6 * from [sta_nivel1] where smetric = 'inventory' and stype = '" + tipo + "' order by id desc";
string qry = "select * from (" + query1 + ") q1 order by id";
//Connection object, retrieves sql data
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper.QryManager(qry);
//string myCnStr2 = Properties.Settings.Default.db_1033_dashboard;
//SqlConnection conn2 = new SqlConnection(myCnStr2);
//string query1 = "select top 6 * from [sta_nivel1] where smetric = 'inventory' and stype = '" + tipo + "' order by id desc";
//SqlCommand cmd2 = new SqlCommand("select * from ("+query1+") q1 order by id", conn2);
//SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
//DataTable dt2 = new DataTable();
//da2.Fill(dt2);
foreach (DataRow dr2 in dt2.Rows)
{
chartI.Series["Series1"].Points.AddXY(dr2["sdesc"].ToString(), dr2["factual"].ToString());
chartI.Series["Series2"].Points.AddXY(dr2["sdesc"].ToString(), dr2["fgoal"].ToString());
ASPxLabelI.Text = "Last update: " + dr2["sLstWkDay"].ToString().Substring(0, 10);
}
chartIp.Series["Series1"].Points.Clear();
chartIp.Series["Series2"].Points.Clear();
chartIp.Series["Series3"].Points.Clear();
qry = "select * from [sta_nivel1p] where smetric = 'inventory' and stype = '" + tipo + "' order by id";
DataTable dt3 = dBHelper.QryManager(qry);
//string myCnStr3 = Properties.Settings.Default.db_1033_dashboard;
//SqlConnection conn3 = new SqlConnection(myCnStr3);
//SqlCommand cmd3 = new SqlCommand("select * from [sta_nivel1p] where smetric = 'inventory' and stype = '" + tipo + "' order by id", conn3);
//SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
//DataTable dt3 = new DataTable();
//da3.Fill(dt3);
double xTemp = .90;
foreach (DataRow dr3 in dt3.Rows)
{
double xGoal = Convert.ToDouble(dr3["fgoal"].ToString());
chartIp.Series["Series1"].Points.AddXY(dr3["sdesc"].ToString(), dr3["factual"].ToString());
chartIp.Series["Series2"].Points.AddXY(dr3["sdesc"].ToString(), dr3["fgoal"].ToString());
chartIp.Series["Series3"].Points.AddXY(dr3["sdesc"].ToString(), (xGoal * xTemp).ToString());
xTemp = xTemp - .05;
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n1_Default.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartP05(int indice, string clase)
{
try
{
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
semana = semana - 1; //semana actual aun no cierra
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
chartTP05.Series["Series1"].Points.Clear();
chartTP05.Series["Series2"].Points.Clear();
string xTipo = "weekly";
//tipo=0
String xFilter = "SITE";
string xClass = "All";
if (indice == 1)
{
xClass = clase;
xFilter = "VSM";
}
if (indice == 2)
{
xClass = clase;
xFilter = "CELL";
}
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
string qry = "";
qry = "SELECT TOP 8 * FROM [sta_nivel2] WHERE [sMetric] = 'Utilization' AND [sClass] = '" + xClass + "' AND [sType] = '" + xTipo + "' AND [sFilter] = '" + xFilter +
"' AND [sLstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "'" +
" Order by [sLstWkDay] desc";
string qry1 = "select * from (" + qry + ") q1 order by id";
DataTable dtPareto = dBHelper.QryManager(qry1);
foreach (DataRow dr1 in dtPareto.Rows)
{
double xActual = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
chartTP05.Series["Series1"].Points.AddXY(dr1["sdesc"].ToString(), xActual);
chartTP05.Series["Series2"].Points.AddXY(dr1["sdesc"].ToString(), xGoal);
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Productivity.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartI03(int tipo, string clase, string filtro)
{
try
{
chartTI03.Series["Series1"].Points.Clear();
chartTI03.Series["Series2"].Points.Clear();
chartTI03.Series["Series3"].Points.Clear();
string xTipo = "weekly";
if (tipo < 2)
{
xTipo = "WEEKLY";
}
if (tipo == 2)
{
xTipo = "MONTHLY";
}
if (tipo == 3)
{
xTipo = "QUARTERLY";
}
if (tipo == 4)
{
xTipo = "YEARLY";
}
string query1 = "";
if (xTipo == "WEEKLY")
{
query1 = "select top 13 * from [sta_nivel2] where smetric = 'vmi' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' order by id desc";
}
else
{
query1 = "select top 6 * from [sta_nivel2] where smetric = 'vmi' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' order by id desc";
}
string qry = "select * from (" + query1 + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
double xActual = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
chartTI03.Series["Series1"].Points.AddXY(dr1["sdesc"].ToString(), Math.Round((xActual / 1000000), 2));
chartTI03.Series["Series2"].Points.AddXY(dr1["sdesc"].ToString(), Math.Round((xGoal / 1000000), 2));
chartTI03.Series["Series3"].Points.AddXY(dr1["sdesc"].ToString(), Math.Round((xGoal / 1000000), 2));
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Inventory.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartS03(int tipo, string clase, string filtro)
{
try
{
chartTS03.Series["Series1"].Points.Clear();
chartTS03.Series["Series2"].Points.Clear();
chartTS03.Series["Series3"].Points.Clear();
chartPS03.Series["Series1"].Points.Clear();
chartPS03.Series["Series2"].Points.Clear();
string xTipo = "weekly";
if (tipo < 2)
{
xTipo = "WEEKLY";
}
if (tipo == 2)
{
xTipo = "MONTHLY";
}
if (tipo == 3)
{
xTipo = "QUARTERLY";
}
if (tipo == 4)
{
xTipo = "YEARLY";
}
int semana = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
string query1 = "";
if (xTipo == "WEEKLY")
{
query1 = "select top 13 * from [sta_nivel2] where smetric = 'tpms' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' and sdesc <= '" + (semana - 1) + "' order by id desc";
}
else
{
query1 = "select top 13 * from [sta_nivel2] where smetric = 'tpms' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' order by id desc";
}
string qry = "select * from (" + query1 + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry);
foreach (DataRow dr1 in dt1.Rows)
{
double xActual = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
chartTS03.Series["Series1"].Points.AddXY(dr1["sdesc"].ToString(), xActual);
chartTS03.Series["Series2"].Points.AddXY(dr1["sdesc"].ToString(), xGoal);
chartTS03.Series["Series3"].Points.AddXY(dr1["sdesc"].ToString(), "0");
}
string query2 = "select * from [sta_nivel2p] where smetric = 'tpms' and stype = '" + filtro + "' order by id";
if (clase != "All")
{
query2 = "select * from [sta_nivel2p] where smetric = 'tpms' and stype = '" + filtro + "' and scause = '" + clase + "' order by id";
}
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(query2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["factual"].ToString());
double xGoal = Convert.ToDouble(dr2["fsum"].ToString());
chartPS03.Series["Series1"].Points.AddXY(dr2["scause"].ToString(), xActual);
chartPS03.Series["Series2"].Points.AddXY(dr2["scause"].ToString(), xGoal);
chartPS03.Series["Series1"].ToolTip = "#VALX";
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Safety.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
private void loadChartD01(int tipo, string clase, string filtro)
{
try
{
chartTD01.Series["Series1"].Points.Clear();
chartTD01.Series["Series2"].Points.Clear();
chartTD01.Series["Series3"].Points.Clear();
chartPD01.Series["Series1"].Points.Clear();
chartPD01.Series["Series2"].Points.Clear();
string xTipo = "weekly";
if (tipo < 2)
{
xTipo = "WEEKLY";
}
if (tipo == 2)
{
xTipo = "MONTHLY";
}
if (tipo == 3)
{
xTipo = "QUARTERLY";
}
if (tipo == 4)
{
xTipo = "YEARLY";
}
//string query1 = "select top 8 * from [sta_nivel2] where smetric = 'ottr' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "' order by id desc";
FunctionHelper.FncHelper fh = new FunctionHelper.FncHelper();
DateTime st = fh.GetSaturday(DateTime.Now);
string qry = "select TOP 8 * from [sta_nivel2] where smetric = 'ottr' and sfilter = '" + filtro + "' and sclass = '" + clase + "' and stype = '" + xTipo + "'" +
" AND [sLstWkDay] BETWEEN '" + st.AddDays(-91).ToShortDateString() + "' AND '" + st.ToShortDateString() + "' Order by [sLstWkDay] desc";
string qry1 = "select * from (" + qry + ") q1 order by id";
SQLHelper.DBHelper dBHelper = new SQLHelper.DBHelper();
DataTable dt1 = dBHelper.QryManager(qry1);
foreach (DataRow dr1 in dt1.Rows)
{
double xActual = Convert.ToDouble(dr1["factual"].ToString());
double xGoal = Convert.ToDouble(dr1["fgoal"].ToString());
chartTD01.Series["Series1"].Points.AddXY(dr1["sdesc"].ToString(), xActual);
chartTD01.Series["Series2"].Points.AddXY(dr1["sdesc"].ToString(), xGoal);
//chartTD01.Series["Series3"].Points.AddXY(dr1["sdesc"].ToString(), "0");
chartTD01.Series["Series2"].ToolTip = "#VALY";
}
string query2 = "select top 5 * from [sta_nivel2p] where smetric = 'ottr' and stype = 'causes' order by id desc";
string qry2 = "select * from (" + query2 + ") q1 order by fActual desc";
SQLHelper.DBHelper dBHelper2 = new SQLHelper.DBHelper();
DataTable dt2 = dBHelper2.QryManager(qry2);
foreach (DataRow dr2 in dt2.Rows)
{
double xActual = Convert.ToDouble(dr2["factual"].ToString());
double xGoal = Convert.ToDouble(dr2["fsum"].ToString());
chartPD01.Series["Series1"].Points.AddXY(dr2["scause"].ToString(), xActual);
chartPD01.Series["Series2"].Points.AddXY(dr2["scause"].ToString(), xGoal);
chartPD01.Series["Series2"].ToolTip = "#VALX";
}
}
catch (Exception ex)
{
int errNum = -99999999;
string errDesc = "";
HttpContext.Current.Items.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;
}
Server.Transfer("~\\CustomErrors\\Errors.aspx?handler=n2_Delivery.aspx&msg=" + errNum + "&errDesc=" + errDesc);
}
}
