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 <ClientUsageLinesTbl> GetAllClientUsageLinesTbl(long pCustomerID, string SortBy)
{
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_SELECT + " WHERE CustomerID = " + pCustomerID.ToString();
if (!String.IsNullOrEmpty(SortBy))
{
_sqlCmd += " ORDER BY " + SortBy; // Add order by string
}
TrackerDb _TrackerDB = new TrackerDb();
OleDbDataReader _DataReader = _TrackerDB.ReturnDataReader(_sqlCmd);
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["LineDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LineDate"]);
_DataItem.CupCount = (_DataReader["CupCount"] == DBNull.Value) ? 0 : Convert.ToInt64(_DataReader["CupCount"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.Qty = (_DataReader["Qty"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Qty"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
}
return(_DataItems);
}
public string Update(PriceLevelsTbl pPriceLevelsTbl, int pOrignal_PriceLevelID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_PriceLevelID > 0)
{
_TDB.AddWhereParams(pOrignal_PriceLevelID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pPriceLevelsTbl.PriceLevelID, DbType.Boolean, "@PriceLevelID");
}
_TDB.AddParams(pPriceLevelsTbl.PriceLevelDesc, DbType.String, "@PriceLevelDesc");
_TDB.AddParams(Math.Round(pPriceLevelsTbl.PricingFactor, 3), DbType.Double, "@PricingFactor");
_TDB.AddParams(pPriceLevelsTbl.Enabled, DbType.Int32, "@Enabled");
_TDB.AddParams(pPriceLevelsTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_result);
}
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);
}
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 <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 string Update(UsedItemGroupTbl pUsedItemGroupTbl, int pOrignal_UsedItemGroupID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_UsedItemGroupID > 0)
{
_TDB.AddWhereParams(pOrignal_UsedItemGroupID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pUsedItemGroupTbl.UsedItemGroupID, DbType.Int32, "@UsedItemGroupID");
}
_TDB.AddParams(pUsedItemGroupTbl.ContactID, DbType.Int64, "@ContactID");
_TDB.AddParams(pUsedItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID");
_TDB.AddParams(pUsedItemGroupTbl.LastItemTypeID, DbType.Int32, "@LastItemTypeID");
_TDB.AddParams(pUsedItemGroupTbl.LastItemTypeSortPos, DbType.Int32, "@LastItemTypeSortPos");
_TDB.AddParams(pUsedItemGroupTbl.LastItemDateChanged, DbType.Date, "@LastItemDateChanged");
_TDB.AddParams(pUsedItemGroupTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); // UPDATE UsedItemGroupTbl SET ContactID = ?, GroupItemTypeID = ?, LastItemTypeID = ?, LastItemTypeSortPos = ?, LastItemDateChanged = ?, Notes = ? WHERE (UsedItemGroupID = ?)";
_TDB.Close();
return(_result);
}
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 <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 <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);
}
/*
* 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 string Update(PaymentTermsTbl pPaymentTermsTbl, int pOrignal_PaymentTermID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_PaymentTermID > 0)
{
_TDB.AddWhereParams(pOrignal_PaymentTermID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pPaymentTermsTbl.PaymentTermID, DbType.Boolean, "@PaymentTermID");
}
_TDB.AddParams(pPaymentTermsTbl.PaymentTermDesc, DbType.String, "@PaymentTermDesc");
_TDB.AddParams(pPaymentTermsTbl.PaymentDays, DbType.Int32, "@PaymentDays");
_TDB.AddParams(pPaymentTermsTbl.DayOfMonth, DbType.Int32, "@DayOfMonth");
_TDB.AddParams(pPaymentTermsTbl.UseDays, DbType.Boolean, "@UseDays");
_TDB.AddParams(pPaymentTermsTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pPaymentTermsTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_result);
}
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 <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);
}
protected void GoBtn_Click(object sender, EventArgs e)
{
// construct the string with the where clause as per above parameters.
string _strSQL = "SELECT ItemTypeTbl.ItemDesc, ROUND(SUM(OrdersTbl.QuantityOrdered),2) AS Quantity" +
" FROM (OrdersTbl INNER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID)";
bool _ByRoastDate = ddlFilterByRoastDate.SelectedValue.Equals("RoastDate");
if (_ByRoastDate)
{
_strSQL += " WHERE (OrdersTbl.RoastDate >= ?) AND (OrdersTbl.RoastDate <= ?) ";
}
else
{
_strSQL += " WHERE (OrdersTbl.RequiredByDate >= ?) AND (OrdersTbl.RequiredByDate <= ?) ";
}
_strSQL += " AND (ItemTypeTbl.ServiceTypeID = 2) GROUP BY ItemTypeTbl.ItemDesc";
TrackerDb _TDB = new TrackerDb();
DateTime _dtFrom = Convert.ToDateTime(tbxDateFrom.Text); // (ddlDateFrom.SelectedValue);
_TDB.AddWhereParams(_dtFrom, DbType.Date, "@RoastDateFrom");
_TDB.AddWhereParams(Convert.ToDateTime(tbxDateTo.Text), DbType.Date, "@RoastDateTo"); // ddlDateTo.SelectedValue), DbType.Date, "@RoastDateTo");
double _Dbl = _dtFrom.DayOfYear / 7;
ViewState[CONST_WEEKDESC] = "Y" + _dtFrom.Year.ToString() + " Wk " + Convert.ToString(Math.Ceiling(_Dbl) + 1);
ltrlDates.Text = String.Format("{0} - From: {1} to {2}", ((_ByRoastDate) ? "Roast Date" : "Prep Date"), tbxDateFrom.Text, tbxDateTo.Text); // ddlDateFrom.SelectedValue + " " + ddlDateTo.SelectedValue;
// zero values
ZeroViewStateVals();
;
DataSet _ds = _TDB.ReturnDataSet(_strSQL);
gvPreperationSummary.DataSource = _ds;
gvPreperationSummary.DataBind();
}
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 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);
}
/*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
*
*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
*
* // add parameters in the order they appear in the update command
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
* _cmd.Parameters.Add(new OleDbParameter { Value = ItemTypeID });
* _cmd.Parameters.Add(new OleDbParameter { Value = Math.Round(QuantityOrdered,2) });
* _cmd.Parameters.Add(new OleDbParameter { Value = PackagingID });
* _cmd.Parameters.Add(new OleDbParameter { Value = OrderID });
*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
* return false;
* }
* catch (OleDbException ex)
* {
* string _ErrStr = ex.Message;
* return _ErrStr == "";
* }
* finally
* {
* _conn.Close();
* }
*
* return true;
* }
*/
public bool InsertOrderDetails(Int64 CustomerID, DateTime OrderDate, DateTime RoastDate, Int32 ToBeDeliveredBy,
DateTime RequiredByDate, Boolean Confirmed, Boolean Done, String Notes,
double QuantityOrdered, Int32 PackagingID, Int32 ItemTypeID)
{
string _sqlCmd = "INSERT INTO OrdersTbl (CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredBy, Confirmed, Done, Notes, " +
" ItemTypeID, QuantityOrdered, PackagingID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//1 2 3 4 5 6 7 8 9 10 11
bool _Success = false;
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, RequiredByDate);
TrackerDb _TDB = new TrackerDb();
// first summary data
_TDB.AddParams(CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(ToBeDeliveredBy, DbType.Int32, "@ToBeDeliveredBy");
_TDB.AddParams(Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(Done, DbType.Boolean, "@Done");
_TDB.AddParams(Notes, DbType.String, "@Notes");
// Now line data
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
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 <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);
}
/// <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 <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 <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 string UpdateItemGroup(ItemGroupTbl pItemGroupTbl, int pOrignal_ItemGroupID)
{
string _result = string.Empty;
TrackerDb _TDB = new TrackerDb();
#region UpdateParameters
if (pOrignal_ItemGroupID > 0)
{
_TDB.AddWhereParams(pOrignal_ItemGroupID, DbType.Int32); // check this line it assumes id field is int32
}
else
{
_TDB.AddWhereParams(pItemGroupTbl.ItemGroupID, DbType.Int32, "@ItemGroupID");
}
_TDB.AddParams(pItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID");
_TDB.AddParams(pItemGroupTbl.ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(pItemGroupTbl.ItemTypeSortPos, DbType.Int32, "@ItemTypeSortPos");
_TDB.AddParams(pItemGroupTbl.Enabled, DbType.Boolean, "@Enabled");
_TDB.AddParams(pItemGroupTbl.Notes, DbType.String, "@Notes");
#endregion
// Now we have the parameters excute the SQL
_result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE);
_TDB.Close();
return(_result);
}
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 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 <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 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 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);
}
/// <summary>
/// Execute the SQL statement does not return results, such as: delete, update, insert operation
/// </summary>
/// <param name="strSQL">SQL String of a non Query Type</param>
/// <returns>success or failure</returns>
public bool UpdateOrderHeader(OrderHeaderData pOrderHeader, List <string> pOrders)
{
bool _resultState = false;
string _strSQL = CONST_ORDERUPDATEHEADER_SQL + " WHERE ";
// for all the OrderIds passed create a where clause
for (int i = 0; i < pOrders.Count - 1; i++)
{
_strSQL += " OrderID = " + pOrders[i] + " OR";
}
_strSQL += " OrderID = " + pOrders[pOrders.Count - 1];
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pOrderHeader.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pOrderHeader.OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(pOrderHeader.RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(pOrderHeader.ToBeDeliveredBy, DbType.Int64, "@ToBeDeliveredBy");
_TDB.AddParams(pOrderHeader.RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(pOrderHeader.Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(pOrderHeader.Done, DbType.Boolean, "@Done");
_TDB.AddParams(pOrderHeader.InvoiceDone, DbType.Boolean, "@InvoiceDone");
_TDB.AddParams(pOrderHeader.PurchaseOrder, DbType.String, "@PurchaseOrder");
_TDB.AddParams(pOrderHeader.Notes, DbType.String, "@Notes");
_resultState = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_strSQL));
_TDB.Close();
return(_resultState);
}