PrepAndDeliveryData GetPreAndDeliveryDate(int pIdx, int pCityID, List <CityPrepDaysTbl> pCityPrepDays, DateTime pForThisDate)
{
PrepAndDeliveryData _PrepAndDeliveryData = new PrepAndDeliveryData();
byte _ThisDatesDOW = (byte)pForThisDate.DayOfWeek;
int _CityStartIndex = pCityPrepDays.FindIndex(pIdx, x => x.CityID == pCityID); // should be the same value as pIdx
if (_CityStartIndex > -1)
{
byte _FoundDoW = GetCorrectedDOW(pCityPrepDays[_CityStartIndex].PrepDayOfWeekID); // default is next week
int _FoundDeliveryDelay = pCityPrepDays[_CityStartIndex].DeliveryDelayDays;
int _FoundSortOrder = pCityPrepDays[_CityStartIndex].DeliveryOrder;
// now see if there is a DoW after this one and if so use that as the next prep date
int _CityEndIndex = pCityPrepDays.FindIndex(_CityStartIndex, x => x.CityID != pCityID);
if (_CityEndIndex > -1)
{
// we now have the start and the end of the CityPrepDays
// now find the first occurance that the day of week is larger to the one in the list, we want the one before
int _CityDoWIndex = pCityPrepDays.FindIndex(_CityStartIndex, _CityEndIndex - _CityStartIndex, x => GetCorrectedDOW(x.PrepDayOfWeekID) >= _ThisDatesDOW);
if (_CityDoWIndex > -1)
{
_FoundDoW = GetCorrectedDOW(pCityPrepDays[_CityDoWIndex].PrepDayOfWeekID);
_FoundDeliveryDelay = pCityPrepDays[_CityDoWIndex].DeliveryDelayDays;
_FoundSortOrder = pCityPrepDays[_CityDoWIndex].DeliveryOrder;
}
}
if (_FoundDoW >= _ThisDatesDOW)
{
_PrepAndDeliveryData.PrepDate = pForThisDate.AddDays(_FoundDoW - _ThisDatesDOW);
}
else
{
_PrepAndDeliveryData.PrepDate = pForThisDate.AddDays((int)(7 - _ThisDatesDOW + _FoundDoW));
}
_PrepAndDeliveryData.DeliveryDate = _PrepAndDeliveryData.PrepDate.AddDays(_FoundDeliveryDelay); // _NextDeliveryDelay = _ThisDeliveryDelay;
_PrepAndDeliveryData.SortOrder = _FoundSortOrder;
}
return(_PrepAndDeliveryData);
#region OldDOWCode
//int _FirstDeliveryDelay, _FirstSortOrder;
//byte _ThisPDOW = (byte)System.DayOfWeek.Monday; // monday is the first day of the week in C# while in VBA it was Sunday
//int _ThisDeliveryDelay = 0, _ThisSortOrder = 0;
//int _NextDeliveryDelay = 0;
//byte _ThisDatesDOW = (byte)pForThisDate.DayOfWeek;
//bool _bFirst, _bFound, _EndOfList = false;
//_EndOfList = (_CityIndex == -1);
//while (!_EndOfList)
//{
// // set the first item of this collection
// _FirstPDOW = GetCorrectedDOW(pCityPrepDays[_CityIndex].PrepDayOfWeekID);
// // set the dow of week depending on what is the first day of the week
// _FirstDeliveryDelay = pCityPrepDays[_CityIndex].DeliveryDelayDays;
// _FirstSortOrder = pCityPrepDays[_CityIndex].DeliveryOrder;
// _bFound = false;
// // find this cities next roast day, search until city changes or end of file
// while ((!_EndOfList) && (!_bFound))
// {
// _ThisPDOW = GetCorrectedDOW(pCityPrepDays[_CityIndex].PrepDayOfWeekID);
// _ThisDeliveryDelay = pCityPrepDays[_CityIndex].DeliveryDelayDays;
// _ThisSortOrder = pCityPrepDays[_CityIndex].DeliveryOrder;
// // go to next record exit if we are on another city
// _CityIndex++;
// _EndOfList = (_CityIndex <= pCityPrepDays.Count);
// if (!_EndOfList)
// _bFound = ((_ThisDatesDOW <= _ThisPDOW) || (pCityID != pCityPrepDays[_CityIndex].CityID)); // 'perhaps a time check may be needed?
// }
// if (_EndOfList)
// _bFirst = true;
// else
// {
// _bFirst = ((pCityID != pCityPrepDays[_CityIndex].CityID) && (_ThisDatesDOW > _ThisPDOW));
// // go to the next City ID, if we get here then we have found a prep day for this City so we must skip ahead
// if (pCityID == pCityPrepDays[_CityIndex].CityID) // we have found a day that is after the next day
// _EndOfList = true; // set _EOF to true since we are finished with this city
// else if (!_bFound)
// _bFirst = true; // must be the first record
// }
// if (_bFirst) // this means there are no pre days this week
// {
// if (_FirstPDOW >= _ThisDatesDOW)
// _PrepAndDeliveryData.PrepDate = pForThisDate.AddDays(_FirstPDOW - _ThisDatesDOW);
// else
// _PrepAndDeliveryData.PrepDate = pForThisDate.AddDays((int)(7 - _ThisDatesDOW + _FirstPDOW));
// _NextDeliveryDelay = _FirstDeliveryDelay;
// _PrepAndDeliveryData.SortOrder = _FirstSortOrder;
// } // use the first
// else
// {
// _PrepAndDeliveryData.PrepDate = DateTime.Now.AddDays(_ThisPDOW - _ThisDatesDOW);
// _NextDeliveryDelay = _ThisDeliveryDelay;
// _PrepAndDeliveryData.SortOrder = _ThisSortOrder;
// }
// _PrepAndDeliveryData.DeliveryDate = _PrepAndDeliveryData.PrepDate.AddDays(_NextDeliveryDelay);
//}
//return _PrepAndDeliveryData;
#endregion
}
/// <summary> Set the next roast Date by city, notice this forces the calculation so if you do not want to run it every time check if
/// IsNextRoastDateByCityTodays, i.e. has it been calcualted already </summary>
public void SetNextRoastDateByCity()
{
CityPrepDaysTbl _CityPrepDaysDAL = new CityPrepDaysTbl();
List <CityPrepDaysTbl> _CityPrepDaysTbl = _CityPrepDaysDAL.GetAll("CityID, PrepDayOfWeekID");
int _CurrentCityID = int.MinValue;
DateTime _NextDate = DateTime.MinValue;
PrepAndDeliveryData _ThisPrepAndDeliveryData = new PrepAndDeliveryData();
PrepAndDeliveryData _NextPrepAndDeliveryData = new PrepAndDeliveryData();
// should make this a system preference?
DateTime _ThisDate = DateTime.Now.Date;
if (_ThisDate.Hour >= 14)
{
_ThisDate = _ThisDate.AddDays(1);
}
int i = 0;
while (i < _CityPrepDaysTbl.Count)
{
_CurrentCityID = _CityPrepDaysTbl[i].CityID;
_ThisPrepAndDeliveryData = GetPreAndDeliveryDate(i, _CurrentCityID, _CityPrepDaysTbl, _ThisDate);
_NextDate = (_ThisPrepAndDeliveryData.PrepDate == _ThisPrepAndDeliveryData.DeliveryDate) ? _ThisPrepAndDeliveryData.PrepDate.AddDays(1).Date : _ThisPrepAndDeliveryData.DeliveryDate.Date;
_NextPrepAndDeliveryData = GetPreAndDeliveryDate(i, _CurrentCityID, _CityPrepDaysTbl, _NextDate);
UpdateOrInsertCityNextRstDate(_CurrentCityID, _ThisPrepAndDeliveryData, _NextPrepAndDeliveryData);
// go to next city
i++;
while ((i < _CityPrepDaysTbl.Count) && (_CurrentCityID == _CityPrepDaysTbl[i].CityID))
{
i++;
}
}
// tell the system table we are done
TrackerDb _TrackerDb = new TrackerDb();
List <DBParameter> _Params = new List <DBParameter>();
_Params.Add(new DBParameter {
DataValue = DateTime.Now.Date, DataDbType = DbType.Date
});
_TrackerDb.ExecuteNonQuerySQLWithParams("UPDATE SysDataTbl SET DateLastPrepDateCalcd = ? WHERE ID=1", _Params);
_TrackerDb.Close();
#region OldSetCode
//string _sqlStr = "SELECT CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder FROM CityPrepDaysTbl ORDER BY CityID, PrepDayOfWeekID";
//int _iCityID = 0;
//byte _FirstPDOW;
//short _FirstDeliveryDelay, _FirstSortOrder;
//byte _ThisPDOW = (byte)System.DayOfWeek.Monday ; // monday is the first day of the week in C# while in VBA it was Sunday
//short _ThisDeliveryDelay = 0, _ThisSortOrder = 0;
//short _NextDeliveryDelay = 0, _NextSortOrder = 0;
//// DateTime dtNow = System.DateTime.Now;
//DateTime _dtPrep, _DeliveryDate;
//// NOTE for the database Sunday = 1 for System.DateTime.Now.DayOfWeek Sunday = 0; so this need to be remembers when doing coparisons and calculations
//byte _TodaysDOW = (byte)System.DateTime.Now.DayOfWeek;
//// (byte)((byte)System.DateTime.Now.DayOfWeek+1);
////if (_TodaysDOW > 7) _TodaysDOW = 1; // fix Sunday;
//bool _bFirst, _bFound, _bIsSameCity = false, _EOF = false;
//// open a database connection if it is not open already
//if (TToolCon == null)
// OpenTToolsConnection();
//OleDbCommand _Cmd = new OleDbCommand(_sqlStr, TToolCon);
//OleDbDataReader _Reader = _Cmd.ExecuteReader();
//// count how many cites there are
//// List <NextRoastDateByCityTbl> _NRDbyCities = new List<NextRoastDateByCityTbl>(); // that would be the max
//// read the data if it is there
//// while (_Reader.Read())
//_EOF = !_Reader.Read();
//while (!_EOF)
//{
// _iCityID = (int)_Reader["CityID"];
// // set the first item of this collection
// _FirstPDOW = GetCorrectedDOW((byte)_Reader["PrepDayOfWeekID"]);
// // set the dow of week depending on what is the first day of the week
// _FirstDeliveryDelay = (short)_Reader["DeliveryDelayDays"] ;
// _FirstSortOrder = (short) ((_Reader["DeliveryOrder"] == DBNull.Value) ? 1 : _Reader["DeliveryOrder"]);
// _bFound = false;
// // find this cities next roast day, search until city changes or end of file
// while ((!_EOF) && (!_bFound))
// {
// _ThisPDOW = GetCorrectedDOW((byte)_Reader["PrepDayOfWeekID"]);
// _ThisDeliveryDelay = (short)_Reader["DeliveryDelayDays"];
// _ThisSortOrder = (short)((_Reader["DeliveryOrder"] == DBNull.Value) ? 1 : _Reader["DeliveryOrder"]);
// // go to next record exit if we are on another city
// _EOF = !_Reader.Read();
// if (!_EOF)
// _bFound = ((_TodaysDOW <= _ThisPDOW) || (_iCityID != (int)_Reader["CityID"])); // ' time check?
// }
// //
// if (! _EOF) {
// _bFirst = ((_iCityID != (int)_Reader["CityID"]) && (_TodaysDOW > _ThisPDOW));
// // go to the next City ID, if we get here then we have found a prep day for this City so we must skip ahead
// _bIsSameCity = (_iCityID == (int)_Reader["CityID"]);
// if (_bIsSameCity) // we have found a day that is after the next day
// {
// while ((!_EOF) && (_bIsSameCity))
// {
// _EOF = ! _Reader.Read();
// if (!_EOF)
// _bIsSameCity = (_iCityID == (int)_Reader["CityID"]);
// }
// } // not eof
// else if (!_bFound)
// _bFirst = true; // must be the first record
// if (_bFirst) // this means there are no pre days this week
// {
// if (_FirstPDOW >= _ThisPDOW)
// _dtPrep = DateTime.Now.AddDays(_FirstPDOW - _TodaysDOW);
// else
// _dtPrep = DateTime.Now.AddDays((int)(7 - _TodaysDOW + _FirstPDOW ));
// _NextDeliveryDelay = _FirstDeliveryDelay;
// _NextSortOrder = _FirstSortOrder;
// } // use the first
// else
// {
// _dtPrep = DateTime.Now.AddDays(_ThisPDOW - _TodaysDOW);
// _NextDeliveryDelay = _ThisDeliveryDelay;
// _NextSortOrder = _ThisSortOrder;
// } // use the next
// // insert or udpate the records depending on whether or not the records exists.
// _DeliveryDate = _dtPrep.AddDays(_NextDeliveryDelay);
// bool _Success = UpdateOrInsertCityNextRstDate(_iCityID, _dtPrep, _DeliveryDate, _NextSortOrder); // may want do do something here
// }
//} // end loop while
//_Reader.Close();
//_Cmd.CommandText = "UPDATE SysDataTbl SET DateLastPrepDateCalcd = " + DateTime.Now.ToShortDateString() + " WHERE ID=1";
//_Cmd.ExecuteNonQuery();
//// kill memory used
//_Cmd.Dispose();
//_Reader.Close();
//_Reader.Dispose();
#endregion
}
private string UpdateOrInsertCityNextRstDate(int pCityID, PrepAndDeliveryData pThisPrepAndDeliveryData, PrepAndDeliveryData pNextPrepAndDeliveryData) // (int pCityID, DateTime pPrepDate, DateTime pDeliveryDate, short pOrder)
{
string _result = String.Empty;
NextRoastDateByCityTbl _NextRoastDateByCityTbl = new NextRoastDateByCityTbl();
_NextRoastDateByCityTbl.CityID = pCityID;
_NextRoastDateByCityTbl.PrepDate = pThisPrepAndDeliveryData.PrepDate;
_NextRoastDateByCityTbl.DeliveryDate = pThisPrepAndDeliveryData.DeliveryDate;
_NextRoastDateByCityTbl.DeliveryOrder = pThisPrepAndDeliveryData.SortOrder;
_NextRoastDateByCityTbl.NextPrepDate = pNextPrepAndDeliveryData.PrepDate;
_NextRoastDateByCityTbl.NextDeliveryDate = pNextPrepAndDeliveryData.DeliveryDate;
TrackerDb _TrackerDb = new TrackerDb();
IDataReader _Reader = _TrackerDb.ExecuteSQLGetDataReader("SELECT CityID FROM NextRoastDateByCityTbl WHERE CityID = " + pCityID);
if ((_Reader != null) && (_Reader.Read()))
{
_result = _NextRoastDateByCityTbl.UpdatePrepDataForCity(pCityID, _NextRoastDateByCityTbl);
_Reader.Close();
}
else
{
_result = _NextRoastDateByCityTbl.InsertPrepDataForCity(_NextRoastDateByCityTbl);
}
_TrackerDb.Close();
return(_result);
//bool _Success = false;
//string _sqlStr = "SELECT CityID FROM NextRoastDateByCityTbl WHERE CityID = " + pCityID;
//OleDbCommand _Cmd = new OleDbCommand(_sqlStr, TToolCon);
//OleDbDataReader _Reader = _Cmd.ExecuteReader();
//// if a record exists update command otherwise insert
//if (_Reader.Read())
// _sqlStr = CITYNEXTRT_UPDATE;
//else
// _sqlStr = CITYNEXTRT_INS;
//// close the reader
//_Reader.Close();
//_Cmd.CommandText = _sqlStr; // Set the SQL string
//_Cmd.Parameters.Add(new OleDbParameter { Value = pPrepDate, DbType = System.Data.DbType.Date });
//_Cmd.Parameters.Add(new OleDbParameter { Value = pDeliveryDate, DbType = System.Data.DbType.Date });
//_Cmd.Parameters.Add(new OleDbParameter { Value = pOrder, DbType = System.Data.DbType.Int16 });
//_Cmd.Parameters.Add(new OleDbParameter { Value = pCityID, DbType = System.Data.DbType.Int32 });
//try
//{
// _Success = _Cmd.ExecuteNonQuery() > 0;
//}
//catch (Exception E)
//{
// if (E.Message != "")
// throw;
//}
//finally
//{
// _Cmd.Dispose();
//}
//return _Success;
}
