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