/app/Console/Commands/CampagneTokensOld.php

<?php namespace App\Console\Commands;

use App\Models\PlanningFaiVolume;
use Illuminate\Console\Command;
use Symfony\Component\Console\Input\InputArgument;
use Carbon\Carbon;
use App\Models\Campagne;
use App\Models\Destinataire;
use App\Models\Fai;
use App\Models\Planning;
use App\Models\Token;

use Mail;

class CampagneTokensOld extends Command {

    protected $max_tries = 5;

    protected $name = 'campagne:tokens_old';
    protected $description = 'Prepares a campaign (tokens...)';

    public function __construct()
    {
        parent::__construct();
        $this->passe = 0;
        $this->selected = 0;
        $this->fai_selected = 0;
        $this->how_many_to_select = 0;
        $this->remaining = 0;
        $this->total = 0;
        $this->chunk_size = 25000;
        $this->total_tested = 0;
        $this->total_excluded = 0;

        $this->to_exclude = [];
    }

    /**
     *
     */
    public function fire()
    {
        \DB::disableQueryLog();

        $planning = Planning::find($this->argument('planning_id'));

        \Log::info("[CampagneTokens][P$planning->id] : Début (Planning $planning->id / Campagne $planning->campagne_id)");

        $count = \DB::table('tokens')
            ->where('planning_id',$planning->id)
            ->count();

        if($planning->tokens_at != null or $count > 0){
            \DB::statement("UPDATE tokens SET planning_id = 0, campagne_id = NULL WHERE planning_id = $planning->id");
            // au cas où le script se relance on ré-initialise les tokens déjà sélectionnés
        }
        $campagne = Campagne::find($planning->campagne_id);
        $today = Carbon::today()->format('Y-m-d');
        

        $this->remaining = $planning->volume;
        $done_fais = [];

        // on charge les "déja destinataires"
        $dateOffset = date('Y-m-d', strtotime("-7 days"));

        \DB::table('tokens')
            ->where('base_id', $campagne->base_id)
            ->where('campagne_id', $campagne->id)
            ->where('date_active','>',$dateOffset)
            ->chunk($this->chunk_size, function ($already) {
                foreach ($already as $historique) {
                    $this->to_exclude[$historique->destinataire_id] = 1;
                }
            });

        \DB::table('repoussoirs')
            ->where('campagne_id', $campagne->id)
            ->chunk($this->chunk_size, function ($repoussoirs) {
                foreach ($repoussoirs as $r) {
                    $this->to_exclude[$r->destinataire_id] = 1;
                }
            });

//        echo 'Repoussoirs : '.count($this->to_exclude)."\n";

        //Volume par FAI pour la campagne
        $planvols = \DB::table('plannings_fais_volumes')
            ->select('volume','fai_id')
            ->where('planning_id',$planning->id)
            ->get();

        foreach($planvols as $planvol){
            \Log::info("// FAI ID // $planvol->fai_id ");
            $getMin = array();

            $this->fai_selected = 0;
            $done_fais[] = $planvol->fai_id;
            $lefai = Fai::find($planvol->fai_id);


            if ($this->remaining <= 0) {
                break;
            }

            $total_fai_per_day = \DB::table('fai_sender')
                ->selectRaw('sum(quota_left) as total')
                ->where('fai_id',$lefai->id)
                ->where('quota_left','>',0)
                ->first();

            $getMin[] = $planvol->volume;

            if(!empty($total_fai_per_day)){
                $getMin[] = $total_fai_per_day->total;
            }

            if($lefai->quota_campagne > 0) {
                $getMin[] = $lefai->quota_campagne;
            }

            $how_many = min($getMin);
            \Log::info("// HOW MANY // $how_many");
            $this->how_many_to_select = $how_many;
            $chunk = min($how_many, $this->chunk_size);

            if($chunk == 0) {
                continue;
            }
//            echo "Trying to find $how_many tokens \n";
            \DB::table('tokens')
                ->where('base_id', $campagne->base_id)
                ->whereRaw('campagne_id IS null')
                ->where('date_active', $today)
                ->where('fai_id', $planvol->fai_id)
                ->orderBy('priority')
                ->orderByRaw('rand()')
                ->take($how_many)
                ->chunk($chunk, function ($tokens) use ($planning, $planvol) {
                    if($this->fai_selected < $this->how_many_to_select){
                        \Log::info("// IF // Fai selected $this->fai_selected -- HowManyToSelect $this->how_many_to_select");
                        $this->fai_selected += $this->checkTokens($planning, $tokens, ($planvol->volume - $this->fai_selected));
                    } else {
                        \Log::info("// ELSE // Fai selected $this->fai_selected -- HowManyToSelect $this->how_many_to_select");
                        return false;
                    }
                });
        }

        // d'abord, les FAI à quota
        $fais = Fai::where('quota_campagne','>',0)
            ->whereNotIn('id',$done_fais)
            ->orderBy('quota_campagne')
            ->get();

        foreach($fais as $fai) {
//            echo "\n\nFAI (quota) : $fai->nom ( $fai->quota_campagne ) \n";
            $getMin = array();
            $this->fai_selected = 0;
            $tries = 0;
            $done_fais[] = $fai->id;

            if ($this->remaining <= 0) {
                break;
            }

            $getMin[] = $this->remaining;
            $getMin[] = $fai->quota_campagne;

            $planvol = \DB::table('plannings_fais_volumes')
                ->select('volume')
                ->where('planning_id',$planning->id)
                ->where('fai_id',$fai->id)
                ->first();

            $total_fai_per_day = \DB::table('fai_sender')
                ->selectRaw('sum(quota_left) as total')
                ->where('fai_id',$fai->id)
                ->where('quota_left','>',0)
                ->first();

            if(!empty($total_fai_per_day)){
                $getMin[] = $total_fai_per_day->total;
            }
            if(!empty($planvol)){
                $getMin[] = $planvol->volume;
            }
//            echo "Trying to find $how_many tokens \n";
            $how_many = min($getMin);
            $chunk = min($how_many, $this->chunk_size);
            $this->how_many_to_select = $how_many;

            if($chunk == 0) {
                continue;
            }

            \DB::table('tokens')
                ->where('base_id', $campagne->base_id)
                ->whereRaw('campagne_id IS null')
                ->where('date_active', $today)
                ->where('fai_id', $fai->id)
                ->orderBy('priority')
                ->orderByRaw('rand()')
                ->take($how_many)
                ->chunk($chunk, function ($tokens) use ($planning, $planvol) {
//                    $this->checkTokens($planning, $tokens);
                    if(!empty($planvol)){
                        if($this->fai_selected < $this->how_many_to_select){
                            $this->fai_selected += $this->checkTokens($planning, $tokens,($planvol->volume - $this->fai_selected));
                        }
                    } else{
                        $this->checkTokens($planning, $tokens);
                    }
                });
//            $this->remaining -= $this->selected;
//            echo "REMAINING : " . $this->remaining . "\n";
//            die();
        }
//        die();
//        echo "Le reste : Trying to find $this->remaining tokens\n";
        $cache_tokens = array();
        $pdo = \DB::connection()->getPdo();
        $date_active = date('Y-m-d');
        if(count($done_fais)>0) {
            $query = "SELECT id, destinataire_id, fai_id FROM tokens WHERE base_id = $campagne->base_id and campagne_id is null and date_active = '$date_active' and fai_id not in (" . implode(',', $done_fais) . ") order by priority, rand()";
            $stmt = $pdo->prepare($query);
            $stmt->execute();

            while ($token = $stmt->fetchObject()) {

                if ($this->remaining > 0) {
                    $cache_tokens[] = $token;
                }

                if (count($cache_tokens) >= 5000) {
                    $this->checkTokens($planning, $cache_tokens);
                    $cache_tokens = array();
                }

            }
        } else{
            $query = "SELECT id, destinataire_id, fai_id FROM tokens WHERE base_id = $campagne->base_id and campagne_id is null and date_active = '$date_active' order by priority, rand()";
            $stmt = $pdo->prepare($query);
            $stmt->execute();
            while ($token = $stmt->fetchObject()) {

                if ($this->remaining > 0) {
                    $cache_tokens[] = $token;
                }

                if (count($cache_tokens) >= 5000) {
                    $this->checkTokens($planning, $cache_tokens);
                    $cache_tokens = array();
                }

            }
        }

        if (count($cache_tokens) >= 0) {
            $this->checkTokens($planning, $cache_tokens);
            $cache_tokens = array();
        }

        \Log::info("[CampagneTokens][P$planning->id] : After last checkTokens (Planning $planning->id / Campagne $planning->campagne_id)");

        echo "Total : $this->total\n";
        echo "Passes : $this->passe \n";
        echo "Total Sélectionnés : $this->total \n";
        echo "Remaining : $this->remaining \n";
        echo "Total tokens testés : ".$this->total_tested."\n";
        echo "Total exclus : ".$this->total_excluded."\n";

        $planning->tokens_at = date('Y-m-d H:i:s');
        $planning->save();
        \Log::info("[CampagneTokens][P$planning->id] : C$planning->campagne_id -- terminé (Planning $planning->id / Campagne $planning->campagne_id)");

        $idplanning = $this->argument('planning_id');
         \Log::info("Lancement du calcul des statistiques pour la plannif n : " . $idplanning);
        // \Log::info("Stats off");
          \Artisan::call('tokens:stats', ['planning_id' => $idplanning ]);

        $resultat_selected = \DB::table('tokens')->where('date_active', date('Y-m-d'))->where('planning_id',$planning->id)->count();
        \DB::statement("UPDATE plannings SET volume_selected ='" . $resultat_selected . "' WHERE id = '" . $planning->id ."'");
        \Log::info("[CampagneTokens][P$planning->id] :  Calcul volume réel selected {".$resultat_selected."} (Planning $planning->id / Campagne $planning->campagne_id)");

        // alert info
        $controlev = 0.1 * $planning->volume;
        if($resultat_selected < $controlev){
            $email = 'Un volume de ' . $planning->volume . ' a été demandé pour la campagne ' . $campagne->nom . ' mais le nombre réel de destinataires selectionnés est de : ' . $resultat_selected ;

            Mail::raw($email, function ($message, $campagne) {

                $destinataires = \App\Models\User::where('is_valid', 1)
                    ->where('user_group_id', 1)
                    ->where('email', '!=', "")
                    ->get();

                $message->from('dev@lead-factory.net', 'Tor')->subject('Avertissement : pb sur la sélection de tokens pour '.$campagne->ref.' [' . $campagne->id .'] le ' . date('d-m-Y H:i'));
                foreach($destinataires as $d){
                    $message->to($d->email);
                }
                return "true";
            });
        }

        \App\Helpers\Profiler::report();
    }

