Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

Rev 31 | Rev 44 | Ir a la última revisión | | Comparar con el anterior | Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 1 
//
2 
//  UserNotificationDao.swift
3 
//  twogetskills
4 
//
5 
//  Created by Efrain Yanez Recanatini on 7/31/22.
6 
//
7 
 
8 
 
9 
import UIKit
10 
import SQLite3
11 
 
12 
class UserNotificationDao {
13 
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
19 efrain 14 
    private var database = Database.sharedInstance
1 efrain 15 
 
16 
    static let sharedInstance: UserNotificationDao = {
17 
           let instance = UserNotificationDao()
18 
 
19 
           // setup code
20 
           return instance
21 
    }()
22 
 
23 
    func selectById(id : Int)-> UserNotificationModel {
19 efrain 24 
        let db = database.open()
1 efrain 25 
        var model = UserNotificationModel ()
26 
 
27 
        var query = "SELECT "
28 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + ", "
29 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
30 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 31 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 32 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " , "
33 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " , "
25 efrain 34 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " , "
23 efrain 35 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " , "
36 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
1 efrain 37 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
38 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)'  LIMIT 1;"
39 
 
40 
        var statement : OpaquePointer? = nil
41 
 
42 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
43 
            if (sqlite3_step(statement) == SQLITE_ROW) {
44 
 
45 
 
46 
                model.id = Int(sqlite3_column_int(statement, 0))
47 
 
22 efrain 48 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
1 efrain 49 
 
50 
                model.title = String(describing: String(cString: sqlite3_column_text(statement, 2)))
51 
 
23 efrain 52 
                model.body = String(describing: String(cString: sqlite3_column_text(statement, 3)))
1 efrain 53 
 
23 efrain 54 
                model.url = String(describing: String(cString: sqlite3_column_text(statement, 4)))
1 efrain 55 
 
23 efrain 56 
                model.viewed = Int(sqlite3_column_int(statement, 5))
1 efrain 57 
 
25 efrain 58 
                model.command = String(describing: String(cString: sqlite3_column_text(statement, 6)))
1 efrain 59 
 
60 
 
25 efrain 61 
                model.dateOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
23 efrain 62 
 
25 efrain 63 
                model.timeOn = String(describing: String(cString: sqlite3_column_text(statement, 8)))
64 
 
65 
 
11 efrain 66 
               //print("\nSuccessfully get record")
1 efrain 67 
 
68 
            }
19 efrain 69 
        } else {
70 
            database.printError()
1 efrain 71 
        }
72 
        sqlite3_finalize(statement)
73 
        return model
74 
    }
23 efrain 75 
 
76 
    func selectAllDistinctDateByUserUuid(userUuid : String) -> [String] {
77 
        let db = database.open()
78 
        var records = [String]()
79 
 
80 
        var query = "SELECT "
81 
        query = query + " DISTINCT(" + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + ") "
82 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
83 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
84 
        query = query + " ORDER BY " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " DESC ;"
85 
 
86 
        var statement : OpaquePointer? = nil
87 
 
88 
 
89 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
90 
            while (sqlite3_step(statement) == SQLITE_ROW) {
91 
 
92 
                let date = String(describing: String(cString: sqlite3_column_text(statement, 0)))
93 
 
94 
                records.append(date)
95 
 
96 
            }
97 
        } else {
98 
            database.printError()
99 
        }
100 
        sqlite3_finalize(statement)
101 
        return records
102 
    }
1 efrain 103 
 
104 
 
23 efrain 105 
    func selectAllByUserUuidAndDate(userUuid : String, date : String)-> [UserNotificationModel] {
19 efrain 106 
        let db = database.open()
1 efrain 107 
        var records = [UserNotificationModel]()
108 
 
109 
        var query = "SELECT "
110 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + ", "
111 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
112 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 113 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 114 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " , "
115 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " , "
25 efrain 116 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " , "
23 efrain 117 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " , "
118 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
1 efrain 119 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
23 efrain 120 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
121 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " = '\(date)'"
122 
        query = query + " ORDER BY  " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " DESC,  "
123 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON + " DESC; "
1 efrain 124 
        var statement : OpaquePointer? = nil
125 
 
126 
 
127 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
128 
            while (sqlite3_step(statement) == SQLITE_ROW) {
129 
 
130 
                var  model = UserNotificationModel()
131 
                model.id = Int(sqlite3_column_int(statement, 0))
132 
 
22 efrain 133 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
1 efrain 134 
 
135 
                model.title = String(describing: String(cString: sqlite3_column_text(statement, 2)))
136 
 
23 efrain 137 
                model.body = String(describing: String(cString: sqlite3_column_text(statement, 3)))
1 efrain 138 
 
23 efrain 139 
                model.url = String(describing: String(cString: sqlite3_column_text(statement, 4)))
1 efrain 140 
 
23 efrain 141 
                model.viewed = Int(sqlite3_column_int(statement, 5))
1 efrain 142 
 
25 efrain 143 
                model.command = String(describing: String(cString: sqlite3_column_text(statement, 6)))
1 efrain 144 
 
25 efrain 145 
 
146 
                model.dateOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
147 
 
148 
                model.timeOn = String(describing: String(cString: sqlite3_column_text(statement, 8)))
23 efrain 149 
 
150 
 
1 efrain 151 
 
152 
                records.append(model)
153 
 
154 
            }
19 efrain 155 
        } else {
156 
            database.printError()
1 efrain 157 
        }
