Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

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