<?php

namespace App\Http\Controllers;

use App\Gadai;
use App\Marhun;
use App\Cawangan;
use App\Transaction;
use App\TransaksiKeuntungan;
use App\TransactionOnline;
use App\BatalGadai;
use App\TuntutBaki;
use App\AnsRugi;
use App\Lelong;
use App\WTD;
use App\HapusKira;
use App\Tebus;
use App\Vault;
use App\Tunai;
use App\Komuditi;
use App\KawalanStokLama;
use App\KawalanStok;
use App\KadarUpah;
use App\Moratorium;
use App\Audit;
use App\Customer;
use App\KomuditiPurchase;
use App\KomuditiTransaksi;
use App\Advansur;
use App\HistoryEmasSandaran;
use App\InOutMar;
use App\Services\Miscellaneous\StatistikService;

use Illuminate\Http\Request;

use App\Services\MasterServices;
use App\Support\OnePercent;
use App\Services\Transaction\AnsuranServices as AnsServ;
use Illuminate\Support\Facades\Log;

use Illuminate\Support\Facades\DB;

use Carbon\Carbon;

class GadaiController extends Controller
{

    public function showAllGadais(){
        return response()->json(Gadai::all());
    }

    public function gadaiTransactionByCawanganAndDay($cawangan_code,$date){
	try{

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

        $gadaiTransaction = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$date)->orderBy('sirig', 'desc')->first();
	}catch(\Exception $e){
		Log::debug($e);
		Log::debug($cawangan_code);
		return response()->json('error');
	}
        return response()->json($gadaiTransaction);
    }

    public function showOneGadai($noic){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $gadai_all = [];
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('nokp','=',$noic)->get();
            if(sizeof($gadai) != 0){
                array_push($gadai_all,$gadai);
            }
        }

        return response()->json($gadai_all);
    }

    public function searchGadaiOnline(Request $request){

        $transaction_online = TransactionOnline::on('mysql7')->where([['norujukan',$request->norujukan],['status','PAID'],['update_status',0]])->first();
        if($transaction_online){
            return response()->json(true);
        }else{
            return response()->json(false);
        }
    }

    public function showOneGadaiMobile($noic){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $gadai_all = [];
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('nokp','=',$noic)->get();
            if(sizeof($gadai) != 0){
                array_push($gadai_all,$gadai);
            }
        }
        $senarai_cawangan = [];
        foreach($gadai_all as $index=>$gadai_cawangan){
            $cawangan_gadai = Cawangan::on('mysql7')->where('kodcaw','=',$gadai_cawangan[0]['kodcaw'])->first();
            array_push($senarai_cawangan,['nama_cawangan'=>$cawangan_gadai['details_caw'],'kodcaw'=>$gadai_cawangan[0]['kodcaw']]);
        }

        return response()->json($senarai_cawangan);
    }

    public function showOneGadaiByNorujukan($norujukan){

        $cawangan_all = Cawangan::on('mysql7')->get();

        $gadai_all = [];

        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->get();
            if(sizeof($gadai) != 0){
                array_push($gadai_all,$gadai);
            }
        }


        return response()->json($gadai_all);
    }

    public function showOneGadaiByNorujukanKodcaw($kodcaw,$norujukan){

        $cawangan_all = Cawangan::on('mysql7')
        ->where('kodcaw','=',$kodcaw)
        ->get();

        $gadai_all = [];

        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->get();
            if(sizeof($gadai) != 0){
                array_push($gadai_all,$gadai);
            }
        }


        return response()->json($gadai_all);
    }

    public function showOneGadaiByCawanganOnly($kodcaw,$tarikh){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])
                    ->where([['sttebus','=',''],['t_gadai','<=',$tarikh]])
                    ->orWhere([['sttebus','<>',''],['t_update','>',$tarikh],['t_gadai','<=',$tarikh]])
                    ->get();

        return response()->json($gadai);
    }

    public function showOneGadaibyRujukanAndCawangan($kodcaw,$norujukan){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->get();

        return response()->json($gadai);
    }

    public function create($kodcaw,Request $request){

        $master = new MasterServices($kodcaw);
        $response = $master->createGadai($request);

        return $response;
    }

    public function createold($kodcaw,Request $request){
        $current = Carbon::now();
        $current = new Carbon();

        $t_matang = Carbon::now();
        $t_matang = new Carbon();
        $t_matang = $t_matang->addMonths(6)->subDays(1);

        $t_kontrak = Carbon::now();
        $t_kontrak = new Carbon();
        $t_kontrak = $t_kontrak->addMonths(12)->subDays(1);

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->create([
            'kodcaw'=>$kodcaw,
            'norujukan' => $request->norujukan,
            "prkod" => $request->prkod,
            'nopelanggan' => $request->nopelanggan,
            'nokp' => $request->nokp,
            'sirig' => $request->sirig,
            't_gadai' => $request->t_gadai,
            'sttebus' => '',
            'masa' =>$current->toTimeString(),
            "nilaimarhun" => $request->nilaimarhun,
            "n_marhuns" => $request->n_marhuns,
            "berat" => $request->berat,
            "pinjaman" => $request->pinjaman,
            "telahbayar" => $request->telahbayar,
            "upahtelahbyr" => $request->upahtelahbyr,
            "cajtelahbyr" => $request->cajtelahbyr,
            "ansuran" => $request->ansuran,
            "ansupah" => $request->ansupah,
            "kadarupah" => $request->kadarupah,
            "t_matang" => $t_matang,
            "t_matang1" => $t_kontrak,
            "bakiupah" => $request->bakiupah,
            "cajlain" => $request->cajlain,
            "marhun" => $request->marhun,
            "kuantiti" => $request->kuantiti,
            "jbg" => 0.5,
            "tempoh" => 0.5,
            "kodgl" => $request->kodgl,
            "taglel" => $request->taglel,
            "jantina" => $request->jantina,
            "penilai" => $request->penilai,
            "namapenilai" => $request->namapenilai,
            "namateller" => $request->namateller,
            "tagserah" => $request->tagserah,
            "namapelulus" => $request->namapelulus,
            "percentpinj" => $request->percentpinj,
            "jumtebus" => $request->jumtebus,
            "upahnotis" => $request->upahnotis,
            "upahdibayar" => $request->upahdibayar,
            "semakbarcode" => $request->semakbarcode,
            "historygadaian" => $request->historygadaian,
            "idjompay" => $request->idjompay,
            "bayarupahonline" => $request->bayarupahonline

        ]);
        return response()->json($gadai, 201);
    }


    public function daftarGadaiMarhun($norujukan,Request $request){
        // dd("ggwp");
        $master = new MasterServices($request->kodcaw);
        $response = $master->createMarhunGadai($request->norujukan,$request);

        return response()->json($response, 200);
    }

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

        $gadai_transaction = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$tarikh)->get();

        return response()->json($gadai_transaction);
    }

    public function checkNorujukan(Request $request){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$request->kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->where([['norujukan','=',$request->norujukan],['nokp','=',$request->nokp]])->first();

        return response()->json($gadai);
    }

    public function updateOneGadaiByNorujukan($norujukan,$kodcaw,Request $request){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->update(array(
            ''
        ));

        return response()->json($gadai);
    }

    public function kiraupahTawarruq($kodcaw,$norujukan){
        $master = new MasterServices($kodcaw);
        $response = $master->kiraUpahTawarruq($kodcaw,$norujukan);

        return $response;
    }

    public function kiraUpahRebet($kodcaw,$norujukan){
        $master = new MasterServices($kodcaw);
        $response = $master->kiraUpahRebet($norujukan);

        return $response;
    }

    public function updateGadaiMarhunOnline($kodcaw,$norujukan,Request $request){
        $current = Carbon::now();
        $current = new Carbon();

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->first();

        $upahsemasa = $this->kiraupahTawarruq($kodcaw,$norujukan);
        $upahrebet = $this->kiraUpahRebet($kodcaw,$norujukan);

        $total_bayaran_prev = Transaction::on('mysql7')->where([['norujukan','=',$norujukan],['trans_type','=','A']])->sum('duit_masuk');

        $total_bayaran_semasa = $total_bayaran_prev + $request->bayaran;

        if($total_bayaran_semasa >= $gadai['bakiupah']){
            $lelong_tawarruq = 0;
        }else{
            $lelong_tawarruq = 1;
        }

        $baki_hargajualan = $gadai['bakihargajualan'] - $request->bayaran;

        $baki_bayaran = $request->bayaran - $upahsemasa;

        $baki_pinjaman = $gadai['bakipjm'] - $baki_bayaran;

        $transaction = Transaction::on('mysql7')->create([
            'trans_type' => 'A',
            'norujukan' => $norujukan,
            'nokp' => $gadai['nokp'],
            'teller' => 'online',
            'kodcaw' => $kodcaw,
            'duit_masuk' => $request->bayaran,
            'created_at' => $current,
            'bakiharga' => $baki_hargajualan,
        ]);

        $transaction_upah = TransaksiKeuntungan::on('mysql7')->create([
            'norujukan' => $norujukan,
            'bayaran_keuntungan' => $upahsemasa,
            'bayaran_pembiayaan' => $baki_bayaran,
            'keuntungan_rebet'=> $upahrebet,
            'tarikh_bayaran'=> $current,
            'kodcaw'=> $kodcaw
        ]);

        $update_gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->update(array(
            'bakipjm' => $baki_pinjaman,
            'jbg' => 0,
            't_update' => $current->toDateString(),
            'bakihargajualan' => $baki_hargajualan
        ));
        return response()->json($update_gadai, 200);
    }

    public function updateGadaiMarhunKaunter($kodcaw,$norujukan,Request $request){
        $master = new MasterServices($request->kodcaw);
        $update = $master->updateGadaiMarhunKaunter($kodcaw,$norujukan,$request);

        return $update;
    }

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

        $current = Carbon::now();
        $current = new Carbon();
        $gadai = Gadai::on($cawangan['mysql'])
                ->where([
                    ['t_gadai','=',$current->toDateString()],
                    ['cetak','=',0],
                    ['pinjaman','!=',0.00]
                    ])
                ->get();

        return response()->json($gadai);
    }

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

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

        $gadai = Gadai::on($cawangan['mysql'])
                ->where([
                    ['t_gadai','=',$current->toDateString()],
                    ['cetak','=',1],['tagserah','=',0]
                    ])
                ->get();

        return response()->json($gadai);
    }

    public function batalGadai($kodcaw, $norujukan,Request $request){

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

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

        // $old_data = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->first();

        // Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();
        // Marhun::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

        // $old_data = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->first();
        $old_data = Gadai::on($cawangan['mysql'])->where([['norujukan','=',$norujukan],['pinjaman','=','NULL']])->orWhere([['norujukan','=',$norujukan],['pinjaman','=',0.00]])->first();


        if($old_data){
            Gadai::on($cawangan['mysql'])->where([['norujukan','=',$norujukan],['pinjaman','=','NULL']])->delete();
            Marhun::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

            $audit = new Audit();
            $audit->users = $request->teller;
            $audit->actions = $request->transaction;
            $audit->norujukan = $norujukan;
            $audit->old_data = $old_data;
            $audit->kodcaw = $request->kodcaw;
            $audit->created_at = $current;
            $audit->updated_at = $current;
            $audit->save();

            //return response('Batal Berjaya', 200);
            return response('Batal Berjaya', 200);
        } else {
            return response()->json('false');
        }
    }

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

        $gadai = Gadai::on($cawangan['mysql'])
                ->where('norujukan','=',$norujukan)
                ->update(array(
                    'tagkomuditi' => 1
                    ));

        return response()->json($gadai,200);
    }

    public function cetakSAG($kodcaw,$norujukan,Request $request){
        $harini = Carbon::now();

        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])
                ->where('norujukan','=',$norujukan)
                ->update(array(
                    'cetak' => 1,
                    'penilai' => $request->penilai
                    ));

        $audit = new Audit();
        $audit->users = isset($request->penilai) ? $request->penilai : '';
        $audit->actions = "Cetak SAG";
        $audit->norujukan = $request->norujukan;
        // $audit->new_data = $request->norujukan;
        $audit->kodcaw = $kodcaw;
        $audit->created_at = $harini;
        $audit->updated_at = $harini;
        $audit->save();

        return response()->json($gadai,200);
    }

    public function serahMarhun($kodcaw,$norujukan){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])
                ->where('norujukan','=',$norujukan)
                ->update(array(
                    'tagserah' => 1
                    ));

        return response()->json($gadai,200);
    }

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

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

        $gadai = Gadai::on($cawangan['mysql'])
                ->where([
                    ['t_gadai','=',$current->toDateString()],
                    ['cetak','=',1],['tagserah','=',1],
                    ['semakbarcode','!=','Lulus']
                    ])
                ->get();

        return response()->json($gadai);
    }


    public function barcodeScanTebus($kodcaw,$norujukan){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->first();
        $namaTeller=$gadai['tellernew'];

        $update_gadai=Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)-> update(array(
            'semakbarcode'=>'Tebus','tellernew'=>$namaTeller));

        return response()->json($update_gadai,200);
    }


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

        $dahscan = Gadai::on($cawangan['mysql'])->where([['norujukan','=',$norujukan],['semakbarcode','Lulus']])->first();

        if($dahscan){
            return response()->json('scanned');
        }else{
            $available_gadai = Gadai::on($cawangan['mysql'])->where('norujukan',$norujukan)->first();

            if($available_gadai)
            {
                $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->update([
                    'semakbarcode'=>'Lulus'
                ]);

                return response()->json('success');
            }else{
                return response()->json('unavailable');
            }
        }
    }

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

        $rekod_gadaian = Gadai::on($cawangan['mysql'])->where('norujukan','=',$request->norujukan)->first();

        if($rekod_gadaian['semakbarcode'] == 'Lulus'){
            return response()->json('true');
        }else{
            return response()->json('false');
        }
    }

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

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

        $gadai = Gadai::on($cawangan['mysql'])
                ->where([
                    ['t_gadai','=',$current->toDateString()],
                    ['cetak','=',1],['tagserah','=',1],
                    ['semakbarcode','=','Lulus']
                    ])
                ->orderBy('sirig','asc')
                ->get();

        return response()->json($gadai);
    }

    public function getLaporanGadai($kodcaw,$tarikh){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $gadai_data = Gadai::on($cawangan['mysql'])->select('t_gadai','norujukan','nokp','semakbarcode','masa','berat','nilaimarhun','pinjaman','upah12','percentpinj','teller','nokp','ujrah','onepercent')
        ->where('t_gadai','=',$tarikh)
        ->orderBy('teller')
        ->orderBy('sirig')
        ->get();

        $gadai = [];

        if($gadai_data){
            foreach($gadai_data as $index=>$gadaiData){
                $customer_info = Customer::on('mysql7')->where('kplama','=',$gadaiData['nokp'])->orWhere('kpbaru','=',$gadaiData['nokp'])->first();
                array_push($gadai,[
                    "t_gadai"=> $gadaiData['t_gadai'],
                    "norujukan"=> $gadaiData['norujukan'],
                    "nama"=> $customer_info['nama'],
                    "nokp"=> $gadaiData['nokp'],
                    "semakbarcode"=> $gadaiData['semakbarcode'],
                    "masa"=> $gadaiData['masa'],
                    "berat"=> $gadaiData['berat'],
                    "nilaimarhun"=> $gadaiData['nilaimarhun'],
                    "pinjaman"=> $gadaiData['pinjaman'],
                    "upah12"=> $gadaiData['upah12'],
                    "percentpinj"=> $gadaiData['percentpinj'],
                    "alamat1"=> $customer_info['alamat1'],
                    "teller"=> $gadaiData['teller'],
                    "onepercent"=> $gadaiData['onepercent'],
                    "ujrah"=> $gadaiData['ujrah']
                ]);
            }
        }

        return response()->json($gadai);
    }

    public function getLaporanGadaiRange($kodcaw,Request $request){

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

        $gadai = Gadai::on($cawangan['mysql'])
                ->where('t_gadai','>=',$request->mula)
                ->where('t_gadai','<=',$request->akhir)
                ->orderBy('teller')
                ->orderBy('t_gadai','asc')
                ->orderBy('sirig','asc')
                ->get();

        return response()->json($gadai);
    }

    public function getLaporanGadaiRangeDate($kodcaw,Request $request){

        $mula = $request->mula;
        $akhir = $request->akhir;

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

        $gadai_data = Gadai::on($cawangan['mysql'])
            ->whereBetween('t_gadai', [$request->mula, $request->akhir])
            ->orderBy('t_gadai')
            ->orderBy('teller')
            ->orderBy('sirig')
            ->get();

        $nokps = $gadai_data->pluck('nokp')->unique();

        $customers = Customer::on('mysql7')
            ->where(function($query) use ($nokps) {
                $query->whereIn('kpbaru', $nokps)
                    ->orWhereIn('kplama', $nokps);
            })
            ->get()
            ->flatMap(fn($c) => [
                $c->kpbaru => $c,
                $c->kplama => $c
        ]);
        $gadai = $gadai_data->map(function ($g) use ($customers) {

            foreach($customers as $key=>$value){
                if($g->nokp == $value->kpbaru || $g->nokp == $value->kplama){
                    $customer = $value;
                }   
            }

            return [
            
                "nama" => optional($customer)->nama,
                "alamat1" => optional($customer)->alamat1,
                "t_gadai" => $g->t_gadai,
                "norujukan" => $g->norujukan,
                "norujukan"=> $g->norujukan,
              
                "nokp"=> $g->nokp,
                "semakbarcode"=> $g->semakbarcode,
                "masa"=> $g->masa,
                "berat"=> $g->berat,
                "nilaimarhun"=> $g->nilaimarhun,
                "pinjaman"=> $g->pinjaman,
                "upah12"=> $g->upah12,
                "percentpinj"=> $g->percentpinj,
                "alamat1"=> optional($customer)->alamat1,
                "teller"=> $g->teller,
                "ujrah"=> $g->ujrah,
                "onepercent"=> $g->onepercent,
                "tagbaru"=> $g->tagbaru,
                "kodkerja"=> optional($customer)->kodkerja,
                "bangsa"=> optional($customer)->bangsa,

            ];
        });
        // dd($gadai);
       
        $statistikService = new StatistikService($kodcaw,$mula,$akhir);
        $reportgadai = $statistikService->LaporanStatistik($gadai);
        //  dd($nokps);

        return response()->json([
        'gadai'      => $gadai,
        'kodcaw'     => $kodcaw,
        'mula'       => $mula,
        'akhir'      => $akhir,
        'statistik'  => $reportgadai,
        'jumlah'     => $gadai->count(),
    ]);
    }

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

        $gadai = BatalGadai::on($cawangan['mysql'])
                ->where('tarikh','=',$tarikh)
                ->orderBy('tarikh','desc')
                ->get();

        return response()->json($gadai);
    }

    public function dashboardall($kodcaw){
        $current = Carbon::now();
        $current = new Carbon();

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

        $array_dashboard = [];

        //Gadai Terkumpul
        $gadai = Gadai::on($cawangan['mysql'])
        // ->where('sttebus','=','')
        ->whereNull('hargajualan')
        ->whereYear('t_gadai','=',$current->year)
        ->sum('pinjaman');

        $gadaitawarruq = Gadai::on($cawangan['mysql'])
        // ->where('sttebus','=','')
        ->whereNotNull('hargajualan')
        ->whereYear('t_gadai','=',$current->year)
        ->sum('pinjaman');

        $tebus = Tebus::on($cawangan['mysql'])
        ->leftJoin('gadai', 'gadai.norujukan', '=', 'tebus.norujukan')
        ->whereNull('gadai.hargajualan')
        ->whereYear('tebus.t_tebus','=',$current->year)
        ->sum('tebus.pinjaman');

        $tebustawarruq = Tebus::on($cawangan['mysql'])
        ->leftJoin('gadai', 'gadai.norujukan', '=', 'tebus.norujukan')
        ->whereNotNull('gadai.hargajualan')
        ->whereYear('tebus.t_tebus','=',$current->year)
        ->sum('tebus.pinjaman');

        $gadvar = $gadai + $gadaitawarruq;
        $tebvar = $tebus + $tebustawarruq;

        //Variasi
        $variasi = $gadvar - $tebvar;


        //Outstanding
        $outstanding = Gadai::on($cawangan['mysql'])
        ->where('sttebus','=','')
        ->sum('pinjaman');

        array_push($array_dashboard,["gadai" => $gadai,"gadaitawarruq" => $gadaitawarruq,"tebus" => $tebus,"tebustawarruq" => $tebustawarruq, "variasi" => $variasi, "outstanding" => $outstanding]);

        return response()->json($array_dashboard);
    }

    public function GadaiTebusList($kodcaw,Request $request){

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


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

        $arrayharian = [];

        $gadai = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','=',$request->tarikh)
        // ->where('sttebus','=','')
        // ->where('teller','=',$request->teller)
        ->where('tagserah','=',1)
        ->orderBy('t_gadai')
        ->get();

        $tebus = Tebus::on($cawangan['mysql'])
        ->where('t_tebus','=',$request->tarikh)
        // ->where('jenistebus','!=','T')
        // ->where('teller','=',$request->teller)
        ->where('tagterima','=',1)
        ->orderBy('t_tebus')
        ->get();

        foreach($gadai as $index=>$g)
        {
            array_push($arrayharian,$g);
        }

        foreach($tebus as $index1=>$t)
        {
            array_push($arrayharian,$t);
        }

        return response()->json($arrayharian,200);

    }

    public function GadaiTebusListTeller($kodcaw,Request $request){
        $current = Carbon::now();
        $current = new Carbon();


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

        $arrayharian = [];

        $gadai = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','=',$request->tarikh)
        ->where('teller','=',$request->teller)
        ->where('tagserah','=',1)
        ->orderBy('t_gadai')
        ->get();

        $tebus = Tebus::on($cawangan['mysql'])
        ->where('t_tebus','=',$request->tarikh)
        ->where('teller','=',$request->teller)
        ->where('tagterima','=',1)
        ->orderBy('t_tebus')
        ->get();

        foreach($gadai as $index=>$g)
        {
            array_push($arrayharian,$g);
        }

        foreach($tebus as $index1=>$t)
        {
            array_push($arrayharian,$t);
        }

        return response()->json($arrayharian,200);
    }

    public function gadaiRingkasanTerkumpul($kodcaw,Request $request){

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

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

        $array_terkumpul = [];

        $gadai = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman')
                ->where('t_gadai','<=',$request->tarikh)
                ->where('sttebus','=','')
                // ->whereYear('t_gadai','=',$current->year)
                ->orderBy('t_gadai','desc')
                ->get();


        // $tebus = Gadai::on($cawangan['mysql'])
        // ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman, SUM(bakiupah) as upahsimpan')
        // ->where('t_gadai','<=',$request->tarikh)
        // ->where('sttebus','=','T')
        // // ->whereYear('t_gadai','=',$current->year)
        // ->orderBy('t_gadai','desc')
        // ->get();

        $tebus = Tebus::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman, SUM(bakiupah) as upahsimpan')
        ->where('t_tebus','<=',$request->tarikh)
        ->where('jenistebus','=','T')
        // ->whereYear('t_gadai','=',$current->year)
        ->orderBy('t_tebus','desc')
        ->get();

        $lelong = Gadai::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman')
        ->where('t_gadai','<=',$request->tarikh)
        ->where('sttebus','=','L')
        // ->whereYear('t_gadai','=',$current->year)
        ->orderBy('t_gadai','desc')
        ->get();

        $lelongrugi = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','<=',$request->tarikh)
        ->where('sttebus','=','L')
        ->where('baki', 'like', '-%')
        ->rightJoin('lelong', 'gadai.norujukan', '=', 'lelong.norujukan')
        ->sum('baki');

        $lelongbaki = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','<=',$request->tarikh)
        ->where('sttebus','=','L')
        ->where('baki', 'not like', '-%')
        ->rightJoin('lelong', 'gadai.norujukan', '=', 'lelong.norujukan')
        ->sum('baki');

        $cajlelong = Lelong::on($cawangan['mysql'])
        ->where('t_jual','<=',$request->tarikh)
        ->sum('cajlelong');


        $lelongupahsimpan = Lelong::on($cawangan['mysql'])
        ->where('t_jual','<=',$request->tarikh)
        ->sum('bakiupah');

        $terimarugilelong = AnsRugi::on($cawangan['mysql'])
        ->where('tarikh','<=',$request->tarikh)
        ->sum('bayaran');

        $bayarlebihanlelong = TuntutBaki::on($cawangan['mysql'])
        ->where('tarikh','<=',$request->tarikh)
        ->sum('jumlah');

        $hapuskira = HapusKira::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(gadai.nilaimarhun) as jumlahmarhun, SUM(gadai.pinjaman) as jumlahpinjaman')
        ->where('hapuskira.Tarikh','<=',$request->tarikh)
        ->rightJoin('gadai', 'gadai.norujukan', '=', 'hapuskira.norujukan')
        ->get();

        $advansur = Tebus::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman, SUM(bakiupah) as upahsimpan')
        ->where('t_tebus','<=',$request->tarikh)
        ->where('jenistebus','=','A')
        ->get();



        array_push($array_terkumpul,["gadai" => $gadai,"tebus" => $tebus, "lelong" => $lelong,"lelongbaki" => $lelongbaki,"lelongrugi" => $lelongrugi,"bayarlebihanlelong" => $bayarlebihanlelong, "terimarugilelong" => $terimarugilelong, "cajlelong" => $cajlelong, "lelongupahsimpan" => $lelongupahsimpan,"hapuskira" => $hapuskira,"advansur" => $advansur]);

        return response()->json($array_terkumpul);
    }

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

        $array_harian = [];

        $gadai = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman')
                ->where('t_gadai','=',$request->tarikh)
                ->orderBy('t_gadai')
                ->get();

        $tebus = Tebus::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman, SUM(bakiupah) as upahsimpan')
        ->where('t_tebus','=',$request->tarikh)
        ->where('jenistebus','=','T')
        ->orderBy('t_tebus')
        ->get();

        $lelong = Gadai::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman')
        ->where('t_gadai','=',$request->tarikh)
        ->where('sttebus','=','L')
        ->orderBy('t_gadai')
        ->get();

        $lelongrugi = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','=',$request->tarikh)
        ->where('sttebus','=','L')
        ->where('baki', 'like', '-%')
        ->rightJoin('lelong', 'gadai.norujukan', '=', 'lelong.norujukan')
        ->sum('baki');

        $lelongbaki = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','=',$request->tarikh)
        ->where('sttebus','=','L')
        ->where('baki', 'not like', '-%')
        ->rightJoin('lelong', 'gadai.norujukan', '=', 'lelong.norujukan')
        ->sum('baki');

        $cajlelong = Lelong::on($cawangan['mysql'])
        ->where('t_jual','=',$request->tarikh)
        ->sum('cajlelong');


        $lelongupahsimpan = Lelong::on($cawangan['mysql'])
        ->where('t_jual','=',$request->tarikh)
        ->sum('bakiupah');

        $terimarugilelong = AnsRugi::on($cawangan['mysql'])
        ->where('tarikh','=',$request->tarikh)
        ->sum('bayaran');

        $bayarlebihanlelong = TuntutBaki::on($cawangan['mysql'])
        ->where('tarikh','=',$request->tarikh)
        ->sum('jumlah');

        $wtd = WTD::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(gadai.nilaimarhun) as jumlahmarhun, SUM(wtd.pinjaman) as jumlahpinjaman')
        ->where('wtd.tarikh','=',$request->tarikh)
        ->rightJoin('gadai', 'gadai.norujukan', '=', 'wtd.norujukan')
        ->get();

        $hapuskira = HapusKira::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(gadai.nilaimarhun) as jumlahmarhun, SUM(gadai.pinjaman) as jumlahpinjaman')
        ->where('hapuskira.Tarikh','=',$request->tarikh)
        ->rightJoin('gadai', 'gadai.norujukan', '=', 'hapuskira.norujukan')
        ->get();

        $advansur = Tebus::on($cawangan['mysql'])
        ->selectRaw('COUNT(*) AS bilangan, SUM(nilaimarhun) as jumlahmarhun, SUM(pinjaman) as jumlahpinjaman, SUM(bakiupah) as upahsimpan')
        ->where('t_tebus','=',$request->tarikh)
        ->where('jenistebus','=','A')
        ->get();

        array_push($array_harian,["gadai" => $gadai,"tebus" => $tebus, "lelong" => $lelong,"lelongbaki" => $lelongbaki,"lelongrugi" => $lelongrugi,"bayarlebihanlelong" => $bayarlebihanlelong, "terimarugilelong" => $terimarugilelong, "cajlelong" => $cajlelong, "lelongupahsimpan" => $lelongupahsimpan, "wtd" => $wtd,"hapuskira" => $hapuskira,"advansur" => $advansur]);

        return response()->json($array_harian);
    }

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

        $dateans = new Carbon($request->tarikh);

        $ansuran_data = Transaction::on('mysql7')
                ->select('transaction.norujukan',
                         'trans.tarikh_bayaran',
                         'transaction.nokp',
                         'transaction.kodcaw',
                         'transaction.trans_type',
                         'cust.nama','trans.bayaran_pembiayaan',
                         'transaction.duit_masuk',
                         'trans.bayaran_keuntungan',
                         'transaction.teller',
                         'trans.ujrah',
                         'trans.onepercent')
                ->leftJoin('transaksi_keuntungan as trans', function($join)
                {
                    $join->on('transaction.norujukan', '=', 'trans.norujukan');
                    $join->on('transaction.created_at', '=', 'trans.tarikh_bayaran');
                })
                ->leftJoin('customer as cust', function($join)
                {
                    $join->on('transaction.nokp', '=', 'cust.kpbaru');
                    $join->orOn('transaction.nokp', '=', 'cust.kplama');
                })
                ->where('transaction.kodcaw','=',$kodcaw)
                ->where('transaction.trans_type','=','A')
                ->whereDate('transaction.created_at','=',$dateans)
                ->orderBy('transaction.teller')
                ->get();

        $ansuran = [];

        if($ansuran_data){
            foreach($ansuran_data as $index=>$ansuranData){
                $gadai = Gadai::on($cawangan['mysql'])->where('norujukan',$ansuranData['norujukan'])->first();

                array_push($ansuran,[
                    "norujukan" => $ansuranData['norujukan'],
                    "tarikh_bayaran" => $ansuranData['tarikh_bayaran'],
                    "nokp" => $ansuranData['nokp'],
                    "kodcaw" => $ansuranData['kodcaw'],
                    "trans_type" => $ansuranData['trans_type'],
                    "nama" => $ansuranData['nama'],
                    "tempoh" => $gadai['tempoh'],
                    "bakipjm" => $gadai['bakipjm'],
                    "bayaran_pembiayaan" => $ansuranData['bayaran_pembiayaan'],
                    "duit_masuk" => $ansuranData['duit_masuk'],
                    "bayaran_keuntungan" => $ansuranData['bayaran_keuntungan'],
                    "teller" => $ansuranData['teller'],
                    "ansuranujrah" => $ansuranData['ujrah'],
                    "ansuranonepercent" => $ansuranData['onepercent'],


                ]);
            }
        }

        return response()->json($ansuran);
    }

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

    // Bina query asas dahulu
    $query = Transaction::on('mysql7')
        ->select(
            'transaction.norujukan',
            'trans.tarikh_bayaran',
            'transaction.nokp',
            'transaction.kodcaw',
            'transaction.trans_type',
            'cust.nama',
            'trans.bayaran_pembiayaan',
            'transaction.duit_masuk',
            'trans.bayaran_keuntungan',
            'transaction.teller',
            'trans.ujrah',
            'trans.onepercent'
        )
        ->leftJoin('transaksi_keuntungan as trans', function ($join) {
            $join->on('transaction.norujukan', '=', 'trans.norujukan');
            $join->on('transaction.created_at', '=', 'trans.tarikh_bayaran');
        })
        ->leftJoin('customer as cust', function ($join) {
            $join->on('transaction.nokp', '=', 'cust.kpbaru');
            $join->orOn('transaction.nokp', '=', 'cust.kplama');
        })
        ->where('transaction.kodcaw', '=', $kodcaw)
        ->where('transaction.trans_type', '=', 'A')
        ->whereDate('transaction.created_at', '>=', $request->mula)
        ->whereDate('transaction.created_at', '<=', $request->akhir);

    // Tambah filter berdasarkan jenis
    if ($request->jenis == 'arrahnpay') {
        $query->where('transaction.teller', '=', 'Online');
    } elseif ($request->jenis == 'kaunter') {
        $query->where('transaction.teller', '!=', 'Online');
    }

    // Dapatkan data selepas filter
    $ansuran_data = $query
        ->orderBy('transaction.teller')
        ->orderBy('trans.tarikh_bayaran')
        ->get();

    $ansuran = [];

    // Loop data dan tambahkan maklumat dari jadual gadai
    if ($ansuran_data) {
        foreach ($ansuran_data as $index => $ansuranData) {
            $gadai = Gadai::on($cawangan['mysql'])->where('norujukan', $ansuranData['norujukan'])->first();

            array_push($ansuran, [
                "norujukan" => $ansuranData['norujukan'],
                "tarikh_bayaran" => $ansuranData['tarikh_bayaran'],
                "nokp" => $ansuranData['nokp'],
                "kodcaw" => $ansuranData['kodcaw'],
                "trans_type" => $ansuranData['trans_type'],
                "nama" => $ansuranData['nama'],
                "tempoh" => optional($gadai)->tempoh,
                "bakipjm" => optional($gadai)->bakipjm,
                "bayaran_pembiayaan" => $ansuranData['bayaran_pembiayaan'],
                "duit_masuk" => $ansuranData['duit_masuk'],
                "bayaran_keuntungan" => $ansuranData['bayaran_keuntungan'],
                "teller" => $ansuranData['teller'],
                "ansuranujrah" => $ansuranData['ujrah'],
                "ansuranonepercent" => $ansuranData['onepercent'],
                "t_gadai" => optional($gadai)->t_gadai,
            ]);
        }
    }

    // Susun ikut teller dan tarikh bayar
    usort($ansuran, function ($a, $b) {
        $compare_teller = strcmp($a['teller'], $b['teller']);

        if ($compare_teller === 0) {
            return strcmp($a['tarikh_bayaran'], $b['tarikh_bayaran']);
        }

        return $compare_teller;
    });

    return response()->json($ansuran);
}


