Creating a CSV Upload Page

If you need to allow users to upload data into your site and import that directly into various forms you can extend I2CE_PageFormCSV page class. The CSV file can optionally include headers so that the order of the columns doesn't have to be set. This example will entail adding some data as a child form for a person in iHRIS Manage, however this many not be required for your use case so you won't need to include the pieces for this parent form. For this example we'll configure a page that will allow you to upload education details for a particular person. A tutorial has been added to iHRIS Common to see the full code for this tutorial.

Configuring Your Module

First you need to configure a new page in your module. You can just use a standard module header for this and require the Forms module (although this is probably already enabled by default) as well as PersonEducation since that's the form we'll be importing.

<source lang="xml"> <?xml version="1.0"?> <!DOCTYPE I2CEConfiguration SYSTEM "I2CE_Configuration.dtd"> <I2CEConfiguration name="FormCSVUpload">

 <metadata>
   <displayName>FormCSVUpload</displayName>
   <category>Tutorial</category>
   <description>Tutorial for uploading CSV data into iHRIS.  This example will load education details to a person.</description>
   <creator>IntraHealth Informatics</creator>
   <email>hris@capacityplus.org</email>
   <link>https://launchpad.net/ihris-manage</link>
   <version>4.2.0</version>
   <path name="classes">
     <value>./lib</value>
   </path>
   <path name="templates">
     <value>./templates</value>
   </path>
   <requirement name="forms">
     <atLeast version="4.2" />
   </requirement>
   <requirement name="PersonEducation">
     <atLeast version="4.2" />
   </requirement>
   <priority>200</priority>
 </metadata>
 <configurationGroup name="FormCSVUpload" path="/I2CE">
   <displayName>FormCSVUpload</displayName>
 </configurationGroup>

</I2CEConfiguration> </source>

Now we can add in the configuration for the new page we're creating. This should go in the main configurationGroup of your module.

<source lang="xml">

   <configurationGroup name="page">
     <configurationGroup name="upload_education">
       <displayName>Upload page</displayName>
       <description>The page 'upload_education' which is used to add education details to a person from a CSV file upload.</description>
       <configuration name="class" values="single">
         <value>iHRIS_PageFormUpload_Education</value>
       </configuration>
       <configuration name="style" values="single">
         <value>shell</value>
       </configuration>
       <configurationGroup name="args">
         <configuration name="title" values="single" locale="en_US">
           <value>Upload Education</value>
         </configuration>
         <configuration name="defaultHTMLFile" values="single">
           <value>upload_education.html</value>
         </configuration>
         <configuration name="tasks" values="many">
           <value>person_can_edit_child_form_education</value>
         </configuration>
       </configurationGroup>
     </configurationGroup> 
   </configurationGroup> 

</source>

Creating Your Template

Now we need to create the template file mentioned in the module: upload_education.html. This will include the file upload as well as a checkbox to mark if the file has headers or not. This example will required that the headers are set. It will also include some additional display of the person form and links back to the default view page.

<source lang="html5">

Upload Education for: ,

 <form method="POST" enctype="multipart/form-data">
   

Upload Education Details
<label for="education">Select CSV file to upload</label> * <input type="file" id="education" name="education" /> Has Headers? <input type="checkbox" id="has_header:education" name="has_header:education" value="1" checked="checked" /> <label for="has_header:education">This file has headers.</label>

 </form>

</source>

Extending the CSV Upload Class

Next you need to create a new page class as named in the module: iHRIS_PageFormUpload_Education.php.

You should include comments as necessary, but those won't be included in this tutorial to save space. You can view the full code to see all comments.

