Type of post:

tutorial

Target Audience:

programmer

Requisites:

php, sql, yii framework

Keywords:

CSV Export, PHP, Yii Framework, Tutorial

Download v.1.0

---

This module, developed for the Yii framework, allows you to download in CSV format every model and its relations (also MANY_MANY).

We have looked for a module that could export any DB table in a customised format and there are nice extensions that allows that but we haven’t found any module flexible enough to support model relations export and filters.

The module works like this:

1. An admin (no programming-skills required) creates a report template
2. The user clicks on the export button in a search form and select the report
3. User downloads the report. The filter of the search form will be applied.

This module includes also a logger that shows:

• The user who downloaded the report
• The IP address of the user connection
• The filter applied to the report
• The name of the report
• The model

Requirements

Tested with Yii 1.1.13 (but I see no reason why it would not work for previous Yii versions)

Usage

Extract into protected/modules directory

Edit your config.php file:

'modules'=>array(
 ...
 'd_export'=>array(
    'class'=>'application.modules.d_export.DExportModule',
    'accessPermissionUsers'=>array('@'),
    'userModel'=>'User',
    'install'=>true,
    'adminUserId'=>1,
    'layout'=>'//layouts/column2'
  )
)

Configuration

Point your application to the url /d_export . Once you’ve done that, edit config.php file again and set 'install'=>false

Configuration parameters:

accessPermissionUsers (array)

the Users that are allowed to access the module, defaults to ‘@’ (only logged in)

accessPermissionRoles (array)

roles that are allowed to access the module, defaults empty

userModel (string)

the name of your User model (defaults to “User”)

install (bool)

whether or not to install the module (aka: to create the tables in the DB)

adminUserId (int)

the Id of the SuperUser

exportReportBehaviors (arrray)

any custom behavior to attach to the ExportReport model

exportWidgetViewPath (string)

the path as in Yii::getPathOfAlias() of the theme of the form generated byt the ExportWidget. Defaults to d_export.views.exportWidget

layout (string)

the layout used in the controllers. Defaults to “//layouts/column1″

Scaffolding

Add an ID to your search form

$form=$this->beginWidget('CActiveForm', array(
	'action'=>Yii::app()->createUrl($this->route),
	'method'=>'get',
    'id'=>'YOURID'
));

Put the following widget at the top of any _search.php file (or any search form):

$this->widget('application.modules.d_export.components.exportWidget.ExportWidget',
            array(
                'searchFormId'=>'YOURID',
                'model'=>$model
            )
        );

as you have noticed, searchFormId must be the same id as the one in your search form.

Widget Parameters

searchFormId (string)

the ID of the search Form

model (object)

the model

Full Example of your _search.php file

<?php
/* @var $this UserController */
/* @var $model User */
/* @var $form CActiveForm */
?>
<div class="wide form">
<?php
$this->widget('application.modules.d_export.components.exportWidget.ExportWidget',
            array(
                'searchFormId'=>'user-search',
                'model'=>$model
            )
        );
        ?>
<?php 
$form=$this->beginWidget('CActiveForm', array(
	'action'=>Yii::app()->createUrl($this->route),
	'method'=>'get',
    'id'=>'user-search'
)); ?>
	<div class="row">
           <?php echo $form->label($model,'id'); ?>
           <?php echo $form->textField($model,'id',array('size'=>20,'maxlength'=>20)); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'username'); ?>
	   <?php echo $form->textField($model,'username',array('size'=>60,'maxlength'=>128)); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'email'); ?>
	   <?php echo $form->textField($model,'email',array('size'=>60,'maxlength'=>128)); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'activkey'); ?>
	   <?php echo $form->textField($model,'activkey',array('size'=>60,'maxlength'=>128)); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'status_id'); ?>
	   <?php echo $form->textField($model,'status_id'); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'lastvisit'); ?>
	   <?php echo $form->textField($model,'lastvisit'); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'create_time'); ?>
	   <?php echo $form->textField($model,'create_time'); ?>
	</div>
	<div class="row">
	   <?php echo $form->label($model,'update_time'); ?>
	   <?php echo $form->textField($model,'update_time'); ?>
	</div>
	<div class="row buttons">
	   <?php echo CHtml::submitButton('Search'); ?>
	</div>
<?php $this->endWidget(); ?>
</div>

Create a new Report

Go in d_export/exportReport/create and create a new report. You can choose a name, the model (this tool will loop through all your models, modules included) and whether or not to include the headers in the CSV file.

In “Header” you set the header name ex: “user_id”; in “Value” you put the value in PHP format as a value of a column of CGridView, ex: $data->id.

Example: User report with profile and address

Let’s say you have a model User with 2 relations: UserProfile (‘profile’ in the $model->relations()) and Address (‘address’ in the $model->relations()). The report will look like:

Name

Users with profile and address

Model

User

Include Headers

Yes

Header / Value

• id / $data->id
• email / $data->email
• firstname / $data->profile->firstname
• lastname / $data->profile->lastname
• address / $data->address->address
• city / $data->address->city
• postcode / $data->address->post_code

Voilà.

The user will now find a report named “Users with profile and address” in the search form of the model “User”. When the export button is clicked, the module takes retrieves the search string of the form as well as the model name. It then run a search in the background and convert the data into a CSV file.

Where to go from here and possible developments

You can further edit your export data in building custom “get” methods in your models. For example, let’s say you would like to convert a timestamp into a custom datetime format. Assuming you have an attribute named “timestamp” you could:

public function getTimeDate()
    {
        return date("d/m/Y", strtotime($this->timestamp));
    }

and you can invoke the property as $data->date in your export report.

Also, it would be interesting to build an AJAX tool in the ExportReport model that loops into the selected model and finds not only the attributes coming from the DB, but the ones of the related models too and the get methods. Thus it can be avoided the $data-> value and the process could be entirely developer-free.