ajax-copyright-hist.php

<?php
/***********************************************************
 * Copyright (C) 2014-2019 Siemens AG
 * Author: Daniele Fognini, Johannes Najjar, Steffen Weber
 *
 * 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.
 ***********************************************************/

use Fossology\Lib\Auth\Auth;
use Fossology\Lib\Dao\CopyrightDao;
use Fossology\Lib\Dao\UploadDao;
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Util\DataTablesUtility;
use Fossology\Agent\Copyright\UI\TextFindingsAjax;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;

define("TITLE_COPYRIGHTHISTOGRAMPROCESSPOST", _("Private: Browse post"));

/**
 * @class CopyrightHistogramProcessPost
 * @brief Handles Ajax requests for copyright
 */
class CopyrightHistogramProcessPost extends FO_Plugin
{
  /** @var string $listPage
   * Page slug for plugin
   */
  protected $listPage;
  /** @var string
   * Upload tree to be used
   */
  private $uploadtree_tablename;
  /** @var DbManager
   * DbManager object
   */
  private $dbManager;
  /** @var UploadDao
   * UploadDao object
   */
  private $uploadDao;
  /** @var CopyrightDao
   * CopyrightDao object
   */
  private $copyrightDao;

  /** @var DataTablesUtility
   * DataTablesUtility object
   */
  private $dataTablesUtility;

  /** @var array $textFindingTypes
   * List of types used for text findings
   */
  private $textFindingTypes = ["copyFindings"];

  function __construct()
  {
    $this->Name = "ajax-copyright-hist";
    $this->Title = TITLE_COPYRIGHTHISTOGRAMPROCESSPOST;
    $this->DBaccess = PLUGIN_DB_READ;
    $this->OutputType = 'JSON';
    $this->LoginFlag = 0;
    $this->NoMenu = 0;

    parent::__construct();
    global $container;
    $this->dataTablesUtility = $container->get('utils.data_tables_utility');
    $this->uploadDao = $container->get('dao.upload');
    $this->dbManager = $container->get('db.manager');
    $this->copyrightDao = $container->get('dao.copyright');
  }


  /**
   * @brief Display the loaded menu and plugins.
   * @see FO_Plugin::Output()
   */
  function Output()
  {
    $returnValue = 0;
    if ($this->State != PLUGIN_STATE_READY) {
      return 0;
    }

    $action = GetParm("action", PARM_STRING);
    $upload = GetParm("upload", PARM_INTEGER);
    $type = GetParm("type", PARM_STRING);

    if ($action=="deletedecision" || $action=="undodecision") {
      $decision = GetParm("decision", PARM_INTEGER);
      $pfile = GetParm("pfile", PARM_INTEGER);
    } else if ($action=="deleteHashDecision" || $action=="undoHashDecision") {
      $hash = GetParm("hash", PARM_STRING);
    } else if($action=="update" || $action=="delete" || $action=="undo") {
      $id = GetParm("id", PARM_STRING);
      $getEachID = array_filter(explode(",", trim($id, ',')), function($var) {
          return $var !== "";
      });
      if(count($getEachID) == 4) {
        list($upload, $item, $hash, $type) = $getEachID;
      } else {
        return new Response('bad request while '.$action,
                Response::HTTP_BAD_REQUEST,
                array('Content-type'=>'text/plain')
          );
      }
    }

    /* check upload permissions */
    if (!(($action == "getData" || $action == "getDeactivatedData") &&
        ($this->uploadDao->isAccessible($upload, Auth::getGroupId())) ||
        ($this->uploadDao->isEditable($upload, Auth::getGroupId())))) {
      $permDeniedText = _("Permission Denied");
      $returnValue = "<h2>$permDeniedText</h2>";
    }
    $this->uploadtree_tablename = $this->uploadDao->getUploadtreeTableName(
      $upload);

    if (in_array($type, $this->textFindingTypes) &&
      ($action == "getData" || $action == "getDeactivatedData")) {
      $textFindingsHandler = new TextFindingsAjax($this->uploadtree_tablename);
      if ($action == "getData") {
        $returnValue = $textFindingsHandler->doGetData($type, $upload);
      } elseif ($action == "getDeactivatedData") {
        $returnValue = $textFindingsHandler->doGetData($type, $upload, false);
      }
    } else {
      switch ($action) {
        case "getData":
          $returnValue = $this->doGetData($upload);
          break;
        case "getDeactivatedData":
          $returnValue = $this->doGetData($upload, false);
          break;
        case "update":
          $returnValue = $this->doUpdate($item, $hash, $type);
          break;
        case "delete":
          $returnValue = $this->doDelete($item, $hash, $type);
          break;
        case "undo":
          $returnValue = $this->doUndo($item, $hash, $type);
          break;
        case "deletedecision":
          $returnValue = $this->doDeleteDecision($decision, $pfile, $type);
          break;
        case "undodecision":
          $returnValue = $this->doUndoDecision($decision, $pfile, $type);
          break;
        case "deleteHashDecision":
          $returnValue = $this->doDeleteHashDecision($hash, $upload, $type);
          break;
        case "undoHashDecision":
          $returnValue = $this->doUndoHashDecision($hash, $upload, $type);
          break;
        default:
          $returnValue = "<h2>" . _("Unknown action") . "</h2>";
      }
    }
    return $returnValue;
  }

