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 
//  CapsuleDao.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 CapsuleDao {
17 efrain 12 
    private var database = Environment(\.database).wrappedValue
1 efrain 13 
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
14 
 
17 efrain 15 
 
1 efrain 16 
    func getCountAll() -> Int {
17 efrain 17 
        let db = database.open()
1 efrain 18 
        var count = 0
19 
        let query = "SELECT COUNT(*) AS total FROM \(Constants.TABLE_CAPSULE) LIMIT 1"
20 
 
21 
        var statement : OpaquePointer? = nil
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 
        }
30 
        sqlite3_finalize(statement)
31 
        return count
32 
    }
33 
 
34 
    func getCountByTopicUuid(topicUuid: String) -> Int {
17 efrain 35 
        let db = database.open()
1 efrain 36 
        var count = 0
37 
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_CAPSULE +
38 
            " WHERE " +  Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " = \(topicUuid) LIMIT 1"
39 
 
40 
        var statement : OpaquePointer? = nil
41 
 
42 
 
43 
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
44 
            if(sqlite3_step(statement) == SQLITE_ROW){
45 
                count = Int(sqlite3_column_int(statement, 0))
46 
               // print("\(count)")
47 
            }
48 
        }
49 
        sqlite3_finalize(statement)
50 
        return count
51 
    }
52 
 
15 efrain 53 
    func selectAll() -> [CapsuleModel] {
17 efrain 54 
        let db = database.open()
1 efrain 55 
        var records = [CapsuleModel]()
56 
        var query = "SELECT "
57 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
58 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
59 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
60 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
61 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
62 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
63 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
64 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
65 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
66 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
67 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
68 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
69 
        query = query + " FROM " + Constants.TABLE_CAPSULE
70 
        query = query + " ORDER BY " + Constants.TABLE_CAPSULE_FIELD_NAME + " ;"
71 
        var statement : OpaquePointer? = nil
72 
 
73 
 
74 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
75 
            while (sqlite3_step(statement) == SQLITE_ROW) {
76 
                var model = CapsuleModel()
77 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
78 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
79 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
80 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
81 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
82 
                model.position = Int(sqlite3_column_int(statement, 5))
83 
 
84 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
85 
 
86 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
87 
 
88 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
89 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
90 
 
91 
 
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 
 
95 
                records.append(model)
96 
            }
97 
        }
98 
        sqlite3_finalize(statement)
99 
        return records
100 
    }
101 
 
102 
    func selectAllByTopicUuid(topicUuid: String)-> [CapsuleModel] {
17 efrain 103 
        let db = database.open()
1 efrain 104 
        var records = [CapsuleModel]()
105 
        var query = "SELECT "
106 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
107 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
108 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
109 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
110 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
111 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
112 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
113 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
114 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
115 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
116 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
117 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
118 
        query = query + " FROM " + Constants.TABLE_CAPSULE
119 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " = '\(topicUuid)' "
120 
        query = query + " ORDER BY " + Constants.TABLE_CAPSULE_FIELD_POSITION + " ;"
121 
        var statement : OpaquePointer? = nil
122 
 
123 
 
124 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
125 
            while (sqlite3_step(statement) == SQLITE_ROW) {
126 
                var model = CapsuleModel()
127 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
128 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
129 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
130 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
131 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
132 
                model.position = Int(sqlite3_column_int(statement, 5))
133 
 
134 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
135 
 
136 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
137 
 
138 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
139 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
140 
 
141 
 
142 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
143 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
144 
 
145 
                records.append(model)
146 
            }
147 
        }
148 
        sqlite3_finalize(statement)
149 
        return records
150 
    }
151 
 
