1 <?php
 2 require_once "vendor/autoload.php";
 3   
 4 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 5 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 6 
 7 spl_autoload_register(function ($class_name) { // https://www.php.net/manual/en/language.oop5.autoload.php
 8     include $class_name . '.class.php';
 9 });
10 
11 //if the id is not in the URL
12 if(!isset($_GET['inquiryid'])){
13     exit('<script>alert("No inquiry ID entered, please try again"); window.close()</script>');
14 }else{
15     $inquiryID = $_GET['inquiryid'];
16 }
17 //adapted from the phpspreadsheet documentation https://phpspreadsheet.readthedocs.io/en/latest/
18 
19 $sqlData = new GetMySQLData();
20 $spreadsheet = new Spreadsheet();
21 $Excel_writer = new Xlsx($spreadsheet);
22 
23 //iterating 3 times to create 3 worksheets to represent the 3 tables in MySQL
24 for($l = 0; $l < 3; $l++){
25     //setting the active spreadsheet, starting at the first one at index 0
26     $spreadsheet->setActiveSheetIndex($l);
27     $activeSheet = $spreadsheet->getActiveSheet();
28     switch($l){
29         case 0:
30             $table = 'inquiry';
31             break;
32         case 1:
33             $table = 'customers';
34             break;
35         case 2:
36             $table = 'day_plans';
37             break;
38     }
39     $activeSheet->setTitle($table);
40     //getting the sql colums  from the current table
41     $columnsQuery = $sqlData->getColumns($table);
42 
43     if($columnsQuery->num_rows > 0){
44         $column = 'A';
45         //the MySQL colums result is turned into an assoicative array and is used to populate the first row cells for the excel worksheet
46         while($row = $columnsQuery->fetch_assoc()) {
47             $activeSheet->setCellValue($column."1" , $row['Field']);
48             $fields[] = $row['Field'];
49             $column++;
50         }
51     }
52     //the data within the MySQL table is returned as an assoicative array and is then used to populate cells in the worksheet
53     $query = $sqlData->getTableDataFromInquiryID($table, $inquiryID);
54     if($query->num_rows > 0){
55         $i = 2;
56         while($row = $query->fetch_assoc()){
57             $column = 'A';
58             for($j = 0; $j < count($fields); $j++){
59                 $activeSheet->setCellValue($column.$i , $row[$fields[$j]]);
60                 $column++;
61             }
62             $i++;
63         }
64         unset($fields);
65     }
66     //this if statement ensures that an extra worksheet is not made since when creating the spreadsheet, a worksheet is already made.
67     if($l != 2){
68         $spreadsheet->createSheet();
69     }
70 }
71 
72 //naming the file
73 $filename = 'Inquiry_for_ID_'.$inquiryID.'.xlsx';
74 
75 //making this html file to start a download when opened
76 header('Content-Type: application/vnd.ms-excel');
77 header('Content-Disposition: attachment;filename='. $filename);
78 header('Cache-Control: max-age=0');
79 $Excel_writer->save('php://output');
80 ?>