<?php
namespace Controller;

use LQDN\Command\AdminCreateCommand;
use LQDN\Command\AdminDeleteCommand;
use LQDN\Command\AdminChangePasswordCommand;
use LQDN\Command\CounterpartCreateCommand;
use LQDN\Command\DonationValidateCommand;
use LQDN\Command\DonationInvalidateCommand;
use LQDN\Command\DonationResetPdfCommand;
use LQDN\Command\DonationCreateCommand;
use LQDN\Command\UserUpdateByAdminCommand;
use LQDN\Command\UserCreateCommand;
use LQDN\Command\UserUpdateCumulCommand;
use LQDN\Command\AdminUpdateParentCommand;
use LQDN\Command\AdminUpdateTotalUsersCommand;

class Admin extends Controller
{
    // Fonction appelée avant routage
    public function beforeRoute($f3, $args)
    {
        parent::beforeRoute($f3, $args);

        // Template de base par défaut
        $this->template = "backend/base.html";

        // URL de base à utiliser sur le site
        $f3->set('DONS_STATUTS', array(
            0 => "Ponctuel non validé",
            1 => "Ponctuel validé",
            4 => "Récurrent validé",
            42 => "En cours de traitement",
            100 => "Récurrent non validé",
            101 => "Récurrent validé",
            102 => "Récurrent remis",
        ));

        $f3->set('CT_STATUTS', array(
            1 => "Demandé",
            2 => "Envoyé",
            3 => "NPAI",
            99 => "Annulé",
        ));

        $f3->set('CB_STATUTS', array(
            1 => "en cours",
            2 => "à relancer",
            3 => "relancé",
            99 => "résilié",
            100 => "non applicable",
        ));
        
        $f3->set('TAILLES', array(
            0 => _('PlaceHolder -- ne pas utiliser') . ' S',
            1 => _('Coupe Homme, Taille') . ' S',
            2 => _('Coupe Homme, Taille') . ' M',
            3 => _('Coupe Homme, Taille') . ' L',
            4 => _('Coupe Homme, Taille') . ' XL',
            5 => _('Coupe Femme, Taille') . ' S',
            6 => _('Coupe Femme, Taille') . ' M',
            7 => _('Coupe Femme, Taille') . ' L',
            8 => _('Coupe Femme, Taille') . ' XL',
            9 => _('Coupe Homme, Taille') . ' XXL',
            10 => _('Coupe Femme, Taille') . ' XXL',
        ));

        if (!$f3->get('SESSION.admin')) {
            $mapper = new\DB\SQL\Mapper($f3->get('DB'), 'admins');
            $auth = new \Auth($mapper, array('id'=>'user_id', 'pw'=>'password'));
            $success = $auth->basic('\Controller\Admin::hash_password');
            if (!$success) {
                $f3->push('SESSION.error', 'Accès non autorisé');
                $f3->push('SESSION.message', '');
                $f3->error(401);
            } else {
                $f3->set('SESSION.admin', true);
            }
        };

        $f3->set('PIPLOME_URL', PIPLOME_URL);
    }

    public function afterRoute($f3, $args)
    {
        // Test si il y a des messages
        if (!$f3->exists('SESSION.message')) {
            $f3->push('SESSION.message', '');
        }
        if (!$f3->exists('SESSION.error')) {
            $f3->push('SESSION.error', '');
        }

        // Rendu de la page
        if ($this->template!='') {
            echo \Template::instance()->render($this->template);
        }
        // Une fois que tout est affiché, on peut supprimer les notifications
        $f3->clear('SESSION.message');
        $f3->clear('SESSION.error');
    }


    public static function hash_password($password)
    {
        return hash('sha256', $password);
    }

    public function show($f3, $args)
    {
        if ($f3->get('VERB')=='POST' && $f3->exists('POST.action')) {
            switch ($f3->get('POST.action')) {
            case 'valid':
                $f3->get('container')['command_handler']->handle(new DonationValidateCommand($f3->get('POST.id')));
                break;
            case 'invalid':
                $f3->get('container')['command_handler']->handle(new DonationInvalidateCommand($f3->get('POST.id')));
                break;
            case 'pdf':
                $f3->get('container')['command_handler']->handle(new DonationResetPdfCommand($f3->get('POST.id')));
                break;
            }
        }

        $text = $f3->exists('POST.texte') ? $f3->get('POST.texte') : '';
        $sum = $f3->exists('POST.somme') ? $f3->get('POST.somme') : '';
        $public = $f3->exists('POST.public') ? $f3->get('POST.public') : '';
        $status = $f3->exists('POST.status') ? $f3->get('POST.status') : '';
        $limit = $f3->exists('POST.limite') ? (int) $f3->get('POST.limite') : 50;
        $page = $f3->exists('POST.page') ? (int) $f3->get('POST.page') : 1;
        $date1 = $f3->exists('POST.date1') ? (string) $f3->get('POST.date1') . " 00:00:00" : '';
        $date2 = $f3->exists('POST.date2') ? (string) $f3->get('POST.date2') . " 00:00:00" : date("Y-m-d H:i:s");

        $results = $f3->get('container')['donation_finder']->adminSearch($text, $sum, $public, $status, $date1, $date2, $limit, $page);

        $f3->set('row_count', count($results));
        $f3->set('texte', $text);
        $f3->set('somme', $sum);
        $f3->set('limite', $limit);
        $f3->set('page', $page);
        $f3->set('public', $public);
        $f3->set('status', $status);
        $f3->set('date1', substr($date1, 0, 10));
        $f3->set('date2', substr($date2, 0, 10));
        $f3->set('data', $results);
        $f3->set('piplome_url', PIPLOME_URL);

        $f3->set('block_content', 'backend/dons.html');
    }

