Hi!
Im creating individual excel file base on the duedate and client name of a reservation and sending it automatically on that date.but im having a trouble dispalying the SUM/ Grandtotal of each excel summary that i created. the grandtotal inside my while loop is also incrementing. i dont now what i am doing wrong.
Example
for today i have 4 Client that has a duedate today. and the script automatically generated the excel file summary of each client.
Client 1. 7 STONES
Client 2. Your Travel Planner
Client 3. ZOEMIE
And the Last Zulu Plaza . Notice that this one has no problem dispalying trhe Grandtotal
You can see that from the First excel file to the 3rd it has a duplicate Grandtotal
This is the Script that i used creating the excel files.
<?php
error_reporting(E_ALL);
include '../includes/pdo_config.php';
include '../includes/phpexcel_config.php';
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Asia/Manila');
/** PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
$today = date('Y-m-d');
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/Billing.xls");
try {
$getname=$con->prepare("SELECT msicph_globalsouthwest.accounts_info.name,
msicph_globalsouthwest.accounts_info.email
FROM msicph_globalsouthwest.accounts_info
WHERE msicph_globalsouthwest.accounts_info.name
IN ( SELECT msicph_southwest.reservations.resfor
FROM msicph_southwest.reservations
WHERE msicph_southwest.reservations.`duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY))");
$getname->execute();
$i = 0;
ob_start();
while ($dataname = $getname->fetch(PDO::FETCH_ASSOC)) {
$resfor=$dataname['name'];
$email=$dataname['email'];
$stmts = $con->prepare("SELECT * FROM reservations WHERE resfor='$resfor' and
`duedate` = DATE_SUB(CURDATE(), INTERVAL - 3 DAY)");
$stmts->execute();
$objPHPExcel->getActiveSheet()->setCellValue('I1', PHPExcel_Shared_Date::PHPToExcel(time()));
$baseRow = 5;
$run = 0; // control variable to set a pause every 20 messages
ob_start();
foreach($stmts as $r => $dataRow) {
$billname=$dataRow['resfor'];
$rental_date=$dataRow['rental_date'];
$rental_time=$dataRow['rental_time'];
$row = $baseRow + $r;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $dataRow['resnum'])
->setCellValue('B'.$row, $dataRow['name'])
->setCellValue('C'.$row, date('M d, Y', strtotime($dataRow['rental_date']))." / ".$dataRow['rental_time'])
->setCellValue('D'.$row, $dataRow['service_type'])
->setCellValue('E'.$row, $dataRow['modetrans'])
->setCellValue('F'.$row, $dataRow['from'])
->setCellValue('G'.$row, $dataRow['to'])
->setCellValue('H'.$row, date('M d, Y', strtotime($dataRow['duedate'])))
->setCellValue('I'.$row, $dataRow['total_rates']);
if ($run >= 2){
sleep(4);
$run = 0;
}
} //END OF FOREACH
ob_end_flush();
$add=$r + 8;
$last=$r + 5;
$basecell="I".$add;
$basecells="H".$add;
// DISPLAY THE GRANDTOTAL
$objPHPExcel->getActiveSheet()->setCellValue($basecells,'Grand total');
$objPHPExcel->getActiveSheet()->setCellValue($basecell,'=SUM(I4'.':I'.$last.')');
$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
echo $objWriter->save(str_replace(__FILE__,'excelfiles/'.$resfor.'.xls',__FILE__));
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
$path='../toexcel/excelfiles/'.$resfor.'.xls';
$conn->beginTransaction();
$conn->exec("INSERT INTO Billing_info (name,email, path_to,sdate)
VALUES ('$resfor','$email','$path','$today' )");
$conn->commit();
if ($i >= 2){
sleep(4);
$i = 0;
}
} //end of while
ob_end_flush();
$conn= null;
} // TRY
catch(PDOException $e) {
//echo "Error: " . $e->getMessage();
}
$con= null;
Thanks
Neil
