Proyectos de Subversion Iphone Microlearning - Inconcert

Rev

Rev 15 | 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 
//  SlideDao.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 SlideDao {
12 
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
16 efrain 13 
    public var db : OpaquePointer?
1 efrain 14 
 
15 
    func getCountByCapsuleUuid(capsuleUuid: String) -> Int {
15 efrain 16 
 
1 efrain 17 
        let query = "SELECT COUNT(*) AS total FROM " + Constants.TABLE_SLIDE +
18 
            " WHERE " + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + " = '\(capsuleUuid)'  ;"
19 
 
20 
        var statement : OpaquePointer? = nil
21 
        var count = 0;
22 
 
23 
 
24 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
25 
            if(sqlite3_step(statement) == SQLITE_ROW){
26 
                count = Int(sqlite3_column_int(statement, 0))
27 
                //print("\(count)")
28 
            }
29 
        } else {
15 efrain 30 
 
1 efrain 31 
        }
32 
        sqlite3_finalize(statement)
33 
        return count
34 
    }
35 
 
36 
    func getCountByTopicUuid(topicUuid: String) -> Int {
15 efrain 37 
 
1 efrain 38 
        let query = "SELECT COUNT(*) AS total FROM " + Constants.TABLE_SLIDE +
39 
            " WHERE " + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + " = '\(topicUuid)' ;"
40 
 
41 
        var statement : OpaquePointer? = nil
42 
        var count = 0
43 
 
44 
 
45 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
46 
            if(sqlite3_step(statement) == SQLITE_ROW){
47 
                count = Int(sqlite3_column_int(statement, 0))
48 
                //print("\(count)")
49 
            }
50 
        } else {
15 efrain 51 
 
1 efrain 52 
        }
53 
        sqlite3_finalize(statement)
54 
        return count
55 
    }
56 
 
57 
    func selectAllByCapsuleUuid(capsuleUuid: String)-> [SlideModel] {
15 efrain 58 
 
1 efrain 59 
        var records = [SlideModel]()
60 
 
61 
        var query = "SELECT " + Constants.TABLE_SLIDE_FIELD_UUID
62 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID
63 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID
64 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID
65 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_NAME
66 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_DESCRIPTION
67 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_TYPE
68 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_FILE
69 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_BACKGROUND
70 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_POSITION
71 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_ADDED_ON
72 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_UPDATED_ON
73 
        query = query + " FROM " + Constants.TABLE_SLIDE
74 
        query = query + " WHERE " + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + " = '\(capsuleUuid)' "
75 
        query = query + " ORDER BY "  + Constants.TABLE_SLIDE_FIELD_POSITION + " ;"
76 
        var statement : OpaquePointer? = nil
77 
 
78 
 
79 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
80 
            while (sqlite3_step(statement) == SQLITE_ROW) {
81 
                var model = SlideModel()
82 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
83 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
84 
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
85 
                model.quizUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
86 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 4)))
87 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 5)))
88 
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 6)))
89 
                model.file = String(describing: String(cString: sqlite3_column_text(statement, 7)))
90 
                model.background = String(describing: String(cString: sqlite3_column_text(statement, 8)))
91 
                model.position = Int(sqlite3_column_int(statement, 9))
92 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
93 
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 11)))
94 
                records.append(model)
95 
            }
96 
        } else {
15 efrain 97 
 
1 efrain 98 
        }
99 
        sqlite3_finalize(statement)
100 
        return records
101 
    }
102 
 
103 
 
104 
    func selectByUuid(uuid: String)-> SlideModel {
15 efrain 105 
 
1 efrain 106 
        var model = SlideModel()
107 
        var query = "SELECT " + Constants.TABLE_SLIDE_FIELD_UUID
108 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID
109 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID
110 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID
111 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_NAME
112 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_DESCRIPTION
113 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_TYPE
114 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_FILE
115 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_BACKGROUND
116 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_POSITION
117 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_ADDED_ON
118 
        query = query + ", " + Constants.TABLE_SLIDE_FIELD_UPDATED_ON
119 
        query = query + " FROM " + Constants.TABLE_SLIDE
120 
        query = query + " WHERE " + Constants.TABLE_SLIDE_FIELD_UUID + " = '\(uuid)' LIMIT 1;"
121 
        var statement : OpaquePointer? = nil
122 
 
123 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
124 
            if(sqlite3_step(statement) == SQLITE_ROW) {
125 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
126 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
127 
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
128 
                model.quizUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
129 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 4)))
130 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 5)))
131 
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 6)))
132 
                model.file = String(describing: String(cString: sqlite3_column_text(statement, 7)))
133 
                model.background = String(describing: String(cString: sqlite3_column_text(statement, 8)))
134 
                model.position = Int(sqlite3_column_int(statement, 9))
135 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
136 
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 11)))
137 
            }
138 
        } else {
15 efrain 139 
 
1 efrain 140 
        }
141 
        sqlite3_finalize(statement)
142 
        return model
143 
    }
144 
 
