/core_modules/importExport/backend/helpers/ImportExportHelper.php

<?php
/**
 * Created by PhpStorm.
 * User: art
 * Date: 03.05.2017
 * Time: 10:54
 */

namespace backend\modules\importExport\helpers;

use backend\modules\importExport\models\ImportExportMessage;
use metalguardian\fileProcessor\helpers\FPM;
use yii\base\Exception;
use yii\helpers\Inflector;
use yii\web\UploadedFile;

/**
 * Class DbimportHelper
 * @package backend\modules\importExport\helpers
 */
class ImportExportHelper
{
    const EXCEL_FORMAT_XML = 'Excel2003XML';
    const EXCEL_FORMAT_BIN = null;

    /**
     * Upload file and get path
     * @param $model
     * @param $attribute
     * @return bool|string
     */
    public static function uploadAndGetPath($model, $attribute)
    {
        if (count(UploadedFile::getInstances($model, $attribute)) == 0) {
            return false;
        }
        $file = UploadedFile::getInstances($model, $attribute)[0];
        $fileNameArray = explode('.', $file->name);
        $file->name = Inflector::slug(Inflector::transliterate($fileNameArray[0])) . '.' . $fileNameArray[1];
        $file->name = str_replace('-', '_', $file->name);
        $fileId = FPM::transfer()->saveUploadedFile($file);
        return \Yii::getAlias('@webroot') . FPM::getOriginalDirectoryUrl($fileId) . $fileId . '-' . $file->name;
    }

    /**
     * Get excel format
     * @param $filePath
     * @return string
     */
    public static function getExcelFormat($filePath)
    {
        //check is xml format
        $signature = [
            '<?xml version="1.0"'
        ];

        $isXml = self::validSignature($filePath, $signature);

        if ($isXml) {
            //check is valid xml
            $xmlValid = simplexml_load_file($filePath);

            //fix it if not valid
            if (!$xmlValid) {
                $data = file_get_contents($filePath);
                $data = self::stripInvalidXml($data);
                file_put_contents($filePath, $data);
            }

            //check if Excel2003XML for PHPExcel
            $signature = [
                '<?mso-application progid="Excel.Sheet"?>'
            ];
            $isExcelXml = self::validSignature($filePath, $signature);

            //if wrong format for PHPExcel - add signature tag
            if (!$isExcelXml) {
                $data = file_get_contents($filePath);
                $data = preg_replace('/' . preg_quote('?>', '/') . '/', '?>
<?mso-application progid="Excel.Sheet"?>', $data, 1);

                file_put_contents($filePath, $data);
            }
            return self::EXCEL_FORMAT_XML;
        } else {
            return self::EXCEL_FORMAT_BIN;
        }
    }

    /**
     * fix excel data in preview, sometimes get something like this:
     * 69.6 (in excel cell) show as 69.59999999999999 (in preview) - don't know why
     * @param $dataArray
     * @return string[]
     */
    public static function fixExcelPreview($dataArray)
    {
        for ($i = 0; $i < count($dataArray); $i++) {
            foreach ($dataArray[$i] as $label => $value) {
                if (is_numeric($value)) {
                    $numArray = explode('.', $value);
                    if (isset($numArray[1]) && (strlen($numArray[1] > 2))) {
                        $dataArray[$i][$label] = number_format($value, 2, '.', '');
                    }
                }
            }
        }

        return $dataArray;
    }

    /**
     * format string for Excel preview
     * @param $columnValue
     * @return string
     */
    public static function formatForPreview($columnValue)
    {
        $cleanValue = strip_tags($columnValue);

        if (mb_strlen($cleanValue) > 50) {
            $cleanValue = mb_substr($cleanValue, 0, 50);
            $cleanValue .= '...';
        }

        return $cleanValue;
    }


    /**
     * check file signature
     * @param $filePath
     * @param $signature
     * @return bool
     */
    private static function validSignature($filePath, $signature)
    {
        // Open file
        $fileHandle = self::openFile($filePath, 'r');

        // Read sample data (first 2 KB will do)
        $data = fread($fileHandle, 2048);

        fclose($fileHandle);

        $valid = true;
        foreach ($signature as $match) {
            // every part of the signature must be present
            if (strpos($data, $match) === false) {
                $valid = false;
                break;
            }
        }
        return $valid;
    }

    /**
     * open file
     * @param $filePath
     * @param $type
     * @return mixed
     * @throws Exception
     */
    private static function openFile($filePath, $type)
    {
        // Check if file exists
        if (!file_exists($filePath) || !is_readable($filePath)) {
            throw new Exception('Could not open ' . $filePath . ' for reading! File does not exist.');
        }

        // Open file
        $fileHandle = fopen($filePath, $type);
        if ($fileHandle === false) {
            throw new Exception('Could not open file ' . $filePath . ' for reading.');
        }

        return $fileHandle;
    }

    /**
     * fix invalid XML
     * @param $value
     * @return string
     */
    private static function stripInvalidXml($value)
    {
        $ret = '';
        $current = null;
        if (empty($value)) {
            return $ret;
        }

        $length = strlen($value);
        for ($i = 0; $i < $length; $i++) {
            $current = ord($value{$i});
            if (($current == 0x9) ||
                ($current == 0xA) ||
                ($current == 0xD) ||
                (($current >= 0x20) && ($current <= 0xD7FF)) ||
                (($current >= 0xE000) && ($current <= 0xFFFD)) ||
                (($current >= 0x10000) && ($current <= 0x10FFFF))
            ) {
                $ret .= chr($current);
            } else {
                $ret .= ' ';
            }
        }
        return $ret;
    }

    /**
     * add message with data import result
     * @param $type
     * @param $handler
     * @param $message
     * @param int $isShow
     */
    public static function addImportExportMessage($type, $handler, $message, $isShow = 1)
    {
        $newMessage = new ImportExportMessage();
        $newMessage->type = $type;
        $newMessage->handler = $handler;
        $newMessage->message = $message;
        $newMessage->is_show = $isShow;
        $newMessage->is_shown = 0;
        $newMessage->save(false);
    }

    /**
     * add message with data import result
     * @param $type
     * @param $handler
     * @param $messageGlobal
     * @param $massageLog
     */
    public static function addDoubleImportExportMessage($type, $handler, $messageGlobal, $massageLog)
    {
        //add global message
        $newMessage = new ImportExportMessage();
        $newMessage->type = $type;
        $newMessage->handler = $handler;
        $newMessage->message = $messageGlobal;
        $newMessage->is_show = 1;
        $newMessage->is_shown = 0;
        $newMessage->save(false);

        //add log message
        $newMessage = new ImportExportMessage();
        $newMessage->type = $type;
        $newMessage->handler = $handler;
        $newMessage->message = $massageLog;
        $newMessage->is_show = 0;
        $newMessage->is_shown = 0;
        $newMessage->save(false);
    }


    /**
     * @param $massage
     */
    public static function addExportErrorMessage($massage)
    {
        $messageGlobal = \Yii::t('back/import-export', 'Export error. View log for details');
        self::addDoubleImportExportMessage(
            ImportExportMessage::MESSAGE_TYPE_ERROR,
            ImportExportMessage::MESSAGE_HANDLER_EXPORT,
            $messageGlobal,
            $massage
        );
    }
}