1 | <?php |
||||||
2 | |||||||
3 | namespace Backend\Modules\FormBuilder\Actions; |
||||||
4 | |||||||
5 | use Backend\Core\Engine\Authentication; |
||||||
6 | use Backend\Core\Engine\Base\Action as BackendBaseAction; |
||||||
7 | use Backend\Core\Language\Language as BL; |
||||||
8 | use Backend\Core\Engine\Model as BackendModel; |
||||||
9 | use Backend\Modules\FormBuilder\Engine\Model as BackendFormBuilderModel; |
||||||
10 | use Common\Exception\RedirectException; |
||||||
11 | use ForkCMS\Utility\Csv\Writer; |
||||||
12 | use PhpOffice\PhpSpreadsheet\Spreadsheet; |
||||||
13 | |||||||
14 | /** |
||||||
15 | * This action is used to export submissions of a form. |
||||||
16 | */ |
||||||
17 | class ExportData extends BackendBaseAction |
||||||
18 | { |
||||||
19 | /** |
||||||
20 | * CSV column headers. |
||||||
21 | * |
||||||
22 | * @var array |
||||||
23 | */ |
||||||
24 | private $columnHeaders = []; |
||||||
25 | |||||||
26 | /** |
||||||
27 | * The filter. |
||||||
28 | * |
||||||
29 | * @var array |
||||||
30 | */ |
||||||
31 | private $filter; |
||||||
32 | |||||||
33 | /** |
||||||
34 | * Form id. |
||||||
35 | * |
||||||
36 | * @var int |
||||||
37 | */ |
||||||
38 | private $id; |
||||||
39 | |||||||
40 | /** |
||||||
41 | * CSV rows. |
||||||
42 | * |
||||||
43 | * @var array |
||||||
44 | */ |
||||||
45 | private $rows = []; |
||||||
46 | |||||||
47 | /** |
||||||
48 | * Builds the query for this datagrid. |
||||||
49 | * |
||||||
50 | * @return array An array with two arguments containing the query and its parameters. |
||||||
51 | */ |
||||||
52 | private function buildQuery(): array |
||||||
53 | { |
||||||
54 | // init var |
||||||
55 | $parameters = [$this->id]; |
||||||
56 | |||||||
57 | /* |
||||||
58 | * Start query, as you can see this query is build in the wrong place, because of the filter |
||||||
59 | * it is a special case wherein we allow the query to be in the actionfile itself |
||||||
60 | */ |
||||||
61 | $query = |
||||||
62 | 'SELECT i.*, UNIX_TIMESTAMP(i.sent_on) AS sent_on, d.* |
||||||
63 | FROM forms_data AS i |
||||||
64 | INNER JOIN forms_data_fields AS d ON i.id = d.data_id |
||||||
65 | WHERE i.form_id = ?'; |
||||||
66 | |||||||
67 | // add start date |
||||||
68 | if ($this->filter['start_date'] !== '') { |
||||||
69 | // explode date parts |
||||||
70 | $chunks = explode('/', $this->filter['start_date']); |
||||||
71 | |||||||
72 | // add condition |
||||||
73 | $query .= ' AND i.sent_on >= ?'; |
||||||
74 | $parameters[] = BackendModel::getUTCDate(null, gmmktime(23, 59, 59, $chunks[1], $chunks[0], $chunks[2])); |
||||||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
$chunks[1] of type string is incompatible with the type integer expected by parameter $month of gmmktime() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
$chunks[2] of type string is incompatible with the type integer expected by parameter $year of gmmktime() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||||
75 | } |
||||||
76 | |||||||
77 | // add end date |
||||||
78 | if ($this->filter['end_date'] !== '') { |
||||||
79 | // explode date parts |
||||||
80 | $chunks = explode('/', $this->filter['end_date']); |
||||||
81 | |||||||
82 | // add condition |
||||||
83 | $query .= ' AND i.sent_on <= ?'; |
||||||
84 | $parameters[] = BackendModel::getUTCDate(null, gmmktime(23, 59, 59, $chunks[1], $chunks[0], $chunks[2])); |
||||||
85 | } |
||||||
86 | |||||||
87 | return [$query, $parameters]; |
||||||
88 | } |
||||||
89 | |||||||
90 | public function execute(): void |
||||||
91 | { |
||||||
92 | $this->checkToken(); |
||||||
93 | $this->id = $this->getRequest()->query->getInt('id'); |
||||||
94 | |||||||
95 | // does the item exist |
||||||
96 | if ($this->id !== 0 && BackendFormBuilderModel::exists($this->id)) { |
||||||
97 | parent::execute(); |
||||||
98 | $this->setFilter(); |
||||||
99 | $this->setItems(); |
||||||
100 | |||||||
101 | $spreadSheet = new Spreadsheet(); |
||||||
102 | $sheet = $spreadSheet->getActiveSheet(); |
||||||
103 | $sheet->fromArray($this->columnHeaders, null, 'A1'); |
||||||
104 | $sheet->fromArray($this->rows, null, 'A2'); |
||||||
105 | |||||||
106 | throw new RedirectException( |
||||||
107 | 'Return the csv data', |
||||||
108 | $this->get(Writer::class) |
||||||
109 | ->getResponseForUser( |
||||||
110 | $spreadSheet, |
||||||
111 | date('Ymd_His') . '.csv', |
||||||
112 | Authentication::getUser() |
||||||
113 | ) |
||||||
114 | ); |
||||||
115 | } else { |
||||||
116 | // no item found, redirect to index, because somebody is fucking with our url |
||||||
117 | $this->redirect(BackendModel::createUrlForAction('Index') . '&error=non-existing'); |
||||||
118 | } |
||||||
119 | } |
||||||
120 | |||||||
121 | /** |
||||||
122 | * Sets the filter based on the $_GET array. |
||||||
123 | */ |
||||||
124 | private function setFilter(): void |
||||||
125 | { |
||||||
126 | // start date is set |
||||||
127 | if ($this->getRequest()->query->has('start_date') && $this->getRequest()->query->get('start_date', '') !== '') { |
||||||
128 | // redefine |
||||||
129 | $startDate = $this->getRequest()->query->get('start_date', ''); |
||||||
130 | |||||||
131 | // explode date parts |
||||||
132 | $chunks = explode('/', $startDate); |
||||||
133 | |||||||
134 | // valid date |
||||||
135 | if (count($chunks) == 3 && checkdate((int) $chunks[1], (int) $chunks[0], (int) $chunks[2])) { |
||||||
136 | $this->filter['start_date'] = $startDate; |
||||||
137 | } else { |
||||||
138 | // invalid date |
||||||
139 | $this->filter['start_date'] = ''; |
||||||
140 | } |
||||||
141 | } else { |
||||||
142 | // not set |
||||||
143 | $this->filter['start_date'] = ''; |
||||||
144 | } |
||||||
145 | |||||||
146 | // end date is set |
||||||
147 | if ($this->getRequest()->query->has('end_date') && $this->getRequest()->query->get('end_date', '') !== '') { |
||||||
148 | // redefine |
||||||
149 | $endDate = $this->getRequest()->query->get('end_date', ''); |
||||||
150 | |||||||
151 | // explode date parts |
||||||
152 | $chunks = explode('/', $endDate); |
||||||
153 | |||||||
154 | // valid date |
||||||
155 | if (count($chunks) == 3 && checkdate((int) $chunks[1], (int) $chunks[0], (int) $chunks[2])) { |
||||||
156 | $this->filter['end_date'] = $endDate; |
||||||
157 | } else { |
||||||
158 | // invalid date |
||||||
159 | $this->filter['end_date'] = ''; |
||||||
160 | } |
||||||
161 | } else { |
||||||
162 | // not set |
||||||
163 | $this->filter['end_date'] = ''; |
||||||
164 | } |
||||||
165 | } |
||||||
166 | |||||||
167 | /** |
||||||
168 | * Fetch data for this form from the database and reformat to csv rows. |
||||||
169 | */ |
||||||
170 | private function setItems(): void |
||||||
171 | { |
||||||
172 | // init header labels |
||||||
173 | $lblSessionId = \SpoonFilter::ucfirst(BL::lbl('SessionId')); |
||||||
174 | $lblSentOn = \SpoonFilter::ucfirst(BL::lbl('SentOn')); |
||||||
175 | $this->columnHeaders = [$lblSessionId, $lblSentOn]; |
||||||
176 | |||||||
177 | // fetch query and parameters |
||||||
178 | list($query, $parameters) = $this->buildQuery(); |
||||||
179 | |||||||
180 | // get the data |
||||||
181 | $records = (array) $this->get('database')->getRecords($query, $parameters); |
||||||
182 | $data = []; |
||||||
183 | |||||||
184 | // reformat data |
||||||
185 | foreach ($records as $row) { |
||||||
186 | // first row of a submission |
||||||
187 | if (!isset($data[$row['data_id']])) { |
||||||
188 | $data[$row['data_id']][$lblSessionId] = $row['session_id']; |
||||||
189 | $data[$row['data_id']][$lblSentOn] = \SpoonDate::getDate( |
||||||
190 | 'Y-m-d H:i:s', |
||||||
191 | $row['sent_on'], |
||||||
192 | BL::getWorkingLanguage() |
||||||
193 | ); |
||||||
194 | } |
||||||
195 | |||||||
196 | // value is serialized |
||||||
197 | $value = unserialize($row['value'], ['allowed_classes' => false]); |
||||||
198 | |||||||
199 | // flatten arrays |
||||||
200 | if (is_array($value)) { |
||||||
201 | $value = implode(', ', $value); |
||||||
202 | } |
||||||
203 | |||||||
204 | // group submissions |
||||||
205 | $data[$row['data_id']][$row['label']] = \SpoonFilter::htmlentitiesDecode($value, null, ENT_QUOTES); |
||||||
206 | |||||||
207 | // add into headers if not yet added |
||||||
208 | if (!in_array($row['label'], $this->columnHeaders)) { |
||||||
209 | $this->columnHeaders[] = $row['label']; |
||||||
210 | } |
||||||
211 | } |
||||||
212 | |||||||
213 | // reorder data so they are in the correct column |
||||||
214 | foreach ($data as $id => $row) { |
||||||
215 | foreach ($this->columnHeaders as $header) { |
||||||
216 | // submission has this field so add it |
||||||
217 | if (isset($row[$header])) { |
||||||
218 | $this->rows[$id][] = $row[$header]; |
||||||
219 | } else { |
||||||
220 | // submission does not have this field so add a placeholder |
||||||
221 | $this->rows[$id][] = ''; |
||||||
222 | } |
||||||
223 | } |
||||||
224 | } |
||||||
225 | |||||||
226 | // remove the keys |
||||||
227 | $this->rows = array_values($this->rows); |
||||||
228 | } |
||||||
229 | } |
||||||
230 |