Read 153 times | Created 2014-04-25 02:20:53 | Updated 2014-04-25 02:21:31 | | |

 

<?php
/*
USE `test`;

DROP TABLE IF EXISTS `kategori_neraca`;
CREATE TABLE IF NOT EXISTS `kategori_neraca` (
  id_kategori_neraca int(11) unsigned not null auto_increment,
  nama_kategori_neraca varchar(100) not null,
  jenis_neraca varchar(10) not null,
  username_pegawai varchar(50) not null,
  PRIMARY KEY (id_kategori_neraca)
) ENGINE=MyISAM;

INSERT INTO `kategori_neraca`
VALUES
(1,'Aset Lancar','AKTIVA','agus'),
(2,'Aset Tidak Lancar','AKTIVA','agus'),
(3,'Aset Lain-lain','AKTIVA','agus');

DROP TABLE IF EXISTS `detail_neraca`;
CREATE TABLE IF NOT EXISTS `detail_neraca` (
  id_detail_neraca int(11) unsigned not null auto_increment,
  id_kategori_neraca int(11) unsigned not null,
  nama_neraca varchar(100) not null,
  nilai_neraca DOUBLE,
  PRIMARY KEY (id_detail_neraca)
) ENGINE=MyISAM;

INSERT INTO `detail_neraca`
VALUES
(1,1,'Biaya dibayar dimuka',0),
(2,1,'Biaya dibayar dibelakang',368000000.0),
(3,1,'Efek dimiliki dan jatuh tempo',4000000.0),
(4,2,'Kas dan Setara Kas',4000000.0),
(5,2,'Pajak dibayar di muka',10000000.0),
(6,2,'Kas',100000000.0);

DROP TABLE IF EXISTS `subkategori_neraca`;
CREATE TABLE IF NOT EXISTS `subkategori_neraca` (
  id_subkategori_neraca int(11) unsigned not null auto_increment,
  id_detail_neraca int(11) unsigned not null,
  nama_subkategori varchar(100) not null,
  nilai_neraca DOUBLE,
  PRIMARY KEY (id_subkategori_neraca)
) ENGINE=MyISAM;

INSERT INTO `subkategori_neraca`
VALUES
(1,1,'Piutang Satu',90000.0),
(2,1,'Piutang Dua',190000.0),
(3,1,'Piutang Tiga',987000000.0),
(4,1,'Piutang Empat',98043000.0),
(5,1,'Piutang Lima',9000000.0);
*/
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='test';
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Neraca</title>
    <style>
      .h1 {background-color:#33e;font-weight:bold;}
      .h2 {background-color:#aaf;font-weight:bold;}
      .h0 {color:#fff;background-color:#336;font-weight:bold;}
    </style>
  </head>
  <body>
    <table border='1'>
      <thead>
        <tr class='h0'>
          <th colspan='2'>ASET</th>
          <th>Aksi</th>
        </tr>
      </thead>
      <tbody>
      <?php
      $kategori=$detail='';
      $h1=$h2=$h3=0;
      $sql="
            SELECT
               k.id_kategori_neraca,
               k.nama_kategori_neraca,
               d.id_detail_neraca,
               d.nama_neraca,
               s.nama_subkategori,
               IF(ISNULL(s.nilai_neraca),d.nilai_neraca,s.nilai_neraca) AS nilai_neraca
            FROM kategori_neraca k
            LEFT JOIN detail_neraca d USING(id_kategori_neraca)
            LEFT JOIN subkategori_neraca s USING(id_detail_neraca)
            WHERE
             k.username_pegawai='agus'
             AND k.jenis_neraca='AKTIVA'
            ORDER BY k.id_kategori_neraca,d.id_detail_neraca,s.id_subkategori_neraca";          
      $result=$db->query($sql);
      while($data=$result->fetch_object())
      {
        if($kategori!=$data->nama_kategori_neraca){
          $kategori=$data->nama_kategori_neraca;
          echo "<tr class='h1'><td colspan='3'>".(++$h1).". $kategori</td></tr>n";
          $h2=0;
        }
        if($detail!=$data->nama_neraca){
          $detail=$data->nama_neraca;
          echo (empty($data->nama_subkategori)?(empty($data->nilai_neraca)?"":"<tr class='h2'><td>".($h1).".".(++$h2).".$detail </td>"):"<tr class='h2'><td colspan='3'>".($h1).".".(++$h2).".$detail</td></tr>n");
          $h3=0;
        }
        echo (empty($data->nama_subkategori)?"":"<tr><td width='250'>{$h1}.{$h2}.".(++$h3).". {$data->nama_subkategori}</td>");
        echo (empty($data->nilai_neraca)?"":"<td align='right'>".number_format($data->nilai_neraca,2)."</td><td align='center'>edit|del</td></tr>n");
      }
      $result->close();
      ?>
      </tbody>
    </table>
    <hr/>
    <table border='1'>
      <thead>
        <tr class='h0'>
          <th colspan='4'>ASET</th>
          <th>Aksi</th>
        </tr>
      </thead>
      <tbody>
      <?php
      $sql="
            SELECT
               k.id_kategori_neraca,
               k.nama_kategori_neraca,
               d.id_detail_neraca,
               d.nama_neraca,
               ( SELECT COUNT(1)
                 FROM detail_neraca
                 LEFT JOIN subkategori_neraca USING(id_detail_neraca)
                 WHERE id_kategori_neraca=k.id_kategori_neraca
               ) AS jml_detail,
               s.nama_subkategori,
               IF(ISNULL(s.nilai_neraca),d.nilai_neraca,s.nilai_neraca) AS nilai_neraca,
               ( SELECT COUNT(1)
                 FROM subkategori_neraca
                 WHERE id_detail_neraca=d.id_detail_neraca
               ) AS jml_subkategori
            FROM kategori_neraca k
            LEFT JOIN detail_neraca d USING(id_kategori_neraca)
            LEFT JOIN subkategori_neraca s USING(id_detail_neraca)
            WHERE
             k.username_pegawai='agus'
             AND k.jenis_neraca='AKTIVA'
            ORDER BY k.id_kategori_neraca,d.id_detail_neraca,s.id_subkategori_neraca";
      $result=$db->query($sql);
      $jml_detail=0;
      $jml_sub=0;
      $h1=0;
      while($data=$result->fetch_object())
      {
        echo "<tr valign='top'>";
        if($jml_detail<=1){
          $jml_detail=$data->jml_detail;
          echo "<td rowspan='{$jml_detail}'".($data->jml_detail==0?" colspan='3'":'').">"
              .(++$h1).".{$data->nama_kategori_neraca}</td>";        
          if($jml_sub<=1){
            $h2=1;
            $h3=0;
            $jml_sub=$data->jml_subkategori;
            echo ($data->jml_detail==0?"":"<td rowspan='{$jml_sub}'".($data->jml_subkategori==0?" colspan='2'":'').">"
                ."{$h1}.{$h2}.{$data->nama_neraca}</td>")
                .($data->jml_subkategori==0?'':"<td>{$h1}.{$h2}.".(++$h3).".{$data->nama_subkategori}</td>")
                ."<td align='right'>".number_format($data->nilai_neraca,2)."</td>";
          }else{
            $jml_sub--;
          }
        }else{
          $jml_detail--;
          if($jml_sub<=1){
            $h3=0;
            $jml_sub=$data->jml_subkategori;
            echo "<td rowspan='{$jml_sub}'".($data->jml_subkategori==0?" colspan='2'":'').">{$h1}.".(++$h2).".{$data->nama_neraca}</td>"
               ."<td align='right'>".number_format($data->nilai_neraca,2)."</td>";      
          }else{
            $jml_sub--;
            echo "<td>{$h1}.{$h2}.".(++$h3).".{$data->nama_subkategori}</td>
                 <td align='right'>".number_format($data->nilai_neraca,2)."</td>";
            
          }
        }     
        echo "<td align='center'>edit|del</td></tr>n";
      }
      $result->close();
      $db->close();
      ?>
      </tbody>
    </table>
  </body>
</html>