Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

Rev 23 | Rev 31 | 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 
    }
161 
 
162 
 
163 
 
23 efrain 164 
    func getCountByUserUuid(userUuid : String) -> Int {
19 efrain 165 
        let db = database.open()
23 efrain 166 
        var query = "SELECT COUNT(*) AS total FROM " + Constants.TABLE_USER_NOTIFICATION
167 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 168 
 
169 
        var statement : OpaquePointer? = nil
170 
        var count = 0;
171 
 
172 
 
173 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
174 
            if(sqlite3_step(statement) == SQLITE_ROW){
175 
                count = Int(sqlite3_column_int(statement, 0))
11 efrain 176 
                //print("\(count)")
1 efrain 177 
            }
178 
        } else {
179 
            count = -1
19 efrain 180 
            database.printError()
1 efrain 181 
        }
182 
        sqlite3_finalize(statement)
183 
        return count
184 
    }
185 
 
186 
    func insert(userNotification : UserNotificationModel) -> Int {
19 efrain 187 
        let db = database.open()
1 efrain 188 
        var result : Int = 0
189 
        var query = "INSERT INTO " + Constants.TABLE_USER_NOTIFICATION + " ( "
190 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
191 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 192 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 193 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + ", "
194 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + ", "
25 efrain 195 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + ", "
23 efrain 196 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + ", "
197 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
25 efrain 198 
        query = query + " ) VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
1 efrain 199 
        var statement : OpaquePointer?
200 
 
201 
 
202 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
203 
 
22 efrain 204 
            sqlite3_bind_text(statement, 1, userNotification.userUuid, -1, SQLITE_TRANSIENT)
1 efrain 205 
            sqlite3_bind_text(statement, 2, userNotification.title, -1 , SQLITE_TRANSIENT)
23 efrain 206 
            sqlite3_bind_text(statement, 3, userNotification.body , -1, SQLITE_TRANSIENT)
1 efrain 207 
            sqlite3_bind_text(statement, 4, userNotification.url , -1, SQLITE_TRANSIENT)
208 
            sqlite3_bind_int(statement, 5, Int32(userNotification.viewed))
25 efrain 209 
            sqlite3_bind_text(statement, 6, userNotification.command, -1, SQLITE_TRANSIENT)
210 
            sqlite3_bind_text(statement, 7, userNotification.dateOn, -1, SQLITE_TRANSIENT)
211 
            sqlite3_bind_text(statement, 8, userNotification.timeOn , -1, SQLITE_TRANSIENT)
23 efrain 212 
 
1 efrain 213 
            if (sqlite3_step(statement) == SQLITE_DONE) {
214 
                result = Int(sqlite3_last_insert_rowid(db))
215 
            } else {
216 
                 print("No se pudo insertar el registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 217 
 
218 
                database.printError()
1 efrain 219 
 
220 
            }
221 
        } else {
222 
            print("Fallo la preparación del insertar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 223 
 
224 
            database.printError()
1 efrain 225 
        }
226 
 
227 
        sqlite3_finalize(statement)
228 
 
229 
        return result
230 
    }
231 
 
232 
    func markViewed(id : Int) {
19 efrain 233 
        let db = database.open()
1 efrain 234 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
25 efrain 235 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + "  1 "
1 efrain 236 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)';"
237 
        var statement : OpaquePointer? = nil
238 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
239 
            if (sqlite3_step(statement) != SQLITE_DONE) {
240 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 241 
 
242 
                database.printError()
1 efrain 243 
            }
244 
        } else {
245 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 246 
 
247 
            database.printError()
1 efrain 248 
        }
249 
        sqlite3_finalize(statement)
250 
    }
251 
 
23 efrain 252 
    func markViewedAllPendingByUserUuid(userUuid : String) {
19 efrain 253 
        let db = database.open()
1 efrain 254 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
25 efrain 255 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + "  1 "
23 efrain 256 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 0 "
257 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 258 
        var statement : OpaquePointer? = nil
259 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
260 
            if (sqlite3_step(statement) != SQLITE_DONE) {
261 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 262 
 
263 
                database.printError()
1 efrain 264 
            }
265 
        } else {
266 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 267 
 
268 
            database.printError()
1 efrain 269 
        }
270 
        sqlite3_finalize(statement)
271 
    }
25 efrain 272 
 
273 
    func markViewedAllPendingByUserUuidAndCommand(userUuid : String, command : String) {
274 
        let db = database.open()
275 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
276 
        query = query + " SET " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + "  1 "
277 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 0 "
278 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
279 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_COMMAND + " = '\(command)'; "
280 
        var statement : OpaquePointer? = nil
281 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
282 
            if (sqlite3_step(statement) != SQLITE_DONE) {
283 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
284 
 
285 
                database.printError()
286 
            }
287 
        } else {
288 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
289 
 
290 
            database.printError()
291 
        }
292 
        sqlite3_finalize(statement)
293 
    }
1 efrain 294 
 
295 
    func remove(id: Int) {
19 efrain 296 
        let db = database.open()
1 efrain 297 
        let query = "DELETE FROM " + Constants.TABLE_USER_NOTIFICATION
298 
            + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)' ;"
299 
        var statement : OpaquePointer? = nil
300 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
301 
            if (sqlite3_step(statement) != SQLITE_DONE) {
302 
                print("No se pudo borrar el registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 303 
 
304 
                database.printError()
1 efrain 305 
            }
306 
        } else {
307 
            print("Fallo la preparación del borrado del registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" )
19 efrain 308 
 
309 
            database.printError()
1 efrain 310 
        }
311 
        sqlite3_finalize(statement)
312 
    }
313 
 
314 
 
23 efrain 315 
 
17 efrain 316 
    func removeAllUserUuidNotEqual(userUuid : String)
317 
    {
19 efrain 318 
        let db = database.open()
17 efrain 319 
        let query = "DELETE FROM " +  Constants.TABLE_USER_NOTIFICATION +
320 
            " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " <> '\(userUuid)' ;"
321 
        var statement : OpaquePointer? = nil
322 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
323 
            if sqlite3_step(statement) != SQLITE_DONE {
324 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" +
325 
                " de los usuarios diferentes de : \(userUuid)" )
19 efrain 326 
 
327 
                database.printError()
17 efrain 328 
            }
329 
        } else {
330 
            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 331 
 
332 
            database.printError()
17 efrain 333 
        }
334 
        sqlite3_finalize(statement)
335 
    }
336 
 
1 efrain 337 
}
338