145 
    func insert(slide : SlideModel) {
15 efrain 146 
 
1 efrain 147 
        var query = "INSERT INTO " + Constants.TABLE_SLIDE + " ( "
148 
        query = query + Constants.TABLE_SLIDE_FIELD_UUID + ", "
149 
        query = query + Constants.TABLE_SLIDE_FIELD_TOPIC_UUID + ", "
150 
        query = query + Constants.TABLE_SLIDE_FIELD_CAPSULE_UUID + ", "
151 
        query = query + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + ", "
152 
        query = query + Constants.TABLE_SLIDE_FIELD_NAME + ", "
153 
        query = query + Constants.TABLE_SLIDE_FIELD_DESCRIPTION + ", "
154 
        query = query + Constants.TABLE_SLIDE_FIELD_TYPE + ", "
155 
        query = query + Constants.TABLE_SLIDE_FIELD_FILE + ", "
156 
        query = query + Constants.TABLE_SLIDE_FIELD_BACKGROUND + ", "
157 
        query = query + Constants.TABLE_SLIDE_FIELD_POSITION + ", "
158 
        query = query + Constants.TABLE_SLIDE_FIELD_ADDED_ON + ", "
159 
        query = query + Constants.TABLE_SLIDE_FIELD_UPDATED_ON + " ) "
160 
        query = query + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
161 
        var statement : OpaquePointer?
162 
 
163 
 
164 
 
165 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK){
166 
 
167 
            sqlite3_bind_text(statement, 1, slide.uuid, -1, SQLITE_TRANSIENT)
168 
            sqlite3_bind_text(statement, 2, slide.topicUuid, -1, SQLITE_TRANSIENT)
169 
            sqlite3_bind_text(statement, 3, slide.capsuleUuid, -1, SQLITE_TRANSIENT)
170 
            sqlite3_bind_text(statement, 4, slide.quizUuid, -1, SQLITE_TRANSIENT)
171 
            sqlite3_bind_text(statement, 5, slide.name , -1, SQLITE_TRANSIENT)
172 
            sqlite3_bind_text(statement, 6, slide.description , -1, SQLITE_TRANSIENT)
173 
            sqlite3_bind_text(statement, 7, slide.type , -1, SQLITE_TRANSIENT)
174 
            sqlite3_bind_text(statement, 8, slide.file , -1, SQLITE_TRANSIENT)
175 
            sqlite3_bind_text(statement, 9, slide.background , -1, SQLITE_TRANSIENT)
176 
            sqlite3_bind_int(statement, 10, Int32(slide.position))
177 
            sqlite3_bind_text(statement,11, slide.addedOn , -1, SQLITE_TRANSIENT)
178 
            sqlite3_bind_text(statement, 12, slide.updatedOn, -1, SQLITE_TRANSIENT)
179 
 
180 
           if sqlite3_step(statement) != SQLITE_DONE {
181 
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 182 
 
1 efrain 183 
           }
184 
        } else {
185 
            print("Fallo la preparación de insertar un registro en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 186 
 
1 efrain 187 
        }
188 
 
189 
        sqlite3_finalize(statement)
190 
    }
191 
 
192 
    func update(slide: SlideModel) {
15 efrain 193 
 
1 efrain 194 
        var query = "UPDATE " + Constants.TABLE_SLIDE
195 
        query = query + " SET " + Constants.TABLE_SLIDE_FIELD_QUIZ_UUID + " = '\(slide.quizUuid)', "
196 
        query = query + Constants.TABLE_SLIDE_FIELD_NAME + " = '\(slide.name)', "
197 
        query = query + Constants.TABLE_SLIDE_FIELD_DESCRIPTION + " = '\(slide.description)', "
198 
        query = query + Constants.TABLE_SLIDE_FIELD_TYPE + " = '\(slide.type)', "
199 
        query = query + Constants.TABLE_SLIDE_FIELD_FILE + " = '\(slide.file)', "
200 
        query = query + Constants.TABLE_SLIDE_FIELD_BACKGROUND + " = '\(slide.background)', "
201 
        query = query + Constants.TABLE_SLIDE_FIELD_POSITION + " = '\(slide.position)', "
202 
        query = query + Constants.TABLE_SLIDE_FIELD_ADDED_ON + " = '\(slide.addedOn)', "
203 
        query = query + Constants.TABLE_SLIDE_FIELD_UPDATED_ON + " = '\(slide.updatedOn)' "
204 
        query = query + " WHERE " + Constants.TABLE_SLIDE_FIELD_UUID + " = '\(slide.uuid)'  ;"
205 
        var statement : OpaquePointer? = nil
206 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
207 
            if sqlite3_step(statement) != SQLITE_DONE {
208 
                print("No se puedo actualizar un registro en la tabla: \(Constants.TABLE_SLIDE) ")
15 efrain 209 
 
1 efrain 210 
            }
211 
        } else {
212 
            print("Fallo la preparación de actualizar un registro en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 213 
 
1 efrain 214 
        }
215 
        sqlite3_finalize(statement)
216 
    }
217 
 
218 
    func remove(uuid: String) {
15 efrain 219 
 
1 efrain 220 
        let query = "DELETE FROM " + Constants.TABLE_SLIDE +
221 
            " WHERE " + Constants.TABLE_SLIDE_FIELD_UUID + " = '\(uuid)' ;"
222 
        var statement : OpaquePointer? = nil
223 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
224 
            if sqlite3_step(statement) != SQLITE_DONE {
225 
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 226 
 
1 efrain 227 
            }
228 
        } else {
229 
            print("Fallo la preparación de borrar un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 230 
 
1 efrain 231 
        }
232 
        sqlite3_finalize(statement)
233 
    }
234 
 
235 
    func removeAll() {
15 efrain 236 
 
1 efrain 237 
        let query = "DELETE FROM " + Constants.TABLE_SLIDE + ";"
238 
        var statement : OpaquePointer? = nil
239 
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
240 
            if sqlite3_step(statement) != SQLITE_DONE {
241 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_SLIDE)")
15 efrain 242 
 
1 efrain 243 
            }
244 
        } else {
245 
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_SLIDE) ")
15 efrain 246 
 
1 efrain 247 
        }
248 
        sqlite3_finalize(statement)
249 
    }
250 
 
251 
}
252