<?php

namespace App\Mail;

use Illuminate\Bus\Queueable;
use Illuminate\Mail\Mailable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Contracts\Queue\ShouldQueue;

class reporter extends Mailable
{
    use Queueable, SerializesModels;

    /**
     * Create a new message instance.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Build the message.
     *
     * @return $this
     */
    public function build()
    {

        $pending = DB::table('adverts')->join('users','adverts.belong','users.id')
            ->join('categories','adverts.cat_id','categories.cat_id')
            ->join('pub_sizes','adverts.size','pub_sizes.id')
            ->where('adverts.status','pending')
            ->select('adverts.*','categories.cat_name','pub_sizes.p_size','pub_sizes.location',
                'pub_sizes.cost','users.name','users.id as user')->get();
        $rejected = DB::table('adverts')->join('users','adverts.belong','users.id')
            ->join('categories','adverts.cat_id','categories.cat_id')
            ->join('pub_sizes','adverts.size','pub_sizes.id')
            ->where('adverts.status','0')
            ->select('adverts.*','categories.cat_name','pub_sizes.p_size','pub_sizes.location',
                'pub_sizes.cost','users.name','users.id as user')->get();
        $approved = DB::table('adverts')->join('users','adverts.belong','users.id')
            ->join('categories','adverts.cat_id','categories.cat_id')
            ->join('pub_sizes','adverts.size','pub_sizes.id')
            ->where('adverts.status','0')
            ->select('adverts.*','categories.cat_name','pub_sizes.p_size','pub_sizes.location',
                'pub_sizes.cost','users.name','users.id as user')->get();

        $published = DB::table('adverts')->join('users','adverts.belong','users.id')
            ->join('categories','adverts.cat_id','categories.cat_id')
            ->join('pub_sizes','adverts.size','pub_sizes.id')
            ->where([['adverts.status','1'],['adverts.clear','1']])
            ->select('adverts.*','categories.cat_name','pub_sizes.p_size','pub_sizes.location',
                'pub_sizes.cost','users.name','users.id as user')->get();

        //transactions from the last 5 days
        $lastweek = date("Y-m-d", strtotime( '-5 days' ) );
        $now = date("Y-m-d");
        $pen_trans = DB::table('payments')
            ->join('users','payments.user_id','users.id')
            ->where('p_status','pending')
            ->select('payments.*','users.name')
            ->whereBetween('payments.created_at',[$lastweek, $now])
            ->get();
        $failed_trans = DB::table('payments')
            ->join('users','payments.user_id','users.id')
            ->select('payments.*','users.name')
            ->where('p_status','like','Failed%')
            ->whereBetween('payments.created_at',[$lastweek, $now])
            ->get();
        $succ_trans = DB::table('payments')
            ->join('users','payments.user_id','users.id')
            ->select('payments.*','users.name')
            ->where('p_status','like','success%')
            ->whereBetween('payments.created_at',[$lastweek, $now])
            ->get();

        return $this->markdown('dailyReport',compact('published','approved','rejected'
            ,'pending','failed_trans','succ_trans','pen_trans'))
        ->subject('Kwik Daily Report')
        ->to('info@kwikadvertizer.com');
    }
}