<?php
class Keuangan_grafik_model extends CI_model {

  public function __construct()
  {
		parent::__construct();
  }

  public function rp_apbd_widget($thn, $opt=false)
  {
		$this->db->select('Akun, Nama_Akun');
		$this->db->join('keuangan_ref_rek1', 'keuangan_ref_rek1.id_keuangan_master = keuangan_master.id', 'left');

    if ($opt)
		{
      $this->db->where("Akun NOT LIKE '1.%'");
  		$this->db->where("Akun NOT LIKE '2.%'");
  		$this->db->where("Akun NOT LIKE '3.%'");
  		$this->db->where("Akun NOT LIKE '7.%'");
    }
    else
    {
      $this->db->where("Akun NOT LIKE '1.%'");
  		$this->db->where("Akun NOT LIKE '7.%'");
    }

		$this->db->where('tahun_anggaran', $thn);
		$this->db->order_by('Akun', 'asc');
		$this->db->group_by('Akun');
		$this->db->group_by('Nama_Akun');
		$data['jenis_pelaksanaan'] = $this->db->get('keuangan_master')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('jenis_pelaksanaan');
		$data['anggaran'] = $this->db->get('keuangan_ta_rab_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Nilai) AS realisasi');
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_pendapatan'] = $this->db->get('keuangan_ta_tbp_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->like('Kd_Rincian', '5.', 'after');
		$data['realisasi_belanja'] = $this->db->get('keuangan_ta_spp_rinci')->result_array();

    $this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->like('Kd_Rincian', '5.', 'after');
		$data['realisasi_belanja_um'] = $this->db->get('keuangan_ta_spp_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Nilai) AS realisasi');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->like('Kd_Rincian', '5.', 'after');
		$data['realisasi_belanja_spj'] = $this->db->get('keuangan_ta_spj_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Nilai) AS realisasi');
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_bunga'] = $this->db->get('keuangan_ta_mutasi')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 2) AS jenis_pelaksanaan, SUM(Kredit) AS realisasi');
		$this->db->group_by('jenis_pelaksanaan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_biaya'] = $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();

