<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\DB;
use App\Booking;
use EF;

class Installment extends Model
{

    private $generic_condition;
  

    public function __construct()
    {
           
       $this->setCondition(['is_recycle' => '0']);
    }

    public function setCondition($condition = [])
    {
        $this->generic_condition = $condition;
    }

    public function getCondition()
    {
        return $this->generic_condition;
    }

    public function getInstallmentByType($booking_id,$whereIn)
    {

      $condition = ['booking_id' => $booking_id];

      $condition += $this->getCondition();

      return Installment::where($condition)->whereIn('type_of_installment',$whereIn)->orderBy('id','asc')->get();
    }

     public function removeBulkInstallment($booking_id)
    {
        $condition = ['booking_id' => $booking_id] + $this->getCondition();

        return Installment::where($condition)->update(['is_recycle' => 1]);
    }

    public function sumInstallmentByBooking($booking_id)
    {

      $condition = ['booking_id' => $booking_id];

      $condition += $this->getCondition();

         $result = (Installment::where($condition)->where('received_amount','>',0)->selectRaw(' SUM(amount - received_amount) as balance'))->first();

         return $result->balance;
    }

    

   

    public function getInstallment($new_condition = [])
    {

      $conditions = $this->getCondition();

      $condition += $new_condition;


      return Installment::where($condition)->orderBy('due_date','asc')->get();
    }

    public function getInstallmentsByColoumn($column)
    {
        return Installment::where($this->getCondition())->distinct($column)->orderBy($column,'asc')->get([$column]);
    }
   public function getBookingInstallmentByType($booking_id,$type)
   {
      $condition = $this->getCondition();
      $condition += ['booking_id' => $booking_id ,'type_of_installment' => $type];
      return Installment::where($condition)->orderBy('id','desc')->get();
   }


    public function getInstallmentByStauts($booking_id,$statusIn = ['Unpaid'],$order = 'desc')
    {
      $condition = $this->getCondition();

      $condition += ['booking_id' => $booking_id];

      return Installment::where($condition)->whereIn('status',$statusIn)->orderBy('due_date',$order)->get();
    }

    public function getInstallmentById($id,$column = "*")
    {
      $condition = $this->getCondition();
      $condition += ['id' => $id];
      return Installment::where($condition)->with(['booking'])->select($column)->first();

    }
    public function getBookingInstallments($id)
    {
   
        $condition = $this->getCondition();
        $condition += ['booking_id' => $id];

        return Installment::where($condition)->orderBy('due_date','asc')->get();
    }

public function SumofShortAmount($booking_id)
   {

        $condition = $this->getCondition();
          $condition += ['booking_id' => $booking_id];

         $short = (Installment::where($condition)->where('received_amount','>',0)->selectRaw(' SUM(amount - received_amount) as balance'))->first();

         return $short->balance;


   }
    public function storePlan($object)
    {
                
        return \DB::transaction(function () use ($object)  
        {
            $booking = Booking::find($object['booking_id']);
             if(isset($booking->id))
            {
                if($booking->plan_type == "")
                {
                  $booking->plan_type = $object['plan_type'];
                  
                }
                $booking->installment_mode = $object['installment_mode'];
                $booking->update();
          
               for ($i = 0; $i < count($object['amount']) ; $i++)
               {
                  if($object['amount'] > 0)
                  {
                      $plan = new Installment;

                       $plan->booking_id          = $booking->id;

                       $plan->user_id             = $booking->user_id;

                       $plan->branch_id           = $booking->branch_id;

                       $plan->type_of_installment = $object['payment_type'][$i];

                       $plan->description         = $object ['description'][$i];

                       $plan->actual_amount       = $object['amount'][$i];

                       $plan->amount              = $object['amount'][$i];


                       $plan->due_date            = date('Y-m-d',strtotime($object['date'][$i]));


                       $plan->save();
                  }
                   

               }
               return 1;

           }
          return 0;


        });   

    }


    public function updatePlanById($object)
    {
                
        return \DB::transaction(function () use ($object)  
        {
            $installment = Installment::find($object['id']);

             if(isset($installment->id))
            {

                 $installment->description         = $object ['description'];

                 $installment->amount              = $object['amount'];


                 $installment->due_date            = date('Y-m-d',strtotime($object['due_date']));

                $installment->update();

                return with($installment);
              }
              else
              {

               return 0;
              }

        });           

    }

