BULK Update in YII using CGRIDVIEW

A Description of the action of UpdateALL button
<b>
<font color="red"> Bulk Update: </font>
Select same file number in the office list by clicking the check boxes beside the file numbers
and then click new button "Update ALL", then provide all the required fields and click save. This saves all the files.
</b>

<div class="row buttons">
<center>
      <table border='0'>
      <tr>
      <td> <img src="<?php echo $imgpath ?>" />
      </td>
      <td><?php echo CHtml::button('Update ALL',array('name'=>'btnupdateall','class'=>'updateall-button')); ?>
      </td>
      </tr>
      </table>
        </center>
</div>

Delete either first set of rows or last set rows in MYSQL in dynamic fashion

DELETE FROM table <condition> ORDER BY the field DESC|ASC limit 100

for first 100,

DELETE FROM table <condition if required> ORDER BY <field> ASC limit 100

and for last 100,

DELETE FROM table <condition if required> ORDER BY <field> DESC limit 100

To copy the same cell value ( do not increment cell number) in excel without increment in excel formula while dragging

For example if we have a excel sheet with tables in which we have data,
There are two sheets namely, PRESENT and DATESHEET.

In which the datesheet A1 cell consisting todays date in one format and C1 cell consisting report exported time and E1 consisting dateTime according to Database format. These three are required in our database to generate a report.

There is a requirement the report to be generated datewise format, But as we export the data from a website or database the date field is not present , so we need to export the data in a excel/csv format and we need to right a script in which the date field should be added.

suppose our data is exported to sheet namelt PRESENT ( here I have taken attendance report example)

As said earlier we have datesheet and present sheet. Now we need open a new tab/sheet in our excel file with name SCRIPT

In this script sheet we need to write the formula as stated below

=CONCATENATE("insert into daypresent(slno,office_name,today_date,report_time,db_datetime,tot_registered_emp,
tot_present) values('",$present.A4,"','",$present.B4,"','",$datesheet.$A$1,"','",
$datesheet.$C$1,"','",$datesheet.$E$1,"','",$present.C4,"','",$present.E4,"');")

Kindly observe in the above formula we are using Sheetname.Cell_reference prefixed with $ symbol. The symbol refer to that particular sheet and the specified cell. So here we are concatenating the two sheets cells in our script sheet.

Fine, after entering the formula in the first row in script sheet we can get the all records by simply dragging the cell to the rows (this is depends on our database record count). To the count we need to drag the formula.

Here is the case, The date is common for all the rows when we drag the cell formula the excel feature increment the cell value automatically we makes the next insert statement for row two . So in the next cell the formula becomes

=CONCATENATE("insert into daypresent(slno,office,tdate,rdate,rtime,regemp,present) values('",$present.A5,"','",$present.B5,"','",$datesheet.$A$1,"','",$datesheet.$C$1,"','",
$datesheet.$E$1,"','",$present.C5,"','",$present.E5,"');")

Present.A4 to Present.A5 , Present.B4 to Present.B5 .....       this is what we required but
 what about the datesheet we need the same cell value for all the rows

to achieve this, we need to give $datesheet.$A$1

the  cell values remains same even we drag the contents if we specify $A$1. That is the cell count wont increment

CGridView Multiple rows update , Bulk Update of records in YII

By keeping a simple check box before each record in CGridView we can achieve bulk update of records. User can check the selected records and click on UpdateALL button , this is very useful when multiple records having same data to update. Here in this case we are having File Monitoting System . The Inward section receives more than 200 files daily and in which some files belongs to same section with same PR Number so instead of updating each file and giving the file number and forwarding to section, User can simply select the files and update the selected files at once.