  /**
   * @brief Handles GET request and create a JSON response
   *
   * Gets the copyright history for given upload and generate
   * a JSONResponse using getTableData()
   * @param int $upload     Upload id to fetch results
   * @param bool $activated True to get activated results, false for disabled
   * @return JsonResponse JSON response for JavaScript
   */
  protected function doGetData($upload, $activated = true)
  {
    $item = GetParm("item", PARM_INTEGER);
    $agent_pk = GetParm("agent", PARM_STRING);
    $type = GetParm("type", PARM_STRING);
    $filter = GetParm("filter", PARM_STRING);
    $listPage = "copyright-list";

    header('Content-type: text/json');
    list($aaData, $iTotalRecords, $iTotalDisplayRecords) = $this->getTableData($upload, $item, $agent_pk, $type,$listPage, $filter, $activated);
    return new JsonResponse(array(
            'sEcho' => intval($_GET['sEcho']),
            'aaData' => $aaData,
            'iTotalRecords' => $iTotalRecords,
            'iTotalDisplayRecords' => $iTotalDisplayRecords
        )
    );
  }

  /**
   * @brief Get the copyright data and fill in expected format
   * @param int     $upload    Upload id to get results from
   * @param int     $item      Upload tree id of the item
   * @param int     $agent_pk  Id of the agent who loaded the results
   * @param string  $type      Type of the statement (statement, url, email, author or ecc)
   * @param string  $listPage  Page slug to use
   * @param string  $filter    Filter data from query
   * @param boolean $activated True to get activated copyrights, else false
   * @return array[][] Array of table data, total records in database, filtered records
   */
  private function getTableData($upload, $item, $agent_pk, $type, $listPage, $filter, $activated = true)
  {
    list ($rows, $iTotalDisplayRecords, $iTotalRecords) = $this->getCopyrights($upload, $item, $this->uploadtree_tablename, $agent_pk, $type, $filter, $activated);
    $aaData = array();
    if (!empty($rows))
    {
      $rw = $this->uploadDao->isEditable($upload, Auth::getGroupId());
      foreach ($rows as $row)
      {
        $aaData [] = $this->fillTableRow($row, $item, $upload, $agent_pk, $type,$listPage, $filter, $activated, $rw);
      }
    }

    return array($aaData, $iTotalRecords, $iTotalDisplayRecords);

  }

  /**
   * @brief Get results from database and format for JSON
   * @param int     $upload_pk           Upload id to get results from
   * @param int     $item                Upload tree id of the item
   * @param string  $uploadTreeTableName Upload tree table to use
   * @param int     $agentId             Id of the agent who loaded the results
   * @param string  $type                Type of the statement (statement, url, email, author or ecc)
   * @param string  $filter              Filter data from query
   * @param boolean $activated           True to get activated copyrights, else false
   * @return array[][] Array of table records, filtered records, total records
   */
  protected function getCopyrights($upload_pk, $item, $uploadTreeTableName, $agentId, $type, $filter, $activated = true)
  {
    $offset = GetParm('iDisplayStart', PARM_INTEGER);
    $limit = GetParm('iDisplayLength', PARM_INTEGER);

    $tableName = $this->getTableName($type);
    $tableNameEvent = $tableName.'_event';
    $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=$5 ";
    }

