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 importDataFromDumpFile($sid, DatabaseHandler $dbHandler, $userId, $filePath, $my_pligg_base_no_slash)
{
$ktrExeDao = new KTRExecutorDAO();
$tableNames = $dbHandler->loadTables();
$logIds = array();
foreach ($tableNames as $tableName) {
$logIds[$tableName] = $ktrExeDao->addExecutionInfoTuple($sid, $tableName, $userId);
}
try {
$dbImporter = DatabaseImporterFactory::createDatabaseImporter($dbHandler->getDriver(), $sid, $my_pligg_base_no_slash);
$dbImporter->importDbData($filePath);
foreach ($logIds as $logId) {
$ktrExeDao->updateExecutionInfoStatus($logId, 'success');
}
} catch (Exception $e) {
foreach ($logIds as $logId) {
$ktrExeDao->updateExecutionInfoStatus($logId, 'error');
$ktrExeDao->updateExecutionInfoErrorMessage($logId, $e->getMessage());
}
}
$queryEngine = new QueryEngine();
foreach ($logIds as $tableName => $logId) {
$numProcessed = $queryEngine->GetTotalNumberTuplesInTableBySidAndNameFromExternalDB($sid, $tableName);
$ktrExeDao->updateExecutionInfoTupleAfterPanTerminated($logId, 0, '', $numProcessed, 'success');
}
}
function MineRelationships()
{
$queryEngine = new QueryEngine();
$perPage = $_POST["perPage"];
$pageNo = $_POST["pageNo"];
$result = $queryEngine->MineRelationships($_POST["sid"]);
$columns = NULL;
if ($result != 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"]["perPage"] = $perPage;
$json_array["Control"]["totalPage"] = 40;
$json_array["Control"]["pageNo"] = $pageNo;
$json_array["Control"]["cols"] = $columns;
echo json_encode($json_array);
}
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;
}
public function testAddRelationshipWithPartialData()
{
$queryEngine = new QueryEngine();
$user_id = 20;
$from = ['sid' => 753, 'tableName' => 'Sheet1', 'columns' => ['cid(616) + cid(617)']];
$to = ['sid' => 752, 'tableName' => 'Sheet1', 'columns' => ['cid(609)']];
$confidence = 0;
$queryEngine->AddRelationship($user_id, $name, $description, $from, $to, $confidence, $comment);
}
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;
}
/**
* Returns execution status with additional information like how many records were processed.
* @param $sid sid of the story
* @return stdClass info about the status
*/
public static function getExecutionStatus($sid)
{
$ktrExecutorDAO = new KTRExecutorDAO();
$tuplesFromExecuteInfoTable = $ktrExecutorDAO->getTuplesBySid($sid);
$queryEngine = new QueryEngine();
if (!isset($tuplesFromExecuteInfoTable) || count($tuplesFromExecuteInfoTable) == 0) {
$tuplesFromExecuteInfoTable = array();
$tables = $queryEngine->GetTablesList($sid);
// Add table into exec info.
foreach ($tables as $key => $tableName) {
$rec = new stdClass();
$rec->tableName = $tableName;
$rec->status = "success";
$rec->Eid = "NA";
$rec->ErrorMessage = "";
$rec->RecordsProcessed = $queryEngine->GetTotalNumberTuplesInTableBySidAndName($sid, "[{$tableName}]");
$rec->Sid = $sid;
$rec->TimeEnd = "2013-08-18 12:21:19";
//FIXME
$rec->TimeStart = "2013-08-18 12:21:19";
$rec->UserId = "NA";
$tuplesFromExecuteInfoTable[] = $rec;
}
}
$result = array();
foreach ($tuplesFromExecuteInfoTable as $i => $tupleFromExecuteInfoTable) {
if ($tupleFromExecuteInfoTable->status == 'success' || $tupleFromExecuteInfoTable->status == 'error') {
$tupleFromExecuteInfoTable->numberProcessRecords = $queryEngine->GetTotalNumberTuplesInTableBySidAndName($sid, "[{$value->tableName}]");
//$tupleFromExecuteInfoTable->numberProcessRecords = $tupleFromExecuteInfoTable->RecordsProcessed;
} else {
//TODO FIXME: table name should not be wrapped into [] at this spet. Need global refactoring to move wrapping table into brackets closer to the query execution
$tupleFromExecuteInfoTable->numberProcessRecords = $queryEngine->GetTotalNumberTuplesInTableBySidAndName($sid, "[{$value->tableName}]");
}
$result[] = $tupleFromExecuteInfoTable;
}
return $result;
}
<?php
include_once realpath(dirname(__FILE__)) . '/../config.php';
include_once realpath(dirname(__FILE__)) . '/../DAL/QueryEngine.php';
error_reporting(E_ALL ^ E_STRICT ^ E_NOTICE);
ini_set('display_errors', 1);
$from = new stdClass();
$from->sid = 751;
$from->tableName = "Extra-State War Participants (V";
$from->links = array("cid(593)", "cid(594)");
// size of this array should be equal to the size of array in To
$searchTerms = array("cid(593)" => "30", "cid(594)" => "00.");
$queryEngine = new QueryEngine();
echo json_encode($queryEngine->GetDistinctForColumns($from, 10, 1, $searchTerms));
?>
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;
}
private function getColumnsBySid($sid, $tableName = null)
{
$queryEngine = new QueryEngine();
$result = $queryEngine->GetTablesInfo($sid);
return $result[$tableName];
}
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 CheckDataMatching()
{
$from = $_POST["from"];
$to = $_POST["to"];
$queryEngine = new QueryEngine();
echo json_encode($queryEngine->CheckDataMatching($from, $to));
}
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"}]');
}