   public function removeInstallment($id)
    {
        return \DB::transaction(function () use ($id) {

            $installment = Installment::find($id);

            if(isset($installment->id))
            {
                $installment->is_recycle   = '1';    

                $installment->update();

                
            }
            
            return with($installment);
        });
    }

    public function verificationPlan($booking_id)
    {

            $booking = new Booking;

            $booking = $booking->getBookingById($booking_id);

            $remaining_plan = 0;


            if(isset($booking->id))
            {
                $installment_sum  = $this->sumOfInstallments($booking_id);

                $remaining_plan = ($booking->agreed_amount - $installment_sum);  

                             
            }

            return $remaining_plan;

    }
    public function updateVerificationPlan($installment_id)
    {

            $installment = new Installment;

            $installment = $installment->getInstallmentById($installment_id);

            $remaining_plan = 0;


            if(isset($installment->id))
            {
              $booking = new Booking;

              $booking = $booking->getBookingById($installment->booking_id);
              $condition = $this->getCondition();
              $condition += ['booking_id' => $booking->id];

              $installment_sum = Installment::where($condition)->where('id','!=',$installment_id)->sum('amount');
      

                $remaining_plan = ($booking->agreed_amount - $installment_sum);                
            }

            return $remaining_plan;

    }

    public function sumOfInstallments($booking_id)
    {
    
      $condition = $this->getCondition();
      $condition += ['booking_id' => $booking_id];

      return Installment::where($condition)->sum('amount');


    }

    public function sumPaidBalance($booking_id,$status)
    {

       $condition = $this->getCondition();
       $condition += ['booking_id' => $booking_id ,'status' => $status];
        return Installment::where($condition)->selectRaw('sum(amount-received_amount) as paidbalance')->value('paidbalance');
    }
    //GET INSTALLMENT RECEIVED AMOUNT
    public function sumOfReceivedInstallments($booking_id)
    {

      $condition = $this->getCondition();
      $condition += ['booking_id' => $booking_id];

      return Installment::where($condition)->sum('received_amount');


    }
   //GET OVER DUE BY DATE
    public function getOverDue($date,$operator)
    {
      return Installment::where($this->getCondition())->whereDate('due_date',$operator,$date)->orderBy('due_date','asc')->get();

    }


    public function getBookingWiseOverDue($date,$operator)
    {
   
      $condition = $this->getCondition();
      $condition += ['status' => 'Unpaid'];


      return Installment::groupBy('booking_id')->where($condition)
            ->whereDate('due_date',$operator,$date)
            ->selectRaw('booking_id, SUM(amount) - SUM(received_amount) as balance')->get();         
    }

    public function getLatestDueDate($date,$operator,$booking_id,$order = 'desc')
    {
         $condition  = $this->getCondition();
         $condition += ['status' => 'Unpaid','booking_id'=>$booking_id];
         return Installment::where($condition)->whereDate('due_date',$operator,$date)->orderBy('due_date',$order)->value('due_date');
    }

    public function getLatestInstallmentDue($date,$operator,$booking_id,$order = 'desc')
    {
         $condition  = $this->getCondition();
         $condition += ['status' => 'Unpaid','booking_id'=>$booking_id];
         return Installment::where($condition)->whereDate('due_date',$operator,$date)->orderBy('due_date',$order)->first();
    }

    public function getCurrentInstallmentDue($year,$month,$booking_id,$order = 'desc')
    {
         $condition  = $this->getCondition();
         $condition += ['status' => 'Unpaid','booking_id'=>$booking_id];
         return Installment::where($condition)->whereYear('due_date',$year)->whereMonth('due_date',$month)->orderBy('due_date',$order)->first();
    }



    public function getTypeWiseLatestDueDate($date,$operator,$booking_id,$type,$order = 'desc')
    {

   
       $condition  = $this->getCondition();
         $condition += ['status' => 'Unpaid','booking_id'=>$booking_id,'type_of_installment'=> $type];
        return Installment::where($condition)->whereDate('due_date',$operator,$date)->orderBy('due_date',$order)->value('due_date');
    }


