I have these following functions.
public function importExcelFile(){
$file = $_FILES['file']['tmp_name'];
$data = extract_excel_data($file);
$i = 0;
foreach($data['values'] as $dataValues) {
$categories = [];
$brands = [];
$models = [];
foreach($dataValues as $value){
if(array_filter($value)) {
/* If a row does not contain brand/category/model for the product then fetch the resp. info. from previous row */
if(empty(trim($value[0]))) {
$categories[] = $prev_cat;
} else {
$categories[] = strtoupper(trim($value[0]));
$prev_cat = strtoupper(trim($value[0]));
}
if(empty(trim($value[1]))) {
$brands[] = $prev_brand;
} else {
$brands[] = strtoupper(trim($value[1]));
$prev_brand = strtoupper(trim($value[1]));
}
if(empty(trim($value[2]))) {
$models[] = $prev_model;
} else {
$models[] = $value[2];
$prev_model = $value[2];
}
}
}
//insert device category
$this->insert_setups('category', $categories);
//insert brand
$this->insert_setups('brand', $brands);
// Check if branch already exists in the database
$check_branch = $this->global_model->getDetailByWhere('branch', array('name'=>$data['branch'][$i].' branch'))->result();
$branch_arr = [];
//insert branch
if(empty($check_branch)) {
$branch_arr = array(
'name' => $data['branch'][$i].' branch',
'location' => $data['branch'][$i],
'status' => 1,
'created_by' => $this->session->userdata('id'),
'created_on' => date('Y-m-d')
);
$this->global_model->insertData('branch', $branch_arr);
}
$branch_id = $this->global_model->getDetailByWhere('branch', array('name'=>$data['branch'][$i].' branch'))->row()->id;
$db_device_categories = [];
$db_brands = [];
// get categoris, brands
$db_device_categories = $this->arrangeArray('category', $where =array());
$db_brands = $this->arrangeArray('brand', $where =array());
//detail_print($db_brands);
// insert new models from database
foreach(array_unique($models) as $model_key=>$model){
$check_model = $this->global_model->getDetailByWhere('model', array('name'=>$model))->result();
$insert = [];
if(empty($check_model)){
$insert = array(
'name' => $model,
'item_type' => 1,
'category_id' => $db_device_categories[$categories[$model_key]],
'brand_id' => $db_brands[$brands[$model_key]],
'created_by' => $this->session->userdata("id"),
'created_on' => date('Y-m-d'),
);
$this->global_model->insertData('model', $insert);
}
}
$db_device_models = [];
// get models from database
$db_device_models = $this->arrangeArray('model', $where = array('item_type'=>1));
$categoriy_id = [];
$brand_id = [];
$model_id = [];
$opening_stock = [];
// arrange the exported array with respective id
foreach($dataValues as $values){
if(array_filter($values)) {
if(empty(trim($values[0]))) {
$category_id = $prev_cat;
} else {
$category_id = strtoupper(trim($values[0]));
$prev_cat = strtoupper(trim($values[0]));
}
if(empty(trim($values[1]))) {
$brand_id = $prev_brand;
} else {
$brand_id = strtoupper(trim($values[1]));
$prev_brand = strtoupper(trim($values[1]));
}
if(empty(trim($values[2]))) {
$model_id = $prev_model;
} else {
$model_id = $values[2];
$prev_model = $values[2];
}
$opening_stock[] = array(
'category_id' => $db_device_categories[$category_id],
'brand_id' => $db_brands[$brand_id],
'model_id' => $db_device_models[$model_id],
'imei' => (string)$values[3],
'cost_price' => isset($values[5]) ? $values[5] : 0,
'selling_price' => isset($values[6]) ? $values[6] : 0
);
}
}
$group_by_model = [];
// group the array by model_id
foreach(array_unique($models) as $model1){
$where = $db_device_models[$model1];
$group_by_model[] = array_filter($opening_stock, function($elements) use ($where){
return $elements["model_id"] == $where;
});
}
if(!$this->purchase_model->insertOpeningStock($group_by_model, $branch_id)){
$this->session->set_flashdata('error', 'Opening stock of devices insertion failed.');
redirect('purchase/uploadExcelFile');
}
$i++;
}
$this->session->set_flashdata('success', 'Opening stock of devices added successfully.');
redirect('purchase/uploadExcelFile');
}
private function arrangeArray($table, $where){
$list = $this->global_model->getDetailByWhere($table, $where)->result_array();
foreach($list as $item){
$name = $item['name'];
$arranged_list[$name] = $item['id'];
}
return !empty($arranged_list) ? $arranged_list : NULL;
}
private function insert_setups($table_name, $setups){
foreach(array_unique($setups) as $value){
$check_setup = $this->global_model->getDetailByWhere($table_name, array('name'=>$value))->result();
if(empty($check_setup)){
$insert = array(
'name' => $value,
'created_by' => $this->session->userdata("id"),
'created_on' => date('Y-m-d'),
);
$this->global_model->insertData($table_name, $insert);
}
}
}
What this function does is, it extracts data from the uploaded excel file and inserts the data to various tables accordingly. Now as you can see, there are multiple queries running in different locations inside the importExcelFile() method. So my question is, how do I use codeigniter transaction in such a way that all the queries inside this function are performed atomically. If any one query fails, all other query's work is rolled back. Also, is this code considered clean ?
P.S. I'm so sorry if my last question was inappropriate here.
this might be helpful to you. transactions in codeigniter
$this->db->trans_begin();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
if ($this->db->trans_status() === FALSE)
{
$this->db->trans_rollback();
}
else
{
$this->db->trans_commit();
}