<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, DELETE, PUT');
header('Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept, Authorization');
header('Access-Control-Allow-Credentials: true');
header('Content-Type: application/json');

defined('BASEPATH') or exit('No direct script access allowed');

use chriskacerguis\RestServer\RestController;

class Api extends RestController
{
  function __construct()
  {
    parent::__construct();
    $this->load->model('model');
    $this->load->library('Pdf');
    // $this->db->query("SET sql_mode = '' ");
    date_default_timezone_set("Asia/Kuala_Lumpur");
  }

  public function index_get()
  {
    $this->response(['message' => 'Halo Bosku', 'status' => true], 200);
    // redirect(base_url());

  }

  public function index_post()
  {
    $this->response(['message' => 'Halo Bosku post', 'status' => true], 400);
    // redirect(base_url());

  }




  // -----------------------------------------------------------------------------------------------------------

  public function siswa_post()
  {
    $nama = $this->post('nama');
    $jenis_kelamin = $this->post('jenis_kelamin');
    $tanggal_lahir = $this->post('tanggal_lahir');
    $tempat_lahir = $this->post('tempat_lahir');
    $alamat = $this->post('alamat');
    $no_telpon = $this->post('no_telpon');
    $agama = $this->post('agama');
    $kewarganegaraan = $this->post('kewarganegaraan');
    $pendidikan_sd = $this->post('pendidikan_sd');
    $pendidikan_smp = $this->post('pendidikan_smp');
    $pendidikan_sma = $this->post('pendidikan_sma');
    $kemampuan = $this->post('kemampuan');
    $hobi = $this->post('hobi');
    $foto = $_FILES['foto'];

    $cek_last_ai = $this->model->cek_last_ai('tb_siswa');

    $upload_dir = 'assets/siswa/' . $cek_last_ai . '/';
    if (!is_dir($upload_dir)) {
      mkdir($upload_dir);
    }

    $path = $upload_dir . $foto['name'];
    move_uploaded_file($foto['tmp_name'], $path);

    $array = [
      'nama' => $nama,
      'jenis_kelamin' => $jenis_kelamin,
      'tanggal_lahir' => $tanggal_lahir,
      'tempat_lahir' => $tempat_lahir,
      'alamat' => $alamat,
      'no_telpon' => $no_telpon,
      'agama' => $agama,
      'kewarganegaraan' => $kewarganegaraan,
      'pendidikan_sd' => $pendidikan_sd,
      'pendidikan_smp' => $pendidikan_smp,
      'pendidikan_sma' => $pendidikan_sma,
      'kemampuan' => $kemampuan,
      'hobi' => $hobi,
      'img_url' => $path
    ];

    $this->model->insert('tb_siswa', $array);



    $this->response(['message' => 'ini untuk siswa post', 'status' => $array], 200);
  }

  public function siswa_get()
  {
    $data = $this->model->custom_query("SELECT * FROM tb_siswa ORDER BY nama Asc")->result();
    $this->response(['message' => 'ini untuk siswa get', 'status' => true, 'data' => $data], 200);
  }

  public function siswa_detail_get()
  {
    $id = $this->get('id');
    $data = $this->model->tampil_data_where('tb_siswa', ['id_siswa' => $id])->result();
    if (count($data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 200);
    $this->response(['message' => 'ini untuk siswa get', 'status' => true, 'data' => $data[0]], 200);
  }

  public function siswa_edit_post()
  {
    $id = $this->get('id');
    $nama = $this->post('nama');
    $tanggal_lahir = $this->post('tanggal_lahir');
    $tempat_lahir = $this->post('tempat_lahir');
    $jenis_kelamin = $this->post('jenis_kelamin');
    $alamat = $this->post('alamat');
    $no_telpon = $this->post('no_telpon');
    $agama = $this->post('agama');
    $kewarganegaraan = $this->post('kewarganegaraan');
    $pendidikan_sd = $this->post('pendidikan_sd');
    $pendidikan_smp = $this->post('pendidikan_smp');
    $pendidikan_sma = $this->post('pendidikan_sma');
    $kemampuan = $this->post('kemampuan');
    $hobi = $this->post('hobi');

    $cek_data = $this->model->tampil_data_where('tb_siswa', ['id_siswa' => $id])->result();
    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);

    $array = [
      'nama' => $nama,
      'tanggal_lahir' => $tanggal_lahir,
      'tempat_lahir' => $tempat_lahir,
      'jenis_kelamin' => $jenis_kelamin,
      'alamat' => $alamat,
      'no_telpon' => $no_telpon,
      'agama' => $agama,
      'kewarganegaraan' => $kewarganegaraan,
      'pendidikan_sd' => $pendidikan_sd,
      'pendidikan_smp' => $pendidikan_smp,
      'pendidikan_sma' => $pendidikan_sma,
      'kemampuan' => $kemampuan,
      'hobi' => $hobi
    ];

    $this->model->update('tb_siswa', ['id_siswa' => $id], $array);


    $this->response(['message' => 'ini untuk update siswa', 'status' => true], 200);
  }

