grade/import/csv/index.php

   1 <?php  //$Id$
   2
   3 ///////////////////////////////////////////////////////////////////////////
   4 //                                                                       //
   5 // NOTICE OF COPYRIGHT                                                   //
   6 //                                                                       //
   7 // Moodle - Modular Object-Oriented Dynamic Learning Environment         //
   8 //          http://moodle.com                                            //
   9 //                                                                       //
  10 // Copyright (C) 1999 onwards  Martin Dougiamas  http://moodle.com       //
  11 //                                                                       //
  12 // This program is free software; you can redistribute it and/or modify  //
  13 // it under the terms of the GNU General Public License as published by  //
  14 // the Free Software Foundation; either version 2 of the License, or     //
  15 // (at your option) any later version.                                   //
  16 //                                                                       //
  17 // This program is distributed in the hope that it will be useful,       //
  18 // but WITHOUT ANY WARRANTY; without even the implied warranty of        //
  19 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         //
  20 // GNU General Public License for more details:                          //
  21 //                                                                       //
  22 //          http://www.gnu.org/copyleft/gpl.html                         //
  23 //                                                                       //
  24 ///////////////////////////////////////////////////////////////////////////
  25 require_once '../../../config.php';
  26 require_once $CFG->libdir.'/gradelib.php';
  27 require_once $CFG->dirroot.'/grade/lib.php';
  28 require_once '../grade_import_form.php';
  29 require_once '../lib.php';
  30
  31 $id            = required_param('id', PARAM_INT); // course id
  32 $separator     = optional_param('separator', '', PARAM_ALPHA);
  33 $verbosescales = optional_param('verbosescales', 1, PARAM_BOOL);
  34
  35 if (!$course = get_record('course', 'id', $id)) {
  36     print_error('nocourseid');
  37 }
  38
  39 require_login($course);
  40 $context = get_context_instance(CONTEXT_COURSE, $id);
  41 require_capability('moodle/grade:import', $context);
  42 require_capability('gradeimport/csv:view', $context);
  43
  44 // sort out delimiter
  45 if (isset($CFG->CSV_DELIMITER)) {
  46     $csv_delimiter = '\\' . $CFG->CSV_DELIMITER;
  47     $csv_delimiter2 = $CFG->CSV_DELIMITER;
  48
  49     if (isset($CFG->CSV_ENCODE)) {
  50         $csv_encode = '/\&\#' . $CFG->CSV_ENCODE . '/';
  51     }
  52 } else if ($separator == 'tab') {
  53     $csv_delimiter = "\t";
  54     $csv_delimiter2 = "";
  55     $csv_encode = "";
  56 } else {
  57     $csv_delimiter = "\,";
  58     $csv_delimiter2 = ",";
  59     $csv_encode = '/\&\#44/';
  60 }
  61
  62 $strgrades = get_string('grades', 'grades');
  63 $actionstr = get_string('csv', 'grades');
  64 $navigation = grade_build_nav(__FILE__, $actionstr, array('courseid' => $course->id));
  65
  66 print_header($course->shortname.': '.get_string('grades'), $course->fullname, $navigation);
  67 print_grade_plugin_selector($id, 'import', 'csv');
  68
  69 // set up import form
  70 $mform = new grade_import_form(null, array('includeseparator'=>!isset($CFG->CSV_DELIMITER), 'verbosescales'=>true));
  71
  72 // set up grade import mapping form
  73 $header = '';
  74 $gradeitems = array();
  75 if ($id) {
  76     if ($grade_items = grade_item::fetch_all(array('courseid'=>$id))) {
  77         foreach ($grade_items as $grade_item) {
  78             // skip course type and category type
  79             if ($grade_item->itemtype == 'course' || $grade_item->itemtype == 'category') {
  80                 continue;
  81             }
  82
  83             // this was idnumber
  84             $gradeitems[$grade_item->id] = $grade_item->get_name();
  85         }
  86     }
  87 }
  88
  89 if ($importcode = optional_param('importcode', '', PARAM_FILE)) {
  90     $filename = $CFG->dataroot.'/temp/gradeimport/cvs/'.$USER->id.'/'.$importcode;
  91     $fp = fopen($filename, "r");
  92     $header = split($csv_delimiter, fgets($fp,1024), PARAM_RAW);
  93 }
  94
  95 $mform2 = new grade_import_mapping_form(null, array('gradeitems'=>$gradeitems, 'header'=>$header));
  96
  97 // if import form is submitted
  98 if ($formdata = $mform->get_data()) {
  99
 100     // Large files are likely to take their time and memory. Let PHP know
 101     // that we'll take longer, and that the process should be recycled soon
 102     // to free up memory.
 103     @set_time_limit(0);
 104     @raise_memory_limit("192M");
 105     if (function_exists('apache_child_terminate')) {
 106         @apache_child_terminate();
 107     }
 108
 109     // use current (non-conflicting) time stamp
 110     $importcode = get_new_importcode();
 111     if (!$filename = make_upload_directory('temp/gradeimport/cvs/'.$USER->id, true)) {
 112         die;
 113     }
 114     $filename = $filename.'/'.$importcode;
 115
 116     $text = $mform->get_file_content('userfile');
 117     // trim utf-8 bom
 118     $textlib = textlib_get_instance();
 119     /// normalize line endings and do the encoding conversion
 120     $text = $textlib->convert($text, $formdata->encoding);
 121     $text = $textlib->trim_utf8_bom($text);
 122     // Fix mac/dos newlines
 123     $text = preg_replace('!\r\n?!',"\n",$text);
 124     $fp = fopen($filename, "w");
 125     fwrite($fp,$text);
 126     fclose($fp);
 127
 128     $fp = fopen($filename, "r");
 129
 130     // --- get header (field names) ---
 131     $header = split($csv_delimiter, fgets($fp,1024), PARAM_RAW);
 132
 133     // print some preview
 134     $numlines = 0; // 0 preview lines displayed
 135
 136     print_heading(get_string('importpreview', 'grades'));
 137     echo '<table>';
 138     echo '<tr>';
 139     foreach ($header as $h) {
 140         $h = clean_param($h, PARAM_RAW);
 141         echo '<th>'.$h.'</th>';
 142     }
 143     echo '</tr>';
 144     while (!feof ($fp) && $numlines <= $formdata->previewrows) {
 145         $lines = split($csv_delimiter, fgets($fp,1024));
 146         echo '<tr>';
 147         foreach ($lines as $line) {
 148             echo '<td>'.$line.'</td>';;
 149         }
 150         $numlines ++;
 151         echo '</tr>';
 152     }
 153     echo '</table>';
 154
 155     // display the mapping form with header info processed
 156     $mform2 = new grade_import_mapping_form(null, array('gradeitems'=>$gradeitems, 'header'=>$header));
 157     $mform2->set_data(array('importcode'=>$importcode, 'id'=>$id, 'verbosescales'=>$verbosescales, 'separator'=>$separator));
 158     $mform2->display();
 159
 160 //} else if (($formdata = data_submitted()) && !empty($formdata->map)) {
 161
 162 // else if grade import mapping form is submitted
 163 } else if ($formdata = $mform2->get_data()) {
 164
 165     $importcode = clean_param($formdata->importcode, PARAM_FILE);
 166     $filename = $CFG->dataroot.'/temp/gradeimport/cvs/'.$USER->id.'/'.$importcode;
 167
 168     if (!file_exists($filename)) {
 169         error('error processing upload file');
 170     }
 171
 172     if ($fp = fopen($filename, "r")) {
 173         // --- get header (field names) ---
 174         $header = split($csv_delimiter, clean_param(fgets($fp,1024), PARAM_RAW));
 175
 176         foreach ($header as $i => $h) {
 177             $h = trim($h); $header[$i] = $h; // remove whitespace
 178         }
 179     } else {
 180         error ('could not open file');
 181     }
 182
 183     $map = array();
 184     // loops mapping_0, mapping_1 .. mapping_n and construct $map array
 185     foreach ($header as $i => $head) {
 186         $map[$i] = $formdata->{'mapping_'.$i};
 187     }
 188
 189     // if mapping informatioin is supplied
 190     $map[clean_param($formdata->mapfrom, PARAM_RAW)] = clean_param($formdata->mapto, PARAM_RAW);
 191
 192     // check for mapto collisions
 193     $maperrors = array();
 194     foreach ($map as $i=>$j) {
 195         if ($j == 0) {
 196             // you can have multiple ignores
 197             continue;
 198         } else {
 199             if (!isset($maperrors[$j])) {
 200                 $maperrors[$j] = true;
 201             } else {
 202                 // collision
 203                 unlink($filename); // needs to be uploaded again, sorry
 204                 error('mapping collision detected, 2 fields maps to the same grade item '.$j);
 205             }
 206         }
 207     }
 208
 209     // Large files are likely to take their time and memory. Let PHP know
 210     // that we'll take longer, and that the process should be recycled soon
 211     // to free up memory.
 212     @set_time_limit(0);
 213     @raise_memory_limit("192M");
 214     if (function_exists('apache_child_terminate')) {
 215         @apache_child_terminate();
 216     }
 217
 218     // we only operate if file is readable
 219     if ($fp = fopen($filename, "r")) {
 220
 221         // read the first line makes sure this doesn't get read again
 222         $header = split($csv_delimiter, fgets($fp,1024));
 223
 224         $newgradeitems = array(); // temporary array to keep track of what new headers are processed
 225         $status = true;
 226
 227         while (!feof ($fp)) {
 228             // add something
 229             $line = split($csv_delimiter, fgets($fp,1024));
 230
 231             if(count($line) <= 1){
 232                 // there is no data on this line, move on
 233                 continue;
 234             }
 235
 236             // array to hold all grades to be inserted
 237             $newgrades = array();
 238             // array to hold all feedback
 239             $newfeedbacks = array();
 240             // each line is a student record
 241             foreach ($line as $key => $value) {
 242                 //decode encoded commas
 243                 $value = clean_param($value, PARAM_RAW);
 244                 $value = trim($value);
 245                 if ($csv_encode != $csv_delimiter2) {
 246                     $value = preg_replace($csv_encode, $csv_delimiter2, $value);
 247                 }
 248
 249                 /*
 250                  * the options are
 251                  * 1) userid, useridnumber, usermail, username - used to identify user row
 252                  * 2) new - new grade item
 253                  * 3) id - id of the old grade item to map onto
 254                  * 3) feedback_id - feedback for grade item id
 255                  */
 256
 257                 $t = explode("_", $map[$key]);
 258                 $t0 = $t[0];
 259                 if (isset($t[1])) {
 260                     $t1 = (int)$t[1];
 261                 } else {
 262                     $t1 = '';
 263                 }
 264
 265                 switch ($t0) {
 266                     case 'userid': //
 267                         if (!$user = get_record('user','id', addslashes($value))) {
 268                             // user not found, abort whold import
 269                             import_cleanup($importcode);
 270                             notify("user mapping error, could not find user with id \"$value\"");
 271                             $status = false;
 272                             break 3;
 273                         }
 274                         $studentid = $value;
 275                     break;
 276                     case 'useridnumber':
 277                         if (!$user = get_record('user', 'idnumber', addslashes($value))) {
 278                              // user not found, abort whold import
 279                             import_cleanup($importcode);
 280                             notify("user mapping error, could not find user with idnumber \"$value\"");
 281                             $status = false;
 282                             break 3;
 283                         }
 284                         $studentid = $user->id;
 285                     break;
 286                     case 'useremail':
 287                         if (!$user = get_record('user', 'email', addslashes($value))) {
 288                             import_cleanup($importcode);
 289                             notify("user mapping error, could not find user with email address \"$value\"");
 290                             $status = false;
 291                             break 3;
 292                         }
 293                         $studentid = $user->id;
 294                     break;
 295                     case 'username':
 296                         if (!$user = get_record('user', 'username', addslashes($value))) {
 297                             import_cleanup($importcode);
 298                             notify("user mapping error, could not find user with username \"$value\"");
 299                             $status = false;
 300                             break 3;
 301                         }
 302                         $studentid = $user->id;
 303                     break;
 304                     case 'new':
 305                         // first check if header is already in temp database
 306
 307                         if (empty($newgradeitems[$key])) {
 308
 309                             $newgradeitem = new object();
 310                             $newgradeitem->itemname = $header[$key];
 311                             $newgradeitem->importcode = $importcode;
 312                             $newgradeitem->importer   = $USER->id;
 313
 314                             // failed to insert into new grade item buffer
 315                             if (!$newgradeitems[$key] = insert_record('grade_import_newitem', addslashes_recursive($newgradeitem))) {
 316                                 $status = false;
 317                                 import_cleanup($importcode);
 318                                 notify(get_string('importfailed', 'grades'));
 319                                 break 3;
 320                             }
 321                             // add this to grade_import_newitem table
 322                             // add the new id to $newgradeitem[$key]
 323                         }
 324                         $newgrade = new object();
 325                         $newgrade->newgradeitem = $newgradeitems[$key];
 326                         $newgrade->finalgrade   = $value;
 327                         $newgrades[] = $newgrade;
 328
 329                         // if not, put it in
 330                         // else, insert grade into the table
 331                     break;
 332                     case 'feedback':
 333                         if ($t1) {
 334                             // case of an id, only maps id of a grade_item
 335                             // this was idnumber
 336                             if (!$gradeitem = new grade_item(array('id'=>$t1, 'courseid'=>$course->id))) {
 337                                 // supplied bad mapping, should not be possible since user
 338                                 // had to pick mapping
 339                                 $status = false;
 340                                 import_cleanup($importcode);
 341                                 notify(get_string('importfailed', 'grades'));
 342                                 break 3;
 343                             }
 344
 345                             // t1 is the id of the grade item
 346                             $feedback = new object();
 347                             $feedback->itemid   = $t1;
 348                             $feedback->feedback = $value;
 349                             $newfeedbacks[] = $feedback;
 350                         }
 351                     break;
 352                     default:
 353                         // existing grade items
 354                         if (!empty($map[$key])) {
 355                             // case of an id, only maps id of a grade_item
 356                             // this was idnumber
 357                             if (!$gradeitem = new grade_item(array('id'=>$map[$key], 'courseid'=>$course->id))) {
 358                                 // supplied bad mapping, should not be possible since user
 359                                 // had to pick mapping
 360                                 $status = false;
 361                                 import_cleanup($importcode);
 362                                 notify(get_string('importfailed', 'grades'));
 363                                 break 3;
 364                             }
 365
 366                             // check if grade item is locked if so, abort
 367                             if ($gradeitem->is_locked()) {
 368                                 $status = false;
 369                                 import_cleanup($importcode);
 370                                 notify(get_string('gradeitemlocked', 'grades'));
 371                                 break 3;
 372                             }
 373
 374                             $newgrade = new object();
 375                             $newgrade->itemid     = $gradeitem->id;
 376                             if ($gradeitem->gradetype == GRADE_TYPE_SCALE and $verbosescales) {
 377                                 if ($value === '' or $value == '-') {
 378                                     $value = null; // no grade
 379                                 } else {
 380                                     $scale = $gradeitem->load_scale();
 381                                     $scales = explode(',', $scale->scale);
 382                                     array_unshift($scales, '-'); // scales start at key 1
 383                                     $key = array_search($value, $scales);
 384                                     if ($key === false) {
 385                                         echo "<br/>t0 is $t0";
 386                                         echo "<br/>grade is $value";
 387                                         $status = false;
 388                                         import_cleanup($importcode);
 389                                         notify(get_string('badgrade', 'grades'));
 390                                         break 3;
 391                                     }
 392                                     $value = $key;
 393                                 }
 394                                 $newgrade->finalgrade = $value;
 395                             } else {
 396                                 if ($value === '' or $value == '-') {
 397                                     $value = null; // no grade
 398
 399                                 } else if (!is_numeric($value)) {
 400                                 // non numeric grade value supplied, possibly mapped wrong column
 401                                     echo "<br/>t0 is $t0";
 402                                     echo "<br/>grade is $value";
 403                                     $status = false;
 404                                     import_cleanup($importcode);
 405                                     notify(get_string('badgrade', 'grades'));
 406                                     break 3;
 407                                 }
 408                                 $newgrade->finalgrade = $value;
 409                             }
 410                             $newgrades[] = $newgrade;
 411                         } // otherwise, we ignore this column altogether
 412                           // because user has chosen to ignore them (e.g. institution, address etc)
 413                     break;
 414                 }
 415             }
 416
 417             // no user mapping supplied at all, or user mapping failed
 418             if (empty($studentid) || !is_numeric($studentid)) {
 419                 // user not found, abort whold import
 420                 $status = false;
 421                 import_cleanup($importcode);
 422                 notify('user mapping error, could not find user!');
 423                 break;
 424             }
 425
 426             // insert results of this students into buffer
 427             if ($status and !empty($newgrades)) {
 428
 429                 foreach ($newgrades as $newgrade) {
 430
 431                     // check if grade_grade is locked and if so, abort
 432                     if (!empty($newgrade->itemid) and $grade_grade = new grade_grade(array('itemid'=>$newgrade->itemid, 'userid'=>$studentid))) {
 433                         if ($grade_grade->is_locked()) {
 434                             // individual grade locked
 435                             $status = false;
 436                             import_cleanup($importcode);
 437                             notify(get_string('gradelocked', 'grades'));
 438                             break 2;
 439                         }
 440                     }
 441
 442                     $newgrade->importcode = $importcode;
 443                     $newgrade->userid     = $studentid;
 444                     $newgrade->importer   = $USER->id;
 445                     if (!insert_record('grade_import_values', addslashes_recursive($newgrade))) {
 446                         // could not insert into temporary table
 447                         $status = false;
 448                         import_cleanup($importcode);
 449                         notify(get_string('importfailed', 'grades'));
 450                         break 2;
 451                     }
 452                 }
 453             }
 454
 455             // updating/inserting all comments here
 456             if ($status and !empty($newfeedbacks)) {
 457                 foreach ($newfeedbacks as $newfeedback) {
 458                     $sql = "SELECT *
 459                               FROM {$CFG->prefix}grade_import_values
 460                              WHERE importcode=$importcode AND userid=$studentid AND itemid=$newfeedback->itemid AND importer={$USER->id}";
 461                     if ($feedback = get_record_sql($sql)) {
 462                         $newfeedback->id = $feedback->id;
 463                         update_record('grade_import_values', addslashes_recursive($newfeedback));
 464
 465                     } else {
 466                         // the grade item for this is not updated
 467                         $newfeedback->importcode = $importcode;
 468                         $newfeedback->userid     = $studentid;
 469                         $newfeedback->importer   = $USER->id;
 470                         insert_record('grade_import_values', addslashes_recursive($newfeedback));
 471                     }
 472                 }
 473             }
 474         }
 475
 476         /// at this stage if things are all ok, we commit the changes from temp table
 477         if ($status) {
 478             grade_import_commit($course->id, $importcode);
 479         }
 480         // temporary file can go now
 481         unlink($filename);
 482     } else {
 483         error ('import file '.$filename.' not readable');
 484     }
 485
 486 } else {
 487     // display the standard upload file form
 488     $mform->display();
 489 }
 490
 491 print_footer();
 492 ?>