//     public function getLaporanAnsuranUjrah($kodcaw, Request $request)
// {
    // $cawangan = Cawangan::on('mysql7')->where('kodcaw', '=', $kodcaw)->first();
    // $semuaDescpt = Transaction::on('mysql7')
    // ->where('kodcaw', '=', $kodcaw)
    // ->pluck('descpt');

    // dd($semuaDescpt);

    // Dapatkan connection cawangan
//     $connection = $cawangan['mysql'];

//     // Mula query utama ansuran
//     $query = Transaction::on('mysql7')
//         ->select(
//             'transaction.norujukan',
//             'trans.tarikh_bayaran',
//             'transaction.nokp',
//             'transaction.kodcaw',
//             'transaction.trans_type',
//             'cust.nama',
//             'trans.bayaran_pembiayaan',
//             'transaction.duit_masuk',
//             'trans.bayaran_keuntungan',
//             'transaction.teller',
//             'trans.ujrah',
//             'trans.onepercent'
//         )
//         ->leftJoin('transaksi_keuntungan as trans', function ($join) {
//             $join->on('transaction.norujukan', '=', 'trans.norujukan');
//             $join->on('transaction.created_at', '=', 'trans.tarikh_bayaran');
//         })
//         ->leftJoin('customer as cust', function ($join) {
//             $join->on('transaction.nokp', '=', 'cust.kpbaru');
//             $join->orOn('transaction.nokp', '=', 'cust.kplama');
//         })
//         ->where('transaction.kodcaw', '=', $kodcaw)
//         ->where('transaction.trans_type', '=', 'A')
//         ->whereDate('transaction.created_at', '>=', $request->mula)
//         ->whereDate('transaction.created_at', '<=', $request->akhir);


