def del_user(usrname): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) UserDb.del_user(usrname) redirect('/account_mngn')
def del_role(rolename): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) UserDb.del_role(rolename) return rolename, '已删除'
def query_vhl_info(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) plate = request.forms.get('plate') print plate #dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute("SELECT rowid, * FROM vehicleinfo WHERE WYCPH like \'%%%s%%\'"%(plate,)) res = [('序号', '车牌号', '公司全称', '境外车牌号', '境内车牌号', '所属国籍', '车辆类型代码', '车辆高度', '肽位', '批文有效期', '通行口岸代码', '通行有效期', '申请表号', '现批文号码', '款式颜色', '车头字', '载重吨位', '内地承办单位', '录入检查员代码', '录入时间', '操作口岸代码', '备注', '主驾驶', '副驾驶')] res += cur.fetchall() #cur.execute("SELECT * FROM vehicleinfo") #res = cur.fetchall() cur.close() dbconn.close() return template('./view/query.tpl', query_results=res, query_tbl='vehicle', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query_vehicle(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) veh_rec_hdr = (u'车牌号', u'公司全称', u'司机', u'证件类型', u'证件号码', u'进出时间', u'港口', u'进出状态', u'司机照片', u'车辆照片') tab_query_cols = ('direction') like_query_cols = ('plate', 'idnum', 'company') query_cond = {} for kw in tab_query_cols: input = request.forms.get(kw) if input: query_cond[kw] = input where_str = cons_query_where_clause(query_cond) like_cond = {} for kw in like_query_cols: input = request.forms.get(kw) if input: like_cond[kw] = input like_str = cons_like_clause(like_cond) # add query interval interval = cons_query_interval(request.forms.get('start'), request.forms.get('end')) if interval: print interval start, end = interval query_cond['start'] = start query_cond['end'] = end interval_str = ' datetime(date) BETWEEN datetime(:start) and datetime(:end)' else: interval_str = '' dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() final_cond = ' and '.join([subcond for subcond in (where_str, like_str, interval_str) if subcond]) final_query_str = "SELECT * FROM vehicle_rec_table" if final_cond: final_query_str += " WHERE " + final_cond cur.execute(final_query_str, query_cond) res = cur.fetchall() cur.close() dbconn.close() for vhlrec in res: if not os.path.isfile(vhlrec[-1]): if vhlrec[-1].endswith('.jpg'): retr_img_from_ftp(vhlrec[-1]) if not os.path.isfile(vhlrec[-2]): if vhlrec[-2].endswith('.jpg'): retr_img_from_ftp(vhlrec[-2]) if request.forms.get('export'): csvname = datetime.strftime(datetime.now(), '%Y%m%dT%H%M%S') + '.csv' with open(csvname, 'wb') as csvfile: writer = csv.writer(csvfile, dialect='excel') writer.writerow(veh_rec_hdr) writer.writerows(res) return '<p>数据已导出,点击下载文件<a href="/static/%s">%s</a></p>'%(csvname, csvname) return template('./view/query.tpl', query_results=[veh_rec_hdr]+res, query_tbl='vehicle_recs', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def update(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/update_company.tpl', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), rowid=rowid, querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query_home(): act_user = get_act_user() if act_user is None: redirect('/') privs = UserDb.get_privilege(UserDb.get(act_user).role) return template('./view/query.tpl', query_results=[], query_tbl='', privs=privs, curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def add_ship(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/ship.tpl', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def edit_role(rolename): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/setting.tpl', setting='edit_role', roles=UserDb.get_roles(), privs=UserDb.get_privilege(act_user.role), role2edit=rolename, curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query_veh_info(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/query.tpl', query_results=[], query_tbl='driver', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def change_passwd(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/setting.tpl', setting="change_password", privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def edit_role(rolename): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) desc = request.forms.get('desc') status = request.forms.get('status') print desc, status, rolename UserDb.update_role_status_desc(rolename, status, desc) redirect('/user_roles')
def access_control(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/setting.tpl', setting='access_granting', roles=UserDb.get_roles(), privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def update_user(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) return template('./view/setting.tpl', setting="adduser", roles=UserDb.get_roles(), privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) stations = sdb.get_stations_from_driver_recs() stations = list(set(stations)) return template('./view/query.tpl', query_results=[], query_tbl='driver_recs', stations=stations, privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def update_passwd(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) passwd = request.forms.get('newpass') cnfm_passwd = request.forms.get('confirmedpass') if passwd != cnfm_passwd: return '新密码两次输入不一致,请返回重试!' UserDb.change_passwd(act_user.usrname, passwd) redirect('/account_mngn')
def account_mngn(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) users = UserDb.fetch_users() return template('./view/setting.tpl', setting='accounts', users=users, privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def edit_user(usrname): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) nickname = request.forms.get('nickname') desc = request.forms.get('desc') role = request.forms.get('role') print usrname, nickname, desc, role UserDb.change_user_info(usrname, desc, role, nickname) redirect('/account_mngn')
def grant(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) privs = ['sys', 'query', 'vehicle', 'driver', 'company', 'ship'] granted = [] for priv in privs: if request.forms.get(priv): granted.append(priv) role = request.forms.get('grant') print role UserDb.update_privilege(role, granted)
def account_query(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) user = request.forms.get('account') if request.forms.get('query'): return template('./view/setting.tpl', setting="accounts", users=[UserDb.get(user)], privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp()) elif request.forms.get('create'): redirect('/user_update')
def update_user(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) usrname = request.forms.get('usrname') passwd = request.forms.get('passwd') role = request.forms.get('role') desc = request.forms.get('desc') nickname= request.forms.get('nickname') status = request.forms.get('status') newuser = UserDb.User(usrname, passwd, role=='系统管理员', nickname, desc, status=status, role=role) newuser.put() redirect('/account_mngn')
def add_role(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) rolename = request.forms.get('rn') op = request.forms.get('create') if op and rolename: r = UserDb.Role(rolename=rolename) #UserDb.add_role(r) r.put() redirect('/user_roles') else: op = request.forms.get('query') if op: redirect('/user_roles')
def validate_from_db(usr, passwd): user = UserDb.get(usr) if user is not None and user.usrname == usr and user.password == passwd: ret = True, user else: ret = False, user return ret
def update(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) cols = get_colomns('crs_shp_table') num_of_cols = len(cols) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute('SELECT * FROM crs_shp_table WHERE rowid=%s'%(rowid,)) row = cur.fetchone() dbconn.close() default_data = {} if row: for i in xrange(num_of_cols): default_data[cols[i].lower()] = row[i] return template('./view/update_ship.tpl', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), rowid=rowid, default_data=default_data, querydisp=get_query_disp(), settingdisp=get_setting_disp())
def delcomp(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) print rowid dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute('DELETE FROM company_table WHERE rowid=%s'%(rowid,)) dbconn.commit() dbconn.close() redirect('/query_company')
def deldriver(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) print rowid dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute('DELETE FROM crs_shp_table WHERE rowid=%s'%(rowid,)) dbconn.commit() dbconn.close() send_sql('DELETE FROM crs_shp_table WHERE rowid=%s'%(rowid,)) redirect('/query_ship')
def delvehicle(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) print rowid dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute('DELETE FROM vehicleinfo WHERE rowid=%s'%(rowid,)) dbconn.commit() dbconn.close() send_sql('DELETE FROM vehicleinfo WHERE rowid=%s'%(rowid,)) redirect('/query_vehicle_info')
def query_ship(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) cruise = request.forms.get('cruise') #dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute("SELECT rowid, * FROM crs_shp_table WHERE HC like \'%%%s%%\'"%(cruise,)) res = [('序号', '航次', '船舶检索标识', 'MMSI号', '交通工具类型代码', '船舶种类代码', '船舶中文名称', '船舶英文名称', 'IMO号', '国际呼号', '国籍地区代码', '船员变更标识', '重点关注标识', '当前检查分类', '当前检查状态', '口岸代码', '操作员', '操作部门', '操作时间', '船籍港', '当前停靠地(码头)', '当前停靠地(泊位)', '解档修改状态', '加封人', '加封时间', '启封人', '启封时间', '武器弹药', '加封口岸', '启封口岸', '优检标志', '船舶状态')] res += cur.fetchall() cur.close() dbconn.close() return template('./view/query.tpl', query_results=res, query_tbl='ship', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query_driver_info(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) name = request.forms.get('name') #dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute("SELECT rowid, * FROM driverinfo_use WHERE XM like \'%%%s%%\'"%(name,)) res = [('序号', '停留期', '前往国', '来自国', '许可证号', '身份证号', '第2姓名', '第2出生日期', '第二证件号码', '第二证件类别代码', '通行口岸代码', '民族代码', '通用标志', '操作人代码', '操作时间', '操作口岸', '备注', '签证号', '证件号码', '证件种类', '姓名', '性别', '出生日期', '国籍代码', '申请表号', '准驾签注有效期', '公司全称', '签证签注代码', '发证机关代码', '签证签注有效期', 'IC卡号')] res += cur.fetchall() cur.close() dbconn.close() return template('./view/query.tpl', query_results=res, query_tbl='driver', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def query_company(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) fullname = request.forms.get('fullname') print fullname #dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() #cur.execute("SELECT * FROM company_table WHERE GSQC=:name", {'name':fullname}) #cur.execute("SELECT * FROM company_table") cur.execute('SELECT rowid, * FROM company_table WHERE GSQC like \'%%%s%%\''%(fullname,)) tab_hdr = [('序号', '公司代码', '公司全称', '公司简称', '类型代码', '所属国籍', '负责人', '业务范围', '使用标记', '操作员', '操作时间', '操作口岸', '备注')] res = tab_hdr + cur.fetchall() cur.close() dbconn.close() return template('./view/query.tpl', query_results=res, query_tbl='company', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())
def update(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) #tab_cols = sch.sql_table_columns['driverinfo_use'] user_input = {} #cols = re.findall('([A-Z]+)', tab_cols) cols = get_colomns('driverinfo_use') for col in cols: colname = col.lower() colval = request.forms.get(colname) user_input[col] = colval sql = 'UPDATE driverinfo_use SET ' + cons_set_clause(user_input) + ' WHERE rowid=%s'%(rowid,) dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute(sql) dbconn.commit() dbconn.close() send_sql(sql) redirect('/query_driver_info')
def update(rowid): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) #tab_cols = sch.sql_table_columns[] user_input = {} cols = get_colomns('vehicleinfo') for col in cols: colname = col.lower() colval = request.forms.get(colname) user_input[col] = colval print 'update', rowid sql = 'UPDATE vehicleinfo SET ' + cons_set_clause(user_input) + ' WHERE rowid=%s'%(rowid,) print sql dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute(sql) dbconn.commit() dbconn.close() send_sql(sql) redirect('/query_vehicle_info')
def query_vehicle(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) veh_rec_hdr = (u'车牌号', u'公司全称', u'司机', u'证件类型', u'证件号码', u'进出时间', u'港口', u'进出状态', u'司机照片', u'车辆照片') tab_query_cols = ('direction') like_query_cols = ('plate', 'idnum', 'company') query_cond = {} for kw in tab_query_cols: input = request.forms.get(kw) if input: query_cond[kw] = input where_str = cons_query_where_clause(query_cond) like_cond = {} for kw in like_query_cols: input = request.forms.get(kw) if input: like_cond[kw] = input like_str = cons_like_clause(like_cond) # add query interval interval = cons_query_interval(request.forms.get('start'), request.forms.get('end')) if interval: print interval start, end = interval query_cond['start'] = start query_cond['end'] = end interval_str = ' datetime(date) BETWEEN datetime(:start) and datetime(:end)' else: interval_str = '' final_cond = ' and '.join([subcond for subcond in (where_str, like_str, interval_str) if subcond]) final_query_str = "SELECT * FROM vehicle_rec_table" if final_cond: final_query_str += " WHERE " + final_cond try: dbconn = sdb.connect() dbconn.text_factory = str cur = dbconn.cursor() cur.execute(final_query_str, query_cond) res = cur.fetchall() cur.close() dbconn.close() except: return "对不起,数据库访问失败,请稍后再试!" for vhlrec in res: if not os.path.isfile('./pictures/%s'%vhlrec[-1]): if vhlrec[-1].endswith('.jpg'): retr_img_from_ftp(vhlrec[-1]) if not os.path.isfile('./pictures/%s'%vhlrec[-2]): if vhlrec[-2].endswith('.jpg'): retr_img_from_ftp(vhlrec[-2]) if request.forms.get('export'): csvname = datetime.strftime(datetime.now(), '%Y%m%dT%H%M%S') + '.csv' with open(csvname, 'wb') as csvfile: writer = csv.writer(csvfile, dialect='excel') writer.writerow(veh_rec_hdr) writer.writerows(decode_utf8(res)) return '<p>数据已导出,点击右键另存为<a href="/static/%s">%s</a></p>'%(csvname, csvname) return template('./view/query.tpl', query_results=[veh_rec_hdr]+res, query_tbl='vehicle_recs', privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())