public function getAllEntries($tableName, $uploadId, $uploadTreeTableName, $type = null, $onlyCleared = false, $decisionType = null, $extrawhere = null)
{
$statementName = __METHOD__ . $tableName . $uploadTreeTableName;
$params = array();
$whereClause = "";
$distinctContent = "";
$tableNameDecision = $tableName . "_decision";
if ($uploadTreeTableName === "uploadtree_a") {
$params[] = $uploadId;
$whereClause .= " AND UT.upload_fk = \$" . count($params);
$statementName .= ".withUI";
}
if ($type == "skipcontent") {
$distinctContent = "";
} else {
$distinctContent = ", C.content";
}
if ($type !== null && $type != "skipcontent") {
$params[] = $type;
$whereClause .= " AND C.type = \$" . count($params);
$statementName .= ".withType";
}
$clearingTypeClause = null;
if ($onlyCleared) {
$joinType = "INNER";
if ($decisionType !== null) {
$params[] = $decisionType;
$clearingTypeClause = "WHERE clearing_decision_type_fk = \$" . count($params);
$statementName .= ".withDecisionType";
} else {
throw new \Exception("requested only cleared but no type given");
}
} else {
$joinType = "LEFT";
if ($decisionType !== null) {
$params[] = $decisionType;
$clearingTypeClause = "WHERE clearing_decision_type_fk IS NULL OR clearing_decision_type_fk = \$" . count($params);
$statementName .= ".withDecisionType";
}
}
$statementName .= "." . $joinType . "Join";
if ($extrawhere !== null) {
$whereClause .= " AND " . $extrawhere;
$statementName .= "._" . $extrawhere . "_";
}
$latestInfo = "SELECT DISTINCT ON(CD.pfile_fk, UT.uploadtree_pk{$distinctContent})\n CD.description as description, CD.textfinding as textfinding,\n CD.comment as comments, UT.uploadtree_pk as uploadtree_pk,\n CD.clearing_decision_type_fk AS clearing_decision_type_fk,\n C.content AS content\n FROM {$tableName} C\n INNER JOIN {$uploadTreeTableName} UT\n ON C.pfile_fk = UT.pfile_fk\n {$joinType} JOIN {$tableNameDecision} CD\n ON C.pfile_fk = CD.pfile_fk\n WHERE C.content IS NOT NULL AND C.content!='' {$whereClause} \n ORDER BY CD.pfile_fk, UT.uploadtree_pk, C.content, CD.copyright_decision_pk DESC";
if ($clearingTypeClause !== null) {
$sql = "SELECT * FROM ({$latestInfo}) AS latestInfo {$clearingTypeClause}";
} else {
$sql = $latestInfo;
}
$this->dbManager->prepare($statementName, $sql);
$sqlResult = $this->dbManager->execute($statementName, $params);
$result = $this->dbManager->fetchAll($sqlResult);
$this->dbManager->freeResult($sqlResult);
return $result;
}
/**
* @param int $uploadId
* @param int $groupId
* @return int
*/
public function getReusedUpload($uploadId, $groupId)
{
$statementName = __METHOD__;
$this->dbManager->prepare($statementName, "SELECT reused_upload_fk, reused_group_fk, reuse_mode FROM upload_reuse WHERE upload_fk = \$1 AND group_fk=\$2");
$res = $this->dbManager->execute($statementName, array($uploadId, $groupId));
$reusedPairs = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $reusedPairs;
}
/**
* @param string $scannerName
* @param int $uploadId
* @return array[] with keys agent_id,agent_rev,agent_name
*/
public function getSuccessfulAgentEntries($scannerName, $uploadId)
{
$stmt = __METHOD__ . ".getAgent.{$scannerName}";
$this->dbManager->prepare($stmt, $sql = "SELECT agent_pk AS agent_id,agent_rev,agent_name " . "FROM agent LEFT JOIN {$scannerName}" . self::ARS_TABLE_SUFFIX . " ON agent_fk=agent_pk " . "WHERE agent_name=\$2 AND agent_enabled AND upload_fk=\$1 AND ars_success " . "ORDER BY agent_id DESC");
$res = $this->dbManager->execute($stmt, array($uploadId, $scannerName));
$agents = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $agents;
}
public function getFolderChildUploads($parentId, $trustGroupId)
{
$statementName = __METHOD__;
$parameters = array($parentId, $trustGroupId);
$this->dbManager->prepare($statementName, $sql = "\nSELECT u.*,uc.*,fc.foldercontents_pk FROM foldercontents fc\n INNER JOIN upload u ON u.upload_pk = fc.child_id\n INNER JOIN upload_clearing uc ON u.upload_pk=uc.upload_fk AND uc.group_fk=\$2\nWHERE fc.parent_fk = \$1 AND fc.foldercontents_mode = " . self::MODE_UPLOAD . " AND u.upload_mode = 104\n");
$res = $this->dbManager->execute($statementName, $parameters);
$results = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $results;
}
public function getBulkMatches($bulkId, $groupId)
{
$stmt = __METHOD__;
$sql = "SELECT uploadtree_fk AS itemid\n FROM clearing_event ce\n INNER JOIN highlight_bulk h\n ON ce.clearing_event_pk = h.clearing_event_fk\n WHERE lrb_fk = \$1 AND group_fk = \$2";
$this->dbManager->prepare($stmt, $sql);
$res = $this->dbManager->execute($stmt, array($bulkId, $groupId));
$result = $this->dbManager->fetchAll($res);
$this->dbManager->freeResult($res);
return $result;
}
/**
* @brief Get job queue data from db.
*
* @param $job_pks Array of $job_pk's to display.
* @param $page Get data for this display page. Starts with zero.
*
* @return array of job data
* \code
* JobData [job_pk] Array of job records (JobRec)
*
* JobRec['jobqueue'][jq_pk] = array of JobQueue records
* JobRec['jobqueue'][jq_pk]['depends'] = array of jq_pk's for dependencies
* JobRec['upload'] = array for upload record
* JobRec['job'] = array for job record
* JobRec['uploadtree'] = array for parent uploadtree record
*
* JobQueue ['jq_pk'] = jq_pk
* JobQueue ['jq_type'] = jq_type
* JobQueue ['jq_itemsprocessed'] = jq_itemsprocessed
* JobQueue ['jq_starttime'] = jq_starttime
* JobQueue ['jq_endtime'] = jq_endtime
* JobQueue ['jq_log'] = jq_log
* JobQueue ['jq_endtext'] = jq_endtext
* JobQueue ['jq_end_bits'] = jq_end_bits
* \endcode
**/
public function getJobInfo($job_pks, $page = 0)
{
/* Output data array */
$jobData = array();
foreach ($job_pks as $job_pk) {
/* Get job table data */
$statementName = __METHOD__ . "JobRec";
$jobRec = $this->dbManager->getSingleRow("SELECT * FROM job WHERE job_pk= \$1", array($job_pk), $statementName);
$jobData[$job_pk]["job"] = $jobRec;
if (!empty($jobRec["job_upload_fk"])) {
$upload_pk = $jobRec["job_upload_fk"];
/* Get Upload record for job */
$statementName = __METHOD__ . "UploadRec";
$uploadRec = $this->dbManager->getSingleRow("SELECT * FROM upload WHERE upload_pk= \$1", array($upload_pk), $statementName);
if (!empty($uploadRec)) {
$jobData[$job_pk]["upload"] = $uploadRec;
/* Get Upload record for uploadtree */
$uploadtree_tablename = $uploadRec["uploadtree_tablename"];
$statementName = __METHOD__ . "uploadtreeRec";
$uploadtreeRec = $this->dbManager->getSingleRow("SELECT * FROM {$uploadtree_tablename} where upload_fk = \$1 and parent is null", array($upload_pk), $statementName);
$jobData[$job_pk]["uploadtree"] = $uploadtreeRec;
} else {
$statementName = __METHOD__ . "uploadRecord";
$uploadRec = $this->dbManager->getSingleRow("SELECT * FROM upload right join job on upload_pk = job_upload_fk where job_upload_fk = \$1", array($upload_pk), $statementName);
/* upload has been deleted so try to get the job name from the original upload job record */
$jobName = $this->getJobName($uploadRec["job_upload_fk"]);
$uploadRec["upload_filename"] = "Deleted Upload: " . $uploadRec["job_upload_fk"] . "(" . $jobName . ")";
$uploadRec["upload_pk"] = $uploadRec["job_upload_fk"];
$jobData[$job_pk]["upload"] = $uploadRec;
}
}
/* Get jobqueue table data */
$statementName = __METHOD__ . "job_pkforjob";
$this->dbManager->prepare($statementName, "SELECT jq.*,jd.jdep_jq_depends_fk FROM jobqueue jq LEFT OUTER JOIN jobdepends jd ON jq.jq_pk=jd.jdep_jq_fk WHERE jq.jq_job_fk=\$1 ORDER BY jq_pk ASC");
$result = $this->dbManager->execute($statementName, array($job_pk));
$rows = $this->dbManager->fetchAll($result);
if (!empty($rows)) {
foreach ($rows as $jobQueueRec) {
$jq_pk = $jobQueueRec["jq_pk"];
if (array_key_exists($job_pk, $jobData) && array_key_exists('jobqueue', $jobData[$job_pk]) && array_key_exists($jq_pk, $jobData[$job_pk]['jobqueue'])) {
$jobData[$job_pk]['jobqueue'][$jq_pk]["depends"][] = $jobQueueRec["jdep_jq_depends_fk"];
} else {
$jobQueueRec["depends"] = array($jobQueueRec["jdep_jq_depends_fk"]);
$jobData[$job_pk]['jobqueue'][$jq_pk] = $jobQueueRec;
}
}
} else {
unset($jobData[$job_pk]);
}
$this->dbManager->freeResult($result);
}
return $jobData;
}
protected function getCopyrights($upload_pk, $item, $uploadTreeTableName, $agentId, $type, $filter)
{
$offset = GetParm('iDisplayStart', PARM_INTEGER);
$limit = GetParm('iDisplayLength', PARM_INTEGER);
$tableName = $this->getTableName($type);
$orderString = $this->getOrderString();
list($left, $right) = $this->uploadDao->getLeftAndRight($item, $uploadTreeTableName);
if ($filter == "") {
$filter = "none";
}
$sql_upload = "";
if ('uploadtree_a' == $uploadTreeTableName) {
$sql_upload = " AND UT.upload_fk={$upload_pk} ";
}
$join = "";
$filterQuery = "";
if ($type == 'statement' && $filter == "nolic") {
$noLicStr = "No_license_found";
$voidLicStr = "Void";
$join = " INNER JOIN license_file AS LF on cp.pfile_fk=LF.pfile_fk ";
$filterQuery = " AND LF.rf_fk IN (SELECT rf_pk FROM license_ref WHERE rf_shortname IN ('{$noLicStr}','{$voidLicStr}')) ";
} else {
// No filter, nothing to do
}
$params = array($left, $right, $type, $agentId);
$filterParms = $params;
$searchFilter = $this->addSearchFilter($filterParms);
$unorderedQuery = "FROM {$tableName} AS cp " . "INNER JOIN {$uploadTreeTableName} AS UT ON cp.pfile_fk = UT.pfile_fk " . $join . "WHERE cp.content!='' " . "AND ( UT.lft BETWEEN \$1 AND \$2 ) " . "AND cp.type = \$3 " . "AND cp.agent_fk= \$4 " . $sql_upload;
$totalFilter = $filterQuery . " " . $searchFilter;
$grouping = " GROUP BY content ";
$countQuery = "SELECT count(*) FROM (SELECT content, count(*) {$unorderedQuery} {$totalFilter} {$grouping}) as K";
$iTotalDisplayRecordsRow = $this->dbManager->getSingleRow($countQuery, $filterParms, __METHOD__ . $tableName . ".count");
$iTotalDisplayRecords = $iTotalDisplayRecordsRow['count'];
$countAllQuery = "SELECT count(*) FROM (SELECT content, count(*) {$unorderedQuery}{$grouping}) as K";
$iTotalRecordsRow = $this->dbManager->getSingleRow($countAllQuery, $params, __METHOD__, $tableName . "count.all");
$iTotalRecords = $iTotalRecordsRow['count'];
$range = "";
$filterParms[] = $offset;
$range .= ' OFFSET $' . count($filterParms);
$filterParms[] = $limit;
$range .= ' LIMIT $' . count($filterParms);
$sql = "SELECT content, hash, count(*) as copyright_count " . $unorderedQuery . $totalFilter . " GROUP BY content, hash " . $orderString . $range;
$statement = __METHOD__ . $filter . $tableName . $uploadTreeTableName;
$this->dbManager->prepare($statement, $sql);
$result = $this->dbManager->execute($statement, $filterParms);
$rows = $this->dbManager->fetchAll($result);
$this->dbManager->freeResult($result);
return array($rows, $iTotalDisplayRecords, $iTotalRecords);
}
/**
* @return array
*/
public function getLicenseArray($groupId = null)
{
$statementName = __METHOD__;
$rfTable = 'license_all';
$options = array('columns' => array('rf_pk', 'rf_shortname', 'rf_fullname'), 'candidatePrefix' => $this->candidatePrefix);
if ($groupId === null) {
$groupId = isset($_SESSION) && array_key_exists('GroupId', $_SESSION) ? $_SESSION['GroupId'] : 0;
}
$licenseViewDao = new LicenseViewProxy($groupId, $options, $rfTable);
$withCte = $licenseViewDao->asCTE();
$this->dbManager->prepare($statementName, $withCte . " select rf_pk id,rf_shortname shortname,rf_fullname fullname from {$rfTable} order by LOWER(rf_shortname)");
$result = $this->dbManager->execute($statementName);
$licenseRefs = $this->dbManager->fetchAll($result);
$this->dbManager->freeResult($result);
return $licenseRefs;
}
/**
* \return return rows to process, and $upload_pk
* @param $Uploadtree_pk
* @param $Agent_pk
* @param $upload_pk
* @param $hash
* @param $type
* @param $tableName
* @throws Exception
* @return array
*/
function GetRows($Uploadtree_pk, $Agent_pk, &$upload_pk, $hash, $type, $tableName)
{
global $PG_CONN;
/******* Get license names and counts ******/
/* Find lft and rgt bounds for this $Uploadtree_pk */
$sql = "SELECT lft,rgt,upload_fk FROM uploadtree\n WHERE uploadtree_pk = {$Uploadtree_pk}";
$result = pg_query($PG_CONN, $sql);
DBCheckResult($result, $sql, __FILE__, __LINE__);
$row = pg_fetch_assoc($result);
$lft = $row["lft"];
$rgt = $row["rgt"];
$upload_pk = $row["upload_fk"];
pg_free_result($result);
/* get all the copyright records for this uploadtree. */
$sql = "SELECT content, type, uploadtree_pk, ufile_name, PF\n from {$tableName},\n (SELECT uploadtree_pk, pfile_fk as PF, ufile_name from uploadtree \n where upload_fk=\$1\n and uploadtree.lft BETWEEN \$2 and \$3) as SS\n where PF=pfile_fk and agent_fk=\$4 and hash=\$5 and type=\$6 order by uploadtree_pk";
$statement = __METHOD__ . $tableName;
$this->dbManager->prepare($statement, $sql);
$result = $this->dbManager->execute($statement, array($upload_pk, $lft, $rgt, $Agent_pk, $hash, $type));
$rows = $this->dbManager->fetchAll($result);
$this->dbManager->freeResult($result);
return $rows;
}
function addConstraints()
{
$sql = "SELECT c.conname AS constraint_name,\n CASE c.contype\n WHEN 'c' THEN 'CHECK'\n WHEN 'f' THEN 'FOREIGN KEY'\n WHEN 'p' THEN 'PRIMARY KEY'\n WHEN 'u' THEN 'UNIQUE'\n END AS type,\n CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,\n CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,\n t.relname AS table_name, array_to_string(c.conkey, ' ') AS constraint_key,\n CASE confupdtype\n WHEN 'a' THEN 'NO ACTION'\n WHEN 'r' THEN 'RESTRICT'\n WHEN 'c' THEN 'CASCADE'\n WHEN 'n' THEN 'SET NULL'\n WHEN 'd' THEN 'SET DEFAULT'\n END AS on_update,\n CASE confdeltype\n WHEN 'a' THEN 'NO ACTION'\n WHEN 'r' THEN 'RESTRICT'\n WHEN 'c' THEN 'CASCADE'\n WHEN 'n' THEN 'SET NULL'\n WHEN 'd' THEN 'SET DEFAULT' END AS on_delete,\n CASE confmatchtype\n WHEN 'u' THEN 'UNSPECIFIED'\n WHEN 'f' THEN 'FULL'\n WHEN 'p' THEN 'PARTIAL'\n END AS match_type,\n t2.relname AS references_table,\n array_to_string(c.confkey, ' ') AS fk_constraint_key\n FROM pg_constraint AS c\n LEFT JOIN pg_class AS t ON c.conrelid = t.oid\n INNER JOIN information_schema.tables AS tab ON t.relname = tab.table_name\n LEFT JOIN pg_class AS t2 ON c.confrelid = t2.oid\n ORDER BY constraint_name,table_name\n ";
$stmt = __METHOD__;
$this->dbman->prepare($stmt, $sql);
$result = $this->dbman->execute($stmt);
$Results = $this->dbman->fetchAll($result);
$this->dbman->freeResult($result);
/* Constraints use indexes into columns. Covert those to column names. */
for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
$Key = "";
$Keys = explode(" ", $Results[$i]['constraint_key']);
foreach ($Keys as $K) {
if (empty($K)) {
continue;
}
if (!empty($Key)) {
$Key .= ",";
}
$Key .= '"' . $this->currSchema['TABLEID'][$Results[$i]['table_name']][$K] . '"';
}
$Results[$i]['constraint_key'] = $Key;
$Key = "";
$Keys = explode(" ", $Results[$i]['fk_constraint_key']);
foreach ($Keys as $K) {
if (empty($K)) {
continue;
}
if (!empty($Key)) {
$Key .= ",";
}
$Key .= '"' . $this->currSchema['TABLEID'][$Results[$i]['references_table']][$K] . '"';
}
$Results[$i]['fk_constraint_key'] = $Key;
}
/* Save the constraint */
/** There are different types of constraints that must be stored in order **/
/** CONSTRAINT: PRIMARY KEY **/
for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
if ($Results[$i]['type'] != 'PRIMARY KEY') {
continue;
}
$sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
$sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
$sql .= " " . $Results[$i]['type'];
$sql .= " (" . $Results[$i]['constraint_key'] . ")";
if (!empty($Results[$i]['references_table'])) {
$sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
$sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
}
$sql .= ";";
$this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
$Results[$i]['processed'] = 1;
}
/** CONSTRAINT: UNIQUE **/
for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
if ($Results[$i]['type'] != 'UNIQUE') {
continue;
}
$sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
$sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
$sql .= " " . $Results[$i]['type'];
$sql .= " (" . $Results[$i]['constraint_key'] . ")";
if (!empty($Results[$i]['references_table'])) {
$sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
$sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
}
$sql .= ";";
$this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
$Results[$i]['processed'] = 1;
}
/** CONSTRAINT: FOREIGN KEY **/
for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
if ($Results[$i]['type'] != 'FOREIGN KEY') {
continue;
}
$sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
$sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
$sql .= " " . $Results[$i]['type'];
$sql .= " (" . $Results[$i]['constraint_key'] . ")";
if (!empty($Results[$i]['references_table'])) {
$sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
$sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
}
if (!empty($Results[$i]['on_update'])) {
$sql .= " ON UPDATE " . $Results[$i]['on_update'];
}
if (!empty($Results[$i]['on_delete'])) {
$sql .= " ON DELETE " . $Results[$i]['on_delete'];
}
$sql .= ";";
$this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
$Results[$i]['processed'] = 1;
}
/** CONSTRAINT: ALL OTHERS **/
for ($i = 0; !empty($Results[$i]['constraint_name']); $i++) {
if (!empty($Results[$i]['processed']) && $Results[$i]['processed'] == 1) {
continue;
}
$sql = "ALTER TABLE \"" . $Results[$i]['table_name'] . "\"";
$sql .= " ADD CONSTRAINT \"" . $Results[$i]['constraint_name'] . '"';
$sql .= " " . $Results[$i]['type'];
$sql .= " (" . $Results[$i]['constraint_key'] . ")";
if (!empty($Results[$i]['references_table'])) {
$sql .= " REFERENCES \"" . $Results[$i]['references_table'] . "\"";
$sql .= " (" . $Results[$i]['fk_constraint_key'] . ")";
}
$sql .= ";";
$this->currSchema['CONSTRAINT'][$Results[$i]['constraint_name']] = $sql;
$Results[$i]['processed'] = 1;
}
}
