ObligationCsvImport.php

Go to the documentation of this file.

<?php
/*
 SPDX-FileCopyrightText: © 2014-2017 Siemens AG
 
 SPDX-License-Identifier: GPL-2.0-only
*/
 
namespace Fossology\Lib\Application;
 
use Fossology\Lib\Db\DbManager;
use Fossology\Lib\Util\ArrayOperation;
use Fossology\Lib\BusinessRules\ObligationMap;
 
class ObligationCsvImport
{
  protected $dbManager;
  protected $delimiter = ',';
  protected $enclosure = '"';
  protected $headrow = null;
  protected $alias = array(
      'type'=>array('type','Type'),
      'topic'=>array('topic','Obligation or Risk topic'),
      'text'=>array('text','Full Text'),
      'classification'=>array('classification','Classification'),
      'modifications'=>array('modifications','Apply on modified source code'),
      'comment'=>array('comment','Comment'),
      'licnames'=>array('licnames','Associated Licenses'),
      'candidatenames'=>array('candidatenames','Associated candidate Licenses'),
      'external_id'=>array('id','LicenseDB Id'),
    );
 
  protected $obligationMap;
 
  public function __construct(DbManager $dbManager, $obligationMap = null)
  {
    $this->dbManager = $dbManager;
    $this->obligationMap = $obligationMap;
  }
 
  public function setDelimiter($delimiter=',')
  {
    $this->delimiter = substr($delimiter,0,1);
  }
 
  public function setEnclosure($enclosure='"')
  {
    $this->enclosure = substr($enclosure,0,1);
  }
 
  public function handleFile($filename, $fileExtension)
  {
    if (!is_file($filename) || ($handle = fopen($filename, 'r')) === false) {
      return _('Internal error');
    }
    $cnt = -1;
    $msg = '';
    try {
      if ($fileExtension == 'csv') {
        while (($row = fgetcsv($handle,0,$this->delimiter,$this->enclosure)) !== false) {
          $log = $this->handleCsv($row);
          if (!empty($log)) {
            $msg .= "$log\n";
          }
          $cnt++;
        }
        $msg .= _('Read csv').(": $cnt ")._('obligations');
      } else {
        $jsonContent = fread($handle, filesize($filename));
        $data = json_decode($jsonContent, true);
        if ($data === null && json_last_error() !== JSON_ERROR_NONE) {
          $msg .= "Error decoding JSON: " . json_last_error_msg() . "\n";
        }
        $msg = $this->importJsonData($data, $msg);
        $msg .= _('Read json').(":". count($data) ." ")._('obligations');
      }
    } catch(\Exception $e) {
      fclose($handle);
      return $msg .= _('Error while parsing file').': '.$e->getMessage();
    }
    fclose($handle);
    return $msg;
  }
 
  function handleRowJson($row)
  {
    $newArray = array();
    foreach ($row as $key => $value) {
      $newKey = $key;
      foreach ($this->alias as $aliasKey => $aliasValues) {
        if (in_array($key, $aliasValues)) {
          $newKey = $aliasKey;
          break;
        }
      }
      $newArray[$newKey] = $value;
    }
    return $newArray;
  }
 
  private function handleCsv($row)
  {
    if ($this->headrow===null) {
      $this->headrow = $this->handleHeadCsv($row);
      return 'head okay';
    }
 
    $mRow = array();
    foreach (array('type','topic','text','classification','modifications','comment','licnames','candidatenames') as $needle) {
      $mRow[$needle] = $row[$this->headrow[$needle]];
    }
 
    return $this->handleCsvObligation($mRow);
  }
 
  private function handleHeadCsv($row)
  {
    $headrow = array();
    foreach (array('type','topic','text','classification','modifications','comment','licnames','candidatenames') as $needle) {
      $col = ArrayOperation::multiSearch($this->alias[$needle], $row);
      if (false === $col) {
        throw new \Exception("Undetermined position of $needle");
      }
      $headrow[$needle] = $col;
    }
    return $headrow;
  }
 
