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