    $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, $upload_pk);

    $filterParms = $params;
    $searchFilter = $this->addSearchFilter($filterParms);

    $activatedClause = "";
    if ($activated) {
      $activatedClause = "NOT";
    }
    $unorderedQuery = "FROM $tableName AS cp " .
        "INNER JOIN $uploadTreeTableName AS UT ON cp.pfile_fk = UT.pfile_fk " .
        "LEFT JOIN $tableNameEvent AS ce ON ce.".$tableName."_fk = cp.".$tableName."_pk " .
        "AND ce.upload_fk = $5 " .
        $join .
        "WHERE cp.content!='' " .
        "AND ( UT.lft  BETWEEN  $1 AND  $2 ) " .
        "AND cp.type = $3 " .
        "AND cp.agent_fk= $4 " .
        "AND cp." . $tableName . "_pk $activatedClause IN " .
        "(SELECT " . $tableName . "_fk FROM $tableNameEvent WHERE upload_fk = $5 AND is_enabled = false)" .
        $sql_upload;
    $grouping = " GROUP BY mcontent ";

    $countQuery = "SELECT count(*) FROM (
  SELECT mcontent AS content FROM (SELECT
    (CASE WHEN (ce.content IS NULL OR ce.content = '') THEN cp.content ELSE ce.content END) AS mcontent
    $unorderedQuery $filterQuery $grouping) AS k $searchFilter) AS cx";
    $iTotalDisplayRecordsRow = $this->dbManager->getSingleRow($countQuery,
        $filterParms, __METHOD__.$tableName . ".count" . ($activated ? '' : '_deactivated'));
    $iTotalDisplayRecords = $iTotalDisplayRecordsRow['count'];

    $countAllQuery = "SELECT count(*) FROM (SELECT
(CASE WHEN (ce.content IS NULL OR ce.content = '') THEN cp.content ELSE ce.content END) AS mcontent
$unorderedQuery$grouping) as K";
    $iTotalRecordsRow = $this->dbManager->getSingleRow($countAllQuery, $params, __METHOD__,$tableName . "count.all" . ($activated ? '' : '_deactivated'));
    $iTotalRecords = $iTotalRecordsRow['count'];

    $range = "";
    $filterParms[] = $offset;
    $range .= ' OFFSET $' . count($filterParms);
    $filterParms[] = $limit;
    $range .= ' LIMIT $' . count($filterParms);

    $sql = "SELECT mcontent AS content, mhash AS hash, copyright_count FROM (SELECT
