Our goal in this project is to create some reporting pages for a bookstore.
To run this project;
-
Download ammps ( software stack of Apache, Mysql & PHP )
-
After installation, copy all files to "C:\Program Files\Ampps\www" ( this is default for me )
-
Then go to "http://localhost/CSE348/install.php" using your web browser
Before running the project, Please go to MYSQL --> Edit --> Preferences --> SQL Editor and set to connection read timeout to at least 60. Because we will be running some scripts to simulate sales transactions on database side
To complete this project I have used :
- VSCode for PHP and SQL codes
- MySQL WorkBench ( with ammps, mysql will be installed but I don't like the browser gui. So I have installed the workbench, workbench is much more capable )
- For database connections I have used PDO - PHP Data Objects
- To read and upload .csv files to database I have used "LOAD DATA INFILE" command.
To use this command you may need to run this sql command to check default folder path
- SHOW VARIABLES LIKE "secure_file_priv"; To change it; go to "C:\Program Files\Ampps\mysql\my.ini" file and find "# SERVER SECTION" then add the below line under this section :
- secure_file_priv="C:/Program Files/Ampps/www/CSE348" Modified my.ini file : 
LOAD DATA INFILE 'C:\\Program Files\\Ampps\\www\\CSE348\\turkey.csv' IGNORE INTO TABLE temp FIELDS TERMINATED BY ';' ENCLOSED BY '' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col1, @col2, @col3) set district_name = TRIM(@col1), city_name = TRIM(@col2), branch_name = TRIM(@col3);DROP PROCEDURE IF EXISTS procedure_SimulateSales; SET @row_number = 0; -- DELIMITER $$ -- CREATE PROCEDURE procedure_SimulateSales() CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_SimulateSales`() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE branchCount INT DEFAULT (Select Count(1) from branch); DECLARE customerID INT DEFAULT 0; DECLARE branchID INT DEFAULT 0; DECLARE stockID INT DEFAULT 0; DECLARE bookAmount INT DEFAULT 0; DECLARE totalStock INT DEFAULT 0; DECLARE minDate DATETIME DEFAULT '2020-04-30 14:53:27'; DECLARE maxDate DATETIME DEFAULT '2021-04-30 14:53:27'; DECLARE c1 CURSOR FOR -- Generate random number between 40 and 500 for every branch SELECT id as customerID, CASE WHEN @row_number < branchCount THEN @row_number:=@row_number + 1 ELSE @row_number:=@row_number + 1 - branchCount END as branchID, (FLOOR( 10 + RAND( ) *10 )) AS bookAmount FROM bedirhan_bardakci.tampRandomCustomers; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- not found olursa true yap CREATE TABLE IF NOT EXISTS tampRandomCustomers ( id INT(6) NULL ); Delete from bedirhan_bardakci.tampRandomCustomers; INSERT INTO bedirhan_bardakci.tampRandomCustomers (id ) SELECT id FROM bedirhan_bardakci.customer order by RAND(); OPEN c1; WHILE NOT done DO FETCH NEXT FROM c1 INTO customerID, branchID, bookAmount; SELECT Count(1) into totalStock FROM bedirhan_bardakci.stock where branch_id = branchID and isSold = 0; IF (done = FALSE ) THEN -- Prevent Last row of inner cursor fetched twice WHILE bookAmount > 0 and totalStock > 0 DO SELECT id into stockID FROM bedirhan_bardakci.stock where branch_id = branchID and isSold = 0 LIMIT 1; INSERT INTO bedirhan_bardakci.sale (customer_id, salesman_id, stock_id, amount, saledate) VALUES ( customerID, (select id from bedirhan_bardakci.salesman where branch_id = branchID order by RAND() LIMIT 1), stockID, 1, (SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, minDate, maxDate)), minDate)) ); Update bedirhan_bardakci.stock set isSold = 1 where id = stockID; SET bookAmount = bookAmount - 1; SET totalStock = totalStock - 1; END WHILE; END IF; END WHILE; CLOSE c1; DROP TABLE IF EXISTS tampRandomCustomers; END; -- END$$ -- DELIMITER ; CALL procedure_SimulateSales(); $mysql_host = "localhost"; $mysql_user = "root"; $mysql_password = "mysql"; $db = new PDO("mysql:host=$mysql_host", $mysql_user, $mysql_password); $query2 = file_get_contents(__DIR__ . "\question3_C_1.sql"); $query2 = $db->prepare($query2); $query2->bindParam(':param_id', $selected); $query2->execute(); $tempRowIndex = 1; $tempIncome = 0; $tempAmount = 0; foreach ($query2 as $row2) { echo "<tr>"; echo "<td>" . $tempRowIndex . "</td>"; echo "<td>" . $row2['customerName'] . " " . $row2['customerSurnmae'] . "</td>"; echo "<td>" . $row2['totalAmount'] . " pcs</td>"; echo "<td>" . $row2['totalIncome'] . " TL</td>"; echo "</tr>"; $tempIncome = $tempIncome + $row2['totalIncome']; $tempAmount = $tempAmount + $row2['totalAmount']; $tempRowIndex = $tempRowIndex + 1; } echo "</table> <br>";-
Official Turkey City List Download Link is kind of hidden on the page, so I want to put a screenshot :)