  private function getKeyFromTopicAndText($row)
  {
    $req = array($row['topic'], $row['text']);
    $row = $this->dbManager->getSingleRow('SELECT ob_pk FROM obligation_ref WHERE ob_topic=$1 AND ob_md5=md5($2)',$req);
    return ($row === false) ? false : $row['ob_pk'];
  }
 
  private function compareLicList($exists, $listFromCsv, $candidate, $row)
  {
    $getList = $this->obligationMap->getLicenseList($exists, $candidate);
    $listFromDb = $this->reArrangeString($getList);
    $listFromCsv = $this->reArrangeString($listFromCsv);
    return strcmp($listFromDb, $listFromCsv);
  }
 
  private function reArrangeString($string)
  {
    $string = explode(";", $string);
    sort($string);
    return implode(",", $string);
  }
 
  private function clearListFromDb($exists, $candidate)
  {
    $licId = 0;
    $this->obligationMap->unassociateLicenseFromObligation($exists, $licId, $candidate);
    return true;
  }
 
  private function handleCsvObligation($row)
  {
    /* @var $dbManager DbManager */
    $dbManager = $this->dbManager;
    $exists = $this->getKeyFromTopicAndText($row);
    $associatedLicenses = "";
    $candidateLicenses = "";
    $msg = "";
    if ($exists !== false) {
      $msg = $this->updateOtherFields($exists, $row);
      if ( $this->compareLicList($exists, $row['licnames'], false, $row) === 0 ) {
        $msg .=" No Changes in AssociateLicense";
      } else {
        $this->clearListFromDb($exists, false);
        if (!empty($row['licnames'])) {
          $associatedLicenses .= $this->AssociateWithLicenses($row['licnames'], $exists, false);
        }
        $msg .=" Updated AssociatedLicense license";
      }
      if ($this->compareLicList($exists, $row['candidatenames'], true, $row) === 0) {
        $msg .=" No Changes in CandidateLicense";
      } else {
        $this->clearListFromDb($exists, true);
        if (!empty($row['candidatenames'])) {
          $associatedLicenses .= $this->AssociateWithLicenses($row['candidatenames'], $exists, true);
        }
        $msg .=" Updated CandidateLicense";
      }
      return $msg . "\n" . $associatedLicenses . "\n";
    }
 
    $stmtInsert = __METHOD__.'.insert';
    $dbManager->prepare($stmtInsert,'INSERT INTO obligation_ref (ob_type,ob_topic,ob_text,ob_classification,ob_modifications,ob_comment,ob_md5)'
            . ' VALUES ($1,$2,$3,$4,$5,$6,md5($3)) RETURNING ob_pk');
    $resi = $dbManager->execute($stmtInsert,array($row['type'],$row['topic'],$row['text'],$row['classification'],$row['modifications'],$row['comment']));
    $new = $dbManager->fetchArray($resi);
    $dbManager->freeResult($resi);
 
    if (!empty($row['licnames'])) {
      $associatedLicenses .= $this->AssociateWithLicenses($row['licnames'], $new['ob_pk']);
    }
    if (!empty($row['candidatenames'])) {
      $candidateLicenses = $this->AssociateWithLicenses($row['candidatenames'], $new['ob_pk'], true);
    }
 
    $message = "License association results for obligation '$row[topic]':\n";
    $message .= "$associatedLicenses";
    $message .= "$candidateLicenses";
    $message .= "Obligation with id=$new[ob_pk] was added successfully.\n";
    return $message;
  }
 
