Merge branch MDL-29189-master of git://github.com/samhemelryk/moodle
[moodle.git] / mod / feedback / analysis_to_excel.php
1 <?php
3 /**
4 * prints an analysed excel-spreadsheet of the feedback
5 *
6 * @author Andreas Grabs
7 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
8 * @package feedback
9 */
11 require_once("../../config.php");
12 require_once("lib.php");
13 // require_once('easy_excel.php');
14 require_once("$CFG->libdir/excellib.class.php");
16 feedback_load_feedback_items();
18 $id = required_param('id', PARAM_INT);  //the POST dominated the GET
19 $coursefilter = optional_param('coursefilter', '0', PARAM_INT);
21 $url = new moodle_url('/mod/feedback/analysis_to_excel.php', array('id'=>$id));
22 if ($coursefilter !== '0') {
23     $url->param('coursefilter', $coursefilter);
24 }
25 $PAGE->set_url($url);
27 $formdata = data_submitted();
29 if (! $cm = get_coursemodule_from_id('feedback', $id)) {
30     print_error('invalidcoursemodule');
31 }
33 if (! $course = $DB->get_record("course", array("id"=>$cm->course))) {
34     print_error('coursemisconf');
35 }
37 if (! $feedback = $DB->get_record("feedback", array("id"=>$cm->instance))) {
38     print_error('invalidcoursemodule');
39 }
41 if (!$context = get_context_instance(CONTEXT_MODULE, $cm->id)) {
42         print_error('badcontext');
43 }
45 require_login($course->id, true, $cm);
47 require_capability('mod/feedback:viewreports', $context);
49 //buffering any output
50 //this prevents some output before the excel-header will be send
51 ob_start();
52 $fstring = new stdClass();
53 $fstring->bold = get_string('bold', 'feedback');
54 $fstring->page = get_string('page', 'feedback');
55 $fstring->of = get_string('of', 'feedback');
56 $fstring->modulenameplural = get_string('modulenameplural', 'feedback');
57 $fstring->questions = get_string('questions', 'feedback');
58 $fstring->itemlabel = get_string('item_label', 'feedback');
59 $fstring->question = get_string('question', 'feedback');
60 $fstring->responses = get_string('responses', 'feedback');
61 $fstring->idnumber = get_string('idnumber');
62 $fstring->username = get_string('username');
63 $fstring->fullname = get_string('fullnameuser');
64 $fstring->courseid = get_string('courseid', 'feedback');
65 $fstring->course = get_string('course');
66 $fstring->anonymous_user = get_string('anonymous_user','feedback');
67 ob_end_clean();
69 //get the questions (item-names)
70 if(!$items = $DB->get_records('feedback_item', array('feedback'=>$feedback->id, 'hasvalue'=>1), 'position')) {
71     print_error('no_items_available_yet', 'feedback', $CFG->wwwroot.'/mod/feedback/view.php?id='.$id);
72     exit;
73 }
75 $filename = "feedback.xls";
77 $mygroupid = groups_get_activity_group($cm);
79 // Creating a workbook
80 // $workbook = new EasyWorkbook("-");
81 $workbook = new MoodleExcelWorkbook('-');
82 // $workbook->setTempDir($CFG->tempdir.'');
83 $workbook->send($filename);
84 // $workbook->setVersion(8);
86 //creating the needed formats
87 $xlsFormats = new stdClass();
88 $xlsFormats->head1 = $workbook->add_format(array(
89                         'bold'=>1,
90                         'size'=>12));
92 $xlsFormats->head2 = $workbook->add_format(array(
93                         'align'=>'left',
94                         'bold'=>1,
95                         'bottum'=>2));
97 $xlsFormats->default = $workbook->add_format(array(
98                         'align'=>'left',
99                         'v_align'=>'top'));
101 // $xlsFormats->head2_green = $workbook->add_format(array(
102                         // 'align'=>'left',
103                         // 'bold'=>1,
104                         // 'v_align'=>'top',
105                         // 'bottum'=>2,
106                         // 'fg_color'=>'green'));
108 $xlsFormats->value_bold = $workbook->add_format(array(
109                         'align'=>'left',
110                         'bold'=>1,
111                         'v_align'=>'top'));
113 // $xlsFormats->value_blue = $workbook->add_format(array(
114                         // 'align'=>'left',
115                         // 'bold'=>1,
116                         // 'v_align'=>'top',
117                         // 'top'=>2,
118                         // 'fg_color'=>'blue'));
120 // $xlsFormats->value_red = $workbook->add_format(array(
121                         // 'align'=>'left',
122                         // 'bold'=>1,
123                         // 'v_align'=>'top',
124                         // 'top'=>2,
125                         // 'fg_color'=>'red'));
127 $xlsFormats->procent = $workbook->add_format(array(
128                         'align'=>'left',
129                         'bold'=>1,
130                         'v_align'=>'top',
131                         'num_format'=>'#,##0.00%'));
133 // Creating the worksheets
134 $sheetname = clean_param($feedback->name, PARAM_ALPHANUM);
135 error_reporting(0);
136 $worksheet1 =& $workbook->add_worksheet(substr($sheetname, 0, 31));
137 // $worksheet1->set_workbook($workbook);
138 $worksheet2 =& $workbook->add_worksheet('detailed');
139 // $worksheet2->set_workbook($workbook);
140 error_reporting($CFG->debug);
141 // $worksheet1->pear_excel_worksheet->set_portrait();
142 // $worksheet1->pear_excel_worksheet->set_paper(9);
143 // $worksheet1->pear_excel_worksheet->center_horizontally();
144 $worksheet1->hide_gridlines();
145 // $worksheet1->pear_excel_worksheet->set_header("&\"Arial," . $fstring->bold . "\"&14".$feedback->name);
146 // $worksheet1->pear_excel_worksheet->set_footer($fstring->page." &P " . $fstring->of . " &N");
147 $worksheet1->set_column(0, 0, 10);
148 $worksheet1->set_column(1, 1, 30);
149 $worksheet1->set_column(2, 20, 15);
150 // $worksheet1->set_margins_LR(0.10);
152 // $worksheet2->pear_excel_worksheet->set_landscape();
153 // $worksheet2->pear_excel_worksheet->set_paper(9);
154 // $worksheet2->pear_excel_worksheet->center_horizontally();
156 //writing the table header
157 $rowOffset1 = 0;
158 // $worksheet1->setFormat("<f>",12,false);
159 $worksheet1->write_string($rowOffset1, 0, UserDate(time()), $xlsFormats->head1);
161 ////////////////////////////////////////////////////////////////////////
162 //print the analysed sheet
163 ////////////////////////////////////////////////////////////////////////
164 //get the completeds
165 $completedscount = feedback_get_completeds_group_count($feedback, $mygroupid, $coursefilter);
166 if($completedscount > 0){
167     //write the count of completeds
168     $rowOffset1++;
169     $worksheet1->write_string($rowOffset1, 0, $fstring->modulenameplural.': '.strval($completedscount), $xlsFormats->head1);
172 if(is_array($items)){
173     $rowOffset1++;
174     $worksheet1->write_string($rowOffset1, 0, $fstring->questions.': '. strval(sizeof($items)), $xlsFormats->head1);
177 $rowOffset1 += 2;
178 $worksheet1->write_string($rowOffset1, 0, $fstring->itemlabel, $xlsFormats->head1);
179 $worksheet1->write_string($rowOffset1, 1, $fstring->question, $xlsFormats->head1);
180 $worksheet1->write_string($rowOffset1, 2, $fstring->responses, $xlsFormats->head1);
181 $rowOffset1++ ;
183 if (empty($items)) {
184      $items=array();
186 foreach($items as $item) {
187     //get the class of item-typ
188     $itemobj = feedback_get_item_class($item->typ);
189     $rowOffset1 = $itemobj->excelprint_item($worksheet1, $rowOffset1, $xlsFormats, $item, $mygroupid, $coursefilter);
192 ////////////////////////////////////////////////////////////////////////
193 //print the detailed sheet
194 ////////////////////////////////////////////////////////////////////////
195 //get the completeds
197 $completeds = feedback_get_completeds_group($feedback, $mygroupid, $coursefilter);
198 //important: for each completed you have to print each item, even if it is not filled out!!!
199 //therefor for each completed we have to iterate over all items of the feedback
200 //this is done by feedback_excelprint_detailed_items
202 $rowOffset2 = 0;
203 //first we print the table-header
204 $rowOffset2 = feedback_excelprint_detailed_head($worksheet2, $xlsFormats, $items, $rowOffset2);
207 if(is_array($completeds)){
208     foreach($completeds as $completed) {
209         $rowOffset2 = feedback_excelprint_detailed_items($worksheet2, $xlsFormats, $completed, $items, $rowOffset2);
210     }
214 $workbook->close();
215 exit;
216 ////////////////////////////////////////////////////////////////////////////////
217 ////////////////////////////////////////////////////////////////////////////////
218 //functions
219 ////////////////////////////////////////////////////////////////////////////////
222 function feedback_excelprint_detailed_head(&$worksheet, $xlsFormats, $items, $rowOffset) {
223     global $fstring, $feedback;
225     if(!$items) return;
226     $colOffset = 0;
228     // $worksheet->setFormat('<l><f><ru2>');
230     $worksheet->write_string($rowOffset + 1, $colOffset, $fstring->idnumber, $xlsFormats->head2);
231     $colOffset++;
233     $worksheet->write_string($rowOffset + 1, $colOffset, $fstring->username, $xlsFormats->head2);
234     $colOffset++;
236     $worksheet->write_string($rowOffset + 1, $colOffset, $fstring->fullname, $xlsFormats->head2);
237     $colOffset++;
239     foreach($items as $item) {
240         // $worksheet->setFormat('<l><f><ru2>');
241         $worksheet->write_string($rowOffset, $colOffset, $item->name, $xlsFormats->head2);
242         $worksheet->write_string($rowOffset + 1, $colOffset, $item->label, $xlsFormats->head2);
243         $colOffset++;
244     }
246     // $worksheet->setFormat('<l><f><ru2>');
247     $worksheet->write_string($rowOffset + 1, $colOffset, $fstring->courseid, $xlsFormats->head2);
248     $colOffset++;
250     // $worksheet->setFormat('<l><f><ru2>');
251     $worksheet->write_string($rowOffset + 1, $colOffset, $fstring->course, $xlsFormats->head2);
252     $colOffset++;
254     return $rowOffset + 2;
257 function feedback_excelprint_detailed_items(&$worksheet, $xlsFormats, $completed, $items, $rowOffset) {
258     global $DB, $fstring;
260     if(!$items) return;
261     $colOffset = 0;
262     $courseid = 0;
264     $feedback = $DB->get_record('feedback', array('id'=>$completed->feedback));
265     //get the username
266     //anonymous users are separated automatically because the userid in the completed is "0"
267     // $worksheet->setFormat('<l><f><ru2>');
268     if($user = $DB->get_record('user', array('id'=>$completed->userid))) {
269         if ($completed->anonymous_response == FEEDBACK_ANONYMOUS_NO) {
270             $worksheet->write_string($rowOffset, $colOffset, $user->idnumber, $xlsFormats->head2);
271             $colOffset++;
272             $userfullname = fullname($user);
273             $worksheet->write_string($rowOffset, $colOffset, $user->username, $xlsFormats->head2);
274             $colOffset++;
275         } else {
276             $userfullname = $fstring->anonymous_user;
277             $worksheet->write_string($rowOffset, $colOffset, '-', $xlsFormats->head2);
278             $colOffset++;
279             $worksheet->write_string($rowOffset, $colOffset, '-', $xlsFormats->head2);
280             $colOffset++;
281         }
282     }else {
283         $userfullname = $fstring->anonymous_user;
284         $worksheet->write_string($rowOffset, $colOffset, '-', $xlsFormats->head2);
285         $colOffset++;
286         $worksheet->write_string($rowOffset, $colOffset, '-', $xlsFormats->head2);
287         $colOffset++;
288     }
290     $worksheet->write_string($rowOffset, $colOffset, $userfullname, $xlsFormats->head2);
292     $colOffset++;
293     foreach($items as $item) {
294         $value = $DB->get_record('feedback_value', array('item'=>$item->id, 'completed'=>$completed->id));
296         $itemobj = feedback_get_item_class($item->typ);
297         $printval = $itemobj->get_printval($item, $value);
298         $printval = trim($printval);
300         // $worksheet->setFormat('<l><vo>');
301         if(is_numeric($printval)) {
302             $worksheet->write_number($rowOffset, $colOffset, $printval, $xlsFormats->default);
303         } elseif($printval != '') {
304             $worksheet->write_string($rowOffset, $colOffset, $printval, $xlsFormats->default);
305         }
306         $printval = '';
307         $colOffset++;
308         $courseid = isset($value->course_id) ? $value->course_id : 0;
309         if($courseid == 0) $courseid = $feedback->course;
310     }
311     $worksheet->write_number($rowOffset, $colOffset, $courseid, $xlsFormats->default);
312     $colOffset++;
313     if (isset($courseid) AND $course = $DB->get_record('course', array('id' => $courseid))) {
314         $shortname = format_string($course->shortname, true, array('context' => get_context_instance(CONTEXT_COURSE, $courseid)));
315         $worksheet->write_string($rowOffset, $colOffset, $shortname, $xlsFormats->default);
316     }
317     return $rowOffset + 1;