    function checkTokens($planning, $tokens, $how_many = null) {
        $this->selected = 0;
        $this->passe++;

        if(empty($how_many)){
            $how_many = $this->remaining;
        }

        if ($this->remaining <= 0) {
            return;
        }

        $cache_ids = [];

//        echo "Found ".count($tokens)." tokens\t remaining avant : $this->remaining \t";

        foreach($tokens as $idx => $token) {
            $this->total_tested++;

            if (isset($this->to_exclude[$token->destinataire_id])) {
                $this->total_excluded++;
                continue;
            }

            $this->to_exclude[$token->destinataire_id] = 1;

            if ($this->remaining > 0 && $how_many > 0) {
//                echo "Remain : ".$this->remaining."\n";
                $this->selected++;
                $this->remaining--;
                $how_many--;
                $this->total++;

                $cache_ids[] = $token->id;
                //echo "Passe $this->passe \t campagne ".$campagne->id." \t idx ".str_pad($idx,3, ' ',  STR_PAD_LEFT) ." \t Reste $this->remaining \t Sélectionnés $this->selected \n";
            }
//            \t Fai ".$token->fai->id." ($token->fai->quota_campagne) \t Token $token->id \t Reste ".($this->remaining - $this->selected)." \n";
        }

        if (count($cache_ids) > 0) {
            $this->writeUpdates($cache_ids, $planning->campagne_id,$planning->id);
            $num = count($cache_ids);
//            echo "Exclus $this->total_excluded \t Writing $num - Remaining après : $this->remaining";
        }
        return $this->selected;
//        echo "\n";
    }

    protected function getArguments()
    {
        return [
            ['planning_id', InputArgument::REQUIRED, 'Planning id.'],
        ];
    }

    private function writeUpdates($ids, $campagne_id, $planning_id) {
        \DB::table('tokens')->whereIn('id', $ids)->update(['campagne_id' => $campagne_id, 'planning_id' => $planning_id]);
    }

}