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 query_driver(): act_user = get_act_user() if act_user is None: redirect('/') act_user = UserDb.get(act_user) driver_rec_hdr = (u'姓名', u'类别', u'身份证号', u'船舶', u'进出时间', u'港口', u'边检站', u'进出状态', u'报警状态', u'照片', ) tab_query_cols = ('cat', 'station', 'harbour', 'direction', 'alarm') like_query_cols = ('name', 'vechicle') query_cond = {} isalarm = False for kw in tab_query_cols: input = request.forms.get(kw) if kw == 'alarm' and input == '船舶离港报警': isalarm = True input = '' 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: 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_orclex('haitong', '111111', sdb.DB_URL) final_cond = ' and '.join([subcond for subcond in (where_str, like_str, interval_str) if subcond]) final_query_str = "SELECT * FROM driver_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 + " ORDER by date DESC", query_cond) res = cur.fetchall() # get ships moving off cur.execute("SELECT ZWCBM, YWCBM FROM crs_shp_table WHERE STATUS like \'%%%s%%\'"%('离港',)) off_ships = cur.fetchall() off_ship = [ship[0] or ship[1] for ship in off_ships] except: return "对不起,数据库访问失败,请稍后再试!" if isalarm: arecs = [] checked = [] for r in res: if r[0] in checked: continue else: checked.append(r[0]) if r[3] in off_ship: print u'进门', ' ', r[7].decode('utf8') if (r[1]=='临时登轮证' or r[1]=='长期登轮证') and ('进门' in r[7]): arecs.append(r) elif (r[1]=='船员登陆证' or r[1]=='台湾船员登陆证' or r[1]=='临时入境许可')\ and ('出门' in r[7]): arecs.append(r) else: pass res = arecs for drvrec in res: if not os.path.isfile('./pictures/%s'%drvrec[-1]): if drvrec[-1].endswith('.jpg'): retr_img_from_ftp(drvrec[-1]) # export results to csv file 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(driver_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=[driver_rec_hdr]+res, query_tbl='driver_recs', stations=list(set(sdb.get_stations_from_driver_recs())), privs=UserDb.get_privilege(act_user.role), curr_user=get_act_user(), querydisp=get_query_disp(), settingdisp=get_setting_disp())