report/app/common/api/VerifyApi.php

<?php

namespace app\common\api;

use app\admin\model\Notice as NoticeModel;
use app\enterprise\model\Talent;
use app\common\api\DictApi;
use app\admin\model\Enterprise;
use think\facade\Db;
use app\common\model\TalentCondition;

/**
 * Description of VerifyApi
 *
 * @author sgq
 */
class VerifyApi {

    public static function getTalentInfoById($id, $isAdmin = false) {//添加admin只为区别导入数据管理端的显示差异
        $where = [];
        $where[] = ["id", "=", $id];
        $info = Talent::findOrEmpty($id)->toArray();
        if ($info) {
            if ($info["talent_type"]) {
                $info["talentTypeName"] = DictApi::selectByParentCode("talent_type")[$info["talent_type"]];
            }
            if ($info["nationality"]) {
                $info["nationalityName"] = DictApi::selectByParentCode("nationality")[$info["nationality"]];
            }
            if ($info["nation"]) {
                $info["nationName"] = DictApi::selectByParentCode("nation")[$info["nation"]];
            }
            if ($info["politics"]) {
                $info["politicsName"] = DictApi::selectByParentCode("politics")[$info["politics"]];
            }
            if ($info["province"]) {
                $info["provinceName"] = Db::table("un_common_location")->where("code", "=", $info["province"])->findOrEmpty()["name"];
            }
            if ($info["city"]) {
                $info["cityName"] = Db::table("un_common_location")->where("code", "=", $info["city"])->findOrEmpty()["name"];
            }
            if ($info["county"]) {
                $info["countyName"] = Db::table("un_common_location")->where("code", "=", $info["county"])->findOrEmpty()["name"];
            }
            $enterprise = Enterprise::findOrEmpty($info["enterprise_id"])->toArray();
            $info["enterpriseName"] = $enterprise["name"];
            $info["enterpriseId"] = $enterprise["id"];
            $info["enterpriseType"] = $enterprise["type"];
            $info["enterpriseEphone"] = $enterprise["ephone"];
            $info["enterpriseTag"] = $enterprise["enterpriseTag"];
            $info["enterpriseBankCard"] = $enterprise["bankCard"];
            $info["enterpriseBankNetwork"] = $enterprise["bankNetwork"];
            $info["enterpriseBank"] = $enterprise["bank"];
            if ($enterprise["street"]) {
                $info["street"] = $enterprise["street"];
                $info["streetName"] = DictApi::selectByParentCode("street")[$enterprise["street"]];
            }
            if ($enterprise["industryFieldNew"]) {
                $info["industryFieldName"] = DictApi::selectByParentCode("industry_field")[$enterprise["industryFieldNew"]];
            }
            if ($enterprise["enterpriseTag"]) {
                $info["enterpriseTagName"] = DictApi::selectByParentCode("enterprise_tag")[$enterprise["enterpriseTag"]];
            }
            if ($info["talent_arrange"]) {
                $info["talentArrangeName"] = DictApi::selectByParentCode("talent_arrange")[$info["talent_arrange"]];
            }
            if ($info["import_way"]) {
                $info["importWayName"] = DictApi::selectByParentCode("import_way")[$info["import_way"]];
            }
            if ($info["source"]) {
                $info["sourceName"] = DictApi::selectByParentCode("source")[$info["source"]];
            }
            if ($info["source_city"]) {
                $info["sourceCityName"] = Db::table("un_common_location")->where("code", "=", $info["source_city"])->findOrEmpty()["name"];
            }
            if ($info["source_county"]) {
                $info["sourceCountyName"] = Db::table("un_common_location")->where("code", "=", $info["source_county"])->findOrEmpty()["name"];
            }
            if ($info["highest_degree"]) {
                $info["highestDegreeName"] = DictApi::selectByParentCode("highest_degree")[$info["highest_degree"]];
            }
            if ($info["qz_talent_info"]) {
                $qz_talent_info = explode("；", $info["qz_talent_info"]);
                list($tmp1, $timeStart) = explode("：", $qz_talent_info[1]);
                list($tmp2, $timeEnd) = explode("：", $qz_talent_info[2]);
                if (strtotime($timeStart)) {
                    $tmp_time = date("Y-m-d", strtotime($timeStart));
                    $qz_talent_info[1] = implode("：", [$tmp1, $tmp_time]);
                    if ($isAdmin) {
                        $batch_info = NoticeModel::where('batch', $tmp_time)->find();
                        if ($batch_info) {
                            $qz_talent_info[1] = "<a target='_blank' href='/common/notice/view/id/" . $batch_info['id'] . "'>" . $qz_talent_info[1] . "</a>";
                        }
                    }
                }
                if (strtotime($timeEnd)) {
                    $qz_talent_info[2] = implode("：", [$tmp2, date("Y-m-d", strtotime($timeEnd))]);
                }
                $info["qz_talent_info"] = implode("；", $qz_talent_info);
            }
            if ($info["fj_talent_info"]) {
                $fj_talent_info = explode("；", $info["fj_talent_info"]);
                list($tmp1, $timeStart) = explode("：", $fj_talent_info[0]);
                list($tmp2, $validYear) = explode("：", $fj_talent_info[2]);
                $timeStart = str_replace(["."], "-", $timeStart);
                if (strtotime($timeStart)) {
                    $tmp_time = date("Y-m-d", strtotime($timeStart));
                    $fj_talent_info[0] = implode("：", [$tmp1, $tmp_time]);
                    if ($isAdmin) {
                        $batch_info = NoticeModel::where('batch', $tmp_time)->find();
                        if ($batch_info) {
                            $fj_talent_info[0] = "<a target='_blank' href='/common/notice/view/id/" . $batch_info['id'] . "'>" . $fj_talent_info[0] . "</a>";
                        }
                    }
                    if (strtotime($validYear)) {
                        $fj_talent_info[2] = implode("：", [$tmp2, date("Y-m-d", strtotime($validYear))]);
                    } else {
                        $validval = intval($validYear);
                        if ($validYear > 0) {
                            $timestr = str_replace($validval, "", $validYear);
                            switch ($timestr) {
                                case "月":
                                    $timetype = "months";
                                    break;
                                case "日":
                                    $timetype = "days";
                                    break;
                                default:
                                    $timetype = "years";
                                    break;
                            }
                            $fj_talent_info[2] = implode("：", [$tmp2, date("Y-m-d", strtotime("+{$validval} {$timetype} -1 days", strtotime($timeStart)))]);
                        }
                    }
                }
                $info["fj_talent_info"] = implode("；", $fj_talent_info);
            }
            if ($info["talent_condition"]) {
                $talent_condition = \app\common\model\TalentCondition::findOrEmpty($info["talent_condition"]);
                $info["talentConditionName"] = $talent_condition["name"];
                $info["talent_arrange_category"] = $talent_condition["talentLevelCat"];
                $info["talentArrangeCatName"] = DictApi::selectByParentCode("talent_condition_cats")[$talent_condition["talentLevelCat"]];
            }
        }
        return $info;
    }

