Read 214 times | Created 2013-01-15 03:26:56 | Updated 2013-01-15 03:51:28 | | |

 

<?php
/*******************************
// PIVOT TABLE
// created by  : Cahya DSN
// create date : 2012-09-30
// update date : 2012-10-03
*******************************/
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Pivot Table Stock</title>
    <style type="text/css">
      * {padding:0;margin:0}
      .barang {width:100px;color:#900;}
      .merk {width:100px;color:#900;}
      .stok {width:50px;color:#900;}
      h3 {margin:10px 10px 0px;color:#090;}
      table {margin-left:10px;border:solid 1px #333;}
      th {background-color:#eee;border:solid 1px #333;}
      td {text-align:center;border:solid 1px #333;}
      td.list_barang {text-align:left;font-weight:bold;color:#009;padding-left:5px;}
      td._in {background-color:#ffc;}
    </style>
  </head>
  <body>
<?php
/*
-- Sample Data SQL 
-- use test;
DROP TABLE IF EXISTS `tbl_stock`;
CREATE TABLE IF NOT EXISTS `tbl_stock` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `barang` varchar(30) NOT NULL,
  `merk` varchar(30) NOT NULL,
  `_in` int(11) NOT NULL DEFAULT '0',
  `_out` int(11) NOT NULL DEFAULT '0',
  `stok` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
INSERT INTO `tbl_stock`(`barang`,`merk`,`_in`,`_out`,`stok`) 
VALUES("Mouse","Acer",12,2,10),("Keyboard","Logitech",10,0,10),("Mouse","Microsoft",5,0,5),("Printer","Canon IP 145",10,7,3);
*/
//-- konfigurasi koneksi database
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='test';
//-- connect to database
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
//-- query data ke database
$sql="SELECT barang,merk,_in,_out,stok FROM tbl_stock";
//-- inisialisasi variabel penampung
$record=array();
$data=array();
$barang=array();
$merk=array();
//-- menjalankan query ke database
if($result=$db->query($sql)){
  //-- ambil data dan masukkan ke variabel penampung
  while($row=$result->fetch_object()){
    //-- copy data record ke variable $record utk table tanpa pivot  
    $record[]=$row;
    //-- penyiapan data untuk pivot table
    if(isset($data[$row->barang])){
          if(isset($data[$row->barang][$row->merk])){
                  $data[$row->barang][$row->merk][0]+=$row->_in;
                  $data[$row->barang][$row->merk][1]+=$row->_out;
                  $data[$row->barang][$row->merk][2]+=$row->stok;
          }else{
                  $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
                  $merk[]=$row->merk;
          }
    }else{
          $data[$row->barang]=array();
          $barang[]=$row->barang;
          $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
          $merk[]=$row->merk;
    }
  }
  $result->close();
}
$db->close();
$merk=array_unique($merk);
//-- cetak dalam bentuk table
//======= Table Data sebelum di Pivot  ========
echo "<h3>SEBELUM</h3>";
echo "<table>n"
    ."<tr>"
    ."<th class="barang">Barang</th>"
    ."<th class="barang">Merk</th>"
    ."<th class="stok">In</th>"
    ."<th class="stok">Out</th>"
    ."<th class="stok">Stok</th>"
    ."</tr>";
for($i=0;$i<count($record);$i++){
  echo "<tr>"
      ."<td class="list_barang">".$record[$i]->barang."</td>"
      ."<td class="list_barang">".$record[$i]->merk."</td>"
      ."<td>".$record[$i]->_in."</td>"
      ."<td>".$record[$i]->_out."</td>"
      ."<td>".$record[$i]->stok."</td>"
      ."</tr>n";
}
echo "</table>n";
//======= Table Data sesudah di Pivot  ========
echo "<h3>SESUDAH</h3>";
echo "<table>n"
    ."<thead>n"
    ."<tr><th rowspan="2" class="barang">Barang</th>";
for($i=0;$i<count($merk);$i++){
        echo "<th colspan="2" class="merk">".$merk[$i]."</th>";
}
echo "<th rowspan="2" class="stok">Stok</th></tr>n<tr>n";
for($i=0;$i<count($merk);$i++){
        echo "<th>in</th><th>out</th>";
}
echo "</tr>n</thead>n<tbody>n";
for($i=0;$i<count($barang);$i++){
  echo "<tr>"
      ."<td class="list_barang">".$barang[$i]."</td>";
  $stok=0;    
  for($j=0;$j<count($merk);$j++){
    echo (isset($data[$barang[$i]][$merk[$j]])?"<td class="_in">".$data[$barang[$i]][$merk[$j]][0]."</td><td>".$data[$barang[$i]][$merk[$j]][1]."</td>":"<td class="_in">0</td><td>0</td>");
    $stok+=(isset($data[$barang[$i]][$merk[$j]])?$data[$barang[$i]][$merk[$j]][2]:0);
  }
  echo "<td class="_in">".$stok."</td></tr>n";
}
echo "</tbody><table>n";
?>
</body>
</html>