1<?php
2
3/**
4 * The reporting class for simple report generation.
5 *
6 * This Source Code Form is subject to the terms of the Mozilla Public License,
7 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
8 * obtain one at http://mozilla.org/MPL/2.0/.
9 *
10 * @package   phpMyFAQ
11 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
12 * @author    Gustavo Solt <gustavo.solt@mayflower.de>
13 * @copyright 2011-2020 phpMyFAQ Team
14 * @license   http://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0
15 * @link      https://www.phpmyfaq.de
16 * @since     2011-02-04
17 */
18
19namespace phpMyFAQ;
20
21/**
22 * Class Report
23 *
24 * @package phpMyFAQ
25 */
26class Report
27{
28    /**
29     * @var Configuration
30     */
31    private $config;
32
33    /**
34     * Constructor.
35     *
36     * @param Configuration
37     */
38    public function __construct(Configuration $config)
39    {
40        $this->config = $config;
41    }
42
43    /**
44     * Generates a huge array for the report.
45     *
46     * @return array
47     */
48    public function getReportingData(): array
49    {
50        $report = [];
51
52        $query = sprintf(
53            '
54            SELECT
55                fd.id AS id,
56                fd.lang AS lang,
57                fcr.category_id AS category_id,
58                c.name as category_name,
59                c.parent_id as parent_id,
60                fd.sticky AS sticky,
61                fd.thema AS question,
62                fd.author AS original_author,
63                fd.updated AS updated,
64                fv.visits AS visits,
65                u.display_name AS last_author
66            FROM
67                %sfaqdata fd
68            LEFT JOIN
69                %sfaqcategoryrelations fcr
70            ON
71                (fd.id = fcr.record_id AND fd.lang = fcr.record_lang)
72            LEFT JOIN
73                %sfaqvisits fv
74            ON
75                (fd.id = fv.id AND fd.lang = fv.lang)
76            LEFT JOIN
77                %sfaqchanges as fc
78            ON
79                (fd.id = fc.id AND fd.lang = fc.lang)
80            LEFT JOIN
81                %sfaquserdata as u
82            ON
83                (u.user_id = fc.usr)
84            LEFT JOIN
85                %sfaqcategories as c
86            ON
87                (c.id = fcr.category_id AND c.lang = fcr.record_lang)
88            ORDER BY
89                fd.id
90            ASC',
91            Database::getTablePrefix(),
92            Database::getTablePrefix(),
93            Database::getTablePrefix(),
94            Database::getTablePrefix(),
95            Database::getTablePrefix(),
96            Database::getTablePrefix()
97        );
98
99        $result = $this->config->getDb()->query($query);
100
101        $lastId = 0;
102        while ($row = $this->config->getDb()->fetchObject($result)) {
103            if ($row->id == $lastId) {
104                $report[$row->id]['faq_translations'] += 1;
105            } else {
106                $report[$row->id] = [
107                    'faq_id' => $row->id,
108                    'faq_language' => $row->lang,
109                    'category_id' => $row->category_id,
110                    'category_parent' => $row->parent_id,
111                    'category_name' => $row->category_name,
112                    'faq_translations' => 0,
113                    'faq_sticky' => $row->sticky,
114                    'faq_question' => $row->question,
115                    'faq_org_author' => $row->original_author,
116                    'faq_updated' => Date::createIsoDate($row->updated),
117                    'faq_visits' => $row->visits,
118                    'faq_last_author' => $row->last_author,
119                ];
120            }
121            $lastId = $row->id;
122        }
123
124        return $report;
125    }
126
127    /**
128     * Convert string to the correct encoding and removes possible
129     * bad strings to avoid formula injection attacks.
130     *
131     * @param  string $outputString String to encode.
132     * @return string Encoded string.
133     */
134    public function convertEncoding(string $outputString = ''): string
135    {
136        $outputString = html_entity_decode($outputString, ENT_QUOTES, 'utf-8');
137        $outputString = str_replace(',', ' ', $outputString);
138
139        if (extension_loaded('mbstring')) {
140            $detected = mb_detect_encoding($outputString);
141
142            if ($detected !== 'ASCII') {
143                $outputString = mb_convert_encoding($outputString, 'UTF-16', $detected);
144            }
145        }
146
147        $toBeRemoved = ['=', '+', '-', 'HYPERLINK'];
148        $outputString = str_replace($toBeRemoved, '', $outputString);
149
150        return $outputString;
151    }
152}
153