    public function getOverDueByBooking($booking_id,$date = '2199-01-01')
     {
       $condition  = $this->getCondition();
       $condition += ['booking_id' => $booking_id];
       return Installment::where($condition)->where('due_date','<=',$date)->selectRaw(' SUM(amount) - SUM(received_amount) as balance')
       ->first();

    }

    
    public function rejectVoucherInstallment($payment){
      if(isset($payment)){
          $installment = Installment::find($payment->installment_id);
          if(isset($installment)){

            $installment->received_amount-=$payment->amount;
            if($installment->received_amount < $installment->amount){
              $installment->status = "Unpaid"; 
            }
            $installment->update();
          }
          return 1;
      }
    }



      public function storeVoucherInstallment($payment){
      if(isset($payment)){
          $installment = Installment::Find($payment->installment_id);
          if(isset($installment)){
            $installment->received_amount+=$payment->amount;
            if($installment->received_amount < $installment->amount){
              $installment->status = "Unpaid"; 
            }
            $installment->update();
        }
          return 1;
      }
    }
    

    public function updateInstallmentByPayment($id,$new_amount,$old_amount)
    {
   
      return \DB::transaction(function () use ($id,$new_amount,$old_amount)  {

        $data = ['remaining_amount' => 0,'collect_amount' => 0, 'installment' => false];

        $installment = Installment::find($id);

  
        if(isset($installment->id))
        {

            // FOR REBACK INSTALLMENT
            if($old_amount > 0)
            {
               $installment->received_amount -= $old_amount;
            }
             
            // FOR NEWER INSTALLMENT
            if($new_amount > 0)
            {
              $collect_install_amount = $installment->amount - $installment->received_amount;
               
              // ACC TO PAY MULTIPLE INSTALLMENT STRUCTURE TO DEAL INSTALLMENTS
               if($collect_install_amount > 0 && $new_amount <= $collect_install_amount)
               {
                   $installment->received_amount += $new_amount;

                   $data['collect_amount'] = $new_amount;
               }
               else if($collect_install_amount > 0 && $new_amount > $collect_install_amount)
               {
                   $installment->received_amount += $collect_install_amount;

                   $data['collect_amount'] = $collect_install_amount;

                   $data['remaining_amount'] = abs($collect_install_amount - $new_amount);
               }

               
            }
          
            // THIS IF FIND INSALLMENT IS COMPLETED PAID OR NOT
            if($installment->received_amount == $installment->amount)
            {
                $installment->status = 'Paid';
            }
            else
            {
                $installment->status = 'Unpaid';
            }


            $installment->update();

            $data['installment'] = $installment;


            return with($data);
        }

      });
        
    }

    public function installmentReport($from,$to,$installment_condition = [])
    {

        return Installment::join('bookings','bookings.id','installments.booking_id')

        ->join('plots','plots.id','bookings.plot_id')

        ->join('users','users.id','bookings.user_id')

        ->whereDate('installments.due_date','>=',$from)

        ->whereDate('installments.due_date','<=',$to)

        ->where($installment_condition + ['bookings.is_recycle' => 0])

        ->where(['installments.is_recycle' => 0])

       
        ->groupBy('booking_id','users.name','users.contact','bookings.ms','bookings.discount','bookings.agreed_amount','plots.plot_id','plots.block_name','plots.category','plots.size')
        
        ->selectRaw('users.name,users.contact,bookings.ms,bookings.discount,bookings.agreed_amount,plots.plot_id,plots.block_name,plots.category,plots.size,plots.category,booking_id,(SUM(amount) - SUM(received_amount)) AS balance')

        ->get();

    }
    public function completeInstallmentReport($from,$to,$installment_condition = [])
    {
        $installment_condition += ['bookings.status' => 'Approve'];

        return Installment::join('bookings','bookings.id','installments.booking_id')

        ->join('plots','plots.id','bookings.plot_id')

        ->join('users','users.id','bookings.user_id')

        

        ->whereDate('installments.due_date','>=',$from)

        ->whereDate('installments.due_date','<=',$to)

        ->where($installment_condition + ['bookings.is_recycle' => 0])

        ->where(['installments.is_recycle' => 0])

        ->groupBy('booking_id','users.name','users.contact','bookings.ms','bookings.discount','bookings.agreed_amount','plots.plot_id','plots.block_name','plots.category','plots.size')
        
        ->selectRaw('users.name,users.contact,bookings.ms,bookings.discount,bookings.agreed_amount,plots.plot_id,plots.block_name,plots.category,plots.size,plots.category,booking_id,(SUM(amount) - SUM(received_amount)) AS balance')

        ->get();

    }
//THIS FUNCTION IS USED TO CALCULATE OVERDUE AGAINST PAYMENT TYPE(ADVANCE,CONFIRMATION,POSESSION ETC)
    public function getTypeWiseDue($booking_id,$payment_type,$s_date= '',$e_date='')
    {

      $s_date = str_replace('1970-01-01','1970-01-01',date('Y-m-d',strtotime($s_date)));

      $e_date = str_replace('1970-01-01','2099-01-01',date('Y-m-d',strtotime($e_date)));

      $condition = $this->getCondition();

      $condition += ['booking_id' => $booking_id , 'type_of_installment' => $payment_type];

      return Installment::where($condition)->where('due_date','>=',$s_date)->where('due_date','<=',$e_date)->selectRaw('(SUM(amount) - SUM(received_amount)) as balance')
               ->value('balance');
    }

