ClearingDao.php

<?php
/*
Copyright (C) 2014-2018,2020, Siemens AG
Author: Johannes Najjar

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\Clearing\ClearingEvent;
use Fossology\Lib\Data\Clearing\ClearingEventBuilder;
use Fossology\Lib\Data\Clearing\ClearingEventTypes;
use Fossology\Lib\Data\ClearingDecision;
use Fossology\Lib\Data\ClearingDecisionBuilder;
use Fossology\Lib\Data\DecisionScopes;
use Fossology\Lib\Data\DecisionTypes;
use Fossology\Lib\Data\LicenseRef;
use Fossology\Lib\Data\Tree\ItemTreeBounds;
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Proxy\UploadTreeProxy;
use Fossology\Lib\Proxy\ScanJobProxy;
use Monolog\Logger;
use Fossology\Lib\Data\AgentRef;

class ClearingDao
{
  private $dbManager;
  private $logger;
  private $uploadDao;
  private $licenseRefCache;

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

  private function getRelevantDecisionsCte(ItemTreeBounds $itemTreeBounds, $groupId, $onlyCurrent, &$statementName, &$params, $condition="")
  {
    $uploadTreeTable = $itemTreeBounds->getUploadTreeTableName();

    $params[] = DecisionTypes::WIP; $p1 = "$". count($params);
    $params[] = $groupId; $p2 = "$". count($params);

    $sql_upload = "";
    if ('uploadtree' === $uploadTreeTable || 'uploadtree_a' === $uploadTreeTable) {
      $params[] = $itemTreeBounds->getUploadId(); $p = "$". count($params);
      $sql_upload = "ut.upload_fk=$p AND ";
    }
    if (!empty($condition)) {
      $statementName .= ".(".$condition.")";
      $condition .= " AND ";
    }

    $filterClause = $onlyCurrent ? "DISTINCT ON(itemid)" : "";

    $statementName .= "." . $uploadTreeTable . ($onlyCurrent ? ".current": "");

    $globalScope = DecisionScopes::REPO;
    $localScope = DecisionScopes::ITEM;

    return "WITH allDecs AS (
              SELECT
                cd.clearing_decision_pk AS id,
                cd.pfile_fk AS pfile_id,
                ut.uploadtree_pk AS itemid,
                cd.user_fk AS user_id,
                cd.decision_type AS type_id,
                cd.scope AS scope,
                EXTRACT(EPOCH FROM cd.date_added) AS ts_added,
                CASE cd.scope WHEN $globalScope THEN 1 ELSE 0 END AS scopesort
              FROM clearing_decision cd
                INNER JOIN $uploadTreeTable ut
                  ON (ut.pfile_fk = cd.pfile_fk AND cd.scope = $globalScope)
                    OR (ut.uploadtree_pk = cd.uploadtree_fk
                      AND cd.scope = $localScope AND cd.group_fk = $p2)
              WHERE $sql_upload $condition
                cd.decision_type!=$p1),
            decision AS (
              SELECT $filterClause *
              FROM allDecs
              ORDER BY itemid, scopesort, id DESC
            )";
  }

  function getClearedLicenses(ItemTreeBounds $itemTreeBounds, $groupId)
  {
    $statementName = __METHOD__;

    $params = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
    $condition = "ut.lft BETWEEN $1 AND $2";

    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent=true, $statementName, $params, $condition);
    $params[] = DecisionTypes::IRRELEVANT;
    $sql = "$decisionsCte
            SELECT
              lr.rf_pk AS license_id,
              lr.rf_shortname AS shortname,
              lr.rf_fullname AS fullname
            FROM decision
              INNER JOIN clearing_decision_event cde ON cde.clearing_decision_fk = decision.id
              INNER JOIN clearing_event ce ON ce.clearing_event_pk = cde.clearing_event_fk
              INNER JOIN license_ref lr ON lr.rf_pk = ce.rf_fk
            WHERE NOT ce.removed AND type_id!=$".count($params)."
            GROUP BY license_id,shortname,fullname";

    $this->dbManager->prepare($statementName, $sql);

    $res = $this->dbManager->execute($statementName, $params);

    $licenses = array();
    while ($row = $this->dbManager->fetchArray($res)) {
      $licenses[] = new LicenseRef($row['license_id'], $row['shortname'], $row['fullname']);
    }
    $this->dbManager->freeResult($res);

    return $licenses;
  }


  function getFileClearings(ItemTreeBounds $itemTreeBounds, $groupId, $onlyCurrent=true, $forClearingHistory=false)
  {
    $this->dbManager->begin();

    $statementName = __METHOD__;

    $params = array($itemTreeBounds->getItemId());
    $condition = "ut.uploadtree_pk = $1";

    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent, $statementName, $params, $condition);

    $clearingsWithLicensesArray = $this->getDecisionsFromCte($decisionsCte, $statementName, $params, $forClearingHistory);

    $this->dbManager->commit();
    return $clearingsWithLicensesArray;
  }

  function getFileClearingsFolder(ItemTreeBounds $itemTreeBounds, $groupId, $includeSubFolders=true, $onlyCurrent=true)
  {
    $this->dbManager->begin();

    $statementName = __METHOD__;

    if (!$includeSubFolders) {
      $params = array($itemTreeBounds->getItemId());
      $condition = "ut.realparent = $1";
    } else {
      $params = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
      $condition = "ut.lft BETWEEN $1 AND $2";
    }

    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent, $statementName, $params, $condition);

    $clearingsWithLicensesArray = $this->getDecisionsFromCte($decisionsCte, $statementName, $params);

    $this->dbManager->commit();
    return $clearingsWithLicensesArray;
  }

  private function getDecisionsFromCte($decisionsCte, $statementName, $params, $forClearingHistory=false)
  {
    $sql = "$decisionsCte
            SELECT
              decision.*,
              users.user_name AS user_name,
              ce.clearing_event_pk as event_id,
              ce.user_fk as event_user_id,
              ce.group_fk as event_group_id,
              lr.rf_pk AS license_id,
              lr.rf_shortname AS shortname,
              lr.rf_fullname AS fullname,
              ce.removed AS removed,
              ce.type_fk AS event_type_id,
              ce.reportinfo AS reportinfo,
              ce.comment AS comment,
              ce.acknowledgement AS acknowledgement
            FROM decision
            LEFT JOIN users ON decision.user_id = users.user_pk
            LEFT JOIN clearing_decision_event cde ON cde.clearing_decision_fk = decision.id
            LEFT JOIN clearing_event ce ON ce.clearing_event_pk = cde.clearing_event_fk
            LEFT JOIN license_ref lr ON lr.rf_pk = ce.rf_fk
            ORDER BY decision.id DESC, event_id ASC";

    $this->dbManager->prepare($statementName, $sql);

    $result = $this->dbManager->execute($statementName, $params);
    $clearingsWithLicensesArray = array();

    $previousClearingId = -1;
    $previousItemId = -1;
    $clearingEvents = array();
    $clearingEventCache = array();
    $clearingDecisionBuilder = ClearingDecisionBuilder::create();
    $firstMatch = true;
    while ($row = $this->dbManager->fetchArray($result)) {
      $clearingId = $row['id'];
      $itemId = $row['itemid'];
      $licenseId = $row['license_id'];
      $eventId = $row['event_id'];
      $licenseShortName = $row['shortname'];
      $licenseName = $row['fullname'];
      $licenseIsRemoved = $row['removed'];

      $eventType = $row['event_type_id'];
      $eventUserId = $row['event_user_id'];
      $eventGroupId = $row['event_group_id'];
      $comment = $row['comment'];
      $reportInfo = $row['reportinfo'];
      $acknowledgement = $row['acknowledgement'];

      if ($clearingId !== $previousClearingId && $itemId !== $previousItemId) {
        //store the old one
        if (!$firstMatch) {
          $clearingsWithLicensesArray[] = $clearingDecisionBuilder->setClearingEvents($clearingEvents)->build();
        }

        $firstMatch = false;
        //prepare the new one
        if ($forClearingHistory) {
          $previousClearingId = $clearingId;
        } else {
          $previousItemId = $itemId;
        }
        $clearingEvents = array();
        $clearingDecisionBuilder = ClearingDecisionBuilder::create()
            ->setClearingId($row['id'])
            ->setUploadTreeId($itemId)
            ->setPfileId($row['pfile_id'])
            ->setUserName($row['user_name'])
            ->setUserId($row['user_id'])
            ->setType(intval($row['type_id']))
            ->setScope(intval($row['scope']))
            ->setTimeStamp($row['ts_added']);
      }

      if ($licenseId !== null) {
        if (!array_key_exists($eventId, $clearingEventCache)) {
          if (!array_key_exists($licenseId, $this->licenseRefCache)) {
            $this->licenseRefCache[$licenseId] = new LicenseRef($licenseId, $licenseShortName, $licenseName);
          }
          $licenseRef = $this->licenseRefCache[$licenseId];
          $clearingEventCache[$eventId] = $this->buildClearingEvent($eventId, $eventUserId, $eventGroupId, $licenseRef, $licenseIsRemoved, $eventType, $reportInfo, $comment, $acknowledgement);
        }
        $clearingEvents[] = $clearingEventCache[$eventId];
      }
    }

    if (!$firstMatch) {
      $clearingsWithLicensesArray[] = $clearingDecisionBuilder->setClearingEvents($clearingEvents)->build();
    }
    $this->dbManager->freeResult($result);

    return $clearingsWithLicensesArray;
  }
  public function getRelevantClearingDecision(ItemTreeBounds $itemTreeBounds, $groupId)
  {
    $clearingDecisions = $this->getFileClearings($itemTreeBounds, $groupId);
    if (count($clearingDecisions) > 0) {
      return $clearingDecisions[0];
    }
    return null;
  }

  public function removeWipClearingDecision($uploadTreeId, $groupId)
  {
    $sql = "DELETE FROM clearing_decision WHERE uploadtree_fk=$1 AND group_fk=$2 AND decision_type=$3";
    $this->dbManager->prepare($stmt = __METHOD__, $sql);
    $this->dbManager->freeResult($this->dbManager->execute($stmt, array($uploadTreeId, $groupId, DecisionTypes::WIP)));
  }

  public function createDecisionFromEvents($uploadTreeId, $userId, $groupId, $decType, $scope, $eventIds)
  {
    if ( ($scope == DecisionScopes::REPO) &&
         !empty($this->getCandidateLicenseCountForCurrentDecisions($uploadTreeId))) {
      throw new \Exception( _("Cannot add candidate license as global decision\n") );
    }
    $this->dbManager->begin();

    $this->removeWipClearingDecision($uploadTreeId, $groupId);

    $statementName = __METHOD__;
    $this->dbManager->prepare($statementName,
        "
INSERT INTO clearing_decision (
  uploadtree_fk,
  pfile_fk,
  user_fk,
  group_fk,
  decision_type,
  scope
) VALUES (
  $1,
  (SELECT pfile_fk FROM uploadtree WHERE uploadtree_pk=$1),
  $2,
  $3,
  $4,
  $5) RETURNING clearing_decision_pk
  ");
    $res = $this->dbManager->execute($statementName,
        array($uploadTreeId, $userId,  $groupId, $decType, $scope));
    $result = $this->dbManager->fetchArray($res);
    $clearingDecisionId = $result['clearing_decision_pk'];
    $this->dbManager->freeResult($res);

    $statementNameClearingDecisionEventInsert = __METHOD__ . ".insertClearingDecisionEvent";
    $this->dbManager->prepare($statementNameClearingDecisionEventInsert,
      "INSERT INTO clearing_decision_event (clearing_decision_fk, clearing_event_fk) VALUES($1, $2)"
    );

    foreach ($eventIds as $eventId) {
      $this->dbManager->freeResult($this->dbManager->execute($statementNameClearingDecisionEventInsert, array($clearingDecisionId, $eventId)));
    }

    $this->dbManager->commit();
  }

  public function getRelevantClearingEvents($itemTreeBounds, $groupId, $includeSubFolders=true)
  {
    $decision = $this->getFileClearingsFolder($itemTreeBounds, $groupId, $includeSubFolders, $onlyCurrent=true);
    $events = array();
    $date = 0;

    if (count($decision)) {
      foreach ($decision[0]->getClearingEvents() as $event) {
        $events[$event->getLicenseId()] = $event;
      }
      $date = $decision[0]->getTimeStamp();
    }

    $stmt = __METHOD__;
    $sql = 'SELECT rf_fk,rf_shortname,rf_fullname,clearing_event_pk,comment,type_fk,removed,reportinfo,acknowledgement, EXTRACT(EPOCH FROM date_added) AS ts_added
             FROM clearing_event LEFT JOIN license_ref ON rf_fk=rf_pk
             WHERE uploadtree_fk=$1 AND group_fk=$2 AND date_added>to_timestamp($3)
             ORDER BY clearing_event_pk ASC';
    $this->dbManager->prepare($stmt, $sql);
    $res = $this->dbManager->execute($stmt,array($itemTreeBounds->getItemId(),$groupId,$date));

    while ($row = $this->dbManager->fetchArray($res)) {
      $licenseRef = new LicenseRef($row['rf_fk'],$row['rf_shortname'],$row['rf_fullname']);
      $events[$row['rf_fk']] = ClearingEventBuilder::create()
              ->setEventId($row['clearing_event_pk'])
              ->setComment($row['comment'])
              ->setTimeStamp($row['ts_added'])
              ->setEventType($row['type_fk'])
              ->setLicenseRef($licenseRef)
              ->setRemoved($this->dbManager->booleanFromDb($row['removed']))
              ->setReportinfo($row['reportinfo'])
              ->setAcknowledgement($row['acknowledgement'])
              ->setUploadTreeId($itemTreeBounds->getItemId())
              ->build();
    }
    $this->dbManager->freeResult($res);
    return $events;
  }

  public function updateClearingEvent($uploadTreeId, $userId, $groupId, $licenseId, $what, $changeTo)
  {
    $this->dbManager->begin();

    $statementGetOldata = "SELECT * FROM clearing_event WHERE uploadtree_fk=$1 AND rf_fk=$2 AND group_fk=$3  ORDER BY clearing_event_pk DESC LIMIT 1";
    $statementName = __METHOD__ . 'getOld';
    $params = array($uploadTreeId, $licenseId, $groupId);
    $row = $this->dbManager->getSingleRow($statementGetOldata, $params, $statementName);

    if (!$row) {  //The license was not added as user decision yet -> we promote it here
      $type = ClearingEventTypes::USER;
      $row['type_fk'] = $type;
      $row['comment'] = "";
      $row['reportinfo'] = "";
      $row['acknowledgement'] = "";
    }

    if ($what == 'reportinfo') {
      $reportInfo = $changeTo;
      $comment = $row['comment'];
      $acknowledgement = $row['acknowledgement'];
    } elseif ($what == 'comment') {
      $reportInfo = $row['reportinfo'];
      $comment = $changeTo;
      $acknowledgement = $row['acknowledgement'];
    } else {
      $reportInfo = $row['reportinfo'];
      $comment = $row['comment'];
      $acknowledgement = $changeTo;
    }
    $this->insertClearingEvent($uploadTreeId, $userId, $groupId, $licenseId, false, $row['type_fk'], $reportInfo, $comment, $acknowledgement);

    $this->dbManager->commit();

  }

  public function copyEventIdTo($eventId, $itemId, $userId, $groupId)
  {
    $stmt = __METHOD__;
    $this->dbManager->prepare($stmt,
      "INSERT INTO clearing_event(uploadtree_fk, user_fk, group_fk, type_fk, rf_fk, removed, reportinfo, comment, acknowledgement)
        SELECT $2, $3, $4, type_fk, rf_fk, removed, reportinfo, comment, acknowledgement FROM clearing_event WHERE clearing_event_pk = $1"
      );

    $this->dbManager->freeResult($this->dbManager->execute($stmt, array($eventId, $itemId, $userId, $groupId)));
  }

  public function insertClearingEvent($uploadTreeId, $userId, $groupId, $licenseId, $isRemoved, $type = ClearingEventTypes::USER, $reportInfo = '', $comment = '', $acknowledgement = '', $jobId=0)
  {
    $insertIsRemoved = $this->dbManager->booleanToDb($isRemoved);

    $stmt = __METHOD__;
    $params = array($uploadTreeId, $userId, $groupId, $type, $licenseId, $insertIsRemoved, $reportInfo, $comment, $acknowledgement);
    $columns = "uploadtree_fk, user_fk, group_fk, type_fk, rf_fk, removed, reportinfo, comment, acknowledgement";
    $values = "$1,$2,$3,$4,$5,$6,$7,$8,$9";

    if ($jobId > 0) {
      $stmt.= ".jobId";
      $params[] = $jobId;
      $columns .= ", job_fk";
      $values .= ",$".count($params);
    } else {
      $this->markDecisionAsWip($uploadTreeId, $userId, $groupId);
    }

    $this->dbManager->prepare($stmt, "INSERT INTO clearing_event ($columns) VALUES($values) RETURNING clearing_event_pk");
    $res = $this->dbManager->execute($stmt, $params);

    $row = $this->dbManager->fetchArray($res);
    $this->dbManager->freeResult($res);

    return intval($row['clearing_event_pk']);
  }

  public function getEventIdsOfJob($jobId)
  {
    $statementName = __METHOD__;
    $this->dbManager->prepare(
        $statementName,
        "SELECT uploadtree_fk, clearing_event_pk, rf_fk FROM clearing_event WHERE job_fk = $1"
    );

    $res = $this->dbManager->execute($statementName, array($jobId));

    $events = array();
    while ($row = $this->dbManager->fetchArray($res)) {
      $itemId = intval($row['uploadtree_fk']);
      $eventId = intval($row['clearing_event_pk']);
      $licenseId = intval($row['rf_fk']);

      $events[$itemId][$licenseId] = $eventId;
    }
    $this->dbManager->freeResult($res);

    return $events;
  }

  protected function buildClearingEvent($eventId, $userId, $groupId, $licenseRef, $licenseIsRemoved, $type, $reportInfo, $comment, $acknowledgement)
  {
    $removed = $this->dbManager->booleanFromDb($licenseIsRemoved);

    return ClearingEventBuilder::create()
      ->setEventId($eventId)
      ->setUserId($userId)
      ->setGroupId($groupId)
      ->setEventType($type)
      ->setLicenseRef($licenseRef)
      ->setRemoved($removed)
      ->setReportInfo($reportInfo)
      ->setAcknowledgement($acknowledgement)
      ->setComment($comment)
      ->build();
  }

  public function markDecisionAsWip($uploadTreeId, $userId, $groupId)
  {
    $statementName = __METHOD__;

    $this->dbManager->prepare($statementName,
        "INSERT INTO clearing_decision (uploadtree_fk,pfile_fk,user_fk,group_fk,decision_type,scope) VALUES (
            $1, (SELECT pfile_fk FROM uploadtree WHERE uploadtree_pk=$1), $2, $3, $4, $5)");
    $res = $this->dbManager->execute($statementName,
        array($uploadTreeId, $userId, $groupId, DecisionTypes::WIP, DecisionScopes::ITEM));
    $this->dbManager->freeResult($res);
  }

  public function isDecisionWip($uploadTreeId, $groupId)
  {
    $sql = "SELECT decision_type FROM clearing_decision WHERE uploadtree_fk=$1 AND group_fk = $2 ORDER BY date_added DESC LIMIT 1";
    $latestDec = $this->dbManager->getSingleRow($sql,
                 array($uploadTreeId, $groupId), $sqlLog = __METHOD__);
    if ($latestDec === false) {
      return false;
    }
    return ($latestDec['decision_type'] == DecisionTypes::WIP);
  }

  public function isDecisionTBD($uploadTreeId, $groupId)
  {
    $sql = "SELECT decision_type FROM clearing_decision WHERE uploadtree_fk=$1 AND group_fk = $2 ORDER BY date_added DESC LIMIT 1";
    $latestDec = $this->dbManager->getSingleRow($sql,
                 array($uploadTreeId, $groupId), $sqlLog = __METHOD__);
    if ($latestDec === false) {
      return false;
    }
    return ($latestDec['decision_type'] == DecisionTypes::TO_BE_DISCUSSED);
  }

  public function isDecisionDNU($uploadTreeId, $groupId)
  {
    $sql = "SELECT decision_type FROM clearing_decision
              WHERE uploadtree_fk=$1 AND group_fk = $2
            ORDER BY clearing_decision_pk DESC LIMIT 1";
    $latestDec = $this->dbManager->getSingleRow($sql,
                 array($uploadTreeId, $groupId), $sqlLog = __METHOD__);
    if ($latestDec === false) {
      return false;
    }
    return ($latestDec['decision_type'] == DecisionTypes::DO_NOT_USE);
  }

  public function getBulkHistory(ItemTreeBounds $itemTreeBound, $groupId, $onlyTried = true)
  {
    $uploadTreeTableName = $itemTreeBound->getUploadTreeTableName();
    $itemId = $itemTreeBound->getItemId();
    $uploadId = $itemTreeBound->getUploadId();
    $left = $itemTreeBound->getLeft();

    $params = array($uploadId, $itemId, $left, $groupId);
    $stmt = __METHOD__ . "." . $uploadTreeTableName;

    $triedExpr = "$3 between ut2.lft and ut2.rgt";
    $triedFilter = "";
    if ($onlyTried) {
      $triedFilter = "and " . $triedExpr;
      $stmt .= ".tried";
    }

    $sql = "WITH alltried AS (
            SELECT lr.lrb_pk, ce.clearing_event_pk ce_pk, lr.rf_text, ce.uploadtree_fk,
              $triedExpr AS tried
            FROM license_ref_bulk lr
              LEFT JOIN highlight_bulk h ON lrb_fk = lrb_pk
              LEFT JOIN clearing_event ce ON ce.clearing_event_pk = h.clearing_event_fk
              LEFT JOIN $uploadTreeTableName ut ON ut.uploadtree_pk = ce.uploadtree_fk
              INNER JOIN $uploadTreeTableName ut2 ON ut2.uploadtree_pk = lr.uploadtree_fk
            WHERE ut2.upload_fk = $1 AND lr.group_fk = $4
              $triedFilter
              ORDER BY lr.lrb_pk
            ), aggregated_tried AS (
            SELECT DISTINCT ON(lrb_pk) lrb_pk, ce_pk, rf_text AS text, tried, matched
            FROM (
              SELECT DISTINCT ON(lrb_pk) lrb_pk, ce_pk, rf_text, tried, true AS matched FROM alltried WHERE uploadtree_fk = $2
              UNION ALL
              SELECT DISTINCT ON(lrb_pk) lrb_pk, ce_pk, rf_text, tried, false AS matched FROM alltried WHERE uploadtree_fk != $2 OR uploadtree_fk IS NULL
            ) AS result ORDER BY lrb_pk, matched DESC)
            SELECT lrb_pk, text, rf_shortname, removing, tried, ce_pk, matched
            FROM aggregated_tried
              INNER JOIN license_set_bulk lsb ON lsb.lrb_fk = lrb_pk
              INNER JOIN license_ref lrf ON lsb.rf_fk = lrf.rf_pk
            ORDER BY lrb_pk";

    $this->dbManager->prepare($stmt, $sql);
    $res = $this->dbManager->execute($stmt, $params);

    $bulks = array();
    while ($row = $this->dbManager->fetchArray($res)) {
      $bulkRun = $row['lrb_pk'];
      if (!array_key_exists($bulkRun, $bulks)) {
        $bulks[$bulkRun] = array(
            "bulkId" => $row['lrb_pk'],
            "id" => $row['ce_pk'],
            "text" => $row['text'],
            "matched" => $this->dbManager->booleanFromDb($row['matched']),
            "tried" => $this->dbManager->booleanFromDb($row['tried']),
            "removedLicenses" => array(),
            "addedLicenses" => array());
      }
      $key = $this->dbManager->booleanFromDb($row['removing']) ? 'removedLicenses' : 'addedLicenses';
      $bulks[$bulkRun][$key][] = $row['rf_shortname'];
    }

    $this->dbManager->freeResult($res);
    return $bulks;
  }


  public function getBulkMatches($bulkId, $groupId)
  {
    $stmt = __METHOD__;
    $sql = "SELECT uploadtree_fk AS itemid
            FROM clearing_event ce
            INNER JOIN highlight_bulk h
            ON ce.clearing_event_pk = h.clearing_event_fk
            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;
  }

  function getClearedLicenseIdAndMultiplicities(ItemTreeBounds $itemTreeBounds, $groupId)
  {
    $statementName = __METHOD__;

    $params = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
    $condition = "ut.lft BETWEEN $1 AND $2";

    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent=true, $statementName, $params, $condition);
    $params[] = DecisionTypes::IRRELEVANT;
    $sql = "$decisionsCte
            SELECT
              COUNT(DISTINCT itemid) AS count,
              lr.rf_shortname AS shortname,
              rf_pk
            FROM decision
              LEFT JOIN clearing_decision_event cde ON cde.clearing_decision_fk = decision.id
              LEFT JOIN clearing_event ce ON ce.clearing_event_pk = cde.clearing_event_fk
              LEFT JOIN license_ref lr ON lr.rf_pk = ce.rf_fk
            WHERE (NOT ce.removed OR clearing_event_pk IS NULL) AND type_id!=$".count($params)."
            GROUP BY shortname,rf_pk";

    $this->dbManager->prepare($statementName, $sql);
    $res = $this->dbManager->execute($statementName, $params);
    $multiplicity = array();
    while ($row = $this->dbManager->fetchArray($res)) {
      $shortname= empty($row['rf_pk']) ? LicenseDao::NO_LICENSE_FOUND : $row['shortname'];
      $multiplicity[$shortname] = $row;
    }
    $this->dbManager->freeResult($res);

    return $multiplicity;
  }

  public function markDirectoryAsDecisionType(ItemTreeBounds $itemTreeBounds, $groupId, $userId, $decisionMark)
  {
    if ($decisionMark == "doNotUse") {
      $decisionMark = DecisionTypes::DO_NOT_USE;
    } else {
      $decisionMark = DecisionTypes::IRRELEVANT;
    }
    $this->markDirectoryAsDecisionTypeIfScannerDetected($itemTreeBounds, $groupId, $userId, false, $decisionMark);
    $this->markDirectoryAsDecisionTypeIfUserEdited($itemTreeBounds, $groupId, $userId, false, $decisionMark);
  }

  public function deleteDecisionTypeFromDirectory(ItemTreeBounds $itemTreeBounds, $groupId, $userId, $decisionMark)
  {
    if ($decisionMark == "deleteDoNotUse") {
      $decisionMark = DecisionTypes::DO_NOT_USE;
    } else {
      $decisionMark = DecisionTypes::IRRELEVANT;
    }
    $this->markDirectoryAsDecisionTypeIfScannerDetected($itemTreeBounds, $groupId, $userId, true, $decisionMark);
    $this->markDirectoryAsDecisionTypeIfUserEdited($itemTreeBounds, $groupId, $userId, true, $decisionMark);
  }

  protected function markDirectoryAsDecisionTypeIfScannerDetected(ItemTreeBounds $itemTreeBounds, $groupId, $userId, $removeDecision=false, $decisionMark=DecisionTypes::IRRELEVANT)
  {
    $statementName = __METHOD__ ;
    $params = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
    $params[] = $groupId;
    $a = count($params);
    $options = array(UploadTreeProxy::OPT_SKIP_THESE=>'noLicense',
                     UploadTreeProxy::OPT_ITEM_FILTER=>' AND (lft BETWEEN $1 AND $2)',
                     UploadTreeProxy::OPT_GROUP_ID=>'$'.$a.'');
    $uploadTreeProxy = new UploadTreeProxy($itemTreeBounds->getUploadId(), $options, $itemTreeBounds->getUploadTreeTableName());
    if (!$removeDecision) {
      $params[] = $userId;
      $params[] = $decisionMark;
      $params[] = DecisionScopes::ITEM;
      $sql = $uploadTreeProxy->asCte()
          .' INSERT INTO clearing_decision (uploadtree_fk,pfile_fk,user_fk,group_fk,decision_type,scope)
             SELECT uploadtree_pk itemid,pfile_fk pfile_id, $'.($a+1).', $'.$a.', $'.($a+2).', $'.($a+3).'
               FROM UploadTreeView WHERE NOT EXISTS (
             SELECT uploadtree_fk FROM clearing_decision
              WHERE decision_type=$'.($a+2).' AND uploadtree_fk=UploadTreeView.uploadtree_pk)';
    } else {
      $params[] = $decisionMark;
      $sql = $uploadTreeProxy->asCte()
          .' DELETE FROM clearing_decision WHERE clearing_decision_pk IN (
             SELECT clearing_decision_pk
               FROM clearing_decision cd INNER JOIN (
             SELECT MAX(date_added) AS date_added, uploadtree_fk
               FROM clearing_decision WHERE uploadtree_fk IN (
             SELECT uploadtree_pk FROM UploadTreeView) GROUP BY uploadtree_fk) cd2 ON cd.uploadtree_fk = cd2.uploadtree_fk
                AND cd.date_added = cd2.date_added AND decision_type = $'.($a+1).')';
    }
    $this->dbManager->prepare($statementName, $sql);
    $res = $this->dbManager->execute($statementName,$params);
    $this->dbManager->freeResult($res);
  }

  protected function markDirectoryAsDecisionTypeIfUserEdited(ItemTreeBounds $itemTreeBounds, $groupId, $userId, $removeDecision=false, $decisionMark=DecisionTypes::IRRELEVANT)
  {
    $statementName = __METHOD__ ;
    $params = array($itemTreeBounds->getLeft(), $itemTreeBounds->getRight());
    $condition = "ut.lft BETWEEN $1 AND $2";
    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent=true, $statementName, $params, $condition);
    if (!$removeDecision) {
      $params[] = $userId;
      $a = count($params);
      $params[] = $groupId;
      $params[] = $decisionMark;
      $params[] = DecisionScopes::ITEM;
      $this->dbManager->prepare($statementName, $decisionsCte
          .' INSERT INTO clearing_decision (uploadtree_fk,pfile_fk,user_fk,group_fk,decision_type,scope)
             SELECT itemid,pfile_id, $'.$a.', $'.($a+1).', $'.($a+2).', $'.($a+3).'
               FROM allDecs ad WHERE type_id != $'.($a+2));
    } else {
      $params[] = $decisionMark;
      $a = count($params);
      $this->dbManager->prepare($statementName, $decisionsCte
          .' DELETE FROM clearing_decision WHERE decision_type = $'.$a.'
                AND clearing_decision_pk IN (
             SELECT id FROM allDecs WHERE type_id = $'.$a.')');
    }
    $res = $this->dbManager->execute($statementName,$params);
    $this->dbManager->freeResult($res);
  }

  public function getMainLicenseIds($uploadId, $groupId)
  {
    $stmt = __METHOD__;
    $sql = "SELECT rf_fk FROM upload_clearing_license WHERE upload_fk=$1 AND group_fk=$2";
    $this->dbManager->prepare($stmt, $sql);
    $res = $this->dbManager->execute($stmt,array($uploadId,$groupId));
    $ids = array();
    while ($row = $this->dbManager->fetchArray($res)) {
      $ids[$row['rf_fk']] = $row['rf_fk'];
    }
    $this->dbManager->freeResult($res);
    return $ids;
  }

  public function makeMainLicense($uploadId, $groupId, $licenseId)
  {
    $this->dbManager->insertTableRow('upload_clearing_license',
            array('upload_fk'=>$uploadId,'group_fk'=>$groupId,'rf_fk'=>$licenseId));
  }

  public function removeMainLicense($uploadId, $groupId, $licenseId)
  {
    $this->dbManager->getSingleRow('DELETE FROM upload_clearing_license WHERE upload_fk=$1 AND group_fk=$2 AND rf_fk=$3',
            array($uploadId,$groupId,$licenseId));
  }

  function getFilesForDecisionTypeFolderLevel(ItemTreeBounds $itemTreeBounds, $groupId, $onlyCurrent=true, $decisionMark="")
  {
    if (!empty($decisionMark)) {
      $decisionMark = DecisionTypes::DO_NOT_USE;
    } else {
      $decisionMark = DecisionTypes::IRRELEVANT;
    }
    $statementName = __METHOD__;
    $params = array();
    $decisionsCte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, $onlyCurrent, $statementName, $params);
    $params[] = $decisionMark;
    $sql = "$decisionsCte
            SELECT
      itemid as uploadtree_pk,
            lr.rf_shortname AS shortname,
      comment
            FROM decision
            LEFT JOIN clearing_decision_event cde ON cde.clearing_decision_fk = decision.id
            LEFT JOIN clearing_event ce ON ce.clearing_event_pk = cde.clearing_event_fk
            LEFT JOIN license_ref lr ON lr.rf_pk = ce.rf_fk
            WHERE type_id=$".count($params);
    $this->dbManager->prepare($statementName, $sql);
    $res = $this->dbManager->execute($statementName, $params);
    $irrelevantFiles = $this->dbManager->fetchAll($res);
    $this->dbManager->freeResult($res);
    return $irrelevantFiles;
  }

  public function getPreviousBulkIds($uploadId, $groupId, $userId, $onlyCount=0)
  {
    $stmt = __METHOD__;
    $bulkIds = array();
    $sql = "SELECT jq_args FROM upload_reuse, jobqueue, job
            WHERE upload_fk=$1 AND group_fk=$2
             AND EXISTS(SELECT * FROM group_user_member gum WHERE gum.group_fk=upload_reuse.group_fk AND gum.user_fk=$3)
             AND jq_type=$4 AND jq_job_fk=job_pk
             AND job_upload_fk=reused_upload_fk AND job_group_fk=reused_group_fk";
    $this->dbManager->prepare($stmt, $sql);
    $res = $this->dbManager->execute($stmt,array($uploadId, $groupId, $userId,'monkbulk'));
    while ($row=  $this->dbManager->fetchArray($res)) {
      $bulkIds = array_merge($bulkIds,explode("\n", $row['jq_args']));
    }
    $this->dbManager->freeResult($res);
    if (empty($onlyCount)) {
      return array_unique($bulkIds);
    } else {
      return count(array_unique($bulkIds));
    }
  }

  public function getClearingDecisionsCount($uploadId, $groupId)
  {
    $itemTreeBounds = $this->uploadDao->getParentItemBounds($uploadId);
    $statementName = "";
    $params = array();

    $cte = $this->getRelevantDecisionsCte($itemTreeBounds, $groupId, true,
      $statementName, $params);

    $statementName = __METHOD__ . $statementName;
    $sql = "$cte SELECT COUNT(*) AS cnt FROM decision WHERE type_id <> ".DecisionTypes::TO_BE_DISCUSSED;

    $clearedCounter = $this->dbManager->getSingleRow($sql, $params,
      $statementName);
    return $clearedCounter['cnt'];
  }

  public function getTotalDecisionCount($uploadId, $groupId)
  {
    $uploadTreeTable = $this->uploadDao->getUploadtreeTableName($uploadId);
    $scanJobProxy = new ScanJobProxy($GLOBALS['container']->get('dao.agent'), $uploadId);
    $scanJobProxy->createAgentStatus(array_keys(AgentRef::AGENT_LIST));
    $latestAgentIds = $scanJobProxy->getLatestSuccessfulAgentIds();
    $agentIds = "{" . implode(",", $latestAgentIds) . "}";

    $globalScope = DecisionScopes::REPO;
    $params = array($groupId, $uploadId, $agentIds);
    $statement = __METHOD__ . "." . $uploadTreeTable;
    $sql = "
WITH allDecs AS (
  SELECT DISTINCT ON (ut.uploadtree_pk) * FROM $uploadTreeTable AS ut
    LEFT JOIN license_file AS lf
      ON lf.pfile_fk = ut.pfile_fk
      AND lf.agent_fk = ANY($3::int[])
      AND lf.rf_fk NOT IN (SELECT rf_pk FROM license_ref
        WHERE rf_shortname = ANY(VALUES('No_license_found'),('Void'))
      )
      AND lf.rf_fk IS NOT NULL
    LEFT JOIN clearing_decision AS cd ON
      (ut.uploadtree_pk = cd.uploadtree_fk)
      OR (ut.pfile_fk = cd.pfile_fk AND cd.scope = $globalScope)
      AND cd.group_fk = $1
  WHERE ut.upload_fk = $2 AND (
    CASE
      WHEN lf.fl_pk IS NULL AND cd.clearing_decision_pk IS NULL
        THEN FALSE
        ELSE TRUE
      END
  )
)
SELECT count(*) AS cnt
FROM (SELECT DISTINCT uploadtree_pk FROM allDecs) AS no_license_uploadtree;";
    $foundCounter = $this->dbManager->getSingleRow($sql, $params, $statement);
    return $foundCounter['cnt'];
  }

  public function getCandidateLicenseCountForCurrentDecisions($uploadTreeId, $uploadId=0)
  {
    if (!empty($uploadId)) {
      $itemTreeBounds = $this->uploadDao->getParentItemBounds($uploadId, $uploadTreeTableName);
      $uploadTreeTableName = $this->uploadDao->getUploadtreeTableName($uploadId);
      $params[] = $itemTreeBounds->getLeft();
      $params[] = $itemTreeBounds->getRight();
      $condition = "UT.lft BETWEEN $1 AND $2";
      $uploadtreeStatement = " uploadtree_fk IN (SELECT uploadtree_pk FROM $uploadTreeTableName UT WHERE $condition)";
    } else {
      $params = array($uploadTreeId);
      $uploadtreeStatement = " uploadtree_fk = $1";
    }

    $sql = "WITH latestEvents AS (
      SELECT rf_fk, date_added, removed FROM (
        SELECT rf_fk, date_added, removed, row_number()
          OVER (PARTITION BY rf_fk ORDER BY date_added DESC) AS ROWNUM
        FROM clearing_event WHERE $uploadtreeStatement) SORTABLE 
          WHERE ROWNUM = 1 ORDER BY rf_fk)
      SELECT count(*) FROM license_candidate WHERE license_candidate.rf_pk IN
        (SELECT rf_fk FROM latestEvents WHERE removed=false);";
    $countCandidate = $this->dbManager->getSingleRow($sql,
                 $params, $sqlLog = __METHOD__);

    return $countCandidate['count'];
  }

  public function marklocalDecisionsAsGlobal($uploadId)
  {
    $statementName = __METHOD__ . $uploadId;

    $sql = "WITH latestDecisions AS (
              SELECT clearing_decision_pk FROM (
                SELECT clearing_decision_pk, uploadtree_fk, date_added, row_number()
                  OVER (PARTITION BY uploadtree_fk ORDER BY date_added DESC) AS ROWNUM
              FROM clearing_decision WHERE uploadtree_fk IN
                   (SELECT uploadtree_pk FROM uploadtree WHERE upload_fk = $1)) SORTABLE
                WHERE ROWNUM = $2 ORDER BY uploadtree_fk)
             UPDATE clearing_decision SET scope = $2 WHERE clearing_decision_pk IN (
               SELECT clearing_decision_pk FROM latestDecisions) RETURNING clearing_decision_pk";

    $countUpdated = $this->dbManager->getSingleRow($sql,
                 array($uploadId, DecisionScopes::REPO), $statementName);

    return count($countUpdated);
  }
}