<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

use App\User;
use App\SearchModel;
use Auth;
use DB;
use DataTables;

class CashOnHandController extends Controller
{

    function __construct()
    {
        $this->middleware('permission:CashOnHand-List');
    }
    public function report()
    {
        return view('administrator.accounting.cash-on-hand.report');
    }

    public function get_report_cash_on_hand_list(Request $request)
    {
        $start_date =  (empty($request->start_date)) ? '' : date('Y-m-d', strtotime($request->start_date));
        $end_date   =  (empty($request->end_date)) ? '' : date('Y-m-d', strtotime($request->end_date));
        $search = $request->input('search.value');
        $count_total = DB::table('nso007_journal')
        ->when(\Branch::branch_id()!=1,function($q){
            $q->where('nso007_journal.branch_id', \Branch::branch_id());
        })
            ->whereIn('nso007_journal.journal_acccode', [11001, 11002, 11012])
            ->where('nso007_journal.journal_status', 1)
            ->count();
        $count_filter = DB::table('nso007_journal')
            // ->join('nso007_bank_agency', 'nso007_bank_agency.id', 'nso007_journal.journal_type')
            ->join('users', 'users.id', 'nso007_journal.journal_by')
            // ->join('users as user_approve', 'user_approve.id', 'nso007_journal.journal_by_approve')
            ->when(\Branch::branch_id()!=1,function($q){
                $q->where('nso007_journal.branch_id', \Branch::branch_id());
            })
            ->whereIn('nso007_journal.journal_acccode', [11001, 11002, 11012])
            ->where('nso007_journal.journal_status', 1)
            ->where(function ($query) use ($search) {
                $query->orWhere('nso007_journal.journal_paydate', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_invoice', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_debit', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_credit', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_des', 'LIKE', '%' . $search . '%');
            })
            ->count();
        $cash_on_hand = DB::table('nso007_journal')
                ->join('nso007_account_chart', 'nso007_account_chart.id', 'nso007_journal.journal_acccode')
                ->join('users', 'users.id', 'nso007_journal.journal_by')
                // ->join('users as user_approve', 'user_approve.id', 'nso007_journal.journal_by_approve')
                // ->join('nso007_bank_agency', 'nso007_bank_agency.id', 'nso007_journal.journal_type')
                ->select(
                    // 'nso007_bank_agency.name as acc_name',
                    'nso007_journal.journal_acccode',
                    'nso007_journal.id',
                    'nso007_journal.journal_paydate',
                    'nso007_journal.journal_invoice AS invoice',
                    'nso007_journal.journal_debit AS cash_in',
                    'nso007_journal.journal_credit AS cash_out',
                    DB::raw("IFNULL(nso007_journal.journal_des, 'N/A') AS des"),
                    DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by"),
                    // DB::raw("CONCAT(user_approve.user_firstname,' ',user_approve.user_lastname) AS user_approve")
                )
                ->when(\Branch::branch_id()!=1,function($q){
                    $q->where('nso007_journal.branch_id', \Branch::branch_id());
                })
                ->whereIn('nso007_journal.journal_acccode', [11001, 11002, 11012])
                ->where('nso007_journal.journal_status', 1)
                ->when(!empty($start_date) || !empty($end_date),function($q) use($start_date,$end_date){
                    if (!empty($start_date) && !empty($end_date)) {
                        $q->whereBetween(DB::raw("(STR_TO_DATE(journal_paydate,'%Y-%m-%d'))"), [date('Y-m-d', strtotime($start_date)), date('Y-m-d', strtotime($end_date))]);
                    } elseif (!empty($start_date) && empty($end_date)) {
                        $q->where(DB::raw("(STR_TO_DATE(journal_paydate,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                    } else {
                        $q->where(DB::raw("(STR_TO_DATE(journal_paydate,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                    }
                })
                ->orderBy('nso007_journal.id', 'asc')
                ->get();
        return  Datatables::of($cash_on_hand)
            ->with([
                "recordsTotal" => $count_total,
                "recordsFiltered" => $count_filter,
            ])
            ->addIndexColumn()
            ->addColumn('cash_in', function ($cash_on_hand) {
                return '$' . number_format($cash_on_hand->cash_in, 2);
            })
            ->addColumn('cash_out', function ($cash_on_hand) {
                return '$' . number_format($cash_on_hand->cash_out, 2);
            })
            ->addColumn('journal_paydate', function ($cash_on_hand) {
                return date('d-m-Y', strtotime($cash_on_hand->journal_paydate));
            })
            ->make(true);
    }

    public function report_print(Request $request)
    {
        /*OLD ONE*/
        /*$start_date =  (empty($request->start_date))? '': date('Y-m-d',strtotime($request->start_date));
        $end_date   =  (empty($request->end_date))? '': date('Y-m-d',strtotime($request->end_date));

        $mdoel_searching = new SearchModel();
        $journal_date =  (empty($start_date))? '':' nso007_journal.journal_paydate BETWEEN "'.$start_date.'" AND "'.$end_date.'" ';
        $journal_acccode =  (empty($request->journal_acccode))? '':' nso007_journal.journal_acccode = "'.$request->journal_acccode.'" ';

        $data_search = array(
            'journal_date' =>$journal_date,
            'journal_acccode' =>$journal_acccode,
        );
        $search= $mdoel_searching->search($data_search);
        $search = (empty($search))? " WHERE nso007_journal.journal_acccode = 10000 AND journal_status = 1  ":" " .$search. "  AND nso007_journal.journal_acccode = 10000 AND journal_status = 1";

        $cash_on_hand = DB::select("
            SELECT
            nso007_journal.id,
            nso007_journal.journal_paydate AS do_date,
            nso007_journal.journal_invoice AS invoice,
            IFNULL(nso007_journal.journal_des, 'N/A') AS des,
            nso007_journaltype.journaltype_name AS journal_type,
            CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by,
            nso007_journal.journal_debit AS cash_in,
            nso007_journal.journal_credit AS cash_out,
            nso007_journal.journal_paydate
            FROM nso007_journal
            INNER JOIN nso007_account_chart ON nso007_journal.journal_acccode = nso007_account_chart.id
            INNER JOIN users ON nso007_journal.journal_by = users.id
            INNER JOIN nso007_journaltype ON nso007_journal.journal_type = nso007_journaltype.id
            $search 
            ORDER BY nso007_journal.id ASC");*/
        /*End*/
        /*New One*/
        $start_date =  (empty($request->start_date)) ? '' : date('Y-m-d', strtotime($request->start_date));
        $end_date   =  (empty($request->end_date)) ? '' : date('Y-m-d', strtotime($request->end_date));

        $cash_on_hand = DB::table('nso007_journal')
            ->join('nso007_account_chart', 'nso007_account_chart.id', 'nso007_journal.journal_acccode')
            ->join('users', 'users.id', 'nso007_journal.journal_by')
            ->join('nso007_journaltype', 'nso007_journaltype.id', 'nso007_journal.journal_type')
            ->select(
                'nso007_account_chart.acc_name',
                'nso007_journal.id',
                'nso007_journal.journal_paydate',
                'nso007_journal.journal_invoice AS invoice',
                'nso007_journaltype.journaltype_name AS journal_type',
                'nso007_journal.journal_debit AS cash_in',
                'nso007_journal.journal_credit AS cash_out',
                DB::raw("IFNULL(nso007_journal.journal_des, 'N/A') AS des"),
                DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by")
            )
            ->where('nso007_journal.branch_id', \Branch::branch_id())
            ->whereIn('nso007_journal.journal_acccode', [11001, 11002, 11012])
            ->where('nso007_journal.journal_status', 1)
            ->whereBetween('nso007_journal.journal_paydate', [$start_date, $end_date])
            ->orderBy('nso007_journal.id', 'asc')
            ->get();
        /*End*/

        $cashIN = 0;
        $cashOut = 0;
        foreach ($cash_on_hand as $key => $value) {
            $cashIN += $value->cash_in;
            $cashOut += $value->cash_out;
        }
        $total = $cashIN - $cashOut;
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        // dd($cash_on_hand);

        return view('administrator.accounting.cash-on-hand.report-view-print', compact('cash_on_hand', 'cashIN', 'cashOut', 'total', 'branch'));
    }

    public function report_cash_flow()
    {
        return view('administrator.accounting.cash-flow.report');
    }

    public function get_report_cash_flow_list(Request $request)
    {
        $search = $request->input('search.value');
        $count_total = DB::table('nso007_journal')
            ->where('nso007_journal.branch_id', \Branch::branch_id())
            ->where('nso007_journal.journal_acccode', 'LIKE', '10%')
            ->where('nso007_journal.journal_status', 1)
            ->count();
        $count_filter = DB::table('nso007_journal')
            ->join('nso007_account_chart', 'nso007_account_chart.id', 'nso007_journal.journal_acccode')
            ->join('users', 'users.id', 'nso007_journal.journal_by')
            ->join('nso007_journaltype', 'nso007_journaltype.id', 'nso007_journal.journal_type')
            ->where('nso007_journal.branch_id', \Branch::branch_id())
            ->where('nso007_journal.journal_acccode', 'LIKE', '10%')
            ->where('nso007_journal.journal_status', 1)
            ->where(function ($query) use ($search) {
                $query->orWhere('nso007_journal.journal_paydate', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_invoice', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journaltype.journaltype_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_debit', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_credit', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_journal.journal_des', 'LIKE', '%' . $search . '%');
            })
            ->count();


        $cash_on_hand = DB::table('nso007_journal')
            ->join('nso007_account_chart', 'nso007_account_chart.id', 'nso007_journal.journal_acccode')
            ->join('users', 'users.id', 'nso007_journal.journal_by')
            ->join('nso007_journaltype', 'nso007_journaltype.id', 'nso007_journal.journal_type')
            ->select(
                'nso007_journal.id',
                'nso007_journal.journal_paydate',
                'nso007_journal.journal_invoice AS invoice',
                'nso007_journaltype.journaltype_name AS journal_type',
                'nso007_journal.journal_debit AS cash_in',
                'nso007_journal.journal_credit AS cash_out',
                DB::raw("IFNULL(nso007_journal.journal_des, 'N/A') AS des"),
                DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by")
            )
            ->where('nso007_journal.branch_id', \Branch::branch_id())
            ->where('nso007_journal.journal_acccode', 'LIKE', '10%')
            ->where('nso007_journal.journal_status', 1)
            ->orderBy('nso007_journal.id', 'asc')
            ->take(20);



        if ($request->isMethod('get')) {
            return  Datatables::of($cash_on_hand)
                ->with([
                    "recordsTotal" => $count_total,
                    "recordsFiltered" => $count_filter,
                ])
                ->addColumn('cash_in', function ($cash_on_hand) {
                    return '$' . number_format($cash_on_hand->cash_in, 2);
                })
                ->addColumn('cash_out', function ($cash_on_hand) {
                    return '$' . number_format($cash_on_hand->cash_out, 2);
                })
                ->addColumn('journal_paydate', function ($cash_on_hand) {
                    return date('Y-m-d', strtotime($cash_on_hand->journal_paydate));
                })
                ->addIndexColumn()
                ->make(true);
        } else {
            $start_date =  (empty($request->start_date)) ? '' : date('Y-m-d', strtotime($request->start_date));
            $end_date   =  (empty($request->end_date)) ? '' : date('Y-m-d', strtotime($request->end_date));
            $status = $request->status;
            $cash_on_hand = DB::table('nso007_journal')
                ->join('nso007_account_chart', 'nso007_account_chart.id', 'nso007_journal.journal_acccode')
                ->join('users', 'users.id', 'nso007_journal.journal_by')
                ->join('nso007_journaltype', 'nso007_journaltype.id', 'nso007_journal.journal_type')
                ->select(
                    'nso007_journal.id',
                    'nso007_journal.journal_paydate',
                    'nso007_journal.journal_invoice AS invoice',
                    'nso007_journaltype.journaltype_name AS journal_type',
                    'nso007_journal.journal_debit AS cash_in',
                    'nso007_journal.journal_credit AS cash_out',
                    DB::raw("IFNULL(nso007_journal.journal_des, 'N/A') AS des"),
                    DB::raw("CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by")
                )
                ->where('nso007_journal.branch_id', \Branch::branch_id())
                ->when(request('status'), function ($query) use ($status) {
                    if ($status == 1) {
                        $query->where('nso007_journal.journal_acccode', 10000);
                        $query->where('nso007_journal.journal_status', 1);
                    } else if ($status == 2) {
                        $query->where('nso007_journal.journal_acccode', 10001);
                        $query->where('nso007_journal.journal_status', 1);
                    }
                })
                ->when(request('form_date', ''), function ($query) use ($start_date, $end_date) {
                    $query->whereBetween('nso007_journal.journal_paydate', [$start_date, $end_date]);
                })
                ->orderBy('nso007_journal.id', 'asc')
                ->get();
            echo json_encode($cash_on_hand);
        }
    }

    public function report_cash_flow_print(Request $request)
    {
        $start_date =  (empty($request->start_date)) ? '' : date('Y-m-d', strtotime($request->start_date));
        $end_date   =  (empty($request->end_date)) ? '' : date('Y-m-d', strtotime($request->end_date));

        $mdoel_searching = new SearchModel();
        $journal_date =  (empty($start_date)) ? '' : ' nso007_journal.journal_paydate BETWEEN "' . $start_date . '" AND "' . $end_date . '" ';
        $title = '';
        $journal_acccode = '';
        if ($request->status == 1) {
            $title = 'របាយការណ៍សាច់ប្រាក់ក្នុងដៃ';
            $journal_acccode =  'nso007_journal.journal_acccode = 10000 AND journal_status = 1';
        } else if ($request->status == 2) {
            $journal_acccode =  'nso007_journal.journal_acccode = 10001 AND journal_status = 1';
            $title = 'របាយការណ៍សាច់ប្រាក់ក្នុងធនាគា';
        } else {
            $title = 'របាយការណ៍សរុបសាច់ប្រាក់ក្នុងធនាគា និង សាច់ប្រាក់ក្នុងដៃ';
        }

        $data_search = array(
            'journal_date' => $journal_date,
            'journal_acccode' => $journal_acccode,
        );
        $search = $mdoel_searching->search($data_search);
        $search = (empty($search)) ? " WHERE nso007_journal.branch_id = " . \Branch::branch_id() . " AND (nso007_journal.journal_acccode = 10000 OR nso007_journal.journal_acccode = 10001) AND journal_status = 1  " : " " . $search . "AND nso007_journal.branch_id = '" . \Branch::branch_id() . "'  ";

        $cash_flow = DB::select("
            SELECT
            nso007_journal.id,
            nso007_journal.journal_paydate AS do_date,
            nso007_journal.journal_invoice AS invoice,
            IFNULL(nso007_journal.journal_des, 'N/A') AS des,
            nso007_journaltype.journaltype_name AS journal_type,
            CONCAT(users.user_firstname,' ',users.user_lastname) AS user_by,
            nso007_journal.journal_debit AS cash_in,
            nso007_journal.journal_credit AS cash_out,
            nso007_journal.journal_paydate
            FROM nso007_journal
            INNER JOIN nso007_account_chart ON nso007_journal.journal_acccode = nso007_account_chart.id
            INNER JOIN users ON nso007_journal.journal_by = users.id
            INNER JOIN nso007_journaltype ON nso007_journal.journal_type = nso007_journaltype.id
            $search 
            ORDER BY nso007_journal.id ASC");

        $cashIN = 0;
        $cashOut = 0;
        foreach ($cash_flow as $key => $value) {
            $cashIN += $value->cash_in;
            $cashOut += $value->cash_out;
        }
        $total = $cashIN - $cashOut;
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.accounting.cash-flow.report-view-print', compact('cash_flow', 'cashIN', 'cashOut', 'total', 'branch', 'title'));
    }
}
