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;
}
public function GetLinksByRelId($relId, $simThreshold)
{
$links = array();
$linksSql = "select cl_from, cl_to from `colfusion_relationships_columns` where rel_id = '" . mysql_real_escape_string($relId) . "'";
$linkInfos = $this->ezSql->get_results($linksSql);
$linkRatioSql = "select cl_from, cl_to, dataMatchingFromRatio, dataMatchingToRatio from colfusion_relationships_columns_dataMathing_ratios where similarity_threshold = {$simThreshold}";
$linkRatios = $this->ezSql->get_results($linkRatioSql);
$linkRatioValues = array();
foreach ($linkRatios as $linkRatio) {
$linkRatioValues["{$linkRatio->cl_from}_{$linkRatio->cl_to}"]['fromRatio'] = $linkRatio->dataMatchingFromRatio;
$linkRatioValues["{$linkRatio->cl_from}_{$linkRatio->cl_to}"]['toRatio'] = $linkRatio->dataMatchingToRatio;
}
$transHandler = new TransformationHandler();
foreach ($linkInfos as $linkInfo) {
$link = new ColfusionLink();
$link->fromPart = $transHandler->decodeTransformationInput($linkInfo->cl_from, true);
$link->toPart = $transHandler->decodeTransformationInput($linkInfo->cl_to, true);
$link->fromPartEncoded = $linkInfo->cl_from;
$link->toPartEncoded = $linkInfo->cl_to;
$link->fromRatio = $linkRatioValues["{$link->fromPartEncoded}_{$link->toPartEncoded}"]['fromRatio'];
$link->toRatio = $linkRatioValues["{$link->fromPartEncoded}_{$link->toPartEncoded}"]['toRatio'];
$links[] = $link;
}
return $links;
}
public function doQuery($select, $from, $where, $groupby, $relationships, $perPage, $pageNo)
{
//TODO: fix it, transformation should also accept sybmols in which to enclose table and column names
$transHandler = new TransformationHandler();
if (count($from) == 1) {
// then we don't need to do any joins.
$dataset = $from[0];
//TODO seems like inputObj is an array. This need to be fixed
if (is_array($dataset->inputObj)) {
$dataset->inputObj = json_decode(json_encode($dataset->inputObj));
}
if (isset($dataset->inputObj->oneSid)) {
if ($dataset->inputObj->oneSid === "true" || $dataset->inputObj->oneSid === true) {
//onde story
$datasetObj = (object) array('sid' => $dataset->inputObj->sid, 'tableName' => $dataset->tableName);
//NOTE: $select, $where and $group cannot have [] sybmols comming directly from column or table name
$select = $transHandler->decodeTransformationInput($select, true);
$where = $transHandler->decodeTransformationInput($where, true);
$groupby = $transHandler->decodeTransformationInput($groupby, true);
return $this->prepareAndRunQuery($select, $datasetObj, $where, $groupby, $perPage, $pageNo);
} else {
//merged stories.
return $this->prepareAndRunMergedDatasetQuery($select, $dataset->inputObj, $where, $groupby, $perPage, $pageNo);
}
} else {
//NOTE: $select, $where and $group cannot have [] sybmols comming directly from column or table name
$select = $transHandler->decodeTransformationInput($select, true);
$where = $transHandler->decodeTransformationInput($where, true);
$groupby = $transHandler->decodeTransformationInput($groupby, true);
return $this->prepareAndRunQuery($select, $dataset, $where, $groupby, $perPage, $pageNo);
//TODO: fix it, transformation should also accept sybmols in which to enclose table and column names
}
} else {
// need to do joins
// TODO: implement
}
}
public function GetColumnsFromSource($source)
{
// get string names of columns which are involved in the links
$transHandler = new TransformationHandler();
$i = 0;
$cidsArray = array();
$columnNamesArray = array();
$columnNameAndAliasArray = array();
$columnAliasArray = array();
// foreach ($source->transformation as $key=>$transformation) {
//TODO: fix, currently only one column, no transformation is supported.
$ar = array("cid(", ")");
$cidsArray[] = str_replace($ar, "", $source->transformation);
$columnName = $transHandler->decodeTransformationInput($source->transformation, true);
// DALUtils::decodeLinkPart($link, $usedColumnNames);
$columnNamesArray[] = "[" . $columnName . "]";
// $columnAlias = "column$i";
$i += 1;
if (isset($columnAlias)) {
$columnNameAndAliasArray[] = "[" . $columnName . "] as '" . $columnAlias . "'";
} else {
$columnNameAndAliasArray[] = "[" . $columnName . "]";
}
$columnAliasArray[] = $columnAlias;
// }
$result = new stdClass();
$result->cids = implode(",", array_values($cidsArray));
$result->columnNames = implode(",", array_values($columnNamesArray));
$result->columnNameAndAlias = implode(",", array_values($columnNameAndAliasArray));
$result->columnAliases = implode(",", array_values($columnAliasArray));
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;
}
include_once realpath(dirname(__FILE__)) . '/DataMatcher.php';
include_once realpath(dirname(__FILE__)) . '/DataMatcherLinkOnePart.php';
include_once realpath(dirname(__FILE__)) . '/../DAL/TransformationHandler.php';
if (!$current_user->authenticated) {
die('Please login to use this function.');
}
$fromSid = $_POST["fromSid"];
$fromTable = $_POST["fromTable"];
$toSid = $_POST["toSid"];
$toTable = $_POST["toTable"];
$fromTransInput = $_POST["fromTransInput"];
//TODO FIXME this is not correct, we shoudl send tranformation values e.g. cid(), not decoded values, because what is there is a formula?Or make sure this approach works
$toTransInput = $_POST["toTransInput"];
//TODO FIXME this is not correct, we shoudl send tranformation values e.g. cid(), not decoded values, because what is there is a formula?
$action = $_POST["action"];
$transHandler = new TransformationHandler();
$encodedFromTrans = $transHandler->encodeTransformationInput($fromSid, $fromTable, $fromTransInput);
//$fromCids = $transHandler->getWrappedCids($encodedFromTrans);
$encodedToTrans = $transHandler->encodeTransformationInput($toSid, $toTable, $toTransInput);
//$toCids = $transHandler->getWrappedCids($encodedToTrans);
$from = new DataMatcherLinkOnePart();
$from->sid = $fromSid;
$from->tableName = $fromTable;
$from->transformation = $encodedFromTrans;
$to = new DataMatcherLinkOnePart();
$to->sid = $toSid;
$to->tableName = $toTable;
$to->transformation = $encodedToTrans;
// Params for data-tables server-side processing.
$pageLength = $_POST['pageLength'];
$page = $_POST['page'];
public static function DecodeStringWithCids($stringToDecode)
{
// var_dump($stringToDecode);
$transHandler = new TransformationHandler();
return $transHandler->decodeTransformationInputWithPrefix($stringToDecode, array("tableName", "sid"));
}
