def get(self):
cur_page = self.get_argument('cur_page', '1')
if cur_page == '0':
sql = """
select id, name
from wx_discount_type
order by id
"""
conn = Postgres()
data = conn.fetchall(sql)
return self.finish(json.dumps(data))
else:
page_size = 5
sql = """
select (
select count(*)
from wx_discount_type
) as total,
id, type, name
from wx_discount_type
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
conn = Postgres()
data = conn.fetchall(sql)
table_data = {
'data': data,
'page_size': page_size,
'total': data[0]['total'] if data else 0
}
return self.finish(table_data)
def post(self):
discount_id = self.get_argument('discount_id', None)
openid = self.get_argument('openid', None)
res = {'code': 0}
if not discount_id or not openid:
res['code'] = -1
res['msg'] = '赠送失败！'
return self.finish(res)
try:
conn = Postgres()
end_time = datetime.strftime(datetime.now() + timedelta(days=7),
'%Y-%m-%d')
sql = """
insert into wx_user_discount_rel (openid, discount_id, end_time)
values ('%s', %d, '%s');
""" % (openid, int(discount_id), end_time)
conn.execute(sql)
res['msg'] = '赠送成功！'
return self.finish(res)
except Exception:
res['code'] = -1
res['msg'] = '赠送失败！'
return self.finish(res)
def post(self):
type = self.get_argument('type', None)
name = self.get_argument('name', None)
res = {'code': 0}
if not type:
res['code'] = -1
res['msg'] = '类型不能为空！'
return self.finish(res)
if not name:
res['code'] = -1
res['msg'] = '名称不能为空！'
return self.finish(res)
sql = """
insert into wx_discount_type (type, name)
values ('%s', '%s')
""" % (type, name)
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '添加成功'
except Exception as e:
logger.error('优惠券类型添加失败：%s' % e)
res['code'] = -1
res['msg'] = '添加失败！'
finally:
return self.finish(res)
def get(self):
openid = self.get_argument('openid', None)
discount_id = self.get_argument('discount_id', '0')
logger.info('openid: %s' % openid)
logger.info('discount_id: %s' % discount_id)
sql = """
select openid, discount_id, discount, name, type, rule
from wx_user_discount_rel as wudr
inner join wx_discount as wd on wd.id = wudr.discount_id
inner join wx_discount_type as wdt on wdt.id = wd.type_id
where openid = '%s'
and discount_id = %d
and wudr.state = false
""" % (openid, int(discount_id))
res = {'code': 0}
conn = Postgres()
data = conn.fetchone(sql)
if data:
res['msg'] = '扫码成功'
res['data'] = data
else:
res['code'] = -1
res['msg'] = '该优惠券已失效，请勿重复扫码！'
return self.finish(res)
def post(self):
openid = self.get_argument('openid', None)
discount_id = self.get_argument('discount_id', None)
use_time = datetime.strftime(datetime.now(), '%Y-%m-%d')
conn = Postgres()
sql = """
update wx_user_discount_rel
set state = true, use_time = '%s'
where openid = '%s'
and discount_id = %d;
""" % (use_time, openid, int(discount_id))
sql += """
update wx_user
set score = score + 20, experience = experience + 20
where openid = '%s';
""" % openid
res = {
'code': 0,
}
try:
conn.execute(sql)
res['msg'] = '使用成功！'
except Exception:
res['code'] = -1
res['msg'] = '使用失败，请重新扫码！'
return self.finish(res)
def post(self):
username = self.get_argument('username', None)
password = self.get_argument('password', None)
res = {
'code': 0
}
if not username or not password:
res['code'] = -1
res['msg'] = '账号或密码不能为空！'
return self.finish(res)
password_md5 = hashlib.md5(password.encode('utf-8')).hexdigest()
conn = Postgres()
sql = """
select id
from admin_user
where username = '******'
and password = '******'
""" % (username, password_md5)
user = conn.fetchone(sql)
if not user:
res['code'] = -1
res['msg'] = '账号或密码错误！'
else:
res['msg'] = '登录成功！'
self.set_secure_cookie('username', username, expires=time.time() + (30 * 60))
return self.finish(res)
def put(self):
id = self.get_argument('id', '0')
type_id = self.get_argument('type_id', '0')
discount = self.get_argument('discount', '')
score = self.get_argument('score', '0')
count = self.get_argument('count', '0')
rule = self.get_argument('rule', '')
state = self.get_argument('state', 'false')
sql = """
update wx_discount
set type_id = %d, discount = '%s', score = %d, count = %d, rule = '%s', state = %s
where id = %d
""" % (int(type_id), discount, int(score), int(count), rule, state, int(id))
res = {
'code': 0
}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '保存成功！'
except Exception as e:
logger.error('优惠券保存失败：%s' % e)
res['code'] = -1
res['msg'] = '保存失败！'
finally:
return self.finish(res)
def post(self):
type_id = self.get_argument('type_id', None)
discount = self.get_argument('discount', None)
score = self.get_argument('score', None)
state = self.get_argument('state', None)
rule = self.get_argument('rule', None)
res = {
'code': 0
}
if not type_id:
res['code'] = -1
res['msg'] = '优惠券类型不能为空！'
return self.finish(res)
sql = """
insert into wx_discount (type_id, discount, score, state, rule)
values (%d, '%s', %d, %s, '%s')
""" % (int(type_id), discount, int(score), state, rule)
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '添加成功'
except Exception as e:
logger.error('优惠券添加失败：%s' % e)
res['code'] = -1
res['msg'] = '添加失败！'
finally:
return self.finish(res)
def post(self):
username = self.get_argument('username', None)
password = self.get_argument('password', None)
res = {
'code': 0
}
if not username or not password:
res['code'] = -1
res['msg'] = '账号或密码不能为空！'
return self.finish(res)
conn = Postgres()
user = conn.fetchone("select id from admin_user where username = '******'" % username)
if user:
res['code'] = -1
res['msg'] = '该用户已经存在！'
return self.finish(res)
password_md5 = hashlib.md5(password.encode('utf-8')).hexdigest()
try:
sql = """
insert into admin_user (username, password)
values ('%s', '%s')
""" % (username, password_md5)
conn.execute(sql)
res['msg'] = '用户注册成功！'
except Exception as e:
res['code'] = -1
res['msg'] = '用户注册失败！'
logger.error(e)
finally:
return self.finish(res)
def delete(self):
id = self.get_argument('id', '0')
sql = """
delete from wx_discount_type
where id = %d
""" % int(id)
res = {'code': 0}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '删除成功！'
except Exception as e:
logger.error('优惠券类型删除失败：%s' % e)
res['code'] = -1
res['msg'] = '删除失败！'
finally:
self.finish(res)
def post(self):
conn = Postgres()
fans_sql = """
select
(select count(*) from wx_user) as total,
(select count(*) from wx_user where sex = 1) as man,
(select count(*) from wx_user where sex = 2) as woman,
(select count(*) from wx_user where sex = 0) as other
"""
fans_data = conn.fetchone(fans_sql)
discount_sql = """
select
(select count(*) from wx_user_discount_rel) as total,
(select count(*) from wx_user_discount_rel where state = true) as use,
(select count(*) from wx_user_discount_rel where state = false) as un_use;
"""
discount_data = conn.fetchone(discount_sql)
experience_sql = """
select username, experience
from wx_user
order by experience desc, id desc
limit 10;
"""
experience_data = conn.fetchall(experience_sql)
score_sql = """
select username, score
from wx_user
order by score desc, id desc
limit 10;
"""
score_data = conn.fetchall(score_sql)
data = {
'fans': fans_data,
'discount': discount_data,
'experience': experience_data,
'score': score_data
}
return self.finish(json.dumps(data))
def get(self):
cur_page = self.get_argument('cur_page', None)
limit = ''
page_size = 0
if cur_page:
page_size = 5
limit += 'limit %d offset %d' % (page_size, (abs(int(cur_page)) - 1) * page_size)
sql = """
select (
select count(*)
from wx_discount
) as total,
wd.id, name, type_id, discount, score, count, rule, state
from wx_discount as wd
left join wx_discount_type as wdt on wdt.id = wd.type_id
order by score desc, id desc
""" + limit + """
"""
conn = Postgres()
data = conn.fetchall(sql)
optionSql = """
select id, name
from wx_discount_type
order by id
"""
optionData = conn.fetchall(optionSql)
total = data[0]['total'] if data else 0
table_data = {
'data': data,
'page_size': page_size or total,
'total': total,
'options': optionData
}
return self.finish(table_data)
def get(self):
cur_page = self.get_argument('cur_page', '1')
search_val = self.get_argument('search_val', '')
is_admin = self.get_argument('is_admin', None)
where = 'where true'
if search_val:
where += " and username like '%{}%'".format(search_val)
if is_admin and int(is_admin) == 1:
where += " and is_admin = false"
elif is_admin and int(is_admin) == 2:
where += " and is_admin = true"
page_size = 5
sql = """
select (
select count(*)
from wx_user
""" + where + """
) as total,
id, openid, username, sex, image_url, city, score, experience, is_admin
from wx_user
""" + where + """
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
conn = Postgres()
data = conn.fetchall(sql)
table_data = {
'data': data,
'page_size': page_size,
'total': data[0]['total'] if data else 0
}
return self.finish(table_data)
def get(self):
code = self.get_argument('code', None)
logger.info('code: %s' % code)
try:
user_auth = UserAuth(APPID, APPSECRET, code)
access_token, openid = user_auth.get_access_token()
except Exception as e:
return self.write(str(e))
# openid = 'oBGCb1GE38DXO03ebeY0MtnfJKmc'
conn = Postgres()
sql = """
select wd.id,name,type,discount,score,count,rule
from wx_discount as wd
inner join wx_discount_type as wdt on wd.type_id = wdt.id
where state = true
order by score desc, id desc
"""
data = conn.fetchall(sql)
return self.render('discount.html', data=data, openid=openid)
def put(self):
id = self.get_argument('id', '0')
type = self.get_argument('type', '')
name = self.get_argument('name', '')
sql = """
update wx_discount_type
set type = '%s', name = '%s'
where id = %d
""" % (type, name, int(id))
res = {'code': 0}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '保存成功！'
except Exception as e:
logger.error('优惠券类型保存失败：%s' % e)
res['code'] = -1
res['msg'] = '保存失败！'
finally:
return self.finish(res)
def post(self):
openid = self.get_argument('openid', None)
res = {
'code': 0
}
print(openid)
if openid:
now = datetime.strftime(datetime.now(), '%Y-%m-%d')
conn = Postgres()
data = conn.fetchone("select date from wx_user where openid = '%s'" % openid)
if data['date'] and data['date'] == now:
res['code'] = -1
res['msg'] = '积分领取失败'
return self.finish(res)
conn.execute(
"update wx_user set score = score + 5, experience = experience + 5, date = '%s' where openid = '%s'" % (
now, openid))
res['msg'] = '积分领取成功'
return self.finish(res)
res['code'] = -1
res['msg'] = '积分领取失败'
return self.finish(res)
def delete(self):
openid = self.get_argument('openid', None)
sql = """
delete from wx_user
where openid = '%s';
""" % openid
sql += """
delete from wx_user_discount_rel
where openid = '%s';
""" % openid
res = {'code': 0}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '删除成功！'
except Exception as e:
logger.error('用户管理删除失败：%s' % e)
res['code'] = -1
res['msg'] = '删除失败！'
finally:
self.finish(res)
def put(self):
id = self.get_argument('id', '0')
score = self.get_argument('score', '0')
experience = self.get_argument('experience', '0')
is_admin = self.get_argument('is_admin', 'false')
sql = """
update wx_user
set score = %d, experience = %d, is_admin = %s
where id = %d
""" % (int(score), int(experience), is_admin, int(id))
res = {'code': 0}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '保存成功！'
except Exception as e:
logger.error('用户管理保存失败：%s' % e)
res['code'] = -1
res['msg'] = '保存失败！'
finally:
return self.finish(res)
def put(self):
id = self.get_argument('id', '0')
state = self.get_argument('state', 'false')
use_time = datetime.strftime(datetime.now(), '%Y-%m-%d')
sql = """
update wx_user_discount_rel
set state = %s, use_time = '%s'
where id = %d
""" % (state, use_time, int(id))
res = {'code': 0}
try:
conn = Postgres()
conn.execute(sql)
res['msg'] = '保存成功！'
except Exception as e:
logger.error('优惠券保存失败：%s' % e)
res['code'] = -1
res['msg'] = '保存失败！'
finally:
return self.finish(res)
def get(self):
# 根据code获取网页access_token和openid
code = self.get_argument('code', None)
logger.info('code: %s' % code)
try:
user_auth = UserAuth(APPID, APPSECRET, code)
info_data = user_auth.get_user_info()
except Exception as e:
return self.write(str(e))
# info_data = {
# 'openid': 'oBGCb1GE38DXO03ebeY0MtnfJKmc',
# 'nickname': '李家富',
# 'sex': 1,
# 'language': 'zh_CN',
# 'city': '广州',
# 'province': '广东', 'country': '中国',
# 'headimgurl': 'http://thirdwx.qlogo.cn/mmopen/vi_32/Q0EBmjmic8Is2ezTGhysF7JcUjSjnNVOYrCVoOJ6hIBNziaQiaFN76OSIpa7OpdibS3Z2qeGfOSV3mibklxGHV3VLCg/132',
# 'privilege': []
# }
conn = Postgres()
data = conn.fetchone("select id from wx_user where openid = '%s'" % info_data.get('openid'))
logger.info('查看数据库是否存在该用户信息: %s' % data)
if not data:
sql = """
insert into wx_user (openid, username, sex, image_url, province, city, score)
values ('%s', '%s', %d, '%s', '%s', '%s', 15);
""" % (
info_data.get('openid'), info_data.get('nickname'), info_data.get('sex'), info_data.get('headimgurl'),
info_data.get('province'), info_data.get('city')
)
end_time = datetime.strftime(datetime.now() + timedelta(days=7), '%Y-%m-%d')
sql += """
insert into wx_user_discount_rel (openid, discount_id, end_time)
values ('%s', %d, '%s');
""" % (info_data.get('openid'), 1, end_time)
conn.execute(sql)
else:
sql = """
update wx_user
set username = '******', sex = %d, image_url = '%s', province = '%s', city = '%s'
where openid = '%s'
""" % (
info_data.get('nickname'), info_data.get('sex'), info_data.get('headimgurl'), info_data.get('province'),
info_data.get('city'), info_data.get('openid')
)
conn.execute(sql)
user_sql = """
select openid, username, image_url, province, city, score, experience, is_admin, date
from wx_user
where openid = '%s'
""" % info_data.get('openid')
user_data = conn.fetchone(user_sql)
now = datetime.strftime(datetime.now(), '%Y-%m-%d')
discount_sql = """
select wd.id,discount,name,end_time,type,rule
from wx_user_discount_rel as wud
left join wx_discount as wd on wd.id = wud.discount_id
inner join wx_discount_type as wdt on wdt.id = wd.type_id
where openid = '%s'
and end_time >= '%s'
and wud.state = false
order by id desc
""" % (info_data.get('openid'), now)
discount_data = conn.fetchall(discount_sql)
info = {
'openid': user_data['openid'],
'username': user_data['username'] or '密语君',
'province': user_data['province'] or '保密',
'city': user_data['city'] or '保密',
'image_url': user_data['image_url'],
'score': user_data['score'],
'experience': user_data['experience'],
'is_admin': user_data['is_admin'],
'discount': len(discount_data),
'discount_list': discount_data,
'is_receive': user_data['date'] >= datetime.strftime(datetime.now(), '%Y-%m-%d')
}
logger.info('用户信息: %s' % info)
return self.render('personal.html', info=info)
def post(self):
discount_id = self.get_argument('discount_id', None)
openid = self.get_argument('openid', None)
res = {
'code': 0
}
if not discount_id or not openid:
res['code'] = -1
res['msg'] = '领取失败'
return self.finish(res)
conn = Postgres()
cur_time = datetime.strftime(datetime.now(), '%Y-%m-%d')
sql = """
select *
from wx_user_discount_rel
where discount_id = %d
and openid = '%s'
and end_time >= '%s'
and state = false
""" % (int(discount_id), openid, cur_time)
count = conn.fetchall(sql)
if len(count):
res['code'] = -1
res['msg'] = '每种类型优惠券只能领取一张，请使用后再领取！'
return self.finish(res)
discount = conn.fetchone("select count from wx_discount where id = %d" % int(discount_id))
if discount['count'] <= 0:
res['code'] = -1
res['msg'] = '对不起，该优惠券已经被领完了！'
return self.finish(res)
user_score = conn.fetchone("select score from wx_user where openid = '%s'" % openid)
discount_score = conn.fetchone("select score from wx_discount where id = %d" % int(discount_id))
if not user_score or user_score['score'] < discount_score['score']:
res['code'] = -1
res['msg'] = '对不起，您的积分不够哦！'
return self.finish(res)
try:
end_time = datetime.strftime(datetime.now() + timedelta(days=7), '%Y-%m-%d')
sql = """
insert into wx_user_discount_rel (openid, discount_id, end_time)
values ('%s', %d, '%s');
""" % (openid, int(discount_id), end_time)
sql += """
update wx_discount set count = count - 1
where id = %d;
""" % int(discount_id)
sql += """
update wx_user set score = score - %d
where openid = '%s';
""" % (discount_score['score'], openid)
conn.execute(sql)
res['msg'] = '领取成功'
return self.finish(res)
except Exception:
res['code'] = -1
res['msg'] = '领取失败'
return self.finish(res)
def get(self):
cur_page = self.get_argument('cur_page', '1')
search_val = self.get_argument('search_val', '')
time_filter = self.get_argument('end_time', None)
use_filter = self.get_argument('use_state', None)
where = ''
if search_val:
where += "where username like '%{}%'".format(search_val)
filter = ''
cur_time = datetime.strftime(datetime.now(), '%Y-%m-%d')
if time_filter and int(time_filter) == 1:
filter += " and end_time >= '%s'" % cur_time
elif time_filter and int(time_filter) == 2:
filter += " and end_time < '%s'" % cur_time
if use_filter and int(use_filter) == 1:
filter += " and wudr.state = false"
elif use_filter and int(use_filter) == 2:
filter += " and wudr.state = true"
page_size = 5
# 获取用户信息
sql = """
with wudr as (
select
wudr.id,
wu.id as user_id,
wdt.name as type,
wd.discount,
wd.rule,
wudr.end_time,
wudr.use_time,
wudr.state
from wx_user_discount_rel as wudr
left join wx_user as wu on wu.openid = wudr.openid
left join wx_discount as wd on wd.id = wudr.discount_id
left join wx_discount_type as wdt on wdt.id = wd.type_id
where true
""" + filter + """
order by wudr.id desc
)
select (
select count(distinct wu.id)
from wx_user as wu
right join wudr on wudr.user_id = wu.id
""" + where + """
) as total,
wu.id, username, image_url,
count(wudr.id) as discount_count
from wx_user as wu
right join wudr on wudr.user_id = wu.id
""" + where + """
group by wu.id, username, image_url
having count(wudr.id) > 0
order by discount_count desc, wu.id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
conn = Postgres()
data = conn.fetchall(sql)
# 获取每个用户对应的优惠券信息
for d in data:
dSql = """
select
wudr.id,
wu.id as user_id,
wdt.name as type,
wd.discount,
wd.rule,
wudr.end_time,
wudr.use_time,
wudr.state
from wx_user_discount_rel as wudr
left join wx_user as wu on wu.openid = wudr.openid
left join wx_discount as wd on wd.id = wudr.discount_id
left join wx_discount_type as wdt on wdt.id = wd.type_id
where wu.id = %d
""" + filter + """
order by wudr.id desc
"""
dSql = dSql % d['id']
dData = conn.fetchall(dSql)
d['discount_id'] = dData
table_data = {
'data': data,
'page_size': page_size,
'total': data[0]['total'] if data else 0
}
return self.finish(table_data)