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