Proyectos de Subversion Iphone Microlearning - Inconcert

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

import SwiftUI
import SQLite3

class ProgressDao {
    private var database = Environment(\.database).wrappedValue
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
    
    
    func selectByTopicUuid(topicUuid: String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query +  " FROM " +  Constants.TABLE_PROGRESS
        query = query +  " WHERE " +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)'  "
        query = query +  " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_TOPIC)' LIMIT 1 ;"

        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }
    
    
    func selectByTopicUuidAndUserUuid(topicUuid: String, userUuid : String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query +  " FROM " +  Constants.TABLE_PROGRESS
        query = query +  " WHERE " +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)'  "
        query = query +  " AND " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)'  "
        query = query +  " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_TOPIC)' LIMIT 1 ;"

        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }
    
    func selectByCapsuleUuid(capsuleUuid: String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query +  " FROM " +  Constants.TABLE_PROGRESS
        query = query +  " WHERE " +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " = '\(capsuleUuid)'  "
        query = query +  " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_CAPSULE)' LIMIT 1 ;"
       
        
        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
                
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
        
    }
    
    func selectByCapsuleUuidAndUserUuid(capsuleUuid: String, userUuid : String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query  = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query +  " FROM " +  Constants.TABLE_PROGRESS
        query = query +  " WHERE " +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " = '\(capsuleUuid)'  "
        query = query +  " AND " + Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)' "
        query = query +  " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_CAPSULE)' LIMIT 1 ;"

        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
                
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
        
    }

    func selectBySlideUuid(slideUuid: String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query + " FROM " +  Constants.TABLE_PROGRESS
        query = query  + " WHERE " +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " = '\(slideUuid)'  "
        query = query  + " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_SLIDE)' LIMIT 1 ;"
               
        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }
    
    
    func selectBySlideUuidAndUserUuid(slideUuid: String, userUuid : String)-> ProgressModel {
        let db = database.open()
        //var records = [ProgressModel]()
        var model = ProgressModel()
        var query = "SELECT "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_UPDATED_ON
        query = query + " FROM " +  Constants.TABLE_PROGRESS
        query = query  + " WHERE " +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + " = '\(slideUuid)'  "
        query = query  + " AND " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)'  "
        query = query  + " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + " = '\( Constants.PROGRESS_TYPE_SLIDE)' LIMIT 1 ;"
               
        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.companyUuid = String(describing: String(cString: sqlite3_column_text(statement, 1)))
                model.userUuid = 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.totalSlides = Int(sqlite3_column_int(statement, 6))
                model.viewSlides = Int(sqlite3_column_int(statement, 7))
                model.progress = Double(sqlite3_column_double(statement, 8))
                model.type = String(describing: String(cString: sqlite3_column_text(statement, 9)))
                model.returning = Int(sqlite3_column_int(statement, 10))
                model.returningAfterCompleted = Int(sqlite3_column_int(statement, 11))
                model.completed = Int(sqlite3_column_int(statement, 12))
                model.addedOn = String(describing: String(cString: sqlite3_column_text(statement, 13)))
                model.updatedOn = String(describing: String(cString: sqlite3_column_text(statement, 14)))
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return model
    }

    func getCountSlideCompletedByCapsuleUuidAndUserUuid(capsuleUuid: String, userUuid: String) -> Int {
        let db = database.open()
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS +
        " WHERE " +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + " = '\(capsuleUuid)' "  +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "= '\(userUuid)'" +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\(Constants.PROGRESS_TYPE_SLIDE)'" +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "  =  1 "
        
        var statement : OpaquePointer? = nil
        var count = 0;
        
            
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count;
    }
    
    func getCountSlideCompletedByTopicUuidAndUserUuid(topicUuid: String, userUuid: String) -> Int {
        let db = database.open()
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS +
        " WHERE " +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)' "  +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + "= '\(userUuid)'" +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\(Constants.PROGRESS_TYPE_SLIDE)'" +
        " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "  =  1 "
        
        var statement : OpaquePointer? = nil
        var count = 0;
        
            
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count;
    }
    
    func getCountCapsulesCompletedByUserUuid(userUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS +
            " WHERE " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesIncompletedByUserUuid(userUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS +
            " WHERE " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 0 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesCompletedByTopicUuid(topicUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS +
            " WHERE " +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesCompletedWithReturningByUserUuid(userUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT SUM(" +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + ") AS total FROM " +  Constants.TABLE_PROGRESS
            + " WHERE " +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " > 0 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesCompletedWithoutReturningByUserUuid(userUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS + " WHERE "
            +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + " = '\(userUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " = 0 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesCompletedWithReturningByTopicUuid(topicUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS
            + " WHERE " +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " > 0 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
    
    func getCountCapsulesCompletedWithoutReturningByTopicUuid(topicUuid : String) -> Int {
        let db = database.open()
        var count = 0
        let query = "SELECT COUNT(*) AS total FROM " +  Constants.TABLE_PROGRESS + " WHERE "
            +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID + " = '\(topicUuid)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_TYPE + "= '\( Constants.PROGRESS_TYPE_CAPSULE)' " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + "= 1 " +
            " AND " +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " = 0 LIMIT 1"

        var statement : OpaquePointer? = nil
        
        
        if (sqlite3_prepare(db, query, -1, &statement, nil) == SQLITE_OK) {
            if(sqlite3_step(statement) == SQLITE_ROW){
                count = Int(sqlite3_column_int(statement, 0))
                //print("\(count)")
            }
        } else {
           
        }
        sqlite3_finalize(statement)
        return count
    }
        
        
    func insert( record : ProgressModel) {
        let db = database.open()
        var query = "INSERT INTO " +  Constants.TABLE_PROGRESS + " ( "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPANY_UUID + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_USER_UUID + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOPIC_UUID  + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_CAPSULE_UUID + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_SLIDE_UUID + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_TYPE + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + ", "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " , "
        query = query +  Constants.TABLE_PROGRESS_FIELD_ADDED_ON + ", "
        query = query +  Constants.TABLE_PROGRESS_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, 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_int(statement, 6, Int32(record.totalSlides))
            sqlite3_bind_int(statement, 7, Int32(record.viewSlides))
            sqlite3_bind_double(statement, 8, Double(record.progress))
            sqlite3_bind_text(statement, 9, record.type , -1, SQLITE_TRANSIENT)
            sqlite3_bind_int(statement, 10, Int32(record.returning))
            sqlite3_bind_int(statement, 11, Int32(record.returningAfterCompleted))
            sqlite3_bind_int(statement, 12, Int32(record.completed))
            sqlite3_bind_text(statement, 13, record.addedOn, -1, SQLITE_TRANSIENT)
            sqlite3_bind_text(statement, 14, record.updatedOn, -1, SQLITE_TRANSIENT)
            
           if sqlite3_step(statement) != SQLITE_DONE {
                print("No se pudo insertar un registro en la tabla: \(Constants.TABLE_PROGRESS)")
               
               
           } else {
             print("insertamos un registro de progreso")
           }
        } else {
            print("No se pudo preparar insertar un registro en la tabla: \(Constants.TABLE_PROGRESS)")
           
        }
       
        sqlite3_finalize(statement)
    }

    func update(record : ProgressModel) {
        let db = database.open()
        var query = "UPDATE " +  Constants.TABLE_PROGRESS
        query = query + " SET " +  Constants.TABLE_PROGRESS_FIELD_TOTAL_SLIDES + " = '\(record.totalSlides)', "
        query = query +  Constants.TABLE_PROGRESS_FIELD_VIEW_SLIDES + " = '\(record.viewSlides)', "
        query = query +  Constants.TABLE_PROGRESS_FIELD_PROGRESS + " = '\(record.progress)', "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING + " = '\(record.returning)', "
        query = query +  Constants.TABLE_PROGRESS_FIELD_RETURNING_AFTER_COMPLETED + " = '\(record.returningAfterCompleted)', "
        query = query +  Constants.TABLE_PROGRESS_FIELD_COMPLETED + " = '\(record.completed)' "
        query = query + " WHERE " +  Constants.TABLE_PROGRESS_FIELD_ID + " = \(record.id);"
        
        //print("capsuleUpdate : \(query)")
        
        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_PROGRESS)")
               
            }
        } else {
            print("Fallo la preparación de actualizar un registro en la tabla: \(Constants.TABLE_PROGRESS)")
           
        }
        sqlite3_finalize(statement)
    }

    func remove(id: Int) {
        let db = database.open()
        let query = "DELETE FROM " +  Constants.TABLE_PROGRESS + " WHERE " +  Constants.TABLE_PROGRESS_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 un registro con el id: \(id) en la tabla: \(Constants.TABLE_PROGRESS)")
               
            }
        } else {
            print("Fallo la preparación de borrar un registro con el id: \(id) en la tabla: \(Constants.TABLE_PROGRESS)")
           
        }
        sqlite3_finalize(statement)
    }

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

}