<?php
require_once('includes/definition.php');

class bank_transactions_model{
    private $db;
    private $query;
    private $validatename;
    private $validatename1;
    private $validatename2;

    public function __construct(){
        $this->db=Conectar::conexion();
        $this->query=array();
        $this->validatename=array();
        $this->validatename1=array();
        $this->validatename2=array();
    }

/**
* Consulta de bank_transactionss.
*
* Consulta de bank_transactionss registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de bank_transactionss registrados
* @param Int $datos id del usuario
*/
public function query_bank_transactions($datos,$fecha){
    if (is_null($datos)) {   
        $consulta=$this->db->query("SELECT b.id_transaction as id,IF(b.transaction = 1, 'Ingreso', 'Egreso') as transaccion,IF(b.type_transaction = 1, 'Deposito', 'Transferencia') as tipo_t,b.operation_number as numero_o,b.amount as monto,b.date_transaction as fecha,DATE_FORMAT(b.time_transaction, '%h:%i %p') as hora,b.file_transaction as archivo,b.reason_transaction as motivo,c.holder_account as titular,c.number_account as num_cuenta,ban.name_bank as banco FROM bank_transactions as b INNER JOIN bank_accounts as c ON (b.id_account=c.id_account) INNER JOIN banks as ban ON (c.id_bank=ban.id_bank) WHERE b.id_status !='3' AND b.date_transaction='$fecha' ");
    }else{
        $consulta=$this->db->query("SELECT b.id_transaction as id,b.transaction as transaccion,b.time_transaction as hora,b.operation_number as numero_o,b.amount as monto,b.type_transaction as tipo,b.reason_transaction as motivo,b.id_account as cuenta,b.file_transaction as archivo FROM bank_transactions as b WHERE b.id_transaction='$datos' AND b.id_status !='3'");
    }
    while($filas=$consulta->fetch_assoc()){
        $this->query[]=$filas;
    }
    return $this->query;
}

/**
* Consulta de bancos.
*
* Consulta de bancos registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de bancos registrados
* @param Int $datos id del usuario
*/
public function query_bank_accounts($datos){
    $consulta=$this->db->query("SELECT 1 FROM bank_accounts as b WHERE b.id_account='$datos' AND b.id_status=1  ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}
/**
* Chequear si nombre de bank_transactions existe.
*
*Funcion para consultar nombre de bank_transactions registrado y asi evitar duplicidad ya que es un campo unico Si recibe parametro id not null es para indicar que ignore ese id en la consulta*
*
* @return Boolean false si existe true lo contrario 
* @param String $datos nombre de bank_transactions,Int $id del bank_transactions
*/
public function check_operation($datos,$id){
    $datos=trim($datos);
    if (is_null($id)) {
        $consulta=$this->db->query("SELECT 1 FROM bank_transactions WHERE operation_number='$datos'");
    }else{
        $consulta=$this->db->query("SELECT 1 FROM bank_transactions WHERE operation_number='$datos' AND id_transaction !='$id' ");
    }
    $consulta2=$consulta->num_rows;
    if ($consulta2 > 0) {
        return false;
    }
    return true;
}
/**
* Consulta de bancos.
*
* Consulta de bancos registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de bancos registrados
* @param Int $datos id del usuario
*/
public function query_balance($datos){
    $consulta=$this->db->query("SELECT b.id_balance as id,b.balance_account as saldo FROM accounts_balance as b WHERE b.id_account='$datos' AND b.id_status=1  ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}
/**
* Registrar bank_transactions.
*
*Funcion para registrar bank_transactions*
*
* @return Boolean  
*/
public function register_bank_transaction($cuentas_bancarias,$transaccion,$hora,$n_operacion,$monto,$tipo,$motivo,$comprobante,$user){
    $date=date('Y-m-d');
    $create_date=date('Y-m-d H:i:s');
    $name_archivo = str_replace(" ", "_", $comprobante['name']).$create_date;
    $ext_img = pathinfo($comprobante['name'], PATHINFO_EXTENSION);
    $name_image=$name_archivo;
    //Query Saldo//
    $saldo_total=$this->query_balance($cuentas_bancarias);
    $saldo=$saldo_total[0]["saldo"];
    $balance=$saldo_total[0]["id"];
    $suma=($transaccion=="2") ? $saldo-$monto : $saldo+$monto;
    $suma=number_format($suma,2,".","");
    //Insert//
    $insert=$this->db->query("INSERT INTO bank_transactions(id_account,transaction,type_transaction,operation_number,date_transaction,time_transaction,amount,file_transaction,reason_transaction,id_user,create_date,id_balance,new_amount,before_amount) VALUES ('$cuentas_bancarias','$transaccion','$tipo','$n_operacion','$date','$hora','$monto','$name_image','$motivo','$user','$date','$balance','$suma','$saldo')");
    if ($insert) {
    $update=$this->db->query("UPDATE accounts_balance SET balance_account='$suma' WHERE id_balance='$balance'");
    $location=$_SESSION['ruta_img'].'bank_transactions_img/'.$name_image;
    move_uploaded_file($comprobante['tmp_name'],$location);
    }
    return $insert;
}
/**
* Chequear si bank_transaction.
*
*Funcion para validar si bank_transaction existe*
*
* @return Boolean  
* @param Int $id id de bank_transaction
*/
public function check_account_balance($cuenta){
    $consulta=$this->db->query("SELECT 1 FROM accounts_balance WHERE id_account='$cuenta' AND id_status ='1'");
    $consulta1=$consulta->num_rows;
    if ($consulta1 == 0) {
        return false;
    }
    return true;
}
/**
* Chequear si bank_transaction.
*
*Funcion para validar si bank_transaction existe*
*
* @return Boolean  
* @param Int $id id de bank_transaction
*/
public function check_bank_transaction($id){
    $consulta=$this->db->query("SELECT 1 FROM bank_transactions as b INNER JOIN accounts_balance as c ON (b.id_balance=c.id_balance) WHERE b.id_transaction='$id' AND b.id_status !='3' AND c.id_status='1' ");
    $consulta1=$consulta->num_rows;
    if ($consulta1 == 0) {
        return false;
    }
    return true;
}
/**
* Consulta de bancos.
*
* Consulta de bancos registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de bancos registrados
* @param Int $datos id del usuario
*/
public function query_max_transaction($datos){
    $consulta=$this->db->query("SELECT MAX(id_transaction) as id FROM bank_transactions WHERE id_account='$datos' AND id_status !='3' ");
    //$query=[];
    while($filas=$consulta->fetch_assoc()){
        $query=$filas['id'];
    }
    return $query;
}
/**
* Registrar bank_transactions.
*
*Funcion para registrar bank_transactions*
*
* @return Boolean  
*/
public function update_balance($suma,$balance,$user){
    $create_date=date('Y-m-d H:i:s');
    $update=$this->db->query("UPDATE accounts_balance SET balance_account='$suma',id_user='$user',create_date='$create_date' WHERE id_balance='$balance'");
    return $update;
}
/**
* Eliminar bank_transactions.
*
*Funcion para eliminar bank_transactions*
*
* @return Boolean  
* @param Int $id id de bank_transactions
*/
public function delete_bank_transaction($id,$user){
    /*$delete=$this->db->query("DELETE FROM bank_transactions WHERE id_transaction='$id'");
    if ($delete) {
    $nombre=$this->query_bank_transactions($id);
    $nombre=$nombre['image'];
    $location=$_SESSION['ruta_img'].'bank_transactions_img/'.$nombre;
    unlink($location);
    }*/
    $create_date=date('Y-m-d H:i:s');
    $update=$this->db->query("UPDATE bank_transactions SET id_status='3',id_user='$user',create_date='$create_date' WHERE id_transaction='$id'");

    return $update;
}

/**
* Consulta de bank_transactionss.
*
* Consulta de bank_transactionss registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de bank_transactionss registrados
* @param Int $datos id del usuario
*/
public function query_bank_transactions_delete(){
  
  $consulta=$this->db->query("SELECT b.id_transaction as id,IF(b.transaction = 1, 'Ingreso', 'Egreso') as transaccion,IF(b.type_transaction = 1, 'Deposito', 'Transferencia') as tipo_t,b.operation_number as numero_o,b.amount as monto,b.date_transaction as fecha,DATE_FORMAT(b.time_transaction, '%h:%i %p') as hora,b.file_transaction as archivo,b.reason_transaction as motivo,c.holder_account as titular,c.number_account as num_cuenta,ban.name_bank as banco,u.user_name as usuario FROM bank_transactions as b INNER JOIN bank_accounts as c ON (b.id_account=c.id_account) INNER JOIN banks as ban ON (c.id_bank=ban.id_bank) INNER JOIN users as u ON (b.id_user=u.id_user) WHERE b.id_status ='3' ");

    while($filas=$consulta->fetch_assoc()){
        $this->query[]=$filas;
    }
    return $this->query;
}

}
?>