<?php

require_once($_SERVER['DOCUMENT_ROOT'] . '/sys/db.php');

function buildCombined ($id, $query) {

  $dal = new DAL();

  $sql = "SELECT RQ.id, query, combinequery_id, " .
         "(CASE WHEN CT.text = 'COMBINE' THEN '' ELSE CONCAT(CT.text, ' ') END) as combine " .
         "FROM reportquery RQ, combinetype CT " .
         "WHERE RQ.id = $id AND RQ.combineidx > 0 " .
         "AND RQ.combinetype_id = CT.id AND RQ.active = 1 LIMIT 1" ;
  $report = $dal->query($sql);

  if (null == $report)
    return $query;
  else {
//  print_r ("query:$report->query XX");      
    return buildCombined ($report->combinequery_id, $query .
                          $report->combine . $report->query . " ");
  }                          
}  

function buildCriteria ($criteria, $offset, $where) {
//print_r ("crit:$criteria $offset $where ");    
  $start = strpos ($where, "?", $offset);
  if ($start == false)
    return $criteria;
  else {
    $comma = ($criteria == "") ? "" : ",";  
    $offset = $start;
    $end = strpos ($where, " ", $offset);
    if (!$end) {
      $next = substr ($where, $start);
      $end = strlen($where); 
    }
    else { 
      $length = $end - $start;
      $next = substr ($where, $start, $length);
    }  
    return buildCriteria($criteria . $comma . $next, $end, $where);
  }
}   

foreach ($_POST as $paramName => $paramValue) {
  if ($paramName == "id")
    $rq_id = $paramValue;
  else if ($paramName == "filename")
    $filename = $paramValue;
  else if ($paramName == "reportdescr")
    $reportdescr = $paramValue;
  else if ($paramName == "queryselect")
    $queryselect = $paramValue;
  else if ($paramName == "queryfrom")
    $queryfrom = $paramValue;
  else if ($paramName == "querywhere")
    $querywhere = $paramValue;
  else if ($paramName == "queryorganize")
    $queryorganize = $paramValue;
  elseif ($paramName == "wherefields") 
    $wherefields = $paramValue;  
  elseif ($paramName == "combinetype_id") 
    $combinetype_id = $paramValue;  
}

$catqueryselect = str_replace("\n", " ", $queryselect); // remove new lines
$catqueryfrom   = str_replace("\n", " ", $queryfrom); // remove new lines
$catquerywhere  = str_replace("\n", " ", $querywhere); // remove new lines

$wherefields  = buildCriteria ("", 0, $catquerywhere);

$query = "SELECT " .  $catqueryselect . " FROM " . $catqueryfrom . 
         " WHERE " . $catquerywhere . " " .  $queryorganize ;

$dal = new DAL(); 

if ($rq_id == 0) {
  $sql = "INSERT INTO reportquery " .
         "(filename, reportdescr, queryselect, queryfrom, querywhere, " .
         "queryorganize, wherefields, query, combinetype_id) " .
         "VALUES ('$filename', '$reportdescr', '$queryselect', '$queryfrom', " .
         "'$querywhere', '$queryorganize', '$wherefields', '$query', " .
         "'$combinetype_id') " ;
  $rq_id = $dal->query($sql);

  $sql = "INSERT INTO reportquerysetup () VALUES () " ;
  $result = $dal->query($sql);
}
else {
  $sql = "UPDATE reportquery SET " .
         "filename='$filename', reportdescr='$reportdescr', " .
         "queryselect='$queryselect', queryfrom='$queryfrom', " .
         "querywhere='$querywhere', queryorganize='$queryorganize', " .
         "wherefields='$wherefields', query='$query' " .
         "WHERE id = $rq_id ";

  $result = $dal->query($sql);
}         

$combinedQuery = buildCombined ($rq_id, "");

if ($combinedQuery != "") {
  $sql = "UPDATE reportquery SET " .
         "combinedQuery='$combinedQuery' " .
         "WHERE id = $rq_id ";
  $result = $dal->query($sql);
} 

echo json_encode($rq_id);

?>

