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