1<?php
2/*
3Copyright (C) 2015,2021, Siemens AG
4
5This program is free software; you can redistribute it and/or
6modify it under the terms of the GNU General Public License
7version 2 as published by the Free Software Foundation.
8
9This program is distributed in the hope that it will be useful,
10but WITHOUT ANY WARRANTY; without even the implied warranty of
11MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12GNU General Public License for more details.
13
14You should have received a copy of the GNU General Public License along
15with this program; if not, write to the Free Software Foundation, Inc.,
1651 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
17*/
18
19namespace Fossology\Lib\Application;
20
21use Fossology\Lib\BusinessRules\LicenseMap;
22use Fossology\Lib\Db\DbManager;
23
24/**
25 * @file
26 * @brief Helper class to export license list as a CSV from the DB
27 */
28
29/**
30 * @class LicenseCsvExport
31 * @brief Helper class to export license list as a CSV from the DB
32 */
33class LicenseCsvExport
34{
35  /** @var DbManager $dbManager
36   * DB manager in use */
37  protected $dbManager;
38  /** @var string $delimiter
39   * Delimiter for CSV */
40  protected $delimiter = ',';
41  /** @var string $enclosure
42   * Enclosure for CSV strings */
43  protected $enclosure = '"';
44
45  /**
46   * Constructor
47   * @param DbManager $dbManager DB manager to use.
48   */
49  public function __construct(DbManager $dbManager)
50  {
51    $this->dbManager = $dbManager;
52  }
53
54  /**
55   * @brief Update the delimiter
56   * @param string $delimiter New delimiter to use.
57   */
58  public function setDelimiter($delimiter=',')
59  {
60    $this->delimiter = substr($delimiter,0,1);
61  }
62
63  /**
64   * @brief Update the enclosure
65   * @param string $enclosure New enclosure to use.
66   */
67  public function setEnclosure($enclosure='"')
68  {
69    $this->enclosure = substr($enclosure,0,1);
70  }
71
72  /**
73   * @brief Create the CSV from the DB
74   * @param int $rf Set the license ID to get only one license, set 0 to get all
75   * @return string csv
76   */
77  public function createCsv($rf=0)
78  {
79    $forGroupBy = " GROUP BY rf.rf_shortname, rf.rf_fullname, rf.rf_text, rc.rf_shortname, rr.rf_shortname, rf.rf_url, rf.rf_notes, rf.rf_source, rf.rf_risk, gp.group_name";
80    $sql = "WITH marydoneCand AS (
81  SELECT * FROM license_candidate
82  WHERE marydone = true
83), allLicenses AS (
84SELECT DISTINCT ON(rf_pk) * FROM
85  ONLY license_ref
86  NATURAL FULL JOIN marydoneCand)
87SELECT
88  rf.rf_shortname, rf.rf_fullname, rf.rf_text, rc.rf_shortname parent_shortname,
89  rr.rf_shortname report_shortname, rf.rf_url, rf.rf_notes, rf.rf_source,
90  rf.rf_risk, gp.group_name, string_agg(ob_topic, ', ') obligations
91FROM allLicenses AS rf
92  LEFT OUTER JOIN obligation_map om ON om.rf_fk = rf.rf_pk
93  LEFT OUTER JOIN obligation_ref ON ob_fk = ob_pk
94  FULL JOIN groups AS gp ON gp.group_pk = rf.group_fk
95  LEFT JOIN license_map mc ON mc.rf_fk=rf.rf_pk AND mc.usage=$2
96  LEFT JOIN license_ref rc ON mc.rf_parent=rc.rf_pk
97  LEFT JOIN license_map mr ON mr.rf_fk=rf.rf_pk AND mr.usage=$3
98  LEFT JOIN license_ref rr ON mr.rf_parent=rr.rf_pk
99WHERE rf.rf_detector_type=$1";
100
101    $param = array(1, LicenseMap::CONCLUSION, LicenseMap::REPORT);
102    if ($rf > 0) {
103      $stmt = __METHOD__ . '.rf';
104      $param[] = $rf;
105      $sql .= ' AND rf.rf_pk = $'.count($param).$forGroupBy;
106      $row = $this->dbManager->getSingleRow($sql,$param,$stmt);
107      $vars = $row ? array( $row ) : array();
108    } else {
109      $stmt = __METHOD__;
110      $sql .= $forGroupBy . ', rf.rf_pk ORDER BY rf.rf_pk';
111      $this->dbManager->prepare($stmt,$sql);
112      $res = $this->dbManager->execute($stmt,$param);
113      $vars = $this->dbManager->fetchAll( $res );
114      $this->dbManager->freeResult($res);
115    }
116    $out = fopen('php://output', 'w');
117    ob_start();
118    $head = array(
119      'shortname', 'fullname', 'text', 'parent_shortname', 'report_shortname',
120      'url', 'notes', 'source', 'risk', 'group', 'obligations');
121    fputcsv($out, $head, $this->delimiter, $this->enclosure);
122    foreach ($vars as $row) {
123      fputcsv($out, $row, $this->delimiter, $this->enclosure);
124    }
125    $content = ob_get_contents();
126    ob_end_clean();
127    return $content;
128  }
129}
130