Ejemplo n.º 1
0
        public List <ReoccuringOrderTbl> GetAll(bool pOnlyEnabled, string SortBy)
        {
            List <ReoccuringOrderTbl> _DataItems = new List <ReoccuringOrderTbl>();
            //      string _connectionStr = CONST_CONSTRING;
            TrackerDb _TDB = new TrackerDb();

            //using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
            //{
            string _sqlCmd = CONST_SQL_SELECT;

            if (pOnlyEnabled)
            {
                _sqlCmd += " WHERE ([Enabled] = true) ";                             // add where if required
            }
            if (!String.IsNullOrEmpty(SortBy))
            {
                _sqlCmd += " ORDER BY " + SortBy;                                    // Add order by string
            }
            //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);                    // run the qurey we have built
            //_conn.Open();
            //OleDbDataReader _DataReader = _cmd.ExecuteReader();

            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);

            while (_DataReader.Read())
            {
                ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl();

                _DataItem.ID               = (_DataReader["ID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ID"]);
                _DataItem.CustomerID       = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
                _DataItem.ReoccuranceType  = (_DataReader["ReoccuranceType"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ReoccuranceType"]);
                _DataItem.ReoccuranceValue = (_DataReader["Value"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["Value"]);
                _DataItem.ItemRequiredID   = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]);
                _DataItem.QtyRequired      = (_DataReader["QtyRequired"] == DBNull.Value) ? 0.0 : Convert.ToDouble(_DataReader["QtyRequired"]);
                _DataItem.DateLastDone     = (_DataReader["DateLastDone"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["DateLastDone"]);
                _DataItem.NextDateRequired = (_DataReader["NextDateRequired"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["NextDateRequired"]);
                _DataItem.RequireUntilDate = (_DataReader["RequireUntilDate"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["RequireUntilDate"]);
                _DataItem.Enabled          = (_DataReader["Enabled"] == DBNull.Value) ? false : Convert.ToBoolean(_DataReader["Enabled"]);
                _DataItem.Notes            = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString();
                _DataItems.Add(_DataItem);
            }
            _DataReader.Close();
            _TDB.Close();

            //        }
            return(_DataItems);
        }
Ejemplo n.º 2
0
        public bool SetReoccuringItemsLastDate()
        {
            bool _Success = false;
            //string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString; ;
            List <ReoccuringOrderTbl> _ReoccuringOrderItems = new List <ReoccuringOrderTbl>();
            TrackerDb _TDB = new TrackerDb();
            //using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
            //{
            string _sqlCmd = CONST_SQL_GETITEMSLASTDATE;
            // not needed if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy;     // Add order by string
            //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);                    // run the qurey we have built
            //_conn.Open();
            // read all the data from the query and post it into the DataList
            //OleDbDataReader _DataReader = _cmd.ExecuteReader();
            IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);

            while (_DataReader.Read())
            {
                ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl();

                _DataItem.CustomerID     = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]);
                _DataItem.ItemRequiredID = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]);
                _DataItem.DateLastDone   = (_DataReader["LastDatePerItem"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastDatePerItem"]);
                _ReoccuringOrderItems.Add(_DataItem);
            }  // while

            //now update the table
            //_conn.Close();
            //_conn.Dispose();
            //_cmd.Dispose();

            _DataReader.Close();
            _TDB.Close();
            //}
            for (int i = 0; i < _ReoccuringOrderItems.Count; i++)
            {
                //using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
                //{
                _TDB = new TrackerDb();

                //OleDbCommand _cmd = new OleDbCommand(CONST_UPDATE_ITEMSLASTDATE, _conn);
                #region Parameters
                // Add data SET data DateLastDone
                //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].DateLastDone });
                _TDB.AddParams(_ReoccuringOrderItems[i].DateLastDone, DbType.Date);
                // Where data
                //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].CustomerID });
                //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].ItemRequiredID });
                _TDB.AddWhereParams(_ReoccuringOrderItems[i].CustomerID, DbType.Int32);
                _TDB.AddWhereParams(_ReoccuringOrderItems[i].ItemRequiredID, DbType.Int32);
                #endregion
                _Success = _TDB.ExecuteNonQuerySQL(CONST_UPDATE_ITEMSLASTDATE) == string.Empty;
                //try
                //{
                //    _conn.Open();
                //    _Success = (_cmd.ExecuteNonQuery() >= 0);
                //}
                //catch (OleDbException oleErr)
                //{ _Success = oleErr.ErrorCode != 0; }
                //finally
                //{
                //    _conn.Close();
                //    _conn.Dispose();
                //    _cmd.Dispose();
                //}
                _TDB.Close();
            }

            return(_Success);
        }