Proyectos de Subversion Iphone Microlearning - Inconcert

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

import SwiftUI
import SQLite3

class UserLogDao {
    private var database = Environment(\.database).wrappedValue
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    
    func selectByUserUuidAndActivityAndAddedOn(userUuid: String, activity : String, addedOn : String)-> UserLogModel {
        let db = database.open()
        var model = UserLogModel()
        var sql = "SELECT "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
        sql = sql +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
        sql = sql +  " FROM " +  Constants.TABLE_USER_LOG
        sql = sql +  " WHERE " +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " = '\(userUuid)'  "
        sql = sql +  " AND " +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " = '\(activity)'  "
        sql = sql +  " AND " +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " = '\(addedOn)'  "
        sql = sql +  " LIMIT 1 ;"
        let query = sql
        
        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            if (sqlite3_step(statement) == SQLITE_ROW) {
                model.id = Int(sqlite3_column_int(statement, 0))
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement,5)))
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))

            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }
    
   
    
    func selectAll()-> [UserLogModel] {
        let db = database.open()
        var records = [UserLogModel]()
        var query = "SELECT "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
        query = query + " FROM " + Constants.TABLE_USER_LOG
        query = query + " ORDER BY " + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " DESC, " + Constants.TABLE_USER_LOG_FIELD_ID + " DESC;"
        
        var statement : OpaquePointer? = nil
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                var model = UserLogModel()
                model.id = Int(sqlite3_column_int(statement, 0))
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement, 5)))
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))

                records.append(model)
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return records
    }
     
    func selectAllByUserUuid(userUuid : String)-> [UserLogModel] {
        let db = database.open()
        var records = [UserLogModel]()
        var query = "SELECT "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ACTIVITY + " , "
        query = query +  Constants.TABLE_USER_LOG_FIELD_ADDED_ON
        query = query + " FROM " + Constants.TABLE_USER_LOG
        query = query + " WHERE " + Constants.TABLE_USER_LOG_FIELD_USER_UUID  + " = '\(userUuid)' "
        query = query + " ORDER BY " + Constants.TABLE_USER_LOG_FIELD_ID + " DESC, "
        query = query + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " DESC; "
        
        var statement : OpaquePointer? = nil
        
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                var model = UserLogModel()
                model.id = Int(sqlite3_column_int(statement, 0))
                model.userUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 2)))
                model.topicUuid = String(describing: String(cString: sqlite3_column_text(statement, 3)))
                model.capsuleUuid = String(describing: String(cString: sqlite3_column_text(statement, 4)))
                model.slideUuid = String(describing: String(cString: sqlite3_column_text(statement, 5)))
                model.activity = String(describing: String(cString: sqlite3_column_text(statement, 6)))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 7)))

                records.append(model)
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return records
    }
    
    func insert(record : UserLogModel) {
        let db = database.open()
        var query = "INSERT INTO " + Constants.TABLE_USER_LOG + " ( "
        query = query + Constants.TABLE_USER_LOG_FIELD_COMPANY_UUID + " , "
        query = query + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " , "
        query = query + Constants.TABLE_USER_LOG_FIELD_TOPIC_UUID + ", "
        query = query + Constants.TABLE_USER_LOG_FIELD_CAPSULE_UUID + ", "
        query = query + Constants.TABLE_USER_LOG_FIELD_SLIDE_UUID + ", "
        query = query + Constants.TABLE_USER_LOG_FIELD_ACTIVITY + ", "
        query = query + Constants.TABLE_USER_LOG_FIELD_ADDED_ON + " ) "
        query = query + " VALUES (?, ?, ?, ?, ?, ?, ?);"
        
        var statement : OpaquePointer?
        
           
        if (sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK) {
                  
            sqlite3_bind_text(statement, 1, record.companyUuid, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 2, record.userUuid, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 3, record.topicUuid, -1, SQLITE_TRANSIENT )
            sqlite3_bind_text(statement, 4, record.capsuleUuid, -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 5, record.slideUuid, -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 6, record.activity , -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 7, record.addedOn , -1, SQLITE_TRANSIENT)

            if (sqlite3_step(statement) != SQLITE_DONE) {
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_USER_LOG)")
               
            } else {
                print("Insertamos un registo de log")
               
            }
        } else {
            print("Fallo la preparación en el insertar un registro en la tabla: \(Constants.TABLE_USER_LOG)")
           
        }
       
        sqlite3_finalize(statement)
    }

    func remove(id: Int) {
        let db = database.open()
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG + " WHERE " + Constants.TABLE_USER_LOG_FIELD_ID + " = " + "\(id) ;"
        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 id: \(id) en la tabla: \(Constants.TABLE_USER_LOG)")
               
            }
        } else {
            print("No se pudo borrar el registro con id: \(id) en la tabla: \(Constants.TABLE_USER_LOG)")
           
        }
        sqlite3_finalize(statement)
    }

    func removeAll() {
        let db = database.open()
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG + ";"
        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_LOG)")
               
            }
        } else {
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG)")
           
        }
        sqlite3_finalize(statement)
    }
    
    func removeAllUserUuidNotEqual(userUuid : String) {
        let db = database.open()
        let query = "DELETE FROM " + Constants.TABLE_USER_LOG
            + " WHERE " + Constants.TABLE_USER_LOG_FIELD_USER_UUID + " <> '" + userUuid + "' ";
        
        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_LOG) " +
                        " de usuarios diferents a : \(userUuid) ")
               
                
            } else {
                print("Se borraron todos los registros en la tabla: \(Constants.TABLE_USER_LOG) " +
                        " de usuarios diferents a : \(userUuid) ")
               
            }
        } else {
            print("Fallo la preparación de borrar todos los registros en la tabla: \(Constants.TABLE_USER_LOG) de los usuarios diferentes a : \(userUuid)")
           
        }
        sqlite3_finalize(statement)
    }

    
}