Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

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