152 
    func selectLastPendingByUserUuid(userUuid: String)-> [CapsuleModel] {
17 efrain 153 
        let db = database.open()
1 efrain 154 
        var records = [CapsuleModel]()
155 
        var query = "SELECT "
156 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
157 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
158 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
159 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
160 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
161 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
162 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
163 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
164 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
165 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
166 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
167 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
168 
        query = query + " FROM " + Constants.TABLE_CAPSULE
169 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " NOT IN ("
170 
        query = query + " SELECT " + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID
171 
        query = query + " FROM " + Constants.TABLE_PROGRESS
172 
        query = query + " WHERE " + Constants.TABLE_PROGRESS_FIELD_TYPE + " = '" + Constants.PROGRESS_TYPE_CAPSULE + "'";
173 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " ='\(userUuid)\' "
174 
        query = query + ") ORDER BY " + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON + " LIMIT 1 ;"
175 
 
176 
        //print("selectLastPendingByUserUuid query : \(query)")
177 
 
178 
 
179 
        var statement : OpaquePointer? = nil
180 
 
181 
 
182 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
183 
            while (sqlite3_step(statement) == SQLITE_ROW) {
184 
                var model = CapsuleModel()
185 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
186 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
187 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
188 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
189 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
190 
                model.position = Int(sqlite3_column_int(statement, 5))
191 
 
192 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
193 
 
194 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
195 
 
196 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
197 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
198 
 
199 
 
200 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
201 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
202 
 
203 
                records.append(model)
204 
            }
205 
        }
206 
        sqlite3_finalize(statement)
207 
        return records
208 
    }
209 
 
210 
    func selectAllPendingByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
17 efrain 211 
        let db = database.open()
1 efrain 212 
        var records = [CapsuleModel]()
213 
        var query = "SELECT "
214 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
215 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
216 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
217 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
218 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
219 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
220 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
221 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
222 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
223 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
224 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
225 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
226 
        query = query + " FROM " + Constants.TABLE_CAPSULE
227 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " NOT IN ("
228 
        query = query + " SELECT " + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID
229 
        query = query + " FROM " + Constants.TABLE_PROGRESS
230 
        query = query + " WHERE " + Constants.TABLE_PROGRESS_FIELD_TYPE + " = '" + Constants.PROGRESS_TYPE_CAPSULE + "'";
231 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " ='\(userUuid)\' "
232 
        query = query + ") "
233 
 
234 
        if !search.isEmpty {
235 
            query = query + " AND " + Constants.TABLE_CAPSULE_FIELD_NAME + " LIKE '%\(search)%' "
236 
        }
237 
 
238 
 
239 
        query = query + "ORDER BY " + Constants.TABLE_CAPSULE_FIELD_NAME + " ;"
240 
 
241 
 
242 
        //print("selectAllPendingByUserUuid query : \(query)")
243 
 
244 
        var statement : OpaquePointer? = nil
245 
 
246 
 
247 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
248 
            while (sqlite3_step(statement) == SQLITE_ROW) {
249 
                var model = CapsuleModel()
250 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
251 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
252 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
253 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
254 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
255 
                model.position = Int(sqlite3_column_int(statement, 5))
256 
 
257 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
258 
 
259 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
260 
 
261 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
262 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
263 
 
264 
 
265 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
266 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
267 
 
268 
                records.append(model)
269 
            }
270 
        }
271 
        sqlite3_finalize(statement)
272 
        return records
273 
    }
274 
 
275 
    func selectAllInProgressByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
17 efrain 276 
        let db = database.open()
1 efrain 277 
        var records = [CapsuleModel]()
278 
        var query = "SELECT "
279 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
280 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
281 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
282 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
283 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
284 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
285 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
286 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
287 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
288 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
289 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
290 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
291 
        query = query + " FROM " + Constants.TABLE_CAPSULE
292 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " IN ("
293 
        query = query + " SELECT " + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID
294 
        query = query + " FROM " + Constants.TABLE_PROGRESS
295 
        query = query + " WHERE " + Constants.TABLE_PROGRESS_FIELD_TYPE + " = '" + Constants.PROGRESS_TYPE_CAPSULE + "'";
296 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)\' "
297 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_PROGRESS + " < 0 "
298 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_COMPLETED + " = 0 "
299 
        query = query + ") "
300 
 
301 
        if !search.isEmpty {
302 
            query = query + " AND " + Constants.TABLE_CAPSULE_FIELD_NAME + " LIKE '%\(search)%' "
303 
        }
304 
 
305 
 
306 
        query = query + "ORDER BY " + Constants.TABLE_CAPSULE_FIELD_NAME + " ;"
307 
 
308 
        //print("selectAllInProgressByUserUuid query : \(query)")
309 
 
