<?php
namespace App\Http\Controllers\Admin;

use App\User;
use App\Orders;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Morilog\Jalali;
use DB;
use Session;
use Auth;
use App\Settings;

class ReportsController extends Controller
{
    public function index(){
        $date = (object)array();
        $MonthStart1 = Jalali\Jalalian::forge('now')->format('Y/m/01 00:00');
        $MonthStart = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $MonthStart1);
        $MonthStop1 = Jalali\Jalalian::forge('now')->addMonths(1)->format('Y/m/01 00:00');
        $MonthStop = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $MonthStop1);


        $date->start1 = $MonthStart1;
        $date->start = $MonthStart;
        $date->stop1 = $MonthStop1;
        $date->stop = $MonthStop;
        return view('admin.reports.reports',['date'=>$date]);
    }

    public function load(Request $request){

        $functions = new \App\functions;
        $date_start = $functions->toLatin($request->date_start);
        $date_start = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $date_start);

        $date_stop = $functions->toLatin($request->date_stop);
        $date_stop = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $date_stop);


        $result = (object)array();


        $result->UserInvitation = User::where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->whereNotNull('invitationID')->count();
        $result->Ticket = \App\Ticket::where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->count();
        $result->CardBank = \App\CardBank::where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->count();

        $result->Finance = \App\UserFinance::where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->count();

        $result->FinanceIncCount = \App\UserFinance::where('type','برداشت')->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->count();
        $result->FinanceIncAmount = \App\UserFinance::where('type','برداشت')->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->sum('amount');
        $result->FinanceDecCount = \App\UserFinance::where('type','واریز')->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->count();
        $result->FinanceDecAmount = \App\UserFinance::where('type','واریز')->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->sum('amount');

        $result = self::GetDetailGeneral($date_start,$date_stop);

        $cryptocurrency = \App\Cryptocurrency::all()->pluck('name')->toArray();
        $result->Currencys = $cryptocurrency;
        foreach($result->Currencys as $Currency){
            $result->$Currency = (object)array();
            $result->$Currency = self::GetDetailCurrency($Currency,$date_start,$date_stop);
        }


        //ChartLine
        $Label_CL = array();
        $UserAdd_CL = array();
        $AmountSell = array();
        $AmountBuy = array();

        $date1Timestamp = strtotime($date_start);
        $date2Timestamp = strtotime($date_stop);

        $difference = $date2Timestamp - $date1Timestamp;
        $days = floor($difference / (60*60*24) );

        for($i=$days;$i>=0;$i--){

            // label
            $date1 = date("Y-m-d",strtotime('-'. ($i-1) . ' day',strtotime($date_stop)));
            array_push($Label_CL,  Jalali\CalendarUtils::strftime('d(F)', strtotime($date1)) );
            $date2 = date("Y-m-d",strtotime(" + 1 day", strtotime($date1)) );

            $CountUser = User::where('created_at', '>=',$date1)->where('created_at', '<=',$date2)->count();
            array_push($UserAdd_CL,$CountUser );

            $Sell = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$date1)->where('created_at', '<=',$date2)->where('type', 'sell')->sum('amount');
            array_push($AmountSell, round($Sell/1000000,1) );

            $Buy = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$date1)->where('created_at', '<=',$date2)->where('type','!=', 'sell')->sum('amount');
            array_push($AmountBuy, round($Buy/1000000,1) );
        }
        //die();
        $result->Label_CL = $Label_CL;
        $result->UserAdd_CL = $UserAdd_CL;
        $result->AmountSell = $AmountSell;
        $result->AmountBuy = $AmountBuy;

        //ChartPie
        $result->PieCurrencyOrders = Orders::select('orders_model')->selectRaw('sum(amount) as amount')->where('status','!=','معلق')
            ->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->where('status','!=','معلق,عودت')->groupBy('orders_model')->get();

        $result->PieCurrencyVia = Orders::select('via')->selectRaw('count(*) as count')->where('status','!=','معلق')
            ->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->where('status','!=','معلق,عودت')->groupBy('via')->get();

        $result->PieCurrencyViaUsers = User::select('register_via as via')->selectRaw('count(*) as count')->groupBy('register_via')
            ->where('created_at', '>=',$date_start)->where('created_at', '<=',$date_stop)->get();

        //return response()->json($result->PieCurrencyVia);


        return view('admin.reports.load',['result'=>$result]);
    }

    public function GetDetailGeneral($DateStart,$dateStop){
        $result = (object)array();
        $result->CountOrders = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->count();
        $result->AmountOrders = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->sum('amount');
        $result->CountOrdersSell = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->count();
        $result->AmountOrdersSell = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->sum('amount');
        $result->CountOrdersBuy = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->count();
        $result->AmountOrdersBuy = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->sum('amount');
        $result->CountUser = User::where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->count();

        $AmountMaxSell = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->orderBy('amount','desc')->first();
        $result->AmountMaxSell = isset($AmountMaxSell) ? $AmountMaxSell->amount : 0;
        $AmountMaxBuy = Orders::where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->orderBy('amount','desc')->first();
        $result->AmountMaxBuy = isset($AmountMaxBuy) ? $AmountMaxBuy->amount : 0;
        return $result;
    }
    public function GetDetailCurrency($Currency,$DateStart,$dateStop){
        $result = (object)array();
        $result->CountOrders = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->count();
        $result->AmountOrders = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->sum('amount');
        $result->CountOrdersBuy = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->count();
        $result->CountOrdersSell = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->count();
        $result->AmountOrdersBuy = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->sum('amount');
        $result->AmountOrdersSell = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->sum('amount');

        $AmountMaxSell = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type', 'sell')->orderBy('amount','desc')->first();
        $result->AmountMaxSell = isset($AmountMaxSell) ? $AmountMaxSell->amount : 0;
        $result->IDMaxSell = isset($AmountMaxSell) ? $AmountMaxSell->id : 0;

        $AmountMaxBuy = Orders::where('orders_model',$Currency)->where('status','!=','معلق')->where('status','!=','معلق,عودت')->where('created_at', '>=',$DateStart)->where('created_at', '<=',$dateStop)->where('type','!=', 'sell')->orderBy('amount','desc')->first();
        $result->AmountMaxBuy = isset($AmountMaxBuy) ? $AmountMaxBuy->amount : 0;
        $result->IDMaxBuy = isset($AmountMaxBuy) ? $AmountMaxBuy->id : '';

        $Currencys = array('PSVouchers','PerfectMoney','PMvoucher','webmoney');
        if(in_array($Currency,$Currencys)) {
            $tabel = 'arzland_orders_' . strtolower($Currency);
            $dollarBuy = DB::select('select sum(amount_dollar) as amount_dollar from ' . $tabel . ',arzland_orders where ' . $tabel . '.id_order = arzland_orders.id
                                            and arzland_orders.type != "sell" and created_at >= ? and created_at <=?', [$DateStart, $dateStop]);
            $result->dollarBuy = round($dollarBuy[0]->amount_dollar, 6);

            $dollarSell = DB::select('select sum(amount_dollar) as amount_dollar from ' . $tabel . ',arzland_orders where ' . $tabel . '.id_order = arzland_orders.id
                                            and arzland_orders.type = "sell" and created_at >= ? and created_at <=?', [$DateStart, $dateStop]);
            $result->dollarSell = round($dollarSell[0]->amount_dollar, 6);
        }else{
            $dollarBuy = DB::select('select sum(amount_coin) as amount_dollar from arzland_orders_cryptocurrency,arzland_orders where arzland_orders_cryptocurrency.id_order = arzland_orders.id
                                            and arzland_orders.type != "sell" and coin=? and created_at >= ? and created_at <=? ', [$Currency,$DateStart, $dateStop]);
            $result->dollarBuy = round($dollarBuy[0]->amount_dollar, 6);

            $dollarSell = DB::select('select sum(amount_coin) as amount_dollar from arzland_orders_cryptocurrency,arzland_orders where arzland_orders_cryptocurrency.id_order = arzland_orders.id
                                            and arzland_orders.type = "sell" and coin=? and created_at >= ? and created_at <=?', [$Currency,$DateStart, $dateStop]);
            $result->dollarSell = round($dollarSell[0]->amount_dollar, 6);
        }

        return $result;
    }



    public function user_index(){
        return view('admin.reports.users');
    }


    public function list_user(Request $request){

        $functions = new \App\functions;


        $result["data"] = array();

        $whereRaw = '';
        if(isset($request->currency))
            $whereRaw .=' (orders_model = "'.$request->currency.'")';

        if(isset($request->date_start)){
            $request->date_start = $functions->toLatin($request->date_start);
            $request->date_start = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $request->date_start);
            if($request->sort != 'Invitation')
                $whereRaw .= ' (arzland_orders.created_at >= "'.$request->date_start.'")';
        }

        if(isset($request->date_stop)){
            $request->date_stop = $functions->toLatin($request->date_stop);
            $request->date_stop = Jalali\CalendarUtils::createCarbonFromFormat('Y/m/d H:i', $request->date_stop);
            if($request->sort != 'Invitation')
                $whereRaw .= ' (arzland_orders.created_at <="'.$request->date_stop.'")';
        }

        if(isset($request->sort)){
            if($request->sort == 'Buy')
                $Users = DB::select('select arzland_users.*,count(arzland_orders.id) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user and arzland_orders.type !="sell" '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');
            if($request->sort == 'Sell')
                $Users = DB::select('select arzland_users.*,count(arzland_orders.id) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user and arzland_orders.type ="sell" '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');
            if($request->sort == 'AmountBuySell')
                $Users = DB::select('select arzland_users.*,sum(arzland_orders.amount) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');
            if($request->sort == 'AmountBuy')
                $Users = DB::select('select arzland_users.*,sum(arzland_orders.amount) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user and arzland_orders.type !="sell" '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');
            if($request->sort == 'AmountSell')
                $Users = DB::select('select arzland_users.*,sum(arzland_orders.amount) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user and arzland_orders.type ="sell" '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');
            if($request->sort == 'CommissionCount')
                $Users = DB::select('select arzland_users.*,(select count(id) from arzland_users_invitation where arzland_users_invitation.id_user = arzland_users.id) as V from arzland_users,arzland_orders where
                                            arzland_users.id = arzland_orders.id_user'. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id HAVING V > 0 Order BY V desc limit 300');
            if($request->sort == 'CommissionAmount')
                $Users = DB::select('select arzland_users.*,(select sum(amount) from arzland_users_invitation where arzland_users_invitation.id_user = arzland_users.id) as V from arzland_users,arzland_orders where
                                            arzland_users.id = arzland_orders.id_user'. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id HAVING V > 0 Order BY V desc limit 300');
            if($request->sort == 'Invitation')
                $Users = DB::select('select users.*,(select count(id) from arzland_users where invitationID = users.id) as V from arzland_users as users HAVING V > 0 Order BY V desc limit 300');

        }
        $whereRaw = str_replace(') (',') and (', $whereRaw);

        if(!isset($request->sort))
            $Users = DB::select('select arzland_users.*,count(arzland_orders.id) as V from arzland_users,arzland_orders where arzland_users.id = arzland_orders.id_user '. ($whereRaw != '' ? ' and '.$whereRaw : '') .' GROUP BY arzland_users.id Order BY V desc limit 300');

        //return response()->json($Users);

        foreach($Users as $user):

            $array["1"] = $user->name .' '.$user->family;
            $array["2"] = $user->code_meli;
            $array["3"] = $user->mobile;
            $array["4"] = Jalali\CalendarUtils::strftime('d F Y - H:i', strtotime($user->created_at));

            $array["5"] = number_format($user->V);

            switch ($user->access) {
                case 0:
                    $array["6"] =  '<span class="badge badge-pill bg-danger font-weight-light">بلاک</span>';
                    break;
                case 1:
                    $array["6"] =  '<span class="badge badge-pill bg-success font-weight-light">فعال</span>';
                    break;
                case 2:
                    $array["6"] =  '<span class="badge badge-pill bg-warning font-weight-light">رد شده</span>';
                    break;
                case 3:
                    $array["6"] =  '<span class="badge badge-pill bg-info font-weight-light">انتظار تایید</span>';
                    break;
            }

            $array["7"] = '<a href="'.asset('').env('PanelPrefix').'/users/'.$user->id.'" class="btn btn-light round btn-sm">جزییات</a>';
            array_push($result["data"], $array);
        endforeach;
        return response()->json($result);
    }
}

