<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
use DB;

class CustomerStatement extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */

    protected $fillable = [
       'surcharge_calculated_by_cron','is_due_date_hide', 'customer_id','customer_property_id', 'payment_head', 'inst_no', 'due_date', 'due', 'received','balance','rcpt_no','ref_no','rcpt_date','rcpt_amount','late_days','surcharge','payment_mode','receipt_id','allotment_date','confirmation_date','development_date','file_processing_date','registration_date','allotment_fee','confirmation_fee','booking_fee','development_fee','file_processing_fee','registration_fee','inst_track','multi_project_id'
    ];

    //Payement Head
    const down_payment = 1;
    const installment = 3;
    const full_payment = 6;
    const other = 7;
    const possession = 2;
    const transfer_fee = 4;
    const allotment = 8;
    const confirmation = 9;
    const booking = 10;
    const development = 11;
    const file_processing = 12;
    const registration = 13;
    const surcharge = 14;
    const ballon = 18;
    const balloting = 19;
    const semi_annual_installment = 20;


    //amount schedule
    const percentage = 1;
    const manual = 2;

    public function receipt() {
        return $this->belongsTo('\App\Receipt','receipt_id')->with('customer');
    }

    public function getDashboardStats($ids=null) {
        if($ids == null){
            $ids[0] = 'all';
        }
        $data = array();
        
         $cancel_properties = CustomerProperty::where('status' , CustomerProperty::cancel)->
         when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
         ->pluck('id');
         
        $data['received-amount'] = Receipt::whereNotIn('customer_property_id' , $cancel_properties)
        ->where('is_approved' , 1)->where('is_deleted',false)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->sum('amount');
        $merge_amount = CustomerAdjustment::where('status',CustomerAdjustment::sold)
        -> when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->join('receipts', 'customer_adjustments.receipt_id', '=', 'receipts.id')
           
            ->where('receipts.multi_project_id', $ids);  
        })
        ->sum('sold_amount');

        $data['received-amount'] += $merge_amount;

        $data['open-adjustment'] = CustomerAdjustment::where('status',CustomerAdjustment::open)
        -> when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->join('receipts', 'customer_adjustments.receipt_id', '=', 'receipts.id')
           
            ->where('receipts.multi_project_id', $ids);  
        })
        ->sum('merge_amount');

        $data['open-adjustment-count'] = CustomerAdjustment::where('status',CustomerAdjustment::open)
        -> when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->join('receipts', 'customer_adjustments.receipt_id', '=', 'receipts.id')
           
            ->where('receipts.multi_project_id', $ids);  
        })
        ->count();

        $data['property-sold'] = CustomerProperty::
        when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })->
        count();
        $data['current-received-amount'] = Receipt::whereMonth('receipt_date', '=', date('m'))
        ->whereNotIn('customer_property_id' , $cancel_properties)
        ->where('is_approved' , 1)->where('is_deleted',false)->
        when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->sum('amount');

        // $data['expense'] = Expense::sum('amount') + DealerPaidRebate::sum('amount');
        // $data['current-expense'] = Expense::whereMonth('billed_at', '=', date('m'))->sum('amount')  + DealerPaidRebate::whereMonth('date', '=', date('m'))->sum('amount');

        //get total Revenue
        $revenue = \App\Http\Controllers\ReportController::getAccountChildSum('Income',Carbon::today(),$ids) * -1;

        //get total CGS 
        $cgs = \App\Http\Controllers\ReportController::getAccountChildSum('Cost of Goods Sold',Carbon::today(),$ids);

        //get Total Expense
        $expense = \App\Http\Controllers\ReportController::getAccountChildSum('Expense',Carbon::today(),$ids);

        $data['earning'] = $revenue - $cgs - $expense;

        
        $data['month'] = Carbon::now()->format('F');
        // $data['current-earning'] = Receipt::whereMonth('receipt_date', '=', date('m'))->whereNotIn('customer_property_id' , $cancel_properties)->where('is_approved' , 1)->where('is_deleted',false)->sum('amount')
        //         - Expense::whereMonth('billed_at', '=', date('m'))->sum('amount')  - DealerPaidRebate::whereMonth('date', '=', date('m'))->sum('amount');

        $data['open_inventory_count'] = Inventory::where('status',Inventory::open)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->count();
        $data['open_inventory_cost'] = Inventory::where('status',Inventory::open)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->sum('total_price');
        $data['sold_inventory_count'] = Inventory::where('status',Inventory::sold)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->count();
        $data['sold_inventory_paid'] = CustomerProperty::where('is_deleted',false)
        ->where('status','!=',CustomerProperty::cancel)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->whereHas('inventory',function($query) {
                $query->where('status',Inventory::sold);

            })->sum('net_pay');

        return $data;
    }

    public function getDueAmount($limit=null,$ids=null) {

        if($ids == null){
            $ids[0] = 'all';
        }
        $data = array();

        $custimer_statemt_ids = CustomerStatement::
       when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })->
        where('due_date','<=',Carbon::now()->endOfMonth())
        ->select(DB::raw("SUM(balance) as amt,customer_property_id"))->having('amt','>',0)
        ->groupby('customer_property_id')->limit($limit)
        ->orderBy('amt','desc')->pluck('customer_property_id');


        if(!$custimer_statemt_ids->isEmpty()){

        $ids_ordered = implode(',', $custimer_statemt_ids->toArray());


        $data['properties'] = CustomerProperty::where('is_deleted',false)
        ->where('status','!=',CustomerProperty::cancel)
        ->when($ids[0] !== 'all', function ($query) use ($ids) {
            $query->whereIn('multi_project_id', $ids);
                            })
        ->whereIn('id',$custimer_statemt_ids)->orderByRaw("FIELD(id, $ids_ordered)")->with('inventory','customer')->get();

        $receivedAmt = array();
        $dueDate = array();
        $total  = array();

        foreach($data['properties'] as $statement) {
            $data['receivedAmt'][$statement->id] = CustomerStatement::where('customer_property_id',$statement->id)
            ->sum('received');
            $data['dueAmt'][$statement->id] = CustomerStatement::where('customer_property_id',$statement->id)->where('due_date','<=',Carbon::now()->endOfMonth())->value(DB::raw("SUM(balance)"));
            $data['dueDate'][$statement->id] = CustomerStatement::where('customer_property_id',$statement->id)->where('due_date','<=',Carbon::now()->endOfMonth())->orderBy('due_date','desc')->first();
            $remainingInstallments = CustomerStatement::where([['due_date','<=',Carbon::now()->endOfMonth()],['customer_property_id',$statement->id],['balance','>',0]])->count();
            if($remainingInstallments)
            {
                if($remainingInstallments >=3 )
                {
                    $data['color'][$statement->id] = 'red';
                }
                elseif($remainingInstallments >=2 ) {
                    $data['color'][$statement->id] = 'orange';
                }
                else
                    $data['color'][$statement->id] = 'black';

            }
            else
                $data['color'][$statement->id] = 'black';

             $data['dueMonth'][$statement->id] = CustomerStatement::where([['due_date','<=',Carbon::now()->endOfMonth()],['customer_property_id',$statement->id],['balance','>',0]])->get();

             $data['total'][$statement->id] = CustomerStatement::where([['due_date','<=',Carbon::now()->endOfMonth()],['customer_property_id',$statement->id],['balance','>',0]])->count();

        }
    }
    else
        $data['properties']=[];
    
        return $data;
    }

    public function updateTable($filters,$id=null) {

        $query = CustomerProperty::where('is_deleted',false)
        ->when($id[0] !== 'all', function ($query) use ($id) {
            $query->whereIn('multi_project_id', $id);
        })
        ;

        foreach($filters as $key => $val) 
        {
            if($key == 'status')
                $query = $query->where('status',$val);
            elseif($key == 'unit')
                $query = $query->whereHas('inventory',function($query) use($val){
                    $query->where('id',$val);
                });
            elseif($key == 'customer'){

            
                $query = $query->whereHas('customer', function ($query) use ($val) {
                    $query->where('id', $val);
                })->orWhereHas('secondaryCustomer', function ($query) use ($val) {
                    $query->where('customer_id', $val);
                });
                
            }
               
              
               
            elseif($key == 'dealer')
                $query = $query->whereHas('dealer',function($query) use($val){
                        $query->where('id',$val);
                      });
            elseif($key == 'unit_size')
                $query = $query->whereHas('inventory.size',function($query) use($val){
                    $query->where('id',$val);
                });
            elseif($key == 'unit_type')
                $query = $query->whereHas('inventory.type',function($query) use($val){
                    $query->where('id',$val);
                });

             elseif($key == 'unit_block')
                $query = $query->whereHas('inventory.floor.unitBlock',function($query) use($val){
                    $query->where('id',$val);
                });
                


                
        }

        $query->with('inventory','customer','dealer');
        $data['allStatements'] = $query->get();

        $receivedAmt = array();
        if (in_array('development_charges', $filters)){
            foreach($data['allStatements'] as $statement) 
            {
                
               
                $receivedAmt[$statement->id] =    CustomerStatement::where('customer_property_id', $statement->id)->where('is_deleted', false)->where('payment_head',CustomerStatement::development)->sum('received');
            }
}
else{
         foreach($data['allStatements'] as $statement) 
        {
            
            $receivedAmt[$statement->id] = Receipt::where('customer_property_id' , $statement->id)->where('is_approved' , 1)->where('is_deleted',false)->sum('amount');
            // CustomerStatement::where([['customer_property_id',$statement->id],['is_deleted',false]])->sum('received');
        }
    }
        $data['receivedAmt'] = $receivedAmt;

        return $data;
    }

    public function updateTableForCancel($filters,$id=null) {

        $query = CustomerProperty::where('is_deleted',false)
        ->when($id[0] !== 'all', function ($query) use ($id) {
            $query->whereIn('multi_project_id', $id);
        })
        ;

        foreach($filters as $key => $val) 
        {
            if($key == 'customer')
                $query = $query->whereHas('customer',function($query) use($val){
                    $query->where('id',$val);
                });
            elseif($key == 'dealer')
                $query = $query->whereHas('dealer',function($query) use($val){
                        $query->where('id',$val);
                      });
           
                


                
        }

         
   
    

        return $data;
    }

}
