ElixirVault/www/system-brs-core/sys/sysTranslationsPub/inc/sysTranslationsPubManager.php

<?php

namespace BRSystems\Core\Sys\sysTranslationsPub\inc;

use BRSystems\Core\Library\cExcel\cExcel;
use BRSystems\Core\Library\cExcel\chunkReadFilter;
use BRSystems\Core\Library\cJSON\cJSON;
use BRSystems\Core\Sys\sysLanguages\inc\sysLanguagesModule;

/**
 * pubDatabase data module
 *
 * @category CMS Classificators Manager
 * @author Mareks Keišs <mareks.keiss@brsystems.lv>
 * @since Version 1.0
 */
class sysTranslationsPubManager
{
    public $dir;
    public $name;
    public $module;

    private $core;
    private $colKeywords = array(
        'ID' => 'id',
        'MESSAGE' => 'msg_message',
    );

    private $rowInsertLimit = 100;

    public function __construct(&$coreClass)
    {
        $this->core   = $coreClass;
        $this->module = new sysTranslationsPubModule();
    }

    /**
     * Function to import file data - called directly from file upload
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category CMS Classificator Manager
     * @param array $file - imported file
     * @return array of data
     */
    public function importDocDataFromFile($file){

        $stats = array(
            'n' => 0,
            'u' => 0,
        );

        $sFile = __MDIR__ . substr($file['path'], 1) . $file['file'];
        if(file_exists($sFile)){
            //---------------------------------------------------------------------------
            // NEW LARGE FILE IMPORT +++
            //---------------------------------------------------------------------------
            $trimSymbols = " \t\n\r\0\x0B\xC2\xA0";

            // Increase time & memory limit
            if (!isset($GLOBALS['coverage_tests_running'])) {
                set_time_limit(60*60*3);
                ini_set('memory_limit', '128M');
            }

            // Create PHP Excel instances
            $oExcel = new cExcel();
            $oReader = $oExcel->createReader($sFile);

            $chunkFilter = new chunkReadFilter();
            $oReader->setReadDataOnly(true);
            $oReader->setReadFilter($chunkFilter);


            //---------------------------------------------------------------------------
            // LOAD HEADERS
            //---------------------------------------------------------------------------
            $colsToSelect = array();
            $colsToSelectTrans = array();
            $cRow = 3;  // Data located
            $FIELDS = $this->colKeywords;

            // set languages to field list
            $lng = new sysLanguagesModule();
            $langs = $lng->getLanguages(true, true);
            foreach($langs as $val)
                $langCode[strtoupper($val['lang_code'])] = $val['id'];
            unset($langs);

            $chunkFilter->setRows(1, 2);
            $oWorkbook = $oReader->load($sFile);
            $oSheet = $oWorkbook->getActiveSheet();

            // Associate each column with data column - check first 2x rows for ids
            $tCols = $oSheet->getHighestDataColumn();
            $array = $oSheet->rangeToArray("A1:{$tCols}2");

            foreach ($array as $r=>$row) {
                foreach ($row as $c=>$cell) {
                    $sUpperKey = strtoupper($cell);
                    if(isset($FIELDS[$sUpperKey])){
                        $colsToSelect[$FIELDS[$sUpperKey]] = $c;
                    }elseif(isset($langCode[$sUpperKey])){
                        $colsToSelectTrans[$langCode[$sUpperKey]] = $c;
                    }
                }
            }

            $i = 0;
            $chunkSize = 150;
            $emptyDefault = $emptyRows = 10;

            // Get next available msg ID
            $nextHighestId = $this->module->getMaxId();

            while($emptyRows > 0){

                $chunkFilter->setRows($cRow, $chunkSize);
                $oWorkbook = $oReader->load($sFile);
                $oSheet = $oWorkbook->getActiveSheet();


                $array = $oSheet->rangeToArray("A{$cRow}:{$tCols}" . ($cRow+$chunkSize));

                //---------------------------------------------------------------------------
                // ANALYSE AND CREATE INSERT
                //---------------------------------------------------------------------------
                $insert = array();
                $insertDelete = array();

                $insertTrans = array();
                $insertNew = array();

                if(!!$array && is_array($array)){

                    foreach($array as $row){

                        // Remove special chars
                        foreach($row as &$v) {
                            $v = trim($v, $trimSymbols);

                            // Spec char replace fix
                            // Excel import copyright fix Ⓒ ascii -> 169
                            $v = str_replace(array(chr(169)), array('&copy;'), $v);
                        }
                        unset($v);

                        // Generate inserts for messages
                        if(!empty($row[$colsToSelect[$this->colKeywords['MESSAGE']]])){
                            $msg = $row[$colsToSelect[$this->colKeywords['MESSAGE']]];
                            //echo "{$msg} \n";

                            /*if(0 && !empty($row[$colsToSelect[$this->colKeywords['ID']]])){
                                $msgId = $row[$colsToSelect[$this->colKeywords['ID']]];
                                $insert[] = array(
                                    'id' => $msgId,
                                    'msg_message' => $msg
                                );
                                $insertDelete[] = $msgId;

                                foreach($colsToSelectTrans as $kl=>$vl){
                                    $insertTrans[] = array(
                                        'msg_id' => $msgId,
                                        'msg_lang_id' => $kl,
                                        'msg_translation' => $row[$vl]
                                    );
                                }

                            }else{*/

                               // echo "{$i} - {$msg} \n";
                               // $i++;

                                // Insert new
                            //pr($msg);
                                $insertNew[$msg] = array(
                                    'id' => ++$nextHighestId,
                                    'msg_message' => $msg
                                );

                                foreach($colsToSelectTrans as $kl=>$vl){

                                    $insertTrans[] = array(
                                        'msg_id' => &$insertNew[$msg]['id'],
                                        'msg_lang_id' => $kl,
                                        'msg_translation' => $row[$vl]
                                    );
                                }
                            /*}*/
                            $emptyRows = $emptyDefault;
                        }else{
                            --$emptyRows;
                        }
                    }
                }

                // Add count for new msg
                $stats['n'] += count($insertNew);

                // Get all ids for existing
                $existingIds = $this->module->getMsgIdsByMsg(array_keys($insertNew));
                if(!!$existingIds){
                    foreach($existingIds as $v){
                        if(isset($insertNew[$v['msg_message']])){
                            $insertNew[$v['msg_message']]['id'] = $v['id'];
                            $insertDelete[] = $v['id'];

                            $stats['n']--;
                            $stats['u']++;
                        }
                    }
                }

                if(!!$insertNew){
                    $this->module->saveTableImportData('site_pub_msg', $insertNew, array('id' => $insertDelete));
                    $this->module->saveTableImportData('site_pub_msg_translations', $insertTrans, array('msg_id' => $insertDelete));
                    $this->module->truncateTable('temp_pub_msg_links');
                }

                $cRow += $chunkSize;

                $oWorkbook->disconnectWorksheets();
                unset($array, $oSheet, $oWorkbook, $insert);
            }

            // Clear APC translations
            $this->core->oApc->setExpiredStartingWith(array('msg_sm'));

            cJSON::sendOk(array('msg' => str_replace(array('%NCOUNT%', '%UCOUNT%'), array($stats['n'], $stats['u']), "File import successful! New %NCOUNT% & updated %UCOUNT%")));
        }
        

        cJSON::sendError(array('msg' => 'An error occurred while importing file data!'));
    }
}