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