<?php

class stats {
    
    public static function countStatistiquesGenerale(int $_actifOnly = 1){
        $actif = ($_actifOnly == 1) ? " AND actif = 1" : "";
        db::query("SELECT "
                . "SUM(IF(sexe = 'F'" . $actif . ", 1, 0)) AS Femme, "
                . "SUM(IF(sexe = 'M'" . $actif . ", 1, 0)) AS Homme, "
                . "SUM(IF(contrat = 1" . $actif . ", 1, 0)) AS ContratOn, "
                . "SUM(IF(contrat = 0" . $actif . ", 1, 0)) AS ContratOff, "
                . "SUM(IF(actif = 1, 1, 0)) AS ActifOn, "
                . "SUM(IF(actif = 0, 1, 0)) AS ActifOff, "
                . "SUM(IF(lieu = 'ISSY - 147', 1, 0)" . $actif . ") AS lieuParis147, "
                . "SUM(IF(lieu = 'LYON (IVOIRE)', 1, 0)" . $actif . ") AS lieuLyonIvoire, "
                . "SUM(IF(lieu = 'PEROLS', 1, 0)" . $actif . ") AS lieuPerols, "
                . "SUM(IF(lieu = 'PARIS CAMBACERE', 1, 0)" . $actif . ") AS lieuParisCambaceres, "
                . "SUM(IF(lieu = 'BLAGNAC CENT. 3', 1, 0)" . $actif . ") AS lieuToulouseBalgnac, "
                . "SUM(IF(lieu = 'PARIS-BOUCHARDO', 1, 0)" . $actif . ") AS lieuParisBouchardo "
                . "FROM " . DB_T_SALARIES);
        return db::single();
    }
    
    private static function byLieu(string $_lieu){
        switch ($_lieu) {
            case "147":
            return "ISSY - 147";
                break;
            case "LYON":
            return "LYON (IVOIRE)";   
                break;
            case "PEROLS":
            return "PEROLS";
                break;
            case "CAMBACERE":
            return "PARIS CAMBACERE";
                break;
            case "BLAGNAC":
            return "BLAGNAC CENT. 3";
                break;
            case "BOUCHARDO":
            return "PARIS-BOUCHARDO";
                break;
            default:
            return NULL;  
        }
    }

    public static function countStatistiquesGenraleEntrees(string $_lieu = NULL, int $_actifOnly = 1){
        $return = NULL;
        $actif = ($_actifOnly == 1) ? " WHERE contrat = 1" : "";
        $lieu = ($_lieu == NULL) ? "" : " AND lieu = '" . self::byLieu($_lieu) . "'";
        
        db::query("SELECT SUBSTR(jourEntree, 1 , 4) AS anEntree FROM " . DB_T_SALARIES . $actif . $lieu);
        foreach (db::resultset() as $value) {
            $return[$value["anEntree"]] = (empty($return[$value["anEntree"]])) ? 1 : $return[$value["anEntree"]]+1;
        }
        if(!empty($return)){
            ksort($return);
        } else {
            $return = array(date("Y") => 0);
        }
        
        return $return;
    }
    
    public static function countStatistiquesByLieu(string $_lieu){
        $lieu = self::byLieu($_lieu);
        
        if($lieu == NULL){
            return FALSE;
        }

        db::query("SELECT "
                . "SUM(IF(sexe = 'F' AND actif = 1, 1, 0)) AS Femme, "
                . "SUM(IF(sexe = 'M' AND actif = 1, 1, 0)) AS Homme, "
                . "SUM(IF(contrat = 1 AND actif = 1, 1, 0)) AS ContratOn, "
                . "SUM(IF(contrat = 0 AND actif = 1, 1, 0)) AS ContratOff, "
                . "SUM(IF(actif = 1, 1, 0)) AS ActifOn, "
                . "SUM(IF(actif = 0, 1, 0)) AS ActifOff "
                . "FROM " . DB_T_SALARIES . " "
                . "WHERE lieu = :lieu");
        db::bind(':lieu', $lieu);
        return db::single();
    }
    
}