# MySQL-Front 5.0  (Build 1.78)

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;


# Host: localhost    Database: jualbeli
# ------------------------------------------------------
# Server version 5.0.45-community-nt-log

#
# Table structure for table tbarang
#

DROP TABLE IF EXISTS `tbarang`;
CREATE TABLE `tbarang` (
  `kodebrg` varchar(10) NOT NULL,
  `namabrg` varchar(30) NOT NULL,
  `satuan` varchar(20) NOT NULL,
  `kategori` varchar(25) NOT NULL,
  PRIMARY KEY  (`kodebrg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tbarang
#
LOCK TABLES `tbarang` WRITE;
/*!40000 ALTER TABLE `tbarang` DISABLE KEYS */;

INSERT INTO `tbarang` VALUES ('BRG001','Sabun GIV','buah','Sabun mandi');
INSERT INTO `tbarang` VALUES ('BRG002','Sabun Lifeboy','buah','Sabun mandi');
INSERT INTO `tbarang` VALUES ('BRG003','Soklin 1 Kg','buah','Sabun Cuci');
INSERT INTO `tbarang` VALUES ('BRG004','Rinso 1 Kg','buah','Sabun Cuci');
/*!40000 ALTER TABLE `tbarang` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tnotabeli
#

DROP TABLE IF EXISTS `tnotabeli`;
CREATE TABLE `tnotabeli` (
  `nonota` varchar(15) NOT NULL,
  `user` varchar(15) NOT NULL,
  `kodesp` varchar(10) NOT NULL,
  `tglbeli` date default NULL,
  PRIMARY KEY  (`nonota`),
  KEY `FK_tnotabeli_tuser` (`user`),
  KEY `FK_tnotabeli_tsuplier` (`kodesp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tnotabeli
#
LOCK TABLES `tnotabeli` WRITE;
/*!40000 ALTER TABLE `tnotabeli` DISABLE KEYS */;

INSERT INTO `tnotabeli` VALUES ('B001','fitri','SP001','2008-01-12');
INSERT INTO `tnotabeli` VALUES ('B002','fitri','SP002','2008-01-13');
INSERT INTO `tnotabeli` VALUES ('B003','fitri','SP001','2008-01-13');
/*!40000 ALTER TABLE `tnotabeli` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tnotajual
#

DROP TABLE IF EXISTS `tnotajual`;
CREATE TABLE `tnotajual` (
  `nonota` varchar(15) NOT NULL,
  `tgljual` date NOT NULL,
  PRIMARY KEY  (`nonota`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tnotajual
#
LOCK TABLES `tnotajual` WRITE;
/*!40000 ALTER TABLE `tnotajual` DISABLE KEYS */;

INSERT INTO `tnotajual` VALUES ('J001','2008-11-14');
INSERT INTO `tnotajual` VALUES ('J002','2008-11-14');
/*!40000 ALTER TABLE `tnotajual` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tpembelian
#

DROP TABLE IF EXISTS `tpembelian`;
CREATE TABLE `tpembelian` (
  `nonota` varchar(15) NOT NULL,
  `kodebrg` varchar(10) NOT NULL,
  `harga` int(10) NOT NULL,
  `jumlah` int(5) NOT NULL,
  KEY `FK_tpembelian_tnotabeli` (`nonota`),
  KEY `FK_tpembelian_tbarang` (`kodebrg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tpembelian
#
LOCK TABLES `tpembelian` WRITE;
/*!40000 ALTER TABLE `tpembelian` DISABLE KEYS */;

INSERT INTO `tpembelian` VALUES ('B001','BRG001',1000,10);
INSERT INTO `tpembelian` VALUES ('B001','BRG002',1200,20);
INSERT INTO `tpembelian` VALUES ('B001','BRG003',1500,30);
INSERT INTO `tpembelian` VALUES ('B001','BRG004',1800,30);
INSERT INTO `tpembelian` VALUES ('B002','BRG001',1000,10);
INSERT INTO `tpembelian` VALUES ('B002','BRG002',1200,20);
INSERT INTO `tpembelian` VALUES ('B002','BRG003',1500,30);
INSERT INTO `tpembelian` VALUES ('B002','BRG004',1800,30);
INSERT INTO `tpembelian` VALUES ('B003','BRG002',1200,10);
INSERT INTO `tpembelian` VALUES ('B003','BRG003',1500,20);
INSERT INTO `tpembelian` VALUES ('B003','BRG004',1800,20);
/*!40000 ALTER TABLE `tpembelian` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tpenjualan
#

DROP TABLE IF EXISTS `tpenjualan`;
CREATE TABLE `tpenjualan` (
  `nonota` varchar(14) NOT NULL,
  `kodebrg` varchar(10) NOT NULL,
  `harga` int(10) NOT NULL,
  `jumlah` int(5) NOT NULL,
  KEY `kodebrg` (`kodebrg`),
  KEY `nonota` (`nonota`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tpenjualan
#
LOCK TABLES `tpenjualan` WRITE;
/*!40000 ALTER TABLE `tpenjualan` DISABLE KEYS */;

INSERT INTO `tpenjualan` VALUES ('J001','BRG001',1200,5);
INSERT INTO `tpenjualan` VALUES ('J001','BRG002',1400,3);
INSERT INTO `tpenjualan` VALUES ('J001','BRG003',1700,4);
INSERT INTO `tpenjualan` VALUES ('J002','BRG001',1200,2);
INSERT INTO `tpenjualan` VALUES ('J002','BRG002',1400,3);
INSERT INTO `tpenjualan` VALUES ('J002','BRG003',1700,4);
/*!40000 ALTER TABLE `tpenjualan` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tsuplier
#

DROP TABLE IF EXISTS `tsuplier`;
CREATE TABLE `tsuplier` (
  `kodesp` varchar(10) NOT NULL default '',
  `nama` varchar(30) default NULL,
  `alamat` varchar(35) default NULL,
  `telp` varchar(18) default NULL,
  PRIMARY KEY  (`kodesp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tsuplier
#
LOCK TABLES `tsuplier` WRITE;
/*!40000 ALTER TABLE `tsuplier` DISABLE KEYS */;

INSERT INTO `tsuplier` VALUES ('SP001','PT. Sinar Abadi','Jl. Janti No 60','0274-\r\n789765');
INSERT INTO `tsuplier` VALUES ('SP002','PT. Perkasa Jaya','Bulaksumur No. 23','0274-\r\n456782');
/*!40000 ALTER TABLE `tsuplier` ENABLE KEYS */;
UNLOCK TABLES;

#
# Table structure for table tuser
#

DROP TABLE IF EXISTS `tuser`;
CREATE TABLE `tuser` (
  `user` varchar(15) NOT NULL,
  `namaDepan` varchar(25) NOT NULL,
  `namaBelakang` varchar(30) NOT NULL,
  `JK` char(1) NOT NULL,
  `bagian` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL,
  PRIMARY KEY  (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Dumping data for table tuser
#
LOCK TABLES `tuser` WRITE;
/*!40000 ALTER TABLE `tuser` DISABLE KEYS */;

INSERT INTO `tuser` VALUES ('Anwar','Anwar','Listianto','L','casir','hgs');
INSERT INTO `tuser` VALUES ('fitri','Fitri','Agustina','P','member','hgs');
INSERT INTO `tuser` VALUES ('imam','Imam','Makhfadi','L','Admin','dpln');
INSERT INTO `tuser` VALUES ('Listiani','Listiani','Astuti','P','Casir','ecd');
INSERT INTO `tuser` VALUES ('yuli','Yuli','Listiani','P','casir','hgs');
/*!40000 ALTER TABLE `tuser` ENABLE KEYS */;
UNLOCK TABLES;

#
# View structure for view qbarangbeli
#

DROP VIEW IF EXISTS `qbarangbeli`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qbarangbeli` AS select `tbarang`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,sum(`tpembelian`.`jumlah`) AS `totalbeli` from (`tpembelian` left join `tbarang` on((`tbarang`.`kodebrg` = `tpembelian`.`kodebrg`))) group by `tbarang`.`kodebrg`,`tbarang`.`namabrg`,`tbarang`.`satuan`;

#
# Dumping data for table qbarangbeli
#
LOCK TABLES `qbarangbeli` WRITE;
/*!40000 ALTER TABLE `qbarangbeli` DISABLE KEYS */;


#
# View structure for view qbarangjual
#

DROP VIEW IF EXISTS `qbarangjual`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qbarangjual` AS select `tbarang`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,if(sum(`qpenjualan`.`jumlah`),sum(`qpenjualan`.`jumlah`),0) AS `totaljual` from (`tbarang` left join `qpenjualan` on((`tbarang`.`kodebrg` = `qpenjualan`.`kodebrg`))) group by `tbarang`.`kodebrg`,`tbarang`.`namabrg`,`tbarang`.`satuan`;

#
# Dumping data for table qbarangjual
#
LOCK TABLES `qbarangjual` WRITE;
/*!40000 ALTER TABLE `qbarangjual` DISABLE KEYS */;


#
# View structure for view qjenisklm
#

DROP VIEW IF EXISTS `qjenisklm`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qjenisklm` AS select `tuser`.`JK` AS `jk`,count(`tuser`.`JK`) AS `jumlahjk` from `tuser` group by `tuser`.`JK`;

#
# Dumping data for table qjenisklm
#
LOCK TABLES `qjenisklm` WRITE;
/*!40000 ALTER TABLE `qjenisklm` DISABLE KEYS */;


#
# View structure for view qpembelian
#

DROP VIEW IF EXISTS `qpembelian`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qpembelian` AS select `tnotabeli`.`nonota` AS `nonota`,`tsuplier`.`nama` AS `nama`,`tpembelian`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,`tpembelian`.`harga` AS `harga`,`tpembelian`.`jumlah` AS `jumlah`,(`tpembelian`.`harga` * `tpembelian`.`jumlah`) AS `jmlbeli` from (`tsuplier` join ((`tpembelian` join `tnotabeli` on((`tpembelian`.`nonota` = `tnotabeli`.`nonota`))) left join `tbarang` on((`tbarang`.`kodebrg` = `tpembelian`.`kodebrg`))) on((`tsuplier`.`kodesp` = `tnotabeli`.`kodesp`)));

#
# Dumping data for table qpembelian
#
LOCK TABLES `qpembelian` WRITE;
/*!40000 ALTER TABLE `qpembelian` DISABLE KEYS */;


#
# View structure for view qpenjualan
#

DROP VIEW IF EXISTS `qpenjualan`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qpenjualan` AS select `tnotajual`.`nonota` AS `nonota`,`tnotajual`.`tgljual` AS `tgljual`,`tpenjualan`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,`tpenjualan`.`jumlah` AS `jumlah`,`tpenjualan`.`harga` AS `harga`,(`tpenjualan`.`jumlah` * `tpenjualan`.`harga`) AS `jumlahjual` from ((`tnotajual` join `tpenjualan` on((`tnotajual`.`nonota` = `tpenjualan`.`nonota`))) left join `tbarang` on((`tbarang`.`kodebrg` = `tpenjualan`.`kodebrg`)));

#
# Dumping data for table qpenjualan
#
LOCK TABLES `qpenjualan` WRITE;
/*!40000 ALTER TABLE `qpenjualan` DISABLE KEYS */;


#
# View structure for view qstokbarang
#

DROP VIEW IF EXISTS `qstokbarang`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qstokbarang` AS select `tbarang`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,`qbarangbeli`.`totalbeli` AS `totalbeli`,`qbarangjual`.`totaljual` AS `totaljual`,(`qbarangbeli`.`totalbeli` - `qbarangjual`.`totaljual`) AS `stok` from ((`tbarang` left join `qbarangbeli` on((`tbarang`.`kodebrg` = `qbarangbeli`.`kodebrg`))) left join `qbarangjual` on((`tbarang`.`kodebrg` = `qbarangjual`.`kodebrg`)));

#
# Dumping data for table qstokbarang
#
LOCK TABLES `qstokbarang` WRITE;
/*!40000 ALTER TABLE `qstokbarang` DISABLE KEYS */;


#
# View structure for view qtotbelian
#

DROP VIEW IF EXISTS `qtotbelian`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qtotbelian` AS select `tnotabeli`.`nonota` AS `nonota`,`tnotabeli`.`tglbeli` AS `tglbeli`,`tsuplier`.`nama` AS `nama`,sum(`qpembelian`.`jmlbeli`) AS `JUMLAH` from (`tsuplier` join (`tnotabeli` join `qpembelian` on((`tnotabeli`.`nonota` = `qpembelian`.`nonota`))) on((`tsuplier`.`kodesp` = `tnotabeli`.`kodesp`))) group by `tnotabeli`.`nonota`;

#
# Dumping data for table qtotbelian
#
LOCK TABLES `qtotbelian` WRITE;
/*!40000 ALTER TABLE `qtotbelian` DISABLE KEYS */;


#
# View structure for view qtotjualan
#

DROP VIEW IF EXISTS `qtotjualan`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `qtotjualan` AS select `tnotajual`.`nonota` AS `nonota`,`tnotajual`.`tgljual` AS `tgljual`,sum(`qpenjualan`.`jumlahjual`) AS `jumlah` from (`tnotajual` join `qpenjualan` on((`tnotajual`.`nonota` = `qpenjualan`.`nonota`))) group by `tnotajual`.`nonota`,`tnotajual`.`tgljual`;

#
# Dumping data for table qtotjualan
#
LOCK TABLES `qtotjualan` WRITE;
/*!40000 ALTER TABLE `qtotjualan` DISABLE KEYS */;


#
#  Foreign keys for table tnotabeli
#

ALTER TABLE `tnotabeli`
ADD CONSTRAINT `tnotabeli_ibfk_1` FOREIGN KEY (`kodesp`) REFERENCES `tsuplier` (`kodesp`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tnotabeli_ibfk_2` FOREIGN KEY (`user`) REFERENCES `tuser` (`user`) ON DELETE CASCADE ON UPDATE CASCADE;

#
#  Foreign keys for table tpembelian
#

ALTER TABLE `tpembelian`
ADD CONSTRAINT `tpembelian_ibfk_1` FOREIGN KEY (`kodebrg`) REFERENCES `tbarang` (`kodebrg`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tpembelian_ibfk_2` FOREIGN KEY (`nonota`) REFERENCES `tnotabeli` (`nonota`) ON DELETE CASCADE ON UPDATE CASCADE;

#
#  Foreign keys for table tpenjualan
#

ALTER TABLE `tpenjualan`
ADD CONSTRAINT `tpenjualan_ibfk_1` FOREIGN KEY (`kodebrg`) REFERENCES `tbarang` (`kodebrg`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tpenjualan_ibfk_2` FOREIGN KEY (`nonota`) REFERENCES `tnotajual` (`nonota`) ON DELETE CASCADE ON UPDATE CASCADE;


/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;