<?php

use Illuminate\Support\Facades\Route;
use Barryvdh\DomPDF\Facade\Pdf;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('login');
    // return Redirect::to('/dashboard');
})->name('login');

Route::get('/login', function () {
    return Redirect::to('/');
    // return Redirect::to('/dashboard');
});

Route::post('/login', function () {

    if(\Auth::attempt(array('email' => $_POST["email"], 'password' => $_POST["password"])))
    {
        return Redirect::to('/dashboard');
    }
    else
    {
        return Redirect::to('/?error=1');
    }

})->name('login');

Route::get('/logout', function(){

    Auth::logout();
    return redirect('/');

});

Route::group(['middleware' => 'auth'],function(){

    //Route::get('/', \App\Http\Livewire\Login::class);
    Route::get('/dashboard', \App\Http\Livewire\Dashboard::class);
    Route::get('/settings', \App\Http\Livewire\Setting::class);
    Route::get('/courses', \App\Http\Livewire\Course::class);
    Route::get('/categories', \App\Http\Livewire\Category::class);
    Route::get('/nations_list', \App\Http\Livewire\Nation::class);
    Route::get('/provinces', \App\Http\Livewire\Province::class);
    Route::get('/cities', \App\Http\Livewire\City::class);
    Route::get('/banks', \App\Http\Livewire\Bank::class);
    Route::get('/vats', \App\Http\Livewire\Vat::class);
    Route::get('/disciplines', \App\Http\Livewire\Discipline::class);
    Route::get('/course_types', \App\Http\Livewire\CourseType::class);
    Route::get('/course_subscriptions', \App\Http\Livewire\CourseSubscription::class);
    Route::get('/course_durations', \App\Http\Livewire\CourseDuration::class);
    Route::get('/course_levels', \App\Http\Livewire\CourseLevel::class);
    Route::get('/course_frequencies', \App\Http\Livewire\CourseFrequency::class);
    Route::get('/course_list', \App\Http\Livewire\CourseList::class);
    Route::get('/course_member', \App\Http\Livewire\CourseMember::class);
    Route::get('/receipts', \App\Http\Livewire\Receipt::class);
    Route::get('/cards', \App\Http\Livewire\Card::class);
    Route::get('/causals', \App\Http\Livewire\Causal::class);
    Route::get('/payment_methods', \App\Http\Livewire\PaymentMethod::class);
    Route::get('/members', \App\Http\Livewire\Member::class);
    Route::get('/suppliers', \App\Http\Livewire\Supplier::class);
    Route::get('/sponsors', \App\Http\Livewire\Sponsor::class);
    Route::get('/records', \App\Http\Livewire\Record::class);
    Route::get('/reminders', \App\Http\Livewire\Reminder::class);
    Route::get('/in', \App\Http\Livewire\RecordIN::class);
    Route::get('/out', \App\Http\Livewire\RecordOUT::class);
    Route::get('/records_in_out', \App\Http\Livewire\RecordINOUT::class);
    Route::get('/users', \App\Http\Livewire\User::class);
    Route::get('/profile', \App\Http\Livewire\Profile::class);
});

    Route::get('/receipt/{id}', function($id){
        $receipt = \App\Models\Receipt::findOrFail($id);
        $pdf = PDF::loadView('receipt', array('receipt' => $receipt));
        $pdfName = "Ricevuta_" . $receipt->member->last_name . "_" . $receipt->number . "_" . $receipt->year . ".pdf";
        return $pdf->stream($pdfName);
        /*return response()->streamDownload(
            fn () => print($pdf),
            "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf"
        );*/
    });

    Route::get('/receipt/mail/{id}', function($id){
        $receipt = \App\Models\Receipt::findOrFail($id);
        if ($receipt->status == 99)
            sendReceiptDeleteEmail($receipt);
        else
            sendReceiptEmail($receipt);
        /*
        $pdf = PDF::loadView('receipt', array('receipt' => $receipt));
        $pdfName = "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf";
        Storage::put('public/pdf/' . $pdfName, $pdf->output());
        $email = \App\Models\Member::findOrFail($receipt->member_id)->email;
        if ($email != '')
        {
            Mail::to($email)->send(new \App\Mail\ReceipEmail([
                'name' => 'Luca',
                'pdf' => 'public/pdf/' . $pdfName,
                'number' => $receipt->number . "/" . $receipt->year
            ]));
        }
            */

        return true;
        //return $pdf->stream();
        /*return response()->streamDownload(
            fn () => print($pdf),
            "ricevuta_" . $receipt->number . "_" . $receipt->year . ".pdf"
        );*/
    });


    Route::get('/nations', function(){
        if (isset($_GET["q"]))
            $datas = \App\Models\Nation::where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
        else
            $datas = \App\Models\Nation::orderBy('name')->get();
        $data = array();
        foreach($datas as $d)
        {
            $data[] = array("id" => $d->id, "text" => $d->name);
        }
        return array("results" => $data);
    });

    Route::get('/provinces/{nation_id}', function($nation_id){
        if (isset($_GET["q"]))
            $datas = \App\Models\Province::where('nation_id', $nation_id)->where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
        else
            $datas = \App\Models\Province::where('nation_id', $nation_id)->orderBy('name')->get();
        $data = array();
        foreach($datas as $d)
        {
            $data[] = array("id" => $d->id, "text" => $d->name);
        }
        return array("results" => $data);
    });

    Route::get('/cities/{province_id}', function($province_id){
        if (isset($_GET["q"]))
            $datas = \App\Models\City::where('province_id', $province_id)->where('name', 'like', $_GET["q"] . '%')->orderBy('name')->get();
        else
            $datas = \App\Models\City::where('province_id', $province_id)->orderBy('name')->get();
        $data = array();
        foreach($datas as $d)
        {
            $data[] = array("id" => $d->id, "text" => $d->name);
        }
        return array("results" => $data);
    });

    Route::get('/get_members', function(){

        $datas = [];

        // $datas = \App\Models\Member::select('members.*')->where('id', '>', 0);
        $x = \App\Models\Member::select('id', 'first_name', 'last_name', 'phone', 'birth_date', 'to_complete', 'current_status', 'certificate', 'certificate_date')->where('id', '>', 0);

        if (isset($_GET["search"]["value"]))
        {
            $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
            $x = $x->where(function ($query) use ($v) {
                $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                    ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
            });
            //where('first_name', 'like', '%' . $_GET["search"]["value"] . '%');
        }
        if ($_GET["cards"] != "")
        {
            $card_ids = \App\Models\MemberCard::whereIn('card_id', explode(",", $_GET["cards"]))->pluck('member_id');
            $x = $x->whereIn('id', $card_ids);
        }
        if ($_GET["filterCategories"] != "null")
        {
            $cc = array();
            $cats = explode(",", $_GET["filterCategories"]);
            foreach($cats as $c)
            {
                $m_ids = \App\Models\MemberCategory::where('category_id', $c)->pluck('member_id')->toArray();
                if (sizeof($cc) > 0)
                    $cc = array_intersect($cc, $m_ids);
                else
                    $cc = $m_ids;
            }
            $x = $x->whereIn('id', $cc);
            //$cats_ids = \App\Models\MemberCategory::whereIn('category_id', explode(",", $_GET["filterCategories"]))->pluck('member_id');
            //$x = $x->whereIn('id', $cats_ids);
        }
        if ($_GET["fromYear"] != "")
        {
            $x = $x->where('birth_date', '<', date("Y-m-d", strtotime("-" . $_GET["fromYear"] . " year", time())));
        }
        if ($_GET["toYear"] != "")
        {
            $x = $x->where('birth_date', '>', date("Y-m-d", strtotime("-" . $_GET["toYear"] . " year", time())));
        }
        if ($_GET["fromYearYear"] != "")
        {
            $x = $x->whereYear('birth_date', '>=', $_GET["fromYearYear"]);
        }
        if ($_GET["toYearYear"] != "")
        {
            $x = $x->whereYear('birth_date', '<=', $_GET["toYearYear"]);
        }

        $ids = [];

        if ($_GET["filterCertificateType"] != "null")
        {
            $types = \App\Models\MemberCertificate::where('type', $_GET["filterCertificateType"])->where('expire_date', '>', date("Y-m-d"))->pluck('member_id');
            $x = $x->whereIn('id', $types->toArray());;
            //$ids = array_merge($ids, $types->toArray());
        }

        if ($_GET["filterScadenza"] != "null")
        {
            if ($_GET["filterScadenza"] == "1")
                $scad = \App\Models\MemberCertificate::where('expire_date', '<', date("Y-m-d"))->pluck('member_id');
            if ($_GET["filterScadenza"] == "2")
                $scad = \App\Models\MemberCertificate::whereBetween('expire_date', [date("Y-m-d"), date("Y-m-d", strtotime("+1 month"))])->pluck('member_id');
            //$ids = array_merge($ids, $scad->toArray());
            $x = $x->whereIn('id', $scad->toArray());;
            //$x = $x->whereIn('id', $scadenza);
        }

        if ($_GET["filterStatus"] != "null")
        {
            $status = explode(",", $_GET["filterStatus"]);
            $members = \App\Models\Member::all();
            foreach($status as $s)
            {
                foreach($members as $m)
                {

                    $state = $m->isActive();

                    if ($state["status"] == $s)
                        $ids[] = $m->id;

                }

            }
        }

        if (sizeof($ids) > 0)
        {
            $x = $x->whereIn('id', $ids);
        }
        else
        {
            if ($_GET["filterStatus"] != "null")
                $x = $x->whereIn('id', [-1]);
        }

        $count = $x->count();

        $x = $x->orderBy('to_complete', 'DESC');

        if (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 0)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'phone';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 4)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 5)
                $column = 'current_status';
            if ($_GET["order"][0]["column"] == 6)
                $column = 'certificate';
            if ($column != '')
            {
                $x = $x->orderBy($column, $_GET["order"][0]["dir"]);
                if ($column == 'certificate')
                    $x = $x->orderBy('certificate_date', $_GET["order"][0]["dir"]);
            }
            else
                $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
        }
        else
            $x = $x->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');

        if (isset($_GET["start"]))
            $x = $x->offset($_GET["start"])->limit($_GET["length"])->get();
        else
            $x = $x->get();

        foreach($x as $idx => $r)
        {

            // $status = $r->getStatus();
            // $status = $status["status"];

            $status = $r->current_status;

            $class = $status > 0 ? ($status == 2 ? 'active' : 'due') : 'suspended';
            $text = $status > 0 ? ($status == 2 ? 'Tesserato' : 'Sospeso') : 'Non tesserato';

            if ($r->to_complete)
            {
                $text = 'Da completare';
                $class = "complete";
            }

            // $has_certificate = $r->hasCertificate();
            $y = '';

            if ($r->certificate_date != '')
            {
                $y = $r->certificate . "|" . date("d/m/Y", strtotime($r->certificate_date));
            }

            /*
            if($has_certificate["date"] != '')
            {
                if($has_certificate["date"] < date("Y-m-d"))
                    $y .= '0';

                if($has_certificate["date"] >= date("Y-m-d") && $has_certificate["date"] < date("Y-m-d", strtotime("+1 month")))
                    $y .= '1';

                if($has_certificate["date"] >= date("Y-m-d", strtotime("+1 month")))
                    $y .= '2';
                $y .= '|';
                $y .= $has_certificate["date"] != '' ? date("d/m/Y", strtotime($has_certificate["date"])) : '';
            }*/

            $datas[] = array(
                //'c' => $idx + 1,
                //'id' => "ID" . str_pad($r->id, 5, "0", STR_PAD_LEFT),
                'last_name' => $r->last_name . "|" . $r->id,
                'first_name' => $r->first_name . "|" . $r->id,
                'phone' => $r->phone,
                'age' => $r->getAge(),
                'year' => date("Y", strtotime($r->birth_date)),
                'status' => $class . "|" . $text,
                // 'state' => $x,
                'certificate' => $y,
                'action' => $r->id
            );
        }

        return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count));

    });

    Route::get('/get_record_in', function(){

        $datas = [];

        $x = \App\Models\Record::select('records.*', \DB::raw('members.first_name as first_name'), \DB::raw('members.last_name as last_name'), \DB::raw('payment_methods.name as payment')) // , \DB::raw('SUM(records.id) As total'))
                ->leftJoin('members', 'records.member_id', '=', 'members.id')
                ->leftJoin('payment_methods', 'records.payment_method_id', '=', 'payment_methods.id')
                ->where('records.type', 'IN');

        $y = \App\Models\Record::select('records_rows.amount', 'records.member_id', 'records.corrispettivo_fiscale', 'records.deleted', 'records.financial_movement', 'records_rows.causal_id', \DB::raw('members.first_name as first_name'), \DB::raw('members.last_name as last_name')) // , \DB::raw('SUM(records.id) As total'))
        ->leftJoin('members', 'records.member_id', '=', 'members.id')
        ->leftJoin('records_rows', 'records.id', '=', 'records_rows.record_id')
        ->where('records.type', 'IN');

        $hasFilter = false;

        if (isset($_GET["search"]["value"]))
        {
            if ($_GET["search"]["value"] != '')
            {
                $hasFilter = true;

                $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                $x = $x->where(function ($query) use ($v) {
                    $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                        ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
                });

                $y = $y->where(function ($query) use ($v) {
                    $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                        ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
                });
            }

                /*$v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                $x = $x->where(function ($query) use ($v) {
                    $query->where('first_name', 'like', '%' . $v . '%')
                            ->orWhere('last_name', 'like', '%' . $v . '%');
                });
                $y = $y->where(function ($query) use ($v) {
                                    $query->where('first_name', 'like', '%' . $v . '%')
                                            ->orWhere('last_name', 'like', '%' . $v . '%');
                                });
                            }*/
            //where('first_name', 'like', '%' . $_GET["search"]["value"] . '%');
        }


        //$x = $x->where(function ($query) use ($v) {

            // $datas = \App\Models\Record::where('type', 'IN')->with('member', 'payment_method');
            if ($_GET["filterCommercial"] == 1)
            {
                $hasFilter = true;
                $x = $x->where('commercial', true );
                $y = $y->where('records.commercial', true );
            }
            if ($_GET["filterCommercial"] == 2)
            {
                $hasFilter = true;
                $x = $x->where('commercial', false);
                $y = $y->where('records.commercial', false);
            }
            if ($_GET["filterMember"] > 0)
            {
                $hasFilter = true;
                $x = $x->where('member_id', $_GET["filterMember"]);
                $y = $y->where('member_id', $_GET["filterMember"]);
            }
            if ($_GET["filterPaymentMethod"] != "null")
            {
                $hasFilter = true;
                $payments = explode(",", $_GET["filterPaymentMethod"]);
                $x = $x->whereIn('payment_method_id', $payments);
                $y = $y->whereIn('payment_method_id', $payments);
            }
            if ($_GET["filterCausals"] != "null")
            {
                $hasFilter = true;
                $causals = explode(",", $_GET["filterCausals"]);
                //$causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
                $causals = \App\Models\RecordRow::whereIn('causal_id', $causals)->pluck('record_id');
                $x = $x->whereIn('records.id', $causals);
                $y = $y->whereIn('records.id', $causals);
            }
            if ($_GET["filterFrom"] != '')
            {
                $hasFilter = true;
                $x = $x->where('date', '>=', $_GET["filterFrom"]);
                $y = $y->where('date', '>=', $_GET["filterFrom"]);
            }
            if ($_GET["filterTo"] != '')
            {
                $hasFilter = true;
                $x = $x->where('date', '<=', $_GET["filterTo"]);
                $y = $y->where('date', '<=', $_GET["filterTo"]);
            }

        //});

        $start = 0;
        $limit = 100000;

        if (isset($_GET["start"]))
        {
            $start = $_GET["start"];
            $limit = $_GET["length"];
        }

        $excludeCausals = [];
        /*$borsellino = \App\Models\Causal::where('money', true)->first();
        if ($borsellino)
            $excludeCausals[] = $borsellino->id;*/

        // Aggiungo
        $excludes = \App\Models\Causal::where('no_records', true)->get();
        foreach($excludes as $e)
        {
            $excludeCausals[] = $e->id;
        }

        $exclude_from_records = \App\Models\Member::where('exclude_from_records', true)->pluck('id')->toArray();

        // Pagamento money
        $moneys = \App\Models\PaymentMethod::where('money', true)->pluck('id')->toArray();

        // Causale money
        $moneysCausal = \App\Models\Causal::where('money', true)->pluck('id')->toArray();

        $total = 0;

        $causals = [];
        if ($_GET["filterCausals"] != "null")
            $causals = explode(",", $_GET["filterCausals"]);

        foreach($y->get() as $r)
        {

            if (!in_array($r->payment_method_id, $moneys))
            {
                if ((!in_array($r->member_id, $exclude_from_records) || in_array($r->causal_id, $moneysCausal)) && (!$r->deleted || $r->deleted == null) && (!in_array($r->causal_id, $excludeCausals) || in_array($r->causal_id, $moneysCausal)) && (!$r->financial_movement || $r->financial_movement == null) && (!$r->corrispettivo_fiscale || $r->corrispettivo_fiscale == null))
                {
                    if (sizeof($causals) == 0 || in_array($r->causal_id, $causals))
                    {
                        $total += $r->amount;
                        if ($r->vat_id > 0)
                            $total += getVatValue($r->amount, $r->vat_id);
                    }
                }

            }
        }

        $count = $x->count();


        if (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 0)
                $column = 'date';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'records.amount';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 4)
                $column = 'commercial';
            if ($column != '')
                $x = $x->orderBy($column, $_GET["order"][0]["dir"])->orderBy('records.id', 'DESC');
            else
                $x = $x->orderBy('records.id', 'DESC');
        }
        else
            $x = $x->orderBy('records.date', 'DESC')->orderBy('records.id', 'DESC');

        $x = $x->offset($start)->limit($limit)->get();

        foreach($x as $idx => $r)
        {

            $causals = '';
            foreach($r->rows as $row)
            {
                $causals .= $row->causal->getTree() . "<br>";
            }

            $datas[] = array(
                //'id' => $r->id,
                'date' => $r->date,
                'total' => formatPrice($r->getTotal()),
                'first_name' => $r->first_name,
                'last_name' => $r->last_name,
                'commercial' => $r->financial_movement ? 'Movimento finanziario' : ($r->commercial ? 'SI' : 'NO'),
                'causals' => $causals,
                'payment' => $r->payment_method->name,
                'status' => $r->deleted ? 'Annullato' : '',
                'action' => $r->id . "||" . ($r->deleted ? 'x' : '')
            );


        }

        /*$datas[] = array(
            //'id' => $r->id,
            'date' => '',
            'total' => formatPrice($total),
            'first_name' => '',
            'last_name' => '',
            'commercial' => '',
            'causals' => '',
            'payment' => '',
            'status' => '',
            'action' => ''
        );*/

        if ($hasFilter)
            return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count, "totals" => formatPrice($total)));
        else
            return json_encode(array("data" => $datas,  "recordsTotal" => $count, "recordsFiltered" => $count));

    });

    Route::get('/get_record_out', function(){

        $datas = [];

        $hasFilter = false;

        $x = \App\Models\Record::where('type', 'OUT')->with('supplier', 'payment_method');

        if ($_GET["filterSupplier"] > 0)
        {
            $x = $x->where('supplier_id', $_GET["filterSupplier"]);
            $hasFilter = true;
        }
        /*if ($_GET["filterPaymentMethod"] > 0)
        {
            $x = $x->where('payment_method_id', $_GET["filterPaymentMethod"]);
        }
        if ($_GET["filterCausals"] > 0)
        {
            $causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
            $x = $x->whereIn('records.id', $causals);
        }*/
        if ($_GET["filterPaymentMethod"] != "null")
        {
            $payments = explode(",", $_GET["filterPaymentMethod"]);
            $x = $x->whereIn('payment_method_id', $payments);
            $hasFilter = true;
        }
        if ($_GET["filterCausals"] != "null")
        {
            $causals = explode(",", $_GET["filterCausals"]);
            //$causals = \App\Models\RecordRow::where('causal_id', $_GET["filterCausals"])->pluck('record_id');
            $causals = \App\Models\RecordRow::whereIn('causal_id', $causals)->pluck('record_id');
            $x = $x->whereIn('records.id', $causals);
            $hasFilter = true;
        }
        if ($_GET["filterFrom"] != '')
        {
            $x = $x->where('date', '>=', $_GET["filterFrom"]);
            $hasFilter = true;
        }
        if ($_GET["filterTo"] != '')
        {
            $x = $x->where('date', '<=', $_GET["filterTo"]);
            $hasFilter = true;
        }

        $total = 0;
        foreach($x->get() as $r)
        {
            foreach($r->rows as $rr)
            {
                $total += $rr->amount;
                if ($rr->vat_id > 0)
                    $total += getVatValue($rr->amount, $rr->vat_id);
            }
        }

        $x = $x->get();

        foreach($x as $idx => $r)
        {

            $causals = '';
            foreach($r->rows as $row)
            {
                $causals .= $row->causal->getTree() . "<br>";
            }

            $datas[] = array(
                //'id' => $r->id,
                'date' => $r->date,
                'total' => formatPrice($r->getTotal()),
                'supplier' => $r->supplier->name,
                'causals' => $causals,
                'payment' => $r->payment_method->name,
                'action' => $r->id . "|"
            );

        }
        /*
        $datas[] = array(
            //'id' => $r->id,
            'date' => '',
            'total' => formatPrice($total),
            'supplier' => '',
            'causals' => '',
            'payment' => '',
            'action' => ''
        );
        */
        if ($hasFilter)
            return json_encode(array("data" => $datas, "totals" => formatPrice($total)));
        else
            return json_encode(array("data" => $datas));

    });

    Route::get('/get_course_list', function(){

        $member_course = \App\Models\MemberCourse::with('member')->with('course');

        if (isset($_GET["search"]["value"]))
        {

            if ($_GET["search"]["value"] != '')
            {
                $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                    $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                        ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
                })->pluck('id');
                /*
                $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
                $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                    $query->where('first_name', 'like', '%' . $v . '%')
                            ->orWhere('last_name', 'like', '%' . $v . '%');
                })->pluck('id');*/
                $member_course = $member_course->whereIn('member_id', $member_ids);
            }
        }

        if ($_GET["filterCourse"] != "null")
        {
            $course_ids = [];
            $courses = explode(",", $_GET["filterCourse"]);
            foreach($courses as $c)
            {
                $all = \App\Models\Course::where('name', 'like', '%' . $c . "%")->get();
                foreach($all as $a)
                {
                    $course_ids[] = $a->id;
                }
            }
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }

        if ($_GET["filterYear"] != "")
        {
            $course_ids = \App\Models\Course::where('year', $_GET["filterYear"])->pluck('id');
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }

        if ($_GET["filterLevel"] != "null")
        {
            $levels = explode(",", $_GET["filterLevel"]);
            $course_ids = \App\Models\Course::whereIn('course_level_id', $levels)->pluck('id');
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterFrequency"] != "null")
        {
            $frequencies = explode(",", $_GET["filterFrequency"]);
            $course_ids = \App\Models\Course::whereIn('course_frequency_id', $frequencies)->pluck('id');
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterType"] != "null")
        {
            $types = explode(",", $_GET["filterType"]);
            $course_ids = \App\Models\Course::whereIn('course_type_id', $types)->pluck('id');
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterDuration"] != "null")
        {
            $durations = explode(",", $_GET["filterDuration"]);
            $course_ids = \App\Models\Course::whereIn('course_duration_id', $durations)->pluck('id');
            $member_course = $member_course->whereIn('course_id', $course_ids);
        }

        $totals = [];
        $totalIsc = [];

        $datas = [];

        $xxx = 1;

        $member_course_totals = $member_course->get();
        foreach($member_course_totals as $x)
        {

            $price = 0;
            $price = $x->price; // $x->course->price;
            $subPrice = $x->subscription_price; // $x->course->subscription_price;
            $records = \App\Models\Record::where('member_course_id', $x->id)->where('deleted', 0)->get();
            $prices = [];
            foreach ($records as $record)
            {
                foreach ($record->rows as $row)
                {

                    if ($row->causal_id == $x->course->sub_causal_id) //  || str_contains(strtolower($row->note), 'iscrizione'))
                    //if (str_contains(strtolower($row->note), 'iscrizione'))
                    {
                        $subPrice = $row->amount;
                    }
                    if ($row->causal_id == $x->course->causal_id && !str_contains(strtolower($row->note), 'iscrizione'))
                    {
                        $tot = sizeof(json_decode($row->when));
                        foreach(json_decode($row->when) as $m)
                        {
                            $prices[$m->month] = $row->amount / $tot;
                        }
                    }
                }
            }

            for($i=1; $i<=12; $i++)
            {

                $cls = getColor($x->months, $i);
                if ($cls != 'wgrey')
                {

                    if (!isset($totals[$i]))
                    {
                        $totals[$i]['green'] = 0;
                        $totals[$i]['orange'] = 0;
                        $totals[$i]['yellow'] = 0;
                    }


                    if ($cls == 'yellow')
                    {
                        $totals[$i][$cls] += 1;
                    }
                    else
                    {
                        $p = isset($prices[$i]) ? $prices[$i] : $price;
                        $totals[$i][$cls] += $p;
                    }
                }
            }

            $sub = $x->subscribed ? "Y" : "N";
            if (isset($totalIsc[$sub]))
                $totalIsc[$sub] += $subPrice;
            else
                $totalIsc[$sub] = $subPrice;

            $datas[] = array(
                "column_19" => $x->course->name,
                "column_0" => $x->member->last_name,
                "column_1" => $x->member->first_name,
                "column_2" => $x->subscribed . "§" . formatPrice($subPrice),
                "column_3" => getColor($x->months, 9) . "§" . formatPrice(isset($prices[9]) ? $prices[9] : $price),
                "column_4" => getColor($x->months, 10) . "§" . formatPrice(isset($prices[10]) ? $prices[10] : $price),
                "column_5" => getColor($x->months, 11) . "§" . formatPrice(isset($prices[11]) ? $prices[11] : $price),
                "column_6" => getColor($x->months, 12) . "§" . formatPrice(isset($prices[12]) ? $prices[12] : $price),
                "column_7" => getColor($x->months, 1) . "§" . formatPrice(isset($prices[1]) ? $prices[1] : $price),
                "column_8" => getColor($x->months, 2) . "§" . formatPrice(isset($prices[2]) ? $prices[2] : $price),
                "column_9" => getColor($x->months, 3) . "§" . formatPrice(isset($prices[3]) ? $prices[3] : $price),
                "column_10" => getColor($x->months, 4) . "§" . formatPrice(isset($prices[4]) ? $prices[4] : $price),
                "column_11" => getColor($x->months, 5) . "§" . formatPrice(isset($prices[5]) ? $prices[5] : $price),
                "column_12" => getColor($x->months, 6) . "§" . formatPrice(isset($prices[6]) ? $prices[6] : $price),
                "column_13" => getColor($x->months, 7) . "§" . formatPrice(isset($prices[7]) ? $prices[7] : $price),
                "column_14" => getColor($x->months, 8) . "§" . formatPrice(isset($prices[8]) ? $prices[8] : $price),
                "column_15" => $x->course_id,
                "column_16" => $x->id,
                "column_17" => $x->member_id,
                "column_18" => $xxx++
            );

        }

        $count = $member_course->count();

        $js = '';
        $xx = 4;
        $str = '';
        if ($count > 0)
        {
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=green><small>" . (isset($totalIsc["Y"]) ? formatPrice($totalIsc["Y"]) : 0) . "</small></a><br>";
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=orange><small>" . (isset($totalIsc["N"]) ? formatPrice($totalIsc["N"]) : 0) . "</small></a><br>";
            $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=yellow><small>0</small></a><br>";
        }
        $js .= $xx . "§" . $str . "_";
        $str = "";
        foreach($totals as $z => $t)
        {
            if ($z == 1) $xx = 9;
            if ($z == 2) $xx = 10;
            if ($z == 3) $xx = 11;
            if ($z == 4) $xx = 12;
            if ($z == 5) $xx = 13;
            if ($z == 6) $xx = 14;
            if ($z == 7) $xx = 15;
            if ($z == 8) $xx = 16;
            if ($z == 9) $xx = 5;
            if ($z == 10) $xx = 6;
            if ($z == 11) $xx = 7;
            if ($z == 12) $xx = 8;
            $str = '';
            foreach($t as $x => $c)
            {
                $y = $x == 'yellow' ? $c : formatPrice($c);
                $str .= "<a style='width:100%;float:right; text-align:right; display:block;' class=" . $x . "><small>" . $y . "</small></a><br>";
            }
            $js .= $xx . "§" . $str . "_";
            $xx += 1;
        }

        if (isset($_GET["order"]))
        {
            $s = $_GET["order"][0]["column"];
            if ($s == 1) $s = 21;
            array_multisort(array_column($datas, 'column_' . ($s - 2)), $_GET["order"][0]["dir"] == "asc" ? SORT_ASC : SORT_DESC, SORT_NATURAL|SORT_FLAG_CASE, $datas);
        }

        $xxx = 1;
        foreach($datas as $yyy => $d)
        {
            $datas[$yyy]["column_18"] = $xxx++;
        }

        if (isset($_GET["start"]))
            $datas = array_slice($datas, $_GET["start"], $_GET["length"]);

        return json_encode(array("data" => $datas, "recordsTotal" => $count, "recordsFiltered" => $count, "totals" => $js));

    });

    Route::get('/get_course_members', function(){

        //$datas = \App\Models\MemberCourse::with('member');

        $datas = \App\Models\MemberCourse::select('member_courses.*', 'courses.name as course_name', 'members.first_name', 'members.last_name', 'members.email', 'members.phone', 'members.birth_date')
        ->leftJoin('courses', 'member_courses.course_id', '=', 'courses.id')
        ->leftJoin('members', 'member_courses.member_id', '=', 'members.id');

        if (isset($_GET["search"]["value"]))
        {
            $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));

            $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                    ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
            })->pluck('id');

            /*$member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->where('first_name', 'like', '%' . $v . '%')
                        ->orWhere('last_name', 'like', '%' . $v . '%');
            })->pluck('id');*/
            $datas = $datas->whereIn('member_id', $member_ids);
        }

        if ($_GET["filterCourse"] != "null")
        {
            $course_ids = [];
            $courses = explode(",", $_GET["filterCourse"]);
            foreach($courses as $c)
            {
                $all = \App\Models\Course::where('name', 'like', '%' . $c . "%")->get();
                foreach($all as $a)
                {
                    $course_ids[] = $a->id;
                }
            }
            $datas = $datas->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterLevel"] != "null")
        {
            $levels = explode(",", $_GET["filterLevel"]);
            $course_ids = \App\Models\Course::whereIn('course_level_id', $levels)->pluck('id');
            $datas = $datas->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterFrequency"] != "null")
        {
            $frequencies = explode(",", $_GET["filterFrequency"]);
            $course_ids = \App\Models\Course::whereIn('course_frequency_id', $frequencies)->pluck('id');
            $datas = $datas->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterType"] != "null")
        {
            $types = explode(",", $_GET["filterType"]);
            $course_ids = \App\Models\Course::whereIn('course_type_id', $types)->pluck('id');
            $datas = $datas->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterDuration"] != "null")
        {
            $durations = explode(",", $_GET["filterDuration"]);
            $course_ids = \App\Models\Course::whereIn('course_duration_id', $durations)->pluck('id');
            $datas = $datas->whereIn('course_id', $course_ids);
        }
        if ($_GET["filterDays"] != "null")
        {
            $ids = [];
            $days = explode(",", $_GET["filterDays"]);
            foreach($days as $d)
            {
                $all = \App\Models\MemberCourse::where('when', 'like', "%" . $d . "%")->get();
                foreach($all as $a)
                {
                    $ids[] = $a->id;
                }
            }
            $datas = $datas->whereIn('member_courses.id', $ids);
        }
        if ($_GET["filterHours"] != "null")
        {
            $ids = [];
            $hours = explode(",", $_GET["filterHours"]);
            foreach($hours as $h)
            {
                $all = \App\Models\MemberCourse::where('when', 'like', '%"from":"' . $h . "%")->get();
                foreach($all as $a)
                {
                    $ids[] = $a->id;
                }
            }
            $datas = $datas->whereIn('member_courses.id', $ids);
        }

        if ($_GET["filterSubscription"] != "")
        {
            $ids = \App\Models\MemberCourse::where('subscribed', $_GET["filterSubscription"] == 1 ? true : false)->pluck('id');
            $datas = $datas->whereIn('member_courses.id', $ids);
            //$this->filter .= $this->filter != '' ? ', ' : '';
            //$this->filter .= "Pagata sottoscrizione : " . ($this->filterSubscription == 1 ? "SI" : "NO") . " ";
        }

        if ($_GET["filterCertificateType"] != "null")
        {
            $ctypes = \App\Models\MemberCertificate::where('type', $_GET["filterCertificateType"])->where('expire_date', '>', date("Y-m-d"))->pluck('member_id');
            $datas = $datas->whereIn('member_id', $ctypes);
        }
        if ($_GET["filterCertificateScadenza"] != "null")
        {
            if ($_GET["filterCertificateScadenza"] == "1")
                $scad = \App\Models\MemberCertificate::where('expire_date', '<', date("Y-m-d"))->pluck('member_id');
            if ($_GET["filterCertificateScadenza"] == "2")
                $scad = \App\Models\MemberCertificate::whereBetween('expire_date', [date("Y-m-d"), date("Y-m-d", strtotime("+1 month"))])->pluck('member_id');
            $datas = $datas->whereIn('member_id', $scad);
        }

        if ($_GET["fromYear"] != "")
        {
            $m_ids = \App\Models\Member::where('birth_date', '<', date("Y-m-d", strtotime("-" . $_GET["fromYear"] . " year", time())))->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }

        if ($_GET["toYear"] != "")
        {
            $m_ids = \App\Models\Member::where('birth_date', '>', date("Y-m-d", strtotime("-" . $_GET["toYear"] . " year", time())))->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }

        if ($_GET["fromFromYear"] != "")
        {
            $m_ids = \App\Models\Member::whereYear('birth_date', '>=', $_GET["fromFromYear"])->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }
        if ($_GET["toToYear"] != "")
        {
            $m_ids = \App\Models\Member::whereYear('birth_date', '<=', $_GET["toToYear"])->pluck('id');
            $datas = $datas->whereIn('member_id', $m_ids);
        }
        if ($_GET["filterCards"] != "null")
        {
            $cards = explode(",", $_GET["filterCards"]);
            $card_ids = \App\Models\MemberCard::whereIn('card_id', $cards)->pluck('member_id');
            $datas = $datas->whereIn('member_id', $card_ids);
        }

        if ($_GET["filterYear"] != "")
        {
            $course_ids = \App\Models\Course::where('year', $_GET["filterYear"])->pluck('id');
            $datas = $datas->whereIn('course_id', $course_ids);
            //$this->filter .= $this->filter != '' ? ', ' : '';
            //$this->filter .= "Anno : " . $this->filterYear . " ";
        }

        $aRet = [];

        if (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'course_name';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 4)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 5)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 6)
                $column = 'birth_date';
            if ($_GET["order"][0]["column"] == 7)
                $column = 'phone';
            if ($_GET["order"][0]["column"] == 8)
                $column = 'email';
            if ($column != '')
                $datas = $datas->orderBy($column, $_GET["order"][0]["dir"]);
            else
                $datas = $datas->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');
        }
        else
            $datas = $datas->orderBy('last_name', 'ASC')->orderBy('first_name', 'ASC');

        if ($_GET["filterStatus"] != "null")
        {
            $status = explode(",", $_GET["filterStatus"]);
            foreach($status as $s)
            {
                foreach($datas->get() as $aaa)
                {

                    $state = \App\Models\Member::findOrFail($aaa->member_id)->isActive();
                    if ($state["status"] == $s)
                        $aRet[] = $aaa;
                }
            }
        }
        else
            $aRet = $datas->get();

        $ret = [];
        foreach($aRet as $idx => $r)
        {
            $date1 = new DateTime($r->birth_date);
            $date2 = new DateTime("now");
            $interval = $date1->diff($date2);

            $ret[] = array(

                "column_0" => $idx + 1,
                "column_8" => $r->course_name,
                "column_1" => $r->last_name,
                "column_2" => $r->first_name,
                "column_3" => strval($interval->y),
                "column_4" => date("Y", strtotime($r->birth_date)),
                "column_5" => $r->phone,
                "column_6" => $r->email,
                "column_7" => $r->member_id
            );
        }

        if (isset($_GET["start"]))
            $ret = array_slice($ret, $_GET["start"], $_GET["length"]);

        return json_encode(array("data" => $ret, "recordsTotal" => sizeof($aRet), "recordsFiltered" => sizeof($aRet)));

    });

    Route::get('/get_receipts', function(){


        $x = \App\Models\Receipt::select('receipts.*', 'members.first_name', 'members.last_name')->leftJoin('members', 'receipts.member_id', '=', 'members.id');
        if (isset($_GET["search"]["value"]))
        {
            $v = str_replace("'", "\'", stripcslashes($_GET["search"]["value"]));
            $member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->whereRaw("CONCAT(first_name, ' ', last_name) like '%" . $v . "%'")
                    ->orWhereRaw("CONCAT(last_name, ' ', first_name) like '%" . $v . "%'");
            })->pluck('id');
            /*$member_ids = \App\Models\Member::where(function ($query) use ($v) {
                $query->where('first_name', 'like', '%' . $v . '%')
                        ->orWhere('last_name', 'like', '%' . $v . '%');
            })->pluck('id');*/
            $x = $x->whereIn('member_id', $member_ids);
        }
        if ($_GET["filterStatus"] != '')
            $x = $x->where('receipts.status', $_GET["filterStatus"]);
        if ($_GET["filterFrom"] != "")
            $x = $x->where('date', '>=', $_GET["filterFrom"]);
        if ($_GET["filterTo"] != "")
            $x = $x->where('date', '<=', $_GET["filterTo"]);

        $count = $x->count();

        if (isset($_GET["order"]))
        {
            $column = '';
            if ($_GET["order"][0]["column"] == 0)
                $column = 'year';
            if ($_GET["order"][0]["column"] == 1)
                $column = 'number';
            if ($_GET["order"][0]["column"] == 2)
                $column = 'last_name';
            if ($_GET["order"][0]["column"] == 3)
                $column = 'first_name';
            if ($_GET["order"][0]["column"] == 4)
                $column = 'status';
            if ($_GET["order"][0]["column"] == 5)
                $column = 'date';
            if ($column != '')
                $x = $x->orderBy($column, $_GET["order"][0]["dir"])->orderBy('id', 'DESC');
            else
                $x = $x->orderBy('id', 'DESC');
        }
        else
            $x = $x->orderBy('id', 'DESC');

        if (isset($_GET["start"]))
            $x = $x->offset($_GET["start"])->limit($_GET["length"])->get();
        else
            $x = $x->get();
        $datas = [];
        foreach($x as $idx => $r)
        {
            $ids = $r->id . "|" . $r->record_id;
            $datas[] = array(
                'year' => $r->year,
                'number' => $r->number,
                'last_name' => $r->member->last_name,
                'first_name' => $r->member->first_name,
                'status' => $r->status,
                'date' => date("d/m/Y", strtotime($r->date)),
                'totals' => formatPrice($r->rows->sum('amount')),
                'action' => $ids
            );

        }

        return json_encode(array("data" => $datas, "recordsTotal" => $count, "recordsFiltered" => $count));

    });


