// public double CalcDailyAverageForServiceItem(double pTypicalAvePerItem, int pServiceTypeID)
// {
#region OldVBACodeCalc
// Dim rstClientUsageLines As Recordset
// Dim strSQL As String
// Dim dt As Date
// Dim iLastCount, iCurrCount, iLastQty, iQty As Long
// Dim dtCurr As Date
// 'Find the latest record with service type = "Filter"
//' GetNextRequireDate = ConstNullDate
// strSQL = "SELECT TOP 6 ClientUsageLinesTbl.Date, ClientUsageLinesTbl.CupCount, ClientUsageLinesTbl.Qty "
// strSQL = strSQL + " FROM ClientUsageLinesTbl"
// strSQL = strSQL + " WHERE ClientUsageLinesTbl.CustomerID = " + pCustomerID
// strSQL = strSQL + " AND ClientUsageLinesTbl.ServiceTypeID = " + sType
// strSQL = strSQL + " ORDER BY ClientUsageLinesTbl.Date DESC"
// Set rstClientUsageLines = CurrentDb.OpenRecordset(strSQL)
// ' set the variables to default so that
// nAve = 0
// iCurrCount = 0
// dtCurr = ConstNullDate
// ' Get the initial data if available
// If Not rstClientUsageLines.EOF Then
// rstClientUsageLines.MoveFirst ' Order is DESC
// iLastCount = rstClientUsageLines("CupCount")
// iCurrCount = iLastCount
// dtCurr = rstClientUsageLines("Date")
// iLastQty = rstClientUsageLines("Qty")
// If IsNull(iLastQty) Then
// iLastQty = 1
// End If
// Else
// ' Default Value is a rough estimate when they will need this type
// nAve = iDefVal
// End If
// ' take the last cup count and deducting the current count, dividing by the last qty calculate the
// ' difference and then roll the average
// Do While Not rstClientUsageLines.EOF
// 'Calc a rolling average
// If nAve = 0 Then
// nAve = (iLastCount - rstClientUsageLines("CupCount")) / iLastQty
// Else
// nAve = (nAve + (iLastCount - rstClientUsageLines("CupCount")) / iLastQty) / 2
// End If
// iLastCount = rstClientUsageLines("CupCount")
// iLastQty = rstClientUsageLines("Qty")
// If IsNull(iLastQty) Then
// iLastQty = 1
// End If
// rstClientUsageLines.MoveNext
// Loop
// ' At this point we either have a average or zeros
// If nAve = 0 Then
// ' if 0- then one record was found, so set it to the last qty
// nAve = iLastCount / iLastQty
// End If
// ' if CurrCount is still zero there where no records, so next required date is initial date + nAve
// If iCurrCount = 0 Then
// dtCurr = LookupInstallDate(pCustomerID)
// End If
// ' Now either we have the default value or an average
// dtCurr = DateAdd("d", Round(nAve / nCupAve, 0), dtCurr)
//' d = dtCurr
// CalcNextRequiredDate = RemoveTimePortion(dtCurr)
// ' close the record set
// rstClientUsageLines.Close
#endregion
// return _Average;
// }
/// <summary>
/// Set the service data for this client
/// </summary>
/// <param name="pCustomerID"></param>
/// <param name="pTrackedServiceItemData"></param>
/// <param name="pDailyAverage"></param>
/// <returns></returns>
private ClientServiceItem SetClientServiceData(long pCustomerID, TrackedServiceItemTbl pTrackedServiceItemData, double pDailyAverage)
{
int _DaysLeftForItem;
bool _IsPerDayCalc = (pDailyAverage == 0);
ClientServiceItem _ClientServiceItem = new ClientServiceItem();
_ClientServiceItem.UsageDateFieldName = pTrackedServiceItemData.UsageDateFieldName;
_ClientServiceItem.UsageAveFieldName = pTrackedServiceItemData.UsageAveFieldName;
_ClientServiceItem.ThisItemsAverage =
CalcAveConsumption(pCustomerID, pTrackedServiceItemData.ServiceTypeID, pTrackedServiceItemData.TypicalAvePerItem, _IsPerDayCalc);
/*NextDate for item is equal to LastDate + DaysLeftOfItemUse;
* DaysLeftOfItemUse = (QtyLastProvided X TypicalUsePerKg) / KgDailConsumption */
LineUsageData _LatestData = GetLatestUsageData(pCustomerID, pTrackedServiceItemData.ServiceTypeID); // get the last record in the database
// check for minb date, and allocate the result to install date
if (_LatestData.UsageDate == DateTime.MinValue)
{
_LatestData.UsageDate = GetInstallDate(pCustomerID);
}
//// xxx
// this is wrong we need to calculate the days left so we need to times qty by typical use per qty and then divide by average
// daysleft = (lastQty * lastAveForItem) / ThisItemsAverage
if (_IsPerDayCalc)
{
_DaysLeftForItem = (int)Math.Round(((_LatestData.LastQty * TrackerTools.CONST_TYPICALNUMCUPSPERKG) / _ClientServiceItem.ThisItemsAverage), 0);
}
else
{
_DaysLeftForItem = (int)Math.Round((_LatestData.LastQty * _ClientServiceItem.ThisItemsAverage) / pDailyAverage, 0);
}
// set the date deepending on holidays
_ClientServiceItem.NextUsageDate = AddHolidayExtension(pCustomerID, _LatestData.UsageDate.AddDays(_DaysLeftForItem), pTrackedServiceItemData.ServiceTypeID);
return(_ClientServiceItem);
}
/// <summary>
/// Generic Function to get the next date of a serivce type, depending on average cup count and last date an qty added
/// </summary>
/// <param name="pCustomerID">ID of the customer to check/param>
/// <param name="iDefDate">default date value</param>
/// <param name="pServiceType">the Service Type to Check</param>
/// <returns>NextRequireDate of an item</returns>
/// <remarks>
/// Logic: using the customer see if the service type has been consumed, if so calculate the average between each date of use
/// otherwise use the AverageConsumption and the install date to guess the next date. Return that date
/// </remarks>
public bool CalcAndSaveNextRequiredDates(long pCustomerID) // , long pLastCupCount)
{
bool _Success = false;
#region LogicCalcAndSave
/*
* logic:
*
* 1. retrieve the list of items from TrackedServiceItemTbl, so that the ThisItemSetsDailyAverage is at the top an the others are below
* for each item:
* 2. retrieve the last item of this service type provided.
*
* 3a. if the date it was last provided > install date, then:
* calculate the average use for that item and set the next_date_required it to the last_date + days to go
* if this is serviceitemcoffee then set daily average variable to be used for general averages
* 3b. else
* using the typical average for the item add that to install date and using the daily average calculate when next will need
* if this date is < now make it now
*
* 4. for each item save this to the summary table for the client, or at least save eac hitem to a list then use the list to
* create an update command to update all items for this customer as per the fileds retrieved.
*
*/
#endregion
// 1. Retrieve list of TrackedServiceItemTbl, so that the Theh Item that SetsDailyAverage is at the top
TrackedServiceItemTbl _TrackedServiceItemTbl = new TrackedServiceItemTbl();
List <TrackedServiceItemTbl> _TrackerServiceItems = _TrackedServiceItemTbl.GetAll("ThisItemSetsDailyAverage, ServiceTypeID");
double _DailyAverage = TrackerTools.CONST_TYPICALAVECONSUMPTION;
int _ServiceItemNum = 0;
List <ClientServiceItem> _ClientServiceItems = new List <ClientServiceItem>();
// do the primary item
while (_TrackerServiceItems[_ServiceItemNum].ThisItemSetsDailyAverage) // should be only one but just in case
{
ClientServiceItem _ClientServiceItem = SetClientServiceData(pCustomerID, _TrackerServiceItems[_ServiceItemNum], 0); // 0 means set Daily Average
_DailyAverage = _ClientServiceItem.ThisItemsAverage;
_ClientServiceItems.Add(_ClientServiceItem);
_ServiceItemNum++;
}
// now do the secpndary items
while (_ServiceItemNum < _TrackerServiceItems.Count) // should be pnly one bu just in case
{
ClientServiceItem _ClientServiceItem = SetClientServiceData(pCustomerID, _TrackerServiceItems[_ServiceItemNum], _DailyAverage);
_ClientServiceItems.Add(_ClientServiceItem);
_ServiceItemNum++;
}
//
#region OldVBACodeCalc
//_NumDays = (int)Math.Round((200 * _LastCleanUpdateData.LastQty) / _AveConsump);
//if (_LastCleanUpdateData.UsageDate.CompareTo(DateTime.MinValue) // NULLDATE == 0)
// _dtNextClean.AddDays(_NumDays);
//// Adjust for holidays if neccessary
//_dtNextClean = AddHolidayExtension(pCustomerID, _dtNextClean, TrackerTools.CONST_STRING_SERVTYPECLEAN)
// // lblStatus.Caption = "Doing next filter, decal and service calculations"
//// //Do FitlerDate calc
// // if they are clients the require us to predict when they will need fitlers or decalificaiton
// // tablets then get the Last Date
// dtNextFilter = CalcNextRequiredDate(pCustomerID, 300, AveConsump, CONST_SERVTYPEFilter, nFilterAve)
// dtNextDescal = CalcNextRequiredDate(pCustomerID, 350, AveConsump, CONST_SERVTYPEDescale, nDescaleAve)
// dtNextService = CalcNextRequiredDate(pCustomerID, 5500, AveConsump, CONST_SERVTYPEService, nServiceAve)
// // LastServiceDate = LookupLastServiceDate()
// // if there has already been a filter then use average otherwise assume 220 as the cup count
//Else
// // do default values
// If dtInstall <> ConstNullDate Then
// dtNextCoffee = DateAdd("d", 14, dtInstall)
// dtNextClean = DateAdd("d", 28, dtInstall) // Assume it is at least 28 days away
#endregion
/* 4. for each item save this to the summary table for the client, or at least save eac hitem to a list then use the list to
* create an update command to update all items for this customer as per the fileds retrieved.*/
// check if a record exists in the client usage table
ClientUsageTbl _ClientUsageTbl = new ClientUsageTbl();
if (_ClientUsageTbl.UsageDataExists(pCustomerID))
{
_Success = UpdateClientUsageTable(pCustomerID, _ClientServiceItems);
}
else
{
_Success = InsertClientUsageTable(pCustomerID, _ClientServiceItems);
}
return(_Success);
}