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.