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"}]'); }