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
- Using create form
- 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.
