<?php

namespace App\Http\Controllers\Administrator;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Crypt;

use App\User;
use App\ItemModel;
use App\CategoryModel;
use App\ProjectModel;
use App\BranchModel;
use App\Classes\AgencyCommission;
use App\JournalModel;
use App\PositionModel;
use App\ClientModel;
use App\CommissionItemModel;
use App\CommissionModel;
use App\FundsRegisterModel;
use App\FundsTransactionModel;
use App\Helpers\Branch;
use App\SearchModel;
use DataTables;
use Auth;
use DB;
use Session;
use App\SaleStockDetailModel;
use App\LoanPaidModel;
use App\SaleStockModel;

/**
 * 
 */
class OperationController extends Controller
{

    function __construct()
    {
        $this->middleware('permission:All-Payment-List', ['only' => ['all_payment_to_collect']]);
        $this->middleware('permission:Payment-Due-List', ['only' => ['payment_collection']]);
        $this->middleware('permission:PaymentCollected-List', ['only' => ['payment_collected']]);
        $this->middleware('permission:InstallmentCompleted-List', ['only' => ['payment_completed']]);
        $this->middleware('permission:Sale-Payment-Summary-List', ['only' => ['payment_client']]);
        $this->middleware('permission:Commission-Group-List', ['only' => ['report_commis']]);
        $this->middleware('permission:Commission-Network-List', ['only' => ['report_com_network_index']]);
        $this->middleware('permission:Canceled-Installment-List', ['only' => ['payment_cancellation']]);
    }

    //////////////////////////// report_all_payment_to_collection ///////////////////////////
    //////////////////////////////////////////////////////////////////////////

    public function report_deposit()
    {

        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->toArray();
        
        return view('administrator.payment.deposit.report',compact('projects', 'categories', 'clients'));
    }
    public function get_data_report_deposit(Request $request)
    {
        $input = $request->all();
        $sale = DB::table('nso007_deposit')
        ->join('nso007_sale_stock as sale','sale.id','nso007_deposit.deposit_saleid')
        ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_saleid', 'sale.id')
        ->join('nso007_item_stock as stock', 'stock.id', 'detail.saledetail_itemid')
        ->join('nso007_category_stock as cate', 'cate.id', 'stock.item_categoryid')
        ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
        ->join('nso007_client as client', 'client.id', 'sale.sale_clientid')
        ->leftjoin('nso007_sale_policy_payment','sale.id','nso007_sale_policy_payment.sale_id')
        ->leftjoin('users', 'users.id', 'sale.agency_id')
        ->leftjoin('nso007_agency_team as team', 'team.id', 'users.agency_team_id')
        ->leftJoin('nso007_loan','nso007_loan.loan_saleid','sale.id')
        ->select(
            'client.id',
            'nso007_deposit.deposit_date',
            'nso007_deposit.deposit_amount',
            DB::raw('concat(client.client_firstname," ",client.client_lastname) As client_fullname'),
            'client.client_phone1',
            'client.client_address',
            'sale.sale_date',
            'sale.sale_deposit',
            'sale.sale_receive',
            'sale.approve_status',
            'sale.sale_datecontract',
            'sale.sale_amount as saledetail_price',
            'stock.item_stockstatus',
            'stock.item_salestatus',
            'sale.sale_discount',
            'stock.item_price',
            'cate.category_name',
            'project.project_name',
            'sale.id as sale_id',
            'sale.status_pos_approve_type',
            'nso007_loan.loan_cancel_status',
            DB::Raw('group_concat(stock.item_house) As item_name'),
            DB::raw('SUM(stock.item_count) As total_item'),
            DB::raw('CONCAT(users.user_firstname," ",user_lastname) As agent'),
            'team.agency_team_name as team_name',
            'project.branch_id',
            'nso007_sale_policy_payment.payment_policy_id',
            'nso007_sale_policy_payment.policy_detail_id'
        )
        ->when(empty(\Branch::branch_key()),function($query){
            return $query->where('project.branch_id', \Branch::branch_id());
        })
        ->where(function($query) use($input){
            if(!empty($input['start_date']&&!empty($input['end_date']))){
                $query->whereBetween("nso007_deposit.deposit_date",[date('Y-m-d',strtotime($input['start_date'])),date('Y-m-d',strtotime($input['end_date']))]);
            }
            if(!empty($input['phone'])){
                $query->where("client.client_phone1",$input['phone']);
            }
            if(!empty($input['project'])){
                $query->whereIn('project.id',$input['project']);
            }
            if(!empty($input['category'])){
                $query->whereIn("cate.id",$input['category']);
            }
            if(!empty($input['client'])){
                $query->whereIn("client.id",$input['client']);
            }
        })
        ->groupBy('nso007_deposit.id')
        ->get();

        return  Datatables::of($sale)
            ->addIndexColumn()
            ->setRowId('id')
            ->setRowData([
                'sale-id' => function($sale) {
                    return $sale->sale_id;
                }
            ])
            ->addColumn('deposit_date',function($sale){
                return date("d-m-Y");
            })
            ->addColumn('sale_receive', function ($sale) {
                return '$' . number_format($sale->deposit_amount, 2);
            })
            ->editColumn('sale_amount',function($sale){
                return '$'.number_format(($sale->saledetail_price-$sale->sale_discount),2);
            })
            ->addColumn('sale_discount', function ($sale) {
                return '$' . number_format($sale->sale_discount, 2);
            })
            ->addColumn('item_price', function ($sale) {
                return '$' . number_format($sale->saledetail_price, 2);
            })
            ->addColumn('sale_date', function ($sale) {
                return date('d-m-Y', strtotime($sale->sale_date));
            })
            ->editColumn('other',function($sale){
                if($sale->loan_cancel_status==2) return "Canceled Installment";
                else {
                    if($sale->sale_deposit==1) return "Deposit";
                    else if($sale->sale_deposit==2) return "Installment";
                    else return "";
                }
            })
            ->addColumn('contract_date', function ($sale) {
                $current_day = date("Y-m-d", time() + 86400);
                $contract_date = date('Y-m-d', strtotime($sale->sale_datecontract));
                if ($current_day == $contract_date) {
                    return '<span class="text text-danger">' . date('d-m-Y', strtotime($contract_date)) . '</span>';
                } else {
                    return date('d-m-Y', strtotime($sale->sale_datecontract));
                }
            })
            ->editColumn('payment_policy',function ($sale)
            {
                if(!empty($sale->policy_detail_id)) return optional(DB::table("nso007_payment_policy_detail")->where("id",$sale->policy_detail_id)->first())->name;
                elseif(!empty($sale->payment_policy_id)) return optional(DB::table("nso007_payment_policy")->where('id',$sale->payment_policy_id)->first())->name;
            })
            ->rawColumns(['contract_date' => 'contract_date'])
            ->make(true);
    }

    public function print_report_deposit(Request $request)
    {
        $input = $request->all();
        $sale = DB::table('nso007_deposit')
        ->join('nso007_sale_stock as sale','sale.id','nso007_deposit.deposit_saleid')
        ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_saleid', 'sale.id')
        ->join('nso007_item_stock as stock', 'stock.id', 'detail.saledetail_itemid')
        ->join('nso007_category_stock as cate', 'cate.id', 'stock.item_categoryid')
        ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
        ->join('nso007_client as client', 'client.id', 'sale.sale_clientid')
        ->leftjoin('nso007_sale_policy_payment','sale.id','nso007_sale_policy_payment.sale_id')
        ->leftjoin('users', 'users.id', 'sale.agency_id')
        ->leftjoin('nso007_agency_team as team', 'team.id', 'users.agency_team_id')
        ->leftJoin('nso007_loan','nso007_loan.loan_saleid','sale.id')
        ->select(
            'client.id',
            'nso007_deposit.deposit_date',
            'nso007_deposit.deposit_amount',
            DB::raw('concat(client.client_firstname," ",client.client_lastname) As client_fullname'),
            'client.client_phone1',
            'client.client_address',
            'sale.sale_date',
            'sale.sale_deposit',
            'sale.sale_receive',
            'sale.approve_status',
            'sale.sale_datecontract',
            'sale.sale_amount as saledetail_price',
            'stock.item_stockstatus',
            'stock.item_salestatus',
            'sale.sale_discount',
            'stock.item_price',
            'cate.category_name',
            'project.project_name',
            'sale.id as sale_id',
            'sale.status_pos_approve_type',
            'nso007_loan.loan_cancel_status',
            DB::Raw('group_concat(stock.item_house) As item_name'),
            DB::raw('SUM(stock.item_count) As total_item'),
            DB::raw('CONCAT(users.user_firstname," ",user_lastname) As agent'),
            'team.agency_team_name as team_name',
            'project.branch_id',
            'nso007_sale_policy_payment.payment_policy_id',
            'nso007_sale_policy_payment.policy_detail_id'
        )
        ->when(empty(\Branch::branch_key()),function($query){
            return $query->where('project.branch_id', \Branch::branch_id());
        })
        ->where(function($query) use($input){
            if(!empty($input['start_date']&&!empty($input['end_date']))){
                $query->whereBetween("nso007_deposit.deposit_date",[date('Y-m-d',strtotime($input['start_date'])),date('Y-m-d',strtotime($input['end_date']))]);
            }
            if(!empty($input['phone'])){
                $query->where("client.client_phone1",$input['phone']);
            }
            if(!empty($input['projects'])){
                $query->whereIn('project.id',$input['projects']);
            }
            if(!empty($input['categories'])){
                $query->whereIn("cate.id",$input['categories']);
            }
            if(!empty($input['clients'])){
                $query->whereIn("client.id",$input['clients']);
            }
        })
        ->groupBy('nso007_deposit.id')
        ->get();

        $total_sale = 0;
        $total_discount = 0;
        $total_sale_amount = 0;
        $total_deposit_amount = 0;
        foreach ($sale as $key => $value) {
            $total_sale += $value->saledetail_price;
            $total_discount += $value->sale_discount;
            $total_sale_amount += $total_sale - $total_discount;
            $total_deposit_amount += $value->deposit_amount;
        }
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.deposit.print_report', compact('sale','total_sale', 'total_discount', 'total_sale_amount', 'total_deposit_amount', 'branch'));
    }

