title: Yii2: Match excel column with master record
body: <p>I am working on <code>yii2</code>. I have a master table called <code>sims</code> in my DB and all the records are saved and updated in it. In my GUI these records are maintained in <code>SIM List</code>. Now, there is a use-case in which I am issuing the sims to a person. The issuance is done by two ways </p>
  
  <ol>
  <li>Using create form</li>
  <li>Via excel file</li>
  </ol>
  
  <p>Both cases are running perfectly. Now, issuing sims via excel file, I want to check <code>imsi</code> number(s) whether they are available in the master record or not.  </p>
  
  <p>Bellow is my <code>Import</code> controller</p>
  
  <pre><code> public function actionImport(){
      $file_name = $_POST['file_name'];
      $header_index = $_POST['header_index'];
      $fieldSet = $_POST['field'];
  
  
      $data = \moonland\phpexcel\Excel::widget([
          'mode' =&gt; 'import',
          'fileName' =&gt; 'uploads/' . $file_name,
          'setFirstRecordAsKeys' =&gt; 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' =&gt; 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' =&gt; 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=&gt;$value)
      {
          if($key &lt;= $header_index) continue;
          $sims = new SimIssueanceTransaction();
  
          foreach($value as $k=&gt;$v){
  
  
              $v = preg_replace('/\s+/', ' ', trim($v));
              if(isset($fieldSet[0]['imsi']) &amp;&amp; $fieldSet[0]['imsi']==$k){
                  $sims-&gt;sim_id = Sims::imsiToidexcel($v);
                  $sims-&gt;imsi =$v."";
  
              }
  
              if(isset($fieldSet[0]['issued_to']) &amp;&amp; $fieldSet[0]['issued_to']==$k){
                  $sims-&gt;issued_to = $v;
              }
  
              if (isset($fieldSet[0]['purpose']) &amp;&amp; $fieldSet[0]['purpose'] == $k) {
                  $sims-&gt;purpose = $v;
              }
  
          }
          $sims-&gt;issued_at = date('Y-m-d H:i:s');
          $sims-&gt;issued_by = Yii::$app-&gt;user-&gt;id;
          $sims-&gt;historic =1;
  
          if($sims-&gt;purpose=='Local SIM Issue')
          {
              $sims-&gt;status = Sims::$status_titles[1];
              Sims::change_status($sims-&gt;sim_id,Sims::$status_titles[1]);
          }
          else
          {
              $sims-&gt;status = Sims::$status_titles[2];
              Sims::change_status($sims-&gt;sim_id,Sims::$status_titles[2]);
          }
  
  
          if($sims-&gt;save())
          {
              $ok_count++;
          }
          else
          {
              $status_arr[] = $sims-&gt;errors;
          }
  
  
      }
  
      return $this-&gt;render('excel_finish', ['records_saved' =&gt; $ok_count,'status_arr'=&gt;$status_arr]);
  }
  </code></pre>
  
  <p>I want to add a check in bellow condition </p>
  
  <pre><code>if(isset($fieldSet[0]['imsi']) &amp;&amp; $fieldSet[0]['imsi']==$k){
                  $sims-&gt;sim_id = Sims::imsiToidexcel($v);
                  $sims-&gt;imsi =$v."";
  
              }
  </code></pre>
  
  <p><strong>Update 1</strong></p>
  
  <p>My <code>Sims</code> model is below </p>
  
  <pre><code>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' =&gt; 20],
          [['sim_number', 'status','credit_limit','plan_name'], 'string', 'max' =&gt; 50],
          [['monthly_bill'], 'string', 'max' =&gt; 100],
          //[['imsi'], 'unique'],
          [['created_by'], 'exist', 'skipOnError' =&gt; true, 'targetClass' =&gt; User::className(), 'targetAttribute' =&gt; ['created_by' =&gt; 'id']],
      ];
  }
  public function attributeLabels()
  {
      return [
          'id' =&gt; 'ID',
          'imsi' =&gt; 'Imsi',
          'sim_number' =&gt; 'Sim Number',
          'operator_name' =&gt; 'Operator Name',
          'data_details' =&gt; 'Data Details',
          'sms_details' =&gt; 'Sms Details',
          'monthly_bill' =&gt; 'Monthly Bill',
          'created_by' =&gt; 'Created By',
          'created_at' =&gt; 'Created At',
          'updated_at' =&gt; 'Updated At',
          'status' =&gt; 'Status',
          'updated_by' =&gt; 'Updated By',
          'sim_stauts' =&gt; 'Sim Stauts',
          'issued_to' =&gt; 'Issued To',
          'returned_by' =&gt; 'Returned By',
          'historic' =&gt; 'Version',
          'returned_at'=&gt;'Returned At',
          'bill_date' =&gt; 'Billing Date',
          'credit_limit' =&gt; 'Credit Limit',
          'plan_name'=&gt; 'Plan Name'
      ];
  }
  </code></pre>
  
  <p><strong>Update 2</strong></p>
  
  <p>As per suggestion I have created a function </p>
  
  <pre><code> protected function findImsi($imsi){
  
      if(($model=Sims::findOne(['imsi'=&gt;$imsi]))!== null){
          return true;
      }
      else{
          return false;
      }
  
  
  }
  </code></pre>
  
  <p>Then in my <code>import</code> controller </p>
  
  <pre><code>foreach($final_data as $key=&gt;$value)
      {
          if($key &lt;= $header_index) continue;
          $sims = new SimIssueanceTransaction();
  
          foreach($value as $k=&gt;$v){
  
              $v = preg_replace('/\s+/', ' ', trim($v));
              $imsiValid = isset($fieldSet[0]['imsi']) &amp;&amp; $fieldSet[0]['imsi'] == $k &amp;&amp; $this-&gt;findImsi($v);
  
              if ($imsiValid) {
                  $sims-&gt;sim_id = Sims::imsiToidexcel($v);
                  $sims-&gt;imsi = $v . "";
              }
              else
              {
  
                  \Yii::$app-&gt;getSession()-&gt;setFlash('error', '
   &lt;div class="alert alert-error alert-dismissable"&gt;
   &lt;button aria-hidden="true" data-dismiss="alert" class="close" type="button"&gt;×&lt;/button&gt;
   &lt;strong&gt;Error!!! No Record is inserted..&lt;/strong&gt; IMSI must be wrong &lt;/div&gt;');
                  return $this-&gt;redirect(['simissueancetransaction/excel']);
              }
  .
  .
  .
  }
  .
  .
  .
  }
  </code></pre>
  
  <p>While uploading an excel file with correct values in it, the <code>$imsiValid</code> is <code>true</code> but still it's not entering the <code>if</code> condition </p>
  
  <p>By doing <code>var_dump($final_data);</code> I got the following result </p>
  
  <pre><code>array(3) { [1]=&gt; array(4) { ["A"]=&gt; string(4) "imsi" ["B"]=&gt; string(9) "issued to" ["C"]=&gt; string(7) "purpose" ["D"]=&gt; NULL } [2]=&gt; array(4) { ["A"]=&gt; string(18) "899204031015192575" ["B"]=&gt; float(134) ["C"]=&gt; string(20) "Production SIM Issue" ["D"]=&gt; NULL } [3]=&gt; array(4) { ["A"]=&gt; string(18) "899204031015192576" ["B"]=&gt; float(134) ["C"]=&gt; string(20) "Production SIM Issue"} }
  </code></pre>
  
  <p><strong>Update 3</strong></p>
  
  <p>Below are the other two action controller functions for upload excel file. </p>
  
  <pre><code>  public function actionExcel(){
  
      $file_name = "excel_" . Yii::$app-&gt;user-&gt;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-&gt;redirect(Url::to('process?file_name=' . $file_name . "&amp;header_no=" . $_POST['header_no']));
              }
          }
      }
      return $this-&gt;render("excel",['error'=&gt;$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' =&gt; 'import',
              'fileName' =&gt; 'uploads/' . $file_name,
              'setFirstRecordAsKeys' =&gt; 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' =&gt; 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' =&gt; 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-&gt;render('excel_options',['headers'=&gt;$headers,'file_name'=&gt;$file_name,'header_index'=&gt;$header_index]);
  
  }
  </code></pre>
  
  <p>After these two function the <code>import</code> function is called</p>
  
  <p>The model in which all the records are saved and updated is <code>Sims</code>. </p>
  
  <p>How can I match the IMSI number with the master record?</p>
  
  <p>Any help would be highly appreciated.</p>