Vodagoods 网吧app后端分享

#coding:utf-8  
import smtplib  
from email.mime.text import MIMEText
from email.utils import formataddr
import pymysql
import time
import json
from bottle import route, run ,redirect,request,default_app
from beaker.middleware import SessionMiddleware


#设置session参数
session_opts = {
    'session.type':'file',                   # 以文件的方式保存session
    'session.cookei_expires':259200,       # session过期时间为3600秒
    'session.data_dir':'/tmp/sessions',  # session存放路径
    'sessioni.auto':True
    }
    
@route('/iflogin')
def iflogin():
    username = request.query.username[10:-2]
    s = request.environ.get('beaker.session') 
    ausername = s.get(username,username)  
    if not ausername:
        return json.dumps({"Result":"False"})
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_user WHERE user_name="{}";'''.format(username))
    data = cursor.fetchone()
    if data==None:
        return json.dumps({"Result":"False"})
    if data[6]!=None:
        groupin=data[6]
        cursor.execute( '''select group_name from app_groaup WHERE group_id={};'''.format(groupin))
        groupnamein=cursor.fetchone()[0]
    else:
        groupnamein="No Group"
    if data[8]!=None:
        teamin=data[8]
        cursor.execute( '''select team_name from app_team WHERE team_id={};'''.format(teamin))
        teamnamein=cursor.fetchone()[0]
    else:
        teamnamein="No Team"
    db.close()
    rtDic={"Result":"True","id":data[0],"username":data[1],"email":data[3],"phone":data[4],"fight":data[5],"date":str(data[7]),"groupname":groupnamein,"teamname":teamnamein,"wallet":data[9]}
    return json.dumps(rtDic)
    
def mail(my_user,username):
    ret=True
    try:
        my_sender='admin@vodagoods.com'
        msg=MIMEText('''亲爱的{}：\n\n恭喜您成功注册成为sng会员！'''.format(username),'plain','utf-8')
        msg['From']=formataddr(["sng admin",my_sender])   
        msg['To']=formataddr([my_user,my_user])   
        msg['Subject']='注册成功'
        server=smtplib.SMTP("210.9.30.6",25)  
        server.login("admin","admin")    
        server.sendmail(my_sender,my_user,str(msg))   
        server.quit()   
    except Exception:  
        ret=False

@route('/adduser')
def adduser():
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_user WHERE user_id>=0;''')
    userId = len(cursor.fetchall())
    username = request.query.username
    cursor.execute( '''select * from app_user WHERE user_name="{}";'''.format(username))
    email = request.query.email
    if len(cursor.fetchall())>0 or "@" not in email:
        return json.dumps({"Result":"False"})
    password = request.query.password	
    phone = request.query.phone
    date= time.strftime("%Y-%m-%d", time.localtime()) 
    sql='''INSERT INTO app_user(
        user_id,user_name,user_passwd,user_email,user_phone,user_fight,submission_date,wallet)
        VALUES(
        {},"{}","{}","{}","{}",{},'{}',{});
        '''.format(userId,username,password,email,phone,0,date,0)
    cursor.execute(sql)
    db.close()
    mail(email,username)
    return  json.dumps({"Result":"True"})

@route('/finduser')
def finduser():
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    username = request.query.username
    password = request.query.password
    cursor.execute( '''select * from app_user WHERE user_name="{}";'''.format(username))
    data = cursor.fetchone()
    if data==None:
        return json.dumps({"Result":"False"})
    if data[6]!=None:
        groupin=data[6]
        cursor.execute( '''select group_name from app_group WHERE group_id={};'''.format(groupin))
        groupnamein=cursor.fetchone()[0]
    else:
        groupnamein="No Group"
    if data[8]!=None:
        teamin=data[8]
        cursor.execute( '''select team_name from app_team WHERE team_id={};'''.format(teamin))
        teamnamein=cursor.fetchone()[0]
    else:
        teamnamein="No Team"
    db.close()
    if password==data[2]:
        rtDic={"Result":"True","id":data[0],"username":data[1],"email":data[3],"phone":data[4],"fight":data[5],"date":str(data[7]),"groupname":groupnamein,"teamname":teamnamein,"wallet":data[9]}
        if rtDic["Result"]=="True":
            s = request.environ.get('beaker.session')  
            s[username] = username
            s.save()
            return json.dumps(rtDic)
    else:
        return json.dumps({"Result":"False"})
    