    public function validate($f3, $args)
    {
        $id = '';
        $error = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='GET') {
            if ($f3->exists('PARAMS.id')) {
                $id = $f3->get('PARAMS.id');
            } else {
                $error= 'Pas d\'id associé à ce don';
            }
            if ($id != '') {
                $result = $db->query("SELECT status,
                    somme,
                    adresse_id,
                    user_id
                    FROM dons
                    WHERE id = ".\Utils::asl($id));
                $result = $result->fetch(\PDO::FETCH_ASSOC);
                $user_amount = $db->query("SELECT total, cumul FROM users
                    WHERE id = ".\Utils::asl($result['user_id']));
                $user_amount = $user_amount->fetch(\PDO::FETCH_ASSOC);
            }
            if ($result['status'] >= 100) {
                $status = 101;
            } else {
                $status = 1;
            }
            $total = $user_amount['total'] + $result['somme'];
            $cumul = $user_amount['cumul'] + $result['somme'];
        }
        if ($error != '') {
            $f3->push('SESSION.error', $error);
        } else {
            $db->query(
                "UPDATE dons
                SET status = $status
                WHERE id = ".\Utils::asl($id)
            );
            $db->query("UPDATE users
                SET total = $total,
                cumul = $cumul
                WHERE id = ".\Utils::asl($result['user_id']));
        }
        $f3->reroute("/admin/users/".$result['user_id']);
    }


    public function invalidate($f3, $args)
    {
        $id = '';
        $error = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='GET') {
            if ($f3->exists('PARAMS.id')) {
                $id = $f3->get('PARAMS.id');
            } else {
                $error= 'Pas d\'id associé à ce don';
            }
            if ($id != '') {
                $result = $db->query("SELECT status,
                    somme,
                    adresse_id,
                    user_id
                    FROM dons
                    WHERE id = ".\Utils::asl($id));
                $result = $result->fetch(\PDO::FETCH_ASSOC);
                $user_amount = $db->query("SELECT total, cumul FROM users
                    WHERE id = ".\Utils::asl($result['user_id']));
                $user_amount = $user_amount->fetch(\PDO::FETCH_ASSOC);
            }
            if ($result['status'] >= 100) {
                $status = 100;
            } else {
                $status = 0;
            }
            $total = $user_amount['total'] - $result['somme'];
            $cumul = $user_amount['cumul'] - $result['somme'];
            if ($cumul < 0) {
                $error = "Impossible d'invalider, une contrepartie a été demandée";
            }
        }
        if ($error != '') {
            $f3->push('SESSION.error', $error);
        } else {
            $db->query(
                "UPDATE dons
                SET status = $status
                WHERE id = ".\Utils::asl($id)
            );
            $db->query("UPDATE users
                SET total = $total,
                cumul = $cumul
                WHERE id = ".\Utils::asl($result['user_id']));
        }
        $f3->reroute("/admin/users/".$result['user_id']);
    }

    public function support($f3, $args)
    {
        $id = '';
        $pseudo = '';
        $email = '';
        $somme = '';
        $lang = '';
        $abo = '0';
        $public = '0';
        $user_id = '';
        $msg = '';
        $adresse_id = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='GET') {
            if ($f3->exists('PARAMS.id')) {
                $id = $f3->get('PARAMS.id');
            }
            $f3->set('block_content', 'backend/don.html');
        } else {
            $id = $f3->get('POST.id');
            $pseudo = $f3->get('POST.pseudo');
            $email = $f3->get('POST.email');
            $somme = $f3->get('POST.somme');
            $lang = $f3->get('POST.lang');
            if ($f3->exists('POST.abo')) {
                $abo = '1';
            }
            if ($f3->exists('POST.public')) {
                $public = '1';
            }

            // Champs minimaux : pseudo, somme, email
            if ($pseudo=='' or $somme=='' or $email=='') {
                $msg = "Erreur: Les champs 'Pseudo', 'Montant' et 'Email' sont obligatoires";
            } else {
                // Mise à jour ou ajout du don
                if ($id=='') {
                    // We need to check if there's already a user with this email
                    $result = $db->query("SELECT id
                        FROM users
                        WHERE email='".\Utils::asl($email)."'");
                    if ($result) {
                        // The user already exist, lets get the user_id
                        $user_id = $result->fetch(\PDO::FETCH_ASSOC);
                        $user_id = $user_id['id'];
                    } else {
                        // Let's create the account first
                        $db->query("INSERT INTO users
                            (email,
                            pseudo,
                            cumul,
                            total)
                            VALUES('".\Utils::asl($email)."',
                                '".\Utils::asl($pseudo)."',
                                '".\Utils::asl($somme)."',
                                '0')");
                        $user_id = $db->lastInsertId();
                    }
                    $db->query("INSERT INTO dons
                        (datec,
                        user_id,
                        somme,
                        lang,
                        abo,
                        public,
                        status)
                        VALUES
                        (now(),
                            '".\Utils::asl($user_id)."',
                            '".\Utils::asl($somme)."',
                            '".\Utils::asl($lang)."',
                            '".\Utils::asl($abo)."',
                            '".\Utils::asl($public)."',
                            1)");
                    // Récupération de l'id pour passage en mode édition
                    $id = $db->lastInsertId();
                } else {
                    // We will get the ids associated to the gift first
                    $result = $db->query("SELECT adresse_id,
                        user_id
                        FROM dons
                        WHERE id = ".\Utils::asl($f3->get('POST.id')));
                    $result = $result->fetch(\PDO::FETCH_ASSOC);
                    // Let's update the adresse first or create the adress
                    $db->query("UPDATE users
                        SET email='".\Utils::asl($email)."',
                        pseudo='".\Utils::asl($pseudo)."'
                        WHERE id={$result['user_id']}");
                    $db->query("UPDATE dons
                        SET somme='".\Utils::asl($somme)."',
                        lang='".\Utils::asl($lang)."',
                        abo='".\Utils::asl($abo)."',
                        public='".\Utils::asl($public)."', 
                        adresse_id='".$result['adresse_id']."'
                        WHERE id='".\Utils::asl($_POST['id'])."'");
                }
            }
        }

        if ($id!='') {
            // Récupération du don, de l'adresse et de l'utilisateur
            $result = $db->query("SELECT d.id AS id,
                u.email AS email,
                u.pseudo AS pseudo,
                d.somme AS somme,
                d.lang AS lang,
                d.abo AS abo,
                d.public AS public,
                d.adresse_id AS adresse_id,
                d.user_id AS user_id
                FROM dons d
                JOIN users u ON u.id = d.user_id
                WHERE d.id=".$id);
            $don = $result->fetch(\PDO::FETCH_ASSOC);
        } else {
            $don = array(
                'id'=>$id,
                'email'=>$email,
                'pseudo'=>$pseudo,
                'somme'=>$somme,
                'lang'=>$lang,
                'abo'=>$abo,
                'public'=>$public,
                'user_id'=>$user_id,
            );
        }

        $f3->set('id', $id);
        $f3->set('msg', $msg);
        $f3->set('don', $don);

        $f3->set('block_content', 'backend/don.html');
    }

    public function counterparts_dashboard($f3, $args)
    {
        $db = $f3->get('DB');
        $f3->set('counterparts_import', '');
        $total = array("hoodie" => 0, "pishirt" => 0, "pibag" => 0, "piplome" => 0);

        if ($f3->get('VERB') == 'POST') {
            $separator = ';';
            // Si on a un POST sur cette page, c'est que l'on a un csv à parser
            if ($_FILES['file']['tmp_name'] == '') {
                $f3->push('SESSION.error', 'Veuillez uploader un fichier au format csv');
            } else {
                // Lecture du fichier
                if (($handle = fopen($_FILES['file']['tmp_name'], "r")) !== false) {
                    // D'abord le header : email / quoi
                    $fields = fgetcsv($handle, 1000, $separator);
                    $mail_idx = -1;
                    $quoi_idx = -1;
                    foreach ($fields as $key => $value) {
                        if ($value == "Quoi") {
                            $quoi_idx = $key;
                        }
                        if ($value == "Mail") {
                            $mail_idx = $key;
                        }
                    }

                    // On tourne sur le fichier maintenant
                    while (($data = fgetcsv($handle, 1000, $separator)) !== false) {
                        // Récupération des données
                        $mail = $data[$mail_idx];
                        $quoi = $data[$quoi_idx];

                        // On cherche l'utilisateur
                        $user = $f3->get('container')['user_finder']->findByEmail($mail);
                        // Et son addresse
                        $adresse = $f3->get('container')['address_finder']->findByUserId($user['id']);

                        if (count($user) >= 1 and is_array($user)) {
                            // On a un utilisateur, cool
                            // On crée une contrepartie, si son cumul est suffisant
                            $needed = 0;

                            switch ($quoi) {
                            case 'hoodie':
                                $needed = 250;
                                break;
                            case 'pishirt':
                                $needed = 100;
                                break;
                            case 'pibag':
                                $needed = 50;
                                break;
                            case 'piplome':
                                $needed = 30;
                                break;
                            }
                            if ($user['cumul'] >= $needed) {
                                // Assez de point, on crée
                                // On récupère le dernier ID inséré
                                $parent = $f3->get('container')['counterpart_finder']->getNextInsertedId();
                                $f3->get('container')['command_handler']->handle(new CounterpartCreateCommand($adresse['id'], $user['id'], $quoi, 1, 2, date("Y-m-d H:i:s"), 'Imported from a file', $parent));
                                // On mets à jour le cumul de l'utilisateur
                                $f3->get('container')['command_handler']->handle(new UserUpdateCumulCommand($user['id'], $user['cumul'] - $needed));
                                $total[$quoi] +=1;
                            } else {
                                $f3->push('SESSION.error', 'Pas assez de cumul pour un '.$quoi.' avec l\'email'.$mail);
                            }
                        } else {
                            $f3->push('SESSION.error', 'Pas d\'utilisateur avec l\'adresse '.$mail);
                        }
                    }
                    fclose($handle);
                }
            } // Fin de l'import CSV
            $f3->set('counterparts_import', $total);
        }

        // On définit des valeurs par défaut
        $query = "SELECT DISTINCT quoi FROM contreparties;";
        $result = $db->query($query);
        if ($result) {
            foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $f3->set($row['quoi'].'_s1', 0);
                $f3->set($row['quoi'].'_s2', 0);
            }
        }
        // Calcul des quantités
        $query = "SELECT quoi,
            status,
            COUNT(status) AS qte
            FROM contreparties
            GROUP BY quoi, status;";
        $result = $db->query($query);
        if ($result) {
            foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $f3->set($row['quoi'].'_s'.$row['status'], $row['qte']);
            }
        }
        // Calcul des pishirts et hoodies par taille
        for ($i=1;$i<9;$i++) {
            $f3->set('pishirt_t'.$i, 0);
            $f3->set('hoodie_t'.$i, 0);
        }
        $query = "SELECT quoi,
            taille,
            COUNT(taille) AS qte
            FROM contreparties
            GROUP BY quoi, taille;";
        $result = $db->query($query);
        if ($result) {
            foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $f3->set($row['quoi'].'_t'.$row['taille'], $row['qte']);
            }
        }
        $f3->set('block_content', 'backend/contreparties_tableau.html');
    }

    public function counterparts($f3, $args)
    {
        $export = false;
        $texte = '';
        $pibag = '';
        $piplome = '';
        $pishirt = '';
        $hoodie = '';
        $pseudo = '';
        $quoi = 0;
        $limite = 50;
        $page = 1;
        $status = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='POST') {
            if ($f3->exists('POST.action')) {
                switch ($f3->get('POST.action')) {
                case 'asked':
                    $db->query("UPDATE contreparties SET status=1 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'sent':
                    $db->query("UPDATE contreparties SET status=2 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'npai':
                    $db->query("UPDATE contreparties SET status=3 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'canceled':
                    $db->query("UPDATE contreparties SET status=99 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'export':
                    $export = true;
                    break;
                case 'pdf':
                    $db->query("UPDATE dons SET pdf='' WHERE id=".$f3->get('POST.id')."");
                    break;
                }
            }
            $texte = $f3->get('POST.texte');
            $pibag = $f3->get('POST.pibag');
            $piplome = $f3->get('POST.piplome');
            $pishirt = $f3->get('POST.pishirt');
            $hoodie = $f3->get('POST.hoodie');
            $quoi = $quoi + $pibag + $piplome + $pishirt + $hoodie;
            $limite = $f3->exists('POST.limite') ? (int) $f3->get('POST.limite') : 50;
            $page = $f3->exists('POST.page') ? (int) $f3->get('POST.page') : 1;
            $status = $f3->get('POST.status');
        }

        $query = "SELECT c.id,
            c.datec as datec,
            c.quoi as quoi,
            c.taille as taille,
            c.status as status,
            c.user_id as user_id,
            c.parent as parent,
            u.email as email,
            u.pseudo as pseudo,
            u.commentaire as commentaire,
            a.nom as nom,
            a.adresse as adresse,
            a.adresse2 as adresse2,
            a.codepostal as codepostal,
            a.ville as ville,
            a.pays as pays
            FROM contreparties c
            LEFT OUTER JOIN users u ON c.user_id = u.id
            LEFT OUTER JOIN adresses a ON u.id = a.user_id
            WHERE 1=1 ";
        if ($texte) {
            $query .= " AND (c.id like '%".$texte."%'
                OR u.pseudo like '%".$texte."%'
                OR u.email like '%".$texte."%'
                OR a.nom like '%".$texte."%'
                OR a.ville like '%".$texte."%'
                OR a.pays like '%".$texte."%'
                OR a.codepostal like '%".$texte."%'
                OR a.adresse2 like '%".$texte."%'
                OR a.adresse like '%".$texte."%')";
        }
        switch ($quoi) {
        case 0:
            break;
        case 1:
            $query .= " AND c.quoi='".\Utils::asl('pibag')."' ";
            break;
        case 2:
            $query .= " AND c.quoi='".\Utils::asl('piplome')."' ";
            break;
        case 4:
            $query .= " AND c.quoi='".\Utils::asl('pishirt')."' ";
            break;
        case 8:
            $query .= " AND c.quoi='".\Utils::asl('hoodie')."' ";
            break;
        default:
            $query .= " AND datec IN (
                SELECT datec
                FROM contreparties
                WHERE status=1
                GROUP BY user_id, datec
                HAVING SUM(
                    CASE quoi
                    WHEN 'pibag' THEN 1
                    WHEN 'piplome' THEN 2
                    WHEN 'pishirt' THEN 4
                    WHEN 'hoodie' THEN 8
                    END
                )=$quoi)
";
        }
        if ($status) {
            $query .= " AND c.status IN (".\Utils::asl($status).") ";
        }
        $first = ($page - 1) * $limite;
        $last = $page * $limite;
        $query .= " ORDER BY c.datec DESC LIMIT $first, $last;";
        $result = $db->query($query);
        $data = array();
        if ($result) {
            $i = 0;
            foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                if ($row['quoi']=='piplome') {
                    $pdf = $db->query("SELECT d.id AS id,
                        a.nom AS pseudo,
                        d.pdf AS pdf
                        FROM dons d
                        JOIN adresses a ON a.id = d.adresse_id
                        WHERE d.id='".$row['taille']."'");
                    if ($pdf) {
                        $pdf = $pdf->fetch(\PDO::FETCH_ASSOC);
                    }
                    $row['pdf_id'] = $pdf['id'];
                    $row['pdf_nom'] = $pdf['pseudo'];
                    $row['pdf_url'] = $pdf['pdf'];
                } else {
                    $row['pdf_id'] = '';
                    $row['pdf_nom'] = '';
                    $row['pdf_url'] = '';
                }
                $data[] = $row;
                $i++;
            }
            $f3->set('row_count', $i);
        } else {
            $f3->set('row_count', 0);
        }

        $f3->set('texte', $texte);
        $f3->set('quoi', $quoi);
        $f3->set('pibag', $pibag);
        $f3->set('piplome', $piplome);
        $f3->set('pishirt', $pishirt);
        $f3->set('hoodie', $hoodie);
        $f3->set('limite', $limite);
        $f3->set('page', $page);
        $f3->set('status', $status);
        $f3->set('query', $query);
        $f3->set('data', $data);
        $f3->set('pseudo', $pseudo);
        $f3->set('PIPLOME_URL', PIPLOME_URL);

        if ($export) {
            $rows = array();
            $count = 1;
            foreach ($data as $row) {
                // Affichage de la taille de manière courte
                $taille = "";
                $atailles = $f3->get('TAILLES');
                if ($row['quoi']!="piplome" and $row['quoi']!="pibag") {
                    $row['taille'] = preg_replace('(Coupe |Taille |omme,|emme,)', '', $atailles[$row['taille']]);
                }
                if ($row['quoi']=="pibag") {
                    $row['taille'] = "N/A";
                }
                $row['count'] = $count++;
                $row['email'] = \Utils::clean_encoding($row['email']);
                $row['pseudo'] = \Utils::clean_encoding($row['pseudo']);
                $row['nom'] = \Utils::clean_encoding($row['nom']);
                $adresse = \Utils::clean_encoding($row['adresse']);
                $adresse = preg_replace('/\n|\r/', ' ', $adresse);
                $adresse = preg_replace('/^'.$row['nom'].'/', '', $adresse);
                $row['adresse'] = $adresse;
                $adresse2 = \Utils::clean_encoding($row['adresse2']);
                $adresse2 = preg_replace('/\n|\r/', ' ', $adresse2);
                $adresse2 = preg_replace('/^'.$row['nom'].'/', '', $adresse2);
                $row['adresse2'] = $adresse2;
                $row['codepostal'] = \Utils::clean_encoding($row['codepostal']);
                $row['ville'] = \Utils::clean_encoding($row['ville']);
                $row['pays'] = \Utils::clean_encoding($row['pays']);
                $row['datec'] = \Utils::clean_encoding($row['datec']);
                $row['taille'] = \Utils::clean_encoding($row['taille']);
                $row['pdf_id'] = \Utils::clean_encoding($row['pdf_id']);
                $row['pdf_nom'] = \Utils::clean_encoding($row['pdf_nom']);
                $row['commentaire'] = \Utils::clean_encoding($row['commentaire']);
                $rows[] = $row;
            }
            $f3->set('headers', array(
                "email",
                "pseudo",
                "nom",
                "adresse",
                "adresse2",
                "code postal",
                "ville",
                "pays",
                "demande",
                "taille",
                "piplome_id",
                "nom sur le piplome",
                "commentaire",
            ));
            $f3->set('rows', $rows);
            echo Template::instance()->render('export.csv', 'text/csv');
            die();
        } else {
            $f3->set('block_content', 'backend/contreparties.html');
        }
    }

    public function counterpart($f3, $args)
    {
        $id = '';
        $user_id = '';
        $quoi = '';
        $taille = '';
        $nom = '';
        $adresse = '';
        $adresse2 = '';
        $codepostal = '';
        $ville = '';
        $pays = '';
        $msg = '';
        $comment = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='GET') {
            if ($f3->exists('PARAMS.id')) {
                $id = $f3->get('PARAMS.id');
            }
        } else {
            $id = $f3->get('POST.id');
            $user_id = $f3->get('POST.user_id');
            $quoi = $f3->get('POST.quoi');
            $taille = $f3->get('POST.taille');
            $nom = $f3->get('POST.nom');
            $email = $f3->get('POST.email');
            $adresse = $f3->Get('POST.adresse');
            $adresse2 = $f3->Get('POST.adresse2');
            $codepostal = $f3->get('POST.codepostal');
            $ville = $f3->get('POST.ville');
            $pays = $f3->get('POST.pays');
            $status = $f3->get('POST.status');
            $comment = $f3->get('POST.commentaire');

            // Mise à jour de la contrepartie
            if ($id!='') {
                // First, let's retrieve the adresse from the adresse_id if it exists
                $result = $db->query("SELECT adresse_id
                    FROM contreparties
                    WHERE id = '".\Utils::asl($f3->get('POST.id'))."'
                    AND adresse_id IS NOT NULL");
                $result = $result->fetch(\PDO::FETCH_ASSOC);
                $adresse_id = $result['adresse_id'];

                if (!$adresse_id or $adresse_id == 0) {
                    // We need to create an adresse_id
                    $db->query("INSERT INTO adresses
                        VALUES (nom='".\Utils::asl($nom)."',
                            adresse='".\Utils::asl($adresse)."',
                            adresse2='".\Utils::asl($adresse2)."',
                            codepostal='".\Utils::asl($codepostal)."',
                            ville='".\Utils::asl($ville)."',
                            pays='".\Utils::asl($pays)."')");
                    $adresse_id = $db->lastInsertId();
                } else {
                    // Updating the adresses first
                    $db->query("UPDATE adresses
                        SET nom='".\Utils::asl($nom)."',
                        adresse='".\Utils::asl($adresse)."',
                        adresse2='".\Utils::asl($adresse2)."',
                        codepostal='".\Utils::asl($codepostal)."',
                        ville='".\Utils::asl($ville)."',
                        pays='".\Utils::asl($pays)."'
                        WHERE id = $adresse_id");
                };
                $db->query("UPDATE contreparties
                    SET quoi='".\Utils::asl($quoi)."',
                    taille='".\Utils::asl($taille)."',
                    status='".\Utils::asl($status)."'
                    WHERE id='".\Utils::asl($_POST['id'])."'");
                // We will update the comment
                $db->query("UPDATE users SET commentaire='".\Utils::asl($comment)."' WHERE id='".\Utils::asl($user_id)."'");
            }
        }

        if ($id!='') {
            $contrepartie = $f3->get('container')['counterpart_finder']->findById((int) $id);
        } else {
            $contrepartie = array(
                'id'=>$id,
                'user_id'=>$user_id,
                'quoi'=>$quoi,
                'taille'=>$taille,
                'nom'=>$nom,
                'adresse'=>$adresse,
                'adresse2'=>$adresse2,
                'codepostal'=>$codepostal,
                'ville'=>$ville,
                'pays'=>$pays,
                'status'=>$status,
                'commentaire'=>$comment,
            );
        }
        $pdfs = array();
        if ($contrepartie['quoi']=='piplome') {
            $result = $db->query("SELECT d.id AS id,
                d.pdf AS pdf,
                a.nom AS nom
                FROM dons d 
                INNER JOIN adresses a ON d.adresse_id = a.id
                WHERE pdf<>'' 
                AND d.status IN (1,4,101)
                AND d.user_id='".$contrepartie['user_id']."'");
            if ($result) {
                foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $pdf) {
                    $pdfs[] = $pdf;
                }
            }
        }
        $f3->set('pdfs', $pdfs);

        $f3->set('id', $id);
        $f3->set('msg', $msg);
        $f3->set('contrepartie', $contrepartie);

        $f3->set('block_content', 'backend/contrepartie.html');
    }

    public function adresse($f3, $args)
    {
        $id = '';
        $form_name = '';
        $user_id = '';
        $db = $f3->get('DB');
        if ($f3->get('VERB') == 'POST') {
            $id = \Utils::asl($f3->get('POST.id'));
            $form_name = $f3->get('POST.action');
        }
        if ($form_name == 'delete') {
            $result = $db->query("SELECT user_id FROM adresses WHERE id = $id");
            $result = $result->fetch(\PDO::FETCH_ASSOC);
            $user_id = \Utils::asl($result["user_id"]);
            $db->query("UPDATE dons SET adresse_id = NULL
                WHERE user_id = $user_id");
            $db->query("UPDATE contreparties SET adresse_id = NULL
                WHERE user_id = $user_id");
            $db->query(
                "DELETE FROM adresses
                WHERE id = $id"
        );
        } else {
            $result = $db->query("SELECT user_id FROM adresses WHERE id = $id");
            $result = $result->fetch(\PDO::FETCH_ASSOC);
            $user_id = \Utils::asl($result["user_id"]);
            $nom = \Utils::asl($f3->get('POST.nom'));
            $adresse = \Utils::asl($f3->get('POST.adresse'));
            $adresse2 = \Utils::asl($f3->get('POST.adresse2'));
            $codepostal = \Utils::asl($f3->get('POST.codepostal'));
            $ville = \Utils::asl($f3->get('POST.ville'));
            $pays = \Utils::asl($f3->get('POST.pays'));
            $db->query(
                "UPDATE adresses SET
                nom = '".$nom."',
                adresse = '".$adresse."',
                adresse2 = '".$adresse2."',
                codepostal = '".$codepostal."',
                ville = '".$ville."',
                pays = '".$pays."'
                WHERE id = $id"
            );
        }
        $f3->reroute("/admin/users/$user_id");
    }

    public function user($f3, $args)
    {
        $id = array_key_exists('id', $args) ? \Utils::asl($args['id']) : '';
        $f3->set('id', $id);

        if ($f3->get('VERB') == 'POST') {
            $pseudo = \Utils::asl($f3->get('POST.pseudo'));
            $email = $f3->get('POST.email');
            $commentaire = $f3->get('POST.commentaire');
            $total = $f3->get('POST.total', 0);
            $cumul = $f3->get('POST.cumul', 0);

            if ($id > 0) {
                $f3->get('container')['command_handler']->handle(new UserUpdateByAdminCommand($id, $pseudo, $email, $commentaire, $total, $cumul));
            } else {
                $password = hash('sha256', \Utils::generate_password());
                $db = $f3->get('DB');
                $db->query("INSERT INTO users
                    VALUES pseudo = '".$pseudo."',
                    hash = '".$hash."',
                    commentaire = '".$commentaire."',
                    email = '".$email."'");
                $id = $db->lastInsertId();
            }
        }

        // Let's get everything we have related to our user
        $user = $f3->get('container')['user_finder']->findById($id);
        $user['adresse'] = $f3->get('container')['address_finder']->findByUserId($user['id']);
        $user['dons'] = $f3->get('container')['donation_finder']->findByUserId($user['id']);
        $user['contreparties'] = $f3->get('container')['counterpart_finder']->findByUserId($user['id']);

        $f3->set('user', $user);
        $f3->set('block_content', 'backend/user.html');
    }

    public function users($f3, $args)
    {
        $text = $f3->exists('POST.texte') ? $f3->get('POST.texte') : '';
        $status = $f3->exists('POST.status') ? $f3->get('POST.status') : '';
        $limit = $f3->exists('POST.limite') ? (int) $f3->get('POST.limite') : 50;
        $page = $f3->exists('POST.page') ? (int) $f3->get('POST.page') : 1;

        $results = $f3->get('container')['user_finder']->adminSearch($text, $status, $limit);

        $f3->set('texte', $text);
        $f3->set('status', $status);
        $f3->set('limite', $limit);
        $f3->set('page', $page);
        $f3->set('data', $results);
        $f3->set('row_count', count($results));
        $f3->set('block_content', 'backend/users.html');
    }

    public function cbs($f3, $args)
    {
        $export = false;
        $texte = '';
        $status = '';
        $limit = '50';
        $page = '1';
        $db = $f3->get('DB');
        if ($f3->get('VERB')=='POST') {
            if ($f3->exists('POST.action')) {
                switch ($f3->get('POST.action')) {
                case 'current':
                    $db->query("UPDATE users SET status=1 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'to_relaunch':
                    $db->query("UPDATE users SET status=2 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'relaunched':
                    $db->query("UPDATE users SET status=3 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'command':
                    if ($f3->get('POST.command')!='') {
                        $query = "UPDATE users SET status=".$f3->get('POST.command')." where id IN (".$f3->get('POST.checkboxes').")";
                        $db->query($query);
                    }
                    break;
                case 'canceled':
                    $db->query("UPDATE users SET status=99 WHERE id=".$f3->get('POST.id')."");
                    break;
                case 'export':
                    $export = true;
                    break;
                }
            }

            $limit = $f3->exists('POST.limite') ? (int) $f3->get('POST.limite') : 50;
            $page = $f3->exists('POST.page') ? (int) $f3->get('POST.page') : 1;
            $texte = $f3->exists('POST.texte') ? $f3->get('POST.texte') : '';
            $status = $f3->exists('POST.status') ? $f3->get('POST.status') : '';
        }

        $query = "SELECT id, email, users.expiration, status FROM users INNER JOIN identifiers ON users.id=identifiers.user_id WHERE identifier!='' AND identifier IS NOT NULL AND status != ''";
        if ($texte!='') {
            $query .= " AND (email like '%".$texte."%'
                OR pseudo like '%".$texte."%')";
        }
        if ($status!='') {
            $query .= " AND status='".$status."'";
        }
        $first = ($page - 1) * $limit;
        $last = $page * $limit;
        $query .= " ORDER BY expiration ASC LIMIT $first, $last;";
        $result = $db->query($query);
        $cbs = array();
        if ($result) {
            $i = 0;
            foreach ($result->fetchAll(\PDO::FETCH_ASSOC) as $data) {
                $cb['id'] = $data['id'];
                $cb['email'] = $data['email'];
                $time_struct = strptime($data['expiration'], "%Y-%m-%d %H:%M:%S");
                $new_expiration = strftime("%m-%Y", mktime(
                    $time_struct['tm_hour'],
                    $time_struct['tm_min'],
                    $time_struct['tm_sec'],
                    $time_struct['tm_mon']+1,
                    $time_struct['tm_mday'],
                    $time_struct['tm_year']+1900
                ));
                $cb['expiration'] = $new_expiration;
                if ($data['status'] == '') {
                    $cb['status'] = 100;
                } else {
                    $cb['status'] = $data['status'];
                }
                $cbs[] = $cb;
                $i++;
            }
            $f3->set('row_count', $i);
        }

        $f3->set('texte', $texte);
        $f3->set('status', ($status!='') ? $status : 0);
        $f3->set('limite', $limit);
        $f3->set('query', $query);
        $f3->set('data', $cbs);
        $f3->set('page', $page);

        if ($export) {
            $statuts = $f3->get('CB_STATUTS');
            $rows = array();
            $count = 1;
            foreach ($cbs as $row) {
                $row['count'] = $count++;
                $row['id'] = \Utils::clean_encoding($row['id']);
                $row['email'] = \Utils::clean_encoding($row['email']);
                $row['expiration'] = \Utils::clean_encoding($row['expiration']);
                $row['status'] = $statuts[$row['status']];
                $rows[] = $row;
            }
            $f3->set('headers', array(
                "id",
                "email",
                "expiration",
                "status",
            ));
            $f3->set('rows', $rows);
            echo Template::instance()->render('cbs_export.csv', 'text/csv');
            die();
        } else {
            $f3->set('block_content', 'backend/cbs.html');
        }
    }

    public function banque($f3, $args)
    {
        $message = '';
        $error = '';
        $result = '';
        if ($f3->get('VERB')=='POST') {
            if ($_FILES['file']['tmp_name']=='') {
                $message = "Merci de sélectionner un fichier.";
            } else {
                switch ($f3->get('POST.tab')) {
                case 'verif':
                    $datas = $this->banque_verif($f3, $_FILES['file']['tmp_name']);
                    if ($datas['dons_ok']>0) {
                        $result = $datas['dons_ok'] . " dons ok sur un total de ". $datas['dons_total']." dons.";
                    }
                    if ($datas['recurrents_ok']>0) {
                        $result .= '<br />' . $datas['recurrents_ok'] . " dons récurrents comptabilisés sur un total de ". $datas['recurrents_total']." dons récurrents.";
                    }
                    if ($datas['dons_nok']>0) {
                        $error = $datas['dons_nok'] . " dons avec un souci sur un total de ". $datas['dons_total']." dons.";
                        $message = '';
                        foreach ($datas['dons'] as $key=>$value) {
                            $message .= $value.'<br />';
                        }
                    }
                    if ($datas['recurrents_nok']>0) {
                        $error .= '<br />' . $datas['recurrents_nok'] . " dons récurrents non comptabilisés sur un total de ". $datas['recurrents_total']." dons récurrents.";
                    }
                    break;
                case 'update':
                    if ($f3->get('POST.test')=='1') {
                        $datas = $this->banque_update($f3, $_FILES['file']['tmp_name']);
                        $result = $datas['update'] . " dons récurrents mis à jour sur un total de ". $datas['total']." dons récurrents.";
                        $message = "Il s'agit d'un <b>test</b><br />";
                    } else {
                        $datas = $this->banque_update($f3, $_FILES['file']['tmp_name'], false);
                        $result = $datas['update'] . " dons récurrents mis à jour sur un total de ". $datas['total']." dons récurrents.";
                    }
                    if (count($datas['comptabilise'])>0 or count($datas['sans_cumul'])>0) {
                        $error = count($datas['comptabilise']) . " dons récurrents déjà comptabilisés et ". count($datas['sans_cumul'])." dons récurrents sans cumul.";
                        if (count($datas['comptabilise'])>0) {
                            $message .= 'Dons déjà comptabilisés:<br />';
                            foreach ($datas['comptabilise'] as $key=>$value) {
                                $message .= $value.'<br />';
                            }
                        }
                        if (count($datas['sans_cumul'])>0) {
                            $message .= 'Dons sans cumul:<br />';
                            foreach ($datas['sans_cumul'] as $key=>$value) {
                                $message .= $value.'<br />';
                            }
                        }
                    }
                    break;
                default:
                    $datas = $this->banque_cbs($f3, $_FILES['file']['tmp_name']);
                    $result = $datas['total_cbs'] . " dates d'expiration mises à jour.";
                    break;
                }
            }
        }
        $f3->set('result', $result);
        $f3->push('SESSION.error', $error);
        $f3->push('SESSION.message', $message);

        $f3->set('block_content', 'backend/banque.html');
    }

    public function banque_cbs($f3, $filename)
    {
        $separator = ";";
        $datas = array();
        $db = $f3->get('DB');
        // Lecture du fichier
        if (($handle = fopen($filename, "r")) !== false) {
            // On récupère d'abord les champs du fichier
            $fields = fgetcsv($handle, 1000, $separator);
            $identifier_idx = -1;
            $expire_idx = -1;
            foreach ($fields as $key=>$value) {
                if ($value=="Alias") {
                    $identifier_idx = $key;
                }
                if ($value=="Date expiration") {
                    $expire_idx = $key;
                }
            }

            // On lit maintenant tout, ligne par ligne, afin de stocker les
            // informations bancaires pour chaque don récurrent.
            $total_cbs = 0; // Nombre de CB mises à jour
            while (($data = fgetcsv($handle, 1000, $separator)) !== false) {
                // Récupération de l'identifiant du don
                $identifier = $data[$identifier_idx];

                // Construction de la date d'expiration de la CB
                $expire = $data[$expire_idx];
                $expire = preg_split("#/#", $data[$expire_idx]);
                $expire_date = mktime(0, 0, 0, $expire[0], 1, $expire[1]);
                $expire = strftime("%Y-%m-%d 00:00:00", $expire_date);
                $today_date = time();
                $today = strftime("%Y-%m-%d 00:00:00", $today_date);

                // Mise à jour dans la base de données de la date d'expiration
                $result = $db->query("SELECT user_id FROM identifiers WHERE identifier='".$identifier."'");
                if ($result) {
                    $user_id = $result->fetch(\PDO::FETCH_ASSOC)[0];
                    $total_cbs += 1;
                    $db->query("UPDATE identifiers SET expiration='".$expire. "' WHERE identifier='".$identifier."'");
                    if ($expire_date<$today_date) {
                        $db->query("INSERT INTO dons SET
                            status = '103',
                            datec = NOW(),
                            somme = 0,
                            user_id = '" .$user_id ."',
                            identifier = '" . $identifier ."',
                            cumul = 0;");
                    }
                }
            }
            fclose($handle);
        }
        $datas['total_cbs'] = $total_cbs;
        return $datas;
    }

    public function banque_verif($f3, $filename)
    {
        $separator = ";";
        $datas = array();
        $db = $f3->get('DB');
        // Lecture du fichier
        if (($handle = fopen($filename, "r")) !== false) {
            // On récupère d'abord les champs du fichier
            $fields = fgetcsv($handle, 1000, $separator);
            $identifier_idx = -1;
            $effect_idx = -1;
            $transaction_idx = -1;
            $amount_idx = -1;
            $statut_idx = -1;
            foreach ($fields as $key=>$value) {
                if ($value=="Identifiant compte client") {
                    $identifier_idx = $key;
                }
                if ($value=="Remis le") {
                    $effect_idx = $key;
                }
                if ($value=="Transaction") {
                    $transaction_idx = $key;
                }
                if ($value=="Montant du paiement") {
                    $amount_idx = $key;
                }
                if ($value=="Statut") {
                    $statut_idx = $key;
                }
            }

            // On lit maintenant tout, ligne par ligne, afin de vérifier les informations bancaires pour chaque don.
            $dons_ok = 0;
            $dons_nok = 0;
            $dons = array();
            $recurrents_ok = 0;
            $recurrents_nok = 0;
            while (($data = fgetcsv($handle, 1000, $separator)) !== false) {
                // Récupération des informations du don
                $identifier = $data[$identifier_idx];
                $effect = $data[$effect_idx];
                $transaction = $data[$transaction_idx];
                $amount = $data[$amount_idx];
                $statut = $data[$statut_idx];
                if ($statut == 'Refusé') {
                    $db->query("UPDATE dons SET status = 103 WHERE id='".$transaction."'");
                    continue;
                }

                if ($identifier=='') {
                    // Pour un don ponctuel, on recherche un équivalent
                    $result = $db->query("SELECT COUNT(*) FROM dons WHERE id='".$transaction. "' AND somme='".$amount."' AND status=1");
                    if ($result) {
                        $combien = $result->fetch(\PDO::FETCH_ASSOC);
                        if ($combien[0]>0) {
                            $dons_ok += 1;
                        } else {
                            $dons_nok += 1;
                            $subresult = $db->query("SELECT id, status, somme FROM dons WHERE id='".$transaction. "'");
                            if ($subresult) {
                                $subresult = $subresult->fetch(\PDO::FETCH_ASSOC);
                                if (count($subresult)>0) {
                                    $dons[] = "Transaction " . $transaction . " de " . $subresult[2] . " € avec un statut " . $subresult[1] . ".";
                                } else {
                                    $dons[] = "Transaction " . $transaction . " inconnue.";
                                }
                            } else {
                                $dons[] = "Erreur de requête sur la transaction " . $transaction;
                            }
                        }
                    }
                } else {
                    $time_struct = strptime($effect, "%d/%m/%Y %H:%M:%S");
                    $new_effect = strftime("%Y-%m-%d %H:%M:%S", mktime(
                        $time_struct['tm_hour']+1,
                        $time_struct['tm_min'],
                        $time_struct['tm_sec'],
                        $time_struct['tm_mon']+1,
                        $time_struct['tm_mday'],
                        $time_struct['tm_year']+1900
                    ));
                    // Pour un don récurrent, on recherche son équivalent qui peut ne pas exister
                    $result = $db->query("SELECT email FROM users JOIN identifiers ON identifiers.user_id = users.id  WHERE identifiers.identifier='".$identifier. "'");
                    if ($result) {
                        $email = $result->fetch(\PDO::FETCH_ASSOC);
                        $email = $email[0];

                        $result = $db->query("SELECT COUNT(1) FROM dons d JOIN users u ON u.id = d.user_id WHERE u.email='".$email. "' and d.status=102 and d.datec='".$new_effect."'");
                        if ($result) {
                            $combien = $result->fetch(\PDO::FETCH_ASSOC);
                            if ($combien[0]>0) {
                                // Don récurrent comptabilisé
                                $recurrents_ok += 1;
                            } else {
                                // Don récurrent pas encore comptabilisé
                                $recurrents_nok += 1;
                            }
                        }
                    }
                }
            }
            fclose($handle);
            $dons_total = $dons_ok + $dons_nok;
            $recurrents_total = $recurrents_ok + $recurrents_nok;
        }
        $datas['dons_ok'] = $dons_ok;
        $datas['dons_nok'] = $dons_nok;
        $datas['dons_total'] = $dons_total;
        $datas['dons'] = $dons;
        $datas['recurrents_ok'] = $recurrents_ok;
        $datas['recurrents_nok'] = $recurrents_nok;
        $datas['recurrents_total'] = $recurrents_total;
        return $datas;
    }

    public function banque_update($f3, $filename, $testing=true)
    {
        $separator = ";";
        $datas = array();
        $update = 0;
        $total = 0;
        $comptabilise = array();
        $sans_cumul = array();
        $emails = array();
        $db = $f3->get('DB');
        // Lecture du fichier
        if (($handle = fopen($filename, "r")) !== false) {
            // On récupère d'abord les champs du fichier
            $fields = fgetcsv($handle, 1000, $separator);
            $identifier_idx = -1;
            $transaction_idx = -1;
            $effect_idx = -1;
            $amount_idx = -1;
            $statut_idx = -1;
            foreach ($fields as $key=>$value) {
                if ($value=="Alias") {
                    $identifier_idx = $key;
                }
                if ($value=="Commande") {
                    $transaction_idx = $key;
                }
                if ($value=="Date remise") {
                    $effect_idx = $key;
                }
                if ($value=="Montant du paiement") {
                    $amount_idx = $key;
                }
                if ($value=="Statut rapprochement") {
                    $statut_idx = $key;
                }
            }

            // On lit maintenant tout, ligne par ligne, afin de vérifier les informations bancaires pour chaque don.
            while (($data = fgetcsv($handle, 1000, $separator)) !== false) {
                if (count($data) <= 0) {
                    continue;
                }
                // Récupération des informations du don
                $identifier = isset($data[$identifier_idx]) ? $data[$identifier_idx] : false;
                $transaction = isset($data[$transaction_idx]) ? $data[$transaction_idx] : false;
                $effect = isset($data[$effect_idx]) ? $data[$effect_idx] : false;
                $amount = isset($data[$amount_idx]) ? $data[$amount_idx] : false;
                $statut = isset($data[$statut_idx]) ? $data[$statut_idx] : false;
                if ($identifier && $transaction && $effect && $amount && $statut == false) {
                    continue;
                }
                if ($statut == 'Refusé' and !$testing) {
                    $db->query("UPDATE dons SET status = 103 WHERE id='".$transaction."'");
                    continue;
                }

                // On ne s'intéresse qu'aux dons récurrents
                if ($identifier!='') {
                    $total ++;
                    $email = '';
                    $time_struct = strptime($effect, "%d/%m/%Y %H:%M:%S");
                    $new_effect = strftime("%Y-%m-%d %H:%M:%S", mktime(
                        $time_struct['tm_hour']+1,
                        $time_struct['tm_min'],
                        $time_struct['tm_sec'],
                        $time_struct['tm_mon']+1,
                        $time_struct['tm_mday'],
                        $time_struct['tm_year']+1900
                    ));
                    // Pour un don récurrent, on recherche son équivalent qui peut ne pas exister
                    $result = $db->query("SELECT email
                        FROM users
                        JOIN identifiers
                        ON users.id = identifiers.user_id
                        AND identifiers.identifier='".$identifier. "'");
                    if ($result->rowCount() == 0) {
                        // Identifier does not exist, we must create it. We have either a pseudo or an email.
                        $user_field = explode("_", $identifier)[1];
                        $user = '';

                        if (strpos($user_field, '@')) {
                            // identifier is an email
                            $user = $f3->get('container')['user_finder']->findByEmail($user_field);
                        } else {
                            // identifier is a pseudo
                            $user = $f3->get('container')['user_finder']->findByPseudo($user_field);
                        }

                        if ($user and !$testing) {
                            $db->query("INSERT INTO identifiers(user_id, identifier) VALUES ('".$user['id']."', '".$identifier."')");
                            $email = $user['email'];
                        } else {
                            // No user, let's try to create the user
                            $email = '';
                            $pseudo = '';
                            if (strpos($user_field, '@')) {
                                $email = $user_field;
                                $pseudo = explode("@", $user_field)[0];
                            } else {
                                $email = $user_field . "@example.org";
                                $pseudo = $user_field;
                            }
                            $hash = hash('sha256', date("%Y-%m-%d %H:%i:%d").$email);
                            if (!$testing) {
                                $f3->get('container')['command_handler']->handle(new UserCreateCommand($email, $hash, $pseudo, 0, 0));
                            };
                        }
                    } else {
                        $email = $result->fetch(\PDO::FETCH_ASSOC);
                        $email = $email['email'];
                        // On stocke l'email pour comptabiliser les dons
                    }
                    if (array_key_exists($email, $emails)) {
                        $emails[$email] ++;
                    } else {
                        $emails[$email] = 1;
                    }
                    // On a besoin de l'utilisateur
                    $user = $f3->get('container')['user_finder']->findByEmail($email);
                    // Récupération de l'id du bon abonnement
                    $stmt = $db->query("SELECT d.id AS id
                        FROM dons d
                            JOIN users u ON u.id = d.user_id
                        WHERE u.email='".$email."'
                            AND d.status=101
                            AND d.id='".$transaction."'");
                    $result = $stmt->fetch(\PDO::FETCH_ASSOC);
                    if (!$result) {
                        if ($testing) {
                            $cumul = rand(50000, 70000);
                        } else {
                            // don non trouvé en statut 101. On le crée (la banque à raison)
                            // statut = cumul
                            $f3->get('container')['command_handler']
                                ->handle(new DonationCreateCommand(
                                    $user['id'],
                                    101,
                                    $new_effect,
                                    $amount,
                                    0,
                                    0
                                ));
                            // Et on le récupère
                            $cumul = $db->lastInsertId();
                            $don = $f3->get('container')['donation_finder']->findById($cumul);
                        };
                    } else {
                        $cumul = $result['id'];
                    }
                    // On regarde si le don n'as pas déjà été ajouté (en vérifiant la datec)
                    $result = $db->query("SELECT COUNT(1)
                            FROM dons d
                                JOIN users u ON u.id = d.user_id
                            WHERE u.email='".$email. "'
                                AND d.status=102
                                AND d.datec='".$new_effect."'
                                AND d.cumul=".$cumul."
                            ");
                    if ($result) {
                        $combien = $result->fetch();
                        if ($combien[0]==0) {
                            $update ++;
                            // Don récurrent non comptabilisé, on l'ajoute
                            if (!$testing) {
                                $query = "INSERT INTO dons (status,
                                    datec,
                                    somme,
                                    user_id,
                                    cumul,
                                    identifier)
                                    VALUES (
                                        102,
                                        '".$new_effect."',
                                        ".$amount.",
                                        '".$user['id']."',
                                        '".$cumul."',
                                        '".$identifier."')";
                            }
                            if (!$testing) {
                                $db->query($query);
                                // On met alors à jour le cumul et le total de l'utilisateur concerné
                                $result = $db->query("UPDATE users
                                            SET cumul=cumul+".$amount.",
                                                total=total+".$amount."
                                            WHERE id='".$user['id']."'");
                            }
                        } else {
                            $comptabilise[] = $identifier;
                        }
                    }
                }
            }
            fclose($handle);
        }
        $datas['update'] = $update;
        $datas['total'] = $total;
        $datas['comptabilise'] = $comptabilise;
        $datas['sans_cumul'] = $sans_cumul;
        return $datas;
    }


    public function stats($f3, $args)
    {
        $startingDay = new \DateTime('2014-11-12');
        $finder = $f3->get('container')['stat_finder'];

        $results = [
            "Dons/heure sur les dernières 24 heures" => $finder->donationsPerHour(),
            "30 derniers dons" => $finder->latestDonations(),
            "Nombre de dons moyen par jour depuis le début de la campagne" => $finder->averageDonationPerDay($startingDay),
            "Montant moyen des dons depuis le début de la campagne" => $finder->averageDonationAmount($startingDay),
            "Nombre de dons par heure depuis le début de la campagne" => $finder->donationsPerHour($startingDay),
            "Répartitions des montants sur les 30 derniers jours" => $finder->donationsRepartitionByDay(),
            "Répartitions des montants depuis le début de la campagne" => $finder->donationsRepartitionByAmount($startingDay),
        ];

        foreach ($results as $title => $result) {
            $columns = [];
            $rows = [];
            foreach ($result as $statline) {
                $columns = array_keys($statline);
                $rows[] = array_values($statline);
            }

            $stats[] = [
                'title' => $title,
                'columns' => $columns,
                'rows' => $rows,
            ];
        }
        $f3->set('stats', $stats);

        $f3->set('block_content', 'backend/stats.html');
    }

    public function recompute_cumul($f3, $args)
    {
        $f3->get('container')['command_handler']->handle(new AdminUpdateParentCommand());
        $this->show($f3, $args);
    }

    public function recompute_total($f3, $args)
    {
        $f3->get('container')['command_handler']->handle(new AdminUpdateTotalUsersCommand());
        $this->show($f3, $args);
    }

    public function accounts($f3, $args)
    {
        if ($f3->get('VERB') == 'POST') {
            $action = $f3->get('POST.action');
            switch ($action) {
                case 'create':
                    $f3->get('container')['command_handler']->handle(new AdminCreateCommand($f3->get('POST.user_id'), $f3->get('POST.password')));
                    break;
                case 'delete':
                    $f3->get('container')['command_handler']->handle(new AdminDeleteCommand($f3->get('POST.id')));
                    break;
                case 'update':
                    $f3->get('container')['command_handler']->handle(new AdminChangePasswordCommand($f3->get('POST.id'), $f3->get('POST.password')));
                    break;
                default:
                    break;
            }
            $f3->reroute('/admin/accounts');
        }

        $db = $f3->get('DB');
        $stmt = $db->query("SELECT * FROM admins");
        $admins = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $f3->set('admins', $admins);
        $f3->set('block_content', 'backend/admins.html');
    }
};