Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 523 lines (497 sloc) 20.965 kb
c88a74c Fix line ending
c_schmitz authored
1 <?php
2 /*
3 * LimeSurvey
4 * Copyright (C) 2007 The LimeSurvey Project Team / Carsten Schmitz
5 * All rights reserved.
6 * License: GNU/GPL License v2 or later, see LICENSE.php
7 * LimeSurvey is free software. This version may have been modified pursuant
8 * to the GNU General Public License, and as distributed it includes or
9 * is derivative of works licensed under the GNU General Public License or
10 * other free or open source software licenses.
11 * See COPYRIGHT.php for copyright notices and details.
12 *
13 * $Id: export_data_functions.php 11664 2011-12-16 05:19:42Z tmswhite $
14 */
15
16 /**
17 * Strips html tags and replaces new lines
18 *
19 * @param $string
20 * @return $string
21 */
22 function strip_tags_full($string) {
23 $string=html_entity_decode($string, ENT_QUOTES, "UTF-8");
24 //combining these into one mb_ereg_replace call ought to speed things up
25 //$string = str_replace(array("\r\n","\r","\n",'-oth-'), '', $string);
26 //The backslashes must be escaped twice, once for php, and again for the regexp
27 //$string = str_replace("'|\\\\'", "&apos;", $string);
28 return FlattenText($string);
29 }
30
31 /**
32 * Returns true if passed $value is numeric
33 *
34 * @param $value
35 * @return bool
36 */
37 function my_is_numeric($value) {
38 if (empty($value)) return true;
39 $eng_or_world = preg_match
40 ('/^[+-]?'. // start marker and sign prefix
41 '(((([0-9]+)|([0-9]{1,4}(,[0-9]{3,4})+)))?(\\.[0-9])?([0-9]*)|'. // american
42 '((([0-9]+)|([0-9]{1,4}(\\.[0-9]{3,4})+)))?(,[0-9])?([0-9]*))'. // world
43 '(e[0-9]+)?'. // exponent
44 '$/', // end marker
45 $value) == 1;
46 return ($eng_or_world);
47 }
48
49 function spss_export_data ($na = null) {
50 global $length_data;
51
52 // Build array that has to be returned
53 $fields = spss_fieldmap();
54
55 //Now get the query string with all fields to export
56 $query = spss_getquery();
57
58 $result=db_execute_num($query) or safe_die("Couldn't get results<br />$query<br />".$connect->ErrorMsg()); //Checked
59 $num_fields = $result->FieldCount();
60
61 //This shouldn't occur, but just to be safe:
62 if (count($fields)<>$num_fields) safe_die("Database inconsistency error");
63
64 while (!$result->EOF) {
65 $row = $result->GetRowAssoc(true); //Get assoc array, use uppercase
66 reset($fields); //Jump to the first element in the field array
67 $i = 1;
68 foreach ($fields as $field)
69 {
70 $fieldno = strtoupper($field['sql_name']);
71 if ($field['SPSStype']=='DATETIME23.2'){
72 #convert mysql datestamp (yyyy-mm-dd hh:mm:ss) to SPSS datetime (dd-mmm-yyyy hh:mm:ss) format
73 if (isset($row[$fieldno]))
74 {
75 list( $year, $month, $day, $hour, $minute, $second ) = preg_split( '([^0-9])', $row[$fieldno] );
76 if ($year != '' && (int)$year >= 1970)
77 {
78 echo "'".date('d-m-Y H:i:s', mktime( $hour, $minute, $second, $month, $day, $year ) )."'";
79 } else
80 {
81 echo ($na);
82 }
83 } else
84 {
85 echo ($na);
86 }
87 } else if ($field['LStype'] == 'Y')
88 {
89 if ($row[$fieldno] == 'Y') // Yes/No Question Type
90 {
91 echo( "'1'");
92 } else if ($row[$fieldno] == 'N'){
93 echo( "'2'");
94 } else {
95 echo($na);
96 }
97 } else if ($field['LStype'] == 'G') //Gender
98 {
99 if ($row[$fieldno] == 'F')
100 {
101 echo( "'1'");
102 } else if ($row[$fieldno] == 'M'){
103 echo( "'2'");
104 } else {
105 echo($na);
106 }
107 } else if ($field['LStype'] == 'C') //Yes/No/Uncertain
108 {
109 if ($row[$fieldno] == 'Y')
110 {
111 echo( "'1'");
112 } else if ($row[$fieldno] == 'N'){
113 echo( "'2'");
114 } else if ($row[$fieldno] == 'U'){
115 echo( "'3'");
116 } else {
117 echo($na);
118 }
119 } else if ($field['LStype'] == 'E') //Increase / Same / Decrease
120 {
121 if ($row[$fieldno] == 'I')
122 {
123 echo( "'1'");
124 } else if ($row[$fieldno] == 'S'){
125 echo( "'2'");
126 } else if ($row[$fieldno] == 'D'){
127 echo( "'3'");
128 } else {
129 echo($na);
130 }
131 } elseif (($field['LStype'] == 'P' || $field['LStype'] == 'M') && (substr($field['code'],-7) != 'comment' && substr($field['code'],-5) != 'other'))
132 {
133 if ($row[$fieldno] == 'Y')
134 {
135 echo("'1'");
136 } else
137 {
138 echo("'0'");
139 }
140 } elseif (!$field['hide']) {
141 $strTmp=mb_substr(strip_tags_full($row[$fieldno]), 0, $length_data);
142 if (trim($strTmp) != ''){
143 $strTemp=str_replace(array("'","\n","\r"),array("''",' ',' '),trim($strTmp));
144 /*
145 * Temp quick fix for replacing decimal dots with comma's
146 if (my_is_numeric($strTemp)) {
147 $strTemp = str_replace('.',',',$strTemp);
148 }
149 */
150 echo "'$strTemp'";
151 }
152 else
153 {
154 echo $na;
155 }
156 }
157 if ($i<$num_fields && !$field['hide']) echo ',';
158 $i++;
159 }
160 echo "\n";
161 $result->MoveNext();
162 }
163 }
164
165 /**
166 * Check it the gives field has a labelset and return it as an array if true
167 *
168 * @param $field array field from spss_fieldmap
169 * @return array or false
170 */
171 function spss_getvalues ($field = array(), $qidattributes = null ) {
172 global $surveyid, $dbprefix, $connect, $clang, $language, $length_vallabel;
173
174 if (!isset($field['LStype']) || empty($field['LStype'])) return false;
175 $answers=array();
176 if (strpos("!LORFWZWH1",$field['LStype']) !== false) {
177 if (substr($field['code'],-5) == 'other' || substr($field['code'],-7) == 'comment') {
178 //We have a comment field, so free text
179 } else {
180 $query = "SELECT {$dbprefix}answers.code, {$dbprefix}answers.answer,
181 {$dbprefix}questions.type FROM {$dbprefix}answers, {$dbprefix}questions WHERE";
182
183 if (isset($field['scale_id'])) $query .= " {$dbprefix}answers.scale_id = " . (int) $field['scale_id'] . " AND";
184
185 $query .= " {$dbprefix}answers.qid = '".$field["qid"]."' and {$dbprefix}questions.language='".$language."' and {$dbprefix}answers.language='".$language."'
186 and {$dbprefix}questions.qid='".$field['qid']."' ORDER BY sortorder ASC";
187 $result=db_execute_assoc($query) or safe_die("Couldn't lookup value labels<br />$query<br />".$connect->ErrorMsg()); //Checked
188 $num_results = $result->RecordCount();
189 if ($num_results > 0)
190 {
191 $displayvaluelabel = 0;
192 # Build array that has to be returned
193 for ($i=0; $i < $num_results; $i++)
194 {
195 $row = $result->FetchRow();
196 $answers[] = array('code'=>$row['code'], 'value'=>mb_substr(strip_tags_full($row["answer"]),0,$length_vallabel));
197 }
198 }
199 }
200 } elseif ($field['LStype'] == ':') {
201 $displayvaluelabel = 0;
202 //Get the labels that could apply!
203 if (is_null($qidattributes)) $qidattributes=getQuestionAttributes($field["qid"], $field['LStype']);
204 if (trim($qidattributes['multiflexible_max'])!='') {
205 $maxvalue=$qidattributes['multiflexible_max'];
206 } else {
207 $maxvalue=10;
208 }
209 if (trim($qidattributes['multiflexible_min'])!='')
210 {
211 $minvalue=$qidattributes['multiflexible_min'];
212 } else {
213 $minvalue=1;
214 }
215 if (trim($qidattributes['multiflexible_step'])!='')
216 {
217 $stepvalue=$qidattributes['multiflexible_step'];
218 } else {
219 $stepvalue=1;
220 }
221 if ($qidattributes['multiflexible_checkbox']!=0) {
222 $minvalue=0;
223 $maxvalue=1;
224 $stepvalue=1;
225 }
226 for ($i=$minvalue; $i<=$maxvalue; $i+=$stepvalue)
227 {
228 $answers[] = array('code'=>$i, 'value'=>$i);
229 }
230 } elseif ($field['LStype'] == 'M' && substr($field['code'],-5) != 'other' && $field['size'] > 0)
231 {
232 $answers[] = array('code'=>1, 'value'=>$clang->gT('Yes'));
233 $answers[] = array('code'=>0, 'value'=>$clang->gT('Not Selected'));
234 } elseif ($field['LStype'] == "P" && substr($field['code'],-5) != 'other' && substr($field['code'],-7) != 'comment')
235 {
236 $answers[] = array('code'=>1, 'value'=>$clang->gT('Yes'));
237 $answers[] = array('code'=>0, 'value'=>$clang->gT('Not Selected'));
238 } elseif ($field['LStype'] == "G" && $field['size'] > 0)
239 {
240 $answers[] = array('code'=>1, 'value'=>$clang->gT('Female'));
241 $answers[] = array('code'=>2, 'value'=>$clang->gT('Male'));
242 } elseif ($field['LStype'] == "Y" && $field['size'] > 0)
243 {
244 $answers[] = array('code'=>1, 'value'=>$clang->gT('Yes'));
245 $answers[] = array('code'=>2, 'value'=>$clang->gT('No'));
246 } elseif ($field['LStype'] == "C" && $field['size'] > 0)
247 {
248 $answers[] = array('code'=>1, 'value'=>$clang->gT('Yes'));
249 $answers[] = array('code'=>2, 'value'=>$clang->gT('No'));
250 $answers[] = array('code'=>3, 'value'=>$clang->gT('Uncertain'));
251 } elseif ($field['LStype'] == "E" && $field['size'] > 0)
252 {
253 $answers[] = array('code'=>1, 'value'=>$clang->gT('Increase'));
254 $answers[] = array('code'=>2, 'value'=>$clang->gT('Same'));
255 $answers[] = array('code'=>3, 'value'=>$clang->gT('Decrease'));
256 }
257 if (count($answers)>0) {
258 //check the max width of the answers
259 $size = 0;
260 $spsstype = $field['SPSStype'];
261 foreach ($answers as $answer) {
262 $len = mb_strlen($answer['code']);
263 if ($len>$size) $size = $len;
264 if ($spsstype=='F' && (my_is_numeric($answer['code'])===false || $size>16)) $spsstype='A';
265 }
266 $answers['SPSStype'] = $spsstype;
267 $answers['size'] = $size;
268 return $answers;
269 } else {
270 return false;
271 }
272 }
273
274 /**
275 * Creates a fieldmap with all information necessary to output the fields
276 *
277 * @param $prefix string prefix for the variable ID
278 * @return array
279 */
280 function spss_fieldmap($prefix = 'V') {
281 global $surveyid, $dbprefix, $typeMap, $connect, $clang;
282 global $surveyprivate, $tokensexist, $language;
283
284 $fieldmap = createFieldMap($surveyid, 'full'); //Create a FULL fieldmap
285
286 #See if tokens are being used
287 $tokensexist = tableExists('tokens_'.$surveyid);
288
289 #Lookup the names of the attributes
290 $query="SELECT sid, anonymized, language FROM {$dbprefix}surveys WHERE sid=$surveyid";
291 $result=db_execute_assoc($query) or safe_die("Couldn't count fields<br />$query<br />".$connect->ErrorMsg()); //Checked
292 $num_results = $result->RecordCount();
293 $num_fields = $num_results;
294 # Build array that has to be returned
295 for ($i=0; $i < $num_results; $i++) {
296 $row = $result->FetchRow();
297 $surveyprivate=$row['anonymized'];
298 $language=$row['language'];
299 }
300
301 $fieldno=0;
302
303 $fields=array();
304 if (isset($tokensexist) && $tokensexist == true && $surveyprivate == 'N') {
305 $tokenattributes=GetTokenFieldsAndNames($surveyid,false);
306 foreach ($tokenattributes as $attributefield=>$attributedescription)
307 {
308 //Drop the token field, since it is in the survey too
309 if($attributefield!='token') {
310 $fieldno++;
311 $fields[] = array('id'=>"$prefix$fieldno",'name'=>mb_substr($attributefield, 0, 8),
312 'qid'=>0,'code'=>'','SPSStype'=>'A','LStype'=>'Undef',
313 'VariableLabel'=>$attributedescription,'sql_name'=>$attributefield,'size'=>'100',
314 'title'=>$attributefield,'hide'=>0, 'scale'=>'');
315 }
316 }
317 }
318
319 $tempArray = array();
320 $fieldnames = array_values($connect->MetaColumnNames("{$dbprefix}survey_$surveyid", true));
321 $num_results = count($fieldnames);
322 $num_fields = $num_results;
323 $diff = 0;
324 $noQID = Array('id', 'token', 'datestamp', 'submitdate', 'startdate', 'startlanguage', 'ipaddr', 'refurl', 'lastpage');
325 # Build array that has to be returned
326 for ($i=0; $i < $num_results; $i++) {
327 #Conditions for SPSS fields:
328 # - Length may not be longer than 8 characters
329 # - Name may not begin with a digit
330 $fieldname = $fieldnames[$i];
331 $fieldtype = '';
332 $ftype='';
333 $val_size = 1;
334 $hide = 0;
335 $export_scale = '';
336 $code='';
337 $scale_id = null;
338 $aQuestionAttribs=array();
339
340 #Determine field type
341 if ($fieldname=='submitdate' || $fieldname=='startdate' || $fieldname == 'datestamp') {
342 $fieldtype = 'DATETIME23.2';
343 } elseif ($fieldname=='startlanguage') {
344 $fieldtype = 'A';
345 $val_size = 19;
346 } elseif ($fieldname=='token') {
347 $fieldtype = 'A';
348 $val_size = 16;
349 } elseif ($fieldname=='id') {
350 $fieldtype = 'F';
351 $val_size = 7; //Arbitrarilty restrict to 9,999,999 (7 digits) responses/survey
352 } elseif ($fieldname == 'ipaddr') {
353 $fieldtype = 'A';
354 $val_size = 15;
355 } elseif ($fieldname == 'refurl') {
356 $fieldtype = 'A';
357 $val_size = 255;
358 } elseif ($fieldname == 'lastpage') {
359 $hide = 1;
360 }
361
362 #Get qid (question id)
363 if (in_array($fieldname, $noQID) || substr($fieldname,0,10)=='attribute_'){
364 $qid = 0;
365 $varlabel = $fieldname;
366 $ftitle = $fieldname;
367 } else{
368 //GET FIELD DATA
369 if (!isset($fieldmap[$fieldname])) {
370 //Field in database but no longer in survey... how is this possible?
371 //@TODO: think of a fix.
372 $fielddata = array();
373 $qid=0;
374 $varlabel = $fieldname;
375 $ftitle = $fieldname;
376 $fieldtype = "F";
377 $val_size = 1;
378 } else {
379 $fielddata=$fieldmap[$fieldname];
380 $qid=$fielddata['qid'];
381 $ftype=$fielddata['type'];
382 $fsid=$fielddata['sid'];
383 $fgid=$fielddata['gid'];
384 $code=mb_substr($fielddata['fieldname'],strlen($fsid."X".$fgid."X".$qid));
385 $varlabel=$fielddata['question'];
386 if (isset($fielddata['scale'])) $varlabel = "[{$fielddata['scale']}] ". $varlabel;
387 if (isset($fielddata['subquestion'])) $varlabel = "[{$fielddata['subquestion']}] ". $varlabel;
388 if (isset($fielddata['subquestion2'])) $varlabel = "[{$fielddata['subquestion2']}] ". $varlabel;
389 if (isset($fielddata['subquestion1'])) $varlabel = "[{$fielddata['subquestion1']}] ". $varlabel;
390 $ftitle=$fielddata['title'];
391 if (!is_null($code) && $code<>"" ) $ftitle .= "_$code";
392 if (isset($typeMap[$ftype]['size'])) $val_size = $typeMap[$ftype]['size'];
393 if (isset($fielddata['scale_id'])) $scale_id = $fielddata['scale_id'];
394 if($fieldtype == '') $fieldtype = $typeMap[$ftype]['SPSStype'];
395 if (isset($typeMap[$ftype]['hide'])) {
396 $hide = $typeMap[$ftype]['hide'];
397 $diff++;
398 }
399 //Get default scale for this type
400 if (isset($typeMap[$ftype]['Scale'])) $export_scale = $typeMap[$ftype]['Scale'];
401 //But allow override
402 $aQuestionAttribs = getQuestionAttributes($qid,$ftype);
403 if (isset($aQuestionAttribs['scale_export'])) $export_scale = $aQuestionAttribs['scale_export'];
404 }
405
406 }
407 $fieldno++;
408 $fid = $fieldno - $diff;
409 $lsLong = isset($typeMap[$ftype]["name"])?$typeMap[$ftype]["name"]:$ftype;
410 $tempArray = array('id'=>"$prefix$fid",'name'=>mb_substr($fieldname, 0, 8),
411 'qid'=>$qid,'code'=>$code,'SPSStype'=>$fieldtype,'LStype'=>$ftype,"LSlong"=>$lsLong,
412 'ValueLabels'=>'','VariableLabel'=>$varlabel,"sql_name"=>$fieldname,"size"=>$val_size,
413 'title'=>$ftitle,'hide'=>$hide,'scale'=>$export_scale, 'scale_id'=>$scale_id);
414 //Now check if we have to retrieve value labels
415 $answers = spss_getvalues($tempArray, $aQuestionAttribs);
416 if (is_array($answers)) {
417 //Ok we have answers
418 if (isset($answers['size'])) {
419 $tempArray['size'] = $answers['size'];
420 unset($answers['size']);
421 }
422 if (isset($answers['SPSStype'])) {
423 $tempArray['SPSStype'] = $answers['SPSStype'];
424 unset($answers['SPSStype']);
425 }
426 $tempArray['answers'] = $answers;
427 }
428 $fields[] = $tempArray;
429 }
430 return $fields;
431 }
432
433 /**
434 * Creates a query string with all fields for the export
435 *
436 * @return string
437 */
438 function spss_getquery() {
439 global $surveyprivate, $dbprefix, $surveyid, $tokensexist;
440
441 #See if tokens are being used
442 if (isset($tokensexist) && $tokensexist == true && $surveyprivate == 'N') {
443 $query="SELECT ";
444 $tokenattributes=GetTokenFieldsAndNames($surveyid,false);
445 foreach ($tokenattributes as $attributefield=>$attributedescription) {
446 //Drop the token field, since it is in the survey too
447 if($attributefield!='token') {
448 $query .= "{$dbprefix}tokens_$surveyid.$attributefield, ";
449 }
450 }
451 $query .= "{$dbprefix}survey_$surveyid.*
452 FROM {$dbprefix}survey_$surveyid
453 LEFT JOIN {$dbprefix}tokens_$surveyid ON {$dbprefix}survey_$surveyid.token = {$dbprefix}tokens_$surveyid.token";
454 } else {
455 $query = "SELECT *
456 FROM {$dbprefix}survey_$surveyid";
457 }
458 switch (incompleteAnsFilterstate()) {
459 case 'inc':
460 //Inclomplete answers only
461 $query .= ' WHERE submitdate is null ';
462 break;
463 case 'filter':
464 //Inclomplete answers only
465 $query .= ' WHERE submitdate is not null ';
466 break;
467 }
468 return $query;
469 }
470
471 /**
472 * BuildXMLFromQuery() creates a datadump of a table in XML using XMLWriter
473 *
474 * @param mixed $xmlwriter The existing XMLWriter object
475 * @param mixed $Query The table query to build from
476 * @param mixed $tagname If the XML tag of the resulting question should be named differently than the table name set it here
477 * @param array $excludes array of columnames not to include in export
478 */
479 function BuildXMLFromQuery($xmlwriter, $Query, $tagname='', $excludes = array())
480 {
481 global $dbprefix, $connect;
482 $QueryResult = db_execute_assoc($Query) or safe_die ("ERROR: $QueryResult<br />".$connect->ErrorMsg()); //safe
483 preg_match('/FROM (\w+)( |,)/', $Query, $MatchResults);
484 if ($tagname!='')
485 {
486 $TableName=$tagname;
487 }
488 else
489 {
490 $TableName = $MatchResults[1];;
491 $TableName = substr($TableName, strlen($dbprefix), strlen($TableName));
492 }
493 if ($QueryResult->RecordCount()>0)
494 {
495 $exclude = array_flip($excludes); //Flip key/value in array for faster checks
496 $xmlwriter->startElement($TableName);
497 $xmlwriter->startElement('fields');
498 $Columninfo = $QueryResult->fields;
499 foreach ($Columninfo as $fieldname=>$value)
500 {
501 if (!isset($exclude[$fieldname])) $xmlwriter->writeElement('fieldname',$fieldname);
502 }
503 $xmlwriter->endElement(); // close columns
504 $xmlwriter->startElement('rows');
505 while ($Row = $QueryResult->FetchRow())
506 {
507 $xmlwriter->startElement('row');
508 foreach ($Row as $Key=>$Value)
509 {
510 if (!isset($exclude[$Key])) {
511 $xmlwriter->startElement($Key);
512 // Remove invalid XML characters
513 $xmlwriter->writeCData(preg_replace('/[^\x9\xA\xD\x20-\x{D7FF}\x{E000}-\x{FFFD}\x{10000}-\x{10FFFF}]/u','',$Value));
514 $xmlwriter->endElement();
515 }
516 }
517 $xmlwriter->endElement(); // close row
518 }
519 $xmlwriter->endElement(); // close rows
520 $xmlwriter->endElement(); // close tablename
521 }
522 }
Something went wrong with that request. Please try again.