<?php
namespace App\Services\Reports\Account;

use App\Advansur;
use App\Cawangan;
use App\Gadai;
use App\GlCode;
use App\GlDetail;
use App\imbangdugabackup;
use App\imbangdugalama;
use App\Lelong;
use App\Panjar;
use App\Tebus;
use App\Transaction;
use App\TransaksiKeuntungan;
use App\WTD;
use App\AkruNet;
use Carbon\Carbon;

class GeneralLedger
{
    protected $kodcaw;
    protected $mula;
    protected $hingga;

    public function __construct($kodcaw,$mula,$hingga){
        $this->kodcaw = $kodcaw;
        $this->mula = $mula;
        $this->hingga = $hingga;
    }

    private function getTarikhTawarruq($kodcaw){
        switch ($this->kodcaw) {
            case 'KB2':
                $tarikhtawarruq = '2021-04-13';
            break;
            case 'WCY':
                $tarikhtawarruq = '2021-05-04';
            break;
            case '1STOP':
                $tarikhtawarruq = '2021-05-09';
            break;
            case '020301':
                $tarikhtawarruq = '2021-05-18';
            break;
            case 'ALS':
                $tarikhtawarruq = '2021-05-24';
            break;
            case 'WB':
                $tarikhtawarruq = '2021-05-24';
            break;
            case 'APM':
                $tarikhtawarruq = '2021-06-08';
            break;
            case 'ARM':
                $tarikhtawarruq = '2021-06-08';
            break;
            case '020306':
                $tarikhtawarruq = '2021-06-08';
            break;
            case '010311':
                $tarikhtawarruq = '2021-06-13';
            break;
            case 'GC':
                $tarikhtawarruq = '2021-06-13';
            break;
            case 'JLI':
                $tarikhtawarruq = '2021-06-13';
            break;
            case '010621':
                $tarikhtawarruq = '2021-06-15';
            break;
            case 'ARP':
                $tarikhtawarruq = '2021-06-15';
            break;
            case '010621':
                $tarikhtawarruq = '2021-06-21';
            break;
            case '010136':
                $tarikhtawarruq = '2021-06-20';
            break;
            case '011235':
                $tarikhtawarruq = '2021-06-21';
            break;
            case '011227':
                $tarikhtawarruq = '2021-06-21';
            break;
            case '010620':
                $tarikhtawarruq = '2021-06-21';
            break;
            case '010617':
                $tarikhtawarruq = '2021-06-21';
            break;
            case '010915':
                $tarikhtawarruq = '2021-06-22';
            break;
            case '011228':
                $tarikhtawarruq = '2021-06-22';
            break;
            case '010219':
                $tarikhtawarruq = '2021-06-22';
            break;
            case '011230':
                $tarikhtawarruq = '2021-06-23';
            break;
            case '011229':
                $tarikhtawarruq = '2021-06-22';
            break;
            case '011013':
                $tarikhtawarruq = '2021-06-23';
            break;
            case '011412':
                $tarikhtawarruq = '2021-06-23';
            break;
	        case '011234':
                $tarikhtawarruq = '2021-06-28';
            break;
            case '020303':
                $tarikhtawarruq = '2021-06-29';
            break;
            case '010618':
                $tarikhtawarruq = '2021-06-29';
            break;
            case '011231':
                $tarikhtawarruq = '2021-06-23';
            break;
            case '011232':
                $tarikhtawarruq = '2021-06-28';
            break;
            case '011233':
                $tarikhtawarruq = '2021-06-28';
            break;
          }

          return $tarikhtawarruq;
    }

    public function getPinjamanDiberi()
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();        

        $gadai = Gadai::on($cawangan['mysql'])
            ->where('tagpalsu','=',0)
            ->where([['t_gadai','>=',$this->mula],['t_gadai','<=',$this->hingga]])
            ->sum('pinjaman');

        $tebus = Gadai::on($cawangan['mysql'])
            ->join('tebus as teb','teb.norujukan','=','gadai.norujukan')
            ->where('gadai.sttebus','=','T')
            ->where([['teb.t_tebus','>=',$this->mula],['teb.t_tebus','<=',$this->hingga]])
            ->sum('teb.pinjaman');