310 
        var statement : OpaquePointer? = nil
311 
 
312 
 
313 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
314 
            while (sqlite3_step(statement) == SQLITE_ROW) {
315 
                var model = CapsuleModel()
316 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
317 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
318 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
319 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
320 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
321 
                model.position = Int(sqlite3_column_int(statement, 5))
322 
 
323 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
324 
 
325 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
326 
 
327 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
328 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
329 
 
330 
 
331 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
332 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
333 
 
334 
                records.append(model)
335 
            }
336 
        }
337 
        sqlite3_finalize(statement)
338 
        return records
339 
    }
340 
 
341 
    func selectAllCompletedByUserUuid(userUuid: String, search : String = "")-> [CapsuleModel] {
17 efrain 342 
        let db = database.open()
1 efrain 343 
        var records = [CapsuleModel]()
344 
        var query = "SELECT "
345 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
346 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
347 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
348 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
349 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
350 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
351 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
352 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
353 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
354 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
355 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
356 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
357 
        query = query + " FROM " + Constants.TABLE_CAPSULE
358 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " IN ("
359 
        query = query + " SELECT " + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID
360 
        query = query + " FROM " + Constants.TABLE_PROGRESS
361 
        query = query + " WHERE " + Constants.TABLE_PROGRESS_FIELD_TYPE + " = '" + Constants.PROGRESS_TYPE_CAPSULE + "'";
362 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)\' "
363 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_PROGRESS + " >= 0 "
364 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_COMPLETED + " = 1 "
365 
        query = query + ") "
366 
 
367 
        if !search.isEmpty {
368 
            query = query + " AND " + Constants.TABLE_CAPSULE_FIELD_NAME + " LIKE '%\(search)%' "
369 
        }
370 
 
371 
 
372 
        query = query + "ORDER BY " + Constants.TABLE_CAPSULE_FIELD_NAME + " ;"
373 
 
374 
        //print("selectAllCompletedByUserUuid query : \(query)")
375 
 
376 
 
377 
        var statement : OpaquePointer? = nil
378 
 
379 
 
380 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
381 
            while (sqlite3_step(statement) == SQLITE_ROW) {
382 
                var model = CapsuleModel()
383 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
384 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
385 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
386 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
387 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
388 
                model.position = Int(sqlite3_column_int(statement, 5))
389 
 
390 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
391 
 
392 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
393 
 
394 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
395 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
396 
 
397 
 
398 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
399 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
400 
 
401 
                records.append(model)
402 
            }
403 
        }
404 
        sqlite3_finalize(statement)
405 
        return records
406 
    }
407 
 
408 
    func selectLastInProgress(userUuid: String)-> CapsuleModel {
17 efrain 409 
        let db = database.open()
1 efrain 410 
        var model = CapsuleModel()
411 
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
412 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
413 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
414 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
415 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
416 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
417 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
418 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
419 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
420 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
421 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
422 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
423 
        query = query + " FROM " + Constants.TABLE_CAPSULE
424 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + "  IN ("
425 
        query = query + " SELECT " + Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID
426 
        query = query + " FROM " + Constants.TABLE_PROGRESS
427 
        query = query + " WHERE " + Constants.TABLE_PROGRESS_FIELD_TYPE + " = '" + Constants.PROGRESS_TYPE_CAPSULE + "'";
428 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_PROGRESS + "  <= 100 "
429 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_COMPLETED + " = 0 "
430 
        query = query + " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " ='\(userUuid)\' "
431 
        query = query + " ORDER BY " + Constants.TABLE_PROGRESS_FIELD_UPDATED_ON + " DESC LIMIT 1"
432 
        query = query + ") "
433 
 
434 
        var statement : OpaquePointer? = nil
435 
 
436 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
437 
            if (sqlite3_step(statement) == SQLITE_ROW) {
438 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
439 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
440 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
441 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
442 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
443 
                model.position = Int(sqlite3_column_int(statement, 5))
444 
 
445 
 
446 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
447 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
448 
 
449 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
450 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
451 
 
452 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
453 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
454 
 
455 
            }
456 
        }
457 
        sqlite3_finalize(statement)
458 
        return model
459 
    }
460 
 
