Example #1
0
/*
 *    " 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);
        }
Example #2
0
        public CustomersWithDatesAndUsageTbl GetCustomerWithDatesAndUsage(long pCustomerID)
        {
            CustomersWithDatesAndUsageTbl _DataItem = null;

            TrackerDb _TDB = new classes.TrackerDb();

            _TDB.AddWhereParams(pCustomerID, DbType.Int64);

//      OleDbDataReader _DataReader = _TrackerDB.ReturnDataReader(CONST_SQL_CUSTOMERSUSAGE_SELECT, _WhereParameters);
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_CUSTOMERSUSAGE_SELECT);

            if (_DataReader != null)
            {
                if (_DataReader.Read())
                {
                    _DataItem = new CustomersWithDatesAndUsageTbl();

                    _DataItem.Customer.CustomerID          = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
                    _DataItem.Customer.CompanyName         = (_DataReader["CompanyName"] == DBNull.Value) ? String.Empty : _DataReader["CompanyName"].ToString();
                    _DataItem.Customer.ContactTitle        = (_DataReader["ContactTitle"] == DBNull.Value) ? String.Empty : _DataReader["ContactTitle"].ToString();
                    _DataItem.Customer.ContactFirstName    = (_DataReader["ContactFirstName"] == DBNull.Value) ? String.Empty : _DataReader["ContactFirstName"].ToString();
                    _DataItem.Customer.ContactLastName     = (_DataReader["ContactLastName"] == DBNull.Value) ? String.Empty : _DataReader["ContactLastName"].ToString();
                    _DataItem.Customer.ContactAltFirstName = (_DataReader["ContactAltFirstName"] == DBNull.Value) ? String.Empty : _DataReader["ContactAltFirstName"].ToString();
                    _DataItem.Customer.ContactAltLastName  = (_DataReader["ContactAltLastName"] == DBNull.Value) ? String.Empty : _DataReader["ContactAltLastName"].ToString();
                    _DataItem.Customer.Department          = (_DataReader["Department"] == DBNull.Value) ? String.Empty : _DataReader["Department"].ToString();
                    _DataItem.Customer.BillingAddress      = (_DataReader["BillingAddress"] == DBNull.Value) ? String.Empty : _DataReader["BillingAddress"].ToString();
                    _DataItem.Customer.City                     = (_DataReader["City"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["City"]);
                    _DataItem.Customer.Province                 = (_DataReader["Province"] == DBNull.Value) ? String.Empty : _DataReader["Province"].ToString();
                    _DataItem.Customer.PostalCode               = (_DataReader["PostalCode"] == DBNull.Value) ? String.Empty : _DataReader["PostalCode"].ToString();
                    _DataItem.Customer.Region                   = (_DataReader["Region"] == DBNull.Value) ? String.Empty : _DataReader["Region"].ToString();
                    _DataItem.Customer.PhoneNumber              = (_DataReader["PhoneNumber"] == DBNull.Value) ? String.Empty : _DataReader["PhoneNumber"].ToString();
                    _DataItem.Customer.Extension                = (_DataReader["Extension"] == DBNull.Value) ? String.Empty : _DataReader["Extension"].ToString();
                    _DataItem.Customer.FaxNumber                = (_DataReader["FaxNumber"] == DBNull.Value) ? String.Empty : _DataReader["FaxNumber"].ToString();
                    _DataItem.Customer.CellNumber               = (_DataReader["CellNumber"] == DBNull.Value) ? String.Empty : _DataReader["CellNumber"].ToString();
                    _DataItem.Customer.EmailAddress             = (_DataReader["EmailAddress"] == DBNull.Value) ? String.Empty : _DataReader["EmailAddress"].ToString();
                    _DataItem.Customer.AltEmailAddress          = (_DataReader["AltEmailAddress"] == DBNull.Value) ? String.Empty : _DataReader["AltEmailAddress"].ToString();
                    _DataItem.Customer.ContractNo               = (_DataReader["ContractNo"] == DBNull.Value) ? String.Empty : _DataReader["ContractNo"].ToString();
                    _DataItem.Customer.CustomerTypeID           = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
                    _DataItem.Customer.EquipType                = (_DataReader["EquipType"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["EquipType"]);
                    _DataItem.Customer.CoffeePreference         = (_DataReader["CoffeePreference"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CoffeePreference"]);
                    _DataItem.Customer.PriPrefQty               = (_DataReader["PriPrefQty"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["PriPrefQty"]);
                    _DataItem.Customer.PrefPrepTypeID           = (_DataReader["PrefPrepTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PrefPrepTypeID"]);
                    _DataItem.Customer.PrefPackagingID          = (_DataReader["PrefPackagingID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PrefPackagingID"]);
                    _DataItem.Customer.SecondaryPreference      = (_DataReader["SecondaryPreference"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SecondaryPreference"]);
                    _DataItem.Customer.SecPrefQty               = (_DataReader["SecPrefQty"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["SecPrefQty"]);
                    _DataItem.Customer.TypicallySecToo          = (_DataReader["TypicallySecToo"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TypicallySecToo"]);
                    _DataItem.Customer.PreferedAgent            = (_DataReader["PreferedAgent"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PreferedAgent"]);
                    _DataItem.Customer.SalesAgentID             = (_DataReader["SalesAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SalesAgentID"]);
                    _DataItem.Customer.MachineSN                = (_DataReader["MachineSN"] == DBNull.Value) ? String.Empty : _DataReader["MachineSN"].ToString();
                    _DataItem.Customer.UsesFilter               = (_DataReader["UsesFilter"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["UsesFilter"]);
                    _DataItem.Customer.autofulfill              = (_DataReader["autofulfill"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["autofulfill"]);
                    _DataItem.Customer.enabled                  = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);
                    _DataItem.Customer.AlwaysSendChkUp          = (_DataReader["AlwaysSendChkUp"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["AlwaysSendChkUp"]);
                    _DataItem.Customer.NormallyResponds         = (_DataReader["NormallyResponds"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["NormallyResponds"]);
                    _DataItem.Customer.ReminderCount            = (_DataReader["xxx"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["xxx"]);
                    _DataItem.Customer.LastDateSentReminder     = (_DataReader["LastDateSentReminder"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["LastDateSentReminder"]);
                    _DataItem.Customer.Notes                    = (_DataReader["Notes"] == DBNull.Value) ? String.Empty : _DataReader["Notes"].ToString();
                    _DataItem.NextRoastDateByCity.CityID        = (_DataReader["CityID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityID"]);
                    _DataItem.NextRoastDateByCity.DeliveryDate  = (_DataReader["DeliveryDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["DeliveryDate"]).Date;
                    _DataItem.NextRoastDateByCity.PrepDate      = (_DataReader["PreperationDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["PreperationDate"]).Date;
                    _DataItem.NextRoastDateByCity.DeliveryOrder = (_DataReader["DeliveryOrder"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["DeliveryOrder"]);
                    _DataItem.ClientUsage.LastCupCount          = (_DataReader["LastCupCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastCupCount"]);
                    _DataItem.ClientUsage.NextCoffeeBy          = (_DataReader["NextCoffeeBy"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextCoffeeBy"]).Date;
                    _DataItem.ClientUsage.NextCleanOn           = (_DataReader["NextCleanOn"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextCleanOn"]).Date;
                    _DataItem.ClientUsage.NextFilterEst         = (_DataReader["NextFilterEst "] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextFilterEst"]).Date;
                    _DataItem.ClientUsage.NextDescaleEst        = (_DataReader["NextDescaleEst"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextDescaleEst"]).Date;
                    _DataItem.ClientUsage.NextServiceEst        = (_DataReader["NextServiceEst"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["NextServiceEst"]).Date;
                    _DataItem.ClientUsage.DailyConsumption      = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
                    _DataItem.ClientUsage.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();
            }
            _TDB.Close();

            return(_DataItem);
        }
Example #3
0
/*
 * "SELECT CustomersTbl.CustomerID AS ContactID, CustomersTbl.CompanyName, CustomersTbl.ContactTitle, CustomersTbl.ContactFirstName, " +
 *           "CustomersTbl.ContactLastName, CustomersTbl.ContactAltFirstName, CustomersTbl.ContactAltLastName, CustomersTbl.Department, " +
 *           "CustomersTbl.BillingAddress, CustomersTbl.City, CustomersTbl.PostalCode, CustomersTbl.PreferedAgent, CustomersTbl.SalesAgentID, " +
 *           "CustomersTbl.PhoneNumber, CustomersTbl.Extension, CustomersTbl.FaxNumber, CustomersTbl.CellNumber, CustomersTbl.EmailAddress,  " +
 *           "CustomersTbl.AltEmailAddress, CustomersTbl.UsesFilter, CustomersTbl.EquipType, CustomersTbl.CustomerTypeID, CustomersTbl.TypicallySecToo," +
 *           "CustomersTbl.PriPrefQty, CustomersTbl.SecPrefQty, CustomersTbl.ReminderCount, " +
 *           "CustomersTbl.autofulfill, CustomersTbl.AlwaysSendChkUp, CustomersTbl.enabled, CustomersTbl.Notes, " +
 *           "ClientUsageTbl.LastCupCount, ClientUsageTbl.NextCoffeeBy, ClientUsageTbl.NextCleanOn, ClientUsageTbl.NextFilterEst, ClientUsageTbl.NextDescaleEst, "+
 *           "ClientUsageTbl.NextServiceEst, ClientUsageTbl.DailyConsumption,  " +
 *           "NextRoastDateByCityTbl.PreperationDate, NextRoastDateByCityTbl.DeliveryDate, NextRoastDateByCityTbl.NextPreperationDate, NextRoastDateByCityTbl.NextDeliveryDate " +
 *      "FROM ((CustomersTbl INNER JOIN ClientUsageTbl ON CustomersTbl.CustomerID = ClientUsageTbl.CustomerId) " +
 *            " LEFT JOIN NextRoastDateByCityTbl ON CustomersTbl.City = NextRoastDateByCityTbl.CityID) " +
 *            " LEFT JOIN ItemNoStockItemQry ON CustomersTbl.CoffeePreference = ItemNoStockItemQry.ItemTypeID  " +
 *      " WHERE ((LastDateSentReminder IS Null) OR (LastDateSentReminder <> ?)) AND (CustomersTbl.enabled=True)  " +
 *            " AND (CustomersTbl.PredictionDisabled=False) " +
 *            " AND ((ClientUsageTbl.NextCoffeeBy < ?) OR " +
 *                  "(NextRoastDateByCityTbl.NextDeliveryDate<=DateAdd('d', 8, ClientUsageTbl.NextCoffeeBy)) OR " +
 *                  "CustomersTbl.AlwaysSendChkUp=True) " +
 *            " AND (NOT Exists  (SELECT  OrdersTbl.CustomerId FROM OrdersTbl  " +
 *                 " WHERE (OrdersTbl.CustomerId=CustomersTbl.CustomerId) AND (OrdersTbl.RoastDate>=Date() AND  " +
 *                         " OrdersTbl.RoastDate<=DateAdd('d',9,Date()))	 )) " +
 *       " ORDER BY CustomersTbl.CompanyName";
 */
