Yii2: Match excel column with master record

Question

I am working on yii2. I have a master table called sims in my DB and all the records are saved and updated in it. In my GUI these records are maintained in SIM List. Now, there is a use-case in which I am issuing the sims to a person. The issuance is done by two ways

1. Using create form
2. Via excel file

Both cases are running perfectly. Now, issuing sims via excel file, I want to check imsi number(s) whether they are available in the master record or not.

Bellow is my Import controller

 public function actionImport(){
    $file_name = $_POST['file_name'];
    $header_index = $_POST['header_index'];
    $fieldSet = $_POST['field'];


    $data = \moonland\phpexcel\Excel::widget([
        'mode' => 'import',
        'fileName' => 'uploads/' . $file_name,
        'setFirstRecordAsKeys' => false, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
        'setIndexSheetByName' => false, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
        'getOnlySheet' => 0, // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
    ]);

    //loop therogh first sheet
    $ok_count = 0;
    $status_arr = [];
    $final_data = isset($data[0]) ? $data[0] : $data;
    foreach($final_data as $key=>$value)
    {
        if($key <= $header_index) continue;
        $sims = new SimIssueanceTransaction();

        foreach($value as $k=>$v){


            $v = preg_replace('/\s+/', ' ', trim($v));
            if(isset($fieldSet[0]['imsi']) && $fieldSet[0]['imsi']==$k){
                $sims->sim_id = Sims::imsiToidexcel($v);
                $sims->imsi =$v."";

            }

            if(isset($fieldSet[0]['issued_to']) && $fieldSet[0]['issued_to']==$k){
                $sims->issued_to = $v;
            }

            if (isset($fieldSet[0]['purpose']) && $fieldSet[0]['purpose'] == $k) {
                $sims->purpose = $v;
            }

        }
        $sims->issued_at = date('Y-m-d H:i:s');
        $sims->issued_by = Yii::$app->user->id;
        $sims->historic =1;

        if($sims->purpose=='Local SIM Issue')
        {
            $sims->status = Sims::$status_titles[1];
            Sims::change_status($sims->sim_id,Sims::$status_titles[1]);
        }
        else
        {
            $sims->status = Sims::$status_titles[2];
            Sims::change_status($sims->sim_id,Sims::$status_titles[2]);
        }


        if($sims->save())
        {
            $ok_count++;
        }
        else
        {
            $status_arr[] = $sims->errors;
        }


    }

    return $this->render('excel_finish', ['records_saved' => $ok_count,'status_arr'=>$status_arr]);
}

I want to add a check in bellow condition

if(isset($fieldSet[0]['imsi']) && $fieldSet[0]['imsi']==$k){
                $sims->sim_id = Sims::imsiToidexcel($v);
                $sims->imsi =$v."";

            }

Update 1

My Sims model is below

