<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\ClientModel;
use App\ItemModel;
use App\ProjectModel;
use App\BranchModel;
use App\LoanModel;
use App\LoanProcessModel;
use App\SaleStockDetailModel;
use Auth;
use Illuminate\Support\Carbon;
use DataTables;
use DB;
use Illuminate\Support\Facades\Crypt;

class ReportArrearController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:Arrear-List');
    }
    public function index()
    {
    }
    public function report_arrear()
    {
        $items = ItemModel::select('item_house As item_name', 'id')
            ->where('item_salestatus', 2)
            ->pluck('item_name', 'id')
            ->prepend('Please Select', '');
        $clients = ClientModel::select(DB::raw('CONCAT(id," ",client_fullname) AS client_fullname'), 'id')
            ->pluck('client_fullname', 'id')
            ->prepend('Please Select', '');
        $project = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->prepend('Please Select Option', '')->toArray();
        return view('administrator.payment.arrear.report', compact('items', 'clients', 'project'));
    }
    public function get_report_arrear_list(Request $request)
    {
        $date           = date('Y-m-d');
        $start          = $request->start_date;
        $end            = $request->end_date;
        $project_id     = $request->project_id;
        $client_id      = $request->client_id;
        $client_phone   = $request->client_phone;
        $item_id        = $request->item_id;
        $where_process  = " WHERE loanprocess_datepayment < '". $date ."' AND loanprocess_paid IS NULL ";
        $where_sale     = ' WHERE stock.id IS NOT NULL';
        if(!empty($start) && !empty($end)){
            $where_process .= ' AND (loanprocess_datepayment BETWEEN "'.date('Y-m-d',strtotime($start)).'" AND "'.date('Y-m-d',strtotime($end)).'")';
        }else if(!empty($start) && empty($end)){
            $where_process .= " AND loanprocess_datepayment >= ".date('Y-m-d',strtotime($start))." ";
        }else if(empty($start) && !empty($end)){
            $where_process .= " AND loanprocess_datepayment <= ".date('Y-m-d',strtotime($end))." ";
        }

        if(!empty($project_id)){
            $where_sale .= " AND project.id = ".$project_id."";
        }
        if(!empty($item_id)){
            $where_sale .= " AND stock.id = ".$item_id."";
        }
        // dd($where_process,$where_sale);
        $arrear = DB::table('nso007_loan as loan')
            ->join(DB::raw('(SELECT SUM(loanprocess_principle) AS loan_principle,SUM(loanprocess_interest) AS loan_interest,TIMESTAMPDIFF(DAY,loanprocess_datepayment,"' . $date . '") AS day_late, SUM(loanprocess_owepay) AS loan_pay,
                         loanprocess_loanid as loan_id,loanprocess_datepayment,group_concat(id) As process_id FROM nso007_loan_process '.$where_process.' GROUP BY loanprocess_loanid ORDER BY loanprocess_datepayment asc) AS process'), function ($join) {
                $join->on('loan.id', '=', 'process.loan_id');
            })
            ->join(
                DB::raw('(SELECT sale_detail.saledetail_saleid AS id,GROUP_CONCAT(stock.item_house) AS item_house, project.project_name
                         FROM nso007_sale_detail_stock as sale_detail JOIN nso007_item_stock as stock ON sale_detail.saledetail_itemid = stock.id JOIN nso007_project_stock as project ON project.id = stock.item_projectid '.$where_sale.' GROUP BY sale_detail.saledetail_saleid) AS sale'),
                function ($j) {
                    $j->on('loan.loan_saleid', '=', 'sale.id');
                }
            )
            ->join('nso007_client as client', 'client.id', '=', 'loan.loan_clientid')
            ->select(
                'process.loan_principle',
                'process.loan_interest',
                'process.loan_pay',
                'loan.id',
                'sale.id as sale_id',
                'process.process_id',
                'sale.project_name',
                'process.day_late',
                'sale.item_house as item_name',
                'loan.loan_amountos',
                'process.loanprocess_datepayment',
                'client.client_fullname',
                'client.client_phone1',
                'loan.loan_disbursement'
            )
            ->when($client_id,function($q,$client_id){
                $q->where('client.id',$client_id);
            })
            ->when($client_phone,function($q,$client_phone){
                $q->where('client.client_phone1','LIKE',"%$client_phone%");
            })
            ->when(empty(\Branch::branch_key()),function($q){
                $q->where('loan.branch_id',\Branch::branch_id());
            })
            
            ->orderBy('loan.id')
            ->get();
            return Datatables::of($arrear)
                ->addColumn('loan_amountos', function ($arrear) {
                    return '$ ' . number_format($arrear->loan_amountos, 2);
                })
                ->addColumn('loan_principle', function ($arrear) {
                    return '$ ' . number_format($arrear->loan_principle, 2);
                })
                ->addColumn('loan_interest', function ($arrear) {
                    return '$ ' . $arrear->loan_interest;
                })
                ->editColumn("loanprocess_datepayment", function ($arrear) {
                    return date("d-m-Y", strtotime($arrear->loanprocess_datepayment));
                })
                ->editColumn("loan_disbursement", function ($arrear) {
                    return date("d-m-Y", strtotime($arrear->loan_disbursement));
                })
                ->editColumn('penalty', function ($arrear) {
                    $penalty = 0;
                    $loanProcessID = explode(',',$arrear->process_id);
                    $loanProcess = LoanProcessModel::whereIn('id',$loanProcessID)->get();
                    if(!empty($loanProcess)){
                        foreach($loanProcess as $process){
                            $penalty += $this->penaltyofPayment($process->id);
                        }
                    }
                    return "$ ".number_format($penalty,4);
                })
                ->editColumn('item_name',function($arrear){
                    return $this->getItemNameBySaleID($arrear->sale_id);
                })
                ->addIndexColumn()
                ->make(true);
    }
    public function getItemNameBySaleID($sale_id){
        return ItemModel::whereIn('id',explode(',',SaleStockDetailModel::where('saledetail_saleid',$sale_id)->select(DB::raw('group_concat(saledetail_itemid) As item_id'))->first()->item_id))->select(DB::Raw('group_concat(item_house) As item_name'))->first()->item_name;
    }
    public function penaltyofPayment($loan_processid)
    {
        $loan_processid = (int) $loan_processid;
        $penalty = LoanProcessModel::select('loanprocess_owepay AS loan_pay', 'loanprocess_datepayment', 'loanprocess_datepaid', 'penalty_paid', DB::raw('TIMESTAMPDIFF(DAY,loanprocess_datepayment,"' . date('Y-m-d') . '") AS day_late'))
            ->where([['id', $loan_processid]])
            ->whereDate('loanprocess_datepayment', "<", Carbon::now())
            ->where(function ($query) {
                $query->whereNull('loanprocess_paid');
                $query->orwhere('loanprocess_paid', 2);
            })
            ->first();
        $total_day      = 0;
        $penaltyAmount  = 0;
        if (!empty($penalty)) {
            if (!empty($penalty->loanprocess_datepaid)) {
                $total_day     = $this->diffdays($penalty->loanprocess_datepaid, date('Y-m-d'));
            } else {
                $total_day = $penalty->day_late - 5;
            }
            if($total_day>0){
                $penaltyAmount = ($penalty->penalty_paid) + ($penalty->loan_pay * 0.05 * $total_day) / 30;
            }else{
                $penaltyAmount = $penalty->penalty_paid;
            }
            
        } else {
            $penaltyAmount = LoanProcessModel::find($loan_processid)->penalty_paid;
        }
        return (float) $penaltyAmount;
    }
    public function report_arrear_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));
        $client_name    = (empty($request->clients)) ? '' : $request->clients;
        $item_name      = (empty($request->item_name)) ? '' : $request->item_name;
        $project_name   = (empty($request->projects)) ? '' : $request->projects;
        $date           = date('Y-m-d');

        $where_process = " WHERE loanprocess_datepayment < '$date' AND loanprocess_paid IS NULL ";
        if (!empty($request->start_date) && !empty($request->end_date)) {
            $where_process = " WHERE loanprocess_datepayment BETWEEN '$start_date' AND '$end_date' AND loanprocess_paid IS NULL ";
        } else if (empty($request->start_date) && !empty($request->end_date)) {
            $where_process = " WHERE loanprocess_datepayment <= '$end_date' AND loanprocess_paid IS NULL ";
        } else if (!empty($request->start_date) && empty($request->end_date)) {
            $where_process = " WHERE loanprocess_datepayment >= '$start_date' AND loanprocess_paid IS NULL ";
        }
        $where_sale = " WHERE stock.item_name IS NOT NULL";
        if (!empty($request->item_name)) {
            $where_sale .= " AND stock.id = '$item_name' ";
        }
        if (!empty($request->projects)) {
            $where_sale .= " AND project.id = '$project_name' ";
        }
        $payment_arrear = DB::table('nso007_loan as loan')
            ->join(DB::raw("(SELECT SUM(loanprocess_principle) AS loanprocess_principle,SUM(loanprocess_interest) AS loanprocess_interest,SUM(TIMESTAMPDIFF(DAY,loanprocess_datepayment,'" . $date . "')) AS day_late,nso007_loan_process.loanprocess_loanid as loan_id,nso007_loan_process.loanprocess_datepayment FROM nso007_loan_process $where_process GROUP BY nso007_loan_process.loanprocess_loanid) AS process"), function ($join) {
                $join->on('loan.id', '=', 'process.loan_id');
            })
            ->join(
                DB::raw("(SELECT sale_detail.saledetail_saleid AS id,GROUP_CONCAT(stock.item_house) AS item_house, project.project_name
        FROM nso007_sale_detail_stock as sale_detail JOIN nso007_item_stock as stock ON sale_detail.saledetail_itemid = stock.id JOIN nso007_project_stock as project ON project.id = stock.item_projectid $where_sale GROUP BY sale_detail.saledetail_saleid) AS sale"),
                function ($j) {
                    $j->on('loan.loan_saleid', '=', 'sale.id');
                }
            )
            ->join('nso007_client as client', 'client.id', '=', 'loan_clientid')
            ->select(
                'process.loanprocess_principle',
                'process.loanprocess_interest',
                'loan.id',
                'sale.project_name',
                'process.day_late',
                'sale.item_house as item_name',
                'loan.loan_amountos',
                'process.loanprocess_datepayment',
                'client.client_fullname',
                'client.client_phone1',
                'loan.loan_disbursement'
            )
            ->when(request('clients', ''), function ($query) use ($client_name) {
                $query->where('client.id', $client_name);
            })
            ->get();



        // DB::table('nso007_loan_process')

        //     ->join('nso007_loan', 'nso007_loan_process.loanprocess_loanid', '=', 'nso007_loan.id')
        //     ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', '=', 'nso007_loan.loan_saleid')
        //     ->join('nso007_item_stock', 'nso007_item_stock.id', '=', 'nso007_sale_detail_stock.saledetail_itemid')
        //     ->join('nso007_project_stock', 'nso007_project_stock.id', '=', 'nso007_item_stock.item_projectid')
        //     ->join('nso007_client', 'nso007_client.id', '=', 'nso007_loan.loan_clientid')
        //     ->select(
        //         'nso007_loan_process.id',
        //         'nso007_loan_process.loanprocess_datepayment',
        //         'nso007_loan_process.loanprocess_interest',
        //         'nso007_loan_process.loanprocess_principle',
        //         'nso007_project_stock.project_name',
        //         'nso007_item_stock.item_name',
        //         'nso007_client.client_fullname',
        //         'nso007_client.client_phone1',
        //         'nso007_loan.loan_disbursement',
        //         'nso007_loan.loan_amountos',
        //         DB::raw('TIMESTAMPDIFF(DAY,loanprocess_datepayment,"'.$date.'") AS day_late')
        //     )
        //     ->when(empty(\Branch::branch_key()), function($query){
        //         $query->where('nso007_project_stock.branch_id',\Branch::branch_id());
        //     })
        //     ->where('nso007_loan_process.loanprocess_des')
        //     ->where('nso007_loan_process.loanprocess_datepayment','<',$date)
        //     ->where(function($query){
        //         $query->orWhere('nso007_loan_process.loanprocess_paid' ,2);
        //         $query->orWhere('nso007_loan_process.loanprocess_paid' );
        //     })
        //     ->when(request('clients', ''), function ($query) use ($client_name){
        //         $query->where('nso007_client.id', $client_name);
        //     })
        //     ->when(request('item_name', ''), function ($query) use ($item_name){
        //         $query->where('nso007_item_stock.id', $item_name);
        //     })
        //     ->when(request('projects', ''), function ($query) use ($project_name){
        //         $query->where('nso007_project_stock.id', $project_name);
        //     })
        //     ->when(request('start_date', ''), function ($query) use ($start_date,$end_date){
        //         $query->whereBetween('nso007_loan_process.loanprocess_datepayment', [$start_date,$end_date]);
        //     })
        //     ->orderBy('id','asc')->get();

        $total_principle = 0;
        $total_interest = 0;
        $total_payment = 0;
        foreach ($payment_arrear as $key => $value) {
            $total_principle += $value->loanprocess_principle;
            $total_interest += $value->loanprocess_interest;
            $total_payment += $value->loan_amountos;
        }
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.arrear.report-view-print', compact('payment_arrear', 'total_principle', 'total_interest', 'total_payment', 'branch'));
    }
}
