static public OverstayVioActionsDTO GetVioActionForSpaceDuringTimeRange(int customerID, int meterID, int areaID, int bayNumber, DateTime startTime, DateTime endTime)
{
if (OverstayVioActionDBManager.Instance._IsInitialized == false)
{
throw new Exception("OverstayVioActionDBManager singleton instance hasn't been initialized for database access");
}
Dictionary <string, object> sqlParams = new Dictionary <string, object>();
sqlParams.Add("@CustomerID", customerID);
sqlParams.Add("@MeterID", meterID);
//sqlParams.Add("AreaID", areaID);
sqlParams.Add("@BayNumber", bayNumber);
sqlParams.Add("@StartTime", startTime);
sqlParams.Add("@EndTime", endTime);
string query =
"SELECT UniqueKey, CustomerID, AreaID, MeterID, BayNumber, RBACUserID, EventTimestamp, ActionTaken" +
" FROM OverstayVioActions" +
" where CustomerID = @CustomerID" +
" and MeterID = @MeterID" +
" and BayNumber = @BayNumber" +
" and EventTimestamp >= @StartTime" +
" and EventTimestamp <= @EndTime" +
" order by EventTimestamp desc" +
" LIMIT 1";
DataSuppliers.SqLiteDAO sqLiteDataLayer = OverstayVioActionDBManager.Instance._DAO;
return(sqLiteDataLayer.GetSingle <OverstayVioActionsDTO>(query, sqlParams, true));
}
static public void InsertOverstayVioAction(int CustomerID, int MeterID, int AreaID, int BayNumber, int RBACUserID,
DateTime EventTimestamp, string ActionTaken)
{
if (OverstayVioActionDBManager.Instance._IsInitialized == false)
{
throw new Exception("OverstayVioActionDBManager singleton instance hasn't been initialized for database access");
}
// Set the SQL query parameters
Dictionary <string, object> sqlParams = new Dictionary <string, object>();
sqlParams.Add("CustomerID", CustomerID);
sqlParams.Add("MeterID", MeterID);
sqlParams.Add("AreaID", AreaID);
sqlParams.Add("BayNumber", BayNumber);
sqlParams.Add("RBACUserID", RBACUserID);
sqlParams.Add("EventTimestamp", EventTimestamp);
sqlParams.Add("ActionTaken", ActionTaken);
// Perform DB Insert
DataSuppliers.SqLiteDAO sqLiteDataLayer = OverstayVioActionDBManager.Instance._DAO;
Int64 autoIncValue = sqLiteDataLayer.ExecuteInsertAndReturnAutoInc(
"INSERT INTO [OverstayVioActions]" +
" (CustomerID, MeterID, AreaID, BayNumber, RBACUserID, EventTimestamp, ActionTaken)" +
" VALUES (" +
" ?, ?, ?," +
" ?, ?, ?," +
" ?" +
" )", sqlParams, true);
}
