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