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