Proyectos de Subversion Iphone Microlearning - Inconcert

//
//  UserExtendedDao.swift
//  twogetskills
//
//  Created by Efrain Yanez Recanatini on 3/1/22.
//

import UIKit
import SQLite3

class UserExtendedDao {
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    private var database = Database.sharedInstance
    
    static let sharedInstance: UserExtendedDao = {
           let instance = UserExtendedDao()
           
           // setup code
           return instance
    }()


    func selectAll()-> [UserExtendedModel] {
        let db = database.open()
         var records = [UserExtendedModel]()
         
         let query = "SELECT " +
              Constants.TABLE_USER_EXTENDED_FIELD_UUID + " , " +
              Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " , " +
              Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " , " +
              Constants.TABLE_USER_EXTENDED_FIELD_VALUE +
             " FROM " +  Constants.TABLE_USER_EXTENDED + " ORDER BY " +   Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " ;"
         var statement : OpaquePointer? = nil
         
         
         if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
             while sqlite3_step(statement) == SQLITE_ROW {
                 var model = UserExtendedModel()
                 model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
                 model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                 model.label = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                 model.value = String(describing: String(cString: sqlite3_column_text(statement,3)))
                 

                 records.append(model)
                 //print("\nSuccessfully get records")
                 
             }
         } else {
            database.printError()
         }
         sqlite3_finalize(statement)
         return records
     }
    
    
    func selectAllByCompanyUuid(companyUuid : String)-> [UserExtendedModel] {
        let db = database.open()
         var records = [UserExtendedModel]()
         
         var query = "SELECT "
         query = query + Constants.TABLE_USER_EXTENDED_FIELD_UUID + " , "
         query = query + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " , "
         query = query + Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " , "
         query = query + Constants.TABLE_USER_EXTENDED_FIELD_VALUE
         query = query + " FROM " +  Constants.TABLE_USER_EXTENDED
         query = query + " WHERE " + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " = '\(companyUuid)' "
         query = query + " ORDER BY " +   Constants.TABLE_USER_EXTENDED_FIELD_LABEL + " ;"
         var statement : OpaquePointer? = nil
         
         
         if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK{
             while sqlite3_step(statement) == SQLITE_ROW {
                 var model = UserExtendedModel()
                 model.uuid = String(describing: String(cString: sqlite3_column_text(statement, 0)))
                 model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                 model.label = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                 model.value = String(describing: String(cString: sqlite3_column_text(statement,3)))
                 

                 records.append(model)
                 //print("\nSuccessfully get records")
                 
             }
         } else {
            database.printError()
         }
         sqlite3_finalize(statement)
         return records
     }
    
    func insert(record : UserExtendedModel) {
        let db = database.open()
        var query = "INSERT INTO " + Constants.TABLE_USER_EXTENDED + " ( "
        query = query + Constants.TABLE_USER_EXTENDED_FIELD_UUID + " , "
        query = query + Constants.TABLE_USER_EXTENDED_FIELD_COMPANY_UUID + " , "
        query = query + Constants.TABLE_USER_EXTENDED_FIELD_LABEL + ", "
        query = query + Constants.TABLE_USER_EXTENDED_FIELD_VALUE + " ) "
        query = query + " VALUES (?, ?, ?, ?);"
        
        var statement : OpaquePointer?
        
           
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
                  
            sqlite3_bind_text(statement, 1, record.uuid, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 2, record.companyUuid, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 3, record.label, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 4, record.value, -1, SQLITE_TRANSIENT)
            
            if (sqlite3_step(statement) != SQLITE_DONE) {
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_USER_EXTENDED)")
                database.printError()
            } else {
                print("Insertamos un registo en la tabla: \(Constants.TABLE_USER_EXTENDED)")
            }
        } else {
            print("Fallo la preparación en el insertar un registro en la tabla: \(Constants.TABLE_USER_EXTENDED)")
            database.printError()
        }
       
        sqlite3_finalize(statement)
    }

    
    func removeAll() {
        let db = database.open()
        let query = "DELETE FROM " + Constants.TABLE_USER_EXTENDED + ";"
        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 en la tabla: \(Constants.TABLE_USER_EXTENDED)")
                database.printError()
            }
        } else {
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_EXTENDED)")
            database.printError()
        }
        sqlite3_finalize(statement)
    }
    
    
}