anketa = $anketa; SurveyInfo::getInstance()->SurveyInit($this->anketa); $this->db_table = SurveyInfo::getInstance()->getSurveyArchiveDBString(); } function display ($merge = false) { if ( count($_POST) == 0 ) { echo '
'; $this->displayAppendMerge($merge); echo '
'; } else { $this->do_append_merge(); } } function displayAppendMerge ( $merge = false ) { 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, 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':'').($row['tip']==6?'table':'').')'; $field_types[$row['id']] = $row['tip']; } $import_type = isset($_POST['import_type']) ? (int)$_POST['import_type'] : 2; session_start(); // Append if (!$merge){ echo '
'.$lang['srv_data_subnavigation_append'].''; echo '
'; echo ''; echo ''; echo ''; // Opis združevanja podatkov echo '

'.$lang['srv_append_note'].'

'; // Korak 1 echo '

'.$lang['srv_append_step1'].'

'; echo '
'; echo '
    '; echo '
  • '.$lang['srv_append_step1_notea'].'
    • '; echo '
  • '.$lang['srv_append_step1_noteb'].'
    • '; echo '
  • '.$lang['srv_append_step1_notec'].'
    • '; echo '
'; echo '
'; echo '
    '; $field_lang = array(); if (count($field_list ) > 0) { foreach ($field_list AS $field => $text_label) { # tukaj polja niso izbrana $is_selected = false; # če je polje obkljukano $css = $is_selected ? ' class="inv_field_enabled"' : ''; # labela sproži klik checkboxa $label_for = ' for="'.$field.'_chk"'; echo '
  • '; echo ''; echo ''.$text_label.''; echo '
    • '; if ($is_selected == true) { $field_lang[] = $text_label; } } } echo '
'; echo '
'; echo '

'.$lang['srv_append_step1_noted'].'

'; echo '
'; // Korak 2 echo '

'.$lang['srv_append_step2'].'

'; echo '
'; echo ''; echo '
'; echo ''.$lang['srv_appendmerge_source'].''; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo ''; echo '
'; # iz seznama echo ''; # id=inv_import_list # iz datoteke echo ''; # id=inv_import_file echo '
'; echo ''; echo '
'; $d = new Dostop(); if ($d->checkDostopSub('edit')){ echo ''; printf($lang['src_coding_alert'], $this->anketa); echo ''; } echo '
'; //echo ''; # id=inv_import_list_container echo '
'; } // Merge else{ echo '
'.$lang['srv_data_subnavigation_merge'].''; echo '
'; echo ''; echo ''; echo ''; //Opis združevanja podatkov echo '

'.$lang['srv_merge_note'].'

'; // Korak 1 echo '

'.$lang['srv_merge_step1'].'

'; echo '
'; echo '
    '; echo '
  • '.$lang['srv_merge_step1_notea'].'
    • '; echo '
  • '.$lang['srv_merge_step1_noteb'].'
    • '; echo '
  • '.$lang['srv_merge_step1_notec'].'
    • '; echo '
'; echo '
'; echo '
    '; $field_lang = array(); if (count($field_list ) > 0) { foreach ($field_list AS $field => $text_label) { # tukaj polja niso izbrana $is_selected = false; # če je polje obkljukano $css = $is_selected ? ' class="inv_field_enabled"' : ''; # labela sproži klik checkboxa $label_for = ' for="'.$field.'_chk"'; echo '
  • '; echo ''; echo ''.$text_label.''; echo '
    • '; if ($is_selected == true) { $field_lang[] = $text_label; } } } echo '
'; echo '
'; echo '

'.$lang['srv_merge_step1_noted'].'

'; echo '
'; // Korak 2 echo '

'.$lang['srv_merge_step2'].'

'; echo '
'; echo ''; echo '
'; echo ''.$lang['srv_appendmerge_source'].''; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo '
'; // Korak 3 echo '

'.$lang['srv_merge_step3'].'

'; echo '
'; echo ''; echo '

'.$lang['srv_izberite_identifikator'].'

'; echo '
    '; echo '
