def add_function(project_id, parent_function_id, function_name): # new function id: # 1. last_function_id = max function_id starts with parent_function_id # 2. new_function_id = last_function_id + 1, 3 digits, each digit: 012……789abc……xyzABC……XYZ # worker_id not null sql = f'''select distinct max(id) from project_function where project_id=\'{project_id}\' and parent_function_id=\'{parent_function_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) last_function_id = db.selectDB(sql)[0]['max(id)'] if last_function_id is None: last_function_id = '000' three_id = list(last_function_id[-3:]) add(three_id, 2) if parent_function_id == '000': new_function_id = three_id[0] + three_id[1] + three_id[2] else: new_function_id = parent_function_id + three_id[0] + three_id[ 1] + three_id[2] sql = f'''insert into project_function(id, function_name, project_id, parent_function_id, function_status, delete_label) values(\'{new_function_id}\', \'{function_name}\', \'{project_id}\', \'{parent_function_id}\', 0, 0);''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.otherDB(sql)
def get_risk(project_id): sql = f'''select `id`, risk_type as type, risk_describe as `describe`, risk_level as level, risk_effect as effect, risk_solve as solve, risk_status as status, risk_rate as rate, risk_duty_id as duty_id, risk_follower_id as follower_id from project_risk where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if res == 'Empty': return [] for i in res: sql = f'''select name from employee where id=\'{i['duty_id']}\'''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) name = db.selectDB(sql)[0]['name'] i['duty_name'] = name sql = f'''select name from employee where id=\'{i['follower_id']}\'''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) name = db.selectDB(sql)[0]['name'] i['follower_name'] = name return res
def get_total_user(project_id=None): # total user in company sql = f'''select distinct id, name from employee;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if project_id is None: return res sql = f'''select distinct person_id from project_participant where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) worker_in = db.selectDB(sql) if worker_in == 'Empty': worker_in = [] w_list = [] for i in worker_in: w_list.append(i['person_id']) for i in res: if i['id'] in w_list: i['status'] = 1 else : i['status'] = 0 return res
def get_project_member(project_id, function_id=None): sql = f'''select distinct employee.id as worker_id, employee.name as worker_name from employee join project_participant on employee.id=project_participant.person_id where project_participant.project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) worker = db.selectDB(sql) if function_id is None: return worker if worker != 'Empty' else [] sql = f'''select distinct worker_id from function_partition where function_id=\'{function_id}\' and project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) worker_in_func = db.selectDB(sql) worker_list = [] if worker_in_func != 'Empty': for i in worker_in_func: worker_list.append(i['worker_id']) for i in worker: if i['worker_id'] in worker_list: i['status'] = 1 else: i['status'] = 0 return worker
def get_total_user(project_id=None): p = {} p['select_key'] = ['id', 'name'] p['tablename'] = 'employee' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(d.selectSql(p)) # return total user in company if project_id is not None: # status: if user in project # return uid, name, status p['select_key'] = ['person_id'] p['tablename'] = 'project_participant' p['key'] = ['project_id'] p['value'] = [' = ' + project_id] db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) worker_in = db.selectDB(d.selectSql(p)) w_list = [] for i in worker_in: w_list.append(i['person_id']) for i in res: if i['id'] in w_list: i['status'] = 1 else: i['status'] = 0 return res
def get_normal_account(): # return username, name, career, department # login:id | username | password # employee:id | name | gender | career|superior_id | tele | department| mailbox sql = '''select login.username,employee.name,employee.career,employee.department from login join employee on login.id = employee.id;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) for record in res: if record['career'] == None: # leader or worker sql = f'''select 2 from project_participant join login on login.id = project_participant.leader_id where login.username = '******'username']}';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) if db.selectDB(sql) == 'Empty': record['career'] = 'worker' else: record['career'] = 'leader' return res
def create_super_account(username, password): # return 0/1 (ok, username already exist) sql = f'''select username from super_login where username = '******';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) if db.selectDB(sql) != 'Empty': return 1 sql = f'''insert into super_login(username,password) values('{username}','{password}');''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) return 0
def add_project_member(project_id, uid, leader_id): # add in project_participant sql = f'''insert into project_participant values(\'{project_id}\', \'{uid}\', \'{leader_id}\');''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) if db.otherDB(sql) != 'ok': return 'error' # add in authority sql = f'''insert into authority values(\'{project_id}\', \'{uid}\', 0, 0, 0);''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return res if res != 'none' else 'error'
def add_equipment(project_id, name, manager, start_time, end_time, status): # get max id in db sql = f'''select max(id) from project_equipment where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) new_id = db.selectDB(sql)[0]['max(id)'] new_id = 0 if new_id is None else int(new_id) new_id += 1 # insert sql = f'''insert into project_equipment values(\'{new_id}\', \'{project_id}\', \'{name}\', \'{start_time}\', \'{end_time}\', {status}, 0, null, \'{manager}\');''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.otherDB(sql)
def add_flaw(project_id, describe, level, follower): # get max id in db sql = f'''select max(id) from project_flaw where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) new_id = db.selectDB(sql)[0]['max(id)'] new_id = 0 if new_id is None else int(new_id) new_id += 1 # insert sql = f'''insert into project_flaw values(\'{new_id}\', \'{project_id}\', \'{describe}\', \'{level}\', \'{follower}\', 0);''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.otherDB(sql)
def modify_authority(project_id, uid, git_authority, file_authority, mail_authority): sql = f'''update authority set git_authority=\'{git_authority}\', file_authority=\'{file_authority}\', mail_authority=\'{mail_authority}\' where project_id=\'{project_id}\' and worker_id=\'{uid}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return 'ok'
def add_function_member(project_id, function_id, worker_id): sql = f'''insert into function_partition values(\'{project_id}\', \'{function_id}\', \'{worker_id}\')''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return 'ok'
def get_info_by_uid(uid, is_superior=False, include_finished=False): # is_superior=True: uid是上级的uid,要获取他所有项目下级的工时 # is_superior=False: uid是自己的uid,获取自己所有工时 # include_finished: whether return approved part if is_superior: sql = f''' select distinct work_time.id, employee.name as worker_name, project_function.function_name, work_time.event_name, work_time.start_time, work_time.end_time, project.name as project_name from work_time join employee on work_time.worker_id=employee.id join project_function on work_time.function_id=project_function.id join project_participant on project_participant.person_id=work_time.worker_id join project on project.id = work_time.project_id where work_time.delete_label=0 and project_participant.leader_id=\'{uid}\';''' else: sql = f''' select distinct work_time.id, employee.name as worker_name, project_function.function_name, work_time.event_name, work_time.start_time, work_time.end_time, work_time.status, project.name as project_name from work_time join employee on work_time.worker_id=employee.id join project_function on work_time.function_id=project_function.id join project on project.id = work_time.project_id where work_time.delete_label=0 and work_time.worker_id=\'{uid}\';''' if not include_finished: sql = sql[:-1] + ' and work_time.status=1;' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) return res if res != 'Empty' else []
def repush(project_id): # check status == 0 (rejection), return 'error' if not # set status to 1 (pending) sql = f'''update project set status=1 where status = 0 and id = '{project_id}';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return 'error' if db.otherDB(sql) == 'none' else 'ok'
def get_info_by_work_time_id(work_time_id): para_dict = {} para_dict['select_key'] = [ 'work_time.id', 'employee.name', 'project_function.function_name', 'work_time.event_name', 'work_time.start_time', 'work_time.end_time', 'work_time.delete_label', 'project.name as project_name' ] para_dict['select_value'] = [] para_dict['tablename'] = 'work_time' para_dict['key'] = ['work_time.id', 'work_time.delete_label'] work_time_id = '=' + work_time_id para_dict['value'] = [work_time_id, '!=1'] para_dict['join_tablename'] = ['employee', 'project_function', 'project'] para_dict['on_key'] = [ 'employee.id', 'project_function.id', 'work_time.project_id' ] para_dict['on_value'] = [ 'work_time.worker_id', 'work_time.function_id', 'project.id' ] db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(d.selectSql(para_dict)) if res == 'Empty': return 'error' return res
def delete_function(project_id, function_id): # check if delete_label == 0, return 'error' if delete_label == 1 sql = f'''select distinct delete_label from project_function where project_id=\'{project_id}\' and id=\'{function_id}\' and delete_label=0;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if res == 'Empty': return 'error' # 1. set delete_label to 1 # 2. set children functions' delete_label to 1 (children function: function id starts with function_id) # children function is parent of grandson function.when children function canceled,children of children function should also canceled sql = f'''update project_function set delete_label=1 where project_id=\'{project_id}\' and id like \'{function_id}%\' ;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.otherDB(sql)
def delete_function_member(project_id, function_id, worker_id): sql = f'''delete from function_partition where project_id=\'{project_id}\' and function_id=\'{function_id}\' and worker_id=\'{worker_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return 'ok'
def modify_flaw(project_id, id, describe, level, follower, status): sql = f'''update project_flaw set `describe`=\'{describe}\', level=\'{level}\', follower_id=\'{follower}\', status=\'{status}\' where `id`=\'{id}\' and project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return 'ok'
def get_custom(): # return all custom # return (custom_id, company_name) p = {} p['select_key'] = ['id as custom_id','company_name'] p['tablename'] = 'customer' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.selectDB(d.selectSql(p))
def modify_function(project_id, function_id, function_name): sql = f'''update project_function set function_name=\'{function_name}\' where id=\'{function_id}\' and project_id=\'{project_id}\'''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) return 'ok'
def add_risk(project_id, id, risk_type, describe, level, effect, solve, duty, rate, follower): # get max id in db sql = f'''select max(id) from project_risk where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) new_id = db.selectDB(sql)[0]['max(id)'] new_id = 0 if new_id is None else int(new_id) new_id += 1 # insert sql = f'''insert into project_risk values(\'{new_id}\', \'{project_id}\', \'{risk_type}\', \'{describe}\', \'{level}\', \'{effect}\', 0, \'{duty}\', \'{rate}\', \'{follower}\', \'{solve}\');''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return db.otherDB(sql)
def delete(work_time_id): sql = f'select delete_label from work_time where id = {work_time_id};' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if res == 'Empty': # can't find work_time_id return 'error' if res[0]['delete_label'] == '1': # already delete return 'ok' sql = f'update work_time set delete_label = 1 where id = {work_time_id};' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) return res
def login_super(username, password): # super account login # return 0/1 (ok/fail) sql = f'''select username from super_login where username = '******' and password = '******';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) return 1 if db.selectDB(sql) == 'Empty' else 0
def confirm(project_id, status): # check if project status is 1 (pending), return 'error' if not sql = f'select status from project where id=\'{project_id}\';' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if res[0]['status'] != 1: return 'error' # modify project status, from 1 to 0/2 (rejection/established) sql = f'update project set status=\'{status}\' where id=\'{project_id}\';' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.otherDB(sql) if res == 'ok': return 'ok' else: return 'error'
def get_info_include_work_time(uid, project_id): sql = f'''select distinct project.id, project.name, project.status, project.update_time, work_time.remain as remain_work_time from project join work_time on work_time.project_id=project.id where work_time.id in (select max(id) from work_time where worker_id=\'{uid}\' and project.id=\'{project_id}\' group by project_id);''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) return res[0]['remain_work_time'] if res != 'Empty' else ''
def get_flaw(project_id): sql = f'''select e.id, `describe`, level, u.id as follower_id, u.name as follower_name, status from project_flaw as e join employee as u on e.follower_id=u.id where project_id=\'{project_id}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) return res if res != 'Empty' else []
def login(username, password): # login:id | username | password # employee:id | name | gender | career # superior_id | tele | department| mailbox sql = '''select login.id,employee.career from login join employee on login.id = employee.id where login.username = '******' and login.password = '******';''' % ( username, password) db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if not res == 'Empty': if res[0]['career'] in ['0', '1']: return (0, res[0]) # leader or worker sql = '''select * from project_participant join login on login.id = project_participant.leader_id where login.username = '******' ;''' % username db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) if db.selectDB(sql) == 'Empty': res[0]['career'] = '3' return (0, res[0]) else: res[0]['career'] = '2' return (0, res[0]) else: sql = '''select login.id,employee.career from login join employee on login.id = employee.id where login.username = '******';''' % username db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) if res == 'Empty': return 1 else: return 2
def modify_normal_account(uid, username, password, name, career, department): # update password if password is not None: sql = f'''update login set password = '******' where id='{uid}'; ''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) # update username sql = f'''update login set username='******' where id='{uid}'; ''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) # update employee sql = f'''update employee set name='{name}', career='{career}', department='{department}' where id='{uid}'; ''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) return 'ok'
def get_function(project_id, worker_id=None): if worker_id is None: # return function_id, function_name, worker_id, worker_name, parent_function_id sql = f''' select distinct id as function_id, function_name, parent_function_id from project_function where project_id=\'{project_id}\' and delete_label=0;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) ret = db.selectDB(sql) if ret == 'Empty': return [] for i in range(len(ret)): ret[i]['worker_id'] = '' ret[i]['worker_name'] = '' sql = f'''select e.id as worker_id, e.name as worker_name from function_partition as f join employee as e on f.worker_id=e.id where f.project_id=\'{project_id}\' and f.function_id=\'{ret[i]['function_id']}\';''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) worker_list = db.selectDB(sql) if worker_list == 'Empty': continue for worker in worker_list: ret[i]['worker_id'] += worker['worker_id'] + ',' ret[i]['worker_name'] += worker['worker_name'] + ',' ret[i]['worker_id'] = ret[i]['worker_id'][:-1] ret[i]['worker_name'] = ret[i]['worker_name'][:-1] return ret sql = f'''select distinct f.id as function_id, f.function_name from project_function as f join function_partition as fp on f.id=fp.function_id and f.project_id=fp.project_id where fp.worker_id=\'{worker_id}\' and fp.project_id=\'{project_id}\' and f.delete_label=0;''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) res = db.selectDB(sql) return res if res != 'Empty' else []
def create(uid, project_id, function_id, event_name, start_time, end_time, remain, describe): # status: "ok"/"fail_x" (fail_1: work_time > 24h, fail_2: start_time >= end_time, fail_3: cannot cast to int) # 1. check fail_1, fail_2, fail_3 if not start_time.isdigit() or not end_time.isdigit( ) or not remain.isdigit(): return 'fail_3' start_time, end_time, remain = int(start_time), int(end_time), int(remain) if start_time >= end_time: return 'fail_2' date = time.strftime("%Y-%m-%d", time.localtime()) date += ' 00:00:00' sql = f'select sum(end_time-start_time) from work_time where worker_id=\'{uid}\' and date=\'{date}\';' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) work_time = db.selectDB(sql)[0]['sum(end_time-start_time)'] if work_time is None: work_time = 0 if work_time + (end_time - start_time) > 24: return 'fail_1' # 2. get max id sql = f'select max(id) from work_time;' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) work_time_id = db.selectDB(sql)[0]['max(id)'] if work_time_id is None: work_time_id = 0 work_time_id += 1 # 3. insert (status=1, delete_label=0) sql = f'''insert into work_time(id,worker_id,project_id, date, function_id, event_name, start_time, end_time, remain, `describe`,status,delete_label) values({work_time_id},\'{uid}\',\'{project_id}\', \'{date}\', \'{function_id}\',\'{event_name}\', {start_time}, {end_time}, {remain}, \'{describe}\',1,0);''' db = d.ConnectToMysql(config.host, config.username, config.password, config.database, config.port) db.otherDB(sql) return 'ok'