Read 303 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>