Proyectos de Subversion Iphone Microlearning - Inconcert

Rev

Rev 15 | Ir a la última revisión | | Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 1 
//
2 
//  UserLogDao.swift
3 
//  twogetskills
4 
//
5 
//  Created by Efrain Yanez Recanatini on 2/21/22.
6 
//
7 
 
8 
import UIKit
9 
import SQLite3
10 
 
11 
class UserLogDao {
12 
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
13 
    private var database = Database.sharedInstance
14 
 
15 
    static let sharedInstance: UserLogDao = {
16 
           let instance = UserLogDao()
17 
 
18 
           // setup code
19 
           return instance
20 
    }()
21 
 
22 
 
23 
    func selectByUserUuidAndActivityAndAddedOn(userUuid: String, activity : String, addedOn : String)-> UserLogModel {
24 
        let db = database.open()
25 
        var model = UserLogModel()
26 
        var sql = "SELECT "
27 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
28 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
29 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
30 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
31 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
32 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
33 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
34 
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
35 
        sql = sql +  " FROM " +  Constants.TABLE_USER_LOG
36 
        sql = sql +  " WHERE " +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " = '\(userUuid)'  "
37 
        sql = sql +  " AND " +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " = '\(activity)'  "
38 
        sql = sql +  " AND " +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " = '\(addedOn)'  "
39 
        sql = sql +  " LIMIT 1 ;"
40 
        let query = sql
41 
 
42 
        var statement : OpaquePointer? = nil
43 
 
44 
 
45 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
46 
            if (sqlite3_step(statement) == SQLITE_ROW) {
47 
                model.id = Int(sqlite3_column_int(statement, 0))
48 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
49 
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
50 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
51 
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
52 
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement,5)))
53 
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
54 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
55 
 
56 
            }
57 
        } else {
58 
            database.printError()
59 
        }
60 
        sqlite3_finalize(statement)
61 
        return model
62 
    }
63 
 
64 
 
65 
 
66 
    func selectAll()-> [UserLogModel] {
67 
        let db = database.open()
68 
        var records = [UserLogModel]()
69 
        var query = "SELECT "
70 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
71 
        query = query +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
72 
        query = query +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
73 
        query = query +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
74 
        query = query +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
75 
        query = query +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
76 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
77 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
78 
        query = query + " FROM " + Constants.TABLE_USER_LOG
79 
        query = query + " ORDER BY " + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " DESC, " + Constants.TABLE_USER_LOG_FIELD_ID + " DESC;"
80 
 
81 
        var statement : OpaquePointer? = nil
82 
 
83 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
84 
            while (sqlite3_step(statement) == SQLITE_ROW) {
85 
                var model = UserLogModel()
86 
                model.id = Int(sqlite3_column_int(statement, 0))
87 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
88 
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
89 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
90 
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
91 
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement, 5)))
92 
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
93 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
94 
 
95 
                records.append(model)
96 
            }
97 
        } else {
98 
            database.printError()
99 
        }
100 
        sqlite3_finalize(statement)
101 
        return records
102 
    }
103 
 
104 
    func selectAllByUserUuid(userUuid : String)-> [UserLogModel] {
105 
        let db = database.open()
106 
        var records = [UserLogModel]()
107 
        var query = "SELECT "
108 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
109 
        query = query +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
110 
        query = query +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
111 
        query = query +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
112 
        query = query +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
113 
        query = query +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
114 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
115 
        query = query +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
116 
        query = query + " FROM " + Constants.TABLE_USER_LOG
117 
        query = query + " WHERE " + Constants.TABLE_USER_LOG_FIELD_USER_UUID  + " = '\(userUuid)' "
118 
        query = query + " ORDER BY " + Constants.TABLE_USER_LOG_FIELD_ID + " DESC, "
119 
        query = query + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " DESC; "
120 
 
121 
        var statement : OpaquePointer? = nil
122 
 
123 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
124 
            while (sqlite3_step(statement) == SQLITE_ROW) {
125 
                var model = UserLogModel()
126 
                model.id = Int(sqlite3_column_int(statement, 0))
127 
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
128 
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
129 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
130 
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
131 
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement, 5)))
132 
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
133 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
134 
 
135 
                records.append(model)
136 
            }
