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 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;
}
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;
}