public function rules()
{
    return [
        [['imsi','operator_name','data_details','sms_details','status'], 'required'],
        [['created_by', 'updated_by', 'sim_stauts', 'issued_to', 'returned_by', 'historic'], 'integer'],
        [['created_at', 'updated_at','returned_at'], 'safe'],
        [['imsi', 'operator_name', 'data_details', 'sms_details','bill_date'], 'string', 'max' => 20],
        [['sim_number', 'status','credit_limit','plan_name'], 'string', 'max' => 50],
        [['monthly_bill'], 'string', 'max' => 100],
        //[['imsi'], 'unique'],
        [['created_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['created_by' => 'id']],
    ];
}
public function attributeLabels()
{
    return [
        'id' => 'ID',
        'imsi' => 'Imsi',
        'sim_number' => 'Sim Number',
        'operator_name' => 'Operator Name',
        'data_details' => 'Data Details',
        'sms_details' => 'Sms Details',
        'monthly_bill' => 'Monthly Bill',
        'created_by' => 'Created By',
        'created_at' => 'Created At',
        'updated_at' => 'Updated At',
        'status' => 'Status',
        'updated_by' => 'Updated By',
        'sim_stauts' => 'Sim Stauts',
        'issued_to' => 'Issued To',
        'returned_by' => 'Returned By',
        'historic' => 'Version',
        'returned_at'=>'Returned At',
        'bill_date' => 'Billing Date',
        'credit_limit' => 'Credit Limit',
        'plan_name'=> 'Plan Name'
    ];
}

Update 2

As per suggestion I have created a function

 protected function findImsi($imsi){

    if(($model=Sims::findOne(['imsi'=>$imsi]))!== null){
        return true;
    }
    else{
        return false;
    }


}

Then in my import controller

foreach($final_data as $key=>$value)
    {
        if($key <= $header_index) continue;
        $sims = new SimIssueanceTransaction();

        foreach($value as $k=>$v){

            $v = preg_replace('/\s+/', ' ', trim($v));
            $imsiValid = isset($fieldSet[0]['imsi']) && $fieldSet[0]['imsi'] == $k && $this->findImsi($v);

            if ($imsiValid) {
                $sims->sim_id = Sims::imsiToidexcel($v);
                $sims->imsi = $v . "";
            }
            else
            {

                \Yii::$app->getSession()->setFlash('error', '
 <div class="alert alert-error alert-dismissable">
 <button aria-hidden="true" data-dismiss="alert" class="close" type="button">×</button>
 <strong>Error!!! No Record is inserted..</strong> IMSI must be wrong </div>');
                return $this->redirect(['simissueancetransaction/excel']);
            }
.
.
.
}
.
.
.
}

While uploading an excel file with correct values in it, the $imsiValid is true but still it's not entering the if condition

By doing var_dump($final_data); I got the following result

array(3) { [1]=> array(4) { ["A"]=> string(4) "imsi" ["B"]=> string(9) "issued to" ["C"]=> string(7) "purpose" ["D"]=> NULL } [2]=> array(4) { ["A"]=> string(18) "899204031015192575" ["B"]=> float(134) ["C"]=> string(20) "Production SIM Issue" ["D"]=> NULL } [3]=> array(4) { ["A"]=> string(18) "899204031015192576" ["B"]=> float(134) ["C"]=> string(20) "Production SIM Issue"} }

Update 3

Below are the other two action controller functions for upload excel file.

  public function actionExcel(){

    $file_name = "excel_" . Yii::$app->user->id . ".xlsx";

    $error = "";
    if(isset($_FILES['file'])) {
        $path_parts = pathinfo($_FILES["file"]["name"]);
        $extension = $path_parts['extension'];

        if(!in_array($extension,['xlsx','xls'])){

            $error = "Invalid file";
        }else {
            if (move_uploaded_file($_FILES['file']['tmp_name'], 'uploads/' . $file_name)) {

                $this->redirect(Url::to('process?file_name=' . $file_name . "&header_no=" . $_POST['header_no']));
            }
        }
    }
    return $this->render("excel",['error'=>$error]);
}
public function actionProcess(){

    $file_name = $_GET['file_name'];

    // $data = \moonland\phpexcel\Excel::import("uploads/test.xlsx"); // $config is an optional

    try {
        $header_index = $_GET['header_no'];

        $data = \moonland\phpexcel\Excel::widget([
            'mode' => 'import',
            'fileName' => 'uploads/' . $file_name,
            'setFirstRecordAsKeys' => false, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
            'setIndexSheetByName' => false, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
            'getOnlySheet' => 0, // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
        ]);
        if (isset($data[0])) {
            $headers = $data[0][$header_index];
        } else {
            $headers = $data[$header_index];
        }

    }catch (Exception $x){
        die("Error");
    }

    return $this->render('excel_options',['headers'=>$headers,'file_name'=>$file_name,'header_index'=>$header_index]);

}

After these two function the import function is called

The model in which all the records are saved and updated is Sims.

How can I match the IMSI number with the master record?

Any help would be highly appreciated.

Answer 1

What i have understood from your question is that you have Sims model in which you have saved all the sims along with the imsi and before you insert any record into the SimIssueanceTransaction you want to verify if it exists in the Sims or not.

If this is correct then take the following steps

Add a method to your controller

protected function findModel($imsi){
    if(($model=Sims::findOne(['imsi'=>$imsi])) !== null){
        return true;
    }
    return false;
}

and then replace these lines

$v = preg_replace('/\s+/', ' ', trim($v));
if (isset($fieldSet[0]['imsi']) && $fieldSet[0]['imsi'] == $k) {
    $sims->sim_id = Sims::imsiToidexcel($v);
    $sims->imsi = $v . "";
}

with the following

$v = preg_replace('/\s+/', ' ', trim($v));
$imsiValid = isset($fieldSet[0]['imsi']) && $fieldSet[0]['imsi'] == $k;

if ($imsiValid) {
  if($this->findModel($v)){
    $sims->sim_id = Sims::imsiToidexcel($v);
    $sims->imsi = $v . "";
  }
}

Note :I assume that $v will have the imsi when $fieldSet[0]['imsi']==$k as you are setting $sims->imsi = $v inside the condition, other wise change $this->findModel($v) accordingly