Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

Rev 22 | Rev 25 | 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 + " , "
23 efrain 34 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " , "
35 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
1 efrain 36 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
37 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)'  LIMIT 1;"
38 
 
39 
        var statement : OpaquePointer? = nil
40 
 
41 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
42 
            if (sqlite3_step(statement) == SQLITE_ROW) {
43 
 
44 
 
45 
                model.id = Int(sqlite3_column_int(statement, 0))
46 
 
22 efrain 47 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
1 efrain 48 
 
49 
                model.title = String(describing: String(cString: sqlite3_column_text(statement, 2)))
50 
 
23 efrain 51 
                model.body = String(describing: String(cString: sqlite3_column_text(statement, 3)))
1 efrain 52 
 
23 efrain 53 
                model.url = String(describing: String(cString: sqlite3_column_text(statement, 4)))
1 efrain 54 
 
23 efrain 55 
                model.viewed = Int(sqlite3_column_int(statement, 5))
1 efrain 56 
 
23 efrain 57 
                model.dateOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
1 efrain 58 
 
23 efrain 59 
                model.timeOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
1 efrain 60 
 
23 efrain 61 
 
11 efrain 62 
               //print("\nSuccessfully get record")
1 efrain 63 
 
64 
            }
19 efrain 65 
        } else {
66 
            database.printError()
1 efrain 67 
        }
68 
        sqlite3_finalize(statement)
69 
        return model
70 
    }
23 efrain 71 
 
72 
    func selectAllDistinctDateByUserUuid(userUuid : String) -> [String] {
73 
        let db = database.open()
74 
        var records = [String]()
75 
 
76 
        var query = "SELECT "
77 
        query = query + " DISTINCT(" + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + ") "
78 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
79 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
80 
        query = query + " ORDER BY " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " DESC ;"
81 
 
82 
        var statement : OpaquePointer? = nil
83 
 
84 
 
85 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
86 
            while (sqlite3_step(statement) == SQLITE_ROW) {
87 
 
88 
                let date = String(describing: String(cString: sqlite3_column_text(statement, 0)))
89 
 
90 
                records.append(date)
91 
 
92 
            }
93 
        } else {
94 
            database.printError()
95 
        }
96 
        sqlite3_finalize(statement)
97 
        return records
98 
    }
1 efrain 99 
 
100 
 
23 efrain 101 
    func selectAllByUserUuidAndDate(userUuid : String, date : String)-> [UserNotificationModel] {
19 efrain 102 
        let db = database.open()
1 efrain 103 
        var records = [UserNotificationModel]()
104 
 
105 
        var query = "SELECT "
106 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + ", "
107 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
108 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 109 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 110 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + " , "
111 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " , "
23 efrain 112 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " , "
113 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
1 efrain 114 
        query = query + " FROM " + Constants.TABLE_USER_NOTIFICATION
23 efrain 115 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
116 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " = '\(date)'"
117 
        query = query + " ORDER BY  " + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + " DESC,  "
118 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON + " DESC; "
1 efrain 119 
        var statement : OpaquePointer? = nil
120 
 
121 
 
122 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
123 
            while (sqlite3_step(statement) == SQLITE_ROW) {
124 
 
125 
                var  model = UserNotificationModel()
126 
                model.id = Int(sqlite3_column_int(statement, 0))
127 
 
22 efrain 128 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
1 efrain 129 
 
130 
                model.title = String(describing: String(cString: sqlite3_column_text(statement, 2)))
131 
 
23 efrain 132 
                model.body = String(describing: String(cString: sqlite3_column_text(statement, 3)))
1 efrain 133 
 
23 efrain 134 
                model.url = String(describing: String(cString: sqlite3_column_text(statement, 4)))
1 efrain 135 
 
23 efrain 136 
                model.viewed = Int(sqlite3_column_int(statement, 5))
1 efrain 137 
 
23 efrain 138 
                model.dateOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
1 efrain 139 
 
23 efrain 140 
                model.timeOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
141 
 
142 
 
1 efrain 143 
 
144 
                records.append(model)
145 
 
146 
            }
19 efrain 147 
        } else {
148 
            database.printError()
1 efrain 149 
        }
150 
        sqlite3_finalize(statement)
151 
        return records
152 
    }
153 
 
154 
 
155 
 
23 efrain 156 
    func getCountByUserUuid(userUuid : String) -> Int {
19 efrain 157 
        let db = database.open()
23 efrain 158 
        var query = "SELECT COUNT(*) AS total FROM " + Constants.TABLE_USER_NOTIFICATION
159 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 160 
 
161 
        var statement : OpaquePointer? = nil
162 
        var count = 0;
163 
 
164 
 
165 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
166 
            if(sqlite3_step(statement) == SQLITE_ROW){
167 
                count = Int(sqlite3_column_int(statement, 0))
11 efrain 168 
                //print("\(count)")
1 efrain 169 
            }
170 
        } else {
171 
            count = -1
19 efrain 172 
            database.printError()
1 efrain 173 
        }
174 
        sqlite3_finalize(statement)
175 
        return count
176 
    }
177 
 