(CASE WHEN (ce.content IS NULL OR ce.content = '') THEN cp.content ELSE ce.content END) AS mcontent,
(CASE WHEN (ce.hash IS NULL OR ce.hash = '') THEN cp.hash ELSE ce.hash END) AS mhash,
count(*) AS copyright_count " .
      "$unorderedQuery $filterQuery GROUP BY mcontent, mhash) AS k $searchFilter $orderString $range";
    $statement = __METHOD__ . $filter.$tableName . $uploadTreeTableName . ($activated ? '' : '_deactivated');
    $rows = $this->dbManager->getRows($sql, $filterParms, $statement);

    return array($rows, $iTotalDisplayRecords, $iTotalRecords);
  }

  /**
   * @brief Get table name based on statement type
   *
   * - statement => copyright
   * - ecc       => ecc
   * - others    => author
   * @param string $type Result type
   * @return string Table name
   */
  private function getTableName($type)
  {
    switch ($type) {
      case "ecc" :
        $tableName = "ecc";
        break;
      case "keyword" :
        $tableName = "keyword";
        $filter="none";
        break;
      case "statement" :
        $tableName = "copyright";
        break;
      case "scancode_statement" :
        $tableName = "scancode_copyright";
      default:
        $tableName = "author";
    }
    return $tableName;
  }

  /**
   * @brief Create sorting string for database query
   * @return string Sorting string
   */
  private function getOrderString()
  {
    $columnNamesInDatabase = array('copyright_count', 'content');

    $defaultOrder = CopyrightHistogram::returnSortOrder();

    $orderString = $this->dataTablesUtility->getSortingString($_GET, $columnNamesInDatabase, $defaultOrder);

    return $orderString;
  }

  /**
   * @brief Add filter on content
   * @param[out] array $filterParams Parameters list for database query
   * @return string Filter string for query
   */
  private function addSearchFilter(&$filterParams)
  {
    $searchPattern = GetParm('sSearch', PARM_STRING);
    if (empty($searchPattern))
    {
      return '';
    }
    $filterParams[] = "%$searchPattern%";
    return 'WHERE mcontent ilike $'.count($filterParams).' ';
  }

  /**
   * @brief Helper to create action column for results
   * @param string  $hash         Hash of the content
   * @param int     $uploadTreeId Item id in upload tree table
   * @param int     $upload       Upload id
   * @param string  $type         Type of content
   * @param boolean $activated    True if content is activated, else false
   * @param boolean $rw           true if content is editable
   * @return string
   */
  private function getTableRowAction($hash, $uploadTreeId, $upload, $type, $activated = true, $rw = true)
  {
    if($rw)
    {
      $act = "<img";
      if(!$activated)
      {
        $act .= " hidden='true'";
      }
      $act .= " id='delete$type$hash' onClick='delete$type($upload,$uploadTreeId,\"$hash\",\"$type\");' class=\"delete\" src=\"images/space_16.png\">";
      $act .= "<span";
      if($activated) {
        $act .= " hidden='true'";
      }
      $act .= " id='update$type$hash'>deactivated [<a href=\"#\" id='undo$type$hash' onClick='undo$type($upload,$uploadTreeId,\"$hash\",\"$type\");return false;'>Undo</a>]</span>";
      return $act;
    }
    if(!$activated) {
      return "deactivated";
    }
    return "";
  }

  /**
   * @brief Fill table content for JSON response
   * @param array   $row          Result row from database
   * @param int     $uploadTreeId Upload tree id of the item
   * @param int     $upload       Upload id
   * @param int     $agentId      Agent id
   * @param string  $type         Type of content
   * @param string  $listPage     Page slug
   * @param string  $filter       Filter for query
   * @param boolean $activated    True to get activated results, false otherwise
   * @return string[]
   * @internal param boolean $normalizeString
   */
  private function fillTableRow($row, $uploadTreeId, $upload, $agentId, $type,$listPage, $filter = "", $activated = true, $rw = true)
  {
    $hash = $row['hash'];
    $output = array('DT_RowId' => "$upload,$uploadTreeId,$hash,$type" );

    $link = "<a href='";
    $link .= Traceback_uri();
    $urlArgs = "?mod=".$listPage."&agent=$agentId&item=$uploadTreeId&hash=$hash&type=$type";
    if (!empty($filter)) {
      $urlArgs .= "&filter=$filter";
    }
    $link .= $urlArgs . "'>" . $row['copyright_count'] . "</a>";
    $output['0'] = $link;
    $output['1'] = convertToUTF8($row['content']);
    $output['2'] = $this->getTableRowAction($hash, $uploadTreeId, $upload, $type, $activated, $rw);
    if($rw && $activated)
    {
      $output['3'] = "<input type='checkbox' class='deleteBySelect$type' id='deleteBySelect$type$hash' value='".$upload.",".$uploadTreeId.",".$hash.",".$type."'>";
    }
    else
    {
        $output['3'] = "<input type='checkbox' class='undoBySelect$type' id='undoBySelect$type$hash' value='".$upload.",".$uploadTreeId.",".$hash.",".$type."'>";
    }
    return $output;
  }

  /**
   * @brief Update result
   * @param int    $itemId Upload tree id of the item to update
   * @param string $hash   Hash of the content
   * @param string $type   'copyright'|'ecc'|'keyword'
   * @return Response
   */
  protected function doUpdate($itemId, $hash, $type)
  {
    $content = GetParm("value", PARM_RAW);
    if (!$content)
    {
      return new Response('empty content not allowed', Response::HTTP_BAD_REQUEST ,array('Content-type'=>'text/plain'));
    }

    $item = $this->uploadDao->getItemTreeBounds($itemId, $this->uploadtree_tablename);
    $cpTable = $this->getTableName($type);
    $this->copyrightDao->updateTable($item, $hash, $content, Auth::getUserId(), $cpTable);

    return new Response('success', Response::HTTP_OK,array('Content-type'=>'text/plain'));
  }

  /**
   * @brief Disable a result
   * @param int    $itemId Upload tree id of the item to update
   * @param string $hash   Hash of the content
   * @param string $type   'copyright'|'ecc'
   * @return Response
   */
  protected function doDelete($itemId, $hash, $type)
  {
    $item = $this->uploadDao->getItemTreeBounds($itemId, $this->uploadtree_tablename);
    $cpTable = $this->getTableName($type);
    $this->copyrightDao->updateTable($item, $hash, '', Auth::getUserId(), $cpTable, 'delete');
    return new Response('Successfully deleted', Response::HTTP_OK, array('Content-type'=>'text/plain'));
  }

  /**
   * @brief Rollback a result
   * @param int    $itemId Upload tree id of the item to update
   * @param string $hash   Hash of the content
   * @param string $type   'copyright'|'ecc'
   * @return Response
   */
  protected function doUndo($itemId, $hash, $type) {
    $item = $this->uploadDao->getItemTreeBounds($itemId, $this->uploadtree_tablename);
    $cpTable = $this->getTableName($type);
    $this->copyrightDao->updateTable($item, $hash, '', Auth::getUserId(), $cpTable, 'rollback');
    return new Response('Successfully restored', Response::HTTP_OK, array('Content-type'=>'text/plain'));
  }

  /**
   * @brief Disable a decision
   * @param int    $decisionId Decision id
   * @param string $pfileId    Pfile id of the item
   * @param string $type       'copyright'|'ecc'
   * @return JsonResponse
   */
  protected function doDeleteDecision($decisionId, $pfileId, $type) {
    if($type === 'scancode_statement')
    {
      $type = 'statement';
    }
    $this->copyrightDao->removeDecision($type."_decision", $pfileId, $decisionId);
    return new JsonResponse(array("msg" => $decisionId . " .. " . $pfileId  . " .. " . $type));
  }

  /**
   * @brief Rollback a decision
   * @param int    $decisionId Decision id
   * @param string $pfileId    Pfile id of the item
   * @param string $type       'copyright'|'ecc'
   * @return JsonResponse
   */
  protected function doUndoDecision($decisionId, $pfileId, $type) {
    if($type === 'scancode_statement')
    {
      $type = 'statement';
    }
    $this->copyrightDao->undoDecision($type."_decision", $pfileId, $decisionId);
    return new JsonResponse(array("msg" => $decisionId . " .. " . $pfileId  . " .. " . $type));
  }

  /**
   * @brief Disable decisions for an upload which matches a hash
   * @param string $hash   Hash of the decision
   * @param int    $upload Upload id
   * @param string $type   'copyright'|'ecc'
   * @return JsonResponse
   */
  protected function doDeleteHashDecision($hash, $upload, $type)
  {
    if($type === 'scancode_statement')
    {
      $type = 'statement';
    }
    $tableName = $type."_decision";
    $decisions = $this->copyrightDao->getDecisionsFromHash($tableName, $hash,
      $upload, $this->uploadtree_tablename);
    foreach ($decisions as $decision) {
      $this->copyrightDao->removeDecision($tableName, $decision['pfile_fk'],
        $decision[$tableName . '_pk']);
    }
    return new JsonResponse(array("msg" => "$hash .. $upload .. $type"));
  }

  /**
   * @brief Rollback decisions for an upload which matches a hash
   * @param string $hash   Hash of the decision
   * @param int    $upload Upload id
   * @param string $type   'copyright'|'ecc'
   * @return JsonResponse
   */
  protected function doUndoHashDecision($hash, $upload, $type)
  {
    if($type === 'scancode_statement')
    {
      $type = 'statement';
    }
    $tableName = $type."_decision";
    $decisions = $this->copyrightDao->getDecisionsFromHash($tableName, $hash,
      $upload, $this->uploadtree_tablename);
    foreach ($decisions as $decision) {
      $this->copyrightDao->undoDecision($tableName, $decision['pfile_fk'],
        $decision[$tableName . '_pk']);
    }
    return new JsonResponse(array("msg" => "$hash .. $upload .. $type"));
  }
}