158 
        sqlite3_finalize(statement)
159 
        return records
160 
    }
31 efrain 161 
 
162 
    func selectAllByUserUuid(userUuid : String)-> [UserNotificationModel] {
163 
        let db = database.open()
164 
        var records = [UserNotificationModel]()
165 
 
166 
        var query = "SELECT "
167 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + ", "
168 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
169 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
170 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
171 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " , "
172 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " , "
173 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " , "
174 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " , "
175 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
176 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
177 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
40 efrain 178 
        query = query + " ORDER BY  " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " || 'T' || "
31 efrain 179 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON + " DESC; "
180 
        var statement : OpaquePointer? = nil
181 
 
182 
 
183 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
184 
            while (sqlite3_step(statement) == SQLITE_ROW) {
185 
 
186 
                var  model = UserNotificationModel()
187 
                model.id = Int(sqlite3_column_int(statement, 0))
188 
 
189 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
190 
 
191 
                model.title = String(describing: String(cString: sqlite3_column_text(statement, 2)))
192 
 
193 
                model.body = String(describing: String(cString: sqlite3_column_text(statement, 3)))
194 
 
195 
                model.url = String(describing: String(cString: sqlite3_column_text(statement, 4)))
196 
 
197 
                model.viewed = Int(sqlite3_column_int(statement, 5))
198 
 
199 
                model.command = String(describing: String(cString: sqlite3_column_text(statement, 6)))
200 
 
201 
 
202 
                model.dateOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
203 
 
204 
                model.timeOn = String(describing: String(cString: sqlite3_column_text(statement, 8)))
1 efrain 205 
 
31 efrain 206 
 
207 
 
208 
                records.append(model)
209 
 
210 
            }
211 
        } else {
212 
            database.printError()
213 
        }
214 
        sqlite3_finalize(statement)
215 
        return records
216 
    }
1 efrain 217 
 
218 
 
31 efrain 219 
 
220 
 
221 
 
23 efrain 222 
    func getCountByUserUuid(userUuid : String) -> Int {
19 efrain 223 
        let db = database.open()
23 efrain 224 
        var query = "SELECT COUNT(*) AS total FROM " + Constants.TABLE_USER_NOTIFICATION
225 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 226 
 
227 
        var statement : OpaquePointer? = nil
228 
        var count = 0;
229 
 
230 
 
231 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
232 
            if(sqlite3_step(statement) == SQLITE_ROW){
233 
                count = Int(sqlite3_column_int(statement, 0))
11 efrain 234 
                //print("\(count)")
1 efrain 235 
            }
236 
        } else {
237 
            count = -1
19 efrain 238 
            database.printError()
1 efrain 239 
        }
240 
        sqlite3_finalize(statement)
241 
        return count
242 
    }
243 
 
