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())