CopyrightDao.php

<?php
/*
Copyright (C) 2014-2018, Siemens AG
Author: Andreas Würl

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
version 2 as published by the Free Software Foundation.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/

namespace Fossology\Lib\Dao;

use Fossology\Lib\Data\Highlight;
use Fossology\Lib\Data\Tree\ItemTreeBounds;
use Fossology\Lib\Db\DbManager;
use Monolog\Logger;

class CopyrightDao
{
  private $dbManager;
  private $uploadDao;
  private $clearingDao;
  private $logger;

  function __construct(DbManager $dbManager, UploadDao $uploadDao)
  {
    $this->dbManager = $dbManager;
    $this->uploadDao = $uploadDao;
    $this->logger = new Logger(self::class);
    global $container;
    $this->clearingDao = $container->get('dao.clearing');
  }

  public function getHighlights($uploadTreeId, $tableName="copyright", $agentId=0,
          $typeToHighlightTypeMap=array(
                                          'statement' => Highlight::COPYRIGHT,
                                          'email' => Highlight::EMAIL,
                                          'url' => Highlight::URL,
                                          'author' => Highlight::AUTHOR)
   )
  {
    $pFileId = 0;
    $row = $this->uploadDao->getUploadEntry($uploadTreeId);

    if (!empty($row['pfile_fk'])) {
      $pFileId = $row['pfile_fk'];
    } else {
      $text = _("Could not locate the corresponding pfile.");
      print $text;
    }

    $statementName = __METHOD__.$tableName;
    $params = array($pFileId);
    $addAgentValue = "";
    if (!empty($agentId)) {
      $statementName .= '.agentId';
      $addAgentValue = ' AND agent_fk=$2';
      $params[] = $agentId;
    }
    $columnsToSelect = "type, content, copy_startbyte, copy_endbyte";
    $getHighlightForTableName = "SELECT $columnsToSelect FROM $tableName WHERE copy_startbyte IS NOT NULL AND pfile_fk=$1 $addAgentValue";
    if ($tableName != "copyright") {
      $sql = $getHighlightForTableName;
    } else {
      $sql = "$getHighlightForTableName UNION SELECT $columnsToSelect FROM author WHERE copy_startbyte IS NOT NULL AND pfile_fk=$1 $addAgentValue";
    }
    $this->dbManager->prepare($statementName,$sql);
    $result = $this->dbManager->execute($statementName, $params);

    $highlights = array();
    while ($row = $this->dbManager->fetchArray($result)) {
      $type = $row['type'];
      $content = $row['content'];
      $htmlElement =null;
      $highlightType = array_key_exists($type, $typeToHighlightTypeMap) ? $typeToHighlightTypeMap[$type] : Highlight::UNDEFINED;
      $highlights[] = new Highlight($row['copy_startbyte'], $row['copy_endbyte'], $highlightType, -1, -1, $content, $htmlElement);
    }
    $this->dbManager->freeResult($result);

    return $highlights;
  }

  public function saveDecision($tableName, $pfileId, $userId , $clearingType,
                               $description, $textFinding, $comment, $decision_pk=-1)
  {
    $primaryColumn = $tableName . '_pk';
    $assocParams = array(
      'user_fk' => $userId,
      'pfile_fk' => $pfileId,
      'clearing_decision_type_fk' => $clearingType,
      'description' => $description,
      'textfinding' => $textFinding,
      'hash' => hash('sha256', $textFinding),
      'comment'=> $comment
    );

    if ($decision_pk <= 0) {
      $rows = $this->getDecisionsFromHash($tableName, $assocParams['hash']);
      foreach ($rows as $row) {
        if ($row['pfile_fk'] == $pfileId) {
          $decision_pk = $row[$primaryColumn];
        }
      }
    }
    if ($decision_pk <= 0) {
      return $this->dbManager->insertTableRow($tableName, $assocParams,
        __METHOD__.'Insert.'.$tableName, $primaryColumn);
    } else {
      $assocParams['is_enabled'] = true;
      $this->dbManager->updateTableRow($tableName, $assocParams, $primaryColumn,
        $decision_pk, __METHOD__.'Update.'.$tableName);
      return $decision_pk;
    }
  }

  public function removeDecision($tableName,$pfileId, $decisionId)
  {
    $primaryColumn = $tableName . '_pk';
    $this->dbManager->prepare(__METHOD__,
      "UPDATE $tableName
        SET is_enabled = 'f'
      WHERE $primaryColumn = $1
        AND pfile_fk = $2");
    $this->dbManager->execute(__METHOD__, array($decisionId, $pfileId));
  }

  public function undoDecision($tableName,$pfileId, $decisionId)
  {
    $primaryColumn = $tableName . '_pk';
    $this->dbManager->prepare(__METHOD__,
      "UPDATE $tableName
        SET is_enabled = 't'
      WHERE $primaryColumn = $1
        AND pfile_fk = $2");
    $this->dbManager->execute(__METHOD__, array($decisionId, $pfileId));
  }

  public function getScannerEntries($tableName, $uploadTreeTableName, $uploadId, $type, $extrawhere)
  {
    $statementName = __METHOD__.$tableName.$uploadTreeTableName;
    $params = array();
    $extendWClause = null;

    if ($uploadTreeTableName === "uploadtree_a") {
      $params[]= $uploadId;
      $extendWClause .= " AND UT.upload_fk = $".count($params);
      $statementName .= ".withUI";
    }

    if ($type !== null && $type != "skipcontent") {
      $params[]= $type;
      $extendWClause .= " AND C.type = $".count($params);
      $statementName .= ".withType";
    }

    if ($extrawhere !== null) {
      $extendWClause .= " AND ". $extrawhere;
      $statementName .= "._".$extrawhere."_";
    }

    $sql = "SELECT UT.uploadtree_pk as uploadtree_pk, C.content AS content
              FROM $tableName C
             INNER JOIN $uploadTreeTableName UT ON C.pfile_fk = UT.pfile_fk
             WHERE C.content IS NOT NULL
               AND C.content!=''
               AND C.is_enabled='true'
               $extendWClause
             ORDER BY UT.uploadtree_pk, C.content DESC";
    $this->dbManager->prepare($statementName, $sql);
    $sqlResult = $this->dbManager->execute($statementName, $params);
    $result = $this->dbManager->fetchAll($sqlResult);
    $this->dbManager->freeResult($sqlResult);

    return $result;
  }

  public function getEditedEntries($tableName, $uploadTreeTableName, $uploadId,
    $decisionType, $extrawhere="")
  {
    $statementName = __METHOD__.$tableName.$uploadTreeTableName;
    $params = array();
    $extendWClause = null;

    if ($uploadTreeTableName === "uploadtree_a") {
      $params[]= $uploadId;
      $extendWClause .= " AND UT.upload_fk = $".count($params);
      $statementName .= ".withUI";
    }

    if (!empty($decisionType)) {
      $params[]= $decisionType;
      $extendWClause .= " AND clearing_decision_type_fk = $".count($params);
      $statementName .= ".withDecisionType";
    }

    if (!empty($extrawhere)) {
      $extendWClause .= " AND ". $extrawhere;
      $statementName .= "._".$extrawhere."_";
    }

    $columns = "CD.description as description, CD.textfinding as textfinding, CD.comment as comments, UT.uploadtree_pk as uploadtree_pk";

    $primaryColumn = $tableName . '_pk';
    $sql = "SELECT $columns
              FROM $tableName CD
             INNER JOIN $uploadTreeTableName UT ON CD.pfile_fk = UT.pfile_fk
             WHERE CD.is_enabled = 'true'
              $extendWClause
             ORDER BY CD.pfile_fk, UT.uploadtree_pk, CD.textfinding, CD.$primaryColumn DESC";
    $this->dbManager->prepare($statementName, $sql);
    $sqlResult = $this->dbManager->execute($statementName, $params);
    $result = $this->dbManager->fetchAll($sqlResult);
    $this->dbManager->freeResult($sqlResult);

    return $result;
  }

  public function getAllEntriesReport($tableName, $uploadId, $uploadTreeTableName, $type=null, $onlyCleared=false, $decisionType=null, $extrawhere=null, $groupId=null)
  {
    $tableNameDecision = $tableName."_decision";
    if ($tableName == 'copyright') {
      $scannerEntries = $this->getScannerEntries($tableName, $uploadTreeTableName, $uploadId, $type, $extrawhere);
      if (!empty($groupId)) {
        $itemTreeBounds = $this->uploadDao->getParentItemBounds($uploadId, $uploadTreeTableName);
        $irrelevantDecisions = $this->clearingDao->getFilesForDecisionTypeFolderLevel($itemTreeBounds, $groupId);
        $uniqueIrrelevantDecisions = array_unique(array_column($irrelevantDecisions, 'uploadtree_pk'));
        foreach ($scannerEntries as $key => $value) {
          if (in_array($value['uploadtree_pk'], $uniqueIrrelevantDecisions)) {
            unset($scannerEntries[$key]);
          }
        }
      }
      $editedEntries = $this->getEditedEntries($tableNameDecision, $uploadTreeTableName, $uploadId, $decisionType);
      return array_merge($scannerEntries, $editedEntries);
    } else {
      return $this->getEditedEntries($tableNameDecision, $uploadTreeTableName, $uploadId, $decisionType);
    }
  }

  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 !== 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."_";
    }
    $decisionTableKey = $tableNameDecision . "_pk";

    $latestInfo = "SELECT DISTINCT ON(CD.pfile_fk, UT.uploadtree_pk, C.content, CD.textfinding)
             CD.description as description, CD.textfinding as textfinding,
             CD.comment as comments, UT.uploadtree_pk as uploadtree_pk,
             CD.clearing_decision_type_fk AS clearing_decision_type_fk,
             C.content AS content
            FROM $tableName C
            INNER JOIN $uploadTreeTableName UT
              ON C.pfile_fk = UT.pfile_fk
            $joinType JOIN (SELECT * FROM $tableNameDecision WHERE is_enabled='true') AS CD
              ON C.pfile_fk = CD.pfile_fk
            WHERE C.content IS NOT NULL
              AND C.content!=''
              AND C.is_enabled='true'
              $whereClause
            ORDER BY CD.pfile_fk, UT.uploadtree_pk, C.content, CD.textfinding, CD.$decisionTableKey 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;
  }

  public function getDecisions($tableName,$pfileId)
  {
    $statementName = __METHOD__.$tableName;
    $orderTablePk = $tableName.'_pk';
    $sql = "SELECT * FROM $tableName where pfile_fk = $1 and is_enabled order by $orderTablePk desc";
    $params = array($pfileId);

    return $this->dbManager->getRows($sql, $params, $statementName);
  }

  public function getDecisionsFromHash($tableName, $hash, $upload = null, $uploadtreetable = null)
  {
    $statementName = __METHOD__ . ".$tableName";
    $orderTablePk = $tableName.'_pk';
    $join = "";
    $joinWhere = "";
    $params = [$hash];

    if ($upload != null) {
      if (empty($uploadtreetable)) {
        return -1;
      }
      $statementName.= ".filterUpload";
      $params[] = $upload;
      $join = "INNER JOIN $uploadtreetable AS ut ON cp.pfile_fk = ut.pfile_fk";
      $joinWhere = "AND ut.upload_fk = $" . count($params);
    }

    $sql = "SELECT * FROM $tableName AS cp $join " .
      "WHERE cp.hash = $1 $joinWhere ORDER BY $orderTablePk;";

    return $this->dbManager->getRows($sql, $params, $statementName);
  }

  public function updateTable($item, $hash, $content, $userId, $cpTable='copyright', $action='')
  {
    $itemTable = $item->getUploadTreeTableName();
    $stmt = __METHOD__.".$cpTable.$itemTable";
    $params = array($hash,$item->getLeft(),$item->getRight());

    if ($action == "delete") {
      $setSql = "is_enabled='false'";
      $stmt .= '.delete';
    } else if ($action == "rollback") {
      $setSql = "is_enabled='true'";
      $stmt .= '.rollback';
    } else {
      $setSql = "content = $4, hash = md5($4), is_enabled='true'";
      $params[] = $content;
    }

    $cpTablePk = $cpTable."_pk";
    $sql = "UPDATE $cpTable AS cpr SET $setSql
            FROM $cpTable as cp
            INNER JOIN $itemTable AS ut ON cp.pfile_fk = ut.pfile_fk
            WHERE cpr.$cpTablePk = cp.$cpTablePk
              AND cp.hash = $1
              AND ( ut.lft BETWEEN $2 AND $3 )";
    if ('uploadtree_a' == $item->getUploadTreeTableName()) {
      $params[] = $item->getUploadId();
      $sql .= " AND ut.upload_fk=$".count($params);
      $stmt .= '.upload';
    }

    $this->dbManager->prepare($stmt, "$sql");
    $resource = $this->dbManager->execute($stmt, $params);
    $this->dbManager->freeResult($resource);
  }
}