/// <summary>
/// Retrieve the customer's defaults as per the ClientTbl
/// </summary>
/// <param name="_CustID">the customer id</param>
/// <returns>Contact / Customers prefered items for ID passed</returns>
public ContactPreferedItems RetrieveCustomerPrefs(long _CustID)
{
// using custonmer id query the customers table to get the customers preferences.
// prep the TTools connection, if it has not already been preped
TrackerDb _TDB = new TrackerDb();
ContactPreferedItems _ContactPreferedItems = new ContactPreferedItems(_CustID);
// get the customers City
string _sqlStr = "SELECT CustomersTbl.PreferedAgent, CustomersTbl.CoffeePreference, CustomersTbl.PriPrefQty, CustomersAccInfoTbl.RequiresPurchOrder " +
" FROM (CustomersTbl LEFT OUTER JOIN CustomersAccInfoTbl ON CustomersTbl.CustomerID = CustomersAccInfoTbl.CustomerID) WHERE CustomersTbl.CustomerID = " + _CustID.ToString();
IDataReader _Reader = _TDB.ExecuteSQLGetDataReader(_sqlStr);
if (_Reader != null)
{
if (_Reader.Read())
{
_ContactPreferedItems.DeliveryByID = (_Reader["PreferedAgent"] == DBNull.Value) ? CONST_DEFAULT_DELIVERYBYID : (int)_Reader["PreferedAgent"];
_ContactPreferedItems.PreferedItem = (_Reader["CoffeePreference"] == DBNull.Value) ? 0 : (int)_Reader["CoffeePreference"];
_ContactPreferedItems.PreferedQty = (_Reader["PriPrefQty"] == DBNull.Value) ? 1 : Convert.ToDouble(_Reader["PriPrefQty"]);
_ContactPreferedItems.RequiresPurchOrder = (_Reader["RequiresPurchOrder"] == DBNull.Value) ? false : (bool)(_Reader["RequiresPurchOrder"]);
}
_Reader.Close();
}
_TDB.Close();
return(_ContactPreferedItems);
}
/// -----------------------------------------------
/// Tools that use the database
/// -----------------------------------------------
/// <summary>Has Next Roast Date by City be calculated today? </summary>
/// <returns>Yes if it has been</return s>
public bool IsNextRoastDateByCityTodays()
{
bool _hasBeenDone = false;
TrackerDb _TrackerDb = new TrackerDb();
IDataReader _DBReader = _TrackerDb.ExecuteSQLGetDataReader("SELECT DateLastPrepDateCalcd FROM SysDataTbl WHERE (ID = 1)");
if ((_DBReader != null) && (_DBReader.Read()))
{
// should make this a system preference?
DateTime _ThisDate = DateTime.Now.Date;
if (_ThisDate.Hour >= 14)
{
_ThisDate = _ThisDate.AddDays(1);
}
_hasBeenDone = (_ThisDate.Date == Convert.ToDateTime(_DBReader["DateLastPrepDateCalcd"].ToString()).Date);
}
_DBReader.Close();
//if (TToolCon == null)
// OpenTToolsConnection();
//// query the system table to find out if todays next roast date has been calculated
//string _sqlStr = "SELECT DateLastPrepDateCalcd FROM SysDataTbl WHERE (ID = 1)";
//OleDbCommand _Cmd = new OleDbCommand(_sqlStr, TToolCon);
//OleDbDataReader _Reader = _Cmd.ExecuteReader();
//if (_Reader.Read())
// _hasBeenDone = (DateTime.Now.Date == Convert.ToDateTime(_Reader["DateLastPrepDateCalcd"].ToString()).Date);
//// kill memory used
//_Cmd.Dispose();
//_Reader.Close();
//_Reader.Dispose();
// return result
return(_hasBeenDone);
}
/// <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;
}