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