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); }
public List <PaymentTermsTbl> GetAll(string SortBy) { List <PaymentTermsTbl> _DataItems = new List <PaymentTermsTbl>(); 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()) { PaymentTermsTbl _DataItem = new PaymentTermsTbl(); #region StoreThisDataItem _DataItem.PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PaymentTermID"]); _DataItem.PaymentTermDesc = (_DataReader["PaymentTermDesc"] == DBNull.Value) ? string.Empty : _DataReader["PaymentTermDesc"].ToString(); _DataItem.PaymentDays = (_DataReader["PaymentDays"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PaymentDays"]); _DataItem.DayOfMonth = (_DataReader["DayOfMonth"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["DayOfMonth"]); _DataItem.UseDays = (_DataReader["UseDays"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["UseDays"]); _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 List <ReoccuranceTypeTbl> GetAll(string SortBy) { List <ReoccuranceTypeTbl> _DataItems = new List <ReoccuranceTypeTbl>(); TrackerDb _TDB = new TrackerDb(); // string _connectionStr = CONST_CONSTRING; //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 qurey we have built //_conn.Open(); //OleDbDataReader _DataReader = _cmd.ExecuteReader(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { ReoccuranceTypeTbl _DataItem = new ReoccuranceTypeTbl(); _DataItem.ID = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]); _DataItem.Type = (_DataReader["Type"] == DBNull.Value) ? string.Empty : _DataReader["Type"].ToString(); _DataItems.Add(_DataItem); } //} _DataReader.Close(); _TDB.Close(); return(_DataItems); }
/// <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 CustomersTbl GetCustomersByCustomerID(long pCustomerID, string pSortBy) { List <CustomersTbl> _ListCustomersTblData = new List <CustomersTbl>(); TrackerDb _TrackerDb = new TrackerDb(); //string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString; string _sqlCmd = CONST_SQL_CUSTOMERS_SELECT; if (pCustomerID > 0) { _sqlCmd += " WHERE CustomerID = ? "; } if (!String.IsNullOrEmpty(pSortBy)) { _sqlCmd += " ORDER BY " + pSortBy; } // run the qurey we have built if (pCustomerID > 0) { _TrackerDb.AddWhereParams(pCustomerID, DbType.Int64); // Parameters.Add(new OdbcParameter { Value = pCustomerID }); } IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { _ListCustomersTblData.Add(MoveReaderDataToCustomersTblData(_DataReader)); //_CustomersTblData); } _TrackerDb.Close(); return(_ListCustomersTblData[0]); }
/// <summary> /// Retrieve the customer's defaults as per the ClientTbl /// </summary> /// <param name="_CustID">the customer id</param> /// <returns>Contact / Customers prefered items for ID passed</returns> public ContactPreferedItems RetrieveCustomerPrefs(long _CustID) { // using custonmer id query the customers table to get the customers preferences. // prep the TTools connection, if it has not already been preped TrackerDb _TDB = new TrackerDb(); ContactPreferedItems _ContactPreferedItems = new ContactPreferedItems(_CustID); // get the customers City string _sqlStr = "SELECT CustomersTbl.PreferedAgent, CustomersTbl.CoffeePreference, CustomersTbl.PriPrefQty, CustomersAccInfoTbl.RequiresPurchOrder " + " FROM (CustomersTbl LEFT OUTER JOIN CustomersAccInfoTbl ON CustomersTbl.CustomerID = CustomersAccInfoTbl.CustomerID) WHERE CustomersTbl.CustomerID = " + _CustID.ToString(); IDataReader _Reader = _TDB.ExecuteSQLGetDataReader(_sqlStr); if (_Reader != null) { if (_Reader.Read()) { _ContactPreferedItems.DeliveryByID = (_Reader["PreferedAgent"] == DBNull.Value) ? CONST_DEFAULT_DELIVERYBYID : (int)_Reader["PreferedAgent"]; _ContactPreferedItems.PreferedItem = (_Reader["CoffeePreference"] == DBNull.Value) ? 0 : (int)_Reader["CoffeePreference"]; _ContactPreferedItems.PreferedQty = (_Reader["PriPrefQty"] == DBNull.Value) ? 1 : Convert.ToDouble(_Reader["PriPrefQty"]); _ContactPreferedItems.RequiresPurchOrder = (_Reader["RequiresPurchOrder"] == DBNull.Value) ? false : (bool)(_Reader["RequiresPurchOrder"]); } _Reader.Close(); } _TDB.Close(); return(_ContactPreferedItems); }
public int GetServiceID(int pItemID) { //string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString; TrackerDb _TDB = new TrackerDb(); //using (OdbcConnection _conn = new OdbcConnection(_connectionStr)) //{ int _ServiceID = 0; //OdbcCommand _cmd = new OdbcCommand(CONST_SQL_SELECTSERVICETYPEID, _conn); // run the qurey we have built //_cmd.Parameters.Add(new OdbcParameter { Value = pItemID }); //_conn.Open(); //OdbcDataReader _DataReader = _cmd.ExecuteReader(); _TDB.AddWhereParams(pItemID, DbType.Int32); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTSERVICETYPEID); if (_DataReader.Read()) { _ServiceID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]); } //} return(_ServiceID); }
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); }
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 <RepairFaultsTbl> GetAll(string SortBy) { List <RepairFaultsTbl> _DataItems = new List <RepairFaultsTbl>(); 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()) { RepairFaultsTbl _DataItem = new RepairFaultsTbl(); _DataItem.RepairFaultID = (_DataReader["RepairFaultID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["RepairFaultID"]); _DataItem.RepairFaultDesc = (_DataReader["RepairFaultDesc"] == DBNull.Value) ? string.Empty : _DataReader["RepairFaultDesc"].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 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); }
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); }
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 List <CityPrepDaysTbl> GetAll(string SortBy) { TrackerDb _TrackerDb = new TrackerDb(); string _sqlCmd = CONST_SQL_SELECT; if (!String.IsNullOrEmpty(SortBy)) { _sqlCmd += " ORDER BY " + SortBy; } List <CityPrepDaysTbl> _DataItems = new List <CityPrepDaysTbl>(); IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { CityPrepDaysTbl _DataItem = new CityPrepDaysTbl(); _DataItem.CityPrepDaysID = (_DataReader["CityPrepDaysID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityPrepDaysID"]); _DataItem.CityID = (_DataReader["CityID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CityID"]); _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 <CustomerTypeTbl> GetAll(string SortBy) { List <CustomerTypeTbl> _DataItems = new List <CustomerTypeTbl>(); //string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString; TrackerDb _TDB = new TrackerDb(); //using (OdbcConnection _conn = new OdbcConnection(_connectionStr)) //{ string _sqlCmd = CONST_SQL_SELECT; if (!String.IsNullOrEmpty(SortBy)) { _sqlCmd += " ORDER BY " + SortBy; // Add order by string } //OdbcCommand _cmd = new OdbcCommand(_sqlCmd, _conn); // run the qurey we have built //_conn.Open(); // OdbcDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { CustomerTypeTbl _DataItem = new CustomerTypeTbl(); _DataItem.CustTypeID = (_DataReader["CustTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustTypeID"]); _DataItem.CustTypeDesc = (_DataReader["CustTypeDesc"] == DBNull.Value) ? string.Empty : _DataReader["CustTypeDesc"].ToString(); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); _DataItems.Add(_DataItem); } //} return(_DataItems); }
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); }
private List <RepairsTbl> GetAllRepairs(string SortBy, string pSQL, List <OdbcParameter> 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 List <ReoccuringOrderTbl> GetAll(bool pOnlyEnabled, string SortBy) { List <ReoccuringOrderTbl> _DataItems = new List <ReoccuringOrderTbl>(); // string _connectionStr = CONST_CONSTRING; TrackerDb _TDB = new TrackerDb(); //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ string _sqlCmd = CONST_SQL_SELECT; if (pOnlyEnabled) { _sqlCmd += " WHERE ([Enabled] = true) "; // add where if required } if (!String.IsNullOrEmpty(SortBy)) { _sqlCmd += " ORDER BY " + SortBy; // Add order by string } //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built //_conn.Open(); //OleDbDataReader _DataReader = _cmd.ExecuteReader(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl(); _DataItem.ID = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]); _DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]); _DataItem.ReoccuranceType = (_DataReader["ReoccuranceType"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReoccuranceType"]); _DataItem.ReoccuranceValue = (_DataReader["Value"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Value"]); _DataItem.ItemRequiredID = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]); _DataItem.QtyRequired = (_DataReader["QtyRequired"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["QtyRequired"]); _DataItem.DateLastDone = (_DataReader["DateLastDone"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["DateLastDone"]); _DataItem.NextDateRequired = (_DataReader["NextDateRequired"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextDateRequired"]); _DataItem.RequireUntilDate = (_DataReader["RequireUntilDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["RequireUntilDate"]); _DataItem.Enabled = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]); _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); _DataItems.Add(_DataItem); } _DataReader.Close(); _TDB.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); }
public int GetPriceLevelIDByDesc(string pPriceLevelDesc) { int _PriceLevelID = 0; TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pPriceLevelDesc, DbType.String, "@PriceLevelDesc"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTBYDESC); if (_DataReader != null) { if (_DataReader.Read()) { _PriceLevelID = (_DataReader["PriceLevelID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PriceLevelID"]); } _DataReader.Close(); } _TDB.Close(); return(_PriceLevelID); }
public string GetCityName(int pCityID) { string _ItemDesc = String.Empty; TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pCityID, DbType.Int32, "@ID"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTCITYBYID); if (_DataReader != null) { if (_DataReader.Read()) { _ItemDesc = (_DataReader["City"] == DBNull.Value) ? "" : _DataReader["City"].ToString(); } _DataReader.Close(); } _TDB.Close(); return(_ItemDesc); }
public int GetByPaymentTypeIDByCustomerID(long pCustomerID) { int _PaymentTermID = 0; TrackerDb _TDB = new TrackerDb(); // params would go here if need _TDB.AddWhereParams(pCustomerID, DbType.Int64, "@CustomerID"); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTPaymentTermID_BYCUSTID); if (_DataReader != null) { if (_DataReader.Read()) { _PaymentTermID = (_DataReader["PaymentTermID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["PaymentTermID"]); } _DataReader.Close(); } _TDB.Close(); return(_PaymentTermID); }
public DateTime GetMinReminderDate() { DateTime _MinDate = DateTime.MinValue; TrackerDb _TDB = new TrackerDb(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTMINREMINDERDATE); if (_DataReader != null) { if (_DataReader.Read()) { _MinDate = (_DataReader["MinReminderDate"] == DBNull.Value) ? System.DateTime.MinValue : Convert.ToDateTime(_DataReader["MinReminderDate"]).Date; } _DataReader.Close(); } _TDB.Close(); return(_MinDate); }
/// <summary> /// Return the item in the SysDataTbl that is a group Item /// </summary> /// <returns>TheID of the ServiceType that is a group</returns> public int GetGroupItemTypeID() { int _Result = 0; TrackerDb _TDB = new TrackerDb(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_SELECTGROUPSERVICETYPEID); if (_DataReader != null) { if (_DataReader.Read()) { _Result = (_DataReader["GroupItemTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["GroupItemTypeID"]); } _DataReader.Close(); } _TDB.Close(); return(_Result); }
public int GetCustomersInvoiceType(long pCustomerID) { int _InvoiceTypeID = InvoiceTypeTbl.CONST_DEFAULT_INVOICETYPEID; TrackerDb _TDB = new TrackerDb(); _TDB.AddWhereParams(pCustomerID, DbType.Int64); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(CONST_SQL_GETCUSTOMERSINVOICETYPE); if (_DataReader != null) { if (_DataReader.Read()) { _InvoiceTypeID = (_DataReader["InvoiceTypeID"] == DBNull.Value) ? InvoiceTypeTbl.CONST_DEFAULT_INVOICETYPEID : Convert.ToInt32(_DataReader["InvoiceTypeID"]); } _DataReader.Close(); } _TDB.Close(); return(_InvoiceTypeID); }
public List <OrderDetailData> LoadOrderDetailData(Int64 CustomerId, DateTime DeliveryDate, String Notes, int MaximumRows, int StartRowIndex) { List <OrderDetailData> oDetails = new List <OrderDetailData>(); TrackerDb _TDB = new TrackerDb(); string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE "; if (CustomerId == 9) //CustomersTbl.CONST_CUSTOMERID_GENERALOROTHER) { // _sqlCmd += "([CustomerId] = " + CustomersTbl.CONST_STR_CUSTOMERID_GENERALOROTHER + ") AND ([RequiredByDate] = ?) AND ([Notes] = ?)"; _sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)"; _TDB.AddWhereParams(DeliveryDate, DbType.Date, "@RequiredByDate"); _TDB.AddWhereParams(Notes, DbType.String, "@Notes"); } else { _sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)"; _TDB.AddWhereParams(CustomerId, DbType.Int64, "@CustomerId"); _TDB.AddWhereParams(DeliveryDate, DbType.Date, "@RequiredByDate"); } IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { OrderDetailData od = new OrderDetailData(); od.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : (Int32)_DataReader["ItemTypeID"]; od.PackagingID = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : (Int32)_DataReader["PackagingID"]; od.OrderID = (Int32)_DataReader["OrderId"]; // this is the PK cannot be null od.QuantityOrdered = (_DataReader["QuantityOrdered"] == DBNull.Value) ? 1 : Math.Round(Convert.ToDouble(_DataReader["QuantityOrdered"]), 2); oDetails.Add(od); } _DataReader.Close(); } _TDB.Close(); return(oDetails); }
public List <DateTime> GetLast20DatesReminderSent() { List <DateTime> _DataItems = new List <DateTime>(); TrackerDb _TrackerDb = new TrackerDb(); string _sqlCmd = CONST_SQL_SELECT_REMINDERDATESSENT; IDataReader _DataReader = _TrackerDb.ExecuteSQLGetDataReader(_sqlCmd); if (_DataReader != null) { while (_DataReader.Read()) { DateTime _DataItem = new DateTime(); _DataItem = (_DataReader["DateSentReminder"] == DBNull.Value) ? System.DateTime.Now.Date : Convert.ToDateTime(_DataReader["DateSentReminder"]).Date; _DataItems.Add(_DataItem); } _DataReader.Close(); } _TrackerDb.Close(); return(_DataItems); }