Skip to content
Snippets Groups Projects

11- 13515040 - Patrick Nugroho Hadiwinoto

Open 11- 13515040 - Patrick Nugroho Hadiwinoto
1SleepNotFound/TugasBesar1_2017:master into master
Open Martin Lutta requested to merge 1SleepNotFound/TugasBesar1_2017:master into master
Compare
and
85 files
+ 3251
− 0
Preferences
Compare changes
Files
85
database/pr_ojek.sql 0 → 100644
+ 224
− 0
-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 07, 2017 at 03:20 PM
-- Server version: 10.1.25-MariaDB
-- PHP Version: 5.6.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
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 utf8mb4 */;
--
-- Database: `pr_ojek`
--
-- --------------------------------------------------------
--
-- Table structure for table `driver_preferred_locations`
--
CREATE TABLE `driver_preferred_locations` (
`no` int(11) NOT NULL,
`driverID` int(11) NOT NULL,
`preferred_location` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `driver_preferred_locations`
--
INSERT INTO `driver_preferred_locations` (`no`, `driverID`, `preferred_location`) VALUES
(1, 5, 'Bandung'),
(2, 5, 'Jakarta'),
(3, 5, 'Surabaya');
-- --------------------------------------------------------
--
-- Table structure for table `driver_reviews`
--
CREATE TABLE `driver_reviews` (
`no` int(11) NOT NULL,
`driverID` int(11) NOT NULL,
`rating` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `driver_reviews`
--
INSERT INTO `driver_reviews` (`no`, `driverID`, `rating`) VALUES
(1, 5, 4),
(2, 3, 3),
(3, 5, 2),
(4, 5, 5),
(5, 3, 3);
-- --------------------------------------------------------
--
-- Table structure for table `transaction_history`
--
CREATE TABLE `transaction_history` (
`transID` int(4) NOT NULL,
`passengerID` int(4) NOT NULL,
`driverID` int(4) NOT NULL,
`pickLoc` varchar(20) NOT NULL,
`destLoc` varchar(20) NOT NULL,
`date` date NOT NULL,
`stars` int(1) NOT NULL,
`comment` text,
`hidden_order` int(11) NOT NULL,
`hidden_driver` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `transaction_history`
--
INSERT INTO `transaction_history` (`transID`, `passengerID`, `driverID`, `pickLoc`, `destLoc`, `date`, `stars`, `comment`, `hidden_order`, `hidden_driver`) VALUES
(1, 5, 4, 'Bandung', 'Jakarta', '2017-10-05', 4, 'Aku menyesal :(', 1, 0),
(8, 5, 4, 'Bandung', 'Jakarta', '2017-10-05', 5, NULL, 0, 0),
(9, 5, 4, 'Bandung', 'Jakarta', '0000-00-00', 3, NULL, 0, 0),
(10, 5, 4, 'Bandung', 'Jakarta', '0000-00-00', 1, NULL, 0, 0),
(11, 3, 5, 'Bandung', 'Jakarta', '2017-10-01', 3, 'Nice one', 0, 0);
--
-- Triggers `transaction_history`
--
DELIMITER $$
CREATE TRIGGER `rating_and_votes_update` AFTER INSERT ON `transaction_history` FOR EACH ROW BEGIN
select driverID into @driver from transaction_history WHERE transID=LAST_INSERT_ID();
SET @count = (SELECT COUNT(stars) FROM transaction_history WHERE driverID=@driver);
SET @sum = (SELECT SUM(stars) FROM transaction_history WHERE driverID=@driver);
UPDATE driver_review SET rating=(@sum/@count), votes=@count WHERE driverID = @driver;
END
$$
DELIMITER ;
-- --------------------------------------------------------
--
-- Table structure for table `user_data`
--
CREATE TABLE `user_data` (
`ID` int(4) NOT NULL,
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`full_name` varchar(30) NOT NULL,
`email` varchar(20) NOT NULL,
`phone` varchar(20) NOT NULL,
`isDriver` varchar(3) NOT NULL,
`picturePath` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `user_data`
--
INSERT INTO `user_data` (`ID`, `username`, `password`, `full_name`, `email`, `phone`, `isDriver`, `picturePath`) VALUES
(3, 'admin', 'admin', 'admin', 'admin@gmail.com', '085780058876', 'yes', '../uploads/3.JPG'),
(4, 'martin', 'tes', 'Martin Lutta Putra', 'asd@gmail.com', '085780058876', 'yes', '../uploads/4.PNG'),
(5, 'lutta', 'hehe', 'Martin Lutta', 'hehe@gmail.com', '085780058882', 'yes', '../uploads/5.JPG'),
(6, 'vinjerdim', 'marvin', 'Marvin Jerremy Budiman', 'vinjerdim@gmail.com', '085780000001', 'no', ''),
(7, 'patricknugrohoh', 'patrick', 'Patrick Nugroho H.', 'patricnugrohoh@gmail', '085780000002', 'yes', '');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `driver_preferred_locations`
--
ALTER TABLE `driver_preferred_locations`
ADD PRIMARY KEY (`no`),
ADD KEY `driverID` (`driverID`);
--
-- Indexes for table `driver_reviews`
--
ALTER TABLE `driver_reviews`
ADD PRIMARY KEY (`no`),
ADD KEY `driverID` (`driverID`);
--
-- Indexes for table `transaction_history`
--
ALTER TABLE `transaction_history`
ADD PRIMARY KEY (`transID`),
ADD KEY `fk_passenger_id` (`passengerID`),
ADD KEY `fk_driver_id` (`driverID`);
--
-- Indexes for table `user_data`
--
ALTER TABLE `user_data`
ADD PRIMARY KEY (`ID`),
ADD UNIQUE KEY `ID` (`ID`),
ADD UNIQUE KEY `username` (`username`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `driver_preferred_locations`
--
ALTER TABLE `driver_preferred_locations`
MODIFY `no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `driver_reviews`
--
ALTER TABLE `driver_reviews`
MODIFY `no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `transaction_history`
--
ALTER TABLE `transaction_history`
MODIFY `transID` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
--
-- AUTO_INCREMENT for table `user_data`
--
ALTER TABLE `user_data`
MODIFY `ID` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `driver_preferred_locations`
--
ALTER TABLE `driver_preferred_locations`
ADD CONSTRAINT `driver_preferred_locations_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`);
--
-- Constraints for table `driver_reviews`
--
ALTER TABLE `driver_reviews`
ADD CONSTRAINT `driver_reviews_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`);
--
-- Constraints for table `transaction_history`
--
ALTER TABLE `transaction_history`
ADD CONSTRAINT `fk_driver_id` FOREIGN KEY (`driverID`) REFERENCES `user_data` (`ID`),
ADD CONSTRAINT `fk_passenger_id` FOREIGN KEY (`passengerID`) REFERENCES `user_data` (`ID`);
COMMIT;
/*!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 */;