function query($datainfo)
{
/*
* Here implement the query code;
*/
if (is_null($datainfo)) {
$datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($datainfo as $key => $value) {
$temp->{$key} = $value;
}
$datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($datainfo as $key => $value) {
$temp->{$key} = $value;
}
$datainfo = $temp;
$this->datainfo = $datainfo;
}
$inputObj = $datainfo->inputObj;
$columnCat = (object) $datainfo->columnCat;
$columnAgg = (object) $datainfo->columnAgg;
$columnAggType = $datainfo->columnAggType;
$select = "SELECT cid(" . $columnCat->cid . ") AS 'Category', ";
$sid = $datainfo->sid;
$table = $datainfo->table;
switch ($columnAggType) {
case "Count":
$select .= "COUNT(cid(" . $columnAgg->cid . ")) AS 'AggValue' ";
break;
case "Sum":
$select .= "SUM(cid(" . $columnAgg->cid . ")) AS 'AggValue' ";
break;
case "Avg":
$select .= "AVG(cid(" . $columnAgg->cid . ")) AS 'AggValue' ";
break;
case "Max":
$select .= "MAX(cid(" . $columnAgg->cid . ")) AS 'AggValue' ";
break;
case "Min":
$select .= "MIN(cid(" . $columnAgg->cid . ")) AS 'AggValue' ";
break;
}
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
$groupby = " GROUP BY cid(" . $columnCat->cid . ") ";
//$queryResult = json_decode('{"cat":"aaa","agg":"bbb","content":[["Year", "Sales"],["2004",15],["2005",12],["2006",16],["2007",18]]}');
$queryEngine = new QueryEngine();
$rst = array();
$rst['content'] = $queryEngine->doQuery($select, $fromArray, null, $groupby, null, null, null);
$rst['columnCat'] = $columnCat->columnName;
$rst['columnAggType'] = $columnAggType;
$rst['sid'] = $sid;
$rst['table'] = $table;
//$rst['pieAggType'] = $pieAggType;
//$rst['$pieColumnCat'] = $pieColumnCat;
$this->queryResult = $rst;
return $rst;
}
function query($_datainfo)
{
if (is_null($_datainfo)) {
$_datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
$this->datainfo = $_datainfo;
}
$inputObj = $_datainfo->inputObj;
$sid = $_datainfo->sid;
$table = $_datainfo->table;
//$pieColumnCat = $_datainfo->pieColumnCat['columnName'];
//$pieColumnAgg = $_datainfo->pieColumnAgg['text'];//$_datainfo->pieColumnAgg[key($_datainfo->pieColumnAgg)];
$pieColumnCat = (object) $_datainfo->pieColumnCat;
$pieColumnAgg = (object) $_datainfo->pieColumnAgg;
$pieAggType = $_datainfo->pieAggType;
$where = $_datainfo->where;
$select = "SELECT cid(" . $pieColumnCat->cid . ") AS 'Category', ";
switch ($pieAggType) {
case "Count":
$select .= "COUNT(cid(" . $pieColumnAgg->cid . ")) AS 'AggValue' ";
break;
case "Sum":
$select .= "SUM(cid(" . $pieColumnAgg->cid . ")) AS 'AggValue' ";
break;
case "Avg":
$select .= "AVG(cid(" . $pieColumnAgg->cid . ")) AS 'AggValue' ";
break;
case "Max":
$select .= "MAX(cid(" . $pieColumnAgg->cid . ")) AS 'AggValue' ";
break;
case "Min":
$select .= "MIN(cid(" . $pieColumnAgg->cid . ")) AS 'AggValue' ";
break;
}
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
$groupby = " GROUP BY cid(" . $pieColumnCat->cid . ") ";
$queryEngine = new QueryEngine();
$rst = array();
//var_dump($select, $fromArray, $groupby);
$rst['content'] = $queryEngine->doQuery($select, $fromArray, null, $groupby, null, null, null);
$rst['pieAggType'] = $pieAggType;
$rst['pieColumnCat'] = $pieColumnCat->columnName;
$rst['sid'] = $sid;
$rst['table'] = $table;
//$queryResult = json_decode('{"string":"aaa","number":"bbb","content":[{"Category":" 2.0","AggValue":"1"},{"Category":" 3.0","AggValue":"2"},{"Category":" 4.0","AggValue":"3"}]}');
$this->queryResult = $rst;
return $rst;
}
function query($_datainfo)
{
/*
* Here implement the query code;
*/
if (is_null($_datainfo)) {
$_datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
$this->datainfo = $_datainfo;
}
$sid = $_datainfo->sid;
$table = $_datainfo->table;
$inputObj = $_datainfo->inputObj;
$comboColumnCat = (object) $_datainfo->comboColumnCat;
$comboColumnAgg = (object) $_datainfo->comboColumnAgg;
$comboAggType = $_datainfo->comboAggType;
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
$select = "SELECT cid(" . $comboColumnCat->cid . ") AS 'Category' ";
for ($i = 0; $i < sizeof($comboAggType); $i++) {
$select .= ", " . $comboAggType[$i] . "(cid(" . $comboColumnAgg->cid . ")) AS '" . $comboAggType[$i] . "' ";
}
$groupby = " GROUP BY cid(" . $comboColumnCat->cid . ") ";
$queryEngine = new QueryEngine();
$rst['content'] = $queryEngine->doQuery($select, $fromArray, null, $groupby, null, null, null);
$rst['comboAggType'] = $comboAggType;
$rst['comboColumnCat'] = $comboColumnCat->columnName;
$rst['comboColumnAgg'] = $comboColumnAgg->columnName;
$rst['sid'] = $sid;
$rst['table'] = $table;
//rst['content'] = json_decode('[{"Category":"aaa","AVG":"13","MAX":"18","MIN":"12"},{"Category":"bbb","AVG":"14","MAX":"16","MIN":"11"},{"Category":"ccc","AVG":"10","MAX":"14","MIN":"6"},{"Category":"ddd","AVG":"43","MAX":"45","MIN":"14"}]');
$this->queryResult = $rst;
return $rst;
}
function query($_datainfo)
{
/*
* Here implement the query code;
*/
if (is_null($_datainfo)) {
$_datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
$this->datainfo = $_datainfo;
}
$sid = $_datainfo->sid;
$table = $_datainfo->table;
$inputObj = $_datainfo->inputObj;
$latitude = (object) $_datainfo->latitude;
$longitude = (object) $_datainfo->longitude;
//$_datainfo->mapTooltip = array("ID","dvalue");
if (isset($_datainfo->mapTooltip)) {
$mapTooltip = $_datainfo->mapTooltip;
}
$where = $_datainfo->where;
$tips = "";
if (empty($mapTooltip)) {
//echo("Please select the columns!");
} else {
for ($i = 0; $i < count($mapTooltip); $i++) {
$mt = (object) $mapTooltip[$i];
$tips .= " cid(" . $mt->cid . ") as " . $mt->columnName;
if (!empty($mapTooltip[$i + 1])) {
$tips .= ",";
}
}
}
$select = "SELECT cid(" . $latitude->cid . ") as 'latitude', cid(" . $longitude->cid . ") as 'longitude'";
if (isset($_datainfo->mapTooltip)) {
$select .= ", " . $tips;
}
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
if (!empty($where)) {
$select .= $where;
}
$queryEngine = new QueryEngine();
//$rst = $db->get_results($sql);
$rst = array();
$rst['content'] = $queryEngine->doQuery($select, $fromArray, null, null, null, null, null);
$rst['sid'] = $sid;
$rst['table'] = $table;
if (isset($_datainfo->mapTooltip)) {
$rst['mapTooltip'] = $mapTooltip;
}
//$rst['content'] = json_decode('[{"latitude":"37.4232","longitude":"-122.0853","ID":"1","dvalue":"3"},{"latitude":"37.42234","longitude":"-122.0134","ID":"2","dvalue":"5"},{"latitude":"37.4452","longitude":"-122.0753","ID":"3","dvalue":"4"}]');
$this->queryResult = $rst;
return $rst;
}
public function GetStoryCorrelationSummary($sid, $tableName)
{
$datasetDAO = new DatasetDAO();
//TODO: get cids of all column in the table by sid and tableName
$columns = $datasetDAO->getTableColumns($sid, $tableName);
$result = array();
$oneRow = array();
$row = 0;
$keys = array_keys($columns);
$values = array_values($columns);
for ($i = 0; $i < sizeof($keys); $i++) {
$queryEngine = new QueryEngine();
$inputObj = new stdClass();
$inputObj->sid = $sid;
$cidi = $keys[$i];
$columnNamei = $values[$i];
$cidiType = $this->statisticsDAO->GetColumnType($cidi);
$oneRow["statistics"] = "Correlation " . $columnNamei;
for ($j = 0; $j < sizeof($keys); $j++) {
$cidj = $keys[$j];
$columnNamej = $values[$j];
$cidjType = $this->statisticsDAO->GetColumnType($cidj);
if (strtoupper($cidiType) == "NUMBER" && strtoupper($cidjType) == "NUMBER") {
$select = "SELECT ROUND(CORR({$values[$i]}, {$values[$j]}),2) AS 'Correlation' ";
$from = (object) array('sid' => $sid, 'tableName' => "[{$tableName}]");
$fromArray = array($from);
$obj = $queryEngine->doQuery($select, $fromArray, null, null, null, null, null);
$oneRow[$columnNamej] = $obj[0]["Correlation"];
//$oneRow[$columnNamej] = "--";
} else {
$oneRow[$columnNamej] = "--";
//continue;
}
}
$result[$row] = $oneRow;
$row++;
}
$columns = NULL;
foreach ($result as $r) {
$json_array["data"][] = $r;
if ($columns === NULL) {
if (is_array($r)) {
$columns = implode(",", array_keys($r));
} else {
$columns = implode(",", array_keys(get_object_vars($r)));
}
}
}
$json_array["Control"]["cols"] = $columns;
return $json_array;
}
public static function GetDistinctForColumns($dataMatcherLinkOnePart, $perPage, $pageNo, $searchTerms = null)
{
$from = (object) array('sid' => $dataMatcherLinkOnePart->sid, 'tableName' => "[{$dataMatcherLinkOnePart->tableName}]");
$fromArray = array($from);
$transHandler = new TransformationHandler();
$columnNames = array();
$columnNamesNoBrack = array();
$whereArray = array();
// foreach ($dataMatcherLinkOnePart->transformation as $key=>$transformation) {
$column = $transHandler->decodeTransformationInput($dataMatcherLinkOnePart->transformation, true);
$columnNames[] = "[{$column}]";
$columnNamesNoBrack[] = $column;
if (isset($searchTerms)) {
if (isset($searchTerms[$dataMatcherLinkOnePart->transformation])) {
$whereArray[] = " [{$column}] like '%" . $searchTerms[$dataMatcherLinkOnePart->transformation] . "%' ";
}
}
// }
$columns = implode(",", array_values($columnNames));
$where = null;
if (count($whereArray) > 0) {
$where = "where " . implode(" and ", array_values($whereArray));
}
$queryEngine = new QueryEngine();
$result = new stdClass();
$result->columns = $columnNamesNoBrack;
$result->rows = $queryEngine->doQuery("SELECT distinct {$columns}", $fromArray, $where, null, null, $perPage, $pageNo);
$result->totalRows = $queryEngine->doQuery("SELECT COUNT(distinct {$columns}) as ct", $fromArray, $where, null, null, null, null);
return $result;
}
function query($_datainfo)
{
/*
* Here implement the query code;
*/
if (is_null($_datainfo)) {
$_datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
$this->datainfo = $_datainfo;
}
$sid = $_datainfo->sid;
$table = $_datainfo->table;
$inputObj = $_datainfo->inputObj;
$color = $_datainfo->color;
$tableColumns = $_datainfo->tableColumns;
$perPage = $_datainfo->perPage;
// controls how many tuples shown on each page
$pageNo = $_datainfo->currentPage;
// controls which page to show, got from JS function
$where = $_datainfo->where;
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
$queryEngine = new QueryEngine();
$totalTuple = $queryEngine->doQuery("SELECT COUNT(*)", $fromArray, null, null, null, null, null);
$tp = 0;
foreach ($totalTuple[0] as $value) {
$tp = $value;
}
$totalPage = ceil(intval($tp) / intval($perPage));
$startPoint = ($pageNo - 1) * $perPage;
$cols = '';
if (empty($tableColumns)) {
//echo("Please select the columns!");
} else {
$transformationHandler = new TransformationHandler();
for ($i = 0; $i < count($tableColumns); $i++) {
$tc = (object) $tableColumns[$i];
$prefix = $transformationHandler->getColumnPrefixByCid($tc->cid, array("tableName", "sid"));
$cols .= "cid(" . $tc->cid . ") as [" . $prefix . $tc->columnName . "] ";
if (!empty($tableColumns[$i + 1])) {
$cols .= ", ";
}
}
}
$select = "SELECT " . $cols;
//var_dump($select);
$resultDoQuery = $queryEngine->doQuery($select, $fromArray, null, null, null, $perPage, $pageNo);
$returnedColumns = array();
//var_dump($resultDoQuery);
if (isset($resultDoQuery) && count($resultDoQuery) > 0) {
if (is_array($resultDoQuery[0])) {
$returnedColumns = array_keys($resultDoQuery[0]);
} else {
if (is_object($resultDoQuery[0])) {
$returnedColumns = array_keys(get_object_vars($resultDoQuery[0]));
}
}
}
$rst['content'] = $resultDoQuery;
$rst["perPage"] = $perPage;
$rst["totalPage"] = $totalPage;
$rst['totalTuple'] = $totalTuple;
$rst["pageNo"] = $pageNo;
$rst['color'] = $color;
$rst['tableColumns'] = $tableColumns;
$rst['returnedColumns'] = $returnedColumns;
$rst['currentPage'] = $pageNo;
$rst['sid'] = $sid;
$rst['table'] = $table;
//$rst = json_decode('{"column":["ID","la","rownum","long","dvalue"],"data":[{"rownum":"1","ID":" 2.0","la":" 35.273637","long":" 108.0","dvalue":" 2.0"},{"rownum":"2","ID":" 3.0","la":" 40.234","long":" 119.0","dvalue":" 3.0"},{"rownum":"3","ID":" 4.0","la":" 29.873","long":" 125.0","dvalue":" 4.0"}],"Control":{"perPage":"50","totalPage":1,"pageNo":"1","cols":["ID","la","long","dvalue"],"color":"blue"}}');
$this->queryResult = $rst;
return $rst;
}
function query($_datainfo)
{
/*
* Here implement the query code;
*/
if (is_null($_datainfo)) {
$_datainfo = $this->datainfo;
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
} else {
$temp = new stdClass();
foreach ($_datainfo as $key => $value) {
$temp->{$key} = $value;
}
$_datainfo = $temp;
$this->datainfo = $_datainfo;
}
$sid = $_datainfo->sid;
$table = $_datainfo->table;
$inputObj = $_datainfo->inputObj;
$firstColumn = (object) $_datainfo->firstColumn;
$dateColumn = (object) $_datainfo->dateColumn;
$otherColumns = $_datainfo->otherColumns;
$where = $_datainfo->where;
$valueColumns = "";
$valueColumnsArray = array();
if (empty($otherColumns)) {
echo "Please select the columns!";
} else {
for ($i = 0; $i < count($otherColumns); $i++) {
$oc = (object) $otherColumns[$i];
$valueColumns .= "cid(" . $oc->cid . ") as 'valueColumn" . $i . "'";
array_push($valueColumnsArray, "valueColumn" . $i);
if (!empty($otherColumns[$i + 1])) {
$valueColumns .= ", ";
}
}
}
$select = "SELECT cid(" . $firstColumn->cid . ") as 'firstColumn', cid(" . $dateColumn->cid . ") as 'dateColumn', ";
$select .= $valueColumns;
if (!empty($where)) {
$select .= $where;
}
$from = (object) array('inputObj' => $inputObj, 'tableName' => "[{$table}]");
$fromArray = array($from);
$queryEngine = new QueryEngine();
//TODO: Return date as three separate values: year, month, day
$rst = array();
$rst['firstColumn'] = $firstColumn->cid;
$rst['dateColumn'] = $dateColumn->cid;
$rst['originalOtherColumns'] = $otherColumns;
$rst['otherColumns'] = $valueColumnsArray;
//array('numberofCase','precipitation','state');
$rst['sid'] = $sid;
$rst['table'] = $table;
echo $select;
$rst['content'] = $queryEngine->doQuery($select, $fromArray, null, null, null, null, null);
// foreach($valueColumns as $value){
// array_push($rst['otherColumns'], $value);
// }
$this->queryResult = $rst;
return $rst;
// $rst = array();
//$rst['firstColumn'] = 'disease';
//$rst['dateColumn'] = 'date';
//$rst['otherColumns'] = array('numberofCase','precipitation','state');
//$rst['content'] = json_decode('[{"disease":"flu","year":"2000","month":"10","numberOfCases":"123","precipitation":"13","state":"PA"},{"disease":"phthisis","year":"2000","month":"10","numberOfCases":"126","precipitation":"15","state":"CA"},{"disease":"anemia","year":"2000","month":"10","numberOfCases":"128","precipitation":"17","state":"NY"}]');
}
