public ClientUsageTbl GetUsageData(long pCustomerID)
{
ClientUsageTbl _DataItem = new ClientUsageTbl();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
try
{
OleDbCommand _cmd = new OleDbCommand(CONST_SQL_SELECT, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read())
{
_DataItem.CustomerID = pCustomerID;
_DataItem.LastCupCount = (_DataReader["LastCupCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastCupCount"]);
_DataItem.NextCoffeeBy = (_DataReader["NextCoffeeBy"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextCoffeeBy"]);
_DataItem.NextCleanOn = (_DataReader["NextCleanOn"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextCleanOn"]);
_DataItem.NextFilterEst = (_DataReader["NextFilterEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextFilterEst"]);
_DataItem.NextDescaleEst = (_DataReader["NextDescaleEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextDescaleEst"]);
_DataItem.NextServiceEst = (_DataReader["NextServiceEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextServiceEst"]);
_DataItem.DailyConsumption = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
_DataItem.FilterAveCount = (_DataReader["FilterAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["FilterAveCount"]);
_DataItem.DescaleAveCount = (_DataReader["DescaleAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["DescaleAveCount"]);
_DataItem.ServiceAveCount = (_DataReader["ServiceAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["ServiceAveCount"]);
_DataItem.CleanAveCount = (_DataReader["CleanAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["CleanAveCount"]);
}
}
catch (Exception _ex)
{
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_DataItem);
}
/// <summary>
/// Update the Customer Usage (ID passed as part of ClientUsage data) to reflect new values
/// </summary>
/// <param name="pClientUsage">Client Usage data for ClientUsage.CustomerID</param>
/// <returns>blank or error strin</returns>
public bool Update(ClientUsageTbl pClientUsage)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_UPDATE;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// Add data sent LastCupCount, NextCoffeeBy, NextCleanOn, NextFilterEst, NextDescaleEst, NextServiceEst,
// DailyConsumption, CleanAveCount, FilterAveCount, DescaleAveCount, ServiceAveCount
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.LastCupCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.NextCoffeeBy
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.NextCleanOn
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.NextFilterEst
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.NextDescaleEst
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.DailyConsumption
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.CleanAveCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.FilterAveCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.DescaleAveCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.ServiceAveCount
});
// WHERE CustomerID = ?
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsage.CustomerID
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() >= 0);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
}
finally
{
_conn.Close();
}
return(_Success);
}
}