<?php

namespace App\Services\Reports;

use App\AkruNet;
use App\AkruNetPecahan;
use App\Cawangan;
use App\Gadai;
use App\imbangdugabackup;
use App\Lelong;
use App\Transaction;
use App\Vault;
use App\Tunai;
use App\TransaksiKeuntungan;
use App\GlDetail;
use App\imbangdugalama;
use App\GlCode;
use App\Customer;
use App\Tebus;
use App\Advansur;
use App\HapusKira;
use App\Panjar;
use App\Services\Reports\OnePercentService as ReportOne;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class ImbangDugaService
{
    private $kodcaw;
    private $tarikh;

    /* ImbangDuga Variable */
    private $keuntungan_penggadai;

    public function __construct(String $kodcaw, $tarikh)
    {
        $this->kodcaw = $kodcaw;
        $this->tarikh = $tarikh;
    }

    /* External Method */

    private function log($message)
    {
        Log::debug($message);
    }

    private function logerror($message)
    {
        Log::error($message);
    }

    private function transactionExecute(callable $callablefunc)
    {
        try {
            DB::beginTransaction();
            $callablefunc();
            return 'success';
            DB::commit();
        } catch (\Exception $e) {
            $this->log($e->getMessage());
            return 'error';
            DB::rollback();
        }
    }

    private function stokAuditClass(String $month)
    {
        $className = "App\StokAudit{$month}";

        if (class_exists($className))
            return $className;
        else {
            $this->logerror('not found StokAudit');
            throw new \Exception('[500]: An exception found - not found stok audit -' . $month, Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    private function cawanganInfo()
    {
        try {
            return Cawangan::on('mysql7')->where('kodcaw', '=', $this->kodcaw)->firstOrFail();
        } catch (ModelNotFoundException $e) {
            $this->logerror($e->getMessage());
            throw new \Exception('[500]: Cawangan not found', Response::HTTP_NOT_FOUND);
        } catch (\Exception $e) {
            $this->logerror($e->getMessage());
            throw new \Exception('[500]: An exception found', Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }


    /* Calculation Method */

    private function calculateAkruBersih($currentClass, $previousClass, $date, $cawangan)
    {
        $currentMonth = (clone $date)->month;
        $currentYear = (clone $date)->year;

        $previousDate = (clone $date)->subMonthNoOverflow();
        $previousMonth = $previousDate->month;
        $previousYear = $previousDate->year;

        $totalCurrent = $currentClass::on($cawangan['mysql'])
            ->where('tahun', $currentYear)
            ->where('bulan', $currentMonth)
            ->sum('upahblmbyar');

        $totalPrevious = $previousClass::on($cawangan['mysql'])
            ->where('tahun', $previousYear)
            ->where('bulan', $previousMonth)
            ->sum('upahblmbyar');

        $currentProfit = $this->calculateKeuntunganSemasa($date);

        $akru_net = abs(($totalPrevious - $currentProfit) - $totalCurrent);

        return $akru_net;
    }

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

        $firstMonth = (clone $currentDate)->firstOfMonth();
        $lastMonth = (clone $currentDate)->endOfMonth();
        /* 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)
        ->whereYear('transaction.created_at', '=', $currentDate->year)
        ->whereMonth('transaction.created_at', '=', $currentDate->month)
        ->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', '>=', $firstMonth], ['t_jual', '<=', $lastMonth], ['t_jual', '>', '2022-07-24']])
            ->whereYear('t_jual', '=', $currentDate->year)
            ->sum('bakiupah');

        $total_keuntungan_semasa = ($total_ansuran + $total_tebus + $upahlelong);

        /* End Keuntungan Semasa Calculation */
        return $total_keuntungan_semasa;
    }

    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 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];
    }

    /* Getter Method */

    public function getKeuntunganPenggadai()
    {
        $date = new Carbon($this->tarikh);
        $month = (clone $date)->month;
        $year = (clone $date)->year;

        try {
            $className = $this->stokAuditClass($month);
            $cawangan = $this->cawanganInfo();
        } catch (\Exception $e) {
            $this->logerror($e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => $e->getMessage()
            ], $e->getCode() ?: 500);
        }

        /* Algorithm to get the data */

        $stokaudit = $className::on($cawangan['mysql'])
            ->where('tahun', $year)
            ->selectRaw('SUM(upahblmbyar) as upah, SUM(ujrah) as ujrah, SUM(utgonepercent) as onepercent')
            ->first();

        $keuntungan_penggadai = $stokaudit->upah + $stokaudit->ujrah + $stokaudit->onepercent;

        $this->keuntungan_penggadai = $keuntungan_penggadai;

        return $keuntungan_penggadai;
    }

    public function getPendapatanTerakru()
    {
        $date = new Carbon($this->tarikh);
        $current_month = (clone $date)->month;
        $current_year = (clone $date)->year;

        $previousDate = (clone $date)->subMonthNoOverflow();
        $previous_month = $previousDate->month;
        try {
            $currentClassName = $this->stokAuditClass($current_month);
            $previousClassName = $this->stokAuditClass($previous_month);

            $cawangan = $this->cawanganInfo();
        } catch (\Exception $e) {
            $this->logerror($e->getMessage());
            return response()->json([
                'status' => 'error',
                'message' => $e->getMessage()
            ], $e->getCode() ?: 500);
        }
        /* Algorithm to get the data */
        $akru = AkruNet::on($cawangan['mysql'])->where('tahun', $current_year)->where('bulan', '<', $current_month)->sum('jumlah');
        $akru_bersih_bulan = $this->calculateAkruBersih($currentClassName, $previousClassName, $date, $cawangan);

        AkruNet::on($cawangan['mysql'])->where('tahun',$current_year)->where('bulan','=',$current_month)->update([
            'jumlah' => $akru_bersih_bulan,
        ]);

        $total_akru = $akru + $akru_bersih_bulan;
        return $total_akru;
    }
    public function getKeuntunganSebenar()
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw', '=', $this->kodcaw)->first();

        $tarikh = new Carbon($this->tarikh);
        $year = (clone $tarikh)->year;
        $month = (clone $tarikh)->month;

        $lastDayPreviousMonth = $tarikh->subMonthsNoOverflow()->endOfMonth()->toDateString();
        $lastEndYear = $tarikh->subMonthsNoOverflow()->endOfMonth();
        $lastYear = Carbon::now()->subYear()->year;

        //$get_imbangduga = imbangdugabackup::on($cawangan['mysql'])->where('tarikh',$lastDayPreviousMonth)->first()->toArray();

        /* Added because if the last month is not the same year */
        if($lastEndYear->year != $lastYear){
            $get_imbangduga = imbangdugabackup::on($cawangan['mysql'])->where('tarikh',$lastDayPreviousMonth)->first()->toArray();
        }else{
            $get_imbangduga['keun_sebenar'] = 0;
        }

        $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)
            ->whereYear('transaction.created_at', '=', $year)
            ->whereMonth('transaction.created_at', '=', $month)
            ->get();

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

        $collection_gadai = Gadai::on($cawangan['mysql'])->whereIn('norujukan', $array_norujukan)->whereYear('t_gadai',$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');

	$backdate_keun_sebenar = isset($get_imbangduga['keun_sebenar']) ?  $get_imbangduga['keun_sebenar'] : 0;
        $total = $total_ansuran + $total_tebus + $backdate_keun_sebenar;

        return $total;
    }

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

        $date = new Carbon($this->tarikh);
        $date_sebelum = clone $date;

        $date_sebelum = $date_sebelum->firstOfMonth();
        $month_sebelum = $date_sebelum->subMonth()->format('n');
        $year_sebelum = $date_sebelum->format('Y');

        $month_sekarang = $date->format('n');
        $year_generate = $date->format('Y');

        /* generate for month */
        $first_month_current = new Carbon($this->tarikh);
        $first_month_current = $first_month_current->firstOfMonth();
        $last_month_current = new Carbon($this->tarikh);
        $last_month_current = $last_month_current->lastOfMonth();

        $class_audit_current = $this->stokAuditClass($month_sekarang);
        $result_audit_current = $class_audit_current::on($cawangan['mysql'])
            ->where('tahun', $year_generate);

        // $result_audit_current = $this->StokAuditGenerateTerakru($month_sekarang, $cawangan, $year_generate);
        $onepercent_current = $result_audit_current->sum('utgonepercent');
        $ujrah_current = $result_audit_current->sum('ujrah');

        /* B */
        $class_audit_previous = $this->stokAuditClass($month_sebelum);
        $result_audit_previous = $class_audit_previous::on($cawangan['mysql'])
            ->where('tahun', $year_sebelum);

        $onepercent_previous = $result_audit_previous->sum('utgonepercent');
        $ujrah_previous = $result_audit_previous->sum('ujrah');

        /* C */
        $keuntungan_semasa = $this->CurrentProfit($first_month_current->toDateString(), $last_month_current->toDateString(), $this->kodcaw);

        /* D & E */
        $accrual_income_onepercent = $onepercent_current - ($onepercent_previous - $keuntungan_semasa['onepercent']);
        $accrual_income_ujrah = $ujrah_current - ($ujrah_previous - $keuntungan_semasa['ujrah']);

        $available_akru = AkruNetPecahan::on($cawangan['mysql'])
            ->where('bulan', '<', (int)$month_sekarang)
            ->where('tahun', $year_generate);

        $akrunet = AkruNetPecahan::on($cawangan['mysql'])
            ->where('bulan', '=', (int)$month_sekarang)
            ->where('tahun', $year_generate)->first();
        if(empty($akrunet)){
            AkruNetPecahan::on($cawangan['mysql'])
            ->create([
                'bulan' => (int)$month_sekarang,
                'tahun' => $year_generate,
                'persen_semasa' => $onepercent_current,
                'ujrah_semasa' => $ujrah_current,
                'persen_sebelum' => $onepercent_previous,
                'ujrah_sebelum' => $ujrah_previous,
                'keuntungan_semasa_1persen' => $keuntungan_semasa['onepercent'],
                'keuntungan_semasa_ujrah' => $keuntungan_semasa['ujrah'],
                'baki_keun_blm_byr_1persen' => ($onepercent_previous - $keuntungan_semasa['onepercent']),
                'baki_keun_blm_byr_ujrah' => ($ujrah_previous - $keuntungan_semasa['ujrah']),
                'pend_akru_1persen' => $accrual_income_onepercent,
                'pend_akru_ujrah' => $accrual_income_ujrah,
            ]);
        }else{
            AkruNetPecahan::on($cawangan['mysql'])
            ->where('bulan', '=', (int)$month_sekarang)
            ->where('tahun', $year_generate)
            ->update([
                'persen_semasa' => $onepercent_current,
                'ujrah_semasa' => $ujrah_current,
                'persen_sebelum' => $onepercent_previous,
                'ujrah_sebelum' => $ujrah_previous,
                'keuntungan_semasa_1persen' => $keuntungan_semasa['onepercent'],
                'keuntungan_semasa_ujrah' => $keuntungan_semasa['ujrah'],
                'baki_keun_blm_byr_1persen' => ($onepercent_previous - $keuntungan_semasa['onepercent']),
                'baki_keun_blm_byr_ujrah' => ($ujrah_previous - $keuntungan_semasa['ujrah']),
                'pend_akru_1persen' => $accrual_income_onepercent,
                'pend_akru_ujrah' => $accrual_income_ujrah,
            ]);
        }

        $onepercent_akru = $available_akru->sum('pend_akru_1persen') + $accrual_income_onepercent;
        $ujrah_akru = $available_akru->sum('pend_akru_ujrah') + $accrual_income_ujrah;

        return ['onepercent' => $onepercent_akru, 'ujrah' => $ujrah_akru];
    }

    public function getTunaiDiTangan(){
        $current = Carbon::now();
     $current = new Carbon();   
     
     $totalbakiakhirtunai = 0;
  
      $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$this->kodcaw)->first();
      $vault=Vault::on($cawangan['mysql'])
              ->where('kodcaw','=',$this->kodcaw)
              ->where('tarikh','<=', $this->tarikh)
              ->orderby('tarikh','desc')->first();

      $tunai = Tunai::on($cawangan['mysql'])
               ->where('kodcaw','=',$this->kodcaw)
               ->where('tarikh','=', $this->tarikh)
               ->get();

      $total = 0;
      foreach($tunai as $index=>$money)
      {
         $total+=$money['baki'];
      }


      if($total != 0)
      {
         $totalbakiakhirtunai = $vault['bakiakhir'] + $total;
      }else
      {
         $totalbakiakhirtunai = $vault['bakiakhir'];
      }
   
      return $totalbakiakhirtunai;
    }

    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->tarikh)
          ->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' || $this->kodcaw == '020303')
       {
          $serahanpkb = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanpkb)
             ->where('tarikh','<=', $this->tarikh)
             ->where('tarikh','>','2013-12-31')
             ->sum('amaun');
 
          $serahanJMC = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanjmc)
             ->where('tarikh','<=', $this->tarikh)
             ->where('tarikh','>','2013-12-31')
             ->sum('amaun');
 
          $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{
 
          if($this->kodcaw == '020301')
             $serahanbaru = '1000/030';
          else
             $serahanbaru = '1000/026';
 
          $serahanpkb = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanpkb)
             ->where('tarikh','<=', $this->tarikh)
             ->sum('amaun');
 
          $serahanJMC = GlDetail::on($cawangan['mysql'])
             ->where('dtacct','=',$serahanbaru)
             ->where('tarikh','<=', $this->tarikh)
             ->sum('amaun');
 
          $totalserahan = $serahanpkbTawarruq + $serahanpkb + $serahanJMC;
       }
      
 
       //Selepas Tawarruq
 
       $pendahuluanPKBtawarruq = GlDetail::on($cawangan['mysql'])
       ->where('ktacct','=',$pendahuluanpkb)
       ->where([['tarikh','<=', $this->tarikh],['tarikh','>','2021-03-24']])
       ->sum('pendahuluan');
       
       //Sebelum Tawarruq
 
       $pendahuluanPKB = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=',$pendahuluanpkb)
          ->where([['tarikh','<=', $this->tarikh],['tarikh','>','2013-12-31']])
          ->sum('amaun');
 
       $pendahuluanJMC = GlDetail::on($cawangan['mysql'])
          ->where('ktacct','=','7000/106')
          ->where([['tarikh','<=', $this->tarikh],['tarikh','>','2013-12-31']])
          ->sum('amaun');
 
       $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');
 
       if($this->kodcaw == '011229')
          $totalserahan -= 0.10;
       
       if($this->kodcaw == '011013')
          $totalserahan += 641000;
 
       $arraypend = ['pendahuluan' => $total,'serahan' => $totalserahan,'pindahkekpkb' => $pindahkekpkb];
 
       return $arraypend;
    }

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

        $datetawarruq = new Carbon('2021-04-14');
        $daterequest = new Carbon($this->tarikh);
        $daterequest->addDays(1);

        $current = Carbon::now();
        $current = new Carbon();

        if($this->kodcaw == 'GC'){
            $transaksi_data = [];
            TransaksiKeuntungan::on('mysql7')
                ->where('tarikh_bayaran', '<=', $daterequest)
                ->where('kodcaw', $this->kodcaw)
                ->chunk(1000, function ($transactions) use (&$transaksi_data) {
                    $transaksi_data = array_merge($transaksi_data, $transactions->pluck('norujukan')->toArray());
                });
            
            $gadaiData = [];
            $chunkedTransaksiData = array_chunk($transaksi_data, 1000);    
            
            foreach ($chunkedTransaksiData as $chunk) {
                Gadai::on($cawangan['mysql'])
                    ->whereIn('norujukan', $chunk)
                    ->where(function ($query) {
                        $query->where([
                            ['sttebus', '=', ''],
                            ['hargajualan', '<>', null]
                        ])
                        ->orWhere([
                            ['sttebus', '!=', ''],
                            ['t_update', '>', $this->tarikh],
                            ['hargajualan', '<>', null]
                        ]);
                    })
                   ->orderBy('norujukan')
                    ->chunk(1000, function ($gadaiChunk) use (&$gadaiData) {
                        $gadaiData = array_merge($gadaiData, $gadaiChunk->pluck('norujukan')->toArray());
                    });
            }

            $totalPembiayaan = 0;
            $chunkedGadaiData = array_chunk($gadaiData, 1000);    
            
            foreach ($chunkedGadaiData as $chunk) {
                $totalPembiayaan += TransaksiKeuntungan::on('mysql7')
                    ->whereIn('norujukan', $chunk)
                    ->where('tarikh_bayaran', '<=', $daterequest)
                    ->sum('bayaran_pembiayaan');
            }

            return $totalPembiayaan;
        }

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

        $gadaiData = Gadai::on($cawangan['mysql'])->whereIn('norujukan',$transaksi_data)
            ->where([['sttebus',''],['hargajualan','<>','null']])
            ->orWhere([['sttebus','!=',''],['t_update','>',$this->tarikh],['hargajualan','<>','null']])
            ->pluck('norujukan');


        $transaksi = TransaksiKeuntungan::on('mysql7')
            ->whereIn('norujukan',$gadaiData)
            ->where('tarikh_bayaran' ,'<=', $daterequest)
            ->sum('bayaran_pembiayaan');

        return $transaksi;
    }


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

        $current = new Carbon($this->tarikh);

        $daterequest = new Carbon($this->tarikh);
        $daterequest->addDays(1);

        $total = 0;

        //Untung Rugi Terkumpul Sebelum 01.12.2013
        $utgrugilama = imbangdugalama::on($cawangan['mysql']) //--masuk
            ->sum('untung_t');

        //Yuran Ahli
        $yuranTlama = imbangdugalama::on($cawangan['mysql'])
            ->sum('yuran_t');

        $yuranbaru = Customer::on('mysql7')
            ->where('tarikhdaftar','>','2013-12-31')
            ->whereYear('tarikhdaftar','<',$current->year)
            ->sum('yuranahli');

        //Caj Lelong
        $lelong = Lelong::on($cawangan['mysql']) //--masuk
            ->whereNotNull('t_jual')
            ->where('t_jual','>','2013-12-31')
            ->whereYear('t_jual','<',$current->year)
            ->sum('cajlelong');

        $cajlelonglama = imbangdugalama::on($cawangan['mysql'])
            ->sum('cajlelong_t');


        //Upah Simpan Tebus
        $upahsimpantebus = Tebus::on($cawangan['mysql']) //--masuk
            ->where('t_tebus','>','2013-12-31')
            ->whereYear('t_tebus','<',$current->year)
            ->sum('upahdibayar');

        //Upah Simpan Lelong
        $upahsimpanlelong = Lelong::on($cawangan['mysql']) //--masuk
            ->where('t_jual','>','2013-12-31')
            ->whereYear('t_jual','<',$current->year)
            ->sum('bakiupah');

        //Upah Simpan Advansur
        $upahsimpanansur = Advansur::on($cawangan['mysql']) //--masuk
            ->whereYear('tarikh','<',$current->year)
            ->sum('upahdibayar');

        //Upah Tawarruq

        $keuntungan_data = TransaksiKeuntungan::on('mysql7')
        ->where('transaksi_keuntungan.tarikh_bayaran' ,'<=', $daterequest)
        ->whereYear('transaksi_keuntungan.tarikh_bayaran','<',$current->year)
        ->where('kodcaw',$this->kodcaw)
        ->pluck('norujukan');

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

        $upahsimpankeuntungansebelumtawarruq = TransaksiKeuntungan::on('mysql7')
            ->where('tarikh_bayaran' ,'<=', $daterequest)
            ->whereYear('tarikh_bayaran','<',$current->year)
            ->whereIn('norujukan',$gadaiData)
            ->sum('bayaran_keuntungan');

        $upahsimpanlama = imbangdugalama::on($cawangan['mysql'])
            ->sum('upah_t');

        //GST Caj Lelong
        $gstcajlelong = Lelong::on($cawangan['mysql']) //--masuk
            ->where('t_jual','<=',$this->tarikh)
            ->whereYear('t_jual','<',$current->year)
            ->sum('gst_cl');

        //Upah Hapus Kira
        $upahhapuskira = HapusKira::on($cawangan['mysql'])
            ->where('tarikh','>','2013-12-31')
            ->whereYear('tarikh','<',$current->year)
            ->sum('bakiupah');

        $panjar1 = Panjar::on($cawangan['mysql'])
            ->whereYear('tarikh','<',$current->year)
            ->where('tag','=','1')
            ->sum('bayaran');

        $panjar2 = Panjar::on($cawangan['mysql'])
            ->whereYear('tarikh','<',$current->year)
            ->where('tag','=','2')
            ->sum('bayaran');

        $panjar3 = Panjar::on($cawangan['mysql'])
            ->whereYear('tarikh','<',$current->year)
            ->where('tag','=','3')
            ->sum('bayaran');

        $parameter = ($panjar1*-1) + ($panjar2*-1) + ($panjar3*-1);

        // $parameter = Parameter::on($cawangan['mysql']) //--masuk
        //     ->sum('sumpanjarlama');

        //Keuntungan Tawarruq Sebenar
        $keuntungansebenar = 0;
        $dateSM = '2021-04-24';

        if($this->tarikh < '2021-06-09')
        {
            $keuntungan_data = TransaksiKeuntungan::on('mysql7')
                ->whereDate('transaksi_keuntungan.tarikh_bayaran', '<=', $this->tarikh)
                ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<', $dateSM)
                ->whereYear('tarikh_bayaran','<',$current->year)
                ->where('kodcaw',$this->kodcaw)
                ->pluck('norujukan');

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

            $keuntungan = TransaksiKeuntungan::on('mysql7')
                ->whereIn('norujukan',$gadaiData)
                ->whereDate('tarikh_bayaran', '<=', $this->tarikh)
                ->whereDate('tarikh_bayaran' ,'<', $dateSM)
                ->whereYear('tarikh_bayaran','<',$current->year)
                ->sum('bayaran_keuntungan');

            $keuntunganreducing_data = TransaksiKeuntungan::on('mysql7')
                ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->tarikh)
                ->whereDate('transaksi_keuntungan.tarikh_bayaran','>=', $dateSM)
                ->whereYear('tarikh_bayaran','<',$current->year)
                ->where('kodcaw',$this->kodcaw)
                ->pluck('norujukan');

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

            $keuntunganreducing = TransaksiKeuntungan::on('mysql7')
                ->whereIn('norujukan',$gadaiData)
                ->whereDate('tarikh_bayaran' ,'<=', $this->tarikh)
                ->whereDate('tarikh_bayaran','>=', $dateSM)
                ->whereYear('tarikh_bayaran','<',$current->year)
                ->sum('keuntungan_rebet');

            $keuntungansebenar = ($keuntungan + $keuntunganreducing);


        }else{

        $keuntungan_data = TransaksiKeuntungan::on('mysql7')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran', '<=', $this->tarikh)
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<', $dateSM)
                    ->whereYear('tarikh_bayaran','<',$current->year)
                    ->where('kodcaw',$this->kodcaw)
                    ->pluck('norujukan');


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

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

        $keuntunganreducing_data = TransaksiKeuntungan::on('mysql7')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<', '2021-06-09')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran','>=', $dateSM)
                    ->whereYear('tarikh_bayaran','<',$current->year)
                    ->where('kodcaw',$this->kodcaw)
                    ->pluck('norujukan');

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

        $keuntunganreducing = TransaksiKeuntungan::on('mysql7')
            ->whereIn('norujukan',$gadaiData)
            ->whereDate('tarikh_bayaran' ,'<', '2021-06-09')
            ->whereDate('tarikh_bayaran','>=', $dateSM)
            ->sum('keuntungan_rebet');

        $bukanansuran_data = 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')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->tarikh)
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', '2021-06-09')
                    ->whereYear('tarikh_bayaran','<',$current->year)
                    ->where('trans.kodcaw',$this->kodcaw)
                    ->pluck('transaksi_keuntungan.norujukan');

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

        $bukanansuran = TransaksiKeuntungan::on('mysql7')
                    ->whereIn('transaksi_keuntungan.norujukan',$gadaiData)
                    ->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')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->tarikh)
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', '2021-06-09')
                    ->whereYear('transaksi_keuntungan.tarikh_bayaran','<',$current->year)
                    ->sum('transaksi_keuntungan.keuntungan_rebet');

        $ansuran_data = 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')
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->tarikh)
                    ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', '2021-06-09')
                    ->whereYear('tarikh_bayaran','<',$current->year)
                    ->where('trans.kodcaw',$this->kodcaw)
                    ->pluck('transaksi_keuntungan.norujukan');

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

        $ansuran = TransaksiKeuntungan::on('mysql7')
            ->whereIn('transaksi_keuntungan.norujukan',$gadaiData)
            ->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')
            ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $this->tarikh)
            ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'>=', '2021-06-09')
            ->whereYear('transaksi_keuntungan.tarikh_bayaran','<',$current->year)
            ->sum('transaksi_keuntungan.bayaran_keuntungan');

            $reports = new ReportOne($this->kodcaw);
            $previous_date = new Carbon($this->tarikh);
            $previous_date->subYear()->endOfYear()->toDateString();
            $onepercent = $reports->getTotalKeuntungan('B',$previous_date);
            $ujrah = $reports->getTotalUjrah('B',$previous_date);

        $keuntungansebenar = ($keuntungan + $keuntunganreducing + $ansuran + $bukanansuran) + $onepercent + $ujrah;

        }


        $total = ($utgrugilama + $lelong + $upahsimpantebus + $upahsimpanlelong + $upahsimpanansur + $gstcajlelong + $keuntungansebenar + $upahsimpankeuntungansebelumtawarruq) + $parameter;

        if($this->kodcaw == '011229')
            $total -= 45390.37;


        return $total;
    }

    public function setImbangDugaBackup()
    {

        $funcInsert = function () {

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

            $keuntungan_penggadai = $this->getKeuntunganPenggadai();
            $pend_terakru = $this->getPendapatanTerakru();
            $keun_sebenar = $this->getKeuntunganSebenar();
            $accrual = $this->getOnePercentUjrah();
            $tunaiditangan = $this->getTunaiDiTangan();
            $totalpendser = $this->getPendahuluanSerahan();
            $bayaranpend = $this->getBayaranPendahuluan();
            $pendahuluan = $totalpendser['pendahuluan'];
            $serahan = $totalpendser['serahan'];
            $ujrah = $accrual['ujrah'];
            $onepercent = $accrual['onepercent'];
            $bayaranpend = -1*($bayaranpend);
            // $untungrugiterkumpul = $this->getUntungRugiTerkumpul();

            $totalharta = imbangdugabackup::on($cawangan['mysql'])->where('tarikh', $this->tarikh)
                ->get()
                ->map(function ($ig) use ($keuntungan_penggadai,$tunaiditangan,$serahan,$bayaranpend) {
                    return floatval($tunaiditangan) + $ig['pembiayaantawarruq'] + $bayaranpend + $ig['pinjamankoop'] + $ig['emaspalsu']
                        + $ig['pindahkekpkb'] + $ig['penghutangkaki'] + $ig['phapuskira'] + $ig['belian1stop'] + $keuntungan_penggadai + $serahan;
                });

            $totaltanggungan = imbangdugabackup::on($cawangan['mysql'])->where('tarikh', $this->tarikh)
                ->get()
                ->map(function ($ig) use ($ujrah, $onepercent, $keun_sebenar, $pend_terakru,$pendahuluan) {
                    return $pend_terakru + $ig['bds'] + $ig['perubatan'] + $ig['keraian'] + $ig['rugilelong'] + $ig['bakilelong']
                        + $ig['wtd'] + $ig['untungrugiterkumpul'] + $keun_sebenar + $pendahuluan + $ig['cajlelong']
                        + $ig['cajperkhidmatan'] + $ig['cajlelongxtra'] + $ig['upahsimpanxtra'] + $ig['bakilelongxtra'] + $ig['rugilelongxtra']
                        + $ig['utgterkumpulxtra'] + $ig['jualan1stop'] + $ig['hapuskira'] + $ujrah + $onepercent;
                });

            $booltally = false;

            if (round($totalharta[0], 2) == round($totaltanggungan[0], 2)) {
                $booltally = true;
            } else {
                $booltally = false;
            }

            imbangdugabackup::on($cawangan['mysql'])->where('tarikh', $this->tarikh)->update([
                'tunaiditangan' => $tunaiditangan,
                'keun_penggadai' => $keuntungan_penggadai,
                'keun_sebenar' => $keun_sebenar,
                'pend_terakru' => $pend_terakru,
                'ujrah' => $ujrah,
                'onepercent' => $onepercent,
                'bayaranpendahuluan' => $bayaranpend,
                'serahan' => $serahan,
                'pendahuluan' => $pendahuluan,
                // 'untungrugiterkumpul' => $untungrugiterkumpul,
                'totalharta' => round($totalharta[0], 2),
                'totaltanggungan' => round($totaltanggungan[0], 2),
                'tagtally' => $booltally,
                'updated_at' => Carbon::now()->toDateTimeString(),
            ]);
        };


        return $this->transactionExecute($funcInsert);
    }

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

        $tagtallyValue = 0;
        if($jenistally == 'taktally'){
            $tagtallyValue = 0;
        }else if($jenistally == 'tally'){
            $tagtallyValue = 1;
        }

        $response = imbangdugabackup::on($cawangan['mysql'])->whereDate('tarikh','=',$this->tarikh)
        ->when($jenistally != 'semua', function ($query) use ($tagtallyValue) {
            return $query->where('tagtally', $tagtallyValue);
        })
        ->firstOr(function () {
            return ['message' => 'No Data'];
        });

        if(isset($response['message']) == false) $response = $response;
        
        return $response;
    }
}