public List <ClientUsageLinesTbl> GetAllClientUsageLinesTbl(long pCustomerID, string SortBy)
{
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_SELECT + " WHERE CustomerID = " + pCustomerID.ToString();
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
TrackerDb _TrackerDB = new TrackerDb();
OleDbDataReader _DataReader = _TrackerDB.ReturnDataReader(_sqlCmd);
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["LineDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LineDate"]);
_DataItem.CupCount = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Qty = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
}
return(_DataItems);
}
public List <ClientUsageLinesTbl> GetLast10UsageLines(long pCustomerID, int pServiceTypeId)
{
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = "SELECT TOP 10 ClientUsageLineNo, CustomerID, [Date] AS LineDate, CupCount, ServiceTypeID " +
"FROM ClientUsageLinesTbl WHERE ClientUsageLinesTbl.CustomerID = ? ";
if (pServiceTypeId > 0)
{
_sqlCmd += " AND ClientUsageLinesTbl.ServiceTypeID = ?";
}
_sqlCmd += " ORDER BY ClientUsageLinesTbl.Date DESC";
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
if (pServiceTypeId > 0)
{
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeId
});
}
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["LineDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LineDate"]);
_DataItem.CupCount = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
//_DataItem.Qty = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
//_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_DataItems.Clear();
}
finally
{
_conn.Close();
}
}
return(_DataItems);
}
/// <summary>
/// Return the latest data for the customer for a service type, or 0 for last item
/// </summary>
/// <param name="pCustomerID">the custoerm id</param>
/// <param name="pServiceTypeID">default 0, means any service type otherwise pass the type.</param>
/// <returns>data from that line</returns>
public ClientUsageLinesTbl GetLatestUsageData(long pCustomerID, int pServiceTypeID)
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = "SELECT TOP 1 ClientUsageLineNo, CustomerID, [Date] As LineDate, CupCount, ServiceTypeID, Qty " +
" FROM ClientUsageLinesTbl WHERE CustomerID = ? ";
if (pServiceTypeID > 0)
{
_sqlCmd += "AND ServiceTypeID = ?";
}
// now make sure it is the last one
_sqlCmd += " ORDER BY [Date] DESC";
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
if (pServiceTypeID > 0)
{
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeID
});
}
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read() && (_DataReader[0] != null))
{
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["LineDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LineDate"]);
_DataItem.CupCount = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Qty = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_DataItem);
}
// real routine
public List <ClientUsageLinesTbl> GetAllCustomerServiceLines(long pCustomerID, int pServiceTypeID, string pSortBy)
{
string CONST_SQL_SELECT = "SELECT ClientUsageLineNo, CustomerID, Date, CupCount, ServiceTypeID, Qty, Notes "
+ " FROM ClientUsageLinesTbl"
+ " WHERE ClientUsageLinesTbl.CustomerID = ?" // + pCustomerID
+ " AND ClientUsageLinesTbl.ServiceTypeID = ?";
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(pSortBy))
{
_sqlCmd += " ORDER BY " + pSortBy; // Add order by string
}
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeID
});
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["Date"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["Date"]);
_DataItem.CupCount = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Qty = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_DataItems.Clear();
}
finally
{
_conn.Close();
}
}
return(_DataItems);
}
public bool UpdateItemsUsed(ClientUsageLinesTbl pClientUsageLine, long OriginalClientUsageLineNo)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[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
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.CustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.LineDate
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.CupCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.ServiceTypeID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.Qty
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.Notes
});
// " WHERE ClientUsageLineNo = ?)";
_cmd.Parameters.Add(new OleDbParameter {
Value = OriginalClientUsageLineNo
});
#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);
}
}