		return $data;
  }

  public function r_pd_widget($thn, $opt=false)
  {
		$this->db->select('keuangan_ref_rek3.Jenis, keuangan_ref_rek3.Nama_Jenis');
		$this->db->join('keuangan_ref_rek2', 'keuangan_ref_rek2.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->join('keuangan_ref_rek3', 'keuangan_ref_rek3.Kelompok = keuangan_ref_rek2.Kelompok', 'left');

    if ($opt)
		{
      $this->db->where("keuangan_ref_rek3.Jenis LIKE '4.%'");
    }
    else
    {
  		$this->db->where("keuangan_ref_rek3.Jenis NOT LIKE '1.%'");
  		$this->db->where("keuangan_ref_rek3.Jenis NOT LIKE '5.%'");
  		$this->db->where("keuangan_ref_rek3.Jenis NOT LIKE '6.%'");
  		$this->db->where("keuangan_ref_rek3.Jenis NOT LIKE '7.%'");
    }

    $this->db->where("keuangan_ref_rek3.Nama_Jenis NOT LIKE '%Hutang%'");
    $this->db->where("keuangan_ref_rek3.Nama_Jenis NOT LIKE '%Ekuitas SAL%'");

		$this->db->where('tahun_anggaran', $thn);
		$this->db->order_by('keuangan_ref_rek3.Jenis', 'asc');
		$data['jenis_pendapatan'] = $this->db->get('keuangan_master')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 6) AS jenis_pendapatan, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', '4.', 'after');
		$this->db->group_by('jenis_pendapatan');
		$this->db->where('Tahun', $thn);
		$data['anggaran'] = $this->db->get('keuangan_ta_rab_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 6) AS jenis_pendapatan, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', '4.', 'after');
		$this->db->group_by('jenis_pendapatan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_pendapatan'] = $this->db->get('keuangan_ta_tbp_rinci')->result_array();

		$this->db->select('LEFT(Kd_Rincian, 6) AS jenis_pendapatan, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', '4.', 'after');
		$this->db->group_by('jenis_pendapatan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_bunga'] = $this->db->get('keuangan_ta_mutasi')->result_array();

    $this->db->select('LEFT(Kd_Rincian, 6) AS jenis_pendapatan, SUM(Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', '4.', 'after');
		$this->db->group_by('jenis_pendapatan');
		$this->db->where('Tahun', $thn);
		$data['realisasi_jurnal'] = $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();

		return $data;
  }

  public function r_bd_widget($thn, $opt=false)
  {
    $this->db->select('Kd_Bid, Nama_Bidang');
    $this->db->join('keuangan_ta_bidang', 'keuangan_ta_bidang.id_keuangan_master = keuangan_master.id', 'left');
    if ($opt)
		{
      $this->db->where("Kd_Bid NOT LIKE '01%'");
      $this->db->where("Kd_Bid NOT LIKE '02%'");
      $this->db->where("Kd_Bid NOT LIKE '03%'");
    }
    else
    {
      $this->db->where("Kd_Bid NOT LIKE '01%'");
    }
    $this->db->where('Tahun', $thn);

    $this->db->order_by('Kd_Bid', 'asc');
    $data['jenis_belanja'] = $this->db->get('keuangan_master')->result_array();
    // Perlu ditambahkan baris berikut untuk memaksa menampilkan semua bidang di grafik keuangan
    // TODO: lihat apakah bisa diatasi langsung di script penampilan
    if (!$opt)
    {
	    array_unshift($data['jenis_belanja'] , array('Kd_Bid' => '03', 'Nama_Bidang' => 'ROW_SPACER'));
	    array_unshift($data['jenis_belanja'] , array('Kd_Bid' => '02', 'Nama_Bidang' => 'ROW_SPACER'));
    }

    $this->db->select('LEFT(Kd_Keg, 10) AS jenis_belanja, SUM(AnggaranStlhPAK) AS pagu');
    $this->db->like('Kd_Rincian', '5.', 'after');
    $this->db->group_by('jenis_belanja');
    $this->db->where('Tahun', $thn);
    $data['anggaran'] = $this->db->get('keuangan_ta_rab_rinci')->result_array();

    $this->db->select('LEFT(Kd_Keg, 10) AS jenis_belanja, SUM(Nilai) AS realisasi');
    $this->db->like('Kd_Rincian', '5.', 'after');
    $this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
    $this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
    $this->db->group_by('jenis_belanja');
    $data['realisasi_belanja'] = $this->db->get('keuangan_ta_spp_rinci')->result_array();

    $this->db->select('LEFT(Kd_Keg, 10) AS jenis_belanja, SUM(Nilai) AS realisasi');
    $this->db->like('Kd_Rincian', '5.', 'after');
    $this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
    $this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
    $this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
    $this->db->group_by('jenis_belanja');
    $data['realisasi_belanja_um'] = $this->db->get('keuangan_ta_spp_rinci')->result_array();

    $this->db->select('LEFT(Kd_Keg, 10) AS jenis_belanja, SUM(Nilai) AS realisasi');
    $this->db->like('Kd_Rincian', '5.', 'after');
    $this->db->where('Tahun', $thn);
    $this->db->group_by('jenis_belanja');
    $data['realisasi_belanja_spj'] = $this->db->get('keuangan_ta_spj_rinci')->result_array();

    $this->db->select('LEFT(Kd_Keg, 10) AS jenis_belanja, SUM(Nilai) AS realisasi');
    $this->db->like('Kd_Rincian', '5.', 'after');
    $this->db->group_by('jenis_belanja');
    $this->db->where('Tahun', $thn);
    $data['realisasi_bunga'] = $this->db->get('keuangan_ta_mutasi')->result_array();

		return $data;
  }

  /*
    lap_rp_apbd merupakan fungsi Akhir (Main) dari semua sub dan sub-sub fungsi :

    Sub fungsi Pendapatan
    1.1 sub-sub fungsi : Pagu Pendapatan
    1.2 sub-sub fungsi : Realisasi Pendapatan

    Sub fungsi Belanja
    2.1 sub-sub fungsi : Pagu Belanja
    2.2 sub-sub fungsi : Realisasi Belanja

    Sub fungsi Pembiayaan Masuk
    3.1 sub-sub fungsi : Pagu Pembiayaan Masuk
    3.1 sub-sub fungsi : Realisasi Pembiayaan Masuk

    Sub fungsi Pembiayaan Keluar
    4.1 sub-sub fungsi : Pagu Pembiayaan Keluar
    4.2 sub-sub fungsi : Realisasi Pembiayaan Keluar
  */

  //Query Laporan Pelaksanaan Realisasi
  public function lap_rp_apbd($thn, $smt1=false)
  {
		$this->db->select('Akun, Nama_Akun, id_keuangan_master');
		$this->db->join('keuangan_ref_rek1', 'keuangan_ref_rek1.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->where("Akun = '4.'");
		$this->db->where('tahun_anggaran', $thn);
		$data['pendapatan'] = $this->db->get('keuangan_master')->result_array();

		foreach ($data['pendapatan'] as $i => $p)
		{
		  $data['pendapatan'][$i]['anggaran'] = $this->pagu_akun($p['Akun'], $thn);
		  $data['pendapatan'][$i]['realisasi'] = $this->real_akun_pendapatan($p['Akun'], $thn, $smt1);
		  $data['pendapatan'][$i]['realisasi_bunga'] = $this->real_akun_pendapatan_bunga($p['Akun'], $thn, $smt1);
      $data['pendapatan'][$i]['realisasi_jurnal'] = $this->real_akun_pendapatan_jurnal($p['Akun'], $thn, $smt1);
		  $data['pendapatan'][$i]['sub_pendapatan'] = $this->get_subval_pendapatan($p['id_keuangan_master'], $p['Akun'], $thn, $smt1);
		}

		$this->db->select('Akun, Nama_Akun, id_keuangan_master');
		$this->db->join('keuangan_ref_rek1', 'keuangan_ref_rek1.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->where("Akun = '5.'");
		$this->db->where('tahun_anggaran', $thn);
		$data['belanja'] = $this->db->get('keuangan_master')->result_array();

		foreach ($data['belanja'] as $i => $p)
		{
		  $data['belanja'][$i]['anggaran'] = $this->pagu_akun($p['Akun'], $thn);
		  $data['belanja'][$i]['realisasi'] = $this->real_akun_belanja($p['Akun'], $thn, $smt1);
      $data['belanja'][$i]['realisasi_um'] = $this->real_akun_belanja_um($p['Akun'], $thn, $smt1);
		  $data['belanja'][$i]['realisasi_spj'] = $this->real_akun_belanja_spj($p['Akun'], $thn, $smt1);
		  $data['belanja'][$i]['realisasi_bunga'] = $this->real_akun_belanja_bunga($p['Akun'], $thn, $smt1);
		  $data['belanja'][$i]['sub_belanja'] = $this->get_subval_belanja($p['id_keuangan_master'], $p['Akun'], $thn, $smt1);
		}

    $this->db->select('Kd_Bid, Nama_Bidang, id_keuangan_master');
		$this->db->join('keuangan_ta_bidang', 'keuangan_ta_bidang.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->where('tahun_anggaran', $thn);
		$data['belanja_bidang'] = $this->db->get('keuangan_master')->result_array();

		foreach ($data['belanja_bidang'] as $i => $p)
		{
      $data['belanja_bidang'][$i]['anggaran'] = $this->pagu_akun_bidang($p['Kd_Bid'], $thn);
		  $data['belanja_bidang'][$i]['realisasi'] = $this->real_akun_belanja_bidang($p['Kd_Bid'], $thn, $smt1);
      $data['belanja_bidang'][$i]['realisasi_um'] = $this->real_akun_belanja_bidang_um($p['Kd_Bid'], $thn, $smt1);
		  $data['belanja_bidang'][$i]['realisasi_spj'] = $this->real_akun_belanja_spj_bidang($p['Kd_Bid'], $thn, $smt1);
		  $data['belanja_bidang'][$i]['realisasi_bunga'] = $this->real_akun_belanja_bunga_bidang($p['Kd_Bid'], $thn, $smt1);
      $data['belanja_bidang'][$i]['sub_belanja'] = $this->get_subval_belanja_bidang($p['id_keuangan_master'], $p['Kd_Bid'], $thn, $smt1);
		}

		$this->db->select('Akun, Nama_Akun, id_keuangan_master');
		$this->db->join('keuangan_ref_rek1', 'keuangan_ref_rek1.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->where("Akun = '6.'");
		$this->db->where('tahun_anggaran', $thn);
		$data['pembiayaan'] = $this->db->get('keuangan_master')->result_array();
		foreach ($data['pembiayaan'] as $i => $p)
		{
		  $data['pembiayaan'][$i]['anggaran'] = $this->pagu_akun($p['Akun'], $thn);
		  $data['pembiayaan'][$i]['realisasi'] = $this->real_akun_pembiayaan($p['Akun'], $thn, $smt1);
		  $data['pembiayaan'][$i]['sub_pembiayaan'] = $this->get_subval_pembiayaan($p['id_keuangan_master'], $p['Akun'], $thn, $smt1);
		}

		$this->db->select('Akun, Nama_Akun, id_keuangan_master');
		$this->db->join('keuangan_ref_rek1', 'keuangan_ref_rek1.id_keuangan_master = keuangan_master.id', 'left');
		$this->db->where("Akun = '6.'");
		$this->db->where('tahun_anggaran', $thn);
		$data['pembiayaan_keluar'] = $this->db->get('keuangan_master')->result_array();
		foreach ($data['pembiayaan_keluar'] as $i => $p)
		{
		  $data['pembiayaan_keluar'][$i]['anggaran'] = $this->pagu_akun($p['Akun'], $thn);
		  $data['pembiayaan_keluar'][$i]['realisasi'] = $this->real_akun_pembiayaan_keluar($p['Akun'], $thn, $smt1);
		  $data['pembiayaan_keluar'][$i]['sub_pembiayaan_keluar'] = $this->get_subval_pembiayaan_keluar($p['id_keuangan_master'], $p['Akun'], $thn, $smt1);
		}
		return $data;
  }

  private function pagu_akun($akun, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_akun_bidang($akun, $thn)
  {
    $this->db->select('LEFT(Kd_Keg, 10) AS Akun, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function real_akun_pendapatan($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_tbp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_tbp', 'keuangan_ta_tbp.No_Bukti = keuangan_ta_tbp_rinci.No_Bukti', 'left');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_tbp_rinci')->result_array();
  }

  private function real_akun_pendapatan_bunga($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_akun_pendapatan_jurnal($akun, $thn, $smt1=false)
  {
    $this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(keuangan_ta_jurnal_umum_rinci.Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_jurnal_umum_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_jurnal_umum', 'keuangan_ta_jurnal_umum.NoBukti = keuangan_ta_jurnal_umum_rinci.NoBukti', 'left');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();
  }

  private function real_akun_belanja($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_akun_belanja_um($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_akun_belanja_spj($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_spj_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spj', 'keuangan_ta_spj.No_SPJ = keuangan_ta_spj_rinci.No_SPJ', 'left');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spj_rinci')->result_array();
  }

  private function real_akun_belanja_bunga($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_akun_belanja_bidang($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Keg, 10) AS Akun, SUM(Nilai) AS realisasi');
    $this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_akun_belanja_bidang_um($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Keg, 10) AS Akun, SUM(Nilai) AS realisasi');
    $this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_akun_belanja_spj_bidang($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Keg, 10) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->where('keuangan_ta_spj_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spj', 'keuangan_ta_spj.No_SPJ = keuangan_ta_spj_rinci.No_SPJ', 'left');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spj_rinci')->result_array();
  }

  private function real_akun_belanja_bunga_bidang($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Keg, 10) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_akun_pembiayaan($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_akun_pembiayaan_keluar($akun, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 2) AS Akun, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $akun, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Akun');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function get_subval_pendapatan($id_keuangan_master, $akun, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Nama_Kelompok');
		$this->db->where('Akun', $akun);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek2')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_subval_pendapatan($d['Kelompok'], $thn);
		  $data[$i]['realisasi'] = $this->real_subval_pendapatan($d['Kelompok'], $thn, $smt1);
		  $data[$i]['realisasi_bunga'] = $this->real_subval_bunga($d['Kelompok'], $thn, $smt1);
      $data[$i]['realisasi_jurnal'] = $this->real_subval_jurnal($d['Kelompok'], $thn, $smt1);
		  $data[$i]['sub_pendapatan2'] = $this->sub_pendapatan2($id_keuangan_master, $d['Kelompok'], $thn, $smt1);
		}
		return $data;
  }

  private function get_subval_belanja($id_keuangan_master, $akun, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Nama_Kelompok');
		$this->db->where('Akun', $akun);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek2')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_subval_belanja($d['Kelompok'], $thn);
		  $data[$i]['realisasi'] = $this->real_subval_belanja($d['Kelompok'], $thn, $smt1);
      $data[$i]['realisasi_um'] = $this->real_subval_belanja_um($d['Kelompok'], $thn, $smt1);
		  $data[$i]['realisasi_spj'] = $this->real_subval_belanja_spj($d['Kelompok'], $thn, $smt1);
		  $data[$i]['realisasi_bunga'] = $this->real_subval_belanja_bunga($d['Kelompok'], $thn, $smt1);
		  $data[$i]['sub_belanja2'] = $this->sub_belanja2($id_keuangan_master, $d['Kelompok'], $thn, $smt1);
		}
		return $data;
  }

  private function get_subval_belanja_bidang($id_keuangan_master, $akun, $thn, $smt1=false)
  {
		$this->db->select('Kd_Keg, Nama_Kegiatan');
    $this->db->like('LEFT(Kd_Keg, 10)', $akun, 'after');
		$this->db->where('id_keuangan_master', $id_keuangan_master);
    $this->db->order_by('Kd_Keg');
		$data = $this->db->get('keuangan_ta_kegiatan')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_subval_belanja_bidang($d['Kd_Keg'], $thn);
		  $data[$i]['realisasi'] = $this->real_subval_belanja_bidang($d['Kd_Keg'], $thn, $smt1);
      $data[$i]['realisasi_um'] = $this->real_subval_belanja_bidang_um($d['Kd_Keg'], $thn, $smt1);
		  $data[$i]['realisasi_spj'] = $this->real_subval_belanja_spj_bidang($d['Kd_Keg'], $thn, $smt1);
		  $data[$i]['realisasi_bunga'] = $this->real_subval_belanja_bunga_bidang($d['Kd_Keg'], $thn, $smt1);
		}
		return $data;
  }

  private function get_subval_pembiayaan($id_keuangan_master, $akun, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Nama_Kelompok');
		$this->db->where('Akun', $akun);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$this->db->where('Kelompok', '6.1.');
		$data = $this->db->get('keuangan_ref_rek2')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_subval_pembiayaan($d['Kelompok'], $thn);
		  $data[$i]['realisasi'] = $this->real_subval_pembiayaan($d['Kelompok'], $thn, $smt1);
		  $data[$i]['sub_pembiayaan2'] = $this->sub_pembiayaan2($id_keuangan_master, $d['Kelompok'], $thn, $smt1);
		}
		return $data;
  }

  private function get_subval_pembiayaan_keluar($id_keuangan_master, $akun, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Nama_Kelompok');
		$this->db->where('Akun', $akun);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$this->db->where('Kelompok', '6.2.');
		$data = $this->db->get('keuangan_ref_rek2')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_subval_pembiayaan_keluar($d['Kelompok'], $thn);
		  $data[$i]['realisasi'] = $this->real_subval_pembiayaan_keluar($d['Kelompok'], $thn, $smt1);
		  $data[$i]['sub_pembiayaan_keluar2'] = $this->sub_pembiayaan_keluar2($id_keuangan_master, $d['Kelompok'], $thn, $smt1);
		}
		return $data;
  }

  private function pagu_subval_pendapatan($kelompok, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_subval_belanja($kelompok, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_subval_belanja_bidang($kelompok, $thn)
  {
		$this->db->select('Kd_Keg AS Kelompok, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Keg', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Kd_Keg');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_subval_pembiayaan($kelompok, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_subval_pembiayaan_keluar($kelompok, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function real_subval_pendapatan($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_tbp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_tbp', 'keuangan_ta_tbp.No_Bukti = keuangan_ta_tbp_rinci.No_Bukti', 'left');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_tbp_rinci')->result_array();
  }

  private function real_subval_bunga($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_subval_jurnal($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(keuangan_ta_jurnal_umum_rinci.Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_jurnal_umum_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_jurnal_umum', 'keuangan_ta_jurnal_umum.NoBukti = keuangan_ta_jurnal_umum_rinci.NoBukti', 'left');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();
  }

  private function real_subval_belanja($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_subval_belanja_um($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_subval_belanja_bidang($kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kd_Keg AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Keg', $kelompok, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_subval_belanja_bidang_um($kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kd_Keg AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Keg', $kelompok, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_subval_belanja_spj($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_spj_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spj', 'keuangan_ta_spj.No_SPJ = keuangan_ta_spj_rinci.No_SPJ', 'left');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spj_rinci')->result_array();
  }

  private function real_subval_belanja_spj_bidang($kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kd_Keg AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Keg', $kelompok, 'after');
		$this->db->where('keuangan_ta_spj_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spj', 'keuangan_ta_spj.No_SPJ = keuangan_ta_spj_rinci.No_SPJ', 'left');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spj_rinci')->result_array();
  }

  private function real_subval_belanja_bunga($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_subval_belanja_bunga_bidang($kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kd_Keg AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Keg', $kelompok, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_subval_pembiayaan($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(keuangan_ta_jurnal_umum_rinci.Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_jurnal_umum_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_jurnal_umum', 'keuangan_ta_jurnal_umum.NoBukti = keuangan_ta_jurnal_umum_rinci.NoBukti', 'left');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();
  }

  private function real_subval_pembiayaan_keluar($kelompok, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 4) AS Kelompok, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $kelompok, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kelompok');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function sub_pendapatan2($id_keuangan_master, $kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Jenis, Nama_Jenis');
		$this->db->where('Kelompok', $kelompok);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek3')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_pendapatan2($d['Jenis'], $thn);
		  $data[$i]['realisasi'] = $this->real_pendapatan2($d['Jenis'], $thn, $smt1);
		  $data[$i]['realisasi_bunga'] = $this->real_pendapatan_bunga2($d['Jenis'], $thn, $smt1);
      $data[$i]['realisasi_jurnal'] = $this->real_pendapatan_jurnal2($d['Jenis'], $thn, $smt1);
		}
		return $data;
  }

  private function sub_belanja2($id_keuangan_master,$kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Jenis, Nama_Jenis');
		$this->db->where('Kelompok', $kelompok);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek3')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_belanja2($d['Jenis'], $thn);
		  $data[$i]['realisasi'] = $this->real_belanja2($d['Jenis'], $thn, $smt1);
      $data[$i]['realisasi_um'] = $this->real_belanja2_um($d['Jenis'], $thn, $smt1);
		  $data[$i]['realisasi_spj'] = $this->real_belanja2_spj($d['Jenis'], $thn, $smt1);
		  $data[$i]['realisasi_bunga'] = $this->real_belanja2_bunga($d['Jenis'], $thn, $smt1);
		}
		return $data;
  }

  private function sub_pembiayaan2($id_keuangan_master,$kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Jenis, Nama_Jenis');
		$this->db->where('Kelompok', '6.1.');
		$this->db->where('Kelompok', $kelompok);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek3')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_pembiayaan2($d['Jenis'], $thn);
		  $data[$i]['realisasi'] = $this->real_pembiayaan2($d['Jenis'], $thn, $smt1);
		}
		return $data;
  }

  private function sub_pembiayaan_keluar2($id_keuangan_master,$kelompok, $thn, $smt1=false)
  {
		$this->db->select('Kelompok, Jenis, Nama_Jenis');
		$this->db->where('Kelompok', '6.2.');
		$this->db->where('Kelompok', $kelompok);
		$this->db->where('id_keuangan_master', $id_keuangan_master);
		$data = $this->db->get('keuangan_ref_rek3')->result_array();
		foreach ($data as $i => $d)
		{
		  $data[$i]['anggaran'] = $this->pagu_pembiayaan_keluar2($d['Jenis'], $thn);
		  $data[$i]['realisasi'] = $this->real_pembiayaan_keluar2($d['Jenis'], $thn, $smt1);
		}
		return $data;
  }

  private function pagu_pendapatan2($jenis, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_belanja2($jenis, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_pembiayaan2($jenis, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function pagu_pembiayaan_keluar2($jenis, $thn)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(AnggaranStlhPAK) AS pagu');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_rab_rinci')->result_array();
  }

  private function real_pendapatan2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_tbp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_tbp', 'keuangan_ta_tbp.No_Bukti = keuangan_ta_tbp_rinci.No_Bukti', 'left');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_tbp.Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_tbp_rinci')->result_array();
  }

  private function real_pendapatan_bunga2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_pendapatan_jurnal2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(keuangan_ta_jurnal_umum_rinci.Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_jurnal_umum_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_jurnal_umum', 'keuangan_ta_jurnal_umum.NoBukti = keuangan_ta_jurnal_umum_rinci.NoBukti', 'left');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kd_Rincian');
		return $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();
  }

  private function real_belanja2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		//$this->db->where('keuangan_ta_spp.Jn_SPP', 'LS');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_belanja2_um($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		$this->db->where('keuangan_ta_spp.Jn_SPP', 'UM');
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function real_belanja2_spj($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->join('keuangan_ta_spj', 'keuangan_ta_spj.No_SPJ = keuangan_ta_spj_rinci.No_SPJ', 'left');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_spj_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('keuangan_ta_spj.Tgl_SPJ >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spj.Tgl_SPJ <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_spj_rinci')->result_array();
  }

  private function real_belanja2_bunga($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('Tahun', $thn);
		if ($smt1)
		{
			$this->db->where('Tgl_Bukti >=', '01/01/$thn 00:00:00');
			$this->db->where('Tgl_Bukti <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_mutasi')->result_array();
  }

  private function real_pembiayaan2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(keuangan_ta_jurnal_umum_rinci.Kredit) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_jurnal_umum_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_jurnal_umum', 'keuangan_ta_jurnal_umum.NoBukti = keuangan_ta_jurnal_umum_rinci.NoBukti', 'left');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_jurnal_umum.Tanggal <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Kd_Rincian');
		return $this->db->get('keuangan_ta_jurnal_umum_rinci')->result_array();
  }

  private function real_pembiayaan_keluar2($jenis, $thn, $smt1=false)
  {
		$this->db->select('LEFT(Kd_Rincian, 6) AS Jenis, SUM(Nilai) AS realisasi');
		$this->db->like('Kd_Rincian', $jenis, 'after');
		$this->db->where('keuangan_ta_spp_rinci.Tahun', $thn);
		if ($smt1)
		{
			$this->db->join('keuangan_ta_spp', 'keuangan_ta_spp.No_SPP = keuangan_ta_spp_rinci.No_SPP', 'left');
			$this->db->where('keuangan_ta_spp.Tgl_SPP >=', '01/01/$thn 00:00:00');
			$this->db->where('keuangan_ta_spp.Tgl_SPP <=', '06/31/$thn 00:00:00');
		}
		$this->db->group_by('Jenis');
		return $this->db->get('keuangan_ta_spp_rinci')->result_array();
  }

  private function data_widget_pendapatan($tahun, $opt=false)
  {
    if ($opt)
		{
      $raw_data = $this->r_pd_widget($tahun, $opt=true);
  		$res_pendapatan = array();
  		$tmp_pendapatan = array();
  		foreach ($raw_data['jenis_pendapatan'] as $r)
  		{
  		  $tmp_pendapatan[$r['Jenis']]['nama'] = $r['Nama_Jenis'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_pendapatan'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }
    else
    {
      $raw_data = $this->r_pd_widget($tahun, $opt=false);
  		$res_pendapatan = array();
  		$tmp_pendapatan = array();
  		foreach ($raw_data['jenis_pendapatan'] as $r)
  		{
  		  $tmp_pendapatan[$r['Jenis']]['nama'] = $r['Nama_Jenis'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_pendapatan'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['realisasi_pendapatan'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['realisasi_bunga'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_jurnal'] as $r)
  		{
  		  $tmp_pendapatan[$r['jenis_pendapatan']]['realisasi_jurnal'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }

		foreach ($tmp_pendapatan as $key => $value)
		{
		  array_push($res_pendapatan, $value);
		}

		return $res_pendapatan;
  }

  private function data_widget_belanja($tahun, $opt=false )
  {
    if ($opt)
		{
      $raw_data = $this->r_bd_widget($tahun, $opt=true);
  		$res_belanja = array();
  		$tmp_belanja = array();
      foreach ($raw_data['jenis_belanja'] as $r)
  		{
  		  $tmp_belanja[$r['Kd_Bid']]['nama'] = $r['Nama_Bidang'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_belanja_um'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja_spj'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi1'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_biaya'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }
    else
    {
      $raw_data = $this->r_bd_widget($tahun, $opt=false);
  		$res_belanja = array();
  		$tmp_belanja = array();
      foreach ($raw_data['jenis_belanja'] as $r)
  		{
  		  $tmp_belanja[$r['Kd_Bid']]['nama'] = $r['Nama_Bidang'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi_belanja'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_belanja_um'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi_belanja_um'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja_spj'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi_belanja_spj'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi_bunga'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_biaya'] as $r)
  		{
  		  $tmp_belanja[$r['jenis_belanja']]['realisasi_biaya'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }

    foreach ($tmp_belanja as $key => $value)
    {
      array_push($res_belanja, $value);
    }

		return $res_belanja;
  }

  private function data_widget_pelaksanaan($tahun, $opt=false)
  {
    if ($opt)
		{
      $raw_data = $this->rp_apbd_widget($tahun, $opt=true);
  		$res_pelaksanaan = array();
  		$tmp_pelaksanaan = array();
  		foreach ($raw_data['jenis_pelaksanaan'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['Akun']]['nama'] = $r['Nama_Akun'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_pendapatan'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_belanja_um'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja_spj'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi1'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_jurnal'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi1'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_biaya'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }
    else
    {
      $raw_data = $this->rp_apbd_widget($tahun, $opt=false);
  		$res_pelaksanaan = array();
  		$tmp_pelaksanaan = array();

  		foreach ($raw_data['jenis_pelaksanaan'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['Akun']]['nama'] = $r['Nama_Akun'];
  		}

  		foreach ($raw_data['anggaran'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['anggaran'] = ($r['pagu'] ? $r['pagu'] : 0);
  		}

  		foreach ($raw_data['realisasi_pendapatan'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_pendapatan'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_belanja'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_belanja_um'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_belanja_um'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_belanja_spj'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_belanja_spj'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_bunga'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_bunga'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

      foreach ($raw_data['realisasi_jurnal'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_jurnal'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}

  		foreach ($raw_data['realisasi_biaya'] as $r)
  		{
  		  $tmp_pelaksanaan[$r['jenis_pelaksanaan']]['realisasi_biaya'] = ($r['realisasi'] ? $r['realisasi'] : 0);
  		}
    }

		foreach ($tmp_pelaksanaan as $key => $value)
		{
		  array_push($res_pelaksanaan, $value);
		}

		return $res_pelaksanaan;
  }

  public function widget_keuangan()
  {
		$data = $this->keuangan_model->list_tahun_anggaran();

		foreach ($data as $tahun)
		{
		  $res[$tahun]['res_pendapatan'] = $this->data_widget_pendapatan($tahun, $opt=true);
		  $res[$tahun]['res_belanja'] = $this->data_widget_belanja($tahun, $opt=true);
		  $res[$tahun]['res_pelaksanaan'] = $this->data_widget_pelaksanaan($tahun, $opt=true);
		}

		$result = array(
		  //Encode ke JSON
		  'data' => json_encode($res),
		  'tahun' => $this->keuangan_model->list_tahun_anggaran(),
		  //Cari tahun anggaran terbaru (terbesar secara value)
		  'tahun_terbaru' => $this->keuangan_model->list_tahun_anggaran()[0]
		);

		return $result;
  }

  private function data_keuangan_tema($tahun)
  {
		$data['res_pelaksanaan'] = $this->data_widget_pelaksanaan($tahun, $opt=false);
		$data['res_pelaksanaan']['laporan'] = 'APBDes '. $tahun . ' Pelaksanaan';
		$data['res_pendapatan'] = $this->data_widget_pendapatan($tahun, $opt=false);
		$data['res_pendapatan']['laporan'] = 'APBDes '. $tahun . ' Pendapatan';
		$data['res_belanja'] = $this->data_widget_belanja($tahun, $opt=false);
		$data['res_belanja']['laporan'] = 'APBDes '. $tahun . ' Pembelanjaan';

		return $data;
  }

  public function grafik_keuangan_tema($tahun = NULL)
  {
		if (is_null($tahun)) $tahun = date('Y');
		$thn = $this->keuangan_model->list_tahun_anggaran();
		if (empty($thn))
		{
		  return null;
		}

		if (!in_array($tahun, $thn))
		{
		  $tahun = $thn[0];
		}
		$raw_data = $this->data_keuangan_tema($tahun);
		foreach ($raw_data as $keys => $raws)
		{
		  foreach ($raws as $key => $raw)
		  {
			if ($key == 'laporan')
			{
			  $result['data_widget'][$keys]['laporan'] = $raw;
			  continue;
			}

      $data['judul'] = $raw['nama'];
			$data['anggaran'] = $raw['anggaran'];
			$data['realisasi'] = $raw['realisasi']+$raw['realisasi_pendapatan']+($raw['realisasi_belanja']-$raw['realisasi_belanja_um'])+$raw['realisasi_belanja_spj']+$raw['realisasi_bunga']+$raw['realisasi_jurnal']+$raw['realisasi_biaya'];

			if ($data['anggaran'] != 0 && $data['realisasi'] != 0)
			{
			  $data['persen'] = $data['realisasi'] / $data['anggaran'] * 100;
			}
			elseif ($data['realisasi'] != 0)
			{
			  $data['persen'] = 100;
			}
			else
			{
			  $data['persen'] = 0;
			}
			$data['persen'] = round($data['persen'], 2);

			$result['data_widget'][$keys][] = $data;
		  }
		}
		$result['tahun'] = $tahun;
		return $result;
  }
}
