summaryrefslogtreecommitdiffstats
path: root/admin/survey/classes/class.SurveyAppendMerge.php
diff options
context:
space:
mode:
Diffstat (limited to 'admin/survey/classes/class.SurveyAppendMerge.php')
-rw-r--r--admin/survey/classes/class.SurveyAppendMerge.php937
1 files changed, 718 insertions, 219 deletions
diff --git a/admin/survey/classes/class.SurveyAppendMerge.php b/admin/survey/classes/class.SurveyAppendMerge.php
index c16549a..4fa6651 100644
--- a/admin/survey/classes/class.SurveyAppendMerge.php
+++ b/admin/survey/classes/class.SurveyAppendMerge.php
@@ -6,6 +6,9 @@
*
*/
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\IOFactory;
+
class SurveyAppendMerge {
private $anketa;
@@ -24,9 +27,7 @@ class SurveyAppendMerge {
SurveyInfo::getInstance()->SurveyInit($this->anketa);
- if (SurveyInfo::getInstance()->getSurveyColumn('db_table') == 1)
- $this->db_table = '_active';
-
+ $this->db_table = SurveyInfo::getInstance()->getSurveyArchiveDBString();
}
function display ($merge = false) {
@@ -48,11 +49,13 @@ class SurveyAppendMerge {
global $lang, $site_path, $site_url;
$field_list = array();
+ $field_types = array();
- $sql = sisplet_query("SELECT s.id, s.variable, s.tip FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$this->anketa' AND s.tip IN (1, 2, 3, 7, 8, 21) ORDER BY g.vrstni_red, s.vrstni_red");
+ $sql = sisplet_query("SELECT s.id, s.variable, s.tip FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$this->anketa' AND s.tip IN (1, 2, 3, 7, 8, 21, 6) ORDER BY g.vrstni_red, s.vrstni_red");
while ($row = mysqli_fetch_array($sql)) {
- $field_list[$row['id']] = $row['variable'] . ' ('.($row['tip']==1?'radio':'').($row['tip']==2?'checkbox':'').($row['tip']==3?'dropdown':'').($row['tip']==21?'text':'').($row['tip']==7?'number':'').($row['tip']==8?'date':'').')';
+ $field_list[$row['id']] = $row['variable'] . ' ('.($row['tip']==1?'radio':'').($row['tip']==2?'checkbox':'').($row['tip']==3?'dropdown':'').($row['tip']==21?'text':'').($row['tip']==7?'number':'').($row['tip']==8?'date':'').($row['tip']==6?'table':'').')';
+ $field_types[$row['id']] = $row['tip'];
}
$import_type = isset($_POST['import_type']) ? (int)$_POST['import_type'] : 2;
@@ -69,15 +72,19 @@ class SurveyAppendMerge {
echo '<input type="hidden" name="do" value="0">';
echo '<input type="hidden" name="anketa" value="'.$this->anketa.'">';
- # sporočilo za personalizirana e-vabila in respondente iz baze
- echo $lang['srv_append_note'];
+ // Opis združevanja podatkov
+ echo '<p class="bottom16">'.$lang['srv_append_note'].'</p>';
// Korak 1
- echo $lang['srv_append_step1'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_append_step1'].'</p>';
+ echo '<div class="appendmerge_indent">';
+
+ echo '<ul>';
+ echo '<li>'.$lang['srv_append_step1_notea'].'</li>';
+ echo '<li>'.$lang['srv_append_step1_noteb'].'</li>';
+ echo '<li>'.$lang['srv_append_step1_notec'].'</li>';
+ echo '</ul>';
- echo $lang['srv_append_step1_note'];
-
echo '<div id="inv_field_container">';
echo '<ul class="connectedSortable">';
@@ -95,7 +102,7 @@ class SurveyAppendMerge {
$label_for = ' for="'.$field.'_chk"';
echo '<li id="'.$field.'"'.$css.'>';
- echo '<input id="'.$field.'_chk" type="checkbox" name="fields[]" value="'.$field.'" class="inv_checkbox' . $hidden_checkbox . '"'.($is_selected == true ? ' checked="checked"' : '').' '.($merge?' onclick="merge_getItems();"':'').'>';
+ echo '<input id="'.$field.'_chk" type="checkbox" name="fields[]" value="'.$field.'" class="inv_checkbox"'.($is_selected == true ? ' checked="checked"' : '').' '.($merge?' onclick="merge_getItems();"':'').'>';
echo '<label'.$label_for.' style="display: inline">'.$text_label.'</label>';
echo '</li>';
@@ -104,25 +111,34 @@ class SurveyAppendMerge {
}
}
}
- echo '</ul><br />';
+ echo '</ul>';
echo '</div>';
+
+ echo '<p>'.$lang['srv_append_step1_noted'].'</p>';
+
echo '</div>';
// Korak 2
- echo $lang['srv_append_step2'];
- echo '<div class="append_step">';
-
- echo '<script type="text/javascript">';
- echo "$('ul.connectedSortable').sortable({update : function () { append_refreshFieldsList(); }, forcePlaceholderSize: 'true',tolerance: 'pointer',placeholder: 'inv_field_placeholder',});";
- echo '</script>';
+ echo '<p class="bold">'.$lang['srv_append_step2'].'</p>';
+
+ echo '<div class="appendmerge_indent">';
- echo '<span><label><input name="inv_import_type" id="inv_import_type0" type="radio" value="0" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m='.$_GET['m'].'\');" checked="checked">';
- echo ''.$lang['srv_iz_seznama'].'</label></span>';
- echo '<span><label><input name="inv_import_type" id="inv_import_type1" type="radio" value="1" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m=upload_xls\');">';
- echo ''.$lang['srv_iz_excela'].'</label></span>';
- echo '<br class="clr"/>';
- echo '<br class="clr"/>';
+ echo '<script type="text/javascript">initAppendMergeConnectedSortable();</script>';
+
+ echo '<div class="setting_holder">';
+ echo '<span class="setting_title">'.$lang['srv_appendmerge_source'].'</span>';
+
+ echo '<div class="setting_item">';
+ echo '<input name="inv_import_type" id="inv_import_type0" type="radio" value="0" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m='.$_GET['m'].'\');" checked="checked">';
+ echo '<label for="inv_import_type0">'.$lang['srv_iz_seznama'].'</label>';
+ echo '</div>';
+ echo '<div class="setting_item">';
+ echo '<input name="inv_import_type" id="inv_import_type1" type="radio" value="1" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m=upload_xls\');">';
+ echo '<label for="inv_import_type1">'.$lang['srv_iz_excela'].'</label>';
+ echo '</div>';
+
+ echo '</div>';
echo '<input type="hidden" name="do_merge" value="0" id="do_merge">';
@@ -133,21 +149,34 @@ class SurveyAppendMerge {
echo '<div id="inv_import_list"'.($import_type != 1 ? '' : ' style="display:none"').'>' ;
// Korak 3
- echo $lang['srv_append_step3'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_append_step3'].'</p>';
+ echo '<div class="appendmerge_indent">';
- echo $lang['srv_append_step3_note'];
+ //echo $lang['srv_append_step3_note'];
+
+ echo '<ul>';
+ echo '<li>'.$lang['srv_append_step3_note1'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note2'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note3'].'</li>';
+ echo '</ul>';
+
+ echo '<p class="top16 bottom16">'.$lang['srv_append_step3_note4'].'</p>';
+
+ echo '<ul>';
+ echo '<li>'.$lang['srv_append_step3_note5'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note6'].'</li>';
+ echo '</ul>';
+
+ echo '<p class="top16 bottom16">'.$lang['srv_append_step3_note7'].'</p>';
+ echo '<p>'.$lang['srv_append_step3_note8'].'</p>';
+ echo '<p>'.$lang['srv_append_step3_note9'].'</p>';
+
+ $recipients_list = isset($_POST['inv_recipients_list']) ? mysql_real_unescape_string( $_POST['inv_recipients_list']) : array();
- echo '<span class="inv_sample" >';
- echo $lang['srv_inv_recipiens_sample'].'&nbsp;</span><span class="inv_sample">';
- echo $lang['srv_inv_recipiens_sample1'];
- echo '</span>';
- echo '<br class="clr" />';
- echo '</span>';
- echo '<br class="clr" />'.$lang['srv_inv_recipiens_fields'].' <span id="inv_field_list" class="inv_type_0">';
- echo implode(',',$field_lang);
- echo '</span>';
- echo '<br class="clr" /><textarea id="inv_recipients_list" cols="50" rows="9" name="inv_recipients_list" style="margin-bottom: 7px;">';
+ echo '<p class="top16">'.$lang['srv_append_step3_note10'].' <span id="inv_field_list" class="inv_type_0 bold">';
+ echo implode(', ',$field_lang);
+ echo '</span></p>';
+ echo '<textarea id="inv_recipients_list" name="inv_recipients_list"">';
if (is_array($recipients_list) && count($recipients_list) > 0 ) {
echo implode("\n",$recipients_list);
}
@@ -161,32 +190,28 @@ class SurveyAppendMerge {
echo '<div id="inv_import_file"'.($import_type == 1 ? '' : ' style="display:none"').'>' ;
// Korak 3
- echo $lang['srv_append_step3_xls'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_append_step3_xls'].'</p>';
+
+ echo '<div class="appendmerge_indent">';
- echo $lang['srv_mailing_upload_list'];
- echo ' <input type="file" name="recipientsFile" id="recipientsFile" />';
-
- echo '<br /><br />';
-
- echo $lang['srv_excel_upload_note'];
-
+ echo '<p class="top16 bottom16"><input type="file" name="recipientsFile" id="recipientsFile" /></p>';
+
echo '</div>';
echo '</div>'; # id=inv_import_file
- echo '<br class="clr" />';
- echo '<span id="inv_upload_recipients_nosbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" onclick="append_submit(); return false;">'.$lang['srv_inv_btn_add_recipients_add'].'</a></span>';
-
+ echo '<div class="button_holder bottom16">';
+ echo '<button class="medium blue" onclick="append_submit(); return false;">'.$lang['srv_append_add'].'</button>';
+ echo '</div>';
+
$d = new Dostop();
if ($d->checkDostopSub('edit')){
- echo '<span style="margin: 5px 20px; line-height:24px">';
+ echo '<span class="red">';
printf($lang['src_coding_alert'], $this->anketa);
echo '</span>';
}
echo '</form>';
- echo '<br class="clr"/>';
//echo '</div>'; # id=inv_import_list_container
@@ -203,15 +228,19 @@ class SurveyAppendMerge {
echo '<input type="hidden" name="do" value="0">';
echo '<input type="hidden" name="anketa" value="'.$this->anketa.'">';
- # sporočilo za personalizirana e-vabila in respondente iz baze
- echo $lang['srv_merge_note'];
-
+ //Opis združevanja podatkov
+ echo '<p class="bottom16">'.$lang['srv_merge_note'].'</p>';
+
// Korak 1
- echo $lang['srv_merge_step1'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_merge_step1'].'</p>';
+ echo '<div class="appendmerge_indent">';
+
+ echo '<ul>';
+ echo '<li>'.$lang['srv_merge_step1_notea'].'</li>';
+ echo '<li>'.$lang['srv_merge_step1_noteb'].'</li>';
+ echo '<li>'.$lang['srv_merge_step1_notec'].'</li>';
+ echo '</ul>';
- echo $lang['srv_merge_step1_note'];
-
echo '<div id="inv_field_container">';
echo '<ul class="connectedSortable">';
@@ -222,14 +251,13 @@ class SurveyAppendMerge {
$is_selected = false;
# če je polje obkljukano
- $css = $is_selected ? ' class="inv_field_enabled"' : '';
-
+ $css = $is_selected ? ' class="inv_field_enabled"' : '';
# labela sproži klik checkboxa
$label_for = ' for="'.$field.'_chk"';
- echo '<li id="'.$field.'"'.$css.'>';
- echo '<input id="'.$field.'_chk" type="checkbox" name="fields[]" value="'.$field.'" class="inv_checkbox' . $hidden_checkbox . '"'.($is_selected == true ? ' checked="checked"' : '').' '.($merge?' onclick="merge_getItems();"':'').'>';
+ echo '<li id="'.$field.'"'.$css.' data-tip="'.$field_types[$field].'">';
+ echo '<input id="'.$field.'_chk" type="checkbox" name="fields[]" value="'.$field.'" class="inv_checkbox"'.($is_selected == true ? ' checked="checked"' : '').' '.($merge?' onclick="merge_getItems();"':'').'>';
echo '<label'.$label_for.' style="display: inline">'.$text_label.'</label>';
echo '</li>';
@@ -238,33 +266,40 @@ class SurveyAppendMerge {
}
}
}
- echo '</ul><br />';
- echo '</div>';
+ echo '</ul>';
echo '</div>';
-
+ echo '<p>'.$lang['srv_merge_step1_noted'].'</p>';
+
+ echo '</div>';
+
// Korak 2
- echo $lang['srv_merge_step2'];
- echo '<div class="append_step">';
-
- echo '<script type="text/javascript">';
- echo "$('ul.connectedSortable').sortable({update : function () { append_refreshFieldsList(); }, forcePlaceholderSize: 'true',tolerance: 'pointer',placeholder: 'inv_field_placeholder',});";
- echo '</script>';
-
- echo '<span><label><input name="inv_import_type" id="inv_import_type0" type="radio" value="0" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m='.$_GET['m'].'\');" checked="checked">';
- echo ''.$lang['srv_iz_seznama'].'</label></span>';
- echo '<span><label><input name="inv_import_type" id="inv_import_type1" type="radio" value="1" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m=upload_xls\');">';
- echo ''.$lang['srv_iz_excela'].'</label></span>';
-
- echo '<br class="clr"/>';
- echo '<br class="clr"/>';
-
- echo '</div>';
+ echo '<p class="bold">'.$lang['srv_merge_step2'].'</p>';
+
+ echo '<div class="appendmerge_indent">';
+ echo '<script type="text/javascript">initAppendMergeConnectedSortable();</script>';
+
+ echo '<div class="setting_holder">';
+ echo '<span class="setting_title">'.$lang['srv_appendmerge_source'].'</span>';
+
+ echo '<div class="setting_item">';
+ echo '<input name="inv_import_type" id="inv_import_type0" type="radio" value="0" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m='.$_GET['m'].'\');" checked="checked">';
+ echo '<label for="inv_import_type0">'.$lang['srv_iz_seznama'].'</label>';
+ echo '</div>';
+ echo '<div class="setting_item">';
+ echo '<input name="inv_import_type" id="inv_import_type1" type="radio" value="1" onclick="append_change_import_type(\''.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m=upload_xls\');">';
+ echo '<label for="inv_import_type1">'.$lang['srv_iz_excela'].'</label>';
+ echo '</div>';
+
+ echo '</div>';
+
+ echo '</div>';
// Korak 3
- echo $lang['srv_merge_step3'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_merge_step3'].'</p>';
+
+ echo '<div class="appendmerge_indent">';
echo '<input type="hidden" name="do_merge" value="1" id="do_merge">';
echo '<p>'.$lang['srv_izberite_identifikator'].'</p>';
@@ -277,25 +312,38 @@ class SurveyAppendMerge {
echo '<div id="inv_import_list"'.($import_type != 1 ? '' : ' style="display:none"').'>' ;
// Korak 4
- echo $lang['srv_merge_step4'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_merge_step4'].'</p>';
+
+ echo '<div class="appendmerge_indent">';
- echo $lang['srv_merge_step4_note'];
+ echo '<ul>';
+ echo '<li>'.$lang['srv_append_step3_note1'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note2'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note3'].'</li>';
+ echo '</ul>';
- echo $lang['srv_inv_recipiens_sample'].'&nbsp;</span><span class="inv_sample">';
- echo $lang['srv_inv_recipiens_sample1'];
- echo '</span>';
- echo '<br class="clr" />';
- echo '</span>';
- echo '<br class="clr" />'.$lang['srv_inv_recipiens_fields'].' <span id="inv_field_list" class="inv_type_0">';
+ echo '<p class="top16 bottom16">'.$lang['srv_append_step3_note4'].'</p>';
+
+ echo '<ul>';
+ echo '<li>'.$lang['srv_append_step3_note5'].'</li>';
+ echo '<li>'.$lang['srv_append_step3_note6'].'</li>';
+ echo '</ul>';
+
+ echo '<p class="top16 bottom16">'.$lang['srv_append_step3_note7'].'</p>';
+ echo '<p>'.$lang['srv_append_step3_note8'].'</p>';
+ echo '<p>'.$lang['srv_append_step3_note9'].'</p>';
+
+ echo '<p class="top16">'.$lang['srv_append_step3_note10'].' <span id="inv_field_list" class="inv_type_0 bold">';
echo implode(',',$field_lang);
echo '</span>';
- echo '<br class="clr" /><textarea id="inv_recipients_list" cols="50" rows="9" name="inv_recipients_list" style="margin-bottom: 7px;">';
+
+ $recipients_list = isset($_POST['inv_recipients_list']) ? mysql_real_unescape_string( $_POST['inv_recipients_list']) : array();
+
+ echo '<textarea id="inv_recipients_list" name="inv_recipients_list">';
if (is_array($recipients_list) && count($recipients_list) > 0 ) {
echo implode("\n",$recipients_list);
}
echo '</textarea>';
- echo '<br class="clr"/>';
echo '</div>';
echo '</div>'; # id=inv_import_list
@@ -304,34 +352,27 @@ class SurveyAppendMerge {
echo '<div id="inv_import_file"'.($import_type == 1 ? '' : ' style="display:none"').'>' ;
// Korak 4
- echo $lang['srv_merge_step4'];
- echo '<div class="append_step">';
+ echo '<p class="bold">'.$lang['srv_merge_step4_xls'].'</p>';
+ echo '<div class="appendmerge_indent">';
- echo $lang['srv_mailing_upload_list'];
echo ' <input type="file" name="recipientsFile" id="recipientsFile" />';
- echo '<br class="clr" /><br />';
-
- echo $lang['srv_excel_upload_note'];
-
echo '</div>';
echo '</div>'; # id=inv_import_file
-
- echo '<br class="clr" />';
- echo '<span id="inv_upload_recipients_nosbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" onclick="append_submit(); return false;">'.$lang['srv_inv_btn_add_recipients_add'].'</a></span>';
-
+ echo '<div class="button_holder bottom16">';
+ echo '<button class="medium blue" onclick="append_submit(); return false;">'.$lang['srv_merge_add'].'</button>';
+ echo '</div>';
+
$d = new Dostop();
if ($d->checkDostopSub('edit')){
- echo '<span style="margin: 5px 20px; line-height:24px">';
+ echo '<span class="red">';
printf($lang['src_coding_alert'], $this->anketa);
echo '</span>';
}
echo '</form>';
- echo '<br class="clr"/>';
-
echo '</fieldset>';
}
}
@@ -342,14 +383,13 @@ class SurveyAppendMerge {
global $global_user_id;
# dodamo uporabnike
- $fields = $_POST['fields'];
+ $fields = isset($_POST['fields']) ? $_POST['fields'] : array();
$recipients_list = mysql_real_unescape_string( $_POST['inv_recipients_list'] );
- $merge = (int)$_POST['merge'];
+ $merge = isset($_POST['merge']) ? (int)$_POST['merge'] : 0;
$do_merge = (int)$_POST['do_merge'];
$import_type = (int)$_POST['inv_import_type'];
if ($_POST['do']=='1') $do = true; else $do = false;
-
-
+
// ce uploadamo datoteko
if ($import_type == 1) {
@@ -412,26 +452,31 @@ class SurveyAppendMerge {
// napaka
if ($result <= 0) {
echo '<h2>'.$lang['error'].'</h2>';
+ echo '<div class="popup_close"><a href="#" onClick="append_submit_close(); return false;"">✕</a></div>';
if ($result < 0)
echo '<p>'.$output.'</p>';
else
echo '<p>'.$lang['srv_append-merge_error_value'].'</p>';
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_gray" onclick="append_submit_close(); return false;"><span>'.$lang['back'].'</span></a></span>';
+ echo '<div class="button_holder">';
+ echo '<button class="medium white-blue" onclick="append_submit_close(); return false;">'.$lang['back'].'</button>';
+ echo '</div>';
} else {
echo '<h2>'.$lang['srv_potrditev'].'</h2>';
+ echo '<div class="popup_close"><a href="#" onClick="append_submit_close(); return false;"">✕</a></div>';
if ($do_merge == 0)
echo '<p>'.$lang['srv_append-merge_process_1'].' '.$result.' '.$lang['srv_append-merge_process_2'].'. '.'</p>';
else
echo '<p>'.$lang['srv_append-merge_process_o_1'].' '.$result.' '.$lang['srv_append-merge_process_o_2'].'</p>';
-
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_gray" onclick="append_submit_close(); return false;"><span>'.$lang['srv_cancel'].'</span></a></span>';
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft spaceLeft"><a class="ovalbutton ovalbutton_orange" onclick="append_submit(1); return false;"><span>'.$lang['srv_potrdi'].'</span></a></span>';
+ echo '<div class="button_holder">';
+ echo '<button class="medium white-blue" onclick="append_submit_close(); return false;">'.$lang['srv_cancel'].'</button>';
+ echo '<button class="medium blue" onclick="append_submit(1); return false;">'.$lang['srv_potrdi'].'</button>';
+ echo '</div>';
}
@@ -439,25 +484,27 @@ class SurveyAppendMerge {
} else {
echo '<h2>'.$lang['fin_import_ok'].'</h2>';
+ echo '<div class="popup_close"><a href="#" onClick="append_submit_close(); return false;"">✕</a></div>';
echo '<p>'.$output.'</p>';
- echo '<p>'.$lang['srv_append-merge_fin'].'</p>';
-
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" href="index.php?anketa='.$this->anketa.'&a=data'.(count($this->usr_ids) < 100?'&highlight_usr='.implode('-', $this->usr_ids).'':'').'"><span>'.$lang['data_show'].'</span></a></span>';
-
+ if ($do_merge == 0) echo '<p>'.$lang['srv_append-merge_fin'].'</p>';
+
+ echo '<div class="button_holder">';
+ echo '<button class="medium white-blue" onclick="append_submit_close(); return false;">'.$lang['srv_zapri'].'</button>';
+ echo '<button class="medium blue" onclick="window.location.href=\'index.php?anketa='.$this->anketa.'&a=data'.(count($this->usr_ids) < 100?'&highlight_usr='.implode('-', $this->usr_ids).'':'').'\'">'.$lang['data_show'].'</button>';
+ echo '</div>';
}
if ($import_type == 1){
- echo '<br /><br />';
echo '</fieldset>';
}
}
private $usr_ids = array();
- function appendData($do = true, $fields, $rawdata) {
+ function appendData($do, $fields, $rawdata) {
if ($do) TrackingClass::update($this->anketa, 1);
-
+
if (false) {
echo 'dumping for append:';
echo '<pre>';
@@ -480,7 +527,7 @@ class SurveyAppendMerge {
while ($row = mysqli_fetch_array($sql)) {
$tip[$row['id']] = $row['tip'];
- if ( in_array($row['tip'], array(1, 2, 3)) ) {
+ if ( in_array($row['tip'], array(1, 2, 3, 6)) ) {
$s = sisplet_query("SELECT id, variable FROM srv_vrednost WHERE spr_id='$row[id]' ORDER BY vrstni_red");
while ($r = mysqli_fetch_array($s)) {
@@ -496,7 +543,7 @@ class SurveyAppendMerge {
}
$added = 0;
foreach ($data AS $dataline) {
- $line = explode(',', $dataline);
+ $line = explode(';', $dataline);
foreach ($line AS $key => $val) {
$line[$key] = trim($val);
@@ -511,16 +558,16 @@ class SurveyAppendMerge {
}
function insertLine($line,$fields,$tip,$vre_id,$do,$fromExcel=false){
-
$added=0;
-
+
// izberemo random hash, ki se ni v bazi
$ip = GetIP();
do {
$rand = md5(mt_rand(1, mt_getrandmax()).'@'.$ip);
$sql = sisplet_query("SELECT id FROM srv_user WHERE cookie = '$rand'");
} while (mysqli_num_rows($sql) > 0);
-
+
+ $usr_id = 0;
// nov user
if ($do) {
$sql = sisplet_query("INSERT INTO srv_user (id, ank_id, cookie, time_insert, time_edit, last_status, lurker) VALUES ('', '$this->anketa', '$rand', NOW(), NOW(), '5', '0')");
@@ -531,41 +578,106 @@ class SurveyAppendMerge {
if ($usr_id > 0 || $do==false) {
$this->usr_ids[] = $usr_id;
-
+
$i = 0;
+ // pri excel uvozu so stolpci posebej pri check za vsak check, to je potem difference med stevcem spremenljivk ($i) in dejanskih stolpcev (i+vre_diff)
+ $vre_diff = 0;
foreach ($fields AS $id) {
-
+
if ($do) {
if ( in_array($tip[$id], array(1, 3)) ) {
-
- $vre = $vre_id[$id][$line[$i]];
-
+
+ $vre = isset($vre_id[$id][$line[$i+$vre_diff]]) ? $vre_id[$id][$line[$i+$vre_diff]] : '';
+
if ($vre != '') {
$sql = sisplet_query("INSERT INTO srv_data_vrednost".$this->db_table." (spr_id, vre_id, usr_id, loop_id) VALUES ('$id', '$vre', '$usr_id', NULL)");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
}
} elseif ( in_array($tip[$id], array(2)) ) {
-
- $checks = explode(' ', $line[$i]);
-
- if ( count($checks) > 0 ) {
+
+ $checks = array();
+ //array vrednosti checkboxov (array 1 in 0 po vrsti, deljeni s presledkom)
+ if(!$fromExcel)
+ $checks = isset($line[$i+$vre_diff]) ? explode(' ', $line[$i+$vre_diff]) : array();
+ else{
+ //pri excel prestejemo vrednosti v spremenljivki
+ $sql = sisplet_query("SELECT COUNT(*) as cnt FROM srv_vrednost WHERE spr_id='$id'");
+ if ($sql) {
+ $row = mysqli_fetch_array($sql);
+ //izrezemo stolpce, ki so vrednosti spremenljivke checkbox
+ $checks = array_slice($line, $i+$vre_diff, $row['cnt']);
+ }
+ }
+
+ if ( count($checks) > 0) {
$sql = sisplet_query("DELETE FROM srv_data_vrednost".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
- foreach ($checks AS $k => $v) {
-
- $vre = $vre_id[$id][$v];
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
- if ($vre != '') {
-
+ //ce obstaja vrednost in je checkbox nastavljen na 1, potem shrani v bazo vrednost
+ if ($vre != '' && $v == 1) {
$sql = sisplet_query("INSERT INTO srv_data_vrednost".$this->db_table." (spr_id, vre_id, usr_id, loop_id) VALUES ('$id', '$vre', '$usr_id', NULL)");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
}
+ }
+
+ //ce smo v excel, pristejemo razliko vseh vrednosti v stevec (-1 ker se 1 steje sama spremenljivka)
+ if($fromExcel) $vre_diff += count($checks)-1;
+ }
+
+ } elseif ( in_array($tip[$id], array(6)) ) {
+
+ //podnobno, kot pri check, je tukaj vrednost vprasanje v tabeli
+ $checks = array();
+ //array vrednosti tabele (array 1 in 0 po vrsti, deljeni s presledkom)
+ if(!$fromExcel)
+ $checks = isset($line[$i+$vre_diff]) ? explode(' ', $line[$i+$vre_diff]) : array();
+ else{
+ //pri excel prestejemo vrednosti v spremenljivki
+ $sql = sisplet_query("SELECT COUNT(*) as cnt FROM srv_vrednost WHERE spr_id='$id'");
+ if ($sql) {
+ $row = mysqli_fetch_array($sql);
+ //izrezemo stolpce, ki so vrednosti spremenljivke tabela
+ $checks = array_slice($line, $i+$vre_diff, $row['cnt']);
+ }
+ }
+
+ if ( count($checks) > 0) {
+ $sql = sisplet_query("DELETE FROM srv_data_grid".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //get number of grids for this question
+ $sqlg = sisplet_query("SELECT grids FROM srv_spremenljivka WHERE id='$id'");
+ $grids_num = mysqli_fetch_array($sqlg)['grids'];
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
+
+ //ce obstaja vrednost jo shrani v bazo
+ if ($vre != '' && is_numeric($v) && $v > 0 && $v <= $grids_num) {
+ $sql = sisplet_query("INSERT INTO srv_data_grid".$this->db_table." (spr_id, vre_id, usr_id, grd_id, loop_id) VALUES ('$id', '$vre', '$usr_id', '$v', NULL)");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
}
+
+ //ce smo v excel, pristejemo razliko vseh vrednosti v stevec (-1 ker se 1 steje sama spremenljivka)
+ if($fromExcel) $vre_diff += count($checks)-1;
}
} elseif ( in_array($tip[$id], array(7, 21, 8)) ) {
@@ -576,21 +688,32 @@ class SurveyAppendMerge {
$vre = 0;
}
+ $value = $line[$i+$vre_diff];
+
if ($tip[$id] == 8)
{
#datum prekodiramo v pravo obliko
- $value = PHPExcel_Style_NumberFormat::toFormattedString($line[$i], "D.M.YYYY");
+ //$value = PHPExcel_Style_NumberFormat::toFormattedString($line[$i], "D.M.YYYY");
+
+ // Create a DateTime object from the input date string
+ $date = new DateTime($value);
+ // Format the date as "D.M.YYYY"
+ $value = $date->format('d.m.Y');
}
- else
- {
- $value = $line[$i];
- }
+ //brisi vse vejice in pusti samo zadnjo piko za decimalke
+ elseif($tip[$id] == 7){
+ // Replace all commas with dots
+ $value = str_replace(',', '.', $value);
+ // Remove all dots except the last one
+ $value = preg_replace('/\.(?=.*\.)/', '', $value);
+ }
+
$sql = sisplet_query("INSERT INTO srv_data_text".$this->db_table." (id, spr_id, vre_id, text, text2, usr_id, loop_id) VALUES ('', '$id', '$vre', '$value', '', '$usr_id', NULL)");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
}
}
-
+
$i++;
}
$added++;
@@ -598,7 +721,7 @@ class SurveyAppendMerge {
return $added;
}
- function mergeData($do = true, $fields, $rawdata, $merge) {
+ function mergeData($do, $fields, $rawdata, $merge) {
if ($do) TrackingClass::update($this->anketa, 1);
if (false) {
@@ -631,7 +754,7 @@ class SurveyAppendMerge {
while ($row = mysqli_fetch_array($sql)) {
$tip[$row['id']] = $row['tip'];
- if ( in_array($row['tip'], array(1, 2, 3)) ) {
+ if ( in_array($row['tip'], array(1, 2, 3, 6)) ) {
$s = sisplet_query("SELECT id, variable FROM srv_vrednost WHERE spr_id='$row[id]' ORDER BY vrstni_red");
while ($r = mysqli_fetch_array($s)) {
@@ -652,7 +775,7 @@ class SurveyAppendMerge {
foreach ($data AS $dataline) {
- $line = explode(',', $dataline);
+ $line = explode(';', $dataline);
foreach ($line AS $key => $val) {
$line[$key] = trim($val);
@@ -663,8 +786,8 @@ class SurveyAppendMerge {
// poiscemo userja za merge
if ( in_array($merge_tip, array(1, 2, 3)) ) {
- $sqlu = sisplet_query("SELECT usr_id FROM srv_data_vrednost".$this->db_table." WHERE spr_id='$merge' AND vre_id='".$vre_id[$merge][$line[$merge_key]]."'");
-
+ $sqlu = sisplet_query("SELECT usr_id FROM srv_data_vrednost".$this->db_table." LEFT JOIN srv_user ON srv_user.id = srv_data_vrednost".$this->db_table.".usr_id WHERE spr_id='$merge' AND vre_id='".$vre_id[$merge][$line[$merge_key]]."' AND deleted = '0'");
+
} elseif ( in_array($merge_tip, array(7, 21, 8)) ) {
if ($merge_tip == 21) {
$vre = $vre_id[$merge];
@@ -672,7 +795,7 @@ class SurveyAppendMerge {
$vre = 0;
}
- $sqlu = sisplet_query("SELECT usr_id FROM srv_data_text".$this->db_table." WHERE spr_id='$merge' AND vre_id='$vre' AND text='$line[$merge_key]'");
+ $sqlu = sisplet_query("SELECT usr_id FROM srv_data_text".$this->db_table." LEFT JOIN srv_user ON srv_user.id = srv_data_vrednost".$this->db_table.".usr_id WHERE spr_id='$merge' AND vre_id='$vre' AND text='$line[$merge_key]' AND deleted = '0'");
}
if (!$sqlu) echo mysqli_error($GLOBALS['connect_db']);
@@ -706,38 +829,86 @@ class SurveyAppendMerge {
}
} elseif ( in_array($tip[$id], array(2)) ) {
-
+
$checks = explode(' ', $line[$i]);
-
+
if ( count($checks) > 0 ) {
-
+
$sql = sisplet_query("DELETE FROM srv_data_vrednost".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
-
- foreach ($checks AS $k => $v) {
-
- $vre = $vre_id[$id][$v];
-
- if ($vre != '') {
+
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
+ //ce obstaja vrednost in je checkbox nastavljen na 1, potem shrani v bazo vrednost
+ if ($vre != '' && $v == 1) {
$sql = sisplet_query("INSERT INTO srv_data_vrednost".$this->db_table." (spr_id, vre_id, usr_id, loop_id) VALUES ('$id', '$vre', '$usr_id', NULL)");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
}
}
}
-
+ } elseif ( in_array($tip[$id], array(6)) ) {
+
+ $checks = explode(' ', $line[$i]);
+
+ if ( count($checks) > 0 ) {
+
+ $sql = sisplet_query("DELETE FROM srv_data_grid".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //get number of grids for this question
+ $sqlg = sisplet_query("SELECT grids FROM srv_spremenljivka WHERE id='$id'");
+ $grids_num = mysqli_fetch_array($sqlg)['grids'];
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
+
+ //ce obstaja vrednost in je checkbox nastavljen na 1, potem shrani v bazo vrednost
+ if ($vre != '' && is_numeric($v) && $v > 0 && $v <= $grids_num) {
+ $sql = sisplet_query("INSERT INTO srv_data_grid".$this->db_table." (spr_id, vre_id, usr_id, grd_id, loop_id) VALUES ('$id', '$vre', '$usr_id', '$v', NULL)");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
+ }
+ }
} elseif ( in_array($tip[$id], array(7, 21, 8)) ) {
+ $value = $line[$i];
+
if ($tip[$id] == 21) {
$vre = $vre_id[$id];
} elseif ($tip[$id] == 7 || $tip[$id] == 8) {
$vre = 0;
+
+ if($tip[$id] == 8){
+ // Create a DateTime object from the input date string
+ $date = new DateTime($line[$i]);
+ // Format the date as "D.M.YYYY"
+ $value = $date->format('d.m.Y');
+ }
+
+ //brisi vse vejice in pusti samo zadnjo piko za decimalke
+ elseif($tip[$id] == 7){
+ // Replace all commas with dots
+ $value = str_replace(',', '.', $value);
+ // Remove all dots except the last one
+ $value = preg_replace('/\.(?=.*\.)/', '', $value);
+ }
}
// ker je primary nastavljen na ID, moramo najprej pobrisat
$sql = sisplet_query("DELETE FROM srv_data_text".$this->db_table." WHERE spr_id='$id' AND vre_id='$vre' AND usr_id='$usr_id'");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
- $sql = sisplet_query("INSERT INTO srv_data_text".$this->db_table." (spr_id, vre_id, text, usr_id) VALUES ('$id', '$vre', '$line[$i]', '$usr_id')");
+ $sql = sisplet_query("INSERT INTO srv_data_text".$this->db_table." (spr_id, vre_id, text, usr_id) VALUES ('$id', '$vre', '$value', '$usr_id')");
if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
}
}
@@ -757,8 +928,14 @@ class SurveyAppendMerge {
function upload_xls(){
global $lang, $site_url,$site_path,$global_user_id;
-
- echo '<fieldset><legend>'.$lang['srv_data_subnavigation_append'].'</legend>';
+
+ $do_merge = isset($_POST['do_merge']) ? (int)$_POST['do_merge'] : 0;
+
+ // prikazemo obvestilo in formo za potrditev
+ if ($do_merge == 1)
+ echo '<fieldset><legend>'.$lang['srv_data_subnavigation_merge'].'</legend>';
+ else
+ echo '<fieldset><legend>'.$lang['srv_data_subnavigation_append'].'</legend>';
if (isset($_POST['fields']) && count($_POST['fields']) > 0){
@@ -768,28 +945,32 @@ class SurveyAppendMerge {
&& (int)$_FILES['recipientsFile']['error'] == 0
&& (pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) == 'xls' || pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) == 'xlsx') )
{
-
+
$orig_parts = pathinfo($_FILES["recipientsFile"]["name"]);
$path_parts = pathinfo($_FILES["recipientsFile"]["tmp_name"]);
-
+
$fileName = $this->anketa.'_'.$global_user_id.'_'.$path_parts['filename'].'.'.$orig_parts['extension'];
$file = $site_path.'admin/survey/tmp/'.$fileName;
$move = move_uploaded_file($_FILES['recipientsFile']['tmp_name'], $file);
if ($move == true){
+ //$spreadsheet = new Spreadsheet();
+
#spremenljivke
$field_list = array();
- $sql = sisplet_query("SELECT s.id, s.variable, s.tip FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$this->anketa' AND s.tip IN (1, 2, 3, 7, 8, 21) ORDER BY g.vrstni_red, s.vrstni_red");
+ $sql = sisplet_query("SELECT s.id, s.variable, s.tip FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$this->anketa' AND s.tip IN (1, 2, 3, 7, 8, 21, 6) ORDER BY g.vrstni_red, s.vrstni_red");
while ($row = mysqli_fetch_array($sql)) {
- $field_list[$row['id']] = $row['variable'] . ' ('.($row['tip']==1?'radio':'').($row['tip']==2?'checkbox':'').($row['tip']==3?'dropdown':'').($row['tip']==21?'text':'').($row['tip']==7?'number':'').($row['tip']==8?'date':'').')';
+ $field_list[$row['id']] = $row['variable'] . ' ('.($row['tip']==1?'radio':'').($row['tip']==2?'checkbox':'').($row['tip']==3?'dropdown':'').($row['tip']==21?'text':'').($row['tip']==7?'number':'').($row['tip']==8?'date':'').($row['tip']==6?'table':'').')';
}
-
- include_once("./excel/PHPExcel.php");
- require_once("./excel/PHPExcel/IOFactory.php");
$result = array();
- $objPHPExcel = PHPExcel_IOFactory::load($file);
- $objWorksheet = $objPHPExcel->getActiveSheet();
+ $objPHPExcel = array();
+ if($orig_parts['extension'] == 'xls')
+ $objPHPExcel = IOFactory::createReader('Xls')->load($file);
+ else
+ $objPHPExcel = IOFactory::load($file);
+
+ $objWorksheet = $objPHPExcel->getSheet(0);
// Get worksheet dimensions
$highestRow = $objWorksheet->getHighestRow();
@@ -802,9 +983,11 @@ class SurveyAppendMerge {
$columns[] = $column;
}
- echo '<form id="append_xml" name="resp_uploader" enctype="multipart/form-data" method="POST" action="'.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m=append_xls">';
+ echo '<form id="append_xml" name="resp_uploader" enctype="multipart/form-data" method="POST" action="'.$site_url.'admin/survey/index.php?anketa='.$this->anketa.'&a=data&m='
+ .((isset($_POST['do_merge']) && $_POST['do_merge'] == 1) ? 'merge_xls' : 'append_xls').'">';
echo '<input type="hidden" name="anketa" value="'.$this->anketa.'">';
echo '<input type="hidden" name="file" value="'.$fileName.'">';
+ echo '<input type="hidden" name="merge" value="'.(isset($_POST["merge"]) ? $_POST["merge"] : 0).'">';
foreach ($_POST['fields'] AS $field)
{
echo '<input id="'.$field.'_chk" type="hidden" name="fields[]" value="'.$field.'" />';
@@ -813,12 +996,12 @@ class SurveyAppendMerge {
echo $lang['srv_append_xls_note'];
echo '<div id="inv_field_container">';
- echo '<div class="append_step">';
+ echo '<div class="appendmerge_indent">';
echo '<ul class="connectedSortable">';
foreach ($_POST['fields'] AS $spr_id)
{
- echo '<li class="inv_field_enabled"><label>'.$field_list[$spr_id].'</label></li>';
+ echo '<li class="inv_field_enabled nocursor"><label>'.$field_list[$spr_id].'</label></li>';
}
echo '</ul>';
echo '</div>';
@@ -826,32 +1009,41 @@ class SurveyAppendMerge {
if (count($columns) > 0){
- echo $lang['srv_append_xls_step1'];
+ echo '<p>'.$lang['srv_append_xls_step1'].'</p>';
echo '<div id="inv_field_container">';
- echo '<div class="append_step">';
+ echo '<div class="appendmerge_indent">';
echo '<ul class="connectedSortable">';
foreach ($columns AS $column){
- echo '<li><label><input type="checkbox" name="xls_column[]" value="'.$column.'">'.$column.'</label></li>';
+ echo '<li><input type="checkbox" name="xls_column[]" id="'.$column.'" value="'.$column.'"><label for="'.$column.'">'.$column.'</label></li>';
}
echo '</ul>';
echo '</div>';
echo '</div>';
}
+ echo $lang['srv_append_xls_step2'];
- echo $lang['srv_append_xls_step1'];
+ echo '<div class="appendmerge_indent">';
- echo '<div class="append_step">';
- echo '<p>'.$lang['srv_append_xls_step2_begin'].': <input name="start_row" type="number" min="1" max="'.(int)$highestRow.'" value="1"></p>';
- echo '<p>'.$lang['srv_append_xls_step2_end'].': <input name="end_row" type="number" min="1" max="'.(int)$highestRow.'" value="'.(int)$highestRow.'"></p>';
- echo '<span id="inv_upload_recipients_nosbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" onclick="$(\'#append_xml\').submit();">'.$lang['srv_inv_btn_add_recipients_add'].'</a></span>';
- echo '<br /><br />';
+ echo '<div class="setting_holder">';
+ echo '<label>'.$lang['srv_append_xls_step2_begin'].'</label>';
+ echo '<input name="start_row" type="number" min="1" max="'.(int)$highestRow.'" value="1">';
+ echo '</div>';
+
+ echo '<div class="setting_holder">';
+ echo '<label>'.$lang['srv_append_xls_step2_end'].'</label>';
+ echo '<input name="end_row" type="number" min="1" max="'.(int)$highestRow.'" value="'.(int)$highestRow.'">';
+ echo '</div>';
+
+ echo '<div class="button_holder top16 bottom0">';
+ echo '<button class="medium blue" onclick="$(\'#append_xml\').submit();">'.($do_merge == 1 ? $lang['srv_merge_add']:$lang['srv_append_add']).'</button>';
+ echo '</div>';
+
echo '</div>';
- echo '</form>';
- }
+ echo '</form>'; }
}
elseif(pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) != 'xls' && pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) != 'xlsx'){
echo $lang['srv_iz_excela_xls_error'];
@@ -862,7 +1054,7 @@ class SurveyAppendMerge {
}
else
{
- echo 'No list';
+ echo $lang['srv_append-merge_required_questions'];
}
echo '</fieldset>';
@@ -873,29 +1065,27 @@ class SurveyAppendMerge {
$fileName = $_POST['file'];
$file = $site_path.'admin/survey/tmp/'.$fileName;
+ $orig_parts = pathinfo($fileName);
$columns = $_POST['xls_column'];
$fields = $_POST['fields'];
- $do_merge = (int)$_POST['do_merge'];
-
if (file_exists($file)
&& is_array($columns) && count($columns ) > 0
&& is_array($fields) && count($fields ) > 0)
{
$do = 1;
-
- include_once("./excel/PHPExcel.php");
- require_once("./excel/PHPExcel/IOFactory.php");
- $objPHPExcel = PHPExcel_IOFactory::load($file);
- $objWorksheet = $objPHPExcel->getActiveSheet();
+ if($orig_parts['extension'] == 'xls')
+ $objPHPExcel = IOFactory::createReader('Xls')->load($file);
+ else
+ $objPHPExcel = IOFactory::load($file);
+
+ $objWorksheet = $objPHPExcel->getSheet(0);
// Get worksheet dimensions
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
-
-
$start_row = min($_POST['start_row'],$highestRow);
$end_row = min($_POST['end_row'],$highestRow);
@@ -912,7 +1102,7 @@ class SurveyAppendMerge {
while ($row = mysqli_fetch_array($sql)) {
$tip[$row['id']] = $row['tip'];
- if ( in_array($row['tip'], array(1, 2, 3)) ) {
+ if ( in_array($row['tip'], array(1, 2, 3, 6)) ) {
$s = sisplet_query("SELECT id, variable FROM srv_vrednost WHERE spr_id='$row[id]' ORDER BY vrstni_red");
while ($r = mysqli_fetch_array($s)) {
@@ -938,14 +1128,15 @@ class SurveyAppendMerge {
$line[] = mysqli_real_escape_string($GLOBALS['connect_db'], $value);
#$line[] = mysqli_real_escape_string($GLOBALS['connect_db'], $objWorksheet->getCell("$column$row")->getValue());
}
- $added += $this->insertLine($line,$fields,$tip,$vre_id,$do);
+ $added += $this->insertLine($line,$fields,$tip,$vre_id,$do, true);
}
$result = $added;
}
else
{
- echo $lang['srv_append-merge_error_file'];
+ if(!file_exists($file)) $result = -1;
+ else $result = -3;
}
if ($result == -1) {
@@ -958,10 +1149,7 @@ class SurveyAppendMerge {
// prikazemo obvestilo in formo za potrditev
- if ($do_merge == 1)
- echo '<fieldset><legend>'.$lang['srv_data_subnavigation_merge'].'</legend>';
- else
- echo '<fieldset><legend>'.$lang['srv_data_subnavigation_append'].'</legend>';
+ echo '<fieldset><legend>'.$lang['srv_data_subnavigation_append'].'</legend>';
if ( ! $do ) {
@@ -973,18 +1161,11 @@ class SurveyAppendMerge {
echo '<p>'.$output.'</p>';
else
echo '<p>'.$lang['merge_error_value'].'.</p>';
-
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_gray" onclick="append_submit_close(); return false;"><span>'.$lang['back'].'</span></a></span>';
-
} else {
echo '<h2>'.$lang['srv_potrditev'].'</h2>';
- if ($do_merge == 0)
- echo '<p>'.$lang['srv_append-merge_process_1'].' '.$result.' '.$lang['srv_append-merge_process_2'].'. </p>';
- else
- echo '<p>'.$lang['srv_append-merge_process_o_1'].' '.$result.' '.$lang['srv_append-merge_process_o_2'].'</p>';
-
+ echo '<p>'.$lang['srv_append-merge_process_1'].' '.$result.' '.$lang['srv_append-merge_process_2'].'. </p>';
echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" onclick="append_submit(1); return false;"><span>'.$lang['srv_potrdi'].'</span></a></span>';
echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_gray" onclick="append_submit_close(); return false;"><span>'.$lang['srv_cancel'].'</span></a></span>';
@@ -993,15 +1174,333 @@ class SurveyAppendMerge {
// shranjeno, prikazemo resultat
} else {
- echo '<h2>'.$lang['fin_import_ok'].'</h2>';
+ echo '<p class="bold bottom16">'.$lang['fin_import_ok'].'</p>';
echo '<p>'.$output.'</p>';
echo '<p>'.$lang['fin_import_ok_text'].'</p>';
+
+ echo '<div class="button_holder top16 bottom0">';
+ echo '<button class="medium blue" onclick="window.location.href=\'index.php?anketa='.$this->anketa.'&a=data\'">'.$lang['data_show'].'</button>';
+ echo '</div>';
+ }
+
+ echo '</fieldset>';
+ }
+
+ function merge_xls() {
+ global $lang, $site_url,$site_path,$global_user_id;
+
+ $fileName = $_POST['file'];
+ $file = $site_path.'admin/survey/tmp/'.$fileName;
+ $orig_parts = pathinfo($fileName);
+
+ $columns = $_POST['xls_column'];
+ $fields = $_POST['fields'];
+
+ $merge = isset($_POST['merge']) ? (int)$_POST['merge'] : 0;
+
+ if (count($fields) <= 0) return -1;
+ if ($merge <= 0) return -2;
+
+ if (file_exists($file)
+ && is_array($columns) && count($columns ) > 0
+ && is_array($fields) && count($fields ) > 0)
+ {
+ $do = 1;
+
+ if($orig_parts['extension'] == 'xls')
+ $objPHPExcel = IOFactory::createReader('Xls')->load($file);
+ else
+ $objPHPExcel = IOFactory::load($file);
+
+ $objWorksheet = $objPHPExcel->getSheet(0);
+
+ // Get worksheet dimensions
+ $highestRow = $objWorksheet->getHighestRow();
+ $highestColumn = $objWorksheet->getHighestColumn();
+
+ $start_row = min($_POST['start_row'],$highestRow);
+ $end_row = min($_POST['end_row'],$highestRow);
+ if ($end_row < $start_row)
+ {
+ $end_row = $start_row;
+ }
+
+ #podatki spremenljivk
+ $ids = array();
+ $tip = array();
+ $vre_id = array();
+ $sql = sisplet_query("SELECT id, tip FROM srv_spremenljivka WHERE id IN (".implode(',', $fields).")");
+ while ($row = mysqli_fetch_array($sql)) {
+ $tip[$row['id']] = $row['tip'];
+
+ if ( in_array($row['tip'], array(1, 2, 3, 6)) ) {
+
+ $s = sisplet_query("SELECT id, variable FROM srv_vrednost WHERE spr_id='$row[id]' ORDER BY vrstni_red");
+ while ($r = mysqli_fetch_array($s)) {
+ $vre_id[$row['id']][$r['variable']] = $r['id'];
+ }
+
+ } elseif ( in_array($row['tip'], array(7, 21,8)) ) {
+ // v tekstovno spremenljivko pisemo v prvo polje
+ $s = sisplet_query("SELECT id FROM srv_vrednost WHERE spr_id='$row[id]' ORDER BY vrstni_red");
+ $r = mysqli_fetch_array($s);
+ $vre_id[$row['id']] = $r['id'];
+ }
+ }
+
+ $merge_key = array_keys($fields, $merge);
+ $merge_key = $merge_key[0];
+
+ $subarray = array_slice($fields, 0, $merge_key);
+
+ //stej offset stolpcev v excel, v primeru da ena spremenljivka ni en stolpec (npr. checkboxi)
+ $column_offset = 0;
+ foreach($subarray as $spr_id){
+ if($tip[$spr_id] == 2){
+ //pri excel prestejemo vrednosti v spremenljivki
+ $sql = sisplet_query("SELECT COUNT(*) as cnt FROM srv_vrednost WHERE spr_id='$spr_id'");
+ if ($sql) {
+ $row1 = mysqli_fetch_array($sql);
+ //izrezemo stolpce, ki so vrednosti spremenljivke checkbox
+ $column_offset += $row1['cnt'];
+ }
+ //odstejemo ena, ker sama spremenljivka v originalnem arrayu je en column (vse vrednosti pod eno spremenljivko)
+ $column_offset--;
+ }
+ }
+
+ //pristejemo offset k merge_key
+ $merge_key += $column_offset;
+
+ $merge_tip = $tip[$merge];
+
+ $merged = 0;
- echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" href="index.php?anketa='.$this->anketa.'&a=data"><span>'.$lang['data_show'].'</span></a></span>';
+ for ($row = $start_row; $row <= $end_row; ++$row)
+ {
+ $line=array();
+ foreach ($columns AS $column)
+ {
+ $value = $objWorksheet->getCell("$column$row")->getCalculatedValue();
+ $line[] = mysqli_real_escape_string($GLOBALS['connect_db'], $value);
+ #$line[] = mysqli_real_escape_string($GLOBALS['connect_db'], $objWorksheet->getCell("$column$row")->getValue());
+ }
+
+ if ($line[$merge_key] != '') {
+
+ // poiscemo userja za merge
+ if ( in_array($merge_tip, array(1, 2, 3)) ) {
+
+ $sqlu = sisplet_query("SELECT usr_id FROM srv_data_vrednost".$this->db_table." LEFT JOIN srv_user ON srv_user.id = srv_data_vrednost".$this->db_table.".usr_id WHERE spr_id='$merge' AND vre_id='".$vre_id[$merge][$line[$merge_key]]."' AND deleted = '0'");
+
+ } elseif ( in_array($merge_tip, array(7, 21, 8)) ) {
+ if ($merge_tip == 21) {
+ $vre = $vre_id[$merge];
+ } elseif ($merge_tip == 7 || $merge_tip == 8) {
+ $vre = 0;
+ }
+
+ $sqlu = sisplet_query("SELECT usr_id FROM srv_data_text".$this->db_table." WHERE spr_id='$merge' AND vre_id='$vre' AND text='$line[$merge_key]'");
+ }
+ if (!$sqlu) echo mysqli_error($GLOBALS['connect_db']);
+
+ while ($rowu = mysqli_fetch_array($sqlu)) {
+
+ if ($do) {
+ $usr_id = $rowu['usr_id'];
+
+ $s = sisplet_query("UPDATE srv_user SET time_edit=NOW() WHERE id='$usr_id'");
+ if (!$s) echo mysqli_error($GLOBALS['connect_db']);
+
+ $this->usr_ids[] = $usr_id;
+ }
+
+ $i = 0;
+ // pri excel uvozu so stolpci posebej pri check za vsak check, to je potem difference med stevcem spremenljivk ($i) in dejanskih stolpcev (i+vre_diff)
+ $vre_diff = 0;
+ foreach ($fields AS $id) {
+
+ if ($id != $merge) {
+
+ if ($do) {
+
+ if ( in_array($tip[$id], array(1, 3)) ) {
+
+ $vre = isset($vre_id[$id][$line[$i+$vre_diff]]) ? $vre_id[$id][$line[$i+$vre_diff]] : '';
+
+ if ($vre != '') {
+ $sql = sisplet_query("DELETE FROM srv_data_vrednost".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ $sql = sisplet_query("INSERT INTO srv_data_vrednost".$this->db_table." (spr_id, vre_id, usr_id, loop_id) VALUES ('$id', '$vre', '$usr_id', NULL)");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
+
+ } elseif ( in_array($tip[$id], array(2)) ) {
+
+ //pri excel prestejemo vrednosti v spremenljivki
+ $sql = sisplet_query("SELECT COUNT(*) as cnt FROM srv_vrednost WHERE spr_id='$id'");
+ if ($sql) {
+ $row1 = mysqli_fetch_array($sql);
+ //izrezemo stolpce, ki so vrednosti spremenljivke checkbox
+ $checks = array_slice($line, $i+$vre_diff, $row1['cnt']);
+ }
+
+ if ( count($checks) > 0 ) {
+
+ $sql = sisplet_query("DELETE FROM srv_data_vrednost".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
+
+ //ce obstaja vrednost in je checkbox nastavljen na 1, potem shrani v bazo vrednost
+ if ($vre != '' && $v == 1) {
+ $sql = sisplet_query("INSERT INTO srv_data_vrednost".$this->db_table." (spr_id, vre_id, usr_id, loop_id) VALUES ('$id', '$vre', '$usr_id', NULL)");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
+ }
+
+ //pristej vrednosti k offsetu kot differencial stolpcev in odstej ena zaradi spremenljivke
+ if(count($checks) > 1) $vre_diff += count($checks)-1;
+ }
+ } elseif ( in_array($tip[$id], array(6)) ) {
+
+ //pri excel prestejemo vrednosti v spremenljivki
+ $sql = sisplet_query("SELECT COUNT(*) as cnt FROM srv_vrednost WHERE spr_id='$id'");
+ if ($sql) {
+ $row1 = mysqli_fetch_array($sql);
+ //izrezemo stolpce, ki so vrednosti spremenljivke checkbox
+ $checks = array_slice($line, $i+$vre_diff, $row1['cnt']);
+ }
+
+ if ( count($checks) > 0 ) {
+
+ $sql = sisplet_query("DELETE FROM srv_data_grid".$this->db_table." WHERE spr_id='$id' AND usr_id='$usr_id' AND loop_id IS NULL");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+
+ //pridobi samo array vseh vrednosti po vrsti (id-ji)
+ $vrednosti_ids = array_values($vre_id[$id]);
+
+ //get number of grids for this question
+ $sqlg = sisplet_query("SELECT grids FROM srv_spremenljivka WHERE id='$id'");
+ $grids_num = mysqli_fetch_array($sqlg)['grids'];
+
+ //pojdi cez vse checkbox vrednosti
+ foreach ($checks AS $index => $v) {
+ //preveri, ce obstaja index vrednosti v array vrednosti
+ $vre = isset($vrednosti_ids[$index]) ? $vrednosti_ids[$index] : '';
+
+ //ce obstaja vrednost potem shrani v bazo
+ if ($vre != '' && is_numeric($v) && $v > 0 && $v <= $grids_num) {
+ $sql = sisplet_query("INSERT INTO srv_data_grid".$this->db_table." (spr_id, vre_id, usr_id, grd_id, loop_id) VALUES ('$id', '$vre', '$usr_id', '$v', NULL)");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
+ }
+
+ //pristej vrednosti k offsetu kot differencial stolpcev in odstej ena zaradi spremenljivke
+ if(count($checks) > 1) $vre_diff += count($checks)-1;
+ }
+ } elseif ( in_array($tip[$id], array(7, 21, 8)) ) {
+
+ $value = $line[$i+$vre_diff];
+
+ if ($tip[$id] == 21) {
+ $vre = $vre_id[$id];
+ } elseif ($tip[$id] == 7 || $tip[$id] == 8) {
+ $vre = 0;
+
+ if($tip[$id] == 8){
+ // Create a DateTime object from the input date string
+ $date = new DateTime($value);
+ // Format the date as "D.M.YYYY"
+ $value = $date->format('d.m.Y');
+ }
+
+ //brisi vse vejice in pusti samo zadnjo piko za decimalke
+ elseif($tip[$id] == 7){
+ // Replace all commas with dots
+ $value = str_replace(',', '.', $value);
+ // Remove all dots except the last one
+ $value = preg_replace('/\.(?=.*\.)/', '', $value);
+ }
+ }
+
+ // ker je primary nastavljen na ID, moramo najprej pobrisat
+ $sql = sisplet_query("DELETE FROM srv_data_text".$this->db_table." WHERE spr_id='$id' AND vre_id='$vre' AND usr_id='$usr_id'");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ $sql = sisplet_query("INSERT INTO srv_data_text".$this->db_table." (spr_id, vre_id, text, usr_id) VALUES ('$id', '$vre', '$value', '$usr_id')");
+ if (!$sql) echo mysqli_error($GLOBALS['connect_db']);
+ }
+ }
+ }
+ $i++;
+ }
+ $merged++;
+ }
+ }
+ }
+
+ $result = $merged;
+ }
+ else
+ {
+ if(!file_exists($file)) $result = -1;
+ else $result = -3;
+ }
+
+ if ($result == -1) {
+ $output = $lang['srv_append-merge_required_field'];
+ } elseif ($result == -2) {
+ $output = $lang['srv_append-merge_required_id'];
+ } elseif ($result == -3) {
+ $output = $lang['srv_append-merge_required_data'];
+ } elseif ($result >= 0) {
+ $output = $lang['srv_append-merge_merged_1'].' '.$result.' '.$lang['srv_append-merge_merged_2'];
+ }
+
+
+ // prikazemo obvestilo in formo za potrditev
+ echo '<fieldset><legend>'.$lang['srv_data_subnavigation_merge'].'</legend>';
+
+ if ( ! $do ) {
+
+ // napaka
+ if ($result <= 0) {
+ echo '<h2>'.$lang['error'].'</h2>';
+
+ if ($result < 0)
+ echo '<p>'.$output.'</p>';
+ else
+ echo '<p>'.$lang['merge_error_value'].'.</p>';
+ } else {
+
+ echo '<h2>'.$lang['srv_potrditev'].'</h2>';
+
+ echo '<p>'.$lang['srv_append-merge_process_o_1'].' '.$result.' '.$lang['srv_append-merge_process_o_2'].'</p>';
+
+ echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_orange" onclick="append_submit(1); return false;"><span>'.$lang['srv_potrdi'].'</span></a></span>';
+ echo '<span id="inv_upload_recipients_no_sbmt" class="buttonwrapper floatLeft"><a class="ovalbutton ovalbutton_gray" onclick="append_submit_close(); return false;"><span>'.$lang['srv_cancel'].'</span></a></span>';
+ }
+
+ // shranjeno, prikazemo resultat
+ } else {
+
+ echo '<p class="bold bottom16">'.$lang['fin_import_ok'].'</p>';
+
+ echo '<p>'.$output.'</p>';
+ //echo '<p>'.$lang['fin_import_ok_text'].'</p>';
+
+ echo '<div class="button_holder top16 bottom0">';
+ echo '<button class="medium blue" onclick="window.location.href=\'index.php?anketa='.$this->anketa.'&a=data\'">'.$lang['data_show'].'</button>';
+ echo '</div>';
}
- echo '<br /><br />';
echo '</fieldset>';
}
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-21 17:37:17 +0100