// 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); }