/DB/db_pos_7_27_2016.sql
SQL | 355 lines | 187 code | 64 blank | 104 comment | 0 complexity | 6d157ef6a33f9186866a8ebccd992976 MD5 | raw file
- -- phpMyAdmin SQL Dump
- -- version 3.5.2.2
- -- http://www.phpmyadmin.net
- --
- -- Host: 127.0.0.1
- -- Generation Time: Jul 27, 2016 at 06:51 AM
- -- Server version: 5.5.27
- -- PHP Version: 5.4.7
- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
- SET time_zone = "+00:00";
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
- /*!40101 SET NAMES utf8 */;
- --
- -- Database: `db_pos`
- --
- -- --------------------------------------------------------
- --
- -- Table structure for table `customers`
- --
- CREATE TABLE IF NOT EXISTS `customers` (
- `customer_id` int(11) NOT NULL AUTO_INCREMENT,
- `customer_type_id` int(11) NOT NULL,
- `customer_name` varchar(255) NOT NULL,
- `customer_address` varchar(255) NOT NULL,
- `customer_phone` varchar(15) NOT NULL,
- `customer_email` varchar(20) NOT NULL,
- `customer_status` varchar(10) NOT NULL,
- PRIMARY KEY (`customer_id`),
- KEY `customer_type_id` (`customer_type_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
- -- --------------------------------------------------------
- --
- -- Table structure for table `customer_type`
- --
- CREATE TABLE IF NOT EXISTS `customer_type` (
- `customer_type_id` int(11) NOT NULL AUTO_INCREMENT,
- `customer_type` varchar(255) NOT NULL,
- `customer_type_des` varchar(255) NOT NULL,
- `deleted_at` varchar(255) NOT NULL,
- PRIMARY KEY (`customer_type_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
- -- --------------------------------------------------------
- --
- -- Table structure for table `login`
- --
- CREATE TABLE IF NOT EXISTS `login` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `first_name` varchar(50) NOT NULL,
- `last_name` varchar(50) NOT NULL,
- `email` varchar(50) NOT NULL,
- `password` varchar(50) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
- --
- -- Dumping data for table `login`
- --
- INSERT INTO `login` (`id`, `first_name`, `last_name`, `email`, `password`) VALUES
- (1, 'mamun', 'rahaman', 'mamunur@gmail.com', '123456'),
- (2, 'jashim', 'uddin', 'tania_ctg1@yahoo.com', 'e10adc3949ba59abbe56e057f20f883e'),
- (3, 'jashim', 'uddin', 'tania_ctg2@yahoo.com', 'e10adc3949ba59abbe56e057f20f883e'),
- (4, 'jashim', 'uddin', 'tania_ctg5@yahoo.com', 'e10adc3949ba59abbe56e057f20f883e'),
- (5, 'Smart', 'Rashed', 'smartrashed@yahoo.com', '81dc9bdb52d04dc20036dbd8313ed055');
- -- --------------------------------------------------------
- --
- -- Table structure for table `producat_category`
- --
- CREATE TABLE IF NOT EXISTS `producat_category` (
- `category_id` int(11) NOT NULL AUTO_INCREMENT,
- `category_name` varchar(20) NOT NULL,
- PRIMARY KEY (`category_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
- --
- -- Dumping data for table `producat_category`
- --
- INSERT INTO `producat_category` (`category_id`, `category_name`) VALUES
- (4, 'Laptop'),
- (5, 'Water'),
- (6, 'Book');
- -- --------------------------------------------------------
- --
- -- Table structure for table `products`
- --
- CREATE TABLE IF NOT EXISTS `products` (
- `product_id` int(11) NOT NULL AUTO_INCREMENT,
- `category_id` int(11) NOT NULL,
- `product_name` varchar(255) NOT NULL,
- `product_code` varchar(255) NOT NULL,
- `product_size_id` int(11) NOT NULL,
- `product_price` int(255) NOT NULL,
- `product_sell_price` varchar(255) NOT NULL,
- `manufac_date` date NOT NULL,
- `exp_date` date NOT NULL,
- PRIMARY KEY (`product_id`),
- KEY `category_id` (`category_id`,`product_size_id`),
- KEY `category_id_2` (`category_id`,`product_size_id`),
- KEY `product_size_id` (`product_size_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
- --
- -- Dumping data for table `products`
- --
- INSERT INTO `products` (`product_id`, `category_id`, `product_name`, `product_code`, `product_size_id`, `product_price`, `product_sell_price`, `manufac_date`, `exp_date`) VALUES
- (1, 4, 'HP-1000', 'L20330', 3, 2000, '2222', '2016-07-01', '2016-07-30'),
- (8, 4, 'DELL-100', 'D-100', 3, 400, '450', '0000-00-00', '0000-00-00'),
- (10, 6, 'Quran', '786', 5, 400, '450', '0000-00-00', '0000-00-00');
- -- --------------------------------------------------------
- --
- -- Table structure for table `product_size`
- --
- CREATE TABLE IF NOT EXISTS `product_size` (
- `product_size_id` int(11) NOT NULL AUTO_INCREMENT,
- `product_size_name` varchar(255) NOT NULL,
- `description` varchar(255) NOT NULL,
- PRIMARY KEY (`product_size_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
- --
- -- Dumping data for table `product_size`
- --
- INSERT INTO `product_size` (`product_size_id`, `product_size_name`, `description`) VALUES
- (3, '1 KG', 'Onion'),
- (4, '15"', 'Laptop'),
- (5, 'Small', 'Bokk');
- -- --------------------------------------------------------
- --
- -- Table structure for table `purchase`
- --
- CREATE TABLE IF NOT EXISTS `purchase` (
- `purchase_id` int(11) NOT NULL AUTO_INCREMENT,
- `supplier_id` int(11) NOT NULL,
- `product_cat_id` int(11) NOT NULL,
- `product_id` int(11) NOT NULL,
- `product_size_id` int(11) NOT NULL,
- `quantity` varchar(255) NOT NULL,
- `purchase_date` int(11) NOT NULL,
- `user_id` int(11) NOT NULL,
- PRIMARY KEY (`purchase_id`),
- KEY `product_cat_id` (`product_cat_id`),
- KEY `supplier_id` (`supplier_id`,`product_cat_id`,`product_id`,`product_size_id`,`user_id`),
- KEY `supplier_id_2` (`supplier_id`),
- KEY `product_cat_id_2` (`product_cat_id`),
- KEY `product_id` (`product_id`),
- KEY `product_size_id` (`product_size_id`),
- KEY `user_id` (`user_id`),
- KEY `product_id_2` (`product_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
- --
- -- Dumping data for table `purchase`
- --
- INSERT INTO `purchase` (`purchase_id`, `supplier_id`, `product_cat_id`, `product_id`, `product_size_id`, `quantity`, `purchase_date`, `user_id`) VALUES
- (10, 1, 6, 10, 5, '10', 1469560463, 1),
- (11, 1, 6, 10, 5, '10', 0, 1),
- (12, 1, 6, 10, 5, '10', 0, 1),
- (13, 1, 6, 10, 5, '10', 2016, 1),
- (14, 1, 6, 10, 5, '10', 2016, 1),
- (15, 1, 6, 10, 5, '10', 0, 1);
- -- --------------------------------------------------------
- --
- -- Table structure for table `sales`
- --
- CREATE TABLE IF NOT EXISTS `sales` (
- `sale_id` int(11) NOT NULL AUTO_INCREMENT,
- `category_id` int(11) NOT NULL,
- `product_id` int(11) NOT NULL,
- `product_size_id` int(11) NOT NULL,
- `customer_id` int(11) NOT NULL,
- `sale_price` varchar(255) NOT NULL,
- `vat` varchar(255) NOT NULL,
- `quantity` varchar(255) NOT NULL,
- `sale_date` date NOT NULL,
- `user_id` int(11) NOT NULL,
- PRIMARY KEY (`sale_id`),
- KEY `category_id` (`category_id`,`product_id`,`product_size_id`,`customer_id`,`user_id`),
- KEY `category_id_2` (`category_id`),
- KEY `product_id` (`product_id`),
- KEY `product_size_id` (`product_size_id`),
- KEY `customer_id` (`customer_id`),
- KEY `user_id` (`user_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
- -- --------------------------------------------------------
- --
- -- Table structure for table `stock`
- --
- CREATE TABLE IF NOT EXISTS `stock` (
- `stock_id` int(11) NOT NULL AUTO_INCREMENT,
- `purchase_id` int(11) NOT NULL,
- `sales_id` int(11) NOT NULL,
- `product_id` int(11) NOT NULL,
- PRIMARY KEY (`stock_id`),
- KEY `purchase_id` (`purchase_id`),
- KEY `sales_id` (`sales_id`),
- KEY `product_id` (`product_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
- -- --------------------------------------------------------
- --
- -- Table structure for table `suppliers`
- --
- CREATE TABLE IF NOT EXISTS `suppliers` (
- `supplier_id` int(11) NOT NULL AUTO_INCREMENT,
- `supplier_name` varchar(255) NOT NULL,
- `supplier_address` varchar(255) NOT NULL,
- `supplier_phone` varchar(255) NOT NULL,
- `supplier_email` varchar(255) NOT NULL,
- `supplier_bank_account` varchar(255) NOT NULL,
- `supplier_status` varchar(255) NOT NULL,
- PRIMARY KEY (`supplier_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
- --
- -- Dumping data for table `suppliers`
- --
- INSERT INTO `suppliers` (`supplier_id`, `supplier_name`, `supplier_address`, `supplier_phone`, `supplier_email`, `supplier_bank_account`, `supplier_status`) VALUES
- (1, 'Rashed', 'Noakhali', '01673760122', 'smartrashed@yahoo.com', '564789', 'Good');
- -- --------------------------------------------------------
- --
- -- Table structure for table `users`
- --
- CREATE TABLE IF NOT EXISTS `users` (
- `user_id` int(11) NOT NULL AUTO_INCREMENT,
- `user_type_id` int(11) NOT NULL,
- `user_name` varchar(255) NOT NULL,
- `user_email` varchar(255) NOT NULL,
- `user_pass` varchar(255) NOT NULL,
- PRIMARY KEY (`user_id`),
- KEY `user_type_id` (`user_type_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
- --
- -- Dumping data for table `users`
- --
- INSERT INTO `users` (`user_id`, `user_type_id`, `user_name`, `user_email`, `user_pass`) VALUES
- (1, 1, 'smartrashed', 'smartrashed@gmail.com', '123456');
- -- --------------------------------------------------------
- --
- -- Table structure for table `user_type`
- --
- CREATE TABLE IF NOT EXISTS `user_type` (
- `user_type_id` int(11) NOT NULL AUTO_INCREMENT,
- `user_type_name` varchar(255) NOT NULL,
- PRIMARY KEY (`user_type_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
- --
- -- Dumping data for table `user_type`
- --
- INSERT INTO `user_type` (`user_type_id`, `user_type_name`) VALUES
- (1, 'Smart Rashed');
- --
- -- Constraints for dumped tables
- --
- --
- -- Constraints for table `customers`
- --
- ALTER TABLE `customers`
- ADD CONSTRAINT `customers_ibfk_2` FOREIGN KEY (`customer_type_id`) REFERENCES `customer_type` (`customer_type_id`) ON DELETE CASCADE ON UPDATE CASCADE;
- --
- -- Constraints for table `products`
- --
- ALTER TABLE `products`
- ADD CONSTRAINT `products_ibfk_4` FOREIGN KEY (`product_size_id`) REFERENCES `product_size` (`product_size_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `products_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `producat_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;
- --
- -- Constraints for table `purchase`
- --
- ALTER TABLE `purchase`
- ADD CONSTRAINT `purchase_ibfk_10` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `purchase_ibfk_6` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `purchase_ibfk_7` FOREIGN KEY (`product_cat_id`) REFERENCES `producat_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `purchase_ibfk_8` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `purchase_ibfk_9` FOREIGN KEY (`product_size_id`) REFERENCES `product_size` (`product_size_id`) ON DELETE CASCADE ON UPDATE CASCADE;
- --
- -- Constraints for table `sales`
- --
- ALTER TABLE `sales`
- ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_type` (`user_type_id`),
- ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `producat_category` (`category_id`),
- ADD CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`),
- ADD CONSTRAINT `sales_ibfk_4` FOREIGN KEY (`product_size_id`) REFERENCES `product_size` (`product_size_id`),
- ADD CONSTRAINT `sales_ibfk_5` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`);
- --
- -- Constraints for table `stock`
- --
- ALTER TABLE `stock`
- ADD CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchase` (`purchase_id`),
- ADD CONSTRAINT `stock_ibfk_2` FOREIGN KEY (`sales_id`) REFERENCES `sales` (`sale_id`),
- ADD CONSTRAINT `stock_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`);
- --
- -- Constraints for table `users`
- --
- ALTER TABLE `users`
- ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`user_type_id`) REFERENCES `user_type` (`user_type_id`);
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;