1. To achieve above, first we design the view part. So in the admin view of the main table (here say it      is files) then the path would be >application_folder/protected/views/files/admin.php , Open the
     file in which we have a CGridView defined already

     First note the user about this simple action, the below statements is for that purpose only

   <b><font color="red"> Bulk Update: </font> Select same file number inward list by clicking the    check boxes beside inward numbers and then click new button "Update ALL" give all the     
   required fields and click save </b> 

   Then we need a Button with label "Update ALL" in our view,
   
   <?php
            $baseUrl = Yii::app()->baseUrl;
            $imgpath = $baseUrl.'/images/new.gif';
    ?>
   <div class="row buttons">
    <center>
        <table border='0'><tr>
             <td> <img src="<?php echo $imgpath ?>" /></td>
             <td><?php echo CHtml::button('Update 
                                           ALL',array('name'=>'btnupdateall','class'=>'updateall-button')); ?>
            </td>
         </tr></table>
    </center>
    </div>

   Now we need to register a client script (JQuery script) for the button, Please add the following underlined lines to the script.
 <?php
       Yii::app()->clientScript->registerScript('search', "
        $('.search-button').click(function(){
$('.search-form').toggle();
return false;
      });
      $('.search-form form').submit(function(){
p$('#inward-grid').yiiGridView('update', {
data: $(this).serialize()
});
return false;
      });
      $('.updateall-button').click(function(){
       var atLeastOneIsChecked = $('input[name=\"inward-grid_c0[]\"]:checked').length > 0;
        if (!atLeastOneIsChecked)
        {
                alert('Please select atleast one inward number to Update');
        }
        else if (window.confirm('Are you sure you want to Update ALL the selected inward files?'))
        {
                document.getElementById('inward-search-form').action='index.php?r=inward/updateall';
                document.getElementById('inward-search-form').submit();
        }
      });
      ");

  ?>

and Now in columns of the CGridView  add a check box before the file number field

<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'inward-grid',
'selectableRows'=>2,   // Two means many 0 means none
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
        array(
                    'value'=>'$data->inw_no',
                    'class'=>'CCheckBoxColumn',
                    ),
'inw_no',

Now the check box is added to the gridview.

2. The Action Part:  We have defined the action for the UpdateALL button in our view now we have        to write the action in our controller i.e., inward controller. (note the above highlighted text
      action=index.php?r=inward/updateall)

     ////////////////////////////////// UPDATE ALL ////////////////////////////////////////////////////

     public function actionUpdateall()
    {
   $session=new CHttpSession;
        if (isset($_POST['inward-grid_c0']))
        {
               $session->open();
               $session['sel'] = $_POST['inward-grid_c0'];
        }
       
       // if(isset($_POST['inward-grid_c0']))
        if(isset($session['sel']))
        {
                $upd_inws = $session['sel'];
// $upd_inws = $_POST['inward-grid_c0'];
                $uname=Yii::app()->user->name;
                $baseUrl = Yii::app()->baseUrl;
                $cs = Yii::app()->getClientScript();

                $cs->registerScriptFile($baseUrl.'/js/utility.js');
                $cs->registerScriptFile($baseUrl.'/js/validations.js'); 
                
                /* $model_inw=new Inward;
                $model_inwTrans = new InwTrans; */
                $model = new Inward;
            $cnt=count($_POST);
            $isinw = isset($_POST['Inward']);
                //if(isset($_POST['Inward']))
                
                //if(isset($_POST['Inward']))
                if(isset($_POST['frender']))
                {
                                             
                        $model->attributes=$_POST['Inward'];
                        $valid = true;

                        if(($uname<>'record.collectorate') and 
                                ($model->sec_fno!='' or $model->sec_fno!=null) or 
                                ($model->sec_year!='' or $model->sec_year!=null)){
                                //$model->scenario='PR';
                           if(($model->sec_fno!='')and($model->sec_year!='')){
                            $valid=true;
                           }else{
                            $valid=false;
                           }
                        }
                      
                        if(($uname==='record.collectorate') and $model->clo_yn==='Y'){
                           // $model->scenario='finalization';
                        if(($model->sec_fno!='')and($model->sec_year!='')and($model->nfp!='')
                        and($model->cfp!='')and($model->tot!='')and($model->clo_date!='')and
                        ($model->clo_cat!='')){
                        $valid=true;
                        }else{
                        $valid=false;
                        }
                        }
                            

                           // Dialog::message('Model',"model attributed $model->sec_fno");

                        if($valid){
// Dialog::message('title',"username is $uname");
$curYear= date('Y');
$model->sec_year = $curYear - $model->sec_year;
                               foreach ($upd_inws as $inwno){
                                $umodel = Inward::model()->findByPk($inwno);
                              //  Dialog::message('INFO',"Got Inward number $umodel->inw_no");
                                 if($umodel->updateallFields($model)){
                                        $umodel->save();
                                  }
                               } 
                               $session->close();

// destroys all data registered to a session.
                       //  $session->destroy();
                      //$this->actionAdmin();
                      Dialog::message('INFO', "Records Saved Successfully Click Home button before proceeding");
                               $searchModel=new Inward('search');
                               $searchModel->unsetAttributes();  // clear any default values
  $searchModel->sec_fno=$model->sec_fno;
  $searchModel->sec_year=$model->sec_year;
                      $this->render('admin',array(
    'model'=>$searchModel,
      ));
                        }else{
                       
                          Dialog::message('ERROR', "unable to save Please Enter all the fields");
                       /*  $process = Yii::app()->createController('FirstController'); //create instance of controller
                         $process->test1();  */
                        // InwardController::errorpage();
                           $this->render('errorpage');
                        }

                     
                }else{
               
                 $this->render('updateall',array(
                        'model'=>$model,
                 ));
                }
        }
        else
        {
                //Yii::app()->user->setFlash('error', 'Please select at least one record to Update.');
          Dialog::message('ERROR', "Please select at least one record to Update.");
          $model = new Inward;
               $this->render('admin',array(
   'model'=>$model,
      ));
        }               
    }
////////////////////////////////////////////////////////////////////////////////////////////////////

3. Finally the UpdateAll view has to be created with the fields which are common for the files. ie., in our views/app_folder/updateall.php

//////////////////////////////// updateall.php  ////////////////////////////////////////////////////

<div class="form">

<?php $form=$this->beginWidget('CActiveForm', array(
        'id'=>'inward-form',
        'enableAjaxValidation'=>false,
        'htmlOptions' => array('enctype' => 'multipart/form-data'),
)); ?>

<p class="note">Fields with <span class="required">*</span> are required.</p>
<?php echo $form->errorSummary($model); ?>
<center>
<?php 
     $uname=Yii::app()->user->name;
?>
<table id="withborder" border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<tr><td>
<table border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<div class="row"> <tr><td>
                <?php echo $form->labelEx($model,'PR Number'); ?>
</td><td>
<?php echo $form->textField($model,'sec_fno',array('size'=>8,'maxlength'=>11)); ?>
<?php echo $form->error($model,'Sec. Asst.'); ?>
<?php echo "PR year"; ?>
   <?php  
$curYear= date('Y');
$prevYear = $curYear-5;
   echo $form->dropDownList ($model, 'sec_year', range($curYear,$prevYear), array('prompt'=>'Select Year'));  ?>
  <?php echo $form->error($model,'sub_scode'); ?>
</td>
</tr>
</div>
<?php
    if($uname<>'record.collectorate'){
?>
 <div class="row">
   <tr><td>
                <?php echo $form->labelEx($model,'Forward to --->'); ?>
      </td><td>
 <?php echo $form->dropDownList($model, 'sec_code' , CHtml::listData(Sections::model()->findAll(), 'sec_code', 'sec_name'),
                        array(
                                'prompt'=>'Select Section',
                                'ajax'=> array(
                                'type' => 'POST',
                                'url'=>CController::createUrl('Inward/Sec'),
                                'update'=>'#'.CHtml::activeId($model,'sub_scode'),
                                )
                        )
                    );
        ?>
                <?php echo $form->error($model,'Sec. Asst.'); ?>
          <?php echo "Sec.Asst."; ?>

 <?php echo $form->DropDownList($model,'sub_scode',CHtml::listData(SubSections::model()->findAllByAttributes(array('sec_code'=>$model->sec_code)),'sub_scode','sub_section'),array('prompt'=>'Select Subsection')); ?>
                <?php echo $form->error($model,'sub_scode'); ?>
         </td>
        </tr>
    </div>

 <div class="row">
                <tr>
                <td>
                <?php echo $form->labelEx($model,'File Status'); ?>
                </td>
                <td>
                <?php echo $form->textField($model,'filestatus',array('size'=>50,'maxlength'=>300)); ?>
                            <?php echo $form->error($model,'filestatus'); ?>
                </td>
                </tr>
        </div>


        <div class="row">
                <tr>
                <td>
                <?php echo $form->labelEx($model,'Remarks'); ?>
                </td>
                <td>
                <?php echo $form->textField($model,'sec_rem',array('size'=>50,'maxlength'=>360)); ?>
                            <?php echo $form->error($model,'sec_rem'); ?>
                </td>
                </tr>
        </div>
<?php } ?>
<?php   $tuname=Yii::app()->user->name; ?>

<?php  if($tuname=='record.collectorate') { ?>

<tr>
        <div class="row">
        <td>
<?php echo $form->labelEx($model,'close the file?'); ?> 
</td> <td>
<?php echo $form->dropDownList($model,'clo_yn',
array('N' =>'No', 'Y'=> 'Yes'),
array(
'ajax'=>array(
          'type'=>'POST',
          'url'=>CController::createUrl('Inward/Dis'),
          'update'=>'#description_id',
)));
?>
        </td>
</div>
</tr>

</table>
<table border=0 cellspacing=0 cellpadding=0 bgcolor="lightblue">
<?php $tc=$model->clo_yn; ?>
<tr>
<td>
<div id="description_id">
<?php if($tc=='Y'){ ?>

<?php
echo "<table>";
echo "<tr><td>";
echo "<b>Close type</b>";
echo "</td>";
echo "<td>";
echo $form->dropDownList($model,'clo_cat',array('D'=>'D','L'=>'L','N'=>'N','R'=>'R'),array('prompt'=>'Select Dis'));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Closing Date <br/> (Year-Mon-Date)</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'clo_date',array('size'=>30,'maxlength'=>30));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>NFP</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'nfp',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>CFP</b>";
echo "</td>";
echo "<td>";
echo $form->textField($model,'cfp',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Total</b>";
echo "</td>";
echo "<td>";
echo $form->textField($model,'tot',array('size'=>10,'maxlength'=>10));
echo "</td>";
echo "</tr>";
echo "<tr><td>";
echo "<b>Remarks</b>";
echo "</td>";
echo "<td>";
 echo $form->textField($model,'clo_rem',array('size'=>45,'maxlength'=>360));
echo $form->error($model,'clo_rem'); 
echo "</td>";
echo "</tr>";
echo "</table>";
?>

<?php } ?>
</div>
</td>
</tr>
<?php  }
echo CHtml::hiddenField('frender' , '1', array('id' => 'frender'));

 ?>
<?php /// END OF CLOSING IF for RECORD SECTION ?>

        <div class="row buttons">
         <tr>
           <td><td>
           <td align="left">
                <?php echo CHtml::submitButton($model->isNewRecord ? 'Save' : 'Save'); ?>
                <?php // echo CHtml::button('Save',array('submit' => array('inward/updateall'))); ?>
           </td>
        </tr>
        </div>
</table>
</td>
</tr>
</table>

<?php $this->endWidget(); ?>

</div><!-- form -->

</center>

Thats it.. I have done for my example with defined fields, change the fields according to your requirement.

GOOD LUCK!!!

Model rules for date validation and triming the values before validation

Today I faced problem with validating date fields .

I am accepting date using CJuiDateTimepicker it is working fine, but when I gave regular expression to validate a date field. It is giving error even for the valid date.

The main cause for the above situation is the DatePicker picking up date and a space appended to the date at the end of date. However we have 'date' datatype in mysql for these fields so the dates are automatically trimmed and saved in DB perfectly.

But to validate the dates we have define the rules as below

array('heardt,fdate,secdt,judgedt,nheardt,mandsurdt,leactdt,serorddt,dispdt', 'filter', 'filter'=>'trim'),    // This will trim the values
            array('heardt,fdate,secdt,judgedt,nheardt,mandsurdt,leactdt,serorddt,dispdt','match' ,'pattern'=> '/^([0-9]{4})-([0-1]{0,1}[0-9]{1})-([0-3]{0,1}[0-9]{1})$/','allowEmpty'=>true,'message'=> 'Date Format is YYYY-MM-DD'),
/* The above one validates the format of date. This format accepts 0000-00-00 as date */

array('dispdt','match','pattern'=> '/^([0-9]{4})-([0-1]{0,1}[0-9]{1})-([0-3]{0,1}[0-9]{1})$/','allowEmpty'=>false, 'message'=>'Disposed date mandatory when case is disposed','on'=>'disposed'),

/* The above one works when $model->scenario is assaigned to disposed. */

By this we can validate the dates using model rules