public List <ReoccuringOrderTbl> GetAll(bool pOnlyEnabled, string SortBy)
{
List <ReoccuringOrderTbl> _DataItems = new List <ReoccuringOrderTbl>();
string _connectionStr = CONST_CONSTRING;
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();
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);
}
}
return(_DataItems);
}
public bool SetReoccuringItemsLastDate()
{
bool _Success = false;
string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString;;
List <ReoccuringOrderTbl> _ReoccuringOrderItems = new List <ReoccuringOrderTbl>();
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();
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();
}
for (int i = 0; i < _ReoccuringOrderItems.Count; i++)
{
using (OleDbConnection _conn = new OleDbConnection(_connectionStr))
{
OleDbCommand _cmd = new OleDbCommand(CONST_UPDATE_ITEMSLASTDATE, _conn);
#region Parameters
// Add data SET data DateLastDone
_cmd.Parameters.Add(new OleDbParameter {
Value = _ReoccuringOrderItems[i].DateLastDone
});
// Where data
_cmd.Parameters.Add(new OleDbParameter {
Value = _ReoccuringOrderItems[i].CustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = _ReoccuringOrderItems[i].ItemRequiredID
});
#endregion
try
{
_conn.Open();
_Success = (_cmd.ExecuteNonQuery() >= 0);
}
catch (OleDbException oleErr)
{ _Success = oleErr.ErrorCode != 0; }
finally
{
_conn.Close();
_conn.Dispose();
_cmd.Dispose();
}
}
}
return(_Success);
}