import re from typing import Tuple import datetime import arrow import sqlalchemy as sa import json from fastapi import Depends import pandas as pd import numpy as np from utils.func import get_uid from sqlalchemy import func, or_, and_, not_ from copy import deepcopy import crud import schemas from dateutil import parser from core.config import settings from db import get_database from db.redisdb import get_redis_pool, RedisDrive from models.user_label import UserClusterDef from utils import get_week, strptime, start_end_month, strptime1 # 面试数据ck增删改查 class InterviewDo: def __init__(self, query_in: schemas.InterviewQuery): self.query_in = query_in self.find_column = set() self.data_in = {} self.where = {} self.data = {} self.out_form = '' async def init(self, *args, **kwargs): self.find_column = self.query_in.find_column self.data_in = self.query_in.data_in self.where = self.query_in.interview_query if 'out_form' in self.where: out_form = self.where.pop('out_form') self.out_form = out_form self.data = self.query_in.data def handler_filts(self, where): """ :param filters: (filts:list,relation:str) :param g_f: :param relation: :return: """ filters = [] for col, value in where.items(): # 以下值跳过 if col in ['time', 'start_time', 'end_time']: continue # 最大值 if col in ['max_interview_stage', 'max_education']: key = col.split('_', 1)[-1] filters.append(key <= value) continue # 最小值 if col in ['min_interview_stage', 'min_education']: key = col.split('_', 1)[-1] filters.append(key >= value) continue # 相等 filters.append(col == value) return filters # 插入面试数据 def insert_interview_sql(self): # 简历初始文档 data_mode = { "interview_name": "", "interview_type": 1, "interview_sign": 0, "hope_money": "", "feedback": 0, "interview_round": 0, "event_time": "", "name": "", "phone": "", "job_name": "", "hr_name": "", "work_exp": 0, "interview_stage": 1, "owner_name": 2, "education": 1, "work_undergo": [], "project_undergo": [], "work_list": [], "school": "", "at_school": "", "specialty": "", "specialty_do": [], "mmended_state": 0, "mail": "", "account": "", "id_card": "", "gender": "", "age": 0, "gam": "", "interview_state": 1, "counts": 1, "nation": "汉", "review": [], "upgrade": [], "come_time": "", "now_money": "", "men_state": 1, "teacher_state": 1, "teacher_back": 1, "offer_state": 1, "offer_exam_state": 1, "notice_state": 1, "pass_why": 0, "pass_text": [], "language": [], "remembrance": [], "birthday": '', "file_url": '' } insert_data = [] res_data = [] if isinstance(self.data_in, dict): insert_data = [self.data_in] if isinstance(self.data_in, list): insert_data = self.data_in sql = f"insert into HR.resumes(interview_name, interview_type, interview_sign, hope_money, feedback," \ f" interview_round, event_time, uid, name, phone, job_name, hr_name, work_exp, interview_stage, owner_name," \ f" education, work_undergo, project_undergo, work_list, school, at_school, specialty, specialty_do, " \ f"mmended_state, mail, account, id_card, gender, age, gam, interview_state, counts, nation, come_time," \ f" review, upgrade, now_money, men_state, teacher_state, teacher_back, offer_state, offer_exam_state," \ f" notice_state, pass_why, pass_text,language,remembrance,birthday, file_url) values" now_time = datetime.datetime.now() for data in insert_data: s1 = data['event_time'] s2 = data['hope_money'] s3 = data['now_money'] if not s1: data['event_time'] = now_time else: data['event_time'] = parser.parse(s1) if s2 and isinstance(s2, int): data['hope_money'] = str(s2) if s3 and isinstance(s3, int): data['now_money'] = str(s3) data_true = deepcopy(data) data_true['uid'] = get_uid() education = data_true['education'] # 学历int转化 education_int = { '大专': 1, '本科': 2, '研究生': 3, '博士': 4, '硕士': 5, } if education and isinstance(education, str): data_true['education'] = education_int.get(education, 1) age = data_true['age'] # 年龄int转化 if age and isinstance(age, str): true_age = re.search(r"\d+\.?\d*", age) if len(true_age.group()) > 2: data_true['age'] = 20 else: data_true['age'] = int(true_age.group()) work_exp = data_true['work_exp'] # 工作经验float转化 if not work_exp: data_true['work_exp'] = 0 if work_exp and isinstance(work_exp, str): true_work_exp = re.search(r"\d+\.?\d*", work_exp) if len(true_work_exp.group()) > 3: data_true['work_exp'] = 0.0 else: data_true['work_exp'] = float(true_work_exp.group()) append_data = deepcopy(data_mode) append_data.update(data_true) res_data.append(append_data) print(sql) return { 'sql': sql, 'insert_data': res_data } # 更新面试数据sql def update_interview_sql(self): updateStr = '' whereStr = '' # 转json字符串 if 'remembrance_list' in self.data_in: remembrance = self.data_in.pop('remembrance_list') self.data_in['remembrance'] = remembrance if 'language_list' in self.data_in: language = self.data_in.pop('language_list') self.data_in['language'] = language if 'project_undergo' in self.data_in: if self.data_in.get('project_undergo', []): self.data_in['project_undergo'] = [json.dumps(i) for i in self.data_in['project_undergo']] else: self.data_in['project_undergo'] = [] if 'work_list' in self.data_in: if self.data_in.get('work_list', []): self.data_in['work_list'] = [json.dumps(i) for i in self.data_in['work_list']] else: self.data_in['work_list'] = [] if 'language' in self.data_in: if self.data_in.get('language', []): self.data_in['language'] = [json.dumps(i) for i in self.data_in['language']] else: self.data_in['language'] = [] if 'remembrance' in self.data_in: if self.data_in.get('remembrance', []): self.data_in['remembrance'] = [json.dumps(i) for i in self.data_in['remembrance']] else: self.data_in['remembrance'] = [] # 字符串转datetime if self.data_in.get('in_time', ''): chk_in_time = self.data_in['in_time'].replace('-', '/').replace('.', '/') if len(chk_in_time.split('/')) == 2: self.data_in['in_time'] = str(datetime.datetime.strptime(chk_in_time, "%Y/%m").date()) if len(chk_in_time.split('/')) == 3: self.data_in['in_time'] = str(datetime.datetime.strptime(chk_in_time, "%Y/%m/%d").date()) if self.data_in.get('out_time', ''): chk_out_time = self.data_in['out_time'].replace('-', '/').replace('.', '/') if len(chk_out_time.split('/')) == 2: self.data_in['out_time'] = str(datetime.datetime.strptime(chk_out_time, "%Y/%m").date()) if len(chk_out_time.split('/')) == 3: self.data_in['out_time'] = str(datetime.datetime.strptime(chk_out_time, "%Y/%m/%d").date()) if self.data_in.get('birthday', ''): chk_birthday = self.data_in['birthday'].replace('-', '/').replace('.', '/') if len(chk_birthday.split('/')) == 2: self.data_in['birthday'] = str(datetime.datetime.strptime(chk_birthday, "%Y/%m").date()) if len(chk_birthday.split('/')) == 3: self.data_in['birthday'] = str(datetime.datetime.strptime(chk_birthday, "%Y/%m/%d").date()) if self.data_in.get('star_time', ''): chk_star_time = self.data_in['star_time'].replace('-', '/').replace('.', '/') if len(chk_star_time.split('/')) == 2: self.data_in['star_time'] = str(datetime.datetime.strptime(chk_star_time, "%Y/%m").date()) if len(chk_star_time.split('/')) == 3: self.data_in['star_time'] = str(datetime.datetime.strptime(chk_star_time, "%Y/%m/%d").date()) if self.data_in.get('end_time', ''): chk_end_time = self.data_in['end_time'].replace('-', '/').replace('.', '/') if len(chk_end_time.split('/')) == 2: self.data_in['end_time'] = str(datetime.datetime.strptime(chk_end_time, "%Y/%m").date()) if len(chk_end_time.split('/')) == 3: self.data_in['end_time'] = str(datetime.datetime.strptime(chk_end_time, "%Y/%m/%d").date()) if self.data_in.get('graduate_time', ''): chk_graduate = self.data_in['graduate_time'].replace('-', '/').replace('.', '/') if len(chk_graduate.split('/')) == 2: self.data_in['graduate_time'] = str(datetime.datetime.strptime(chk_graduate, "%Y/%m").date()) if len(chk_graduate.split('/')) == 3: self.data_in['graduate_time'] = str(datetime.datetime.strptime(chk_graduate, "%Y/%m/%d").date()) for key, value in self.data_in.items(): if key == 'uid': continue if value == None: continue if value == '': continue if updateStr: updateStr += ',' if isinstance(value, str): updateStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue updateStr += str(key) + ' = ' + str(value) + ' ' if 'uid' in self.data_in: self.where.update({ 'uid': self.data_in['uid'] }) for key, value in self.where.items(): if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' updateStr.strip() whereStr.strip() sql = f"alter table HR.resumes update {updateStr} where {whereStr}" print(sql) return { 'sql': sql, } def find_interview_sql(self): whereStr = '' findStr = '' if not self.find_column: findStr = '*' else: for fstr in self.find_column: findStr += fstr + ', ' for key, value in self.where.items(): if key in ['interview_id', 'hr_id', 'interview_name', 'hr_name','department']: # 多个条件 if ',' in value: values = value.split(',') for index, i in enumerate(values): if index > 0: whereStr += 'or ' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: if whereStr: whereStr += 'and ' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: whereStr += str(key) + ' like ' + "'%" + i + "%'" + ' ' continue if value == '': continue # 单个条件 if whereStr: whereStr += 'and ' + str(key) + ' like ' + "'%" + value + "%'" + ' ' else: whereStr += str(key) + ' like ' + "'%" + value + "%'" + ' ' continue if key == 'hr_manner': if value == '': if whereStr: whereStr += 'and ' + '(' + str(key) + ' = ' + str(0) + ' ' else: whereStr += str(key) + ' = ' + str(0) + ' ' whereStr += 'or ' + str(key) + ' = ' + str(1) + ') ' if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql, } # 包含所有查询 def find_interview_every_sql(self): whereStr = '' findStr = '' if not self.find_column: findStr = 'hr_manner' else: for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if key in ['interview_id', 'hr_id', 'interview_name', 'hr_name', 'department']: # 多个条件 if ',' in value: values = value.split(',') bast_index = len(values) - 1 for index, i in enumerate(values): if index > 0: if index == bast_index: whereStr += 'or ' + str(key) + ' like ' + "'%" + i + "%'" + ') ' else: whereStr += 'or ' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: if whereStr: if bast_index > 0: whereStr += 'and (' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: whereStr += 'and ' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: if bast_index > 0: whereStr += '(' + str(key) + ' like ' + "'%" + i + "%'" + ' ' else: whereStr += str(key) + ' like ' + "'%" + i + "%'" + ' ' continue if value == '': continue # 单个条件 if whereStr: whereStr += 'and ' + str(key) + ' like ' + "'%" + value + "%'" + ' ' else: whereStr += str(key) + ' like ' + "'%" + value + "%'" + ' ' continue if key == 'hr_manner': if value == '': if whereStr: whereStr += 'and (' + str(key) + ' = ' + str(0) + ' ' else: whereStr += '(' + str(key) + ' = ' + str(0) + ' ' whereStr += 'or ' + str(key) + ' = ' + str(1) + ') ' if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') sql = f"select {findStr} from HR.resumes where {whereStr}" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" else: if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql, } # 主页初筛查询 def find_interview_home_sql(self): self.where.update({'interview_stage': 1}) whereStr = '' findStr = '' # 主页查询字段 self.find_column = ["uid", "age", "gender", "at_school", "name", "event_time", "owner_name", "education", "school", "specialty", "interview_name", "mmended_state", "work_list", "work_exp", "notice_state", "job_name", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' for key, value in self.where.items(): if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') sql = f"select {findStr} from HR.resumes where {whereStr}" print(sql) return { 'sql': sql, } # 复筛查询 def find_interview_screen_sql(self): self.where.update({'interview_stage': 2}) whereStr = '' findStr = '' # 主页查询字段 self.find_column = ["uid", "age", "gender", "at_school", "name", "event_time", "owner_name", "education", "school", "specialty", "interview_name", "mmended_state", "work_list", "work_exp", "notice_state", "job_name", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' for key, value in self.where.items(): if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') sql = f"select {findStr} from HR.resumes where {whereStr}" print(sql) return { 'sql': sql, } # 面试阶段查询 def find_interview_exam_sql(self): self.where.update({'interview_stage': 3}) whereStr = '' findStr = '' # 面试阶段查询字段 self.find_column = ["uid", "age", "gender", "at_school", "name", "event_time", "owner_name", "education", "school", "specialty", "interview_name", "mmended_state", "work_list", "work_exp", "job_name", "feedback", "interview_round", "interview_state", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' for key, value in self.where.items(): if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') sql = f"select {findStr} from HR.resumes where {whereStr}" print(sql) return { 'sql': sql, } # offer阶段查询 def find_interview_offer_sql(self): self.where.update({'interview_stage': 4}) whereStr = '' findStr = '' # 面试阶段查询字段 self.find_column = ["uid", "age", "gender", "at_school", "name", "event_time", "owner_name", "education", "school", "specialty", "interview_name", "mmended_state", "work_list", "work_exp", "job_name", "feedback", "interview_round", "interview_state", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' for key, value in self.where.items(): if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') sql = f"select {findStr} from HR.resumes where {whereStr}" print(sql) return { 'sql': sql, } # 各阶段数量查询 def find_stage_num_sql(self): findStr = '' # 面试阶段查询字段 self.find_column = ["interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' findStr = findStr.strip().strip(',') sql = f"select {findStr},count(*) as value from HR.resumes group by interview_stage" print(sql) return { 'sql': sql, } # 待入职阶段查询 def find_interview_waite_in_sql(self): whereStr = '' findStr = '' # 面试阶段查询字段 self.find_column = ["uid", "age", "gender", "at_school", "name", "event_time", "owner_name", "education", "school", "specialty", "interview_name", "mmended_state", "work_list", "work_exp", "graduate_time", "job_name", "feedback", "interview_round", "interview_state", "interview_stage", "pass_why", "pass_text","teacher_state"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') sql = f"select {findStr} from HR.resumes where {whereStr}" print(sql) return { 'sql': sql, } # 分组报表候选人质量查询 def get_man_mass_form_sql(self): whereStr = '' findStr = '' # 查询字段 self.find_column = ["uid", "age", "gender", "name", "education", "owner_name", "school", "work_exp", "job_name", "account", "work_list"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') sql = f"select {findStr} from HR.resumes where {whereStr}" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 分组报表候选人明细查询 def get_man_info_form_sql(self): whereStr = '' findStr = '' # 查询字段 self.find_column = ["uid", "age", "gender", "name", "education", "school", "work_exp", "job_name", "account", "work_list", "graduate_time", "phone"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 渠道质量sql def get_owner_form_sql(self): findStr = '' whereStr = '' # 查询字段 self.find_column = ["name", "owner_name", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 初筛渠道质量sql def get_owner_stage1_form_sql(self): findStr = '' whereStr = '' # 查询字段 self.find_column = ["name", "owner_name", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 self.where.update({ 'interview_stage': 1 }) if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 阶段数据sql def get_every_stage_form_sql(self, job_ids): findStr = '' whereStr = '' # 查询字段 self.find_column = ["name", "interview_stage", "job_id"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr} and job_id in {job_ids}" else: sql = f"select {findStr} from HR.resumes where job_id in {job_ids}" if start_time: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr} and job_id in {job_ids}" else: sql = f"select {findStr} from HR.resumes where job_id in {job_ids}" print(sql) return { 'sql': sql } # hr工作量sql def get_hr_works_form_sql(self): findStr = '' whereStr = '' # 查询字段 self.find_column = ["name", "interview_stage", "job_id", 'hr_name'] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 招聘趋势分析sql def get_trend_form_sql(self): whereStr = '' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() # 有日期条件 start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if is_date: if whereStr: sql = f"select toDate(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes where {whereStr}" else: sql = f"select toDate(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select toDate(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes where {whereStr}" else: sql = f"select toDate(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes" sql += f" group by date, interview_stage" print(sql) return { 'sql': sql, 'sdate': start_time, 'edate': end_time, } # 渠道效果sql def get_owner_effect_sql(self): findStr = '' whereStr = '' # 查询字段 self.find_column = ["name", "owner_name", "interview_stage"] for fstr in self.find_column: findStr += fstr + ', ' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() findStr = findStr.strip().strip(',') # 有日期条件 if is_date: start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {findStr} from HR.resumes where {whereStr}" else: sql = f"select {findStr} from HR.resumes" print(sql) return { 'sql': sql } # 年度招聘数据sql def get_year_form_sql(self): whereStr = '' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() # 有日期条件 start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') if is_date: if whereStr: sql = f"select toStartOfMonth(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes where {whereStr}" else: sql = f"select toStartOfMonth(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select toStartOfMonth(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes where {whereStr}" else: sql = f"select toStartOfMonth(addHours(`event_time`, 0)) as date,interview_stage, count() as value from HR.resumes" sql += f" group by date, interview_stage" print(sql) return { 'sql': sql, 'sdate': start_time, 'edate': end_time, } # 年度招聘数据sql def year_job_form_sql(self): whereStr = '' is_date = 0 if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time', 'chk_type']: is_date = 1 continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() # 有日期条件 start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') chk_type = self.where.get('chk_type', 'day') if chk_type == 'day': select_str = f"toDate(addHours(`event_time`, 0))" if chk_type == 'week': select_str = f"toStartOfWeek(addHours(`event_time`, 0))" if chk_type == 'month': select_str = f"toStartOfMonth(addHours(`event_time`, 0))" if is_date: if whereStr: sql = f"select {select_str} as date,interview_stage, job_id, count() as value from HR.resumes where {whereStr}" else: sql = f"select {select_str} as date,interview_stage, job_id, count() as value from HR.resumes" if start_time or end_time: sql += f" where" if start_time: if whereStr: sql += f" and toDate(addHours(`event_time`, 0)) >= '{start_time}'" else: sql += f" toDate(addHours(`event_time`, 0)) >= '{start_time}'" if end_time: sql += f" and toDate(addHours(`event_time`, 0)) <= '{end_time}'" # 没有日期条件 else: if whereStr: sql = f"select {select_str} as date,interview_stage, job_id, count() as value from HR.resumes where {whereStr}" else: sql = f"select {select_str} as date,interview_stage, job_id, count() as value from HR.resumes" sql += f" group by date, interview_stage, job_id" print(sql) return { 'sql': sql, 'sdate': start_time, 'edate': end_time, 'chk_type': chk_type, } # 年度入离值数据sql def year_in_out_sql(self): whereStr = '' if self.where: for key, value in self.where.items(): if key in ['start_time', 'end_time']: continue if isinstance(value, str): if not value.strip(): continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + "'" + value + "'" + ' ' else: whereStr += str(key) + ' = ' + "'" + value + "'" + ' ' continue if whereStr: whereStr += 'and ' + str(key) + ' = ' + str(value) + ' ' else: whereStr += str(key) + ' = ' + str(value) + ' ' whereStr = whereStr.strip() # 有日期条件 start_time = self.where.get('start_time', '') end_time = self.where.get('end_time', '') select_in_str = f"toStartOfMonth(addHours(`in_time`, 0))" select_out_str = f"toStartOfMonth(addHours(`out_time`, 0))" if whereStr: sql_in = f"select {select_in_str} as date, count() as value from HR.resumes where {whereStr}" sql_out = f"select {select_out_str} as date, count() as value from HR.resumes where {whereStr}" else: sql_in = f"select {select_in_str} as date, count() as value from HR.resumes" sql_out = f"select {select_out_str} as date, count() as value from HR.resumes" if start_time or end_time: sql_in += f" where" sql_out += f" where" if start_time: if whereStr: sql_in += f" and toDate(addHours(`in_time`, 0)) >= '{start_time}'" sql_out += f" and toDate(addHours(`out_time`, 0)) >= '{start_time}'" else: sql_in += f" toDate(addHours(`in_time`, 0)) >= '{start_time}'" sql_out += f" toDate(addHours(`out_time`, 0)) >= '{start_time}'" if end_time: sql_in += f" and toDate(addHours(`in_time`, 0)) <= '{end_time}'" sql_out += f" and toDate(addHours(`out_time`, 0)) <= '{end_time}'" sql_in += f" group by date" sql_out += f" group by date" print(sql_in, sql_out) return { 'sql_in': sql_in, 'sql_out': sql_out, 'sdate': start_time, 'edate': end_time, } if __name__ == '__main__': col = 'max_interview_stage' if col in ['max_interview_stage', 'max_education']: key = col.split('_', 1)[-1] print(key)