'; echo '
'; # iz seznama echo ''; # id=inv_import_list # iz datoteke echo ''; # id=inv_import_file echo '
'; echo ''; echo '
'; $d = new Dostop(); if ($d->checkDostopSub('edit')){ echo ''; printf($lang['src_coding_alert'], $this->anketa); echo ''; } echo '
'; echo '
'; } } function do_append_merge() { global $lang; global $site_url; global $global_user_id; # dodamo uporabnike $fields = isset($_POST['fields']) ? $_POST['fields'] : array(); $recipients_list = mysql_real_unescape_string( $_POST['inv_recipients_list'] ); $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) { $file_name = $_FILES["recipientsFile"]["tmp_name"]; $fh = @fopen($file_name, "rb"); if ($fh) { $recipients_list = fread($fh, filesize($file_name)); fclose($fh); } if (isset ($_POST['recipientsDelimiter'])) { $recipients_list = str_replace($_POST['recipientsDelimiter'], "|~|", $recipients_list); } else { $recipients_list = str_replace(",", "|~|", $recipients_list); } } // append if ($do_merge == 0) { $result = $this->appendData($do, $fields, $recipients_list); if ($result == -1) { $output = $lang['srv_append-merge_required_field']; } elseif ($result == -3) { $output = $lang['srv_append-merge_required_data']; } elseif ($result >= 0) { $output = $lang['srv_append-merge_added_1'].' '.$result.' '.$lang['srv_append-merge_added_2']; } // merge } elseif ($do_merge == 1) { $result = $this->mergeData($do, $fields, $recipients_list, $merge); 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 if ($import_type == 1){ if ($do_merge == 1) echo '
'.$lang['srv_data_subnavigation_merge'].''; else echo '
'.$lang['srv_data_subnavigation_append'].''; } if ( ! $do ) { // napaka if ($result <= 0) { echo '

'.$lang['error'].'

'; echo '
✕
'; if ($result < 0) echo '

'.$output.'

'; else echo '

'.$lang['srv_append-merge_error_value'].'

'; echo '
'; echo ''; echo '
'; } else { echo '

'.$lang['srv_potrditev'].'

'; echo '
✕
'; if ($do_merge == 0) echo '

'.$lang['srv_append-merge_process_1'].' '.$result.' '.$lang['srv_append-merge_process_2'].'. '.'

'; else echo '

'.$lang['srv_append-merge_process_o_1'].' '.$result.' '.$lang['srv_append-merge_process_o_2'].'

'; echo '
'; echo ''; echo ''; echo '
'; } // shranjeno, prikazemo resultat } else { echo '

'.$lang['fin_import_ok'].'

'; echo '
✕
'; echo '

'.$output.'

'; if ($do_merge == 0) echo '

'.$lang['srv_append-merge_fin'].'

'; echo '
'; echo ''; echo ''; echo '
'; } if ($import_type == 1){ echo '
'; } } private $usr_ids = array(); function appendData($do, $fields, $rawdata) { if ($do) TrackingClass::update($this->anketa, 1); if (false) { echo 'dumping for append:'; echo '
';
			print_r($fields);
			echo '
'; echo '
';
			print_r($rawdata);
			echo '
'; } $data = explode("\n", $rawdata); if (count($fields) <= 0) return -1; if ($rawdata == '') return -3; if (count($data) <= 0) return -3; $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']; } } $added = 0; foreach ($data AS $dataline) { $line = explode(';', $dataline); foreach ($line AS $key => $val) { $line[$key] = trim($val); } $added += $this->insertLine($line,$fields,$tip,$vre_id,$do); } if ($do) SurveyPostProcess::forceRefreshData($this->anketa); return $added; } 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')"); if (!$sql) echo mysqli_error($GLOBALS['connect_db']); $usr_id = mysqli_insert_id($GLOBALS['connect_db']); } 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 = 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 = 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']); //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']); } } //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)) ) { if ($tip[$id] == 21) { $vre = $vre_id[$id]; } elseif ($tip[$id] == 7 || $tip[$id] == 8) { $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"); // 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); } $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++; } return $added; } function mergeData($do, $fields, $rawdata, $merge) { if ($do) TrackingClass::update($this->anketa, 1); if (false) { echo 'dumping for merge:'; echo '
';
			print_r($fields);
			echo '
'; echo '
';
			print_r($rawdata);
			echo '
'; echo '
';
			print_r($merge);
			echo '