@route('/addgroup')
def addgroup():
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_group WHERE group_id>=0;''')
    groupId = len(cursor.fetchall())
    groupname = request.query.groupname[10:-2]
    groupintro=request.query.groupintro[10:-2]
    ownerid = request.query.ownerid	
    cursor.execute( '''select * from app_group WHERE group_name="{}";'''.format(groupname))
    if len(cursor.fetchall())>0 or len(groupname)==0 or len(groupintro)==0:
        return json.dumps({"Result":"False2"})
    date= time.strftime("%Y-%m-%d", time.localtime()) 
    sql='''INSERT INTO app_group(
        group_id,group_name,group_fight,group_owner_id,group_max_mbr,group_now_mbr,group_submission_date,group_intro)
        VALUES(
        {},"{}",{},{},{},{},'{}',"{}");
        '''.format(groupId,groupname,0,ownerid,50,1,date,groupintro)
    cursor.execute(sql)
    cursor.execute('''UPDATE app_user SET user_group={} WHERE user_id={};'''.format(groupId,ownerid))
    db.close()
    return  json.dumps({"Result":"True"})

@route('/addteam')
def addteam():
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( "select * from app_team WHERE team_id>=0;")
    teamId = len(cursor.fetchall())
    teamname = request.query.teamname[10:-2]
    teamintro=request.query.teamintro[10:-2]
    ownerid = request.query.ownerid	
    cursor.execute( '''select * from app_team WHERE team_name="{}";'''.format(teamname))
    if len(cursor.fetchall())>0 or len(teamname)==0 or len(teamintro)==0:
        return json.dumps({"Result":"False"})
    date= time.strftime("%Y-%m-%d", time.localtime()) 
    sql='''INSERT INTO app_team(
        team_id,team_name,team_type,team_fight,team_owner_id,team_max_mbr,team_now_mbr,team_submission_date,team_intro)
        VALUES(
        {},"{}",{},{},{},{},{},'{}',"{}");
        '''.format(teamId,teamname,0,0,ownerid,5,1,date,teamintro)
    cursor.execute(sql)
    cursor.execute('''UPDATE app_user SET user_team={} WHERE user_id={};'''.format(teamId,ownerid))
    db.close()
    return  json.dumps({"Result":"True"})
    
@route('/groupintro')
def groupintro():
    groupname = request.query.groupname
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select group_owner_id from app_group WHERE group_name="{}";'''.format(groupname))
    userId=cursor.fetchone()[0]
    cursor.execute( '''select group_intro from app_group WHERE group_name="{}";'''.format(groupname))
    nr=cursor.fetchone()[0]
    cursor.execute( '''select user_name from app_user WHERE user_id={};'''.format(userId))
    owner=cursor.fetchone()[0]
    cursor.execute( '''select group_id from app_group WHERE group_name="{}";'''.format(groupname))
    groupId=cursor.fetchone()[0]
    cursor.execute( '''select user_id from app_user WHERE user_group={};'''.format(groupId))
    allUserId=cursor.fetchall()
    userlist=[]
    for i in allUserId:
        userlist.append(i[0])
    cursor.execute( '''select group_now_mbr from app_group WHERE group_name="{}";'''.format(groupname))
    groupNow=cursor.fetchone()[0]
    cursor.execute( '''select group_max_mbr from app_group WHERE group_name="{}";'''.format(groupname))
    groupMax=cursor.fetchone()[0]
    db.close()
    return json.dumps({"intro":nr,"owner":owner,"member":"{}".format(userlist),"nowmeb":groupNow,"maxmeb":groupMax})

@route('/teamintro')
def teamintro():
    teamname = request.query.teamname
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select team_owner_id from app_team WHERE team_name="{}";'''.format(teamname))
    userId=cursor.fetchone()[0]
    cursor.execute( '''select team_intro from app_team WHERE team_name="{}";'''.format(teamname))
    nr=cursor.fetchone()[0]
    cursor.execute( '''select user_name from app_user WHERE user_id={};'''.format(userId))
    owner=cursor.fetchone()[0]
    cursor.execute( '''select team_id from app_team WHERE team_name="{}";'''.format(teamname))
    teamId=cursor.fetchone()[0]
    cursor.execute( '''select user_id from app_user WHERE user_team={};'''.format(teamId))
    allUserId=cursor.fetchall()
    userlist=[]
    for i in allUserId:
        userlist.append(i[0])
    cursor.execute( '''select team_now_mbr from app_team WHERE team_name="{}";'''.format(teamname))
    teamNow=cursor.fetchone()[0]
    cursor.execute( '''select team_max_mbr from app_team WHERE team_name="{}";'''.format(teamname))
    teamMax=cursor.fetchone()[0]
    db.close()
    return json.dumps({"intro":nr,"owner":owner,"member":"{}".format(userlist),"nowmeb":teamNow,"maxmeb":teamMax})
    
@route('/intogroup')
def intogroup():
    groupname = request.query.groupname[10:-2]
    username = request.query.username[9:-1]
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select group_now_mbr from app_group WHERE group_name="{}";'''.format(groupname))
    groupNow=cursor.fetchone()[0]
    cursor.execute( '''select group_max_mbr from app_group WHERE group_name="{}";'''.format(groupname))
    groupMax=cursor.fetchone()[0]
    if groupNow>=groupMax :
        return json.dumps({"Result":"False"})
    cursor.execute( '''select group_id from app_group WHERE group_name="{}";'''.format(groupname))
    groupId=cursor.fetchone()[0]
    cursor.execute('''UPDATE app_user SET user_group={} WHERE user_name="{}";'''.format(groupId,username))
    cursor.execute('''UPDATE app_group SET group_now_mbr={} WHERE group_name="{}";'''.format(groupNow+1,groupname))
    return  json.dumps({"Result":"True"})
    