    public function getTypeWiseDueDate($s_date,$e_date,$booking_id,$type,$order = 'desc')
    {
      $s_date = str_replace('1970-01-01','1970-01-01',date('Y-m-d',strtotime($s_date)));

      $e_date = str_replace('1970-01-01','2099-01-01',date('Y-m-d',strtotime($e_date)));
   
       $condition  = $this->getCondition();
         $condition += ['status' => 'Unpaid','booking_id'=>$booking_id,'type_of_installment'=> $type];

        return Installment::where($condition)->whereDate('due_date','>=',$s_date)->whereDate('due_date','<=',$e_date)->orderBy('due_date',$order)->value('due_date');
    }

    public function getOverDueByIntervalBranchwise($start_date,$end_date,$branch_ids = [])
    {
      return Installment::where($this->getCondition())->whereDate('due_date','>=',$start_date)->whereDate('due_date','<=',$end_date)->branchwise($branch_ids)->orderBy('due_date','asc')->get();

    }

    public function getOverDueBranchwise($date,$operator,$branch_ids =[])
    {
      return Installment::where($this->getCondition())->whereDate('due_date',$operator,$date)->orderBy('due_date','asc')->branchwise($branch_ids)->get();
    }

    public function scopeBranchwise($query,$branch_ids)
    {
        if(in_array(0,$branch_ids))
        {
            $branch_ids = Branch::where($this->getCondition())->pluck('id')->toArray();
        }

        return $query->whereIn('branch_id',$branch_ids);
    }

    public function getBookingWiseOverDueBranchwise($date,$operator,$branch_ids = [])
    {

      $condition = $this->getCondition();
      $condition += ['status' => 'Unpaid'];


      return Installment::groupBy('booking_id')->where($condition)->branchwise($branch_ids)
            ->whereDate('due_date',$operator,$date)
            ->selectRaw('booking_id, SUM(amount) - SUM(received_amount) as balance')->get();
    }


    public function booking()
    {
        return $this->belongsTo(Booking::class,'booking_id')->where($this->getCondition())->withDefault();
    }

    public function user()
    {
        return $this->belongsTo(User::class,'user_id')->withDefault();
    }

    public function massRemoveInstallment($object)
    {
        return DB::transaction(function () use ($object)  {

            Installment::where()->remove($object);
        });
    }


     public function getDueInstallmentReport($from,$to,$project_id = [] , $booking_id = [])
    {
           
        $query = Installment::whereDate('due_date', '<=' , EF::dateHtml(today() ) )->where('status', 'Unpaid');
   
        if(@count($project_id) > 0)
        {
            $query = $query->whereIn('branch_id',[$project_id]);
        }
      

        if(strlen(trim($from)) > 0)
        {
            $query = $query->whereDate('created_at','>=',EF::dateHtml($from));
        }

        if(strlen(trim($to)) > 0)
        {
            $query = $query->whereDate('created_at','<=',EF::dateHtml($to));
        }

        return $query->get();
    }

}
