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'