137 
        } else {
138 
            database.printError()
139 
        }
140 
        sqlite3_finalize(statement)
141 
        return records
142 
    }
143 
 
144 
    func insert(record : UserLogModel) {
145 
        let db = database.open()
146 
        var query = "INSERT INTO " + Constants.TABLE_USER_LOG + " ( "
147 
        query = query + Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
148 
        query = query + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
149 
        query = query + Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + ", "
150 
        query = query + Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + ", "
151 
        query = query + Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + ", "
152 
        query = query + Constants.TABLE_USER_LOG_FIELD_ACTIVITY + ", "
153 
        query = query + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " ) "
154 
        query = query + " VALUES (?, ?, ?, ?, ?, ?, ?);"
155 
 
156 
        var statement : OpaquePointer?
157 
 
158 
 
159 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
160 
 
161 
            sqlite3_bind_text(statement, 1, record.companyUuid, -1, SQLITE_TRANSIENT )
162 
            sqlite3_bind_text(statement, 2, record.userUuid, -1, SQLITE_TRANSIENT )
163 
            sqlite3_bind_text(statement, 3, record.topicUuid, -1, SQLITE_TRANSIENT )
164 
            sqlite3_bind_text(statement, 4, record.capsuleUuid, -1, SQLITE_TRANSIENT)
165 
            sqlite3_bind_text(statement, 5, record.slideUuid, -1, SQLITE_TRANSIENT)
166 
            sqlite3_bind_text(statement, 6, record.activity , -1, SQLITE_TRANSIENT)
167 
            sqlite3_bind_text(statement, 7, record.addedOn , -1, SQLITE_TRANSIENT)
168 
 
169 
            if (sqlite3_step(statement) != SQLITE_DONE) {
170 
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_USER_LOG)")
171 
                database.printError()
172 
            } else {
173 
                print("Insertamos un registo de log")
174 
                database.printError()
175 
            }
176 
        } else {
177 
            print("Fallo la preparación en el insertar un registro en la tabla: \(Constants.TABLE_USER_LOG)")
178 
            database.printError()
179 
        }
180 
 
181 
        sqlite3_finalize(statement)
182 
    }
183 
 
184 
    func remove(id: Int) {
185 
        let db = database.open()
186 
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG + " WHERE " + Constants.TABLE_USER_LOG_FIELD_ID + " = " + "\(id) ;"
187 
        var statement : OpaquePointer? = nil
188 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
189 
            if sqlite3_step(statement) != SQLITE_DONE {
190 
                print("No se pudo borrar el registro con el id: \(id) en la tabla: \(Constants.TABLE_USER_LOG)")
191 
                database.printError()
192 
            }
193 
        } else {
194 
            print("No se pudo borrar el registro con id: \(id) en la tabla: \(Constants.TABLE_USER_LOG)")
195 
            database.printError()
196 
        }
197 
        sqlite3_finalize(statement)
198 
    }
199 
 
200 
    func removeAll() {
201 
        let db = database.open()
202 
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG + ";"
203 
        var statement : OpaquePointer? = nil
204 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
205 
            if sqlite3_step(statement) != SQLITE_DONE {
206 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG)")
207 
                database.printError()
208 
            }
209 
        } else {
210 
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG)")
211 
            database.printError()
212 
        }
213 
        sqlite3_finalize(statement)
214 
    }
215 
 
216 
    func removeAllUserUuidNotEqual(userUuid : String) {
217 
        let db = database.open()
218 
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG
219 
            + " WHERE " + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " <> '" + userUuid + "' ";
220 
 
221 
        var statement : OpaquePointer? = nil
222 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
223 
            if sqlite3_step(statement) != SQLITE_DONE {
224 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG) " +
225 
                        " de usuarios diferents a : \(userUuid) ")
226 
                database.printError()
227 
 
228 
            } else {
229 
                print("Se borraron todos los registros en la tabla: \(Constants.TABLE_USER_LOG) " +
230 
                        " de usuarios diferents a : \(userUuid) ")
231 
                database.printError()
232 
            }
233 
        } else {
234 
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG) de los usuarios diferentes a : \(userUuid)")
235 
            database.printError()
236 
        }
237 
        sqlite3_finalize(statement)
238 
    }
239 
 
240 
 
241 
}