<?php

namespace App\Helpers;

use App\Models\{Menu, Activity, User};
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;

class Helper
{
    public static function checkCoin($id, $row)
    {
        $check = DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        return $check->$row;
    }

    public static function addCoin($id, $amount)
    {
        $check = DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $current = $check->running;
        $new = $current + $amount;
        DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->update(['running' => $new]);
    }

    public static function rdcCoin($id, $amount)
    {
        $check = DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $current = $check->running;
        $new = $current - $amount;
        DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->update(['running' => $new]);
    }

    public static function runCoin($id, $dtfrom, $dtto)
    {
        $check = DB::table('coins')
            ->where('idbranch', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $opening = $check->opening;
        $total_in = DB::table('transactions')
            ->where('transactions.idbranch', '=', $id)
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.idbranch', '=', $id)
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_bonus = DB::table('transactions')
            ->where('transactions.idbranch', '=', $id)
            ->where('transactions.trctype', '=', 'BONUS')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.fee');

        return $opening + $total_out - ($total_in + $total_bonus);
    }

    public static function runCoinAll($dtfrom, $dtto)
    {
        $opening = DB::table('coins')
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->sum('opening');

        $total_in = DB::table('transactions')
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_bonus = DB::table('transactions')
            ->where('transactions.trctype', '=', 'BONUS')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.fee');

        return $opening + $total_out - ($total_in + $total_bonus);
    }

    public static function checkBalance($id, $row)
    {
        $check = DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        return $check->$row;
    }

    public static function addBalance($id, $amount)
    {
        $check = DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $current = $check->running;
        $new = $current + $amount;
        DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->update(['running' => $new]);
    }

    public static function rdcBalance($id, $amount, $fee)
    {
        $check = DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $current = $check->running;
        $new = $current - ($amount + $fee);
        DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->update(['running' => $new]);
    }

    public static function runBalance($id, $dtfrom, $dtto)
    {
        $check = DB::table('balances')
            ->where('idacc', '=', $id)
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $opening = $check->opening;
        $total_in = DB::table('transactions')
            ->where('transactions.idacc', '=', $id)
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.idacc', '=', $id)
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_fee = DB::table('transactions')
            ->where('transactions.idacc', '=', $id)
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.fee');

        return $opening + $total_in - ($total_out + $total_fee);
    }
    public static function runAllBalance()
    {
        $newOpening = 0;
        $checkAccount = DB::table('accounts')
            ->get();
        foreach($checkAccount as $ca){
            $check = DB::table('balances')
                ->where('idacc', '=', $ca->idacc)
                ->whereNull('dtclosing')
                ->where('closing', '=', '0')
                ->latest()
                ->first();
            $opening = $check->opening;
            $newOpening += $opening;
        }
        $total_in = DB::table('transactions')
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.amount');

        $total_fee = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.fee');

        //return $opening + $total_in - ($total_out + $total_fee);
        return $newOpening + $total_in - ($total_out + $total_fee);
    }

    public static function runAllCoin()
    {
        $check = DB::table('coins')
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->latest()
            ->first();
        $opening = $check->opening;
        $total_in = DB::table('transactions')
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.amount');

        $total_bonus = DB::table('transactions')
            ->where('transactions.trctype', '=', 'BONUS')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->sum('transactions.fee');

        return $opening + $total_out - ($total_in + $total_bonus);
    }
    public static function runBlcAll($dtfrom, $dtto)
    {
        $opening = DB::table('balances')
            ->whereNull('dtclosing')
            ->where('closing', '=', '0')
            ->sum('opening');

        $total_in = DB::table('transactions')
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_out = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.amount');

        $total_fee = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.fee');

        return $opening + $total_in - ($total_out + $total_fee);
    }

    public static function sumTrc($type, $dtfrom, $dtto, $status)
    {
        if ($type == 'ALL') {
            $result = DB::table('transactions')
                ->where('transactions.trcstatus', '=', $status)
                ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
                ->sum('transactions.amount');
        } else {
            $result = DB::table('transactions')
                ->where('transactions.trctype', '=', $type)
                ->where('transactions.trcstatus', '=', $status)
                ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
                ->sum('transactions.amount');
        }
        return $result;
    }

    public static function countTrc($type, $dtfrom, $dtto, $status)
    {
        if ($type == 'ALL') {
            $result = DB::table('transactions')
                ->where('transactions.trcstatus', '=', $status)
                ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
                ->count('transactions.idtrc');
        } else {
            $result = DB::table('transactions')
                ->where('transactions.trctype', '=', $type)
                ->where('transactions.trcstatus', '=', $status)
                ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
                ->count('transactions.idtrc');
        }
        return $result;
    }

    public static function getTransfer($id, $type)
    {
        $check = DB::table('transactions')
            ->join('accounts', 'accounts.idacc', '=', 'transactions.idacc')
            ->join('banks', 'banks.idbank', '=', 'accounts.idbank')
            ->where('transactions.note', '=', $id)
            ->where('transactions.dttype', '=', $type)
            ->select('banks.bankcode', 'accounts.accname', 'accounts.accno')
            ->first();
        return $check->bankcode . ' | ' . $check->accname . ' - ' . $check->accno;
    }

    public static function sumTransaction($id, $type, $dtfrom, $dtto, $row)
    {
        $result = DB::table('transactions')
            ->where('transactions.idacc', '=', $id)
            ->where('transactions.dttype', '=', $type)
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->whereBetween(DB::raw('LEFT(transactions.datetrc,10)'), [$dtfrom, $dtto])
            ->sum('transactions.' . $row);
        return $result;
    }

    public static function optRead($option)
    {
        $result = DB::table('settings')
            ->where('optname', '=', $option)
            ->where('isactive', '=', 1)
            ->first();
        return $result->optvalue;
    }

    public static function Activity($page, $id, $description)
    {
        $getMenu = Menu::where('page', $page)->first();
        $idmenu = $getMenu->id;
        Activity::create([
            'idmenu' => $idmenu,
            'iduser' => Auth::user()->iduser,
            'idtrc' => $id,
            'description' => $description
        ]);
        // return back()->withStatus('Data Has Been Saved!');
    }

    public static function getActivity($id)
    {
        $result = @Activity::where('idmenu', $id)
            ->orderBy('id', 'DESC')
            ->first();
        $user = @User::find($result->iduser);
        $menu = @Menu::find($id);
        return '<span style="font-style:italic"><strong>LAST ACTIVITY BY : </strong>' . strtoupper(@$user->username) . '<br /><strong>' . @$result->created_at . '</strong> : ' . @$result->description . ' ' . strtoupper(@$menu->title) . ' ' . @$result->idtrc . '</span>';
    }

    public static function appVersion()
    {
        $result = DB::table('versions')->latest('version')->first();
        return $result->version;
    }

    public static function ucText($text)
    {
        return ucwords(strtolower($text));
    }

    public static function removeSpace($text)
    {
        $text = preg_replace('/[\t\n\r\0\x0B]/', '', $text);
        $text = preg_replace('/([\s])\1+/', ' ', $text);
        $text = trim($text);
        return $text;
    }

    public static function textSlug($result)
    {
        $result = strip_tags($result);
        $result = preg_replace('/&.+?;/', '', $result);
        $result = preg_replace('/\s+/', ' ', $result);
        $result = preg_replace('|%([a-fA-F0-9][a-fA-F0-9])|', ' ', $result);
        $result = preg_replace('|-+|', ' ', $result);
        $result = preg_replace('/&#?[a-z0-9]+;/i', '', $result);
        $result = preg_replace('/[^%A-Za-z0-9 _-]/', ' ', $result);
        $result = trim($result, ' ');
        $result = strtolower(str_replace(' ', '-', $result));
        return $result;
    }

    public static function cutText($word, $length)
    {
        $content = ($word);
        $content = substr($content, 0, $length);
        //$content = substr($content,0,strrpos($content," "));
        return $content . '...';
    }

    public static function cleanText($result)
    {
        $result = strip_tags($result);
        $result = preg_replace('/&.+?;/', '', $result);
        $result = preg_replace('/\s+/', ' ', $result);
        $result = preg_replace('|%([a-fA-F0-9][a-fA-F0-9])|', ' ', $result);
        $result = preg_replace('|-+|', ' ', $result);
        $result = preg_replace('/&#?[a-z0-9]+;/i', '', $result);
        $result = preg_replace('/[^%A-Za-z0-9 _-]/', '', $result);
        $result = trim($result, '');
        $result = strtolower($result);
        return $result;
    }

    public static function hintText($text)
    {
        return substr($text, 0, -5) . 'xxxx';
    }

    public static function quickFilter($type)
    {
        switch ($type) {
            case 'today':
                return "'" . date("Y-m-d", strtotime("now")) . "'" . ",'" . date("Y-m-d", strtotime("now")) . "'";
                break;

            case 'yesterday':
                return "'" . date("Y-m-d", strtotime("-1 day")) . "'" . ",'" . date("Y-m-d", strtotime("-1 day")) . "'";
                break;

            case 'thisweek':
                return "'" . date('Y-m-d', strtotime('Monday this week')) . "'" . ",'" . date('Y-m-d', strtotime('Sunday this week')) . "'";
                break;

            case 'lastweek':
                return "'" . date('Y-m-d', strtotime('Monday last week')) . "'" . ",'" . date('Y-m-d', strtotime('Sunday last week')) . "'";
                break;

            case 'thismonth':
                return "'" . date('Y-m-d', strtotime('first day of this month')) . "'" . ",'" . date('Y-m-d', strtotime('last day of this month')) . "'";
                break;

            case 'lastmonth':
                return "'" . date('Y-m-d', strtotime('first day of last month')) . "'" . ",'" . date('Y-m-d', strtotime('last day of last month')) . "'";
                break;
        }
    }

    public static function latesDeposit()
    {
        $list = DB::table('transactions')
            ->where('transactions.dttype', '=', 'DB')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->orderBy('idtrc', 'desc')
            ->take(5)
            ->get();


        return $list;
    }

    public static function latesWithdrawl()
    {
        $list = DB::table('transactions')
            ->where('transactions.dttype', '=', 'CR')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->where('transactions.trcstatus', '=', 'CONFIRM')
            ->orderBy('idtrc', 'desc')
            ->take(5)
            ->get();

        return $list;
    }

    public static function showMemberDetail($id)
    {
        $member = DB::table('members')
            ->join('branches', 'members.idbranch', '=', 'branches.idbranch')
            ->join('banks', 'members.idbank', '=', 'banks.idbank')
            ->select('members.idmember', 'branches.branchname', 'banks.bankcode', 'members.username', 'members.fullname', 'members.email', 'members.phone', 'members.mbraccname', 'members.mbraccno', 'members.level', 'members.remark', 'members.isactive', 'members.created_at', 'members.updated_at')
            ->where('members.idmember', '=', $id)
            ->first();
        
        $showDetail = $member->mbraccname .' - '. $member->bankcode;

        return $showDetail;
    }

        

    public static function todayDeposit()
    {
        $total_in = DB::table('transactions')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->whereDate('transactions.datetrc', '=', Carbon::today())
            ->sum('transactions.amount');

        return $total_in;
    }

    public static function todayByStatusDeposit($status)
    {
        $total_in = DB::table('transactions')
            ->where('transactions.trctype', '=', 'DEPOSIT')
            ->where('transactions.trcstatus', '=', $status)
            ->whereDate('transactions.datetrc', '=', Carbon::today())
            ->sum('transactions.amount');
        
        return $total_in;
    }

    public static function todayByStatusWithdrawl($status)
    {
        $total_in = DB::table('transactions')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->where('transactions.trcstatus', '=', $status)
            ->whereDate('transactions.datetrc', '=', Carbon::today())
            ->sum('transactions.amount');
        
        return $total_in;
    }

    public static function todayWithdrawl()
    {
        $total_in = DB::table('transactions')
            ->where('transactions.trctype', '=', 'WITHDRAWAL')
            ->whereDate('transactions.datetrc', '=', Carbon::today())
            ->sum('transactions.amount');


        return $total_in;
    }

    public static function countPlayer($today)
    {
        $total_player = DB::table('members')
            ->count();

        if($today == 'TODAY'){
            $total_player = DB::table('members')
            ->whereDate('members.created_at', '=', Carbon::today())
            ->count();
        }
        return $total_player;
    }
    
    
}
