<form action="../src/website/import/report-flow" method="post" enctype="multipart/form-data">

    </br>

    type<input type="text" name="type"  />

    year<input type="text" name="year"  />

    month<input type="text" name="month"  />

<input type="file" name="fileExcel" id="fileExcel" />

<br />

<input type="submit" name="submit" value="Submit" />

</form>

    /**

     * @author Anana

     * 上传商销/盒 文件 异步处理

     */

    public  function actionReportFlow()

    {

        $uploadFile = UploadedFile::getInstanceByName ('fileExcel');

        $type  = intval(parent::getPostParam ('type')); // 文件上传类型

        $year  = intval(parent::getPostParam('year'));//年

        $month = intval(parent::getPostParam('month'));//月

        if($month<10){

            $month = '0'.$month;

        }

        $accountRoleId= \Yii::$app->session['accountRoleRow']['account_role_id'];

        if(!in_array($type,array('1','2'))){

            parent::apiReturn ( 1, [ ], '请先选择要上传的文件类型' );

        }else if(!$year){

            parent::apiReturn ( 1, [ ], '请选择年' );

        }else if(!$month || $month > 12 ){

            parent::apiReturn ( 1, [ ], '请选择月' );

        }else if($uploadFile){

            $fileExt = $uploadFile->getExtension (); // 文件后缀

            $size = $uploadFile->size;//文件大小

            if (! in_array ( $fileExt, array('xlsx','xls','csv') )) {

                $reason [] = '上传的文件后缀名必须为xlsx、xls、csv';

            }else if($size > 20*1024*1024){

                $reason [] = '文件大小不能超过20M';

            }else{

                $fileName = date('Ymdhis').'_'.$accountRoleId;

                if($type==1){

                    //商销/盒数据

                    $dir = 'report_flow_month';

                }else {

                    //终端开户明细

                    $dir = 'open_accounts_month';

                }

                $fileName = $fileName.'.'.$fileExt;

                $companyCode = \Yii::$app->session['companyRow']['code'];

                $updateMonth =date('Y-m-00',strtotime("{$year}-{$month}"));

                $buildsDir = dirname(\yii::$app->basePath)."/builds/{$companyCode}/{$dir}/$updateMonth";

                //创建生成目录文件夹

                if(!file_exists($buildsDir)){

                    mkdir($buildsDir,0777,true);

                }

                $csvPath = $buildsDir.'/'.$fileName;

                $rs = $uploadFile->saveAs($csvPath);//文件另存为

                if($rs){

                    $reportDaySalesFlowUploadLogModel =  new ReportDaySalesFlowUploadLogModel();

                    $reportDaySalesFlowUploadLogModel->file_type    = $type;

                    $reportDaySalesFlowUploadLogModel->role_id      = $accountRoleId;

                    $reportDaySalesFlowUploadLogModel->new_file_name= $fileName;

                    $reportDaySalesFlowUploadLogModel->create_time  = date("Y-m-d H:i:s");

                    $reportDaySalesFlowUploadLogModel->company      = $companyCode;

                    $reportDaySalesFlowUploadLogModel->update_month = $updateMonth;

                    $rs = $reportDaySalesFlowUploadLogModel->insert();

                    if(!$rs){

                        parent::apiReturn ( 1, $jsonData, '上传日志更新失败' );

                    }

                    parent::apiReturn ( 0, [ ], '上传成功' );

                }else{

                    $reason []  = '上传文件失败' . $uploadFile->error;

                }

            }

            $jsonData = [ ];

            if ($reason) {

                $jsonData ['errorList'] =  implode ( ' ', $reason );

                parent::apiReturn ( 1, $jsonData, '错误信息提示' );

            }

        }else{

            parent::apiReturn ( 1, [ ], '上传失败' );

        }

    }

 /**

     * 1 删除report_day_sales_flow_month表当月的老数据

     * 2 插入report_day_sales_flow_month表当月新数据

     *

     *

     * $fileName    文件名

     * $fileLogId   文件id

     */

    private  function  _updateReportDaySalesFlowMonth($fileName,$fileLogId,$updateMonth)

    {

        $connection = $this->_selectDb();

        $buildsDir = dirname(\yii::$app->basePath)."/builds/yiling/xlsx/report_flow_month/$fileName";

        if(!file_exists($buildsDir)){

            echo  "FILE NOT FOUND 找不到文件 \n ";

            return;

        }

        $objReader = \PHPExcel_IOFactory::createReader('CSV');

        $objReader->setReadDataOnly(true);// //只读去数据,忽略里面各种格式等(对于Excel读去,有很大优化)

        $phpExcel = \PHPExcel_IOFactory::load ($buildsDir); // 自动判断Excel版本解析读取

        $sheet = $phpExcel->getSheet ( 0 );

        $collectionList = [ ]; // 初始化数据集合

        $errorData = [ ]; // 导入的错误信息数据 有错误数据无法导入

        $errorArray = [ ];

        // 读取Excel数据行记录

        $totalRow = $sheet->getHighestRow(); // 取得总行数

        $thisTime = date("Y-m-d H:i:s");

        for($i = 2; $i <= $totalRow; ++$i) {

            $year = $sheet->getCellByColumnAndRow(0, $i)->getValue();

            $month = $sheet->getCellByColumnAndRow(1, $i)->getValue();

            $row['year_str'] = $year;

            $row['month_str'] = $month;

            $year_and_month = date("Y-m-00",strtotime(intval($year).'-'.intval($month)));

            $row['update_month'] = $year_and_month;

            $row['dept'] =$sheet->getCellByColumnAndRow(2, $i)->getValue();//部门

            $row['business_dept'] =$sheet->getCellByColumnAndRow(3, $i)->getValue();//业务部门

            $row['business_type'] =$sheet->getCellByColumnAndRow(4, $i)->getValue();//业务类型

            $row['region_description'] =$sheet->getCellByColumnAndRow(5, $i)->getValue();//省区

            $row['bussiness_region_description'] =$sheet->getCellByColumnAndRow(6, $i)->getValue();//业务省区

            $row['region_named_code'] =$sheet->getCellByColumnAndRow(7, $i)->getValue();//区办代码

            $row['region_named'] =$sheet->getCellByColumnAndRow(8, $i)->getValue();//区办

            $row['parent_role_number'] =$sheet->getCellByColumnAndRow(9, $i)->getValue();//主管工号

            $row['parent_role_name'] =$sheet->getCellByColumnAndRow(10, $i)->getValue();//主管姓名

            $row['role_number'] =$sheet->getCellByColumnAndRow(11, $i)->getValue();//代表工号

            $row['role_name'] =$sheet->getCellByColumnAndRow(12, $i)->getValue();//代表姓名

            $row['client_number'] =$sheet->getCellByColumnAndRow(13, $i)->getValue();//终端代码

            $row['client_name'] =$sheet->getCellByColumnAndRow(14, $i)->getValue();//终端名称

            $row['province'] =$sheet->getCellByColumnAndRow(15, $i)->getValue();//省份

            $row['city'] =$sheet->getCellByColumnAndRow(16, $i)->getValue();//城市

            $row['district'] =$sheet->getCellByColumnAndRow(17, $i)->getValue();//区县

            $row['hospital_type'] =$sheet->getCellByColumnAndRow(18, $i)->getValue();//医院类型

            $row['custom_field_1'] =$sheet->getCellByColumnAndRow(19, $i)->getValue();//以岭级别

            $row['nation_level'] =$sheet->getCellByColumnAndRow(20, $i)->getValue();//国家等级

            $row['contract_type'] =$sheet->getCellByColumnAndRow(21, $i)->getValue();//承包类型

            $row['custom_field_2'] =$sheet->getCellByColumnAndRow(22, $i)->getValue();//管理级别1

            $row['group_name'] =$sheet->getCellByColumnAndRow(23, $i)->getValue();//品种

            $row['goods_number'] =$sheet->getCellByColumnAndRow(24, $i)->getValue();//产品代码

            $row['goods_name_spec'] =$sheet->getCellByColumnAndRow(25, $i)->getValue();//产品名称与品规

            $row['purchase'] =$sheet->getCellByColumnAndRow(26, $i)->getValue();//商销/盒

            $row['create_time'] =$thisTime;//导入时间

            $row['file_log_id'] =$fileLogId;

            $data[] = $row;

        }

        //释放内存

        $sheet = $phpExcel =$phpExcel= null;

        $fields = [

            'year_str',

            'month_str',

            'update_month',

            'dept',

            'business_dept',

            'business_type',

            'region_description',

            'bussiness_region_description',

            'region_named_code',

            'region_named',

            'parent_role_number',

            'parent_role_name',

            'role_number',

            'role_name',

            'client_number',

            'client_name',

            'province',

            'city',

            'district',

            'hospital_type',

            'custom_field_1',

            'nation_level',

            'contract_type',

            'custom_field_2',

            'group_name',

            'goods_number',

            'goods_name_spec',

            'purchase',

            'create_time',

            'file_log_id'

        ];

        $rs = $connection->createCommand()->batchInsert('report_day_sales_flow_month',$fields,$data)->execute();

        return $rs;

    }

    /**

     * @author Anana

     * http://localhost/src/website/import/region-named

     * 上传以岭区办

     */

    public  function  actionRegionNamed()

    {

        $uploadFile = UploadedFile::getInstanceByName ('fileExcel');

        if(!$uploadFile){

            parent::apiReturn ( 1, [ ], '上传失败' );

        }

        $fileExt = $uploadFile->getExtension (); // 文件后缀

        $size = $uploadFile->size;//文件大小

        if (! in_array ( $fileExt, array('xlsx','xls','csv') )) {

            parent::apiReturn ( 1, [ ], '上传的文件后缀名必须为xlsx、xls、csv' );

        }

        if($size > 20*1024*1024){

            parent::apiReturn ( 1, [ ], '文件大小不能超过20M' );

        }

        $objReader = \PHPExcel_IOFactory::createReader('CSV');

        $objReader->setReadDataOnly(true);// //只读去数据,忽略里面各种格式等(对于Excel读去,有很大优化)

        $phpExcel = \PHPExcel_IOFactory::load ( $uploadFile->tempName ); // 自动判断Excel版本解析读取

        $sheet = $phpExcel->getSheet ( 0 );

        $collectionList = [ ]; // 初始化数据集合

        // 读取Excel数据行记录

        $totalRow = $sheet->getHighestRow (); // 取得总行数

        //所有部门

        $deptArr = array(

            1 => '医院一部',

            2 => '医院二部',

            3 => '社区部',

            4 => '城乡部'

        );

        $deptFlipArr = array_flip($deptArr);

        //所有业务部门

        $bussinessDeptArr = array(

            1=>'医院一部',

            2=>'肿瘤产品部',

            3=>'医院二部',

            4 =>'社区部',

            5 =>'城乡部',

            6=>'城乡二部'

        );

        $bussinessDeptFlipArr = array_flip($bussinessDeptArr);

        //所有省区

        $regionArea = RegionAreaModel::find()->select(['region_area_id','name'])->where(['is_valid'=>VALID])->asArray()->all();

        foreach ($regionArea as $v){

            $regionAreaArr[$v['region_area_id']] = $v['name'];

        }

        $regionAreaFlipArr = array_flip($regionAreaArr);

        //所有业务省区

        $bussinessProvince = BussinessProvinceModel::find()->select(['bussiness_province_id','bussiness_province'])->where(['is_valid'=>VALID])->asArray()->all();

        foreach ($bussinessProvince as $v){

            $bussinessProvinceArr[$v['bussiness_province_id']] = $v['bussiness_province'];

        }

        $bussinessProvinceFlipArr = array_flip($bussinessProvinceArr);

        $hasError = False;

        $roleId = \Yii::$app->session['accountRoleRow']['account_role_id'];

        $thisTime = date("Y-m-d H:i:s");

        // 跳过标题行

        for($i = 2; $i <= $totalRow; ++ $i) {

            $error = array();

            $dept = trim ( $sheet->getCellByColumnAndRow ( 0, $i )->getValue () ); // 部门

            $bussinessDept = trim ( $sheet->getCellByColumnAndRow ( 1, $i )->getValue () ); //业务部门

            $regionArea = trim ( $sheet->getCellByColumnAndRow ( 2, $i )->getValue () ); //省区

            $bussinessProvince = trim ( $sheet->getCellByColumnAndRow ( 3, $i )->getValue () ); //业务省区

            $regionNamedCode = trim ( $sheet->getCellByColumnAndRow ( 4, $i )->getValue () ); //区办代码

            $regionNamed = trim ( $sheet->getCellByColumnAndRow ( 5, $i )->getValue () ); //区办

            if(!in_array($dept,$deptArr)){

                $hasError = $hasError | true;

                $error[] = '【部门】不存在 ';

            }else{

                $row['dept_id'] = $deptFlipArr[$dept];

            }

            if(!in_array($bussinessDept,$bussinessDeptArr)){

                $hasError = $hasError | true;

                $error[] = '【业务部门】不存在 ';

            }else{

                $row['bussiness_dept_id'] = $bussinessDeptFlipArr[$bussinessDept];

            }

            if(!in_array($regionArea,$regionAreaArr)){

                $hasError = $hasError | true;

                $error[] = '【省区】不存在 ';

            }else{

                $row['region_area_id'] = $regionAreaFlipArr[$regionArea];

            }

            if(!in_array($bussinessProvince,$bussinessProvinceArr)){

                $hasError = $hasError | true;

                $error[] = '【业务省区】不存在 ';

            }else{

                $row['bussiness_province_id'] = $bussinessProvinceFlipArr[$bussinessProvince];

            }

            if(mb_strlen($regionNamedCode)>20){

                $hasError = $hasError | true;

                $error[] = '【区办代码】太长了';

            }

            if(!$regionNamed || mb_strlen($regionNamed)>20){

                $hasError = $hasError | true;

                $error[] = '【区办】未填或太长了';

            }else{

                $regionNamedOne = RegionNamedModel::find()->select(['region_named_id'])->where(['region_named'=>$regionNamed, 'is_valid'=>VALID])->one();

                if($regionNamedOne){

                    $hasError = $hasError | true;

                    $error[] = '此【区办】已存在，不可重复添加';

                }

            }

            //$row['dept'] =$dept;

            //$row['bussiness_dept']=$bussinessDept;

            //$row['region_area'] = $regionArea;

            //$row['bussiness_province'] = $bussinessProvince;

            $row['region_named_code'] =$regionNamedCode;

            $row['region_named'] = $regionNamed;

            $row['role_id'] = $roleId;

            $row['create_time'] = $thisTime;

            if(isset($error) && $error ){

                $row['error'] = implode('：',$error);

                $sheet->setCellValueExplicitByColumnAndRow ( 6, $i,$row['error']);

            }

            $collectionList[] = $row;

        }

        $fileName = 'region_named_error';

        $fileName = $fileName.'.xlsx';

        $companyCode = \Yii::$app->session['companyRow']['code'];

        $buildsDir = dirname(\yii::$app->basePath)."/builds/{$companyCode}/region_named_error";

        //创建生成目录文件夹

        if(!file_exists($buildsDir)){

            mkdir($buildsDir,0777,true);

        }

        $csvPath = $buildsDir.'/'.$fileName;

        //释放内存

        $bussinessDeptFlipArr = $bussinessDeptArr = $bussinessProvinceFlipArr=$bussinessProvinceArr = null;

        //上传数据正确 插入数据表

        if($hasError){

            //释放内存

            $collectionList = null;

            //上传数据错误，写入文件

            $phpExcelWriter = \PHPExcel_IOFactory::createWriter ( $phpExcel, 'Excel5' );

            $rs = $phpExcelWriter->save ( $csvPath );

            //$rs = $uploadFile->saveAs($csvPath);//文件另存为

            parent::apiReturn ( 1, [ ], '上传数据有错误，已生成错误数据文件，请下载并按提示修改' );

        }else{

            //释放内存

            $phpExcel = null;

            \Yii::$app->params ['dbSelect'] = 'company'; // 切换到 company 数据库

            \Yii::$app->params ['dsnSelect'] = 51;//yiling

            $connection = \Yii::$app->companydb;

            //$queryBuilder = $connection->queryBuilder;

            $fields = ['dept_id','bussiness_dept_id','region_area_id','bussiness_province_id','region_named_code','region_named','role_id','create_time'];

            $rs = $connection ->createCommand()->batchInsert(RegionNamedModel::tableName(),$fields, $collectionList)->execute();

            if($rs){

                //删除错误文件

                unlink($csvPath);

                parent::apiReturn ( 0, [ ], '导入数据成功' );

            }

            parent::apiReturn ( 1, [ ], '导入数据失败，请联系程序员');

        }

    }