Exemple #1
0
        /// <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);
        }
Exemple #2
0
        /// -----------------------------------------------
        /// 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);
        }
Exemple #3
0
        /// <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
        }
Exemple #4
0
        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;
        }