461 
    /*
462 
     SELECT * FROM tb_capsules AS c
463 
     WHERE uuid NOT IN (SELECT capsule_uuid FROM tb_progress WHERE type = 'c' AND user_uuid = '4cf9fb4b-0190-4bb4-9369-15eb68e9cace')
464 
     ORDER BY name
465 
 
466 
     SELECT * FROM tb_capsules AS c
467 
     WHERE uuid IN (SELECT capsule_uuid FROM tb_progress WHERE type = 'c' AND user_uuid = '4cf9fb4b-0190-4bb4-9369-15eb68e9cace' AND completed = 0)
468 
     ORDER BY name
469 
     */
470 
 
471 
    func selectByUuid(uuid: String)-> CapsuleModel {
17 efrain 472 
        let db = database.open()
1 efrain 473 
        var model = CapsuleModel()
474 
        var query = "SELECT " + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
475 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + " , "
476 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + "  ,  "
477 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " , "
478 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " , "
479 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " , "
480 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " , "
481 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " , "
482 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " , "
483 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " , "
484 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " , "
485 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
486 
        query = query + " FROM " + Constants.TABLE_CAPSULE
487 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = '\(uuid)'  LIMIT 1;"
488 
        var statement : OpaquePointer? = nil
489 
 
490 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
491 
            if (sqlite3_step(statement) == SQLITE_ROW) {
492 
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
493 
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
494 
                model.name = String(describing: String(cString: sqlite3_column_text(statement, 2)))
495 
                model.description = String(describing: String(cString: sqlite3_column_text(statement, 3)))
496 
                model.image = String(describing: String(cString: sqlite3_column_text(statement, 4)))
497 
                model.position = Int(sqlite3_column_int(statement, 5))
498 
 
499 
 
500 
                model.linkComments = String(describing: String(cString: sqlite3_column_text(statement, 6)))
501 
                model.linkCommentAdd = String(describing: String(cString: sqlite3_column_text(statement, 7)))
502 
 
503 
                model.totalComments = Int(sqlite3_column_int(statement, 8))
504 
                model.totalRating = Decimal(sqlite3_column_double(statement, 9))
505 
 
506 
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 10)))
507 
                model.updatedOn  = String(describing: String(cString: sqlite3_column_text(statement, 11)))
508 
 
509 
            }
510 
        } else {
15 efrain 511 
 
1 efrain 512 
        }
513 
        sqlite3_finalize(statement)
514 
        return model
515 
    }
516 
 
517 
    func insert(capsule: CapsuleModel) {
17 efrain 518 
        let db = database.open()
1 efrain 519 
        var query = "INSERT INTO " + Constants.TABLE_CAPSULE + " ( "
520 
        query = query + Constants.TABLE_CAPSULE_FIELD_UUID + ", "
521 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOPIC_UUID + ", "
522 
        query = query + Constants.TABLE_CAPSULE_FIELD_NAME + ", "
523 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + ", "
524 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + ", "
525 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + ", "
526 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + ", "
527 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + ", "
528 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + ", "
529 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + ", "
530 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + ", "
531 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON
532 
        query = query +  " ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?);"
533 
        var statement : OpaquePointer?
534 
 
535 
 
536 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
537 
 
538 
            sqlite3_bind_text(statement, 1, capsule.uuid, -1, SQLITE_TRANSIENT)
539 
            sqlite3_bind_text(statement, 2, capsule.topicUuid, -1, SQLITE_TRANSIENT)
540 
            sqlite3_bind_text(statement, 3, capsule.name , -1, SQLITE_TRANSIENT)
541 
            sqlite3_bind_text(statement, 4, capsule.description , -1, SQLITE_TRANSIENT)
542 
            sqlite3_bind_text(statement, 5, capsule.image , -1, SQLITE_TRANSIENT)
543 
            sqlite3_bind_int(statement, 6, Int32(capsule.position))
544 
            sqlite3_bind_text(statement, 7, capsule.linkComments , -1, SQLITE_TRANSIENT)
545 
            sqlite3_bind_text(statement, 8, capsule.linkCommentAdd , -1, SQLITE_TRANSIENT)
546 
            sqlite3_bind_int(statement, 9, Int32(capsule.totalComments))
547 
            sqlite3_bind_double(statement, 10,Double("\(capsule.totalRating)") ?? 0)
548 
            sqlite3_bind_text(statement, 11, capsule.addedOn , -1, SQLITE_TRANSIENT)
549 
            sqlite3_bind_text(statement, 12, capsule.updatedOn , -1, SQLITE_TRANSIENT)
550 
 
551 
           if (sqlite3_step(statement) != SQLITE_DONE) {
552 
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
553 
 
15 efrain 554 
 
1 efrain 555 
           }
556 
        } else {
557 
            print("Fallo la prepración de insertar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
558 
 
15 efrain 559 
 
1 efrain 560 
        }
561 
 
562 
        sqlite3_finalize(statement)
563 
    }