//              "                                                     (OrdersTbl.RoastDate<=DateAdd('d',10,NextRoastDateByCityTbl.NextDeliveryDate)) ))))" +

//              "                                                    ((OrdersTbl.RoastDate>DateAdd('ww',-1,NextRoastDateByCityTbl.PreperationDate)) AND " +
//              "                                                     (OrdersTbl.RoastDate<=DateAdd('d',3,NextRoastDateByCityTbl.DeliveryDate)) ))))" +


        #endregion

        public List <ContactsThayMayNeedData> GetContactsThatMayNeedNextWeek()
        {
            List <ContactsThayMayNeedData> _DataItems = new List <ContactsThayMayNeedData>();
            TrackerDb _TDB = new classes.TrackerDb();

            _TDB.AddWhereParams(System.DateTime.Now.Date, System.Data.DbType.Date);
            SysDataTbl _SysData = new SysDataTbl();

            _TDB.AddWhereParams(_SysData.GetMinReminderDate().Date, DbType.Date);

/* this looks wrong the nextcoffee by should be date + 9
 *    _TDB.AddWhereParams(System.DateTime.Now.Date.AddDays(9), DbType.Date);
 */

            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SELECT_CONTACTSTHATMAYNEEDNEXTWEEK);

            if (_DataReader != null)
            {
                while (_DataReader.Read())
                {
                    ContactsThayMayNeedData _DataItem = new ContactsThayMayNeedData();
                    #region GetDataFromQuery
                    // first Customer Contact DATA
                    _DataItem.CustomerData.CustomerID          = (_DataReader["ContactID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["ContactID"]);
                    _DataItem.CustomerData.CompanyName         = (_DataReader["CompanyName"] == DBNull.Value) ? string.Empty : _DataReader["CompanyName"].ToString();
                    _DataItem.CustomerData.ContactTitle        = (_DataReader["ContactTitle"] == DBNull.Value) ? string.Empty : _DataReader["ContactTitle"].ToString();
                    _DataItem.CustomerData.ContactFirstName    = (_DataReader["ContactFirstName"] == DBNull.Value) ? string.Empty : _DataReader["ContactFirstName"].ToString();
                    _DataItem.CustomerData.ContactLastName     = (_DataReader["ContactLastName"] == DBNull.Value) ? string.Empty : _DataReader["ContactLastName"].ToString();
                    _DataItem.CustomerData.ContactAltFirstName = (_DataReader["ContactAltFirstName"] == DBNull.Value) ? string.Empty : _DataReader["ContactAltFirstName"].ToString();
                    _DataItem.CustomerData.ContactAltLastName  = (_DataReader["ContactAltLastName"] == DBNull.Value) ? string.Empty : _DataReader["ContactAltLastName"].ToString();
                    _DataItem.CustomerData.Department          = (_DataReader["Department"] == DBNull.Value) ? string.Empty : _DataReader["Department"].ToString();
                    _DataItem.CustomerData.BillingAddress      = (_DataReader["BillingAddress"] == DBNull.Value) ? string.Empty : _DataReader["BillingAddress"].ToString();
                    _DataItem.CustomerData.City            = (_DataReader["City"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["City"]);
                    _DataItem.CustomerData.PostalCode      = (_DataReader["PostalCode"] == DBNull.Value) ? string.Empty : _DataReader["PostalCode"].ToString();
                    _DataItem.CustomerData.PreferedAgent   = (_DataReader["PreferedAgent"] == DBNull.Value) ? TrackerTools.CONST_DEFAULT_DELIVERYBYID : Convert.ToInt32(_DataReader["PreferedAgent"]);
                    _DataItem.CustomerData.SalesAgentID    = (_DataReader["SalesAgentID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SalesAgentID"]);
                    _DataItem.CustomerData.PhoneNumber     = (_DataReader["PhoneNumber"] == DBNull.Value) ? string.Empty : _DataReader["PhoneNumber"].ToString();
                    _DataItem.CustomerData.Extension       = (_DataReader["Extension"] == DBNull.Value) ? string.Empty : _DataReader["Extension"].ToString();
                    _DataItem.CustomerData.FaxNumber       = (_DataReader["FaxNumber"] == DBNull.Value) ? string.Empty : _DataReader["FaxNumber"].ToString();
                    _DataItem.CustomerData.CellNumber      = (_DataReader["CellNumber"] == DBNull.Value) ? string.Empty : _DataReader["CellNumber"].ToString();
                    _DataItem.CustomerData.EmailAddress    = (_DataReader["EmailAddress"] == DBNull.Value) ? string.Empty : _DataReader["EmailAddress"].ToString();
                    _DataItem.CustomerData.AltEmailAddress = (_DataReader["AltEmailAddress"] == DBNull.Value) ? string.Empty : _DataReader["AltEmailAddress"].ToString();
                    _DataItem.CustomerData.EquipType       = (_DataReader["EquipType"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["EquipType"]);
                    _DataItem.CustomerData.CustomerTypeID  = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
                    _DataItem.CustomerData.PriPrefQty      = (_DataReader["PriPrefQty"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["PriPrefQty"]);
                    _DataItem.CustomerData.SecPrefQty      = (_DataReader["SecPrefQty"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["SecPrefQty"]);
                    _DataItem.CustomerData.ReminderCount   = (_DataReader["ReminderCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReminderCount"]);
                    _DataItem.CustomerData.autofulfill     = (_DataReader["autofulfill"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["autofulfill"]);
                    _DataItem.CustomerData.UsesFilter      = (_DataReader["UsesFilter"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["UsesFilter"]);
                    _DataItem.CustomerData.TypicallySecToo = (_DataReader["TypicallySecToo"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TypicallySecToo"]);
                    _DataItem.CustomerData.AlwaysSendChkUp = (_DataReader["AlwaysSendChkUp"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["AlwaysSendChkUp"]);
                    _DataItem.CustomerData.enabled         = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);
                    _DataItem.CustomerData.Notes           = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                    _DataItem.RequiresPurchOrder           = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
                    // now Customer usage DATA
                    _DataItem.ClientUsageData.LastCupCount     = (_DataReader["LastCupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["LastCupCount"]);
                    _DataItem.ClientUsageData.NextCoffeeBy     = (_DataReader["NextCoffeeBy"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextCoffeeBy"]).Date;
                    _DataItem.ClientUsageData.NextCleanOn      = (_DataReader["NextCleanOn"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextCleanOn"]).Date;
                    _DataItem.ClientUsageData.NextFilterEst    = (_DataReader["NextFilterEst"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextFilterEst"]).Date;
                    _DataItem.ClientUsageData.NextDescaleEst   = (_DataReader["NextDescaleEst"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextDescaleEst"]).Date;
                    _DataItem.ClientUsageData.NextServiceEst   = (_DataReader["NextServiceEst"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextServiceEst"]).Date;
                    _DataItem.ClientUsageData.DailyConsumption = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
                    // now next date
                    _DataItem.NextRoastDateByCityData.PrepDate         = (_DataReader["PreperationDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["PreperationDate"]).Date;
                    _DataItem.NextRoastDateByCityData.DeliveryDate     = (_DataReader["DeliveryDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DeliveryDate"]).Date;
                    _DataItem.NextRoastDateByCityData.NextPrepDate     = (_DataReader["NextPreperationDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextPreperationDate"]).Date;
                    _DataItem.NextRoastDateByCityData.NextDeliveryDate = (_DataReader["NextDeliveryDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextDeliveryDate"]).Date;
                    #endregion

                    _DataItems.Add(_DataItem);
                }
                _DataReader.Dispose();
            }
            _TDB.Close();
            return(_DataItems);
        }