PHP Classes

File: Command/TranslationsUpdateFromExcelCommand.php

Recommend this page to a friend!
  Classes of Joseluis Laso  >  Tradukoj.com Translations API Bundle  >  Command/TranslationsUpdateFromExcelCommand.php  >  Download  
File: Command/TranslationsUpdateFromExcelCommand.php
Role: Class source
Content type: text/plain
Description: Class source
Class: Tradukoj.com Translations API Bundle
Get remote translations for Symfony projects
Author: By
Last change:
Date: 8 years ago
Size: 9,866 bytes
 

Contents

Class file image Download
<?php

/**
 * @author Joseluis Laso <jlaso@joseluislaso.es>
 */

namespace JLaso\TranslationsApiBundle\Command;

use
Doctrine\Common\Collections\ArrayCollection;
use
Doctrine\DBAL\Query\QueryBuilder;
use
Doctrine\ODM\MongoDB\DocumentManager;
use
Doctrine\ORM\EntityManager;
use
JLaso\TranslationsApiBundle\Service\ClientSocketService;
use
JLaso\TranslationsBundle\Document\Repository\TranslationRepository;
use
JLaso\TranslationsBundle\Document\Translation;
use
JLaso\TranslationsBundle\Entity\Project;
use
JLaso\TranslationsBundle\Entity\Repository\ProjectRepository;
use
JLaso\TranslationsBundle\Entity\User;
use
JLaso\TranslationsBundle\Service\MailerService;
use
Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use
Symfony\Component\Console\Command\Command;
use
Symfony\Component\Console\Helper\DialogHelper;
use
Symfony\Component\Console\Helper\HelperSet;
use
Symfony\Component\Console\Helper\TableHelper;
use
Symfony\Component\Console\Input\InputArgument;
use
Symfony\Component\Console\Input\InputInterface;
use
Symfony\Component\Console\Input\InputOption;
use
Symfony\Component\Console\Output\OutputInterface;
use
Symfony\Component\DependencyInjection\Container;

class
TranslationsUpdateFromExcelCommand extends ContainerAwareCommand
{

   
/** @var string */
   
protected $name;
   
/** @var string */
   
protected $description;
   
/** @var ClientSocketService */
   
private $clientApiService;

    protected function
configure()
    {
       
$this->name = 'jlaso:translations:update-from-excel';
       
$this->description = 'Update translations from an Excel document';
       
$this
           
->setName($this->name)
            ->
setDescription($this->description)
            ->
addArgument('excel', InputArgument::REQUIRED, 'excel doc')
            ->
addArgument('language', InputArgument::REQUIRED, 'language')
            ->
addOption('port', null, InputArgument::OPTIONAL, 'port')
            ->
addOption('address', null, InputArgument::OPTIONAL, 'address')
            ->
addOption('approved', null, InputArgument::OPTIONAL, 'approved')
        ;
    }

    protected function
init($server = null, $port = null)
    {
       
/** @var ClientSocketService $clientApiService */
       
$clientApiService = $this->getContainer()->get('jlaso_translations.client.socket');
       
$this->clientApiService = $clientApiService;
       
$this->clientApiService->init($server, $port);
    }

   
/**
     * @param $keys
     * @param $needle
     * @param $reference
     *
     * @return mixed
     */
   
protected function substitute($keys, $needle, $reference)
    {

        foreach(
$keys as $srch=>$replc){

           
//$srch = str_replace(array("(",")","[","]"), array('\(','\)','\[','\]'));
           
if(preg_match("/\((?<idx>\d+)\)/", $srch, $match)){
               
$idx = $match['idx'];
               
$regr = "/\({$idx}\)(?<val>.*?)\({$idx}\)/";
                if(
preg_match($regr, $reference, $match)){
                   
$replc = "%".$match['val']."%";
                }else{
                   
//$regr = "/\({$idx}\)(.*?)\({$idx}\)/";
                   
$replc = "%$1%";
                };
               
$regr = "/\(\s?{$idx}\s?\)(.*?)\(\s?{$idx}\s?\)/";
            }else{
                if(
preg_match("/\[(?<idx>\d+)\]/", $srch, $match)){
                   
$idx = $match['idx'];
                   
$regr = "/\[\s?{$idx}\s?\]/"; //print "\n\t$idx\t$regr\t$replc\n";
               
}else{
                    die(
"error in substitute $srch=>$replc");
                }
            }
           
$needle = preg_replace($regr, $replc, $needle);
        }

        return
$needle;
    }

    protected function
getCellValue(\PHPExcel_Worksheet $sheet, $coord)
    {
       
$cell = $sheet->getCell($coord);
        if(
$cell){
            return
$cell->getValue();
        }
    }

   
/**
     * 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');

       
/** @var \PHPExcel $excel */
       
$excel = $phpExcel->createPHPExcelObject($file);

       
$keySheet = $excel->getSheetByName('key');
       
$key = array(); //array_flip(json_decode($keySheet->getCell('A1'), true));
       
foreach($keySheet->getRowIterator() as $row){

           
$rowNum = $row->getRowIndex();
           
$cellIterator = $row->getCellIterator();
           
$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set

           
foreach ($cellIterator as $cell) {
               
/** @var \PHPExcel_Cell $cell */
               
$cellValue = $cell->getCalculatedValue();
                switch(
$cell->getColumn()){
                    case(
"A"):
                       
$index = "[$rowNum]";
                        break;
                    case(
"B"):
                       
$index = "($rowNum)";
                        break;
                };
                if (!
is_null($cellValue)) {
                   
$key[$index] = $cellValue;
                }
            }
        }

       
// get the worksheet that match its title with language
       
$worksheet = $excel->getSheetByName($language);

       
$output->writeln("\n<comment>Worksheet - " . $worksheet->getTitle() . "</comment>");
       
$localData = array();

        foreach (
$worksheet->getRowIterator() as $row) {
           
/** @var \PHPExcel_Worksheet_Row $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);
           
//$output->writeln(sprintf("<comment>$index</comment>\t<info>%s</info> => %s => <comment>%s</comment>", $keyName, $reference, $substituted));
           
$localData[$keyName] = $substituted;
        }

       
// download translations from server
       
$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);
           
//var_dump($result); die;
           
file_put_contents('/tmp/' . $catalog . '.json', json_encode($result));
           
$bundles = $result['bundles'];
           
//var_dump($result['data']['Bad credentials']); die;

           
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]));
                       
//$output->writeln(sprintf("\t|-- key %s:%s/%s ... ", $catalog, $key, $locale));
                       
echo '.';
                       
//$fileName = isset($messageData['fileName']) ? $messageData['fileName'] : '';
                   
}

                }
            }
        }

       
//print_r($tempData);

       
$output->writeln("\nAnalysing the result of the match process...\n");
       
$count = 0;
       
// data to send to translations server
       
$data = array();
       
// this date guarantees that the data sent to server forces to update key
       
$date = date('c');

       
// get the key that are repeated
       
foreach($tempData as $key=>$restData){
            if(
count($restData) > 1){
               
$output->writeln("\tthe key $key is in more that one catalog");
            }
            foreach(
$restData as $catalog=>$messageData){

                if(!empty(
$messageData['new']) && ($messageData['message'] != $messageData['new'])){

                   
//var_dump($messageData); die;
                   
$data[$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 have the file to process\n");

        if(
$count){
           
//ld($data);
           
$output->writeln('uploadKeys("' . $catalog . '", $data)');
           
$result = $this->clientApiService->uploadKeys($catalog, $data);
           
//var_dump($result);
       
}

       
$output->writeln("\n done!");
    }



}