def get_data():
schemas, tables = [], []
with pg_connection(*current_user.get_config()) as (con, cur, err):
with pg_log_err('list schemas and tables'):
cur.execute(query('list-schemas'))
schemas = cur.fetchall()
cur.execute(query('list-tables'))
tables = cur.fetchall()
schema_cols = ['', 'tables', 'views', 'foreign tables', 'temporary tables', 'functions', 'sequences']
table_cols = ['Column name', 'Column type', 'Column default', 'Column is nullable']
table_data = {'public': OrderedDict()}
for table in tables:
if table[0] not in table_data:
table_data[table[0]] = OrderedDict()
table_data[table[0]][table[1]] = {
'column-data': OrderedDict([
('colum-names', parse_pg_array(table[2])),
('column-types', parse_pg_array(table[3])),
('column-defaults', parse_pg_array(table[4])),
('column-is-nullable', parse_pg_array(table[5])),
]),
'table-size': table[6],
}
return (schemas, schema_cols, table_data, table_cols)
def create_tables(db_table):
db = db_table[0]
table = db_table[1]
pg.query('CREATE TABLE IF NOT EXISTS {} \
( id integer NOT NULL,\
name varchar(20) NOT NULL,\
PRIMARY KEY (id) ) ;'.format(table),
database=db)
for i in range(10):
insert_rows(db, table, i)
def delNoName(type, name):
sql = """
update user_info set %s=null where lower(%s)=lower('%s')
""" % (
type,
type,
name,
)
pg.query(sql)
print "del", type, name
def GET(self):
uid = getUserId()
today = time.strftime('%Y-%m-%d', time.localtime(time.time()))
template_data = {}
user_info_sql = """
select
u.f_name as user_name
,(select f_name from t_street ts where ts.f_id = u.f_street_id) as street_name
from
t_user u
where
u.f_id = %s
""" % uid
user = pg.query(user_info_sql)
if user:
user = user[0]
template_data["user_id"] = uid
template_data["today"] = today
template_data["street_name"] = user.street_name
template_data["coster_name"] = user.user_name
template_data["print"] = ""
parking_count_sql = """
select
sum(case when f_leave_stamp is null then 1 else 0 end) as uncost_times
,sum(case when f_cost_type = '正常缴费' then 1 else 0 end) as cost_times
,round(sum(case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as parking_range
,sum(f_act_cost) as act_cost
,sum(case when f_cost_type like '%免费%' then 1 else 0 end) as free_times
,round(sum(case when f_cost_type like '%免费%' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as free_range
from t_parking_record tpr
where
f_coster_id = %s
and
date(f_parking_stamp) = CURRENT_DATE
""" % uid
parking_count_info = pg.query(parking_count_sql)
if parking_count_info:
parking_count_info = parking_count_info[0]
template_data["car_count"] = parking_count_info.cost_times
template_data["cost_count"] = parking_count_info.act_cost
template_data["free_car_count"] = parking_count_info.free_times
template_data["free_car_time_count"] = parking_count_info.free_range
template_data["stay_car"] = parking_count_info.uncost_times
template_data["print"] = rendeHtml("mobile/dailyreport_print", **template_data)
return rendeHtml("mobile/dailyreport", **template_data)
def create_database(tables_db):
tables = tables_db[0]
db = tables_db[1]
pg.query('CREATE DATABASE {}'.format(db))
table_list = []
for i in range(tables):
table_list.append((db, get_random_string(10)))
pool = ThreadPool(int(math.sqrt(MAX_DB_CONNS)))
result = pool.map(create_tables, table_list)
pool.close()
pool.join()
def POST(self):
u = web.input().get("un", "").strip()
p = web.input().get("pw", "").strip()
ret = {
"error": 0
}
if u and p:
user = list(pg.db.select("t_user", where="f_account='%s'" % u, limit=1))
if user and user[0]["f_password"] == p:
loginUser(user[0]["f_id"], u)
ret["u_id"] = user[0]["f_id"]
sql = """
select
f_icon as icon
,f_name as title
,-1 as count
,f_key as key
,f_uri as url
from t_unit tu
where
tu.f_key like 'Mobile.%%'
and tu.f_type = 'Func'
and tu.f_id in (select f_unit_id from t_role_unit tru where tru.f_role_id in (select f_role_id from t_user_role where f_user_id = %s))
order by f_index desc
""" % ret["u_id"]
units = pg.query(sql)
url = "/mobile/main"
sql_user = """select * from t_user where f_id = %s""" % ret["u_id"]
user = pg.query(sql_user)
if user:
user = user[0]
if user.f_type == "收费人员":
url = "/mobile/main?u_id=" + str(ret["u_id"])
else:
url = "/mobile/queryparkingrecord_checkfilter"
ret["units"] = units
ret["url"] = url
else:
ret["error"] = 1
ret["errorMsg"] = "用户名或密码错误!"
else:
ret["error"] = 1
ret["errorMsg"] = "没有输入用户名和密码!"
return json.dumps(ret)
def GET(self, parking_id):
sql = """
select
p.f_id
,p.f_act_cost
,p.f_parking_code
,p.f_key
,p.f_car_type
,p.f_parking_stamp as f_parking_stamp1
,to_char(p.f_parking_stamp, 'HH24:MI:SS') as f_parking_stamp
,p.f_car_no
,(select f_name from t_street s where s.f_id = p.f_street_id) as f_street_name
from t_parking_record p
where
p.f_id = %s
""" % parking_id
data = pg.query(sql)[0]
data["f_time"] = datetime.datetime.now() - data["f_parking_stamp1"]
data["f_cost"] = str(utils.getActualCost(data["f_time"]) if utils.getActualCost(data["f_time"]) > 0 else 0.00)
data["f_time"] = str(data["f_time"])[0:8]
data["f_act_cost"] = str(data["f_act_cost"])[1:-3]
return rendeHtml("mobile/checkout", parking=data)
def getParkingData(self, uid):
sql = """
select
pr.f_id,
pr.f_act_cost::numeric,
pr.f_parking_code,
pr.f_key,
pr.f_car_no,
(
select
count(*)
from
t_parking_record tpr
where
tpr.f_cost::numeric > 0.00 and
tpr.f_cost_type = '逃逸' and
tpr.f_car_no = pr.f_car_no
) as f_escape_count,
to_char(pr.f_parking_stamp, 'HH24:MI:SS') as f_parking_stamp
from
t_parking p
left join
t_parking_record pr on p.f_key = pr.f_key
where
p.f_id in (select f_parking_id from t_user_parking where f_user_id = %s) and
p.f_state = 1
""" % (uid)
return pg.query(sql)
def getGodInfoFollow(user_id=None, god_name=None, recommand=False):
'''
modify by bigzhu at 15/08/06 17:05:22 可以根据god_name来取
modify by bigzhu at 15/08/28 17:09:31 推荐模式就是只查随机5个
modify by bigzhu at 15/08/28 17:30:38 没有社交帐号的不要查出来
'''
sql = '''
select u.id as god_id,
u.created_date as u_created_date,
* from god_info u
where
not ((twitter is null or twitter='') and (github is null or github='') and (instagram is null or instagram=''))
order by u.created_date desc
'''
if god_name:
sql = '''
select * from (%s) s where lower(user_name)=lower('%s')
''' % (sql, god_name)
if user_id:
sql = '''
select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id
order by ut.u_created_date desc
''' % (sql, user_id)
if recommand:
sql = '''
select * from (%s) s where s.followed is null
''' % sql
sql = '''
select * from (%s) s order by random() limit 5;
''' % sql
return pg.query(sql)
def getGodInfoFollow(user_id=None, god_name=None, recommand=False):
'''
modify by bigzhu at 15/08/06 17:05:22 可以根据god_name来取
modify by bigzhu at 15/08/28 17:09:31 推荐模式就是只查随机5个
modify by bigzhu at 15/08/28 17:30:38 没有社交帐号的不要查出来
'''
sql = '''
select u.id as god_id,
u.created_date as u_created_date,
* from god_info u
where
not ((twitter is null or twitter='') and (github is null or github='') and (instagram is null or instagram=''))
order by u.created_date desc
'''
if god_name:
sql = '''
select * from (%s) s where lower(user_name)=lower('%s')
''' % (sql, god_name)
if user_id:
sql = '''
select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id
order by ut.u_created_date desc
''' % (sql, user_id)
if recommand:
sql = '''
select * from (%s) s where s.followed is null
''' % sql
sql = '''
select * from (%s) s order by random() limit 5;
''' % sql
return pg.query(sql)
def getUserInfoGithub():
'''
create by bigzhu at 15/07/15 22:45:42
'''
sql = '''
select * from user_info u left join github_user g on u.user_name=g.login
'''
return pg.query(sql)
def GET(self):
uid = getUserId()
today = time.strftime('%Y-%m-%d', time.localtime(time.time()))
template_data = {}
user_info_sql = """
select
u.f_name as user_name
,(select f_name from t_street ts where ts.f_id = u.f_street_id) as street_name
from
t_user u
where
u.f_id = %s
""" % uid
user = pg.query(user_info_sql)
if user:
user = user[0]
template_data["today"] = today
template_data["street_name"] = user.street_name
template_data["coster_name"] = user.user_name
template_data["print"] = ""
parking_count_sql = """
select
count(p.f_state) as uncost_times,
sum(pr.f_act_cost) as act_cost
from
t_parking p
left join
t_parking_record pr on pr.f_key = p.f_key
where
p.f_id in (select f_parking_id from t_user_parking where f_user_id = %s) and
p.f_state = 1
""" % uid
parking_count_info = pg.query(parking_count_sql)
if parking_count_info:
parking_count_info = parking_count_info[0]
template_data["act_cost"] = parking_count_info.act_cost
template_data["stay_car"] = parking_count_info.uncost_times
template_data["print"] = rendeHtml("mobile/dailyshift_print", **template_data)
return rendeHtml("mobile/dailyshift", **template_data)
def followedWho(user_id):
sql = (
"""
select god_id from follow_who where user_id=%s
"""
% user_id
)
return pg.query(sql)
def getOpenidsByName(type, name):
sql = '''
select w.openid from user_info u, follow_who f, user_info u2, wechat_user w
where lower(u.%s)=lower('%s')
and u.id = f.god_id
and u2.id = f.user_id
and w.user_name=u2.user_name
''' % (type, name)
return pg.query(sql)
def getOpenidsByName(type, name):
sql = '''
select w.openid from user_info u, follow_who f, user_info u2, wechat_user w
where lower(u.%s)=lower('%s')
and u.id = f.god_id
and u2.id = f.user_id
and w.user_name=u2.user_name
''' % (type, name)
return pg.query(sql)
def getUserInfoGithub(user_name=None):
'''
create by bigzhu at 15/07/15 22:45:42
'''
sql = '''
select * from user_info u left join github_user g on lower(u.user_name)=lower(g.login)
'''
if user_name:
sql += " where user_name='%s' " % user_name
return pg.query(sql)
def getUserInfoGithub(user_name=None):
'''
create by bigzhu at 15/07/15 22:45:42
'''
sql = '''
select * from user_info u left join github_user g on lower(u.user_name)=lower(g.login)
'''
if user_name:
sql += " where user_name='%s' " % user_name
return pg.query(sql)
def Storage(params=None):
handle_params(params)
data = []
with pg_connection(*current_user.get_config()) as (con, cur, err):
with pg_log_err('list storage query'):
cur.execute(query('list-storage'))
data = cur.fetchall()
display_schemas = params.getlist('schema')
schemas = set()
treemap_data = {'name': 'root', 'children': []}
for (schema_name, table_name, t_size, t_tuples) in data:
schemas.add(schema_name)
if schema_name in display_schemas or len(display_schemas) == 0:
treemap_data['children'].append({'schema_name': schema_name, 'name': table_name,
'size': int(t_size), 'rows': int(t_tuples)})
p = Page()
# Header
p.add_page(Header(title='Storage',
js=['static/pages/storage.js',
'static/lib/d3/d3.js',
'static/lib/d3/lib/colorbrewer/colorbrewer.js'],
css=['static/pages/storage.css']))
p.add_page(Navigation(page='storage'))
with p.div({'class': 'container-fluid'}):
with p.div({'class': 'row form-row'}):
with p.div({'class': 'col-md-10'}):
with p.form({'id': 'schema-form', 'class': 'form-inline'}):
for schema in schemas:
with p.label({'class': 'checkbox-inline'}):
checked = (schema in display_schemas or len(display_schemas) == 0) and 'checked' or ''
with p.input({'type': 'checkbox', 'name': 'schema', 'value': '%s' % schema}, args=[checked]):
p.content(schema)
with p.div({'class': 'col-md-2'}):
with p.form({'id': 'mode-form', 'class': 'form-inline'}):
with p.label({'class': 'radio-inline'}):
with p.input({'type': 'radio', 'name': 'mode', 'value': 'size'}, args=['checked']):
p.content('Size')
with p.label({'class': 'radio-inline'}):
with p.input({'type': 'radio', 'name': 'mode', 'value': 'rows'}):
p.content('Rows (est.)')
with p.div({'class': 'row'}):
with p.div({'class': 'col-md-12'}):
with p.div({'id': 'treemap-chart'}): pass
with p.script():
p.content('PGUI.STORAGE.mk_treemap_chart(%s);' % json.dumps(treemap_data))
p.add_page(Footer())
return p
def getPayInfo(openid):
sql = (
"""
select id, card_number, stat_date, card_number, total_fee, status from pay
where openid='%s'
and status='payed'
order by created_date desc
"""
% openid
)
return list(pg.query(sql))
def GET(self):
sql = """
select
*
from
t_document
"""
docs = pg.query(sql)
return rendeHtml("mobile/doclist", docs=docs)
def getBindInfoByOpenid(openid):
sql = '''
select b.openid,
b.card_number,
b.car_number,
b.car_type, -- 车型
b.phone_number, -- 手机号
b.name, -- 姓名
b.id_number -- 身份证号
from bind_card_info b
where b.openid='%s'
''' % openid
return pg.query(sql)
def GET(self):
uid = getUserId()
sql = """
select count(*) as count from t_message
where
f_is_done = 0 and
abs(extract(epoch from now() - f_create_time)/60) < 5
and f_street_id = (select f_street_id from t_user where f_id = %s limit 1)
""" % uid
msg_count = pg.query(sql)[0]
return json.dumps(msg_count)
def POST(self):
web.header('Content-Type', 'application/json;charset=UTF-8')
try:
u_id = getUserId()
print "u_id is %s" % u_id
sql = """
select
f_icon as icon
,f_name as title
,-1 as count
,f_key as key
,f_uri as url
from t_unit tu
where
tu.f_key like 'Mobile.%%'
and tu.f_type = 'Func'
and tu.f_id in (select f_unit_id from t_role_unit tru where tru.f_role_id in (select f_role_id from t_user_role where f_user_id = %s))
order by f_index desc
""" % u_id
data = pg.query(sql)
url = "/mobile/index"
sql_user = """select * from t_user where f_id = %s""" % u_id
user = pg.query(sql_user)
if user:
user = user[0]
if user.f_type == "收费人员":
url = "/mobile/index?u_id=" + u_id
else:
url = "/mobile/queryparkingrecord_checkfilter"
result = {"success": "true", "data": data, "url": url}
return json.dumps(result, cls=ExtendedEncoder)
except Exception:
# 输出错误信息
msg = traceback.print_exc()
json_data = {'success': "false", 'message': '新增数据失败,详细信息:%s' % msg}
return json.dumps(json_data, cls=ExtendedEncoder)
def get_col_size():
if 'table-schema' in request.args and 'table-name' in request.args:
params = {'table-schema': escape(request.args['table-schema']),
'table-name': escape(request.args['table-name'])}
data = []
with pg_connection(*current_user.get_config()) as (con, cur, err):
with pg_log_err('fetching column size for %s' % params):
cur.execute(query('get-column-size'), params)
data = cur.fetchall()
return json.dumps(data)
return 'Please specify a table!'
def getMessages(user_id=None,
god_name=None,
type=None,
id=None,
limit=50,
offset=None,
last_message_id=None):
'''
create by bigzhu at 15/07/14 15:11:44 查出我 Follow 的用户的twitter message
modify by bigzhu at 15/07/17 01:39:21 过于复杂,合并sql,根据god_name也可以查
modify by bigzhu at 15/07/19 15:30:55 可以根据type和id查出某一条记录
modify by bigzhu at 15/07/22 12:49:35 limit 设定取多少条
modify by bigzhu at 15/08/13 17:20:15 建立view,查询简化得不行
modify by bigzhu at 15/08/16 18:09:53 支持对last_time的查询
modify by bigzhu at 15/12/11 11:25:32 改用last_message_id 不再用last_time
'''
sql = "select * from messages"
if last_message_id is not None:
where = ' where id>%s' % last_message_id
sql += where
sql += " order by created_at desc "
if type and id:
sql = '''
select * from (%s) s
where s.m_type='%s'
and s.id = %s
''' % (sql, type, id)
if god_name:
sql = '''
select * from (%s) s
where lower(s.user_name)=lower('%s')
''' % (sql, god_name)
if user_id:
sql = '''
select * from (%s) s
where lower(s.user_name) in (
select lower(user_name) from user_info where id in(
select god_id from follow_who where user_id=%s
)
)
''' % (sql, user_id)
if limit:
sql = '''
select * from (%s) s
limit %s
''' % (sql, limit)
if offset:
sql = sql + ' offset %s' % offset
return pg.query(sql)
def getUserInfoTwitterUser(user_id=None):
sql = '''
select u.id as god_id, 0 followed,
u.created_date as u_created_date,
* from user_info u, twitter_user tu
where u.twitter = tu.screen_name
order by tu.created_date desc
'''
if user_id:
sql = '''
select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id
order by ut.u_created_date desc
''' % (sql, user_id)
return pg.query(sql)
def getUserInfoTwitterUser(user_id=None):
sql = '''
select u.id as god_id, 0 followed,
u.created_date as u_created_date,
* from user_info u, twitter_user tu
where u.twitter = tu.screen_name
order by tu.created_date desc
'''
if user_id:
sql = '''
select * from (%s) ut left join (select god_id followed_god_id, 1 followed from follow_who where user_id=%s) f on ut.god_id=f.followed_god_id
order by ut.u_created_date desc
''' % (sql, user_id)
return pg.query(sql)
def GET(self, id):
sql = """
select
*
from
t_document
where f_id=%d
""" % int(id)
docs = pg.query(sql)
doc = None
if docs:
doc = docs[0]
return rendeHtml("mobile/doccontent", doc=doc)
def get_size_db(db_name):
"""
Return the size of the data of a given database.
This query only check the data of all tables excluding indexes data
If something goes wrong, return 0
"""
status, result_list = pg.query(
"SELECT SUM(pg_table_size(c.oid)) \
FROM pg_class c \
JOIN pg_database d ON d.datdba = c.relowner \
WHERE c.relkind = 'r' \
AND d.datname = \'{}\';".format(db_name),
db_name)
if status:
return result_list[0][0]
else:
return 0
def getPayInfo(openid=None, statuses=None, user_id=None, id=None):
sql = '''
select id, openid, card_number, stat_date, card_number, total_fee, status from pay
where 1=1
'''
if openid:
sql += " and openid='%s' " % openid
if statuses:
in_statuses = db_bz.formatToInSql(statuses)
sql += " and status in (%s) " % in_statuses
if user_id:
sql += " and user_id=%s " % user_id
if id:
sql += " and id=%s " % id
sql += " order by created_date desc "
return list(pg.query(sql))
def getWechatUserBindInfoByOpenid(openid):
'''
查出用户信息
'''
sql = '''
select w.openid,
w.id as wechat_user_id,
b.card_number,
b.car_number,
b.car_type, -- 车型
b.phone_number, -- 手机号
b.name, -- 姓名
b.id_number -- 身份证号
from wechat_user w left join bind_card_info b
on w.id = b.wechat_user_id
where w.openid='%s'
''' % openid
return pg.query(sql)
def getMessages(user_id=None, god_name=None, type=None, id=None, limit=50, offset=None, last_time=None):
'''
create by bigzhu at 15/07/14 15:11:44 查出我 Follow 的用户的twitter message
modify by bigzhu at 15/07/17 01:39:21 过于复杂,合并sql,根据god_name也可以查
modify by bigzhu at 15/07/19 15:30:55 可以根据type和id查出某一条记录
modify by bigzhu at 15/07/22 12:49:35 limit 设定取多少条
modify by bigzhu at 15/08/13 17:20:15 建立view,查询简化得不行
modify by bigzhu at 15/08/16 18:09:53 支持对last_time的查询
'''
sql = "select * from messages"
if last_time is not None:
last_time = time_bz.datetimeToTimestamp(last_time) # 转为timestamp
last_time = last_time - 30*10*10 # 后30分钟的也取出来
where = ' where created_at>to_timestamp(%s)' % last_time
sql += where
if type and id:
sql = '''
select * from (%s) s
where s.m_type='%s'
and s.id = %s
''' % (sql, type, id)
if god_name:
sql = '''
select * from (%s) s
where lower(s.user_name)=lower('%s')
''' % (sql, god_name)
if user_id:
sql = '''
select * from (%s) s
where lower(s.user_name) in (
select lower(user_name) from user_info where id in(
select god_id from follow_who where user_id=%s
)
)
''' % (sql, user_id)
if limit:
sql = '''
select * from (%s) s
limit %s
''' % (sql, limit)
if offset:
sql = sql + ' offset %s' % offset
return pg.query(sql)
def getMessages(user_id=None, god_name=None, type=None, id=None, limit=50, offset=None, last_message_id=None):
'''
create by bigzhu at 15/07/14 15:11:44 查出我 Follow 的用户的twitter message
modify by bigzhu at 15/07/17 01:39:21 过于复杂,合并sql,根据god_name也可以查
modify by bigzhu at 15/07/19 15:30:55 可以根据type和id查出某一条记录
modify by bigzhu at 15/07/22 12:49:35 limit 设定取多少条
modify by bigzhu at 15/08/13 17:20:15 建立view,查询简化得不行
modify by bigzhu at 15/08/16 18:09:53 支持对last_time的查询
modify by bigzhu at 15/12/11 11:25:32 改用last_message_id 不再用last_time
'''
sql = "select * from messages"
if last_message_id is not None:
where = ' where id>%s' % last_message_id
sql += where
sql += " order by created_at desc "
if type and id:
sql = '''
select * from (%s) s
where s.m_type='%s'
and s.id = %s
''' % (sql, type, id)
if god_name:
sql = '''
select * from (%s) s
where lower(s.user_name)=lower('%s')
''' % (sql, god_name)
if user_id:
sql = '''
select * from (%s) s
where lower(s.user_name) in (
select lower(user_name) from user_info where id in(
select god_id from follow_who where user_id=%s
)
)
''' % (sql, user_id)
if limit:
sql = '''
select * from (%s) s
limit %s
''' % (sql, limit)
if offset:
sql = sql + ' offset %s' % offset
return pg.query(sql)
def GET(self):
uid = getUserId()
sql = """
select
*
,(select f_name from t_parking tp1 where tp1.f_id = tm.f_parking_id) as f_parking_name
from
t_message tm
where
f_is_done = 0 and
abs(extract(epoch from now() - f_create_time)/60) < 5
and
f_street_id = (select f_street_id from t_user where f_id = %s limit 1)
order by f_create_time desc
""" % uid
msgs = pg.query(sql)
return rendeHtml("mobile/msglist", msgs=msgs)
def getCardinfos(openid=None, id=None):
sql = '''
select b.id,
b.openid,
b.card_number,
b.car_number,
b.car_type, -- 车型
b.phone_number, -- 手机号
b.name, -- 姓名
b.id_number -- 身份证号
from bind_card_info b
where is_delete=0
'''
if openid:
sql += " and b.openid='%s' " % openid
if id:
sql += " and b.id=%s " % id
return pg.query(sql)
def GET(self):
sql = """
select
f_street_id
,(select f_name from t_street ts where ts.f_id = tpr.f_street_id) as f_street_name
,sum(case when f_leave_stamp is null then 1 else 0 end) as uncost_times
,sum(case when f_cost_type = '正常缴费' then 1 else 0 end) as cost_times
,round(sum(case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as parking_range
,sum(f_act_cost) as act_cost
,sum(case when f_cost_type like '%免费%' then 1 else 0 end) as free_times
,round(sum(case when f_cost_type like '%免费%' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as free_range
from t_parking_record tpr
where
date(f_parking_stamp) = CURRENT_DATE
group by tpr.f_street_id
"""
data = pg.query(sql)
return rendeHtml("mobile/querystreetcount", result=data)
def POST(self, sid):
sql = """
select
*
,(select f_phone from t_user tu where tu.f_id = tpr.f_coster_id) as f_phone
,round((case when f_cost_type = '正常缴费' then abs(extract(epoch from f_leave_stamp - f_parking_stamp)/60) else 0 end)::numeric,0) || '分钟' as f_time
from t_parking_record tpr where f_id = %s
""" % sid
datas = pg.query(sql)
parking_record = {}
if datas:
parking_record = datas[0]
template_data = dict(parking_record.items())
template_data["f_time"] = str(parking_record["f_leave_stamp"] - parking_record["f_parking_stamp"])[0:8]
template_data["f_cost"] = str(parking_record["f_cost"])[1:-3]
template_data["f_act_cost"] = str(parking_record["f_act_cost"])[1:-3]
template_data["f_phone"] = template_data["f_phone"][-4:]
return rendeHtml("mobile/checkout_report", **template_data)
def GET(self):
isAjax = bool(web.input().get("ajax"))
u_id = getUserId()
sql = """
select p.*,rd.f_car_type,to_char(rd.f_parking_stamp,'HH24:MI:SS'),
rd.f_car_no,s.f_name as f_street_name
from t_parking p left join t_parking_record rd on rd.f_key = p.f_key
left join t_street s on p.f_street_id = s.f_id
left join t_parking_image tpi on tpi.f_key = rd.f_key
left join t_user_parking tup on tup.f_parking_id = p.f_id
where
tup.f_user_id = %s
order by p.f_id
""" % u_id
data = pg.query(sql)
if isAjax:
return json.dumps(data)
else:
return rendeHtml("mobile/index", parkings=data)
def insert_rows(db, table, insert_id):
f_name = get_random_string(10)
pg.query(
'INSERT INTO {} (id, name) \
VALUES ( \'{}\', \'{}\')'.format(table, insert_id, f_name), db)
def delNoName(type, name):
sql = '''
update user_info set %s=null where lower(%s)=lower('%s')
''' % (type, type, name)
pg.query(sql)
print 'del', type, name
def getTwitterMessages():
sql = '''
select * from twitter_message tm, twitter_user tu
where tm.t_user_id=tu.id_str order by tm.created_at desc
'''
return pg.query(sql)
def Index(params=None):
"""
Renders the index page which displays some generic connections
information and links to all the activated modules.
"""
handle_params(params)
p = Page()
# Header
p.add_page(Header(title='Index'))
p.add_page(Navigation(page='index'))
with p.div({'class': 'container-fluid'}):
# Connection information
data, params = [], defaultdict(str)
param_keys = ('server_version', 'server_encoding', 'client_encoding',
'is_superuser', 'TimeZone')
with pg_connection(*current_user.get_config()) as (con, cur, err):
for k in param_keys:
params[k] = con.get_parameter_status(k)
with pg_log_err('list databases'):
cur.execute(query('list-databases'))
data = cur.fetchall()
with p.div({'class': 'row'}):
with p.div({'class': 'col-md-2'}):
with p.div({'class': 'btn-group'}):
with p.button({
'class': 'btn btn-default dropdown-toggle',
'data-toggle': 'dropdown',
'aria-expanded': 'false'
}):
p.content(
'Switch database <span class="caret"></span>')
with p.ul({'class': 'dropdown-menu', 'role': 'menu'}):
for n, d in enumerate(data):
with p.li():
with p.a({'href': 'index?database=%s' % d[0]}):
p.content(d[0])
if n < len(data) - 1:
with p.li({'class': 'divider'}):
pass
with p.div({'class': 'col-md-4 small'}):
p.content('<strong>%s</strong>' % current_user.database)
p.content(
'<br>%s@%s:%s' %
(current_user.name, current_user.host, current_user.port))
with p.div({'class': 'col-md-6 small'}):
with p.ul({'class': 'list-inline'}):
for k, v in sorted(params.items()):
with p.li():
p.content('%s: %s' % (k, v))
with p.hr():
pass
# Modules
cols = 12
col_size = 4
col = 0
for page in PAGES[1:]:
if col == 0:
with p.div({'class': 'row'}, close=False):
pass
with p.div({'class': 'col-md-%s' % col_size}):
with p.a({'href': '/%s' % page['name']}):
with p.div({'class': 'page-link'}):
with p.span({
'class':
'page-icon glyphicon glyphicon-%s' %
page['icon']
}):
pass
with p.h3():
p.content(page['caption'])
p.content(page['desc'])
col = (col + col_size) % cols
if col == 0:
p.close('div')
p.add_page(Footer())
return p
def Storage(params=None):
handle_params(params)
data = []
with pg_connection(*current_user.get_config()) as (con, cur, err):
with pg_log_err('list storage query'):
cur.execute(query('list-storage'))
data = cur.fetchall()
display_schemas = params.getlist('schema')
schemas = set()
treemap_data = {'name': 'root', 'children': []}
for (schema_name, table_name, t_size, t_tuples) in data:
schemas.add(schema_name)
if schema_name in display_schemas or len(display_schemas) == 0:
treemap_data['children'].append({
'schema_name': schema_name,
'name': table_name,
'size': int(t_size),
'rows': int(t_tuples)
})
p = Page()
# Header
p.add_page(
Header(title='Storage',
js=[
'static/pages/storage.js', 'static/lib/d3/d3.js',
'static/lib/d3/lib/colorbrewer/colorbrewer.js'
],
css=['static/pages/storage.css']))
p.add_page(Navigation(page='storage'))
with p.div({'class': 'container-fluid'}):
with p.div({'class': 'row form-row'}):
with p.div({'class': 'col-md-10'}):
with p.form({'id': 'schema-form', 'class': 'form-inline'}):
for schema in schemas:
with p.label({'class': 'checkbox-inline'}):
checked = (schema in display_schemas
or len(display_schemas)
== 0) and 'checked' or ''
with p.input(
{
'type': 'checkbox',
'name': 'schema',
'value': '%s' % schema
},
args=[checked]):
p.content(schema)
with p.div({'class': 'col-md-2'}):
with p.form({'id': 'mode-form', 'class': 'form-inline'}):
with p.label({'class': 'radio-inline'}):
with p.input(
{
'type': 'radio',
'name': 'mode',
'value': 'size'
},
args=['checked']):
p.content('Size')
with p.label({'class': 'radio-inline'}):
with p.input({
'type': 'radio',
'name': 'mode',
'value': 'rows'
}):
p.content('Rows (est.)')
with p.div({'class': 'row'}):
with p.div({'class': 'col-md-12'}):
with p.div({'id': 'treemap-chart'}):
pass
with p.script():
p.content('PGUI.STORAGE.mk_treemap_chart(%s);' %
json.dumps(treemap_data))
p.add_page(Footer())
return p
def delGithubUser(user_name):
sql = '''
update user_info set github=null where lower(github)=lower('%s')
''' % user_name
pg.query(sql)
