Command/TranslationsUpdateFromExcelCommand.php
/**
     * FORMAT for the excel document
     * =============================
     *
     * one worksheet named as the language you want to import
     * one workseeht named "key" with the following format
     *   rowX colA ColB
     *     1   [1]  (1)   => (1) var substitution, [1] style substitution
     *
     * the reason for this "key system" is that normally translators haven't to translate the html labels and variables and this is a way to assure this
     */
FORMAT for the excel document

one worksheet named as the language you want to import
one workseeht named "key" with the following format
rowX colA ColB
1   [1]  (1)   => (1) var substitution, [1] style substitution

the reason for this "key system" is that normally translators haven't to translate the html labels and variables and this is a way to assure this
FORMAT for the excel document one worksheet named as the language you want to import one workseeht named " key " with the following format rowX colA ColB 1 [ 1 ] ( 1 ) = > ( 1 ) var substitution [ 1 ] style substitution the reason for this " key system " is that normally translators haven ' t to translate the html labels and variables and this is a way to assure this
public function execute(InputInterface $input, OutputInterface $output)
    {
        $container = $this->getContainer();
        $file      = $input->getArgument('excel');
        $language  = $input->getArgument('language');
        $approved  = (boolean)$input->getOption('approved');
        $this->init($input->getOption('address'), $input->getOption('port'));
        $phpExcel  = $container->get('phpexcel');
        $excel     = $phpExcel->createPHPExcelObject($file);
        $keySheet = $excel->getSheetByName('key');
        $key = array(); 
        foreach($keySheet->getRowIterator() as $row){
            $rowNum = $row->getRowIndex();
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); 
            foreach ($cellIterator as $cell) {
                $cellValue = $cell->getCalculatedValue();
                switch($cell->getColumn()){
                    case("A"):
                        $index = "[$rowNum]";
                        break;
                    case("B"):
                        $index = "($rowNum)";
                        break;
                };
                if (!is_null($cellValue)) {
                    $key[$index] = $cellValue;
                }
            }
        }
        $worksheet = $excel->getSheetByName($language);
        $output->writeln("\n<comment>Worksheet - " . $worksheet->getTitle() . "</comment>");
        $localData = array();
        foreach ($worksheet->getRowIterator() as $row) {
            $index       = $row->getRowIndex();
            $rowNum      = $row->getRowIndex();
            $keyName     = $this->getCellValue($worksheet, "A{$rowNum}");
            $reference   = $this->getCellValue($worksheet, "B{$rowNum}");
            $message     = $this->getCellValue($worksheet, "C{$rowNum}");
            $substituted = $this->substitute($key, $message, $reference);
            $localData[$keyName] = $substituted;
        }
        $result = $this->clientApiService->getCatalogIndex();
        if($result['result']){
            $catalogs = $result['catalogs'];
        }else{
            die('error getting catalogs');
        }
        $tempData = array();
        foreach($catalogs as $catalog){
            $output->writeln(PHP_EOL . sprintf('<info>Processing "%s" catalog ...</info>', $catalog));
            $result = $this->clientApiService->downloadKeys($catalog);
            file_put_contents('/tmp/' . $catalog . '.json', json_encode($result));
            $bundles = $result['bundles'];
            foreach($result['data'] as $key=>$data){
                foreach($data as $locale=>$messageData){
                    if(($locale == $language) && isset($localData[$key])){
                        $tempData[$key][$catalog] = array_merge($messageData, array('new' => $localData[$key]));
                        echo '.';
                    }
                }
            }
        }
        $output->writeln("\nAnalysing the result of the match process...\n");
        $count = 0;
        $data = array();
        $date = date('c');
        $output->writeln("found ".count($localData)." keys in excel\n");
        foreach($tempData as $key=>$restData){
            if(count($restData) > 1){
                $output->writeln(sprintf("\tthe key %s is in more that one catalog", $key));
            }
            foreach($restData as $catalog=>$messageData){
                if(!empty($messageData['new']) && ($messageData['message'] != $messageData['new'])){
                    $data[$catalog][$key][$language] = array(
                        'approved'  => $approved,
                        'message'   => $messageData['new'],
                        'updatedAt' => $date,
                        'fileName'  => isset($messageData['fileName']) ? $messageData['fileName'] : "",
                        'bundle'    => isset($bundles[$key]) ? $bundles[$key] : "",
                    );
                    $output->writeln("the key $key needs to be updated");
                    $count++;
                }
            }
        }
        $total = count($localData);
        $output->writeln("\nfound $count keys that need to be updated from a total of $total keys that had the file to be processed\n");
        if($count){
            foreach($data as $catalog=>$restData) {
                $result = $this->clientApiService->uploadKeys($catalog, $restData);
            }
        }
        $output->writeln("\n done!");
    }
