public List <CompanyNames> GetAllDemo()
{
List <CompanyNames> _CompanyNames = new List <CompanyNames>();
TrackerDb _TDB = new TrackerDb();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTDEMOS);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CompanyNames _Company = new CompanyNames();
_Company.CustomerID = Convert.ToInt64(_DataReader["CustomerID"]);
_Company.CompanyName = (_DataReader["CompanyName"] == DBNull.Value) ? "" : _DataReader["CompanyName"].ToString();
_Company.enabled = (_DataReader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["enabled"]);
_CompanyNames.Add(_Company);
}
_DataReader.Close();
}
_TDB.Close();
return(_CompanyNames);
}
public List <UsedItemGroupTbl> GetAll(string SortBy)
{
List <UsedItemGroupTbl> _DataItems = new List <UsedItemGroupTbl>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
UsedItemGroupTbl _DataItem = new UsedItemGroupTbl();
#region StoreThisDataItem
_DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UsedItemGroupID"]);
_DataItem.ContactID = (_DataReader["ContactID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["ContactID"]);
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItem.LastItemTypeID = (_DataReader["LastItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeID"]);
_DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]);
_DataItem.LastItemDateChanged = (_DataReader["LastItemDateChanged"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastItemDateChanged"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
/*
* string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
*
* using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
* {
* string _sqlCmd = CONST_SQL_SELECT;
* if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy; // Add order by string
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the query we have built
* _conn.Open();
* OleDbDataReader _DataReader = _cmd.ExecuteReader();
* while (_DataReader.Read())
* {
* CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();
*
* _DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
* _DataItem.CustomerTypeID = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
* _DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
* _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
* _DataItems.Add(_DataItem);
* }
* }
* return _DataItems;
* }
*/
public List <CustomerTrackedServiceItemsData> GetAllByCustomerTypeID(int pCustomerTypeID)
{
List <CustomerTrackedServiceItemsData> _DataItems = new List <CustomerTrackedServiceItemsData>();
TrackerDb _TDB = new TrackerDb();
_TDB.AddWhereParams(pCustomerTypeID, DbType.Int32, "@CustomerTypeID");
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECT_FORCUSTOMERTYPE);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();
_DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
_DataItem.CustomerTypeID = pCustomerTypeID;
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public List <CustomerTrackedServiceItemsData> GetAll(string SortBy)
{
List <CustomerTrackedServiceItemsData> _DataItems = new List <CustomerTrackedServiceItemsData>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CustomerTrackedServiceItemsData _DataItem = new CustomerTrackedServiceItemsData();
_DataItem.CustomerTrackedServiceItemsID = (_DataReader["CustomerTrackedServiceItemsID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTrackedServiceItemsID"]);
_DataItem.CustomerTypeID = (_DataReader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerTypeID"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public List <LogTbl> GetAll(string SortBy)
{
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
_sqlCmd += (!String.IsNullOrEmpty(SortBy)) ? " ORDER BY " + SortBy : " ORDER BY DateAdded"; // add default order\
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
List <LogTbl> _DataItems = new List <LogTbl>();
if (_DataReader != null)
{
while (_DataReader.Read())
{
LogTbl _DataItem = new LogTbl();
_DataItem.LogID = (_DataReader["LogID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LogID"]);
_DataItem.DateAdded = (_DataReader["DateAdded"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(_DataReader["DateAdded"]).Date;
_DataItem.UserID = (_DataReader["UserID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UserID"]);
_DataItem.SectionID = (_DataReader["SectionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SectionID"]);
_DataItem.TranactionTypeID = (_DataReader["TranactionTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["TranactionTypeID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.Details = (_DataReader["Details"] == DBNull.Value) ? string.Empty : _DataReader["Details"].ToString();
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public List <SentRemindersLogTbl> GetAllByDate(DateTime pDateSent, string SortBy)
{
List <SentRemindersLogTbl> _DataItems = new List <SentRemindersLogTbl>();
TrackerDb _TrackerDb = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT_BYDATESENT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
_TrackerDb.AddWhereParams(pDateSent.Date, DbType.Date);
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
SentRemindersLogTbl _DataItem = new SentRemindersLogTbl();
_DataItem.ReminderID = (_DataReader["ReminderID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReminderID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.DateSentReminder = (_DataReader["DateSentReminder"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateSentReminder"]).Date;
_DataItem.NextPrepDate = (_DataReader["NextPrepDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["NextPrepDate"]).Date;
_DataItem.ReminderSent = (_DataReader["ReminderSent"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["ReminderSent"]);
_DataItem.HadAutoFulfilItem = (_DataReader["HadAutoFulfilItem"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["HadAutoFulfilItem"]);
_DataItem.HadReoccurItems = (_DataReader["HadReoccurItems"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["HadReoccurItems"]);
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public List <RepairStatusesTbl> GetAll(string SortBy)
{
List <RepairStatusesTbl> _DataItems = new List <RepairStatusesTbl>();
TrackerDb _TrackerDb = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
_sqlCmd += " ORDER BY " + (String.IsNullOrEmpty(SortBy) ? "SortOrder" : SortBy);
// params would go here if need
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
RepairStatusesTbl _DataItem = new RepairStatusesTbl();
_DataItem.RepairStatusID = (_DataReader["RepairStatusID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairStatusID"]);
_DataItem.RepairStatusDesc = (_DataReader["RepairStatusDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairStatusDesc"].ToString();
_DataItem.EmailClient = (_DataReader["EmailClient"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["EmailClient"]);
_DataItem.SortOrder = (_DataReader["SortOrder"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SortOrder"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public List <CityPrepDaysTbl> GetAllByCityId(int pCityID)
{
string _sqlCmd = CONST_SQL_SELECTBYCITYID;
//if (!String.IsNullOrEmpty(SortBy))
// _sqlCmd += " ORDER BY " + SortBy;
TrackerDb _TrackerDb = new TrackerDb();
_TrackerDb.AddWhereParams(pCityID, DbType.Int32);
List <CityPrepDaysTbl> _DataItems = new List <CityPrepDaysTbl>();
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CityPrepDaysTbl _DataItem = new CityPrepDaysTbl();
_DataItem.CityID = pCityID;
_DataItem.CityPrepDaysID = (_DataReader["CityPrepDaysID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityPrepDaysID"]);
_DataItem.PrepDayOfWeekID = (_DataReader["PrepDayOfWeekID"] == DBNull.Value) ? byte.MinValue : Convert.ToByte(_DataReader["PrepDayOfWeekID"]);
_DataItem.DeliveryDelayDays = (_DataReader["DeliveryDelayDays"] == DBNull.Value) ? 0 : Convert.ToInt16(_DataReader["DeliveryDelayDays"]);
_DataItem.DeliveryOrder = (_DataReader["DeliveryOrder"] == DBNull.Value) ? 0 : Convert.ToInt16(_DataReader["DeliveryOrder"]);
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public List <ContactType> GetAllContacts(string SortBy)
{
List <ContactType> _DataItems = new List <ContactType>();
string _sql = CONST_SQL_SELECT;
TrackerDb _TDB = new TrackerDb();
if (!String.IsNullOrEmpty(SortBy))
{
_sql += " ORDER BY " + SortBy;
}
IDataReader _Reader = _TDB.ExecuteSQLGetDataReader(_sql);
if (_Reader != null)
{
while (_Reader.Read())
{
ContactType _DataItem = new ContactType();
_DataItem.CustomerID = Convert.ToInt64(_Reader["CustomerID"]);
_DataItem.CompanyName = (_Reader["CompanyName"] == DBNull.Value) ? "" : _Reader["CompanyName"].ToString();
_DataItem.CustomerTypeID = (_Reader["CustomerTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_Reader["CustomerTypeID"]);
_DataItem.IsEnabled = (_Reader["enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_Reader["enabled"]);
_DataItem.PredictionDisabled = (_Reader["PredictionDisabled"] == DBNull.Value) ? false : Convert.ToBoolean(_Reader["PredictionDisabled"]);
_DataItems.Add(_DataItem);
}
_Reader.Close();
}
_TDB.Close();
return(_DataItems);
}
public List <MachineConditionsTbl> GetAll(string SortBy)
{
List <MachineConditionsTbl> _DataItems = new List <MachineConditionsTbl>();
TrackerDb _TrackerDb = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
_sqlCmd += " ORDER BY " + (String.IsNullOrEmpty(SortBy) ? "SortOrder" : SortBy);
// params would go here if need
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
MachineConditionsTbl _DataItem = new MachineConditionsTbl();
_DataItem.MachineConditionID = (_DataReader["MachineConditionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineConditionID"]);
_DataItem.ConditionDesc = (_DataReader["ConditionDesc"] == DBNull.Value) ? string.Empty : _DataReader["ConditionDesc"].ToString();
_DataItem.SortOrder = (_DataReader["SortOrder"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SortOrder"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public int GetLastIDInserted(long pCustomerID)
{
int _LastID = 0;
TrackerDb _TDB = new TrackerDb();
_TDB.AddWhereParams(pCustomerID, DbType.Int64);
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTLAST);
if (_DataReader != null)
{
if (_DataReader.Read())
{
_LastID = (_DataReader["RepairID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairID"]);
}
else
{
_DataReader.Close();
}
}
_TDB.Close();
return(_LastID);
}
protected void btnFind_Click(object sender, EventArgs e)
{
string _strSQL = "SELECT DISTINCT OrdersTbl.OrderID, CustomersTbl.CompanyName AS CoName," +
" OrdersTbl.CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, OrdersTbl.ItemTypeID, ItemTypeTbl.ItemDesc," +
" OrdersTbl.QuantityOrdered, ItemTypeTbl.ItemShortName, ItemTypeTbl.ItemEnabled, ItemTypeTbl.ReplacementID, CityPrepDaysTbl.DeliveryOrder, " +
" ItemTypeTbl.SortOrder, OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, OrdersTbl.PurchaseOrder, OrdersTbl.Confirmed, OrdersTbl.InvoiceDone, OrdersTbl.Done, OrdersTbl.Notes," +
" PackagingTbl.Description AS PackDesc, PackagingTbl.BGColour, PersonsTbl.Abreviation" +
" FROM ((((CityPrepDaysTbl RIGHT OUTER JOIN CustomersTbl ON CityPrepDaysTbl.CityID = CustomersTbl.City) RIGHT OUTER JOIN " +
" (OrdersTbl LEFT OUTER JOIN PersonsTbl ON OrdersTbl.ToBeDeliveredBy = PersonsTbl.PersonID) ON CustomersTbl.CustomerID = OrdersTbl.CustomerId) LEFT OUTER JOIN " +
" PackagingTbl ON OrdersTbl.PackagingID = PackagingTbl.PackagingID) LEFT OUTER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)" +
" WHERE (CustomersTbl.CompanyName LIKE '%" + tbxFindClient.Text + "%') AND (OrdersTbl.Done = false)" +
" ORDER BY OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, CityPrepDaysTbl.DeliveryOrder, CustomersTbl.CompanyName, ItemTypeTbl.SortOrder";
// sort order needs to depend on day choosen, since at the moment if there are 2 different sort orders on a day even if the day of prep is different it duplicates the item
TrackerDb _TDB = new TrackerDb();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_strSQL);
BuildDeliveryTable(_DataReader, false);
_DataReader.Close();
_TDB.Close();
/*
* OleDbConnection oleConn = GetTrackerOLEConnection();
* // open connection
* oleConn.Open();
* OleDbCommand oleCommand = new OleDbCommand(_strSQL, oleConn);
* OleDbDataReader oleData = oleCommand.ExecuteReader();
*
* // now build the standard table
* BuildDeliveryTable(oleData, false);
*/
}
public List <ActiveDeliveryData> GetActiveDeliveryDateWithDeliveryPerson(string SortBy)
{
List <ActiveDeliveryData> _DataItems = new List <ActiveDeliveryData>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT_ACTIVEDELIVERIES;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); // run the query we have built
if (_DataReader != null)
{
while (_DataReader.Read())
{
ActiveDeliveryData _DataItem = new ActiveDeliveryData();
_DataItem.RequiredByDate = (_DataReader["RequiredByDate"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["RequiredByDate"]).Date;
_DataItem.Person = (_DataReader["Person"] == DBNull.Value) ? string.Empty : _DataReader["Person"].ToString();
_DataItem.PersonID = (_DataReader["PersonID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PersonID"]);
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public UsedItemGroupTbl GetLastUsedItemID(long pContactID, int pItemID, DateTime pDeliveryDate)
{
UsedItemGroupTbl _DataItem = new UsedItemGroupTbl();
TrackerDb _TDB = new TrackerDb();
_TDB.AddWhereParams(pContactID, DbType.Int64, "@ContactID");
_TDB.AddWhereParams(pItemID, DbType.Int32, "@LastItemTypeID");
_TDB.AddWhereParams(pDeliveryDate, DbType.Date, "@LastItemDateChanged ");
//"SELECT UsedItemGroupID, GroupItemTypeID, LastItemTypeSortPos, Notes FROM UsedItemGroupTbl WHERE ContactID = ? AND LastItemTypeID = ? AND LastItemDateChanged = ?";
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SELECTLASTUSEDITEMID);
if (_DataReader != null)
{
if (_DataReader.Read())
{
_DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["UsedItemGroupID"]);
_DataItem.ContactID = pContactID;
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItem.LastItemTypeID = pItemID;
_DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]);
_DataItem.LastItemDateChanged = pDeliveryDate;
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItem);
}
/// <summary>
/// Check to see if this contact has ever taken an item from this group before - if so return it otherwise return -1
/// </summary>
/// <param name="pContactID">The ID of the contact</param>
/// <param name="pGroupItemTypeID">The Group ID requesed</param>
/// <returns>True if it </returns>
public UsedItemGroupTbl ContactLastGroupItem(long pContactID, int pGroupItemTypeID)
{
UsedItemGroupTbl _DataItem = new UsedItemGroupTbl();
TrackerDb _TDB = new TrackerDb();
_TDB.AddWhereParams(pContactID, DbType.Int64, "@ContactID");
_TDB.AddWhereParams(pGroupItemTypeID, DbType.Int32, "@GroupItemTypeID");
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_GETGROUPITEMTYPED); //SELECT UsedItemGroupID, LastItemTypeID, LastItemTypeSortPos, LastItemDateChanged, Notes FROM UsedItemGroupTbl WHERE(ContactID = ?) AND (GroupItemTypeID = ?)";
if (_DataReader != null)
{
if (_DataReader.Read())
{
_DataItem.UsedItemGroupID = (_DataReader["UsedItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["UsedItemGroupID"]);
_DataItem.ContactID = pContactID;
_DataItem.GroupItemTypeID = pGroupItemTypeID;
_DataItem.LastItemTypeID = (_DataReader["LastItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeID"]);
_DataItem.LastItemTypeSortPos = (_DataReader["LastItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastItemTypeSortPos"]);
_DataItem.LastItemDateChanged = (_DataReader["LastItemDateChanged"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastItemDateChanged"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItem);
}
public List <PriceLevelsTbl> GetAll(string SortBy)
{
List <PriceLevelsTbl> _DataItems = new List <PriceLevelsTbl>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
PriceLevelsTbl _DataItem = new PriceLevelsTbl();
#region StoreThisDataItem
_DataItem.PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PriceLevelID"]);
_DataItem.PriceLevelDesc = (_DataReader["PriceLevelDesc"] == DBNull.Value) ? string.Empty : _DataReader["PriceLevelDesc"].ToString();
_DataItem.PricingFactor = (_DataReader["PricingFactor"] == DBNull.Value) ? 0.0 : Math.Round(Convert.ToDouble(_DataReader["PricingFactor"]), 3);
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public List <TransactionTypesTbl> GetAll(string SortBy)
{
List <TransactionTypesTbl> _DataItems = new List <TransactionTypesTbl>();
TrackerDb _TrackerDb = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
TransactionTypesTbl _DataItem = new TransactionTypesTbl();
_DataItem.TransactionID = (_DataReader["TransactionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["TransactionID"]);
_DataItem.TransactionType = (_DataReader["TransactionType"] == DBNull.Value) ? string.Empty : _DataReader["TransactionType"].ToString();
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public const int CONST_DEFAULT_CITYID = 1; // cape town
public static List <CityTblData> GetAllCityTblData(string SortBy)
{
List <CityTblData> _ListCitys = new List <CityTblData>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SUMMARYDATA;
// Add order by string
_sqlCmd += " ORDER BY " + (!String.IsNullOrEmpty(SortBy) ? SortBy : " City");
// run the query we have built
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CityTblData _CityTblItem = new CityTblData();
_CityTblItem.ID = Convert.ToInt32(_DataReader["ID"]);
_CityTblItem.City = (_DataReader["City"] == DBNull.Value) ? "" : _DataReader["City"].ToString();
_ListCitys.Add(_CityTblItem);
}
}
_DataReader.Close();
_TDB.Close();
return(_ListCitys);
}
public List <ItemGroupTbl> GetAll(string SortBy)
{
List <ItemGroupTbl> _DataItems = new List <ItemGroupTbl>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
_sqlCmd += " ORDER BY " + ((String.IsNullOrEmpty(SortBy)) ? "ItemTypeSortPos" : SortBy); // Add order by string
// params would go here if need
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
ItemGroupTbl _DataItem = new ItemGroupTbl();
#region StoreThisDataItem
_DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]);
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]);
_DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]);
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public string GetTransactionTypeByID(Int32 pTransactionID)
{
string _TransactionType = string.Empty;
TrackerDb _TDB = new TrackerDb();
// for some reason this is not working so have replaced with string
//_TDB.AddWhereParams(pTransactionID, DbType.Int32);
string _sql = CONST_SQL_SELECTTTANSACTIONBYID;
_sql = _sql.Replace("?", pTransactionID.ToString());
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sql);
if (_DataReader != null)
{
if (!_DataReader.Read())
{
_TransactionType = (_DataReader["TransactionType"] == DBNull.Value) ? string.Empty : _DataReader["TransactionType"].ToString();
}
else
{
_TransactionType = pTransactionID.ToString();
}
_DataReader.Close();
}
_TDB.Close();
return(_TransactionType);
}
public ItemGroupTbl GetLastGroupItem(int pGroupItemTypeID)
{
ItemGroupTbl _DataItem = new ItemGroupTbl();
TrackerDb _TDB = new TrackerDb();
_TDB.AddWhereParams(pGroupItemTypeID, DbType.Int32, "GroupItemTypeID");
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SELECT_LASTINGROUP);
if (_DataReader != null)
{
if (_DataReader.Read())
{
#region StoreThisDataItem
_DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]);
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]);
_DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]);
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItem);
}
public List <ItemGroupTbl> GetAllByGroupItemTypeID(int pGroupItemID, string SortBy)
{
List <ItemGroupTbl> _DataItems = new List <ItemGroupTbl>();
if (!pGroupItemID.Equals(TrackerDb.CONST_INVALIDID))
{
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECTBYGOUPITEMID; // = "SELECT ItemGroupID, GroupItemTypeID, ItemTypeID, ItemTypeSortPos, Enabled, Notes FROM ItemGroupTbl WHERE GroupItemTypeID=?";
_sqlCmd += " ORDER BY " + ((String.IsNullOrEmpty(SortBy)) ? "ItemTypeSortPos" : SortBy); // Add order by string
// params would go here if need
_TDB.AddWhereParams(pGroupItemID, DbType.Int32, "@GroupItemTypeID");
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
ItemGroupTbl _DataItem = new ItemGroupTbl();
#region StoreThisDataItem
_DataItem.ItemGroupID = (_DataReader["ItemGroupID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemGroupID"]);
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItem.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeID"]);
_DataItem.ItemTypeSortPos = (_DataReader["ItemTypeSortPos"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemTypeSortPos"]);
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
}
return(_DataItems);
}
public List <SysDataTbl> GetAll() //(string SortBy)
{
List <SysDataTbl> _DataItems = new List <SysDataTbl>();
TrackerDb _TrackerDb = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
// if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy; // Add order by string
// params would go here if need
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
SysDataTbl _DataItem = new SysDataTbl();
_DataItem.ID = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]);
_DataItem.LastReoccurringDate = (_DataReader["LastReoccurringDate"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["LastReoccurringDate"]).Date;
_DataItem.DoReoccuringOrders = (_DataReader["DoReoccuringOrders"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["DoReoccuringOrders"]);
_DataItem.DateLastPrepDateCalcd = (_DataReader["DateLastPrepDateCalcd"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["DateLastPrepDateCalcd"]).Date;
_DataItem.MinReminderDate = (_DataReader["MinReminderDate"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["MinReminderDate"]).Date;
_DataItem.GroupItemTypeID = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? TrackerDb.CONST_INVALIDID : Convert.ToInt32(_DataReader["GroupItemTypeID"]);
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
protected void BuildDeliverySheet(bool pPrintForm, string pActiveDeliveryDate, string pOnlyDeliveryBy)
{
// set the session variable for DeliveryBy, for later
Session[CONST_SESSION_DELIVERTBY] = pOnlyDeliveryBy;
string _strSQL = "SELECT DISTINCT OrdersTbl.OrderID, CustomersTbl.CompanyName AS CoName," +
" OrdersTbl.CustomerId, OrdersTbl.OrderDate, OrdersTbl.RoastDate, OrdersTbl.ItemTypeID, ItemTypeTbl.ItemDesc," +
" OrdersTbl.QuantityOrdered, ItemTypeTbl.ItemShortName, ItemTypeTbl.ItemEnabled, ItemTypeTbl.ReplacementID, CityPrepDaysTbl.DeliveryOrder, " +
" ItemTypeTbl.SortOrder, OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, OrdersTbl.PurchaseOrder, OrdersTbl.Confirmed, OrdersTbl.InvoiceDone, OrdersTbl.Done, OrdersTbl.Notes," +
" PackagingTbl.Description AS PackDesc, PackagingTbl.BGColour, PersonsTbl.Abreviation" +
" FROM ((((CityPrepDaysTbl RIGHT OUTER JOIN CustomersTbl ON CityPrepDaysTbl.CityID = CustomersTbl.City) RIGHT OUTER JOIN " +
" (OrdersTbl LEFT OUTER JOIN PersonsTbl ON OrdersTbl.ToBeDeliveredBy = PersonsTbl.PersonID) ON CustomersTbl.CustomerID = OrdersTbl.CustomerId) LEFT OUTER JOIN " +
" PackagingTbl ON OrdersTbl.PackagingID = PackagingTbl.PackagingID) LEFT OUTER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)" +
" WHERE (OrdersTbl.RequiredByDate = #" + pActiveDeliveryDate + "#" + ")" + ((pOnlyDeliveryBy != "") ? " AND OrdersTbl.ToBeDeliveredBy=" + pOnlyDeliveryBy : "") +
" ORDER BY OrdersTbl.RequiredByDate, OrdersTbl.ToBeDeliveredBy, CityPrepDaysTbl.DeliveryOrder, CustomersTbl.CompanyName, ItemTypeTbl.SortOrder";
// sort order needs to depend on day choosen, since at the moment if there are 2 different sort orders on a day even if the day of prep is different it duplicates the item
TrackerDb _TDB = new TrackerDb();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_strSQL);
BuildDeliveryTable(_DataReader, pPrintForm);
_DataReader.Close();
_TDB.Close();
/*
*
* OleDbConnection oleConn = GetTrackerOLEConnection();
* // open connection
* oleConn.Open();
* OleDbCommand oleCommand = new OleDbCommand(_strSQL, oleConn);
*
* OleDbDataReader oleData = oleCommand.ExecuteReader();
* BuildDeliveryTable(oleData, pPrintForm);
*/
}
public List <CustomersAccInfoTbl> GetAll(string SortBy)
{
List <CustomersAccInfoTbl> _DataItems = new List <CustomersAccInfoTbl>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
// params would go here if need
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
if (_DataReader != null)
{
while (_DataReader.Read())
{
CustomersAccInfoTbl _DataItem = new CustomersAccInfoTbl();
#region StoreThisDataItem
_DataItem.CustomersAccInfoID = (_DataReader["CustomersAccInfoID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomersAccInfoID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.CustomerVATNo = (_DataReader["CustomerVATNo"] == DBNull.Value) ? string.Empty : _DataReader["CustomerVATNo"].ToString();
_DataItem.BillAddr1 = (_DataReader["BillAddr1"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr1"].ToString();
_DataItem.BillAddr2 = (_DataReader["BillAddr2"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr2"].ToString();
_DataItem.BillAddr3 = (_DataReader["BillAddr3"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr3"].ToString();
_DataItem.BillAddr4 = (_DataReader["BillAddr4"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr4"].ToString();
_DataItem.BillAddr5 = (_DataReader["BillAddr5"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr5"].ToString();
_DataItem.ShipAddr1 = (_DataReader["ShipAddr1"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr1"].ToString();
_DataItem.ShipAddr2 = (_DataReader["ShipAddr2"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr2"].ToString();
_DataItem.ShipAddr3 = (_DataReader["ShipAddr3"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr3"].ToString();
_DataItem.ShipAddr4 = (_DataReader["ShipAddr4"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr4"].ToString();
_DataItem.ShipAddr5 = (_DataReader["ShipAddr5"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr5"].ToString();
_DataItem.AccEmail = (_DataReader["AccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AccEmail"].ToString();
_DataItem.AltAccEmail = (_DataReader["AltAccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AltAccEmail"].ToString();
_DataItem.PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PaymentTermID"]);
_DataItem.Limit = (_DataReader["Limit"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["Limit"]);
_DataItem.FullCoName = (_DataReader["FullCoName"] == DBNull.Value) ? string.Empty : _DataReader["FullCoName"].ToString();
_DataItem.AccFirstName = (_DataReader["AccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AccFirstName"].ToString();
_DataItem.AccLastName = (_DataReader["AccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AccLastName"].ToString();
_DataItem.AltAccFirstName = (_DataReader["AltAccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccFirstName"].ToString();
_DataItem.AltAccLastName = (_DataReader["AltAccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccLastName"].ToString();
_DataItem.PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PriceLevelID"]);
_DataItem.InvoiceTypeID = (_DataReader["InvoiceTypeID"] == DBNull.Value) ? InvoiceTypeTbl.CONST_DEFAULT_INVOICETYPEID : Convert.ToInt32(_DataReader["InvoiceTypeID"]);
_DataItem.RegNo = (_DataReader["RegNo"] == DBNull.Value) ? string.Empty : _DataReader["RegNo"].ToString();
_DataItem.BankAccNo = (_DataReader["BankAccNo"] == DBNull.Value) ? string.Empty : _DataReader["BankAccNo"].ToString();
_DataItem.BankBranch = (_DataReader["BankBranch"] == DBNull.Value) ? string.Empty : _DataReader["BankBranch"].ToString();
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? true : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TDB.Close();
return(_DataItems);
}
public CustomersAccInfoTbl GetByCustomerID(long pCustomerID)
{
CustomersAccInfoTbl _DataItem = new CustomersAccInfoTbl();
_DataItem.CustomerID = pCustomerID; // assing it to non zero so we can do error checking
string _sql = CONST_SQL_SELECTBYCUSTID;
TrackerDb _TDB = new TrackerDb();
// params would go here if need
_TDB.AddWhereParams(pCustomerID, DbType.Int64, "@CustomerID");
// _sql = _sql.Replace("?", pCustomerID.ToString());
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sql);
if (_DataReader != null)
{
if (_DataReader.Read())
{
#region StoreThisDataItem
_DataItem.CustomersAccInfoID = (_DataReader["CustomersAccInfoID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomersAccInfoID"]);
_DataItem.RequiresPurchOrder = (_DataReader["RequiresPurchOrder"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["RequiresPurchOrder"]);
_DataItem.CustomerVATNo = (_DataReader["CustomerVATNo"] == DBNull.Value) ? string.Empty : _DataReader["CustomerVATNo"].ToString();
_DataItem.BillAddr1 = (_DataReader["BillAddr1"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr1"].ToString();
_DataItem.BillAddr2 = (_DataReader["BillAddr2"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr2"].ToString();
_DataItem.BillAddr3 = (_DataReader["BillAddr3"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr3"].ToString();
_DataItem.BillAddr4 = (_DataReader["BillAddr4"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr4"].ToString();
_DataItem.BillAddr5 = (_DataReader["BillAddr5"] == DBNull.Value) ? string.Empty : _DataReader["BillAddr5"].ToString();
_DataItem.ShipAddr1 = (_DataReader["ShipAddr1"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr1"].ToString();
_DataItem.ShipAddr2 = (_DataReader["ShipAddr2"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr2"].ToString();
_DataItem.ShipAddr3 = (_DataReader["ShipAddr3"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr3"].ToString();
_DataItem.ShipAddr4 = (_DataReader["ShipAddr4"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr4"].ToString();
_DataItem.ShipAddr5 = (_DataReader["ShipAddr5"] == DBNull.Value) ? string.Empty : _DataReader["ShipAddr5"].ToString();
_DataItem.AccEmail = (_DataReader["AccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AccEmail"].ToString();
_DataItem.AltAccEmail = (_DataReader["AltAccEmail"] == DBNull.Value) ? string.Empty : _DataReader["AltAccEmail"].ToString();
_DataItem.PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? _DataItem.PaymentTermID : Convert.ToInt32(_DataReader["PaymentTermID"]);
_DataItem.Limit = (_DataReader["Limit"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["Limit"]);
_DataItem.FullCoName = (_DataReader["FullCoName"] == DBNull.Value) ? string.Empty : _DataReader["FullCoName"].ToString();
_DataItem.AccFirstName = (_DataReader["AccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AccFirstName"].ToString();
_DataItem.AccLastName = (_DataReader["AccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AccLastName"].ToString();
_DataItem.AltAccFirstName = (_DataReader["AltAccFirstName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccFirstName"].ToString();
_DataItem.AltAccLastName = (_DataReader["AltAccLastName"] == DBNull.Value) ? string.Empty : _DataReader["AltAccLastName"].ToString();
_DataItem.PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? _DataItem.PriceLevelID : Convert.ToInt32(_DataReader["PriceLevelID"]);
_DataItem.InvoiceTypeID = (_DataReader["InvoiceTypeID"] == DBNull.Value) ? _DataItem.InvoiceTypeID : Convert.ToInt32(_DataReader["InvoiceTypeID"]);
_DataItem.RegNo = (_DataReader["RegNo"] == DBNull.Value) ? string.Empty : _DataReader["RegNo"].ToString();
_DataItem.BankAccNo = (_DataReader["BankAccNo"] == DBNull.Value) ? string.Empty : _DataReader["BankAccNo"].ToString();
_DataItem.BankBranch = (_DataReader["BankBranch"] == DBNull.Value) ? string.Empty : _DataReader["BankBranch"].ToString();
_DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? true : Convert.ToBoolean(_DataReader["Enabled"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
#endregion
}
_DataReader.Close();
}
_TDB.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);
}
private List <RepairsTbl> GetAllRepairs(string SortBy, string pSQL, List <DBParameter> pWhereParams)
{
List <RepairsTbl> _DataItems = new List <RepairsTbl>();
TrackerDb _TrackerDb = new TrackerDb();
pSQL += " ORDER BY " + (String.IsNullOrEmpty(SortBy) ? "DateLogged DESC" : SortBy); // Add order by string
// params would go here if need
if (pWhereParams != null)
{
_TrackerDb.WhereParams = pWhereParams;
}
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(pSQL);
if (_DataReader != null)
{
while (_DataReader.Read())
{
RepairsTbl _DataItem = new RepairsTbl();
_DataItem.RepairID = (_DataReader["RepairID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairID"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.ContactName = (_DataReader["ContactName"] == DBNull.Value) ? string.Empty : _DataReader["ContactName"].ToString();
_DataItem.ContactEmail = (_DataReader["ContactEmail"] == DBNull.Value) ? string.Empty : _DataReader["ContactEmail"].ToString();
_DataItem.JobCardNumber = (_DataReader["JobCardNumber"] == DBNull.Value) ? string.Empty : _DataReader["JobCardNumber"].ToString();
_DataItem.DateLogged = (_DataReader["DateLogged"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateLogged"]).Date;
_DataItem.LastStatusChange = (_DataReader["LastStatusChange"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["LastStatusChange"]).Date;
_DataItem.MachineTypeID = (_DataReader["MachineTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineTypeID"]);
_DataItem.MachineSerialNumber = (_DataReader["MachineSerialNumber"] == DBNull.Value) ? string.Empty : _DataReader["MachineSerialNumber"].ToString();
_DataItem.SwopOutMachineID = (_DataReader["SwopOutMachineID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SwopOutMachineID"]);
_DataItem.MachineConditionID = (_DataReader["MachineConditionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineConditionID"]);
_DataItem.TakenFrother = (_DataReader["TakenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenFrother"]);
_DataItem.TakenBeanLid = (_DataReader["TakenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenBeanLid"]);
_DataItem.TakenWaterLid = (_DataReader["TakenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenWaterLid"]);
_DataItem.BrokenFrother = (_DataReader["BrokenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenFrother"]);
_DataItem.BrokenBeanLid = (_DataReader["BrokenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenBeanLid"]);
_DataItem.BrokenWaterLid = (_DataReader["BrokenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenWaterLid"]);
_DataItem.RepairFaultID = (_DataReader["RepairFaultID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairFaultID"]);
_DataItem.RepairFaultDesc = (_DataReader["RepairFaultDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairFaultDesc"].ToString();
_DataItem.RepairStatusID = (_DataReader["RepairStatusID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairStatusID"]);
_DataItem.RelatedOrderID = (_DataReader["RelatedOrderID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["RelatedOrderID"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
_DataReader.Close();
}
_TrackerDb.Close();
return(_DataItems);
}
public RepairsTbl GetRepairById(int pRepairID)
{
RepairsTbl _DataItem = null;
if (pRepairID > 0)
{
TrackerDb _TrackerDb = new TrackerDb();
// params would go here if need
_TrackerDb.AddWhereParams(pRepairID, DbType.Int32);
IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(CONST_SQL_SELECTBYREPAIRID);
if (_DataReader != null)
{
if (_DataReader.Read())
{
_DataItem = new RepairsTbl();
_DataItem.RepairID = pRepairID;
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.ContactName = (_DataReader["ContactName"] == DBNull.Value) ? string.Empty : _DataReader["ContactName"].ToString();
_DataItem.ContactEmail = (_DataReader["ContactEmail"] == DBNull.Value) ? string.Empty : _DataReader["ContactEmail"].ToString();
_DataItem.JobCardNumber = (_DataReader["JobCardNumber"] == DBNull.Value) ? string.Empty : _DataReader["JobCardNumber"].ToString();
_DataItem.DateLogged = (_DataReader["DateLogged"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateLogged"]).Date;
_DataItem.LastStatusChange = (_DataReader["LastStatusChange"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["LastStatusChange"]).Date;
_DataItem.MachineTypeID = (_DataReader["MachineTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineTypeID"]);
_DataItem.MachineSerialNumber = (_DataReader["MachineSerialNumber"] == DBNull.Value) ? string.Empty : _DataReader["MachineSerialNumber"].ToString();
_DataItem.SwopOutMachineID = (_DataReader["SwopOutMachineID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SwopOutMachineID"]);
_DataItem.MachineConditionID = (_DataReader["MachineConditionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineConditionID"]);
_DataItem.TakenFrother = (_DataReader["TakenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenFrother"]);
_DataItem.TakenBeanLid = (_DataReader["TakenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenBeanLid"]);
_DataItem.TakenWaterLid = (_DataReader["TakenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenWaterLid"]);
_DataItem.BrokenFrother = (_DataReader["BrokenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenFrother"]);
_DataItem.BrokenBeanLid = (_DataReader["BrokenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenBeanLid"]);
_DataItem.BrokenWaterLid = (_DataReader["BrokenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenWaterLid"]);
_DataItem.RepairFaultID = (_DataReader["RepairFaultID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairFaultID"]);
_DataItem.RepairFaultDesc = (_DataReader["RepairFaultDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairFaultDesc"].ToString();
_DataItem.RepairStatusID = (_DataReader["RepairStatusID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairStatusID"]);
_DataItem.RelatedOrderID = (_DataReader["RelatedOrderID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["RelatedOrderID"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
}
_DataReader.Close();
}
_TrackerDb.Close();
}
return(_DataItem);
}
public List <RepairsTbl> GetListOfRelatedTempOrders()
{
List <RepairsTbl> _RelatedRepairs = new List <RepairsTbl>(); // should only be one but check
TrackerDb _TDB = new TrackerDb();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTITEMWITHANORDER);
if (_DataReader != null)
{
while (_DataReader.Read())
{
RepairsTbl _RelatedRepair = new RepairsTbl();
_RelatedRepair.RepairID = (_DataReader["RepairID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairID"]);
_RelatedRepair.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_RelatedRepair.ContactName = (_DataReader["ContactName"] == DBNull.Value) ? string.Empty : _DataReader["ContactName"].ToString();
_RelatedRepair.ContactEmail = (_DataReader["ContactEmail"] == DBNull.Value) ? string.Empty : _DataReader["ContactEmail"].ToString();
_RelatedRepair.JobCardNumber = (_DataReader["JobCardNumber"] == DBNull.Value) ? string.Empty : _DataReader["JobCardNumber"].ToString();
_RelatedRepair.DateLogged = (_DataReader["DateLogged"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateLogged"]).Date;
_RelatedRepair.LastStatusChange = (_DataReader["LastStatusChange"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["LastStatusChange"]).Date;
_RelatedRepair.MachineTypeID = (_DataReader["MachineTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineTypeID"]);
_RelatedRepair.MachineSerialNumber = (_DataReader["MachineSerialNumber"] == DBNull.Value) ? string.Empty : _DataReader["MachineSerialNumber"].ToString();
_RelatedRepair.SwopOutMachineID = (_DataReader["SwopOutMachineID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SwopOutMachineID"]);
_RelatedRepair.MachineConditionID = (_DataReader["MachineConditionID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["MachineConditionID"]);
_RelatedRepair.TakenFrother = (_DataReader["TakenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenFrother"]);
_RelatedRepair.TakenBeanLid = (_DataReader["TakenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenBeanLid"]);
_RelatedRepair.TakenWaterLid = (_DataReader["TakenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["TakenWaterLid"]);
_RelatedRepair.BrokenFrother = (_DataReader["BrokenFrother"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenFrother"]);
_RelatedRepair.BrokenBeanLid = (_DataReader["BrokenBeanLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenBeanLid"]);
_RelatedRepair.BrokenWaterLid = (_DataReader["BrokenWaterLid"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["BrokenWaterLid"]);
_RelatedRepair.RepairFaultID = (_DataReader["RepairFaultID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairFaultID"]);
_RelatedRepair.RepairFaultDesc = (_DataReader["RepairFaultDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairFaultDesc"].ToString();
_RelatedRepair.RepairStatusID = (_DataReader["RepairStatusID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairStatusID"]);
_RelatedRepair.RelatedOrderID = (_DataReader["RelatedOrderID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["RelatedOrderID"]);
_RelatedRepair.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_RelatedRepairs.Add(_RelatedRepair);
}
_DataReader.Close();
}
_TDB.Close();
return(_RelatedRepairs);
}