function process_spreadsheet($path)
{
$keys = array();
$newArray = array();
$data = csv_to_array($path, ',');
// Set number of elements (minus 1 because we shift off the first row)
$count = count($data) - 1;
//Use first row for names
$labels = array_shift($data);
foreach ($labels as $label) {
$keys[] = $label;
}
// Add Ids, just in case we want them later
$keys[] = 'id';
for ($i = 0; $i < $count; $i++) {
$data[$i][] = $i;
}
// Bring it all together
for ($j = 0; $j < $count; $j++) {
$d = array_combine($keys, $data[$j]);
if (!is_empty_str($d["ID"])) {
// $newArray[$j] = $d;
$newArray[$d["ID"]] = $d;
}
}
return $newArray;
}
function getExpressionTree($expr)
{
global $g_sqlSingleRecFuncs;
$parser = new SqlParser($expr);
$king_expr = new Expression();
$current_expr =& $king_expr;
while (!is_empty_str($elem = $parser->parseNextElementRaw())) {
// function or IN / NOT IN?
if (in_array(strtoupper($elem), $g_sqlSingleRecFuncs) || strtoupper($elem) == "IN" || strtoupper($elem) == "NOT IN") {
$current_expr->expr_str .= $elem . " ";
$elem = $parser->parseNextElementRaw();
if ($elem != "(") {
print_error_msg("( expected after " . $current_expr->expr_str);
return null;
}
$current_expr->expr_str .= $elem;
while (!is_empty_str($elem = $parser->parseNextElementRaw()) && $elem != ")") {
$current_expr->expr_str .= $elem;
}
$current_expr->expr_str .= $elem . " ";
continue;
}
if ($elem == "(") {
$current_expr->expr_str .= " % ";
unset($new_expr);
$new_expr = new Expression("");
$current_expr->addChild($new_expr);
$new_expr->setParent($current_expr);
unset($current_expr);
$current_expr =& $new_expr;
} else {
if ($elem == ")") {
unset($tmp);
$tmp =& $current_expr->getParent();
unset($current_expr);
$current_expr =& $tmp;
} else {
// no spaces on .'s
if ($elem == ".") {
remove_last_char($current_expr->expr_str);
$current_expr->expr_str .= $elem;
} else {
$current_expr->expr_str .= $elem . " ";
}
}
}
}
return $king_expr;
}
function executeSingleRecFuncs()
{
global $g_sqlSingleRecFuncs;
global $g_sqlMathOps;
debug_printb("[executeSingleRecFuncs] executing singlerec functions...<br>");
for ($i = 0; $i < count($this->colFuncs); ++$i) {
if (!$this->colFuncs[$i] || $this->colFuncsExecuted[$i]) {
continue;
}
if (!in_array($this->colFuncs[$i], $g_sqlSingleRecFuncs)) {
continue;
}
debug_print($this->colFuncs[$i] . "(" . $this->colNames[$i] . "): ");
// EVAL
if ($this->colFuncs[$i] == "EVAL") {
$eval_str = $this->colNames[$i];
$out_str = "";
if (has_quotes($eval_str)) {
remove_quotes($eval_str);
}
debug_print("EVAL function, eval String is {$eval_str}!<br>");
$sp = new StringParser();
$sp->specialElements = $g_sqlMathOps;
$sp->setString($eval_str);
while (!is_empty_str($elem = $sp->parseNextElement())) {
debug_print("ELEM: {$elem}\n");
if (is_numeric($elem) || in_array($elem, $g_sqlMathOps)) {
$out_str .= $elem . " ";
} else {
$origColNr = $this->findColNrByAttrs($elem, "", "");
if ($origColNr == NOT_FOUND) {
print_error_msg("EVAL: Column '" . $elem . "' not found!");
return false;
}
$out_str .= "%{$origColNr}%";
}
}
debug_print("New Eval String: {$out_str}\n");
$val_str = "";
// apply function (use values from the original column as input)
$rowCount = count($this->rows);
$colCount = count($this->colNames);
for ($j = 0; $j < $rowCount; ++$j) {
$val_str = $out_str;
for ($k = 0; $k < $colCount; ++$k) {
if (!is_false(strpos($val_str, "%{$k}%"))) {
$val_str = str_replace("%{$k}%", $this->rows[$j]->fields[$k], $val_str);
}
}
debug_print("VAL_STR={$val_str}\n");
$this->rows[$j]->fields[$i] = execFunc($this->colFuncs[$i], $val_str);
}
$this->colFuncsExecuted[$i] = true;
// function with paramater, but the parameter is not a column
} else {
if ($this->colNames[$i] && !is_empty_str($this->colNames[$i]) && (is_numeric($this->colNames[$i]) || has_quotes($this->colNames[$i]))) {
$param = $this->colNames[$i];
if (has_quotes($param)) {
remove_quotes($param);
}
$result = execFunc($this->colFuncs[$i], $param);
$rowCount = count($this->rows);
debug_print("a function with a non-column parameter! (result={$result})<br>");
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = $result;
}
$this->colFuncsExecuted[$i] = true;
// function with parameter? =>execute function with the values form the original column
} else {
if ($this->colNames[$i]) {
debug_print("a function with a column parameter!<br>");
// find original column (without function)
$origColNr = $this->findColNrByAttrs($this->colNames[$i], $this->colTables[$i], "");
if ($origColNr == NOT_FOUND) {
print_error_msg("Column '" . $this->colNames[$i] . "' not found!");
return false;
}
// copy some column header data from the original
$this->colTables[$i] = $this->colTables[$origColNr];
$this->colTableAliases[$i] = $this->colTableAliases[$origColNr];
// apply function (use values from the original column as input)
$rowCount = count($this->rows);
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = execFunc($this->colFuncs[$i], $this->rows[$j]->fields[$origColNr]);
}
$this->colFuncsExecuted[$i] = true;
// function without parameter: just execute!
} else {
debug_print("a function with no parameters!<br>");
$result = execFunc($this->colFuncs[$i], "");
$rowCount = count($this->rows);
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = $result;
}
$this->colFuncsExecuted[$i] = true;
}
}
}
}
}
function parseNextElements($separatorElement, $finishElements, &$arrParsedElements)
{
$arrParsedElements = array();
while (!is_empty_str($elem = $this->peekNextElement())) {
if (strtoupper($elem) == strtoupper($separatorElement)) {
$this->skipNextElement();
break;
}
for ($i = 0; $i < count($finishElements); ++$i) {
if (strtoupper($elem) == strtoupper($finishElements[$i])) {
break 2;
}
}
$arrParsedElements[] = $elem;
$this->skipNextElement();
}
if (count($arrParsedElements) > 0) {
return true;
}
return false;
}
function parseListTablesQuery()
{
$sqlObj = new SqlQuery();
$sqlObj->type = "LIST TABLES";
$colNames = array();
$colTables = array();
$colAliases = array();
$fieldValues = array();
$tables = array();
$groupColumns = array();
$orderColumns = array();
$orderTypes = array();
$where_expr = "";
$distinct = 0;
// parse Where statement (Raw, because the escape-chars are needend in the ExpressionParser)
if (strtoupper($this->peekNextElement()) == "WHERE") {
$this->skipNextElement();
while (!is_empty_str($elem = $this->peekNextElementRaw())) {
if (strtoupper($elem) == "ORDER" || $elem == ";" || strtoupper($elem) == "LIMIT") {
break;
}
$this->skipNextElement();
// no " " on points
if ($elem == ".") {
remove_last_char($where_expr);
$where_expr .= $elem;
} else {
$where_expr .= $elem . " ";
}
}
}
// parse ORDER BY
$orderColumnIndex = 0;
if (strtoupper($this->peekNextElement()) == "ORDER") {
$this->skipNextElement();
if (strtoupper($this->parseNextElement()) != "BY") {
print_error_msg("BY expected");
return null;
}
while (!is_empty_str($elem = $this->peekNextElement())) {
if ($elem == ";" || strtoupper($elem) == "LIMIT") {
break;
}
$this->skipNextElement();
if ($elem == ",") {
$orderColumnIndex++;
} else {
if (strtoupper($elem) == "ASC") {
$orderTypes[$orderColumnIndex] = ORDER_ASC;
} else {
if (strtoupper($elem) == "DESC") {
$orderTypes[$orderColumnIndex] = ORDER_DESC;
} else {
if (!isset($orderColumns[$orderColumnIndex])) {
$orderColumns[$orderColumnIndex] = $elem;
} else {
$orderColumns[$orderColumnIndex] .= $elem;
}
$orderTypes[$orderColumnIndex] = ORDER_ASC;
}
}
}
}
}
// parse LIMIT
$limit = array();
if (strtoupper($this->peekNextElement()) == "LIMIT") {
$this->skipNextElement();
while (!is_empty_str($elem = $this->peekNextElement())) {
if ($elem == ";") {
break;
}
$this->skipNextElement();
if ($elem != ",") {
$limit[] = $elem;
}
}
}
$sqlObj = new SqlQuery("LIST TABLES", $colNames, $tables, $colAliases, $colTables, $where_expr, $groupColumns, $orderColumns, $orderTypes, $limit);
return $sqlObj;
}
function has_quotes($str)
{
if (is_empty_str($str)) {
return false;
}
return ($str[0] == "'" || $str[0] == "\"") && (last_char($str) == "'" || last_char($str) == "\"");
}
