/**
* Format a recordset
*
* @param Garp_Model $model
* @param array $rowset
* @return string
*/
public function format(Garp_Model $model, array $rowset)
{
$phpexcel = new PHPExcel();
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// set metadata
$props = $phpexcel->getProperties();
if (Garp_Auth::getInstance()->isLoggedIn()) {
$userData = Garp_Auth::getInstance()->getUserData();
$bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap');
if ($bootstrap) {
$view = $bootstrap->getResource('view');
$userName = $view->fullName($userData);
$props->setCreator($userName)->setLastModifiedBy($userName);
}
}
$props->setTitle('Garp content export – ' . $model->getName());
if (count($rowset)) {
$this->_addContent($phpexcel, $model, $rowset);
}
/**
* Hm, PHPExcel seems to only be able to write to a file (instead of returning
* an XLS binary string). Therefore, we save a temporary file, read its contents
* and return those, after which we unlink the temp file.
*/
$tmpFileName = APPLICATION_PATH . '/data/logs/tmp.xls';
$writer = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
$writer->save($tmpFileName);
$contents = file_get_contents($tmpFileName);
unlink($tmpFileName);
return $contents;
}
public function __construct($config = array())
{
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
if (isset($config['cacheMethod'])) {
$this->_cacheMethod = $config['cacheMethod'];
}
$this->_setCache();
parent::__construct();
}
/**
* Return the template
* @param string $templateFileName
* @return PHPExcel
*/
protected function getTemplate($templateFileName)
{
$startTime = microtime(true);
require_once __DIR__ . '/PHPExcel/PHPExcel.php';
require_once __DIR__ . '/PHPExcel/PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
$this->logger->log(LOG_INFO, 'getTemplate()');
$this->logger->step(R3_PAES_READ_TEMPLATE, null, null);
$this->xls = PHPExcel_IOFactory::load($templateFileName);
$this->logger->log(LOG_INFO, sprintf('getTemplate() - Done [%.2fsec]', microtime(true) - $startTime));
}
/**
* Constructor
*
* @param \Cake\Network\Request $request Request instance.
* @param \Cake\Network\Response $response Response instance.
* @param \Cake\Event\EventManager $eventManager Event manager instance.
* @param array $viewOptions View options. See View::$_passedVars for list of
* options which get set as class properties.
*
* @throws \Cake\Core\Exception\Exception
*/
public function __construct(Request $request = null, Response $response = null, EventManager $eventManager = null, array $viewOptions = [])
{
parent::__construct($request, $response, $eventManager, $viewOptions);
if (isset($viewOptions['name']) && $viewOptions['name'] == 'Error') {
$this->subDir = null;
$this->layoutPath = null;
$response->type('html');
return;
}
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$this->PhpExcel = new \PHPExcel();
}
function file_to_obj_php_excel($inputFileName)
{
$CI =& get_instance();
PHPExcel_Shared_File::setUseUploadTempDirectory(true);
if ($CI->config->item('spreadsheet_format') == 'XLSX') {
$objReader = new PHPExcel_Reader_Excel2007();
} else {
$objReader = new PHPExcel_Reader_CSV();
PHPExcel_Cell::setValueBinder(new TextValueBinder());
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
return $objPHPExcel;
}
/**
* Constructor
*
* @param \Cake\Network\Request $request Request instance.
* @param \Cake\Network\Response $response Response instance.
* @param \Cake\Event\EventManager $eventManager Event manager instance.
* @param array $viewOptions View options. See View::$_passedVars for list of
* options which get set as class properties.
*
* @throws \Cake\Core\Exception\Exception
*/
public function __construct(Request $request = null, Response $response = null, EventManager $eventManager = null, array $viewOptions = [])
{
parent::__construct($request, $response, $eventManager, $viewOptions);
if (isset($viewOptions['templatePath']) && $viewOptions['templatePath'] == 'Error') {
$this->layoutPath = null;
$this->subDir = null;
$response->type('html');
return;
}
// intitialize PHPExcel-Object
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$this->PHPExcel = new \PHPExcel();
$this->currentSheetIndex = 0;
}
/**
* reads a file PHPExcel can understand and converts a contained worksheet into an array
* which can be used to build entities. If the File contains more than one worksheet and it is not named like the Controller
* you have to provide the name of the workshhet to load in the options array.
* If you set $options['append'] to true, the primary key will be deleted.
* @todo Find a way to make to handle primary keys anmed other than id.
*
* @param string $file name of Excel-File with full path. Must be of a readable Filetype (xls, xlsx, csv, ods)
* @param array $options Override Worksheet name, set append Mode
* @return array . The Array has the same structure as provided by request->data
* @throws MissingTableClassException
*/
public function prepareEntityData($file = null, array $options = [])
{
/** load and configure PHPExcelReader * */
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$fileType = \PHPExcel_IOFactory::identify($file);
$PhpExcelReader = \PHPExcel_IOFactory::createReader($fileType);
$PhpExcelReader->setReadDataOnly(true);
if ($fileType !== 'CSV') {
// csv-files have only one 'worksheet'
/** identify worksheets in file * */
$worksheets = $PhpExcelReader->listWorksheetNames($file);
$worksheetToLoad = null;
if (count($worksheets) === 1) {
$worksheetToLoad = $worksheets[0];
//first option: if there is only one worksheet, use it
} elseif (isset($options['worksheet'])) {
$worksheetToLoad = $options['worksheet'];
//second option: desired worksheet was provided as option
} else {
$worksheetToLoad = $this->_registry->getController()->name;
//last option: try to load worksheet with the name of current controller
}
if (!in_array($worksheetToLoad, $worksheets)) {
throw new MissingTableClassException(__('No proper named worksheet found'));
}
/** load the sheet and convert data to an array */
$PhpExcelReader->setLoadSheetsOnly($worksheetToLoad);
}
$PhpExcel = $PhpExcelReader->load($file);
$data = $PhpExcel->getSheet(0)->toArray();
/** convert data for building entities */
$result = [];
$properties = array_shift($data);
//first row columns are the properties
foreach ($data as $row) {
$record = array_combine($properties, $row);
if (isset($record['modified'])) {
unset($record['modified']);
}
if (isset($options['type']) && $options['type'] == 'append' && isset($record['id'])) {
unset($record['id']);
}
$result[] = $record;
}
/** log in debug mode */
$this->log(count($result) . ' records were extracted from File ' . $file, 'debug');
return $result;
}
public function report($dates, $officials)
{
$results = new AvailReporterResults();
// For wrapping text
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$this->wb = $wb = new \PHPExcel();
$ws = $wb->getSheet();
$ws->setTitle('RefAvail');
$ws->getCell('A1')->setValue('Referee Name');
$ws->getCell('B1')->setValue('Referee Info');
$ws->getColumnDimension('A')->setWidth(20);
$ws->getColumnDimension('B')->setWidth(20);
$col = 'C';
foreach ($dates as $date) {
$dt = \DateTime::createFromFormat('Y-m-d', $date);
$ws->getCell($col . '1')->setValue($dt->format('D M d'));
$ws->getColumnDimension($col)->setWidth(25);
$col++;
}
$row = 2;
foreach ($officials as $official) {
$ws->getCell('A' . $row)->setValue($official['name']);
$info = sprintf("F: %s\n%s\n%s\nR: %s", $official['city'], $official['cell'], $official['home'], $official['rank']);
$ws->getCell('B' . $row)->setValue($info);
$col = 'C';
foreach ($dates as $date) {
$cr = $col . $row;
$avail = implode("\n", $official['avail'][$date]);
$ws->getCell($cr)->setValue($avail);
if ($avail === 'Blocked ALL DAY') {
$style = $ws->getStyle($cr);
$fill = $style->getFill();
$fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('FFFF6666');
}
if ($avail === 'Open All Day') {
$style = $ws->getStyle($cr);
$fill = $style->getFill();
$fill->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('FFCCFFCC');
}
$col++;
}
$row++;
}
$wb->setActiveSheetIndex(0);
return $results;
}
/**
* Set data to active sheet.
*
* @param array $data
* @param array $title
* @param PHPExcel_Chart $charts
*/
public function setData($data, $title = array(), $charts = null)
{
if (!empty($title)) {
array_unshift($data, $title);
}
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_RCUPValueBinder());
$this->objPHPExcel->getActiveSheet()->fromArray($data);
if (!empty($charts)) {
if (!is_array($charts)) {
$charts = array($charts);
}
foreach ($charts as $chart) {
$this->objPHPExcel->getActiveSheet()->addChart($chart);
}
}
}
public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null)
{
// we use our own error handler
global $registry;
$registry = $this->registry;
set_error_handler('error_handler_for_export_import', E_ALL);
register_shutdown_function('fatal_error_shutdown_handler_for_export_import');
// Use the PHPExcel package from http://phpexcel.codeplex.com/
$cwd = getcwd();
chdir(DIR_SYSTEM . 'PHPExcel');
require_once 'Classes/PHPExcel.php';
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder());
chdir($cwd);
// find out whether all data is to be downloaded
$all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id);
// Memory Optimization
if ($this->config->get('export_import_settings_use_export_cache')) {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '16MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
}
try {
// set appropriate timeout limit
set_time_limit(1800);
$languages = $this->getLanguages();
$default_language_id = $this->getDefaultLanguageId();
// create a new workbook
$workbook = new PHPExcel();
// set some default styles
$workbook->getDefaultStyle()->getFont()->setName('Arial');
$workbook->getDefaultStyle()->getFont()->setSize(10);
//$workbook->getDefaultStyle()->getAlignment()->setIndent(0.5);
$workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
// pre-define some commonly used styles
$box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F0F0F0')));
$text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT));
$price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
$weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
// create the worksheets
$worksheet_index = 0;
switch ($export_type) {
case 'c':
// creating the Categories worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Categories');
$this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the CategoryFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('CategoryFilters');
$this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'p':
// creating the Products worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Products');
$this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $price_format, $box_format, $weight_format, $text_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the AdditionalImages worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AdditionalImages');
$this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Specials worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Specials');
$this->populateSpecialsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Discounts worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Discounts');
$this->populateDiscountsWorksheet($worksheet, $default_language_id, $price_format, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Rewards worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Rewards');
$this->populateRewardsWorksheet($worksheet, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductOptions worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptions');
$this->populateProductOptionsWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductOptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptionValues');
$this->populateProductOptionValuesWorksheet($worksheet, $price_format, $box_format, $weight_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductAttributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductAttributes');
$this->populateProductAttributesWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the ProductFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductFilters');
$this->populateProductFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'o':
// creating the Options worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Options');
$this->populateOptionsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the OptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('OptionValues');
$this->populateOptionValuesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'a':
// creating the AttributeGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AttributeGroups');
$this->populateAttributeGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Attributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Attributes');
$this->populateAttributesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
// creating the FilterGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('FilterGroups');
$this->populateFilterGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// creating the Filters worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Filters');
$this->populateFiltersWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
default:
break;
}
$workbook->setActiveSheetIndex(0);
// redirect output to client browser
$datetime = date('Y-m-d');
switch ($export_type) {
case 'c':
$filename = 'categories-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-{$offset}";
} else {
if (isset($min_id)) {
$filename .= "-start-{$min_id}";
}
}
if (isset($rows)) {
$filename .= "-rows-{$rows}";
} else {
if (isset($max_id)) {
$filename .= "-end-{$max_id}";
}
}
}
$filename .= '.xlsx';
break;
case 'p':
$filename = 'products-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-{$offset}";
} else {
if (isset($min_id)) {
$filename .= "-start-{$min_id}";
}
}
if (isset($rows)) {
$filename .= "-rows-{$rows}";
} else {
if (isset($max_id)) {
$filename .= "-end-{$max_id}";
}
}
}
$filename .= '.xlsx';
break;
case 'o':
$filename = 'options-' . $datetime . '.xlsx';
break;
case 'a':
$filename = 'attributes-' . $datetime . '.xlsx';
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
$filename = 'filters-' . $datetime . '.xlsx';
break;
default:
$filename = $datetime . '.xlsx';
break;
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
// Clear the spreadsheet caches
$this->clearSpreadsheetCache();
exit;
} catch (Exception $e) {
$errstr = $e->getMessage();
$errline = $e->getLine();
$errfile = $e->getFile();
$errno = $e->getCode();
$this->session->data['export_import_error'] = array('errstr' => $errstr, 'errno' => $errno, 'errfile' => $errfile, 'errline' => $errline);
if ($this->config->get('config_error_log')) {
$this->log->write('PHP ' . get_class($e) . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
}
return;
}
}
/**
* Returns an initialized PHPExcel object
*
* @return \PHPExcel
*/
private function getPhpExcelObject()
{
require_once ROOT . DS . 'vendor' . DS . 'phpoffice' . DS . 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php';
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
return $objPHPExcel;
}
function change_xlsx($file, $docname, $leadData, $ddtype)
{
//@file_put_contents("now-wc.txt","point2:change_xlsx:".$docname,FILE_APPEND);
if (is_file('files/' . $docname)) {
unlink('files/' . $docname);
}
//if (!copy($file, 'files/'.$docname)) { } else {
if (!copy($file, $docname)) {
} else {
//продолжаем обработку
//работа с xlsx
ini_set('include_path', ini_get('include_path') . ';../PHPExcel-1.8/Classes/');
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
require_once './PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php';
require_once './PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
require_once './PHPExcel-1.8/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
require_once './PHPExcel-1.8/Classes/PHPExcel/Writer/IWriter.php';
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$filenamestr = $docname;
//$filenamestr = "invoice1.xlsx";
$objPHPExcel = $objReader->load($filenamestr);
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// Add some data
//insert row
//$objPHPExcel->getActiveSheet()->insertNewRowBefore(7, 2);
//объединить ячейки
//$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->setActiveSheetIndex(0);
$dname = "";
if ($ddtype == 'tradeact') {
$dname = 'Акт';
} else {
$dname = 'Счет';
}
$strB11val = $dname . " №" . $leadData['ddnumber'] . ' от ' . $leadData['ddate'];
$objPHPExcel->getActiveSheet()->SetCellValue('B11', $strB11val);
$objPHPExcel->getActiveSheet()->SetCellValue('G17', "" . $leadData['recieve']);
$objPHPExcel->getActiveSheet()->SetCellValue('G19', "" . $leadData['recieve']);
$mainarr = explode("!-!", $leadData['alltoinvioce']);
$i = 0;
$inum = 22;
foreach ($mainarr as $elementarr) {
if ("" . $elementarr === "") {
} else {
$inum = 22 + $i;
$objPHPExcel->getActiveSheet()->insertNewRowBefore($inum, 1);
//1 row before22
$strB = 'B' . $inum;
$strBVal = "" . ($i + 1);
$strC = 'C' . $inum;
$strCVal = "" . $elementarr;
$strW = 'W' . $inum;
$strX = 'X' . $inum;
$strY = 'Y' . $inum;
$strZ = 'Z' . $inum;
$strAA = 'AA' . $inum;
$objPHPExcel->getActiveSheet()->SetCellValue($strB, $strBVal);
$objPHPExcel->getActiveSheet()->SetCellValue($strC, $strCVal);
$objPHPExcel->getActiveSheet()->SetCellValue($strX, '');
$objPHPExcel->getActiveSheet()->SetCellValue($strY, '');
$objPHPExcel->getActiveSheet()->SetCellValue($strZ, '');
$objPHPExcel->getActiveSheet()->SetCellValue($strAA, '');
//слипляем 2 строчки
$objPHPExcel->getActiveSheet()->mergeCells($strC . ':' . $strW);
$i++;
}
}
//стили таблички
$styleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => 'FFFF0000'))));
$stylerange = 'B23:AA' . $i;
$objPHPExcel->getActiveSheet()->getStyle($stylerange)->applyFromArray($styleArray);
//стили таблички ===============
$inum = 22 + $i + 4;
$strB = 'B' . $inum;
$objPHPExcel->getActiveSheet()->SetCellValue($strB, 'Всего наименований ' . $i . ', на сумму');
$inum = 22 + $i + 5;
$strB = 'B' . $inum;
$objPHPExcel->getActiveSheet()->SetCellValue($strB, '');
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('invoice');
// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("files/" . $docname);
unset($objPHPExcel);
unset($objReader);
if (is_file($docname)) {
unlink($docname);
}
}
}
public static function setColWidth(&$objPHPExcel, $width, $col, $sheet = 0)
{
\PHPExcel_Cell::setValueBinder(new \PHPExcel_Cell_AdvancedValueBinder());
$objPHPExcel->setActiveSheetIndex($sheet)->getColumnDimension($col)->setWidth($width);
}
/**
* function setValueBinder() = Set the 'default' value binder to be used by objects generated
* whose file handler was instantiated by this factory class
* Cyril Ogana - 2012-07-17
* @param string $binderName This is the name of the value binder to activate
* @return void
* @access public
*/
public function setValueBinder($binderName = 'default')
{
if (!is_string($binderName) || !array_key_exists($binderName, self::$valueBinders)) {
return false;
}
\PHPExcel_Cell::setValueBinder(new self::$valueBinders[$binderName]());
self::$valueBinder = $binderName;
//append binder name to static property
return true;
}
/**
* Create sheet from query and dump named file to browser.
* @param $query the query.
*/
function writeXlsx($query)
{
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
$objPHPExcel = new PHPExcel();
if (!isset($this->rowParser)) {
$this->rowParser = new DefaultRowParser();
}
$objPHPExcel->getProperties()->setCreator($this->creator);
$objPHPExcel->getProperties()->setLastModifiedBy($this->author);
$objPHPExcel->getProperties()->setTitle($this->title);
$objPHPExcel->getProperties()->setSubject($this->subject);
$objPHPExcel->getProperties()->setDescription($this->description);
$objPHPExcel->getProperties()->setKeywords($this->keywords);
$objPHPExcel->getProperties()->setCategory($this->catagory);
global $ADODB_FETCH_MODE;
$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
$resultSet = $this->dbConn->Execute($query);
if ($resultSet === false) {
die("<br>Cannot get spreadsheet data with <pre>" . $query . "</pre> reason " . $this->dbConn->ErrorMsg() . "<br>");
}
//echo $query;
//$colcount = $resultSet->FieldCount();
// start writing in 3rd row, top isf for title and link.
$row = 3;
$this->tableHeader = $this->rowParser->parseToTableHeader($resultSet);
$headCount = count($this->tableHeader);
$headerStyles = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FFC0C0C0')));
for ($i = 0; $i < $headCount; $i++) {
$name = $this->tableHeader[$i];
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, $row, $name);
$coor = XLSWriter::cellCoordinate($i, $row);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
}
$row++;
// get types
$this->columnTypes = $this->rowParser->parseTypes($resultSet);
$XlsTypes = array();
//error_log('there are ' . count($this->columnTypes) . ' types from db =' . print_r($this->columnTypes, true), 0);
for ($i = 0; $i < count($this->columnTypes); $i++) {
$ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
//error_log("found type = {$this->columnTypes[$i]} for column {$i}", 0);
switch ($this->columnTypes[$i]) {
case 'char':
case 'bpchar':
case 'varchar':
case 'text':
case 'date':
$ftype = PHPExcel_Cell_DataType::TYPE_STRING;
break;
case 'int2':
case 'int4':
case 'int8':
case '_numeric':
case 'numeric':
case 'float8':
$ftype = PHPExcel_Cell_DataType::TYPE_NUMERIC;
break;
default:
$ftype = PHPExcel_Cell_DataType::TYPE_STRING;
break;
}
$XlsTypes[] = $ftype;
}
$cellStyleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'color' => array('argb' => 'FF0000')));
$oldValue = '';
if ($this->firstWeightColumn > 0) {
// add weights row
$this->weigthsRow = $row;
$coor = XLSWriter::cellCoordinate($this->firstWeightColumn - 1, $row);
$objPHPExcel->getActiveSheet()->setCellValue($coor, 'Weights', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
$weightSum = 0;
$w = 0;
${$weightLast} = count($this->weights) - 1;
for (; $w < count($this->weights); $w++) {
$coor = XLSWriter::cellCoordinate($this->firstWeightColumn + $w, $row);
$weightSum += $this->weights[$w];
$objPHPExcel->getActiveSheet()->setCellValue($coor, $this->weights[$w], PHPExcel_Cell_DataType::TYPE_NUMERIC);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
}
$coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row);
$wBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row);
$wEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + ${$weightLast}, $row);
$formula = "=SUM({$wBegin}:{$wEnd})";
$objPHPExcel->getActiveSheet()->setCellValue($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
$coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row - 1);
$objPHPExcel->getActiveSheet()->setCellValue($coor, 'Total WT', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($headerStyles);
$row++;
}
while (!$resultSet->EOF) {
$rowData = $this->rowParser->parse($resultSet);
$headCount = count($this->tableHeader);
//$resultSet->FieldCount();
$changeColor = false;
if ($this->colorChangerColumn >= 0) {
if ($oldValue != $rowData[$this->colorChangerColumn]) {
$changeColor = true;
$oldValue = $rowData[$this->colorChangerColumn];
}
} else {
if ($this->autoZebra) {
$changeColor = true;
}
}
if ($changeColor) {
$cellStyleArray['fill']['color']['argb'] = $this->rainBow->getCurrentAsARGBString();
$this->rainBow->getNext();
}
$i = 0;
for (; $i < $headCount; $i++) {
$value = $rowData[$i];
$coor = XLSWriter::cellCoordinate($i, $row);
$xlstype = isset($XlsTypes[$i]) ? $XlsTypes[$i] : PHPExcel_Cell_DataType::TYPE_STRING;
//error_log("writing cell type = {$xlstype} for column {$i}, value {$value}", 0);
$objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $value, $xlstype);
if ($this->columnTypes[$i] == 'date') {
$objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
} else {
if ($this->columnTypes[$i] == 'time') {
$objPHPExcel->getActiveSheet()->getStyle($coor)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8);
}
}
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray);
}
if ($this->weightedSumsColumn >= 0) {
$weightLast = count($this->weights) - 1;
$coor = XLSWriter::cellCoordinate($this->weightedSumsColumn, $row);
$wBegin = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn, $this->weigthsRow);
$wEnd = XLSWriter::cellCoordinateAbsoluteRow($this->firstWeightColumn + $weightLast, $this->weigthsRow);
$rBegin = XLSWriter::cellCoordinate($this->firstWeightColumn, $row);
$rEnd = XLSWriter::cellCoordinate($this->firstWeightColumn + $weightLast, $row);
$wSumCoor = XLSWriter::cellCoordinateAbsolute($this->weightedSumsColumn, $this->weigthsRow);
$formula = "=SUMPRODUCT({$wBegin}:{$wEnd},{$rBegin}:{$rEnd})/{$wSumCoor}";
$objPHPExcel->getActiveSheet()->setCellValueExplicit($coor, $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
$objPHPExcel->getActiveSheet()->getStyle($coor)->applyFromArray($cellStyleArray);
}
$row++;
$resultSet->moveNext();
}
$row = 1;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->linkText);
$objPHPExcel->getActiveSheet()->getCell('A' . $row)->getHyperlink()->setUrl($this->linkUrl);
$row++;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row, $this->title);
$objPHPExcel->getActiveSheet()->getStyle('A' . $row)->applyFromArray($headerStyles);
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($headerStyles);
$rightCell1 = XLSWriter::cellCoordinate(min($headCount - 1, 10), $row);
$objPHPExcel->getActiveSheet()->mergeCells('A' . $row . ':' . $rightCell1);
$rightCell2 = XLSWriter::cellCoordinate(min($headCount - 1, 10), 1);
$objPHPExcel->getActiveSheet()->mergeCells('A1:' . $rightCell2);
// set format
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);
for ($i = 'A', $j = 0; $i <= 'Z' && $j < $headCount; $i++, $j++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true);
// $objPHPExcel->getActiveSheet()->getStyle($i . '2')->applyFromArray($styleArray);
}
PHPExcel_Calculation::getInstance()->clearCalculationCache();
PHPExcel_Calculation::getInstance()->disableCalculationCache();
PHPExcel_Calculation::getInstance()->calculate();
switch ($this->excelFormat) {
case 'Excel2007':
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$this->mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
break;
case 'Excel5':
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$this->mimeType = 'application/vnd.ms-excel';
break;
default:
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$this->mimeType = 'text/comma-separated-values';
break;
}
$tempFile = tempnam('/tmp/', 'PHPEXCEL');
// '/tmp/'.$filename;
$objWriter->setPreCalculateFormulas(true);
$objWriter->save($tempFile);
$fp = @fopen($tempFile, 'r');
if ($fp != false) {
header("Content-type: " . $this->mimeType);
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . filesize($tempFile));
header("Content-Disposition: attachment; filename=\"{$this->filename}\"");
fpassthru($fp);
fclose($fp);
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
unlink($tempFile);
exit(0);
} else {
echo "cannot copy file {$tempFile} to out stream\n";
}
}
public function utilization_report_excelv2()
{
$filter = array();
$filter['start_date']=(time()-(60*60*24*30));
if (isset($_POST['start_time'])) {
$filter['start_date']=$this->m_time->datepicker_to_unix($_POST['start_time']);
}
$filter['end_date']=(time()+(60*60*24*30));
if (isset($_POST['end_time'])) {
$filter['end_date']=$this->m_time->datepicker_to_unix($_POST['end_time'])+(60*60*24)-60;
}
$all_child1=$this->get_all_user_child_node();
$all_child1[$this->user_data->username]=$this->user_data;
$data_head['user_data'] = $this->user_data;
$all_child = array();
$position=$this->m_position->get_all_position();
$lenght_time=(int)(($filter['end_date']-$filter['start_date'])/(60*60*24));
$mod_time=(int)(($filter['end_date']-$filter['start_date'])%(60*60*24));
if ($mod_time>0) {
$lenght_time+=1;
}
$lenght_time=$lenght_time;
foreach ($all_child1 as $key => $value) {
if (isset($position[$value->position])&&$position[$value->position]->non_productive=="n") {
$all_child[$key]=$value;
$all_child[$key]->report=$this->m_utilization_report->get_work_sheet_by_usn($value->username,$filter);
$all_child[$key]->projects=$this->m_utilization_report->group_assign_to_project($all_child[$key]->report);
$all_child[$key]->available_Hours=$this->m_utilization_report->cal_Available_Hours($filter['start_date'],$filter['end_date'],$value->username);
}
}
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
require_once './PHPExcel/Classes/PHPExcel.php';
require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$styleblack = array(
'font' => array(
'bold' => false,
'color' => array('rgb' => 'FFFFFF'),
'size' => 11,
'name' => 'Calibri'
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => '000000')
)
);
$style_center = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
)
);
$stylegray = array(
'font' => array(
'bold' => false,
'color' => array('rgb' => '000000'),
'size' => 11,
'name' => 'Calibri'
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'E2E2E2')
)
);
// Set document properties
$objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group")
->setLastModifiedBy("DekGym3Atom")
->setTitle("Office 2007 XLSX User report")
->setSubject("Office 2007 XLSX User report")
->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("User report");
// Add some data
$cur_col=0;
$cur_row=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Date '.date("d F Y"), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit(date("d F Y",$filter['start_date']).' - '.date("d F Y",$filter['end_date']), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Resources / Project', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(0))->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Available Hours', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(1))->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit('Allocation Hour', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(2))->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(2).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(4).($cur_row));
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit('Spent', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(5))->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(5).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(7).($cur_row));
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('Utilization (%)', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex(8))->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(8).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).($cur_row));
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray);
foreach ($all_child as $key => $value) {
$cur_row+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value->nickname, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Available Hours', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit('TOTAL', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit('BUDGET', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit('OVER', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit('TOTAL', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit('on BUDGET', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit('on TOTAL', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('by Budget', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit('by Spent', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit('GAP', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray);
$flag_first=true;
foreach ($value->projects as $key2 => $value2) {
$cur_row+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING);
if ($flag_first) {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit($value->available_Hours, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($style_center);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(1).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(1).($cur_row+count($value->projects)-1));
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit($value2->hour_amount, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit($value2->budget, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit($value2->hour_over, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit($value2->spend_amount, PHPExcel_Cell_DataType::TYPE_STRING);
$spend_rate="0%";
if ($value2->budget!=0) {
$spend_rate=number_format(($value2->spend_amount/$value2->budget)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit($spend_rate, PHPExcel_Cell_DataType::TYPE_STRING);
$spend_rate="0%";
if ($value2->hour_amount!=0) {
$spend_rate=number_format(($value2->spend_amount/$value2->hour_amount)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit($spend_rate, PHPExcel_Cell_DataType::TYPE_STRING);
if ($flag_first) {
$gap1="0%";
if ($value2->hour_amount!=0) {
$gap1=number_format(($value->report->budget/$value->available_Hours)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit($gap1, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(8).$cur_row)->applyFromArray($style_center);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(8).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(8).($cur_row+count($value->projects)-1));
$gap2="0%";
if ($value2->hour_amount!=0) {
$gap2=number_format(($value->report->spend_amount/$value->available_Hours)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit($gap2, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(9).$cur_row)->applyFromArray($style_center);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(9).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(9).($cur_row+count($value->projects)-1));
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit(number_format($gap2-$gap1)."%", PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($style_center);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells(PHPExcel_Cell::stringFromColumnIndex(10).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).($cur_row+count($value->projects)-1));
}
$flag_first=false;
}
$cur_row+=1;
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).($cur_row).":".PHPExcel_Cell::stringFromColumnIndex(10).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit($value->nickname." Total", PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit($lenght_time.' Days', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(2, $cur_row)->setValueExplicit($value->report->hour_amount, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(3, $cur_row)->setValueExplicit($value->report->budget, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(4, $cur_row)->setValueExplicit($value->report->hour_over, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(5, $cur_row)->setValueExplicit($value->report->spend_amount, PHPExcel_Cell_DataType::TYPE_STRING);
$tspend_rate="0%";
if ($value->report->budget!=0) {
$tspend_rate=number_format(($value->report->spend_amount/$value->report->budget)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(6, $cur_row)->setValueExplicit($tspend_rate, PHPExcel_Cell_DataType::TYPE_STRING);
$tspend_rate="0%";
if ($value->report->hour_amount!=0) {
$tspend_rate=number_format(($value->report->spend_amount/$value->report->hour_amount)*100)."%";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(7, $cur_row)->setValueExplicit($tspend_rate, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(8, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(9, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(10, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('utilization_report');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="utilization_report.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
/**
* Utility function for export2csv and CRM_Report_Form::endPostProcess
* - make XLS file content and return as string.
*
* @param Object &$form CRM_Report_Form object.
* @param Array &$rows Resulting rows from the report.
* @param String Full path to the filename to write in (for mailing reports).
*
* See @CRM_Report_Utils_Report::makeCsv().
*/
static function generateFile(&$form, &$rows, &$stats, $filename = 'php://output')
{
$config = CRM_Core_Config::singleton();
$csv = '';
// Generate an array with { 0=>A, 1=>B, 2=>C, ... }
$foo = array(0 => '', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M');
$a = ord('A');
$cells = array();
for ($i = 0; $i < count($foo); $i++) {
for ($j = 0; $j < 26; $j++) {
$cells[$j + $i * 26] = $foo[$i] . chr($j + $a);
}
}
include 'PHPExcel/Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
// Does magic things for date cells
// https://phpexcel.codeplex.com/discussions/331005
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// FIXME Set the locale of the XLS file
// might not really be necessary (concerns mostly functions? not dates?)
// $validLocale = PHPExcel_Settings::setLocale('fr');
// Set document properties
$objPHPExcel->getProperties()->setCreator("CiviCRM")->setLastModifiedBy("CiviCRM")->setTitle(ts('Report'))->setSubject(ts('Report'))->setDescription(ts('Report'));
$sheet = $objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Report');
// Add headers if this is the first row.
$columnHeaders = array_keys($form->_columnHeaders);
// Replace internal header names with friendly ones, where available.
foreach ($columnHeaders as $header) {
if (isset($form->_columnHeaders[$header])) {
$headers[] = html_entity_decode(strip_tags($form->_columnHeaders[$header]['title']));
}
}
// Add the column headers.
$col = 0;
$cpt = 1;
foreach ($headers as $h) {
$objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $h);
$col++;
}
// Add rows.
$cpt = 2;
foreach ($rows as $row) {
$displayRows = array();
$col = 0;
foreach ($columnHeaders as $k => $v) {
$value = CRM_Utils_Array::value($v, $row);
if (!isset($value)) {
$col++;
continue;
}
// Remove HTML, unencode entities
$value = html_entity_decode(strip_tags($value));
// Data transformation before adding it to the cell
if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_DATE) {
$group_by = CRM_Utils_Array::value('group_by', $form->_columnHeaders[$v]);
if ($group_by == 'MONTH' || $group_by == 'QUARTER') {
$value = CRM_Utils_Date::customFormat($value, $config->dateformatPartial);
} elseif ($group_by == 'YEAR') {
$value = CRM_Utils_Date::customFormat($value, $config->dateformatYear);
} else {
$value = CRM_Utils_Date::customFormat($value, '%Y-%m-%d');
}
}
$objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $value);
// Cell formats
if (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_DATE) {
$objPHPExcel->getActiveSheet()->getStyle($cells[$col] . $cpt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD);
// Set autosize on date columns.
// We only do it for dates because we know they have a fixed width, unlike strings.
// For eco-friendlyness, this should only be done once, perhaps when processing the headers initially
$objPHPExcel->getActiveSheet()->getColumnDimension($cells[$col])->setAutoSize(true);
} elseif (CRM_Utils_Array::value('type', $form->_columnHeaders[$v]) & CRM_Utils_Type::T_MONEY) {
$objPHPExcel->getActiveSheet()->getStyle($cells[$col])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
}
$col++;
}
$cpt++;
}
// Add report statistics on a separate Excel sheet.
if (!empty($stats) && !empty($stats['counts'])) {
$cpt = 1;
$objWorkSheet = $objPHPExcel->createSheet(1);
$objWorkSheet->setTitle(ts('Statistics'));
foreach ($stats['counts'] as $key => $val) {
$objWorkSheet->setCellValue('A' . $cpt, $val['title'])->setCellValue('B' . $cpt, $val['value']);
$cpt++;
}
$objWorkSheet->getColumnDimension('A')->setWidth(30);
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($filename);
return '';
// FIXME
}
/**
*
* See @CRM_Report_Utils_SearchExport::export2excel2007().
*/
static function makeExcel(&$headers, &$columnTypes, &$rows)
{
$config = CRM_Core_Config::singleton();
$csv = '';
// Generate an array with { 0=>A, 1=>B, 2=>C, ... }
$foo = array(0 => '', 1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E');
$a = ord('A');
$cells = array();
for ($i = 0; $i < count($foo); $i++) {
for ($j = 0; $j < 26; $j++) {
$cells[$j + $i * 26] = $foo[$i] . chr($j + $a);
}
}
include 'PHPExcel/Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
// Does magic things for date cells
// https://phpexcel.codeplex.com/discussions/331005
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// Set document properties
$objPHPExcel->getProperties()->setCreator("CiviCRM")->setLastModifiedBy("CiviCRM")->setTitle(ts('Export'))->setSubject(ts('Export'))->setDescription(ts('Export'));
$sheet = $objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Export');
// Add the column headers.
$col = 0;
$cpt = 1;
foreach ($headers as $h) {
try {
$objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $h);
} catch (Exception $e) {
die(print_r($e, 1));
}
$col++;
}
// Add rows.
$cpt = 2;
// Convert the sql headers to civi types
$columnHeaders = CRM_CiviExportExcel_Utils_SearchExport::sqlTypesToCivi($columnTypes);
foreach ($rows as $row) {
$displayRows = array();
$col = 0;
foreach ($columnTypes as $k => $v) {
$value = CRM_Utils_Array::value($k, $row);
if (!isset($value)) {
$col++;
continue;
}
// Remove HTML, unencode entities
$value = html_entity_decode(strip_tags($value));
$objPHPExcel->getActiveSheet()->setCellValue($cells[$col] . $cpt, $value);
// Cell formats
if (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_DATE) {
$objPHPExcel->getActiveSheet()->getStyle($cells[$col] . $cpt)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD);
// Set autosize on date columns.
// We only do it for dates because we know they have a fixed width, unlike strings.
// For eco-friendlyness, this should only be done once, perhaps when processing the headers initially
$objPHPExcel->getActiveSheet()->getColumnDimension($cells[$col])->setAutoSize(true);
} elseif (CRM_Utils_Array::value('type', $columnHeaders[$k]) & CRM_Utils_Type::T_MONEY) {
$objPHPExcel->getActiveSheet()->getStyle($cells[$col])->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
}
$col++;
}
$cpt++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
return '';
// FIXME
}
protected function addHeader($from, $to)
{
$this->_row = 1;
$structure = Doctrine::getTable('Structure')->createQuery('a')->fetchOne();
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
$active_sheet = $this->getDocument()->getActiveSheet();
//Add the logo of the structure at the top left of the page:
$logo = new PHPExcel_Worksheet_Drawing();
$logo->setName('Logo');
$logo->setDescription('Logo');
$logo->setPath('./uploads/images/logo.png');
$logo->setHeight(60);
$logo->setWidth(129);
$logo->setCoordinates('A' . $this->_row);
$logo->setWorksheet($active_sheet);
//Merge the cells at the top right of the page:
$active_sheet->mergeCells('B' . $this->_row . ':D' . $this->_row);
//and put inside the informations about the structure:
$active_sheet->getCell('B' . $this->_row)->setValue($structure->getName() . "\n" . $structure->getAddress()->getStreet() . "\n" . $structure->getAddress()->getAddressCity()->getPostalCode() . " " . $structure->getAddress()->getAddressCity()->getName() . "\n" . "Tel: " . $structure->getTelephoneNumber() . "\n" . "Tel2: " . $structure->getAddress()->getTelephoneNumber() . "\n" . "e-mail: " . $structure->getEmail() . "\n" . "site: " . $structure->getWebsite() . "\n");
$active_sheet->getStyle('B' . $this->_row)->getFont()->setBold(true);
$this->nextLine();
//Write the chosen period at the date of document generation:
$active_sheet->getCell('A' . $this->_row)->setValue($this->_translate("Period") . ":\n" . $this->_translate("From") . " " . date($this->_translate('m/d/y'), strtotime($from)) . " " . $this->_translate("to") . " " . date($this->_translate('m/d/y'), strtotime($to)));
$active_sheet->getCell('B' . $this->_row)->setValue($this->_translate("Generated") . ":\n" . date($this->_translate('m/d/y')) . " " . $this->_translate("at") . " " . date('H:i:s'));
$active_sheet->getStyle('A' . $this->_row . ':B' . $this->_row)->getFont()->setSize(8);
//General specifications of the page:
//Footer:
$active_sheet->getHeaderFooter()->setOddFooter('&L&D&RPage &P/&N');
$this->nextLine(2);
}
public function operatingGPReport_excel_client_mode()
{
$data_view['business_unit_id'] = "no";
$start_time = 0;
$end_carlendar_unix = 0;
$bus_unit = "";
$company = array();
$bu = array();
$company_tmp = $this->m_company->get_all_company();
$bu_tmp = $this->m_company->get_all_bu();
foreach ($company_tmp as $key => $value) {
$company[$value->id] = $value;
}
foreach ($bu_tmp as $key => $value) {
$bu[$value->id] = $value;
}
$business_list = $this->m_business->get_all_business();
if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
$start_time = $this->m_time->datepicker_to_unix($_POST['start_time']);
$end_carlendar_unix = $this->m_time->datepicker_to_unix($_POST['end_time']);
} else {
$start_time = mktime(0, 0, 1, 1, 1, date("Y"));
$end_carlendar_unix = mktime(0, 0, 1, 12, 31, date("Y"));
}
if (isset($_POST['business_unit_id'])) {
$bus_unit = $_POST['business_unit_id'];
} else {
$bus_unit = "all";
}
if (isset($_POST['mode'])) {
$mode = (int) $_POST['mode'];
} else {
$mode = 1;
}
$multi_usn = array("all" => "all");
if (isset($_POST['multi_usn'])) {
unset($multi_usn['all']);
$multi_usn[$_POST['multi_usn']] = $_POST['multi_usn'];
}
$forcast_report = $this->m_forcast->get_forcast_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$pce_report = $this->m_forcast->get_forcast_pce_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$target_bill_report = $this->m_forcast->get_forcast_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$actual_bill_report = $this->m_forcast->get_forcast_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$forcast_out_report = $this->m_account->get_forcast_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$outsource_report = $this->m_account->get_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$target_out_report = $this->m_account->get_outsource_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$actual_out_report = $this->m_account->get_outsource_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$forcast_report_cash = $this->m_forcast->get_forcast_receive_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$pce_report_cash = $this->m_forcast->get_forcast_receive_pce_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$target_bill_report_cash = $this->m_forcast->get_forcast_receive_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$actual_bill_report_cash = $this->m_forcast->get_forcast_receive_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$forcast_out_report_cash = $this->m_account->get_forcast_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$outsource_report_cash = $this->m_account->get_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$target_out_report_cash = $this->m_account->get_outsource_paid_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
$actual_out_report_cash = $this->m_account->get_outsource_paid_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $multi_usn, $mode);
if (isset($this->user_data->prem['cs']) && (!isset($this->user_data->prem['admin']) && !isset($this->user_data->prem['account']) && !isset($this->user_data->prem['csd']))) {
$all_user_under = $this->m_user->get_all_user_by_super_usn_all_lv($this->user_data->username, $this->user_data->username);
$array_user = $this->m_user->change_node_user_to_array($all_user_under);
$array_user[$this->user_data->username] = $this->user_data->username;
$forcast_report = $this->m_forcast->get_forcast_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$pce_report = $this->m_forcast->get_forcast_pce_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$target_bill_report = $this->m_forcast->get_forcast_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$actual_bill_report = $this->m_forcast->get_forcast_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$forcast_out_report = $this->m_account->get_forcast_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$outsource_report = $this->m_account->get_outsource_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$target_out_report = $this->m_account->get_outsource_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$actual_out_report = $this->m_account->get_outsource_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$forcast_report_cash = $this->m_forcast->get_forcast_receive_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$pce_report_cash = $this->m_forcast->get_forcast_receive_pce_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$target_bill_report_cash = $this->m_forcast->get_forcast_receive_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$actual_bill_report_cash = $this->m_forcast->get_forcast_receive_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$forcast_out_report_cash = $this->m_account->get_forcast_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$outsource_report_cash = $this->m_account->get_outsource_paid_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$target_out_report_cash = $this->m_account->get_outsource_paid_target_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
$actual_out_report_cash = $this->m_account->get_outsource_paid_actual_bill_report($start_time, $end_carlendar_unix, $bus_unit, $array_user, $mode);
}
$pce_report = $this->m_forcast->gd_equal_array($outsource_report, $pce_report);
$target_bill_report = $this->m_forcast->gd_equal_array($target_out_report, $target_bill_report);
$actual_bill_report = $this->m_forcast->gd_equal_array($actual_out_report, $actual_bill_report);
$pce_report_cash = $this->m_forcast->gd_equal_array($outsource_report_cash, $pce_report_cash);
$target_bill_report_cash = $this->m_forcast->gd_equal_array($target_out_report_cash, $target_bill_report_cash);
$actual_bill_report_cash = $this->m_forcast->gd_equal_array($actual_out_report_cash, $actual_bill_report_cash);
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
if (PHP_SAPI == 'cli') {
die('This example should only be run from a Web Browser');
}
/** Include PHPExcel */
require_once './PHPExcel/Classes/PHPExcel.php';
require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$styleblack = array('font' => array('bold' => false, 'color' => array('rgb' => 'FFFFFF'), 'size' => 11, 'name' => 'Calibri'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000')));
$style_center = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER));
$stylegray = array('font' => array('bold' => false, 'color' => array('rgb' => '000000'), 'size' => 11, 'name' => 'Calibri'), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'E2E2E2')));
// Set document properties
$objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group")->setLastModifiedBy("DekGym3Atom")->setTitle("Office 2007 XLSX User report")->setSubject("Office 2007 XLSX User report")->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("User report");
// Add some data
$total_horizon_array = array();
$cur_col = 0;
$cur_row = 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('GROSS PROFIT FORECAST', PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row = 2;
$num = 0;
$sumval = 0;
$sumvalr = 0;
$current_time = $start_time;
$cur_month = 1000;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('Client', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
$cur_col += 1;
while ($current_time <= $end_carlendar_unix) {
if ($cur_month != date("n", $current_time)) {
$cur_month = date("n", $current_time);
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
$cur_col += 1;
$total_horizon_array[date("Yn", $current_time)] = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F", $current_time), PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time += 60 * 60 * 24 * $numday;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row += 1;
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($forcast_report) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($forcast_report) - 1));
}
foreach ($forcast_report as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if (isset($value[$current_time])) {
foreach ($value[$current_time] as $key2 => $value2) {
$month_amount += $value2->project_value;
}
}
if (isset($forcast_out_report[$key][$current_time])) {
foreach ($forcast_out_report[$key][$current_time] as $key2 => $value2) {
$month_amount -= $value2->outsource_value;
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$cur_row += 1;
}
/////////////////////////////////////////////// PCE //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($pce_report) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($pce_report) - 1));
}
foreach ($pce_report as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if (isset($value[$current_time])) {
foreach ($value[$current_time] as $key2 => $value2) {
foreach ($value2->pce as $key3 => $value3) {
$month_amount += $value3->pce_amount;
}
}
}
if (isset($outsource_report[$key][$current_time])) {
foreach ($outsource_report[$key][$current_time] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
$month_amount -= $value3->qt_cost;
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
/////////////////////////////////////////////// Target Billing (OC) //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($target_bill_report) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($target_bill_report) - 1));
}
foreach ($target_bill_report as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
foreach ($value as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount += $value4->amount;
}
}
}
}
if (isset($target_out_report[$key])) {
foreach ($target_out_report[$key] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
if (isset($value3->bill[$current_time])) {
foreach ($value3->bill[$current_time] as $key4 => $value4) {
$month_amount -= $value4->amount - $value4->paid_amount;
}
}
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
/////////////////////////////////////////////// Actual Billing //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($actual_bill_report) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($actual_bill_report) - 1));
}
foreach ($actual_bill_report as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
foreach ($value as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount += $value4->paid_amount;
}
}
}
}
if (isset($actual_out_report[$key])) {
foreach ($actual_out_report[$key] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
if (isset($value3->bill[$current_time])) {
foreach ($value3->bill[$current_time] as $key4 => $value4) {
$month_amount -= $value4->amount;
}
}
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$cur_col += 1;
$current_time = $start_time;
$cur_month = 1000;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($total_horizon_array[date("Yn", $current_time)]), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$sum_total += $total_horizon_array[date("Yn", $current_time)];
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$cur_row += 1;
/////////////////////////////////////////////////////////////////////// section 2 ///////////////////////////////////////////////////
$cur_col = 0;
$cur_row += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('OPERATING CASH FLOW FORECAST', PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row += 1;
$num = 0;
$sumval = 0;
$sumvalr = 0;
$current_time = $start_time;
$cur_month = 1000;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
$cur_col += 1;
while ($current_time <= $end_carlendar_unix) {
if ($cur_month != date("n", $current_time)) {
$cur_month = date("n", $current_time);
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
$cur_col += 1;
$total_horizon_array[date("Yn", $current_time)] = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F", $current_time), PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time += 60 * 60 * 24 * $numday;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row += 1;
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($forcast_report_cash) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($forcast_report_cash) - 1));
}
foreach ($forcast_report_cash as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if (isset($value[$current_time])) {
foreach ($value[$current_time] as $key2 => $value2) {
$month_amount += $value2->project_value;
}
}
if (isset($forcast_out_report_cash[$key][$current_time])) {
foreach ($forcast_out_report_cash[$key][$current_time] as $key2 => $value2) {
$month_amount -= $value2->outsource_value;
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$cur_row += 1;
}
/////////////////////////////////////////////// PCE //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($pce_report_cash) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($pce_report_cash) - 1));
}
foreach ($pce_report_cash as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if (isset($value[$current_time])) {
foreach ($value[$current_time] as $key2 => $value2) {
foreach ($value2->pce as $key3 => $value3) {
$month_amount += $value3->pce_amount;
}
}
}
if (isset($outsource_report_cash[$key][$current_time])) {
foreach ($outsource_report_cash[$key][$current_time] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
$month_amount -= $value3->qt_cost;
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
/////////////////////////////////////////////// Target Billing (OC) //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($target_bill_report_cash) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($target_bill_report_cash) - 1));
}
foreach ($target_bill_report_cash as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
foreach ($value as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount += $value4->amount;
}
}
}
}
if (isset($target_out_report_cash[$key])) {
foreach ($target_out_report_cash[$key] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
if (isset($value3->bill[$current_time])) {
foreach ($value3->bill[$current_time] as $key4 => $value4) {
$month_amount -= $value4->amount - $value4->paid_amount;
}
}
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
/////////////////////////////////////////////// Actual Billing //////////////////////////////////////////
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0) . $cur_row)->applyFromArray($style_center);
if (count($actual_bill_report_cash) > 1) {
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A" . $cur_row . ":A" . ($cur_row + count($actual_bill_report_cash) - 1));
}
foreach ($actual_bill_report_cash as $key => $value) {
$cur_col = 1;
if ($mode == 3) {
$str1 = explode("_", $key);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$str1[0]]->name . " " . $bu[$str1[1]]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" " . $company[$key]->name, PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time = $start_time;
$cur_month = 1000;
$month_amount = 0;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
foreach ($value as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount += $value4->paid_amount;
}
}
}
}
if (isset($actual_out_report_cash[$key])) {
foreach ($actual_out_report_cash[$key] as $key2 => $value2) {
foreach ($value2->outsource as $key3 => $value3) {
if (isset($value3->bill[$current_time])) {
foreach ($value3->bill[$current_time] as $key4 => $value4) {
$month_amount -= $value4->amount;
}
}
}
}
}
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total += $month_amount;
$total_horizon_array[date("Yn", $current_time)] += $month_amount;
$month_amount = 0;
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row += 1;
}
$cur_col = 0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(1, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$cur_col += 1;
$current_time = $start_time;
$cur_month = 1000;
$sum_total = 0;
while ($current_time <= $end_carlendar_unix) {
$cur_day = date("j", $current_time);
$numday = date("t", $current_time);
if ($cur_month != date("n", $current_time) && $cur_day == $numday) {
$cur_month = date("n", $current_time);
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($total_horizon_array[date("Yn", $current_time)]), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$sum_total += $total_horizon_array[date("Yn", $current_time)];
}
$current_time += 60 * 60 * 24;
}
$cur_col += 1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
//$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$cur_row += 1;
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Operation GD report');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Operation_GD_report.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
// Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
// always modified
header('Cache-Control: cache, must-revalidate');
// HTTP/1.1
header('Pragma: public');
// HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
public function forcest_receive_excel()
{
$data_view['business_unit_id']="no";
$start_time=0;
$end_carlendar_unix=0;
$bus_unit="";
$company = array();
$bu = array();
$company_tmp=$this->m_company->get_all_company();
$bu_tmp=$this->m_company->get_all_bu();
foreach ($company_tmp as $key => $value) {
$company[$value->id]=$value;
}
foreach ($bu_tmp as $key => $value) {
$bu[$value->id]=$value;
}
$business_list = $this->m_business->get_all_business();
if (isset($_POST['start_time'])&&isset($_POST['end_time'])) {
$start_time=$this->m_time->datepicker_to_unix($_POST['start_time']);
$end_carlendar_unix=$this->m_time->datepicker_to_unix($_POST['end_time']);
}else{
$start_time=mktime(0,0,1,1,1,date("Y"));
$end_carlendar_unix=mktime(0,0,1,12,31,date("Y"));
}
if (isset($_POST['business_unit_id'])) {
$bus_unit=$_POST['business_unit_id'];
}else{
$bus_unit="all";
}
$forcast_report=$this->m_forcast->get_forcast_receive_report($start_time,$end_carlendar_unix,$bus_unit);
$pce_report=$this->m_forcast->get_forcast_receive_pce_report($start_time,$end_carlendar_unix,$bus_unit);
$target_bill_report=$this->m_forcast->get_forcast_receive_target_bill_report($start_time,$end_carlendar_unix,$bus_unit);
$actual_bill_report=$this->m_forcast->get_forcast_receive_actual_bill_report($start_time,$end_carlendar_unix,$bus_unit);
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
require_once './PHPExcel/Classes/PHPExcel.php';
require_once './PHPExcel/Classes/PHPExcel/Cell/AdvancedValueBinder.php';
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$styleblack = array(
'font' => array(
'bold' => false,
'color' => array('rgb' => 'FFFFFF'),
'size' => 11,
'name' => 'Calibri'
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => '000000')
)
);
$stylegray = array(
'font' => array(
'bold' => false,
'color' => array('rgb' => '000000'),
'size' => 11,
'name' => 'Calibri'
),
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'E2E2E2')
)
);
// Set document properties
$objPHPExcel->getProperties()->setCreator("Rcal Neumerlin Group")
->setLastModifiedBy("DekGym3Atom")
->setTitle("Office 2007 XLSX User report")
->setSubject("Office 2007 XLSX User report")
->setDescription("User report document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("User report");
// Add some data
$cur_col=0;
$cur_row=1;
$num=0;
$sumval=0;
$sumvalr=0;
$current_time=$start_time;
$cur_month=1000;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Month->', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setAutoSize(true);
$cur_col+=2;
while ($current_time<=$end_carlendar_unix) {
if ($cur_month!=date("n",$current_time)) {
$cur_month=date("n",$current_time);
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(date("F",$current_time), PHPExcel_Cell_DataType::TYPE_STRING);
}
$current_time+=(60*60*24*$numday);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit("Total", PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Forcast', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($forcast_report as $key => $value) {
if (isset($value->forcast_list[$current_time])) {
foreach ($value->forcast_list[$current_time] as $key2 => $value2) {
$month_amount+=$value2->project_value;
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($cur_col))->setAutoSize(true);
$cur_row+=1;
foreach ($forcast_report as $key => $value) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
if (isset($value->forcast_list[$current_time])) {
foreach ($value->forcast_list[$current_time] as $key2 => $value2) {
$month_amount+=$value2->project_value;
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$cur_row+=1;
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2 as $key3 => $value3) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value3->project_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value3->project_client]->name." ".$bu[$value3->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
if ($current_time==$value3->project_end) {
$month_amount+=$value3->project_value;
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
}
}
}
/////////////////////////////////////////////// PCE //////////////////////////////////////////
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('PCE', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($pce_report as $key => $value) {
if (isset($value->forcast_list[$current_time])) {
foreach ($value->forcast_list[$current_time] as $key2 => $value2) {
foreach ($value2->pce as $key3 => $value3) {
$month_amount+=$value3->pce_amount;
}
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row+=1;
foreach ($pce_report as $key => $value) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
if (isset($value->forcast_list[$current_time])) {
foreach ($value->forcast_list[$current_time] as $key2 => $value2) {
foreach ($value2->pce as $key3 => $value3) {
$month_amount+=$value3->pce_amount;
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$cur_row+=1;
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2 as $key3 => $value3) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value3->project_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value3->project_client]->name." ".$bu[$value3->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$pce_no_str="";
foreach ($value3->pce as $key4 => $value4) {
$pce_no_str.=$value4->pce_no.",";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($pce_no_str, PHPExcel_Cell_DataType::TYPE_STRING);
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
if ($current_time==$value3->project_end) {
foreach ($value3->pce as $key4 => $value4) {
$month_amount+=$value4->pce_amount;
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
}
}
}
/////////////////////////////////////////////// Target Billing (OC) //////////////////////////////////////////
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Target Billing (OC)', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($target_bill_report as $key => $value) {
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->amount;
}
}
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row+=1;
foreach ($target_bill_report as $key => $value) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->amount;
}
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$cur_row+=1;
foreach ($value->forcast_list as $key2 => $value2) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value2->project_client]->name." ".$bu[$value2->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$oc_no_str="";
foreach ($value2->oc as $key3 => $value3) {
$oc_no_str.=$value3->oc_no.",";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($oc_no_str, PHPExcel_Cell_DataType::TYPE_STRING);
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->amount;
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
}
}
/////////////////////////////////////////////// Actual Billing //////////////////////////////////////////
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow(0, $cur_row)->setValueExplicit('Actual Billing', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(1).$cur_row)->applyFromArray($styleblack);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex(2).$cur_row)->applyFromArray($styleblack);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($actual_bill_report as $key => $value) {
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->paid_amount;
}
}
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($styleblack);
$cur_row+=1;
foreach ($actual_bill_report as $key => $value) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value->firstname." ".$value->lastname, PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+1).$cur_row)->applyFromArray($stylegray);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col+2).$cur_row)->applyFromArray($stylegray);
$cur_col+=2;
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($value->forcast_list as $key2 => $value2) {
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->paid_amount;
}
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle(PHPExcel_Cell::stringFromColumnIndex($cur_col).$cur_row)->applyFromArray($stylegray);
$cur_row+=1;
foreach ($value->forcast_list as $key2 => $value2) {
$cur_col=0;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(" ".$value2->project_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($company[$value2->project_client]->name." ".$bu[$value2->project_bu]->bu_name, PHPExcel_Cell_DataType::TYPE_STRING);
$cur_col+=1;
$oc_no_str="";
foreach ($value2->oc as $key3 => $value3) {
$oc_no_str.=$value3->oc_no.",";
}
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit($oc_no_str, PHPExcel_Cell_DataType::TYPE_STRING);
$current_time=$start_time;
$cur_month=1000;
$month_amount=0;
$sum_total=0;
while ($current_time<=$end_carlendar_unix) {
$cur_day=date("j",$current_time);
$numday=date("t",$current_time);
foreach ($value2->oc as $key3 => $value3) {
if (isset($value3->oc_bill[$current_time])) {
foreach ($value3->oc_bill[$current_time] as $key4 => $value4) {
$month_amount+=$value4->paid_amount;
}
}
}
if ($cur_month!=date("n",$current_time)&&$cur_day==$numday) {
$cur_month=date("n",$current_time);
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($month_amount), PHPExcel_Cell_DataType::TYPE_STRING);
$sum_total+=$month_amount;
$month_amount=0;
}
$current_time+=(60*60*24);
}
$cur_col+=1;
$objPHPExcel->setActiveSheetIndex(0)->getCellByColumnAndRow($cur_col, $cur_row)->setValueExplicit(number_format($sum_total), PHPExcel_Cell_DataType::TYPE_STRING);
$cur_row+=1;
}
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Forcast');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Forcast_receive.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
}
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version ##VERSION##, ##DATE##
*/
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', true);
ini_set('display_startup_errors', true);
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
/** PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';
// Set timezone
echo date('H:i:s'), " Set timezone", EOL;
date_default_timezone_set('UTC');
// Set value binder
echo date('H:i:s'), " Set value binder", EOL;
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
// Create new PHPExcel object
echo date('H:i:s'), " Create new PHPExcel object", EOL;
$objPHPExcel = new PHPExcel();
// Set document properties
echo date('H:i:s'), " Set document properties", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
// Set default font
echo date('H:i:s'), " Set default font", EOL;
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
// Set column widths
echo date('H:i:s'), " Set column widths", EOL;
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(14);
// Add some data, resembling some different data types
/**
* @param \PHPExcel_Cell_IValueBinder $ValueBinder
*/
private function setConfiguration(\PHPExcel_Cell_IValueBinder $ValueBinder = null)
{
\PHPExcel_Settings::setCacheStorageMethod(\PHPExcel_CachedObjectStorageFactory::cache_in_memory, array('cacheTime' => 3600));
if (null !== $ValueBinder) {
\PHPExcel_Cell::setValueBinder($ValueBinder);
}
}