$NewPlugin = new CopyrightHistogramProcessPost();
$NewPlugin->Initialize();

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 Fossology\Lib\Proxy\ScanJobProxy;
use Fossology\Lib\Util\StringOperation;
use Fossology\Lib\Data\AgentRef;
use Monolog\Logger;

class CopyrightDao
{
  /** @var DbManager */
  private $dbManager;
  /** @var UploadDao */
  private $uploadDao;
  /** @var Logger */
  private $logger;

  function __construct(DbManager $dbManager, UploadDao $uploadDao)
  {
    $this->dbManager = $dbManager;
    $this->uploadDao = $uploadDao;
    $this->logger = new Logger(self::class);
  }

  /**
   * @param int $uploadTreeId
   * @param string $tableName
   * @param int $agentId
   * @param array $typeToHighlightTypeMap
   * @throws \Exception
   * @return Highlight[]
   */
  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;
    }

    // TODO: checkout this later
    $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;
  }

  /**
   * @param $tableName
   * @param $pfileId
   * @param $userId
   * @param $clearingType
   * @param $description
   * @param $textFinding
   * @param $comment
   * @param int $decision_pk
   * @return int decision_pk of decision
   */
  public function saveDecision($tableName, $pfileId, $userId , $clearingType,
                               $description, $textFinding, $comment, $decision_pk=-1)
  {
    $textFinding = StringOperation::replaceUnicodeControlChar($textFinding);
    $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;
    }
  }

  // using in ajax ln:526, ln 563 Done
  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));
  }

  // using in ajax , ln:542, 586 Done
  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));
  }

  /**
   * @param $uploadFk
   * @param $scope
   * @return array $rows
   */
  public function getAllEventEntriesForUpload($uploadFk, $agentId, $scope=1)
  {
    $statementName = __METHOD__ . $uploadFk;
    $params[] = $uploadFk;
    $params[] = $agentId;
    $params[] = $scope;
    $sql = "SELECT DISTINCT ON (copyright_pk) copyright_pk, C.content, c.hash,
              CE.content AS contentedited, CE.hash AS hashedited
            FROM copyright_event CE
              INNER JOIN copyright C ON C.copyright_pk = CE.copyright_fk
            WHERE CE.upload_fk=$1 AND CE.is_enabled=false AND scope=$3 AND C.agent_fk = $2";
    return $this->dbManager->getRows($sql, $params, $statementName);
  }

  /**
   * @param $tableName
   * @param $uploadTreeTableName
   * @param $uploadId
   * @param $type
   * @param $extrawhere
   * @param $enabled
   * @return array $result
   */
  public function getScannerEntries($tableName, $uploadTreeTableName, $uploadId,
    $type, $extrawhere, $enabled='true')
  {
    $statementName = __METHOD__.$tableName.$uploadTreeTableName;
    $params = array($uploadId);
    $extendWClause = null;
    $tableNameEvent = $tableName.'_event';

    if ($uploadTreeTableName === "uploadtree_a") {
      $extendWClause .= " AND UT.upload_fk = $1";
      $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."_";
    }

    $activatedClause = "";
    if ($enabled != 'false') {
      $activatedClause = "NOT";
      $statementName .= "._"."enabled";
    }

// ASK: How to add scancode_copyright_pk?
    $sql = "SELECT DISTINCT(copyright_pk), UT.uploadtree_pk as uploadtree_pk,
(CASE WHEN (CE.content IS NULL OR CE.content = '') THEN C.content ELSE CE.content END) AS content,
(CASE WHEN (CE.hash IS NULL OR CE.hash = '') THEN C.hash ELSE CE.hash END) AS hash,
C.agent_fk as agent_fk
  FROM $tableName C
  INNER JOIN $uploadTreeTableName UT ON C.pfile_fk = UT.pfile_fk
  LEFT JOIN $tableNameEvent AS CE ON CE.".$tableName."_fk = C.".$tableName."_pk
    AND CE.upload_fk = $1
  WHERE C.content IS NOT NULL
    AND C.content!=''
    AND C." . $tableName . "_pk $activatedClause IN
(SELECT " . $tableName . "_fk FROM $tableNameEvent WHERE upload_fk = $1 AND is_enabled = false)
  $extendWClause
ORDER BY UT.uploadtree_pk, content DESC";
    return $this->dbManager->getRows($sql, $params, $statementName);
  }

  /**
   * @param string  $tableName
   * @param string  $uploadTreeTableName
   * @param integer $uploadId
   * @param integer $decisionType
   * @param string  $extrawhere
   * @return array $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;
  }

  /**
   * @param $tableName
   * @param $uploadId
   * @param $uploadTreeTableName
   * @param $type
   * @param $onlyCleared
   * @param $decisionType
   * @param $extrawhere
   * @return array
   */
  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);
      $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;
    $tableNameEvent = $tableName.'_event';

    $params = array($uploadId);
    $whereClause = "";
    $distinctContent = "";
    $tableNameDecision = $tableName."_decision";

    if ($uploadTreeTableName === "uploadtree_a") {
      $whereClause .= " AND UT.upload_fk = $1";
      $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
            LEFT JOIN $tableNameEvent AS CE
              ON CE.".$tableName."_fk = C.".$tableName."_pk
              AND CE.upload_fk = $1
            $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.".$tableName."_pk NOT IN (SELECT DISTINCT(".$tableName."_fk) FROM $tableNameEvent TE WHERE TE.upload_fk = $1 AND is_enabled = false)
              $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;
  }

  /**
   * @param $tableName
   * @param $pfileId
   * @return array
   */
  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);
  }

  /**
   * @brief Get all the decisions based on hash
   *
   * Get all the decisions which matches the given hash. If the upload is null,
   * get decision from all uploads, otherwise get decisions only for the given
   * upload.
   *
   * @param string $tableName Decision table name
   * @param string $hash      Hash of the decision
   * @param int    $upload    Upload id
   * @param string $uploadtreetable Name of the upload tree table
   * @return array
   */

  //  using in ajax at ln: 560, 583 Done
  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);
  }

  /**
   * @param ItemTreeBounds $item
   * @param string $hash
   * @param string $content
   * @param int $userId
   * @param string $cpTable
   */

  //  ASK: How to add scancode_copyright table here?

  // using this in ajax ln:480 and 496, 510 Done
  public function updateTable($item, $hash, $content, $userId, $cpTable='copyright', $action='', $scope=1)
  {
    $cpTablePk = $cpTable."_pk";
    $cpTableEvent = $cpTable."_event";
    $cpTableEventFk = $cpTable."_fk";
    $itemTable = $item->getUploadTreeTableName();
    $stmt = __METHOD__.".$cpTable.$itemTable";
    $uploadId = $item->getUploadId();
    $params = array($item->getLeft(),$item->getRight(),$uploadId);
    $withHash = "";

    if (!empty($hash)) {
      $params[] = $hash;
      $withHash = " (cp.hash = $4 OR ce.hash = $4) AND ";
      $stmt .= ".hash";
    }
    // get latest agent id for agent
    $agentName = $this->getAgentName($cpTable);
    $scanJobProxy = new ScanJobProxy($GLOBALS['container']->get('dao.agent'),
      $uploadId);
    $scanJobProxy->createAgentStatus([$agentName]);
    $selectedScanners = $scanJobProxy->getLatestSuccessfulAgentIds();
    if (!array_key_exists($agentName, $selectedScanners)) {
      return array();
    }
    $latestAgentId = $selectedScanners[$agentName];
    $agentFilter = '';
    if (!empty($latestAgentId)) {
      $agentFilter = ' AND cp.agent_fk='.$latestAgentId;
    }

    $sql = "SELECT DISTINCT ON ($cpTablePk) $cpTablePk, ut.uploadtree_pk, ut.upload_fk, ce." . $cpTableEvent . "_pk
FROM $cpTable as cp
INNER JOIN $itemTable AS ut ON cp.pfile_fk = ut.pfile_fk
LEFT JOIN $cpTableEvent AS ce ON ce.$cpTableEventFk = cp.$cpTablePk
  AND ce.upload_fk = $3
WHERE $withHash ( ut.lft BETWEEN $1 AND $2 ) $agentFilter AND ut.upload_fk = $3";

    $rows = $this->dbManager->getRows($sql, $params, $stmt);

    foreach ($rows as $row) {
      $paramEvent = array();
      $paramEvent[] = $row['upload_fk'];
      $paramEvent[] = $row[$cpTablePk];
      $paramEvent[] = $row['uploadtree_pk'];
      $sqlExists = "SELECT exists(SELECT 1 FROM $cpTableEvent WHERE $cpTableEventFk = $1 AND upload_fk = $2)::int";
      $rowExists = $this->dbManager->getSingleRow($sqlExists, array($row[$cpTablePk], $row['upload_fk']), $stmt.'Exists');
      $eventExists = $rowExists['exists'];
      if ($action == "delete") {
        if ($eventExists) {
          $paramEvent[] = $scope;
          $sqlEvent = "UPDATE $cpTableEvent SET scope = $4, is_enabled = false
          WHERE upload_fk = $1 AND $cpTableEventFk = $2 AND uploadtree_fk = $3";
          $statement = "$stmt.delete.up";
        } else {
          $paramEvent[] = $scope;
          $sqlEvent = "INSERT INTO $cpTableEvent (upload_fk, $cpTableEventFk, uploadtree_fk, scope) VALUES($1, $2, $3, $4)";
          $statement = "$stmt.delete";
        }
      } else if ($action == "rollback") {
        if ($eventExists) {
          $sqlEvent = "UPDATE $cpTableEvent SET scope = 1, is_enabled = true
          WHERE upload_fk = $1 AND $cpTableEventFk = $2 AND uploadtree_fk = $3";
          $statement = "$stmt.rollback.up";
        } else {
          $sqlEvent = "DELETE FROM $cpTableEvent WHERE upload_fk = $1 AND uploadtree_fk = $3 AND $cpTableEventFk = $2";
          $statement = "$stmt.rollback";
        }
      } else {
        $paramEvent[] = "true";
        $paramEvent[] = StringOperation::replaceUnicodeControlChar($content);

        if ($eventExists) {
          $sqlEvent = "UPDATE $cpTableEvent
                        SET upload_fk = $1, uploadtree_fk = $3, is_enabled = $4, content = $5, hash = md5($5)
                       WHERE $cpTableEventFk = $2 AND uploadtree_fk = $3";
          $statement = "$stmt.update";
        } else {
          $sqlEvent = "INSERT INTO $cpTableEvent(upload_fk, uploadtree_fk, $cpTableEventFk, is_enabled, content, hash)
                       VALUES($1, $3, $2, $4, $5, md5($5))";
          $statement = "$stmt.insert";
        }
      }
      $this->dbManager->getSingleRow($sqlEvent, $paramEvent, $statement);
    }
  }

  /**
   * @brief Get agent name based on table name
   *
   * - copyright => copyright
   * - ecc       => ecc
   * - keyword   => keyword
   * - others    => copyright
   * @param string $table Table name
   * @return string Agent name
   */
  private function getAgentName($table)
  {
    if (array_search($table, ["ecc", "keyword", "copyright", "scancode_copyright"]) !== false) {
      return $table;
    }
    return "copyright";
  }
}