xiaoben-as2-php/protected/controllers/StudenttargetController.php

<?php

class StudenttargetController extends Controller
{

    //学生目标院校导入
    public function actionImport()
    {
        $returnUrl = $this->createUrl('studentinfo/target');

        ini_set ('memory_limit', '300M');
        if(!$_FILES){
            Yii::app()->jump->error('文件大小超过范围', $returnUrl);
        }else {
            if (!isset($_FILES['student_file']) || !isset($_FILES['student_file']['size']) || $_FILES['student_file']['size'] > 5242880) {
                Yii::app()->jump->error('文件大小超过范围', $returnUrl);
            }
        }
        if (Yii::app()->request->getIsPostRequest()) {

            $uploader = new Uploader("upload/tmpDir/UploadStudentTarget/{$this->schoolId}/");

            $uploader->allowTypes = array("xls","xlsx");
            $uploader->fieldsMappings = array("exname" => array(0 => $this->schoolId));
            $file = $uploader->act();

            $file = Arr::current(Arr::current($file));

            if (!$file || !isset($file["error"]))
                Yii::app()->jump->error('请上传的Excel！', $returnUrl);

            if ($file["error"] != 0) {
                switch ($file["error"]) {
                    case 2001:
                        Yii::app()->jump->error('文件类型不符', $returnUrl);
                        break;
                    case 2002:
                        Yii::app()->jump->error('文件大小超出允许范围', $returnUrl);
                        break;
                    default:
                        Yii::app()->jump->error('上传失败', $returnUrl);
                        break;
                }
            }
            $inputFileName = $file["src"];
            // 导入PHPExcel类
            Yii::import('application.extensions.*');
            require_once('phpexcel/PHPExcel/IOFactory.php');
            try{
                $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
                $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
                @unlink($file["src"]);
            }catch (Exception $e){
                @unlink($file["src"]);
                Yii::app()->jump->error('Excel格式不正确：'.$e->getMessage(), $returnUrl);
            }

            if(!$sheetData)
            {
                Yii::app()->jump->error('请上传正确的Excel！', $returnUrl);
            }
            unset($objPHPExcel);
            unset($sheetData[1]);
            if(!$sheetData)
            {
                Yii::app()->jump->error('请输入学生目标院校相关数据', $returnUrl);
            }
            //当前学校所在省份
            $province_id = $this->conn->createCommand("SELECT province_id FROM school WHERE school_id = '{$this->schoolId}';")->queryScalar();

            $insertValue="insert into student_target_university(`student_id`,`student_name`,`student_card`,`class_name`,`grade_name`,`school_id`,`target_university`,`target_score`,`status`,`create_time`) values ";
            $values=array();
            $insertStudentIds = array();
            $studentCard=array();
            foreach ($sheetData as $key=>$val){
                if(count($val)< 5){
                    Yii::app()->jump->error('导入Excel格式不匹配! ', $returnUrl);
                }
                if($val['D']){
                    $studentCard[$val['D']][]=$val['D'];
                }
                if(empty($val['D'])){
                    unset($sheetData[$key]);
                }
                //$values[]="('".$val['B']."','".$val['A']."','".$sex."','".json_encode($extendKeyValue)."','".$id_number."','".$val['D']."')";
            }

            $errorMsg = array();
            foreach ($sheetData as $key=>$item){
                 //重复准考证号的行不存
                 if(isset($studentCard[$item['D']]) && count($studentCard[$item['D']])>1){
                     $errorMsg[] = implode(',',$item);continue;
                 }
                 //查询准考证号是否存在
                 $student = $this->conn->createCommand("SELECT student_id FROM student WHERE student_card = '{$item['D']}' limit 1;")->queryRow();
                 if(!$student){
                     $errorMsg[] = implode(',',$item);continue;
                 }
                 //查询目标院校的 本学校所在省分最低录取分数 没查到不存
                 $targetScoreSql = <<<SQL
select apu.admission_score
from admission_province_university apu
join admission_province ap on ap.ap_id=apu.ap_id and ap.region_id={$province_id} and ap.is_use=1
join admission_university au on au.au_id=apu.au_id and au.name='{$item['E']}'
SQL;
                 $targetScore = $this->conn->createCommand($targetScoreSql)->queryScalar();
                 if(!$targetScore){
                     $errorMsg[] = implode(',',$item);continue;
                 }
                 $values[] = sprintf("('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",$student['student_id'],$item['C'],$item['D'],$item['B'],$item['A'],$this->schoolId,$item['E'],$targetScore,1,time());
                 $insertStudentIds[] = $student['student_id'];
            }

//            if($errorMsg){
//                if(count($errorMsg)>5){
//                    Yii::app()->jump->error('请检查重新上传问题数据:<br>'.implode(' ',array_slice($errorMsg,0,5)).' ...... ', $returnUrl);
//                }else{
//                    Yii::app()->jump->error('请检查重新上传问题数据:<br>'.implode(' ',$errorMsg), $returnUrl);
//                }
//            }

            if($values){
                $sql=$insertValue.implode(',',$values);
                $transaction =	$this->conn->beginTransaction();
                try{
                    if($insertStudentIds) {
                        $studentIds = implode(',', $insertStudentIds);
                        $this->conn->createCommand("update student_target_university set status=0 where student_id in ({$studentIds})")->execute();
                    }

                    $this->conn->createCommand($sql)->execute();
                    $transaction->commit();
                    // Yii::app()->jump->error('Excel导入成功！');
                    //跳转导入页面
                    if($errorMsg){
                        $this->redirect($this->createUrl('studenttarget/error?error='.implode('_',$errorMsg)));
                    }
                    $this->redirect($this->createUrl('studentinfo/target'));

                }catch(Exception $e){
                    $transaction->rollBack();
                    //var_dump($e->getMessage());exit;
                    Yii::app()->jump->error('Excel导入失败！', $returnUrl);
                }
            }else{
                if($errorMsg){
                    $this->redirect($this->createUrl('studenttarget/error?error='.implode('_',$errorMsg)));
                }
                Yii::app()->jump->error('Excel导入失败！', $returnUrl);
            }

        }

    }

    //导入的错误数据页面
    public function actionError()
    {
        $errorData = Req::get('error') ? Req::get('error') : '';
        $dataList  = !empty($errorData)?explode('_',$errorData):array();

        return $this->render('target_error', array(
            'dataList' => $dataList,
        ));
    }
}