<?php
/*
* @package Joomla 1.5
* @copyright Copyright (C) 2005 Open Source Matters. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.php
*
* @component SQL 2 Excel Component
* @copyright Copyright (C) Joomla-R-Us, joomla-r-us.com
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL
*/
// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );
require_once ( JPATH_SITE.DS.'components'.DS.'com_chalange'.DS.'helpers'.DS.'parms.php');
class writeExcel
{
//Crea un excell y lo rellena con los datos de los corredores inscritos a una carrera dada
function createRegisteredSheet($filename, $registered){
require_once(JPATH_SITE.DS.'components'.DS.'com_chalange'.DS.'helpers'.DS.'PEAR'.DS.'Writer_joomlarus.php');
writeExcel::ExcelHeader($filename);
$workbook = new Spreadsheet_Excel_Writer_joomlarus();
$workbook->setVersion(8);
$worksheet1 =& $workbook->addWorksheet(JText::_('REGISTERED SHEET'));
$worksheet1->setInputEncoding('utf-8');
//ancho de las columnas
$worksheet1->setColumn(0, 0, 10);
$worksheet1->setColumn(1, 1, 35);
$worksheet1->setColumn(2, 2, 15);
$worksheet1->setColumn(3, 3, 15);
$worksheet1->setColumn(4, 4, 25);
$worksheet1->setColumn(5, 5, 25);
$worksheet1->setColumn(6, 6, 15);
//cabecera de la tabla
$format_header=& writeExcel::addFormat($workbook, 'Arial',12,'bold','black','none');
writeExcel::writeCell($worksheet1, 0, 0,JText::_('dorsal'),$format_header);
writeExcel::writeCell($worksheet1, 0, 1,JText::_('Names'),$format_header);
writeExcel::writeCell($worksheet1, 0, 2,JText::_('cod_uci'),$format_header);
writeExcel::writeCell($worksheet1, 0, 3,JText::_('numero licencia'),$format_header);
writeExcel::writeCell($worksheet1, 0, 4,JText::_('team'),$format_header);
writeExcel::writeCell($worksheet1, 0, 5,JText::_('sponsor'),$format_header);
writeExcel::writeCell($worksheet1, 0, 6,JText::_('category'),$format_header);
$n=count($registered);
//rellenemos la tabla con los campos pasados
for($i=0; $i<$n; $i++){
$row =& $registered[$i];
writeExcel::writeCell($worksheet1, $i+1, 0, $row->dorsal);
writeExcel::writeCell($worksheet1, $i+1, 1, $row->name);
writeExcel::writeCell($worksheet1, $i+1, 2, $row->codUCI);
writeExcel::writeCell($worksheet1, $i+1, 3, $row->nroLicence);
writeExcel::writeCell($worksheet1, $i+1, 4, $row->club);
writeExcel::writeCell($worksheet1, $i+1, 5, $row->sponsor);
writeExcel::writeCell($worksheet1, $i+1, 6, $row->category);
}
//writeExcel::writeCell($worksheet1, 0, 0, JText::_( 'Workbook does not contain any valid worksheets with data.') );
//writeExcel::writeCell($worksheet1, 1, 0, JText::_( 'Turn on SQL Error Messages in SQL 2 Excel Component to debug the problem.') );
$workbook->close();
}
function writeFile($wbParms, $wsParms, $cmpParms, $substParms)
{
// Get Excel Writer
$writerVersion = writeExcel::get_writer($wbParms, $cmpParms);
switch ($writerVersion) {
case '2000_utf8':
require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'PEAR'.DS.'Writer_joomlarus.php');
break;
case '2000_limited':
default:
require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'Writer'.DS.'Worksheet.php');
require_once(JPATH_SITE.DS.'components'.DS.'com_sql2excel'.DS.'helpers'.DS.'Writer'.DS.'Workbook.php');
}
$db = & JFactory::getDBO();
$wbParms = $wbParms[0];
$extraParms = array();
$extraParms['SQL2EXCEL_WB_LINK'] = $wbParms->link_title;
$extraParms['SQL2EXCEL_WB_DLCNT'] = $wbParms->count + 1;
// SheetNames unique?
writeExcel::checkSheetNames($wsParms, $cmpParms, $substParms);
// Filename
$fileName = trim($wbParms->filename);
$fileName = writeExcel::replace_vars($fileName, $substParms, $extraParms, $cmpParms );
$fileName = trim($fileName);
if ( $fileName == '' || $fileName == null ) {
$fileName = 'download.xls';
}
// Check extension
$path_info = pathinfo($fileName);
if ( !isset($path_info['extension']) ||
( isset($path_info['extension']) && strtolower($path_info['extension']) != 'xls' ) ) {
$fileName .= '.xls';
}
$extraParms['SQL2EXCEL_WB_FN'] = $fileName;
// Write Header
writeExcel::ExcelHeader($fileName);
// Create a workbook
if ( $writerVersion == '2000_utf8' ) {
$workbook = new Spreadsheet_Excel_Writer_joomlarus();
// Set Workbook Version
$workbook->setVersion(8);
} else {
$workbook = new Workbook("-");
}
$i=1;
$nrWorksheets=0;
$marginScaleFactor = (real) Sql2excelParms::get($cmpParms,'marginscalefactor', 1);
$removeCR = Sql2excelParms::get($cmpParms,'removereturnxls', 0);
foreach ( $wsParms as $ws ) {
$sheetName = $ws->sheetname;
$sheetName = writeExcel::replace_vars($sheetName, $substParms,0,$cmpParms);
$sheetName = substr($sheetName,0,40);
if ( trim($sheetName) == '' ) {
$wsIDnr = $nrWorksheets + 1;
$sheetName = 'Sheet ' . $wsIDnr;
}
$extraParms['SQL2EXCEL_WS_SN'] = $sheetName;
$extraParms['SQL2EXCEL_WS_DLCNT'] = $ws->count + 1;
$query = $ws->query;
$extraParms['SQL2EXCEL_WS_SQL_RAW'] = $query;
// Substitute variables in SQL query?
if ( Sql2excelParms::get($cmpParms,'subst_sql', '1') ) {
$query = writeExcel::replace_vars($query, $substParms,0,$cmpParms);
}
$extraParms['SQL2EXCEL_WS_SQL'] = $query;
$formulas = explode(',', $ws->formulas );
if ( trim($query) != '' ) {
// Run query!
$rows = writeExcel::getResults($db, $query, $cmpParms);
$extraParms['SQL2EXCEL_WS_ROWS'] = count($rows);
} else {
$rows = '';
$extraParms['SQL2EXCEL_WS_ROWS'] = 0;
}
// Create a worksheet
$worksheet1 =& $workbook->addWorksheet(substr($sheetName,0,30));
$nrWorksheets++;
// Set encoding
if ( $writerVersion == '2000_utf8' ) {
$worksheet1->setInputEncoding('utf-8');
}
// Zoom level
if ( $ws->zoom != '' && $ws->zoom != 100 ) {
$worksheet1->setZoom($ws->zoom);
}
// Set Margins, if defined
$print_parms = $ws->print_parms;
$print_parms = explode(',',$print_parms);
if ( isset($print_parms[0]) && $print_parms[0] != '' ) {
$worksheet1->setMarginLeft($print_parms[0]*$marginScaleFactor);
}
if ( isset($print_parms[1]) && $print_parms[1] != '' ) {
$worksheet1->setMarginRight($print_parms[1]*$marginScaleFactor);
}
if ( isset($print_parms[2]) && $print_parms[2] != '' ) {
$worksheet1->setMarginTop($print_parms[2]*$marginScaleFactor);
}
if ( isset($print_parms[3]) && $print_parms[3] != '' ) {
$worksheet1->setMarginBottom($print_parms[3]*$marginScaleFactor);
}
// Set column widths - if defined
$colWidths = $ws->colwidths;
$colWidths = explode(',', $colWidths );
for ($i=0; $i<count($colWidths); $i++)
{
if ( is_numeric($colWidths[$i]) && $colWidths[$i] >= 0 ) {
$worksheet1->setColumn($i, $i, $colWidths[$i]);
}
}
// Add Header, if specified
$substHeadFoot = Sql2excelParms::get($cmpParms,'subst_head_footer', '1');
$currRow = 0;
$header = $ws->header;
if ( $header != '' ) {
$headertextArr = explode("\n",$header);
if ( is_array($headertextArr) ) {
if ( $ws->header_parms == '' )
$ws->header_parms = 'Arial,10,normal,black,none';
$hparms = explode(",",$ws->header_parms);
$format_header =& writeExcel::addFormat($workbook, $hparms[0], $hparms[1], $hparms[2], $hparms[3], $hparms[4]);
$format_header_url =& writeExcel::addFormat($workbook, $hparms[0], $hparms[1], $hparms[2], 'blue', $hparms[4]);
foreach ($headertextArr as $headerrow) {
$headerrow = trim($headerrow);
if ( $headerrow != null ) {
if ( $substHeadFoot ) {
$headerrow = writeExcel::replace_vars($headerrow, $substParms, $extraParms, $cmpParms );
}
if ( writeExcel::is_url($headerrow) )
writeExcel::writeCell($worksheet1, $currRow, 0, $headerrow, $format_header_url);
else
writeExcel::writeCell($worksheet1, $currRow, 0, $headerrow, $format_header);
}
$currRow++;
}
}
}
$mindatarow = $currRow;
$maxdatarow = $currRow;
if ( is_array($rows) && is_object($rows[0]) ) {
// Add column titles
$colNames = writeExcel::getColumnNames($rows);
//Number of Columns in user query (exclude JoomFish fields if any)
$nrCols = writeExcel::getColumnCount($colNames, $query);
if ( $ws->show_colheader <> 0 ) {
$headerTextSize = $ws->heading_text_size;
if ( $headerTextSize == null || $headerTextSize == 0 ) { $headerTextSize = 10; }
$headerTextCol = $ws->heading_text_col;
if ( $headerTextCol == null || $headerTextCol == '' ) { $headerTextCol = 'black'; }
$headerBgCol = $ws->heading_bg_col;
if ( $headerBgCol == null || $headerBgCol == '' ) { $headerBgCol = 'white'; }
if ( $writerVersion == '2000_utf8' ) {
$formatot =& $workbook->addFormat();
$formatot->setSize($headerTextSize);
$formatot->setAlign('center');
$formatot->setColor($headerTextCol);
$formatot->setPattern();
$formatot->setFgColor($headerBgCol);
} else {
$formatot =& $workbook->add_format();
$formatot->set_size($headerTextSize);
$formatot->set_align('center');
$formatot->set_color($headerTextCol);
$formatot->set_pattern();
$formatot->set_fg_color($headerBgCol);
}
for ($i=0; $i<$nrCols; $i++)
{
writeExcel::writeCell($worksheet1, $currRow, $i, $colNames[$i], $formatot, $formulas);
}
$currRow++;
}
// Add all the data
$mindatarow = $currRow + 1;
$maxdatarow = $currRow + count($rows);
if ( $ws->data_parms == '' )
$ws->data_parms = 'Arial,10,normal,black,None,none,1,silver,1';
$dparms = explode(",",$ws->data_parms);
if ( sizeof($dparms) >= 9 ) {
$format_data =& writeExcel::addFormat($workbook, $dparms[0], $dparms[1], $dparms[2], $dparms[3]);
$format_data_url =& writeExcel::addFormat($workbook, $dparms[0], $dparms[1], $dparms[2], 'blue');
} else {
$format_data = null;
}
foreach ( $rows as $row ) {
for ( $i=0; $i<$nrCols; $i++) {
if ( sizeof($dparms) >= 9 && $format_data != null ) {
if ( writeExcel::is_url($row->$colNames[$i]) )
writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], $format_data_url, $formulas,$mindatarow,$maxdatarow,$removeCR);
else
writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], $format_data, $formulas,$mindatarow,$maxdatarow,$removeCR);
} else {
writeExcel::writeCell($worksheet1, $currRow, $i, $row->$colNames[$i], 0, $formulas,$mindatarow,$maxdatarow,$removeCR);
}
}
$currRow++;
}
} elseif ( ( $rows == '' || ( is_array($rows) && !is_object($rows[0]) ) ) && $query != '' ) {
// Empty recordset.
$includeNoRec = $ws->include_no_records;
if ( $includeNoRec ) {
$errMsg = $ws->error_norecords;
if ( trim($errMsg) == '' ) {
$errMsg = JText::_( 'No records found' );
writeExcel::writeCell($worksheet1, $currRow, 0, $errMsg);
} else {
$errMsg = explode("\n", $errMsg);
$i=0;
foreach ( $errMsg as $row ) {
$row = trim($row);
$row = writeExcel::replace_vars($row, $substParms, $extraParms, $cmpParms );
writeExcel::writeCell($worksheet1, $currRow + $i, 0, $row);
$i++;
}
}
}
} elseif ( $rows != '' && is_string($rows) && Sql2excelParms::get($cmpParms,'show_sql_errors', 1) ) {
$errMsg = explode("\n", $rows);
$i=0;
foreach ( $errMsg as $row ) {
$row = trim($row);
writeExcel::writeCell($worksheet1, $i, 0, $row);
$i++;
}
}
// Add Footer, if specified
$footer = $ws->footer;
$footertextArr = explode("\n",$footer);
if ( is_array($footertextArr) ) {
if ( $ws->footer_parms == '' )
$ws->footer_parms = 'Arial,10,normal,black,none';
$fparms = explode(",",$ws->footer_parms);
$format_footer =& writeExcel::addFormat($workbook, $fparms[0], $fparms[1], $fparms[2], $fparms[3], $fparms[4]);
$format_footer_url =& writeExcel::addFormat($workbook, $fparms[0], $fparms[1], $fparms[2], 'blue', $fparms[4]);
foreach ($footertextArr as $footerrow) {
$footerrow = trim($footerrow);
if ( $footerrow != null ) {
if ( $substHeadFoot ) {
$footerrow = writeExcel::replace_vars($footerrow, $substParms, $extraParms, $cmpParms );
}
if ( writeExcel::is_url($footerrow) )
writeExcel::writeCell($worksheet1, $currRow, 0, $footerrow, $format_footer_url);
else
writeExcel::writeCell($worksheet1, $currRow, 0, $footerrow, $format_footer);
}
$currRow++;
}
}
}
if ( $nrWorksheets == 0 ) {
$worksheet1 =& $workbook->addWorksheet('Sheet 1');
writeExcel::writeCell($worksheet1, 0, 0, JText::_( 'Workbook does not contain any valid worksheets with data.') );
writeExcel::writeCell($worksheet1, 1, 0, JText::_( 'Turn on SQL Error Messages in SQL 2 Excel Component to debug the problem.') );
}
$workbook->close();
}
////////////////////////////////////////////////////////////
// Check for bad words in an array
// Returns '' if ok
// Returns Error Message if something bad is found.
////////////////////////////////////////////////////////////
function check_bad_words_arr($cmpParms, $wordArr) {
// Get list of bad words to check
$badWordsArr = writeExcel::get_bad_words_array($cmpParms);
$badWordsMsg = '';
if ( is_array($badWordsArr) && is_array($wordArr) ) {
$varNames = array_keys($wordArr);
foreach ( $varNames as $varName ) {
$varValue = $wordArr{$varName};
$varValueArr = explode(' ', $varValue);
foreach ( $varValueArr as $token ) {
$token = strtoupper(trim($token));
if ($token != '' && in_array($token, $badWordsArr) ) {
$badWordsMsg = JText::_( 'BAD_WORDS_ERROR_MSG');
}
}
}
}
return $badWordsMsg;
}
function get_bad_words_array($cmpParms)
{
$badWordsArr = null;
$badWordsStr = Sql2excelParms::get($cmpParms,'badwords', 'ALTER,CREATE,DELETE,DROP,GRANT,INSERT,KILL,LOAD,LOCK,RENAME,REPLACE,REVOKE,SET,TRUNCATE,UPDATE');
if ( trim($badWordsStr) != '' ) {
$badWordsStr = strtoupper($badWordsStr);
$badWordsArr = explode(',',$badWordsStr);
}
return $badWordsArr;
}
////////////////////////////////////////////////////////////
// Check final SQL for bad words
// Returns '' if ok
// Returns Error Message if something bad is found.
////////////////////////////////////////////////////////////
function check_sql_bad_words($cmpParms, $sql) {
// Get list of bad words to check
$badWordsArr = writeExcel::get_bad_words_array($cmpParms);
$sql = str_replace("\n", ' ', $sql);
$sqlWordArr = explode(' ', $sql);
return writeExcel::check_bad_words_arr($cmpParms, $sqlWordArr);
}
function replace_vars($str, $commonVars, $extraVars=0, $cmpParms=0) {
if ( !is_object($cmpParms) ) {
$cmpParms = Sql2excelParms::getParms();
}
$i=0;
$strArr = explode("\n", $str);
foreach ( $strArr as $row ) {
$row = writeExcel::replace_date_codes($row, $cmpParms);
$row = writeExcel::replace_vars_arr($row, $commonVars);
$row = writeExcel::replace_vars_arr($row, $extraVars);
$strArr[$i] = writeExcel::replace_date_codes($row, $cmpParms);
$i++;
}
return implode("\n",$strArr);
}
function replace_vars_arr($str, $varArr) {
$retStr = $str;
if ( is_array($varArr) ) {
$varNames = array_keys($varArr);
foreach ( $varNames as $varName ) {
if ( substr($varName,0,16) == 'SQL2EXCEL_WS_SQL' ) {
// Possible multi-line parameters, convert to one line
$varValues = explode("\n", $varArr{$varName});
$varValue = '';
foreach ( $varValues as $row ) {
$varValue .= trim($row) . ' ';
}
$retStr = str_replace('{' . $varName . '}', trim($varValue), $retStr);
} else {
$retStr = str_replace('{' . $varName . '}', $varArr{$varName}, $retStr);
}
}
}
return $retStr;
}
function writeCell(&$worksheet1, $row, $col, $str, $format=0, $formulas=0, $mindatarow=0, $maxdatarow=65535, $removeCR=0 ) {
if ( $removeCR > 0 ) { $str = writeExcel::removeCR($str, $removeCR); }
if ( substr(trim($str),0,1) == '=' || substr(trim($str),0,1) == '@' ) {
$colUser = $col+1;
$rowUser = $row+1;
if ( is_array($formulas) & in_array($colUser, $formulas) ) {
// Formula! => Parse and replace Row and Col ID's
$str = writeExcel::replace_row_col_ids($str, $rowUser, $colUser, $mindatarow, $maxdatarow);
$worksheet1->write($row, $col, $str, $format);
} else {
// Export as Text
$worksheet1->writeString($row, $col, $str, $format);
}
} elseif( is_string($str) ) {
if ( substr($str,0,1) == "0" && ( strlen($str) > 1 && strpos($str, ".") === false )) {
$worksheet1->writeString($row, $col, $str, $format);
} else {
$worksheet1->write($row, $col, $str, $format);
}
}
}
function removeCR($str,$removeCR) {
$repl = " ";
switch ($removeCR) {
case 2:
$repl = "\n";
break;
case 3:
$repl = "||";
break;
case 4:
$repl = "<br>";
break;
case 5:
$repl = "";
break;
}
$str = str_replace("\r\n",$repl, $str);
$str = str_replace("\n\r",$repl, $str);
$str = str_replace("\n",$repl, $str);
$str = str_replace("\r",$repl, $str);
return $str;
}
function replace_row_col_ids($str, $row, $col,$mindatarow=0, $maxdatarow=0) {
$retStr = $str;
$pos = strpos($str, '{ROWID');
if ( $pos > 0 ) {
$endPos = strpos($str, '}', $pos );
$leftStr = substr($str,0,$pos);
$rightStr = substr($str,$endPos+1);
$midStr = substr($str,$pos+1,$endPos-$pos-1);
$midStr = str_replace('ROWID', $row, $midStr);
eval("\$mStr = $midStr;");
$retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col);
}
$pos = strpos($str, '{COLID');
if ( $pos > 0 ) {
$endPos = strpos($str, '}', $pos );
$leftStr = substr($str,0,$pos);
$rightStr = substr($str,$endPos+1);
$midStr = substr($str,$pos+1,$endPos-$pos-1);
$midStr = str_replace('COLID', $col, $midStr);
eval("\$mStr = $midStr;");
$retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col);
}
$pos = strpos($str, '{MINROWID');
if ( $pos > 0 ) {
$endPos = strpos($str, '}', $pos );
$leftStr = substr($str,0,$pos);
$rightStr = substr($str,$endPos+1);
$midStr = substr($str,$pos+1,$endPos-$pos-1);
$midStr = str_replace('MINROWID', $mindatarow, $midStr);
eval("\$mStr = $midStr;");
$retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col, $mindatarow, $maxdatarow);
}
$pos = strpos($str, '{MAXROWID');
if ( $pos > 0 ) {
$endPos = strpos($str, '}', $pos );
$leftStr = substr($str,0,$pos);
$rightStr = substr($str,$endPos+1);
$midStr = substr($str,$pos+1,$endPos-$pos-1);
$midStr = str_replace('MAXROWID', $maxdatarow, $midStr);
eval("\$mStr = $midStr;");
$retStr = writeExcel::replace_row_col_ids($leftStr . $mStr . $rightStr, $row, $col, $mindatarow, $maxdatarow);
}
return $retStr;
}
function replace_date_codes($str, $cmpParms)
{
$retStr = $str;
$dateMode = Sql2excelParms::get($cmpParms,'datemode', 0);
// date() vs JDate / strtime() codes
$dcodes = array('d' => array('d','d'), // Day of month 01-31
'D' => array('D','a'), // Mon through Sun
'j' => array('j','d'), // Day of the month, 1-31
'l' => array('l','A'), // Sunday through Saturday
'N' => array('N','w'), // 1 (for Monday) through 7 (for Sunday)
'S' => array('S',''), // st, nd, rd or th. Works well with j
'w' => array('w','w'), // 0 (for Sunday) through 6 (for Saturday)
'z' => array('z','j'), // 0 through 365
'W' => array('W','W'), // Week number (not exactly same)
'F' => array('F','B'), // January through December
'm' => array('m','m'), // Month, 01 through 12
'M' => array('M','b'), // Jan through Dec
'n' => array('n','m'), // Month, 1 through 12
't' => array('t',''), // Number days in given month 28 to 31
'L' => array('L',''), // Whether it's a leap year (0 or 1)
'o' => array('o','G'), // ISO-8601 year number.
'Y' => array('Y','Y'), // Year YYYY
'y' => array('y','y'), // Year YY
'a' => array('a','p'), // am/pm
'A' => array('A','p'), // AM/PM
'B' => array('B',''), // Swatch Internet time 000-999
'g' => array('g','I'), // Hour 1 through 12
'G' => array('G','H'), // Hour 0 through 23
'h' => array('h','I'), // Hour 01 through 12
'H' => array('H','H'), // Hour 00 through 23
'i' => array('i','M'), // Minutes with leading zeros, 00 - 59
's' => array('s','S'), // Seconds, with leading zeros
'u' => array('u',''), // Microseconds
'e' => array('e',''), // Timezone abbreviation / offset (depends on OS in Joomla mode)
'I' => array('I',''), // daylight saving time (1 or 0 )
'O' => array('O',''), // Difference to Greenwich time (GMT) in hours (depends on OS in Joomla mode)
'P' => array('P',''), // Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3)
'T' => array('T',''), // Timezone abbreviation
'Z' => array('Z',''), // Timezone offset in seconds.
'c' => array('c',''), // ISO 8601 date (added in PHP 5)
'r' => array('r',''), // RFC 2822 formatted date
'U' => array('U','s') // Seconds since the Unix Epoch
);
global $mainframe;
$mdate = JFactory::getDate();
$mdate->setOffset($mainframe->getCfg('offset'));
foreach(array_keys($dcodes) as $key) {
$code = $dcodes{$key}[$dateMode];
$search = '{' . $key . '}';
if ( $dateMode == 1 ) {
$replace = $mdate->toFormat('%'.$code);
if ( $key == 'z' ) { $replace--; }
if ( ( $key == 'j' || $key == 'n' || $key == 'G' || $key == 'g' ) && substr($replace,0,1) == '0' ) { $replace = substr($replace,1); }
if ( $key == 'N' && $replace == 0 ) { $replace = 7; }
if ( $key == 'a' ) { $replace = strtolower($replace); }
if ( $key == 'U' ) { $replace = time(); }
if ( $key == 't' ) { $replace = writeExcel::days_in_month($mdate->toFormat('%m'), $mdate->toFormat('%Y')); }
if ( $key == 'L' ) { $replace = 0; if ( writeExcel::isleapyear($mdate->toFormat('%Y'))) { $replace = 1; } }
if ( $key == 'I' ) { $replace = date('I'); }
} else {
$replace = date($code);
}
$retStr = str_replace($search,$replace,$retStr);
}
return $retStr;
}
function getResults($db, $query, $cmpParms) {
// Check for bad words in the SQL?
$badWordsMsg = '';
if ( Sql2excelParms::get($cmpParms,'badwordssql', '1') ) {
$badWordsMsg = writeExcel::check_sql_bad_words($cmpParms, $query);
}
if ( $badWordsMsg == '' ) {
// Parse the query for multiple statements
// Make sure we have only \n for line breaks
$queryStr = writeExcel::removeCR($query,2);
// Split statements that end with ; and a new line
$queryTokens = explode(";\n",$query);
$errMsg = '';
$result = true;
// Execute all statements (if more than one)
foreach ( $queryTokens as $queryStmt ) {
if ( is_string($queryStmt) && strlen( $queryStmt) > 0 ) {
$db->setQuery( $queryStmt );
$ret = $db->query();
}
}
$rows = $db->loadObjectList();
if ( !$rows ) {
if ( is_null($rows) ) {
return $db->getErrorMsg();
} else {
return ''; // No results returned
}
} else {
return $rows;
}
} else {
return $badWordsMsg;
}
}
function getColumnNames($rows)
{
$firstRow = $rows[0];
if ( is_object($firstRow) ) {
$colNames = array_keys(get_object_vars($firstRow));
} else {
$colNames = array();
}
return $colNames;
}
// Joom!Fish adds columns to query.
//
// This function gets the column count from the original query string
function getColumnCount($colNames, $query)
{
$query = trim(strtoupper($query));
$query = str_replace("\n", ' ', $query);
$query = writeExcel::remove_quote($query,'`');
$query = writeExcel::remove_quote($query,"'");
$query = writeExcel::remove_quote($query,'"');
// Parentesis
$startP=0;
$retStr = '';
for ( $i=0; $i<strlen($query);$i++) {
$char = substr($query,$i,1);
if ( $char == "(" ) {
$startP++;
} elseif ( $char == ")" ) {
$startP--;
$char = 'Y';
}
if ( $startP > 0 ) { $char = 'Y'; }
$retStr .= $char;
}
$pos = strpos($retStr, ' FROM ');
if ( $pos > 0 && ! strpos($retStr,'*') ) {
$retStr = substr($retStr,0,$pos);
$cols = explode(',', $retStr);
if ( count($cols) < count($colNames) ) {
return count($cols);
} else {
return count($colNames);
}
} else {
return count($colNames);
}
}
function remove_quote($query, $match) {
$found = false;
$retStr = '';
for ( $i=0; $i<strlen($query);$i++) {
$char = substr($query,$i,1);
if ( $found && $char != $match ) {
$char = 'X';
} elseif ( $found && $char == $match ) {
$found = false;
$char = 'X';
} elseif ( !$found && $char == $match ) {
$found = true;
$char = 'X';
}
$retStr .= $char;
}
return $retStr;
}
function convert_utf8($str) {
if ( $this->_writer_version == '2000_limited' ) {
$ret = $str;
if ( function_exists('iconv') ) {
$s = @iconv('UTF-8', 'cp1252//TRANSLIT', $str);
$ret = preg_replace("/([\xC2\xC4])([\x80-\xBF])/e", "chr(ord('\\1')<<6&0xC0|ord('\\2')&0x3F)", $s);
} elseif (function_exists('mb_detect_encoding') && function_exists('utf8_decode') && mb_detect_encoding($str) == 'UTF-8' ) {
$ret = utf8_decode($str);
}
return $ret;
} else {
return $str;
}
}
function ExcelHeader($fileName) {
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"$fileName\"" );
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
}
// Which Excel Writer Version to use
function get_writer($wbParms,$cmpParms)
{
$writerVersion = Sql2excelParms::get($cmpParms,'excelwriter', 'auto');
if ( $writerVersion == 'auto' ) {
if ( function_exists('iconv') && function_exists('mb_strlen') ) {
$writerVersion = '2000_utf8';
} else {
$writerVersion = '2000_limited';
}
}
$this->_writer_version = $writerVersion;
return $writerVersion;
}
function &addFormat(&$workbook, $fontFamily='Arial', $fontSize=0, $fontWeight='normal', $fontCol='none', $fontBgCol='none')
{
$myformat =& $workbook->addFormat();
if ( $fontFamily != 'Arial' ) {
$myformat->setFontFamily($fontFamily);
}
$myformat->setSize($fontSize);
if ( $fontWeight == 'bold' ) {
$myformat->setBold();
}
if ( $fontCol != 'none' && $fontCol != '' ) {
$myformat->setColor($fontCol);
}
if ( $fontBgCol != 'none' && $fontBgCol != '' ) {
$myformat->setPattern();
$myformat->setFgColor($fontBgCol);
}
return $myformat;
}
function is_url($str)
{
if ( substr($str,0,7) == 'http://' )
return true;
else
return false;
}
// calculate number of days in a month
function days_in_month($month, $year) {
return $month == 2 ? ($year % 4 ? 28 : ($year % 100 ? 29 : ($year % 400 ? 28 : 29))) : (($month - 1) % 7 % 2 ? 30 : 31);
}
// Is Leap year?
function isleapyear($year = '') {
if (empty($year)) {
$year = date('Y');
}
$year = (int) $year;
if ($year % 4 == 0) {
if ($year % 100 == 0) {
return ($year % 400 == 0);
} else {
return true;
}
} else {
return false;
}
}
function checkSheetNames($wsParms, $cmpParms, $substParms)
{
$sheetNames = array();
$i=0;
$str = '';
foreach ($wsParms as $ws) {
$sheetNames[$i] = strtolower(trim($ws->sheetname));
$str .= '<li>' . $ws->ws_id . ' - ' . $ws->sheetname . '</li>';
$i++;
}
$uniqueNames = array_unique($sheetNames);
if ( count($sheetNames) != count($uniqueNames) ) {
JError::raiseError(500, '<h1>' . JText::_('Non unique Sheet Names!') . '</h1><p>' . JText::_('The Worksheet Sheet Names does not have unique names.<br>Two more more Worksheets have the same Sheet Name.') . '</p><p>' . JText::_('Please rename the Sheet Names on the Worksheets so that they become unique.') . '</p><ul>' . $str . '</ul>' );
exit;
}
}
}
?>