<?

php

namespace App\Http\Livewire\Pengusul;

use App\Constants\SysAdmin;
use App\Constants\VerifikasiDokumen;
use App\Constants\VerifikasiProposal;
use App\CustomClass\ProposalIndex;
use App\Exports\PengusulExport;
use App\Models\PerguruanTinggi;
use App\Models\ProposalDitetapkan;
use App\Models\ProposalJadwalSeleksi;
use App\Models\ReviewerPenugasan;
use App\Models\TkaiProposal;
use App\Models\TxProposalStatus;
use Carbon\Carbon;
use Excel;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Livewire\Component;
use Livewire\WithPagination;
use Maatwebsite\Excel\Facades\Excel as FacadesExcel;

class Index extends Component

{
use WithPagination;
public $search;
public $jenis_pt = -1;
public $nomor_batch = 0;
public $proposalStatuses = [];
public $statusProposal = "";
public $pernah_revisi = "-1";
public $proposal_status;
public $jadwal;
public $reviewerPenugasan;
public $tab = VerifikasiDokumen::KeyProposalAwal;
public $allProposalIds;
public $canExport = true;
public $is_super_admin = false;
public $arr_penetapan = [];

public function updatedSearch()

{
$this->resetPage();
}

public function updatedJenisPt()

{
$this->resetPage();
}

public function updatedStatusProposal()

{
$this->resetPage();
}

// mount itu seperti constructor, jadi dia akan dijalankan ketika component di
load
public function mount()
 {

$this->is_super_admin = SysAdmin::isCurrentUser();

// select all proposal ids

$this->allProposalIds = TkaiProposal::fiscalYear()->pluck('fund_id');

// select dokumen berdasarkan tab

switch ($this->tab) {
case VerifikasiDokumen::KeyProposalAwal:
$this->render();
break;
case VerifikasiDokumen::KeyPitching:
$this->render();
break;
case VerifikasiDokumen::KeyProposalLengkap:
$this->render();
break;
default:

break;
}
}

public function render()

{
$itemsPerPage = 5;

// get all proposals? dengan jumlah itemsPerPage, menggunakan fungsi

getPengusuls yang akan mengembalikan collection
$proposals = $this->getPengusuls($itemsPerPage);
if ($itemsPerPage) {
// transform collection maksudnya apa ya kak? proposalIndex tahu klo
ini semacam class untuk proposal, berarti memasukkan data ke dalam class tersebut
ya.
// getCollection() buat ambil collection dari query builder
$proposals->getCollection()->transform(function ($val) {
return new ProposalIndex($val);
});
}

// mencari fund_id dari tabel proposal

$fundIds = $proposals->pluck('fund_id');

// mencari status proposal

$this->proposal_status = TxProposalStatus::whereIn('fund_id', $fundIds)
->where('key', VerifikasiProposal::KeyPitching)->get()
->mapWithKeys(function ($item) {
return [$item['fund_id'] => ['status' => $item]];
});

// mencari jadwal proposal

$this->jadwal = ProposalJadwalSeleksi::whereIn('fund_id', $fundIds)
->where('jenis', 'Pitching')->get()
->mapWithKeys(function ($item) {
return [$item['fund_id'] => ['status' => $item]];
});
 // mencari deskripsi status proposal?
$this->proposalStatuses = TxProposalStatus::whereIn('fund_id', $fundIds)
->where('key', $this->tab)
->get()
->append('is_revisi')
->mapWithKeys(function ($item, $key) {
return [$item['fund_id'] => [
'status' => @$item['value'],
'is_read' => @$item['is_read'] ?? true,
'is_revisi' => @$item['is_revisi'],
'updated_by' => @$item['updated_by'],
]];
});
return view('[Link]', compact('proposals'));
}

// mencari pengusul proposal, hasilnya berupa collection

public function getPengusuls($paginate = 0)
{
// mencari status proposal berdasarkan fund_id
$filteredStatus = TxProposalStatus::query()->whereIn('fund_id', $this-
>allProposalIds)->where('key', $this->tab)->get();
// mencari penetapan proposal berdasarkan fund_id
$penetapans = ProposalDitetapkan::whereIn('fund_id', $this-
>allProposalIds)->get();

// looping penetapan proposal untuk mendapatkan dana_mf dan memasukkannya

ke dalam array penetapan
foreach ($penetapans as $penetapan) {
$this->arr_penetapan[$penetapan->fund_id] = $penetapan;
$this->arr_penetapan[$penetapan->fund_id]->dana_mf =
number_format($this->arr_penetapan[$penetapan->fund_id]->dana_mf);

}
// sudah mencoba dd tapi belum mengerti maksud penetapan itu id apa ya kak?
apakah fund_id?
dd($this->arr_penetapan);

// mencari fund_id yang memiliki status proposal, menggunakan fungsi yg udh

dibuat sebelumnya
$allProposalIdsHaveStatus = $filteredStatus->pluck('fund_id');

// mencari fund_id yang tidak memiliki status proposal

$filteredIds = collect([]);
if ($this->statusProposal !== "") {
// jika status proposal itu pending,
if ($this->statusProposal == VerifikasiProposal::StatusPending) {
// jika pending maka filter status proposal berdasarkan value
status pending yg didapat dari statement if
$filteredStatus = $filteredStatus->where('value', $this-
>statusProposal);
// mencari fund_id yang tidak memiliki status proposal
$allProposalIdsDoesntHaveStatus = $this->allProposalIds-
>diff($allProposalIdsHaveStatus);
$filteredIds = $allProposalIdsDoesntHaveStatus-
>merge($filteredStatus->pluck('fund_id'));
} else {
// jika status proposal tidak kosong, maka filter status proposal
berdasarkan value
$filteredStatus = $filteredStatus->where('value', $this-
>statusProposal);
}
}
// cara kerja $this di sini gimana ya? objectnya berarti proposal? tapi kok bisa
manggil pernah_revisi ya?
if ($this->pernah_revisi !== "-1") {

$filteredStatus = $filteredStatus->where('revisi', '>', 0);

}

// merge fund_id yang memiliki status pending, ketika tidak maka ambil
fund_id yang tidak memiliki status pending
if ($this->statusProposal == VerifikasiProposal::StatusPending) {
$filteredIds = $allProposalIdsDoesntHaveStatus->merge($filteredStatus-
>pluck('fund_id'));
} else {
//
$filteredIds = $filteredStatus->pluck('fund_id');
}

// melakukan query builder untuk mencari pengusul proposal, dengan kondisi

yang sudah di filter sebelumnya,
// kemudian melakukan join dengan beberapa tabel lainnya
$builder = DB::connection('tkai')->query()->fromSub(function
($fund_innovator_mitra) {
$fund_innovator_mitra->fromSub(function ($fund_bid) {
// mencari fund_bid berdasarkan fiscal year 2023 dan select
bid_ref_number_type untuk menentukan innovator_id dan mitra_id
$fund_bid->from('tkai_matching_fund', 'tmf')->where('fiscal_year',
2023)->select([
'tmf.*', DB::raw("SPLIT_PART(tmf.bid_ref_number, '-', '1') as
bid_ref_number_type")
]);
// melakukan pemilihan kolom yang akan di select antara
innovator_id atau mitra_id, tapi kenapa biddernya di select juga ya kak?
}, 'fund_bid')->select([
'fund_bid.*',
DB::raw("CASE WHEN bid_ref_number_type = '01' THEN owner_profile_id
ELSE bidder_profile_id END as innovator_id"),
DB::raw("CASE WHEN bid_ref_number_type = '01' THEN
bidder_profile_id ELSE owner_profile_id END as mitra_id")
]);

// ketika casenyya adalah 01 maka mencari atribut innovator

}, 'fund_innovator_mitra')
->leftJoin('tkai_profile_industry as mitra', 'mitra.profile_id', '=',
'fund_innovator_mitra.mitra_id')
->leftJoin('tkai_profile_innovator as innovator',
'innovator.profile_id', '=', 'fund_innovator_mitra.innovator_id')
->leftJoin('tkai_user as user_mitra', 'user_mitra.user_id', '=',
'mitra.user_id')
->leftJoin('tkai_user as user_innovator', 'user_innovator.user_id',
'=', 'innovator.user_id')
->leftJoin('tkai_master_pt as pt', DB::raw("split_part(innovator.id_sp,
':', 2)"), '=', 'pt.kode_pt')
->select([
'fund_innovator_mitra.fund_id',
 DB::raw("fund_innovator_mitra.description::jsonb->'data' as data"),
'fund_innovator_mitra.title',
'fund_innovator_mitra.mf_type',
'fund_innovator_mitra.created_at',
'user_innovator.name as innovator_name',
'[Link] as innovator_nidn',
DB::raw("split_part(innovator.id_sp, ':', 2) as
innovator_kode_pt"),
'[Link] as innovator_nama_pt',
'innovator.organisation_name as innovator_organisation_name',
'user_innovator.email as innovator_email',
'user_innovator.phone as innovator_phone',
'mitra.company_name as mitra_company_name'
])
->when(!empty($this->search), function ($query) {
$query->where(function ($query) {
$query->where('fund_innovator_mitra.title', 'ilike', '%' .
$this->search . '%')
->orWhere('user_innovator.name', 'ilike', '%' . $this-
>search . '%')
->orWhere('fund_innovator_mitra.fund_id', 'ilike', '%' .
$this->search . '%')
->orWhere('innovator.organisation_name', 'ilike', '%' .
$this->search . '%')
->orWhere('mitra.company_name', 'ilike', '%' . $this-
>search . '%');
});
})
->when($this->nomor_batch > 0, function ($query) {
if ($this->nomor_batch == 1) {
$query->where('fund_id', '<=', 14422);
} else if ($this->nomor_batch == 2) {
$query->whereBetween('fund_id', [14423, 18925]);
} else if ($this->nomor_batch == 3) {
$query->where('fund_id', '>', 18925);
}
})
->when($this->jenis_pt >= 0, function ($query) {
$query->where('fund_innovator_mitra.mf_type', $this->jenis_pt);
})
->when($this->statusProposal !== "" || $this->pernah_revisi !== "-1",
function ($query) use ($filteredIds) {
$query->whereIn('fund_id', $filteredIds);
})
->when($this->tab !== VerifikasiDokumen::KeyProposalAwal, function
($query) use ($allProposalIdsHaveStatus) {
$query->whereIn('fund_id', $allProposalIdsHaveStatus);
})
->orderBy('fund_innovator_mitra.fund_id');
// menyesuaikan jumlah query dengan variabel yang telah ditentukan
if ($paginate) {
return $builder->paginate($paginate);
}
return $builder->get();
}

public function export()

{
if (!$this->canExport) {
 return;
}
$this->reviewerPenugasan =
Cache::remember('reviewer_penugasan_proposal_index',
VerifikasiProposal::ExportCacheTTL, function () {
return ReviewerPenugasan::with(['reviewer:id,name',
'penilaians:id,penugasan_id,kriteria_id,is_rekon,nilai'])->get([
'id', 'reviewer_user_id', 'fund_id', 'jenis', 'position',
'penilaian_mandiri', 'penilaian_mandiri_rekon', 'status', 'status_rekon',
]);
});
$proposals = Cache::remember(VerifikasiProposal::ExportCacheKey .
'_fund_' . $this->statusProposal . '_' . $this->pernah_revisi . '_' . $this->tab .
'_' . $this->jenis_pt . '_' . $this->nomor_batch,
VerifikasiProposal::ExportCacheTTL, function () {
return $this->getPengusuls();
});
$proposals->transform(function ($val) {
return new ProposalIndex($val, $this->reviewerPenugasan);
});
$mapPtSatker = Cache::remember(VerifikasiProposal::ExportCacheKey . '_pt',
VerifikasiProposal::ExportCacheTTL, function () {
return PerguruanTinggi::get()->pluck('namaSatker', 'kode_pt');
});
$txProposalStatus = TxProposalStatus::query()
->whereIn('key', [
VerifikasiDokumen::KeyProposalAwal,
VerifikasiDokumen::KeyPitching,
VerifikasiDokumen::KeyProposalLengkap,
VerifikasiDokumen::KeyDeskEvaluation,
])
->whereIn('fund_id', $proposals->pluck('fund_id'))
->with('updatedByRelation')
->get();
$mapFundIdStatus = [];
foreach ($txProposalStatus as $pStatus) {
if (!isset($mapFundIdStatus[$pStatus->fund_id])) {
$mapFundIdStatus[$pStatus->fund_id] = [];
}
if (!isset($mapFundIdStatus[$pStatus->fund_id][$pStatus->key])) {
$mapFundIdStatus[$pStatus->fund_id][$pStatus->key] = [];
}
$mapFundIdStatus[$pStatus->fund_id][$pStatus->key]['value'] =
VerifikasiProposal::toTitle($pStatus->value);
$mapFundIdStatus[$pStatus->fund_id][$pStatus->key]['updated_by'] =
$pStatus->updated_by;
}
return FacadesExcel::download(new PengusulExport($proposals,
$mapFundIdStatus, $mapPtSatker), 'Export Pengusul - ' . Carbon::now()->timestamp .
'.xlsx');
}
}