ProjectData.php 6.8 KB
<?php

namespace app\school\controller\project_common;

use app\school\model\project_common\ProjectReviewResult;
use think\Db;

trait ProjectData
{
    /**
     * 返回中期检查前的的项目
     * @param $projectWhere
     * @return array
     */
    private function approvalProjectData($projectWhere = [])
    {
//        $projectWhere = array_merge(['a.review_stage' => 0], $projectWhere);
        $res = $this->projectData($projectWhere);
        foreach ($res['rows'] as &$row) {
            // 已经中期评审或者立项结题的项目状态为:已立项、已提交
            if ('0' != $row['review_stage']) {
                $row['check_stage'] = 3;
            }
        }
        return $res;
    }

    /**
     * 返回立项评审后项目结题前的项目
     * @param array $projectWhere
     * @return array
     */
    private function midProjectData($projectWhere = [])
    {
        $projectWhere = array_merge(['a.review_stage' => 1], $projectWhere);
        return $this->projectData($projectWhere);
    }

    /**
     * 返回项目结题的项目
     * @param array $projectWhere
     * @return array
     */
    private function conclusionProjectData($projectWhere = [])
    {
        $projectWhere = array_merge(['a.review_stage' => 2], $projectWhere);
        return $this->projectData($projectWhere);
    }

    /**
     * 根据筛选条件查询项目数据
     * @param array $projectWhere
     * @return array
     */
    private function projectData($projectWhere = [])
    {

        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        // 默认查询项目容器下未淘汰(|未退回)的项目
        $projectWhere = array_merge(['a.container_id' => $this->containerId, 'a.project_status' => 0], $projectWhere);
        $field = [
            'a.*',
            't.name as project_type_name',
            'u.nickname',
            'u.student_no',
            'c.college_name as college_name',
            'd.specialty_name as major_name',
            'SUM(r.result) as sum',
            'count(member_id) as memberCount'
        ];
//        $total = $this->model->alias('a')->where($projectWhere)->count();
        $totalQuery = $this->model
            ->field('a.id')
            ->alias('a')
            ->join('user u', 'a.user_id = u.id', 'left')
            ->join('project_type t', 'a.project_type = t.id', 'left')
            ->join('college c', 'u.college = c.id', 'left')
            ->join('specialty d', 'u.specialty = d.id', 'left');
         if(isset($this->reviewStage)) {
             $totalQuery->join('project_review_result r',"a.id = r.project_register_id AND r.review_stage = {$this->reviewStage}",'left');
         }else {
             $totalQuery->join('project_review_result r',"a.id = r.project_register_id",'left');
         }
        $total= $totalQuery->group('a.id')->where($projectWhere)
            ->count();
//        $total = $this->model->alias('a')->where($where)->where($projectWhere)->count();
        $query = $this->model
            ->field($field)
            ->alias('a')
            ->join('user u', 'a.user_id = u.id', 'left')
            ->join('project_type t', 'a.project_type = t.id', 'left')
            ->join('college c', 'u.college = c.id', 'left')
            ->join('fa_members m','a.id = m.item_id and member_type = 1 and member_source = 2','left')
            ->join('specialty d', 'u.specialty = d.id', 'left');
            if(isset($this->reviewStage)) {
                $query->join('project_review_result r',"a.id = r.project_register_id AND r.review_stage = {$this->reviewStage}",'left');
            }else {
                $query->join('project_review_result r',"a.id = r.project_register_id",'left');
            }
            $rows = $query->group('a.id')
            ->order('sum','desc')
            ->where($projectWhere)
            ->limit($offset, $limit)
            ->select();
        $projectIds = array_column($rows, 'id');
        if ( isset($this->reviewStage) ) {
            $tempScoreResults = (new ProjectReviewResult())
                ->where(['project_register_id' => ['in', $projectIds], 'review_stage' => $this->reviewStage])
                ->group('project_register_id')
                ->column('GROUP_CONCAT(review_id, \':\', IFNULL(result, \'-\'))', 'project_register_id');

            foreach ($rows as &$row) {
                $review_scores = [];
                // 项目有评审结果,就添加上
                if (isset($tempScoreResults[$row['id']])) {
                    $review_scores = $this->getReviewResultArr($tempScoreResults[$row['id']]);
                }
                $row['review_scores'] = $review_scores;
            }
        }
        foreach ($rows as &$row) {
            $row['project_teachers'] = json_decode($row['project_teachers'], true);
        }
        $result = [
            'total' => $total,
            'rows' => $rows
        ];
        return $result;
    }

    /**
     * 评审结果字符串转数组(字符串格式:  // 项目id:项目打分结果,项目id:项目打分结果)
     * @param $reviewResultStr
     * @return array
     */
    private function getReviewResultArr($reviewResultStr)
    {
        $result = [];
        foreach (explode(',', $reviewResultStr) as $v) {
            $temp = explode(':', $v);
            $result[$temp[0]] = $temp[1];
        }
        return $result;//
    }


    /**
     * 获取导出csv Sql
     * @param array $projectWhere
     * @return array
     */
    public function getExportSql($projectWhere = [])
    {
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        // 默认查询项目容器下未淘汰(|未退回)的项目
        $projectWhere = array_merge(['a.container_id' => $this->containerId, 'a.project_status' => 0], $projectWhere);
        $field = ['a.id','a.project_name','a.project_no','t.name as project_type_name','f.college_name as project_college','u.nickname','u.student_no','c.college_name as college_name','d.college_name as major_name','CASE a.project_level WHEN 0 THEN \'未评级\' WHEN 1 THEN \'院级\' WHEN 2 THEN \'校级\' WHEN 3 THEN \'省级\' ELSE \'国家级\' END as project_level','CASE  WHEN a.check_stage < 2 THEN \'未立项\'  ELSE \'已立项\' END as check_stage'];
        $total = $this->model->alias('a')->where($where)->where($projectWhere)->count();
        $sql = $this->model
            ->field($field)
            ->alias('a')
            ->join('user u', 'a.user_id = u.id', 'left')
            ->join('project_type t', 'a.project_type = t.id', 'left')
            ->join('college c', 'u.college = c.id', 'left')
            ->join('college d', 'u.specialty = d.id', 'left')
            ->join('college f','a.project_college  = f.id','left')
            ->where($where)->where($projectWhere)
            ->fetchSql(true)
            ->select();

        return ['sql' => $sql,'total' => $total];
    }
}