    public static function getOne($id) {
        return Talent::findOrEmpty($id);
    }

    public static function getFullDeptList($params, $where = []) {
        $order = $params["order"] ?: "desc";
        $offset = $params["offset"] ?: 0;
        $limit = $params["limit"] ?: 10;
        $whereRaw = "";
        switch ($params["checkState"]) {
            case 1:
                $where[] = ["tl.state", "=", TalentState::FST_VERIFY_PASS];
                $where[] = ["ti.pass_dept_check", "=", 0];
                break;
            case 2:
                $where[] = ["tl.state", "=", TalentState::DEPT_VERIFY_REJECT];
                break;
            case 3:
                $where[] = ["tl.state", "=", TalentState::DEPT_VERIFY_PASS];
                break;
            default:
                $whereRaw = sprintf("(tl.state=%d and ti.pass_dept_check=0) or (tl.state=%d) or (tl.state=%d)", TalentState::FST_VERIFY_PASS, TalentState::DEPT_VERIFY_PASS, TalentState::DEPT_VERIFY_REJECT);
                break;
        }

        $companyId = session("user")["companyId"];
        $talent_arrange_kvs = DictApi::selectByParentCode("talent_arrange");
        $enterprise_tag_kvs = DictApi::selectByParentCode("enterprise_tag");
        $count = Talent::alias("ti")
                        ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                        ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                        ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                        ->where($where)
                        ->where($whereRaw)->count();
        $list = Talent::alias("ti")
                        ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                        ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                        ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                        ->where($where)
                        ->where($whereRaw)
                        ->field("ti.*,tl.last_state as 'lastState',tl.state as 'realState',e.name as enterprise_name,e.type as enterprise_type,enterpriseTag,tc.name as talentConditionName")
                        ->limit($offset, $limit)->order("ti.createTime " . $order)
                        ->select()->toArray();
        foreach ($list as &$item) {
            $item["talent_type"] = $item["enterprise_type"] == 1 ? "晋江市现代产业体系人才" : "集成电路优秀人才";
            $item["enterprise_tag"] = $enterprise_tag_kvs[$item["enterpriseTag"]];
            $item["talentArrangeName"] = $talent_arrange_kvs[$item["talent_arrange"]];
        }unset($item);
        return ["total" => $count, "rows" => $list];
    }

