<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\ItemModel;
use App\LoanModel;
use DB;
use App\LoanProcessModel;
use App\ConstructionModel\ConstructorModel;

class DashboardSuperAdminController extends Controller
{
    public function index()
    {
        if (empty(\Branch::branch_key())) {
            $branch_id = '""';
        }

        $cash_on_hand = DB::select("SELECT COALESCE(SUM(journal_debit),0) - COALESCE(SUM(journal_credit),0) AS total
                                    FROM nso007_journal
                                    WHERE journal_acccode = 11001 AND journal_status = 1 AND branch_id ='" . \Branch::branch_id() . "'");
        $cash_in_bank = DB::select("SELECT COALESCE(SUM(journal_debit),0) - COALESCE(SUM(journal_credit),0) AS total
                                    FROM nso007_journal
                                    WHERE journal_acccode IN (11012,11002) AND journal_status = 1 AND branch_id ='" . \Branch::branch_id() . "'");
        $income = DB::select("SELECT COALESCE(SUM(journal_credit),0) AS total
                                    FROM nso007_journal
                                    WHERE journal_acccode like '4%' AND journal_type <> 13 AND branch_id ='" . \Branch::branch_id() . "'");
        $expense = DB::select("SELECT COALESCE(SUM(journal_debit),0) AS total
                                    FROM nso007_journal
                                    WHERE journal_acccode like '5%' AND journal_type <> 13 AND branch_id ='" . \Branch::branch_id() . "'");


        $collect = DB::select("SELECT SUM(loanpaid_principle) AS total FROM nso007_loan_paid
                                    INNER JOIN nso007_loan_process ON nso007_loan_paid.loanpaid_processid = nso007_loan_process.id
                                    INNER JOIN nso007_loan ON nso007_loan.id = nso007_loan_process.loanprocess_loanid
                                    INNER JOIN nso007_sale_stock ON nso007_loan.loan_saleid = nso007_sale_stock.id
                                    INNER JOIN nso007_sale_detail_stock ON nso007_sale_detail_stock.saledetail_saleid = nso007_sale_stock.id
                                    INNER JOIN nso007_item_stock ON nso007_item_stock.id = nso007_sale_detail_stock.saledetail_itemid
                                    INNER JOIN nso007_project_stock ON nso007_project_stock.id = nso007_item_stock.item_projectid
                                    WHERE nso007_project_stock.branch_id ='" . \Branch::branch_id() . "'
                              ");
        $libility = DB::select("SELECT COALESCE(SUM(journal_credit),0) - COALESCE(SUM(journal_debit),0) AS total 
                                FROM nso007_journal 
                                WHERE journal_acccode like '2%' and journal_type <> 222 AND branch_id ='" . \Branch::branch_id() . "'");
        $completed = DB::select("SELECT SUM(loan_amount) AS total, 
                SUM(sale_discount) AS discount, 
                SUM(sale_receive) AS receive
                FROM nso007_loan
                INNER JOIN nso007_sale_stock ON nso007_loan.loan_saleid = nso007_sale_stock.id
                INNER JOIN nso007_sale_detail_stock ON nso007_sale_detail_stock.saledetail_saleid = nso007_sale_stock.id
                INNER JOIN nso007_item_stock ON nso007_item_stock.id = nso007_sale_detail_stock.saledetail_itemid
                INNER JOIN nso007_project_stock ON nso007_project_stock.id = nso007_item_stock.item_projectid
                WHERE loan_status=2 and nso007_project_stock.branch_id ='" . \Branch::branch_id() . "'
                ");

        $collection = DB::select("SELECT SUM(loan_amountos) AS total 
                                    FROM nso007_loan
                                    INNER JOIN nso007_sale_stock ON nso007_loan.loan_saleid = nso007_sale_stock.id
                                    INNER JOIN nso007_sale_detail_stock ON nso007_sale_detail_stock.saledetail_saleid = nso007_sale_stock.id
                                    INNER JOIN nso007_item_stock ON nso007_item_stock.id = nso007_sale_detail_stock.saledetail_itemid
                                    INNER JOIN nso007_project_stock ON nso007_project_stock.id = nso007_item_stock.item_projectid
                                    WHERE nso007_project_stock.branch_id ='" . \Branch::branch_id() . "'
                                  ");
        $data_pur = DB::table('nso007_construction_purchase_details')
                                  ->join('nso007_construction_purchase','nso007_construction_purchase.id','nso007_construction_purchase_details.purchase_id')
                                  ->join('nso007_project_stock','nso007_project_stock.id','nso007_construction_purchase.project_id')
                                  ->leftJoin('nso007_boq_construction','nso007_boq_construction.id','nso007_construction_purchase.boq_id')
                                  ->join('nso007_construction_suppliers','nso007_construction_suppliers.id','nso007_construction_purchase.supplier_id')
                                  ->join('nso007_construction_product','nso007_construction_product.id','nso007_construction_purchase_details.product_id')
                                  ->join("nso007_unit_construction","nso007_unit_construction.id","nso007_construction_product.unit_id")
                                  ->join("nso007_product_categories_construction",'nso007_product_categories_construction.id',"nso007_construction_product.category_id")
                                  ->join("nso007_product_brand_construction",'nso007_product_brand_construction.id',"nso007_construction_product.brand_id")
                                  ->select("nso007_construction_purchase.*",
                                  "nso007_construction_purchase_details.qty as product_qty",
                                  "nso007_construction_purchase_details.price as product_price",
                                  "nso007_construction_purchase_details.purpose_and_location",
                                  "nso007_project_stock.project_name","nso007_boq_construction.title as boq_title",
                                  "nso007_construction_suppliers.name as supplier_name",
                                  "nso007_construction_suppliers.phone as supplier_phone",
                                  "nso007_construction_product.name as product_name",
                                  "nso007_unit_construction.name as unit_name",
                                  "nso007_product_categories_construction.name as category_name",
                                  "nso007_product_brand_construction.name as sub_category"
                              )
                              ->when(empty(\Branch::branch_key()), function ($query) {
                                  $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                              })
                              ->where(function ($query){
                                  $query->where("nso007_construction_purchase.status",0);
                                  $query->orWhere("nso007_construction_purchase.status",5);
                                  $query->orWhere("nso007_construction_purchase.status",6);
                              })
                              ->get();
        $amount_purchase = 0;
        if(!empty($data_pur)){
            foreach($data_pur as $key=>$value){
                if(!empty($value->product_qty)&&!empty($value->product_price)){
                    $amount_purchase += $value->product_qty*$value->product_price;
                }
            }
        }

        $constructor = ConstructorModel::join("nso007_project_stock","nso007_project_stock.id","nso007_constructor_construction.project_id")
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->get();

        $contract_paid = DB::table('nso007_construction_table')
            ->join("nso007_task_working_group_construction","nso007_task_working_group_construction.id","nso007_construction_table.work_group_type_id")
            ->join("nso007_project_stock","nso007_project_stock.id","nso007_construction_table.project_id")
            ->join("nso007_constructor_construction","nso007_constructor_construction.id","nso007_construction_table.contractor_id")
            ->join("nso007_zone_construction","nso007_zone_construction.id","nso007_construction_table.zone_name")
            ->select(
                DB::raw("SUM(nso007_construction_table.total_paid_amount) as paid_amount")
            )
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('branch_id', \Branch::branch_id());
            })
            ->first();
        

        $data["cash_on_hand"] = $cash_on_hand[0]->total;
        $data["cash_in_bank"] = $cash_in_bank[0]->total;
        $data["income"] = $income[0]->total;
        $data["expense"] = $expense[0]->total;
        $data["collect"] = $collect[0]->total;
        $data["libility"] = $libility[0]->total;
        $data["completed"] = $completed[0]->total;
        $data["collection"] = $collection[0]->total;
        $data['amount_purchase'] = $amount_purchase;
        $data['constructor'] = !empty($constructor)?count($constructor):"0";
        $data['contract_paid'] = !empty($contract_paid)?("$".number_format($contract_paid->paid_amount,2)):0;
        $months = DB::table('nso007_loan_process')
            ->leftjoin('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->select(DB::raw('YEAR(loanprocess_datepayment) year, MONTH(loanprocess_datepayment) month'), DB::raw('SUM(loanprocess_owe) As amount'))
            ->where([['branch_id', \Branch::branch_id()]])
            ->where(function ($query) {
                $query->whereYear('loanprocess_datepayment', '>=', date('Y'));
                $query->whereMonth('loanprocess_datepayment', '>=', date('m'));
            })
            ->groupBy('year', 'month')->limit(12)->get();
        $graph = [];
        $index = 1;
        // dd($months);
        foreach ($months as $key => $month) {
            $xy = array(
                'label' => date('F Y', strtotime($month->year . "-" . $month->month)),
                'y' => $month->amount,
                'indexLabel'    => "$ " . number_format($month->amount, 2)
            );
            array_push($graph, $xy);
        }
        return view('administrator.dashboard-super-admin', compact('data', 'graph'));
    }
    public function search(Request $request)
    {
        $start  = $request->start;
        $end    = $request->end;
        $months = DB::table('nso007_loan_process')->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->select(DB::raw('YEAR(loanprocess_datepayment) year, MONTH(loanprocess_datepayment) month'), DB::raw('SUM(loanprocess_owe) As amount'))
            ->where([['branch_id', \Branch::branch_id()]])
            ->when($start, function ($query) use ($start, $end) {
                $query->whereBetween('loanprocess_datepayment', [date('Y-m-d', strtotime($start)), date('Y-m-d', strtotime($end))]);
            })
            ->groupBy('year', 'month')->limit(12)->get();
        $graph = [];
        $index = 1;
        foreach ($months as $key => $month) {
            $xy = array(
                'label' => date('F Y', strtotime($month->year . "-" . $month->month)),
                'y' => $month->amount,
                'indexLabel'    => "$ " . number_format($month->amount, 2)
            );
            array_push($graph, $xy);
        }
        echo json_encode($graph, JSON_NUMERIC_CHECK);
    }
    public function operationalData()
    {
        $data = [];

        $data['client'] = LoanModel::when(\Branch::branch_id() != 1, function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })->groupBy('loan_clientid')->get()->count();
        $data['deposit'] = DB::table('nso007_sale_stock as sale')
            ->join('nso007_sale_detail_stock as sale_detail', 'sale_detail.saledetail_saleid', 'sale.id')
            ->join('nso007_item_stock as item', 'item.id', 'sale_detail.saledetail_itemid')
            ->join('nso007_project_stock as project', 'project.id', 'item.item_projectid')
            ->when(\Branch::branch_id() != 1, function ($query) {
                $query->where('project.branch_id', \Branch::branch_id());
            })
            ->groupBy('sale.id')->get()->count();
        $data['contract'] = LoanModel::when(\Branch::branch_id() != 1, function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })->groupBy('id')->get()->count();
        $data['item']   = ItemModel::join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->when(\Branch::branch_id() != 1, function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })->groupBy('nso007_item_stock.id')->get()->count();
        $collection = DB::table('nso007_loan_process as process')
            ->join('nso007_loan as loan', 'loan.id', 'process.loanprocess_loanid')
            ->where([['loan.branch_id', \Branch::branch_id()], ['loanprocess_datepayment', date('Y-m-d')]])
            ->select(DB::raw('count(process.id) as qty'), DB::raw('SUM(process.loanprocess_owepay) as amount'))->groupBy('loan.id')->get();
        $collected = DB::table('nso007_loan_process as process')
            ->join('nso007_loan as loan', 'loan.id', 'process.loanprocess_loanid')
            ->where([['loan.branch_id', \Branch::branch_id()], ['process.loanprocess_datepayment', date('Y-m-d')], ['process.loanprocess_paid', '<>', null]])
            ->select(DB::raw('count(process.id) as qty'), DB::raw('SUM(process.loanprocess_owepay) as amount'))->groupBy('loan.id')->get();
        $arrear = DB::table('nso007_loan_process as process')
            ->join('nso007_loan as loan', 'loan.id', 'process.loanprocess_loanid')
            ->where([['loan.branch_id', \Branch::branch_id()], ['process.loanprocess_datepayment', '<', date('Y-m-d')], ['process.loanprocess_paid', null]])
            ->select(DB::raw('count(process.id) as qty'), DB::raw('SUM(process.loanprocess_owepay) as amount'))->groupBy('loan.id')->get();
        $data['collection_amount']  = 0;
        $data['collection_qty']     = 0;
        $data['collected_amount']   = 0;
        $data['collected_qty']      = 0;
        $data['arrear_amount']      = 0;
        $data['arrear_qty']         = 0;
        $data['available_item']     = DB::table('nso007_item_stock as stock')
            ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
            ->when(\Branch::branch_id() != 1, function ($query) {
                $query->where('project.branch_id', \Branch::branch_id());
            })
            ->where('available', 1)
            ->get()->count();
        $data['sold_item']          = DB::table('nso007_item_stock as stock')
            ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
            ->when(\Branch::branch_id() != 1, function ($query) {
                $query->where('project.branch_id', \Branch::branch_id());
            })
            ->where('available', 0)
            ->get()->count();
        $data['block_item']          = DB::table('nso007_item_stock as stock')
            ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
            ->when(\Branch::branch_id() != 1, function ($query) {
                $query->where('project.branch_id', \Branch::branch_id());
            })
            ->where([['available', 0],['block_status',2]])
            ->get()->count();
        foreach ($collection as $tion) {
            $data['collection_amount'] += $tion->amount;
        }
        foreach ($collected as $tion) {
            $data['collected_amount'] += $tion->amount;
        }
        foreach ($arrear as $tion) {
            $data['arrear_amount'] += $tion->amount;
        }
        $data['collection_qty'] = count($collection);
        $data['collected_qty'] = count($collected);
        $data['arrear_qty'] = count($arrear);
        echo json_encode($data);
    }
}
