Yii Framework Forum: Save Multiple Records In Transaction - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Save Multiple Records In Transaction Rate Topic: -----

#1 User is offline   IRCSASW 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-September 11
  • Location:Cancún, México.

Posted 27 April 2013 - 07:23 PM

Hello forum,

I have a code that loads an XLS and will be saving each line, the point is that if during processing a line can not be stored all of the above should not be saved.

I'm doing a FOR within a transaction but does not roolBack

..
$transaction = Yii::app()->db->beginTransaction();
try
{
  for($i=1, $i...    <<-- crosses the XLS line by line
  {
    ..
    if ($modelo1->validate())
      $modelo1->save();
    else
      break;      <<-- ends to avoid going all the XLS
    ..
    if ($model2->validate())
      $modelo2->save();
    else
      break;      <<-- ends to avoid going all the XLS
  }
  $transaction->commit();
  Yii::app()->getUser()->setFlash('success','The excel file was successfully imported.');
  $this->refresh();
}
catch(Exception $e)
{
  $transaction->rollBack();
  Yii::app()->user->setFlash('error', "{$e->getMessage()}");
  $this->refresh();
}
..


$ model1 is another table that is filled with some data from XLS if necessary.

Even when an error occurs the data saved are not rolled.

My tables are InnoDB ($modelo1 y 2).

What is happening?

This is the complete code:

public function actionUpload()
{
  $uploaded = false;
  $dir = Yii::getPathOfAlias('webroot')."/protected/uploads/";
  $model=new Upload;
  
  Yii::import('ext.phpexcelreader.JPhpExcelReader');
  $separator=array('$',',','*');
  
  if(isset($_POST['Upload']))
  {
    $model->attributes=$_POST['Upload'];
    
    $file=CUploadedFile::getInstance($model,'file');
    if($model->validate())
    {
      $uploaded = $file->saveAs($dir.$file->getName());
      $data=new JPhpExcelReader($dir.$file->getName());

      $transaction = Yii::app()->db->beginTransaction();
      try
      {
        // Se recorre de nuevo el xls para ir sacando datos e ir insertando
        for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++)
        {
          $wbs_code = substr($data->sheets[0]['cells'][$i][1], 0, 10);  // ---> columna A
          $wbs = Wbs::model()->find('code=:codigo AND active=1', array('codigo'=>$wbs_code));

          $descrip  = $data->sheets[0]['cells'][$i][2];  // ---> columna B
          $commcode = $data->sheets[0]['cells'][$i][3];  // ---> columna C

          $csi_code = substr($data->sheets[0]['cells'][$i][4], 0, 20);  // ---> columna D

          $cliecode = $data->sheets[0]['cells'][$i][5];  // ---> columna E
          $csidescr = $data->sheets[0]['cells'][$i][6];  // ---> columna F
          $csiunit  = $data->sheets[0]['cells'][$i][7];  // ---> columna G

          // Verifica cada valor de D en csi_codes.csi_code, 
          // si alguno no está en el catálogo se tiene que agregar.
          $exists = CsiCodes::model()->exists('csi_code=:codigo AND active=1', array('codigo'=>$csi_code));
          if (!$exists)
          {
            // Se tiene que agregar al catálogo pero se necesita el cost_groups.id para ponerlo en
            // csi_codes.id_cost_group, se obtiene usando el valor de la col. C
            $csicode = new CsiCodes;

            // Obtiene el ID del Commodity Group que está en la col. C
            $commgr = CostGroups::model()->find('csi_cost_group=:codigo AND active=1', 
                         array('codigo'=>$commcode));

            // Llena el nuevo CSI Codes
            $csicode->id_cost_group = $commgr->id;
            $csicode->csi_code      = $csi_code;
            $csicode->client_code   = $cliecode;
            $csicode->descr         = $csidescr;
            $csicode->unit          = $csiunit;
            if ($csicode->validate())
              $csicode->save();
            else
              break;

          }
          else
            $csicode = CsiCodes::model()->find('csi_code=:codigo AND active=1', 
                           array('codigo'=>$csi_code));

          $estimateQty = $data->sheets[0]['cells'][$i][8];  // ---> columna H
          $jobHours    = $data->sheets[0]['cells'][$i][9];  // ---> columna I
          $labor       = $data->sheets[0]['cells'][$i][10];  // ---> columna J
          $material    = $data->sheets[0]['cells'][$i][11];  // ---> columna K
          $equipment   = $data->sheets[0]['cells'][$i][12];  // ---> columna L
          $subcontract = $data->sheets[0]['cells'][$i][13];  // ---> columna M
          $labor2      = $data->sheets[0]['cells'][$i][14];  // ---> columna N
          $material2   = $data->sheets[0]['cells'][$i][15];  // ---> columna O
          $equipment2  = $data->sheets[0]['cells'][$i][16];  // ---> columna P
          $subcontract2= $data->sheets[0]['cells'][$i][17];  // ---> columna Q
          $start       = substr($data->sheets[0]['cells'][$i][18], 0, 10);  // ---> columna R
          $end         = substr($data->sheets[0]['cells'][$i][19], 0, 10);  // ---> columna S

          // Se inserta a bill_of_qtys
          $billOfQtys = new BillOfQtys();
          $billOfQtys->id_wbs = $wbs->id;
          $billOfQtys->id_csi_code = $csicode->id;
          $billOfQtys->estim_qty = $estimateQty;
          $billOfQtys->tot_jobhrs = $jobHours;
          $billOfQtys->tot_labor = $labor;
          $billOfQtys->tot_material = $material;
          $billOfQtys->tot_equipment = $equipment;
          $billOfQtys->tot_subcontract = $subcontract;
          $billOfQtys->ini_date = $start;
          $billOfQtys->end_date = $end;
          $billOfQtys->estim_labor = $labor2;
          $billOfQtys->estim_material = $material2;
          $billOfQtys->estim_equipment = $equipment2;
          $billOfQtys->estim_subcontract = $subcontract2;
          $billOfQtys->active = 1;
          $billOfQtys->cancel_reason = '';
          $billOfQtys->u_crea = 1;
          if ($billOfQtys->validate())
            $billOfQtys->save();
          else
            break;

        }  // fin FOR recorre de nuevo para ir guardando

        /*
         * Se guarda toda la transacción
        */
        $transaction->commit();

        Yii::app()->getUser()->setFlash('success','The excel file was successfully imported.');
        $this->refresh();
      }  
      catch(Exception $e) // si alguna consulta falla se genera una excepción
      {
        $transaction->rollBack();
        Yii::app()->user->setFlash('error', "{$e->getMessage()}");
        $this->refresh();
      }
    }  
    else
    {
      Yii::app()->getUser()->setFlash('error','Invalid file: type, size or undefined.');
      $this->refresh();
    }
  }
  else
    $this->render('uploadxls',array('model'=>$model,'uploaded' => $uploaded));
}


Thanks.
Arturo Ramos
www.ircsasoftware.com.mx
Cancún, México
0

#2 User is offline   sensorario 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,986
  • Joined: 07-September 10
  • Location:Cesena (Italy)

Posted 27 April 2013 - 09:24 PM

Read documentatio:

$transaction=$connection->beginTransaction();
try
{
    $connection->createCommand($sql1)->execute();
    $connection->createCommand($sql2)->execute();
    //.... other SQL executions
    $transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
    $transaction->rollback();
}



write sql command and dont use CActiveRecord
0

#3 User is offline   IRCSASW 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-September 11
  • Location:Cancún, México.

Posted 28 April 2013 - 02:44 AM

Well, then I'll change it...

Thanks.
Arturo Ramos
www.ircsasoftware.com.mx
Cancún, México
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users