       $pembiayaanlelong = Lelong::on($cawangan['mysql'])
            ->join('gadai as gad','gad.norujukan','=','lelong.norujukan')
            ->where([['t_jual','>=',$this->mula],['t_jual','<=',$this->hingga]])
            ->sum('lelong.pinjaman');

        $tebus = $tebus + $pembiayaanlelong;

        $array_rekodemas = ['Gadai' => $gadai,'Tebus' => $tebus];

        return $array_rekodemas;
    }

    public function getPembiayaanTawarruq()
    {

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();

        $Gadai = Gadai::on($cawangan['mysql'])->where([['t_gadai','>=',$this->mula],['t_gadai','<=',$this->hingga],['hargajualan','<>','NULL']])->sum('pinjaman');

        $Tebus = Gadai::on($cawangan['mysql'])
            ->where('gadai.tagpalsu','=',0)
            ->where([['gadai.t_update','>=',$this->mula],['gadai.t_update','<=',$this->hingga],['gadai.hargajualan','<>','NULL']])
            ->whereIn('gadai.sttebus', ['T', 'L'])
            ->sum('gadai.pinjaman');


            $rekod_pinjamandiberi = ['Gadai' => $Gadai,'Tebus' => $Tebus];

        return $rekod_pinjamandiberi;
    }

    public function getBayaranPendahuluan()
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();

        $datemula = new Carbon($this->mula);
        $datemula->addDays(1);

        $datehingga = new Carbon($this->hingga);
        $datehingga->addDays(1);

        $transaksi_data = TransaksiKeuntungan::on('mysql7')
                            ->whereDate('tarikh_bayaran','<=', $this->hingga)
                            ->whereDate('tarikh_bayaran','>=', $this->mula)
                            ->where('kodcaw',$this->kodcaw)
                            ->pluck('norujukan');

        $transaksi_kredit_gadai =  Gadai::on($cawangan['mysql'])
                            ->whereIn('norujukan',$transaksi_data)
                            ->where([['hargajualan','<>','null']])
                            ->pluck('norujukan');

        $transaksi_kredit = TransaksiKeuntungan::on('mysql7')
	      ->leftJoin('transaction as trans', function($join)
                            {
                                $join->on('transaksi_keuntungan.norujukan', '=', 'trans.norujukan');
                                $join->on('transaksi_keuntungan.tarikh_bayaran', '=', 'trans.created_at');
                            })
            ->whereIn('transaksi_keuntungan.norujukan',$transaksi_kredit_gadai)
            ->where('trans.trans_type','=','A')
            ->whereDate('tarikh_bayaran','<=', $this->hingga)
            ->whereDate('tarikh_bayaran','>=', $this->mula)
            ->sum('bayaran_pembiayaan');


        $tebus = Gadai::on($cawangan['mysql'])
                    ->select('gadai.norujukan','gadai.hargajualan','gadai.bakihargajualan')
                    ->whereIn('gadai.sttebus',['T','L'])
                    ->where([['t_update','>=',$this->mula],['t_update','<=',$this->hingga]])
                    ->groupBy('gadai.norujukan')
                    ->get();

        $arraytebus = array();

        foreach($tebus as $index => $t)
        {
            if($t['hargajualan'] != $t['bakihargajualan'])
            {
                array_push($arraytebus,$t['norujukan']);
            }
        }

        $transaksi_debit = TransaksiKeuntungan::on('mysql7')
                            ->leftJoin('transaction as trans', function($join)
                            {
                                $join->on('transaksi_keuntungan.norujukan', '=', 'trans.norujukan');
                                $join->on('transaksi_keuntungan.tarikh_bayaran', '=', 'trans.created_at');
                            })
                            ->where('trans.trans_type','=','A')
                            ->whereIn('trans.norujukan',$arraytebus)
                            ->sum('bayaran_pembiayaan');

        $arraylejer = ["keluar" => $transaksi_debit,"masuk" => $transaksi_kredit];

        return $arraylejer;
    }

    public function getPendahuluanSerahan()
    {
       $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();
 
       $serahanpkb = '1000/020';
       $serahanjmc = '1000/026';
       $pendahuluanpkb = '7000/102';
       $pendahuluanjmc = '7000/106';
       $serahankoperasi = '1000/090';
 
       //Selepas Tawarruq
 
       $serahanpkbTawarruq = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=',$serahanpkb)
          ->where('tarikh','>=', $this->mula)
          ->where('tarikh','<=', $this->hingga)
          ->where('tarikh','>','2021-03-24')
          ->sum('serahan');
 
       //Sebelum Tawarruq
       if($this->kodcaw == 'KB2' || $this->kodcaw == 'WCY' || $this->kodcaw == '1STOP' || $this->kodcaw == 'ARM' || $this->kodcaw == 'APM' || $this->kodcaw == '010621' || $this->kodcaw == '010915')
       {
          $serahanpkb = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanpkb)
             ->where('tarikh','>=', $this->mula)
             ->where('tarikh','<=', $this->hingga)
             ->where('tarikh','>','2013-12-31')
             ->sum('amaun');
 
          $serahanJMC = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanjmc)
             ->where('tarikh','>=', $this->mula)
             ->where('tarikh','<=', $this->hingga)
             ->where('tarikh','>','2013-12-31')
             ->sum('amaun');
 
 
          if($this->mula <= '2013-12-31')
          {
             $serahanpkblama = imbangdugalama::on($cawangan['mysql'])
                ->sum('serahan_pkb');
 
             $serahanjmclama = imbangdugalama::on($cawangan['mysql'])
                ->sum('serahan_jmc');
 
                $totaljmc = $serahanJMC + $serahanjmclama;
                $totalpkb = $serahanpkb + $serahanpkblama;
       
                $totalserahan = $serahanpkbTawarruq + $totaljmc + $totalpkb;
          }else{
 
             $totaljmc = $serahanJMC;
             $totalpkb = $serahanpkb;
    
             $totalserahan = $serahanpkbTawarruq + $totaljmc + $totalpkb;
          }
         
        
       }else{
 
          if($this->kodcaw == '020301')
             $serahanbaru = '1000/030';
          else
             $serahanbaru = '1000/026';
 
          $serahanpkb = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanpkb)
                      ->where('tarikh','>=', $this->mula)
                      ->where('tarikh','<=', $this->hingga)
             ->sum('amaun');
 
          $serahanJMC = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanbaru)
                      ->where('tarikh','>=', $this->mula)
                      ->where('tarikh','<=', $this->hingga)
             ->sum('amaun');
 
          $totalserahan = $serahanpkbTawarruq + $serahanpkb + $serahanJMC;
       }
 
       //Selepas Tawarruq
 
       $pendahuluanPKBtawarruq = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=',$pendahuluanpkb)
          ->where([['tarikh','>=', $this->mula],['tarikh','<=', $this->hingga],['tarikh','>','2021-03-24']])
          ->sum('pendahuluan');
       
       //Sebelum Tawarruq
 
       $pendahuluanPKB = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=',$pendahuluanpkb)
          ->where([['tarikh','>=', $this->mula],['tarikh','<=', $this->hingga],['tarikh','>','2013-12-31']])
          ->sum('amaun');
 
       $pendahuluanJMC = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=','7000/106')
          ->where([['tarikh','>=', $this->mula],['tarikh','<=', $this->hingga],['tarikh','>','2013-12-31']])
          ->sum('amaun');
 
       if($this->mula <= '2013-12-31')
       {
          $pendahuluanpkblama = imbangdugalama::on($cawangan['mysql'])
          ->sum('pendahuluan_pkb');
 
          $pendahuluanjmclama = imbangdugalama::on($cawangan['mysql'])
             ->sum('pendahuluan_jmc');
 
          $total = $pendahuluanPKBtawarruq + $pendahuluanjmclama + $pendahuluanpkblama + $pendahuluanPKB + $pendahuluanJMC;
 
          $pindahkekpkb = GlCode::on($cawangan['mysql'])
          ->where('glcode','=',$serahankoperasi)
          ->sum('amaun');
       }else{
      
          $total = $pendahuluanPKBtawarruq + $pendahuluanPKB + $pendahuluanJMC;
 
          $pindahkekpkb = 0;
 
       }
 
    
       $arraypend = ['pendahuluan' => $total,'serahan' => $totalserahan,'pindahkekpkb' => $pindahkekpkb];
 
       return $arraypend;
    }

    public function getpanjar()
    {
 
       $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();
 
       $baikidanselenggara = Panjar::on($cawangan['mysql'])
       ->where('tarikh','>=', $this->mula)
       ->where('tarikh','<=', $this->hingga)
       ->where('tag', '=', 2)
       ->sum('bayaran');
 
       $perubatan = Panjar::on($cawangan['mysql'])
       ->where('tarikh','>=', $this->mula)
       ->where('tarikh','<=', $this->hingga)
       ->where('tag', '=', 1)
       ->sum('bayaran'); 
 
       $keraian = Panjar::on($cawangan['mysql'])
       ->where('tarikh','>=', $this->mula)
       ->where('tarikh','<=', $this->hingga)
       ->where('tag', '=', 3)
       ->sum('bayaran');
 
 
       $arraycalculation = ['bds' => $baikidanselenggara, 'perubatan' => $perubatan, 'keraian' => $keraian];
 
       return $arraycalculation;
 
    }

    public function BakiLelongCalculation()
    {
        $cas_lelong = 0;
        $jumlah_lelong = 0;
        $baki_lelong = 0;
        $rugi_lelong = 0;

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();


        $bakilelong = Lelong::on($cawangan['mysql'])
            ->where('trkhtuntut','>=',$this->mula)
            ->where('trkhtuntut','<=',$this->hingga)
            ->where('baki','>=',0)
            ->sum('baki');

        $rugilelong = Lelong::on($cawangan['mysql'])
            ->where('trkhtuntut','>=',$this->mula)
            ->where('trkhtuntut','<=',$this->hingga)
            ->where('baki','<',0)
            ->sum('baki');

        $data_lelong = Lelong::on($cawangan['mysql'])->where([['t_jual','>=',$this->mula],['t_jual','<=',$this->hingga]])->get();

        foreach($data_lelong as $index=>$lelong){
            if($lelong['baki'] >= 0){
                $cas_lelong += $lelong['cajlelong'];
                $jumlah_lelong += $lelong['hargajual'];
                $baki_lelong += $lelong['baki'];
            }elseif($lelong['baki'] < 0){
                $cas_lelong += $lelong['cajlelong'];
                $jumlah_lelong += $lelong['hargajual'];
                $rugi_lelong += abs($lelong['baki']);
            }
        }

        $carbonMula = new Carbon($this->mula);
        if($carbonMula->month = 9 && $this->kodcaw == 'ARP')
        {
            $rugilelong += 119;
        }

        $arraylelong = ['bakilelong' => $bakilelong, 'rugilelong' => $rugilelong, 'baki' => $baki_lelong, 'rugi' => $rugi_lelong, 'jumlahlelong' => $jumlah_lelong, 'caslelong' => $cas_lelong];

        return $arraylelong;
    }

    public function getKeuntunganSebenar()
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();

        $total = 0;
        $keuntungansebelumtawarruq = 0;

        //Keuntungan Selepas Tawarruq
        $keuntungan_data = TransaksiKeuntungan::on('mysql7')
            // ->join($cawangan['db_name'].'.gadai as db2','transaksi_keuntungan.norujukan','=','db2.norujukan')
            ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', $this->mula)
            ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->hingga)
            // ->whereYear('transaksi_keuntungan.tarikh_bayaran','=',$current->year)
            // ->whereNull('db2.hargajualan')
            // ->sum('bayaran_keuntungan');
            // ->get();
            ->pluck('norujukan');

        $gadaiData = Gadai::on($cawangan['mysql'])->whereIn('norujukan', $keuntungan_data)->whereNull('hargajualan')->pluck('norujukan');

        $keuntungan = TransaksiKeuntungan::on('mysql7')->whereIn('norujukan',$gadaiData)->sum('bayaran_keuntungan');

        // $keuntungan = 0;
        // if($keuntungan_data){
        //     foreach($keuntungan_data as $index=>$keuntunganData){
        //         $gadaiData = Gadai::on($cawangan['mysql'])->where([['norujukan',$keuntunganData['norujukan']],['hargajualan',null]])->first();
        //         if($gadaiData){
        //             $keuntungan += $keuntunganData['bayaran_keuntungan'];
        //         }
        //     }
        // }

        $upahtebus = Tebus::on($cawangan['mysql'])
            ->where([['t_tebus','>=',$this->mula],['t_tebus','<=',$this->hingga],['t_tebus','>','2013-12-31']])
            // ->whereYear('t_tebus','=',$current->year)
            ->sum('upahdibayar');

        $upahlelong = Lelong::on($cawangan['mysql'])
            ->where([['t_jual','>=',$this->mula],['t_jual','<=',$this->hingga],['t_jual','>','2013-12-31'],['t_jual','<=','2022-07-24']])
            // ->whereYear('t_jual','=',$current->year)
            ->sum('bakiupah');

        $upahansur = Advansur::on($cawangan['mysql'])
            ->where('tarikh','>=',$this->mula)
            ->where('tarikh','<=',$this->hingga)
            // ->whereYear('tarikh','=',$current->year)
            ->sum('upahdibayar');


        $keuntungansebelumtawarruq = $upahtebus + $upahlelong + $upahansur;

        // $keuntunganselepastawarruq = $keuntunganselepastawarruqupah;

        // $total = $keuntungansebelumtawarruq + $keuntunganselepastawarruq;

        $total = $keuntungansebelumtawarruq + $keuntungan;

        return $total;
    }

    public function SemakanKeuntunganLejer(){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();
        $current = Carbon::now();
        $current = new Carbon();

        $mulaCarbon = new Carbon($this->mula);
        $hinggaCarbon = new Carbon($this->hingga);
        
        if($this->isFullMonth($this->mula,$this->hingga) && $mulaCarbon->month < 10 && $mulaCarbon->year == 2024){
            $lastDayOfPreviousMonth = $mulaCarbon->subMonth()->endOfMonth();

            $balancesheet_previousmonth = imbangdugabackup::on($cawangan['mysql'])->where('tarikh',$lastDayOfPreviousMonth->toDateString())->first()->toArray();
            $balancesheet_currentmonth = imbangdugabackup::on($cawangan['mysql'])->where('tarikh',$this->hingga)->first()->toArray();
            // dd($lastDayOfPreviousMonth->toDateString(),$this->hingga,$balancesheet_previousmonth['keun_sebenar'],$balancesheet_currentmonth['keun_sebenar']);
            
            $realprofit_previousmonth = ($lastDayOfPreviousMonth->year == $hinggaCarbon->year) ? $balancesheet_previousmonth['keun_sebenar'] : 0;
            $realprofit_currentmonth = $balancesheet_currentmonth['keun_sebenar'];

            $total_keuntungan_sebenar = $realprofit_currentmonth - $realprofit_previousmonth;

            $month = $lastDayOfPreviousMonth->month;
            $className_previous = "App\StokAudit{$month}";

            $month_current = $hinggaCarbon->month;
            $className_current = "App\StokAudit{$month_current}";

            $stokaudit_previous = $className_previous::on($cawangan['mysql'])->where('tahun',$lastDayOfPreviousMonth->year);
            $stokaudit_current = $className_current::on($cawangan['mysql'])->where('tahun',$hinggaCarbon->year);

            $akrunet = AkruNet::on($cawangan['mysql'])->where('bulan',$month_current)->where('tahun',$hinggaCarbon->year)->first()->toArray();


            $stokaudit_previous = $stokaudit_previous->sum('upahblmbyar');
            $stokaudit_current = $stokaudit_current->sum('upahblmbyar');

            // $pend_terakru = $balancesheet_currentmonth['pend_terakru'];

            $total = $akrunet['jumlah'] - $stokaudit_current;
            $total_keuntungan_semasa = abs($total) - $stokaudit_previous;

            $array_keuntungan = ['keuntungan_sebenar' => abs($total_keuntungan_sebenar), 'keuntungan_semasa' => abs($total_keuntungan_semasa)];

            return $array_keuntungan;
        }

        /* Start Keuntungan Sebenar Calculation */
        $collection_trans = Transaction::on('mysql7')
            ->leftJoin('transaksi_keuntungan as trans', function ($join) {
                $join->on('transaction.norujukan', '=', 'trans.norujukan');
                $join->on('transaction.created_at', '=', 'trans.tarikh_bayaran');
            })
            ->where('transaction.kodcaw', $this->kodcaw)
            ->whereDate('transaction.created_at' ,'>=', $this->mula)
            ->whereDate('transaction.created_at' ,'<=', $this->hingga)
            ->get();

        $array_norujukan = $collection_trans->pluck('norujukan')->toArray();

        $collection_gadai = Gadai::on($cawangan['mysql'])->whereIn('norujukan', $array_norujukan)->whereYear('t_gadai',$mulaCarbon->year)->get();

        $keuntungan_semasa_collection = $collection_trans->filter(function ($trans) use ($collection_gadai) {
            foreach ($collection_gadai as $gadai) {
                if ($gadai->norujukan === $trans->norujukan) {
                    $t_gadai = Carbon::parse($gadai->t_gadai);
                    $t_bayaran = Carbon::parse($trans->tarikh_bayaran);

                    if ($t_gadai->month !== $t_bayaran->month) {
                        return false;
                    }else{
                        return true;
                    }
                }
            }
            return false;
        });

        $total_ansuran = $keuntungan_semasa_collection->filter(function ($transaction){
            return $transaction->trans_type == 'A';
        })->sum('bayaran_keuntungan');

        $total_tebus = $keuntungan_semasa_collection->filter(function ($transaction){
            return $transaction->trans_type !== 'A';
        })->sum('keuntungan_rebet');

        $total_keuntungan_sebenar = $total_ansuran + $total_tebus;
        /* End Keuntungan Sebenar Calculation */

        /* Start Keuntungan Semasa Calculation */
        $collection_trans = Transaction::on('mysql7')
        ->leftJoin('transaksi_keuntungan as trans', function ($join) {
            $join->on('transaction.norujukan', '=', 'trans.norujukan');
            $join->on('transaction.created_at', '=', 'trans.tarikh_bayaran');
        })
        ->where('transaction.kodcaw', $this->kodcaw)
        ->whereDate('transaction.created_at' ,'>=', $this->mula)
        ->whereDate('transaction.created_at' ,'<=', $this->hingga)
        ->get();


        $array_norujukan = $collection_trans->pluck('norujukan')->toArray();

        $collection_gadai = Gadai::on($cawangan['mysql'])->whereIn('norujukan', $array_norujukan)->where('tagbaru',0)->get();

        $keuntungan_semasa_collection = $collection_trans->filter(function ($trans) use ($collection_gadai) {
            foreach ($collection_gadai as $gadai) {
                if ($gadai->norujukan === $trans->norujukan) {
                    $t_gadai = Carbon::parse($gadai->t_gadai);
                    $t_bayaran = Carbon::parse($trans->tarikh_bayaran);

                    if (($t_gadai->month == $t_bayaran->month) && ($t_gadai->year == $t_bayaran->year)) {
                        return false;
                    }else{
                        return true;
                    }
                }
            }
            return false;
        });

        $total_ansuran = $keuntungan_semasa_collection->filter(function ($transaction){
            return $transaction->trans_type == 'A';
        })->sum('bayaran_keuntungan');

        $total_tebus = $keuntungan_semasa_collection->filter(function ($transaction){
            return $transaction->trans_type !== 'A';
        })->sum('keuntungan_rebet');

        /* Keuntungan Lelong */
        $upahlelong = Lelong::on($cawangan['mysql'])
            ->where([['tagujrah','=',0],['t_jual', '>=', $this->mula], ['t_jual', '<=', $this->hingga], ['t_jual', '>', '2022-07-24']])
            ->whereYear('t_jual', '=', $mulaCarbon->year)
            ->sum('bakiupah');

        $total_keuntungan_semasa = ($total_ansuran + $total_tebus + $upahlelong);
        /* End Keuntungan Semasa Calculation */

        $array_keuntungan = ['keuntungan_sebenar' => $total_keuntungan_sebenar, 'keuntungan_semasa' => $total_keuntungan_semasa];

        return $array_keuntungan;
    }

    public function getwtdLejer()
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();


       $wtd = WTD::on($cawangan['mysql'])
            ->where('tarikh','>=', $this->mula)
            ->where('tarikh','<=', $this->hingga)
            ->sum('wtd');

        return $wtd;
    }

    public function getTotalKeuntungan() : Float{
         
        /* Keuntungan Semasa */
        $keuntungan_semasa = $this->CurrentProfit($this->mula,$this->hingga,$this->kodcaw);
        $totalkeuntungan=$keuntungan_semasa['onepercent'];

        return $totalkeuntungan;
    }

    public function getTotalUjrah() : Float{

        /* Keuntungan Semasa */
        $keuntungan_semasa = $this->CurrentProfit($this->mula,$this->hingga,$this->kodcaw);
        $totalujrah = $keuntungan_semasa['ujrah'];
    

        return $totalujrah;
    }

    public function CurrentProfit($start,$end,$kodcaw){

        $start = new Carbon($start);
        $year = $start->format('Y');

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();

        $collection_trans = TransaksiKeuntungan::on('mysql7')
                ->leftJoin('transaction as trans', function($join)
                {
                    $join->on('transaksi_keuntungan.norujukan', '=', 'trans.norujukan');
                    $join->on('transaksi_keuntungan.tarikh_bayaran', '=', 'trans.created_at');
                })
                ->where('trans.kodcaw','=',$kodcaw)
                ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $end)
                ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', $start)
                ->whereYear('transaksi_keuntungan.tarikh_bayaran','=',$year)
                ->get();

        $array_norujukan = $collection_trans->pluck('norujukan')->toArray();

        $collection_gadai = Gadai::on($cawangan['mysql'])->whereIn('norujukan',$array_norujukan)->get();

        /* filtering untuk gadai yang ditebus pada bukan bulan yang sama */
        $keuntungan_semasa_collection = $collection_trans->filter(function ($trans) use ($collection_gadai) {
            
            foreach ($collection_gadai as $gadai) {
                if($gadai->norujukan === $trans->norujukan){
                    $t_gadai = Carbon::parse($gadai->t_gadai);
                    $t_bayaran = Carbon::parse($trans->tarikh_bayaran);
                    
                    if ($t_gadai->month === $t_bayaran->month && $t_gadai->year === $t_bayaran->year) {
                        return false;
                    }else{
                        return true;
                    }
                }
            }
            return false;
        });


        $ujrah = $this->filterCollection($keuntungan_semasa_collection,'A','ujrah');
        $onepercent = $this->filterCollection($keuntungan_semasa_collection,'A','onepercent');
        $ujrah_rebet = $this->filterCollection($keuntungan_semasa_collection,'T','ujrah_rebet');
        $onepercent_rebet = $this->filterCollection($keuntungan_semasa_collection,'T','onepercent_rebet');

        $upahlelong = Lelong::on($cawangan['mysql'])
        ->selectRaw('SUM(ujrah) as ujrah,SUM(onepercent) as onepercent')
        ->where([['tagujrah','=',1],['t_jual','>=',$start],['t_jual','<=',$end]])
        ->whereYear('t_jual','=',$year)
        ->get()->toArray();

        $ujrah_lelong = (empty($upahlelong)) ? 0 : $upahlelong[0]['ujrah'];
        $onepercent_lelong = (empty($upahlelong)) ? 0 : $upahlelong[0]['onepercent'];

        $total_onepercent = $onepercent + $onepercent_rebet + $onepercent_lelong;
        $total_ujrah = $ujrah + $ujrah_rebet + $ujrah_lelong;

        return ['ujrah' => $total_ujrah,'onepercent' => $total_onepercent];
    }

    private function filterCollection($collection,$type,$sumtype) : Float{

        $array_type = ['T','S','P','AT'];
        return $collection->filter(function ($item) use($type,$array_type) {
            if($type == 'T')
                return in_array($item->trans_type, $array_type);

            return $item->trans_type === $type;
        })->sum($sumtype);
    }

    private function isFullMonth($startDate,$endDate){
        // Parse the dates to Carbon instances
        $start = Carbon::parse($startDate);
        $end = Carbon::parse($endDate);

        // Check if the start date is the first day of the month
        $isFirstDay = $start->isSameDay($start->copy()->startOfMonth());
        
        // Check if the end date is the last day of the month
        $isLastDay = $end->isSameDay($end->copy()->endOfMonth());

        // Check if both dates are in the same month and year
        $isSameMonth = $start->isSameMonth($end) && $start->isSameYear($end);

        // Return true only if all conditions are met
        return $isFirstDay && $isLastDay && $isSameMonth;
    }


}