  private function handleLicenseDBObligationImport($row)
  {
    if (empty($row["external_id"])) {
      return "Error: external_id cannot be empty";
    }
    $stmt = __METHOD__ . ".getExistingObligation";
    $obPk = $this->dbManager->getSingleRow("SELECT ob_pk FROM " .
      "obligation_ref WHERE ob_external_id=$1", array($row["external_id"]), $stmt);
    $log = '';
    if (!empty($obPk)) {
      // update obligation
      $log .= $this->updateOtherFields($obPk['ob_pk'], $row);
 
      $this->dbManager->begin();
      // fetch new license associations
      $stmt = __METHOD__ . "getNewLicenseAssociations";
      $newLicIds = array();
      foreach ($row['license_ids'] as $licExternalId) {
        $rfPk = $this->dbManager->getSingleRow("SELECT rf_pk FROM license_ref WHERE rf_external_id = $1;",
          array($licExternalId), $stmt);
        if (!empty($rfPk)) {
          $newLicIds[] = $rfPk['rf_pk'];
        } else {
          $log .= \sprintf('license with rf_external_id %s not found', $licExternalId);
        }
      }
 
      // fetch old license associations
      $stmt = __METHOD__ . "getOldLiceneAssociations";
      $oldLicIdsDb = $this->dbManager->getRows("SELECT rf_fk FROM obligation_map WHERE ob_fk = $1", array($obPk['ob_pk']), $stmt);
      $oldLicIds = array();
      foreach ($oldLicIdsDb as $licId) {
        $oldLicIds[] = $licId['rf_fk'];
      }
 
      // create diff of licenses between current associated licenses and licensedb
      // associated licenses
      $diff = ArrayOperation::getArrayDiffs($oldLicIds, $newLicIds);
 
      if (!empty($diff['remove'])) {
        $log .= ' removed license ids [' . implode(',', $diff['remove']) . ']';
      }
      if (!empty($diff['add'])) {
        $log .= ' added license ids [' . implode(',', $diff['add']) . ']';
      }
 
      // delete associations
      $this->obligationMap->unassociateLicenseFromLicenseList($obPk['ob_pk'], $diff['remove']);
 
      // insert associations
      $this->obligationMap->associateLicenseFromLicenseList($obPk['ob_pk'], $diff['add']);
 
      $this->dbManager->commit();
    } else {
      $stmtInsert = __METHOD__.'.insert';
      $this->dbManager->prepare($stmtInsert,'INSERT INTO obligation_ref (ob_type,ob_topic,ob_text,ob_classification,ob_modifications,ob_comment,ob_md5,ob_external_id)'
              . ' VALUES ($1,$2,$3,$4,$5,$6,md5($3),$7) RETURNING ob_pk');
      $resi = $this->dbManager->execute($stmtInsert,array($row['type'],$row['topic'],$row['text'],$row['classification'],'False',$row['comment'], $row['external_id']));
      $new = $this->dbManager->fetchArray($resi);
      $this->dbManager->freeResult($resi);
 
      $this->dbManager->begin();
      $newLicIds = array();
      $stmt = __METHOD__ . "getNewLicensesForInsert";
      foreach ($row['license_ids'] as $licExternalId) {
        $rfPk = $this->dbManager->getSingleRow("SELECT rf_pk FROM license_ref WHERE rf_external_id = $1;",
          array($licExternalId), $stmt);
        if (!empty($rfPk)) {
          $newLicIds[] = $rfPk['rf_pk'];
        } else {
          $message .= \sprintf('license with rf_external_id %s not found', $licExternalId);
        }
      }
 
      // insert associations
      $this->obligationMap->associateLicenseFromLicenseList($new['ob_pk'], $newLicIds);
      if (!empty($newLicIds)) {
        $log .= ' added license ids [' . implode(',', $newLicIds) . ']';
      }
 
      $this->dbManager->commit();
    }
    return $log;
  }
 
  function AssociateWithLicenses($licList, $obPk, $candidate=False)
  {
    $associatedLicenses = "";
    $message = "";
 
    $licenses = explode(";",$licList);
    foreach ($licenses as $license) {
      $licIds = $this->obligationMap->getIdFromShortname($license, $candidate);
      $updated = false;
      if (empty($licIds)) {
        $message .= "License $license could not be found in the DB.\n";
      } else {
        $updated = $this->obligationMap->associateLicenseFromLicenseList($obPk,
          $licIds, $candidate);
      }
      if ($updated) {
        if ($associatedLicenses == "") {
          $associatedLicenses = "$license";
        } else {
          $associatedLicenses .= ";$license";
        }
      }
    }
 
    if (!empty($associatedLicenses)) {
      $message .= "$associatedLicenses were associated.\n";
    } else {
      $message .= "No ";
      $message .= $candidate ? "candidate": "";
      $message .= "licenses were associated.\n";
    }
    return $message;
  }
 