'; } if (count($fields) <= 0) return -1; if ($merge <= 0) return -2; $merge_key = array_keys($fields, $merge); $merge_key = $merge_key[0]; $data = explode("\n", $rawdata); if ($rawdata == '') return -3; if (count($data) <= 0) return -3; $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_tip = $tip[$merge]; $merged = 0; foreach ($data AS $dataline) { $line = explode(';', $dataline); foreach ($line AS $key => $val) { $line[$key] = trim($val); } 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." 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']); 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; foreach ($fields AS $id) { if ($id != $merge) { if ($do) { if ( in_array($tip[$id], array(1, 3)) ) { $vre = $vre_id[$id][$line[$i]]; 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)) ) { $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']); //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', '$value', '$usr_id')"); if (!$sql) echo mysqli_error($GLOBALS['connect_db']); } } } $i++; } $merged++; } } } if ($do) SurveyPostProcess::forceRefreshData($this->anketa); return $merged; } function upload_xls(){ global $lang, $site_url,$site_path,$global_user_id; $do_merge = isset($_POST['do_merge']) ? (int)$_POST['do_merge'] : 0; // prikazemo obvestilo in formo za potrditev if ($do_merge == 1) echo '
'.$lang['srv_data_subnavigation_merge'].''; else echo '
'.$lang['srv_data_subnavigation_append'].''; if (isset($_POST['fields']) && count($_POST['fields']) > 0){ if (isset($_FILES['recipientsFile']) && is_array($_FILES['recipientsFile']) && isset($_FILES['recipientsFile']['size']) && $_FILES['recipientsFile']['size'] > 0 && (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, 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':'').($row['tip']==6?'table':'').')'; } $result = array(); $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(); $highestColumn = $objWorksheet->getHighestColumn(); $columns = array(); $highestColumn++; for ($column = 'A'; $column != $highestColumn; $column++) { $columns[] = $column; } echo '
'; echo ''; echo ''; echo ''; foreach ($_POST['fields'] AS $field) { echo ''; } echo $lang['srv_append_xls_note']; echo '
'; echo '
'; echo '
    '; foreach ($_POST['fields'] AS $spr_id) { echo '
    • '; } echo '
'; echo '
'; echo '
'; if (count($columns) > 0){ echo '

'.$lang['srv_append_xls_step1'].'

'; echo '
'; echo '
'; echo '
    '; foreach ($columns AS $column){ echo '
    • '; } echo '
'; echo '
'; echo '
'; } echo $lang['srv_append_xls_step2']; echo '
'; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo ''; echo ''; echo '
'; echo '
'; echo ''; echo '
'; echo '
'; echo '
'; } } elseif(pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) != 'xls' && pathinfo($_FILES['recipientsFile']['name'],PATHINFO_EXTENSION) != 'xlsx'){ echo $lang['srv_iz_excela_xls_error']; } else{ echo 'File error. #'.(int)$_FILES['recipientsFile']['error']; } } else { echo $lang['srv_append-merge_required_questions']; } echo '
'; } function append_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']; 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']; } } $added = 0; 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()); } $added += $this->insertLine($line,$fields,$tip,$vre_id,$do, true); } $result = $added; } else { if(!file_exists($file)) $result = -1; else $result = -3; } if ($result == -1) { $output = $lang['srv_append-merge_required_field']; } elseif ($result == -3) { $output = $lang['srv_append-merge_required_data']; } elseif ($result >= 0) { $output = $lang['srv_append-merge_added_1'].' '.$result.' '.$lang['srv_append-merge_added_2']; } // prikazemo obvestilo in formo za potrditev echo '
'.$lang['srv_data_subnavigation_append'].''; if ( ! $do ) { // napaka if ($result <= 0) { echo '

'.$lang['error'].'

'; if ($result < 0) echo '

'.$output.'

'; else echo '

'.$lang['merge_error_value'].'.

'; } else { echo '

'.$lang['srv_potrditev'].'

'; echo '

'.$lang['srv_append-merge_process_1'].' '.$result.' '.$lang['srv_append-merge_process_2'].'.

'; echo ''.$lang['srv_potrdi'].''; echo ''.$lang['srv_cancel'].''; } // shranjeno, prikazemo resultat } else { echo '

'.$lang['fin_import_ok'].'

'; echo '

'.$output.'

'; echo '

'.$lang['fin_import_ok_text'].'

'; echo '
'; echo ''; echo '
'; } echo '
'; } 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; 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 '
'.$lang['srv_data_subnavigation_merge'].''; if ( ! $do ) { // napaka if ($result <= 0) { echo '

'.$lang['error'].'

'; if ($result < 0) echo '

'.$output.'

'; else echo '

'.$lang['merge_error_value'].'.

'; } else { echo '

'.$lang['srv_potrditev'].'

'; echo '

'.$lang['srv_append-merge_process_o_1'].' '.$result.' '.$lang['srv_append-merge_process_o_2'].'

'; echo ''.$lang['srv_potrdi'].''; echo ''.$lang['srv_cancel'].''; } // shranjeno, prikazemo resultat } else { echo '

'.$lang['fin_import_ok'].'

'; echo '

'.$output.'

'; //echo '

'.$lang['fin_import_ok_text'].'

'; echo '
'; echo ''; echo '
'; } echo '
'; } function ajax() { $this->anketa = $_REQUEST['anketa']; if ($_GET['a'] == 'change_import_type') { $this->ajax_change_import_type(); } elseif ($_GET['a'] == 'add_recipients') { $this->ajax_addRecipients(); } elseif ($_GET['a'] == 'submit') { $this->ajax_submit(); } } function ajax_change_import_type() { $this->displayAppendMerge(); } function ajax_addRecipients() { $this->do_append_merge(); } function ajax_submit () { $this->do_append_merge(); } } ?>