Proyectos de Subversion Iphone Microlearning

Rev

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