public function testSimple()
{
$data = ['ROOT' => ['LEVEL1' => [['LEVEL1_CAPTION' => '1'], ['LEVEL1_CAPTION' => '2']]]];
$namedRange = $this->output->getNamedRange('ROOT');
$this->sut->write($data, $namedRange);
$this->output->setActiveSheetIndexByName('TEMPLATE');
$this->output->removeSheetByIndex($this->output->getActiveSheetIndex());
$excelWriter = \PHPExcel_IOFactory::createWriter($this->output, 'Excel2007');
$excelWriter->save(__DIR__ . '/../metadata/output/verticalrangewritertest.xlsx');
}
/**
* @param PHPExcel $objPHPExcel
* @param PHPExcel_Worksheet $sheet
* @param string $col
* @param int|string $row
* @param string $day
* @return bool
* @throws PHPExcel_Exception
*/
function getDay(PHPExcel $objPHPExcel, PHPExcel_Worksheet $sheet, PHPExcel_Cell $cell, $arr_filter)
{
$g = true;
$b = false;
$objPHPExcel->setActiveSheetIndexByName($sheet->getTitle());
$act_sheet = $objPHPExcel->getActiveSheet();
$index_sheet = $objPHPExcel->getActiveSheetIndex();
$row = $cell->getRow();
$column = $cell->getColumn();
// $cell_v=$cell->getFormattedValue();
$columnHiestIndex = PHPExcel_Cell::columnIndexFromString($act_sheet->getHighestColumn($row));
$rowHiestIndex = $act_sheet->getHighestRow();
$column = PHPExcel_Cell::columnIndexFromString($column);
//$this->testEcho($rowHiestIndex);
$cell_value = '';
for ($i = 0; $i < $column; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
// $this->testEcho($cell_value);
if (in_array($cell_value, $arr_filter)) {
//
// $this->testEcho($cell_value);
$b = true;
}
if ($b) {
$g = false;
break;
}
}
if ($g) {
for ($i = $column + 1; $i <= $columnHiestIndex; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
// $this->testEcho($cell_value);
if (in_array($cell_value, $arr_filter)) {
// $this->testEcho($cell_value);
$b = true;
}
if ($b) {
$g = false;
break;
}
}
}
if ($g) {
for ($i = 0; $i < $row; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $column, $i);
// $this->testEcho($cell_value);
if (in_array($cell_value, $arr_filter)) {
// $this->testEcho($cell_value);
$b = true;
}
if ($b) {
$g = false;
break;
}
}
}
if ($g) {
for ($i = $row + 1; $i <= $rowHiestIndex; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $column, $i);
// $this->testEcho($cell_value);
if (in_array($cell_value, $arr_filter)) {
// $this->testEcho($cell_value);
$b = true;
}
if ($b) {
break;
}
}
}
/*
for ($i = 0; $i <= $rowHiestIndex; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $i, $row);
// $this->testEcho($cell_value);
if ($cell_value == $day) {
$this->testEcho($cell_value);
return $b = true;
}
}
$this->testEcho('Тестим B');
$this->testEcho($b);
if (!$b) {
$colHiestIndex = $sheet->getHighestRow($col);
$this->testEcho($colHiestIndex);
for ($i = 0; $i <= $colHiestIndex; $i++) {
$cell_value = $this->getValueMergedCell($objPHPExcel, $index_sheet, $this->arr_merged_allCells, $col, $i);
if ($cell_value == $day) {
$this->testEcho($cell_value);
}
}
}
*/
return $cell_value;
}
/**
* Jump to sheet with string $title.
*
* @param string $title
* @return ExcelWriter
*/
public function gotoSheetByTitle($title)
{
$this->objPHPExcel->setActiveSheetIndexByName($title);
return $this;
}
include 'C:/xampp/htdocs/CPI/functions.php';
$FE_ESTADO_DESDE_MENSUAL = $_REQUEST["FE_ESTADO_DESDE_MENSUAL"];
$FE_ESTADO_HASTA_MENSUAL = $_REQUEST["FE_ESTADO_HASTA_MENSUAL"];
$TABLAS = $_REQUEST["TABLAS"];
$TABLA_CONSULTAR = $_REQUEST["TABLA_CONSULTAR"];
$TABLA_SENTENCIA = $_REQUEST["TABLA_SENTENCIA"];
$OWNER = 'CPI';
$TABLA_TITULOS = 'TITULOS';
$SELECT = 'COUNT';
$descripcionArray = array();
$sentencia = '';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Tablero CPI")->setLastModifiedBy("Tablero CPI")->setTitle("Control")->setSubject("Control")->setDescription("Control")->setKeywords("pdf php")->setCategory("Control");
$objWorkSheet = $objPHPExcel->createSheet();
$objWorkSheet->setTitle('Consolidado');
$objPHPExcel->setActiveSheetIndexByName('Worksheet');
//nombre por default
$objPHPExcel->getActiveSheet()->setTitle('op');
//renombro la hoja
$sentencia = 'SELECT V_CHAR2 "Fecha Desde", V_CHAR3 "Fecha Hasta" FROM CPI.TMP_INDICADORES WHERE V_CHAR2 IS NOT NULL AND V_CHAR3 IS NOT NULL';
$sentenciaArray = cargarArray($sentencia);
$k = 1;
$Letra = 'A';
$Letra1 = 'B';
$Letra2 = 'C';
while ($k < count($sentenciaArray)) {
$fe_dde = $sentenciaArray[$k][0];
$fe_hta = $sentenciaArray[$k][1];
$fePeriodo = date('m-Y', strtotime(str_replace('/', '-', $fe_dde)));
//Me traigo el count de la tabla de la sentencia a consultar. Para poder hacer el while.
$countTabla = "SELECT FUSS_NU_ID, FUSS_CD_TITULO FROM " . $OWNER . "." . $TABLA_SENTENCIA . " ORDER BY 1";
protected function clean()
{
$this->output->setActiveSheetIndexByName('TEMPLATE');
$this->output->removeSheetByIndex($this->output->getActiveSheetIndex());
}
public function export_data($function_args = null)
{
global $bridgehouse_theme, $fund_metabox, $pay_date, $table_prefix, $wpdb;
$defaults = array('type' => null, 'show_bechmarks' => false, 'solution_family_select' => null, 'solution_fund_select' => null, 'solution_class_select' => null, 'show_bechmarks_radio' => null, 'date_range_to' => null, 'date_range_from' => null, 'export_prices_type' => null);
$function_args = wp_parse_args($function_args, $defaults);
extract($function_args);
$cache_id = "export_data-" . $type . "-" . $this->date_range_from . "-" . $this->date_range_to . $solution_family_select . "-" . $solution_class_select . ":" . ICL_LANGUAGE_CODE;
$cache = wp_cache_get($cache_id, 'bridgehouse');
// if we have a cache version use it
if (strlen($cache) > 0) {
return $cache . "<!-- Cached {$cache_id} //-->";
}
ob_start();
//var_dump($function_args);
//$myrows = $this->run_query($function_args);
/*
if(count($myrows) == 0){
switch($type){
case"annualized":
case"historical":
return sprintf("<h3>%s</h3>",__('No data found.','bridgehouse'));
break;
// case"latest":
// return sprintf("<h3>%s</h3>",__('No data found.','bridgehouse'));
// break;
}
}*/
//var_dump('type is '. $type);
switch ($type) {
case "annualized":
/*$q = $wpdb->prepare( "
SELECT * FROM `".$table_prefix."bh_price_data` where
`manager_id` = %d and
`currency_id` = %d and
`class_id` = %s and
(`price_date` between %s and %s)
"
,icl_object_id($solution_family_select, 'investment-manager', true,'en'), $currency_id, $c[0] ,$sql_date_from,$sql_date_to
);*/
break;
case "historical":
$myrows = $this->run_query($function_args);
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '512MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Bridge House")->setLastModifiedBy("Bridge House")->setTitle("Office 2007 XLSX Document")->setSubject("Office 2007 XLSX Document")->setDescription("Historical Prices Data document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Historical Prices");
$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', __(get_the_title($solution_fund_select), 'bridgehouse') . ' | ' . $this->class_array($solution_class_select));
if ($date_range_to && $date_range_from) {
$date_string = ucfirst(date_i18n(_x('F j Y', 'prices date format', 'bridgehouse'), strtotime($this->date_range_from))) . " - " . ucfirst(date_i18n(_x('F j Y', 'prices date format', 'bridgehouse'), strtotime($this->date_range_to)));
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', $date_string);
//formating
$objPHPExcel->getActiveSheet()->mergeCells('A2:C2');
//$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
}
// Add Column Headings
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', __('Closing Date', 'bridgehouse'))->setCellValue('B3', __('NAV ($)', 'bridgehouse'))->setCellValue('C3', __('Change ($)', 'bridgehouse'));
//formating
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setShrinkToFit(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getStyle('A1:C3')->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT), 'borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('C1')->applyFromArray(array('borders' => array('right' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
$objPHPExcel->getActiveSheet()->getStyle('B3:C3')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)));
$objPHPExcel->getActiveSheet()->getStyle('A3')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT)));
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setBold(true);
//Read Data
$row_count = 3;
foreach ($myrows as $row) {
$row_count++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $row_count, ucfirst(date_i18n(_x('F j Y', 'prices date format', 'bridgehouse'), strtotime($row->price_date))))->setCellValue('B' . $row_count, number_format($row->nav, 2, '.', ''))->setCellValue('C' . $row_count, number_format($row->nav_change, 2, '.', ''));
//Force to show 2 decimal digits
$objPHPExcel->getActiveSheet()->getStyle('B' . $row_count . ':C' . $row_count)->getNumberFormat()->setFormatCode('##0.00');
}
// for loop end
$styleThinBorderOutline = array('borders' => array('inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')), 'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A1:C' . $row_count)->applyFromArray($styleThinBorderOutline);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(substr(__(get_the_title($solution_fund_select), 'bridgehouse'), 0, 30));
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Protect cells
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(false);
// Needs to be set to true in order to enable any worksheet protection!
//$objPHPExcel->getActiveSheet()->protectCells('A1:C'.$row_count, 'PHPExcel');
$footer_date_string = __('as of ', 'bridgehouse');
$footer_date_string .= ucfirst(date_i18n(_x('F t Y H:i:s', 'prices date format', 'bridgehouse'), strtotime(date("F j, Y, H:i:s"))));
$footer_cell = $row_count + 1;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $footer_cell, __('Downloaded from BridgehouseCanada.com ', 'bridgehouse') . $footer_date_string);
//$objPHPExcel->getActiveSheet()->mergeCells('A'.$footer_cell.':D'.$footer_cell);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
// Redirect output to a client’s web browser (Excel5)
$file = str_replace("’", '', str_replace("’", "", __(get_the_title($solution_fund_select), 'bridgehouse'))) . '_' . $date_string . ".xls";
$filename = remove_accents(str_replace(" ", "_", $file));
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $filename);
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
unset($objPHPExcel);
unset($objWriter);
exit;
break;
case 'distribution':
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '12MB', 'cacheTime' => 600);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Bridge House")->setLastModifiedBy("Bridge House")->setTitle("Office 2007 XLSX Document")->setSubject("Office 2007 XLSX Document")->setDescription("Historical Prices Data document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Distribution");
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
$str = '';
$row_count = 0;
$inner_row = 0;
//Check for All Fund Manager
if (-1 == $solution_family_select) {
$months = $wpdb->get_results("SELECT `payment_date` FROM `" . $table_prefix . "bh_monthly_distribs` group by `payment_date` order by `payment_date` DESC LIMIT 0, 11");
//ob_start();
$count_month = 0;
foreach ($months as $month) {
$function_args['distribution_month'] = date('Y-m-d', strtotime($month->payment_date));
$myrows = $this->run_query($function_args);
$this->distribution_intro_shown = false;
if ($count_month > 0) {
// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($count_month);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(ucfirst(date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse'), strtotime($month->payment_date))));
} else {
$objPHPExcel->setActiveSheetIndex($count_month);
//Set Title
$objPHPExcel->getActiveSheet()->setTitle(ucfirst(date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse'), strtotime($month->payment_date))));
}
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
//$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$family_args = array('post_type' => 'investment-manager', 'post_status' => 'publish', 'posts_per_page' => -1, 'orderby' => 'title', 'order' => 'ASC');
$family_posts = null;
$family_posts = new WP_Query($family_args);
if ($family_posts->have_posts()) {
$family_posts->the_post();
$row_count = 2;
$inner_row = 2;
foreach ($family_posts->posts as $post) {
$function_args['solution_family_select'] = $post->ID;
extract($function_args);
$myrows = $this->run_query($function_args);
$data = null;
foreach ($myrows as $row) {
if (isset($row->total_factor_usd)) {
$data[$row->fund_id][$row->class_id]['usd'] = sprintf('%-05f', $row->total_factor_usd);
}
if (isset($row->total_factor_cad)) {
$data[$row->fund_id][$row->class_id]['cad'] = sprintf('%-05f', $row->total_factor_cad);
}
}
if (count($myrows) != 0) {
if (!$this->distribution_intro_shown) {
$pay_date = ucfirst(date_i18n(_x('F j, Y', 'Inception date format', 'bridgehouse'), strtotime($myrows[0]->payment_date)));
$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A1', __('The following distributions were paid by Bridgehouse on ', 'bridgehouse') . $pay_date . __(' (record date ', 'bridgehouse') . ucfirst(date_i18n(_x('F j, Y', 'Inception date format', 'bridgehouse'), strtotime($myrows[0]->record_date))) . '):');
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setShrinkToFit(true);
$this->distribution_intro_shown = true;
}
//$row_count++;
//$inner_row++;
// Display Family
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A' . $row_count, html_entity_decode(__(get_the_title($solution_family_select), 'bridgehouse'), ENT_QUOTES, 'UTF-8'));
$objPHPExcel->getActiveSheet()->mergeCells('A' . $row_count . ':D' . $row_count);
//formating
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_count)->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$row_count++;
$inner_row++;
// get funds for this manager
$funds_for_manager = get_post_meta($solution_family_select, 'BH_funds', true);
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A' . $row_count, __('Fund', 'bridgehouse'))->setCellValue('B' . $row_count, __('Class', 'bridgehouse'))->setCellValue('C' . $row_count, __('CAD Distribution', 'bridgehouse'))->setCellValue('D' . $row_count, __('US Distribution', 'bridgehouse'));
//formating
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_count . ':D' . $row_count)->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('C' . $row_count . ':D' . $row_count)->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)));
// loop funds
$fund_index = 0;
foreach ($funds_for_manager as $fund) {
$count_fund_manager = sizeof($funds_for_manager);
$fund_metabox->the_meta($fund);
$solution_fund_id = strtoupper($fund_metabox->get_the_value('short_code'));
// de have valuse for this fund
if (array_key_exists($solution_fund_id, $data)) {
$fund_index++;
$count_fund = sizeof($data);
$row_count++;
//$objPHPExcel->setActiveSheetIndex($count_month)
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row_count, html_entity_decode(get_the_title($fund), ENT_QUOTES, 'UTF-8'));
foreach ($data[$solution_fund_id] as $key => $value) {
//$str .='<tr><td>'.$key.'</td><td>'.$value['cad'].'</td><td>'.$value['usd'].'</td></tr>';
$inner_row++;
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('B' . $inner_row, $key)->setCellValue('C' . $inner_row, number_format($value['cad'], 6, '.', ''))->setCellValue('D' . $inner_row, number_format($value['usd'], 6, '.', ''));
//Force to show 6 decimal digits
$objPHPExcel->getActiveSheet()->getStyle('C' . $inner_row . ':D' . $inner_row)->getNumberFormat()->setFormatCode('##0.000000');
}
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$objPHPExcel->getActiveSheet()->mergeCells('A' . $row_count . ':A' . $inner_row);
if ($count_fund > 1 && $fund_index != $count_fund) {
$row_count = $inner_row;
} else {
$inner_row++;
$row_count = $inner_row;
}
}
}
$styleThinBorderOutline = array('borders' => array('inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')), 'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))));
$last = $row_count - 1;
$objPHPExcel->getActiveSheet()->getStyle('A1:D' . $last)->applyFromArray($styleThinBorderOutline);
}
//end if
}
//end for loop family post
}
//end if statement of family post
$date_string = __('as of ', 'bridgehouse');
$date_string .= ucfirst(date_i18n(_x('F t Y H:i:s', 'prices date format', 'bridgehouse'), strtotime(date("F j, Y, H:i:s"))));
$footer_cell = $inner_row;
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A' . $footer_cell, __('Downloaded from BridgehouseCanada.com ', 'bridgehouse') . $date_string);
//$objPHPExcel->getActiveSheet()->mergeCells('A'.$footer_cell.':D'.$footer_cell);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
/*$footer_cell +=1;
// Echo memory peak usage
$objPHPExcel->setActiveSheetIndex($count_month)
->setCellValue('A'.$footer_cell, 'Peak memory usage: ' . (memory_get_peak_usage(true) / 1024 / 1024) . ' MB');
*/
// Protect cells
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// Needs to be set to true in order to enable any worksheet protection!
$count_month++;
}
// end month for loop
//ob_end_flush();
} else {
$months = $wpdb->get_results("SELECT `payment_date` FROM `" . $table_prefix . "bh_monthly_distribs` group by `payment_date` order by `payment_date` DESC LIMIT 0, 11");
$count_month = 0;
//ob_start();
foreach ($months as $month) {
$function_args['distribution_month'] = date('Y-m-d', strtotime($month->payment_date));
// echo ucfirst (date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse') , strtotime($month->payment_date))).'<br />';
$myrows = $this->run_query($function_args);
if ($count_month > 0) {
// Create a new worksheet, after the default sheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($count_month);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(ucfirst(date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse'), strtotime($month->payment_date))));
} else {
$objPHPExcel->setActiveSheetIndex(0);
//Set Title
$objPHPExcel->getActiveSheet()->setTitle(ucfirst(date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse'), strtotime($myrows[0]->payment_date))));
}
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
//$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$data = null;
foreach ($myrows as $row) {
if (isset($row->total_factor_usd)) {
$data[$row->fund_id][$row->class_id]['usd'] = sprintf('%-05f', $row->total_factor_usd);
}
if (isset($row->total_factor_cad)) {
$data[$row->fund_id][$row->class_id]['cad'] = sprintf('%-05f', $row->total_factor_cad);
}
}
if (count($myrows) != 0) {
if (!$this->distribution_intro_shown) {
$pay_date = ucfirst(date_i18n(_x('F j, Y', 'Inception date format', 'bridgehouse'), strtotime($myrows[0]->payment_date)));
$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A1', __('The following distributions were paid by Bridgehouse on ', 'bridgehouse') . $pay_date . __(' (record date ', 'bridgehouse') . ucfirst(date_i18n(_x('F j, Y', 'Inception date format', 'bridgehouse'), strtotime($myrows[0]->record_date))) . '):');
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setShrinkToFit(true);
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A2', html_entity_decode(__(get_the_title($solution_family_select), 'bridgehouse'), ENT_QUOTES, 'UTF-8'));
// . ' | '. $this->class_array($solution_class_select));
$objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
//formating
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
//$str .='<tr><td colspan=4><strong>'.get_the_title($solution_family_select).'</strong></td></tr>';
$distribution_intro_shown = true;
}
// get funds for this manager
$funds_for_manager = get_post_meta($solution_family_select, 'BH_funds', true);
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A3', __('Fund', 'bridgehouse'))->setCellValue('B3', __('Class', 'bridgehouse'))->setCellValue('C3', __('CAD Distribution', 'bridgehouse'))->setCellValue('D3', __('US Distribution', 'bridgehouse'));
//formating
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->applyFromArray(array('font' => array('bold' => true), 'borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
$objPHPExcel->getActiveSheet()->getStyle('C3:D3')->applyFromArray(array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT)));
// loop funds
$row_count = 3;
//$row_start= 3;
$inner_row = 3;
foreach ($funds_for_manager as $fund) {
$fund_metabox->the_meta($fund);
//var_dump($fund_metabox);
$solution_fund_id = strtoupper($fund_metabox->get_the_value('short_code'));
// de have valuse for this fund
if (array_key_exists($solution_fund_id, $data)) {
//$str .= '<tr><td>'.get_the_title($fund).'</td><td colspan="3">';
$row_count++;
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A' . $row_count, get_the_title($fund));
//$str .= '<table>';
foreach ($data[$solution_fund_id] as $key => $value) {
$inner_row++;
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('B' . $inner_row, $key)->setCellValue('C' . $inner_row, number_format($value['cad'], 6, '.', ''))->setCellValue('D' . $inner_row, number_format($value['usd'], 6, '.', ''));
$objPHPExcel->getActiveSheet()->getStyle('C' . $inner_row . ':D' . $inner_row)->getNumberFormat()->setFormatCode('##0.000000');
}
//$str .= '</table>';
$objPHPExcel->getActiveSheet()->mergeCells('A' . $row_count . ':A' . $inner_row);
$objPHPExcel->getActiveSheet()->getStyle('A' . $row_count)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$row_count = $inner_row;
//$str .='<tr><td colspan=4></tr>';
}
// end if
}
//end for loop
$styleThinBorderOutline = array('borders' => array('inside' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000')), 'outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => '000000'))));
$objPHPExcel->getActiveSheet()->getStyle('A1:D' . $row_count)->applyFromArray($styleThinBorderOutline);
}
//end if row check
$date_string = __('as of ', 'bridgehouse');
$date_string .= ucfirst(date_i18n(_x('F t Y H:i:s', 'prices date format', 'bridgehouse'), strtotime(date("F j, Y, H:i:s"))));
$footer_cell = $row_count + 1;
$objPHPExcel->setActiveSheetIndex($count_month)->setCellValue('A' . $footer_cell, __('Downloaded from BridgehouseCanada.com ', 'bridgehouse') . $date_string);
//$objPHPExcel->getActiveSheet()->mergeCells('A'.$footer_cell.':D'.$footer_cell);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . $footer_cell)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_DARKBLUE);
/*$footer_cell +=1;
// Echo memory peak usage
$objPHPExcel->setActiveSheetIndex($count_month)
->setCellValue('A'.$footer_cell, 'Peak memory usage: ' . (memory_get_peak_usage(true) / 1024 / 1024) . ' MB');
*/
// Protect cells
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(false);
// Needs to be set to true in order to enable any worksheet protection!
$count_month++;
}
//end month for loop
//ob_end_flush();
}
//end else
//Set Web Form Date as Current Sheet
$objPHPExcel->setActiveSheetIndexByName(ucfirst(date_i18n(_x('F j, Y', 'Payment date format', 'bridgehouse'), strtotime($this->distribution_month))));
$file = __('Bridgehouse_Distributions', 'bridgehouse') . '_' . date_i18n(_x('F j Y', 'prices date format', 'bridgehouse'), strtotime(date("F j, Y, H:i:s")));
//_'.$pay_date.".xls";
$filename = str_replace(' ', '_', $file) . '.xls';
//$file = str_replace("'","’",__(get_the_title($solution_fund_select),'bridgehouse')).'_'.$date_string.".xls";
//$filename = $file;
//header('Content-Encoding: UTF-8');
//header('Content-Type: application/vnd.ms-excel');
// Redirect output to a client’s web browser (Excel2007)
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($objPHPExcel, 'Excel5');
//$objWriter->setOffice2003Compatibility(true);
$objWriter->setUseDiskCaching(true);
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
unset($objWriter);
exit;
/*header('Content-Encoding: UTF-8');
header("Content-type: application/vnd.ms-excel;"); // for .xls
header('Content-type: application/x-msdownload; charset=utf-16');
//header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, charset=utf-8;"); //for .xlsx
header("Content-Disposition: attachment; filename=" . $filename);
header ('Content-Transfer-Encoding: binary');
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
*/
//print ($str);
//exit();
break;
//end distribution
}
// end switch
$cache = ob_get_contents();
ob_end_clean();
wp_cache_set($cache_id, $cache, 'bridgehouse');
return $cache . "<!-- not cached //-->";
}
