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

1.jpg

Client 2. Your Travel Planner

2.jpg

Client 3. ZOEMIE

3.jpg

And the Last Zulu Plaza . Notice that this one has no problem dispalying trhe Grandtotal

4.jpg

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

Anyone has an idea?

Before line number 100 that is before ending while loop break the loop

break;
} //end of while

OR
fix the while loop query code (LINE 21), it is giving more then one record i guess.
OR
If you generating mulitple files, then intialise excel object inside while loop and after generating file, destroy it before while loop ends.

Hi! thank you for giving your inputs on this matter.

To answer you on

Before line number 100 that is before ending while loop break the loop

break;
} //end of while

I tried adding the break ; but it only generate 1 exel file in contrast with the record

fix the while loop query code (LINE 21), it is giving more then one record i guess.

Yes it is giving me 1 or more records which is correct. coz for that day there are several client that have a billing duedate.

for

If you generating mulitple files, then intialise excel object inside while loop and after generating file, destroy it before while loop ends.

Yes and No. it depends on the client duedate whether it will create 1 or more excel file.
Also what do you mean by ?

intialise excel object inside while loop

Do i need to put line 17 and 18 inside my while loop?

Thanks Neil

`

Yes exactly put line 17-18 inside while loop.

WOW thats it? holycow im stuck on that for 2 days and you answered it very casually with

Yes exactly put line 17-18 inside while loop.

Where are you d past days men?heheheh

ITs WORKING FINE NOW!!!

THANKS ALOT

NEIL:)

commented: Tell your friends :) +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.