Proyectos de Subversion Iphone Microlearning - Inconcert

Rev

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