    public function all_payment_to_collect()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->toArray();
        return view('administrator.payment.all-payment-collection.report', compact('projects', 'categories', 'clients'));
    }

    public function report_all_payment_to_collect(Request $request)
    {
        $category_id    = $request->category_id;
        $project_id     = $request->project_id;
        $client_id      = $request->client_id;
        $start_date     = $request->start_date;
        $end_date       = $request->end_date;
        $phone          = $request->phone;
        $search         = $request->input('search.value');
        $counttotal = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('nso007_loan_process.id')
            ->get()->count();
        $countfilter = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->where(function($q) use($search){
                $q->orwhere('nso007_client.client_firstname','LIKE','%'.$search.'%');
                $q->orwhere('nso007_client.client_lastname','LIKE','%'.$search.'%');
                $q->orwhere('nso007_project_stock.project_name','LIKE','%'.$search.'%');
                $q->orwhere('nso007_category_stock.category_name','LIKE','%'.$search.'%');
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('nso007_loan_process.id')
            ->get()->count();

        $payment_all_collection = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_process.id as loan_proid',
                'nso007_loan_process.loanprocess_datepayment AS due_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_process.loanprocess_interest AS interest',
                'nso007_loan_process.loanprocess_principle AS principle',
                'nso007_loan_process.loanprocess_owe AS payment'
            )
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when($category_id, function ($q, $category_id) {
                $q->whereIn('nso007_category_stock.id', $category_id);
            })
            ->when($project_id, function ($q, $project_id) {
                $q->whereIn('nso007_project_stock.id', $project_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->whereIn('nso007_client.id', $client_id);
            })
            ->when($phone, function ($q, $phone) {
                $q->where('nso007_client.client_phone1', 'LIKE', "%" . $phone . "%");
            })
            ->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(nso007_loan_process.loanprocess_datepayment,'%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(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('nso007_loan_process.id')
            ->take(20);
        return  Datatables::of($payment_all_collection)
            ->addIndexColumn()
            ->with([
                "recordsTotal" => $counttotal,
                "recordsFiltered" => $countfilter,
            ])
            ->setRowId('loan_proid')
            ->addColumn('interest', function ($payment_all_collection) {
                return '$ ' . number_format($payment_all_collection->interest, 2);
            })
            ->addColumn('payment', function ($payment_all_collection) {
                return '$ ' . number_format($payment_all_collection->payment, 2);
            })
            ->addColumn('principle', function ($payment_all_collection) {
                return '$ ' . number_format($payment_all_collection->principle, 2);
            })
            ->addColumn('due_date', function ($payment_all_collection) {
                return date('d-m-Y', strtotime($payment_all_collection->due_date));
            })
            ->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 report_all_payment_to_collection_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));
        $payment_collection = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_process.id',
                'nso007_loan_process.loanprocess_datepayment AS due_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_process.loanprocess_interest AS interest',
                'nso007_loan_process.loanprocess_principle AS principle',
                'nso007_loan_process.loanprocess_owe AS payment'
            )
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            //            ->where(function ($query) {
            //                $query->whereNull('nso007_loan_process.loanprocess_paid');
            //                $query->orWhere('nso007_loan_process.loanprocess_paid', 2);
            //            })
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when(request('projects'),function($q) use ($request){
                $q->whereIn('nso007_project_stock.id', isset($request->projects) ? $request->projects : NULL);
            })
            ->when(request('clients'),function($q) use ($request){
                $q->whereIn('nso007_client.id', isset($request->clients) ? $request->clients : NULL);
            })
            ->when(request('categories'),function($q) use ($request){
                $q->whereIn('nso007_category_stock.id', isset($request->categories) ? $request->categories : NULL);
            })
            ->where('nso007_client.client_phone1', 'LIKE', isset($request->phone) ? $request->phone : NULL)
            ->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(nso007_loan_process.loanprocess_datepayment,'%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(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('sale_stock.id')
            ->groupBy('nso007_loan_process.id')
            ->get();

        $total_principle = 0;
        $total_interest = 0;
        $total_payment = 0;
        foreach ($payment_collection as $key => $value) {
            $total_principle += $value->principle;
            $total_interest += $value->interest;
            $total_payment += $value->payment;
        }
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.all-payment-collection.report-view-print', compact('payment_collection', 'total_principle', 'total_interest', 'total_payment', 'branch'));
    }
    //////////////////////////// report_collection ///////////////////////////
    //////////////////////////////////////////////////////////////////////////

    public function payment_collection()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->prepend("Please Select","")->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->prepend("Please Select","")->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->prepend("Please Select","")->toArray();

        return view('administrator.payment.collection.report', compact('projects', 'categories', 'clients'));
    }

    public function report_collection(Request $request)
    {
        $project    = $request->project;
        $category   = $request->category;
        $start_date = $request->start_date;
        $end_date   = $request->end_date;
        $client     = $request->client;
        $phone      = $request->phone;
        $search     = $request->input('search.value');

        $countTotal = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->where(function ($query) {
                $query->whereNull('nso007_loan_process.loanprocess_paid');
                $query->orWhere('nso007_loan_process.loanprocess_paid', 2);
            })
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('nso007_loan_process.id')
            ->get()->count();
        $countFilter = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->where(function ($query) {
                $query->whereNull('nso007_loan_process.loanprocess_paid');
                $query->orWhere('nso007_loan_process.loanprocess_paid', 2);
            })
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->where(function ($q) use ($search) {
                $q->orwhere('nso007_client.client_firstname', 'LIKE', '%' . $search . '%');
                $q->orwhere('nso007_client.client_lastname', 'LIKE', '%' . $search . '%');
                $q->orwhere('nso007_item_stock.item_house', 'LIKE', '%' . $search . '%');
                $q->orwhere('nso007_category_stock.category_name', 'LIKE', '%' . $search . '%');
                $q->orwhere('nso007_project_stock.project_name', 'LIKE', '%' . $search . '%');
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('nso007_loan_process.id')
            ->get()->count();
        $payment_collection = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_process.id as loan_proid',
                'nso007_loan_process.loanprocess_datepayment AS due_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_process.loanprocess_interest AS interest',
                'nso007_loan_process.loanprocess_principle AS principle',
                'nso007_loan_process.loanprocess_owe AS payment'
            )
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->where(function ($query) {
                $query->whereNull('nso007_loan_process.loanprocess_paid');
                $query->orWhere('nso007_loan_process.loanprocess_paid', 2);
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when($project, function ($query, $project) {
                $query->where('nso007_project_stock.id', $project);
            })
            ->when($category, function ($query, $category) {
                $query->where('nso007_category_stock.id', $category);
            })
            ->when(!empty($start_date) || !empty($end_date), function ($query) use ($start_date, $end_date) {
                if (!empty($start_date) && !empty($end_date)) {
                    $query->whereBetween('nso007_loan_process.loanprocess_datepayment', [date('Y-m-d', strtotime($start_date)), date('Y-m-d', strtotime($end_date))]);
                } elseif (!empty($start_date) && empty($end_date)) {
                    $query->whereBetween('nso007_loan_process.loanprocess_datepayment', '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $query->whereBetween('nso007_loan_process.loanprocess_datepayment', '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->when($client, function ($query, $client) {
                $query->where('nso007_client.id', $client);
            })
            ->when($phone, function ($query, $phone) {
                $query->where('nso007_client.client_phone1', $phone);
            })
            ->groupBy('nso007_loan_process.id')
            ->take(20);
        return  Datatables::of($payment_collection)
            ->addIndexColumn()
            ->with([
                "recordsTotal" => $countTotal,
                "recordsFiltered" => $countFilter,
            ])
            ->setRowId('loan_proid')
            ->addColumn('interest', function ($payment_collection) {
                return '$' . number_format($payment_collection->interest, 2);
            })
            ->addColumn('payment', function ($payment_collection) {
                return '$' . number_format($payment_collection->payment, 2);
            })
            ->addColumn('principle', function ($payment_collection) {
                return '$' . number_format($payment_collection->principle, 2);
            })
            ->addColumn('due_date', function ($payment_collection) {
                return date('d-m-Y', strtotime($payment_collection->due_date));
            })
            ->make(true);
    }

    public function report_collection_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));

        $payment_collection = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_process.id',
                'nso007_loan_process.loanprocess_datepayment AS due_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_process.loanprocess_interest AS interest',
                'nso007_loan_process.loanprocess_principle AS principle',
                'nso007_loan_process.loanprocess_owe AS payment'
            )
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->where(function ($query) {
                $query->whereNull('nso007_loan_process.loanprocess_paid');
                $query->orWhere('nso007_loan_process.loanprocess_paid', 2);
            })
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when(request('projects'),function($q) use ($request){
                $q->where('nso007_project_stock.id', isset($request->projects) ? $request->projects : NULL);
            })
            ->when(request('clients'),function($q) use ($request){
                $q->where('nso007_client.id',isset($request->clients) ? $request->clients : NULL);
            })
            ->when(request('categories'),function($q) use ($request){
                $q->where('nso007_category_stock.id', 'LIKE', isset($request->categories) ? $request->categories : NULL);
            })
            ->where('nso007_client.client_phone1', 'LIKE', isset($request->phone) ? $request->phone : NULL)
            ->when(request('start_date', ''), function ($query) use ($start_date, $end_date) {
                $query->whereBetween('nso007_loan_process.loanprocess_datepayment', [$start_date, $end_date]);
            })
            ->whereNull('nso007_loan.loan_cancel_status')
            ->orWhere('nso007_loan.loan_cancel_status',1)
            ->groupBy('sale_stock.id')
            ->groupBy('nso007_loan_process.id')
            ->get();

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

    ////////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////////////

    /////////////////////////////////////payment_collected///////////////////////////////////
    /////////////////////////////////////////////////////////////////////////////////////////

    public function payment_collected()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->toArray();
        return view('administrator.payment.collected.report', compact('projects', 'categories', 'clients'));
    }
//Report Payment Collected (Old)
    /*public function report_collected(Request $request)
    {
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $project_id = $request->project_id;
        $category_id = $request->category_id;
        $client_id = $request->client_id;
        $item_name = $request->item_name;
        $search = $request->input('search.value');
        $counttotal = DB::table('nso007_loan_process')
                    ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
                    ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
                    ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
                    ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
                    ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
                    ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
                    ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
                    ->whereNotNull('nso007_loan_process.loanprocess_no')
                    ->where('nso007_loan_process.loanprocess_paid', 1)
                    ->when(empty(\Branch::branch_key()), function ($query) {
                        $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                    })->groupBy('nso007_loan_process.id')->get()->count();

        $countfilter = DB::table('nso007_loan_process')
                    ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
                    ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
                    ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
                    ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
                    ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
                    ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
                    ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
                    ->whereNotNull('nso007_loan_process.loanprocess_no')
                    ->where('nso007_loan_process.loanprocess_paid', 1)
                    ->where(function($q) use($search){
                        $q->orwhere('nso007_client.client_firstname', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_client.client_lastname', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_item_stock.item_house', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_category_stock.category_name', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_project_stock.project_name', 'LIKE', '%' . $search . '%');
                    })
                    ->when(empty(\Branch::branch_key()), function ($query) {
                        $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                    })->groupBy('nso007_loan_process.id')->get()->count();

        $report_collected = DB::table('nso007_loan_process')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_process.id as loan_proid',
                'nso007_loan_process.loanprocess_datepayment AS do_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_process.loanprocess_interest AS interest',
                'nso007_loan_process.loanprocess_principle AS principle',
                'nso007_loan_process.loanprocess_owe AS payment',
                'sale_stock.id'
            )
            ->whereNotNull('nso007_loan_process.loanprocess_no')
            ->where('nso007_loan_process.loanprocess_paid', 1)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when($category_id, function ($q, $category_id) {
                $q->where('nso007_category_stock.id', $category_id);
            })
            ->when($project_id, function ($q, $project_id) {
                $q->where('nso007_project_stock.id', $project_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->where('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->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(nso007_loan_process.loanprocess_datepayment,'%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(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(nso007_loan_process.loanprocess_datepayment,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->groupBy('nso007_loan_process.id')
            ->take(20);
        return  Datatables::of($report_collected)
            ->with([
                'recordsTotal'=>$counttotal,
                'recordsFiltered'=>$countfilter
            ])
            ->addColumn('interest', function ($report_collected) {
                return '$' . number_format($report_collected->interest, 2);
            })
            ->addColumn('payment', function ($report_collected) {
                return '$' . number_format($report_collected->payment, 2);
            })
            ->addColumn('principle', function ($report_collected) {
                return '$' . number_format($report_collected->principle, 2);
            })
            ->addColumn('do_date', function ($report_collected) {
                return date('d-m-Y', strtotime($report_collected->do_date));
            })
            ->editColumn('item_code', function ($report_collected) {
                return SaleStockModel::find($report_collected->id)->itemhouse();
            })
            ->addIndexColumn()
            ->setRowId('loan_proid')
            ->make(true);
    }*/
//Repot Payment Collected (New)
    public function report_collected(Request $request)
    {
        $start_date = $request->start_date;
        $end_date = $request->end_date;
        $project_id = $request->project_id;
        $category_id = $request->category_id;
        $client_id = $request->client_id;
        $item_name = $request->item_name;
        $search = $request->input('search.value');
        $counttotal = DB::table('nso007_loan_paid')
                    ->join('nso007_loan_process','nso007_loan_paid.loanpaid_processid','nso007_loan_process.id')
                    ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
                    ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
                    ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
                    ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
                    ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
                    ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
                    ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
                    ->whereNotNull('nso007_loan_paid.loanpaid_approved')
                    ->when(empty(\Branch::branch_key()), function ($query) {
                        $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                    })->groupBy('nso007_loan_paid.id')->get()->count();

        $countfilter = DB::table('nso007_loan_paid')
                    ->join('nso007_loan_process','nso007_loan_paid.loanpaid_processid','nso007_loan_process.id')
                    ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
                    ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
                    ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
                    ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
                    ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
                    ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
                    ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
                    ->whereNotNull('nso007_loan_process.loanprocess_no')
                    ->whereNotNull('nso007_loan_paid.loanpaid_approved')
                    ->where(function($q) use($search){
                        $q->orwhere('nso007_client.client_firstname', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_client.client_lastname', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_item_stock.item_house', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_category_stock.category_name', 'LIKE', '%' . $search . '%');
                        $q->orwhere('nso007_project_stock.project_name', 'LIKE', '%' . $search . '%');
                    })
                    ->when(empty(\Branch::branch_key()), function ($query) {
                        $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                    })->groupBy('nso007_loan_paid.id')->get()->count();

        $report_collected = DB::table('nso007_loan_paid')
            ->join('nso007_loan_process','nso007_loan_paid.loanpaid_processid','nso007_loan_process.id')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_paid.id AS loan_proid',
                'nso007_loan_paid.loanpaid_payment AS do_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_paid.loanpaid_interest AS interest',
                'nso007_loan_paid.loanpaid_principle AS principle',
                'nso007_loan_paid.loanpaid_penalty AS penalty',
                'nso007_loan_paid.loanpaid_total AS total_amount',
                'sale_stock.id'
            )
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when($category_id, function ($q, $category_id) {
                $q->where('nso007_category_stock.id', $category_id);
            })
            ->when($project_id, function ($q, $project_id) {
                $q->where('nso007_project_stock.id', $project_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->where('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->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(nso007_loan_paid.loanpaid_payment,'%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(nso007_loan_paid.loanpaid_payment,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(nso007_loan_paid.loanpaid_payment,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->whereNotNull('nso007_loan_paid.loanpaid_approved')
            ->groupBy('nso007_loan_paid.id')
            ->take(20);
        return  Datatables::of($report_collected)
            ->with([
                'recordsTotal'=>$counttotal,
                'recordsFiltered'=>$countfilter
            ])
            ->addColumn('interest', function ($report_collected) {
                return '$' . number_format($report_collected->interest, 2);
            })
            ->addColumn('payment', function ($report_collected) {
                return '$' . number_format($report_collected->total_amount, 2);
            })
            ->addColumn('principle', function ($report_collected) {
                return '$' . number_format($report_collected->principle, 2);
            })
            ->addColumn('do_date', function ($report_collected) {
                return date('d-m-Y', strtotime($report_collected->do_date));
            })
            ->addColumn('penalty', function ($report_collected) {
                return '$' . number_format($report_collected->penalty, 2);
            })
            ->editColumn('item_code', function ($report_collected) {
                return SaleStockModel::find($report_collected->id)->itemhouse();
            })
            ->addIndexColumn()
            ->setRowId('loan_proid')
            ->make(true);
    }
//End

    public function report_collected_print(Request $request)
    {
        $start_date = $request->start_date;
        $end_date   = $request->end_date;
        $collected = DB::table('nso007_loan_paid')
            ->join('nso007_loan_process','nso007_loan_paid.loanpaid_processid','nso007_loan_process.id')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan_paid.id',
                'sale_stock.id as sale_id',
                'nso007_loan_paid.loanpaid_payment AS do_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_loan_paid.loanpaid_interest AS interest',
                'nso007_loan_paid.loanpaid_principle AS principle',
                'nso007_loan_paid.loanpaid_penalty AS penalty',
                'nso007_loan_paid.loanpaid_total AS payment'
            )
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when(request('projects'),function($q) use ($request){
                $q->whereIn('nso007_project_stock.id',isset($request->projects) ? $request->projects : NULL);
            })
            ->when(request('clients'),function($q) use ($request){
                $q->whereIn('nso007_client.id',isset($request->clients) ? $request->clients : NULL);
            })
            ->when(request('categories'),function($q) use ($request){
                $q->whereIn('nso007_category_stock.id',isset($request->categories) ? $request->categories : NULL);
            })
            ->where('nso007_item_stock.item_name', 'LIKE', isset($request->item_name) ? $request->item_name : NULL)
            ->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(nso007_loan_paid.loanpaid_payment,'%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(nso007_loan_paid.loanpaid_payment,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(nso007_loan_paid.loanpaid_payment,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->whereNotNull('nso007_loan_paid.loanpaid_approved')
            ->groupBy('sale_stock.id')
            ->groupBy('nso007_loan_paid.id')
            ->get();
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.collected.report-view-print', compact('collected', 'branch'));
    }

    ////////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////////////


    public function payment_completed()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->toArray();
        return view('administrator.payment.completed.report', compact('projects', 'categories', 'clients'));
    }

    public function report_completed(Request $request)
    {
        $start_date = date('Y-m-d', strtotime($request->start_date));
        $end_date = date('Y-m-d', strtotime($request->start_date));
        $search = $request->input('search.value');
        $count_total = DB::table('nso007_loan')
            ->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')
            ->where('nso007_loan.loan_status', 2)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->count();
        $count_filter = DB::table('nso007_loan')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->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_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->where('nso007_loan.loan_status', 2)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->where(function ($query) use ($search) {
                $query->orWhere('nso007_loan.loan_enddate', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_project_stock.project_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_category_stock.category_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_item_stock.item_name', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_client.client_fullname', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_client.client_phone1', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_sale_stock.sale_amount', 'LIKE', '%' . $search . '%');
                $query->orWhere('nso007_sale_stock.sale_discount', 'LIKE', '%' . $search . '%');
            })
            ->count();

        $report_completed = DB::table('nso007_loan')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->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_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan.id',
                'nso007_loan.loan_enddate AS end_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                'nso007_item_stock.item_house AS item_code',
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_sale_stock.sale_amount AS sale_cost',
                'nso007_sale_stock.sale_discount AS sale_discount',
                DB::raw("(nso007_sale_stock.sale_amount - nso007_sale_stock.sale_discount) AS sale_price")
            )
            ->where('nso007_loan.loan_status', 2)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when(request('project'),function($q) use ($request){
                $q->whereIn('nso007_project_stock.id', isset($request->project) ? $request->project : NULL);
            })
            ->when(request('client'),function($q) use ($request){
                $q->whereIn('nso007_client.id', isset($request->client) ? $request->client : NULL);
            })
            ->when(request('category'),function($q) use ($request){
                $q->whereIn('nso007_category_stock.id', isset($request->category) ? $request->category : NULL);
            })
            ->where('nso007_item_stock.item_house', 'LIKE', isset($request->item_name) ? $request->item_name : NULL)
            ->when(request('start_date', ''), function ($query) use ($start_date, $end_date) {
                $query->whereBetween('nso007_loan.loan_enddate', [$start_date, $end_date]);
            })
            ->take(20);
        return  Datatables::of($report_completed)
            ->with([
                'recordsTotal' => $count_total,
                'recordsFiltered' => $count_filter

            ])
            ->addColumn('sale_cost', function ($report_completed) {
                return '$' . number_format($report_completed->sale_cost, 2);
            })
            ->addColumn('sale_discount', function ($report_completed) {
                return '$' . number_format($report_completed->sale_discount, 2);
            })
            ->addColumn('sale_price', function ($report_completed) {
                return '$' . number_format($report_completed->sale_price, 2);
            })
            ->addColumn('end_date', function ($report_completed) {
                return date('d-m-Y', strtotime($report_completed->end_date));
            })
            ->addIndexColumn()
            ->make(true);
    }

    public function report_completed_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));
        $report_completed = DB::table('nso007_loan')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->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_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_loan.id',
                'nso007_loan.loan_enddate AS end_date',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                'nso007_item_stock.item_house AS item_code',
                'nso007_client.client_fullname AS client_name',
                'nso007_client.client_phone1 AS client_contact',
                'nso007_sale_stock.sale_amount AS sale_cost',
                'nso007_sale_stock.sale_discount AS sale_discount',
                DB::raw("(nso007_sale_stock.sale_amount - nso007_sale_stock.sale_discount) AS sale_price")
            )
            ->where('nso007_loan.loan_status', 2)
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when(request('projects'),function($q) use ($request){
                $q->whereIn('nso007_project_stock.id',isset($request->projects) ? $request->projects : NULL);
            })
            ->when(request('clients'),function($q) use ($request){
                $q->whereIn('nso007_client.id',isset($request->clients) ? $request->clients : NULL);
            })
            ->when(request('categories'),function($q) use ($request){
                $q->whereIn('nso007_category_stock.id',isset($request->categories) ? $request->categories : NULL);
            })
            ->where('nso007_item_stock.item_name', 'LIKE', isset($request->item_name) ? $request->item_name : NULL)
            ->when(request('start_date', ''), function ($query) use ($start_date, $end_date) {
                $query->whereBetween('nso007_loan.loan_enddate', [$start_date, $end_date]);
            })
            ->get();

        $total_sale_cost = 0;
        $total_sale_discount = 0;
        $total_sale_price = 0;
        foreach ($report_completed as $key => $value) {
            $total_sale_cost += $value->sale_cost;
            $total_sale_discount += $value->sale_discount;
            $total_sale_price += $value->sale_price;
        }
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.completed.report-view-print', compact('report_completed', 'total_sale_cost', 'total_sale_discount', 'total_sale_price', 'branch'));
    }


    public function payment_client()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::pluck('client_fullname', 'id')->toArray();
        return view('administrator.payment.payment.report', compact('projects', 'categories', 'clients'));
    }
    public function report_client_payment(Request $request)
    {
        $project_id     = $request->project_id;
        $category_id    = $request->category_id;
        $client_id      = $request->client_id;
        $item_name      = $request->item_name;
        $search         = $request->input('search.value');
        $count_total    = DB::table('nso007_sale_stock')
        ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
        ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
        ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
        ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
        ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
        ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
        ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
        ->when($project_id, function ($q, $project_id) {
            $q->whereIn('nso007_project_stock.id', $project_id);
        })
        ->when($category_id, function ($q, $category_id) {
            $q->whereIn('nso007_category_stock.id', $category_id);
        })
        ->when($client_id, function ($q, $client_id) {
            $q->whereIn('nso007_client.id', $client_id);
        })
        ->when($item_name, function ($q, $item_name) {
            $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
        })
        ->when(empty(\Branch::branch_key()), function ($q) {
            $q->where('nso007_loan.branch_id', \Branch::branch_id());
        })
        ->groupBy('nso007_sale_stock.id')
        ->get()->count();
        $count_filter    = DB::table('nso007_sale_stock')
        ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
        ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
        ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
        ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
        ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
        ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
        ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
        ->where(function($q) use($search){
            $q->orwhere('nso007_loan.loan_amount','LIKE','%'.$search.'%');
            $q->orwhere('nso007_project_stock.project_name','LIKE','%'.$search.'%');
            $q->orwhere('nso007_category_stock.category_name','LIKE','%'.$search.'%');
            $q->orwhere('nso007_item_stock.item_house','LIKE','%'.$search.'%');
            $q->orwhere('nso007_client.client_fullname','LIKE','%'.$search.'%');
            $q->orwhere('discount.name','LIKE','%'.$search.'%');
        })
        ->when($project_id, function ($q, $project_id) {
            $q->whereIn('nso007_project_stock.id', $project_id);
        })
        ->when($category_id, function ($q, $category_id) {
            $q->whereIn('nso007_category_stock.id', $category_id);
        })
        ->when($client_id, function ($q, $client_id) {
            $q->whereIn('nso007_client.id', $client_id);
        })
        ->when($item_name, function ($q, $item_name) {
            $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
        })
        ->when(empty(\Branch::branch_key()), function ($q) {
            $q->where('nso007_loan.branch_id', \Branch::branch_id());
        })
        ->groupBy('nso007_sale_stock.id')
        ->get()->count();
        $client_payment = DB::table('nso007_sale_stock')
            ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
            ->select(
                'nso007_client.id',
                'nso007_client.client_fullname AS client_name',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_sale_stock.sale_receive AS deposit_amount',
                'nso007_sale_stock.sale_discount AS sale_discount',
                'nso007_sale_stock.id AS sale_id',
                'nso007_loan.loan_amount AS sale_amount',
                'nso007_loan.loan_discount',
                'discount.name as discount_name',
                'nso007_loan.discounttype_amount',
                'nso007_loan.loan_cancel_status',
                'nso007_loan.loan_amountos AS amount_due',
                'nso007_loan.date_transfer_ownership',
                'nso007_loan.transfer_ownership_status',
                'nso007_loan.remark_transfer_ownership',
                DB::raw("nso007_loan.loan_amount - nso007_sale_stock.sale_discount AS sale_price"),
                DB::raw("(nso007_loan.loan_amount - nso007_loan.loan_amountos - nso007_loan.loan_discount - nso007_sale_stock.sale_receive) AS payment_receive")
            )
            ->when($project_id, function ($q, $project_id) {
                $q->whereIn('nso007_project_stock.id', $project_id);
            })
            ->when($category_id, function ($q, $category_id) {
                $q->whereIn('nso007_category_stock.id', $category_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->whereIn('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->when(empty(\Branch::branch_key()), function ($q) {
                $q->where('nso007_loan.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_sale_stock.id')
            ->take(20);
        return  Datatables::of($client_payment)
            ->addColumn('sale_price', function ($client_payment) {
                return '$ ' . number_format($client_payment->sale_price, 2);
            })
            ->addColumn('deposit_amount', function ($client_payment) {
                return '$ ' . number_format($client_payment->deposit_amount, 2);
            })
            ->addColumn('amount_due', function ($client_payment) {
                return '$ ' . number_format($client_payment->amount_due, 2);
            })
            ->addColumn('sale_price', function ($client_payment) {
                return '$ ' . number_format($client_payment->sale_amount - $client_payment->loan_discount, 2);
            })
            ->editColumn('payment_receive',function($client_payment){
                return "$".number_format($client_payment->payment_receive,2);
            })
            ->addColumn('sale_discount', function ($client_payment) {
                return '$ ' . number_format($client_payment->sale_discount, 2);
            })
            ->editColumn('item_code', function ($client_payment) {
                return $this->getItemNameBySaleID($client_payment->sale_id);
            })
            ->editColumn('sale_amount',function($client_payment)
            {
                return "$".number_format($client_payment->sale_amount,2);
            })
            ->addColumn('other',function($client_payment){
                if($client_payment->loan_cancel_status==2) return "Canceled";
                else return "";
            })
            ->editColumn('transfer_ownership_status',function($client_payment){
                $data = "";
                if(!empty($client_payment->date_transfer_ownership)){
                    $data .= date("d-m-Y",strtotime($client_payment->date_transfer_ownership));
                    if(!empty($client_payment->remark_transfer_ownership)){
                        $data .= " : ".$client_payment->remark_transfer_ownership;
                    }
                   
                }
                if($client_payment->transfer_ownership_status==0){
                    $data .= " (Pending)";
                }
                else $data .= " (Done)";
                return $data;
            })
            ->addIndexColumn()
            ->with([
                'recordsTotal'      => $count_total,
                'recordsFiltered'   => $count_filter
            ])
            ->make(true);
    }
    public function report_client_payment_print(Request $request)
    {
        $project_id     = $request->project_id;
        $category_id    = $request->category_id;
        $client_id      = $request->client_id;
        $item_name      = $request->item_name;
        $client_payment = DB::table('nso007_sale_stock')
            ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
            ->select(
                'nso007_client.id',
                'nso007_client.client_fullname AS client_name',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_sale_stock.sale_receive AS deposit_amount',
                'nso007_sale_stock.sale_discount AS sale_discount',
                'nso007_sale_stock.id AS sale_id',
                'nso007_loan.loan_amount AS sale_amount',
                'nso007_loan.loan_discount',
                'discount.name as discount_name',
                'nso007_loan.discounttype_amount',
                'nso007_loan.loan_amountos AS amount_due',
                'nso007_loan.loan_cancel_status',
                'nso007_loan.transfer_ownership_status',
                DB::raw("nso007_loan.loan_amount - nso007_sale_stock.sale_discount AS sale_price"),
                DB::raw("(nso007_loan.loan_amount - nso007_loan.loan_amountos - nso007_loan.loan_discount - nso007_sale_stock.sale_receive) AS payment_receive")
            )
            ->when($project_id, function ($q, $project_id) {
                $q->whereIn('nso007_project_stock.id', $project_id);
            })
            ->when($category_id, function ($q, $category_id) {
                $q->whereIn('nso007_category_stock.id', $category_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->whereIn('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->when(empty(\Branch::branch_key()), function ($q) {
                $q->where('nso007_loan.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_sale_stock.id')
            ->get();
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.payment.report-view-print', compact('client_payment', 'branch'));
    }

    public function payment_client_transfer_ownership()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->toArray();
        $clients = ClientModel::pluck('client_fullname', 'id')->toArray();
        return view('administrator.payment.payment.transfer-ownership', compact('projects', 'categories', 'clients'));
    }
    public function report_payment_client_transfer_ownership(Request $request)
    {
        $project_id     = $request->project_id;
        $category_id    = $request->category_id;
        $client_id      = $request->client_id;
        $item_name      = $request->item_name;
        $search         = $request->input('search.value');
        $count_total    = DB::table('nso007_sale_stock')
        ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
        ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
        ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
        ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
        ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
        ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
        ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
        ->where("nso007_loan.transfer_ownership_status",1)
        ->when($project_id, function ($q, $project_id) {
            $q->whereIn('nso007_project_stock.id', $project_id);
        })
        ->when($category_id, function ($q, $category_id) {
            $q->whereIn('nso007_category_stock.id', $category_id);
        })
        ->when($client_id, function ($q, $client_id) {
            $q->whereIn('nso007_client.id', $client_id);
        })
        ->when($item_name, function ($q, $item_name) {
            $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
        })
        ->when(empty(\Branch::branch_key()), function ($q) {
            $q->where('nso007_loan.branch_id', \Branch::branch_id());
        })
        ->groupBy('nso007_sale_stock.id')
        ->get()->count();
        $count_filter    = DB::table('nso007_sale_stock')
        ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
        ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
        ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
        ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
        ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
        ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
        ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
        ->where("nso007_loan.transfer_ownership_status",1)
        ->where(function($q) use($search){
            $q->orwhere('nso007_loan.loan_amount','LIKE','%'.$search.'%');
            $q->orwhere('nso007_project_stock.project_name','LIKE','%'.$search.'%');
            $q->orwhere('nso007_category_stock.category_name','LIKE','%'.$search.'%');
            $q->orwhere('nso007_item_stock.item_house','LIKE','%'.$search.'%');
            $q->orwhere('nso007_client.client_fullname','LIKE','%'.$search.'%');
            $q->orwhere('discount.name','LIKE','%'.$search.'%');
        })
        ->when($project_id, function ($q, $project_id) {
            $q->whereIn('nso007_project_stock.id', $project_id);
        })
        ->when($category_id, function ($q, $category_id) {
            $q->whereIn('nso007_category_stock.id', $category_id);
        })
        ->when($client_id, function ($q, $client_id) {
            $q->whereIn('nso007_client.id', $client_id);
        })
        ->when($item_name, function ($q, $item_name) {
            $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
        })
        ->when(empty(\Branch::branch_key()), function ($q) {
            $q->where('nso007_loan.branch_id', \Branch::branch_id());
        })
        ->groupBy('nso007_sale_stock.id')
        ->get()->count();
        $client_payment = DB::table('nso007_sale_stock')
            ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
            ->select(
                'nso007_client.id',
                'nso007_client.client_fullname AS client_name',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_sale_stock.sale_receive AS deposit_amount',
                'nso007_sale_stock.sale_discount AS sale_discount',
                'nso007_sale_stock.id AS sale_id',
                'nso007_loan.loan_amount AS sale_amount',
                'nso007_loan.loan_discount',
                'discount.name as discount_name',
                'nso007_loan.discounttype_amount',
                'nso007_loan.loan_cancel_status',
                'nso007_loan.transfer_ownership_status',
                'nso007_loan.date_transfer_ownership',
                'nso007_loan.remark_transfer_ownership',
                'nso007_loan.date_transfered_ownership',
                'nso007_loan.remark_transfered_ownership'
            )
            ->where("nso007_loan.transfer_ownership_status",1)
            ->when($project_id, function ($q, $project_id) {
                $q->whereIn('nso007_project_stock.id', $project_id);
            })
            ->when($category_id, function ($q, $category_id) {
                $q->whereIn('nso007_category_stock.id', $category_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->whereIn('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->when(empty(\Branch::branch_key()), function ($q) {
                $q->where('nso007_loan.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_sale_stock.id')
            ->take(20);
        return  Datatables::of($client_payment)
            ->addIndexColumn()
            ->with([
                'recordsTotal'      => $count_total,
                'recordsFiltered'   => $count_filter
            ])
            ->addColumn('deposit_amount', function ($client_payment) {
                return '$ ' . number_format($client_payment->deposit_amount, 2);
            })
            ->addColumn('sale_price', function ($client_payment) {
                return '$ ' . number_format($client_payment->sale_amount - $client_payment->loan_discount, 2);
            })
            ->editColumn('date_transfer_ownership',function($client_payment){
                return date("d-m-Y",strtotime($client_payment->date_transfered_ownership));
            })
            ->addColumn('sale_discount', function ($client_payment) {
                return '$ ' . number_format($client_payment->sale_discount, 2);
            })
            ->editColumn('item_code', function ($client_payment) {
                return $this->getItemNameBySaleID($client_payment->sale_id);
            })
            ->editColumn('sale_amount',function($client_payment)
            {
                return "$".number_format($client_payment->sale_amount,2);
            })
            ->addColumn('other',function($client_payment){
                if($client_payment->loan_cancel_status==2) return "Canceled";
                else return "";
            })
            ->make(true);
    }
    public function report_payment_client_transfer_ownership_print(Request $request)
    {
        $project_id     = $request->project_id;
        $category_id    = $request->category_id;
        $client_id      = $request->client_id;
        $item_name      = $request->item_name;
        $client_payment = DB::table('nso007_sale_stock')
            ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->leftjoin('nso007_discount_type as discount', 'discount.id', 'nso007_loan.discount_type')
            ->select(
                'nso007_client.id',
                'nso007_client.client_fullname AS client_name',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) as item_code'),
                'nso007_sale_stock.sale_receive AS deposit_amount',
                'nso007_sale_stock.sale_discount AS sale_discount',
                'nso007_sale_stock.id AS sale_id',
                'nso007_loan.loan_amount AS sale_amount',
                'nso007_loan.loan_discount',
                'discount.name as discount_name',
                'nso007_loan.discounttype_amount',
                'nso007_loan.loan_amountos AS amount_due',
                'nso007_loan.loan_cancel_status',
                'nso007_loan.transfer_ownership_status',
                'nso007_loan.date_transfer_ownership',
                'nso007_loan.remark_transfer_ownership',
                DB::raw("nso007_loan.loan_amount - nso007_sale_stock.sale_discount AS sale_price")
            )
            ->when($project_id, function ($q, $project_id) {
                $q->whereIn('nso007_project_stock.id', $project_id);
            })
            ->when($category_id, function ($q, $category_id) {
                $q->whereIn('nso007_category_stock.id', $category_id);
            })
            ->when($client_id, function ($q, $client_id) {
                $q->whereIn('nso007_client.id', $client_id);
            })
            ->when($item_name, function ($q, $item_name) {
                $q->where('nso007_item_stock.item_house', 'LIKE', "%" . $item_name . "%");
            })
            ->when(empty(\Branch::branch_key()), function ($q) {
                $q->where('nso007_loan.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_sale_stock.id')
            ->get();
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.payment.payment.transfer-ownership-print', compact('client_payment', 'branch'));
    }

    public function report_receivable_note_branch()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->prepend('Please Select Option', '')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->prepend('Please Select Option', '')->toArray();
        $clients = ClientModel::pluck('client_fullname', 'id')->prepend('Please Select Option', '')->toArray();
        return view('administrator.accounting.receivable-note.report-receiveable-note-branch', compact('projects', 'categories', 'clients'));
    }
    public function get_report_receivable_note_branch(Request $request)
    {

        $client_payment = DB::table('nso007_sale_stock')
            ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->select(
                'nso007_client.id',
                'nso007_client.client_fullname AS client_name',
                'nso007_project_stock.project_name',
                'nso007_category_stock.category_name',
                'nso007_item_stock.item_name AS item_code',
                'nso007_sale_stock.sale_receive AS client_deposit',
                'nso007_sale_stock.sale_discount AS sale_discount',
                'nso007_loan.loan_amount AS sale_amount',
                'nso007_loan.loan_amountos AS amount_due',
                DB::raw("nso007_loan.loan_amount - nso007_sale_stock.sale_discount AS sale_price"),
                DB::raw("nso007_loan.loan_amount - nso007_loan.loan_amountos - nso007_sale_stock.sale_discount AS sale_recieve")
            )
            ->where('nso007_project_stock.branch_id', \Branch::branch_id())
            ->get();

        if ($request->isMethod('get')) {
            return  Datatables::of($client_payment)
                ->addColumn('sale_price', function ($client_payment) {
                    return '$' . number_format($client_payment->sale_price, 2);
                })
                ->addColumn('sale_recieve', function ($client_payment) {
                    return '$' . number_format($client_payment->sale_recieve, 2);
                })
                ->addColumn('amount_due', function ($client_payment) {
                    return '$' . number_format($client_payment->amount_due, 2);
                })
                ->addColumn('sale_amount', function ($client_payment) {
                    return '$' . number_format($client_payment->sale_amount, 2);
                })
                ->addColumn('sale_discount', function ($client_payment) {
                    return '$' . number_format($client_payment->sale_discount, 2);
                })

                ->addIndexColumn()
                ->make(true);
        } else {

            $client_payment = DB::table('nso007_sale_stock')
                ->join('nso007_loan', 'nso007_loan.loan_saleid', 'nso007_sale_stock.id')
                ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
                ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
                ->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_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
                ->select(
                    'nso007_client.id',
                    'nso007_client.client_fullname AS client_name',
                    'nso007_project_stock.project_name',
                    'nso007_category_stock.category_name',
                    'nso007_item_stock.item_name AS item_code',
                    'nso007_sale_stock.sale_receive AS client_deposit',
                    'nso007_sale_stock.sale_discount AS sale_discount',
                    'nso007_loan.loan_amount AS sale_amount',
                    'nso007_loan.loan_amountos AS amount_due',
                    DB::raw("nso007_loan.loan_amount - nso007_sale_stock.sale_discount AS sale_price"),
                    DB::raw("nso007_loan.loan_amount - nso007_loan.loan_amountos - nso007_sale_stock.sale_discount AS sale_recieve")
                )
                ->where('nso007_project_stock.branch_id', \Branch::branch_id())
                ->where('nso007_project_stock.id', 'LIKE', isset($request->projects) ? $request->projects : NULL)
                ->where('nso007_client.id', 'LIKE', isset($request->clients) ? $request->clients : NULL)
                ->where('nso007_category_stock.id', 'LIKE', isset($request->categories) ? $request->categories : NULL)
                ->where('nso007_item_stock.item_name', 'LIKE', isset($request->item_name) ? $request->item_name : NULL)
                ->get();

            echo json_encode($client_payment);
        }
    }
    public function get_report_receivable_note_branch_print(Request $request)
    {
        $mdoel_searching = new SearchModel();
        $projects   = (empty($request->projects)) ? '' : ' pro.id = "' . $request->projects . '" ';
        $clients    = (empty($request->clients)) ? '' : ' clin.id = "' . $request->clients . '" ';
        $categories = (empty($request->categories)) ? '' : ' cat.id = "' . $request->categories . '" ';
        $item_name      = (empty($request->item_name)) ? '' : ' item.item_name = "' . $request->item_name . '" ';

        $data_search = array(
            'projects' => $projects,
            'clients' => $clients,
            'categories' => $categories,
            'item_name' => $item_name,
        );
        $search = $mdoel_searching->search($data_search);
        $search = (empty($search)) ? "WHERE pro. branch_id= '" . \Branch::branch_id() . "' " : "WHERE pro.branch_id= '" . \Branch::branch_id() . "'" . $search;

        $client_payment = DB::select("
            SELECT
            clin.id,
            clin.client_fullname AS client_name,
            clin.client_cardid AS client_idcard,
            pro.project_name AS project_name,
            cat.category_name AS category_name,
            item.item_name AS item_code,
            s.sale_receive AS client_deposit,
            loan.loan_amount AS sale_amount,
            s.sale_discount AS sale_discount,
            loan.loan_amountos AS amount_due,
            (loan.loan_amount - s.sale_discount) AS sale_price,
            (loan.loan_amount - loan.loan_amountos - s.sale_discount) AS sale_recieve
            FROM nso007_sale_stock AS s
            INNER JOIN nso007_loan AS loan ON s.id = loan.loan_saleid
            INNER JOIN nso007_client AS clin ON s.sale_clientid = clin.id
            INNER JOIN nso007_sale_detail_stock AS detail ON s.id = detail.saledetail_saleid
            INNER JOIN nso007_item_stock AS item ON detail.saledetail_itemid = item.id
            INNER JOIN nso007_category_stock AS cat ON item.item_categoryid = cat.id
            INNER JOIN nso007_project_stock AS pro ON item.item_projectid = pro.id 
            $search");
        $total_sale_amount      = 0;
        $total_sale_discount    = 0;
        $total_sale_price       = 0;
        $total_sale_recieve     = 0;
        $total_amount_due       = 0;
        foreach ($client_payment as $key => $value) {
            $total_sale_amount      += $value->sale_amount;
            $total_sale_discount    += $value->sale_discount;
            $total_sale_price       += $value->sale_price;
            $total_sale_recieve     += $value->sale_recieve;
            $total_amount_due       += $value->amount_due;
        }
        $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
        return view('administrator.accounting.receivable-note.report-view-print-receiveable-note-branch', compact('client_payment', 'total_sale_amount', 'total_sale_discount', 'total_sale_price', 'total_sale_recieve', 'total_amount_due', 'branch'));
    }
    public function report_commis()
    {
        $agency = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where('user_status', 2)
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        return view('administrator.agency.commission-report.index', compact('agency'));
    }
    public function get_report_commis()
    {

        $data = DB::table('nso007_agency_commission as commission')
            ->join('nso007_client as client', 'client.id', 'commission.client_id')
            ->leftjoin('nso007_sale_stock as sale', 'sale.id', 'commission.sale_id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'sale.id')
            ->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')
            ->leftjoin('nso007_agency_commission_list as list', 'list.commission_id', 'commission.id')
            ->join('users as agency', 'agency.id', 'list.agency_id')
            ->select(
                'commission.*',
                'sale.id as sale_id',
                'nso007_project_stock.project_name',
                'list.amount',
                'list.receive',
                'client.client_fullname as client_name',
                DB::raw('CONCAT(agency.user_firstname, " ", agency.user_lastname) AS agency_name')
            )
            ->where('commission.status', 1)
            ->where('commission.agency_status', 'Sale')
            ->where([['commission.agency_type', 2], ['sale.commission_request', 3]])
            ->groupBy('commission.id')
            ->groupBy('list.id')
            ->get();

        return Datatables::of($data)
            ->addIndexColumn()
            ->addColumn('item', function ($data) {
                $value = [];
                $items = $data->item_id;
                $items = explode(',', $items);
                foreach ($items as $item) {
                    if ($item != "") {
                        $index = DB::table('nso007_item_stock')->where('id', $item)->first();
                        array_push($value, $index->item_house);
                    }
                }
                return implode(',', $value);
            })
            //            ->addColumn('action', function ($data) {
            //                return '<a class="btn btn-sm btn-outline-success" href="' . route("get-view_commission_group_detail", $data->sale_id) . '" title="View Commission Group"><i class="button_info fa fa-eye"></i></a>';
            //            })
            ->rawColumns(['condition' => 'condition'])
            ->make(true);
    }
    public function get_view_commission_group_detail($id)
    {

        $data = DB::table('nso007_agency_commission_list as list')
            ->leftjoin('nso007_agency_commission as commission', 'list.commission_id', 'commission.id')
            ->join('users as agency', 'agency.id', 'list.agency_id')
            ->join('nso007_client as client', 'client.id', 'commission.client_id')
            ->join('nso007_sale_stock as sale', 'sale.id', 'commission.sale_id')

            ->select(
                'list.*',
                'client.client_fullname as client_name',
                DB::raw('CONCAT(agency.user_firstname, " ", agency.user_lastname) AS agency_name')
            )
            ->where('list.com_list_sale_id', $id)
            ->get();

        return view('administrator.agency.commission-report.form-view-commission', compact('data'));
    }

    public function report_com_network_index()
    {
        $agency = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where('user_status', 2)
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        return view('administrator.agency.commission-network-report.index', compact('agency'));
    }
    public function get_report_com_network_index()
    {

        $data = DB::table('nso007_agency_commission as commission')
            ->join('nso007_client as client', 'client.id', 'commission.client_id')
            ->leftjoin('nso007_sale_stock as sale', 'sale.id', 'commission.sale_id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'sale.id')
            ->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')
            ->leftjoin('nso007_agency_commission_list as list', 'list.commission_id', 'commission.id')
            ->join('users as agency', 'agency.id', 'list.agency_id')
            ->select(
                'commission.*',
                'sale.id as sale_id',
                'nso007_project_stock.project_name',
                'list.amount',
                'list.receive',
                'client.client_fullname as client_name',
                DB::raw('CONCAT(agency.user_firstname, " ", agency.user_lastname) AS agency_name')
            )
            ->where('commission.status', 1)
            ->where('commission.agency_status', 'Sale')
            ->where([['commission.agency_type', 1], ['sale.commission_request', 3]])
            ->groupBy('commission.id')
            ->groupBy('list.id')
            ->get();

        return Datatables::of($data)
            ->addIndexColumn()
            ->addColumn('item', function ($data) {
                $value = [];
                $items = $data->item_id;
                $items = explode(',', $items);
                foreach ($items as $item) {
                    if ($item != "") {
                        $index = DB::table('nso007_item_stock')->where('id', $item)->first();
                        array_push($value, $index->item_house);
                    }
                }
                return implode(',', $value);
            })
            ->editColumn('created_at', function ($data) {
                return date('d-m-Y', strtotime($data->created_at));
            })
            //            ->addColumn('action', function ($data) {
            //
            //                return '<a class="btn btn-sm btn-outline-success" href="' . route("get-view_commission_network_detail", $data->sale_id) . '" title="View Commission Network"><i class="button_info fa fa-eye"></i></a>';
            //            })
            ->rawColumns(['condition' => 'condition'])
            ->make(true);
    }
    public function get_view_commission_network_detail($id)
    {

        $data = DB::table('nso007_agency_commission_list as list')
            ->leftjoin('nso007_agency_commission as commission', 'list.commission_id', 'commission.id')
            ->join('users as agency', 'agency.id', 'list.agency_id')
            ->join('nso007_client as client', 'client.id', 'commission.client_id')
            ->join('nso007_sale_stock as sale', 'sale.id', 'commission.sale_id')

            ->select(
                'list.*',
                'client.client_fullname as client_name',
                DB::raw('CONCAT(agency.user_firstname, " ", agency.user_lastname) AS agency_name')
            )
            ->where('list.com_list_sale_id', $id)
            ->get();

        return view('administrator.agency.commission-network-report.form-view-commission', compact('data'));
    }
    public function store_shared_commission(Request $request)
    {
        // dd($request->all());
        $this->validate($request, [
            'commission_shared' => 'required',
            'agency_id'         => 'required',
            'commission_id'     => 'required',
            'agent_type'        => 'required'
        ]);
        $agencies = $request->agency_id;
        foreach ($agencies as $key => $agency) {
            DB::table('nso007_agency_commission_list')
                ->insert([
                    'commission_id' => $request->commission_id,
                    'agency_id'     => $agency,
                    'commission'    => $request->commission_shared[$key]
                ]);
        }
        DB::table('nso007_agency_commission')->where('id', $request->commission_id)->update([
            'commission_status' => 1
        ]);
        return back()->with('success', 'Commission Shared Sucessfully');
    }
    public function commission_detail($id)
    {
        Session::put('commission_id', Crypt::decrypt($id));
        return view('administrator.agency.commission-report.show');
    }
    public function get_commission_detail()
    {
        $id = Session::get('commission_id');
        $data = DB::table('nso007_agency_commission_list as list')
            ->leftjoin('users', 'users.id', 'list.agency_id')
            ->leftjoin('nso007_agency_commission as commis', 'commis.id', 'list.commission_id')
            ->leftjoin('nso007_loan as loan', 'loan.loan_saleid', 'commis.sale_id')
            ->select(
                'list.*',
                DB::raw('CONCAT(users.user_firstname, " ", users.user_lastname) AS agency_name'),
                'loan.loan_percentpay as percent',
                'commis.condition',
                'commis.commis_condition',
                'list.commission',
                'list.id',
                'list.withdrawal'
            )
            ->where('list.commission_id', $id)->get();
        // dd($data);
        return DataTables::of($data)
            ->addIndexColumn()
            ->addColumn('action', function ($data) {
                $result = $this->check_commission($data->percent, $data->commission, $data->condition, $data->commis_condition);

                $btn_deposit = '<span class="button_info button_circle" title="Commission Is Not Available Now"><i class="fa fa-usd"></i></span>';
                if ($result['status'] == true) {
                    $balance    = $result['balance'] - $data->withdrawal;
                    $balance    = intval(($balance * 100)) / 100;

                    if ($balance > 0) {
                        $btn_deposit = '<span class="button_info button_circle btn-withdraw-commission" data-id="' . Crypt::encrypt($data->id) . '" title="Commission Is Not Available Now"><i class="fa fa-usd"></i></span>';
                    } else {
                        $btn_deposit = '<span class="button_info button_circle" data-balance="' . $balance . '" title="Commission Is Not Available Now"><i class="fa fa-usd"></i></span>';
                    }
                }
                return $btn_deposit;
            })
            ->editColumn('balance', function ($data) {
                $result     = $this->check_commission($data->percent, $data->commission, $data->condition, $data->commis_condition);
                $balance    = $result['balance'] - $data->withdrawal;
                $balance    = intval(($balance * 100)) / 100;
                return $balance;
            })
            ->editColumn('deposit', function ($data) {
                return $data->withdrawal;
            })
            ->make(true);
    }
    public function store_withdrawal_commission(Request $request)
    {
        $this->validate($request, [
            'commission_id' => 'required',
            'amount'        => 'required'
        ]);
        $commission = DB::table('nso007_agency_commission_list')->find(Crypt::decrypt($request->commission_id));
        $agency_id  = $commission->agency_id;
        $fund           = FundsRegisterModel::where('user_id', $agency_id)->first();
        if (!$fund) {
            return back()->with('danger', 'This Agency Doesnt have Fund Account');
        }
        DB::table('nso007_funds')->where('user_id', $agency_id)->update([
            'balance' => DB::raw("balance+$request->amount"),
            'received​_balance' => DB::raw("received​_balance+$request->amount")
        ]);


        $account_number = $fund->account_number;
        FundsTransactionModel::insert([
            'transaction_code'  => "TS-" . date('YmdHis'),
            'operation_type'    => 3,
            'account_number'    => $account_number,
            'user_log'          => Auth::user()->id,
            'remark'            => 'Your Commission Sent to your account',
            'request_date'      => date('Y-m-d H:i:s A'),
            'app_rej_date'      => date('Y-m-d H:i:s A'),
            'status'            => 1,
            'amount'            => $request->amount,
            'balance'           => $fund->balance
        ]);
        DB::table('nso007_agency_commission_list')->where('id', Crypt::decrypt($request->commission_id))
            ->update([
                'withdrawal' => DB::raw("withdrawal+$request->amount")
            ]);
        return back()->with('success', "$request->amount was sent to account number $account_number");
    }
    public function check_commission($percentage, $commission_amount, $condit, $commis)
    {
        $result = ['status' => false, 'balance' => 0];
        $condition_commission = [];
        $condition  = explode('|', $condit);
        $commission = explode('|', $commis);
        //push array to array
        foreach ($condition as $key => $condi) {
            $array1 = [$condi, $commission[$key]];
            array_push($condition_commission, $array1);
        }
        // dd($condition_commission);

        $smallest_array_value   = floatval($condition_commission[0][0]);
        $smallest_array         = $condition_commission[0];
        // dd($condition_commission[0][0][0]);
        $set_of_smallest_array  = [];
        array_push($set_of_smallest_array, $condition_commission[0]);
        // dd($set_of_smallest_array);
        //define an array to catch smallest set of condition
        // array_push($set_of_smallest_array,$condition_commission[0][0]);

        foreach ($condition_commission as $condi_commi) {

            if (floatval($condi_commi[0]) < $smallest_array_value) {
                $smallest_array = $condi_commi;
            }
            if (floatval($condi_commi[0]) <= $percentage) {
                array_push($set_of_smallest_array, $condi_commi);
            }
        }

        // dd($smallest_array);
        if ($percentage >= floatval($smallest_array[0])) {
            // dd(floatval($smallest_array[0]));
            $result['status'] = true;

            //check available amount
            $biggest_smallest_array_value   = $set_of_smallest_array[0][0];

            $biggest_smallest_array         = $set_of_smallest_array[0];

            // dd($biggest_smallest_array);

            foreach ($set_of_smallest_array as $small_set) {

                if ($small_set[0] >= floatval($biggest_smallest_array_value)) {
                    $biggest_smallest_array = [];
                    $biggest_smallest_array = $small_set;
                }
            }

            //check avialable commission amount
            $balance_condition  = floatval($biggest_smallest_array[0]);
            $balance_commission = floatval($biggest_smallest_array[1]);
            $max_condition      = floatval(max($condition));

            if ($balance_commission == 100) {

                $balance = $commission_amount;
            } else {
                $balance = ($commission_amount * $balance_condition) / $max_condition;
            }
            $result['balance'] = $balance;
        }
        // dd($balance);
        return $result;
    }
    public function get_commission_info($id)
    {
        $id = Crypt::decrypt($id);
        $commission = DB::table('nso007_agency_commission as commission')
            ->join('nso007_loan as loan', 'loan.loan_saleid', 'commission.sale_id')
            ->select('commission.*', 'loan.loan_percentpay')
            ->where('commission.id', $id)->first();

        $check = $this->check_commission($commission->loan_percentpay, $commission->commission, $commission->condition, $commission->commis_condition);
        if ($check['status'] == true) {
            $check['status'] = 'Available';
        } else {
            $check['status'] = 'Inavailable';
        }
        $check['balance'] = intval(($check['balance'] * 100)) / 100;
        return $check;
    }
    public function payment_cancellation()
    {
        $projects = ProjectModel::when(empty(\Branch::branch_key()), function ($query) {
            $query->where('branch_id', \Branch::branch_id());
        })
            ->pluck('project_name', 'id')->prepend('Please Select Option', '')->toArray();
        $categories = CategoryModel::pluck('category_name', 'id')->prepend('Please Select Option', '')->toArray();
        $clients = ClientModel::join('nso007_sale_stock', 'nso007_sale_stock.sale_clientid', 'nso007_client.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->pluck('nso007_client.client_fullname', 'nso007_client.id')->prepend('Please Select Option', '')->toArray();
        $agencies = DB::table('users as agency')->join('nso007_sale_stock as sale', 'sale.agency_id', 'agency.id')
            ->select(DB::raw('concat(agency.user_firstname," ",agency.user_lastname) As agencyname'), 'agency.id')
            ->pluck('agencyname', 'id')->prepend('Please Select', '');
        return view('administrator.payment.cancellation.index', compact('projects', 'categories', 'clients', 'agencies'));
    }
    public function get_payment_cancellation(Request $request)
    {
        $client_id      = $request->client_id;
        $client_phone   = $request->client_phone;
        $agency_id      = $request->agency_id;
        $item_id        = $request->item_id;
        $project_id     = $request->project_id;
        $category_id    = $request->category_id;
        $start_date     = $request->start_date;
        $end_date       = $request->end_date;
        $data = DB::table('nso007_loan as loan')
            ->join('nso007_client as client', 'client.id', 'loan.loan_clientid')
            ->join('nso007_sale_stock as sale', 'sale.id', 'loan.loan_saleid')
            ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_saleid', 'sale.id')
            ->join('nso007_item_stock as stock', 'stock.id', 'detail.saledetail_itemid')
            ->leftJoin('users as agent', 'agent.id', 'sale.agency_id')
            ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
            ->join('nso007_category_stock as category', 'category.id', 'stock.item_categoryid')
            ->select(
                'loan.*',
                DB::raw('concat(client.client_firstname," ",client_lastname) As client_name'),
                DB::raw('group_concat(stock.item_house) as item_names'),
                DB::raw('CONCAT(agent.user_firstname, " ", agent.user_lastname) AS agent_name'),
                'project.project_name',
                'category.category_name',
                'stock.item_name',
                DB::raw('SUM(stock.item_price) AS salling_price')
            )
            ->when($client_id, function ($query) use ($client_id) {
                $query->where("client.id", $client_id);
            })
            ->when($client_phone, function ($query, $client_phone) {
                $query->where('client.client_phone1', 'LIKE', "%" . $client_phone . "%");
            })
            ->when($agency_id, function ($query) use ($agency_id) {
                $query->where("agent.id", $agency_id);
            })
            ->when($item_id, function ($query) use ($item_id) {
                $query->where("stock.id", $item_id);
            })
            ->when($project_id, function ($query, $project_id) {
                $query->where("project.id", $project_id);
            })
            ->when($category_id, function ($query) use ($category_id) {
                $query->where("category.id", $category_id);
            })
            ->when(empty(\Branch::branch_key()), function ($q) {
                $q->where('loan.branch_id', \Branch::branch_id());
            })
            ->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(loan.updated_at,'%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(loan.updated_at,'%Y-%m-%d'))"), '>=', date('Y-m-d', strtotime($start_date)));
                } else {
                    $q->where(DB::raw("(STR_TO_DATE(loan.updated_at,'%Y-%m-%d'))"), '<=', date('Y-m-d', strtotime($end_date)));
                }
            })
            ->where('loan.loan_cancel_status', 2)->groupBy('loan.id')->get();
        if ($request->ajax()) {
            return Datatables::of($data)
                ->setRowId('id')
                ->addColumn('details_url', function ($data) {
                    return route('datatable.get_client_payment_process', $data->id);
                })
                ->editColumn('item_names', function ($data) {
                    return SaleStockModel::find($data->loan_saleid)->itemhouse();
                })
                ->editColumn('cancel_date', function ($data) {
                    return date('d-m-Y', strtotime($data->updated_at));
                })
                ->addIndexColumn()
                ->make(true);
        }
        return view('administrator.payment.cancellation.report')->with(['data' => $data, 'branch' => BranchModel::find(\Branch::branch_id())]);
    }
    public function commission_info($sale_id)
    {

        $sale_id        = Crypt::decrypt($sale_id);
        $commissions    = CommissionModel::where('sale_id', $sale_id)->get();
        $tr             = '';
        foreach ($commissions as $i => $commission) {
            if ($commission->agency_type == 2) { //group
                $type = "Group";
            } else { //network
                $type = "Network";
            }
            $commLists = DB::table('nso007_agency_commission_list as list')
                ->join('users', 'users.id', 'list.agency_id')
                ->select(DB::raw('CONCAT(user_firstname," ",user_lastname) AS name'), 'commission', 'amount', 'list.id', 'agency_level as level')
                ->where([['com_list_sale_id', $sale_id], ['commission_id', $commission->id]])
                ->orderBy('agency_level', 'DESC')
                ->get();

            foreach ($commLists as $j => $list) {
                $level = '';

                if (!empty($list->level)) {
                    $level = $list->level;
                }
                $tr .=  '<tr>
                            <td>' . $list->name . '</td>
                            <td>' . $type . '</td>
                            <td><input type="hidden" name="commissionlist[]" value="' . $list->id . '"><input type="text" value="' . $list->amount . '" class="form-control number-only" name="amount[]" required>
                            </td>
                            <td>' . $level . '</td>
                        </tr>';
            }
        }
        return $tr;
    }
    public function updateCommissionList(Request $request)
    {
        // dd($request->all());
        $this->validate($request, [
            'commissionlist'    => 'required',
            'amount'            => 'required'
        ]);
        foreach ($request->commissionlist as $i => $id) {
            CommissionItemModel::where('id', $id)->update([
                'amount'    => $request->amount[$i]
            ]);
        }
        return redirect()->back()->with('success', 'Commission List Updated Successfully');
    }
    public function printPandingPayment(Request $request)
    {
        $start      = $request->start;
        $end        = $request->end;
        $cashier    = $request->cashier;
        $reference  = $request->reference;
        $receipt    = $request->receipt;
        $method     = $request->method;
        $paid_pending = LoanPaidModel::join('nso007_loan_process', 'nso007_loan_process.id', 'nso007_loan_paid.loanpaid_processid')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->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('users', 'users.id', 'nso007_loan_paid.loanpaid_by')
            // ->join('nso007_bank_agency as bank', 'bank.id', 'nso007_loan_paid.loandpaid_bankacc')
            ->select(
                'nso007_loan_paid.id as paid_id',
                'nso007_loan_paid.loanpaid_approved',
                'nso007_loan_paid.loanpaid_invoice',
                'nso007_loan_paid.loanpaid_reference',
                'nso007_loan_paid.loanpaid_remark',
                'nso007_loan_paid.loanpaid_des',
                'nso007_loan_paid.loanpaid_createdate',
                'nso007_loan_paid.loanpaid_principle',
                'nso007_loan_paid.loanpaid_penalty',
                'nso007_loan_paid.loanpaid_penalty_option',
                'nso007_loan_paid.loanpaid_total',
                'nso007_loan_paid.created_at',
                'nso007_loan_paid.loanpaid_payment',
                'nso007_project_stock.project_name',
                'nso007_client.client_fullname',
                'nso007_loan_paid.loandpaid_bankacc',
                // 'bank.name as acc_name',
                DB::raw('CONCAT(users.user_firstname," ",users.user_lastname) As cashier')
            )
            ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_house')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->when($start, function ($query) use ($start, $end) {
                $query->whereBetween('loanpaid_payment', [date('Y-m-d', strtotime($start)), date('Y-m-d', strtotime($end))]);
            })
            ->when($cashier, function ($query, $cashier) {
                $query->where('loanpaid_by', $cashier);
            })
            ->when($receipt, function ($query, $receipt) {
                $query->where('loanpaid_invoice', $receipt);
            })
            ->when($reference, function ($query, $reference) {
                $query->where('loanpaid_reference', $reference);
            })
            ->when($method, function ($query, $method) {
                $query->where('loandpaid_bankacc', $method);
            })
            ->where('nso007_loan_paid.loanpaid_approved', null)
            ->groupBy('nso007_loan_paid.id')
            ->get();
        if ($request->ajax()) {
            return Datatables::of($paid_pending)
                ->addIndexColumn()
                ->addColumn('status', function ($paid_pending) {
                    return '<span class="label label-info fa fa-spinner">Pending</span>';
                })
                ->addColumn('loanpaid_principle', function ($paid_pending) {
                    return '$ ' . number_format($paid_pending->loanpaid_principle, 2);
                })
                ->addColumn('loanpaid_total', function ($paid_pending) {
                    return '$ ' . number_format($paid_pending->loanpaid_total, 2);
                })
                ->editColumn('penalty', function ($paid_pending) {
                    return '$ ' . number_format($paid_pending->loanpaid_penalty, 2);
                })
                ->editColumn('option', function ($paid_pending) {
                    $status = '';
                    if (!empty($paid_pending->loanpaid_penalty_option)) {
                        if ($paid_pending->loanpaid_penalty_option == 1) {
                            $status = '<span class="label label-success">Pay Now</span>';
                        } elseif ($paid_pending->loanpaid_penalty_option == 2) {
                            $status = '<span class="label label-primary">Pass to Next Step</span>';
                        } else {
                            $status = '<span class="label label-default">Wave</span>';
                        }
                    }
                    return $status;
                })
                ->editColumn('method', function ($paid_pending) {
                    $method = "";
                    if($paid_pending->loandpaid_bankacc == 11002) $method = "Cash In Bank";
                    else if($paid_pending->loandpaid_bankacc == 11001) $method = "Cash On Hand";
                    else{}
                    return $method;
                })
                ->addColumn('loanpaid_payment', function ($paid_pending) {
                    return date('d-m-Y', strtotime($paid_pending->loanpaid_payment));
                })
                ->addColumn('check_id', function ($paid_pending) {
                    return '<label class="checkbox-list">
                          <input type="checkbox" class="checkbox" data-id="' . $paid_pending->paid_id . '">
                          <span class="checkmark"></span>
                    </label>';
                })
                ->rawColumns(['check_id' => 'check_id', 'option' => 'option', 'status' => 'status'])->make(true);
        } else {
            $branch = DB::table('nso007_branches')->where('id', \Branch::branch_id())->first();
            return view('administrator.client-payment.report.print-pending-payment', compact('paid_pending', 'branch'));
        }
    }
    public function salereport(Request $request){
        if($request->ajax()){

        }
        return view('administrator.client-payment.payment.sale');
    }
}
