westdc-zf1/application/module/Westdc/Dataimport/Processing/Csv.php

<?php
/**
 * Created by PhpStorm.
 * User: liujin834
 * Date: 15-1-27
 * Time: 下午3:12
 */
namespace Westdc\Dataimport\Processing;

use Westdc\Dataimport\ProcessingInterface;
use Westdc\Dataimport\AbstractProcessing;
use Goodby\CSV as CSVOBJ;
use Helpers\PDO;

class Csv extends AbstractProcessing implements ProcessingInterface{

    private $source_file;
    private $csv_object;
    private $csv_line_data;

    const ERROR_INDEX_NOT_MATCHED = "值的个数与其它行不匹配";
    const ERROR_INDEX_TYPE_DIFFERENT = "值的类型与上一行不同";
    const ERROR_EMPTY_FILE = "文件是空的";
    const ERROR_LINE_TITLE = "行:";
    const ERROR_COLUMN_TITLE = "列:";
    const ERROR_DATA_TYPE_NOT_MATCHED = "数据与数据库中的类型不匹配，不能进行导入";

    /**
     * 初始化
     * @param $file
     */
    public function init($file)
    {
        $this->source_file = $file;

        $interpreter = new CSVOBJ\Import\Standard\Interpreter;
        $interpreter->addObserver(function($columns){
            foreach($columns as $k=>$v){
                if(is_numeric($v))
                    $columns[$k] = $v+0;
            }
            $this->csv_line_data[] = $columns;
        });
        $lexer = new CSVOBJ\Import\Standard\Lexer(new CSVOBJ\Import\Standard\LexerConfig);
        $lexer->parse($file,$interpreter);

        $this->csv_object = $lexer;
    }

    /**
     * 獲取總行數
     * @return int
     */
    public function getLineCount(){
        return count($this->csv_line_data);
    }//getLineCount()

    /**
     * 獲取文件大小
     * @return int
     */
    public function getSize(){

        $size = filesize($this->source_file);

        $type = array('Bytes','KB','MB','GB','TB');
        for($i = 0; $size >= 1024; $i++)//单位每增大1024，则单位数组向后移动一位表示相应的单位
        {
            $size/=1024;
        }
        return (floor($size*100)/100).$type[$i];//floor是取整函数，为了防止出现一串的小数，这里取了两位小数
    }//getSize(){

    /**
     * 獲取文件類型
     * @return mixed
     */
    public function getType(){
        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $filetype =  finfo_file($finfo, $this->source_file) ; //文件mime类型
        finfo_close($finfo);
        return $filetype;
    }//getType()

    /**
     * 獲取內容
     * @return mixed
     */
    public function getLines()
    {
        return $this->csv_line_data;
    }//getLines()

    /**
     * @return mixed
     */
    public function getHtmlTable()
    {
        return $this->csv_object->toHTML('table-csv-data with-header');
    }//getTable()

    /**
     * 獲取CSV對象
     * @return mixed
     */
    public function getCsvObject()
    {
        return $this->csv_object;
    }

    /**
    * 面向数据库字段的类型判断
    * @return bool
    */
    private function typeCompare($var1,$var2)
    {
        $t1=gettype($var1);
        $t2=gettype($var2);
        if ($t1==$t2) return true;
        if (($t1=='integer' || $t1=='double') && ($t2=='integer' || $t2=='double'))
            return true;
        return false;
    }    
    /**
    * 檢查文件內容是否規則
    * @return array|bool
    */
    public function checkRegularity()
    {
        $lines = $this->getLines();

        $error_stack = [];

        if(!is_array($lines) || count($lines) < 1)
            return [self::ERROR_EMPTY_FILE];

        foreach($lines as $k=>$v){
            if($k < 1 || empty($v))
                continue;

            if(count($v) != count($lines[$k-1]))
            {
                $error_stack[] = self::ERROR_LINE_TITLE . ($k + 1) .":".self::ERROR_INDEX_NOT_MATCHED;
                continue;
            }

            foreach($v as $vIndex => $item){
                if(!$this->typeCompare($item,$lines[$k-1][$vIndex]))
                {
                    $error_stack[] = self::ERROR_LINE_TITLE . ($k + 1) .":".self::ERROR_INDEX_TYPE_DIFFERENT;
                }
            }
        }

        if(count($error_stack) < 1)
            return true;
        else
            return $error_stack;

    }//checkRegularity()


    /**
     * 检查字段是否对应
     * @param $fields
     * @return array|bool
     */
    public function checkTableField(array $fields)
    {
        $error_stack = [];

        $first_line = $this->getLines()[0];

        $index=0;
        foreach($fields as $field)
        {
            if (!$this->checkFieldsDataType($field['data_type'],$first_line[$index]))
            {
                $error_stack[] = self::ERROR_COLUMN_TITLE . $index .":" . self::ERROR_DATA_TYPE_NOT_MATCHED;
            }
            $index++;
        }

        if(count($error_stack) < 1)
            return true;
        else
            return $error_stack;

    }


    public function import($table,$withid=fasle)
    {
        $config = \Zend_Registry::get('config');
        $db = new PDO([
            'host' => $config->visual_db->hostname,
            'port' => $config->visual_db->port,
            'db' => $config->visual_db->database,
            'user' => $config->visual_db->username,
            'pwd' => $config->visual_db->password
        ]);
        $db->setAttribute(\PDO::ATTR_PERSISTENT,true);

        $error_stack = [];
        try{
            $db->setAttribute(\PDO::ATTR_ERRMODE , \PDO::ERRMODE_EXCEPTION);
            $db->beginTransaction();

            $place_holder=[];

            foreach($this->getLines()[0] as $v)
            {
                $place_holder[] = "?";
            }

            if ($withid)
                $sth = $db->prepare("INSERT INTO $table VALUES (default,".join(",",$place_holder).")");
            else
                $sth = $db->prepare("INSERT INTO $table VALUES (".join(",",$place_holder).")");

            foreach($this->getLines() as $line)
            {
                $sth->execute($line);
            }

            $db->commit();
        }catch(\Exception $e){
            $db->rollBack();
            $error_stack[] = $e->getMessage();
            return $error_stack;
        }

        return true;
    }
}