<?php
$testCondition = "20";
session_start();
// set for reporting errors during testing,
// but remove next two lines for production
//ini_set('error_reporting', E_ALL);
//ini_set('error_reporting', E_ALL ^ E_NOTICE);
//ini_set('display_errors',1);
// include the export_spreadsheet_class
require_once ("export_spreadsheets_class.php");
// create an instance of the export_spreadsheet_class object
$myExport = new exportSpreadsheets();
// set instance properties
$myExport->mysqlServer = "localhost";
$myExport->mysqlUsername = "exportUser";
$myExport->mysqlPassword = "exportPassword";
$myExport->mysqlDatabaseName = "export_spreadsheets";
$myExport->mysqlTableName = "products";
$myExport->debug = false;
$workbook_array = ''; // test condition 1
$workbook_name = "testCondition".$testCondition.".xls";
if ($testCondition > "1")
{
$workbook_array = array ();
$sheetName = "testCondition$testCondition";
}
switch ($testCondition) {
case "1":
break;
case "2":
$workbook_array[$sheetName] = array (
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'description', 'columnTitle'=>'Description', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Price', 'alignment'=>'right','sum'=>'no')
);
break;
case "3":
$workbook_array[$sheetName] = array (
'sheet' => array ( 'sortFieldName'=>'name', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' )
);
break;
case "4":
$workbook_array[$sheetName] = array (
'sheet' => array ( 'sortFieldName'=>'unitPrice', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'description', 'columnTitle'=>'Description', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Price', 'alignment'=>'right','sum'=>'no')
);
break;
case "5":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select * from orders order by orderDate ASC')
);
break;
case "6":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'orderDate', 'columnTitle'=>'Date Ordered', 'alignment'=>'left','sum'=>'no'),
'column4' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column5' => array ( 'fieldName'=>'orderQuantity', 'columnTitle'=>'Quantity', 'alignment'=>'right','sum'=>'no'),
'column6' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'alignment'=>'right','sum'=>'no'),
'column7' => array ( 'fieldName'=>'rowFormula', 'columnTitle'=>'Item Total', 'alignment'=>'right','sum'=>'yes', 'formula'=>'=IF(ISBLANK(column5)+ISBLANK(column6),"",column5*column6)','format'=>'0.00')
);
break;
case "7":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'sheet' => array ( 'sortFieldName'=>'name', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' )
);
break;
case "8":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'sheet' => array ( 'sortFieldName'=>'orderDate', 'sortFieldDirection'=>'DESC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'orderDate', 'columnTitle'=>'Date Ordered', 'alignment'=>'left','sum'=>'no'),
'column4' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column5' => array ( 'fieldName'=>'orderQuantity', 'columnTitle'=>'Quantity', 'alignment'=>'right','sum'=>'no'),
'column6' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'alignment'=>'right','sum'=>'no'),
'column7' => array ( 'fieldName'=>'rowFormula', 'columnTitle'=>'Item Total', 'sum'=>'yes', 'formula'=>'=IF(ISBLANK(column5)+ISBLANK(column6),"",column5*column6)','numberFormat'=>'0.00')
);
break;
case "9":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'')
);
break;
case "10":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no')
);
break;
case "11":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'sheet' => array ( 'sortFieldName'=>'lastName', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' )
);
break;
case "12":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no')
);
break;
case "13":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc')
);
break;
case "14":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'revenue', 'columnTitle'=>'Total Sales', 'alignment'=>'left','sum'=>'yes')
);
break;
case "15":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC', 'pageTitle'=>'Best Customer', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' )
);
break;
case "16":
$workbook_array[$sheetName] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, sum(orders.totalPrice) as revenue from products, orders where orders.productID=products.id group by productID order by totalQuantity desc'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC', 'pageTitle'=>'Most Popular Products', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'alignment'=>'left','sum'=>'yes')
);
break;
case "20":
$workbook_array['condition2'] = array (
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'description', 'columnTitle'=>'Description', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Price', 'alignment'=>'right','sum'=>'no')
);
$workbook_array['condition3'] = array (
'sheet' => array ( 'sortFieldName'=>'name', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' )
);
$workbook_array['condition4'] = array (
'sheet' => array ( 'sortFieldName'=>'unitPrice', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'description', 'columnTitle'=>'Description', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Price', 'alignment'=>'right','sum'=>'no')
);
$workbook_array['condition5'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select * from orders order by orderDate ASC')
);
$workbook_array['condition6'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'orderDate', 'columnTitle'=>'Date Ordered', 'alignment'=>'left','sum'=>'no'),
'column4' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column5' => array ( 'fieldName'=>'orderQuantity', 'columnTitle'=>'Quantity', 'alignment'=>'right','sum'=>'no'),
'column6' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'alignment'=>'right','sum'=>'no'),
'column7' => array ( 'fieldName'=>'rowFormula', 'columnTitle'=>'Item Total', 'alignment'=>'right','sum'=>'yes', 'formula'=>'=IF(ISBLANK(column5)+ISBLANK(column6),"",column5*column6)','format'=>'0.00')
);
$workbook_array['condition7'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'sheet' => array ( 'sortFieldName'=>'name', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' )
);
$workbook_array['condition8'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlQuery'=>'select customers.lastName, customers.firstName, orders.orderDate, products.name, orders.orderQuantity, orders.unitPrice from customers, orders, products where orders.customerID=customers.id and orders.productID=products.id'),
'sheet' => array ( 'sortFieldName'=>'orderDate', 'sortFieldDirection'=>'DESC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'orderDate', 'columnTitle'=>'Date Ordered', 'alignment'=>'left','sum'=>'no'),
'column4' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product', 'alignment'=>'left','sum'=>'no'),
'column5' => array ( 'fieldName'=>'orderQuantity', 'columnTitle'=>'Quantity', 'alignment'=>'right','sum'=>'no'),
'column6' => array ( 'fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'alignment'=>'right','sum'=>'no','numberFormat'=>'0.00'),
'column7' => array ( 'fieldName'=>'rowFormula', 'columnTitle'=>'Item Total', 'alignment'=>'','vAlign'=>'','sum'=>'yes', 'formula'=>'=IF(ISBLANK(column5)+ISBLANK(column6),"",column5*column6)','numberFormat'=>'0.00')
);
$workbook_array['condition9'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers')
);
$workbook_array['condition10'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no')
);
$workbook_array['condition11'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'sheet' => array ( 'sortFieldName'=>'lastName', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' )
);
$workbook_array['condition12'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC','pageTitle'=>$sheetName, 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'no' ),
'column1' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no')
);
$workbook_array['condition13'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc')
);
$workbook_array['condition14'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'column1' => array ( 'fieldName'=>'lastName', 'columnTitle'=>'Last Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'firstName', 'columnTitle'=>'First Name', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'revenue', 'columnTitle'=>'Total Sales', 'alignment'=>'left','sum'=>'yes')
);
$workbook_array['condition15'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select customers.lastName, customers.firstName, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC', 'pageTitle'=>'Best Customer', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' )
);
$workbook_array['condition16'] = array (
'connection' => array ( 'mysqlServer'=>'localhost', 'mysqlUsername'=>'exportUser', 'mysqlPassword'=>'exportPassword','mysqlDatabaseName'=>'export_spreadsheets','mysqlTableName'=>'customers','mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, sum(orders.totalPrice) as revenue from products, orders where orders.productID=products.id group by productID order by totalQuantity desc'),
'sheet' => array ( 'sortFieldName'=>'firstName', 'sortFieldDirection'=>'ASC', 'pageTitle'=>'Most Popular Products', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ( 'fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ( 'fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'alignment'=>'left','sum'=>'no'),
'column3' => array ( 'fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'alignment'=>'left','sum'=>'yes')
);
break;
default:
echo "invalid test case";
}
// export the spreadsheet
$myExport->export_spreadsheets($workbook_name, $workbook_array);
session_destroy();
?>
|
Download
