20
19
* along with Xibo. If not, see <http://www.gnu.org/licenses/>.
22
21
namespace Xibo\Controller;
24
use Xibo\Exception\AccessDeniedException;
25
use Xibo\Exception\InvalidArgumentException;
26
use Xibo\Exception\NotFoundException;
27
use Xibo\Exception\XiboException;
28
use Xibo\Factory\DataSetColumnFactory;
29
use Xibo\Factory\DataSetFactory;
30
use Xibo\Helper\DataSetUploadHandler;
31
use Xibo\Service\ConfigServiceInterface;
32
use Xibo\Service\DateServiceInterface;
33
use Xibo\Service\LogServiceInterface;
34
use Xibo\Service\SanitizerServiceInterface;
38
* @package Xibo\Controller
26
use DataSetGroupSecurity;
28
use Xibo\Helper\ApplicationState;
31
use Xibo\Helper\Theme;
33
defined('XIBO') or die('Sorry, you are not allowed to directly access this page.<br /> Please press the back button in your browser.');
40
36
class DataSet extends Base
42
/** @var DataSetFactory */
43
private $dataSetFactory;
45
/** @var DataSetColumnFactory */
46
private $dataSetColumnFactory;
49
* Set common dependencies.
50
* @param LogServiceInterface $log
51
* @param SanitizerServiceInterface $sanitizerService
52
* @param \Xibo\Helper\ApplicationState $state
53
* @param \Xibo\Entity\User $user
54
* @param \Xibo\Service\HelpServiceInterface $help
55
* @param DateServiceInterface $date
56
* @param ConfigServiceInterface $config
57
* @param DataSetFactory $dataSetFactory
58
* @param DataSetColumnFactory $dataSetColumnFactory
60
public function __construct($log, $sanitizerService, $state, $user, $help, $date, $config, $dataSetFactory, $dataSetColumnFactory)
62
$this->setCommonDependencies($log, $sanitizerService, $state, $user, $help, $date, $config);
64
$this->dataSetFactory = $dataSetFactory;
65
$this->dataSetColumnFactory = $dataSetColumnFactory;
69
* @return SanitizerServiceInterface
71
public function getSanitizer()
73
return parent::getSanitizer();
77
* @return DataSetFactory
79
public function getDataSetFactory()
81
return $this->dataSetFactory;
87
38
public function displayPage()
89
$this->getState()->template = 'dataset-page';
94
* @throws \Xibo\Exception\NotFoundException
98
* operationId="dataSetSearch",
100
* summary="DataSet Search",
101
* description="Search this users DataSets",
105
* description="Filter by DataSet Id",
112
* description="Filter by DataSet Name",
119
* description="Filter by DataSet Code",
126
* description="Embed related data such as columns",
132
* description="successful operation",
135
* @SWG\Items(ref="#/definitions/DataSet")
140
public function grid()
40
$subpage = \Kit::GetParam('sp', _GET, _WORD, '');
42
// Configure the theme
45
// Different pages for data entry and admin
46
if ($subpage == 'DataEntry') {
47
Theme::Set('id', 'DataEntryGrid');
48
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
49
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
51
Theme::Set('form_meta', '<input type="hidden" name="p" value="dataset"><input type="hidden" name="q" value="DataSetDataForm"><input type="hidden" name="datasetid" value="' . $dataSetId . '"><input type="hidden" name="dataset" value="' . $dataSet . '">');
53
// Call to render the template
54
Theme::Set('header_text', $dataSet);
55
Theme::Set('form_fields', array());
56
$this->getState()->html .= Theme::RenderReturn('grid_render');
59
Theme::Set('id', $id);
60
Theme::Set('form_meta', '<input type="hidden" name="p" value="dataset"><input type="hidden" name="q" value="DataSetGrid">');
61
Theme::Set('pager', ApplicationState::Pager($id));
63
// Call to render the template
64
Theme::Set('header_text', __('DataSets'));
65
Theme::Set('form_fields', array());
66
$this->getState()->html .= Theme::RenderReturn('grid_render');
73
if (\Kit::GetParam('sp', _GET, _WORD, 'view') == 'view') {
75
array('title' => __('Add DataSet'),
76
'class' => 'XiboFormButton',
78
'link' => 'index.php?p=dataset&q=AddDataSetForm',
79
'help' => __('Add a new DataSet'),
83
} else if (\Kit::GetParam('sp', _GET, _WORD, 'view') == 'DataEntry') {
85
array('title' => __('More Rows'),
89
'help' => __('Add more rows to the end of this DataSet'),
90
'onclick' => 'XiboGridRender(\'DataEntryGrid\')'
97
public function DataSetGrid()
142
100
$user = $this->getUser();
145
$embed = ($this->getSanitizer()->getString('embed') != null) ? explode(',', $this->getSanitizer()->getString('embed')) : [];
148
'dataSetId' => $this->getSanitizer()->getInt('dataSetId'),
149
'dataSet' => $this->getSanitizer()->getString('dataSet'),
150
'code' => $this->getSanitizer()->getString('code'),
153
$dataSets = $this->dataSetFactory->query($this->gridRenderSort(), $this->gridRenderFilter($filter));
155
foreach ($dataSets as $dataSet) {
156
/* @var \Xibo\Entity\DataSet $dataSet */
157
if (in_array('columns', $embed)) {
163
$dataSet->includeProperty('buttons');
164
$dataSet->buttons = [];
166
// Load the dataSet to get the columns
169
if ($user->checkEditable($dataSet)) {
101
$response = $this->getState();
104
array('name' => 'dataset', 'title' => __('Name')),
105
array('name' => 'description', 'title' => __('Description')),
106
array('name' => 'owner', 'title' => __('Owner')),
107
array('name' => 'groups', 'title' => __('Permissions'))
109
Theme::Set('table_cols', $cols);
113
foreach ($this->user->DataSetList() as $dataSet) {
114
// Add some additional info
115
$dataSet['owner'] = $user->getNameFromID($dataSet['ownerid']);
116
$dataSet['groups'] = $this->GroupsForDataSet($dataSet['datasetid']);
117
$dataSet['buttons'] = array();
119
if ($dataSet['edit']) {
172
$dataSet->buttons[] = array(
122
$dataSet['buttons'][] = array(
173
123
'id' => 'dataset_button_viewdata',
174
124
'class' => 'XiboRedirectButton',
175
'url' => $this->urlFor('dataSet.view.data', ['id' => $dataSet->dataSetId]),
125
'url' => 'index.php?p=dataset&sp=DataEntry&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
176
126
'text' => __('View Data')
180
$dataSet->buttons[] = array(
130
$dataSet['buttons'][] = array(
181
131
'id' => 'dataset_button_viewcolumns',
182
'url' => $this->urlFor('dataSet.column.view', ['id' => $dataSet->dataSetId]),
183
'class' => 'XiboRedirectButton',
132
'url' => 'index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
184
133
'text' => __('View Columns')
188
$dataSet->buttons[] = ['divider' => true];
191
if ($dataSet->isRemote !== 1) {
192
$dataSet->buttons[] = array(
193
'id' => 'dataset_button_import',
194
'class' => 'dataSetImportForm',
195
'url' => $this->urlFor('dataSet.import.form', ['id' => $dataSet->dataSetId]),
196
'text' => __('Import CSV')
201
$dataSet->buttons[] = array(
202
'id' => 'dataset_button_copy',
203
'url' => $this->urlFor('dataSet.copy.form', ['id' => $dataSet->dataSetId]),
208
$dataSet->buttons[] = ['divider' => true];
211
$dataSet->buttons[] = array(
137
$dataSet['buttons'][] = array(
212
138
'id' => 'dataset_button_edit',
213
'url' => $this->urlFor('dataSet.edit.form', ['id' => $dataSet->dataSetId]),
139
'url' => 'index.php?p=dataset&q=EditDataSetForm&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
214
140
'text' => __('Edit')
144
$dataSet['buttons'][] = array(
145
'id' => 'dataset_button_import',
146
'url' => 'index.php?p=dataset&q=ImportCsvForm&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
147
'text' => __('Import CSV')
218
if ($user->checkDeleteable($dataSet) && $dataSet->isLookup == 0) {
151
if ($dataSet['del']) {
219
153
// Delete DataSet
220
$dataSet->buttons[] = array(
154
$dataSet['buttons'][] = array(
221
155
'id' => 'dataset_button_delete',
222
'url' => $this->urlFor('dataSet.delete.form', ['id' => $dataSet->dataSetId]),
156
'url' => 'index.php?p=dataset&q=DeleteDataSetForm&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
223
157
'text' => __('Delete')
228
$dataSet->buttons[] = ['divider' => true];
161
if ($dataSet['modifyPermissions']) {
230
if ($user->checkPermissionsModifyable($dataSet)) {
231
163
// Edit Permissions
232
$dataSet->buttons[] = array(
233
'id' => 'dataset_button_permissions',
234
'url' => $this->urlFor('user.permissions.form', ['entity' => 'DataSet', 'id' => $dataSet->dataSetId]),
164
$dataSet['buttons'][] = array(
165
'id' => 'dataset_button_delete',
166
'url' => 'index.php?p=dataset&q=PermissionsForm&datasetid=' . $dataSet['datasetid'] . '&dataset=' . $dataSet['dataset'],
235
167
'text' => __('Permissions')
240
$this->getState()->template = 'grid';
241
$this->getState()->recordsTotal = $this->dataSetFactory->countLast();
242
$this->getState()->setData($dataSets);
248
public function addForm()
250
$this->getState()->template = 'dataset-form-add';
251
$this->getState()->setData([
252
'dataSets' => $this->dataSetFactory->query(),
253
'help' => $this->getHelp()->link('DataSet', 'Add')
262
* operationId="dataSetAdd",
264
* summary="Add DataSet",
265
* description="Add a DataSet",
269
* description="The DataSet Name",
274
* name="description",
276
* description="A description of this DataSet",
283
* description="A code for this DataSet",
290
* description="Is this a remote DataSet?",
297
* description="The Request Method GET or POST",
304
* description="The URI, without query parameters",
311
* description="query parameter encoded data to add to the request",
316
* name="authentication",
318
* description="HTTP Authentication method None|Basic|Digest",
325
* description="HTTP Authentication User Name",
332
* description="HTTP Authentication Password",
337
* name="refreshRate",
339
* description="How often in seconds should this remote DataSet be refreshed",
346
* description="How often in seconds should this remote DataSet be truncated",
353
* description="An optional dataSetId which should be run before this Remote DataSet",
360
* description="The root of the data in the Remote source which is used as the base for all remote columns",
367
* description="Should the data be aggregated? None|Summarize|Count",
372
* name="summarizeField",
374
* description="Which field should be used to summarize",
380
* description="successful operation",
381
* @SWG\Schema(ref="#/definitions/DataSet"),
384
* description="Location of the new record",
390
* @throws XiboException
392
public function add()
394
$dataSet = $this->dataSetFactory->createEmpty();
395
$dataSet->dataSet = $this->getSanitizer()->getString('dataSet');
396
$dataSet->description = $this->getSanitizer()->getString('description');
397
$dataSet->code = $this->getSanitizer()->getString('code');
398
$dataSet->isRemote = $this->getSanitizer()->getCheckbox('isRemote');
399
$dataSet->userId = $this->getUser()->userId;
402
if ($dataSet->isRemote === 1) {
403
$dataSet->method = $this->getSanitizer()->getString('method');
404
$dataSet->uri = $this->getSanitizer()->getString('uri');
405
$dataSet->postData = trim($this->getSanitizer()->getString('postData'));
406
$dataSet->authentication = $this->getSanitizer()->getString('authentication');
407
$dataSet->username = $this->getSanitizer()->getString('username');
408
$dataSet->password = $this->getSanitizer()->getString('password');
409
$dataSet->refreshRate = $this->getSanitizer()->getInt('refreshRate');
410
$dataSet->clearRate = $this->getSanitizer()->getInt('clearRate');
411
$dataSet->runsAfter = $this->getSanitizer()->getInt('runsAfter');
412
$dataSet->dataRoot = $this->getSanitizer()->getString('dataRoot');
413
$dataSet->summarize = $this->getSanitizer()->getString('summarize');
414
$dataSet->summarizeField = $this->getSanitizer()->getString('summarizeField');
174
Theme::Set('table_rows', $rows);
176
$output = Theme::RenderReturn('table_render');
178
$response->SetGridResponse($output);
182
public function AddDataSetForm()
185
$user = $this->getUser();
186
$response = $this->getState();
188
// Set some information about the form
189
Theme::Set('form_id', 'AddDataSetForm');
190
Theme::Set('form_action', 'index.php?p=dataset&q=AddDataSet');
192
$formFields = array();
193
$formFields[] = FormManager::AddText('dataset', __('Name'), NULL,
194
__('A name for this DataSet'), 'n', 'required');
196
$formFields[] = FormManager::AddText('description', __('Description'), NULL,
197
__('An optional description'), 'd', 'maxlength="250"');
199
Theme::Set('form_fields', $formFields);
201
$response->SetFormRequestResponse(NULL, __('Add DataSet'), '350px', '275px');
202
$response->AddButton(__('Help'), 'XiboHelpRender("' . Help::Link('DataSet', 'Add') . '")');
203
$response->AddButton(__('Cancel'), 'XiboDialogClose()');
204
$response->AddButton(__('Add'), '$("#AddDataSetForm").submit()');
211
public function AddDataSet()
216
$user = $this->getUser();
217
$response = $this->getState();
219
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
220
$description = \Xibo\Helper\Sanitize::getString('description');
222
$dataSetObject = new DataSet($db);
223
if (!$dataSetId = $dataSetObject->Add($dataSet, $description, $this->user->userId))
224
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
417
226
// Also add one column
418
$dataSetColumn = $this->dataSetColumnFactory->createEmpty();
419
$dataSetColumn->columnOrder = 1;
420
$dataSetColumn->heading = 'Col1';
421
$dataSetColumn->dataSetColumnTypeId = 1;
422
$dataSetColumn->dataTypeId = 1;
425
// only when we are not routing through the API
427
$dataSet->assignColumn($dataSetColumn);
433
$this->getState()->hydrate([
435
'message' => sprintf(__('Added %s'), $dataSet->dataSet),
436
'id' => $dataSet->dataSetId,
443
* @param int $dataSetId
444
* @throws \Xibo\Exception\NotFoundException
446
public function editForm($dataSetId)
448
$dataSet = $this->dataSetFactory->getById($dataSetId);
450
if (!$this->getUser()->checkEditable($dataSet))
451
throw new AccessDeniedException();
454
$this->getState()->template = 'dataset-form-edit';
455
$this->getState()->setData([
456
'dataSet' => $dataSet,
457
'dataSets' => $this->dataSetFactory->query(),
458
'help' => $this->getHelp()->link('DataSet', 'Edit')
464
* @param int $dataSetId
467
* path="/dataset/{dataSetId}",
468
* operationId="dataSetEdit",
470
* summary="Edit DataSet",
471
* description="Edit a DataSet",
475
* description="The DataSet ID",
482
* description="The DataSet Name",
487
* name="description",
489
* description="A description of this DataSet",
496
* description="A code for this DataSet",
503
* description="Is this a remote DataSet?",
510
* description="The Request Method GET or POST",
517
* description="The URI, without query parameters",
524
* description="query parameter encoded data to add to the request",
529
* name="authentication",
531
* description="HTTP Authentication method None|Basic|Digest",
538
* description="HTTP Authentication User Name",
545
* description="HTTP Authentication Password",
550
* name="refreshRate",
552
* description="How often in seconds should this remote DataSet be refreshed",
559
* description="How often in seconds should this remote DataSet be truncated",
566
* description="An optional dataSetId which should be run before this Remote DataSet",
573
* description="The root of the data in the Remote source which is used as the base for all remote columns",
580
* description="Should the data be aggregated? None|Summarize|Count",
585
* name="summarizeField",
587
* description="Which field should be used to summarize",
593
* description="successful operation",
594
* @SWG\Schema(ref="#/definitions/DataSet")
598
* @throws XiboException
600
public function edit($dataSetId)
602
$dataSet = $this->dataSetFactory->getById($dataSetId);
604
if (!$this->getUser()->checkEditable($dataSet))
605
throw new AccessDeniedException();
607
$dataSet->dataSet = $this->getSanitizer()->getString('dataSet');
608
$dataSet->description = $this->getSanitizer()->getString('description');
609
$dataSet->code = $this->getSanitizer()->getString('code');
610
$dataSet->isRemote = $this->getSanitizer()->getCheckbox('isRemote');
612
if ($dataSet->isRemote === 1) {
613
$dataSet->method = $this->getSanitizer()->getString('method');
614
$dataSet->uri = $this->getSanitizer()->getString('uri');
615
$dataSet->postData = trim($this->getSanitizer()->getString('postData'));
616
$dataSet->authentication = $this->getSanitizer()->getString('authentication');
617
$dataSet->username = $this->getSanitizer()->getString('username');
618
$dataSet->password = $this->getSanitizer()->getString('password');
619
$dataSet->refreshRate = $this->getSanitizer()->getInt('refreshRate');
620
$dataSet->clearRate = $this->getSanitizer()->getInt('clearRate');
621
$dataSet->runsAfter = $this->getSanitizer()->getInt('runsAfter');
622
$dataSet->dataRoot = $this->getSanitizer()->getString('dataRoot');
623
$dataSet->summarize = $this->getSanitizer()->getString('summarize');
624
$dataSet->summarizeField = $this->getSanitizer()->getString('summarizeField');
630
$this->getState()->hydrate([
631
'message' => sprintf(__('Edited %s'), $dataSet->dataSet),
632
'id' => $dataSet->dataSetId,
639
* @param int $dataSetId
640
* @throws XiboException
642
public function deleteForm($dataSetId)
644
$dataSet = $this->dataSetFactory->getById($dataSetId);
646
if (!$this->getUser()->checkDeleteable($dataSet))
647
throw new AccessDeniedException();
649
if ($dataSet->isLookup)
650
throw new \InvalidArgumentException(__('Lookup Tables cannot be deleted'));
653
$this->getState()->template = 'dataset-form-delete';
654
$this->getState()->setData([
655
'dataSet' => $dataSet,
656
'help' => $this->getHelp()->link('DataSet', 'Delete')
662
* @param int $dataSetId
665
* path="/dataset/{dataSetId}",
666
* operationId="dataSetDelete",
668
* summary="Delete DataSet",
669
* description="Delete a DataSet",
673
* description="The DataSet ID",
679
* description="successful operation"
683
* @throws XiboException
685
public function delete($dataSetId)
687
$dataSet = $this->dataSetFactory->getById($dataSetId);
689
if (!$this->getUser()->checkDeleteable($dataSet))
690
throw new AccessDeniedException();
692
// Is there existing data?
693
if ($this->getSanitizer()->getCheckbox('deleteData') == 0 && $dataSet->hasData())
694
throw new InvalidArgumentException(__('There is data assigned to this data set, cannot delete.'), 'dataSetId');
700
$this->getState()->hydrate([
702
'message' => sprintf(__('Deleted %s'), $dataSet->dataSet)
708
* @param int $dataSetId
709
* @throws \Xibo\Exception\NotFoundException
711
public function copyForm($dataSetId)
713
$dataSet = $this->dataSetFactory->getById($dataSetId);
715
if (!$this->getUser()->checkEditable($dataSet))
716
throw new AccessDeniedException();
719
$this->getState()->template = 'dataset-form-copy';
720
$this->getState()->setData([
721
'dataSet' => $dataSet,
722
'help' => $this->getHelp()->link('DataSet', 'Edit')
728
* @param int $dataSetId
731
* path="/dataset/copy/{dataSetId}",
732
* operationId="dataSetCopy",
734
* summary="Copy DataSet",
735
* description="Copy a DataSet",
739
* description="The DataSet ID",
746
* description="The DataSet Name",
751
* name="description",
753
* description="A description of this DataSet",
760
* description="A code for this DataSet",
766
* description="successful operation",
767
* @SWG\Schema(ref="#/definitions/DataSet")
771
* @throws XiboException
773
public function copy($dataSetId)
775
$dataSet = $this->dataSetFactory->getById($dataSetId);
777
if (!$this->getUser()->checkEditable($dataSet))
778
throw new AccessDeniedException();
782
$oldName = $dataSet->dataSet;
784
// Clone and reset parameters
785
$dataSet = clone $dataSet;
786
$dataSet->dataSet = $this->getSanitizer()->getString('dataSet');
787
$dataSet->description = $this->getSanitizer()->getString('description');
788
$dataSet->code = $this->getSanitizer()->getString('code');
792
$this->getState()->hydrate([
793
'message' => sprintf(__('Copied %s as %s'), $oldName, $dataSet->dataSet),
794
'id' => $dataSet->dataSetId,
801
* @param int $dataSetId
804
* path="/dataset/import/{dataSetId}",
805
* operationId="dataSetImport",
807
* summary="Import CSV",
808
* description="Import a CSV into a DataSet",
812
* description="The DataSet ID to import into.",
819
* description="The file",
824
* name="csvImport_{dataSetColumnId}",
826
* description="You need to provide dataSetColumnId after csvImport_, to know your dataSet columns Ids, you will need to use the GET /dataset/{dataSetId}/column call first. The value of this parameter is the index of the column in your csv file, where the first column is 1",
833
* description="flag (0,1) Set to 1 to erase all content in the dataSet and overwrite it with new content in this import",
838
* name="ignorefirstrow",
840
* description="flag (0,1), Set to 1 to Ignore first row, useful if the CSV file has headings",
846
* description="successful operation"
850
* @throws XiboException
853
public function import($dataSetId)
855
$this->getLog()->debug('Import DataSet');
857
$libraryFolder = $this->getConfig()->GetSetting('LIBRARY_LOCATION');
859
// Make sure the library exists
860
Library::ensureLibraryExists($this->getConfig()->GetSetting('LIBRARY_LOCATION'));
863
'userId' => $this->getUser()->userId,
864
'dataSetId' => $dataSetId,
865
'controller' => $this,
866
'upload_dir' => $libraryFolder . 'temp/',
867
'download_via_php' => true,
868
'script_url' => $this->urlFor('dataSet.import'),
869
'upload_url' => $this->urlFor('dataSet.import'),
870
'image_versions' => array(),
871
'accept_file_types' => '/\.csv/i'
875
// Hand off to the Upload Handler provided by jquery-file-upload
876
new DataSetUploadHandler($options);
878
} catch (\Exception $e) {
879
// We must not issue an error, the file upload return should have the error object already
880
$this->getApp()->commit = false;
883
$this->setNoOutput(true);
888
* @param int $dataSetId
892
* path="/dataset/importjson/{dataSetId}",
893
* operationId="dataSetImportJson",
895
* summary="Import JSON",
896
* description="Import JSON into a DataSet",
900
* description="The DataSet ID to import into.",
908
* description="The row data, field name vs field data format. e.g. { uniqueKeys: [col1], rows: [{col1: value1}]}",
914
* description="successful operation"
918
public function importJson($dataSetId)
920
$dataSet = $this->dataSetFactory->getById($dataSetId);
922
if (!$this->getUser()->checkEditable($dataSet))
923
throw new AccessDeniedException();
925
$body = $this->getApp()->request()->getBody();
928
throw new \InvalidArgumentException(__('Missing JSON Body'));
930
// Expect 2 parameters
931
$data = json_decode($body, true);
933
if (!isset($data['rows']) || !isset($data['uniqueKeys']))
934
throw new \InvalidArgumentException(__('Malformed JSON body, rows and uniqueKeys are required'));
936
$this->getLog()->debug('Import JSON into DataSet with ' . count($data['rows']) . ' and unique keys ' . json_encode($data['uniqueKeys']));
938
// Should we truncate?
939
if (isset($data['truncate']) && $data['truncate']) {
940
$dataSet->deleteData();
943
// Get the columns for this dataset
945
foreach ($dataSet->getColumn() as $column) {
946
/* @var \Xibo\Entity\DataSetColumn $column */
947
if ($column->dataSetColumnTypeId == 1) {
948
$columns[$column->heading] = $column->dataTypeId;
227
$dataSetColumn = new DataSetColumn($db);
228
$dataSetColumn->Add($dataSetId, 'Col1', 1, null, 1);
230
$response->SetFormSubmitResponse(__('DataSet Added'));
234
public function EditDataSetForm()
237
$user = $this->getUser();
238
$response = $this->getState();
240
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
242
$auth = $user->DataSetAuth($dataSetId, true);
244
trigger_error(__('Access Denied'));
246
// Get the information we already know
247
$SQL = sprintf("SELECT DataSet, Description FROM dataset WHERE DataSetID = %d", $dataSetId);
249
if (!$row = $db->GetSingleRow($SQL))
250
trigger_error(__('Unable to get DataSet information'));
252
// Set some information about the form
253
Theme::Set('form_id', 'EditDataSetForm');
254
Theme::Set('form_action', 'index.php?p=dataset&q=EditDataSet');
255
Theme::Set('form_meta', '<input type="hidden" name="datasetid" value="' . $dataSetId . '" />');
257
$formFields = array();
258
$formFields[] = FormManager::AddText('dataset', __('Name'), \Xibo\Helper\Sanitize::string($row['DataSet']),
259
__('A name for this DataSet'), 'n', 'required');
261
$formFields[] = FormManager::AddText('description', __('Description'), \Xibo\Helper\Sanitize::string($row['Description']),
262
__('An optional description'), 'd', 'maxlength="250"');
264
Theme::Set('form_fields', $formFields);
266
$response->SetFormRequestResponse(NULL, __('Edit DataSet'), '350px', '275px');
267
$response->AddButton(__('Help'), 'XiboHelpRender("' . Help::Link('DataSet', 'Edit') . '")');
268
$response->AddButton(__('Cancel'), 'XiboDialogClose()');
269
$response->AddButton(__('Save'), '$("#EditDataSetForm").submit()');
273
public function EditDataSet()
278
$user = $this->getUser();
279
$response = $this->getState();
281
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
283
$auth = $user->DataSetAuth($dataSetId, true);
285
trigger_error(__('Access Denied'));
287
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
288
$description = \Xibo\Helper\Sanitize::getString('description');
290
$dataSetObject = new DataSet($db);
291
if (!$dataSetObject->Edit($dataSetId, $dataSet, $description))
292
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
294
$response->SetFormSubmitResponse(__('DataSet Edited'));
299
* Return the Delete Form as HTML
302
public function DeleteDataSetForm()
305
$response = $this->getState();
307
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
309
$auth = $this->user->DataSetAuth($dataSetId, true);
311
trigger_error(__('Access Denied'));
313
// Set some information about the form
314
Theme::Set('form_id', 'DataSetDeleteForm');
315
Theme::Set('form_action', 'index.php?p=dataset&q=DeleteDataSet');
316
Theme::Set('form_meta', '<input type="hidden" name="datasetid" value="' . $dataSetId . '" />');
318
$formFields = array();
319
$formFields[] = FormManager::AddMessage(__('Are you sure you want to delete?'));
320
$formFields[] = FormManager::AddCheckbox('deleteData', __('Delete any associated data?'), NULL,
321
__('Please tick the box if you would like to delete all the Data contained in this DataSet'), 'c');
323
Theme::Set('form_fields', $formFields);
325
$response->SetFormRequestResponse(NULL, __('Delete this DataSet?'), '350px', '200px');
326
$response->AddButton(__('Help'), 'XiboHelpRender("' . Help::Link('DataSet', 'Delete') . '")');
327
$response->AddButton(__('Cancel'), 'XiboDialogClose()');
328
$response->AddButton(__('Delete'), '$("#DataSetDeleteForm").submit()');
332
public function DeleteDataSet()
337
$user = $this->getUser();
338
$response = $this->getState();
340
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
342
$auth = $user->DataSetAuth($dataSetId, true);
344
trigger_error(__('Access Denied'));
346
$dataSetObject = new DataSet($db);
348
if ($dataSetObject->hasData($dataSetId) && \Kit::GetParam('deleteData', _POST, _CHECKBOX) == 0)
349
trigger_error(__('There is data assigned to this data set, cannot delete.'), E_USER_ERROR);
351
// Proceed with the delete
352
if (!$dataSetObject->Delete($dataSetId))
353
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
355
$response->SetFormSubmitResponse(__('DataSet Deleted'));
359
public function DataSetColumnsForm()
362
$response = $this->getState();
363
$helpManager = new Help($db, $this->user);
365
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
366
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
368
$auth = $this->user->DataSetAuth($dataSetId, true);
370
trigger_error(__('Access Denied'));
373
$SQL .= "SELECT DataSetColumnID, Heading, datatype.DataType, datasetcolumntype.DataSetColumnType, ListContent, ColumnOrder ";
374
$SQL .= " FROM datasetcolumn ";
375
$SQL .= " INNER JOIN `datatype` ";
376
$SQL .= " ON datatype.DataTypeID = datasetcolumn.DataTypeID ";
377
$SQL .= " INNER JOIN `datasetcolumntype` ";
378
$SQL .= " ON datasetcolumntype.DataSetColumnTypeID = datasetcolumn.DataSetColumnTypeID ";
379
$SQL .= sprintf(" WHERE DataSetID = %d ", $dataSetId);
380
$SQL .= "ORDER BY ColumnOrder ";
383
$dataSetColumnObject = new DataSetColumn($db);
385
// Load results into an array
386
if (!$dataSetColumns = $dataSetColumnObject->GetColumns($dataSetId))
387
trigger_error($dataSetColumnObject->GetErrorMessage(), E_USER_ERROR);
391
foreach ($dataSetColumns as $row) {
393
$row['datatype'] = __($row['datatype']);
394
$row['datasetcolumntype'] = __($row['datasetcolumntype']);
397
$row['buttons'][] = array(
398
'id' => 'dataset_button_edit',
399
'url' => 'index.php?p=dataset&q=EditDataSetColumnForm&datasetid=' . $dataSetId . '&datasetcolumnid=' . $row['datasetcolumnid'] . '&dataset=' . $dataSet,
405
$row['buttons'][] = array(
406
'id' => 'dataset_button_delete',
407
'url' => 'index.php?p=dataset&q=DeleteDataSetColumnForm&datasetid=' . $dataSetId . '&datasetcolumnid=' . $row['datasetcolumnid'] . '&dataset=' . $dataSet,
408
'text' => __('Delete')
952
$takenSomeAction = false;
954
// Parse and validate each data row we've been provided
955
foreach ($data['rows'] as $row) {
956
// Parse each property
957
$sanitizedRow = null;
958
foreach ($row as $key => $value) {
959
// Does the property in the provided row exist as a column?
960
if (isset($columns[$key])) {
961
// Sanitize accordingly
962
if ($columns[$key] == 2) {
964
$value = $this->getSanitizer()->double($value);
966
else if ($columns[$key] == 3) {
968
$value = $this->getDate()->getLocalDate($this->getDate()->parse($value));
970
else if ($columns[$key] == 5) {
972
$value = $this->getSanitizer()->int($value);
976
$value = $this->getSanitizer()->string($value);
979
// Data is sanitized, add to the sanitized row
980
$sanitizedRow[$key] = $value;
415
Theme::Set('table_rows', $rows);
417
$form = Theme::RenderReturn('dataset_form_column_grid');
419
$response->SetFormRequestResponse($form, sprintf(__('Columns for %s'), $dataSet), '550px', '400px');
420
$response->AddButton(__('Help'), 'XiboHelpRender("' . $helpManager->Link('DataSet', 'ViewColumns') . '")');
421
$response->AddButton(__('Close'), 'XiboDialogClose()');
422
$response->AddButton(__('Add Column'), 'XiboFormRender("index.php?p=dataset&q=AddDataSetColumnForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet . '")');
426
public function AddDataSetColumnForm()
429
$response = $this->getState();
430
$helpManager = new Help($db, $this->user);
432
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
433
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
435
$auth = $this->user->DataSetAuth($dataSetId, true);
437
trigger_error(__('Access Denied'));
439
// Set some information about the form
440
Theme::Set('form_id', 'DataSetColumnAddForm');
441
Theme::Set('form_action', 'index.php?p=dataset&q=AddDataSetColumn');
442
Theme::Set('form_meta', '<input type="hidden" name="dataset" value="' . $dataSet . '" /><input type="hidden" name="datasetid" value="' . $dataSetId . '" />');
444
$formFields = array();
445
$formFields[] = FormManager::AddText('heading', __('Heading'), NULL, __('The heading for this Column'), 'h', 'required');
446
$formFields[] = FormManager::AddCombo(
447
'datasetcolumntypeid',
450
$db->GetArray('SELECT datasetcolumntypeid, datasetcolumntype FROM datasetcolumntype'),
451
'datasetcolumntypeid',
453
__('Whether this column is a value or a formula'),
455
$formFields[] = FormManager::AddCombo(
459
$db->GetArray('SELECT datatypeid, datatype FROM datatype'),
462
__('The DataType of the Intended Data'),
464
$formFields[] = FormManager::AddText('listcontent', __('List Content'), NULL, __('A comma separated list of items to present in a combo box'), 'l', '');
465
$formFields[] = FormManager::AddNumber('columnorder', __('Column Order'), NULL, __('The order this column should be displayed in when entering data'), 'o', '');
466
$formFields[] = FormManager::AddText('formula', __('Formula'), NULL, __('A formula to use as a calculation for formula column types'), 'f', '');
468
Theme::Set('form_fields', $formFields);
470
$response->SetFormRequestResponse(NULL, __('Add Column'), '350px', '200px');
471
$response->AddButton(__('Help'), 'XiboHelpRender("' . $helpManager->Link('DataSet', 'AddColumn') . '")');
472
$response->AddButton(__('Cancel'), 'XiboFormRender("index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet . '")');
473
$response->AddButton(__('Save'), '$("#DataSetColumnAddForm").submit()');
477
public function AddDataSetColumn()
482
$user = $this->getUser();
483
$response = $this->getState();
485
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
486
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
488
$auth = $user->DataSetAuth($dataSetId, true);
490
trigger_error(__('Access Denied'));
492
$heading = \Xibo\Helper\Sanitize::getString('heading');
493
$listContent = \Xibo\Helper\Sanitize::getString('listcontent');
494
$columnOrder = \Xibo\Helper\Sanitize::getInt('columnorder');
495
$dataTypeId = \Xibo\Helper\Sanitize::getInt('datatypeid');
496
$dataSetColumnTypeId = \Xibo\Helper\Sanitize::getInt('datasetcolumntypeid');
497
$formula = \Xibo\Helper\Sanitize::getString('formula');
499
$dataSetObject = new DataSetColumn($db);
500
if (!$dataSetObject->Add($dataSetId, $heading, $dataTypeId, $listContent, $columnOrder, $dataSetColumnTypeId, $formula))
501
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
503
$response->SetFormSubmitResponse(__('Column Added'));
504
$response->hideMessage = true;
505
$response->loadForm = true;
506
$response->loadFormUri = 'index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet;
510
public function EditDataSetColumnForm()
513
$response = $this->getState();
514
$helpManager = new Help($db, $this->user);
516
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
517
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
518
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
520
$auth = $this->user->DataSetAuth($dataSetId, true);
522
trigger_error(__('Access Denied'));
524
// Set some information about the form
525
Theme::Set('form_id', 'DataSetColumnEditForm');
526
Theme::Set('form_action', 'index.php?p=dataset&q=EditDataSetColumn');
527
Theme::Set('form_meta', '<input type="hidden" name="dataset" value="' . $dataSet . '" /><input type="hidden" name="datasetid" value="' . $dataSetId . '" /><input type="hidden" name="datasetcolumnid" value="' . $dataSetColumnId . '" />');
529
// Get some information about this data set column
530
$SQL = sprintf("SELECT Heading, ListContent, ColumnOrder, DataTypeID, DataSetColumnTypeID, Formula FROM datasetcolumn WHERE DataSetColumnID = %d", $dataSetColumnId);
532
if (!$row = $db->GetSingleRow($SQL))
533
trigger_error(__('Unabled to get Data Column information'), E_USER_ERROR);
535
// Dropdown list for DataType and DataColumnType
536
Theme::Set('datatype_field_list', $db->GetArray('SELECT datatypeid, datatype FROM datatype'));
537
Theme::Set('datasetcolumntype_field_list', $db->GetArray('SELECT datasetcolumntypeid, datasetcolumntype FROM datasetcolumntype'));
539
$formFields = array();
540
$formFields[] = FormManager::AddText('heading', __('Heading'), \Xibo\Helper\Sanitize::string($row['Heading']),
541
__('The heading for this Column'), 'h', 'required');
543
$formFields[] = FormManager::AddCombo(
544
'datasetcolumntypeid',
546
\Xibo\Helper\Sanitize::int($row['DataSetColumnTypeID']),
547
$db->GetArray('SELECT datasetcolumntypeid, datasetcolumntype FROM datasetcolumntype'),
548
'datasetcolumntypeid',
550
__('Whether this column is a value or a formula'),
553
$formFields[] = FormManager::AddCombo(
556
\Xibo\Helper\Sanitize::int($row['DataTypeID']),
557
$db->GetArray('SELECT datatypeid, datatype FROM datatype'),
560
__('The DataType of the Intended Data'),
563
$formFields[] = FormManager::AddText('listcontent', __('List Content'), \Xibo\Helper\Sanitize::string($row['ListContent']),
564
__('A comma separated list of items to present in a combo box'), 'l', '');
566
$formFields[] = FormManager::AddNumber('columnorder', __('Column Order'), \Xibo\Helper\Sanitize::int($row['ColumnOrder']),
567
__('The order this column should be displayed in when entering data'), 'o', '');
569
$formFields[] = FormManager::AddText('formula', __('Formula'), \Xibo\Helper\Sanitize::string($row['Formula']),
570
__('A formula to use as a calculation for formula column types'), 'f', '');
572
Theme::Set('form_fields', $formFields);
574
$response->SetFormRequestResponse(NULL, __('Edit Column'), '350px', '200px');
575
$response->AddButton(__('Help'), 'XiboHelpRender("' . $helpManager->Link('DataSet', 'EditColumn') . '")');
576
$response->AddButton(__('Cancel'), 'XiboFormRender("index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet . '")');
577
$response->AddButton(__('Save'), '$("#DataSetColumnEditForm").submit()');
581
public function EditDataSetColumn()
586
$user = $this->getUser();
587
$response = $this->getState();
589
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
590
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
592
$auth = $user->DataSetAuth($dataSetId, true);
594
trigger_error(__('Access Denied'));
596
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
597
$heading = \Xibo\Helper\Sanitize::getString('heading');
598
$listContent = \Xibo\Helper\Sanitize::getString('listcontent');
599
$columnOrder = \Xibo\Helper\Sanitize::getInt('columnorder');
600
$dataTypeId = \Xibo\Helper\Sanitize::getInt('datatypeid');
601
$dataSetColumnTypeId = \Xibo\Helper\Sanitize::getInt('datasetcolumntypeid');
602
$formula = \Xibo\Helper\Sanitize::getString('formula');
604
$dataSetObject = new DataSetColumn($db);
605
if (!$dataSetObject->Edit($dataSetColumnId, $heading, $dataTypeId, $listContent, $columnOrder, $dataSetColumnTypeId, $formula))
606
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
608
$response->SetFormSubmitResponse(__('Column Edited'));
609
$response->hideMessage = true;
610
$response->loadForm = true;
611
$response->loadFormUri = 'index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet;
615
public function DeleteDataSetColumnForm()
618
$response = $this->getState();
619
$helpManager = new Help($db, $this->user);
621
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
622
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
624
$auth = $this->user->DataSetAuth($dataSetId, true);
626
trigger_error(__('Access Denied'));
628
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
630
// Set some information about the form
631
Theme::Set('form_id', 'DataSetColumnDeleteForm');
632
Theme::Set('form_action', 'index.php?p=dataset&q=DeleteDataSetColumn');
633
Theme::Set('form_meta', '<input type="hidden" name="dataset" value="' . $dataSet . '" /><input type="hidden" name="datasetid" value="' . $dataSetId . '" /><input type="hidden" name="datasetcolumnid" value="' . $dataSetColumnId . '" />');
635
Theme::Set('form_fields', array(FormManager::AddMessage(__('Are you sure you want to delete?'))));
637
$response->SetFormRequestResponse(NULL, __('Delete this Column?'), '350px', '200px');
638
$response->AddButton(__('Help'), 'XiboHelpRender("' . $helpManager->Link('DataSet', 'DeleteColumn') . '")');
639
$response->AddButton(__('Cancel'), 'XiboFormRender("index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet . '")');
640
$response->AddButton(__('Delete'), '$("#DataSetColumnDeleteForm").submit()');
644
public function DeleteDataSetColumn()
649
$user = $this->getUser();
650
$response = $this->getState();
652
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
653
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
655
$auth = $this->user->DataSetAuth($dataSetId, true);
657
trigger_error(__('Access Denied'));
659
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
661
$dataSetObject = new DataSetColumn($db);
662
if (!$dataSetObject->Delete($dataSetColumnId))
663
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
665
$response->SetFormSubmitResponse(__('Column Deleted'));
666
$response->hideMessage = true;
667
$response->loadForm = true;
668
$response->loadFormUri = 'index.php?p=dataset&q=DataSetColumnsForm&datasetid=' . $dataSetId . '&dataset=' . $dataSet;
672
public function DataSetDataForm()
675
$response = $this->getState();
677
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
678
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
680
$auth = $this->user->DataSetAuth($dataSetId, true);
682
trigger_error(__('Access Denied'), E_USER_ERROR);
684
// Get the max number of rows
686
$SQL .= "SELECT MAX(RowNumber) AS RowNumber, COUNT(DISTINCT datasetcolumn.DataSetColumnID) AS ColNumber ";
687
$SQL .= " FROM datasetdata ";
688
$SQL .= " RIGHT OUTER JOIN datasetcolumn ";
689
$SQL .= " ON datasetcolumn.DataSetColumnID = datasetdata.DataSetColumnID ";
690
$SQL .= sprintf("WHERE datasetcolumn.DataSetID = %d AND datasetcolumn.DataSetColumnTypeID = 1 ", $dataSetId);
692
Log::notice($SQL, 'dataset', 'DataSetDataForm');
694
if (!$maxResult = $db->GetSingleRow($SQL)) {
695
trigger_error($db->error());
696
trigger_error(__('Unable to find the number of data points'), E_USER_ERROR);
699
$maxRows = $maxResult['RowNumber'];
700
$maxCols = $maxResult['ColNumber'];
702
// Get some information about the columns in this dataset
703
$SQL = "SELECT Heading, DataSetColumnID, ListContent, ColumnOrder, DataTypeID FROM datasetcolumn WHERE DataSetID = %d AND DataSetColumnTypeID = 1 ";
704
$SQL .= "ORDER BY ColumnOrder ";
706
if (!$results = $db->query(sprintf($SQL, $dataSetId))) {
707
trigger_error($db->error());
708
trigger_error(__('Unable to find the column headings'), E_USER_ERROR);
711
$columnDefinition = array();
713
$form = '<table class="table table-bordered">';
715
$form .= ' <th>#</th>';
717
while ($row = $db->get_assoc_row($results)) {
718
$columnDefinition[] = $row;
719
$heading = $row['Heading'];
721
$form .= ' <th>' . $heading . '</th>';
726
// Loop through the max rows
727
for ($row = 1; $row <= $maxRows + 2; $row++) {
729
$form .= ' <td>' . $row . '</td>';
731
// $row is the current row
732
for ($col = 0; $col < $maxCols; $col++) {
733
$dataSetColumnId = $columnDefinition[$col]['DataSetColumnID'];
734
$listContent = $columnDefinition[$col]['ListContent'];
735
$columnOrder = $columnDefinition[$col]['ColumnOrder'];
736
$dataTypeId = $columnDefinition[$col]['DataTypeID'];
738
// Value for this Col/Row
741
if ($row <= $maxRows) {
742
// This is intended to be a blank row
744
$SQL .= "SELECT Value ";
745
$SQL .= " FROM datasetdata ";
746
$SQL .= "WHERE datasetdata.RowNumber = %d ";
747
$SQL .= " AND datasetdata.DataSetColumnID = %d ";
748
$SQL = sprintf($SQL, $row, $dataSetColumnId);
750
Log::notice($SQL, 'dataset');
752
if (!$results = $db->query($SQL)) {
753
trigger_error($db->error());
754
trigger_error(__('Can not get the data row/column'), E_USER_ERROR);
757
if ($db->num_rows($results) == 0) {
760
$valueRow = $db->get_assoc_row($results);
761
$value = $valueRow['Value'];
984
if (count($sanitizedRow) > 0) {
985
$takenSomeAction = true;
987
// Check unique keys to see if this is an update
988
if (!empty($data['uniqueKeys']) && is_array($data['uniqueKeys'])) {
990
// Build a filter to select existing records
992
foreach ($data['uniqueKeys'] as $uniqueKey) {
993
if (isset($sanitizedRow[$uniqueKey])) {
994
$filter .= 'AND `' . $uniqueKey . '` = \'' . $sanitizedRow[$uniqueKey] . '\' ';
997
$filter = trim($filter, 'AND');
999
// Use the unique keys to look up this row and see if it exists
1000
$existingRows = $dataSet->getData(['filter' => $filter], ['includeFormulaColumns' => false, 'requireTotal' => false]);
1002
if (count($existingRows) > 0) {
1003
foreach ($existingRows as $existingRow) {
1004
$dataSet->editRow($existingRow['id'], array_merge($existingRow, $sanitizedRow));
1008
$dataSet->addRow($sanitizedRow);
765
// Do we need a select list?
766
if ($listContent != '') {
767
$listItems = explode(',', $listContent);
768
$selected = ($value == '') ? ' selected' : '';
769
$select = '<select class="form-control" name="value">';
770
$select .= ' <option value="" ' . $selected . '></option>';
772
for ($i = 0; $i < count($listItems); $i++) {
773
$selected = ($listItems[$i] == $value) ? ' selected' : '';
775
$select .= '<option value="' . $listItems[$i] . '" ' . $selected . '>' . $listItems[$i] . '</option>';
778
$select .= '</select>';
1012
$dataSet->addRow($sanitizedRow);
780
// Numbers are always small
781
$size = ($dataTypeId == 2) ? ' class="form-control col-sm-3"' : '';
783
if ($dataTypeId == 1) {
784
// Strings should be based on something - not sure what.
787
$select = '<input type="text" class="form-control ' . $size . '" name="value" value="' . $value . '">';
790
$action = ($value == '') ? 'AddDataSetData' : 'EditDataSetData';
795
<form id="$fieldId" class="XiboDataSetDataForm form-inline" action="index.php?p=dataset&q=$action">
796
<input type="hidden" name="fieldid" value="$fieldId">
797
<input type="hidden" name="datasetid" value="$dataSetId">
798
<input type="hidden" name="datasetcolumnid" value="$dataSetColumnId">
799
<input type="hidden" name="rownumber" value="$row">
811
$response->SetGridResponse($form);
812
$response->callBack = 'dataSetData';
816
public function AddDataSetData()
819
$user = $this->getUser();
820
$response = $this->getState();
822
$response->uniqueReference = \Xibo\Helper\Sanitize::getString('fieldid');
823
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
824
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
825
$rowNumber = \Xibo\Helper\Sanitize::getInt('rownumber');
826
$value = \Xibo\Helper\Sanitize::getString('value');
828
$auth = $user->DataSetAuth($dataSetId, true);
830
trigger_error(__('Access Denied'));
832
$dataSetObject = new DataSetData($db);
833
if (!$dataSetObject->Add($dataSetColumnId, $rowNumber, $value))
834
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
836
$response->SetFormSubmitResponse(__('Data Added'));
837
$response->loadFormUri = 'index.php?p=dataset&q=EditDataSetData';
838
$response->hideMessage = true;
839
$response->keepOpen = true;
843
public function EditDataSetData()
846
$user = $this->getUser();
847
$response = $this->getState();
849
$response->uniqueReference = \Xibo\Helper\Sanitize::getString('fieldid');
850
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
851
$dataSetColumnId = \Xibo\Helper\Sanitize::getInt('datasetcolumnid');
852
$rowNumber = \Xibo\Helper\Sanitize::getInt('rownumber');
853
$value = \Xibo\Helper\Sanitize::getString('value');
855
$auth = $user->DataSetAuth($dataSetId, true);
857
trigger_error(__('Access Denied'));
860
$dataSetObject = new DataSetData($db);
861
if (!$dataSetObject->Delete($dataSetColumnId, $rowNumber))
862
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
864
$response->SetFormSubmitResponse(__('Data Deleted'));
865
$response->loadFormUri = 'index.php?p=dataset&q=AddDataSetData';
867
$dataSetObject = new DataSetData($db);
868
if (!$dataSetObject->Edit($dataSetColumnId, $rowNumber, $value))
869
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
871
$response->SetFormSubmitResponse(__('Data Edited'));
872
$response->loadFormUri = 'index.php?p=dataset&q=EditDataSetData';
1017
if (!$takenSomeAction)
1018
throw new NotFoundException(__('No data found in request body'));
875
$response->hideMessage = true;
876
$response->keepOpen = true;
1020
$this->getState()->hydrate([
1021
'httpStatus' => 204,
1022
'message' => sprintf(__('Imported JSON into %s'), $dataSet->dataSet)
1027
* Sends out a Test Request and returns the Data as JSON to the Client so it can be shown in the Dialog
1028
* @throws XiboException
881
* Get a list of group names for a layout
882
* @param <type> $layoutId
1030
public function testRemoteRequest()
1032
$testDataSetId = $this->getSanitizer()->getInt('testDataSetId');
1034
if ($testDataSetId !== null) {
1035
$dataSet = $this->dataSetFactory->getById($testDataSetId);
1037
$dataSet = $this->dataSetFactory->createEmpty();
1039
$dataSet->dataSet = $this->getSanitizer()->getString('dataSet');
1040
$dataSet->method = $this->getSanitizer()->getString('method');
1041
$dataSet->uri = $this->getSanitizer()->getString('uri');
1042
$dataSet->postData = $this->getSanitizer()->getString('postData');
1043
$dataSet->authentication = $this->getSanitizer()->getString('authentication');
1044
$dataSet->username = $this->getSanitizer()->getString('username');
1045
$dataSet->password = $this->getSanitizer()->getString('password');
1046
$dataSet->dataRoot = $this->getSanitizer()->getString('dataRoot');
1048
// Call the remote service requested
1049
$data = $this->dataSetFactory->callRemoteService($dataSet, null, false);
1051
if ($data->number > 0) {
1052
// Process the results, but don't record them
1053
$this->dataSetFactory->processResults($dataSet, $data, false);
1056
$this->getLog()->debug('Results: ' . var_export($data, true));
1059
$this->getState()->hydrate([
1060
'message' => __('Run Test-Request for %s', $dataSet->dataSet),
1061
'id' => $dataSet->dataSetId,
885
private function GroupsForDataSet($dataSetId)
890
$SQL .= 'SELECT `group`.Group ';
891
$SQL .= ' FROM `group` ';
892
$SQL .= ' INNER JOIN lkdatasetgroup ';
893
$SQL .= ' ON `group`.GroupID = lkdatasetgroup.GroupID ';
894
$SQL .= ' WHERE lkdatasetgroup.DataSetID = %d ';
896
$SQL = sprintf($SQL, $dataSetId);
898
if (!$results = $db->query($SQL)) {
899
trigger_error($db->error());
900
trigger_error(__('Unable to get group information for dataset'), E_USER_ERROR);
905
while ($row = $db->get_assoc_row($results)) {
906
$groups .= $row['Group'] . ', ';
909
$groups = trim($groups);
910
$groups = trim($groups, ',');
915
public function PermissionsForm()
918
$user = $this->getUser();
919
$response = $this->getState();
920
$helpManager = new Help($db, $user);
922
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
924
$auth = $this->user->DataSetAuth($dataSetId, true);
926
if (!$auth->modifyPermissions)
927
trigger_error(__('You do not have permissions to edit this dataset'), E_USER_ERROR);
929
// Set some information about the form
930
Theme::Set('form_id', 'DataSetPermissionsForm');
931
Theme::Set('form_action', 'index.php?p=dataset&q=Permissions');
932
Theme::Set('form_meta', '<input type="hidden" name="datasetid" value="' . $dataSetId . '" />');
934
// List of all Groups with a view/edit/delete checkbox
936
$security = new DataSetGroupSecurity($this->db);
938
if (!$results = $security->ListSecurity($dataSetId, $user->getGroupFromId($user->userid, true))) {
939
trigger_error(__('Unable to get permissions for this dataset'), E_USER_ERROR);
942
$checkboxes = array();
944
foreach ($results as $row) {
945
$groupId = $row['groupid'];
946
$rowClass = ($row['isuserspecific'] == 0) ? 'strong_text' : '';
950
'name' => \Xibo\Helper\Sanitize::string($row['group']),
951
'class' => $rowClass,
952
'value_view' => $groupId . '_view',
953
'value_view_checked' => (($row['view'] == 1) ? 'checked' : ''),
954
'value_edit' => $groupId . '_edit',
955
'value_edit_checked' => (($row['edit'] == 1) ? 'checked' : ''),
956
'value_del' => $groupId . '_del',
957
'value_del_checked' => (($row['del'] == 1) ? 'checked' : ''),
960
$checkboxes[] = $checkbox;
963
$formFields = array();
964
$formFields[] = FormManager::AddPermissions('groupids[]', $checkboxes);
966
Theme::Set('form_fields', $formFields);
968
$response->SetFormRequestResponse(NULL, __('Permissions'), '350px', '500px');
969
$response->AddButton(__('Help'), 'XiboHelpRender("' . $helpManager->Link('DataSet', 'Permissions') . '")');
970
$response->AddButton(__('Cancel'), 'XiboDialogClose()');
971
$response->AddButton(__('Save'), '$("#DataSetPermissionsForm").submit()');
975
public function Permissions()
980
$user = $this->getUser();
981
$response = $this->getState();
984
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
985
$groupIds = \Kit::GetParam('groupids', _POST, _ARRAY);
987
$auth = $this->user->DataSetAuth($dataSetId, true);
989
if (!$auth->modifyPermissions)
990
trigger_error(__('You do not have permissions to edit this dataset'), E_USER_ERROR);
993
$security = new DataSetGroupSecurity($db);
994
if (!$security->UnlinkAll($dataSetId))
995
trigger_error(__('Unable to set permissions'));
997
// Some assignments for the loop
1004
// List of groupIds with view, edit and del assignments
1005
foreach ($groupIds as $groupPermission) {
1006
$groupPermission = explode('_', $groupPermission);
1007
$groupId = $groupPermission[0];
1010
// First time through
1012
$lastGroupId = $groupId;
1015
if ($groupId != $lastGroupId) {
1016
// The groupId has changed, so we need to write the current settings to the db.
1017
// Link new permissions
1018
if (!$security->Link($dataSetId, $lastGroupId, $view, $edit, $del))
1019
trigger_error(__('Unable to set permissions'), E_USER_ERROR);
1022
$lastGroupId = $groupId;
1028
switch ($groupPermission[1]) {
1043
// Need to do the last one
1045
if (!$security->Link($dataSetId, $lastGroupId, $view, $edit, $del))
1046
trigger_error(__('Unable to set permissions'), E_USER_ERROR);
1049
$response->SetFormSubmitResponse(__('Permissions Changed'));
1053
public function ImportCsvForm()
1057
$response = $this->getState();
1059
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
1060
$dataSet = \Xibo\Helper\Sanitize::getString('dataset');
1062
$auth = $this->user->DataSetAuth($dataSetId, true);
1064
trigger_error(__('Access Denied'), E_USER_ERROR);
1066
// Set the Session / Security information
1067
$sessionId = session_id();
1068
$securityToken = CreateFormToken();
1070
$session->setSecurityToken($securityToken);
1072
// Find the max file size
1073
$maxFileSizeBytes = convertBytes(ini_get('upload_max_filesize'));
1075
// Set some information about the form
1076
Theme::Set('form_id', 'DataSetImportCsvForm');
1077
Theme::Set('form_action', 'index.php?p=dataset&q=ImportCsv');
1078
Theme::Set('form_meta', '<input type="hidden" name="dataset" value="' . $dataSet . '" /><input type="hidden" name="datasetid" value="' . $dataSetId . '" /><input type="hidden" id="txtFileName" name="txtFileName" readonly="true" /><input type="hidden" name="hidFileID" id="hidFileID" value="" />');
1080
Theme::Set('form_upload_id', 'file_upload');
1081
Theme::Set('form_upload_action', 'index.php?p=content&q=FileUpload');
1082
Theme::Set('form_upload_meta', '<input type="hidden" id="PHPSESSID" value="' . $sessionId . '" /><input type="hidden" id="SecurityToken" value="' . $securityToken . '" /><input type="hidden" name="MAX_FILE_SIZE" value="' . $maxFileSizeBytes . '" />');
1084
Theme::Set('prepend', Theme::RenderReturn('form_file_upload_single'));
1086
$formFields = array();
1087
$formFields[] = FormManager::AddCheckbox('overwrite', __('Overwrite existing data?'),
1089
__('Erase all content in this DataSet and overwrite it with the new content in this import.'),
1092
$formFields[] = FormManager::AddCheckbox('ignorefirstrow', __('Ignore first row?'),
1094
__('Ignore the first row? Useful if the CSV has headings.'),
1097
// Enumerate over the columns in the DataSet and offer a column mapping for each one (from the file)
1099
$SQL .= "SELECT DataSetColumnID, Heading ";
1100
$SQL .= " FROM datasetcolumn ";
1101
$SQL .= sprintf(" WHERE DataSetID = %d ", $dataSetId);
1102
$SQL .= " AND DataSetColumnTypeID = 1 ";
1103
$SQL .= "ORDER BY ColumnOrder ";
1105
// Load results into an array
1106
$dataSetColumns = $db->GetArray($SQL);
1108
if (!is_array($dataSetColumns)) {
1109
trigger_error($db->error());
1110
trigger_error(__('Error getting list of dataSetColumns'), E_USER_ERROR);
1115
foreach ($dataSetColumns as $row) {
1118
$formFields[] = FormManager::AddNumber('csvImport_' . \Xibo\Helper\Sanitize::int($row['DataSetColumnID']),
1119
\Xibo\Helper\Sanitize::string($row['Heading']), $i, NULL, 'c');
1122
Theme::Set('form_fields', $formFields);
1124
$response->SetFormRequestResponse(NULL, __('CSV Import'), '350px', '200px');
1125
$response->AddButton(__('Help'), 'XiboHelpRender("' . Help::Link('DataSet', 'ImportCsv') . '")');
1126
$response->AddButton(__('Cancel'), 'XiboDialogClose()');
1127
$response->AddButton(__('Import'), '$("#DataSetImportCsvForm").submit()');
1131
public function ImportCsv()
1135
$response = $this->getState();
1136
$dataSetId = \Xibo\Helper\Sanitize::getInt('datasetid');
1137
$overwrite = \Xibo\Helper\Sanitize::getCheckbox('overwrite');
1138
$ignorefirstrow = \Xibo\Helper\Sanitize::getCheckbox('ignorefirstrow');
1140
$auth = $this->user->DataSetAuth($dataSetId, true);
1142
trigger_error(__('Access Denied'), E_USER_ERROR);
1145
$tmpName = \Xibo\Helper\Sanitize::getString('hidFileID');
1148
trigger_error(__('Please ensure you have picked a file and it has finished uploading'), E_USER_ERROR);
1150
// File name and extension (original name)
1151
$fileName = \Xibo\Helper\Sanitize::getString('txtFileName');
1152
$fileName = basename($fileName);
1153
$ext = strtolower(substr(strrchr($fileName, "."), 1));
1155
// Check it is a CSV file
1157
trigger_error(__('Files with a CSV extension only.'), E_USER_ERROR);
1159
// File upload directory.. get this from the settings object
1160
$csvFileLocation = Config::GetSetting('LIBRARY_LOCATION') . 'temp/' . $tmpName;
1162
// Enumerate over the columns in the DataSet and offer a column mapping for each one (from the file)
1164
$SQL .= "SELECT DataSetColumnID ";
1165
$SQL .= " FROM datasetcolumn ";
1166
$SQL .= sprintf(" WHERE DataSetID = %d ", $dataSetId);
1167
$SQL .= " AND DataSetColumnTypeID = 1 ";
1168
$SQL .= "ORDER BY ColumnOrder ";
1170
// Load results into an array
1171
$dataSetColumns = $db->GetArray($SQL);
1173
if (!is_array($dataSetColumns)) {
1174
trigger_error($db->error());
1175
trigger_error(__('Error getting list of dataSetColumns'), E_USER_ERROR);
1178
$spreadSheetMapping = array();
1180
foreach ($dataSetColumns as $row) {
1182
$dataSetColumnId = \Xibo\Helper\Sanitize::int($row['DataSetColumnID']);
1183
$spreadSheetColumn = \Kit::GetParam('csvImport_' . $dataSetColumnId, _POST, _INT);
1185
// If it has been left blank, then skip
1186
if ($spreadSheetColumn != 0)
1187
$spreadSheetMapping[($spreadSheetColumn - 1)] = $dataSetColumnId;
1190
$dataSetObject = new DataSetData($db);
1192
if (!$dataSetObject->ImportCsv($dataSetId, $csvFileLocation, $spreadSheetMapping, ($overwrite == 1), ($ignorefirstrow == 1)))
1193
trigger_error($dataSetObject->GetErrorMessage(), E_USER_ERROR);
1195
$response->SetFormSubmitResponse(__('CSV File Imported'));
added
removed
lib/Controller/DataSet.php