244 
    func insert(userNotification : UserNotificationModel) -> Int {
19 efrain 245 
        let db = database.open()
1 efrain 246 
        var result : Int = 0
247 
        var query = "INSERT INTO " + Constants.TABLE_USER_NOTIFICATION + " ( "
248 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
249 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 250 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 251 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + ", "
252 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + ", "
25 efrain 253 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + ", "
23 efrain 254 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + ", "
255 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
25 efrain 256 
        query = query + " ) VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
1 efrain 257 
        var statement : OpaquePointer?
258 
 
259 
 
260 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
261 
 
22 efrain 262 
            sqlite3_bind_text(statement, 1, userNotification.userUuid, -1, SQLITE_TRANSIENT)
1 efrain 263 
            sqlite3_bind_text(statement, 2, userNotification.title, -1 , SQLITE_TRANSIENT)
23 efrain 264 
            sqlite3_bind_text(statement, 3, userNotification.body , -1, SQLITE_TRANSIENT)
1 efrain 265 
            sqlite3_bind_text(statement, 4, userNotification.url , -1, SQLITE_TRANSIENT)
266 
            sqlite3_bind_int(statement, 5, Int32(userNotification.viewed))
25 efrain 267 
            sqlite3_bind_text(statement, 6, userNotification.command, -1, SQLITE_TRANSIENT)
268 
            sqlite3_bind_text(statement, 7, userNotification.dateOn, -1, SQLITE_TRANSIENT)
269 
            sqlite3_bind_text(statement, 8, userNotification.timeOn , -1, SQLITE_TRANSIENT)
23 efrain 270 
 
1 efrain 271 
            if (sqlite3_step(statement) == SQLITE_DONE) {
272 
                result = Int(sqlite3_last_insert_rowid(db))
273 
            } else {
274 
                 print("No se pudo insertar el registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 275 
 
276 
                database.printError()
1 efrain 277 
 
278 
            }
279 
        } else {
280 
            print("Fallo la preparación del insertar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 281 
 
282 
            database.printError()
1 efrain 283 
        }
284 
 
285 
        sqlite3_finalize(statement)
286 
 
287 
        return result
288 
    }
289 
 
290 
    func markViewed(id : Int) {
19 efrain 291 
        let db = database.open()
1 efrain 292 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
25 efrain 293 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + "  1 "
1 efrain 294 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)';"
295 
        var statement : OpaquePointer? = nil
296 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
297 
            if (sqlite3_step(statement) != SQLITE_DONE) {
298 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 299 
 
300 
                database.printError()
1 efrain 301 
            }
302 
        } else {
303 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 304 
 
305 
            database.printError()
1 efrain 306 
        }
307 
        sqlite3_finalize(statement)
308 
    }
309 
 
23 efrain 310 
    func markViewedAllPendingByUserUuid(userUuid : String) {
19 efrain 311 
        let db = database.open()
1 efrain 312 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
25 efrain 313 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + "  1 "
23 efrain 314 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 0 "
315 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 316 
        var statement : OpaquePointer? = nil
317 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
318 
            if (sqlite3_step(statement) != SQLITE_DONE) {
319 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 320 
 
321 
                database.printError()
1 efrain 322 
            }
323 
        } else {
324 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 325 
 
326 
            database.printError()
1 efrain 327 
        }
328 
        sqlite3_finalize(statement)
329 
    }
25 efrain 330 
 
331 
    func markViewedAllPendingByUserUuidAndCommand(userUuid : String, command : String) {
332 
        let db = database.open()
333 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
31 efrain 334 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 1 "
25 efrain 335 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 0 "
336 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
337 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " = '\(command)'; "
338 
        var statement : OpaquePointer? = nil
339 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
340 
            if (sqlite3_step(statement) != SQLITE_DONE) {
341 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
342 
 
343 
                database.printError()
344 
            }
345 
        } else {
346 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
347 
 
348 
            database.printError()
349 
        }
350 
        sqlite3_finalize(statement)
351 
    }
1 efrain 352 
 
353 
    func remove(id: Int) {
19 efrain 354 
        let db = database.open()
1 efrain 355 
        let query = "DELETE FROM " + Constants.TABLE_USER_NOTIFICATION
356 
            + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)' ;"
357 
        var statement : OpaquePointer? = nil
358 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
359 
            if (sqlite3_step(statement) != SQLITE_DONE) {
360 
                print("No se pudo borrar el registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 361 
 
362 
                database.printError()
1 efrain 363 
            }
364 
        } else {
365 
            print("Fallo la preparación del borrado del registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" )
19 efrain 366 
 
367 
            database.printError()
1 efrain 368 
        }
369 
        sqlite3_finalize(statement)
370 
    }
371 
 
372 
 
23 efrain 373 
 
17 efrain 374 
    func removeAllUserUuidNotEqual(userUuid : String)
375 
    {
19 efrain 376 
        let db = database.open()
17 efrain 377 
        let query = "DELETE FROM " +  Constants.TABLE_USER_NOTIFICATION +
378 
            " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " <> '\(userUuid)' ;"
379 
        var statement : OpaquePointer? = nil
380 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
381 
            if sqlite3_step(statement) != SQLITE_DONE {
382 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" +
383 
                " de los usuarios diferentes de : \(userUuid)" )
19 efrain 384 
 
385 
                database.printError()
17 efrain 386 
            }
387 
        } else {
388 
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_NOTIFICATION) de los usuarios diferentes de : \(userUuid) ")
19 efrain 389 
 
390 
            database.printError()
17 efrain 391 
        }
392 
        sqlite3_finalize(statement)
393 
    }
394 
 
1 efrain 395 
}
396