    public static function getDeptList($params, $where = []) {
        $order = $params["order"] ?: "desc";
        $offset = $params["offset"] ?: 0;
        $limit = $params["limit"] ?: 10;
        $companyId = session("user")["companyId"];
        switch ($params["checkState"]) {
            case 1:
                //待审核
                $where[] = ["tl.active", "=", 0];
                $where[] = ["tl.state", "=", TalentState::FST_VERIFY_PASS];
                break;
            case 2:
                //已驳回
                $where[] = ["tl.active", "=", 1];
                $where[] = ["tl.new_state", "=", TalentState::SCND_SUBMIT];
                break;
            case 3:
                //已通过
                $where[] = ["tl.active", "=", 1];
                $where[] = ["tl.new_state", "=", TalentState::DEPT_VERIFY_PASS];
                break;
            default:
                $where = sprintf("(tl.active=0 and tl.state=%d) or (tl.active=1 and (tl.new_state in (%d,%d)))", TalentState::FST_VERIFY_PASS, TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_PASS);
                break;
        }

        $talent_arrange_kvs = DictApi::selectByParentCode("talent_arrange");
        $enterprise_tag_kvs = DictApi::selectByParentCode("enterprise_tag");
        $count = Talent::alias("ti")
                        ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                        ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                        ->leftJoin("(select mainId,active,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step`=3 and companyId='{$companyId}' and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                        ->where($where)
                        ->whereRaw("find_in_set(:companyId,companyIds)", ["companyId" => $companyId])->count();
        $list = Talent::alias("ti")
                        ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                        ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                        ->leftJoin("(select mainId,active,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step`=3 and companyId='{$companyId}' and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                        ->where($where)
                        ->whereRaw("find_in_set(:companyId,companyIds)", ["companyId" => $companyId])
                        ->field("ti.*,tl.last_state as 'lastState',tl.state as 'realState',e.name as enterprise_name,e.type as enterprise_type,enterpriseTag,tc.name as talentConditionName")
                        ->limit($offset, $limit)->order("ti.createTime " . $order)
                        ->select()->toArray();
        foreach ($list as &$item) {
            $item["talent_type"] = $item["enterprise_type"] == 1 ? "晋江市现代产业体系人才" : "集成电路优秀人才";
            $item["enterprise_tag"] = $enterprise_tag_kvs[$item["enterpriseTag"]];
            $lastCheckLog = TalentLogApi::getCompanyNewestCheckedLog($item["id"], $companyId);
            $item["deptCheckState"] = $lastCheckLog["new_state"];
            $item["talentArrangeName"] = $talent_arrange_kvs[$item["talent_arrange"]];
        }unset($item);
        return ["total" => $count, "rows" => $list];
    }

    public static function getPublicList($params) {
        $order = $params["order"];
        $offset = $params["offset"];
        $limit = $params["limit"];
        $where = [];
        $where[] = ["e.type", "=", session("user")["type"]];
        if ($params["name"]) {
            $where[] = ["ti.name", "like", "%" . $params["name"] . "%"];
        }
        if ($params["sex"]) {
            $where[] = ["ti.sex", "=", $params["sex"]];
        }
        if ($params["checkState"]) {
            $where[] = ["ti.checkState", "=", $params["checkState"]];
        }
        $type = $params["type"];
        switch ($type) {
            case 1:
            case 2:
                $where[] = ["ti.checkState", "=", TalentState::REVERIFY_PASS];
                break;
            case 3:             //公示
            case 7:             //公示预览
                $where[] = ["ti.checkState", "=", TalentState::ZX_PASS];
                break;
            case 4:             //公示通过
                $where[] = ["ti.checkState", "=", TalentState::ANNOUNCED];
                break;
            case 5:
            case 8:             //公布预览
                $where[] = ["ti.checkState", "=", TalentState::ANNOUNCED_REVERIFY_PASS];
                break;
            case 6:
                $where[] = ["ti.checkState", "=", TalentState::PUBLISH_PASS];
                break;
        }
        $enterprise_tag_kvs = DictApi::selectByParentCode("enterprise_tag");
        $count = Talent::alias("ti")->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")->where($where)->count();
        $list = Talent::alias("ti")->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                        ->where($where)
                        ->limit($offset, $limit)
                        ->order("ti.createTime " . $order)->field("ti.*,e.name as enterpriseName,e.type as enterprise_type,enterpriseTag")->select()->toArray();
        foreach ($list as &$item) {
            $item["talent_type"] = $item["enterprise_type"] == 1 ? "晋江市现代产业体系人才" : "集成电路优秀人才";
            $item["enterprise_tag"] = $enterprise_tag_kvs[$item["enterpriseTag"]];
        }unset($item);
        return ["total" => $count, "rows" => $list];
    }

    public static function getListByIds($ids) {
        $where[] = ["id", "in", $ids];
        return Talent::where($where)->select()->toArray();
    }

    public static function getList($params) {
        $where = [];
        $order = $params["order"] ?: "desc";
        $offset = $params["offset"] ?: 0;
        $limit = $params["limit"] ?: 10;
        $where[] = ["e.type", "=", session("user")["type"]];
        if ($params["name"]) {
            $where[] = ["ti.name", "like", "%{$params["name"]}%"];
        }
        if ($params["card_number"]) {
            $where[] = ["ti.card_number", "like", "%" . $params["card_number"] . "%"];
        }
        if ($params["sex"]) {
            $where[] = ["ti.sex", "=", $params["sex"]];
        }
        if ($params["nation"]) {
            $where[] = ["ti.nation", "=", $params["nation"]];
        }
        if ($params["apply_year"]) {
            $where[] = ["ti.apply_year", "like", "{$params["apply_year"]}%"];
        }
        if ($params["phone"]) {
            $where[] = ["ti.phone", "like", "%{$params["phone"]}%"];
        }
        if ($params["email"]) {
            $where[] = ["ti.email", "like", "%{$params["email"]}%"];
        }
        if ($params["nationality"]) {
            $where[] = ["ti.nationality", "=", $params["nationality"]];
        }
        if ($params["province"]) {
            $where[] = ["ti.province", "=", $params["province"]];
        }
        if ($params["politics"]) {
            $where[] = ["ti.politics", "=", $params["politics"]];
        }
        if ($params["enterprise_id"]) {
            $where[] = ["ti.enterprise_id", "=", $params["enterprise_id"]];
        }
        if ($params["street"]) {
            $where[] = ["e.street", "=", $params["street"]];
        }
        if ($params["industry_field"]) {
            $where[] = ["e.industryFieldNew", "=", $params["industry_field"]];
        }
        if ($params["industry_field_old"]) {
            $where[] = ["e.industryFieldOld", "=", $params["industry_field_old"]];
        }
        if ($params["enterprise_tag"]) {
            $where[] = ["e.enterpriseTag", "=", $params["enterprise_tag"]];
        }
        if ($params["talent_type"]) {
            $where[] = ["ti.talent_type", "=", $params["talent_type"]];
        }
        if ($params["import_way"]) {
            $where[] = ["ti.import_way", "=", $params["import_way"]];
        }
        if ($params["highest_degree"]) {
            $where[] = ["ti.highest_degree", "=", $params["highest_degree"]];
        }
        if ($params["study_abroad"]) {
            $where[] = ["ti.study_abroad", "=", $params["study_abroad"]];
        }
        if ($params["source"]) {
            $where[] = ["ti.source", "=", $params["source"]];
        }
        if ($params["talent_arrange"]) {
            $where[] = ["ti.talent_arrange", "=", $params["talent_arrange"]];
        }
        if ($params["talent_condition"]) {
            $where[] = ["ti.talent_condition", "=", $params["talent_condition"]];
        }
        if ($params["isMatchZhiren"]) {
            $where[] = ["ti.isMatchZhiren", "=", $params["isMatchZhiren"]];
        }

        $process = $params["process"];
        if ($process == 4) {
            $companyId = session('user')['companyId'];
            $company_info = CompanyApi::getOne($companyId);
            if ($company_info["code"] == "super" || $company_info["code"] == "rsj") {
                return self::getFullDeptList($params, $where);
            } else {
                return self::getDeptList($params, $where);
            }
        }
        if ($process == 5) {
            $whereRaw = sprintf("(tl.state in (14,15,16)) or (tl.state=12 and ti.pass_dept_check=0) or (tl.state=10 and ti.pass_dept_check=1) or (tl.state=10 and (tc.companyIds is null or tc.companyIds = ''))");
            switch ($params["checkState"]) {
                case 1:
                    $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_PASS, TalentState::DEPT_VERIFY_PASS]];
                    break;
                case -1:
                    $where[] = ["tl.state", "=", TalentState::REVERIFY_FAIL];
                    break;
            }
            $count = Talent::alias("ti")
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                            ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("new_talent_checklog tl", "tl.mainId=ti.id and tl.id=(select id from new_talent_checklog where mainId=ti.id and `step` is null and active=1 and typeFileId is null order by createTime desc limit 1)")
                            ->whereRaw($whereRaw)->where($where)->count();
            $list = Talent::alias("ti")
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                            ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("new_talent_checklog tl", "tl.mainId=ti.id and tl.id=(select id from new_talent_checklog where mainId=ti.id and `step` is null and active=1 and typeFileId is null order by createTime desc limit 1)")
                            ->whereRaw($whereRaw)
                            ->where($where)
                            ->limit($offset, $limit)
                            ->order("ti.createTime " . $order)
                            ->field("ti.*,tl.last_state as 'lastState',tl.state as 'realState',e.name as enterprise_name,e.type as enterprise_type,enterpriseTag")
                            ->select()->toArray();
        } else {
            switch ($process) {
                case 1:
                    $where[] = ["ti.checkState", "in", [TalentState::FST_SUBMIT, TalentState::BASE_VERIFY_FAIL]];
                    break;
                case 2:
                    $where[] = ["ti.checkState", "in", [TalentState::BASE_VERIFY_PASS, TalentState::BASE_REVERIFY_FAIL]];
                    break;
                case 3:
                    switch ($params["checkState"]) {
                        case -1://保存未提交
                            $where[] = ["tl.new_state", "=", TalentState::SCND_SAVE];
                            $where[] = ["tl.state", "<>", TalentState::FST_VERIFY_REJECT];
                            //$where[] = ["ti.first_submit_time", "exp", Db::raw("is null")];
                            $where[] = ["ti.delete", "=", 0];
                            break;
                        case 1://待审核
                            $where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            //$where[] = ["tl.state", "in", [TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_REJECT, TalentState::REVERIFY_REJECT]];
                            $where[] = ["tl.last_state", "<>", TalentState::FST_VERIFY_REJECT];
                            break;
                        case 2://驳回
                            //$where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            $where[] = ["tl.new_state", "=", TalentState::SCND_SAVE];
                            $where[] = ["tl.state", "=", TalentState::FST_VERIFY_REJECT];
                            break;
                        case 3://通过
                            $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_PASS]];
                            break;
                        case 4://失败
                            $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_FAIL]];
                            break;
                        case 5://重新提交
                            $where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            $where[] = ["tl.last_state", "=", TalentState::FST_VERIFY_REJECT];
                            break;
                        default:
                            //$where[] = ["tl.state", "in", [TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_REJECT, TalentState::REVERIFY_REJECT, TalentState::FST_VERIFY_REJECT]];
                            $whereRaw = sprintf("((tl.new_state=%d and ti.`delete`=0) or tl.new_state=%d or tl.state in (%d,%d))", TalentState::SCND_SAVE, TalentState::SCND_SUBMIT, TalentState::FST_VERIFY_PASS, TalentState::FST_VERIFY_FAIL);
                    }
                    break;
                case 6:
                    $where[] = ["tl.state", ">=", TalentState::REVERIFY_PASS];
                    if ($params["checkState"]) {
                        $where[] = ["tl.state", "=", $params["checkState"]];
                    }
                    break;
                case 7:
                    $where[] = ["tl.state", "=", TalentState::CERTIFICATED];
                    break;
            }
            $count = Talent::alias("ti")
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("(select mainId,last_state,new_state,state,createTime,row_number() over (partition by mainId order by createTime desc) as rowIndex from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null) tl", "tl.mainId=ti.id and tl.rowIndex=1")
                            ->where($where)->where($whereRaw)->count();
            $list = Talent::alias("ti")
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("(select mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("(select mainId,last_state,new_state,state,createTime,row_number() over (partition by mainId order by createTime desc) as rowIndex from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null) tl", "tl.mainId=ti.id and tl.rowIndex=1")
                            ->where($where)->where($whereRaw)->limit($offset, $limit)->order("ti.createTime " . $order)->field("ti.*,tl.last_state as 'lastState',tl.state as 'realState',e.name as enterprise_name,e.type as enterprise_type,enterpriseTag")->select()->toArray();
        }
        $talent_arrange_kvs = DictApi::selectByParentCode("talent_arrange");
        $enterprise_tag_kvs = DictApi::selectByParentCode("enterprise_tag");
        foreach ($list as &$item) {
            $item["talent_type"] = $item["enterprise_type"] == 1 ? "晋江市现代产业体系人才" : "集成电路优秀人才";
            $item["enterprise_tag"] = $enterprise_tag_kvs[$item["enterpriseTag"]];
            $item["talentArrangeName"] = $talent_arrange_kvs[$item["talent_arrange"]];
            $item["talentConditionName"] = TalentCondition::findOrEmpty($item["talent_condition"])["name"];
            //$last_log = TalentLogApi::getLastLog($item["id"], 1, 0, ["step", "=", null]);
            //$item["lastState"] = $last_log["last_state"];
            //$item["realState"] = $last_log["state"];
        }unset($item);
        return ["total" => $count, "rows" => $list];
    }

    public static function getExportDatas($process, $params) {
        $where[] = [];
        //特殊字段处理
        $exportFields = $params["export"];
        $fields = [];
        foreach ($exportFields as $field) {
            if (!in_array($field, ["industryFieldNew", "enterpriseName", "enterpriseTag", "street", "talent_arrange_category", "checkMsg"])) {
                $fields[] = "ti." . $field;
            }
        }
        $fields[] = "e.name as enterpriseName";
        $fields[] = "e.industryFieldNew";
        $fields[] = "e.enterpriseTag";
        $fields[] = "e.street";
        $fields[] = "tc.talentLevelCat";
        $fields[] = "tl.description as checkMsg";
        $fields[] = "tc.name as talentConditionName";
        $fields[] = "tl.description as checkMsg";
        if (in_array("card_type", $exportFields)) {
            $cardTypes = DictApi::selectByParentCode("card_type");
        }
        if (in_array("industryFieldNew", $exportFields)) {
            $industry_fields = DictApi::selectByParentCode("industry_field");
        }
        if (in_array("enterpriseTag", $exportFields)) {
            $enterpriseTags = DictApi::selectByParentCode("enterprise_tag");
        }
        if (in_array("street", $exportFields)) {
            $streets = DictApi::selectByParentCode("street");
        }
        if (in_array("nation", $exportFields)) {
            $nations = DictApi::selectByParentCode("nation");
        }
        if (in_array("nationality", $exportFields)) {
            $nationalitys = DictApi::selectByParentCode("nationality");
        }
        if (in_array("politics", $exportFields)) {
            $politics = DictApi::selectByParentCode("politics");
        }
        if (in_array("talent_type", $exportFields)) {
            $talentTypes = DictApi::selectByParentCode("talent_type");
        }
        if (in_array("talent_arrange_category", $exportFields)) {
            $talentArrangeCategories = DictApi::selectByParentCode("talent_condition_cats");
        }
        if (in_array("highest_degree", $exportFields)) {
            $highest_degree = DictApi::selectByParentCode("highest_degree");
        }
        if (in_array("import_way", $exportFields)) {
            $import_way = DictApi::selectByParentCode("import_way");
        }
        if (in_array("source", $exportFields)) {
            $source = DictApi::selectByParentCode("source");
        }
        if (in_array("source_city", $exportFields)) {
            $source_city = DictApi::selectByParentCode("source_city");
        }
        if (in_array("source_county", $exportFields)) {
            $source_county = DictApi::selectByParentCode("source_county");
        }
        if (in_array("talent_arrange", $exportFields)) {
            $talent_arrange = DictApi::selectByParentCode("talent_arrange");
        }
        $sex = [1 => "男", 2 => "女"];
        $pre_import_type = [1 => "意向合同", 2 => "创业企业名称预核准"];
        $study_abroad = [1 => "是", 2 => "否"];
        $salary_pay_way = [1 => "本单位", 2 => "本单位所属集团公司及权属公司"];
        $return = [1 => "是", 2 => "否"];
        $isMatchZhiren = [0 => "否", 1 => "是"];

        $where = [];
        $where[] = ["e.type", "=", session("user")["type"]];
        if ($params["all"] != 1) {
            if ($params["name"]) {
                $where[] = ["ti.name", "like", "%{$params["name"]}%"];
            }
            if ($params["card_number"]) {
                $where[] = ["ti.card_number", "like", "%" . $params["card_number"] . "%"];
            }
            if ($params["sex"]) {
                $where[] = ["ti.sex", "=", $params["sex"]];
            }
            if ($params["nation"]) {
                $where[] = ["ti.nation", "=", $params["nation"]];
            }
            if ($params["apply_year"]) {
                $where[] = ["ti.apply_year", "like", "{$params["apply_year"]}%"];
            }
            if ($params["phone"]) {
                $where[] = ["ti.phone", "like", "%{$params["phone"]}%"];
            }
            if ($params["email"]) {
                $where[] = ["ti.email", "like", "%{$params["email"]}%"];
            }
            if ($params["nationality"]) {
                $where[] = ["ti.nationality", "=", $params["nationality"]];
            }
            if ($params["province"]) {
                $where[] = ["ti.province", "=", $params["province"]];
            }
            if ($params["politics"]) {
                $where[] = ["ti.politics", "=", $params["politics"]];
            }
            if ($params["enterprise_id"]) {
                $where[] = ["ti.enterprise_id", "=", $params["enterprise_id"]];
            }
            if ($params["street"]) {
                $where[] = ["e.street", "=", $params["street"]];
            }
            if ($params["industry_field"]) {
                $where[] = ["e.industryFieldNew", "=", $params["industry_field"]];
            }
            if ($params["industry_field_old"]) {
                $where[] = ["e.industryFieldOld", "=", $params["industry_field_old"]];
            }
            if ($params["enterprise_tag"]) {
                $where[] = ["e.enterpriseTag", "=", $params["enterprise_tag"]];
            }
            if ($params["talent_type"]) {
                $where[] = ["ti.talent_type", "=", $params["talent_type"]];
            }
            if ($params["import_way"]) {
                $where[] = ["ti.import_way", "=", $params["import_way"]];
            }
            if ($params["highest_degree"]) {
                $where[] = ["ti.highest_degree", "=", $params["highest_degree"]];
            }
            if ($params["study_abroad"]) {
                $where[] = ["ti.study_abroad", "=", $params["study_abroad"]];
            }
            if ($params["source"]) {
                $where[] = ["ti.source", "=", $params["source"]];
            }
            if ($params["talent_arrange"]) {
                $where[] = ["ti.talent_arrange", "=", $params["talent_arrange"]];
            }
            if ($params["talent_condition"]) {
                $where[] = ["ti.talent_condition", "=", $params["talent_condition"]];
            }
            if ($params["isMatchZhiren"]) {
                $where[] = ["ti.isMatchZhiren", "=", $params["isMatchZhiren"]];
            }
            switch ($process) {
                case 1:
                    $where[] = ["ti.checkState", "in", [TalentState::FST_SUBMIT, TalentState::BASE_VERIFY_FAIL]];
                    break;
                case 2:
                    $where[] = ["ti.checkState", "in", [TalentState::BASE_VERIFY_PASS, TalentState::BASE_REVERIFY_FAIL]];
                    break;
                case 3:
                    switch ($params["checkState"]) {
                        case -1://保存未提交
                            $where[] = ["tl.new_state", "=", TalentState::SCND_SAVE];
                            $where[] = ["tl.state", "<>", TalentState::FST_VERIFY_REJECT];
                            //$where[] = ["ti.first_submit_time", "exp", Db::raw("is null")];
                            $where[] = ["ti.delete", "=", 0];
                            break;
                        case 1://待审核
                            $where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            //$where[] = ["tl.state", "in", [TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_REJECT, TalentState::REVERIFY_REJECT]];
                            $where[] = ["tl.last_state", "<>", TalentState::FST_VERIFY_REJECT];
                            break;
                        case 2://驳回
                            //$where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            $where[] = ["tl.new_state", "=", TalentState::SCND_SAVE];
                            $where[] = ["tl.state", "=", TalentState::FST_VERIFY_REJECT];
                            break;
                        case 3://通过
                            $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_PASS]];
                            break;
                        case 4://失败
                            $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_FAIL]];
                            break;
                        case 5://重新提交
                            $where[] = ["ti.checkState", "=", TalentState::SCND_SUBMIT];
                            $where[] = ["tl.last_state", "=", TalentState::FST_VERIFY_REJECT];
                            break;
                        default:
                            //$where[] = ["ti.checkState", "in", [TalentState::SCND_SUBMIT, TalentState::FST_VERIFY_FAIL]];
                            //$where[] = ["tl.new_state", "in", [TalentState::SCND_SAVE, TalentState::SCND_SUBMIT]];
                            $where[] = ["tl.state", "in", [TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_REJECT, TalentState::REVERIFY_REJECT, TalentState::FST_VERIFY_REJECT]];
                    }
                    break;
                case 4:
                    $companyId = session('user')['companyId'];
                    $company_info = CompanyApi::getOne($companyId);
                    if ($company_info["code"] == "super" || $company_info["code"] == "rsj") {
                        switch ($params["checkState"]) {
                            case 1:
                                $where[] = ["tl.state", "=", TalentState::FST_VERIFY_PASS];
                                $where[] = ["ti.pass_dept_check", "=", 0];
                                break;
                            case 2:
                                $where[] = ["tl.state", "=", TalentState::DEPT_VERIFY_REJECT];
                                break;
                            case 3:
                                $where[] = ["tl.state", "=", TalentState::DEPT_VERIFY_PASS];
                                break;
                            default:
                                $whereRaw = sprintf("(tl.state=%d and ti.pass_dept_check=0) or (tl.state=%d) or (tl.state=%d)", TalentState::FST_VERIFY_PASS, TalentState::DEPT_VERIFY_PASS, TalentState::DEPT_VERIFY_REJECT);
                                break;
                        }
                    } else {
                        switch ($params["checkState"]) {
                            case 1:
                                //待审核
                                $where[] = ["tl.active", "=", 0];
                                $where[] = ["tl.state", "=", TalentState::FST_VERIFY_PASS];
                                break;
                            case 2:
                                //已驳回
                                $where[] = ["tl.active", "=", 1];
                                $where[] = ["tl.new_state", "=", TalentState::SCND_SUBMIT];
                                break;
                            case 3:
                                //已通过
                                $where[] = ["tl.active", "=", 1];
                                $where[] = ["tl.new_state", "=", TalentState::DEPT_VERIFY_PASS];
                                break;
                            default:
                                $where = sprintf("(tl.active=0 and tl.state=%d) or (tl.active=1 and (tl.new_state in (%d,%d)))", TalentState::FST_VERIFY_PASS, TalentState::SCND_SUBMIT, TalentState::DEPT_VERIFY_PASS);
                                break;
                        }
                    }
                    break;
                case 5:
                    $whereRaw = sprintf("(tl.state in (14,15,16)) or (tl.state=12 and ti.pass_dept_check=0) or (tl.state=10 and ti.pass_dept_check=1) or (tl.state=10 and (tc.companyIds is null or tc.companyIds = ''))");
                    switch ($params["checkState"]) {
                        case 1:
                            $where[] = ["tl.state", "in", [TalentState::FST_VERIFY_PASS, TalentState::DEPT_VERIFY_PASS]];
                            break;
                        case -1:
                            $where[] = ["tl.state", "=", TalentState::REVERIFY_FAIL];
                            break;
                    }
                    break;
                case 6:
                    $where[] = ["tl.state", ">=", TalentState::REVERIFY_PASS];
                    if ($params["checkState"]) {
                        $where[] = ["tl.state", "=", $params["checkState"]];
                    }
                    break;
                case 7:
                    $where[] = ["tl.state", "=", TalentState::CERTIFICATED];
                    break;
            }
        } else {
            $whereRaw = "tl.state is not null";
            $where[] = ["ti.delete", "=", 0];
        }
        $fields[] = "ti.pass_dept_check";
        $fields[] = "tl.state";
        $fields[] = "tl.new_state";
        $fields[] = "tl.last_state";
        $fields[] = "tc.companyIds";
        if ($process == 4) {
            if ($company_info["code"] == "super" || $company_info["code"] == "rsj") {
                $list = Talent::alias("ti")
                                ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                                ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                                ->leftJoin("(select mainId,description,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                                ->where($where)
                                ->where($whereRaw)
                                ->field($fields)
                                ->select()->toArray();
            } else {
                $fields[] = "tl.active";
                $fields[] = "tl.new_state as deptCheckState";
                $list = Talent::alias("ti")
                                ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                                ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                                ->leftJoin("(select mainId,active,description,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step`=3 and companyId='{$companyId}' and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                                ->where($where)
                                ->whereRaw("find_in_set(:companyId,companyIds)", ["companyId" => $companyId])
                                ->field($fields)
                                ->select()->toArray();
            }
        } else if ($process == 5) {
            $list = Talent::alias("ti")
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                            ->leftJoin("(select description,mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("new_talent_checklog tl", "tl.mainId=ti.id and tl.id=(select id from new_talent_checklog where mainId=ti.id and `step` is null and active=1 and typeFileId is null order by createTime desc limit 1)")
                            ->whereRaw($whereRaw)
                            ->where($where)
                            ->field($fields)
                            ->select()->toArray();
        } else {
            $list = Talent::alias("ti")
                            ->field($fields)
                            ->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")
                            ->leftJoin("new_talent_condition tc", "tc.id=ti.talent_condition")
                            ->leftJoin("(select description,mainId,last_state,new_state,state,createTime from new_talent_checklog where md5(concat(createTime,mainId,`type`)) in (select md5(concat(max(createTime),mainId,`type`)) from `new_talent_checklog` where `type`=1 and `step` is null and active=1 and typeFileId is null group by mainId,`type`)) tl", "`tl`.`mainId`=ti.id")
                            //->leftJoin("new_talent_checklog tl", "tl.mainId=ti.id and tl.id=(select id from new_talent_checklog where mainId=ti.id and `step` is null and active=1 and typeFileId is null order by createTime desc limit 1)")
                            ->where($where)
                            ->where($whereRaw)
                            ->select()->toArray();
        }
        $un_common_location = Db::table("un_common_location")->column('name', 'code');
        foreach ($list as &$item) {
            $item["card_type"] = $cardTypes[$item["card_type"]];
            $item["industryFieldNew"] = $industry_fields[$item["industryFieldNew"]];
            $item["enterpriseTag"] = $enterpriseTags[$item["enterpriseTag"]];
            $item["street"] = $streets[$item["street"]];
            $item["nation"] = $nations[$item["nation"]];
            $item["nationality"] = $nationalitys[$item["nationality"]];
            $item["politics"] = $politics[$item["politics"]];
            $item["talent_type"] = $talentTypes[$item["talent_type"]];
            $item["talent_arrange_category"] = $talentArrangeCategories[$item["talentLevelCat"]];
            $item["sex"] = $sex[$item["sex"]];
            $item["highest_degree"] = $highest_degree[$item["highest_degree"]];
            $item["import_way"] = $import_way[$item["import_way"]];
            $item["salary_pay_way"] = $salary_pay_way[$item["salary_pay_way"]];
            $item["pre_import_type"] = $pre_import_type[$item["pre_import_type"]];
            $item["return"] = $return[$item["return"]];
            $item["isMatchZhiren"] = $isMatchZhiren[$item["isMatchZhiren"]];
            $item["study_abroad"] = $study_abroad[$item["study_abroad"]];
            $item["source"] = $source[$item["source"]];
            $item["talent_arrange"] = $talent_arrange[$item["talent_arrange"]];
            $item["talent_condition"] = $item["talentConditionName"];

            if (in_array("source_city", $exportFields)) {
                $item["source_city"] = $un_common_location[$item["source_city"]];
            }
            if (in_array("source_county", $exportFields)) {
                $item["source_county"] = $un_common_location[$item["source_county"]];
            }
            if (in_array("province", $exportFields)) {
                $item["province"] = $un_common_location[$item["province"]];
            }
            if (in_array("city", $exportFields)) {
                $item["city"] = $un_common_location[$item["city"]];
            }
            if (in_array("county", $exportFields)) {
                $item["county"] = $un_common_location[$item["county"]];
            }
            if ($item["state"] == TalentState::SCND_SUBMIT) {
                if ($item["last_state"] == TalentState::FST_VERIFY_REJECT) {
                    $item["checkState"] = "待初审（重新提交）";
                } else {
                    $item["checkState"] = "待初审（首次提交）";
                }
            } else if ($item["state"] == TalentState::FST_VERIFY_PASS) {
                if ($item["deptCheckState"]) {
                    if ($item["acitve"] == 0 && $item["state"] == TalentState::FST_VERIFY_PASS) {
                        $item["checkState"] = "待部门审核";
                    }
                    if ($item["active"] == 1 && $item["new_state"] == TalentState::DEPT_VERIFY_PASS) {
                        $item["checkState"] = "部门审核通过";
                    }
                    if ($item["active"] == 1 && $item["new_state"] == TalentState::SCND_SUBMIT) {
                        $item["checkState"] = "部门审核驳回";
                    }
                } else {
                    if (!$item["companyIds"] || $item["pass_dept_check"] == 1) {
                        $item["checkState"] = "待复核（初审通过）";
                    }
                    if ($item["pass_dept_check"] == 0) {
                        $item["checkState"] = "待部门并审";
                    }
                }
            } else if ($item["state"] == TalentState::DEPT_VERIFY_REJECT) {
                $item["checkState"] = "待初审（部门并审驳回）";
            } else if ($item["state"] == TalentState::DEPT_VERIFY_PASS) {
                $item["checkState"] = "待复核（部门并审通过）";
            } else {
                $item["checkState"] = TalentState::getStateName($item["state"]);
            }
        }unset($item);
        return $list;
    }

    public static function getListByProcess($process) {
        switch ($process) {
            case 1:
                $where[] = ["ti.checkState", "in", [TalentState::FST_SUBMIT, TalentState::BASE_VERIFY_FAIL]];
                break;
            case 2:
                $where[] = ["ti.checkState", "in", [TalentState::BASE_VERIFY_PASS, TalentState::BASE_REVERIFY_FAIL]];
                break;
            case 3:
                $where[] = ["ti.checkState", "in", [TalentState::SCND_SUBMIT, TalentState::FST_VERIFY_FAIL]];
                break;
            default:
                return null;
        }
        return Talent::alias("ti")->leftJoin("un_enterprise e", "e.id=ti.enterprise_id")->where($where)->field("ti.*,e.agentName,e.agentPhone")->select()->toArray();
    }

    public static function setPublic($mainId, $state, $msg, $batch = null, $type = 1) {
        $data["id"] = $mainId;
        $data["checkState"] = $state;
        switch ($state) {
            case TalentState::ZX_PASS:
            case TalentState::ZX_FAIL:
                $data["isPublic"] = 2;
                break;
            case TalentState::ANNOUNCED:
                $data["isPublic"] = 3;
                $data["publicBatch"] = $batch;
                break;
            case TalentState::ANNOUNCED_REVERIFY_PASS:
            case TalentState::ANNOUNCED_REVERIFY_FAIL:
                $data["isPublic"] = 3;
                break;
            case TalentState::PUBLISH_PASS:
                $data["isPublic"] = 4;
                $data["identifyMonth"] = $batch;
                $data["certificateGetTime"] = $batch; //时间待定
                $data["certificateExpireTime"] = date("Y-m-d", strtotime(sprintf("%s +6 years -1 days", $batch))); //时间待定
                break;
            case TalentState::PUBLISH_FAIL:
                $data["isPublic"] = 4;
                break;
        }
        if (Talent::update($data)) {
            TalentLogApi::write($type, $mainId, $state, $msg, 1);
            return true;
        }
        return false;
    }

}