<?php

namespace App\Repository;
use App\Helpers\Helper;
use App\Models\AccReceivableDetails;
use App\Models\AccReceivables;
use App\Models\Holiday;
use App\Models\Loan;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;


class ClientCollectionRepository
{
    public static function accCollection()
  {
     $data = AccReceivables::where(function($query) {
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('payment_status_id',[2,3]);
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
            if(!request()->client_id ==[]){
                $query->whereIn('client_id',request()->client_id);
            }
        })
        ->whereHas('loan',function($query) {
            $query->whereIn('loan_status_id',[2,5]);
            $query->where('client_type',1);
            if(!request()->term_type ==[]){
                $query->whereIn('term_type',request()->term_type);
            }
            if(!request()->term_condi ==[]){
                $query->whereIn('term_condi',request()->term_condi);
            }
            if(!request()->condition_pay ==[]){
                $query->whereIn('condition_pay',request()->condition_pay);
            }
        })
        ->when(request()->date, function($query){
          $query->whereBetween(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"), request()->date);
        })
        ->when(request()->date ==[], function($query){
          $query->where('payment_date',Carbon::now()->format('Y-m-d'));
        })
        ->orderBy('payment_date','asc')
        ->orderBy('id','asc')
        ->groupBy('co_id');
        return $data;
  }

  public static function accDetails(){
     $data = AccReceivableDetails::leftjoin('acc_receivables','acc_receivables.id','acc_receivable_details.acc_id')

    //  ->where(function($qeury){
    //         $qeury->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),date('Y-m-d'));
    //     })
     ->when(request()->date, function($query){
          $query->whereBetween(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"), request()->date);
        })
        ->when(request()->date ==[], function($query){
          $query->where('acc_receivables.payment_date',Carbon::now()->format('Y-m-d'));
        })
        ->whereNull('acc_receivable_details.deleted_at')
        ->whereIn('acc_receivables.payment_status_id',[2,3])
        ->groupBy('acc_receivables.co_id');
        return $data;
  }



  public static function getPrincipalCollection($co_id){

      $acc_principal = self::accCollection()->where('co_id',$co_id)
       ->sum('principal');

      $acc_de_principal_paid = self::accDetails()->where('acc_receivables.co_id',$co_id)
       ->sum('acc_receivable_details.paid_principal');

       return $acc_principal - $acc_de_principal_paid ;
  }

  public static function getInterestCollection($co_id){

      $acc_interest = self::accCollection()->where('co_id',$co_id)
       ->sum('interest_per_month');

      $acc_de_interest_paid = self::accDetails()->where('acc_receivables.co_id',$co_id)
       ->sum('acc_receivable_details.paid_interest');

       return $acc_interest - $acc_de_interest_paid ;
  }

  public static function getOperServiceCollection($co_id){

      $acc_oper = self::accCollection()->where('co_id',$co_id)
       ->sum('operating_service');

      $acc_de_oper_paid = self::accDetails()->where('acc_receivables.co_id',$co_id)
       ->sum('acc_receivable_details.paid_operating_service');

       return $acc_oper - $acc_de_oper_paid ;
  }

   public static function getUnreceiveCollection($co_id){

      return self::accCollection()->where('co_id',$co_id)
       ->sum('unreceived');
   }

   public static function getCountClientCollection($co_id){

      return self::accCollection()->where('co_id',$co_id)
       ->count('client_id');
   }
}



