Default page

Edit File: Tagihan.php

<?php

namespace Modules\Tagihan\Controllers;
use App\Controllers\BaseController;
use Ramsey\Uuid\Uuid;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\IOFactory; 
use PhpOffice\PhpSpreadsheet\Reader\Exception;

class Tagihan extends BaseController
{
    
    public function index()
    {
        echo "OK";
    }

public function list($id = null){
        $data['pembayaran']  = $this->db->table('pembayaran')->where('id_pembayaran', enc($id))->get()->getRow();
        
        $data['title']  = 'Tagihan '.$data['pembayaran']->nama;
        $data['li_1']   = 'Daftar Tagihan '.$data['pembayaran']->nama;
        $data['body']   = 'Modules\Tagihan\Views\list.php';
        $b = $this->db->table('penerima_beasiswa pb')
            ->join('beasiswa b', 'pb.id_beasiswa = b.id', 'left')
            ->where('b.id_pembayaran', $data['pembayaran']->id_pembayaran)
            ->get()->getResult();

foreach ($b as $k) {
            $data['beasiswa'][$k->id_mhs] = $k->jumlah;
        }

$data['table']  = $this->db->table('tagihan')
            ->select('tagihan.*, mhs.nama, mhs.nim, mhs.jurusan')
            ->join('mhs', 'tagihan.id_mhs = mhs.id')
            ->get()->getResult();

$data['kelompok'] = $this->db->table('kelompok')->select('nama, id_kelompok')->groupBy('id_kelompok, nama')->get()->getResult();
        
        return view('template', $data);
    }

public function getTagihan(){

$p = $this->request->getPost();

$pembayaran = $this->db->table('pembayaran p')
            ->where('id_pembayaran', $p['id_pembayaran'])
            ->get()->getRow();

$d  = $this->db->table('tagihan')
            ->select('tagihan.*, mhs.nama, mhs.nim, mhs.jurusan')
            ->join('mhs', 'tagihan.id_mhs = mhs.id', 'left')
            ->where('tagihan.id_pembayaran', $p['id_pembayaran']);
        
        if($p['smt'] != ''){
            $d->where('tagihan.smt', $p['smt']);
        }
        if($p['jurusan'] != ''){
            $d->where('mhs.jurusan', $p['jurusan']);
        }
        if($p['angkatan'] != ''){
            $d->where('SUBSTRING(nim, 1, 2)', $p['angkatan']);
        }
        if($p['kelompok'] != ''){
            $kelompok = $this->db->table('kelompok')->select('id_mhs')->where('id_kelompok', $p['kelompok'])->get()->getResultArray();
            $filterKelompok = array_column($kelompok, 'id_mhs');

$d->whereIn('id_mhs', $filterKelompok);
        }

$dt = $d->get()->getResultArray();

if($dt){
            $filterIdMhs = array_column($dt, 'id_mhs');
            $dbTransaksi = $this->db->table('transaksi')
                ->where('id_pembayaran', $p['id_pembayaran'])
                ->whereIn('id_mhs', $filterIdMhs);

$transaksi = $dbTransaksi->get()->getResult();            
        
        
            foreach ($transaksi as $k) {
                if($pembayaran->frek == '5'){
                    if($k->sumber_dana == ''){
                        $tr[$k->id_mhs][] = $k->jumlah;
                    }else{
                        $trb[$k->id_mhs][] = $k->jumlah;
                    }
                }else{
                    if($k->sumber_dana == ''){
                        $tr[$k->id_mhs][$k->smt][] = $k->jumlah;
                    }else{
                        $trb[$k->id_mhs][$k->smt][] = $k->jumlah;
                    }
                }    
            }

}

$out = [];
        $hitung_jml[] = 0;
        $hitung_beasiswa[] = 0;
        $hitung_terbayar[] = 0;
        $hitung_sisa[] = 0;

foreach ($dt as $k) {
            $baya = 0;
            $beasisw = 0;
            $id_mhs = $k['id_mhs'];
            $smt = $k['smt'];

if($pembayaran->frek == '5'){
                if(isset($tr[$id_mhs])){
                    $baya = array_sum($tr[$id_mhs]);
                }

if(isset($trb[$id_mhs])){
                    $beasisw = array_sum($trb[$id_mhs]);
                }                
            }else{
                if(isset($tr[$id_mhs][$smt])){
                    $baya = array_sum($tr[$id_mhs][$smt]);
                }

if(isset($trb[$id_mhs][$smt])){
                    $beasisw = array_sum($trb[$id_mhs][$smt]);
                }
            }
            
            $k['prodi'] = jur($k['jurusan']);
            $k['jml'] = number_format($k['jumlah']);
            $k['beasiswa'] = number_format($beasisw);
            $k['bayar'] = number_format($baya);

$k['sisa'] = number_format($k['jumlah'] - $beasisw - $baya);
            $k['aksi'] = '<a class="btn btn-warning btn-sm" onclick="editTagihan(`'.$k['id'].'`)">Edit</a><a class="btn btn-danger btn-sm" onclick="hapus(`'.base_url('tagihan/hapus/').$k['id'].'`)">Hapus</a>';

$out['data'][] = $k;

$hitung_jml[] = $k['jumlah'];
            $hitung_beasiswa[] = $beasisw;
            $hitung_terbayar[] = $baya;
            $hitung_sisa[] = $k['jumlah'] - $beasisw - $baya;
        }

$out['jml'] = number_format(array_sum($hitung_jml));
        $out['beasiswa'] = number_format(array_sum($hitung_beasiswa));
        $out['bayar'] = number_format(array_sum($hitung_terbayar));
        //$out['bayar'] = $tr['64f9c9c1072b0'];
        $out['sisa'] = number_format(array_sum($hitung_sisa));
        $out['monitor'] = '';

return json_encode($out);
    }

public function form($id){
        $p  = $this->db->table('pembayaran')->where('id_pembayaran', enc($id))->get()->getRow();
        // 1. Inisialisasi Spreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        $idTransaksi = $p->id_pembayaran;
        $semester = '';

// ----------------------------------------------------
        // A. JUDUL DAN METADATA
        // ----------------------------------------------------

// Judul: "Form Tagihan"
        $sheet->setCellValue('A1', 'FORM TAGIHAN '.$p->nama);
        $sheet->mergeCells('A1:D1'); // Gabungkan sel untuk judul
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// Metadata: ID
        $sheet->setCellValue('A3', 'ID');
        $sheet->setCellValue('B3', $idTransaksi);
        $sheet->getStyle('A3')->getFont()->setBold(true);

// Metadata: SMT (Semester) - Highlight kuning
        $sheet->setCellValue('A4', 'SMT');
        $sheet->setCellValue('B4', '');
        $sheet->getStyle('A4')->getFont()->setBold(true);
        
        // Atur background kuning untuk nilai SMT
        $sheet->getStyle('B4')->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFFFFF00'); // Kode ARGB untuk Kuning

// ----------------------------------------------------
        // B. HEADER TABEL
        // ----------------------------------------------------

$header = ['No', 'NIM', 'Nama', 'Tagihan'];
        $kolomMulai = 'A';
        $barisMulai = 6;
        $barisAkhir = $barisMulai + 10;
        
        // Tulis Header
        foreach ($header as $key => $judul) {
            $kolom = chr(ord($kolomMulai) + $key); // A, B, C, D
            $sheet->setCellValue($kolom . $barisMulai, $judul);
        }

$sheet->getColumnDimension('A')->setWidth(8);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(60);
        $sheet->getColumnDimension('D')->setWidth(25);
        // Terapkan Styling pada Header
        $sheet->getStyle($kolomMulai . $barisMulai . ':' . $kolom . $barisMulai)->getFont()->setBold(true);
        $sheet->getStyle($kolomMulai . $barisMulai . ':' . $kolom . $barisMulai)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

// ----------------------------------------------------
        // C. ISI DATA TABEL
        // ----------------------------------------------------
        $row = $barisMulai + 1; // Mulai dari baris ke-7 (setelah header di baris 6)
        $no = 1;
        
        for ($i=$barisMulai; $i < $barisAkhir; $i++) {
            // Highlight baris data dengan warna kuning
            $sheet->getStyle('A' . $row . ':D' . $row)->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()->setARGB('FFFFFF00');

// Format Tagihan sebagai mata uang
            $sheet->getStyle('D' . $row)->getNumberFormat()->setFormatCode('#,##0');
            
            $row++;
        }
        
        // ----------------------------------------------------
        // D. TAMBAHKAN BORDER (Garis Tepi) untuk Tabel
        // ----------------------------------------------------
        
        // Border di sekitar seluruh tabel (dari header hingga baris data terakhir)
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

// Terapkan border dari sel header A6 sampai sel data terakhir D[barisAkhir]
        $sheet->getStyle('A6:D' . $barisAkhir)->applyFromArray($styleArray);

// ----------------------------------------------------
        // E. PROSES UNDUH FILE
        // ----------------------------------------------------

$writer = new Xlsx($spreadsheet);
        $filename = 'FORM TAGIHAN ' . $p->nama; // Nama file unduhan

// Atur header untuk download file
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        
        // Output file ke browser
        $writer->save('php://output');
        exit;
    }

public function verifikasiInput(){
        // 1. Ambil file dari request
        $file = $this->request->getFile('file');

if (!$file || !$file->isValid() || $file->hasMoved()) {
            // Cek apakah ada file yang diupload dan valid
            session()->setFlashData(gagal('Silakan pilih file Excel yang valid.'));
            return redirect()->back();
        }

// Cek ekstensi file
        $ext = $file->getClientExtension();
        if ($ext !== 'xlsx' && $ext !== 'xls') {
            session()->setFlashData(gagal('Format file harus .xlsx atau .xls.'));
            return redirect()->back();
        }

// 2. Tentukan Reader berdasarkan ekstensi file
        $reader = IOFactory::createReaderForFile($file->getTempName());
        
        try {
            // 3. Muat file ke objek Spreadsheet
            $spreadsheet = $reader->load($file->getTempName());
            $sheet = $spreadsheet->getActiveSheet();
            
            // Dapatkan baris terakhir yang memiliki data
            $highestRow = $sheet->getHighestRow();

$dataTagihan = [];
            
            // Asumsi: Data Tagihan dimulai dari BARIS ke-7 (setelah header di baris 6)
            $startRow = 7; 
            $allNimsFromExcel = [];

for ($row = $startRow; $row <= $highestRow; $row++) {
                $nim = $sheet->getCell('B' . $row)->getValue();
                if (!empty($nim)) {
                    $allNimsFromExcel[] = $nim;
                }
            }

$uniqueNims = array_unique($allNimsFromExcel);
        
            // ⭐️ Jika tidak ada NIM, hentikan proses untuk efisiensi
            if (empty($uniqueNims)) {
                session()->setFlashData(gagal('Tidak ditemukan NIM dalam file'));
                return redirect()->back()->with('error', 'Tidak ditemukan NIM dalam file.');
            }

$existingUsers = $this->db->table('mhs')->select('nim, id') // Ambil kolom yang diperlukan saja
                                   ->whereIn('nim', $uniqueNims)
                                   ->get()->getResultArray();

// 3. Konversi hasil query menjadi array asosiatif (NIM sebagai key)
            $existingNimMap = [];

foreach ($existingUsers as $user) {
                $existingNimMap[$user['nim']] = true; // Gunakan 'true' sebagai penanda keberadaan
                $id[$user['nim']]= $user['id'];
            }
            
            // Loop dari baris data pertama hingga baris terakhir
            for ($row = $startRow; $row <= $highestRow; $row++) {
                // Ambil data dari kolom yang sesuai:
                // Kolom B = NIM, Kolom C = Nama, Kolom D = Tagihan
                $nim      = $sheet->getCell('B' . $row)->getValue();
                $nama     = $sheet->getCell('C' . $row)->getValue();
                $tagihan  = $sheet->getCell('D' . $row)->getValue();

$status = FALSE; // Status default
                $id_mhs = '';
                // Cek jika NIM tidak kosong (untuk menghindari baris kosong di akhir)
                if (!empty($nim)) {
                    // ⭐️ Cek ketersediaan di array massal (Sangat Cepat, tidak ada query database)
                    if (isset($existingNimMap[$nim])) {
                        $status =  TRUE; 
                        $id_mhs = $id[$nim];
                    }

$dataTagihan[] = [
                        'nim'     => $nim,
                        'nama'    => $nama,
                        'tagihan' => $tagihan,
                        'status'  => $status,
                        'id'      => $id_mhs
                    ];
                }

}

// Dapatkan Metadata jika diperlukan (misal: ID dan SMT)
            $idTransaksi = $sheet->getCell('B3')->getValue();
            $semester    = $sheet->getCell('B4')->getValue();

// Lakukan sesuatu dengan data (misalnya, simpan ke database atau tampilkan)
            // Di sini, kita akan menampilkan hasilnya
            $p = $this->request->getPost();

$data['title']  = 'Verifikasi Tagihan';
            $data['body']   = 'Modules\Tagihan\Views\verifikasi.php';
            $data['table']  = $dataTagihan;
            $data['id_pembayaran'] = $p['data_id'];
            $data['smt'] = $sheet->getCell('B4')->getValue();
            
            return view('template', $data);

} catch (Exception $e) {
            // Tangani error jika file tidak bisa dibaca
            session()->setFlashData(gagal('Gagal membaca file Excel: ' . $e->getMessage()));
            return redirect()->back();
        }
    }

public function add(){
        $p = $this->request->getPost();
        $db = $this->db->table('tagihan');
        $cek = $db->where(['smt' => $p['smt'], 'id_pembayaran' => $p['id_p']])->whereIn('id_mhs', $p['id_mhs'])->get()->getResultArray();
        
        foreach ($cek as $k) {
            $st[$k['id_mhs']] = $k['id'];
        }
        $update = [];
        $insert = [];
        $act_update = FALSE;
        $act_insert = FALSE;
        $hitung_update = 1;
        $hitung_insert = 1;

for($i=0; $i< count($p['id_mhs']); $i++){            
            if(isset($st[$p['id_mhs'][$i]])){
                $act_update = TRUE;
                $update[] = [
                    'id' => $st[$p['id_mhs'][$i]],
                    'id_pembayaran' => $p['id_p'],
                    'jumlah' => $p['tagihan'][$i],
                    'smt' => $p['smt'],
                    'id_mhs' => $p['id_mhs'][$i],
                    'ta' => $p['ta']
                ];

$hitung_update++;
            }else{
                $id = Uuid::uuid4()->toString();
                $act_insert = TRUE;
                $insert[] = [
                    'id' => $id,
                    'id_pembayaran' => $p['id_p'],
                    'jumlah' => $p['tagihan'][$i],
                    'smt' => $p['smt'],
                    'id_mhs' => $p['id_mhs'][$i],
                    'ta' => $p['ta']
                ];
                $hitung_insert++;
            }
        }

$jml_insert = $hitung_insert - 1;
        $jml_update = $hitung_update - 1;
        if($act_insert){
            $db->insertBatch($insert);
        }
        
        if($act_update){
            $db->updateBatch($update, 'id');
        }

session()->setFlashData(success('Berhasil Mengupdate '.$jml_update.' dan Menginput '.$jml_insert.' Data'));
        return redirect()->to(base_url('tagihan/list/'.dc($p['id_p'])));
        echo '<pre>';
            print_r($update);
        echo '</pre>';
    }

public function edit(){
        $p = $this->request->getPost();
        $d = $this->db->table('tagihan')->where('id', $p['data_id'])->update(['jumlah' => $p['jumlah']]);
        session()->setFlashData(success('Berhasil Mengupdate Data'));
        return redirect()->back();

}

public function hapus($id){
        //$d = $this->db->table('tagihan')->where('id', $id)->delete();
        session()->setFlashData(success('Berhasil Menghapus Data'));
        return redirect()->back();

}
}