<?php
class dashboard extends Conectar {

public function get_total_usuarios() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(*) as total FROM tm_ficha WHERE fic_estado = 1";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

public function get_total_equipos() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(*) as total FROM tm_colaborador WHERE estado_colab = 1";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

public function get_total_asignados() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT SUM(valor_session) AS total FROM tm_agenda WHERE YEAR(fec_session) = YEAR(CURDATE()) AND MONTH(fec_session) = MONTH(CURDATE()) AND estado > 1;";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

public function get_horas_colab($id_colab, $mes, $anio) {
        $conectar = parent::conexion();
        parent::set_names();
        $sql = "SELECT COUNT(*) AS total 
                FROM tm_agenda 
                WHERE estado > 1 
                  AND id_colab = ? 
                  AND MONTH(fec_session) = ? 
                  AND YEAR(fec_session) = ?";
        $stmt = $conectar->prepare($sql);
        $stmt->execute([$id_colab, $mes, $anio]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

  public function get_sessiones_colab($id_colab, $mes, $anio) {
        $conectar = parent::conexion();
        parent::set_names();
        $sql = "SELECT SUM(valor_colab) AS total 
                FROM tm_agenda 
                WHERE estado > 1 
                  AND id_colab = ? 
                  AND MONTH(fec_session) = ? 
                  AND YEAR(fec_session) = ?";
        $stmt = $conectar->prepare($sql);
        $stmt->execute([$id_colab, $mes, $anio]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function get_total_sessiones_retencion($id_colab, $mes, $anio) {
        $conectar = parent::conexion();
        parent::set_names();
        $sql = "SELECT ROUND(SUM(valor_colab) * 0.145, 2) AS retencion 
                FROM tm_agenda 
                WHERE estado > 1 
                  AND id_colab = ? 
                  AND MONTH(fec_session) = ? 
                  AND YEAR(fec_session) = ?";
        $stmt = $conectar->prepare($sql);
        $stmt->execute([$id_colab, $mes, $anio]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function get_total_a_recibir($id_colab, $mes, $anio) {
        $conectar = parent::conexion();
        parent::set_names();
        $sql = "SELECT ROUND(SUM(valor_colab) - (SUM(valor_colab) * 0.145), 2) AS tfinal 
                FROM tm_agenda 
                WHERE estado > 1 
                  AND id_colab = ? 
                  AND MONTH(fec_session) = ? 
                  AND YEAR(fec_session) = ?";
        $stmt = $conectar->prepare($sql);
        $stmt->execute([$id_colab, $mes, $anio]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
public function count_total_alumnos() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(*) as total FROM tm_ficha WHERE fic_estado = 1 AND fic_valor_mes <> 0.00";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}
public function count_total_pacientes() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(DISTINCT fic_id) AS total FROM tm_agenda WHERE estado > 1 ;";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

public function total_alumnos_valor_mes() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT SUM(monto) AS total FROM tm_pagos_mensuales WHERE YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE());";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}
public function get_sessiones_pagas() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(*) AS total FROM tm_agenda WHERE fec_session >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND fec_session < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01') AND estado = 3;";

    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}
public function get_sessiones_total() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT COUNT(*) AS total FROM tm_agenda WHERE fec_session >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND fec_session < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01') AND estado > 1;";

    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}
public function get_sessiones_pagas_s() {
    $conectar = parent::conexion();
    parent::set_names();
    $sql = "SELECT SUM(valor_session) AS total FROM tm_agenda WHERE YEAR(fec_session) = YEAR(CURDATE()) AND MONTH(fec_session) = MONTH(CURDATE()) AND estado = 3;";
    $stmt = $conectar->prepare($sql);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

}