@route('/intoteam')
def intoteam():
    teamname = request.query.teamname[10:-2]
    username = request.query.username[9:-1]
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select team_now_mbr from app_team WHERE team_name="{}";'''.format(teamname))
    teamNow=cursor.fetchone()[0]
    cursor.execute( '''select team_max_mbr from app_team WHERE team_name="{}";'''.format(teamname))
    teamMax=cursor.fetchone()[0]
    if groupNow>=groupMax :
        return json.dumps({"Result":"False"})
    cursor.execute( '''select team_id from app_team WHERE team_name="{}";'''.format(teamname))
    teamId=cursor.fetchone()[0]
    cursor.execute('''UPDATE app_user SET user_team={} WHERE user_name="{}";'''.format(teamId,username))
    cursor.execute('''UPDATE app_team SET team_now_mbr={} WHERE team_name="{}";'''.format(teamNow+1,teamname))
    return  json.dumps({"Result":"True"})

@route('/getvideo')
def videoUrl():	
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_video WHERE video_id>=0;''')
    allVideo=cursor.fetchall() 
    allBin=[]
    for i in range (len(allVideo)):
        allBin.append({})
        allBin[i]["title"]=allVideo[i][1]
        allBin[i]["url"]=allVideo[i][2]
        allBin[i]["thumbnail_pic_s"]=allVideo[i][3]
    return json.dumps({"result":{"data":allBin}})

@route('/getshop')
def shopUrl():
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_stock WHERE stock_id>=0;''')
    allStock=cursor.fetchall()
    allBin=[]
    for i in range (len(allStock)):
        allBin.append({})
        allBin[i]["title"]=allStock[i][1]
        allBin[i]["imageurl"]=allStock[i][2]
        allBin[i]["price"]=allStock[i][3]
        allBin[i]["stock"]=allStock[i][4]
    return json.dumps({"result":{"data":allBin}})
    
@route('/topup')
def topup():
    username = request.query.username[10:-2]
    password = request.query.password[10:-2]
    number = request.query.number[10:-2]
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_card WHERE card_number="{}";'''.format(number))
    data = cursor.fetchone()
    if data==None:
        return json.dumps({"Result":"False"})
    if password==data[2] and data[-1]==None:
        cursor.execute( '''select wallet from app_user WHERE user_name="{}";'''.format(username))
        wallet=cursor.fetchone()[0]
        cursor.execute('''UPDATE app_card SET card_used={} WHERE card_number="{}";'''.format(1,number))
        date= time.strftime("%Y-%m-%d", time.localtime()) 
        cursor.execute('''UPDATE app_card SET card_use_date='{}' WHERE card_number="{}";'''.format(date,number))
        cursor.execute('''UPDATE app_card SET card_user_name="{}" WHERE card_number="{}";'''.format(username,number))
        cursor.execute('''UPDATE app_user SET wallet={} WHERE user_name="{}";'''.format(wallet+int(data[3]),username))
        return json.dumps({"Result":"True","Price":data[3]})
    else:
        return json.dumps({"Result":"False"})
        
@route('/myorder')
def myorder():
    username = request.query.username[10:-2]
    db = pymysql.connect("210.9.30.6","app","WEgVsnNuWhlUwVs","app")
    cursor = db.cursor()
    cursor.execute( '''select * from app_order WHERE order_user_name="{}";'''.format(username))
    allStock=cursor.fetchall()
    allBin=[]
    for i in range (len(allStock)):
        allBin.append({})
        allBin[i]["title"]=allStock[i][3]
        allBin[i]["number"]=allStock[i][4]
        cursor.execute( '''select stock_price from app_stock WHERE stock_name="{}";'''.format(allStock[i][3]))
        price=cursor.fetchone()[0]
        allBin[i]["price"]=price
        allBin[i]["totalprice"]=str(float(price)*float(allStock[i][4]))
        allBin[i]["date"]=str(allStock[i][5])
    return json.dumps({"result":{"data":allBin}})
    
app = default_app()
app = SessionMiddleware(app, session_opts)
run(app=app,host='0.0.0.0', port=9090,debug=True)