<?php
/**
* Created on Jun.2010
*
* @author: Gorazd Vesleič
*
* @desc: za izvoze
*
* funkcije:
* - Init() - inicializacija
*
*/
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
DEFINE (NEW_LINE, "\n");
DEFINE (TMP_EXT, '.tmp');
DEFINE (STR_DLMT, '|');
DEFINE (DAT_EXT, '.dat');
if (!defined('EXPORT_FOLDER')) define("EXPORT_FOLDER", "admin/survey/SurveyData");
class SurveyExport
{
private $sid = null; // id ankete
private $folder = ''; # pot do folderja
private $inited = false; # ali smo razred inicializirali
private $SMV = false; # manjkajoče vrednosti
private $survey = null; # podatki ankete
private $db_table = ''; # ali se uporablja aktivna tabela
private $_CURRENT_STATUS_FILTER = ''; # filter po statusih, privzeto izvažamo 6 in 5
public $_FILTRED_VARIABLES = array(); # filter po spremenljivkah
# ali obstaja datoteka z podatki in ali je zadnja verzija
private $_fileStatus = FILE_STATUS_NO_DATA; # (FILE_STATUS_OK,FILE_STATUS_OLD,FILE_STATUS_NO_FILE,FILE_STATUS_NO_DATA)
private $_dataFileName = null; # ime obstoječe datoteke na FS.
private $_headFileName = null; # ime obstoječe datoteke na FS.
private $exportSettings = array();
private $_EXPORT_FULL_META = false; # ali izvažamo polne meta podatke
private $_EXPORT_HIDDEN_SYSTEM = false; # ali izvažamo sistemske podatke (email, telefon)
private $_EXPORT_ONLY_DATA = false; # ali izvažamo samo podatke brez parapodatkov
private $_EXPORT_SPSS_DATA = false; # ali izvažamo podatke za spsss
private $_EXPORT_SPSS_HEAD = true; # ali izvažamo header za spss
private $_EXPORT_EXCEL_HEAD = true; # ali izvažamo header za excel
private $_EXPORT_EXCEL_REPLACE = array(); # ali izvažamo zamenjave za excel
//private $_SPECIAL_EXPORT = false; # samo za posebne primere kadar "moramo" izpisovati skrite sistemske variable (preko get se pošlje special_export=true
private $_HEADERS = array(); # array z header podatki
private $_FIELDS_ARRAY = array(); # array kamor shranimo polja katera za katera pobiramo podatke
private $_QUOTE = '"'; # Kateri zanko uporabimo za nize " ali '
private $_VARIABLE_FILTER = ''; # sed string array z prikazanimi variablami z upoštevanjem filtrov
private $_SVP_PV = array(); # array z prikazanimi variablami z upoštevanjem filtrov
/** Inicializacija
*
* @param $sid
*/
public function Init($sid = null) {
global $admin_type, $lang, $site_path, $global_user_id;
if ($sid == null) {
die('Error! Missing survey Id');
}
$this->folder = $site_path . EXPORT_FOLDER.'/';
# nastavimo id ankete
$this->sid = $sid;
if (IS_WINDOWS) {
$this->_QUOTE = '"';
} else {
$this->_QUOTE = '\'';
}
# informacije ankete
SurveyInfo::getInstance()->SurveyInit($this->sid);
$this->survey = SurveyInfo::getInstance()->resetSurveyData();
$this->survey = SurveyInfo::getInstance()->getSurveyRow();
# aktivne tabele
$this->db_table = SurveyInfo::getInstance()->getSurveyArchiveDBString();
// Preverimo ce ima user dostop
$d = new Dostop();
if(!$d->checkDostop($this->sid)){
die('Error! Access to survey '.$this->sid.' denied.');
}
# vsilimo podstran zaradi profila statusov
$_POST['podstran'] = A_COLLECT_DATA_EXPORT;
$this->_CURRENT_STATUS_FILTER = STATUS_FIELD.' ~ /6|5/';
# za profile statusov
SurveyStatusProfiles :: Init($this->sid);
# za profile variabel
SurveyVariablesProfiles :: Init($this->sid, $global_user_id);
# za profil ifov
SurveyConditionProfiles :: Init($this->sid, $global_user_id);
# za profil časov
SurveyTimeProfiles :: Init($this->sid, $global_user_id);
# manjkajoče vrednosti
$this->SMV = new SurveyMissingValues($this->sid);
#preverimo datoteke
self::checkFile();
self::setUpFilter();
self::getHeaderData(); # header podatke rabimo vedno
}
/** Naredimo izvoz
*
*/
public function DoExport() {
# v odvisnosti kaj eksportiramo
switch ($_GET['m']) {
case 'sav':
self::exportSav();
break;
case 'spss':
self::exportSpss();
break;
case 'excel':
self::exportCSV();
break;
case 'excel_xls':
self::exportExcelXls();
break;
/* case 'txt':
self::exportText();
break; */
}
}
/** Nastavimo filtre
*
*/
private function setUpFilter() {
global $global_user_id;
if ($this->_fileStatus >= 0) {
$filter_testdata = null;
$this->_HEADERS = unserialize(file_get_contents($this->_headFileName));
$this->exportSettings = array();
//mislim, da se to ne porablja vec - smo sli iz seje v nastavitev na anketo
foreach ($_SESSION AS $pkey => $pvalue)
{
// if starts with export
if (!strncmp($pkey, "export", strlen("export")))
{
#ali iz seje ali preko requesta
$this->exportSettings[ltrim($pkey, "{export}")] = $pvalue
|| (isset($_REQUEST[lcfirst($pvalue)]) ? $_REQUEST[lcfirst($pvalue)] : null);
}
}
//pridobi nastavitve za export ankete
//kateri podatki so v export se sedaj veze na naketo (0-onlydata, 1-data in paradata, 2-identifikatorji)
$sql_export_mode = sisplet_query("SELECT value FROM srv_user_setting_for_survey
WHERE sid = '" . $this->sid . "' AND what='default_export_data_mode' AND uid='$global_user_id'");
$export_mode = 0;
if($sql_export_mode){
$export_mode = mysqli_fetch_assoc($sql_export_mode);
$export_mode = isset($export_mode['value']) ? $export_mode['value'] : 0;
}
$this->exportSettings['export_data'] = $export_mode;
$this->_EXPORT_HIDDEN_SYSTEM = ($this->exportSettings['export_data'] == 2);
//$this->_EXPORT_HIDDEN_SYSTEM = (isset($_REQUEST['hiddenSystem']) && ((int)$_REQUEST['hiddenSystem'] == 1 || $_REQUEST['hiddenSystem'] == 'true')) ? true : $this->_EXPORT_HIDDEN_SYSTEM;
$this->_EXPORT_FULL_META = (($this->exportSettings['export_data'] == 1)/* && !$this->_EXPORT_HIDDEN_SYSTEM */);
$this->_EXPORT_ONLY_DATA = (($this->exportSettings['export_data'] == 0)/* && !$this->_EXPORT_HIDDEN_SYSTEM */);
//nikjer v kodi nisem nasel 'special_export'
//$this->_SPECIAL_EXPORT = (isset($_REQUEST['special_export']) && $_REQUEST['special_export'] == 'true') ? true : false;
# filtriranje po statusih
$this->_CURRENT_STATUS_FILTER = SurveyStatusProfiles :: getStatusAsAWKString();
# ali imamo filter na testne podatke
if (isset($this->_HEADERS['testdata']['grids'][0]['variables'][0]['sequence']) && (int)$this->_HEADERS['testdata']['grids'][0]['variables'][0]['sequence'] > 0) {
$test_data_sequence = $this->_HEADERS['testdata']['grids'][0]['variables'][0]['sequence'];
$filter_testdata = SurveyStatusProfiles :: getStatusTestAsAWKString($test_data_sequence);
}
# filtriranje po časih
$_time_profile_awk = SurveyTimeProfiles :: getFilterForAWK($this->_HEADERS['unx_ins_date']['grids']['0']['variables']['0']['sequence']);
$filter_usability = null;
# ali imamo filter na uporabnost
if (isset($this->_HEADERS['usability']['variables'][0]['sequence']) && (int)$this->_HEADERS['usability']['variables'][0]['sequence'] > 0) {
$usability_data_sequence = $this->_HEADERS['usability']['variables'][0]['sequence'];
$filter_usability = SurveyStatusProfiles :: getStatusUsableAsAWKString($usability_data_sequence);
}
# dodamo še ife
SurveyConditionProfiles :: setHeader($this->_HEADERS);
$_condition_profile_AWK = SurveyConditionProfiles:: getAwkConditionString();
if (($_condition_profile_AWK != "" && $_condition_profile_AWK != null )
|| ($_time_profile_awk != "" && $_time_profile_awk != null)
|| ($filter_testdata != null)
|| ($filter_usability != null)) {
$this->_CURRENT_STATUS_FILTER = '('.$this->_CURRENT_STATUS_FILTER;
if ($_condition_profile_AWK != "" && $_condition_profile_AWK != null ) {
$this->_CURRENT_STATUS_FILTER .= '&&'.$_condition_profile_AWK;
}
if ($_time_profile_awk != "" && $_time_profile_awk != null) {
$this->_CURRENT_STATUS_FILTER .= '&&'.$_time_profile_awk;
}
if ($filter_testdata != null ) {
$this->_CURRENT_STATUS_FILTER .= '&&('.$filter_testdata.')';
}
if ($filter_usability != null ) {
$this->_CURRENT_STATUS_FILTER .= '&&('.$filter_usability.')';
}
$this->_CURRENT_STATUS_FILTER .= ')';
}
# FILTRI VARIABEL - Katere variable ne izpisujemo
$svp_pv = array();
# ne prikazujemo user-idja
$not_svp_pv['uid'] = 'uid';
# ne prikazujemo casa klik na nagovor
$not_svp_pv['unx_ins_date'] = 'unx_ins_date';
# ne prikazujemo meta podatkov
if ($this->_EXPORT_FULL_META == false) {
$not_svp_pv['meta'] = 'meta';
$not_svp_pv['recnum'] = 'recnum';
}
# filtriranje po spremenljivkah
$dvp = SurveyUserSetting :: getInstance()->getSettings('default_variable_profile');
$this->_FILTRED_VARIABLES = SurveyVariablesProfiles :: getProfileVariables(SurveyVariablesProfiles :: checkDefaultProfile($dvp));
# skreiramo filter variabel za podatke
if (count($this->_HEADERS) > 0) {
// zloopamo skozi spremenljivke in sestavimo filter po stolpcih
$_tmp_filter = array();
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
# privzeto spremenljivke ne prikazujemo
$_can_show = false;
$tip = isset($spremenljivka['tip']) ? $spremenljivka['tip'] : null;
# če spremenljivka ni v neprikazanih jo prikažemo
if (!in_array($spid, $not_svp_pv)) {
# če imamo sistemski email ali telefon, ime, priimek (v header je nastavljno "hide_system" = 1)
# potem v odvisnosti od nastavitve prikazujemo samo navadne podatke ali pa samo te sistemske, zaradizaščite podatkov
if ($this->_EXPORT_HIDDEN_SYSTEM == true && isset($spremenljivka['hide_system']) && $spremenljivka['hide_system'] == '1' /* || $this->_SPECIAL_EXPORT == true */) {
# prikazujemo sistemske, in spremenljivka je sistemska
$_can_show = true;
} else
if ($this->_EXPORT_HIDDEN_SYSTEM == false && (!isset($spremenljivka['hide_system']) || $spremenljivka['hide_system'] !== '1' )) {
# prikazujemo nesistemske, in spremenljivka ni sistemska
$_can_show = true;
}
}
if ($_can_show == true) {
# če mamo filter po variablah ga upoštevamo
if ( ( $tip == 'm' || $tip == 'sm' )
|| ( count($this->_FILTRED_VARIABLES) == 0 || (count($this->_FILTRED_VARIABLES) > 0 && isset($this->_FILTRED_VARIABLES[$spid])) )
|| ( $this->_EXPORT_HIDDEN_SYSTEM == true )
){
$svp_pv[$spid] = $spid;
if (count($spremenljivka['grids']) > 0 ) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
if (count ($grid['variables']) > 0) {
foreach ($grid['variables'] AS $vid => $variable ){
if (($tip !== 'sm' && $tip !== 'm') || ($this->exportSettings['export_data'] == 1)) {
$_tmp_filter[]= $variable['sequence'];
}
}
}
}
}
}
}
}
}
# prilagodimo array profilov variabel
$this->_SVP_PV = $svp_pv;
if (count($_tmp_filter) > 0) {
$this->_VARIABLE_FILTER = implode(',',$_tmp_filter);
}
}
}
/** polovimo array z header podatki
*
*/
function getHeaderData() {
if ($this->_headFileName != null && $this->_headFileName != '') {
$this->_HEADERS = unserialize(file_get_contents($this->_headFileName));
} else {
echo 'Error! Empty file name!';
}
}
// Print data into active sheet
public function printSheetData($sheet, $data, $top_left = 'A1')
{
// Sample data
$sheet->fromArray(
$data, // The data to set
NULL, // Array values with this value will not be set
$top_left // Top left coordinate of the worksheet range where
);
}
/** naredimo izvoz za excel - xls
*
*/
function exportExcelXls() {
global $site_path, $lang;
$folder = $site_path . EXPORT_FOLDER.'/';
if ($this->_fileStatus >= 0 && $this->_dataFileName !== null && $this->_dataFileName !== '' && $this->_headFileName !== null && $this->_headFileName !== '') {
//za labele
$var_labels = array();
$missing_values = array();
$missing_values['-1'] = $lang['srv_bottom_data_legend_note_li1a'];
$missing_values['-2'] = $lang['srv_bottom_data_legend_note_li2a'];
$missing_values['-3'] = $lang['srv_bottom_data_legend_note_li3a'];
$missing_values['-4'] = $lang['srv_bottom_data_legend_note_li4a'];
$missing_values['-5'] = $lang['srv_bottom_data_legend_note_li5a'];
$missing_values['-97'] = $lang['srv_other_97'];
$missing_values['-98'] = $lang['srv_other_98'];
$missing_values['-99'] = $lang['srv_other_99'];
$data_table = array();
$spreadsheet = new Spreadsheet();
// Zavihek strani
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle($lang['srv_podatki']);
$writer = new Xlsx($spreadsheet);
//stevec stolpcev po headerju
$stevec_headerja = 0;
# naredimo header row
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid])) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
foreach ($grid['variables'] AS $vid => $variable ){
if(($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || ($this->exportSettings['export_data'] == 1)){
$data_table[0][] = strip_tags($variable['variable']);
$data_table[1][] = strip_tags($variable['naslov']);
$data_table_labels[0][] = strip_tags($variable['variable']);
$data_table_labels[1][] = strip_tags($variable['naslov']);
//labele po stolpcih, da se jih kasneje zamenja
$var_labels[$stevec_headerja] = $missing_values;
//dodamo vrednosti custom missingov
if(isset($spremenljivka['missing_values']))
$var_labels[$stevec_headerja] = $spremenljivka['missing_values']+$var_labels[$stevec_headerja];
if( in_array($spremenljivka['tip'], array(1,6,3))){
//dodamo na mesto stolpca, kjer se bodo zamenjali podatki z labelami
$var_labels[$stevec_headerja] = $var_labels[$stevec_headerja]+$spremenljivka['options'];
}
elseif( in_array($spremenljivka['tip'], array(2,16))){
//dodamo na mesto stolpca, kjer se bodo zamenjali podatki z labelami
$var_labels[$stevec_headerja] = $var_labels[$stevec_headerja]+array("0"=>$lang['srv_checked_value_0'], "1"=>$lang['srv_checked_value_1']);
}
$stevec_headerja++;
}
}
}
}
}
# sfiltriramo podatke
exec ('awk -F'.$this->_QUOTE.STR_DLMT.$this->_QUOTE.' '.$this->_QUOTE.$this->_CURRENT_STATUS_FILTER.' {print $0}'.$this->_QUOTE.' '.$this->_dataFileName.' > '.$this->_dataFileName .'_data1'.TMP_EXT);
if (IS_WINDOWS) {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d "|" -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
} else {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d \'|\' -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
}
$out1_1 = shell_exec($cmdLn1_1);
//gremo cez podatke, zacnemo z vrstico 3 zaradi headerja
$line_num = 2;
$fileLines = file($this->_dataFileName .'_data1_1'.TMP_EXT, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
foreach ($fileLines as $line) {
$line_arr = explode('|', $line);
foreach ($line_arr as $index=>$cell) {
//labele
if(in_array($index, array_keys($var_labels))){
$data_table_labels[$line_num][] = isset($var_labels[$index][$cell]) ? $var_labels[$index][$cell] : $cell;
$data_table[$line_num][] = $cell;
}
//grobi podatki
else{
$data_table_labels[$line_num][] = $cell;
$data_table[$line_num][] = $cell;
}
}
$line_num ++;
}
//sprintamo vse podatke (skupaj s header) v tabelo
$this->printSheetData($sheet, $data_table);
// Style table header
$range_header = $this->getRange(0, 2, 0, count($data_table[0])-1);
$style_header = array(
/* 'font' => array(
'bold' => true
),
'alignment' => array(
'center' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
),
'borders' => array(
'allBorders' => array('borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN),
), */
'fill' => array(
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'startColor' => [
'argb' => 'FFF0F0F0',
],
'endColor' => [
'argb' => 'FFF0F0F0',
],
),
);
//set style to header
$sheet->getStyle($range_header)->applyFromArray($style_header);
//freeze header
$sheet->freezePane('A3');
//SHEET Z LABELAMI
// Create a new sheet with
$secondSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, $lang['srv_dataIcons_labels']);
// Add the new sheet to the spreadsheet
$spreadsheet->addSheet($secondSheet);
//sprintamo vse podatke (skupaj s header) v tabelo
$this->printSheetData($secondSheet, $data_table_labels);
//set style to header
$secondSheet->getStyle($range_header)->applyFromArray($style_header);
//freeze header
$secondSheet->freezePane('A3');
$spreadsheet->setActiveSheetIndex(0);
$file_name_full = $this->_dataFileName . '.xlsx';
//shranimo datoteko
$writer->save($file_name_full);
// Set headers for file download
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="anketa' . $this->sid . '-' . date('Y-m-d') . '.xlsx"');
header('Pragma: public');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
ob_clean();
flush();
// Output file contents for download
readfile($file_name_full);
#pobrišemo vse tmp datoteke
foreach (glob($folder . 'export_data_'.$this->sid.'_*'.TMP_EXT) as $fileToDelete) {
unlink($fileToDelete);
}
unlink ($file_name_full);
} else {
echo '';
}
exit;
}
// Get range
private function getRange($row_start, $row_end, $col_start, $col_end)
{
$start_col_name = $this->getExcelColumn($col_start);
$end_col_name = $this->getExcelColumn($col_end);
$range = $start_col_name . $row_start . ':' . $end_col_name . $row_end;
return $range;
}
//get excel name of column based on number of column
private function getExcelColumn($col_number) {
$base = ord('A');
$letters = '';
while ($col_number >= 0) {
$letters = chr($col_number % 26 + $base) . $letters;
$col_number = (int)($col_number / 26) - 1;
}
return $letters;
}
/** naredimo izvoz za excel - xls (stara verzija s html zapisom)
*
*/
function exportExcelXlsOld() {
global $site_path;
$folder = $site_path . EXPORT_FOLDER.'/';
if ($this->_fileStatus >= 0 && $this->_dataFileName !== null && $this->_dataFileName !== '' && $this->_headFileName !== null && $this->_headFileName !== '') {
#zapišemo v temp file
$file_handler = fopen($this->_dataFileName.'.xls',"w");
$output1 = '';
$output2 = '';
# naredimo header row
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid])) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
foreach ($grid['variables'] AS $vid => $variable ){
if(($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || ($this->exportSettings['export_data'] == 1)){
$output1 .= '<td>'.strip_tags($variable['variable']).'</td>';
$output2 .= '<td>'.strip_tags($variable['naslov']).'</td>';
}
}
}
}
}
fwrite($file_handler,$output1."\r\n");
fwrite($file_handler,$output2."\r\n");
fclose($file_handler);
# sfiltriramo podatke
exec ('awk -F'.$this->_QUOTE.STR_DLMT.$this->_QUOTE.' '.$this->_QUOTE.$this->_CURRENT_STATUS_FILTER.' {print $0}'.$this->_QUOTE.' '.$this->_dataFileName.' > '.$this->_dataFileName .'_data1'.TMP_EXT);
if (IS_WINDOWS) {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d "|" -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
} else {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d \'|\' -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
}
$out1_1 = shell_exec($cmdLn1_1);
# zamenjamo | z </td><td>
exec('sed '.$this->_QUOTE.'s/|/<\/td><td align="center">/g'.$this->_QUOTE.' '.$this->_dataFileName .'_data1_1'.TMP_EXT.' >> '.$this->_dataFileName.'.xls');
$convertType = 1; // kateri tip konvertiranja uporabimo
$convertTypes[1] = array('charSet' => "windows-1250",
'delimit' => ";",
'newLine' => "\n",
'BOMchar' => "\xEF\xBB\xBF");
# izvedemo download
if ($fd = fopen ($this->_dataFileName.'.xls', "r")) {
// clean the output buffer
ob_clean();
$fsize = filesize($this->_dataFileName.'.xls');
$path_parts = pathinfo($this->_dataFileName.'.xls');
$ext = strtolower($path_parts["extension"]);
header('Content-type: application/vnd.ms-excel; charset='.$convertTypes[$convertType]['charSet']);
header('Content-Disposition: attachment; filename="anketa'.$this->sid.'-'.date('Y-m-d').'.xls"');
# header('Content-length: '.$fsize);
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
ob_flush();
# dodami boomchar za utf-8
echo $convertTypes[$convertType]['BOMchar'];
// Izpisemo celo tabelo
echo '<table border="1">'."\r\n";
$cnt=0;
while ($line = fgets ($fd)) {
if($cnt > 1) echo '<tr><td align="center">';
else print('</tr>');
$line = str_replace(array("\r","\n"), array("",""), $line);
print($line);
if($cnt > 1) print('</td></tr>');
else print('</tr>');
print("\r\n");
$cnt++;
}
echo '</table>';
}
else {
echo "Napaka";
}
fclose ($fd);
#pobrišemo vse tmp datoteke
foreach (glob($folder . 'export_data_'.$this->sid.'_*'.TMP_EXT) as $fileToDelete) {
unlink($fileToDelete);
}
unlink ($this->_dataFileName.'.xls');
} else {
echo '';
}
exit;
}
/** naredimo izvoz za csv
*
*/
function exportCSV() {
global $site_path;
$folder = $site_path . EXPORT_FOLDER.'/';
session_start();
if ($this->_fileStatus >= 0 && $this->_dataFileName !== null && $this->_headFileName !== null) {
$file_csv = ((int)$_POST['export_format'] == 1);
$extension = ($file_csv) ? '.csv' : '.txt';
if ($_POST['export_delimit'] == 0) {
$field_delimit = ';';
$replace_what = isset($_POST['replace_what0']) ? $_POST['replace_what0'] : array();
$replace_with = isset($_POST['replace_with0']) ? $_POST['replace_with0'] : array();
} else {
$field_delimit = ',';
$replace_what = isset($_POST['replace_what1']) ? $_POST['replace_what1'] : array();
$replace_with = isset($_POST['replace_with1']) ? $_POST['replace_with1'] : array();
}
#zapišemo v temp file
$file_handler = fopen($this->_dataFileName.$extension,"w");
$output1 = '';
$output2 = '';
fclose($file_handler);
# sfiltriramo podatke
exec ('awk -F'.$this->_QUOTE.STR_DLMT.$this->_QUOTE.' '.$this->_QUOTE.$this->_CURRENT_STATUS_FILTER.' {print $0}'.$this->_QUOTE.' '.$this->_dataFileName.' > '.$this->_dataFileName .'_data1'.TMP_EXT);
if (IS_WINDOWS) {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d "|" -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
} else {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d \'|\' -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
}
$out1_1 = shell_exec($cmdLn1_1);
# zamenjamo uporabniške znake
if (is_array($replace_what) && count($replace_what) > 0 && is_array($replace_with) && count($replace_with) > 0) {
$_new_filename = '_data1_1';
$cnt_replace = min(count($replace_what),count($replace_with));
for ($i = 0; $i < $cnt_replace; $i++) {
exec('sed '.$this->_QUOTE .'s/'.$replace_what[$i].'/'.$replace_with[$i].'/g'.$this->_QUOTE .' '.$this->_dataFileName .'_data1_'.($i+1).TMP_EXT.' > '.$this->_dataFileName .'_data1_'.($i+2).TMP_EXT);
$_new_filename = '_data1_'.($i+2);
}
} else {
$_new_filename = '_data1_1';
}
# zamenjamo | z ;
if($file_csv)
exec('sed '.$this->_QUOTE.'s/|/\x22'.$field_delimit.'=\x22/g'.$this->_QUOTE.' '.$this->_dataFileName .$_new_filename.TMP_EXT.' >> '.$this->_dataFileName.$extension);
else
exec('sed '.$this->_QUOTE.'s/|/'.$field_delimit.'/g'.$this->_QUOTE.' '.$this->_dataFileName .'_data1_1'.TMP_EXT.' >> '.$this->_dataFileName.$extension);
$convertType = 1; // kateri tip konvertiranja uporabimo
$convertTypes[1] = array('charSet' => 'windows-1250',
'delimit' => ';',
'newLine' => "\n",
'BOMchar' => "\xEF\xBB\xBF");
# izvedemo download
if ($fd = fopen ($this->_dataFileName.$extension, "r")) {
$fsize = filesize($this->_dataFileName.$extension);
$path_parts = pathinfo($this->_dataFileName.$extension);
$ext = strtolower($path_parts["extension"]);
#ob_clean();
#header('Content-type: application/vnd.ms-excel; charset='.$convertTypes[$convertType]['charSet']);
header('Content-type: text/csv; charset='.$convertTypes[$convertType]['charSet']);
header('Content-Disposition: attachment; filename="anketa'.$this->sid.'-'.date('Y-m-d').$extension.'"');
# ker iz zacasne datoteke preberemo samo podatke brez headerja (header izpisujemo posebej)
# ne moremo podati content-lenght. Razen če bi predhodno vsae zapisali v tmp datoteko in potem prebrali dolžino
# header('Content-length: '.$fsize);
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
#ob_flush();
# dodami boomchar za utf-8
echo $convertTypes[$convertType]['BOMchar'];
# naredimo header row
if ((int)$_POST['export_headers'] == 1) {
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid])) {
if (count($spremenljivka['grids']) > 0) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
foreach ($grid['variables'] AS $vid => $variable ){
if(($spremenljivka['tip'] !== 'sm' && ($spremenljivka['tip'] !== 'm') || $this->exportSettings['export_data'] == 1)){
$output1 .= str_replace($replace_what, $replace_with, strip_tags($variable['variable'])) . $field_delimit;
$output2 .= str_replace($replace_what, $replace_with, strip_tags($variable['naslov'])) . $field_delimit;
}
}
}
}
}
}
echo $output1."\r\n";
echo $output2."\r\n";
}
while ($line= fgets ($fd)) {
if($file_csv) echo '="';
$line = str_replace(array("\r","\n"), array("",""), $line);
print ($line);
if($file_csv) print ('"');
print ("\r\n");
}
}
else {
echo 'x1:Napaka';
}
fclose ($fd);
#pobrišemo vse tmp datoteke
foreach (glob($folder . 'export_data_'.$this->sid.'*dat_data*'.TMP_EXT) as $fileToDelete) {
unlink($fileToDelete);
}
unlink ($this->_dataFileName.$extension);
}
else {
echo 'x2:Napaka!';
}
exit;
}
/** naredimo izvoz za txt SEDAJ JE TO VKLJUCENO V CSV
*
*/
/* function exportText() {
global $site_path;
$folder = $site_path . EXPORT_FOLDER.'/';
if ($this->_fileStatus >= 0 && $this->_dataFileName !== null && $this->_dataFileName !== '' && $this->_headFileName !== null && $this->_headFileName !== '') {
if ($_POST['export_delimit'] == 0) {
$field_delimit = ';';
$replace_what = isset($_POST['replace_what0']) ? $_POST['replace_what0'] : array();
$replace_with = isset($_POST['replace_with0']) ? $_POST['replace_with0'] : array();
} else {
$field_delimit = ',';
$replace_what = isset($_POST['replace_what1']) ? $_POST['replace_what1'] : array();
$replace_with = isset($_POST['replace_with1']) ? $_POST['replace_with1'] : array();
}
#zapišemo v temp file
$file_handler = fopen($this->_dataFileName.'.txt',"w");
$output1 = '';
$output2 = '';
// array za labele (ce jih izpisujemo)
$display_labels = ((int)$_POST['export_labels'] == 1);
$labels = array();
# naredimo header row
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid])) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
foreach ($grid['variables'] AS $vid => $variable ){
if(($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || (isset($this->exportSettings['FullMeta']) && $this->exportSettings['FullMeta'] > 0)){
$output1 .= strip_tags($variable['variable']).';';
$output2 .= strip_tags($variable['naslov']).';';
}
}
}
}
// Ce izpisujemo tudi labele
if($display_labels && isset($spremenljivka['spr_id'])){
if(in_array($spremenljivka['tip'], array('1','3','6','16'))){
$sequences = array();
$sequences = explode('_', $spremenljivka['sequences']);
$vars = $this->getVariableLabels($spremenljivka['spr_id']);
foreach($sequences as $sequence){
$labels[$sequence] = $vars;
}
}
}
}
fwrite($file_handler,$output1."\n");
fwrite($file_handler,$output2."\n");
fclose($file_handler);
# sfiltriramo podatke
exec ('awk -F'.$this->_QUOTE.STR_DLMT.$this->_QUOTE.' '.$this->_QUOTE.$this->_CURRENT_STATUS_FILTER.' {print $0}'.$this->_QUOTE.' '.$this->_dataFileName.' > '.$this->_dataFileName .'_data1'.TMP_EXT);
if (IS_WINDOWS) {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d "|" -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
} else {
#filtri spremenljivk
$cmdLn1_1 = 'cut -d \'|\' -f '.$this->_VARIABLE_FILTER.' '.$this->_dataFileName .'_data1'.TMP_EXT.' > '.$this->_dataFileName .'_data1_1'.TMP_EXT;
}
$out1_1 = shell_exec($cmdLn1_1);
# zamenjamo | z ;
exec('sed '.$this->_QUOTE.'s/|/;/g'.$this->_QUOTE.' '.$this->_dataFileName .'_data1_1'.TMP_EXT.' >> '.$this->_dataFileName.'.txt');
$convertType = 1; // kateri tip konvertiranja uporabimo
$convertTypes[1] = array('charSet' => "windows-1250",
'delimit' => ";",
'newLine' => "\n",
'BOMchar' => "\xEF\xBB\xBF");
# izvedemo download
if ($fd = fopen ($this->_dataFileName.'.txt', "r")) {
// clean the output buffer
ob_clean();
$fsize = filesize($this->_dataFileName.'.txt');
$path_parts = pathinfo($this->_dataFileName.'.txt');
$ext = strtolower($path_parts["extension"]);
header('Content-type: text/plain; charset='.$convertTypes[$convertType]['charSet']);
header('Content-Disposition: attachment; filename="anketa'.$this->sid.'-'.date('Y-m-d').'.txt"');
# header('Content-length: '.$fsize);
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
ob_flush();
# dodami boomchar za utf-8
echo $convertTypes[$convertType]['BOMchar'];
//while(!feof($fd)) {
// $buffer = fread($fd, 2048);
// echo $buffer.'';
//}
$i=0;
// Loop po vrsticah
while(($line = fgets($fd)) !== false) {
// Samo naslovni vrstici z metapodatki oz. vse vrstice ce nimamo label
if($i < 2 || !$display_labels){
echo $line;
}
// Izpisujemo labele v podatkovnih vrsticah
else{
// Vrstico s podatki razbijemo, dodamo labele in jo nazaj sestavimo
$line_array = explode(';', $line);
if(count($line_array) > 0){
foreach($line_array as $seq => $val){
// Izpisemo vrednost
echo $val;
// Izpisemo labelo
$seq += 3;
if(isset($labels[$seq][0]['values'][$val])){
echo ' ("'.$labels[$seq][0]['values'][$val].'")';
}
// Vsem razen zadnjemu dodamo se separator
if($seq < count($line_array)+2)
echo ';';
}
}
}
$i++;
}
}
else {
echo "Napaka";
}
fclose ($fd);
#pobrišemo vse tmp datoteke
foreach (glob($folder . 'export_data_'.$this->sid.'_*'.TMP_EXT) as $fileToDelete) {
unlink($fileToDelete);
}
unlink ($this->_dataFileName.'.txt');
}
else {
echo '';
}
exit;
} */
// shranim SPS in A00, potem pokličem spss in je.
private function exportSav () {
// izbriši staro
// pač... RM dela :-)
@unlink ($this->folder .'tmp_spss2sav' .$this->sid .'.a00');
@unlink ($this->folder .'tmp_spss2sav' .$this->sid .'.sps');
// spss + a00
$this->exportSpss("save", true, true);
$this->exportSpss("save", false, true);
// convert
//echo "Diagnostics for developers (link is below): <br>";
passthru ('pspp ' .$this->folder .'tmp_spss2sav' .$this->sid .'.sps');
//echo '<br><br><strong><a href="SurveyData/' .'tmp_spss2sav' .$this->sid .'.SAV">Link</a></strong>';
// Tole ne dela ker kessira star file
//header ('location: SurveyData/' .'tmp_spss2sav' .$this->sid .'.SAV');
$file_url = 'SurveyData/' .'tmp_spss2sav' .$this->sid .'.SAV';
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"" . basename($file_url) . "\"");
/* if(file_exists($file_url)) */readfile($file_url);
}
/**
* mode download - klasično, pač skrani SAV + A00
* mode save - shrani fajla
*
* pa porinem si noter še data_par (true - a00, false - sps).
*/
private function exportSpss($mode = "download", $data_param = false, $pspp=false) {
global $site_path, $lang;
$folder = $site_path . EXPORT_FOLDER.'/';
$tmp_files = $this->folder.'tmp_spss_'.$this->sid.'.php';
#polovimo max št znakov za posamezne sekvence
if (IS_WINDOWS) {
$command = 'awk -F"|" "BEGIN {{OFS=\"\"} {ORS=\"\n\"} {FS=\"\x7C\"} {SUBSEP=\"\x7C\"}} '
.'{if (MaxFields < NF) MaxFields = NF; for (i=1; i<=NF; i++) { Field[NR, i] = $i; l = length($i); if (l < 1) l = 1; if (Length[i] < l) Length[i] = l; };} '
.'END {print\"\x3C\x3Fphp\"; for (i=1; i<=MaxFields; i++) { print \"$spss_length[\",i,\"]\",\"=\",Length[i],\";\"}; print\"\x3F\x3E\"}" '
.$this->_dataFileName. ' > '.$tmp_files ;
} else {
$command = 'awk -F"|" \'BEGIN {{OFS=""} {ORS="\n"} {FS="\x7C"} {SUBSEP="\x7C"}} '
.'{if (MaxFields < NF) MaxFields = NF; for (i=1; i<=NF; i++) { Field[NR, i] = $i; l = length($i); if (l < 1) l = 1; if (Length[i] < l) Length[i] = l; };} '
.'END {print"\x3C\x3Fphp"; for (i=1; i<=MaxFields; i++) { print "$spss_length[",i,"]","=",Length[i],";"}; print"\x3F\x3E"}\' '
.$this->_dataFileName. ' > '.$this->folder.'tmp_spss_'.$this->sid.'.php';
}
$out = shell_exec($command);
include($tmp_files );
# pobrišemo inkludan fajl, ker jih več ne rabimo
if (file_exists($tmp_files )) {
unlink($tmp_files );
}
if ($this->_fileStatus >= 0 && $this->_dataFileName !== null && $this->_dataFileName !== '' && $this->_headFileName !== null && $this->_headFileName !== '') {
#ali lovimo datoteko s strukturo ali datoteko z podatki
$data = false;
if ((isset($_REQUEST['exportData']) && (int)$_REQUEST['exportData'] == 1) || $data_param == true) {
$data = true;
}
# delamo datoteko s strukturo
if ( $data == false ) {
# TODO - odstranit šumnike iz header datoteke
#$str = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $str);
$_value_labels_numbers = ''; # temp variabla kamor shranimo imena variabel numeričnih odgovorov
$_value_labels_text = ''; # temp variabla kamor shranimo imena variabel tekstovnih odgovorov
#preberemo HEADERS iz datoteke
$this->_HEADERS = unserialize(file_get_contents($this->_headFileName));
# poiščemo maximalno število znakov pri missing vrednostih za tekstovne in number odgovore
$_all_missing_values = SurveyMissingValues::GetMissingValuesForSurvey(array(1,2,3));
$_max_text_missing_chars = 0;
$_max_number_missing_chars = 0;
# polovimo maximalne dolžine znakov
foreach ($_all_missing_values AS $mkey => $missing) {
$_max_text_missing_chars = max($_max_text_missing_chars, strlen($mkey . ': '. $missing));
$_max_number_missing_chars = max($_max_number_missing_chars, strlen($mkey));
}
$maxLengthForSpr = $this->create_array_SPSS(max($_max_text_missing_chars,$_max_number_missing_chars));
//$resultString .= .NEW_LINE;
$resultString = $lang['srv_spss_export_base_instructions'];
$resultString .= NEW_LINE.'.'.NEW_LINE.NEW_LINE;
$resultString .= 'PRESERVE.'.NEW_LINE;
// TOLE NE DELA OK ZA DECIMALKE V NOVIH SPSSih
/*if ($pspp == false) {
$resultString .= 'SET UNICODE ON.'.NEW_LINE;
}
else {
$resultString .= "SET LOCALE='UTF-8'.".NEW_LINE;
}*/
$resultString .= "SET LOCALE='en_US'.".NEW_LINE;
$resultString .= 'SET UNICODE ON.'.NEW_LINE;
$resultString .= 'SET DECIMAL DOT.'.NEW_LINE.NEW_LINE;
# seznam spremenljivk in opis formata
$resultString .= 'GET DATA'.NEW_LINE;
$resultString .= ' /TYPE = TXT'.NEW_LINE;
if ($mode == "download") {
$resultString .= ' /FILE = \'C:\anketa'.$this->sid.'-'.date('Y-m-d').'_podatki.txt\''.NEW_LINE;
}
else {
$resultString .= ' /FILE = \'' .$this->folder .'tmp_spss2sav' .$this->sid .'.a00\''.NEW_LINE;
}
//.$lang['srv_spss_export_file_instructions'].NEW_LINE;
$resultString .= ' /ARRANGEMENT = DELIMITED'.NEW_LINE;
$resultString .= ' /FIRSTCASE = 1'.NEW_LINE;
$resultString .= ' /IMPORTCASE = ALL'.NEW_LINE;
$resultString .= ' /DELIMITERS = " "'.NEW_LINE;
$resultString .= ' /QUALIFIER = "\'"'.NEW_LINE;
$resultString .= ' /VARIABLES = '.NEW_LINE;
# dodamo seznam variabel z tipi podatkov
if (count($this->_HEADERS) > 0) {
$cnt = 1;
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid]) && count($spremenljivka['grids']) > 0) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
if (count($grid['variables']) > 0) {
foreach ($grid['variables'] AS $vid => $variable ){
$seq=$variable['sequence'];
if(!isset($spremenljivka['tip']) || ($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || $this->exportSettings['export_data'] == 1){
# vsako v svojo vrstico
if (substr($variable['spss'],0,1) == 'F') {
# pri številih
$_number = explode('.',substr($variable['spss'],1));
$_cela = isset($_number[0]) && $_number[0] > 0 ? $_number[0] : 0;
$_decimalna = isset($_number[1]) && $_number[1] > 0 ? $_number[1] : 0;
$_spss_chars = 'F'.max($_max_number_missing_chars,$_cela,1).'.'.$_decimalna;
} else if ($variable['spss'] == 'DATETIMEw') {
# pri tekstovnih odgovorih
#$_spss_chars = 'A'.$_max_text_missing_chars;
#polovimo po novi metodi
$_spss_chars = 'A'.$spss_length[$seq];
} else {
# pri tekstovnih odgovorih
#$_spss_chars = substr($variable['spss'],0,1) . max($maxLengthForSpr[$spid],$_max_text_missing_chars,substr($variable['spss'],1),1);
#polovimo po novi metodi
$_spss_chars = 'A'.$spss_length[$seq];
}
// Language meta moramo preimenovati za spss, ker drugace je podvojen z language spremenljivko
if($variable['variable'] == 'Language')
$resultString .= ' Language_meta '.$_spss_chars.NEW_LINE;
else
$resultString .= ' ' . preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']) . ' '.$_spss_chars.NEW_LINE;
# polovimo imena variable za missing vrednosti Nagovora ne dodajmo
if (isset($spremenljivka['tip']) && $spremenljivka['tip'] != 'm' && $spremenljivka['tip'] != 'sm' && $spremenljivka['tip'] != 5) {
if (substr($variable['spss'],0,1) == 'F') {
$_value_labels_numbers .= $variable['variable'].' ';
// Dodamo prelom vsakih 10 variabel zaradi max dolzine vrstice
if($cnt > 10){
$_value_labels_numbers .= NEW_LINE;
$cnt = 0;
}
$cnt++;
}
else if (substr($variable['spss'],0,1) == 'A') {
$_value_labels_text .= $variable['variable'].' ';
// Dodamo prelom vsakih 10 variabel zaradi max dolzine vrstice
if($cnt > 10){
$_value_labels_text .= NEW_LINE;
$cnt = 0;
}
$cnt++;
}
}
}
}
}
}
}
}
}
$resultString .= ' .'.NEW_LINE.NEW_LINE;
#ime ankete brez presledkov
// pspp ne mara _
if ($pspp == false)
$resultString .= 'DATASET NAME ' . preg_replace("/[^a-zA-Z0-9_]/",'_',$this->survey['naslov']) . ' WINDOW=FRONT.' . NEW_LINE.NEW_LINE;
else
$resultString .= 'DATASET NAME ' . preg_replace("/[^a-zA-Z0-9_]/",'',$this->survey['naslov']) . ' WINDOW=FRONT.' . NEW_LINE.NEW_LINE;
#labele vprasanj ==> VARIABLE LABELS
# seznam spremenljivk in opis formata (pika na koncu vsake labele)
if (count($this->_HEADERS) > 0) {
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid]) && count($spremenljivka['grids']) > 0) {
foreach ($spremenljivka['grids'] AS $gid => $grid) {
if (count($grid['variables']) > 0) {
foreach ($grid['variables'] AS $vid => $variable ){
if(!isset($spremenljivka['tip']) || ($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || $this->exportSettings['export_data'] == 1){
switch ($spremenljivka['tip']) {
case '2':
case '6':
$variable_label = substr($spremenljivka['naslov'],0,30).': '.$variable['naslov'];
$_variable = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']);
break;
case '7':
if (isset($spremenljivka['enota'])) {
$variable_label= substr($spremenljivka['naslov'],0,30) .' ('.$variable['naslov'].')';
} else {
$variable_label= substr($spremenljivka['naslov'],0,30) ;
}
$_variable = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']);
break;
case '16':
case '19':
case '20':
$variable_label= substr($spremenljivka['naslov'],0,15) .': '.$grid['naslov'].': '.$variable['naslov'];
$_variable = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']);
break;
case '21':
$variable_label= substr($spremenljivka['naslov'],0,30) .' ('.$variable['naslov'].')';
$_variable = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']);
break;
default:
$variable_label = $variable['naslov'];
$_variable = preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable['variable']);
break;
}
if($variable['other'] == 1 && $variable['text'] == 1) {
$variable_label .= ' '.$lang['srv_sppss_text_other'];
}
$variable_label = $this->getCleanString($variable_label);
// Language meta moramo preimenovati za spss, ker drugace je podvojen z language spremenljivko
if($variable['variable'] == 'Language')
$resultString .= 'VARIABLE LABELS Language_meta \''.$variable_label.'\' .'.NEW_LINE;
else
$resultString .= 'VARIABLE LABELS '.$_variable.' \''.$variable_label.'\' .'.NEW_LINE;
}
}
}
}
}
}
$resultString .=NEW_LINE;
}
# labele vrednosti ==> VALUE LABELS
# seznam label vrednosti spremenljivk (pika na koncu vsakega sklopa)
if (count($this->_HEADERS) > 0) {
foreach ($this->_HEADERS AS $spid => $spremenljivka) {
if (isset($this->_SVP_PV[$spid]) && count($spremenljivka['grids']) > 0) {
if (isset($spremenljivka['options'])) {
$resultString .= 'VALUE LABELS';
foreach ($spremenljivka['grids'] AS $gid => $grid) {
if (count($grid['variables']) > 0) {
foreach ($grid['variables'] AS $vid => $variable ){
if(($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || $this->exportSettings['export_data'] == 1){
if ($variable['other'] != 1) {
$variable = $this->getCleanString($variable['variable']);
$resultString .= ' '.preg_replace ("/[^a-zA-Z0-9_\/]/", "_", $variable);
}
}
}
}
}
// pspp ne mara newline, hoče presledek vmes.
if ($pspp == false) $resultString .= NEW_LINE;
else $resultString .= ' ';
if (count($spremenljivka['options']) > 0) {
foreach ($spremenljivka['options'] AS $okey =>$option) {
if(($spremenljivka['tip'] !== 'sm' && $spremenljivka['tip'] !== 'm') || $this->exportSettings['export_data'] == 1){
if ($spremenljivka['tip'] == 2 || $spremenljivka['tip'] == 16) {
# pri čekboxu prevedemo
if ($pspp == false){
$resultString .= $okey. ' \''.$lang['srv_sppss_checkbox_value_'.$option].'\''.NEW_LINE;
}
else {
$resultString .= $okey. ' \''.$lang['srv_sppss_checkbox_value_'.$option].'\''.' ';
}
}
else {
$option = $this->getCleanString($option);
if ($pspp ==false) $resultString .= $okey. ' \''.$option.'\''.NEW_LINE;
else {
if (strpos ($option, "'")===false) {
$resultString .= $okey. ' \''.$option.'\''.' ';
}
else {
$resultString .= $okey. ' \"'.str_replace ("'", "'", $option) .'\"'.' ';
}
}
}
}
}
}
$resultString .='.'.NEW_LINE.NEW_LINE;
}
}
}
}
# missing vrednosti ==> MISSING VALUES
# seznam - razpon mising vrednosti ( pika na koncu ukaza)
/*
* Manjkajoče vrednosti se definirajo samo za številske (format F) spremenljivke, saj določanje intervala za
* tekstovne (format A) ni podprto. Interval manjkajočih vrednosti označi uporabnik v vmesniku za izvoz, privzeto
* je od -999 do -1. Uporabnik lahko izbere tudi, da ne želi definirati manjkajočih vrednosti – v tem primeru se
* spodnja koda ne vključi v sintakso.
* MISSING VALUES Q1 Q2 (...) Qn (a thru b).
* Q1, Q2, ..., Qn: imena številskih spremenljivk.
*/
$_unset = SurveyMissingValues::GetUnsetValuesForSurvey();
$_missings = SurveyMissingValues::GetMissingValuesForSurvey();
#poiščemo razpon missingov, najmanjči in največji
$_min = null;
$_max = null;
if (count($_unset) > 0) {
foreach ( $_unset AS $key => $_mising_value) {
$_min = ($_min == null) ? $key : min($_min,$key);
$_max = ($_max == null) ? $key : max($_max,$key);
}
}
if (count($_missings) > 0) {
foreach ( $_missings AS $key => $_mising_value) {
$_min = ($_min == null) ? $key : min($_min,$key);
$_max = ($_max == null) ? $key : max($_max,$key);
}
}
if ($_min == null) $_min = -99; # privzeto
if ($_max == null) $_max = -1; # privzeto
if (count($this->_HEADERS) > 0 && $_value_labels_numbers != '') {
$resultString .='MISSING VALUES ';
$resultString .= $_value_labels_numbers . ' ('.$_min.' thru '.$_max.')';
$resultString .='.'.NEW_LINE.NEW_LINE;
}
# missing labele ==> ADD VALUE LABELS
# seznam label za missinge ( pika na koncu seznama) Naredimo dvakrat, 1x za števila in 1x za texte
/** Labele za manjkajoče vrednosti se definirajo za številska in besedilna vprašanja in sicer ločeno.
* Labele se dobijo iz sistemskih nastavitev manjkajočih vrednosti v 1KA (npr. neodgovor itd.)
*/
# za number odgovore
if ( (count($_missings)+count($_unset)) > 0 && $_value_labels_numbers != '') {
if ($pspp == false)
$resultString .='ADD VALUE LABELS ' . $_value_labels_numbers.NEW_LINE;
else
$resultString .='VALUE LABELS ' . $_value_labels_numbers.' ';
if (count($_missings) > 0) {
foreach ( $_missings AS $mkey => $missing_value) {
if ($pspp == false)
$resultString .= $mkey . ' \'' . $this->getCleanString($missing_value).'\''.NEW_LINE;
else {
if (strpos ($this->getCleanString($missing_value), "'")===false) {
$resultString .= $mkey . ' "' . $this->getCleanString($missing_value).'"'.' ';
}
else {
$resultString .= $mkey . ' "' . str_replace ("'", "'", $this->getCleanString($missing_value)) .'"'.' ';
}
}
}
}
if (count($_unset) > 0) {
foreach ( $_unset AS $ukey => $unset_value) {
if ($pspp == false) $resultString .= $ukey . ' \'' . $this->getCleanString($unset_value).'\''.NEW_LINE;
else {
if (strpos ($this->getCleanString($unset_value), "'")===false) {
$resultString .= $ukey . ' \'' . $this->getCleanString($unset_value).'\''.' ';
}
else {
$resultString .= $ukey . ' "' . str_replace ("'", "'", $this->getCleanString($unset_value)).'"'.' ';
}
}
}
}
$resultString .='.'.NEW_LINE.NEW_LINE;
}
# za tekstovne odgovore prekodiramo missinge
// ne smeš, ker prideš preko dovoljene dolžine. V A5 pač ne gre 20 znakov...
// SPSS ima sicer komando "alter" za spremeniti string, ampak ker pspp ne podpira, delam labele!
if ( (count($_missings)+count($_unset)) > 0 && $_value_labels_text != '') {
$cnt = 1;
$resultString .= 'VALUE LABELS ' .$_value_labels_text .' '.NEW_LINE;
if (count($_missings) > 0) {
foreach ( $_missings AS $mkey => $missing_value) {
$resultString .= '\'' .$mkey . '\' \'' .$mkey . ': '. $this->getCleanString($missing_value).'\' ';
$resultString .= NEW_LINE;
}
}
if (count($_unset) > 0) {
foreach ( $_unset AS $ukey => $unset_value) {
if ($pspp == false){
$resultString .= '\'' . $ukey . '\' \'' . $ukey . ': '. $this->getCleanString($unset_value).'\' ';
$resultString .= NEW_LINE;
}
}
}
$resultString .= '.' . NEW_LINE.NEW_LINE;
}
if ($mode != "download") {
$resultString .= 'SAVE /OUTFILE \'' .$this->folder .'tmp_spss2sav' .$this->sid .'.SAV\'.' .NEW_LINE;
}
# povrnemo narejene spremembe v spssu
$resultString .='EXECUTE.'.NEW_LINE;
$resultString .='RESTORE.';
$convertType = 1; // kateri tip konvertiranja uporabimo
$convertTypes[1] = array('charSet' => "windows-1250",
'delimit' => ";",
'newLine' => "\n",
'BOMchar' => "\xEF\xBB\xBF");
// downloadaj
if ($mode == "download") {
ob_clean();
header('Content-type: text/plain; charset='.$convertTypes[$convertType]['charSet']);
header('Content-Disposition: attachment; filename="anketa'.$this->sid.'-'.date('Y-m-d').'.sps"');
# header("Content-length: $fsize");
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
ob_flush();
#$resultString = iconv("UTF-8", "CP1250", $resultString);
# dodami boomchar za utf-8
echo $convertTypes[$convertType]['BOMchar'];
echo $resultString;
}
// shrani nekam na disk če imaš "save" namesto "download"
else {
$fh = fopen($this->folder .'tmp_spss2sav' .$this->sid.'.sps', 'w');
fwrite($fh, $resultString);
fclose($fh);
}
// end if data = false
}
else {
#lovimo podatke
// | -> \x7C
// ` -> \x60
// ' -> \x27
// " -> \x22
$tmp_files = array( 'original' => $this->_dataFileName,
'first' => $this->_dataFileName.'_first',
'first1' => $this->_dataFileName.'_first_1',
'second' => $this->_dataFileName.'_second',
'third' => $this->_dataFileName.'_third',
'fourth' => $this->_dataFileName.'_fourth');
if (IS_WINDOWS) {
# polovimo vrstice z statusom
$cmdLn1 = 'awk -F"'.STR_DLMT.'" "'.$this->_CURRENT_STATUS_FILTER.' {print $0}" '.$tmp_files['original'].' > '.$tmp_files['first'];
#filtri spremenljivk
$cmdLn1_1 = 'cut -d "|" -f '.$this->_VARIABLE_FILTER.' '.$tmp_files['first'].' > '.$tmp_files['first1'];
#zamenjamo ' => `
$cmdLn2 = 'sed "s/\x27/\x60/g" '.$tmp_files['first1'].' > '.$tmp_files['second'];
# zamenjamo | z ' '
$cmdLn3 = 'sed "s/'.STR_DLMT.'/\x27 \x27/g" '.$tmp_files['second'].' > '.$tmp_files['third'];
# dodamo ' na začetek in konec
$cmdLn4 = 'awk '.$this->_QUOTE.'{print \"\'\"$0\"\'\"}'.$this->_QUOTE.' '.$tmp_files['third'].' > '.$tmp_files['fourth'];
} else {
# polovimo vrstice z statusom
$cmdLn1 = "awk -F'\x7C' '".$this->_CURRENT_STATUS_FILTER." {print $0}' ".$tmp_files['original'].' > '.$tmp_files['first'];
#filtri spremenljivk
$cmdLn1_1 = 'cut -d \'|\' -f '.$this->_VARIABLE_FILTER.' '.$tmp_files['first'].' > '.$tmp_files['first1'];
#zamenjamo ' => `
$cmdLn2 = 'sed \'s/\x27/\x60/g\' '.$tmp_files['first1'].' > '.$tmp_files['second'];
# zamenjamo | z ' '
$cmdLn3 = 'sed \'s/'.STR_DLMT.'/\x27 \x27/g\' '.$tmp_files['second'].' > '.$tmp_files['third'];
# dodamo ' na začetek in konec
$this->_QUOTE = '\'';
$cmdLn4 = 'awk \'{print "\x27"$0"\x27"}\' '.$tmp_files['third'].' > '.$tmp_files['fourth'];
}
$out1 = shell_exec($cmdLn1);
$out1_1 = shell_exec($cmdLn1_1);
$out2 = shell_exec($cmdLn2);
$out3 = shell_exec($cmdLn3);
$out4 = shell_exec($cmdLn4);
if (isset($_GET['debug']) && $_GET['debug'] == 1) {
print_r('<br>'.$cmdLn1);
print_r('<br>'.$cmdLn2);
print_r('<br>'.$cmdLn3);
print_r('<br>'.$cmdLn4);
}
# nardimo output
$convertType = 1; // kateri tip konvertiranja uporabimo
$convertTypes[1] = array('charSet' => "windows-1250",
'delimit' => ";",
'newLine' => "\n",
'BOMchar' => "\xEF\xBB\xBF");
if ($mode == "download") {
# izvedemo download
if ($fd = fopen ($tmp_files['fourth'], "r")) {
ob_clean();
$fsize = filesize($tmp_files['fourth']);
$path_parts = pathinfo($tmp_files['fourth']);
$ext = strtolower($path_parts["extension"]);
header('Content-type: text/plain; charset='.$convertTypes[$convertType]['charSet']);
header('Content-Disposition: attachment; filename="anketa'.$this->sid.'-'.date('Y-m-d').'_podatki.txt"');
#header("Content-length: $fsize");
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Cache-Control: private',false);
header('Content-Transfer-Encoding: binary');
ob_flush();
# dodami boomchar za utf-8
echo $convertTypes[$convertType]['BOMchar'];
while(!feof($fd)) {
$buffer = fread($fd, 2048);
echo $buffer;
}
}
else {
echo "Napaka";
}
fclose ($fd);
}
// priprava za SAV
else {
copy ($tmp_files['fourth'], $this->folder .'tmp_spss2sav' .$this->sid.'.a00');
}
#pobrišemo vse tmp datoteke
if (file_exists($tmp_files['first'])) { unlink($tmp_files['first']); }
if (file_exists($tmp_files['first1'])) { unlink($tmp_files['first1']); }
if (file_exists($tmp_files['second'])) { unlink($tmp_files['second']); }
if (file_exists($tmp_files['third'])) { unlink($tmp_files['third']); }
if (file_exists($tmp_files['fourth'])) { unlink($tmp_files['fourth']); }
} // end if data = true
}
else {
}
if ($mode == "download") {
ob_flush();
exit;
}
else {
return;
}
}
/** Preveri ali obstajata datoteki z podatki in headerji in ali sta zadnji ažurni
*
*/
public function checkFile() {
$SDF = SurveyDataFile::get_instance();
$SDF->init($this->sid);
$this->_headFileName = $SDF->getHeaderFileName();
$this->_dataFileName = $SDF->getDataFileName();
$this->_fileStatus = $SDF->getStatus();
return $this->_fileStatus;
}
private function create_array_SPSS($max_missing) {
$array_SPSS = array();
# poberemo max dolžine iz srv_data_text max(text1,text2)
$str_query = 'SELECT dt.spr_id, MAX(LENGTH(dt.text)) AS length, MAX(LENGTH(dt.text2)) AS length2 FROM srv_data_text'.$this->db_table.' dt, srv_grupa g, srv_spremenljivka s WHERE dt.spr_id = s.id AND s.gru_id=g.id AND g.ank_id='.$this->sid.' GROUP BY dt.spr_id';
$_qry_SPSS = sisplet_query($str_query);
while (list($spr_id,$text,$text2) = mysqli_fetch_row($_qry_SPSS)) {
$array_SPSS[$spr_id] = max((int)$text,(int)$text2,$max_missing);
}
$str_query = 'SELECT dt.spr_id, MAX(LENGTH(dt.text)) AS length FROM srv_data_textgrid'.$this->db_table.' AS dt, srv_grupa g, srv_spremenljivka s WHERE dt.spr_id = s.id AND s.gru_id=g.id AND g.ank_id='.$this->sid.' GROUP BY dt.spr_id';
$_qry_SPSS = sisplet_query($str_query);
while (list($spr_id,$text) = mysqli_fetch_row($_qry_SPSS)) {
$array_SPSS[$spr_id] = isset($array_SPSS[$spr_id]) ? max((int)$text,$array_SPSS[$spr_id],$max_missing) : max((int)$text,0,$max_missing);
}
return $array_SPSS;
}
public function ajax() {
if ($_GET['a'] == 'doexport') {
self :: DoExport();
}
}
function getCleanString($string) {
// Replace quotov
$string = preg_replace ("/'/", "`", $string);
// Max dolžina stringa je 240 znakov
$string = $this->splitStringIntoLines($string);
return $string;
}
// Max dolžina stringa je 200 znakov - razbijemo v chunke za spss
private function splitStringIntoLines($string){
if(strlen($string) <= 200)
return $string;
$new_string = chunk_split($string, 200, '\'+'.NEW_LINE.'\'');
$new_string = substr($new_string, 0, -4);
return $new_string;
}
/* private function getVariableLabels($spr_id){
global $lang;
$s = sisplet_query("SELECT tip FROM srv_spremenljivka WHERE id = '$spr_id'");
$r = mysqli_fetch_array($s);
if ( in_array($r['tip'], array(1, 3)) ) {
$output = array();
$output['spr'] = $spr_id;
$output['tip'] = $r['tip'];
$output['values'] = array();
$sql = sisplet_query("SELECT naslov, variable FROM srv_vrednost WHERE spr_id='$spr_id' ORDER BY vrstni_red ASC");
while ($row = mysqli_fetch_array($sql)) {
$output['values'][$row['variable']] = strip_tags( $row['naslov'] );
}
$output['values']['-1'] = $lang['srv_bottom_data_legend_note_li1a'];
$output['values']['-2'] = $lang['srv_bottom_data_legend_note_li2a'];
$output['values']['-3'] = $lang['srv_bottom_data_legend_note_li3a'];
$output['values']['-4'] = $lang['srv_bottom_data_legend_note_li4a'];
$output['values']['-5'] = $lang['srv_bottom_data_legend_note_li5a'];
$response[] = $output;
} elseif ( in_array($r['tip'], array(6, 16)) ) {
$output = array();
$output['spr'] = $spr_id;
$output['tip'] = $r['tip'];
$output['values'] = array();
$sql = sisplet_query("SELECT naslov, variable FROM srv_grid WHERE spr_id='$spr_id' ORDER BY vrstni_red ASC");
while ($row = mysqli_fetch_array($sql)) {
$output['values'][$row['variable']] = strip_tags( $row['naslov'] );
}
$output['values']['-1'] = $lang['srv_bottom_data_legend_note_li1a'];
$output['values']['-2'] = $lang['srv_bottom_data_legend_note_li2a'];
$output['values']['-3'] = $lang['srv_bottom_data_legend_note_li3a'];
$output['values']['-4'] = $lang['srv_bottom_data_legend_note_li4a'];
$output['values']['-5'] = $lang['srv_bottom_data_legend_note_li5a'];
$response[] = $output;
}
return $response;
} */
}
?>
