def get_all_house_info():
check_session_validation()
res = DBSession.execute(text('select * from house order by id DESC'), {})
rows = res.fetchall()
jsonData = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
return jsonData
def get_contact():
check_session_validation()
res = DBSession.execute(text('select * from contact'))
row = res.fetchone()
jsonData = json.dumps(dict(row.items()))
DBSession.commit()
print jsonData
return jsonData
def get_banner_house_list():
check_session_validation()
res = DBSession.execute(
text('select * from house, banner where house.id = banner.houseid'),
{})
rows = res.fetchall()
jsonData = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
return jsonData
def edit_contact():
if request.method == 'POST':
check_session_validation()
DBSession.execute(
text('update contact set phone = :contact where id = 1'),
request.json)
DBSession.commit()
success_json = jsonify({'success': True, 'msg': '修改成功'})
return success_json
def get_house_info(size, page):
# res叫做ResultProxy rows[0]叫做RowProxy
if request.method == 'POST':
# 获取传递过来的参数
cityIndex = request.values.get('cityIndex')
regionIndex = request.values.get('regionIndex')
priceIndex = request.values.get('priceIndex')
# print cityIndex
# print regionIndex
# print priceIndex
# 城市目前只有贵阳，只判断区域和价格范围即可
regionSql = ''
priceSql = ''
if regionIndex == '0':
regionSql = ' where 1=1'
elif regionIndex == '1':
regionSql = ' where region=' + '"观山湖区"'
elif regionIndex == '2':
regionSql = ' where region=' + '"南明区"'
elif regionIndex == '3':
regionSql = ' where region=' + '"云岩区"'
elif regionIndex == '4':
regionSql = ' where region=' + '"花溪区"'
if priceIndex == '0':
priceSql = ' and 1=1 '
elif priceIndex == '1':
priceSql = ' and minprice<1000 '
elif priceIndex == '2':
priceSql = ' and ((minprice>=1000 and minprice<=1500) or (maxprice>=1000 and maxprice<=1500)) '
elif priceIndex == '3':
priceSql = ' and ((minprice>=1500 and minprice<=2000) or (maxprice>=1500 and maxprice<=2000)) '
elif priceIndex == '4':
priceSql = ' and ((minprice>=2000 and minprice<=2500) or (maxprice>=2000 and maxprice<=2500)) '
elif priceIndex == '5':
priceSql = ' and maxprice>2500 '
offset = (page - 1) * size
check_session_validation()
res = DBSession.execute(
text('select * from house' + regionSql + priceSql +
'order by id DESC limit :offset, :size'), {
'offset': offset,
'size': size
})
rows = res.fetchall()
# print rows[0].id
# dictData = dict(rows[0].items())
jsonData = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
print jsonData
return jsonData
def add_recommend():
if request.method == 'POST':
check_session_validation()
DBSession.execute(
text(
'insert into recommend(image, houseid) values(:image, :houseid)'
), request.json)
DBSession.commit()
# flash('添加成功')
return jsonify({'success': True, 'msg': '添加成功'})
def house_list():
check_session_validation()
res = DBSession.execute(text('select * from house'), {})
rows = res.fetchall()
jsonData = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
res = json.loads(jsonData)
for item in res:
images = item['images'].split(',')
item['image'] = imageServer + images[0]
return render_template('/guipiao_manage_template/house_list.html',
houseList=res)
def delete_house():
check_session_validation()
try:
DBSession.execute(text('delete from house where id=:id'), request.json)
DBSession.execute(text('delete from banner where houseid=:id'),
request.json)
DBSession.execute(text('delete from recommend where houseid=:id'),
request.json)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '删除失败，请重试'})
return jsonify({'success': True, 'msg': '删除成功'})
def add_house_info():
if request.method == 'POST':
# name = request.form['name']
# phone = request.form['phone']
# DBSession.execute(text('insert into test(name, phone) values(:name, :phone)'), {'name':name, 'phone':phone})
# 先根据输入的地址获取经纬度
url = 'http://api.map.baidu.com/geocoder/v2/'
output = 'json'
ak = 'w92IN1iDP57Z5TH2G4I9I5kzpTnNj4NC'
# 由于本文地址变量为中文，为防止乱码，先用quote进行编码
address = request.json.get('city') + request.json.get(
'region') + request.json.get('address')
print address
add = quote(str(address))
uri = url + '?' + 'address=' + add + '&output=' + output + '&ak=' + ak
req = urlopen(uri)
res = req.read()
temp = json.loads(res)
# 非0状态都是地址解析失败
if temp['status'] != 0:
# flash('地址解析失败，请重新输入地址')
print '地址解析失败，请重新输入地址'
return jsonify({'success': False, 'msg': '地址解析失败，请重新输入地址'})
else:
lat = temp['result']['location']['lat']
lng = temp['result']['location']['lng']
print lng, lat
params = request.json
params['lat'] = lat
params['lng'] = lng
# 插入数据到数据库
check_session_validation()
DBSession.execute(
text(
'insert into house(city, region, images, address, minprice, maxprice, renttype, installation_wifi, installation_kitchen, installation_hoods, installation_water_heater, installation_washer, installation_toilet, pay_month, pay_season, pay_half, pay_year, longimage, lat, lng)'
+
'values(:city, :region, :images, :address, :minprice, :maxprice, :renttype, :installation_wifi, :installation_kitchen, :installation_hoods, :installation_water_heater, :installation_washer, :installation_toilet, :pay_month, :pay_season, :pay_half, :pay_year, :longimage, :lat, :lng)'
), params)
DBSession.commit()
app.logger.error('添加成功')
# flash('添加成功')
# return redirect(url_for('add_house_info'))
return jsonify({'success': True, 'msg': '添加成功'})
def my_share_house_comment_list(page, openid):
size = 10 # 固定分页条数为10条
offset = (page - 1) * size
check_session_validation()
res = DBSession.execute(
text(
"select a.content, DATE_FORMAT(a.time,'%Y/%m/%d %H:%i:%s') as time, c.avatar, c.nickname, b.address, b.house_type, a.share_house_id as house_id, a.id as comment_id from share_house_comment as a, share_house as b, mini_user as c where a.share_house_id = b.id and b.user_id = :openid and a.user_id = c.openid and b.is_delete = 0 order by a.time desc limit :offset, :size"
), {
'offset': offset,
'size': size,
'openid': openid
})
rows = res.fetchall()
json_data = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
return json_data
def share_house_like_list(page, openid):
size = 10 # 固定分页条数为10条
offset = (page - 1) * size
check_session_validation()
res = DBSession.execute(
text(
"select date_format(sh.due_time, '%Y年%m月%d日') as due_time, sh.house_img, sh.address, sh.house_type, sh.rental, sh.region, sh.id from share_house as sh, share_house_like as shl where sh.is_delete = 0 and shl.user_id = :openid and sh.id = shl.house_id limit :offset, :size"
), {
'offset': offset,
'size': size,
'openid': openid
})
rows = res.fetchall()
json_data = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
return json_data
def get_share_house_list(page, openid=None):
size = 10 # 固定分页条数为10条
offset = (page - 1) * size
condition_sql = ''
if openid is not None:
condition_sql = " and user_id = '" + openid + "'"
check_session_validation()
res = DBSession.execute(
text(
"select date_format(due_time, '%Y年%m月%d日') as due_time, house_img, address, house_type, rental, region, id from share_house where is_delete = 0"
+ condition_sql + " limit :offset, :size"), {
'offset': offset,
'size': size
})
rows = res.fetchall()
json_data = json.dumps([(dict(row.items())) for row in rows])
DBSession.commit()
return json_data
def get_share_house_detail(house_id, openid):
check_session_validation()
# 共享公寓的相关信息（得到字典）
house_res = DBSession.execute(
text('select * from share_house where share_house.id = :house_id'),
{'house_id': house_id})
house_row = house_res.fetchone()
house_row_dict = dict(house_row.items())
# 查询是否收藏
like_res = DBSession.execute(
text(
'select * from share_house_like where user_id = :openid and house_id = :house_id'
), {
'openid': openid,
'house_id': house_id
})
rowcount = like_res.rowcount
house_row_dict['like'] = rowcount
# 查询共享公寓的评论列表
comment_res = DBSession.execute(
text(
'select *, date_format(share_house_comment.time, "%Y/%m/%d %H:%i:%s") as format_time from share_house_comment, mini_user where share_house_comment.share_house_id = :house_id and share_house_comment.user_id = mini_user.openid order by share_house_comment.id desc'
), {'house_id': house_id})
comment_rows = comment_res.fetchall()
comment_dict_list = [(dict(comment_row.items()))
for comment_row in comment_rows]
# 查询共享公寓每个评论对应的回复
for comment_item in comment_dict_list:
comment_id = comment_item['id']
reply_res = DBSession.execute(
text(
'select *, date_format(share_house_reply.time, "%Y/%m/%d %H:%i:%s") as format_time from share_house_reply, mini_user where share_house_reply.share_house_comment_id = :comment_id and share_house_reply.user_id = mini_user.openid order by share_house_reply.id desc'
), {'comment_id': comment_id})
reply_rows = reply_res.fetchall()
reply_dict_list = [(dict(reply_row.items()))
for reply_row in reply_rows]
# 每个回复，添加被回复人的信息
for item in reply_dict_list:
reply_user_id = item['reply_user_id']
reply_user_res = DBSession.execute(
text('select * from mini_user where openid = :reply_user_id'),
{'reply_user_id': reply_user_id})
reply_user_row = reply_user_res.fetchone()
reply_user_dict = dict(reply_user_row.items())
item['reply_avatar'] = reply_user_dict['avatar']
item['reply_nickname'] = reply_user_dict['nickname']
comment_item['reply'] = reply_dict_list # 添加回复信息
house_row_dict['comment'] = comment_dict_list # 添加评论信息
json_data = json.dumps(house_row_dict)
DBSession.commit()
return json_data
def mini_login():
js_code = request.form['js_code']
avatar = request.form['avatar']
nickname = request.form['nickname']
url = 'https://api.weixin.qq.com/sns/jscode2session?appid=wx4dab771ae44ce9b2&secret=1eab4ec4f8b87935ea3080bb638ec9d0&grant_type=authorization_code&js_code=' + js_code
req = urllib2.Request(url=url)
res = urllib2.urlopen(req)
res = res.read()
res_obj = json.loads(res)
openid = res_obj['openid']
print(openid)
# 获取到openid后保存在数据库（有则更新，无则插入）
check_session_validation()
try:
DBSession.execute(
text(
'insert ignore into mini_user(openid, avatar, nickname) values(:openid, :avatar, :nickname)'
), {
'openid': openid,
'avatar': avatar,
'nickname': nickname
})
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '登录失败'})
return jsonify({'success': True, 'openid': openid, 'msg': '登录成功'})
def delete_share_house_comment():
check_session_validation()
try:
DBSession.execute(
text('delete from share_house_comment where id = :comment_id'),
request.form)
DBSession.execute(
text(
'delete from share_house_reply where share_house_comment_id = :comment_id'
), request.form)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '删除失败'})
return jsonify({'success': True, 'msg': '已删除'})
def delete_share_house():
check_session_validation()
try:
DBSession.execute(
text('update share_house set is_delete = 1 where id = :id'),
request.form)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '删除失败，请重试'})
return jsonify({'success': True, 'msg': '已删除'})
def share_house_dislike():
check_session_validation()
try:
DBSession.execute(
text(
'delete from share_house_like where user_id = :user_id and house_id = :house_id'
), request.form)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '取消收藏失败'})
return jsonify({'success': True, 'msg': '已取消', 'like': 0})
def share_house_like():
check_session_validation()
try:
DBSession.execute(
text(
'insert into share_house_like(user_id, house_id) values(:user_id, :house_id)'
), request.form)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '添加收藏失败'})
return jsonify({'success': True, 'msg': '已收藏', 'like': 1})
def add_share_house():
check_session_validation()
try:
DBSession.execute(
text(
'insert into share_house(user_id, house_img, city, region, address, house_type, rental, pay_type, installation, due_time, contact, gender, phone, agreement, ownership) values(:user_id, :house_img, :city, :region, :address, :house_type, :rental, :pay_type, :installation, :due_time, :contact, :gender, :phone, :agreement, :ownership)'
), request.form)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '提交失败，请重试'})
return jsonify({'success': True, 'msg': '提交成功'})
def share_house_reply():
params = {}
params['time'] = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
for key in request.form.iterkeys():
params[key] = request.form[key]
print params
check_session_validation()
try:
DBSession.execute(
text(
'insert into share_house_reply(user_id, reply_user_id, share_house_comment_id, content, time) values(:user_id, :reply_user_id, :share_house_comment_id, :content, :time)'
), params)
DBSession.commit()
except:
DBSession.rollback()
return jsonify({'success': False, 'msg': '提交失败，请重试'})
return jsonify({'success': True, 'msg': '提交成功'})
def delete_recommend():
check_session_validation()
DBSession.execute(text('delete from recommend where id=:id'), request.json)
DBSession.commit()
return jsonify({'success': True, 'msg': '删除成功'})
def shutdown_session(exception=None):
DBSession.remove()
def check_session_validation():
try:
DBSession.scalar(select([1]))
except:
DBSession.rollback()
DBSession.scalar(select([1]))