564 
 
565 
    func update(capsule : CapsuleModel) {
17 efrain 566 
        let db = database.open()
1 efrain 567 
        var query = "UPDATE " + Constants.TABLE_CAPSULE
568 
        query = query + " SET " + Constants.TABLE_CAPSULE_FIELD_NAME + " = '\(capsule.name)', "
569 
        query = query + Constants.TABLE_CAPSULE_FIELD_DESCRIPTION + " = '\(capsule.description)', "
570 
        query = query + Constants.TABLE_CAPSULE_FIELD_IMAGE + " = '\(capsule.image)', "
571 
        query = query + Constants.TABLE_CAPSULE_FIELD_POSITION + " = '\(capsule.position)', "
572 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS + " = '\(capsule.linkComments)', "
573 
        query = query + Constants.TABLE_CAPSULE_FIELD_LINK_COMMENTS_ADD + " = '\(capsule.linkCommentAdd)',"
574 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_COMMENTS + " = '\(capsule.totalComments)', "
575 
        query = query + Constants.TABLE_CAPSULE_FIELD_TOTAL_RATING + " = '\(capsule.totalRating)', "
576 
        query = query + Constants.TABLE_CAPSULE_FIELD_ADDED_ON + " = '\(capsule.addedOn)', "
577 
        query = query + Constants.TABLE_CAPSULE_FIELD_UPDATED_ON + " = '\(capsule.updatedOn)' "
578 
        query = query + " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = '\(capsule.uuid)' ;"
579 
        var statement : OpaquePointer? = nil
580 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
581 
            if (sqlite3_step(statement) != SQLITE_DONE) {
582 
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
583 
 
15 efrain 584 
 
1 efrain 585 
            }
586 
        } else {
587 
            print("Fallo la preparación para actualizar un registro en la tabla: \(Constants.TABLE_CAPSULE)")
588 
 
15 efrain 589 
 
1 efrain 590 
        }
591 
        sqlite3_finalize(statement)
592 
    }
593 
 
594 
 
595 
 
596 
    func remove(uuid: String) {
17 efrain 597 
        let db = database.open()
1 efrain 598 
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE +
599 
            " WHERE " + Constants.TABLE_CAPSULE_FIELD_UUID + " = " + "\(uuid) ;"
600 
        var statement : OpaquePointer? = nil
601 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
602 
            if (sqlite3_step(statement) != SQLITE_DONE) {
603 
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
604 
 
15 efrain 605 
 
1 efrain 606 
            }
607 
        } else {
608 
            print("Fallo la preparación de borrar un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_CAPSULE)")
609 
 
15 efrain 610 
 
1 efrain 611 
        }
612 
        sqlite3_finalize(statement)
613 
    }
614 
 
615 
    func removeAll() {
17 efrain 616 
        let db = database.open()
1 efrain 617 
        let query = "DELETE FROM " + Constants.TABLE_CAPSULE + ";"
618 
        var statement : OpaquePointer? = nil
619 
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
620 
            if (sqlite3_step(statement) != SQLITE_DONE) {
621 
                print("No se pudo borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
622 
 
15 efrain 623 
 
1 efrain 624 
            }
625 
        } else {
626 
            print("No se pudo preparar el borrar todos los registros en la tabla: \(Constants.TABLE_CAPSULE)")
627 
 
15 efrain 628 
 
1 efrain 629 
        }
630 
        sqlite3_finalize(statement)
631 
    }
632 
 
633 
}