function getColor($months, $m)
{
    $class = "wgrey";
    foreach(json_decode($months) as $mm)
    {
        if ($mm->m == $m)
        {
            if ($mm->status == "")
            {
                $class = "orange";
            }
            if ($mm->status == "1")
            {
                $class = "green";
            }
            if ($mm->status == "2")
            {
                $class = "yellow";
            }
        }
    }
    return $class;
}

Route::get('/migrate', function(){
    \Artisan::call('migrate');
    dd('migrated!');
});

Route::get('/updateData', function()
{
    // Call and Artisan command from within your application.
    Artisan::call('update:data');
});

Route::get('/seed', function()
{
    // Call and Artisan command from within your application.
    Artisan::call('db:seed');
});


Route::get('/updateCourseCausal', function(){

    $member_courses = \App\Models\MemberCourse::all();
    foreach($member_courses as $x)
    {

        $records = \App\Models\Record::where('member_course_id', $x->id)->get();

        foreach ($records as $record)
        {
            foreach ($record->rows as $row)
            {

                //if ($row->causal_id == $x->course->sub_causal_id || str_contains(strtolower($row->note), 'iscrizione'))
                if (str_contains(strtolower($row->note), 'iscrizione'))
                {
                    $row->causal_id = $x->course->sub_causal_id;
                    $row->save();
                }
            }
        }
    }
});