//     // 💡 Tambah filter ikut jenis
//     if ($request->jenis == 'online') {
//         $query->where('transaction.descpt', 'LIKE', 'Laporan Ansuran Pembiayaan (Arrahn Pay)%');
//     } elseif ($request->jenis == 'kaunter') {
//         $query->where('transaction.descpt', 'LIKE', 'Laporan Ansuran Pembiayaan (Kaunter aros)%');
//     } else {
//         $query->where('transaction.descpt', 'LIKE', 'Laporan Ansuran Pembiayaan%');
//     }

//     // Order by
//     $query->orderBy('transaction.teller')->orderBy('trans.tarikh_bayaran');

//     // Dapatkan data
//     $ansuran_data = $query->get();

//     // Proses data
//     $ansuran = [];
//     if ($ansuran_data) {
//         foreach ($ansuran_data as $index => $ansuranData) {
//             $gadai = Gadai::on($connection)->where('norujukan', $ansuranData['norujukan'])->first();

//             array_push($ansuran, [
//                 "norujukan" => $ansuranData['norujukan'],
//                 "tarikh_bayaran" => $ansuranData['tarikh_bayaran'],
//                 "nokp" => $ansuranData['nokp'],
//                 "kodcaw" => $ansuranData['kodcaw'],
//                 "trans_type" => $ansuranData['trans_type'],
//                 "nama" => $ansuranData['nama'],
//                 "tempoh" => $gadai['tempoh'] ?? null,
//                 "bakipjm" => $gadai['bakipjm'] ?? null,
//                 "bayaran_pembiayaan" => $ansuranData['bayaran_pembiayaan'],
//                 "duit_masuk" => $ansuranData['duit_masuk'],
//                 "bayaran_keuntungan" => $ansuranData['bayaran_keuntungan'],
//                 "teller" => $ansuranData['teller'],
//                 "ansuranujrah" => $ansuranData['ujrah'],
//                 "ansuranonepercent" => $ansuranData['onepercent'],
//                 "t_gadai" => $gadai['t_gadai'] ?? null,
//             ]);
//         }
//     }

//     // Susun data
//     usort($ansuran, function ($a, $b) {
//         $compare_teller = strcmp($a['teller'], $b['teller']);
//         return $compare_teller === 0
//             ? strcmp($a['tarikh_bayaran'], $b['tarikh_bayaran'])
//             : $compare_teller;
//     });

