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

class reports_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 cuentass.
*
* Consulta de cuentass registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de cuentass registrados
* @param Int $datos id del usuario
*/
public function query_bank_accounts($datos){
    $consulta=$this->db->query("SELECT b.id_account as id,b.holder_account as titular,b.number_account as num_cuenta,ban.name_bank as banco FROM bank_accounts as b INNER JOIN banks as ban ON (b.id_bank=ban.id_bank) ");

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

/**
* Consulta de saldo en cuentass.
*
* Consulta de saldo en cuentass registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de saldo en cuentass registrados
* @param Int $datos id del usuario
*/
public function query_balances($datos,$fecha){
    $consulta=$this->db->query("SELECT s.id_balance as id,s.balance_account as saldo,b.balance_account as saldo_inicial,s.id_status as status FROM accounts_balance as s INNER JOIN initial_balance as b ON (s.id_balance=b.id_balance) WHERE s.id_account='$datos' AND s.date_balance='$fecha' ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}

/**
* Consulta de saldo en cuentass.
*
* Consulta de saldo en cuentass registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de saldo en cuentass registrados
* @param Int $datos id del usuario
*/
public function query_balance($datos){
    $consulta=$this->db->query("SELECT s.id_balance as id,s.balance_account as saldo,b.balance_account as saldo_inicial FROM accounts_balance as s INNER JOIN initial_balance as b ON (s.id_balance=b.id_balance) WHERE s.id_balance='$datos' ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}

/**
* Consulta de saldo en cuentass.
*
* Consulta de saldo en cuentass registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de saldo en cuentass registrados
* @param Int $datos id del usuario
*/
public function query_system_payment($datos,$fecha){
    $consulta=$this->db->query("SELECT s.total_deposits as depositos,s.total_transfers as transferencia,SUM(s.amount_deposits+s.amount_transfers) as total,s.amount_deposits as soles_depositos,s.amount_transfers as soles_transferencia,SUM(s.total_deposits+s.total_transfers) as pagos_total FROM system_payments as s WHERE s.id_account='$datos' AND s.date_payment='$fecha' ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}

/**
* Consulta de saldo en cuentass.
*
* Consulta de saldo en cuentass registrados en caso de recibir parametro not null la consulta se hace por id*
*
* @return Array de saldo en cuentass registrados
* @param Int $datos id del usuario
*/
public function query_movement($datos,$opcion){
    $consulta=$this->db->query("SELECT  b.id_balance as id,(SELECT COUNT(id_transaction)  FROM bank_transactions WHERE transaction='$opcion' AND type_transaction='1' AND id_balance='$datos' AND id_status !='3' ) AS depositos,(SELECT COUNT(id_transaction)  FROM bank_transactions WHERE transaction='$opcion' AND type_transaction='2' AND id_balance='$datos' AND id_status !='3' ) AS transferencias, SUM(b.amount) as soles_total FROM bank_transactions as b WHERE b.id_status !='3' AND b.id_balance='$datos' AND b.transaction='$opcion' ");

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

/**
* Consulta de detalle en cuentas con saldo .
*
*
* @return Array de saldo en cuentass registrados
* @param Int $datos id del usuario
*/
public function query_account_balance($datos){
    $consulta=$this->db->query("SELECT s.id_balance as id,s.balance_account as saldo,s.date_balance as fecha,sal.balance_account as saldo_inicial,b.holder_account as titular,b.number_account as num_cuenta,ban.name_bank as banco FROM accounts_balance as s INNER JOIN initial_balance as sal ON (s.id_balance=sal.id_balance) INNER JOIN bank_accounts as b ON (s.id_account=b.id_account) INNER JOIN banks as ban ON (b.id_bank=ban.id_bank) WHERE s.id_balance='$datos' ");
    $query=[];
    while($filas=$consulta->fetch_assoc()){
        $query[]=$filas;
    }
    return $query;
}

/**
* 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_transactions($datos){

    $consulta=$this->db->query("SELECT b.id_transaction as id,b.transaction 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,b.new_amount as monto_actual FROM bank_transactions as b WHERE b.id_balance ='$datos' AND b.id_status !='3' ");

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


}
?>