Proyectos de Subversion Iphone Microlearning - Nuevo Interface

Rev

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