//     return response()->json($ansuran);
// }

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

        $dateans = new Carbon($request->tarikh);

        $ansuranskaunter = 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','=',$kodcaw)
                ->where('transaction.trans_type','=','A')
                ->where('transaction.teller','<>','Online')
                ->whereDate('transaction.created_at','=',$dateans);

        $ansuransonline = 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','=',$kodcaw)
                ->where('transaction.trans_type','=','A')
                ->where('transaction.teller','=','Online')
                ->whereDate('transaction.created_at','=',$dateans);

        $keuntungankaunter = $ansuranskaunter->sum('trans.bayaran_keuntungan');
        $pendahuluankaunter = $ansuranskaunter->sum('trans.bayaran_pembiayaan');
        $bilangankaunter = $ansuranskaunter->count();
        $ujrahkaunter = $ansuranskaunter->sum('trans.ujrah');
        $onepercentkaunter = $ansuranskaunter->sum('trans.onepercent');

        $keuntunganonline = $ansuransonline->sum('trans.bayaran_keuntungan');
        $pendahuluanonline = $ansuransonline->sum('trans.bayaran_pembiayaan');
        $bilanganonline = $ansuransonline->count();
        $ujrahonline = $ansuransonline->sum('trans.ujrah');
        $onepercentonline = $ansuransonline->sum('trans.onepercent');

        $arrayansuran = [
                            'totalpendahuluankaunter' => $pendahuluankaunter,
                            'totalkeuntungankaunter' => $keuntungankaunter,
                            'bilangankaunter' => $bilangankaunter,
                            'totalujrahkaunter' => $ujrahkaunter,
                            'totalonepercentkaunter' => $onepercentkaunter,

                            'totalpendahuluanonline' => $pendahuluanonline,
                            'totalkeuntunganonline' => $keuntunganonline,
                            'bilanganonline' => $bilanganonline,
                            'totalujrahonline' => $ujrahonline,
                            'totalonepercentonline' => $onepercentonline
                        ];

        return response()->json($arrayansuran,200);
    }

    public function getAnsuranSummaryRange($kodcaw,Request $request){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $dateans = new Carbon($request->mula);
        $dateans2 = new Carbon($request->akhir);

        $ansuranskaunter = 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','=',$kodcaw)
                ->where('transaction.trans_type','=','A')
                ->where('transaction.teller','<>','Online')
                ->whereDate('transaction.created_at','>=',$dateans)
                ->whereDate('transaction.created_at','<=',$dateans2);

        $ansuransonline = 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','=',$kodcaw)
                ->where('transaction.trans_type','=','A')
                ->where('transaction.teller','=','Online')
                ->whereDate('transaction.created_at','>=',$dateans)
                ->whereDate('transaction.created_at','<=',$dateans2);

        $keuntungankaunter = $ansuranskaunter->sum('trans.bayaran_keuntungan');
        $pendahuluankaunter = $ansuranskaunter->sum('trans.bayaran_pembiayaan');
        $bilangankaunter = $ansuranskaunter->count();
        $ujrahkaunter = $ansuranskaunter->sum('trans.ujrah');
        $onepercentkaunter = $ansuranskaunter->sum('trans.onepercent');


        $keuntunganonline = $ansuransonline->sum('trans.bayaran_keuntungan');
        $pendahuluanonline = $ansuransonline->sum('trans.bayaran_pembiayaan');
        $bilanganonline = $ansuransonline->count();
        $ujrahonline = $ansuransonline->sum('trans.ujrah');
        $onepercentonline = $ansuransonline->sum('trans.onepercent');

        $arrayansuran = [
                            'totalpendahuluankaunter' => $pendahuluankaunter,
                            'totalkeuntungankaunter' => $keuntungankaunter,
                            'bilangankaunter' => $bilangankaunter,
                            'totalujrahkaunter' => $ujrahkaunter,
                            'totalonepercentkaunter' => $onepercentkaunter,

                            'totalpendahuluanonline' => $pendahuluanonline,
                            'totalkeuntunganonline' => $keuntunganonline,
                            'bilanganonline' => $bilanganonline,
                            'totalujrahonline' => $ujrahonline,
                            'totalonepercentonline' => $onepercentonline,
                        ];

        return response()->json($arrayansuran,200);
    }

    public function updateHistoryGadai($kodcaw,$norujukan,Request $request){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();

        $updatehistory = Gadai::on($cawangan['mysql'])
                        ->where('norujukan','=',$norujukan)
                        ->update(array(
                            'historygadaian' => $request->historygadaian,
                            'tag_tebus' => $request->jenistebus,
                        ));

        return response()->json($updatehistory,200);
    }

    public function getLaporanKawalanStok($kodcaw,$tarikh){

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

        $array_kawalanstok = [];


        /* Gadai */
        $gadai1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(*) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','<',$tarikh)
                ->where('tagpalsu',0)
                ->whereBetween('nilaimarhun', [1, 400.99])
                ->get();

        $gadai2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(*) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','<',$tarikh)
                ->where('tagpalsu',0)
                ->whereBetween('nilaimarhun', [401, 2000.99])
                ->get();

        $gadai3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(*) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','<',$tarikh)
                ->where('tagpalsu',0)
                ->whereBetween('nilaimarhun', [2001, 1000000])
                ->get();


        $gadaiini1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','=',$tarikh)
                ->where('tagpalsu',0)
                // ->whereBetween('nilaimarhun', [1, 400.99])
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                ->get();

        $gadaiini2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','=',$tarikh)
                ->where('tagpalsu',0)
                // ->whereBetween('nilaimarhun', [401, 2000.99])
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                ->get();

        $gadaiini3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('t_gadai','=',$tarikh)
                ->where('tagpalsu',0)
                // ->whereBetween('nilaimarhun', [2001, 100000])
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                ->get();

        /*tebus*/

        $tebus1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                ->get();

        $tebus2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                ->get();


        $tebus3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                ->get();

        $tebusini1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                ->get();

        $tebusini2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                ->get();

        $tebusini3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','T')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                ->get();


        /*lelong*/

        $lelong1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                ->get();


        $lelong2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                ->get();

        $lelong3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                // ->where('t_update','<',$tarikh)
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                ->get();

        $lelongini1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                ->get();

        $lelongini2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                ->get();

        $lelongini3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','L')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                ->get();


        /*ansuran*/

        $daterequest = new Carbon($tarikh);

        $ansuran1_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.created_at','<',$daterequest)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');

        $ansuran1 = [];

        $ansuran1 = Gadai::on($cawangan['mysql'])
            ->whereIn('norujukan',$ansuran1_data)
            // ->whereBetween('nilaimarhun', [1, 400.99])
            ->where([['nilaimarhun','>=',1],['nilaimarhun','<=',400.99],['sttebus',''],['hargajualan','<>','null']])
            ->orWhere([['nilaimarhun','>=',1],['nilaimarhun','<=',400.99],['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
            ->pluck('norujukan');

        $transaksi1 = TransaksiKeuntungan::on('mysql7')
            ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
            ->whereIn('norujukan',$ansuran1)
            ->where('kodcaw',$kodcaw)
            //->where('bayaran_pembiayaan','>',0)
            ->where('tarikh_bayaran' ,'<=', $daterequest)
            ->get();


        $ansuranharini1_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');
                         })
                    ->whereDate('trans.created_at','=',$tarikh)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');


        $ansuran1harini = Gadai::on($cawangan['mysql'])
                ->whereIn('norujukan',$ansuranharini1_data)
                // ->whereBetween('nilaimarhun', [1, 400.99])
                ->where([['nilaimarhun','>=',1],['nilaimarhun','<=',400.99],['sttebus',''],['hargajualan','<>','null']])
                // ->orWhere([['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
                ->pluck('norujukan');

        $transaksi1harini = TransaksiKeuntungan::on('mysql7')
                ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
                ->whereIn('norujukan',$ansuran1harini)
                ->whereDate('tarikh_bayaran','=',$tarikh)
                ->where('kodcaw',$kodcaw)
                //->where('bayaran_pembiayaan','>',0)
                ->get();


        $ansuran2_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.created_at','<',$daterequest)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');


        $ansuran2 = Gadai::on($cawangan['mysql'])
                ->whereIn('norujukan',$ansuran2_data)
                // ->whereBetween('nilaimarhun', [401, 2000.99])
                ->where([['nilaimarhun','>=',401],['nilaimarhun','<=',2000.99],['sttebus',''],['hargajualan','<>','null']])
                ->orWhere([['nilaimarhun','>=',401],['nilaimarhun','<=',2000.99],['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
                ->pluck('norujukan');

        $transaksi2 = TransaksiKeuntungan::on('mysql7')
                ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
                ->whereIn('norujukan',$ansuran2)
                ->where('tarikh_bayaran' ,'<=', $daterequest)
                ->where('kodcaw',$kodcaw)
                //->where('bayaran_pembiayaan','>',0)
                ->get();

        $ansuranharini2_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');
                         })
                    ->whereDate('trans.created_at','=',$tarikh)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');


        $ansuran2harini = Gadai::on($cawangan['mysql'])
                ->whereIn('norujukan',$ansuranharini2_data)
                // ->whereBetween('nilaimarhun', [401, 2000.99])
                ->where([['nilaimarhun','>=',401],['nilaimarhun','<=',2000.99],['sttebus',''],['hargajualan','<>','null']])
                // ->orWhere([['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
                ->pluck('norujukan');

        $transaksi2harini = TransaksiKeuntungan::on('mysql7')
                ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
                ->whereIn('norujukan',$ansuran2harini)
                ->whereDate('tarikh_bayaran','=',$tarikh)
                ->where('kodcaw',$kodcaw)
                //->where('bayaran_pembiayaan','>',0)
                ->get();

        $ansuran3_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.created_at','<',$daterequest)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');


        $ansuran3 = Gadai::on($cawangan['mysql'])
                ->whereIn('norujukan',$ansuran3_data)
                // ->whereBetween('nilaimarhun', [2001, 1000000])
                ->where([['nilaimarhun','>=',2001],['nilaimarhun','<=',1000000],['sttebus',''],['hargajualan','<>','null']])
                ->orWhere([['nilaimarhun','>=',2001],['nilaimarhun','<=',1000000],['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
                ->pluck('norujukan');

        $transaksi3 = TransaksiKeuntungan::on('mysql7')
                ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
                ->whereIn('norujukan',$ansuran3)
                ->where('tarikh_bayaran' ,'<=', $daterequest)
                ->where('kodcaw',$kodcaw)
                //->where('bayaran_pembiayaan','>',0)
                ->get();


        $ansuranharini3_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');
                         })
                    ->whereDate('trans.created_at','=',$tarikh)
                    ->where('trans.trans_type','=','A')
                    ->groupBy('trans.id')
                    ->where('trans.kodcaw',$kodcaw)
                    ->pluck('trans.norujukan');


        $ansuran3harini = Gadai::on($cawangan['mysql'])
                ->whereIn('norujukan',$ansuranharini3_data)
                // ->whereBetween('nilaimarhun', [2001, 1000000])
                ->where([['nilaimarhun','>=',2001],['nilaimarhun','<=',1000000],['sttebus',''],['hargajualan','<>','null']])
                // ->orWhere([['sttebus','!=',''],['t_update','>',$tarikh],['hargajualan','<>','null']])
                ->pluck('norujukan');

        $transaksi3harini = TransaksiKeuntungan::on('mysql7')
                ->selectRaw('COUNT(*) as bil, SUM(bayaran_pembiayaan) AS total')
                ->whereIn('norujukan',$ansuran3harini)
                ->whereDate('tarikh_bayaran','=',$tarikh)
                ->where('kodcaw',$kodcaw)
                //->where('bayaran_pembiayaan','>',0)
                ->get();


        ##push in array##
        $arrayans1 = ['bil' => $transaksi1[0]->bil, 'pinj' => $transaksi1[0]->total];
        $arrayharinians1 = ['bil' => $transaksi1harini[0]->bil, 'pinj' => $transaksi1harini[0]->total];
        $arrayans2 = ['bil' => $transaksi2[0]->bil, 'pinj' => $transaksi2[0]->total];
        $arrayharinians2 = ['bil' => $transaksi2harini[0]->bil, 'pinj' => $transaksi2harini[0]->total];
        $arrayans3 = ['bil' => $transaksi3[0]->bil, 'pinj' => $transaksi3[0]->total];
        $arrayharinians3 = ['bil' => $transaksi3harini[0]->bil, 'pinj' => $transaksi3harini[0]->total];


        /*hapus kira*/

        $hapuskira1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                ->where('sttebus','H')
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                // ->orderBy('tarikh')
                ->get();

        $hapuskira2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                // ->where('tarikh','<',$tarikh)
                ->where('sttebus','H')
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                // ->orderBy('tarikh')
                ->get();

        $hapuskira3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                // ->where('tarikh','<',$tarikh)
                ->where('sttebus','H')
                ->where(function ($query) use($tarikh){
                    $query->where('t_update','<',$tarikh)
                    ->orWhereNull('t_update');
                })
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                // ->orderBy('tarikh')
                ->get();

        $hapuskiraini1 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                // ->where('tarikh','=',$tarikh)
                ->where('sttebus','H')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',1)
                ->where('nilaimarhun','<=',400.99)
                // ->orderBy('tarikh')
                ->get();

        $hapuskiraini2 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                // ->where('tarikh','=',$tarikh)
                ->where('sttebus','H')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',401)
                ->where('nilaimarhun','<=',2000.99)
                // ->orderBy('tarikh')
                ->get();

        $hapuskiraini3 = Gadai::on($cawangan['mysql'])
                ->selectRaw('COUNT(norujukan) AS bil, SUM(pinjaman) AS pinj')
                // ->where('tarikh','=',$tarikh)
                ->where('sttebus','H')
                ->where('t_update','=',$tarikh)
                ->where('tagpalsu',0)
                ->where('nilaimarhun','>=',2001)
                ->where('nilaimarhun','<=',1000000)
                // ->orderBy('tarikh')
                ->get();


        array_push($array_kawalanstok,["gadai1" => $gadai1, "gadai2" => $gadai2, "gadai3" => $gadai3,"gadaiini1" => $gadaiini1, "gadaiini2" => $gadaiini2, "gadaiini3" => $gadaiini3]);
        array_push($array_kawalanstok,["tebus1" => $tebus1, "tebus2" => $tebus2, "tebus3" => $tebus3,"tebusini1" => $tebusini1, "tebusini2" => $tebusini2, "tebusini3" => $tebusini3]);
        array_push($array_kawalanstok,["lelong1" => $lelong1, "lelong2" => $lelong2, "lelong3" => $lelong3,"lelongini1" => $lelongini1, "lelongini2" => $lelongini2, "lelongini3" => $lelongini3]);
        array_push($array_kawalanstok,["hapuskira1" => $hapuskira1, "hapuskira2" => $hapuskira2, "hapuskira3" => $hapuskira3,"hapuskiraini1" => $hapuskiraini1, "hapuskiraini2" => $hapuskiraini2, "hapuskiraini3" => $hapuskiraini3]);
        array_push($array_kawalanstok,["ans1" => $arrayans1, "ans2" => $arrayans2, "ans3" => $arrayans3,"ansini1" => $arrayharinians1, "ansini2" => $arrayharinians2, "ansini3" => $arrayharinians3]);
        return response()->json($array_kawalanstok);

    }

    public function showAllGadaiToday(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $lastweek = $current->subWeek()->toDateString();
        $gadaiToday = 0;
        $gadaiLastweek = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$harini)->sum('pinjaman');
            $gadai_last_week = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$lastweek)->sum('pinjaman');
            $gadaiLastweek += $gadai_last_week;
            $gadaiToday += $gadai;
        }
        $diffGadaiPercent = 0;
        if($gadaiLastweek != 0){
            $diffGadaiPercent = (($gadaiToday - $gadaiLastweek)/$gadaiLastweek)*100;
        }
        $dataGadai = [];
        array_push($dataGadai,['gadaiToday'=>$gadaiToday,'percentDiff'=>$diffGadaiPercent]);

        return response()->json($dataGadai);

    }

    public function showAllTebusToday(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $lastweek = $current->subWeek()->toDateString();
        $tebusToday = 0;
        $tebusLastWeek = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $tebus = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$harini)->sum('bakipjm');
            $tebus_last_week = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$lastweek)->sum('bakipjm');

            $tebusToday += $tebus;
            $tebusLastWeek += $tebus_last_week;
        }
        $diffTebusPercent = 0;
        if($tebusLastWeek != 0){
            $diffTebusPercent = (($tebusToday - $tebusLastWeek)/$tebusLastWeek)*100;
        }
        $dataTebus = [];
        array_push($dataTebus,['tebusToday'=>$tebusToday,'percentDiff'=>$diffTebusPercent]);
        return response()->json($dataTebus);
    }

    public function showAllVariasiToday(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $lastweek = $current->subWeek()->toDateString();
        $tebusToday = 0;
        $gadaiToday = 0;
        $tebusLastWeek = 0;
        $gadaiLastWeek = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $tebus = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$harini)->sum('bakipjm');
            $gadai = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$harini)->sum('pinjaman');
            $gadai_last_week = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$lastweek)->sum('pinjaman');
            $tebus_last_week = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$lastweek)->sum('bakipjm');

            $tebusToday += $tebus;
            $gadaiToday += $gadai;
            $tebusLastWeek += $tebus_last_week;
            $gadaiLastWeek += $gadai_last_week;
        }
        $variasiToday = $gadaiToday - $tebusToday;
        $variasiLastWeek = $gadaiLastWeek - $tebusLastWeek;
        $percentDiffVariasi = 0;
        if($variasiLastWeek != 0){
            $percentDiffVariasi = (($variasiToday - $variasiLastWeek)/$variasiLastWeek)*100;
        }
        $dataVariasi = [];
        array_push($dataVariasi,['variasiToday'=>$variasiToday,'percentVariasi'=>$percentDiffVariasi]);
        return response()->json($dataVariasi);
    }
    public function showAllUpahToday(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $lastweek = $current->subWeek()->toDateString();
        $upahToday = 0;
        $upahLastWeek = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $upah_tebus_today = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$harini)->sum('bakiupah');
            $upah_today = Advansur::on($cawangan['mysql'])->where('tarikh',$harini)->sum('upahDibayar');

            $upah_tebus_lastweek = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$lastweek)->sum('bakiupah');
            $upah_lastweek = Advansur::on($cawangan['mysql'])->where('tarikh',$lastweek)->sum('upahDibayar');

            $upahToday = $upahToday + $upah_tebus_today + $upah_today;
            $upahLastWeek = $upahLastWeek + $upah_tebus_lastweek + $upah_lastweek;
        }
        $ansuran_today = TransaksiKeuntungan::on('mysql7')->where([['tarikh_bayaran','>=',$harini . ' 00:00:00'],['tarikh_bayaran','<=',$harini . ' 23:59:59']])->sum('bayaran_keuntungan');
        $ansuran_lastweek = TransaksiKeuntungan::on('mysql7')->where([['tarikh_bayaran','>=',$lastweek . ' 00:00:00'],['tarikh_bayaran','<=',$lastweek . ' 23:59:59']])->sum('bayaran_keuntungan');

        $upahToday = $upahToday + $ansuran_today;
        $upahLastWeek = $upahLastWeek + $ansuran_lastweek;
        $diffUpah = 0;
        if($upahLastWeek != 0){
            $diffUpah = (($upahToday - $upahLastWeek)/$upahLastWeek)*100;
        }
        $upahData = [];
        array_push($upahData,['upahToday'=>$upahToday,'percentDiff'=>$diffUpah]);
        return response()->json($upahData);
    }
    public function showAllGadaiTerkumpul(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $gadaiTerkumpul = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $gadaiterkumpul = Gadai::on($cawangan['mysql'])->where('sttebus','=','')->whereYear('t_gadai','=',$current->year)->get();
            if(sizeof($gadaiterkumpul) != 0){
                foreach($gadaiterkumpul as $gadaiterkumpulall)
                {
                    $gadaiTerkumpul += $gadaiterkumpulall['pinjaman'];
                }
            }
        }
        return response()->json(number_format($gadaiTerkumpul,2));
    }

    public function showTebusTerkumpul(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $tebusTerkumpul = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $tebus = Tebus::on('mysql6')->where('jenistebus','=','T')->whereYear('t_tebus','=',$current->year)->sum('pinjaman');
            $tebusTerkumpul += $tebus;
        }

        return response()->json(number_format($tebusTerkumpul,2));
    }

    public function showUpahTerkumpul(){

        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $upahTerkumpul = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $upah = Tebus::on($cawangan['mysql'])->whereYear('t_tebus','=',$current->year)->get();
            if(sizeof($upah) != 0){
                foreach($upah as $upahall)
                {
                    $upahTerkumpul += $upahall['bakiupah'];
                }
            }
        }
        return response()->json(number_format($upahTerkumpul,2));
    }

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

        $total_pembiayaan = 0;
        $gadai = Gadai::on($cawangan['mysql'])->where([['nokp','=',$ic],['sttebus','=','']])->get();
        if(is_array($gadai) || is_object($gadai)){
            foreach($gadai as $index=>$gadai_detail){
                if($gadai_detail['pinjaman'] != 0 || $gadai_detail['pinjaman'] != null){
                    $total_pembiayaan += $gadai_detail['bakipjm'];
                }else{
                    $total_pembiayaan += $gadai_detail['pinjaman'];
                }

            }
        }

        return response()->json($total_pembiayaan);
    }

    public function outstandingSemasa()
    {
        $cawangan_all = Cawangan::on('mysql7')->get();
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $lastweek = $current->subWeek()->toDateString();
        $outstandingSemasa = 0;
        $outstandingLastWeek = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $gadaiactivetawarruq = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['hargajualan','<>',null]])->sum('bakipjm');
            $gadaiactivearrahn = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['hargajualan',null]])->sum('pinjaman');

            $gadaiactivetawarruqLastWeek = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['hargajualan','<>',null],['t_gadai','<=',$lastweek]])->sum('bakipjm');
            $gadaiactivearrahnLastWeek = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['hargajualan',null],['t_gadai','<=',$lastweek]])->sum('pinjaman');

            $totalgadaiactive = $gadaiactivetawarruq + $gadaiactivearrahn;
            $totalgadaiactiveLastWeek = $gadaiactivetawarruqLastWeek + $gadaiactivearrahnLastWeek;

            $outstandingSemasa += $totalgadaiactive;
            $outstandingLastWeek += $totalgadaiactiveLastWeek;
        }
        $percentDiffOutstanding = 0;
        if($outstandingLastWeek != 0){
            $percentDiffOutstanding = (($outstandingSemasa - $outstandingLastWeek)/$outstandingLastWeek)*100;
        }
        $dataOutstanding = [];
        array_push($dataOutstanding,['outstandingToday'=>$outstandingSemasa,'percentOutstanding'=>$percentDiffOutstanding]);
        return response()->json($dataOutstanding);
    }

    public function oneWeekData(){
        $weekly = Carbon::now();
        $weekly = new Carbon();
        $cawangan_all = Cawangan::on('mysql7')->get();
        $dataOneWeek = [];
        for($i=0;$i<7;$i++){
            if($i == 0){
                $weekly_array_date = $weekly->subDays(0);
            }else{
                $weekly_array_date = $weekly->subDays(1);
            }
            array_push($dataOneWeek,['tarikh'=>$weekly_array_date->toDateString(),'totalpembiayaan'=>0,'totaltebus'=>0,'totalupah'=>0, 'totalkomoditi'=>0,'totalnilaimarhun'=>0,'onlinepayment'=>0,'hargaemas'=>0]);
        }

        foreach($dataOneWeek as $index=>$oneWeek){
            $totalGadai = 0;
            $totalTebus = 0;
            $totalUpah = 0;
            $totalKomoditi = 0;
            $totalNilaiMarhun = 0;
            $totalOnline = 0;
            $upah = 0;

            foreach($cawangan_all as $index2=>$cawangan){
                $gadaiCawangan = Gadai::on($cawangan['mysql'])->where('t_gadai',$oneWeek['tarikh'])->sum('pinjaman');
                $tebusCawangan = Tebus::on($cawangan['mysql'])->where('t_tebus',$oneWeek['tarikh'])->sum('bakipjm');
                $upahTebusCawangan = Tebus::on($cawangan['mysql'])->where('t_tebus',$oneWeek['tarikh'])->sum('bakiupah');
                $nilaiMarhunCawangan = Gadai::on($cawangan['mysql'])->where('t_gadai',$oneWeek['tarikh'])->sum('nilaimarhun');
                $upahCawangan = Advansur::on($cawangan['mysql'])->where('tarikh',$oneWeek['tarikh'])->sum('upahDibayar');

                $totalGadai += $gadaiCawangan;
                $totalTebus += $tebusCawangan;
                $totalNilaiMarhun += $nilaiMarhunCawangan;
                $upah_cawangan = $upahTebusCawangan + $upahCawangan;
                $upah += $upah_cawangan;
            }
            $totalOnline = TransactionOnline::on('mysql7')->where([['tarikh',$oneWeek['tarikh']],['status','PAID']])->sum('total_payment');
            $totalKomoditi = KomuditiTransaksi::on('mysql7')->where('tarikh',$oneWeek['tarikh'])->sum('unit_komuditi');
            $ansuran = TransaksiKeuntungan::on('mysql7')->join('transaction as trans', function($join){
                $join->on('transaksi_keuntungan.norujukan', '=', 'trans.norujukan');
                $join->on('transaksi_keuntungan.tarikh_bayaran', '=', 'trans.created_at');
            })
            ->where('trans.teller','<>','Online')
            ->where([['transaksi_keuntungan.tarikh_bayaran','>=',$oneWeek['tarikh'] . ' 00:00:00'],['transaksi_keuntungan.tarikh_bayaran','<=',$oneWeek['tarikh'] . ' 23:59:59']])
            ->sum('bayaran_keuntungan');

            $harga_emas = HistoryEmasSandaran::on('mysql7')->where([['t_efektif','<=',$oneWeek['tarikh']],['karat','22.0K']])->orderBy('t_efektif','desc')->first();

            $totalUpah = $ansuran + $upah;

            $dataOneWeek[$index]['totalpembiayaan'] = $totalGadai;
            $dataOneWeek[$index]['totaltebus'] = $totalTebus;
            $dataOneWeek[$index]['totalupah'] = $totalUpah;
            $dataOneWeek[$index]['totalkomoditi'] = $totalKomoditi;
            $dataOneWeek[$index]['totalnilaimarhun'] = $totalNilaiMarhun;
            $dataOneWeek[$index]['onlinepayment'] = $totalOnline;
            $dataOneWeek[$index]['hargaemas'] = $harga_emas['harga'];
        }

        return response()->json($dataOneWeek);
    }

    public function nilaiMarhunSemasa()
    {
        $cawangan_all = Cawangan::on('mysql7')->get();
        $nilaiMarhunSemasa = 0;
        foreach($cawangan_all as $index=>$cawangan){
            $nilaiMarhunterkumpul = Gadai::on($cawangan['mysql'])->where('sttebus','=','')->sum('nilaimarhun');
            $nilaiMarhunSemasa += $nilaiMarhunterkumpul;
        }
        return response()->json(number_format($nilaiMarhunSemasa,2));
    }

    public function petiBesiSemasa(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $jumlahPetibesi = 0;
        $peti_besi = [];
        foreach($cawangan_all as $index=>$cawangan){
            $petibesi = Vault::on($cawangan['mysql'])->orderBy('tarikh','desc')->first();
            array_push($peti_besi,['cawangan'=>$cawangan['details_caw'],'bakipeti'=>$petibesi['bakieod']]);
            $jumlahPetibesi += $petibesi['bakieod'];
        }

        $data = ['totalvault'=>$jumlahPetibesi,'petibesicawangan'=>$peti_besi];
        return response()->json($data);
    }

    public function prestasiCawangan(Request $request){

        if($request->tarikh == null){
            $current = Carbon::now();
            $current = new Carbon();
            $tarikh = $current->toDateString();
        }else{
            $tarikh = $request->tarikh;
        }

        $cawangan_all = Cawangan::on('mysql7')->get();
        $prestasi_cawangan = [];
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$tarikh)->sum('pinjaman');
            $tebus = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$tarikh)->sum('pinjaman');
            $outstanding = Gadai::on($cawangan['mysql'])->where([['t_gadai','=',$tarikh],['sttebus','=','']])->sum('pinjaman');
            $nilaiMarhun = Gadai::on($cawangan['mysql'])->where([['t_gadai','=',$tarikh],['sttebus','=','']])->sum('nilaimarhun');
            $upah = Tebus::on($cawangan['mysql'])->where('t_tebus','=',$tarikh)->sum('bakiupah');

            array_push($prestasi_cawangan,['cawangan'=>$cawangan['details_caw'],'nilai_marhun'=>(float)$nilaiMarhun,'outstanding'=>(float)$outstanding,'gadai'=>(float)$gadai,'tebus'=>(float)$tebus,'upah'=>(float)$upah,'tarikh'=>$tarikh]);
        }

        return response()->json($prestasi_cawangan);
    }

    public function petiBesiCawangan(Request $request){

        if($request->tarikh == null){
            $current = Carbon::now();
            $current = new Carbon();
            $tarikh = $current->toDateString();
        }else{
            $tarikh = $request->tarikh;
        }

        $cawangan_all = Cawangan::on('mysql7')->get();
        $petibesi_cawangan = [];
        foreach($cawangan_all as $index=>$cawangan){
            $petibesi = Vault::on($cawangan['mysql'])->where('Tarikh','=',$tarikh)->first();
            if(is_array($petibesi) || is_object($petibesi)){
                array_push($petibesi_cawangan,['cawangan'=>$cawangan['details_caw'],'petibesi'=>$petibesi['bakieod'],'tarikh'=>$tarikh]);
            }else{
                array_push($petibesi_cawangan,['cawangan'=>$cawangan['details_caw'],'petibesi'=>0,'tarikh'=>$tarikh]);
            }

        }

        return response()->json($petibesi_cawangan);
    }


    public function GadaiHapus($kodcaw,$norujukan,Request $request)
    {
        $current = Carbon::now();
        $current = new Carbon();

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

        $gadai = Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->get();

        $tebus = Tebus::on($cawangan['mysql'])->where('norujukan','=',$gadai[0]['historygadaian'])->orderBy('t_tebus','desc')->first();

        $tunai=Tunai::on($cawangan['mysql'])->where([['kodcaw','=',$kodcaw],['tarikh','=',$current->toDateString()],['kodlaluan','=',$request->kodlaluan]])
                        ->orderBy('tarikh','desc')
                        ->first();

        $keluar=$tunai['keluar'];
        $baki=$tunai['baki'];
        $masuk=$tunai['masuk'];
        $bakiawal=$tunai['bakiawal'];
        $pendahuluan=$tunai['tambah'];
        $serahan=$tunai['kurang'];
        $totalmasuk = 0;
        $bakiakhir = 0;
        $totalkeluar = 0;

        $audit = new Audit();
        $audit->users = $request->kodlaluan;
        $audit->actions = 'Hapus Gadai';
        $audit->norujukan = $norujukan;
        $audit->old_data = $gadai;
        $audit->kodcaw = $kodcaw;
        $audit->created_at = $current;
        $audit->updated_at = $current;
        $audit->save();

        try{
            DB::connection($cawangan['mysql'])->beginTransaction();
            DB::connection('mysql7')->beginTransaction();
            
            if($gadai[0]['historygadaian'] != "0" && $gadai[0]['historygadaian'] != '')
            {

                if($tebus['tagterima'] == 1)
                {
                    if($tebus['jenistebus'] == 'P')
                    {

                        $totalkeluar = $keluar - ($tebus['addpinjaman'] + $tebus['pinjaman']);
                        $totalmasuk = $masuk - ($tebus['bakiupah'] + $tebus['pinjaman']);

                        //  $bakiakhir = $bakiawal - $totalkeluar + $totalmasuk + $pendahuluan - $serahan;
                        $bakiakhir = $baki - $tebus['bakiupah'] + $tebus['addpinjaman'];

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $totalkeluar,
                        'masuk' => $totalmasuk,
                        'baki' => $bakiakhir
                        ));


                    }else{

                        $totalmasuk = $masuk - ($tebus['pinjaman']+$tebus['bakiupah']);
                        $totalkeluar = $keluar - (($tebus['pinjaman'] + $tebus['bakiupah']) - $tebus['bakipjm']);
                        //  $bakiakhir = $bakiawal - $totalkeluar + $totalmasuk + $pendahuluan - $serahan;

                        if($tebus['jenistebus'] == 'S')
                            $bakiakhir = $baki - $tebus['bakipjm'];
                        else
                            $bakiakhir = $bakiawal - $totalkeluar + $totalmasuk + $pendahuluan - $serahan;

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $totalkeluar,
                        'masuk' => $totalmasuk,
                        'baki' => $bakiakhir
                        ));

                    }
                }else{

                    $bakiakhir = $baki;

                    if($tebus['jenistebus'] == 'P')
                    {

                        $bakiakhir = $bakiakhir - $tebus['bakiupah'] + $tebus['addpinjaman'];

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $keluar,
                        'masuk' => $masuk,
                        'baki' => $bakiakhir
                        ));

                    }else{

                        $totalmasuk = $masuk;
                        $totalkeluar = $keluar;

                        if($tebus['jenistebus'] == 'S')
                            $bakiakhir = ($bakiakhir - $tebus['bakipjm']);
                        else
                            $bakiakhir = $bakiawal - $totalkeluar + $totalmasuk + $pendahuluan - $serahan;

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $keluar,
                        'masuk' => $masuk,
                        'baki' => $bakiakhir
                        ));

                    }

                }


                Gadai::on($cawangan['mysql'])->where('norujukan','=',$gadai[0]['historygadaian'])->update(['sttebus' => '']);


                $saved_historygadaian = $gadai[0]['historygadaian'];

                Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                TransaksiKeuntungan::on('mysql7')->where('norujukan','=',$gadai[0]['historygadaian'])->orderBy('id', 'desc')->limit(1)->delete();

                Transaction::on('mysql7')->where('norujukan','=',$gadai[0]['historygadaian'])->orderBy('id', 'desc')->limit(1)->delete();

                Tebus::on($cawangan['mysql'])->where('norujukan','=',$gadai[0]['historygadaian'])->delete();

                Marhun::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                $gadaiDouble = Gadai::on($cawangan['mysql'])->where([['historygadaian',$gadai[0]['historygadaian']],['norujukan','<>',$gadai[0]['historygadaian']]])->pluck('norujukan');

                if($gadaiDouble){

                    $isGadaianTawarruq = $saved_historygadaian !== '0' || $saved_historygadaian !== null || $saved_historygadaian !== '';
                    if($isGadaianTawarruq){

                        $this->batalKomuditiTawarruq($norujukan);
                        Gadai::on($cawangan['mysql'])->where('norujukan', $norujukan)->delete();
                        Marhun::on($cawangan['mysql'])->where('norujukan', $norujukan)->delete();
                    } else {

                        Gadai::on($cawangan['mysql'])->whereIn('norujukan',$gadaiDouble)->delete();
                        Marhun::on($cawangan['mysql'])->whereIn('norujukan',$gadaiDouble)->delete();
                        $this->batalKomuditi($gadai[0]['historygadaian'],$cawangan['mysql']);
                    }

                }

                // return response('Deleted Successfully', 200);
            }
            else
            {

                if($gadai[0]['semakbarcode'] == 'Lulus')
                {
                    $totalkeluar = $keluar - $gadai[0]['pinjaman'];

                    $bakiakhir = $baki;

                    $bakiakhir += $gadai[0]['pinjaman'];

                    $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                    update(array(
                        'keluar' => $totalkeluar,
                        'masuk' => $masuk,
                        'baki' => $bakiakhir
                    ));
                }else{

                    $bakiakhir = $baki;

                    $bakiakhir += $gadai[0]['pinjaman'];

                    $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                    update(array(
                        'keluar' => $keluar,
                        'masuk' => $masuk,
                        'baki' => $bakiakhir
                    ));
                }

                Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();
                Marhun::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();
                TransaksiKeuntungan::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();
                Transaction::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();

                // return response('Deleted Successfully', 200);
            }

            $array_response = [
                'result' => 'success',
                'data' => 1
            ];

            DB::connection($cawangan['mysql'])->commit();
            DB::connection('mysql7')->commit();

            return response()->json($array_response);
        }catch(\Exception $e){
            DB::connection($cawangan['mysql'])->rollBack();
            DB::connection('mysql7')->rollBack();
            $array_response = [
                'result' => 'error',
                'data' => $e->getMessage()
            ];

            Log::error('Transaction failed', [
                'exception' => $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
 
            return response()->json($array_response);
        }

    }

    public function batalKomuditiTawarruq($norujukan){

        $komuditi_current = KomuditiPurchase::on('mysql7')->orderBy('created_at','desc')->first();

        $transaksi_asal = KomuditiTransaksi::on('mysql7')->where('norujukan', $norujukan)->sum('unit_komuditi');

        $current_commodity_purchase = KomuditiPurchase::on('mysql7')->where('id','=',$komuditi_current['id'])->first();
        $unit_current = $current_commodity_purchase->unit_komuditi;

        $update_unit = $unit_current + $transaksi_asal;

        KomuditiPurchase::on('mysql7')->where('id','=',$komuditi_current['id'])->update([
            'unit_komuditi' => $update_unit
        ]);

        KomuditiTransaksi::on('mysql7')->where('norujukan', $norujukan)->delete();

        return 'success';
    }

    public function batalKomuditi($norujukan,$cawangan_mysql){

        $gadaiDouble = Gadai::on($cawangan_mysql)->where([['historygadaian',$norujukan],['norujukan','<>',$norujukan]])->pluck('norujukan');

        $komuditi_current = KomuditiPurchase::on('mysql7')->orderBy('created_at','desc')->first();

        $transaksi_asal = KomuditiTransaksi::on('mysql7')->whereIn('norujukan','=',$gadaiDouble)->sum('unit_komuditi');

        $unit_current = $komuditi_current['unit_komuditi'];

        $update_unit = $unit_current + $transaksi_asal['unit_komuditi'];

        $update_komuditi = KomuditiPurchase::on('mysql7')->where('id','=',$komuditi_current['id'])->update([
            'unit_komuditi' => $update_unit
        ]);

        $komuditi_transaksi = KomuditiTransaksi::on('mysql7')->whereIn('norujukan',$gadaiDouble)->delete();

        return 'success';
    }

    public function TebusHapus($kodcaw,$norujukan,Request $request)
    {
        $current = Carbon::now();
        $current = new Carbon();

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

        $tebus = Tebus::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->get();

        $gadai = Gadai::on($cawangan['mysql'])->where([['sttebus',''],['historygadaian',$norujukan]])->first();

        $tunai=Tunai::on($cawangan['mysql'])->where([['kodcaw','=',$kodcaw],['tarikh','=',$current->toDateString()],['kodlaluan','=',$request->kodlaluan]])
        ->orderBy('tarikh','desc')
        ->first();

        $keluar=$tunai['keluar'];
        $baki=$tunai['baki'];
        $masuk=$tunai['masuk'];
        $bakiawal=$tunai['bakiawal'];
        $pendahuluan=$tunai['tambah'];
        $serahan=$tunai['kurang'];
        $totalmasuk = 0;
        $bakiakhir = $baki;
        $totalkeluar = 0;

        try{
            if($gadai){
                return response('Bukan-Tebus', 200);
            }else{
                DB::connection($cawangan['mysql'])->beginTransaction();
                DB::connection('mysql7')->beginTransaction();

                $audit = new Audit();
                $audit->users = $request->kodlaluan;
                $audit->actions = 'Hapus Tebus';
                $audit->norujukan = $norujukan;
                $audit->old_data = $gadai;
                $audit->kodcaw = $kodcaw;
                $audit->created_at = $current;
                $audit->updated_at = $current;
                $audit->save();

                if($tebus[0]['tagterima'] == 1)
                {
                    if($tebus[0]['jenistebus'] == 'T')
                    {
                        $totalmasuk = $masuk - $tebus[0]['bakipjm'];

                        // $bakiakhir = $bakiawal-$keluar+$totalmasuk+$pendahuluan-$serahan;
                        $bakiakhir = $baki - $tebus[0]['bakipjm'];

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $keluar,
                        'masuk' => $totalmasuk,
                        'baki' => $bakiakhir
                        ));

                        Tebus::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                        InOutMar::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                        TransaksiKeuntungan::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();

                        Transaction::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();

                        Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->update(['sttebus' => '']);

                        DB::connection($cawangan['mysql'])->commit();
                        DB::connection('mysql7')->commit();

                        return response('Deleted Successfully', 200);
                    }
                    else
                        return response('Bukan-Tebus', 200);
                }else{

                    if($tebus[0]['jenistebus'] == 'T')
                    {

                        $bakiakhir = $bakiakhir - $tebus[0]['bakipjm'];

                        $updatetunai=Tunai::on($cawangan['mysql'])->where( [['tarikh','=', $current->toDateString()],['kodlaluan','=', $request->kodlaluan] ])->
                        update(array(
                        'keluar' => $keluar,
                        'masuk' => $masuk,
                        'baki' => $bakiakhir
                        ));



                        Tebus::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                        InOutMar::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->delete();

                        TransaksiKeuntungan::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();

                        Transaction::on('mysql7')->where('norujukan','=',$norujukan)->orderBy('id', 'desc')->limit(1)->delete();

                        Gadai::on($cawangan['mysql'])->where('norujukan','=',$norujukan)->update(['sttebus' => '']);

                        DB::connection($cawangan['mysql'])->commit();
                        DB::connection('mysql7')->commit();
                        return response('Deleted Successfully', 200);
                    }
                    else
                        return response('Bukan-Tebus', 200);
                }
            }

            return response()->json($array_response);
        }catch(\Exception $e){
            DB::connection($cawangan['mysql'])->rollBack();
            DB::connection('mysql7')->rollBack();
            $array_response = [
                'result' => 'error',
                'data' => $e->getMessage()
            ];

            Log::error('Transaction failed', [
                'exception' => $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
 
            return response()->json($array_response);
        }

    }

    public function senaraiLelong($kodcaw,Request $request){
        if($request->tarikh == null){
            $current = Carbon::now();
            $current = new Carbon();
            $tarikh_lelong = $current;
        }else{
            $tarikh_lelong = Carbon::parse($request->tarikh.' 00:00:00' );
        }

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

        $gadai_aktif = Gadai::on($cawangan['mysql'])->whereNotIn('sttebus',['L','T'])->get();

        $senarai_lelong =[];

        foreach($gadai_aktif as $index=>$gadai){
            $jbg_lelong = $gadai['jbg'];

            $baki_upah = $gadai['bakiupah'];

            if($jbg_lelong >= 9 || $baki_upah != 0){
                array_push($senarai_lelong,$senarai_lelong);
            }
        }

        return response()->json($senarai_lelong);
    }

    public function percent(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $total_gadaian_cawangan = 0;
        $array_cawangan = [];
        $current = Carbon::now();
        $current = new Carbon();
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['t_gadai','=',$current->toDateString()]])->sum('pinjaman');
            array_push($array_cawangan,['cawangan'=>$cawangan['details_caw'],'total_gadai'=>$gadai]);
            $total_gadaian_cawangan += $gadai;
        }
        $array_percentage=[];
        foreach($cawangan_all as $index=>$cawangan){
            if($array_cawangan[$index]['total_gadai'] == 0){
                $percentage = 0;
            }else{
                $percentage = ($array_cawangan[$index]['total_gadai']/$total_gadaian_cawangan)*100;
            }
            array_push($array_percentage,['cawangan'=>$cawangan['details_caw'],'percentage_gadai'=>number_format($percentage,2)]);
        }
        $count_cawangan = sizeof($cawangan_all);
        for($i=0;$i<$count_cawangan;$i++){
            $percent_cawangan = $array_percentage[$i]['percentage_gadai'];
            for($b=0;$b<$count_cawangan;$b++){
                if($percent_cawangan > $array_percentage[$b]['percentage_gadai']){
                    $temp = $array_percentage[$i];
                    $array_percentage[$i] = $array_percentage[$b];
                    $array_percentage[$b] = $temp;
                }
            }
        }
        return response()->json($array_percentage);
    }

    public function percentTop5(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $total_gadaian_cawangan = 0;
        $array_cawangan = [];
        $current = Carbon::now();
        $current = new Carbon();
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['t_gadai','=',$current->toDateString()]])->sum('pinjaman');
            array_push($array_cawangan,['cawangan'=>$cawangan['details_caw'],'total_gadai'=>$gadai]);
            $total_gadaian_cawangan += $gadai;
        }
        $array_percentage=[];

        $count_cawangan = sizeof($cawangan_all);
        for($i=0;$i<$count_cawangan;$i++){
            $percent_cawangan = $array_cawangan[$i]['total_gadai'];
            for($b=0;$b<$count_cawangan;$b++){
                if($percent_cawangan > $array_cawangan[$b]['total_gadai']){
                    $temp = $array_cawangan[$i];
                    $array_cawangan[$i] = $array_cawangan[$b];
                    $array_cawangan[$b] = $temp;
                }
            }
        }
        $array_top5=[];
        $total_top5 = 0;
        for($i=0;$i<5;$i++){
            $total_top5 += $array_cawangan[$i]['total_gadai'];
           array_push($array_top5,$array_cawangan[$i]);
        }
        foreach($array_top5 as $index=>$top5){
            if($top5['total_gadai'] == 0){
                $percentage = 0;
            }else{
                $percentage = ($top5['total_gadai']/$total_top5)*100;
            }

            array_push($array_percentage,['cawangan'=>$top5['cawangan'],'percentage_gadai'=>number_format($percentage,2)]);
        }
        // dd($array_percentage);
        return response()->json($array_percentage);

    }

    public function totalPinjamanGadaian(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $total_gadaian_cawangan = 0;
        $array_cawangan = [];
        $current = Carbon::now();
        $current = new Carbon();
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where([['sttebus','=',''],['t_gadai','=',$current->toDateString()]])->sum('pinjaman');
            array_push($array_cawangan,['cawangan'=>$cawangan['details_caw'],'total_gadai'=>$gadai]);
            $total_gadaian_cawangan += $gadai;

        }
        //sorting
        $count_cawangan = sizeof($cawangan_all);
        for($i=0;$i<$count_cawangan;$i++){
            $percent_cawangan = $array_cawangan[$i]['total_gadai'];
            for($b=0;$b<$count_cawangan;$b++){
                if($percent_cawangan > $array_cawangan[$b]['total_gadai']){
                    $temp = $array_cawangan[$i];
                    $array_cawangan[$i] = $array_cawangan[$b];
                    $array_cawangan[$b] = $temp;
                }
            }
        }

        return response()->json($array_cawangan);

    }

    public function bilanganTransaksi(Request $request){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $penggadai_all = [];
        $current = Carbon::now();
        $current = new Carbon();
        $weekly = Carbon::now();
        $weekly = new Carbon();
        $monthly = Carbon::now();
        $monthly = new Carbon();
        $yearly = Carbon::now();
        $yearly = new Carbon();
        $weekly_array = Carbon::now();
        $weekly_array = new Carbon();
        $monthly_array = Carbon::now();
        $monthly_array = new Carbon();
        $yearly_array = Carbon::now();
        $yearly_array = new Carbon();

        $array_totalup = [];

        if($request->method == 'daily'){
            array_push($array_totalup,['tarikh'=>$current->toDateString(),'totalgadai'=>0,'totaltebus'=>0,'totalupah'=>0,'totalkomoditi'=>0]);
        }
        if($request->method == 'weekly'){//weekly
            for($i=0;$i<7;$i++){
                if($i == 0){
                    $weekly_array_date = $weekly->subDays(0);
                }else{
                    $weekly_array_date = $weekly->subDays(1);
                }
                array_push($array_totalup,['tarikh'=>$weekly_array_date->toDateString(),'totalgadai'=>0,'totaltebus'=>0,'totalupah'=>0, 'totalkomoditi'=>0]);
            }
        }
        if($request->method == 'monthly'){
            for($i=0;$i<12;$i++){
                if($i == 0){
                    $monthly_array_date = $monthly_array->startOfMonth()->subMonths(0);
                }else{
                    $monthly_array_date = $monthly_array->startOfMonth()->subMonths(1);
                }
                array_push($array_totalup,['tarikh'=>$monthly_array_date->year.'-'.sprintf("%02d", $monthly_array_date->month),'totalgadai'=>0,'totaltebus'=>0,'totalupah'=>0, 'totalkomoditi'=>0]);
            }
        }
        if($request->method == 'yearly'){
            for($i=0;$i<5;$i++){
                if($i == 0){
                    $yearly_array_date = $yearly_array->startOfYear()->subYears(0);
                }else{
                    $yearly_array_date = $yearly_array->startOfYear()->subYears(1);
                }
                array_push($array_totalup,['tarikh'=>sprintf("%04d",$yearly_array_date->year),'totalgadai'=>0,'totaltebus'=>0,'totalupah'=>0, 'totalkomoditi'=>0]);
            }
        }
        $weekly_date = $weekly->subDays(6)->toDateString();
        $monthly_date = $monthly->startOfMonth()->subMonths(11)->toDateString();
        $yearly_date = $yearly->startOfYear()->subYears(4)->toDateString();
        foreach($cawangan_all as $index => $cawangan){
            if($request->method == 'daily'){
                $gadai = Gadai::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totalpinjaman')->addSelect('t_gadai')->groupBy('t_gadai')->where('t_gadai','=',$current->toDateString())->orderBy('t_gadai','desc')->get();
                $tebus = Tebus::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totaltebus, SUM(bakiupah) AS totalupah')->addSelect('t_tebus')->groupBy('t_tebus')->where('t_tebus','=',$current->toDateString())->orderBy('t_tebus','desc')->get();

                // $pembeliankomuditi = Komuditi::on('mysql7')->selectRaw('SUM(unit_komuditi) as totalkomoditi')->addSelect('date_purchase')->groupBy('date_purchase')
                // ->where('date_purchase','=',$current->toDateString())->orderBy('created_at','desc')->get();

            }elseif($request->method == 'weekly'){
                $gadai = Gadai::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totalpinjaman')->addSelect('t_gadai')->groupBy('t_gadai')->where([['t_gadai','<=',$current->toDateString()],['t_gadai','>=',$weekly_date]])->orderBy('t_gadai','desc')->get();
                $tebus = Tebus::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totaltebus, SUM(bakiupah) AS totalupah')->addSelect('t_tebus')->groupBy('t_tebus')->where([['t_tebus','<=',$current->toDateString()],['t_tebus','>=',$weekly_date]])->orderBy('t_tebus','desc')->get();

                // $pembeliankomuditi = Komuditi::on('mysql')->selectRaw('SUM(unit_komuditi) as totalkomoditi')->addSelect('date_purchase')->groupBy('date_purchase')
                // ->where([['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$weekly_date]])->orderBy('created_at','desc')->get();

            }elseif($request->method == 'monthly'){
                $gadai = Gadai::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totalpinjaman')->addSelect(DB::connection($cawangan['mysql'])->raw('DATE_FORMAT(t_gadai, "%Y-%m") as t_gadai'))->groupBy(DB::connection($cawangan['mysql'])->raw("DATE_FORMAT(t_gadai,'%Y%m')"))->where([['t_gadai','<=',$current->toDateString()],['t_gadai','>=',$monthly_date]])->orderBy('t_gadai','desc')->get();
                $tebus = Tebus::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totaltebus, SUM(bakiupah) AS totalupah')->addSelect(DB::connection($cawangan['mysql'])->raw('DATE_FORMAT(t_tebus, "%Y-%m") as t_tebus'))->groupBy(DB::connection($cawangan['mysql'])->raw("DATE_FORMAT(t_tebus,'%Y%m')"))->where([['t_tebus','<=',$current->toDateString()],['t_tebus','>=',$monthly_date]])->orderBy('t_tebus','desc')->get();

                // $pembeliankomuditi = Komuditi::on('mysql')->selectRaw('SUM(unit_komuditi) AS totalkomoditi')->addSelect(DB::connection('mysql')->raw('DATE_FORMAT(date_purchase, "%Y-%m") as date_purchase'))
                // ->groupBy(DB::connection('mysql')->raw("DATE_FORMAT(date_purchase,'%Y%m')"))->where([['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$monthly_date]])
                // ->orderBy('created_at','desc')->get();

            }elseif($request->method == 'yearly'){
                $gadai = Gadai::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totalpinjaman')->addSelect(DB::connection($cawangan['mysql'])->raw('DATE_FORMAT(t_gadai, "%Y") as t_gadai'))->groupBy(DB::connection($cawangan['mysql'])->raw("DATE_FORMAT(t_gadai,'%Y')"))->where([['t_gadai','<=',$current->toDateString()],['t_gadai','>=',$yearly_date]])->orderBy('t_gadai','desc')->get();
                // dd($gadai);
                $tebus = Tebus::on($cawangan['mysql'])->selectRaw('SUM(pinjaman) AS totaltebus, SUM(bakiupah) AS totalupah')->addSelect(DB::connection($cawangan['mysql'])->raw('DATE_FORMAT(t_tebus, "%Y") as t_tebus'))->groupBy(DB::connection($cawangan['mysql'])->raw("DATE_FORMAT(t_tebus,'%Y')"))->where([['t_tebus','<=',$current->toDateString()],['t_tebus','>=',$yearly_date]])->orderBy('t_tebus','desc')->get();

                // $pembeliankomuditi = Komuditi::on('mysql')->selectRaw('SUM(unit_komuditi) AS totalkomoditi')->addSelect(DB::connection('mysql')->raw('DATE_FORMAT(date_purchase, "%Y") as date_purchase'))
                // ->groupBy(DB::connection('mysql')->raw("DATE_FORMAT(date_purchase,'%Y')"))->where([['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$yearly_date]])
                // ->orderBy('created_at','desc')->get();

            }
            //komoditi
            if($request->method == 'daily'){
                $pembeliankomuditi = Komuditi::on('mysql7')->selectRaw('SUM(unit_komuditi) as totalkomoditi')->addSelect('date_purchase')->groupBy('date_purchase')
                ->where([['transaction_type','=','Beli Komoditi'],['date_purchase','=',$current->toDateString()]])->orderBy('created_at','desc')->get();
            }elseif($request->method == 'weekly')
            {
                $pembeliankomuditi = Komuditi::on('mysql7')->selectRaw('SUM(unit_komuditi) as totalkomoditi')->addSelect('date_purchase')->groupBy('date_purchase')
                ->where([['transaction_type','=','Beli Komoditi'],['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$weekly_date]])->orderBy('created_at','desc')->get();

            }elseif($request->method == 'monthly')
            {
                $pembeliankomuditi = Komuditi::on('mysql7')->selectRaw('SUM(unit_komuditi) AS totalkomoditi')->addSelect(DB::connection('mysql7')->raw('DATE_FORMAT(date_purchase, "%Y-%m") as date_purchase'))
                ->groupBy(DB::connection('mysql7')->raw("DATE_FORMAT(date_purchase,'%Y%m')"))->where([['transaction_type','=','Beli Komoditi'],['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$monthly_date]])
                ->orderBy('created_at','desc')->get();

            }elseif($request->method == 'yearly')
            {
                $pembeliankomuditi = Komuditi::on('mysql7')->selectRaw('SUM(unit_komuditi) AS totalkomoditi')->addSelect(DB::connection('mysql7')->raw('DATE_FORMAT(date_purchase, "%Y") as date_purchase'))
                ->groupBy(DB::connection('mysql')->raw("DATE_FORMAT(date_purchase,'%Y')"))->where([['transaction_type','=','Beli Komoditi'],['date_purchase','<=',$current->toDateString()],['date_purchase','>=',$yearly_date]])
                ->orderBy('created_at','desc')->get();
            }

            foreach($array_totalup as $array_index=>$totalup){
                foreach($gadai as $indexgadai=>$gadai_detail){
                    if($totalup['tarikh'] == $gadai_detail['t_gadai']){
                        $total_gadai = $totalup['totalgadai'];
                        $total_gadai += $gadai_detail['totalpinjaman'];
                        $array_totalup[$array_index]['totalgadai'] = $total_gadai;
                    }
                }
                foreach($tebus as $indextebus=>$tebus_detail){
                    if($totalup['tarikh'] == $tebus_detail['t_tebus']){
                        $total_tebus = $totalup['totaltebus'];
                        $total_tebus += $tebus_detail['totaltebus'];
                        $array_totalup[$array_index]['totaltebus'] = $total_tebus;
                        $total_upah = $totalup['totalupah'];
                        $total_upah += $tebus_detail['totalupah'];
                        $array_totalup[$array_index]['totalupah'] = $total_upah;
                    }
                }

            }


        }

        foreach($array_totalup as $array_index=>$totalup)
        {
            foreach($pembeliankomuditi as $indextebus=>$pembeliankomuditi_detail){
                if($totalup['tarikh'] == $pembeliankomuditi_detail['date_purchase']){
                    $jumlahkomoditi = $totalup['totalkomoditi'];
                    $jumlahkomoditi += $pembeliankomuditi_detail['totalkomoditi'];
                    $array_totalup[$array_index]['totalkomoditi'] = $jumlahkomoditi;
                }
            }
        }

        return response()->json($array_totalup);
    }

    public function statistikGadaian(){

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

        $cawangan_all = Cawangan::on('mysql7')->get();
        $array_total = ['total_sag'=>0,'total_customer'=>0];
        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->selectRaw('COUNT(norujukan) AS totalsag,COUNT(DISTINCT nokp) as totalcustomer')->where('t_gadai','=',$current->toDateString())->get();
            $total_sag = $array_total['total_sag'];
            $total_sag += $gadai[0]['totalsag'];
            $totalcustomer = $array_total['total_customer'];
            $totalcustomer += $gadai[0]['totalcustomer'];
            $array_total['total_sag'] = $total_sag;
            $array_total['total_customer'] = $totalcustomer;
        }

        return response()->json($array_total);
    }

    public function carian_AgingBulanan($kodcaw,$tarikh,$num)
    {

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

        $gadai_aging=DB::connection($db)->select("SELECT count(norujukan) as a,sum(pinjaman)as b From gadai where (((period_diff(date_format('".$tarikh."', '%Y%m'), date_format(t_gadai, '%Y%m')))+1)=".$num.") and((sttebus='' and t_gadai<='".$tarikh."') or (sttebus!='' and t_update>'".$tarikh."'and t_gadai<='".$tarikh."'))");
        return response()->json($gadai_aging);
    }

    public function carian_agingLebih12_Bulan($kodcaw,$tarikh)
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$kodcaw)->first();
        $db=$cawangan['mysql'];
        $gadai_aging=DB::connection($db)->select("SELECT count(norujukan) as a,sum(pinjaman)as b From gadai where (((period_diff(DATE_FORMAT('".$tarikh."', '%Y%m'), date_format(t_gadai, '%Y%m')))+1)>'12') and((sttebus='' and t_gadai<='".$tarikh."') or (sttebus!='' and t_update>'".$tarikh."' and t_gadai<='".$tarikh."'))");
        return response()->json($gadai_aging);
    }

    public function carian_Seluruh_AgingBulanan($kodcaw,$tarikh)
    {

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

        $gadai_aging=DB::connection($db)->select("SELECT kodcaw,count(norujukan) as a,sum(pinjaman)as b,((period_diff(date_format('".$tarikh."', '%Y%m'), date_format(t_gadai, '%Y%m')))+1) AS bln From gadai where ((sttebus='' and t_gadai<='".$tarikh."') or (sttebus!='' and t_update>'".$tarikh."' and t_gadai<='".$tarikh."'))");
        return response()->json($gadai_aging);
    }

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

        $rekod_gadaian = Gadai::on($cawangan['mysql'])->where('t_gadai','=',$request->tarikh)->get();

        return response()->json($rekod_gadaian);
    }

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

        $tarikhtawarruq = '';

        switch ($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 '011229':
                $tarikhtawarruq = '2021-06-22';
            break;
            case '011230':
                $tarikhtawarruq = '2021-06-23';
            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;
          }

        $rekod_emaspalsu = Gadai::on($cawangan['mysql'])
            ->where('tagpalsu','=',1)
            ->where([['t_gadai','<=',$request->tarikh],['sttebus','=','']])
            ->sum('pinjaman');

        $rekod_pinjamandiberi = Gadai::on($cawangan['mysql'])
            ->where('tagpalsu','=',0)
            ->where([['t_gadai','<=',$tarikhtawarruq],['sttebus','=','']])
            ->orWhere([['sttebus','!=',''],['t_update','>',$request->tarikh],['t_gadai','<=',$tarikhtawarruq]])
            ->sum('pinjaman');

        $totalemas = 0;
        $totalpinjaman = 0;
        if($kodcaw === 'GC')
        {
            $totalemas = floatval($rekod_emaspalsu) - 10000;
            $totalpinjaman = floatval($rekod_pinjamandiberi) + 10000;

        }else{
            $totalemas = $rekod_emaspalsu;
            $totalpinjaman = $rekod_pinjamandiberi;
        }

        $array_rekodemas = ['pinjamankoop' => $totalpinjaman,'emaspalsu' => $totalemas];

        return response()->json($array_rekodemas);
    }

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

        $tarikhtawarruq = '';

        switch ($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;
          }

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

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

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

        $tebus = $tebus + $pembiayaanlelong;

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

        return response()->json($array_rekodemas);
    }

    public function CalculationPembiayaanTawarruq($kodcaw,Request $request)
    {

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

        $rekod_pinjamandiberi = Gadai::on($cawangan['mysql'])
            ->where('tagpalsu','=',0)
            ->where([['t_gadai','<=',$request->tarikh],['sttebus','=',''],['hargajualan','<>','NULL']])
            ->orWhere([['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh],['hargajualan','<>','null']])
            ->sum('pinjaman');

        return response()->json($rekod_pinjamandiberi);
    }

    public function CalculationPembiayaanTawarruqLejer($kodcaw,Request $request)
    {

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

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

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


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

        return response()->json($rekod_pinjamandiberi);
    }

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

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

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

        $keuntungan = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','<=',$request->tarikh)
        ->where('hargajualan','<>','null')
        ->orWhere([['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh],['t_gadai','>=','2021-04-14']])
        ->whereNotNull('upah12')
        ->sum('upah12');

        //$transaksi_data = TransaksiKeuntungan::on('mysql7')
        //->where('transaksi_keuntungan.tarikh_bayaran' ,'<=', $daterequest)
        //->whereYear('tarikh_bayaran','=',$current->year)
        //->get();

        if($cawangan['zone'] == 1){
            $transaksi_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');
            })
            ->join($cawangan['db_name'] . 'gadai as gad', 'gad.norujukan','=','trans.norujukan')
            ->where('trans.trans_type','=','A')
            ->where('transaksi_keuntungan.tarikh_bayaran' ,'<=', $daterequest)
            ->sum('transaksi_keuntungan.bayaran_keuntungan');

            $totalkeuntungan = $keuntungan - $transaksi_data;

        }else{
            $transaksi_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')
                ->where('transaksi_keuntungan.tarikh_bayaran' ,'<=', $daterequest)
                ->get();

            $transaksi = 0;
            if($transaksi_data){
                foreach($transaksi_data as $index=>$transaksiData){
                    $gadaiData = Gadai::on($cawangan['mysql'])->where('norujukan',$transaksiData['norujukan'])->whereNotNull('hargajualan')->first();
                    if($gadaiData){
                        $transaksi += $transaksiData['bayaran_keuntungan'];
                    }
                }
            }

            $totalkeuntungan = $keuntungan - $transaksi;
        }

        return response()->json($totalkeuntungan,200);
    }

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

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

        $keuntungan = Gadai::on($cawangan['mysql'])
        ->where('t_gadai','>=',$request->mula)
        ->where('t_gadai','<=',$request->hingga)
        ->where('hargajualan','<>','null')
        ->orWhere([['t_update','>',$request->mula],['sttebus','!=',''],['t_gadai','>=',$request->mula],['t_gadai','<=',$request->hingga],['t_gadai','>=','2021-04-14']])
        ->whereNotNull('upah12')
        ->sum('upah12');

        $ansuran_data = TransaksiKeuntungan::on('mysql7')
                        // ->join($cawangan['db_name'].'.gadai as db2','transaksi_keuntungan.norujukan','=','db2.norujukan')
                        ->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' ,'>=', $request->mula)
                        ->whereDate('transaksi_keuntungan.tarikh_bayaran' ,'<=', $request->hingga)
                        // ->whereYear('tarikh_bayaran','=',$current->year)
                        ->where('trans.kodcaw',$request->kodcaw)
                        // ->whereNotNull('db2.hargajualan')
                        // ->sum('bayaran_keuntungan');
                        ->get();

            $ansuran = 0;
            if($ansuran_data){
                foreach($ansuran_data as $index=>$ansuranData){
                    $gadaiData = Gadai::on($cawangan['mysql'])->where([['norujukan',$ansuranData['norujukan']],['hargajualan','<>',null]])->first();
                    if($gadaiData){
                        $ansuran += $ansuranData['bayaran_keuntungan'];
                    }
                }
            }

        $arraykeuntungan = ["totalkeuntungan" => $keuntungan,"ansuran" => floatval($ansuran)];

        //$totalkeuntungan = $keuntungan;

        return response()->json($arraykeuntungan,200);
    }

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

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

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

        if($request->kodcaw == 'GC'){
            $transaksi_data = [];
            TransaksiKeuntungan::on('mysql7')
                ->where('tarikh_bayaran', '<=', $daterequest)
                ->where('kodcaw', $request->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) use ($request) {
                        $query->where([
                            ['sttebus', '=', ''],
                            ['hargajualan', '<>', null]
                        ])
                        ->orWhere([
                            ['sttebus', '!=', ''],
                            ['t_update', '>', $request->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 response($totalPembiayaan);
        }

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

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


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

        // $transaksi_data = TransaksiKeuntungan::on('mysql7')
        //             // ->select('norujukan','tarikh_bayaran')
        //             ->where('tarikh_bayaran' ,'<=', $daterequest)
        //             // ->join($cawangan['db_name'].'.gadai as db2','transaksi_keuntungan.norujukan','=','db2.norujukan')
        //             // ->where([['db2.sttebus','=',''],['db2.hargajualan','<>','null'],['tarikh_bayaran' ,'<=', $daterequest]])
        //             // ->orWhere([['db2.sttebus','!=',''],['db2.t_update','>',$request->tarikh],['db2.hargajualan','<>','null'],['tarikh_bayaran' ,'<=', $daterequest]])
        //             // ->sum('bayaran_pembiayaan');
        //             ->pluck('norujukan');
        //             // ->get();

        // $transaksi = 0;

        // if($transaksi_data){
        //     foreach($transaksi_data as $index=>$transaksiData){
        //         $gadaiData = Gadai::on($cawangan['mysql'])->where('norujukan',$transaksiData['norujukan'])->first();
        //         if($gadaiData && (($gadaiData['sttebus'] == '' && $gadaiData['hargajualan'] != null) || ($gadaiData['sttebus'] != '' && $gadaiData['t_update'] > $request->tarikh && $gadaiData['hargajualan'] != null))){
        //             $transaksi += $transaksiData['bayaran_pembiayaan'];
        //         }
        //     }
        // }

        return response($transaksi);
    }

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

        $datetawarruq = new Carbon('2021-04-14');
        $datemula = new Carbon($request->mula);
        $datemula->addDays(1);

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

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

        // $arraylejer = array();

        // $transaksi_data = TransaksiKeuntungan::on('mysql7')
        //     ->where([['tarikh_bayaran' ,'>=', $datemula],['tarikh_bayaran' ,'<=', $datehingga]])
        //     ->where('kodcaw',$kodcaw)
        //     ->pluck('norujukan');

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

        // $transaksi = TransaksiKeuntungan::on('mysql7')
        //     ->whereIn('norujukan',$gadaiData)
        //     ->where([['tarikh_bayaran' ,'>=', $datemula],['tarikh_bayaran' ,'<=', $datehingga]])
        //     ->sum('bayaran_pembiayaan');

        $from = date($request->mula);
        $to = date($request->hingga);

        $transaksi_data = TransaksiKeuntungan::on('mysql7')
                            // ->where([['tarikh_bayaran' ,'>=', $datemula],['tarikh_bayaran' ,'<=', $datehingga]])
                            ->whereDate('tarikh_bayaran','<=', $request->hingga)
                            ->whereDate('tarikh_bayaran','>=', $request->mula)
                            ->where('kodcaw',$kodcaw)
                            ->pluck('norujukan');

        $transaksi_kredit_gadai =  Gadai::on($cawangan['mysql'])
                            ->whereIn('norujukan',$transaksi_data)
                            //->join('tebus as t','gadai.norujukan','=','t.norujukan')
                            ->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','<=', $request->hingga)
            ->whereDate('tarikh_bayaran','>=', $request->mula)
            // ->whereBetween('tarikh_bayaran',[$from,$to])
            ->sum('bayaran_pembiayaan');


         //$transaksi_kredit = TransaksiKeuntungan::on('mysql7')
         //                    ->join($cawangan['db_name'].'.gadai as db2','transaksi_keuntungan.norujukan','=','db2.norujukan')
         //                    ->where([['db2.sttebus','=',''],['db2.hargajualan','<>','null'],['tarikh_bayaran' ,'>=', $datemula],['tarikh_bayaran' ,'<=', $datehingga]])
         //                    ->sum('bayaran_pembiayaan');
	//dd($transaksi_kredit);

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

        $arraytebus = array();

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

        //dd($arraytebus);

        $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 response($arraylejer);
    }

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

        $audit = Gadai::on($cawangan['mysql'])
            ->where([['tagpalsu','=',0],['t_gadai','<=',$request->tarikh],['sttebus','=','']])
            ->orWhere([['tagpalsu','=',0],['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh]])
            ->orderBy('t_gadai', 'ASC')
            ->orderBy('sirig', 'ASC');

        $rekodaudit = $audit->get();
        $totalrekod = $audit->sum('pinjaman');
        $totalberat = $audit->sum('berat');
        $totalnilaimarhun = $audit->sum('nilaimarhun');
        $totalpinjaman = $audit->sum('pinjaman');
        $totalbakipjm = $audit->sum('bakipjm');
        $totaltunggakan = $audit->sum('tunggakan');
        $totalujrah = $audit->sum('tunggakanujrah');
        $totalonepercent = $audit->sum('tunggakanonepercent');


        $arrayrekod = ['rekod' => $rekodaudit, 'total' => $totalrekod,'totalberat' => $totalberat,'totalnilaimarhun' =>  $totalnilaimarhun,'totalpinjaman'=>$totalpinjaman,'totalbakipjm'=>$totalbakipjm,'totaltunggakan'=>$totaltunggakan,'totalujrah'=>$totalujrah,'totalonepercent'=>$totalonepercent];

        return response()->json($arrayrekod,200);
    }

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

        $audit = Gadai::on($cawangan['mysql'])
            // ->where([['t_gadai','<=',$request->tarikh],['sttebus','=','']])
            // ->orWhere([['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh]])
            ->where([['tagpalsu','=',1],['t_gadai','<=',$request->tarikh],['sttebus','=','']])
            ->orWhere([['tagpalsu','=',1],['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh]])
            ->orderBy('t_gadai', 'ASC')
            ->orderBy('sirig', 'ASC');

        $rekodaudit = $audit->get();
        $totalrekod = $audit->sum('pinjaman');
        $totalberat = $audit->sum('berat');
        $totalnilaimarhun = $audit->sum('nilaimarhun');

        $arrayrekod = ['rekod' => $rekodaudit, 'total' => $totalrekod,'totalberat' => $totalberat,'totalnilaimarhun' =>  $totalnilaimarhun];

        return response()->json($arrayrekod,200);
    }

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

        $audit = Gadai::on($cawangan['mysql'])
            ->where([['tagpalsu','=',1],['t_gadai','<=',$request->tarikh],['sttebus','=','']])
            ->orWhere([['tagpalsu','=',1],['t_update','>',$request->tarikh],['sttebus','!=',''],['t_gadai','<=',$request->tarikh]])
            ->orderBy('t_gadai', 'ASC')
            ->orderBy('sirig', 'ASC');

        $rekodaudit = $audit->get();
        $totalrekod = $audit->sum('pinjaman');
        $totalberat = $audit->sum('berat');
        $totalnilaimarhun = $audit->sum('nilaimarhun');

        $arrayrekod = ['rekod' => $rekodaudit, 'total' => $totalrekod,'totalberat' => $totalberat,'totalnilaimarhun' =>  $totalnilaimarhun];

        return response()->json($arrayrekod,200);
    }

    public function kiraupahTawarruqStokAudit($tempoh,$tempohbayar,$kadarupah,$bakipinjaman){

        $bilang_bln = $tempoh - $tempohbayar;

        $upahsemasa_raw = $bilang_bln * (($kadarupah/100 )* $bakipinjaman);

        $upah_semasa_round = number_format($upahsemasa_raw,2);
        $upah_semasa = substr($upah_semasa_round, 0, -1);
	    // $upah_semasa = floatval(preg_replace('/[^\d.]/', '', $upah_semasa));
        if(strpos($upah_semasa,',')){
            $upah_semasa = str_replace(',','',$upah_semasa);
        }
        return $upah_semasa;
    }

    public function updateJBGsemasa(Request $request){
        if($request->zone == 'all'){
            $cawangan = Cawangan::on('mysql7')->get();
        }else{
            $cawangan = Cawangan::on('mysql7')->where('zone',$request->zone)->get();
        }

        foreach($cawangan as $cawangan_mysql){
            $master = new MasterServices($cawangan_mysql['kodcaw']);
            $response = $master->updateJBGSemasa($request);
        }
        return $response;
    }

    public function PembiayaanBulanan(Request $request){

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

        $monthly_date = $monthly->startOfMonth()->subMonths(11)->toDateString();

        $cawangan_all = Cawangan::on('mysql7')->get();

        $arraybulanan = [];
        $array_totalup = [];

        for($i=0;$i<12;$i++){

                    switch($i+1){
                        case 1: $namabulan = 'Jan';
                                $tarikh = $current->year . '-' . '01';
                                break;
                        case 2: $namabulan = 'Feb';
                                $tarikh = $current->year . '-' . '02';
                                break;
                        case 3: $namabulan = 'Mac';
                                $tarikh = $current->year . '-' . '03';
                                break;
                        case 4: $namabulan = 'Apr';
                                $tarikh = $current->year . '-' . '04';
                                break;
                        case 5: $namabulan = 'Mei';
                                $tarikh = $current->year . '-' . '05';
                                break;
                        case 6: $namabulan = 'Jun';
                                $tarikh = $current->year . '-' . '06';
                                break;
                        case 7: $namabulan = 'Jul';
                                $tarikh = $current->year . '-' . '07';
                                break;
                        case 8: $namabulan = 'Aug';
                                $tarikh = $current->year . '-' . '08';
                                break;
                        case 9: $namabulan = 'Sep';
                                $tarikh = $current->year . '-' . '09';
                                break;
                        case 10: $namabulan = 'Okt';
                                $tarikh = $current->year . '-' . '10';
                                break;
                        case 11: $namabulan = 'Nov';
                                $tarikh = $current->year . '-' . '11';
                                break;
                        case 12: $namabulan = 'Dis';
                                $tarikh = $current->year . '-' . '12';
                                break;

                    }
                    array_push($array_totalup,['tarikh'=>$tarikh,'namabulan'=>$namabulan,'pembiayaan'=>0,'bayaran'=>0]);

        }

        foreach($cawangan_all as $index=>$cawangan){

            $gadai = Gadai::on($cawangan['mysql'])
                        ->selectRaw('SUM(pinjaman) AS totalpinjaman')
                        ->addSelect(DB::connection($cawangan['mysql'])
                        ->raw('DATE_FORMAT(t_gadai, "%Y-%m") as t_gadai'))
                        ->groupBy(DB::connection($cawangan['mysql'])
                        ->raw("DATE_FORMAT(t_gadai,'%Y%m')"))
                        ->whereYear('t_gadai',2021)
                        ->where([['nokp','=',$request->icno],['t_gadai','<=',$current->toDateString()],['t_gadai','>=',$monthly_date]])
                        ->orderBy('t_gadai','desc')
                        ->get();

            foreach($array_totalup as $array_index=>$totalup){
                foreach($gadai as $indexgadai=>$gadai_detail){
                    if($totalup['tarikh'] == $gadai_detail['t_gadai']){
                        $total_gadai = $totalup['pembiayaan'];
                        $total_gadai += $gadai_detail['totalpinjaman'];
                        $array_totalup[$array_index]['pembiayaan'] = $total_gadai;
                    }
                }
            }

        }

        $bayaran = Transaction::on('mysql7')->selectRaw('SUM(duit_masuk) AS totalbayaran')
                ->addSelect(DB::connection('mysql7')
                ->raw('DATE_FORMAT(created_at, "%Y-%m") as created_at'))
                ->groupBy(DB::connection('mysql7')
                ->raw("DATE_FORMAT(created_at,'%Y%m')"))
                ->where([['nokp','=',$request->icno],['created_at','<=',$current->toDateString()],['created_at','>=',$monthly_date]])
                ->orderBy('created_at','desc')
                ->get();

        foreach($array_totalup as $array_index=>$totalup){
            foreach($bayaran as $indexbayaran=>$bayaran_detail){
                if($totalup['tarikh'] == $bayaran_detail['created_at']){
                    $total_bayaran = $totalup['bayaran'];
                    $total_bayaran += $bayaran_detail['totalbayaran'];
                    $array_totalup[$array_index]['bayaran'] = $total_bayaran;
                }
            }
        }

        // asort($array_totalup);

        return response()->json($array_totalup);
    }

    public function hapusGadaiNull(){
        $cawangan_all = Cawangan::on('mysql7')->get();

        foreach($cawangan_all as $index=>$cawangan){
            $gadaiData = Gadai::on($cawangan['mysql'])->where([['nilaimarhun',null],['pinjaman',null]])->orWhere([['nilaimarhun',0.00],['pinjaman',0.00]])->pluck('norujukan');

            $deleteMarhun = Marhun::on($cawangan['mysql'])->whereIn('norujukan',$gadaiData)->delete();

            $deleteGadai = Gadai::on($cawangan['mysql'])->whereIn('norujukan',$gadaiData)->delete();
        }
    }

    public function KomoditiDashboard(){
        $current = Carbon::now();
        $current = new Carbon();
        $harini = $current->toDateString();
        $totalKomoditi = Komuditi::on('mysql7')->where([['date_purchase',$harini],['transaction_type','Beli Komoditi']])->sum('unit_komuditi');
        $totalKomoditiJual = Komuditi::on('mysql7')->where([['date_purchase',$harini],['transaction_type','Jual Komoditi']])->sum('unit_komuditi');
	    $totalUse = KomuditiTransaksi::on('mysql7')->where('tarikh',$harini)->sum('unit_komuditi');

        $bakiKomoditi = $totalKomoditi - $totalKomoditiJual  - $totalUse;
        $percentUse = 0;
        if($totalKomoditi != 0){
            $percentUse = ($totalUse/($totalKomoditi-$totalKomoditiJual))*100;
        }

        $dataKomoditi = [];

        array_push($dataKomoditi,['totalKomoditi'=>$totalKomoditi,'bakiKomoditi'=>$bakiKomoditi,'percentUse'=>$percentUse]);

        return response()->json($dataKomoditi);
    }

    public function updateLelongTawarruq(Request $request){
        $cawangan_all = Cawangan::on('mysql7')->get();

        foreach($cawangan_all as $index=>$cawangan){
            $gadai_all = Gadai::on($cawangan['mysql'])->where('sttebus','')->where('hargajualan','!=','bakihargajualan')->get();
            foreach($gadai_all as $index=>$gadai){
                $totalbayaran = round($gadai['hargajualan'] - $gadai['bakihargajualan'],1);
                $upah6bln_raw = $gadai['bakiupah'] - 0.5;

                $upah6bln_round = number_format($upah6bln_raw,2);
                $upah6bln = substr($upah6bln_round, 0, -1);

		        if(strpos($upah6bln,',')){
           		    $upah6bln = str_replace(',','',$upah6bln);
       		    }


                if($totalbayaran >= $upah6bln){
                   $update_gadai= Gadai::on($cawangan['mysql'])->where('norujukan',$gadai['norujukan'])->update([
                        'lelong_tawarruq'=>'0'
                    ]);
                }else{
                    $update_gadai=Gadai::on($cawangan['mysql'])->where('norujukan',$gadai['norujukan'])->update([
                        'lelong_tawarruq'=>'1'
                    ]);
                }
            }
        }
        return response()->json('successful');
    }

    public function upah12kosong(){
        $cawangan_all = Cawangan::on('mysql7')->get();
        $sag = [];

        foreach($cawangan_all as $index=>$cawangan){
            $gadai = Gadai::on($cawangan['mysql'])->where('upah12',0)->get();
            array_push($sag,['cawangan'=>$cawangan['details_caw'],'sag'=>$sag]);
        }

        return response()->json($sag);
    }

    public function updateTebusGadai(){
        $cawangan_all = Cawangan::on('mysql7')->get();

        foreach($cawangan_all as $index=>$cawangan){
            $gadai_all = Gadai::on($cawangan['mysql'])->join('tebus','tebus.norujukan','=','gadai.norujukan')->where('gadai.sttebus','=','')->get();

            foreach($gadai_all as $index=>$gadai){
                Gadai::on($cawangan['mysql'])->where('norujukan',$gadai['norujukan'])->update([
                    'sttebus' => 'T'
                ]);
            }

        }
    }

    public function calculateAkru(Request $request){
        $cawangan = Cawangan::on('mysql7')->where('kodcaw',$request->kodcaw)->first();
        $akru_by_cawangan = [];
        $date_request = Carbon::parse($request->date_end. ' 00:00:00');
        $gadai_all = Gadai::on($cawangan['mysql'])->where([['sttebus',''],['t_gadai','<=',$request->date_end]])->get();
        $akru_cawangan_masih_aktif = 0;
        foreach($gadai_all as $index_gadai=>$gadai){
            if($gadai['t_update'] == null){
                $tarikh_update = Carbon::parse($gadai['t_gadai'].' '. $gadai['masa']);
            }else{
                $tarikh_update = Carbon::parse($gadai['t_update'].' 00:00:00' );
            }
            $tarikh_gadai = Carbon::parse($gadai['t_gadai']. ' 00:00:00');

            $diff_gadai = date_diff($tarikh_gadai,$date_request);
            $year_gadai = $diff_gadai->format("%y") * 12;
            $month_gadai = $diff_gadai->format("%m");
            $days_gadai = $diff_gadai->format("%d");
            $days_gadai = $days_gadai + 1;

            if($days_gadai >= 1 && $days_gadai <= 15){
                $extensivemonth_gadai = 0.5;
            }
            else if($days_gadai >= 16){
                $extensivemonth_gadai = 1;
            }
            $tempoh_gadai = $year_gadai + $month_gadai + $extensivemonth_gadai;

            if($gadai['hargajualan'] > 0){
                $upah = $gadai['pinjaman'] * ($gadai['kadarupah']/100) * $tempoh_gadai;
            }else{
                $upah_asal = $gadai['nilaimarhun'] / 100 * $gadai['kadarupah'];
                $margin_pinjaman = $gadai['pinjaman'] / $gadai['nilaimarhun'] * 100;
                $margin_kadarupah = 80;
                $rebet = ($margin_kadarupah-$margin_pinjaman)/$margin_kadarupah * $upah_asal;
                $upah_sebenar = $upah_asal - $rebet;
                $upah = $upah_sebenar * $tempoh_gadai;
            }

            $akru_cawangan_masih_aktif = $akru_cawangan_masih_aktif + $upah;
        }

        $tebus_all = Gadai::on($cawangan['mysql'])->join('tebus','tebus.norujukan','=','gadai.norujukan')->where([['gadai.t_gadai','<=',$request->date_end],['gadai.t_gadai','>=',$request->date_start]])->orWhere('tebus.t_tebus','>=',$request->date_start)->get();

        // dd($tebus_all[0]);
        $akru_cawangan_tebus = 0;
        foreach($tebus_all as $index_tebus=>$tebus){
            // dd($tebus['n']);
            if($tebus['t_tebus'] > $request->date_end){
                $date_compare_end = Carbon::parse($request->date_end. ' 00:00:00');
            }else{
                $date_compare_end = Carbon::parse($tebus['t_tebus']. ' 00:00:00');
            }

            if($tebus['t_gadai'] < $request->date_start){
                $date_compare_start = Carbon::parse($request->date_start. ' 00:00:00');
            }else{
                $date_compare_start = Carbon::parse($tebus['t_gadai']. ' 00:00:00');
            }

            // $tarikh_gadai = Carbon::parse($tebus['t_gadai']. ' 00:00:00');

            $diff_gadai = date_diff($date_compare_start,$date_compare_end);
            $year_gadai = $diff_gadai->format("%y") * 12;
            $month_gadai = $diff_gadai->format("%m");
            $days_gadai = $diff_gadai->format("%d");
            $days_gadai = $days_gadai + 1;

            if($days_gadai >= 1 && $days_gadai <= 15){
                $extensivemonth_gadai = 0.5;
            }
            else if($days_gadai >= 16){
                $extensivemonth_gadai = 1;
            }
            $tempoh_gadai = $year_gadai + $month_gadai + $extensivemonth_gadai;

            if($tebus['hargajualan'] > 0){
                $upah = $tebus['pinjaman'] * ($tebus['kadarupah']/100) * $tempoh_gadai;
            }else{
                $upah_asal = $tebus['nilaimarhun'] / 100 * $tebus['kadarupah'];
                $margin_pinjaman = $tebus['pinjaman'] / $tebus['nilaimarhun'] * 100;
                $margin_kadarupah = 80;
                $rebet = ($margin_kadarupah-$margin_pinjaman)/$margin_kadarupah * $upah_asal;
                $upah_sebenar = $upah_asal - $rebet;
                $upah = $upah_sebenar * $tempoh_gadai;
            }

            $akru_cawangan_tebus = $akru_cawangan_tebus + $upah;
        }

        $total = $akru_cawangan_tebus + $akru_cawangan_masih_aktif;


        return response()->json(number_format($total,2));
    }

    public function updateTagLelong(Request $request){
        $norujukan = $request->norujukan;
        $kodcaw = $request->kodcaw;
        $t_lelong  = $request->t_lelong;

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

        $updateTag = Gadai::on($cawangan->mysql)->where('norujukan',$norujukan)->update([
            'taglel'=>1,
            't_lelong' => $t_lelong,
        ]);

        return response()->json('success');
    }

    public function revertTagLelong(Request $request){
        $norujukan = $request->norujukan;
        $kodcaw = $request->kodcaw;

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

        $updateTag = Gadai::on($cawangan->mysql)->where('norujukan',$norujukan)->update([
            'taglel'=>0,
            't_lelong' => null,
        ]);

        return response()->json('success');
    }

    public function getKadarUjrah(Request $request){
        $onePercentServices = new Onepercent($request->kodcaw);
        $kadarujrah = $onePercentServices->getKadarUjrah($request);

        return $kadarujrah;
    }

    public function getKadarUjrahByGroupId(Request $request)
    {
        $cawangan = Cawangan::on('mysql7')->where('kodcaw','=',$request->kodcaw)->select('kodcaw', 'mysql', 'group_id')->first();

        $kadar_ujrah = KadarUpah::where('group_id', $cawangan->group_id)->get();

        return $kadar_ujrah;
    }

    public function getOnePercent(Request $request){
        $onePercentServices = new Onepercent($request->kodcaw);
        $keuntungan = $onePercentServices->getOnePercent($request);

        return $keuntungan;
    }

    public function deleteAnsuran(Request $request){
        $ansuranserv = new AnsServ($request->kodcaw);
        $delete = $ansuranserv->delete($request);
        Log::info($delete);
        return response()->json($delete);
    }

    public function getLatestAdvanceWithoutDelete(Request $request){
        $ansuranserv = new AnsServ($request->kodcaw);
        $view = $ansuranserv->getLatestAdvanceWithoutDelete($request);

        return response()->json($view);
    }

    public function getApprovalFromOperation(Request $request){
        $ansuranserv = new AnsServ($request->kodcaw);
        $view = $ansuranserv->getApprovalFromOperation($request);

        return response()->json($view);
    }
}
