<?php
# Export all transactions from database into an excel file
# Open database connection
include 'database.php';
# Excel export class
require_once "xls.php";
$xls = new ExcelExport();
# Create headings
$xls->addRow(array("Name", "Phone", "Email", "Address", "City", "State", "Zip", "Country", "School Name", "District", "Title I", "Percent Free Lunch", "Percent Eng Learners", "Percent Non White", "Years Taught", "Grades Taught", "Quantity", "Request Date", "Start Date", "End Date", "Approved", "Shipped"));
# Read from transactions table
$result = mysql_query("SELECT * FROM rentals_tbl Order By requestdate DESC");
while ($row = mysql_fetch_array($result)) {
# Populate spreadsheet cells with database row data
$xls->addRow(array($row['name'], $row['phone'], $row['email'], $row['address'], $row['city'], $row['state'], $row['zip'], $row['country'], $row['schoolname'], $row['district'], $row['titlei'], $row['freelunch'], $row['englearners'], $row['nonwhite'], $row['yearstaught'], $row['gradestaught'], $row['quantity'], $row['requestdate'], $row['startdate'], $row['enddate'], $row['rental_approved'], $row['shipstatus']));
}
# Create excel file for download
$xls->download("qcn-BorrowedSensors.xls");
# Close database connection
mysql_close($link);
function exportXLS($data)
{
require_once 'ExcelExport.php';
$this->exportFileName = $this->getExportFileName();
$cols = $this->getDisplayColumnInfo();
$xls = new ExcelExport();
$colHeader = array();
foreach ($cols as $idx => $col) {
array_push($colHeader, $col['header']);
}
$xls->addRow($colHeader);
foreach ($data as $idx => $record) {
$record = $this->object2array($record);
$recordA = array();
foreach ($cols as $idx => $col) {
$f = $record[$col['fieldIndex']];
array_push($recordA, empty($f) ? '' : $f);
}
$xls->addRow($recordA);
}
$xls->download($this->exportFileName . '.xls');
}
$billedAmount = $row['qtySensor5'] * 5.0 . '.00';
}
if ($row['qtySensor5'] == 0 && $row['qtySensor49'] != 0) {
$billedAmount = $row['qtySensor49'] * 49.0 . '.00';
}
# Set tax
$taxregion = $row['region_id'];
if ($taxregion != '4') {
$taxresult = mysql_query("SELECT * FROM taxregions_tbl WHERE id = '{$taxregion}'");
$taxrow = mysql_fetch_array($taxresult);
$tax = $taxrow['tax'];
if (strtotime($row['purchasedate']) < strtotime('07/01/2011')) {
$tax += 1;
}
} else {
$tax = '0';
}
if ($tax != '0') {
$taxAmount = $billedAmount * ($tax * 0.01);
$total = $billedAmount - $taxAmount;
} else {
$taxAmount = '0';
$total = $billedAmount;
}
# Populate spreadsheet cells with database row data
$xls->addRow(array($row['name'], $row['phone'], $row['email'], $row['address'], $row['city'], $row['state'], $row['zip'], $row['country'], $row['qtySensor5'], $row['qtySensor49'], $row['purchasedate'], $row['transactionid'], $billedAmount, $tax, $taxAmount, $total, $row['sensor5_approved'], $row['shipstatus'], $row['sensor5_econfirmed']));
}
# Create excel file for download
$xls->download("qcn-transactions.xls");
# Close database connection
mysql_close($link);
