Yii2：将excel列与主记录匹配

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

我正在研究yii2。我的数据库中有一个名为sims的主表，所有记录都保存并更新。在我的GUI中，这些记录保存在SIM列表中。现在，有一个用例，我将sims发给一个人。发行方式有两种

Using create form
使用创建表单
Via excel file
通过excel文件

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.

两种情况都运行得很好。现在，通过excel文件发出sims，我想检查imsi号码是否在主记录中可用。

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

更新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

更新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

在上传包含正确值的excel文件时，$ imsiValid为true，但仍然没有进入if条件

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

通过执行var_dump（$ final_data）;我得到了以下结果

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

更新3

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

以下是上传excel文件的另外两个动作控制器功能。

  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

在这两个函数之后调用import函数

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

保存和更新所有记录的模型是模拟人生。

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

如何将IMSI号码与主记录匹配？

Any help would be highly appreciated.

任何帮助将受到高度赞赏。

1 个解决方案

#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.

我从你的问题中理解的是你有Sims模型，你已经将所有的sims与imsi一起保存，然后在将任何记录插入SimIssueanceTransaction之前，你要验证它是否存在于模拟人生中。

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

注意：我假设$ v将在$ fieldSet [0] ['imsi'] == $ k时使用imsi，因为你在条件中设置$ sims-> imsi = $ v，其他明智的改变$ this-> findModel （$ v）据此

#1