Proyectos de Subversion Iphone Microlearning - Inconcert

//
//  QuestionDao.swift
//  twogetskills
//
//  Created by Efrain Yanez Recanatini on 2/21/22.
//

import UIKit
import SQLite3

class QuestionDao {
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    public var db : OpaquePointer?
    
    func selectAllByQuizUuid(quizUuid: String)-> [QuestionModel] {
        
        var records = [QuestionModel]()
        
        var query = "SELECT "
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
        query = query + " FROM " + Constants.TABLE_QUESTION
        query = query + " WHERE " + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " = '\(quizUuid)'  "
        query = query + " ORDER BY " + Constants.TABLE_QUESTION_FIELD_POSITION + " ;"
        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            while sqlite3_step(statement) == SQLITE_ROW {
                var model = QuestionModel()
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
                model.quizUuid = String(describing: String(cString : sqlite3_column_text(statement, 1)))
                model.text = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                model.points = Int(sqlite3_column_int(statement, 4))
                model.position = Int(sqlite3_column_int(statement, 5))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))

                records.append(model)
                
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return records
    }

    func selectByUuid(uuid: String)-> QuestionModel  {
        
        var model = QuestionModel()
        
        var query = "SELECT "
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " , "
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
        query = query + " WHERE " + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(uuid)' LIMIT 1;"
        
        var statement : OpaquePointer? = nil
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                
                model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
                model.quizUuid = String(describing: String(cString : sqlite3_column_text(statement, 1)))
                model.text = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                model.points = Int(sqlite3_column_int(statement, 4))
                model.position = Int(sqlite3_column_int(statement, 5))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 6)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }
        
    func insert(question: QuestionModel) {
        
        var query = "INSERT INTO " + Constants.TABLE_QUESTION + " ( "
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_QUIZ_UUID + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_TEXT + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_TYPE + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_POSITION + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + ", "
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON
        query = query + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?);"
        var statement : OpaquePointer?
        
           
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
                   
            sqlite3_bind_text(statement, 1, question.uuid, -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 2, question.quizUuid, -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 3, question.text , -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 4, question.type , -1, SQLITE_TRANSIENT)
            sqlite3_bind_int(statement, 5, Int32(question.points))
            sqlite3_bind_int(statement, 6, Int32(question.position))
            sqlite3_bind_text(statement, 7, question.addedOn , -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 8, question.updatedOn , -1, SQLITE_TRANSIENT)
   
           if (sqlite3_step(statement) != SQLITE_DONE) {
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_QUESTION) ")
               
           }
        } else {
            print("Fallo la preparación de insertar un registro en una tabla: \(Constants.TABLE_QUESTION) ")
           
        }
       
        sqlite3_finalize(statement)
    }

    func update(question: QuestionModel) {
        
        var query = "UPDATE " + Constants.TABLE_QUESTION
        query = query + " SET " + Constants.TABLE_QUESTION_FIELD_TEXT + " = '\(question.text)', "
        query = query + Constants.TABLE_QUESTION_FIELD_POINTS + " = '\(question.points)', "
        query = query + Constants.TABLE_QUESTION_FIELD_POSITION + " = '\(question.position)', "
        query = query + Constants.TABLE_QUESTION_FIELD_ADDED_ON + " = '\(question.addedOn)', "
        query = query + Constants.TABLE_QUESTION_FIELD_UPDATED_ON + " = '\(question.updatedOn)' "
        query = query + " WHERE "
        query = query + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(question.uuid)' ;"
        var statement : OpaquePointer? = nil
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if (sqlite3_step(statement) != SQLITE_DONE) {
                print("No se pudo actualizar un registro en la tabla: \(Constants.TABLE_QUESTION)")
               
            }
        } else {
            print("No se pudo preparar el actualizar un registro en la tabla: \(Constants.TABLE_QUESTION)")
           
        }
        sqlite3_finalize(statement)
    }

    func remove(uuid: String) {
        
        let query = "DELETE FROM " + Constants.TABLE_QUESTION +
            " WHERE " + Constants.TABLE_QUESTION_FIELD_UUID + " = '\(uuid)' ;"
        var statement : OpaquePointer? = nil
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if sqlite3_step(statement) != SQLITE_DONE {
                print("No se pudo borrar el registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_QUESTION)")
               
            }
        } else {
            print("No se pudo prepara el borrar de un registro con el uuid: \(uuid) en la tabla: \(Constants.TABLE_QUESTION)" )
           
        }
        sqlite3_finalize(statement)
    }

    func removeAll() {
        
        let query = "DELETE FROM " + Constants.TABLE_QUESTION + ";"
        var statement : OpaquePointer? = nil
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
            if sqlite3_step(statement) != SQLITE_DONE {
                print("No se pudo borrar todos los registros de la tabla: \(Constants.TABLE_QUESTION)")
               
            }
        } else {
            print("No se pudo preparar el borrar todos los registros de la tabla: \(Constants.TABLE_QUESTION)")
           
        }
        sqlite3_finalize(statement)
    }
    
}