  // siswa delete
  public function siswa_delete_post()
  {
    $id = $this->post('id');
    $cek_data = $this->model->tampil_data_where('tb_siswa', ['id_siswa' => $id])->result();
    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);
    // delete the folder
    $upload_dir = 'assets/siswa/' . $id . '/';
    if (is_dir($upload_dir)) {
      array_map('unlink', glob("$upload_dir/*.*"));
      rmdir($upload_dir);
    }
    $this->model->delete('tb_siswa', ['id_siswa' => $id]);

    $this->response(['message' => 'ini untuk siswa delete', 'status' => true], 200);
  }


  public function dana_sosial_post()
  {
    $nama = $this->post('nama') == '' ? null : $this->post('nama');
    $jumlah = $this->post('jumlah') == '' ? null : $this->post('jumlah');
    $jumlah_barang = $this->post('jumlah_barang') == '' ? null : $this->post('jumlah_barang');
    $tanggal = $this->post('tanggal');
    $ket = $this->post('ket') == '' ? null : $this->post('ket');
    $jenis = $this->post('jenis');
    $bentuk = $this->post('bentuk');
    $jenis_barang = $this->post('jenis_barang');
    $satuan = $this->post('satuan');
    $jumlah_barang = $this->post('jumlah_barang');

    $array = [
      'nama' => $nama,
      'jumlah' => $jumlah ?? $jumlah_barang,
      'tanggal' => $tanggal,
      'ket' => $ket,
      'jenis' => $jenis,
      'bentuk' => $bentuk,
      'jenis_barang' => $jenis_barang,
      'satuan' => $satuan,
      'jumlah_barang' => $jumlah_barang
    ];

    $this->model->insert('tb_dana_sosial', $array);

    $this->response(['message' => 'ini untuk dana sosial', 'status' => true], 200);
  }

  public function dana_sosial_khusus_post()
  {
    $donatur = $this->post('donatur') == '' ? null : $this->post('donatur');
    $jumlah = $this->post('jumlah') == '' ? null : $this->post('jumlah');
    $tanggal = $this->post('tanggal');
    $ket = $this->post('ket') == '' ? null : $this->post('ket');
    $jenis = $this->post('jenis');
    $bentuk = $this->post('bentuk');
    $jenis_barang = $this->post('jenis_barang');
    $satuan = $this->post('satuan');
    $jumlah_barang = $this->post('jumlah_barang');

    $array = [
      'id_donatur' => $donatur,
      'jumlah' => $jumlah,
      'tanggal' => $tanggal,
      'ket' => $ket,
      'jenis' => $jenis,
      'bentuk' => $bentuk,
      'jenis_barang' => $jenis_barang,
      'satuan' => $satuan,
      'jumlah_barang' => $jumlah_barang
    ];

    $query = $this->model->insert('tb_dana_sosial_khusus', $array);

    if ($query) {
      $this->response(['message' => 'ini untuk dana sosial', 'status' => true], 200);
    } else {
      $this->response(['message' => 'ini untuk dana sosial', 'status' => false], 400);
    }
  }


  public function dana_sosial_get()
  {
    // $data = $this->model->tampil_data_keseluruhan('tb_dana_sosial')->result();
    $data = $this->model->custom_query("SELECT * FROM tb_dana_sosial ORDER BY nama Asc")->result();
    $this->response(['message' => 'ini untuk dana sosial get', 'status' => true, 'data' => $data], 200);
  }

  // ini untuk khusus
  public function dana_sosial_khusus_get()
  {
    // $data = $this->model->tampil_data_keseluruhan('tb_dana_sosial')->result();
    $data = $this->model->custom_query("SELECT a.*, b.nama_donatur as nama FROM tb_dana_sosial_khusus a join tb_donatur b on a.id_donatur = b.id_donatur ORDER BY b.nama_donatur Asc")->result();
    $this->response(['message' => 'ini untuk dana sosial get', 'status' => true, 'data' => $data], 200);
  }

  public function dana_sosial_detail_get()
  {
    $id = $this->get('id');
    $data = $this->model->tampil_data_where('tb_dana_sosial', ['id_dana_sosial' => $id])->result();
    if (count($data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);
    $this->response(['message' => 'ini untuk dana sosial get', 'status' => true, 'data' => $data[0]], 200);
  }

  public function dana_sosial_khusus_detail_get()
  {
    $id = $this->get('id');
    $data = $this->model->custom_query("SELECT a.*, b.nama_donatur as nama FROM tb_dana_sosial_khusus a join tb_donatur b on a.id_donatur = b.id_donatur where a.id_dana_sosial = $id")->result();
    if (count($data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);
    $this->response(['message' => 'ini untuk dana sosial get', 'status' => true, 'data' => $data[0]], 200);
  }


  public function filter_dana_post()
  {
    $sql = $this->post('sql');

    $data = $this->model->custom_query($sql)->result();

    $jumlah_donasi = 0;
    $jumlah_pengeluaran = 0;

    foreach ($data as $d) {
      if ($d->bentuk == 'Pemasukan') {
        $jumlah_donasi += $d->jumlah;
      }

      if ($d->bentuk == 'Pengeluaran') {
        $jumlah_pengeluaran += $d->jumlah;
      }
    }




    $this->response(['message' => 'ini untuk filter dana', 'status' => true, 'data' => $data, 'jumlah_donasi' => $jumlah_donasi, 'jumlah_pengeluaran' => $jumlah_pengeluaran], 200);
  }

  public function filter_dana_khusus_post()
  {
    $sql = $this->post('sql');

    $data = $this->model->custom_query($sql)->result();

    $jumlah_donasi = 0;
    $jumlah_pengeluaran = 0;

    foreach ($data as $d) {
      if ($d->bentuk == 'Pemasukan') {
        $jumlah_donasi += $d->jumlah;
      }

      if ($d->bentuk == 'Pengeluaran') {
        $jumlah_pengeluaran += $d->jumlah;
      }
    }




    $this->response(['message' => 'ini untuk filter dana', 'status' => true, 'data' => $data, 'jumlah_donasi' => $jumlah_donasi, 'jumlah_pengeluaran' => $jumlah_pengeluaran], 200);
  }

  public function hapus_dana_sosial_post()
  {
    $id_dana_sosial = $this->post('id_dana_sosial');

    $cek_data = $this->model->tampil_data_where('tb_dana_sosial', ['id_dana_sosial' => $id_dana_sosial])->result();
    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);

    $this->model->delete('tb_dana_sosial', ['id_dana_sosial' => $id_dana_sosial]);

    $this->response(['message' => 'ini untuk hapus dana sosial', 'status' => true], 200);
  }

  public function hapus_dana_sosial_khusus_post()
  {
    $id_dana_sosial = $this->post('id_dana_sosial');

    $cek_data = $this->model->tampil_data_where('tb_dana_sosial_khusus', ['id_dana_sosial' => $id_dana_sosial])->result();
    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);

    $this->model->delete('tb_dana_sosial_khusus', ['id_dana_sosial' => $id_dana_sosial]);

    $this->response(['message' => 'ini untuk hapus dana sosial', 'status' => true], 200);
  }

  public function dana_sosial_ttd_post()
  {
    $id = $this->post('id');
    $cek_data = $this->model->tampil_data_where('tb_dana_sosial', ['id_dana_sosial' => $id])->result();

    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);

    $this->model->update('tb_dana_sosial', ['id_dana_sosial' => $id], ['status' => 1]);

    $this->response(['message' => 'ini untuk dana sosial ttd', 'status' => true], 200);
  }

  public function dana_sosial_khusus_ttd_post()
  {
    $id = $this->post('id');
    $cek_data = $this->model->tampil_data_where('tb_dana_sosial_khusus', ['id_dana_sosial' => $id])->result();

    if (count($cek_data) == 0) return $this->response(['message' => 'data tidak ditemukan', 'status' => false], 400);

    $this->model->update('tb_dana_sosial_khusus', ['id_dana_sosial' => $id], ['status' => 1]);

    $this->response(['message' => 'ini untuk dana sosial ttd', 'status' => true], 200);
  }

  public function edit_jabatan_post()
  {
    $jabatan = $this->post('jabatan');
    $jumlah = $this->post('jumlah');
    // change jumlah to int
    $jumlah = (int)$jumlah;

    $cek_data = $this->model->tampil_data_where('tb_jabatan', ['jabatan' => $jabatan])->result();
    for ($j = 0; $j < count($cek_data); $j++) {
      // delete folder
      $upload_dir = 'assets/jabatan/' . $cek_data[$j]->id . '/';
      if (is_dir($upload_dir)) {
        $files = glob($upload_dir . '*');
        foreach ($files as $file) {
          if (is_file($file)) {
            unlink($file);
          }
        }
        rmdir($upload_dir);
      }
    }
    $this->model->delete('tb_jabatan', ['jabatan' => $jabatan]);

    for ($i = 1; $i <= $jumlah; $i++) {
      $nama = $this->post('nama' . $i);
      $foto = $_FILES['image' . $i];

      $cek_last_ai = $this->model->cek_last_ai('tb_jabatan');

      $upload_dir = 'assets/jabatan/' . $cek_last_ai . '/';
      if (!is_dir($upload_dir)) {
        mkdir($upload_dir);
      }

      $path = $upload_dir . $foto['name'];
      move_uploaded_file($foto['tmp_name'], $path);

      $array = [
        'jabatan' => $jabatan,
        'nama' => $nama,
        'img_url' => $path
      ];

      $this->model->insert('tb_jabatan', $array);
    }


    $this->response(['message' => $cek_data, 'status' => true], 200);
  }



  public function jabatan_get()
  {
    $jabatan = $this->get('jabatan');
    $data = $this->model->tampil_data_where('tb_jabatan', ['jabatan' => $jabatan])->result();
    $this->response(['message' => 'ini untuk jabatan get', 'status' => true, 'data' => $data], 200);
  }

  public function pemasukan_get()
  {
    $bulan = $this->get('bulan');
    // get this year
    $tahun = date('Y');
    $sql = "SELECT * FROM tb_dana_sosial where tanggal like '$tahun-$bulan%' and bentuk = 'Pemasukan' and jenis = 'Uang'";
    $data = $this->model->custom_query($sql)->result();

    $jumlah = 0;
    foreach ($data as $d) {
      $jumlah += $d->jumlah;
    }

    $sql_pengeluaran = "SELECT * FROM tb_dana_sosial where tanggal like '$tahun-$bulan%' and bentuk = 'Pengeluaran' and jenis = 'Uang'";
    $data_pengeluaran = $this->model->custom_query($sql_pengeluaran)->result();

    $jumlah_pengeluaran = 0;
    foreach ($data_pengeluaran as $d) {
      $jumlah_pengeluaran += $d->jumlah;
    }

    $this->response(['message' => 'ini untuk pemasukan get', 'status' => true, 'data' => $data, 'jumlah' => $jumlah, 'jumlah_pengeluaran' => $jumlah_pengeluaran], 200);
  }

  public function pemasukan_khusus_get()
  {
    $bulan = $this->get('bulan');
    // get this year
    $tahun = date('Y');
    $sql = "SELECT * FROM tb_dana_sosial_khusus where tanggal like '$tahun-$bulan%' and bentuk = 'Pemasukan' and jenis = 'Uang'";
    $data = $this->model->custom_query($sql)->result();

    $jumlah = 0;
    foreach ($data as $d) {
      $jumlah += $d->jumlah;
    }

    $sql_pengeluaran = "SELECT * FROM tb_dana_sosial where tanggal like '$tahun-$bulan%' and bentuk = 'Pengeluaran' and jenis = 'Uang'";
    $data_pengeluaran = $this->model->custom_query($sql_pengeluaran)->result();

    $jumlah_pengeluaran = 0;
    foreach ($data_pengeluaran as $d) {
      $jumlah_pengeluaran += $d->jumlah;
    }

    $this->response(['message' => 'ini untuk pemasukan get', 'status' => true, 'data' => $data, 'jumlah' => $jumlah, 'jumlah_pengeluaran' => $jumlah_pengeluaran], 200);
  }

  public function jumlah_siswa_get()
  {
    $sql = "SELECT count(*) as jumlah FROM tb_siswa";
    $data = $this->model->custom_query($sql)->result();
    $this->response(['message' => 'ini untuk jumlah siswa get', 'status' => true, 'data' => $data[0]], 200);
  }


  public function donatur_post()
  {
    $nama_donatur = $this->post('nama_donatur');

    $cek_data = $this->model->tampil_data_where('tb_donatur', ['nama_donatur' => $nama_donatur])->result();

    if (count($cek_data) > 0) return $this->response(['message' => 'data sudah ada', 'status' => false], 400);

    $this->model->insert('tb_donatur', ['nama_donatur' => $nama_donatur]);

    $this->response(['message' => 'ini untuk tambah donatur', 'status' => true], 200);
  }

  public function donatur_get()
  {
    $data = $this->model->tampil_data_keseluruhan('tb_donatur')->result();
    $this->response(['message' => 'ini untuk donatur get', 'status' => true, 'data' => $data, 'jumlah' => count($data)], 200);
  }

  public function laporan_bulanan_get()
  {
    $bulan = $this->get('bulan');
    $tahun = $this->get('tahun');
    $bulan_num = $bulan;
    $data = $this->model->custom_query("SELECT * FROM tb_dana_sosial where tanggal like '$tahun-$bulan%' order by tanggal asc")->result();

    $bulan = $this->bulan_name($bulan);

    $pdf = new Pdf('P', 'mm', 'LEGAL');
    $pdf->AddPage();
    $pdf->SetFont('times');
    // $pdf->SetMargins(0, 0, 0);
    // make th center and
    $html = "<html>
    <head>
      <style>
        h1{
          text-align: center;
        }

        table, th, td {
          border: 1px solid black;
          border-collapse: collapse;
          
        }

        th {
          text-align: center;
        }
        
      </style>
    </head>
    <body>
      <h1>LAPORAN BULANAN</h1>
      <hr>
      <p><b>Bulan $bulan, Tahun  $tahun</b></p>
      <table>
        <tr>
          <th><b>Tanggal</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Jumlah / Satuan</b></th>
        </tr>
    ";

    foreach ($data as $d) {
      $tanggal = $this->date_formatter($d->tanggal);
      $html .= "<tr>
        <td>$tanggal</td>
        <td>$d->bentuk</td>
        <td>$d->jenis</td>
        <td>";
      if ($d->jenis == 'Uang') {
        $html .= "Rp. " . number_format($d->jumlah, 0, ',', '.');
      } else {
        $html .= "$d->jenis_barang $d->jumlah $d->satuan";
      }

      $html .= "</td>
      </tr>";
    }

    $html .= "</table>
    </body>
    </html>";

    $pdf->writeHTML($html);
    // save to pdf folder
    $pdf->Output(__DIR__ . '/../../assets/pdf/laporan_bulanan_' . $bulan_num . ',' . $tahun . '.pdf', 'F');
    // $pdf->Output('laporan_bulanan'.$bulan.', '.$tahun.'.pdf', 'I');
    return $this->response(['message' => 'ini untuk laporan bulanan', 'status' => true], 200);
  }

  function laporan_harian_get()
  {
    $tanggal = $this->get('tanggal');
    $hari = $this->get('hari');

    $data = $this->model->custom_query("SELECT * FROM tb_dana_sosial where tanggal = '$tanggal'")->result();
    $tanggal_format = $this->date_formatter($tanggal);

    $pdf = new Pdf('P', 'mm', 'LEGAL');
    $pdf->AddPage();
    $pdf->SetFont('times');
    // $pdf->SetMargins(0, 0, 0);
    // make th center and
    $html = "<html>
    <head>
      <style>
        h1{
          text-align: center;
        }

        table, th, td {
          border: 1px solid black;
          border-collapse: collapse;
          
        }

        th {
          text-align: center;
        }

        
      </style>
    </head>
    <body>
      <h1>LAPORAN HARIAN</h1>
      <hr>
      <p><b>Hari $hari, Tanggal  $tanggal_format</b></p>
      <table>
        <tr>
          <th><b>Tanggal</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Jumlah / Satuan</b></th>
        </tr>
    ";

    foreach ($data as $d) {
      $html .= "<tr>
        <td>$d->tanggal</td>
        <td>$d->bentuk</td>
        <td>$d->jenis</td>
        <td>";
      if ($d->jenis == 'Uang') {
        $html .= "Rp. " . number_format($d->jumlah, 0, ',', '.');
      } else {
        $html .= "$d->jenis_barang $d->jumlah $d->satuan";
      }

      $html .= "</td>
      </tr>";
    }

    $html .= "</table>
    </body>
    </html>";

    $pdf->writeHTML($html);

    // save to pdf folder
    $pdf->Output(__DIR__ . '/../../assets/pdf/laporan_harian_' . $hari . ',' . $tanggal . '.pdf', 'F');
    // $pdf->Output('laporan_harian'.$hari.', '.$tanggal_format.'.pdf', 'I');
    return $this->response(['message' => 'ini untuk laporan harian', 'status' => true], 200);
  }

  function laporan_tahunan_get()
  {
    $tahun = $this->get('tahun');
    $data = $this->model->custom_query("SELECT * FROM tb_dana_sosial where year(tanggal) = '$tahun'")->result();

    $pdf = new Pdf('P', 'mm', 'LEGAL');
    $pdf->AddPage();
    $pdf->SetFont('times');
    // $pdf->SetMargins(0, 0, 0);
    // make th center and
    $html = "<html>
    <head>
      <style>
        h1{
          text-align: center;
        }

        table, th, td {
          border: 1px solid black;
          border-collapse: collapse;
          
        }

        th {
          text-align: center;
        }

        
      </style>
    </head>
    <body>
      <h1>LAPORAN TAHUNAN</h1>
      <hr>
      <p><b>Tahun $tahun</b></p>
      <table>
        <tr>
          <th><b>Tanggal</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Bentuk</b></th>
          <th><b>Jumlah / Satuan</b></th>
        </tr>
    ";

    foreach ($data as $d) {
      $html .= "<tr>
        <td>$d->tanggal</td>
        <td>$d->bentuk</td>
        <td>$d->jenis</td>
        <td>";
      if ($d->jenis == 'Uang') {
        $html .= "Rp. " . number_format($d->jumlah, 0, ',', '.');
      } else {
        $html .= "$d->jenis_barang $d->jumlah $d->satuan";
      }

      $html .= "</td>
      </tr>";
    }

    $html .= "</table>
    </body>
    </html>";

    $pdf->writeHTML($html);

    // save to pdf folder
    $pdf->Output(__DIR__ . '/../../assets/pdf/laporan_tahunan_' . $tahun . '.pdf', 'F');
    // $pdf->Output('laporan_tahunan'.$tahun.'.pdf', 'I');
    return $this->response(['message' => 'ini untuk laporan tahunan', 'status' => true], 200);
  }


  function data_tahunan_get()
  {
    $data = $this->model->custom_query("SELECT * FROM tb_dana_sosial")->result();

    $new_data = [];

    // kumpulkan nilai pemasukkan dan pengeluaran berdasarkan tahun
    // make teh data like [["tahun" => 2021, "pemasukan" => 1000, "pengeluaran" => 2000], ["tahun" => 2022, "pemasukan" => 2000, "pengeluaran" => 3000], ...]

    foreach ($data as $d) {
      $year = date('Y', strtotime($d->tanggal));
      $pemasukan = 0;
      $pengeluaran = 0;

      if ($d->bentuk == 'Pemasukan' and $d->jenis == 'Uang') {
        $pemasukan += $d->jumlah;
      } else if ($d->bentuk == 'Pengeluaran' and $d->jenis == 'Uang') {
        $pengeluaran += $d->jumlah;
      }

      if (count($new_data) == 0) {
        array_push($new_data, ["tahun" => $year, "pemasukan" => $pemasukan, "pengeluaran" => $pengeluaran]);
      } else {
        if ($new_data[count($new_data) - 1]['tahun'] == $year) {
          $new_data[count($new_data) - 1]['pemasukan'] += $pemasukan;
          $new_data[count($new_data) - 1]['pengeluaran'] += $pengeluaran;
        } else {
          array_push($new_data, ["tahun" => $year, "pemasukan" => $pemasukan, "pengeluaran" => $pengeluaran]);
        }
      }
    }

    $this->response(['message' => 'ini untuk data tahunan', 'status' => true, 'data' => $new_data], 200);
  }

  function bulan_name($number)
  {
    $number = (int)$number;
    $arr = ['', 'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'];
    return $arr[$number];
  }

  function date_formatter($tgl)
  {
    // dd-mm-yyyy
    $arr = explode('-', $tgl);
    return $arr[2] . '-' . $arr[1] . '-' . $arr[0];
  }
}