def mission_save(mission_name, mission_content, mission_starttime, mission_plan_end_time, mission_publisher, mission_doers,mission_pubtime):
#规范时间格式
mission_status = "执行中"
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
#使查询结果返回字典类型
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
#向mission插入任务信息
cursor.execute("insert into MISSION \
(mission_name, mission_content, mission_pubtime, mission_starttime, mission_plan_end_time, mission_publisher)\
value ('%s', '%s', '%s', '%s', '%s', '%s');\
"%(mission_name.encode('utf-8'), mission_content.encode('utf-8'), mission_pubtime, mission_starttime.encode('utf-8'), mission_plan_end_time.encode('utf-8'), mission_publisher.encode('utf-8')))
conn.commit()
#向missions_doers中插入信息
cursor.execute("select max(mission_id) from MISSION where mission_publisher = '%s';"%(mission_publisher))
m_id = cursor.fetchall()
mission_id = int(m_id[0]['max(mission_id)'])
doers = mission_doers.split(',')
#删除列表doers中最后一个逗号后的元素
doers.pop()
for doer in doers:
cursor.execute("insert into MISSIONS_DOERS \
(mission_id, mission_doer ,mission_status)\
value ('%s', '%s' ,'%s');\
"%(mission_id, doer.encode('utf-8'), mission_status.encode('utf-8')))
conn.commit()
conn.close()
def mission_list(account_name, role, mission_status="待接受|执行中|已提交|未通过|已完成"):
#当前账户任务信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if str(role) == 'mission_doer':
cursor.execute("select MISSION.mission_name, MISSIONS_DOERS.mission_id,MISSION.mission_publisher, MISSION.mission_starttime, MISSION.mission_plan_end_time, MISSIONS_DOERS.mission_status, MISSIONS_DOERS.mission_doer \
from MISSIONS_DOERS, MISSION \
where MISSIONS_DOERS.mission_doer = '%s' and MISSIONS_DOERS.mission_status = '%s' and MISSIONS_DOERS.mission_id = MISSION.mission_id;\
"%(account_name, mission_status))
m_list_user = cursor.fetchall()
conn.close()
m_list_user = list(m_list_user)
m_list_user = sorted(m_list_user, key=lambda m_list_user: m_list_user['mission_starttime'], reverse=True)
#m_list_user包含mission_name,mission_id,mission_publisher,mission_starttime,mission_plan_end_time,mission_status,mission_doer
return m_list_user
elif str(role) == 'mission_publisher':
cursor.execute("\
select MISSION.mission_id,MISSION.mission_name,MISSION.mission_publisher,MISSION.mission_starttime,\
MISSION.mission_plan_end_time,MISSIONS_DOERS.mission_doer,MISSIONS_DOERS.mission_status,MISSION.mission_pubtime \
from MISSION,MISSIONS_DOERS \
where MISSIONS_DOERS.mission_id = MISSION.mission_id AND \
MISSIONS_DOERS.mission_status = '%s' and MISSION.mission_publisher = '%s';"%\
(mission_status,account_name))
m_list_publisher = cursor.fetchall()
conn.close()
m_list_publisher = list(m_list_publisher)
m_list_publisher = sorted(m_list_publisher, key=lambda m_list_publisher: m_list_publisher['mission_starttime'], reverse=True)
#m_list_publisher包含mission_id,mission_name,mission_doer,mission_publisher,mission_starttime,mission_plan_end_time,mission_status,mission_pubtime
return m_list_publisher
else:
return 0
def message_list(mission_id):
#role是用户所在页面的角色 执行者或发布者，user是当前用户名
print "mission_idmission_idmission_idmission_idmission_idmission_idmission_idmission_idmission_idmission_id"
print mission_id
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
#当 当前用户是发布者时，所有留言可见
# if role == 'mission_publisher':
cursor.execute("select * from MESSAGE where mission_id = '%s';" %
(mission_id))
message_list = cursor.fetchall()
# elif role == 'mission_doer':
# cursor.execute("select * from MESSAGE where mission_id = '%s' and (message_leaver ='%s' or message_type = '1');\
# "%(mission_id, user))
# message_list = cursor.fetchall()
conn.close()
print "message_list"
print message_list
message_list = list(message_list)
#按照message_time排序
message_list = sorted(
message_list, key=lambda message_list: message_list['message_time'])
return message_list
def get_mission_content(mission_id, mission_doer,mission_status):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if mission_status == '已完成':
cursor.execute("SELECT *\
FROM HISTORY_MISSION \
WHERE mission_id='%s'"%mission_id)
mission = list(cursor.fetchall())
if not mission:
cursor.execute("SELECT *\
FROM MISSION JOIN MISSIONS_DOERS\
ON MISSION.mission_id=MISSIONS_DOERS.mission_id\
WHERE mission_doer = '%s' AND MISSION.mission_id='%s'"%(mission_doer, mission_id))
mission += list(cursor.fetchall())
doer_list = mission[0]['mission_doer']
doer_list = doer_list.strip(',')
doers = doer_list.split(',')
mission[0]['mission_doer'] = doers
return mission
mission = list()
cursor.execute("SELECT *\
FROM MISSION JOIN MISSIONS_DOERS\
ON MISSION.mission_id=MISSIONS_DOERS.mission_id\
WHERE mission_doer = '%s' AND MISSION.mission_id='%s'"%(mission_doer, mission_id))
mission += list(cursor.fetchall())
doer_list = mission[0]['mission_doer']
doer_list = doer_list.strip(',')
doers = doer_list.split(',')
mission[0]['mission_doer'] = doers
return mission
def get_download_list(type, args=None):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute(
"SELECT file_url, file_name, HISTORY_MISSION.mission_name, file_uploader, file_upload_time, mission_name, HISTORY_MISSION.mission_id \
FROM FILE, HISTORY_MISSION \
WHERE FILE.mission_id = HISTORY_MISSION.mission_id\
UNION\
SELECT file_url, file_name, MISSION.mission_name, file_uploader, file_upload_time, mission_name, MISSION.mission_id \
FROM FILE, MISSION \
WHERE FILE.mission_id = MISSION.mission_id")
file_list_all = list(cursor.fetchall())
file_list_search = list()
cursor.close()
if type == 'all':
return file_list_all
for m in file_list_all:
if args.upload_time == '' or datetime.datetime.strptime(
args.upload_time,
"%Y-%m-%d").date() <= m['file_upload_time'].date():
if args.type == '':
file_list_search.append(m)
if args.type == '文件名' and args.search_str in m['file_name']:
file_list_search.append(m)
if args.type == '任务名' and 'mission_name' in m and args.search_str in m[
'mission_name']:
file_list_search.append(m)
return file_list_search
def save_info(user, args):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("update ACCOUNT SET \
account_username='******', \
account_sex = '%s',\
account_phone='%s', \
account_email='%s', \
account_address = '%s'\
WHERE account_id = %s\
;"% (args.username, args.account_sex,args.phone,\
args.email,str(args.account_address).encode('utf-8'), args.id))
conn.commit()
if args.get('oldpasswd'):
if args.get('newpassword1') == args.get('newpassword2'):
passwd = data.checkin(user)
newpassword1 = data.md5(args.get('newpassword1'))
oldpasswd = data.md5(args.get('oldpasswd'))
if oldpasswd == passwd[0]['account_password']:
cursor.execute("update ACCOUNT SET \
account_password = '******'\
WHERE account_id = '%s'"\
%(newpassword1, args.get('id')))
conn.commit()
else:
return False
else:
return False
conn.close()
return True
def mission_view_status(account_name, role, mission_id,mission_status):
#我的任务动态中不同状态的任务点击查看详情返回值
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if str(role) == 'mission_doer':
if mission_status == '已完成':
cursor.execute("select * ,'已完成' as mission_status from HISTORY_MISSION where mission_id = '%s'"%(mission_id))
m_list_user = cursor.fetchall()
else:
cursor.execute("select MISSION.mission_name,MISSION.mission_id, MISSION.mission_publisher, MISSION.mission_content, MISSION.mission_starttime, MISSION.mission_plan_end_time, MISSIONS_DOERS.mission_status \
from MISSIONS_DOERS, MISSION \
where MISSIONS_DOERS.mission_doer = '%s' and MISSIONS_DOERS.mission_status = '%s' and MISSIONS_DOERS.mission_id = '%s' and MISSIONS_DOERS.mission_id = MISSION.mission_id;\
"%(account_name, mission_status, mission_id))
m_list_user = cursor.fetchall()
if len(m_list_user) == 0:
m_list_user[0]['mission_id'] = 0
doerDict = {}
cursor.execute("select mission_doer from MISSIONS_DOERS where mission_id = %s ;"%(m_list_user[0]['mission_id']))
m_list_doers = cursor.fetchall()
m_list_doers_list = []
for j in range(len(m_list_doers)):
m_list_doers_list.append(m_list_doers[j]['mission_doer'])
doerDict[m_list_user[0]['mission_id']]=m_list_doers_list
m_list_user[0]['mission_doer']=doerDict[m_list_user[0]['mission_id']]
m_list_user[0]['user'] = account_name
conn.close()
#m_list_user包含mission_doer(all),user(one),mission_name,mission_id,mission_publisher,mission_content,mission_starttime,mission_plan_end_time,mission_status
return m_list_user
elif str(role) == 'mission_publisher':
if mission_status == '已完成':
cursor.execute("select * ,'已完成' as mission_status from HISTORY_MISSION where mission_id = '%s'"%(mission_id))
m_list_publisher = cursor.fetchall()
else:
cursor.execute("\
select MISSION.mission_id,MISSION.mission_name,MISSION.mission_content,MISSION.mission_publisher,MISSION.mission_starttime,MISSION.mission_plan_end_time,MISSIONS_DOERS.mission_status \
from MISSION,MISSIONS_DOERS \
where MISSIONS_DOERS.mission_id = MISSION.mission_id AND MISSIONS_DOERS.mission_status = '%s' and MISSIONS_DOERS.mission_doer = '%s' AND MISSIONS_DOERS.mission_id = '%s' ;"\
%(mission_status,account_name,mission_id))
m_list_publisher = cursor.fetchall()
#添加所有执行人
if len(m_list_publisher) == 0:
m_list_publisher[0]['mission_id'] = 0
doerDict = {}
cursor.execute("select mission_doer from MISSIONS_DOERS where mission_id = %s ;"%(m_list_publisher[0]['mission_id']))
m_list_doers = cursor.fetchall()
m_list_doers_list = []
for j in range(len(m_list_doers)):
m_list_doers_list.append(m_list_doers[j]['mission_doer'])
doerDict[m_list_publisher[0]['mission_id']]=m_list_doers_list
m_list_publisher[0]['mission_doer']=doerDict[m_list_publisher[0]['mission_id']]
m_list_publisher[0]['user'] = account_name
#类型转换
m_list_publisher = list(m_list_publisher)
conn.close()
return m_list_publisher
else:
return 0
def get_account_info(user):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("SELECT * FROM ACCOUNT WHERE account_username='******'"%user)
account_info = cursor.fetchone()
return account_info
def mission_audit(mission_id):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
mission_id = int(mission_id)
cursor.execute("update MISSION set mission_status='已审核' where mission_id='%s';"%mission_id)
conn.commit()
conn.close()
def get_account_id(account_name):
#获取任务id
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("select account_id from ACCOUNT where account_name='%s';"%account_name)
id_getted = cursor.fetchall()[0]['account_id']
conn.close()
return id_getted
def account_view(account_id):
#搜索对应account_id所对应的信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("select * from ACCOUNT where account_id='%s';"%account_id)
a = cursor.fetchall()
conn.close()
return a
def account_delete(account_id):
#删除对应account_id的全部信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("delete from ACCOUNT where account_id = '%s';"%account_id)
conn.commit()
conn.close()
return "账号删除成功"
def mission_id_get(mission_publisher, mission_pubtime):
print mission_pubtime,mission_publisher
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("select mission_id from MISSION where mission_publisher = '%s' AND mission_pubtime = '%s';\
"%(mission_publisher, mission_pubtime))
id_getted = cursor.fetchall()[0]['mission_id']
conn.close()
return id_getted
def mission_delete(mission_id):
#删除任务
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor()
try:
cursor.execute("update MISSIONS_DOERS set mission_status='已删除' where mission_id = %s;"%(mission_id))
conn.commit()
except Exception, e:
conn.close()
return "异常错误"
def mission_search_list(user, arg):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
account_list = account.account_list()
account_list = list(account_list)
for i in account_list:
if data.permission_check(user, i, 'mission') == False:
account_list.remove(i)
if i == 'admin':
account_list.remove(i)
# print 'account_list', len(account_list)
search_list = []
for i in account_list:
cursor.execute("SELECT mission_name, mission_publisher, mission_doer, mission_status,\
mission_starttime, mission_plan_end_time, MISSION.mission_id\
FROM MISSION JOIN MISSIONS_DOERS\
ON MISSION.mission_id=MISSIONS_DOERS.mission_id\
WHERE mission_doer='%s' OR mission_publisher='%s'"%\
(i,i))
search_list += list(cursor.fetchall())
cursor.execute("SELECT mission_name, mission_publisher, mission_doer,\
mission_starttime, mission_plan_end_time, mission_id\
FROM HISTORY_MISSION \
WHERE mission_doer='%s' OR mission_publisher='%s'"\
%(i, i))
search_list += list(cursor.fetchall())
# 去除重复,一点处理
mlist = search_list
search_list = list()
for m in mlist:
if m not in search_list:
if 'mission_status' not in m:
m['mission_status'] = '已完成'
search_list.append(m);
if arg == 'all':
# print search_list
return search_list
mlist = list()
for m in search_list:
if arg.search_str == '' or arg.search_str in m['mission_name']:
if arg.mission_publisher == '' or arg.mission_publisher == m['mission_publisher']:
if arg.mission_doer == '' or arg.mission_doer == m['mission_doer']:
if arg.mission_status == '' or arg.mission_status == m['mission_status']:
if arg.mission_start_time == '' or datetime.datetime.strptime(arg.mission_start_time,"%Y-%m-%d").date() <= m['mission_starttime']:
if arg.mission_end_time == '' or datetime.datetime.strptime(arg.mission_end_time,"%Y-%m-%d").date() >= m['mission_plan_end_time']:
mlist.append(m)
return mlist
def account_list_view(account_department = '*'):
#搜索对应科室部门的所有账号
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if account_department != '*':
cursor.execute("select * from ACCOUNT where account_department='%s';"%account_department)
elif account_department =='*':
cursor.execute("select * from ACCOUNT where account_name='user';")
a = cursor.fetchall()
conn.close()
return a
def get_mission_by_id(mission_id):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("SELECT * FROM HISTORY_MISSION \
WHERE mission_id='%d';"%(int(mission_id)))
mission_content = list(cursor.fetchall())
mission_content[0]["mission_doer"] = list(mission_content[0]["mission_doer"].split(","))
print "mission_content"
print mission_content
#包含mission_id,mission_name,mission_content,mission_starttime,mission_plan_end_time,mission_endtime,\
#mission_publisher,mission_doer,mission_message,mission_appendix
return mission_content
def file_type_change(mission_id, mission_doer):
#任务状态改为已完成时，将对应mission_id的file_type改为2
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute(
"update FILE set file_type = '2' WHERE mission_id = '%s' and file_uploader = '%s' and file_type = '1';\
" % (mission_id, mission_doer))
conn.commit()
conn.close()
def account_save(account_name, account_sex, account_username, account_work, account_position, account_phone,account_address,account_email,account_department, account_power):
#存储账号信息
#设置默认密码user:123456, admin: admin
if account_name == 'user':
account_password = data.md5('123456')
elif account_name == 'admin':
account_password = data.md5('admin')
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("insert into ACCOUNT \
(account_password, account_name, account_sex, account_username, account_work, account_position, account_phone,account_address,account_email,account_department, account_power)\
value ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');\
"%(account_password, account_name.encode('utf-8'), account_sex.encode('utf-8'), account_username.encode('utf-8'), account_work.encode('utf-8'), account_position.encode('utf-8'), account_phone.encode('utf-8'),account_address.encode('utf-8'),account_email.encode('utf-8'),account_department.encode('utf-8'),account_power))
conn.commit()
conn.close()
def account_list(account_department = '*'):
#搜索对应科室部门的所有账号
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if account_department != '*':
account_department = int(account_department)
cursor.execute("select * from ACCOUNT where account_department='%s';"%account_department)
elif account_department =='*':
cursor.execute("select account_username from ACCOUNT where account_name='user';")
account_username = cursor.fetchall()
a = []
for i in range(len(account_username)):
a.append(account_username[i]['account_username'])
a = cnsort.cnsort(a)
conn.close()
return a
def mission_sta_change(mission_id ,mission_status,mission_doer):
c = data.SQLconn()
print "mission_id,mission_status :"
print mission_id
print mission_status
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("update MISSIONS_DOERS set mission_status = '%s' WHERE mission_id = '%s' AND mission_doer = '%s' ;"\
%(mission_status.encode('utf-8'), mission_id, mission_doer.encode('utf-8')))
conn.commit()
if mission_status == '已完成':
print "*****************into wancheng"
cursor.execute("SELECT mission_status FROM MISSIONS_DOERS WHERE mission_id = '%s'"%mission_id)
all_status = list(cursor.fetchall())
check_done = True
for m in all_status:
if m['mission_status'] != '已完成':
check_done = False
print "************check_done:", check_done, m['mission_status']
if check_done:
print "****************chenge history_mission"
cursor.execute("SELECT mission_doer FROM MISSIONS_DOERS WHERE mission_id = '%s'"%mission_id)
mission_doers = list(cursor.fetchall())
doer_str = ''
for doer in mission_doers:
doer_str = doer_str + doer['mission_doer'] + ','
end_time = time.strftime('%Y-%m-%d',time.localtime(time.time()))
cursor.execute("SELECT * FROM MISSION WHERE mission_id = '%s'"%mission_id)
content = cursor.fetchone()
cursor.execute("INSERT INTO HISTORY_MISSION \
(mission_id, mission_name, mission_content, mission_starttime, \
mission_endtime, mission_plan_end_time, mission_publisher, mission_doer) \
VALUES\
('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"\
%(mission_id, content['mission_name'], content['mission_content'], content['mission_starttime'],
end_time, content['mission_plan_end_time'], content['mission_publisher'],
doer_str))
conn.commit()
cursor.execute("DELETE FROM MISSION WHERE mission_id = '%s'"%mission_id)
conn.commit()
# cursor.execute("DELETE FROM MISSIONS_DOERS WHERE mission_id = '%s'"%mission_id)
# conn.commit()
conn.close()
def account_update(account_name, account_sex, account_username, account_work, account_position, account_phone,account_address,account_email,account_department, account_power,account_id):
#跟新account_id 对应账号的信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("update ACCOUNT set \
account_name = '%s', \
account_sex = '%s',\
account_username = '******', \
account_work = '%s', \
account_email = '%s', \
account_phone = '%s', \
account_position = '%s', \
account_department = '%s', \
account_address = '%s',\
account_power = '%s' \
where account_id = '%s';"%(account_name.encode('utf-8'), account_sex.encode('utf-8'), account_username.encode('utf-8'), account_work.encode('utf-8'), account_email.encode('utf-8'), account_phone.encode('utf-8'), account_position.encode('utf-8'), account_department.encode('utf-8'),account_address.encode('utf-8'), account_power, account_id))
conn.commit()
conn.close()
def upload(mission_id, file_name, file_url, file_uploader, file_upload_time,
file_type):
#上传附件
#存储附件信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute("insert into FILE \
(mission_id, file_name, file_url, file_uploader, file_upload_time, file_type)\
value ('%s', '%s', '%s', '%s', '%s', '%s');\
" % (int(mission_id), file_name.encode('utf-8'), file_url.encode('utf-8'),
file_uploader.encode('utf-8'), file_upload_time.encode('utf-8'),
int(file_type)))
conn.commit()
conn.close()
def message_save(mission_id, message_content, message_time, message_leaver,
message_type):
#存储留言
#首先对留言进行审核，防止注入
message_content = message_content.strip()
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute("insert into MESSAGE \
(mission_id, message_content, message_time, message_leaver, message_type) values ('%s', '%s', '%s', '%s', '%s');\
" % (int(mission_id), message_content.encode('utf-8'), message_time,
message_leaver.encode('utf-8'), int(message_type)))
conn.commit()
conn.close()
def file_list(file_type, mission_id, role):
#附件列表
print role
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"],
user=c["user"],
passwd=c["passwd"],
charset=c["charset"],
db=c["db"])
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor.execute(
"select * from FILE WHERE file_type = '%s' and file_uploader = '%s' and mission_id = '%s';\
" % (file_type, role, mission_id))
file_list = cursor.fetchall()
print "file_list(file_type, mission_id, role) :"
print file_list
conn.close()
file_list = list(file_list)
#按照file_upload_time排序
file_list = sorted(file_list,
key=lambda file_list: file_list['file_upload_time'])
return file_list
def get_mission_reference(args):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursor.execute("SELECT HISTORY_MISSION.mission_id, file_url, mission_name, mission_starttime, file_name, file_id\
FROM HISTORY_MISSION left JOIN FILE\
ON HISTORY_MISSION.mission_id=FILE.mission_id\
")
mission_list_all = list(cursor.fetchall())
mission_list = list()
if 'mission_name' in args and args.mission_name != '':
for m in mission_list_all:
num = difflib.SequenceMatcher(None, args.mission_name, m['mission_name']).ratio()
print 'similer:', num
if args.mission_name in m['mission_name'] or num > 0.6:
mission_list.append(m)
elif 'file_name' in args and args.file_name != '':
for m in mission_list_all:
num = difflib.SequenceMatcher(None, args.file_name, m['mission_name']).ratio()
print 'similer:', num
if args.file_name in m['file_name'] or num > 0.6:
mission_list.append(m)
return mission_list
def mission_view(account_name, role, mission_status="待接受|执行中|已提交|未通过|已完成"):
#当前账户任务信息
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if str(role) == 'mission_doer':
if mission_status == "已完成":
cursor.execute("select '已完成' as mission_status, mission_name,mission_id, mission_publisher, mission_content, mission_starttime, mission_plan_end_time, mission_endtime, mission_doer\
from HISTORY_MISSION \
where instr(mission_doer ,'%s') >0 ;\
"%(account_name))
m_list_user = cursor.fetchall()
else:
cursor.execute("select MISSION.mission_name,MISSION.mission_id, MISSION.mission_publisher, MISSION.mission_content, MISSION.mission_starttime, MISSION.mission_plan_end_time, MISSIONS_DOERS.mission_status,MISSION.mission_pubtime \
from MISSIONS_DOERS, MISSION \
where MISSIONS_DOERS.mission_doer = '%s' and MISSIONS_DOERS.mission_status = '%s' and MISSIONS_DOERS.mission_id = MISSION.mission_id;\
"%(account_name, mission_status))
m_list_user = cursor.fetchall()
#将获取的m_list_user中的id存储到list_id列表中
list_id = []
for i in range(len(m_list_user)):
list_id.append(m_list_user[i]['mission_id'])
#将missions_doers中与mission中相同id对应的多执行者放到一个字典doerDict中，key为id，value为doers
doerDict = {}
for i in list_id:
cursor.execute("select mission_doer from MISSIONS_DOERS where mission_id = %s ;"%i)
m_list_doers = cursor.fetchall()
m_list_doers_list = []
for j in range(len(m_list_doers)):
m_list_doers_list.append(m_list_doers[j]['mission_doer'])
doerDict[i]=m_list_doers_list
#将doerDict和之前只缺少doers信息的m_list_mission合并起来，构成最后返回的m_list_user
for i in range(len(m_list_user)):
m_list_user[i]['mission_doer']=doerDict[m_list_user[i]['mission_id']]
m_list_user[i]['user'] = account_name
conn.close()
m_list_user = list(m_list_user)
if mission_status == '已完成':
m_list_user = sorted(m_list_user, key=lambda m_list_user: m_list_user['mission_starttime'], reverse=True)
else:
m_list_user = sorted(m_list_user, key=lambda m_list_user: m_list_user['mission_pubtime'], reverse=True)
return m_list_user
elif str(role) == 'mission_publisher':
cursor = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
if mission_status == '已完成':
cursor.execute("select '已完成' as mission_status, mission_name,mission_id, mission_publisher, mission_content, mission_starttime, mission_plan_end_time, mission_endtime, mission_doer\
from HISTORY_MISSION \
where mission_publisher = '%s' ;\
"%(account_name))
else:
cursor.execute("select MISSION.mission_id,MISSION.mission_name,MISSION.mission_content,MISSION.mission_publisher,MISSION.mission_starttime,MISSION.mission_plan_end_time,MISSIONS_DOERS.mission_doer,MISSIONS_DOERS.mission_status,MISSION.mission_pubtime \
from MISSION,MISSIONS_DOERS \
where MISSIONS_DOERS.mission_id = MISSION.mission_id AND MISSIONS_DOERS.mission_status = '%s' and MISSION.mission_publisher = '%s';"\
%(mission_status,account_name))
m_list_publisher = cursor.fetchall()
conn.close()
for i in range(len(m_list_publisher)):
m_list_publisher[i]['user'] = m_list_publisher[i]['mission_doer']
m_list_publisher = list(m_list_publisher)
if mission_status == '已完成':
m_list_publisher = sorted(m_list_publisher, key=lambda m_list_publisher: m_list_publisher['mission_starttime'], reverse=True)
else:
m_list_publisher = sorted(m_list_publisher, key=lambda m_list_publisher: m_list_publisher['mission_pubtime'], reverse=True)
return m_list_publisher
else:
return 0
def get_mission_simple_content(mission_id):
c = data.SQLconn()
conn = MySQLdb.connect(host=c["host"], user=c["user"], passwd=c["passwd"], charset=c["charset"], db=c["db"])
cursors = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
cursors.execute("SELECT mission_name, mission_content FROM MISSION WHERE mission_id = '%s'" % mission_id)
return list(cursors.fetchall())
