protected bool AddNewOrderLine(int pNewItemID, double pNewQuantityOrdered, int pNewPackagingID)
{
string _ErrorStr = "";
OrderTblData _OrderData = new OrderTblData();
OrderTbl _OrderTbl = new OrderTbl();
_OrderData.CustomerId = Convert.ToInt64(ddlContacts.SelectedValue);
_OrderData.OrderDate = Convert.ToDateTime(tbxOrderDate.Text).Date;
_OrderData.RoastDate = Convert.ToDateTime(tbxRoastDate.Text).Date;
_OrderData.RequiredByDate = Convert.ToDateTime(tbxRequiredByDate.Text).Date;
_OrderData.ToBeDeliveredBy = Convert.ToInt32(ddlToBeDeliveredBy.SelectedValue);
_OrderData.PurchaseOrder = tbxPurchaseOrder.Text;
_OrderData.Confirmed = Convert.ToBoolean(cbxConfirmed.Checked);
_OrderData.InvoiceDone = cbxInvoiceDone.Checked;
_OrderData.Done = Convert.ToBoolean(cbxDone.Checked);
_OrderData.Notes = tbxNotes.Text;
// Now line data
Session[OrderHeaderData.CONST_BOUNDOLDDELIVERYDATE] = _OrderData.RequiredByDate.Date;
TrackerTools _TT = new TrackerTools();
_OrderData.ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(_OrderData.CustomerId, pNewItemID, _OrderData.RequiredByDate);
_OrderData.QuantityOrdered = pNewQuantityOrdered;
_OrderData.PackagingID = pNewPackagingID;
_ErrorStr = _OrderTbl.InsertNewOrderLine(_OrderData);
BindRowQueryParameters();
return(String.IsNullOrEmpty(_ErrorStr));
}
// const string CONST_SQL_CUSTOMERS_DISABLEIFREMINDERTOHIGH = "UPDATE CustomersTbl SET enabled = false WHERE (CustomerID = ?) AND (ReminderCount > ?)";
public bool DisableCustomerIfReminderToHigh(long pCustomerID)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_CUSTOMERS_DISABLEIFREMINDERTOHIGH;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// WHERE CustomerID = ?
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID, OleDbType = OleDbType.Integer
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() >= 0);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
}
finally
{
_conn.Close();
}
}
return(_Success);
}
protected void btnAdd_Click(object sender, EventArgs e)
{
string _ErrorStr = "Connection not openned";
OrderTbl _OrderTbl = new OrderTbl();
OrderHeaderData _OrderHeaderData = Get_dvOrderHeaderData(false);
OrderTblData _OrderData = new OrderTblData();
// first summary data
_OrderData.CustomerId = _OrderHeaderData.CustomerID;
_OrderData.OrderDate = _OrderHeaderData.OrderDate;
_OrderData.RoastDate = _OrderHeaderData.RoastDate;
_OrderData.RequiredByDate = _OrderHeaderData.RequiredByDate;
_OrderData.ToBeDeliveredBy = Convert.ToInt32(_OrderHeaderData.ToBeDeliveredBy);
_OrderData.PurchaseOrder = _OrderHeaderData.PurchaseOrder;
_OrderData.Confirmed = _OrderHeaderData.Confirmed;
_OrderData.InvoiceDone = _OrderHeaderData.InvoiceDone;
_OrderData.Done = _OrderHeaderData.Done;
_OrderData.PurchaseOrder = _OrderHeaderData.PurchaseOrder;
_OrderData.Notes = _OrderHeaderData.Notes;
// Now line data
TrackerTools _TT = new TrackerTools();
_OrderData.ItemTypeID = Convert.ToInt32(ddlNewItemDesc.SelectedValue);
_OrderData.ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(_OrderData.CustomerId, _OrderData.ItemTypeID, _OrderData.RequiredByDate);
_OrderData.QuantityOrdered = Convert.ToDouble(tbxNewQuantityOrdered.Text);
_OrderData.PackagingID = Convert.ToInt32(ddlNewPackaging.SelectedValue);
_ErrorStr = _OrderTbl.InsertNewOrderLine(_OrderData);
ltrlStatus.Text = (String.IsNullOrWhiteSpace(_ErrorStr) ? "Item Added" : "Error adding item: " + _ErrorStr);
HideNewOrderItemPanel();
}
public List <ClientUsageLinesTbl> GetLast10UsageLines(long pCustomerID, int pServiceTypeId)
{
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = "SELECT TOP 10 ClientUsageLineNo, CustomerID, [Date] AS LineDate, CupCount, ServiceTypeID " +
"FROM ClientUsageLinesTbl WHERE ClientUsageLinesTbl.CustomerID = ? ";
if (pServiceTypeId > 0)
{
_sqlCmd += " AND ClientUsageLinesTbl.ServiceTypeID = ?";
}
_sqlCmd += " ORDER BY ClientUsageLinesTbl.Date DESC";
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
if (pServiceTypeId > 0)
{
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeId
});
}
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_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);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_DataItems.Clear();
}
finally
{
_conn.Close();
}
}
return(_DataItems);
}
//private OleDbConnection OpenTrackerOleDBConnection()
//{
// OleDbConnection pConn = null;
// string _connectionString;
// if (ConfigurationManager.ConnectionStrings[CONST_CONSTRING] == null ||
// ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString.Trim() == "")
// {
// throw new Exception("A connection string named " + CONST_CONSTRING + " with a valid connection string " +
// "must exist in the <connectionStrings> configuration section for the application.");
// }
// _connectionString = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString;
// pConn = new OleDbConnection(_connectionString);
// return pConn;
//}
protected void SetContactByID(string pCoNameID)
{
// Set the Contact Name to string passed
// check for null string
if (!string.IsNullOrEmpty(pCoNameID))
{
// now find it
if (ddlContacts.Items.FindByValue(pCoNameID) != null)
{
ddlContacts.SelectedValue = pCoNameID;
TrackerTools tt = new TrackerTools();
TrackerTools.ContactPreferedItems _ContactPreferedItems = tt.RetrieveCustomerPrefs(Convert.ToInt64(pCoNameID));
if (ddlToBeDeliveredBy.Items.FindByValue(_ContactPreferedItems.DeliveryByID.ToString()) != null)
{
ddlToBeDeliveredBy.SelectedValue = _ContactPreferedItems.DeliveryByID.ToString();
}
}
else
{ // did not find company to set it to generic
ddlContacts.SelectedValue = CONST_ZZNAME_DEFAULTID;
tbxNotes.Text += "ID note found: " + pCoNameID + ": ";
}
}
}
public double GetAverageConsumption(long pCustomerID)
{
double _Ave = TrackerDotNet.classes.TrackerTools.CONST_TYPICALAVECONSUMPTION;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
try
{
OleDbCommand _cmd = new OleDbCommand(CONST_SQL_SELECTDAILYCONSUMPTION, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read())
{
_Ave = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
}
}
catch (Exception _ex)
{
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_Ave);
}
/*
* 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 bool UsageDataExists(long pCustomerID)
{
bool _Exists = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
try
{
OleDbCommand _cmd = new OleDbCommand(CONST_SQL_ISARECORD, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
_Exists = _DataReader.HasRows;
}
catch (Exception _ex)
{
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_Exists);
}
/// <summary>
/// Do the current lines have service type coffee in it
/// </summary>
/// <returns>if there is a service item coffee</returns>
public bool HasCoffeeInTempOrder()
{
string _sqlCmd = "SELECT ServiceTypeID " +
" FROM TempOrdersLinesTbl" +
" WHERE ServiceTypeID = " + TrackerTools.CONST_STRING_SERVTYPECOFFEE;
bool _HasCoffeeInTemp = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
// now get data from database
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_conn.Open();
try
{
OleDbDataReader _DataReader = _cmd.ExecuteReader();
_HasCoffeeInTemp = (_DataReader != null) ? _DataReader.HasRows : false; // if it has rows then they exists
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
}
finally
{
_conn.Close();
}
}
return(_HasCoffeeInTemp);
}
// const string CONST_SQL_CUSTOMERS_SELECT_REMINDERCOUNT = "SELECT ReminderCount FROM CustomersTbl WHERE CustomersTbl.CustomerID = ?";
public int GetReminderCount(long pCustomerID)
{
int _ReminderCount = -1;
string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_CUSTOMERS_SELECT_REMINDERCOUNT;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
// WHERE CustomerID = ?
_cmd.Parameters.Add(new OleDbParameter { Value = pCustomerID, OleDbType = OleDbType.Integer });
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read())
_ReminderCount = (_DataReader["ReminderCount"] == DBNull.Value) ? -1 : Convert.ToInt32(_DataReader["ReminderCount"]);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
}
finally
{
_conn.Close();
}
}
return _ReminderCount;
}
// real routine
public List <ClientUsageLinesTbl> GetAllCustomerServiceLines(long pCustomerID, int pServiceTypeID, string pSortBy)
{
string CONST_SQL_SELECT = "SELECT ClientUsageLineNo, CustomerID, Date, CupCount, ServiceTypeID, Qty, Notes "
+ " FROM ClientUsageLinesTbl"
+ " WHERE ClientUsageLinesTbl.CustomerID = ?" // + pCustomerID
+ " AND ClientUsageLinesTbl.ServiceTypeID = ?";
List <ClientUsageLinesTbl> _DataItems = new List <ClientUsageLinesTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = CONST_SQL_SELECT;
if (!String.IsNullOrEmpty(pSortBy))
{
_sqlCmd += " ORDER BY " + pSortBy; // Add order by string
}
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeID
});
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
while (_DataReader.Read())
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
_DataItem.LineDate = (_DataReader["Date"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["Date"]);
_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);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_DataItems.Clear();
}
finally
{
_conn.Close();
}
}
return(_DataItems);
}
/// <summary>
/// Return the latest data for the customer for a service type, or 0 for last item
/// </summary>
/// <param name="pCustomerID">the custoerm id</param>
/// <param name="pServiceTypeID">default 0, means any service type otherwise pass the type.</param>
/// <returns>data from that line</returns>
public ClientUsageLinesTbl GetLatestUsageData(long pCustomerID, int pServiceTypeID)
{
ClientUsageLinesTbl _DataItem = new ClientUsageLinesTbl();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
string _sqlCmd = "SELECT TOP 1 ClientUsageLineNo, CustomerID, [Date] As LineDate, CupCount, ServiceTypeID, Qty " +
" FROM ClientUsageLinesTbl WHERE CustomerID = ? ";
if (pServiceTypeID > 0)
{
_sqlCmd += "AND ServiceTypeID = ?";
}
// now make sure it is the last one
_sqlCmd += " ORDER BY [Date] DESC";
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
if (pServiceTypeID > 0)
{
_cmd.Parameters.Add(new OleDbParameter {
Value = pServiceTypeID
});
}
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read() && (_DataReader[0] != null))
{
_DataItem.CustomerID = pCustomerID;
_DataItem.ClientUsageLineNo = (_DataReader["ClientUsageLineNo"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ClientUsageLineNo"]);
_DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_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"]);
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_DataItem);
}
public bool UpdateItemsUsed(ClientUsageLinesTbl pClientUsageLine, long OriginalClientUsageLineNo)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_UPDATE;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// Add data sent
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.CustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.LineDate
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.CupCount
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.ServiceTypeID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.Qty
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pClientUsageLine.Notes
});
// " WHERE ClientUsageLineNo = ?)";
_cmd.Parameters.Add(new OleDbParameter {
Value = OriginalClientUsageLineNo
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() == 0);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
}
finally
{
_conn.Close();
}
return(_Success);
}
}
protected void btnResetPrepDates_Click(object sender, EventArgs e)
{
pnlResetPrepDate.Visible = true;
TrackerTools _TTools = new TrackerTools();
_TTools.SetNextRoastDateByCity();
sdsCityPrepDates.DataBind();
gvCityPrepDates.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
QOnT.classes.TrackerTools tt = new TrackerTools();
// now.AddDays(-7)
dtThisRoastWeekStart = tt.GetClosestNextRoastDate(DateTime.Now.AddDays(-7), DayOfWeek.Monday);
dtThisRoastWeekEnd = dtThisRoastWeekStart.AddDays(7); // 5 days in a working week;
tbxFilter.Text = dtThisRoastWeekStart.ToShortDateString();
ddlFilterBy.Focus();
this.Form.DefaultButton = btnGo.UniqueID;
}
}
protected void PrepPageData()
{
// load the data into the grid view
TrackerDotNet.classes.TrackerTools _TTools = new TrackerTools();
if (!_TTools.IsNextRoastDateByCityTodays())
{
_TTools.SetNextRoastDateByCity(); // The next roast days have not been calcualted, calculate them and save. When customers city is found save the details
}
// add all people into the grid that are to receive reminders
SetListOfContactsToSendReminderTo(); //(ref strs, ref dts);
upnlCustomerCheckup.Visible = true;
upnlContactItems.Visible = true;
}
public List <TrackedServiceItemTbl> GetAll(string SortBy)
{
List <TrackedServiceItemTbl> _DataItems = new List <TrackedServiceItemTbl>();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.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 qurey we have built
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
while (_DataReader.Read())
{
TrackedServiceItemTbl _DataItem = new TrackedServiceItemTbl();
_DataItem.TrackerServiceItemID = (_DataReader["TrackerServiceItemID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["TrackerServiceItemID"]);
_DataItem.ServiceTypeID = (_DataReader["ServiceTypeID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ServiceTypeID"]);
_DataItem.TypicalAvePerItem = (_DataReader["TypicalAvePerItem"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["TypicalAvePerItem"]);
_DataItem.UsageDateFieldName = (_DataReader["UsageDateFieldName"] == DBNull.Value) ? string.Empty : _DataReader["UsageDateFieldName"].ToString();
_DataItem.UsageAveFieldName = (_DataReader["UsageAveFieldName"] == DBNull.Value) ? string.Empty : _DataReader["UsageAveFieldName"].ToString();
_DataItem.ThisItemSetsDailyAverage = (_DataReader["ThisItemSetsDailyAverage"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["ThisItemSetsDailyAverage"]);
_DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
_DataItems.Add(_DataItem);
}
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_DataItems.Clear();
}
finally
{
_conn.Close();
}
}
return(_DataItems);
}
protected bool AddLastOrder(bool pSetDates)
{
//string _LastOrderSQL, _sFrom, _sWhere;
//string _sCustID;
bool _LastOrder = (Session[CONST_LINESADDED] != null) ? (bool)Session[CONST_LINESADDED] : false;
if (ddlContacts.SelectedValue != null)
{
SetUpdateBools();
// retrieve the last order items from the database.
long _CustID = Convert.ToInt64(ddlContacts.SelectedValue);
// if they want us to set the delivery and roast dates
if (pSetDates)
{
SetPrepAndDeliveryValues(_CustID);
}
ItemUsageTbl _ItemUsage = new ItemUsageTbl();
List <ItemUsageTbl> _LastItemsOrdered = _ItemUsage.GetLastItemsUsed(_CustID, TrackerTools.CONST_SERVTYPECOFFEE);
if (_LastItemsOrdered.Count > 0)
{
foreach (ItemUsageTbl _LastItemOrder in _LastItemsOrdered)
{
if (_LastItemOrder.ItemProvidedID > 0) // now a a order line
{
_LastOrder = (AddNewOrderLine(_LastItemOrder.ItemProvidedID, _LastItemOrder.AmountProvided, _LastItemOrder.PackagingID) || (_LastOrder));
if (!string.IsNullOrEmpty(_LastItemOrder.Notes))
{
tbxNotes.Text += String.Format("{0}last order used a group item, so next item in group selected.", (tbxNotes.Text.Length > 0) ? "; " : "");
}
}
}
}
else
{
TrackerTools _TT = new TrackerTools();
TrackerTools.ContactPreferedItems _ContactPreferedItems = _TT.RetrieveCustomerPrefs(_CustID);
_LastOrder = (AddNewOrderLine(_ContactPreferedItems.PreferedItem, _ContactPreferedItems.PreferedQty, _ContactPreferedItems.PrefPackagingID) || (_LastOrder));
}
Session[CONST_LINESADDED] = _LastOrder;
}
return(_LastOrder);
}
public ClientUsageTbl GetUsageData(long pCustomerID)
{
ClientUsageTbl _DataItem = new ClientUsageTbl();
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
try
{
OleDbCommand _cmd = new OleDbCommand(CONST_SQL_SELECT, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read())
{
_DataItem.CustomerID = pCustomerID;
_DataItem.LastCupCount = (_DataReader["LastCupCount"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["LastCupCount"]);
_DataItem.NextCoffeeBy = (_DataReader["NextCoffeeBy"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextCoffeeBy"]);
_DataItem.NextCleanOn = (_DataReader["NextCleanOn"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextCleanOn"]);
_DataItem.NextFilterEst = (_DataReader["NextFilterEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextFilterEst"]);
_DataItem.NextDescaleEst = (_DataReader["NextDescaleEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextDescaleEst"]);
_DataItem.NextServiceEst = (_DataReader["NextServiceEst"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextServiceEst"]);
_DataItem.DailyConsumption = (_DataReader["DailyConsumption"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["DailyConsumption"]);
_DataItem.FilterAveCount = (_DataReader["FilterAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["FilterAveCount"]);
_DataItem.DescaleAveCount = (_DataReader["DescaleAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["DescaleAveCount"]);
_DataItem.ServiceAveCount = (_DataReader["ServiceAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["ServiceAveCount"]);
_DataItem.CleanAveCount = (_DataReader["CleanAveCount"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["CleanAveCount"]);
}
}
catch (Exception _ex)
{
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
throw;
}
finally
{
_conn.Close();
}
}
return(_DataItem);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// load the data into the grid view
QOnT.classes.TrackerTools _TTools = new TrackerTools();
if (!_TTools.IsNextRoastDateByCityTodays())
{
_TTools.SetNextRoastDateByCity(); // The next roast days have not been calcualted, calculate them and save. When customers city is found save the details
}
List <ContactToRemind> _ContactsToRemind = GetReocurringContacts();
//QOnT.control.ClientsToReceiveReminderQry _ClientsControl = new control.ClientsToReceiveReminderQry();
//List<control.ClientsToReceiveReminderQry> _ClientsToReceiveReminder = _ClientsControl.GetCustomersToReveiveReminder("NextCoffeeBy");
//gvCustomerCheckup.DataSource = _ClientsToReceiveReminder;
//gvCustomerCheckup.DataBind();
}
}
private bool LogARepair(RepairsTbl pRepair, bool pCalcOrderData)
{
bool _Success = false;
// create a new order for delivery [RoastDate]
OrderTblData _OrderData = new OrderTblData();
DateTime _RequiredByDate = DateTime.Now.Date.AddDays(7);
// add the default data to order
_OrderData.CustomerId = pRepair.CustomerID;
_OrderData.ItemTypeID = ItemTypeTbl.CONST_SERVICEITEMID;
_OrderData.QuantityOrdered = 1;
_OrderData.Notes = "Collect/Swop out Machine for Service";
// Calculate the Data from the customer details
if (pCalcOrderData)
{
TrackerTools _TT = new TrackerTools();
_OrderData.RoastDate = _TT.GetNextRoastDateByCustomerID(pRepair.CustomerID, ref _RequiredByDate);
TrackerTools.ContactPreferedItems _ContactPreferedItems = _TT.RetrieveCustomerPrefs(pRepair.CustomerID);
_OrderData.OrderDate = DateTime.Now.Date;
_OrderData.RequiredByDate = _RequiredByDate;
if (_ContactPreferedItems.RequiresPurchOrder)
{
_OrderData.PurchaseOrder = TrackerTools.CONST_POREQUIRED;
}
_OrderData.ToBeDeliveredBy = _ContactPreferedItems.DeliveryByID;
}
else
{
_OrderData.RoastDate = _OrderData.OrderDate = DateTime.Now.Date;
_OrderData.RequiredByDate = _RequiredByDate;
}
// save the data to the orders
OrderTbl _Order = new OrderTbl();
_Order.InsertNewOrderLine(_OrderData);
pRepair.RelatedOrderID = _Order.GetLastOrderAdded(_OrderData.CustomerId, _OrderData.OrderDate, ItemTypeTbl.CONST_SERVICEITEMID);
return(_Success);
}
public bool ResetReminderCount(long pCustomerID, bool pForceEnable)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_CUSTOMERS_RESET_REMINDERCOUNT;
if (pForceEnable)
{
_sqlCmd += CONST_SQL_CUSTOMERS_RESET_REMINDERCOUNT_FORCEENABLE;
}
_sqlCmd += CONST_SQL_CUSTOMERS_RESET_REMINDERCOUNT_WHERE;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// WHERE CustomerID = ?
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID, OleDbType = OleDbType.Integer
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() >= 0);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
}
finally
{
_conn.Close();
}
}
return(_Success);
}
/*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
* // custoemr ZZname = 9 is a general customer name so condition must be to that
* string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE ";
* if (CustomerId == 9)
* _sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)";
* else
* _sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)";
* // attach the command
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
* if (CustomerId == 9)
* {
* _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
* _cmd.Parameters.Add(new OleDbParameter { Value = Notes });
* }
* else
* {
* _cmd.Parameters.Add(new OleDbParameter { Value = CustomerId });
* _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
* }
* try
* {
* _conn.Open();
* OleDbDataReader _DataReader = _cmd.ExecuteReader();
* 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();
* }
* catch (Exception _ex)
* {
* lastError = _ex.Message;
* }
* finally
* {
* _conn.Close();
* }
* return oDetails;
* }
*/
/// <summary>
/// Update Order Details, using the orderID update the line info.
/// </summary>
/// <param name="ItemTypeID"></param>
/// <param name="QuantityOrdered"></param>
/// <param name="PackagingID"></param>
/// <param name="OrderID"></param>
/// <returns></returns>
public bool UpdateOrderDetails(Int64 OrderID, Int64 CustomerID, Int32 ItemTypeID, DateTime DeliveryDate, double QuantityOrdered, Int32 PackagingID)
{
bool _Success = false;
string _sqlCmd = "UPDATE OrdersTbl SET ItemTypeID = ?, QuantityOrdered = ?, PackagingID = ? WHERE (OrderId = ?)";
TrackerDb _TDB = new TrackerDb();
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, DeliveryDate);
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_TDB.AddWhereParams(OrderID, DbType.Int64, "@OrderID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
/// <summary>
/// Update the Customer Usage (ID passed as part of ClientUsage data) to reflect new values
/// </summary>
/// <param name="pClientUsage">Client Usage data for ClientUsage.CustomerID</param>
/// <returns>blank or error strin</returns>
public bool ForceNextCoffeeDate(DateTime pNextDate, long pCustomerID)
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = CONST_SQL_FORCENEXCOFFEETBY;
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
#region Parameters
// Add data sent NextCoffeeBy
_cmd.Parameters.Add(new OleDbParameter {
Value = pNextDate, OleDbType = OleDbType.Date
});
// WHERE CustomerID = ?
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID, OleDbType = OleDbType.Integer
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() >= 0);
}
catch (OleDbException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
}
finally
{
_conn.Close();
}
}
return(_Success);
}
/// <summary>
/// Updatge the cup count for this client
/// </summary>
/// <param name="pCustomerID">client customerid</param>
/// <param name="pLastCupCount">cup count to be set</param>
/// <returns></returns>
public bool UpdateUsageCupCount(long pCustomerID, long pLastCupCount)
{
bool _Success = true;
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDotNet.classes.TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
string _sqlCmd = "UPDATE ClientUsageTbl SET LastCupCount = ? WHERE CustomerId = ?";
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // prepare the qurey we have built
#region Parameters
_cmd.Parameters.Add(new OleDbParameter {
Value = pLastCupCount
});
// Where clause
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
#endregion
try
{
_conn.Open();
_cmd.ExecuteNonQuery();
}
catch (Exception _ex)
{
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
_Success = false;
throw;
}
finally
{
_conn.Close();
}
return(_Success);
}
}
/*
****** Below are all the routine to do specific data retrival from ClientUsageLinesTBL *****
*/
/// <summary>
/// Lookup the customers Install Date, ie the first date they used us. Otherwise set to min
/// </summary>
/// <param name="pCustomerID">the customer ID whose install date you want</param>
/// <returns>the first date the customer has in record</returns>
public DateTime LookupCustomerInstallDate(long pCustomerID)
{
DateTime _GetInstallDate = DateTime.MinValue; // NULLDATE
string _sqlCmd = "SELECT MIN(ClientUsageLinesTbl.Date) FROM ClientUsageLinesTbl WHERE ClientUsageLinesTbl.CustomerID = ?";
string _connectionStr = ConfigurationManager.ConnectionStrings[TrackerDb.CONST_CONSTRING].ConnectionString;
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
try
{
_conn.Open();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
if (_DataReader.Read() && (_DataReader[0] != null))
{
_GetInstallDate = (DateTime)_DataReader[0];
}
}
catch (Exception _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
}
finally
{
_conn.Close();
}
}
return(_GetInstallDate);
}
protected void btnCreateUpdateLogTables_Click(object sender, EventArgs e)
{
TrackerTools _TT = new TrackerTools();
_TT.ClearTrackerSessionErrorString();
TrackerDb _TDB = new TrackerDb();
// create LogTbl if it does not exist
_TDB.CreateIfDoesNotExists(TrackerDb.SQLTABLENAME_LOGTBL);
ltrlStatus.Visible = true;
ltrlStatus.Text = "Log table checked";
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
// list all the users that are not added
PersonsTbl _PersonsTbl = new PersonsTbl();
List <string> _SecurityUsersNotInPeopleTbl = _PersonsTbl.SecurityUsersNotInPeopleTbl();
pnlSetClinetType.Visible = true;
gvCustomerTypes.Visible = false;
gvResults.DataSource = _SecurityUsersNotInPeopleTbl;
ResultsTitleLabel.Text = "Security Users not in People Table ";
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
gvResults.DataBind();
// create SectionTypesTbl if it does not exist
if (_TDB.CreateIfDoesNotExists(TrackerDb.SQLTABLENAME_SECTIONTYPESTBL))
{
ltrlStatus.Text += "; Section Types table checked";
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
// add sections that do
SectionTypesTbl _SectionTypeTbl = new SectionTypesTbl();
if (_SectionTypeTbl.InsertDefaultSections())
{
ltrlStatus.Text += " - default sections added.";
}
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
}
// create TransactionTypesTbl if it does not exists
if (_TDB.CreateIfDoesNotExists(TrackerDb.SQLTABLENAME_TRANSACTIONTYPESTBL))
{
ltrlStatus.Text += "; Transaction Types table checked";
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
// add Transactions that do
TransactionTypesTbl _TransactionTypeTbl = new TransactionTypesTbl();
if (_TransactionTypeTbl.InsertDefaultTransactions())
{
ltrlStatus.Text += " - default Transactions added.";
}
if (_TT.IsTrackerSessionErrorString())
{
ltrlStatus.Text += " - Error: " + _TT.GetTrackerSessionErrorString();
_TT.ClearTrackerSessionErrorString();
}
}
_TDB.Close();
}
protected void btnSetClientType_Click(object sender, EventArgs e)
{
pnlSetClinetType.Visible = true;
gvCustomerTypes.Visible = true;
List <ContactsUpdated> _ContactsUpdated = new List <ContactsUpdated>();
ClientUsageLinesTbl _ClientUsageLines = new ClientUsageLinesTbl();
ItemUsageTbl _ItemUsageTbl = new ItemUsageTbl();
ContactType _Customer = new ContactType();
string _fName = "c:\\temp\\" + String.Format("SetClientType_{0:ddMMyyyy hh mm}.txt", DateTime.Now);
_ColsStream = new StreamWriter(_fName, false); // create new file
_ColsStream.WriteLine("Task, Company Name, origType, newType, PredDisabled");
List <ContactType> _Customers = null;
int i = 0;
try
{
_Customers = _Customer.GetAllContacts("CompanyName"); // get all client sort by Company name
// _Customers.RemoveAll(x => !x.IsEnabled); // delete all the disabled clients
List <int> _CoffeeClients = GetAllCoffeeClientTypes();
List <int> _ServiceOnlyClient = GetAllServiceOnlyClientTypes();
// for each client check if they have ordered stuff and if so then set them as a particular client
while (i < _Customers.Count)
{
ContactsUpdated _Contact = new ContactsUpdated();
// only if they are enabled and not set to info only
if (_Customers[i].CustomerTypeID != CustomerTypeTbl.CONST_INFO_ONLY)
{
_Contact.ContactName = _Customers[i].CompanyName;
_Contact.ContactTypeID = _Customers[i].CustomerTypeID;
_Contact.origContactTypeID = _Customers[i].CustomerTypeID;
_Contact.PredictionDisabled = _Customers[i].PredictionDisabled;
if (_Contact.ContactTypeID == 0) // type not set then assume coffee client.
{
_Contact.ContactTypeID = CustomerTypeTbl.CONST_COFFEE_ONLY;
}
// if they are currently marked as coffee client then check if they have ordered since their install date and with in the last year
if (_CoffeeClients.Contains(_Contact.ContactTypeID))
{
_Contact = CheckCoffeeCustomerIsOne(_Customers[i], _Contact);
}
else // customer is set to only be info or something so lets check if that is true
{
_Contact = CheckNoneCoffeeCustomer(_Customers[i], _Contact);
}
/// Has it changed? is fo update
if (!_Contact.ContactTypeID.Equals(_Contact.origContactTypeID))
{
// copy the values that could have change across since C cannot clone with out a class def so we use the temp class instead
_Customers[i].CustomerTypeID = _Contact.ContactTypeID;
_Customers[i].PredictionDisabled = _Contact.PredictionDisabled;
string _Result = _Customers[i].UpdateContact(_Customers[i]);
_ContactsUpdated.Add(_Contact);
if (String.IsNullOrEmpty(_Result))
{
_ColsStream.WriteLine("Added {0}-{1}: {2}, {3}, {4}, {5}", i, _ContactsUpdated.Count, _Contact.ContactName, _Contact.origContactTypeID, _Contact.ContactTypeID, _Contact.PredictionDisabled);
}
else
{
_ColsStream.WriteLine("Error {0} Adding: {1}, {2}, {3}, {4}, {5}", _Result, i, _Result, _Contact.ContactName, _Contact.origContactTypeID, _Contact.ContactTypeID, _Contact.PredictionDisabled);
}
}
}
i++;
}
}
catch (Exception _ex)
{
string _errStr = _ex.Message;
TrackerTools _TT = new TrackerTools();
string _TTErr = _TT.GetTrackerSessionErrorString();
if (!String.IsNullOrWhiteSpace(_TTErr))
{
_errStr += " TTError: " + _TTErr;
}
showMessageBox _exMsg = new showMessageBox(this.Page, "Error", _errStr);
if (_Customers != null)
{
_ColsStream.WriteLine("ERROR AT: {0}, Name: {1}, ID: {2}, Pred: {3}", i, _Customers[i].CompanyName, _Customers[i].CustomerTypeID, _Customers[i].PredictionDisabled);
}
else
{
_ColsStream.WriteLine("null customers");
}
_ColsStream.WriteLine("Error:" + _errStr);
throw;
}
finally
{
_ColsStream.Close();
}
showMessageBox _sMsg = new showMessageBox(this.Page, "Info", String.Format("A Total of {0}, contacts were updated", _ContactsUpdated.Count));
ltrlStatus.Text = String.Format("A Total of {0}, contacts were updated", _ContactsUpdated.Count);
ltrlStatus.Visible = true;
ResultsTitleLabel.Text = "Set client type results";
gvResults.DataSource = _ContactsUpdated;
gvResults.DataBind();
// upnlSystemToolsButtons.Update();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
long _BoundCustomerId = 1;
DateTime _BoundDeliveryDate = DateTime.Now.Date;
String _BoundNotes = String.Empty;
if (Request.QueryString[CONST_QRYSTR_CUSTOMERID] != null)
{
_BoundCustomerId = Convert.ToInt32(Request.QueryString[CONST_QRYSTR_CUSTOMERID].ToString());
}
if (Request.QueryString[CONST_QRYSTR_DELIVERYDATE] != null)
{
_BoundDeliveryDate = Convert.ToDateTime(Request.QueryString[CONST_QRYSTR_DELIVERYDATE]).Date;
}
if (Request.QueryString[CONST_QRYSTR_NOTES] != null)
{
_BoundNotes = Request.QueryString[CONST_QRYSTR_NOTES].ToString();
}
Session[OrderHeaderData.CONST_BOUNDCUSTOMERID] = _BoundCustomerId;
Session[OrderHeaderData.CONST_BOUNDDELIVERYDATE] = _BoundDeliveryDate.Date;
Session[OrderHeaderData.CONST_BOUNDNOTES] = _BoundNotes;
Session[CONST_ORDERHEADERVALUES] = null;
OrderItemTbl mine_ = new OrderItemTbl();
// set the permissions
MembershipUser _currMember = Membership.GetUser();
btnOrderCancelled.Enabled = (_currMember.UserName.ToLower() == "warren");
bool _EnableNew = User.IsInRole("Administrators") || User.IsInRole("AgentManager") || User.IsInRole("Agents");
btnNewItem.Enabled = _EnableNew;
TrackerTools _TT = new TrackerTools();
_TT.SetTrackerSessionErrorString(string.Empty);
if (Request.QueryString[CONST_QRYSTR_INVOICED] != null)
{
if (Request.QueryString[CONST_QRYSTR_INVOICED].Equals("Y"))
{
MarkItemAsInvoiced();
}
}
else if (Request.QueryString[CONST_QRYSTR_DELIVERED] != null)
{
if (Request.QueryString[CONST_QRYSTR_DELIVERED].Equals("Y"))
{
btnOrderDelivered_Click(sender, e);
}
}
}
else
{
TrackerTools _TT = new TrackerTools();
string _ErrorStr = _TT.GetTrackerSessionErrorString();
if (!string.IsNullOrEmpty(_ErrorStr))
{
showMessageBox _MsgBox = new showMessageBox(this.Page, "Tracker Error", "ERROR: " + _ErrorStr);
_TT.SetTrackerSessionErrorString(string.Empty);
}
}
}
//protected void MarkTempOrdersItemsAsDone(long pCustomerID)
//{
// string _SQLUpdate = "UPDATE OrdersTbl SET OrdersTbl.Done = True WHERE CustomderId = " + pCustomerID.ToString() +
// "AND EXISTS (SELECT RequiredByDate FROM TempOrdersHeaderTbl " +
// " WHERE (RequiredByDate = OrdersTbl.RequiredByDate))";
// TrackerDb _TrackerDb = new TrackerDb();
// _TrackerDb.ExecuteNonQuerySQL(_SQLUpdate);
// // ResetCustomerReminderCount(pCustomerId);
// //'''''''''''''
// //' mark orders as done - should do this last
// //'''
// //lblStatus.Caption = "Marking orders as done"
// //dbs.Execute ("UPDATE OrdersTbl SET OrdersTbl.Done = True " + _
// // "WHERE (((Exists (Select TempOrdersTbl.OrderId from TempOrdersTbl where " + _
// // "TempOrdersTbl.OrderId = OrdersTbl.OrderId))<>False))")
// //'''''''
// //' Resetting count and enable, only if coffee item
// //'
// //dbs.Execute ("UPDATE DISTINCTROW CustomersTbl SET ReminderCount = 0, enabled = True WHERE CustomerID=" + lblCustomerID.Caption)
//}
// Logic for processing order as done:
// 1. move items consumed from temp aable to the items consumed for the contact
// 2. using the summary of the data on the form do calculations around predictions
// on when the contact needs items again.
// 3. Notify client of items deliverred (add option to contacts table)
// 4. return to the previous page.
protected void btnDone_Click(object sender, EventArgs e)
{
const long MAXQTYINSTOCK = 50;
TrackerTools _TrackerTools = new TrackerTools();
_TrackerTools.SetTrackerSessionErrorString(string.Empty);
Label _CustomerIDLabel = (Label)(fvOrderDone.FindControl("CustomerIDLabel"));
Label _CustomerNameLabel = (Label)(fvOrderDone.FindControl("CompanyNameLabel"));
long _CustomerID = Convert.ToInt64(_CustomerIDLabel.Text);
TextBox _OrderDateLabel = (TextBox)(fvOrderDone.FindControl("ByDateTextBox"));
DateTime _OrderDate = Convert.ToDateTime(_OrderDateLabel.Text);
double _CoffeeStock = 0;
// store the current data for display later:
ClientUsageTbl _OriginalUsageDAL = new ClientUsageTbl();
ClientUsageTbl _OriginalUsageData = _OriginalUsageDAL.GetUsageData(_CustomerID);
if (!string.IsNullOrEmpty(_TrackerTools.GetTrackerSessionErrorString()))
{
Response.Write(_TrackerTools.GetTrackerSessionErrorString());
}
TempOrdersDAL _TempOrdersDAL = new TempOrdersDAL(); //
bool _HasCoffeeInTempOrder = _TempOrdersDAL.HasCoffeeInTempOrder();
if (!string.IsNullOrEmpty(_TrackerTools.GetTrackerSessionErrorString()))
{
Response.Write(_TrackerTools.GetTrackerSessionErrorString());
}
/* NEED TO ADD Code to check for zzname */
// check cup count not in wrong place
if ((tbxStock.Text.Length > 0) && (Convert.ToInt64(tbxStock.Text) > MAXQTYINSTOCK))
{
ltrlStatus.Text = "<b>The stock quantity appears very high please check that you have enterred the correct value in kilograms.</b>";
}
else
{
_CoffeeStock = String.IsNullOrEmpty(tbxStock.Text) ? 0 : Convert.ToDouble(tbxStock.Text);
// First we must get the latest cup count
ltrlStatus.Text = "Calculating the latest cup count";
GeneralTrackerDbTools _GeneralTrackerDb = new GeneralTrackerDbTools();
GeneralTrackerDbTools.LineUsageData _LatestCustomerData = _GeneralTrackerDb.GetLatestUsageData(_CustomerID, TrackerTools.CONST_SERVTYPECOFFEE);
if (!string.IsNullOrEmpty(_TrackerTools.GetTrackerSessionErrorString()))
{
Response.Write(_TrackerTools.GetTrackerSessionErrorString());
}
bool _bIsActual = (tbxCount.MaxLength > 0); // if there is a cup count
long _lCupCount = 0;
if (tbxCount.MaxLength > 0)
{
_lCupCount = Convert.ToInt64(tbxCount.Text);
}
// Calculate the cup count if we do not have it on the form or they entered a value that makes no sense
if ((_lCupCount < 1) || (_lCupCount < _LatestCustomerData.LastCount))
{
ltrlStatus.Text = "Calculating the latest est cup count";
_lCupCount = _GeneralTrackerDb.CalcEstCupCount(_CustomerID, _LatestCustomerData, _HasCoffeeInTempOrder);
_bIsActual = false;
}
//' add items to consumption log
_lCupCount = AddItemsToClientUsageTbl(_CustomerID, _bIsActual, _lCupCount, _CoffeeStock, _OrderDate);
// update the last cup count for the client
if (!_OriginalUsageDAL.UpdateUsageCupCount(_CustomerID, _lCupCount))
{
ltrlStatus.Text = "error updating last count";
}
// now update the predictions
_GeneralTrackerDb.UpdatePredictions(_CustomerID, _lCupCount);
// update all items in the order table as done that are done
_TempOrdersDAL.MarkTempOrdersItemsAsDone();
// reset count and enable client
_GeneralTrackerDb.ResetCustomerReminderCount(_CustomerID, _HasCoffeeInTempOrder);
// destroy the temp table that we used to create this temp orders
_TempOrdersDAL.KillTempOrdersData();
ShowResults(_CustomerNameLabel.Text, _CustomerID, _OriginalUsageData);
}
}
