def main(): sdb.main() init_db() dbporc = Process(target=sdb.run_sock_svr, args=()) dbporc.start() websvr = Process(target=run, args=(app, 'wsgiref', '0.0.0.0', '8081')) websvr.start() dbporc.join() websvr.join()
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 add_vehicle():
tab_cols = sch.sql_table_columns['vehicleinfo']
user_input = []
cols = re.findall('([A-Z]+)', tab_cols)
for col in cols:
colname = col.lower()
user_input.append(request.forms.get(colname))
print user_input
#sql = 'INSERT INTO vehicleinfo %s VALUES %s'%(tab_cols, str(tuple(user_input)))
#dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL)
dbconn = sdb.connect()
dbconn.text_factory = str
cur = dbconn.cursor()
sql = 'insert into vehicleinfo values (%s)'%(('?,'*len(cols))[:-1],)
print sql
cur.execute(sql, tuple(user_input))
dbconn.commit()
cur.close()
dbconn.close()
#sql = 'insert into vehicleinfo values %s'%(str(tuple(user_input)),)
user_input = [convert_table_value(item) for item in user_input]
sql = 'insert into vehicleinfo(%s) values (%s)'%(','.join(cols), ','.join(user_input),)
print sql
send_sql(sql)
redirect('/vehicles')
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 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 main():
sdb.main()
init_db()
#create dir for pics
if not os.path.isdir('./pictures'):
os.mkdir('./pictures')
cs = get_clients()
print cs
"""
dbporc = Process(target=sdb.run_sock_svr, args=())
dbporc.start()
websvr = Process(target=run, args=(app, 'wsgiref', '0.0.0.0', '8081'))
websvr.start()
dbporc.join()
websvr.join()
"""
sql_resnd = Timer()
sql_resnd.start()
#run(host='localhost', port=8081, Debug=True, reloader=False)
run(app, host='0.0.0.0', port=8081, server='cherrypy')
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 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 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 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 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 add_ship():
#tab_cols = sch.sql_table_columns['crs_shp_table']
user_input = []
#cols = re.findall('([A-Z1-9]+)', tab_cols)
cols = get_colomns('crs_shp_table')
for col in cols:
colname = col.lower()
user_input.append(request.forms.get(colname))
print user_input
#sql = 'INSERT INTO crs_shp_table %s VALUES %s'%(tab_cols, str(tuple(user_input)))
#dbconn = sdb.connect_orclex('haitong', '111111', sdb.DB_URL)
dbconn = sdb.connect()
dbconn.text_factory = str
cur = dbconn.cursor()
cur.execute('insert into crs_shp_table values (%s)'%(('?,'*len(cols))[:-1]), tuple(user_input))
dbconn.commit()
cur.close()
dbconn.close()
print 'insert ship done'
user_input = [convert_table_value(item) for item in user_input]
sql = 'insert into crs_shp_table values (%s)'%(','.join(user_input),)
send_sql(sql)
redirect('/ships')
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_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 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())
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())