/*
* " SELECT CustomersTbl.CustomerID, CustomersTbl.CompanyName, CustomersTbl.ContactTitle, CustomersTbl.ContactFirstName, " +
* " CustomersTbl.ContactAltFirstName, CustomersTbl.PostalCode, CustomersTbl.EmailAddress, CustomersTbl.AltEmailAddress, CustomersTbl.CustomerTypeID, " +
* " CustomersTbl.PriPrefQty, CustomersTbl.TypicallySecToo, CustomersTbl.PreferedAgent, CustomersTbl.SalesAgentID, " +
* " CustomersTbl.UsesFilter, CustomersTbl.autofulfill, CustomersTbl.enabled, CustomersTbl.PredictionDisabled, " +
* " CustomersTbl.AlwaysSendChkUp, CustomersTbl.ReminderCount, CustomersTbl.LastDateSentReminder, " +
* " NextRoastDateByCityTbl.CityID, NextRoastDateByCityTbl.DeliveryDate, NextRoastDateByCityTbl.PreperationDate, " +
* " ClientUsageTbl.NextCoffeeBy, ClientUsageTbl.NextCleanOn, ClientUsageTbl.NextFilterEst, ClientUsageTbl.NextDescaleEst, ClientUsageTbl.NextServiceEst " +
* " FROM ((ClientUsageTbl RIGHT OUTER JOIN CustomersTbl ON ClientUsageTbl.CustomerId = CustomersTbl.CustomerID) LEFT OUTER JOIN " +
* " NextRoastDateByCityTbl ON CustomersTbl.City = NextRoastDateByCityTbl.CityID) " +
* " WHERE (CustomersTbl.CustomerID = ?)";
*/
public ContactToRemindWithItems GetCustomerDetails(long pCustomerID)
{
ContactToRemindWithItems _DataItem = null;
TrackerDb _TrackerDB = new classes.TrackerDb();
_TrackerDB.AddWhereParams(pCustomerID, DbType.Int64);
IDataReader _DataReader = _TrackerDB.ExecuteSQLGetDataReader(CONST_SQL_CUSTOMERSUSAGE_SELECT);
if (_DataReader != null)
{
if (_DataReader.Read())
{
_DataItem = new ContactToRemindWithItems();
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.CompanyName = (_DataReader["CompanyName"] == DBNull.Value) ? String.Empty : _DataReader["CompanyName"].ToString();
_DataItem.ContactTitle = (_DataReader["ContactTitle"] == DBNull.Value) ? String.Empty : _DataReader["ContactTitle"].ToString();
_DataItem.ContactFirstName = (_DataReader["ContactFirstName"] == DBNull.Value) ? String.Empty : _DataReader["ContactFirstName"].ToString();
_DataItem.ContactAltFirstName = (_DataReader["ContactAltFirstName"] == DBNull.Value) ? String.Empty : _DataReader["ContactAltFirstName"].ToString();
_DataItem.EmailAddress = (_DataReader["EmailAddress"] == DBNull.Value) ? String.Empty : _DataReader["EmailAddress"].ToString();
_DataItem.AltEmailAddress = (_DataReader["AltEmailAddress"] == DBNull.Value) ? String.Empty : _DataReader["AltEmailAddress"].ToString();
_DataItem.CustomerTypeID = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
_DataItem.TypicallySecToo = (_DataReader["TypicallySecToo"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TypicallySecToo"]);
_DataItem.PreferedAgentID = (_DataReader["PreferedAgent"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PreferedAgent"]);
_DataItem.SalesAgentID = (_DataReader["SalesAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SalesAgentID"]);
_DataItem.UsesFilter = (_DataReader["UsesFilter"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["UsesFilter"]);
_DataItem.autofulfill = (_DataReader["autofulfill"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["autofulfill"]);
_DataItem.enabled = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);
_DataItem.AlwaysSendChkUp = (_DataReader["AlwaysSendChkUp"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["AlwaysSendChkUp"]);
_DataItem.ReminderCount = (_DataReader["ReminderCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReminderCount"]);
_DataItem.LastDateSentReminder = (_DataReader["LastDateSentReminder"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["LastDateSentReminder"]).Date;
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.CityID = (_DataReader["CityID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityID"]);
_DataItem.NextDeliveryDate = (_DataReader["DeliveryDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["DeliveryDate"]).Date;
_DataItem.NextPrepDate = (_DataReader["PreperationDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["PreperationDate"]).Date;
// _DataItem.LastCupCount = (_DataReader["LastCupCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastCupCount"]);
_DataItem.NextCoffee = (_DataReader["NextCoffeeBy"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextCoffeeBy"]).Date;
_DataItem.NextClean = (_DataReader["NextCleanOn"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextCleanOn"]).Date;
_DataItem.NextFilter = (_DataReader["NextFilterEst"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextFilterEst"]).Date;
_DataItem.NextDescal = (_DataReader["NextDescaleEst"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextDescaleEst"]).Date;
_DataItem.NextService = (_DataReader["NextServiceEst"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextServiceEst"]).Date;
// _DataItem.DailyConsumption = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
// _DataItem.FilterAveCount = (_DataReader["FilterAveCount"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["FilterAveCount"]);
// _DataItem.ClientUsage.DescaleAveCount = (_DataReader["DescaleAveCount"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["DescaleAveCount"]);
// _DataItem.ClientUsage.ServiceAveCount = (_DataReader["ServiceAveCount"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["ServiceAveCount"]);
// _DataItem.ClientUsage.CleanAveCount = (_DataReader["CleanAveCount"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["CleanAveCount"]);
}
_DataReader.Close();
}
_TrackerDB.Close();
return(_DataItem);
}
public List <ContactToRemindWithItems> GetAllContactAndItems(string SortBy)
{
List <ContactToRemindWithItems> _DataItems = new List <ContactToRemindWithItems>();
string _sqlCmd = CONST_SQL_SELECTALLCONTACTS;
_sqlCmd += (!String.IsNullOrEmpty(SortBy)) ? " ORDER BY " + SortBy : " ORDER BY CompanyName"; // add default order
TrackerDb _TDB = new TrackerDb();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
ContactToRemindWithItems _DataItem = new ContactToRemindWithItems();
_DataItem.TCCID = (_DataReader["TCCID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["TCCID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.CompanyName = (_DataReader["CompanyName"] == DBNull.Value) ? string.Empty : _DataReader["CompanyName"].ToString();
_DataItem.ContactFirstName = (_DataReader["ContactFirstName"] == DBNull.Value) ? string.Empty : _DataReader["ContactFirstName"].ToString();
_DataItem.ContactAltFirstName = (_DataReader["ContactAltFirstName"] == DBNull.Value) ? string.Empty : _DataReader["ContactAltFirstName"].ToString();
_DataItem.CityID = (_DataReader["CityID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityID"]);
_DataItem.EmailAddress = (_DataReader["EmailAddress"] == DBNull.Value) ? string.Empty : _DataReader["EmailAddress"].ToString();
_DataItem.AltEmailAddress = (_DataReader["AltEmailAddress"] == DBNull.Value) ? string.Empty : _DataReader["AltEmailAddress"].ToString();
_DataItem.CustomerTypeID = (_DataReader["CityID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityID"]);
_DataItem.EquipTypeID = (_DataReader["EquipTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["EquipTypeID"]);
_DataItem.TypicallySecToo = (_DataReader["TypicallySecToo"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TypicallySecToo"]);
_DataItem.PreferedAgentID = (_DataReader["PreferedAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PreferedAgentID"]);
_DataItem.SalesAgentID = (_DataReader["SalesAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SalesAgentID"]);
_DataItem.UsesFilter = (_DataReader["UsesFilter"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["UsesFilter"]);
_DataItem.enabled = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);
_DataItem.AlwaysSendChkUp = (_DataReader["AlwaysSendChkUp"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["AlwaysSendChkUp"]);
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.ReminderCount = (_DataReader["ReminderCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReminderCount"]);
_DataItem.NextPrepDate = (_DataReader["NextPrepDate"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextPrepDate"]).Date;
_DataItem.NextDeliveryDate = (_DataReader["NextDeliveryDate"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextDeliveryDate"]).Date;
_DataItem.NextCoffee = (_DataReader["NextCoffee"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextCoffee"]).Date;
_DataItem.NextClean = (_DataReader["NextClean"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextClean"]).Date;
_DataItem.NextFilter = (_DataReader["NextFilter"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextFilter"]).Date;
_DataItem.NextDescal = (_DataReader["NextDescal"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextDescal"]).Date;
_DataItem.NextService = (_DataReader["NextService"] == DBNull.Value) ? DateTime.MaxValue : Convert.ToDateTime(_DataReader["NextService"]).Date;
// now add the items for the customer
_DataItem.ItemsContactRequires = GetAllContactItems(_DataItem.CustomerID, "");
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
