Hello,
I am working on PHPExcel But in there calculation 0.625 we want to coming by dynamical from database. bellow brief description.
They all used to be 62.5% of the delivery fees as seen below:setCellValue('G'.($start_row+2), (($total_delivery/11)*10)*0.625);
all the drivers can have different percentages assigned to them and
we need the driver report to show the correct percentage in the calculations.
For Going on Driver_report.php [code in line: 394]
<?php
/**
* PHPExcel
*
* Copyright (C) 2006 - 2013 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.7.9, 2013-06-02
*/
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
//ini_set('memory_limit', '256M');
date_default_timezone_set('Australia/Brisbane');
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
// Get this directory
$current_dir = dirname(__FILE__);
// Common
$theme_dir = get_theme_root();
include_once("$theme_dir/Glow/baseload/shopping_cart/shopping_cart_functions.php");
/** Include PHPExcel */
require_once 'Classes/PHPExcel.php';
// Some utility functions
function get_restaurant($rid){
return mysql_fetch_assoc(mysql_query("SELECT * FROM food_restaurants WHERE id = $rid"));
}
function get_restaurants($web_order_id){
$res = array();
$q = mysql_query("SELECT DISTINCT(fr.name) as rname
FROM food_order_item foi JOIN food_menus fm
ON foi.product_id = fm.id JOIN food_restaurants fr
ON fm.restaurant_id = fr.id
WHERE foi.order_id = $web_order_id");
while($r = mysql_fetch_assoc($q)){
$res[] = $r['rname'];
}
return $res;
}
function get_delivery_fee($web_order_id){
$res = 0;
$q = mysql_query("SELECT DISTINCT(fr.id) as rid
FROM food_order_item foi JOIN food_menus fm
ON foi.product_id = fm.id JOIN food_restaurants fr
ON fm.restaurant_id = fr.id
WHERE foi.order_id = $web_order_id");
while($r = mysql_fetch_assoc($q)){
$fee = order_fee_by_restaurant($web_order_id, $r['rid']);
$res += $fee['surcharge_fee'];
$restaurant = get_restaurant($r['rid']);
$res += $restaurant['delivery_charge'];
}
return $res;
}
function set_border($objPHPExcel, $range){
$objPHPExcel->getActiveSheet()->getStyle($range)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($range)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($range)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle($range)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
}
$fontBold = array(
'font' => array(
'bold' => true,
)
);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
// Set document properties
$objPHPExcel->getProperties()->setTitle("Driver Activity Report")
->setSubject("Driver Activity Report");
// Some default values
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
// Page margin
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.25);
// Column width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(9);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(9);
//$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(9);
// Add header image into sheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Header');
$objDrawing->setDescription('Header');
$objDrawing->setPath($current_dir . '/header.jpg');
$objDrawing->setCoordinates('A1');
$objDrawing->setOffsetX(15);
$objDrawing->setOffsetY(10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Add title image into sheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Title');
$objDrawing->setDescription('Title');
$objDrawing->setPath($current_dir . '/title.jpg');
$objDrawing->setCoordinates('C1');
$objDrawing->setOffsetX(20);
$objDrawing->setOffsetY(10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
// Common for all restaurants
$report_number = date('ymdHi');
$start_row = 7;
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];
$driver_select = $_POST['driver'];
if($driver_select == 'all_driver'){
$drivers = array();
$q = mysql_query("SELECT * FROM `food_driver` ORDER BY `dname`");
while($r = mysql_fetch_assoc($q)){
$drivers[] = $r['dname'];
}
}else{
$drivers = array($driver_select);
}
$total_order_count = 0;
$total_cash_received = 0;
$total_cash_paid = 0;
$total_total_delivery = 0;
$total_paypal_count = 0;
$total_paypal_amount = 0;
$total_anz_count = 0;
$total_anz_amount = 0;
$total_online_count = 0;
$total_online_amount = 0;
$total_flower_count = 0;
$total_flower_amount = 0;
$total_other_count = 0;
$total_other_amount = 0;
/******************
Start report generating for each driver
******************/
foreach($drivers as $driver){
$order_query = mysql_query("SELECT order_id FROM food_order WHERE DATE(last_update) BETWEEN '$from_date' AND '$to_date' AND status = 'Delivered by $driver'
UNION
SELECT CONCAT('P', oid) FROM food_phone_order WHERE DATE(`date`) BETWEEN '$from_date' AND '$to_date' AND delivered_by = '$driver'");
if(mysql_num_rows($order_query)==0){
continue;
}
$objPHPExcel->getActiveSheet()
->setCellValue('B'.$start_row, 'DRIVER NAME:')
->setCellValue('B'.($start_row+1), 'Report Generated (Date & Time):');
$objPHPExcel->getActiveSheet()->getStyle("B$start_row:B".($start_row+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()
->setCellValue('C'.$start_row, $driver)
->setCellValue('C'.($start_row+1), date('d/m/Y'));
$objPHPExcel->getActiveSheet()
->setCellValue('G'.$start_row, 'Date From:')
->setCellValue('G'.($start_row+1), 'Date To:');
$objPHPExcel->getActiveSheet()->getStyle("G$start_row:G".($start_row+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()
->setCellValue('H'.$start_row, date('d/m/Y', strtotime($from_date)))
->setCellValue('H'.($start_row+1), date('d/m/Y', strtotime($to_date)));
//Output caption
$objPHPExcel->getActiveSheet()
->setCellValue('A'.($start_row+2), 'Order No.')
->setCellValue('B'.($start_row+2), 'Restaurant / Florist')
//->setCellValue('C'.($start_row+2), 'Name')
->setCellValue('C'.($start_row+2), 'Phone')
->setCellValue('D'.($start_row+2), 'Address')
->setCellValue('E'.($start_row+2), 'Total Order Amount')
->setCellValue('F'.($start_row+2), 'Payment Method')
->setCellValue('G'.($start_row+2), 'Cash Received from Restaurant ')
//->setCellValue('H'.($start_row+2), 'Paid To Restaurant')
->setCellValue('H'.($start_row+2), 'PTR')
//->setCellValue('I'.($start_row+2), 'Delivery Fee')
->setCellValue('I'.($start_row+2), 'Deliv')
->setCellValue('J'.($start_row+2), 'Direct Order? (Y/N)');
$objPHPExcel->getActiveSheet()->getStyle("A$start_row:K".($start_row+2))->applyFromArray($fontBold);
$objPHPExcel->getActiveSheet()->getStyle('A'.($start_row+2).':K'.($start_row+2))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A'.($start_row+2).':K'.($start_row+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
set_border($objPHPExcel, 'A'.($start_row+2).':K'.($start_row+2));
// Output data
$start_row += 3;
$order_count = 0;
$cash_received = 0;
$cash_paid = 0;
$total_delivery = 0;
$paypal_count = 0;
$paypal_amount = 0;
$anz_count = 0;
$anz_amount = 0;
$online_count = 0;
$online_amount = 0;
$flower_count = 0;
$flower_amount = 0;
$other_count = 0;
$other_amount = 0;
while($row = mysql_fetch_assoc($order_query)){
$order_count++;
$order_id = $row['order_id'];
$parts = array();
if(substr($order_id,0,1)=='P'){ //phone orders
$order_id = substr($order_id, 1);
$order_row = mysql_fetch_assoc(mysql_query("SELECT * FROM food_phone_order WHERE oid = '$order_id'"));
$restaurant = get_restaurant($order_row['restaurant_id']);
$restaurant_name = $restaurant['name'];
$customer_name = $order_row['customer_name'];
$customer_phone = $order_row['phone'];
//$customer_address = $order_row['house_no'].' '.$order_row['street1'].' '.$order_row['suburb'];
$customer_address = $order_row['suburb'];
$COD = $order_row['COD'];
$payment_method = $order_row['payment_method'];
if($payment_method=="Split"){
$details = $order_row['details'];
$parts = explode(';', $details);
$payment_details = $payment_method."\n".$parts[0]."\n".$parts[1];
}
$cash_from = $order_row['amount_from_restaurant'];
$cash_received += $cash_from;
$cash_to = $order_row['paid_to_restaurant'];
if($payment_method != 'Customer Paid Restaurant'){
$cash_paid += $cash_to;
}
$delivery = $order_row['delivery_fee'];
$total_delivery += $delivery;
$direct_order = $order_row['direct_order'];
}else{ //web orders
$restaurants = get_restaurants($order_id);
$restaurant_name = implode("\n", $restaurants);
$order_row = mysql_fetch_assoc(mysql_query("SELECT * FROM food_order WHERE order_id = '$order_id'"));
$customer_name = $order_row['shipping_first_name'];
$customer_phone = $order_row['shipping_phone'];
//$customer_address = $order_row['shipping_address'].' '.$order_row['shipping_suburb'];
$customer_address = $order_row['shipping_suburb'];
$COD = $order_row['totalprice'];
$payment_method = $order_row['payment_method'];
if($payment_method=="Split"){
$details = $order_row['details'];
$parts = explode(';', $details);
$payment_details = $payment_method."\n".$parts[0]."\n".$parts[1];
}
if($payment_method == 'Hungerline Online'){
//Customer pays by PayPal, so COD does not include 6% surcharge
$COD = $COD/1.06;
}
$cash_from = $cash_to = 0;
$q2 = mysql_query("SELECT amount_from_restaurant, paid_to_restaurant FROM food_order_restaurant WHERE order_id = $order_id");
while($r2 = mysql_fetch_assoc($q2)){
$cash_from += $r2['amount_from_restaurant'];
$cash_to += $r2['paid_to_restaurant'];
}
$cash_received += $cash_from;
if($payment_method != 'Customer Paid Restaurant'){
$cash_paid += $cash_to;
}
$delivery = get_delivery_fee($order_id);
$total_delivery += $delivery;
$direct_order = 'Y'; //through website should be direct
}
if($payment_method=='PayPal Invoice'){
$paypal_count++;
$paypal_amount += $COD;
}elseif($payment_method=='ANZ FastPay'){
$anz_count++;
$anz_amount += $COD;
}elseif($payment_method=='Hungerline Online'){
$online_count++;
$online_amount += $COD;
}elseif($payment_method=='Flowers to invoice'){
$flower_count++;
$flower_amount += $COD;
//Do not know what is this calculation, but Hass said it should be!
$flower_amount += ($delivery - $cash_from);
}elseif($payment_method=='Other Invoice'){
$other_count++;
$other_amount += $COD;
}elseif($payment_method=='Cash'){
$cash_received += $COD;
}elseif($payment_method=='Split'){
foreach($parts as $part){
$tokens = explode(':',$part);
if($tokens[0]=='PayPal Invoice'){
$paypal_count++;
$paypal_amount += $tokens[1];
}elseif($tokens[0]=='ANZ FastPay'){
$anz_count++;
$anz_amount += $tokens[1];
}elseif($tokens[0]=='Hungerline Online'){
$online_count++;
$online_amount += $tokens[1];
}elseif($tokens[0]=='Flowers to invoice'){
$flower_count++;
$flower_amount += $tokens[1];
}elseif($tokens[0]=='Other Invoice'){
$other_count++;
$other_amount += $tokens[1];
}elseif($tokens[0]=='Cash'){
$cash_received += $tokens[1];
}
}
}
// Update value to Excel row
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$start_row, $order_id)
->setCellValue('B'.$start_row, $restaurant_name)
//->setCellValue('C'.$start_row, $customer_name)
->setCellValue('C'.$start_row, $customer_phone)
->setCellValue('D'.$start_row, $customer_address)
->setCellValue('E'.$start_row, $COD)
->setCellValue('F'.$start_row, (($payment_method=='Split')?$payment_details:$payment_method))
->setCellValue('G'.$start_row, $cash_from)
->setCellValue('H'.$start_row, $cash_to)
->setCellValue('I'.$start_row, $delivery)
->setCellValue('J'.$start_row, $direct_order);
$objPHPExcel->getActiveSheet()->getStyle('B'.$start_row)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('E'.$start_row.':'.'I'.$start_row)->getNumberFormat()->setFormatCode('#,##0.00');
$start_row++;
}
//Summary info
$start_row += 2;
$objPHPExcel->getActiveSheet()->getStyle('C'.($start_row+1).':'.'G'.($start_row+3))->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('E'.($start_row+6).':'.'I'.($start_row+10))->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$start_row, "Total Number of Deliveries:")
->setCellValue('C'.$start_row, $order_count)
->setCellValue('A'.($start_row+1), "Total Cash Received:")
->setCellValue('C'.($start_row+1), $cash_received)
->setCellValue('A'.($start_row+2), "Total Cash Paid Out:")
->setCellValue('C'.($start_row+2), $cash_paid)
->setCellValue('A'.($start_row+3), "Cash Balance:")
->setCellValue('C'.($start_row+3), $cash_received-$cash_paid)
->setCellValue('E'.($start_row), "Total Delivery Fees INC GST:")
->setCellValue('G'.($start_row), $total_delivery)
->setCellValue('E'.($start_row+1), "Total Delivery Fees EX GST:")
->setCellValue('G'.($start_row+1), (($total_delivery/11)*10))
->setCellValue('E'.($start_row+2), "Driver Fees EX GST :")
->setCellValue('G'.($start_row+2), (($total_delivery/11)*10)*0.625);
$start_row += 6;
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$start_row, "PayPal Invoices to send:")
->setCellValue('C'.$start_row, $paypal_count)
->setCellValue('A'.($start_row+1), "Number of ANZ FastPay transactions:")
->setCellValue('C'.($start_row+1), $anz_count)
->setCellValue('A'.($start_row+2), "Number of online payments through Website:")
->setCellValue('C'.($start_row+2), $online_count)
->setCellValue('A'.($start_row+3), "Number of Flower deliveries to invoice:")
->setCellValue('C'.($start_row+3), $flower_count)
->setCellValue('A'.($start_row+4), "Number of other deliveries to invoice:")
->setCellValue('C'.($start_row+4), $other_count)
->setCellValue('D'.$start_row, "Amount to invoice:")
->setCellValue('E'.$start_row, $paypal_amount)
->setCellValue('D'.($start_row+1), "Amount to invoice:")
->setCellValue('E'.($start_row+1), $anz_amount)
->setCellValue('D'.($start_row+2), "Amount to invoice:")
->setCellValue('E'.($start_row+2), $online_amount)
->setCellValue('D'.($start_row+3), "Amount to invoice:")
->setCellValue('E'.($start_row+3), $flower_amount)
->setCellValue('D'.($start_row+4), "Amount to invoice:")
->setCellValue('E'.($start_row+4), $other_amount)
->setCellValue('G'.$start_row, "Plus 6% surcharge=")
->setCellValue('I'.$start_row, $paypal_amount*1.06)
->setCellValue('G'.($start_row+1), "Plus 3% surcharge=")
->setCellValue('I'.($start_row+1), $anz_amount*1.03)
->setCellValue('G'.($start_row+2), "Plus 6% surcharge=")
->setCellValue('I'.($start_row+2), $online_amount*1.06);
$start_row += 9; //Some spaces for the next driver
//Grand summary
$total_order_count += $order_count;
$total_cash_received += $cash_received;
$total_cash_paid += $cash_paid;
$total_total_delivery += $total_delivery;
$total_paypal_count += $paypal_count;
$total_paypal_amount += $paypal_amount;
$total_anz_count += $anz_count;
$total_anz_amount += $anz_amount;
$total_online_count += $online_count;
$total_online_amount += $online_amount;
$total_flower_count += $flower_count;
$total_flower_amount += $flower_amount;
$total_other_count += $other_count;
$total_other_amount += $other_amount;
}
/******************
End report generating for each driver
******************/
//Grand summary info
$objPHPExcel->getActiveSheet()->setCellValue('B'.$start_row, "TOTALS");
$start_row += 2;
$objPHPExcel->getActiveSheet()->getStyle('C'.($start_row+1).':'.'G'.($start_row+3))->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('E'.($start_row+6).':'.'I'.($start_row+10))->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$start_row, "Total Number of Deliveries:")
->setCellValue('C'.$start_row, $total_order_count)
->setCellValue('A'.($start_row+1), "Total Cash Received:")
->setCellValue('C'.($start_row+1), $total_cash_received)
->setCellValue('A'.($start_row+2), "Total Cash Paid Out:")
->setCellValue('C'.($start_row+2), $total_cash_paid)
->setCellValue('A'.($start_row+3), "Cash Balance:")
->setCellValue('C'.($start_row+3), $total_cash_received-$total_cash_paid)
->setCellValue('E'.($start_row+1), "Total Delivery Fees:")
->setCellValue('G'.($start_row+1), $total_total_delivery)
->setCellValue('E'.($start_row+2), "Total Wages (62.5% of deliv fees):")
->setCellValue('G'.($start_row+2), (($total_total_delivery/11)*10)*0.625);
$start_row += 6;
$objPHPExcel->getActiveSheet()
->setCellValue('A'.$start_row, "PayPal Invoices to send:")
->setCellValue('C'.$start_row, $total_paypal_count)
->setCellValue('A'.($start_row+1), "Number of ANZ FastPay transactions:")
->setCellValue('C'.($start_row+1), $total_anz_count)
->setCellValue('A'.($start_row+2), "Number of online payments through Website:")
->setCellValue('C'.($start_row+2), $total_online_count)
->setCellValue('A'.($start_row+3), "Number of Flower deliveries to invoice:")
->setCellValue('C'.($start_row+3), $total_flower_count)
->setCellValue('A'.($start_row+4), "Number of other deliveries to invoice:")
->setCellValue('C'.($start_row+4), $total_other_count)
->setCellValue('D'.$start_row, "Amount to invoice:")
->setCellValue('E'.$start_row, $total_paypal_amount)
->setCellValue('D'.($start_row+1), "Amount to invoice:")
->setCellValue('E'.($start_row+1), $total_anz_amount)
->setCellValue('D'.($start_row+2), "Amount to invoice:")
->setCellValue('E'.($start_row+2), $total_online_amount)
->setCellValue('D'.($start_row+3), "Amount to invoice:")
->setCellValue('E'.($start_row+3), $total_flower_amount)
->setCellValue('D'.($start_row+4), "Amount to invoice:")
->setCellValue('E'.($start_row+4), $total_other_amount)
->setCellValue('G'.$start_row, "Plus 6% surcharge=")
->setCellValue('I'.$start_row, $total_paypal_amount*1.06)
->setCellValue('G'.($start_row+1), "Plus 3% surcharge=")
->setCellValue('I'.($start_row+1), $total_anz_amount*1.03)
->setCellValue('G'.($start_row+2), "Plus 6% surcharge=")
->setCellValue('I'.($start_row+2), $total_online_amount*1.06);
//wrap text for payment method column
$objPHPExcel->getActiveSheet()->getStyle('F1:F'.$start_row)->getAlignment()->setWrapText(true);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// Redirect output to a client's web browser (Excel2007)
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="driver_report.xlsx"');
$objWriter->save('php://output');
exit;
ThANK YOU