/// <summary> /// Remove any holidays form the date passed /// </summary> /// <param name="pCustomerID">which customer</param> /// <param name="pDT">the date to remvoe from</param> /// <param name="pServiceTypeID">for which service type Id</param> /// <returns></returns> public DateTime RemoveHolidayPeriodFromDate(long pCustomerID, DateTime pDT, int pServiceTypeID) { // select all the usage lines order DESC so that the last record is the latest ClientUsageLinesTbl _UsageDAL = new ClientUsageLinesTbl(); List <ClientUsageLinesTbl> _UsageLines = _UsageDAL.GetLast10UsageLines(pCustomerID); int _NumValues = 0; // For every holiday period int he last 6 remove the period from the data difference while ((_UsageLines.Count > _NumValues) && (_NumValues < 6)) { if (_UsageLines[_NumValues].ServiceTypeID != 0) { if ((_UsageLines[_NumValues].ServiceTypeID >= TrackerTools.CONST_SERVTYPE1WKHOLI) || (_UsageLines[_NumValues].ServiceTypeID <= TrackerTools.CONST_SERVTYPE2MTHHOLI)) { pDT = pDT.AddMonths(DaysToRemoveFromDate(_UsageLines[_NumValues].ServiceTypeID)); } else if (_UsageLines[_NumValues].ServiceTypeID == pServiceTypeID) { _NumValues = 6; } } _NumValues++; } return(pDT); }
// --- ALL Pure retrieval rotuines below, the do a simple Query and return the data, and a prefixed with Get /// <summary> /// Get the latest usage data from the client usage table /// </summary> /// <param name="pCustomerID">which customer</param> /// <param name="pServiceTypeID">which service type of data , or "" for no service type</param> /// <returns></returns> public LineUsageData GetLatestUsageData(long pCustomerID, int pServiceTypeID) { LineUsageData _LatestUsageData = new LineUsageData(); ClientUsageLinesTbl _UsageData = new ClientUsageLinesTbl(); _UsageData = _UsageData.GetLatestUsageData(pCustomerID, pServiceTypeID); if (_UsageData.CupCount > 0) { _LatestUsageData.LastCount = _UsageData.CupCount; _LatestUsageData.LastQty = _UsageData.Qty; _LatestUsageData.UsageDate = _UsageData.LineDate; } return(_LatestUsageData); }
ContactsUpdated CheckNoneCoffeeCustomer(ContactType pCustomer, ContactsUpdated pContact) { // customer is a none coffee customer check if this is correct ClientUsageLinesTbl _LatestUsageData = new ClientUsageLinesTbl(); ItemUsageTbl _LatestItemData = new ItemUsageTbl(); // the client is set to not have coffee tracked but has taken coffee check if they have taken anything else List <ItemUsageTbl> _LatestCoffeeItems = _LatestItemData.GetLastItemsUsed(pCustomer.CustomerID, TrackerTools.CONST_SERVTYPECOFFEE); _LatestItemData = _LatestItemData.GetLastMaintenanceItem(pCustomer.CustomerID); if (_LatestCoffeeItems.Count > 0) { // they ordered coffee from us but are marked as a none coffee client DateTime _InstallDate = _LatestUsageData.GetCustomerInstallDate(pCustomer.CustomerID); if (_LatestUsageData.LineDate >= _InstallDate) { // there is only coffee for the installation date if (_LatestItemData == null) { pContact.ContactTypeID = CustomerTypeTbl.CONST_INFO_ONLY; // they have no coffee or maint items so they are info only } } else { // they have take coffee past the install date if (_LatestItemData == null) { pContact.ContactTypeID = CustomerTypeTbl.CONST_COFFEE_ONLY; // they have no maint items so they are coffee only } } // else they should be set correctly } else { if (_LatestItemData == null) { pContact.ContactTypeID = CustomerTypeTbl.CONST_INFO_ONLY; // they have no coffee or main items so they are info only } } _LatestCoffeeItems.Clear(); return(pContact); }
ContactsUpdated CheckCoffeeCustomerIsOne(ContactType pCustomer, ContactsUpdated pContact) { // if the customer type is set not to be reminded about coffee ClientUsageLinesTbl _LatestUsageData = new ClientUsageLinesTbl(); ItemUsageTbl _LatestItemData = new ItemUsageTbl(); DateTime _InstallDate = DateTime.MinValue; _LatestUsageData = _LatestUsageData.GetLatestUsageData(pCustomer.CustomerID, TrackerDotNet.classes.TrackerTools.CONST_SERVTYPECOFFEE); if (_LatestUsageData != null) { _InstallDate = _LatestUsageData.GetCustomerInstallDate(pCustomer.CustomerID); if (_LatestUsageData.LineDate <= _InstallDate) { // they have not ordered since the first time, so are they a service client? _LatestItemData = _LatestItemData.GetLastMaintenanceItem(pCustomer.CustomerID); if (_LatestItemData == null) { // they have not ordered since the first order so set prediction to disabled pContact.ContactTypeID = CustomerTypeTbl.CONST_INFO_ONLY; pContact.PredictionDisabled = true; } else // they have ordered other stuff from us but not coffee { pContact.ContactTypeID = CustomerTypeTbl.CONST_SERVICE_ONLY; // set it to the first time of service ony client } } else { // they have ordered coffee have they ordered anything else, and they have been a client for long enough? if (_InstallDate.AddMonths(CONST_MINMONTHS) <= _LatestUsageData.LineDate) { // we have a client that has been ordering for a while if they have not ordered maint stuff then sert them as coffee only or green only _LatestItemData = _LatestItemData.GetLastMaintenanceItem(pCustomer.CustomerID); if (_LatestItemData == null) { pContact.ContactTypeID = CustomerTypeTbl.CONST_COFFEE_ONLY; } else { // they have ordered other stuff if they are set to coffee only set them to something else if (pContact.ContactTypeID == CustomerTypeTbl.CONST_COFFEE_ONLY) { pContact.ContactTypeID = CustomerTypeTbl.CONST_COFFEEANDMAINT; // not they will be reminded of maintenance too } } } else { } } } else { // they have no coffee in thier list are they a none coffee client? _LatestItemData = _LatestItemData.GetLastMaintenanceItem(pCustomer.CustomerID); if (_LatestItemData != null) { // they have ordered maitenance stuff but not coffee stuff before pContact.ContactTypeID = CustomerTypeTbl.CONST_SERVICE_ONLY; // set it to the first time of service ony client } else { pContact.ContactTypeID = CustomerTypeTbl.CONST_INFO_ONLY; // nothing has been ordered so set as prediction } } return(pContact); //_ColsStream.WriteLine("CheckCoffeeCustomerIsOne: 16"); }
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(); }
/// <summary>x /// Calculates the average consumption of an item, either perday or per cups consumed /// </summary> /// <param name="pCustomerID">for wich customer id</param> /// <param name="pServiceTypeID">for what type of service type default is coffee</param> /// <param name="pTypicalAverageConsumption">What is the typical average consumption to set as default</param> /// <param name="pPerDayCalc">Is this a per day or per cup calculation</param> /// <returns>average consumption of the type passed</returns> public double CalcAveConsumption(long pCustomerID, int pServiceTypeID, double pTypicalAverageConsumption, bool pPerDayCalc) { double _AveConsumption = pTypicalAverageConsumption; // average cups divided by cups used List <double> _AverageConsumptions = new List <double>(); double _ThisAverage = 0, _TotalAverages = 0; int _NumValues = 0, _DaysSincePrev = 0; int _ThisServiceTypeID = 0, _UsageLineNo = 1; // start at one since we need at leqast 2 dates to calc DateTime _PrevDate = DateTime.MinValue; // NULLDATE long _PrevCupCount = 0, _CupsSincePrev = 0; ClientUsageLinesTbl _ClientUsageLinesTbl = new ClientUsageLinesTbl(); List <ClientUsageLinesTbl> _UsageData = _ClientUsageLinesTbl.GetLast10UsageLines(pCustomerID, pServiceTypeID); // sort the data so that the first record is the oldest date is priority _UsageData.Sort((x, y) => x.LineDate.CompareTo(y.LineDate)); /// Go through the records starting at the top in descending order and calculate the average cup count excluding SwopStart and SwopStop /// and catering for holiday periods if (_UsageData.Count > 1) // must have at least to values to calcualte an average { _PrevDate = _UsageData[0].LineDate; _PrevCupCount = _UsageData[0].CupCount; while ((_UsageData.Count > _UsageLineNo) && (_NumValues < CONST_MAXROLLINGAVEVALUES)) { _ThisServiceTypeID = _UsageData[_UsageLineNo].ServiceTypeID; // Only process correctly sequenced entries - otherwise we get impossible results (remember we are reading backwards) if (_PrevCupCount < _UsageData[_UsageLineNo].CupCount) // is this bogus data so ignore it { if ((_ThisServiceTypeID == pServiceTypeID) && (_UsageData[_UsageLineNo].LineDate > _PrevDate)) { // we are at the next record so cacl difference _CupsSincePrev = (_UsageData[_UsageLineNo].CupCount - _PrevCupCount); // number of cups consumed if (pPerDayCalc) { _DaysSincePrev = (_UsageData[_UsageLineNo].LineDate - _PrevDate).Days; // number of days between types _ThisAverage = Math.Round((double)(_CupsSincePrev / _DaysSincePrev), 5); _AverageConsumptions.Add(_ThisAverage); // round to the nearest 5th decimal } else { _AverageConsumptions.Add(_CupsSincePrev); // round to the nearest 5th decimal } // now add an average per day _NumValues++; _PrevDate = _UsageData[_UsageLineNo].LineDate; _PrevCupCount = _UsageData[_UsageLineNo].CupCount; } // Check if they were on holiday and deduct that from total days else if ((_ThisServiceTypeID >= TrackerTools.CONST_SERVTYPE1WKHOLI) && (_ThisServiceTypeID <= TrackerTools.CONST_SERVTYPE2MTHHOLI)) { _PrevDate = _PrevDate.AddDays(DaysToAddToDate(_ThisServiceTypeID)); } } _UsageLineNo++; // move to next record } // while // now if there are more than 5 values kill the smallest if (_AverageConsumptions.Count >= CONST_MAXROLLINGAVEVALUES) { double _MaxAve = _AverageConsumptions[0]; for (int i = 1; i < _AverageConsumptions.Count; i++) { if (_AverageConsumptions[i] > _MaxAve) { _MaxAve = _AverageConsumptions[i]; } } _AverageConsumptions.Remove(_MaxAve); } // now calc the Average _TotalAverages = 0; for (int i = 0; i < _AverageConsumptions.Count; i++) { _TotalAverages += _AverageConsumptions[i]; } if (_TotalAverages > 0) { _AveConsumption = Math.Round(_TotalAverages / _AverageConsumptions.Count, 3); } } else { if (pPerDayCalc) { _AveConsumption = TrackerTools.CONST_TYPICALAVECONSUMPTION; } else { _AveConsumption = pTypicalAverageConsumption; } } // check for negative or 0 if (_AveConsumption <= 0) { _AveConsumption = pTypicalAverageConsumption; } return(_AveConsumption); }
/// <summary> /// Get the Installation date is assumed to be the date of the customer's first record in the usage line table. Added for compatiblity reasons /// </summary> /// <param name="pCustomerID">customer ID</param> /// <returns>The install date</returns> public DateTime GetInstallDate(long pCustomerID) { ClientUsageLinesTbl _ClientUsageTbl = new ClientUsageLinesTbl(); return(_ClientUsageTbl.LookupCustomerInstallDate(pCustomerID)); }
/// <summary> /// Add the actual items to the Usage table and Usage Detail Line from the temp tables checking that the customer /// Id is the one that the temp tables are populated with. Also exclude any n/a service types /// </summary> /// <param name="pCustomerID">for which customer</param> /// <param name="pIsActual">is this an actual count</param> /// <param name="pCupCount">what is teh starting CupCount</param> /// <returns>Cup Count</returns> private long AddItemsToClientUsageTbl(long pCustomerID, bool pIsActual, long pCupCount, double pStock, DateTime pDeliveryDate) { ClientUsageFromTempOrder _ClientUsageFromTempOrderDAL = new ClientUsageFromTempOrder(); List <ClientUsageFromTempOrder> _TempOrderDataLines = _ClientUsageFromTempOrderDAL.GetAll(pCustomerID); // create null data records for the tables we are going to populate List <ItemUsageTbl> _ItemUsageLines = new List <ItemUsageTbl>(); List <ClientUsageLinesTbl> _ClientUsageLines = new List <ClientUsageLinesTbl>(); int _LineNo = 0; // use the note to make comments in the item usage table string _strNotes = (pIsActual) ? "actual count" : "estimate count"; if (pStock > 0) { pCupCount = pCupCount - Convert.ToInt64(pStock * TrackerTools.CONST_TYPICALNUMCUPSPERKG); // adjust cup count so that it reflects stock _strNotes += "; Stock of: " + pCupCount.ToString(); } // For every holiday period int he last 6 remove the period from the data difference while (_TempOrderDataLines.Count > _LineNo) { // _strNotes = ""; // clear last notes, ready for new notes ClientUsageLinesTbl _ClientUsageItem = new ClientUsageLinesTbl(); // now calc per item a total _ClientUsageItem.CustomerID = _TempOrderDataLines[_LineNo].CustomerID; _ClientUsageItem.LineDate = pDeliveryDate; _ClientUsageItem.ServiceTypeID = _TempOrderDataLines[_LineNo].ServiceTypeID; _ClientUsageItem.Qty = 0; _ClientUsageItem.CupCount = pCupCount; _ClientUsageItem.Notes = _strNotes; do { // add all quantities for this service type _ClientUsageItem.Qty += (_TempOrderDataLines[_LineNo].Qty * _TempOrderDataLines[_LineNo].UnitsPerQty); ItemUsageTbl _ItemUsageItem = new ItemUsageTbl(); // copy line item to an new line in item usage _ItemUsageItem.CustomerID = _TempOrderDataLines[_LineNo].CustomerID; _ItemUsageItem.Date = pDeliveryDate; _ItemUsageItem.ItemProvided = _TempOrderDataLines[_LineNo].ItemID; _ItemUsageItem.AmountProvided = _TempOrderDataLines[_LineNo].Qty; _ItemUsageItem.PackagingID = _TempOrderDataLines[_LineNo].PackagingID; _ItemUsageItem.Notes = _strNotes; _ItemUsageLines.Add(_ItemUsageItem); _LineNo++; } while ((_TempOrderDataLines.Count > _LineNo) && (_ClientUsageItem.ServiceTypeID == _TempOrderDataLines[_LineNo].ServiceTypeID)); _ClientUsageLines.Add(_ClientUsageItem); } // add client usage lineslines for (int i = 0; i < _ClientUsageLines.Count; i++) { _ClientUsageLines[i].InsertItemsUsed(_ClientUsageLines[i]); } // add item usage lineslines for (int i = 0; i < _ItemUsageLines.Count; i++) { _ItemUsageLines[i].InsertItemsUsed(_ItemUsageLines[i]); } return(pCupCount); }