public function actionIndex() { $delimiter = ";"; //publish css and js Yii::app()->clientScript->registerCssFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/styles.css')); Yii::app()->clientScript->registerScriptFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/ajaxupload.js')); Yii::app()->clientScript->registerScriptFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/download.js')); //getting all tables from db $tables = Yii::app()->getDb()->getSchema()->getTableNames(); $tablesLength = sizeof($tables); $tablesArray = array(); for ($i = 0; $i < $tablesLength; $i++) { $tablesArray[$tables[$i]] = $tables[$i]; } if (Yii::app()->request->isAjaxRequest) { /* ������ ������ */ $_POST = array("table" => "rktv_module_catalog_product", "delimiter" => ";", "mode" => 4, "perRequest" => 1, "csvKey" => "3", "tableKey" => "article", "columns" => array("id" => "", "category_id" => "", "name" => 1, "short" => "", "text" => "", "small_img_id" => "", "large_img_id" => "", "price" => 2, "article" => 3, "unit" => "", "recomendate" => "")); $_POST['thirdStep'] = 1; $_POST['thirdDelimiter'] = ';'; $_POST['thirdTable'] = 'rktv_module_catalog_product'; $_POST['thirdFile'] = 'upload/importCsv/catalog_price.csv'; $_POST['Mode'] = 4; $_POST['perRequest'] = 1; $_POST['Tablekey'] = 'article'; $_POST['CSVkey'] = 3; $_POST['Poles'] = array(0 => "", 1 => "", 2 => "1", 3 => "", 4 => "", 5 => "", 6 => "", 7 => "2", 8 => "3", 9 => "", 10 => ""); /* � �� ���� � �� ����� ��������*/ if ($_POST['thirdStep'] != 1) { //second step $delimiter = CHtml::encode(trim($_POST['delimiter'])); $table = CHtml::encode($_POST['table']); if ($_POST['delimiter'] == '') { $error = 1; } else { // get all columns from csv file $error = 0; $uploaddir = Yii::app()->controller->module->path; $uploadfile = $uploaddir . basename($_POST['fileName']); $filecontent = explode("\n", file_get_contents($uploadfile)); $csvFirstLine = explode($delimiter, $filecontent[0]); //get all columns from selected table $model = new ImportCsv(); $tableColumns = $model->tableColumns($table); } // checking file with earlier imports $paramsArray = $this->checkOldFile($uploadfile); $this->layout = 'clear'; $this->render('secondResult', array('error' => $error, 'tableColumns' => $tableColumns, 'delimiter' => $delimiter, 'table' => $table, 'fromCsv' => $csvFirstLine, 'paramsArray' => $paramsArray)); } else { //third step if (array_sum($_POST['Poles']) > 0) { if ($_POST['perRequest'] != '') { if (is_numeric($_POST['perRequest'])) { $tableKey = CHtml::encode($_POST['Tablekey']); $csvKey = CHtml::encode($_POST['CSVkey']); $mode = CHtml::encode($_POST['Mode']); if (($mode == 2 || $mode == 3 || $mode == 4) && ($tableKey == '' || $csvKey == '')) { $error = 4; } else { $error = 0; $delimiter = CHtml::encode(trim($_POST['thirdDelimiter'])); $table = CHtml::encode($_POST['thirdTable']); $uploadfile = CHtml::encode(trim($_POST['thirdFile'])); $poles = $_POST['Poles']; $perRequest = CHtml::encode($_POST['perRequest']); //import from csv to db $model = new ImportCsv(); $tableColumns = $model->tableColumns($table); //select old rows from table if ($mode == 2 || $mode == 3 || $mode == 4) { $oldItems = $model->selectRows($table, $tableKey); } $replace = addslashes(file_get_contents($uploadfile)); $filecontent = explode("\n", $replace); $lengthFile = sizeof($filecontent); $insertCounter = 0; $insertArray = array(); $error_array = array(); $stepsOk = 0; // begin transaction $transaction = Yii::app()->db->beginTransaction(); try { // import to database for ($i = 0; $i < $lengthFile; $i++) { if ($i != 0 && $filecontent[$i] != '') { $csvLine = explode($delimiter, $filecontent[$i]); //Mode 1. insert All if ($mode == 1) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $poles, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) { $arrays[] = $i; } } } // Mode 2. Insert new if ($mode == 2) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $poles, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) { $arrays[] = $i; } } } } // Mode 3. Insert new and replace old if ($mode == 3) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { // insert new $linesArray[] = $csvLine; $strCounter++; if ($strCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $linesArray, $poles, $tableColumns); $strCounter = 0; $linesArray = array(); if ($import != 1) { $arrays[] = $i; } } } else { //replace old $import = $model->updateOld($table, $csvLine, $poles, $tableColumns, $csvLine[$csvKey - 1], $tableKey); if ($import != 1) { $arrays[] = $i; } } } // Mode 4. Replace old if ($mode == 4) { if ($this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { $import = $model->updateOld($table, $csvLine, $poles, $tableColumns, $csvLine[$csvKey - 1], $tableKey); if ($import != 1) { $arrays[] = $i; } } } } } if ($insertCounter != 0) { $model->InsertAll($table, $insertArray, $poles, $tableColumns); } // commit transaction if not exception $transaction->commit(); } catch (Exception $e) { $transaction->rollBack(); } // save params in file $this->saveInFile($table, $delimiter, $mode, $perRequest, $csvKey, $tableKey, $tableColumns, $poles, $uploadfile); } } else { $error = 3; } } else { $error = 2; } } else { $error = 1; } $this->layout = '_clear'; $this->render('thirdResult', array('error' => $error, 'delimiter' => $delimiter, 'table' => $table, 'uploadfile' => $uploadfile, 'error_array' => $error_array)); } Yii::app()->end(); } else { // first loading $this->render('index', array('delimiter' => $delimiter, 'tablesArray' => $tablesArray)); } }
public function actionIndex() { $delimiter = ";"; $textDelimiter = '"'; //publish css and js Yii::app()->clientScript->registerCssFile( Yii::app()->assetManager->publish( Yii::getPathOfAlias('application.modules.importcsv.assets') . '/styles.css' ) ); Yii::app()->clientScript->registerScriptFile( Yii::app()->assetManager->publish( Yii::getPathOfAlias('application.modules.importcsv.assets') . '/ajaxupload.js' ) ); Yii::app()->clientScript->registerScript('uploadActionPath', 'var uploadActionPath="' . $this->createUrl('default/upload') . '"', CClientScript::POS_BEGIN); Yii::app()->clientScript->registerScriptFile( Yii::app()->assetManager->publish( Yii::getPathOfAlias('application.modules.importcsv.assets') . '/download.js' ) ); //getting all tables from db $tables = Yii::app()->getDb()->getSchema()->getTableNames(); $tablesLength = sizeof($tables); $tablesArray = array(); for ($i = 0; $i < $tablesLength; $i++) { $tablesArray[$tables[$i]] = $tables[$i]; } if (Yii::app()->request->isAjaxRequest) { if ($_POST['thirdStep'] != 1) { //second step $delimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['delimiter'])))); $textDelimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['textDelimiter'])))); $table = CHtml::encode($_POST['table']); if ($_POST['delimiter'] == '') { $error = 1; $csvFirstLine = array(); $paramsArray = array(); } else { // get all columns from csv file $error = 0; $uploaddir = Yii::app()->controller->module->path; $uploadfile = $uploaddir . basename($_POST['fileName']); $file = fopen($uploadfile, "r"); $csvFirstLine = ($textDelimiter) ? fgetcsv($file, 0, $delimiter, $textDelimiter) : fgetcsv($file, 0, $delimiter); fclose($file); // checking file with earlier imports $paramsArray = $this->checkOldFile($uploadfile); } //get all columns from selected table $model = new ImportCsv; $tableColumns = $model->tableColumns($table); //$this->layout = 'clear'; $this->render('secondResult', array( 'error' => $error, 'tableColumns' => $tableColumns, 'delimiter' => $delimiter, 'textDelimiter' => $textDelimiter, 'table' => $table, 'fromCsv' => $csvFirstLine, 'paramsArray' => $paramsArray, )); } else { //third step $delimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['thirdDelimiter'])))); $textDelimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['thirdTextDelimiter'])))); $table = CHtml::encode($_POST['thirdTable']); $uploadfile = CHtml::encode(trim($_POST['thirdFile'])); $columns = $_POST['Columns']; $perRequest = CHtml::encode($_POST['perRequest']); $tableKey = CHtml::encode($_POST['Tablekey']); $csvKey = CHtml::encode($_POST['CSVkey']); $mode = CHtml::encode($_POST['Mode']); $insertArray = array(); $error_array = array(); if (array_sum($_POST['Columns']) > 0) { if ($_POST['perRequest'] != '') { if (is_numeric($_POST['perRequest'])) { if (($mode == 2 || $mode == 3) && ($tableKey == '' || $csvKey == '')) { $error = 4; } else { $error = 0; //import from csv to db $model = new ImportCsv; $tableColumns = $model->tableColumns($table); //select old rows from table if ($mode == 2 || $mode == 3) { $oldItems = $model->selectRows($table, $tableKey); } $filecontent = file($uploadfile); $lengthFile = sizeof($filecontent); $insertCounter = 0; $stepsOk = 0; // begin transaction $transaction = Yii::app()->db->beginTransaction(); try { // import to database for ($i = 0; $i < $lengthFile; $i++) { if ($i != 0 && $filecontent[$i] != '') { $csvLine = ($textDelimiter) ? str_getcsv($filecontent[$i], $delimiter, $textDelimiter) : str_getcsv($filecontent[$i], $delimiter); //Mode 1. insert All if ($mode == 1) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) $arrays[] = $i; } } // Mode 2. Insert new if ($mode == 2) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) $arrays[] = $i; } } } // Mode 3. Insert new and replace old if ($mode == 3) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { // insert new $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) $arrays[] = $i; } } else { //replace old $import = $model->updateOld($table, $csvLine, $columns, $tableColumns, $csvLine[$csvKey - 1], $tableKey); if ($import != 1) $arrays[] = $i; } } } } if ($insertCounter != 0) $model->InsertAll($table, $insertArray, $columns, $tableColumns); // commit transaction if not exception $transaction->commit(); } catch (Exception $e) { // exception in transaction $transaction->rollBack(); } // save params in file $this->saveInFile($table, $delimiter, $mode, $perRequest, $csvKey, $tableKey, $tableColumns, $columns, $uploadfile, $textDelimiter); } } else { $error = 3; } } else { $error = 2; } } else { $error = 1; } //$this->layout = 'clear'; $this->render('thirdResult', array( 'error' => $error, 'delimiter' => $delimiter, 'textDelimiter' => $textDelimiter, 'table' => $table, 'uploadfile' => $uploadfile, 'error_array' => $error_array, )); } Yii::app()->end(); } else { // first loading $this->render('index', array( 'delimiter' => $delimiter, 'textDelimiter' => $textDelimiter, 'tablesArray' => $tablesArray, )); } }
public function actionIndex() { //var_dump($_POST['ExtColumns']); $delimiter = Yii::app()->controller->module->delimiter; $textDelimiter = Yii::app()->controller->module->textDelimiter; $table = Yii::app()->controller->module->table; $perRequest = Yii::app()->controller->module->perRequest; $mode = Yii::app()->controller->module->mode; $tableKey = Yii::app()->controller->module->tableKey; $csvKey = Yii::app()->controller->module->csvKey; //new configyarations $models = Yii::app()->controller->module->models; //publish css and js Yii::app()->clientScript->registerCssFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/styles.css')); Yii::app()->clientScript->registerScriptFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/ajaxupload.js')); Yii::app()->clientScript->registerScript('uploadActionPath', 'var uploadActionPath="' . $this->createUrl('default/upload') . '"', CClientScript::POS_BEGIN); Yii::app()->clientScript->registerScriptFile(Yii::app()->assetManager->publish(Yii::getPathOfAlias('application.modules.importcsv.assets') . '/download.js')); //getting models for import if (is_array($models) and count($models) > 0) { foreach ($models as $smodel => $params) { if (class_exists($smodel, true)) { $modelsArray[$smodel] = isset($params['label']) ? $params['label'] : $smodel; } } } else { foreach (glob('./protected/models/*.php') as $filename) { $modelname = str_replace(array("./protected/models/", ".php"), "", $filename); $modelsArray[$modelname] = $modelname; } } if (Yii::app()->request->isAjaxRequest) { if ($_POST['thirdStep'] != 1) { //second step $delimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['delimiter'])))); $textDelimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['textDelimiter'])))); //$table = CHtml::encode($_POST['table']); //getting table name from model name $selectedmodel = CHtml::encode($_POST['model']); $table = $selectedmodel::model()->tableSchema->name; if ($_POST['delimiter'] == '') { $error = 1; $csvFirstLine = array(); $paramsArray = array(); } else { // get all columns from csv file $error = 0; $uploadfile = $_POST['fileName']; $file = fopen($uploadfile, "r"); $csvFirstLine = $textDelimiter ? fgetcsv($file, 0, $delimiter, $textDelimiter) : fgetcsv($file, 0, $delimiter); fclose($file); // checking file with earlier imports $paramsArray = $this->checkOldFile($uploadfile); } //get all columns from selected table $model = new ImportCsv(); if (isset($models[$selectedmodel]['allowedColumns']) and $models[$selectedmodel]['allowedColumns'] != 'all') { $tableColumns = $models[$selectedmodel]['allowedColumns']; } else { $tableColumns = $model->tableColumns($table); } //picking required columns if (isset($models[$selectedmodel]['requiredColumns'])) { $requiredColumns = $models[$selectedmodel]['requiredColumns']; } else { $requiredColumns = ''; } $this->layout = 'clear'; $this->render('secondResult', array('error' => $error, 'tableColumns' => $tableColumns, 'requiredColumns' => $requiredColumns, 'delimiter' => $delimiter, 'textDelimiter' => $textDelimiter, 'table' => $table, 'fromCsv' => $csvFirstLine, 'paramsArray' => $paramsArray, 'selectedmodel' => $selectedmodel)); } else { //third step $delimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['thirdDelimiter'])))); $textDelimiter = str_replace('"', '"', str_replace("'", "'", CHtml::encode(trim($_POST['thirdTextDelimiter'])))); $table = CHtml::encode($_POST['thirdTable']); $uploadfile = CHtml::encode(trim($_POST['thirdFile'])); $columns = $_POST['Columns']; $perRequest = CHtml::encode($_POST['perRequest']); $tableKey = CHtml::encode($_POST['Tablekey']); $csvKey = CHtml::encode($_POST['CSVkey']); $mode = Yii::app()->controller->module->mode; $insertArray = array(); $error_array = array(); $selectedmodel = CHtml::encode($_POST['hmodel']); $allowedColumns = $models[$selectedmodel]['allowedColumns']; $requiredColumns = $models[$selectedmodel]['requiredColumns']; $uniqueColumns = $models[$selectedmodel]['uniqueColumns']; $dataTypes = $models[$selectedmodel]['dataTypes']; $external_entry = $models[$selectedmodel]['external']; $compare_entry = $models[$selectedmodel]['compare']; $total_rows = 0; $total_rows_inserted = 0; $exceptions = array(); $warnings = array(); if ($requiredColumns == NULL) { $requiredColumns = array(); } //required fields checking $allSelected = true; if ($requiredColumns == 'all') { foreach ($_POST['Columns'] as $index => $singleColumn) { if ($singleColumn == '') { $allSelected = false; } } } else { if (count($requiredColumns) > 0) { foreach ($_POST['Columns'] as $index => $singleColumn) { if (in_array($allowedColumns[$index], $requiredColumns) and $singleColumn == '') { $allSelected = false; } } } } //externals if any $externals = NULL; if ($external_entry) { $externals = isset($_POST['ExtColumns']) ? $_POST['ExtColumns'] : NULL; foreach ($external_entry as $field => $entry) { if (isset($entry['requiredColumns'])) { $ext_req_cols = $entry['requiredColumns']; foreach ($ext_req_cols as $col) { if (!isset($_POST['ExtColumns'][$field][$col]) or $_POST['ExtColumns'][$field][$col] == "") { $allSelected = false; } } } } } //compare fileds validation if any $compares = NULL; if ($compare_entry) { $compares = isset($_POST['CompColumns']) ? $_POST['CompColumns'] : NULL; foreach ($compare_entry as $field => $entry) { if (isset($entry['requiredColumns'])) { $cmp_req_cols = $entry['requiredColumns']; foreach ($cmp_req_cols as $col) { if (!isset($compares[$field][$col]) or $compares[$field][$col] == "") { $allSelected = false; } } } } } if ($allSelected == true) { if ($_POST['perRequest'] != '') { if (is_numeric($_POST['perRequest'])) { if (($mode == 2 || $mode == 3) && ($tableKey == '' || $csvKey == '')) { $error = 4; } else { $error = 0; //import from csv to db $model = new ImportCsv(); if (isset($models[$selectedmodel]['allowedColumns']) and $models[$selectedmodel]['allowedColumns'] != 'all') { $tableColumns = $models[$selectedmodel]['allowedColumns']; } else { $tableColumns = $model->tableColumns($table); } //select old rows from table if ($mode == 2 || $mode == 3) { $oldItems = $model->selectRows($table, $tableKey); } $filecontent = file($uploadfile); $lengthFile = sizeof($filecontent); $insertCounter = 0; $stepsOk = 0; // begin transaction $transaction = Yii::app()->db->beginTransaction(); try { // import to database for ($i = 0; $i < $lengthFile; $i++) { if ($i != 0 && $filecontent[$i] != '') { $csvLine = $textDelimiter ? str_getcsv($filecontent[$i], $delimiter, $textDelimiter) : str_getcsv($filecontent[$i], $delimiter); //Mode 1. insert All if ($mode == 1) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns, $selectedmodel, $allowedColumns, $dataTypes, $uniqueColumns, $externals, $compares); $insertCounter = 0; $insertArray = array(); if ($import['status'] != 1) { $arrays[] = $i; } $exceptions = $import['exceptions']; $warnings = $import['warnings']; $total_rows = $import['total_rows']; $total_rows_inserted = $import['total_rows_inserted']; } } // Mode 2. Insert new if ($mode == 2) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) { $arrays[] = $i; } } } } // Mode 3. Insert new and replace old if ($mode == 3) { if ($csvLine[$csvKey - 1] == '' || !$this->searchInOld($oldItems, $csvLine[$csvKey - 1], $tableKey)) { // insert new $insertArray[] = $csvLine; $insertCounter++; if ($insertCounter == $perRequest || $i == $lengthFile - 1) { $import = $model->InsertAll($table, $insertArray, $columns, $tableColumns); $insertCounter = 0; $insertArray = array(); if ($import != 1) { $arrays[] = $i; } } } else { //replace old $import = $model->updateOld($table, $csvLine, $columns, $tableColumns, $csvLine[$csvKey - 1], $tableKey); if ($import != 1) { $arrays[] = $i; } } } } } if ($insertCounter != 0) { $model->InsertAll($table, $insertArray, $columns, $tableColumns); } // commit transaction if not exception $transaction->commit(); } catch (Exception $e) { // exception in transaction $transaction->rollBack(); } // save params in file //$this->saveInFile($table, $delimiter, $mode, $perRequest, $csvKey, $tableKey, $tableColumns, $columns, $uploadfile, $textDelimiter); //remove file after uploading $this->removefile($uploadfile); } } else { $error = 3; } } else { $error = 2; } } else { $error = 1; } $this->layout = 'clear'; $this->render('thirdResult', array('error' => $error, 'delimiter' => $delimiter, 'textDelimiter' => $textDelimiter, 'table' => $table, 'uploadfile' => $uploadfile, 'error_array' => $error_array, 'exceptions' => $exceptions, 'warnings' => $warnings, 'total_rows' => $total_rows, 'total_rows_inserted' => $total_rows_inserted)); } Yii::app()->end(); } else { // first loading $this->render('index', array('delimiter' => $delimiter, 'table' => $table, 'textDelimiter' => $textDelimiter, 'modelsArray' => $modelsArray)); } }