public function execute ( InputInterface $ input , OutputInterface $ output ) { $ container = $ this -> getContainer ( ) ; $ file = $ input -> getArgument ( 'excel' ) ; $ language = $ input -> getArgument ( 'language' ) ; $ approved = ( boolean ) $ input -> getOption ( 'approved' ) ; $ this -> init ( $ input -> getOption ( 'address' ) , $ input -> getOption ( 'port' ) ) ; $ phpExcel = $ container -> get ( 'phpexcel' ) ; $ excel = $ phpExcel -> createPHPExcelObject ( $ file ) ; $ keySheet = $ excel -> getSheetByName ( 'key' ) ; $ key = array ( ) ; foreach ( $ keySheet -> getRowIterator ( ) as $ row ) { $ rowNum = $ row -> getRowIndex ( ) ; $ cellIterator = $ row -> getCellIterator ( ) ; $ cellIterator -> setIterateOnlyExistingCells ( false ) ; foreach ( $ cellIterator as $ cell ) { $ cellValue = $ cell -> getCalculatedValue ( ) ; switch ( $ cell -> getColumn ( ) ) { case ( " A " ) : $ index = " [ $ rowNum ] " ; break ; case ( " B " ) : $ index = " ( $ rowNum ) " ; break ; } ; if ( ! is_null ( $ cellValue ) ) { $ key [ $ index ] = $ cellValue ; } } } $ worksheet = $ excel -> getSheetByName ( $ language ) ; $ output -> writeln ( " \n <comment>Worksheet -  " . $ worksheet -> getTitle ( ) . " </comment> " ) ; $ localData = array ( ) ; foreach ( $ worksheet -> getRowIterator ( ) as $ row ) { $ index = $ row -> getRowIndex ( ) ; $ rowNum = $ row -> getRowIndex ( ) ; $ keyName = $ this -> getCellValue ( $ worksheet , " A { $ rowNum } " ) ; $ reference = $ this -> getCellValue ( $ worksheet , " B { $ rowNum } " ) ; $ message = $ this -> getCellValue ( $ worksheet , " C { $ rowNum } " ) ; $ substituted = $ this -> substitute ( $ key , $ message , $ reference ) ; $ localData [ $ keyName ] = $ substituted ; } $ result = $ this -> clientApiService -> getCatalogIndex ( ) ; if ( $ result [ 'result' ] ) { $ catalogs = $ result [ 'catalogs' ] ; } else { die ( 'error getting catalogs' ) ; } $ tempData = array ( ) ; foreach ( $ catalogs as $ catalog ) { $ output -> writeln ( PHP_EOL . sprintf ( '<info>Processing "%s" catalog ...</info>' , $ catalog ) ) ; $ result = $ this -> clientApiService -> downloadKeys ( $ catalog ) ; file_put_contents ( '/tmp/' . $ catalog . '.json' , json_encode ( $ result ) ) ; $ bundles = $ result [ 'bundles' ] ; foreach ( $ result [ 'data' ] as $ key => $ data ) { foreach ( $ data as $ locale => $ messageData ) { if ( ( $ locale == $ language ) && isset ( $ localData [ $ key ] ) ) { $ tempData [ $ key ] [ $ catalog ] = array_merge ( $ messageData , array ( 'new' => $ localData [ $ key ] ) ) ; echo '.' ; } } } } $ output -> writeln ( " \n Analysing the result of the match process... \n " ) ; $ count = 0 ; $ data = array ( ) ; $ date = date ( 'c' ) ; $ output -> writeln ( " found  " . count ( $ localData ) . "  keys in excel \n " ) ; foreach ( $ tempData as $ key => $ restData ) { if ( count ( $ restData ) > 1 ) { $ output -> writeln ( sprintf ( " \t the key %s is in more that one catalog " , $ key ) ) ; } foreach ( $ restData as $ catalog => $ messageData ) { if ( ! empty ( $ messageData [ 'new' ] ) && ( $ messageData [ 'message' ] != $ messageData [ 'new' ] ) ) { $ data [ $ catalog ] [ $ key ] [ $ language ] = array ( 'approved' => $ approved , 'message' => $ messageData [ 'new' ] , 'updatedAt' => $ date , 'fileName' => isset ( $ messageData [ 'fileName' ] ) ? $ messageData [ 'fileName' ] : " " , 'bundle' => isset ( $ bundles [ $ key ] ) ? $ bundles [ $ key ] : " " , ) ; $ output -> writeln ( " the key  $ key  needs to be updated " ) ; $ count ++ ; } } } $ total = count ( $ localData ) ; $ output -> writeln ( " \n found  $ count  keys that need to be updated from a total of  $ total  keys that had the file to be processed \n " ) ; if ( $ count ) { foreach ( $ data as $ catalog => $ restData ) { $ result = $ this -> clientApiService -> uploadKeys ( $ catalog , $ restData ) ; } } $ output -> writeln ( " \n  done! " ) ; }
FORMAT for the excel document
FORMAT for the excel document
/** @var \PHPExcel $excel */ //array_flip(json_decode($keySheet->getCell('A1'), true)); // Loop all cells, even if it is not set /** @var \PHPExcel_Cell $cell */ // get the worksheet that match its title with language /** @var \PHPExcel_Worksheet_Row $row */ //$output->writeln(sprintf("<comment>$index</comment>\t<info>%s</info> => %s => <comment>%s</comment>", $keyName, $reference, $substituted)); // download translations from server //$output->writeln(sprintf("\t|-- key %s:%s/%s ... ", $catalog, $key, $locale)); //$fileName = isset($messageData['fileName']) ? $messageData['fileName'] : ''; // data to send to translations server // this date guarantees that the data sent to server forces to update key // get the key that are repeated //var_dump($messageData); die; //var_dump($data); die; //ld($data); //$output->writeln('uploadKeys("' . $catalog . '", $data)'); //var_dump($result);