Minerva :: Calculate Last Stock

Read 134 times | Created 2013-01-30 08:28:32 | Updated 2013-01-30 08:28:32 | | |
<?php  
/*********************************  
FILENAME  : stok.php  
CREATE BY  : cahya dsn  
PURPOSE   : calculate last stock  
CREATE DATE : 2013-01-30  
**********************************  
#table creation  

USE `test`;  

DROP TABLE IF EXISTS `tbl_trans`;  
CREATE TABLE IF NOT EXISTS `tbl_trans` (  
 `idtransaksi` varchar(11) NOT NULL UNIQUE COMMENT 'primary key untuk table tbl_trans',  
 `idbarang` varchar(30) NOT NULL COMMENT 'id barang',  
 `keterangan` varchar(100)  NOT NULL,
 `jumlah` int(11)  NOT NULL DEFAULT '0',
 `status` char(1)  NOT NULL,
 PRIMARY KEY(`idtransaksi`)  
) ENGINE=MyISAM COMMENT='tabel untuk menyimpan data transaksi';  

INSERT INTO `tbl_trans`(`idtransaksi`,`idbarang`,`keterangan`,`jumlah`,`status`) VALUES  
('20130130101','1GB','keluar',10,'K'),  
('20130130102','1GB','retur',5,'R'),
('20130130103','1GB','kurang 3',20,'M'),
('20130130104','2GB','nambah',2,'M'),
('20130130105','2GB','hadiah',4,'M'),
('20130130106','2GB','salurkan 3',7,'K'),
('20130130107','2GB','beli baru',14,'M'),
('20130130108','4GB','beli bekas',25,'M'),
('20130130109','4GB','terjual',11,'K');
;
*/
//database configuration  
$dbhost='localhost';  
$dbuser='root';  
$dbpass='';  
$dbname='test';  
//database connection  
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);  
//query to get organization datas from database   
$sql="SELECT t.idbarang,t.masuk,t.keluar,t.retur, (t.masuk-t.keluar+t.retur) AS sisa
      FROM(
        SELECT 
          idbarang,
          SUM(IF(status='M',jumlah,0))AS masuk,
          SUM(IF(status='K',jumlah,0))AS keluar,
          SUM(IF(status='R',jumlah,0))AS retur
        FROM 
          `tbl_trans` 
        GROUP BY idbarang
        )t";  
$result=$db->query($sql);  

?>
<!DOCTYPE html>  
<html>  
<head>  
<title>Perhitungan Sisa</title>  
</head>
<body>
  <table border="1">
    <tr>
      <th>No</th>
      <th>id Barang</th>
      <th>masuk</th>
      <th>Keluar</th>
      <th>Retur</th>
      <th>Sisa((M-K)+R)</th>     
    </tr>
<?php
$i=0;
while($records=$result->fetch_object()){  
  echo "<tr>"
    ."<td>".(++$i)."</td>"
    ."<td>{$records->idbarang}</td>"
    ."<td>{$records->masuk}</td>"
    ."<td>{$records->keluar}</td>"
    ."<td>{$records->retur}</td>"
    ."<td>{$records->sisa}</td>"
    ."</tr>n";
}
?>
</table>
</body>
</html>
Snippet Code Calculate Last Stock
