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