Report

Oct 4, 2010 at 7:52 PM

Can someone please take a look and see if there is any way to improve this - it is taking a while to run and sometimes timing out

 

<?php


/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors',TRUE);
ini_set("memory_limit","200M");

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once '../../includes/PHPExcel.php';
include ("../../../config.php");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("David Morrison")
							 ->setLastModifiedBy("David Morrison")
							 ->setTitle("Bacs and Suspense Master")
							 ->setSubject("Bacs and Suspense Master")
							 ->setDescription("Contains a list of all allocated and unallocated payments.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Master Book");

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'SOM'");

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

// Do the sql query and output
$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}
$objPHPExcel->getActiveSheet()->setTitle('SOM');

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet(1)->setTitle('CHEQUES');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'CHEQUES'");

$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;

while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet(2)->setTitle('BACS');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'BACS'");

$objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;

while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(2)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet(3)->setTitle('PO');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'PO'");

$objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(3)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(4);
$objPHPExcel->getActiveSheet(4)->setTitle('CASH');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'CASH'");

$objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(4)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(5);
$objPHPExcel->getActiveSheet(5)->setTitle('ALLPAY');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'ALLPAY'");

$objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(5)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}

// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(6);
$objPHPExcel->getActiveSheet(6)->setTitle('CARD');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'UNALLOCATED' AND companyNo = '1' AND type = 'CARD'");

$objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(6)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1));
$i = $i + 1;
}


// Create new sheet and output
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(7);
$objPHPExcel->getActiveSheet(7)->setTitle('ALLOCATED');

$repInfo = mysql_query("SELECT * FROM unallData WHERE status = 'ALLOCATED' AND companyNo = '1' AND order by type");


$objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue("A1", 'Date')
            ->setCellValue("B1", 'Notes')
            ->setCellValue("C1", 'Amount')
			->setCellValue("D1", 'Collector')
			->setCellValue("E1", 'Allocated to')
			->setCellValue("F1", 'Type')
			->setCellValue("G1", 'Date allocation was requested');

$i =2;
while ($info = mysql_fetch_array($repInfo, MYSQL_ASSOC)){
$repInfo2 = mysql_query("SELECT * FROM susdata WHERE unallDataID = '{$info['id']}' AND companyNo = '1'");
$info2 = mysql_fetch_array($repInfo2, MYSQL_ASSOC);

$amount = $info['amount'];
$objPHPExcel->setActiveSheetIndex(7)
            ->setCellValue("A$i", "{$info['date']}")
            ->setCellValue("B$i", "{$info['notes']}")
            ->setCellValue("C$i", substr("$amount",1))
			->setCellValue("D$i", "{$info2['yourname']}")
			->setCellValue("E$i", "{$info2['allocate_to']}")
			->setCellValue("F$i", "{$info2['type']}")
			->setCellValue("G$i", "{$info2['date_submitted']}");
$i = $i + 1;
}

// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Suspense_Master.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');


exit;
?>