I have THREE tables
CREATE TABLE building (
building_id int(23) NOT NULL AUTO_INCREMENT PRIMARY KEY,
city_id int(12) NOT NULL,
cat_id int(12) NOT NULL
building_name varchar(30) NOT NULL,
corporation_name varchar(25) NOT NULL,
supply_rating varchar(20) NOT NULL,
last_inspection date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE cities (
city_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,
city_name char(254) NOT NULL,
city_location text NOT NULL,
state_id int(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE building_category (
cat_id int(12) NOT NULL AUTO_INCREMENT PRIMARY KEY,
cat_name varchar(85) NOT NULL,
parent_id int(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
tHE MAIN TABLE IS BUILDING.
I have an excel file that i want to import into the application, and the save it to the database.
7032
I want it that as its doing the import or upload, it will check the tables of the foreign keys. I f the values have not been created, it will create it. Am try to use the code below, but dont know how to really go about it. Please any other solution?
Controller
<?php
/**
*
* @package modules.consumers.controllers
*/
namespace app\modules\consumers\controllers;
use Yii;
use arogachev\excel\import\basic\Importer;
use yii\web\UploadedFile;
use PHPExcel;
use PHPExcel_IOFactory;
use yii\helpers\Html;
use app\modules\consumers\models\Building;
use app\modules\consumers\models\BuildingManager;
use app\modules\consumers\models\BuildingCategory;
use app\models\AuthAssignment;
use app\models\City;
use app\models\State;
use app\models\Zone;
use app\models\User;
class BuildingImportController extends \yii\web\Controller
{
public function actionIndex()
{
$model = new Building();
$model->scenario = 'import-building';
$importResults = [];
if ($model->load(Yii::$app->request->post())) {
$model->importFile = UploadedFile::getInstance($model, 'importFile');
if($model->saveImportFile()) {
$importResults = $this->importBuildingData($model);
//print_r($importResults); exit;
}
}
return $this->render('index', [
'model' => $model,
'importResults' => $importResults,
]);
}
public function importBuildingData($model)
{
$dispResults = [];
$totalSuccess = 0;
$objPHPExcel = PHPExcel_IOFactory::load($model->importFilePath.$model->importFile);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
//print_r($sheetData); exit;
unset($sheetData[1]);
//start import building row by row
foreach($sheetData as $k => $line){
//print_r($line); exit;
if(!array_filter($line))
continue;
$line = array_map('trim', $line);
$line = array_map(function($value) { return empty($value) ? NULL : $value; }, $line);
$buildingMaster = new Building();
$buildingMaster->scenario = 'import-building';
//set building info attributes
$buildingMaster->last_inspection = Yii::$app->dateformatter->getDateFormat($line['A']); //Last Inspection Date
$buildingMaster->building_name = $line['B']; //Building Name
// $buildingMaster->corporation_name = $line['C']; //Corporation Name
$buildingMaster->address = $line['C']; //Address
$buildingMaster->status = $this->valueReplace($line['D'], $buildingMaster->getBuildingStatus()); // Building Status
$buildingMaster->corporation_type = $this->valueReplace($line['E'], $buildingMaster->getCorporationTypes()); // Corporation Types
$buildingMaster->supply_rating = $line['F']; //Supply Rating
//set building master attribute
$buildingMaster->city = $this->valueReplace($line['L'], City::getAllCity()); //City
$buildingMaster->cat_id = $this->valueReplace($line['M'], BuildingCategory::getBuildingCategoryId()); //Building Category
$buildingMaster->manager_id = $this->valueReplace($line['N'], BuildingManager::getBuildingManager()); //Building Manager
if($buildingMaster->save())
//set building info attribute
$buildingManager->corporation_name = $line['O']; //Corporation Name
return ['dispResults' => $dispResults, 'totalSuccess' => $totalSuccess];
}
protected function valueReplace($value, $arrayData)
{
if(empty($value) || empty($arrayData)) {
return null;
} else {
$key = array_search(strtolower($value), array_map('strtolower', $arrayData));
return ($key) ? $key : NULL;
}
}
public function actionDownloadFile($id)
{
$path = null;
if($id=='SSF') {
$path = Yii::getAlias('@webroot').'/data/import_files/building_import_en.xlsx';
}
if(file_exists($path)) {
return \Yii::$app->response->sendFile($path);
}
throw new NotFoundHttpException('The requested file does not exist.');
}
}
VIEW
<?php
use yii\helpers\Html;
use yii\widgets\Breadcrumbs;
use yii\grid\GridView;
use yii\helpers\Url;
use yii\helpers\ArrayHelper;
use yii\widgets\ActiveForm;
use yii\widgets\Pjax;
fedemotta\datatables\DataTablesBootstrapAsset::register($this);
/* @var $this yii\web\View */
$this->title = Yii::t('consumers', 'Import Building');
$this->params['breadcrumbs'][] = ['label' => Yii::t('consumers', 'Building Profile Master'), 'url'=>['default/index']];
$this->params['breadcrumbs'][] = $this->title;
?>
<?php $form = ActiveForm::begin([
'id' => 'import-building',
'enableAjaxValidation' => false,
'options' => ['enctype' => 'multipart/form-data'],
]); ?>
<div class="box box-primary">
<div class="box-header with-border">
<h3 class="box-title"><i class="fa fa-file-excel-o"></i> <?php echo Yii::t('consumers', 'Select File'); ?></h3>
</div><!--./box-header-->
<div class="box-body">
<div class="row">
<div class="col-sm-12 col-xs-12">
<?= $form->field($model, 'importFile')->fileInput(['title' => Yii::t('consumers', 'Browse Excel File')])->label(false) ?>
</div>
</div>
<div class="row">
<div class="col-sm-12 col-xs-12">
<div class="callout callout-info">
<h4><?php echo Yii::t('consumers', 'You must have to follow the following instruction at the time of importing data'); ?></h4>
<ol>
<li><b><?php echo Yii::t('consumers', 'The field with red color are the required field cannot be blank.'); ?></b></li>
<li><?php echo Yii::t('consumers', 'Building ID is auto generated.'); ?></li>
</ol>
<h5><?php echo Yii::t('consumers', 'Download the sample format of Excel sheet.'); ?> <b><?= Html::a(Yii::t('consumers', 'Download'), ['download-file', 'id' => 'SSF']) ?></b></h5>
</div><!--./callout-->
</div><!--./col-->
</div><!--./row-->
</div><!--./box-body-->
<div class="box-footer">
<?= Html::submitButton('<i class="fa fa-upload"></i>'.Yii::t('consumers', ' Import'), ['class' => 'btn btn-primary']) ?>
</div>
</div><!--./box-->
<?php ActiveForm::end(); ?>
<?php if(!empty($importResults)) : ?>
<!--Start Import Summary Results Block-->
<div class="box box-info">
<div class="box-header">
<h3 class="box-title"><i class="fa fa-list-ul"></i> <?php echo Yii::t('consumers', 'Buildings Import Results'); ?></h3>
</div>
<div class="box-body">
<div class="row">
<div class="col-sm-12">
<?php $totalError = (count($importResults['dispResults'])-$importResults['totalSuccess']); ?>
<?php $headerTr = $content = ''; $i = 1; ?>
</div>
</div>
</div><!--./box-body-->
</div><!--./box-->
<?php endif; ?>
<?php
$ckBoxCheckScript = <<< JS
var table = $('#fixHeader').DataTable( {
scrollY: "450px",
scrollX: true,
scrollCollapse: false,
paging: false,
bSort: false,
bInfo: false,
} );
JS;
$this->registerJs($ckBoxCheckScript, yii\web\View::POS_READY);
?>
Please help me. Any other solution