public List<ChartSeries> getChartSeriesList(string pKPI, string pStoredProcedure)
{
List<ChartSeries> thisChartSeriesList = new List<ChartSeries>();
ChartSeries thisChartSeries = new ChartSeries();
Double dWork;
Boolean ValueIsaNumber;
Boolean firstPass = true;
pStoredProcedure = "p_TTL_GetKPIData";
pKPI = "TotalHoursWorked";
// Get series data from the database
try
{
using (sqlConnection = new SqlConnection(DBConnxnString))
{
var cmd = new SqlCommand()
{
CommandType = System.Data.CommandType.StoredProcedure,
Connection = sqlConnection,
CommandText = pStoredProcedure
};
cmd.CommandTimeout = 300;
sqlConnection.Open();
var dRdr = cmd.ExecuteReader();
String seriesName = "";
String lastSeriesName = "";
String elemName = "";
Double elemValue = 0.0;
// -------------------------------------------------------------------------
// Make a series element list object for this chart series
List<SeriesElement> SeriesElementList = new List<SeriesElement>();
while (dRdr.Read())
{
// -------------------------------------------------------------------------
// Place the database data in local variables for processing.
seriesName = dRdr["SeriesName"].ToString();
elemName = dRdr["ElemName"].ToString();
ValueIsaNumber = Double.TryParse(dRdr["ElementValue"].ToString(), out dWork);
if (ValueIsaNumber)
elemValue = dWork;
else
{
MessageBox.Show("Could not convert the Value for Series Element [" +
elemName + "] to a valid double! Zero substituted.");
elemValue = 0.0;
}
// -------------------------------------------------------------------------
if (firstPass || seriesName == lastSeriesName)
{
if (firstPass)
thisChartSeries.Name = seriesName;
// Add the series ElemName and ElemValue to the current SeriesElementList
SeriesElement thisSeriesElement = new SeriesElement();
thisSeriesElement.Name = elemName;
thisSeriesElement.Value = elemValue;
//thisSeriesElement.Color = "{something}";
//thisSeriesElement.altName = "{something}";
SeriesElementList.Add(thisSeriesElement);
thisSeriesElement = null;
firstPass = false;
lastSeriesName = seriesName;
}
else
{
// Don't forget to add the current SeriesElementList to the current ChartSeries object.
// Make a new chart series object
thisChartSeries = new ChartSeries();
thisChartSeries.Name = seriesName;
// Make a new SeriesElementList
// Make a new Series Element object
// Add the series ElemName and ElemValue to the new SeriesElementList
}
// If a SeriesElementList exists, close it out and place it into thisChartSeries.SeriesElementList
// There will always be at lease one SeriesElementList created before this while loop is started.
//thisChartSeriesList.Add(thisChartSeries);
// thisChartSeries.SeriesElementList.Add(SeriesElementList);
thisChartSeries.Name = seriesName;
// else
thisChartSeries.Name = dRdr["SeriesName"].ToString();
// thisSeriesElement.Color = dRdr["Color"].ToString();
// thisSeriesElement.altName = dRdr["AltName"].ToString();
//SeriesElement thisSeriesElement = new SeriesElement();
//thisSeriesElement.Name = dRdr["ElemName"].ToString();
//ValueIsaNumber = Double.TryParse(dRdr["ElementValue"].ToString(), out dWork);
//if (ValueIsaNumber)
// thisSeriesElement.Value = dWork;
//else
//{
// MessageBox.Show("Could not convert the Value for Series Element [" +
// thisSeriesElement.Name + "] to a valid double! Zero substituted.");
// thisSeriesElement.Value = 0.0;
//}
//thisChartSeries.SeriesElementList.Add(thisSeriesElement);
// Add the Series Element to the list of elements to be returned by this method.
thisChartSeriesList.Add(thisChartSeries);
} // End while
} // End using
}
catch (Exception e)
{
MessageBox.Show("Encountered error when retrieving Chart Series data for KPI [" +
pKPI + "] -- Error: " + e.Message.ToString());
thisChartSeriesList = null;
Application.Exit();
} // End Try/Catch
// -------------------------------------------------------------------------------------
return thisChartSeriesList;
}
public List<SeriesElement> GetListOfSeriesElements(string pKPI, string pSeries, string pConnxnString)
{
List<SeriesElement> SeriesElementList = new List<SeriesElement>();
SeriesElement thisSeriesElement = new SeriesElement();
SqlConnection sqlConnection;
Int32 iWork;
Double dWork;
Boolean ValueIsaNumber;
try
{
// ==========================================
// Just some sample settings to put some real data in here for one series.
thisSeriesElement.Name = "DX";
thisSeriesElement.Value = 45.79;
thisSeriesElement.Color = "Red";
thisSeriesElement.altName = "DeXe";
// Add the Series Element to the list of elements to be returned by this method.
SeriesElementList.Add(thisSeriesElement);
using(sqlConnection = new SqlConnection())
{
var cmd = new SqlCommand()
{
CommandType = System.Data.CommandType.StoredProcedure,
Connection = sqlConnection,
CommandText = "p_VMS_TTL_Get_ElementsForSeries"
};
cmd.CommandTimeout = 300;
cmd.Parameters.Add(new SqlParameter("@KPI", SqlDbType.VarChar) { Value = pKPI });
cmd.Parameters.Add(new SqlParameter("@Series", SqlDbType.VarChar) { Value = pSeries });
sqlConnection.Open();
var dRdr = cmd.ExecuteReader();
// There should be more than one row of data elements returned here.
while (dRdr.Read())
{
thisSeriesElement.Name = dRdr["Series"].ToString();
// -------------------------------------------------------------------------
// Assuring that the series element Value is numeric.
ValueIsaNumber = Double.TryParse(dRdr["ElementValue"].ToString(), out dWork);
if (ValueIsaNumber)
thisSeriesElement.Value = dWork;
else
{
MessageBox.Show("Could not convert the Value for Series Element [" +
thisSeriesElement.Name + "] to a valid integer! Zero substituted.");
thisSeriesElement.Value = 0;
}
// -------------------------------------------------------------------------
// Add the Series Element to the list of elements to be returned by this method.
SeriesElementList.Add(thisSeriesElement);
} // End while
} // End using
}
catch (Exception e)
{
MessageBox.Show("Encountered error when retrieving Series Element data for KPI [" +
pKPI + "] -- Error: " + e.Message.ToString());
SeriesElementList = null;
Application.Exit();
} // End Try/Catch
// -------------------------------------------------------------------------------------
return SeriesElementList;
}