178 
    func insert(userNotification : UserNotificationModel) -> Int {
19 efrain 179 
        let db = database.open()
1 efrain 180 
        var result : Int = 0
181 
        var query = "INSERT INTO " + Constants.TABLE_USER_NOTIFICATION + " ( "
182 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + ", "
183 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TITLE + ", "
23 efrain 184 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_BODY + ", "
1 efrain 185 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_URL + ", "
186 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + ", "
23 efrain 187 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_DATE_ON + ", "
188 
        query = query + Constants.TABLE_USER_NOTIFICATION_FIELD_TIME_ON
189 
        query = query + " ) VALUES (?, ?, ?, ?, ?, ?, ?);"
1 efrain 190 
        var statement : OpaquePointer?
191 
 
192 
 
193 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
194 
 
22 efrain 195 
            sqlite3_bind_text(statement, 1, userNotification.userUuid, -1, SQLITE_TRANSIENT)
1 efrain 196 
            sqlite3_bind_text(statement, 2, userNotification.title, -1 , SQLITE_TRANSIENT)
23 efrain 197 
            sqlite3_bind_text(statement, 3, userNotification.body , -1, SQLITE_TRANSIENT)
1 efrain 198 
            sqlite3_bind_text(statement, 4, userNotification.url , -1, SQLITE_TRANSIENT)
199 
            sqlite3_bind_int(statement, 5, Int32(userNotification.viewed))
23 efrain 200 
            sqlite3_bind_text(statement, 6, userNotification.dateOn, -1, SQLITE_TRANSIENT)
201 
            sqlite3_bind_text(statement, 7, userNotification.timeOn , -1, SQLITE_TRANSIENT)
202 
 
1 efrain 203 
            if (sqlite3_step(statement) == SQLITE_DONE) {
204 
                result = Int(sqlite3_last_insert_rowid(db))
205 
            } else {
206 
                 print("No se pudo insertar el registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 207 
 
208 
                database.printError()
1 efrain 209 
 
210 
            }
211 
        } else {
212 
            print("Fallo la preparación del insertar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 213 
 
214 
            database.printError()
1 efrain 215 
        }
216 
 
217 
        sqlite3_finalize(statement)
218 
 
219 
        return result
220 
    }
221 
 
222 
    func markViewed(id : Int) {
19 efrain 223 
        let db = database.open()
1 efrain 224 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
225 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)';"
226 
        var statement : OpaquePointer? = nil
227 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
228 
            if (sqlite3_step(statement) != SQLITE_DONE) {
229 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 230 
 
231 
                database.printError()
1 efrain 232 
            }
233 
        } else {
234 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 235 
 
236 
            database.printError()
1 efrain 237 
        }
238 
        sqlite3_finalize(statement)
239 
    }
240 
 
23 efrain 241 
    func markViewedAllPendingByUserUuid(userUuid : String) {
19 efrain 242 
        let db = database.open()
1 efrain 243 
        var query = "UPDATE " + Constants.TABLE_USER_NOTIFICATION
23 efrain 244 
        query = query + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_VIEWED + " = 0 "
245 
        query = query + " AND " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " = '\(userUuid)'; "
1 efrain 246 
        var statement : OpaquePointer? = nil
247 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
248 
            if (sqlite3_step(statement) != SQLITE_DONE) {
249 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 250 
 
251 
                database.printError()
1 efrain 252 
            }
253 
        } else {
254 
            print("Fallo la preparación de la actualización de un registro en la tabla \(Constants.TABLE_USER_NOTIFICATION) ")
19 efrain 255 
 
256 
            database.printError()
1 efrain 257 
        }
258 
        sqlite3_finalize(statement)
259 
    }
260 
 
261 
    func remove(id: Int) {
19 efrain 262 
        let db = database.open()
1 efrain 263 
        let query = "DELETE FROM " + Constants.TABLE_USER_NOTIFICATION
264 
            + " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_ID + " = '\(id)' ;"
265 
        var statement : OpaquePointer? = nil
266 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
267 
            if (sqlite3_step(statement) != SQLITE_DONE) {
268 
                print("No se pudo borrar el registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)")
19 efrain 269 
 
270 
                database.printError()
1 efrain 271 
            }
272 
        } else {
273 
            print("Fallo la preparación del borrado del registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" )
19 efrain 274 
 
275 
            database.printError()
1 efrain 276 
        }
277 
        sqlite3_finalize(statement)
278 
    }
279 
 
280 
 
23 efrain 281 
 
17 efrain 282 
    func removeAllUserUuidNotEqual(userUuid : String)
283 
    {
19 efrain 284 
        let db = database.open()
17 efrain 285 
        let query = "DELETE FROM " +  Constants.TABLE_USER_NOTIFICATION +
286 
            " WHERE " + Constants.TABLE_USER_NOTIFICATION_FIELD_USER_UUID + " <> '\(userUuid)' ;"
287 
        var statement : OpaquePointer? = nil
288 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
289 
            if sqlite3_step(statement) != SQLITE_DONE {
290 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_USER_NOTIFICATION)" +
291 
                " de los usuarios diferentes de : \(userUuid)" )
19 efrain 292 
 
293 
                database.printError()
17 efrain 294 
            }
295 
        } else {
296 
            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 297 
 
298 
            database.printError()
17 efrain 299 
        }
300 
        sqlite3_finalize(statement)
301 
    }
302 
 
1 efrain 303 
}
304