API tools faq
paste
Advertisement
cdsatrian

pivot tabel stok

cdsatrian
Sep 30th, 2012
172
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 4.40 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. /*******************************
  3. // PIVOT TABLE
  4. // created by  : Cahya DSN
  5. // create date : 2012-09-30
  6. // update date : 2012-10-03
  7. *******************************/
  8. ?>
  9. <!DOCTYPE html>
  10. <html>
  11.   <head>
  12.     <title>Pivot Table Stock</title>
  13.     <style type="text/css">
  14.       * {padding:0;margin:0}
  15.       .barang {width:100px;color:#900;}
  16.       .merk {width:100px;color:#900;}
  17.       .stok {width:50px;color:#900;}
  18.       h3 {margin:10px 10px 0px;color:#090;}
  19.       table {margin-left:10px;border:solid 1px #333;}
  20.       th {background-color:#eee;border:solid 1px #333;}
  21.       td {text-align:center;border:solid 1px #333;}
  22.       td.list_barang {text-align:left;font-weight:bold;color:#009;padding-left:5px;}
  23.       td._in {background-color:#ffc;}
  24.     </style>
  25.   </head>
  26.   <body>
  27. <?php
  28. /*
  29. -- Sample Data SQL
  30. -- use test;
  31. DROP TABLE IF EXISTS `tbl_stock`;
  32. CREATE TABLE IF NOT EXISTS `tbl_stock` (
  33.   `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  34.   `barang` varchar(30) NOT NULL,
  35.   `merk` varchar(30) NOT NULL,
  36.   `_in` int(11) NOT NULL DEFAULT '0',
  37.   `_out` int(11) NOT NULL DEFAULT '0',
  38.   `stok` int(11) NOT NULL DEFAULT '0',
  39.   PRIMARY KEY (`id`)
  40. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
  41.  
  42. INSERT INTO `tbl_stock`(`barang`,`merk`,`_in`,`_out`,`stok`)
  43. VALUES("Mouse","Acer",12,2,10),("Keyboard","Logitech",10,0,10),("Mouse","Microsoft",5,0,5),("Printer","Canon IP 145",10,7,3);
  44. */
  45. //-- konfigurasi koneksi database
  46. $dbhost='localhost';
  47. $dbuser='root';
  48. $dbpass='';
  49. $dbname='test';
  50. //-- connect to database
  51. $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
  52. //-- query data ke database
  53. $sql="SELECT barang,merk,_in,_out,stok FROM tbl_stock";
  54. //-- inisialisasi variabel penampung
  55. $record=array();
  56. $data=array();
  57. $barang=array();
  58. $merk=array();
  59. //-- menjalankan query ke database
  60. if($result=$db->query($sql)){
  61.   //-- ambil data dan masukkan ke variabel penampung
  62.   while($row=$result->fetch_object()){
  63.     //-- copy data record ke variable $record utk table tanpa pivot  
  64.     $record[]=$row;
  65.     //-- penyiapan data untuk pivot table
  66.     if(isset($data[$row->barang])){
  67.           if(isset($data[$row->barang][$row->merk])){
  68.                   $data[$row->barang][$row->merk][0]+=$row->_in;
  69.                   $data[$row->barang][$row->merk][1]+=$row->_out;
  70.                   $data[$row->barang][$row->merk][2]+=$row->stok;
  71.           }else{
  72.                   $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
  73.                   $merk[]=$row->merk;
  74.           }
  75.     }else{
  76.           $data[$row->barang]=array();
  77.           $barang[]=$row->barang;
  78.           $data[$row->barang][$row->merk]=array($row->_in,$row->_out,$row->stok);
  79.           $merk[]=$row->merk;
  80.     }
  81.   }
  82.   $result->close();
  83. }
  84. $db->close();
  85. $merk=array_unique($merk);
  86. //-- cetak dalam bentuk table
  87. //======= Table Data sebelum di Pivot  ========
  88. echo "<h3>SEBELUM</h3>";
  89. echo "<table>\n"
  90.     ."<tr>"
  91.     ."<th class=\"barang\">Barang</th>"
  92.     ."<th class=\"barang\">Merk</th>"
  93.     ."<th class=\"stok\">In</th>"
  94.     ."<th class=\"stok\">Out</th>"
  95.     ."<th class=\"stok\">Stok</th>"
  96.     ."</tr>";
  97. for($i=0;$i<count($record);$i++){
  98.   echo "<tr>"
  99.       ."<td class=\"list_barang\">".$record[$i]->barang."</td>"
  100.       ."<td class=\"list_barang\">".$record[$i]->merk."</td>"
  101.       ."<td>".$record[$i]->_in."</td>"
  102.       ."<td>".$record[$i]->_out."</td>"
  103.       ."<td>".$record[$i]->stok."</td>"
  104.       ."</tr>\n";
  105. }
  106. echo "</table>\n";
  107. //======= Table Data sesudah di Pivot  ========
  108. echo "<h3>SESUDAH</h3>";
  109. echo "<table>\n"
  110.     ."<thead>\n"
  111.     ."<tr><th rowspan=\"2\" class=\"barang\">Barang</th>";
  112. for($i=0;$i<count($merk);$i++){
  113.         echo "<th colspan=\"2\" class=\"merk\">".$merk[$i]."</th>";
  114. }
  115. echo "<th rowspan=\"2\" class=\"stok\">Stok</th></tr>\n<tr>\n";
  116. for($i=0;$i<count($merk);$i++){
  117.         echo "<th>in</th><th>out</th>";
  118. }
  119. echo "</tr>\n</thead>\n<tbody>\n";
  120. for($i=0;$i<count($barang);$i++){
  121.   echo "<tr>"
  122.       ."<td class=\"list_barang\">".$barang[$i]."</td>";
  123.   $stok=0;    
  124.   for($j=0;$j<count($merk);$j++){
  125.     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>");
  126.     $stok+=(isset($data[$barang[$i]][$merk[$j]])?$data[$barang[$i]][$merk[$j]][2]:0);
  127.   }
  128.   echo "<td class=\"_in\">".$stok."</td></tr>\n";
  129. }
  130. echo "</tbody><table>\n";
  131. ?>
  132. </body>
  133. </html>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!