First, we need to create the page class and set the default form which will be the person form that the uploads will be children for. This will load the person object and set it on the page. The syntax for the link to this page will be: upload_education?id=person|XXXX. <source lang="php"> class iHRIS_PageFormUpload_Education extends I2CE_PageFormCSV {

   protected $person;

   protected function loadObjects() {
       parent::loadObjects();
       if ( !$this->get_exists('id') ) { 
           $this->userMessage("Invalid person id provided.");
           return false;
       }
       $this->person = $this->factory->createContainer( $this->get('id') );
       if ( !$this->person instanceof iHRIS_Person ) { 
           $this->userMessage("Invalid id passed to education upload page.");
           return false;
       }
       $this->person->populate();
       return true;
   }   

   protected function setForm() {
       $this->template->setForm( $this->person );
   }   

</source>

Now we need to validate the file. For this tutorial we will only validate to make sure required headers are there. You could do individual validation per row if you need to. If any validation fails then the file won't be loaded. This makes sure that the following headers/columns are in the CSV file: Education Type, Degree, Major, Year and Institution.

<source lang="php">

   protected function validate() {
       if ( !$this->checked_validation ) {
           if ( !$this->processHeaderRow( 'education' ) ) {
               $this->userMessage("Unable to read headers from CSV file.");
               $this->invalid = true;
               return false;
           }
           $required_headers = array( 'Education Type', 'Degree', 'Major', 'Year', 'Institution' );
           $invalid_headers = array();
           foreach( $required_headers as $header ) {
               if ( !in_array( $header, $this->current['education']['header'] ) ) {
                   $invalid_headers[] = $header;
               }
           }
           if ( count( $invalid_headers ) > 0 ) {
               $this->userMessage( "There are missing headers in the CSV file:  " . implode( ', ', $invalid_headers ) );
               $this->invalid = true;
               return false;
           }
           $this->checked_validation = true;
       }
       return true;
   }

   protected function validateRow( $key ) {
       // Don't perform any row level validation for now.
       return true;
   }

</source>

To load the data, we'll need to do some lookups on mapped values. Degree is a mapped field that includes the Education Type field which is also mapped. The base class includes a method to lookup list values with the lookupList method. It will automatically cache any results so only one lookup is done per value. We need to create one to lookup a degree with the given education type so we can add the following method to the page.

<source lang="php">

   protected function lookupDegree( $degree, $edu_type ) {
       $degree = strtolower( trim( $degree ) );
       if ( !array_key_exists( "degree", $this->cache ) ) {
           $this->cache["degree"] = array();
       }
       if ( !array_key_exists( $edu_type, $this->cache["degree"] ) ) {
           $this->cache["degree"][$edu_type] = array();
       }
       if ( !array_key_exists( $degree, $this->cache["degree"][$edu_type] ) ) {
           $where = array(
                   'operator' => 'AND',
                   'operand' => array(
                       array(
                           'operator' => 'FIELD_LIMIT',
                           'field' => 'edu_type',
                           'style' => 'equals',
                           'data' => array( 'value' => $edu_type ),
                           ),
                       array(
                           'operator' => 'FIELD_LIMIT',
                           'style' => 'lowerequals',
                           'field' => 'name',
                           'data' => array( 'value' => $degree )
                           ),
                       )
                   );
           $id = I2CE_FormStorage::search( "degree", false, $where, array(), true );
           if ( $id ) {
               $this->cache["degree"][$edu_type][$degree] = "degree|$id";
           } else {
               $this->cache["degree"][$edu_type][$degree] = "";
           }
       }
       return $this->cache["degree"][$edu_type][$degree];
   }

</source>

Next we can set up the save methods. One to give a message if the save succeeded or failed with the redirect back to the view page and one to save for each row in the CSV file. Note here that you can access the data in the row by using: $this->current[$key]['row'][HEADER]. You'll also notice that Location is also checked, but isn't required. If you don't have headers for the CSV, then you can access values by number (0, 1, 2, etc.) but the user uploading must be sure to have the correct order so having headers is the preferred way and is simpler to validate. The Education Type and Degree values must match existing values already in iHRIS or the row won't be loaded. For simplicity any invalid rows aren't loaded. You can modify this to search for duplicates to ignore them or perform other validations.

<source lang="php">

   protected function save() {
       if ( parent::save() ) {
           $this->userMessage("The CSV file has been uploaded.");
       } else {
           $this->userMessage("An error occurred trying to upload your file.");
       }
       $this->setRedirect( "view?id=" . $this->person->getNameId() );
       return true;
   }

   protected function saveRow( $key ) {
       // First lookup the education type that was given.
       $edu_type = $this->lookupList( "edu_type", $this->current[$key]['row']['Education Type'] );
       // Then lookup the degree with the education type.
       $degree = $this->lookupDegree( $this->current[$key]['row']['Degree'], $edu_type );
       if ( !$degree ) {
           $this->userMessage( "Unable to load row for: " . $this->current[$key]['row']['Degree'] . " at "
                   . $this->current[$key]['row']['Institution'] );
           // Don't try to load this one, but continue.
           return true;
       }
       $education = $this->factory->createContainer( "education" );
       $education->institution = $this->current[$key]['row']['Institution'];
       if ( array_key_exists( 'Location', $this->current[$key]['row'] ) ) {
           $education->location = $this->current[$key]['row']['Location'];
       }
       $education->getField('year')->setFromDB( $this->current[$key]['row']['Year']."-00-00 00:00:00" );
       $education->getField('degree')->setFromDB( $degree );
       $education->major = $this->current[$key]['row']['Major'];

       $education->setParent( $this->person->getNameId() );
       $education->validate();
       if ( $education->hasInvalid() ) {
           $this->userMessage( "Unable to validate row for: " . $this->current[$key]['row']['Degree'] . " at " 
                   . $this->current[$key]['row']['Institution'] );
       } else {
           $education->save( $this->user );
       }

       $education->cleanup();
       unset( $education );
       return true;
   }

</source>

Testing

Now you can add the link to your view template with something like:

<source lang="html5"> Upload Education from CSV </source>

You can load sample data similar to this as your CSV file in the demo site of iHRIS Manage.

Education Type	Degree	Institution	Location	Year	Major
Degree/Certification	BA/BS	University	Taifafeki	1995	PreMed
Degree/Certification	MPH	College of Public Health	Taifafeki	1997	Policy and Planning

Additional Options

As mentioned before, the data uploaded doesn't have to be related to a particular form so you don't need to include the person form set up. You could have a CSV file load multiple person records similar to and import script, although you will need to be careful about file upload sizes on your system as well as memory limits when loading large amounts of data. For an additional example you can view what was done for iHRIS Train for loading participants. This searches for person records to match and then loads based on that if found or adds a new record if not found.

You can also use the same code for command line processing of CSV files so you can reuse your code. You'll need to add an additional method to your class to enable this and update the module to configure the command line page.

<source lang="xml">

   <configurationGroup name="command_line">
     <version>4.2.0.1</version>
     <configurationGroup name="upload_education">
       <displayName>Upload command line page</displayName>
       <description>The command line page 'upload_education' which is used to add education details to a person from a CSV file.</description>
       <configuration name="class" values="single">
         <value>iHRIS_PageFormUpload_Education</value>
       </configuration>
     </configurationGroup> 
   </configurationGroup> 

</source>

We'll need to modify the loadObjects method to only run when accessed from the web:

<source lang="php">

   protected function loadObjects() {
       parent::loadObjects();
       if ( array_key_exists( 'HTTP_HOST', $_SERVER ) ) {
           if ( !$this->get_exists('id') ) {
               $this->userMessage("Invalid person id provided.");
               return false;
           }
           $this->person = $this->factory->createContainer( $this->get('id') );
           if ( !$this->person instanceof iHRIS_Person ) {
               $this->userMessage("Invalid id passed to education upload page.");
               return false;
           }
           $this->person->populate();
       }
       return true;
   }

</source>

Then we need to add a command line action:

<source lang="php">

   protected function actionCommandLine( $args, $request_remainder ) {
       $cli = new I2CE_CLI();
       $cli->addUsage("--csv=FILENAME: The CSV file to upload.\n");
       $cli->addUsage("--id=person|XXXX: The person ID of the parent form.\n");
       $cli->processArgs();
       if ( !$cli->hasValue('csv') || !$cli->hasValue('id') ) {
           $cli->usage();
           return;
       }
       $this->person = $this->factory->createContainer( $cli->getValue('id' ) );
       if ( !$this->person instanceof iHRIS_Person ) {
           echo "Invalid id passed to education upload page.\n";
           return false;
       }
       $this->person->populate();

       $file = $cli->getValue('csv');
       if ( !file_exists( $file ) ||
               ($upload = fopen( $file, "r" )) === false ) {
           echo "Could not read: " . $file . "\n";
           return;
       }
       $this->files['education'] = array();
       $this->files['education']['file'] = $upload;
       $this->files['education']['header'] = true;

       if ( $this->validate() ) {
           if ( $this->save() ) {
               echo "The CSV file was imported.\n";
           } else {
               echo "Unable to load file.\n";
           }
       } else {
           echo "There was invalid data in the file.\n";
       }
   }

</source>