  function updateOtherFields($exists, $row)
  {
    $stmt = __METHOD__ . '.getOldObligation';
    $oldObligation = $this->dbManager->getSingleRow(
      'SELECT ob_topic, ob_text, ob_classification, ob_modifications, ob_comment, ob_type FROM obligation_ref WHERE ob_pk=$1',
      array($exists),
      $stmt
    );
 
    if (empty($oldObligation)) {
      return "Obligation with id=$exists not found";
    }
 
    $log = "Obligation topic '$oldObligation[ob_topic]' already exists in DB (id = $exists)";
    $stmt = __METHOD__ . '.updateOtherOb';
    $sql = 'UPDATE obligation_ref SET ';
    $extraParams = array();
    $params = array($exists);
 
    if (!empty($row['external_id'])) {
      if (isset($row['topic']) && $row['topic'] != $oldObligation['ob_topic']) {
        $params[] = $row['topic'];
        $stmt .= '.topic';
        $extraParams[] = 'ob_topic=$' . count($params);
        $log .= ', updated topic';
      }
      if (isset($row['text']) && $row['text'] != $oldObligation['ob_text']) {
        $params[] = $row['text'];
        $stmt .= '.text';
        $extraParams[] = 'ob_text=$' . count($params) . ',ob_md5=md5($' .
          count($params) . ')';
        $log .= ', updated text';
      }
      if (isset($row['classification']) && $row['classification'] != $oldObligation['ob_classification']) {
        $params[] = $row['classification'];
        $stmt .= '.classification';
        $extraParams[] = 'ob_classification=$' . count($params);
        $log .= ', updated classification';
      }
      if (isset($row['comment']) && $row['comment'] != $oldObligation['ob_comment']) {
        $params[] = $row['comment'];
        $stmt .= '.comment';
        $extraParams[] = 'ob_comment=$' . count($params);
        $log .= ', updated comment';
      }
      if (isset($row['type']) && $row['type'] != $oldObligation['ob_type']) {
        $params[] = $row['type'];
        $stmt .= '.type';
        $extraParams[] = 'ob_type=$' . count($params);
        $log .= ', updated type';
      }
    } else {
      if (isset($row['classification']) && $row['classification'] != $oldObligation['ob_classification']) {
        $params[] = $row['classification'];
        $stmt .= '.classification';
        $extraParams[] = 'ob_classification=$' . count($params);
        $log .= ', updated classification';
      }
      if (isset($row['modifications']) && $row['modifications'] != $oldObligation['ob_modifications']) {
        $params[] = $row['modifications'];
        $stmt .= '.modifications';
        $extraParams[] = 'ob_modifications=$' . count($params);
        $log .= ', updated modifications';
      }
      if (isset($row['comment']) && $row['comment'] != $oldObligation['ob_comment']) {
        $params[] = $row['comment'];
        $stmt .= '.comment';
        $extraParams[] = 'ob_comment=$' . count($params);
        $log .= ', updated comment';
      }
    }
 
    if (count($params) > 1) {
      $sql .= join(',', $extraParams);
      $sql .= ' WHERE ob_pk=$1';
      $this->dbManager->getSingleRow($sql, $params, $stmt);
    }
 
    return $log;
  }
 
  public function importJsonData($data, string $msg): string
  {
    foreach ($data as $row) {
      $log = $this->handleLicenseDBObligationImport($this->handleRowJson($row));
      if (!empty($log)) {
        $msg .= "$log\n";
      }
    }
    return $msg;
  }
}