class Type(db_app.Model):
__tablename__ = 'type'
typeID = db_app.Column(db_app.Integer,
primary_key=True,
autoincrement=True,
index=True)
type1 = db_app.Column(db_app.String(500), unique=False)
type2 = db_app.Column(db_app.String(500), unique=True)
def return_json(self):
dic = {}
dic['type1'] = self.type1
dic['type2'] = self.type2
dic['typeid'] = self.typeID
return dic
@classmethod
def get_type(cls, args_dic):
args_list = ['typeid', 'type1', 'type2', 'typeid_list']
relation_dic = {'typeid': 'typeID', 'type1': 'type1', 'type2': 'type2'}
query_dic = {}
results = None
for arg in args_list:
if args_dic.get(arg) != None:
if arg == 'typeid_list':
results = cls.query.filter(Type.typeID.in_(args_dic[arg]))
else:
query_dic[relation_dic[arg]] = args_dic.get(arg)
results = results.filter_by(
**query_dic) if results else cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
class Cart(db_app.Model):
__tablename__ = 'cart'
cartID = db_app.Column(db_app.Integer,
primary_key=True,
autoincrement=True,
index=True)
cNumber = db_app.Column(db_app.Integer)
cPrice = db_app.Column(DOUBLE)
Product_itemID = db_app.Column(db_app.Integer,
db_app.ForeignKey('product.itemID'),
index=True)
User_userID = db_app.Column(db_app.Integer,
db_app.ForeignKey('user.userID'),
index=True)
def return_json(self):
dic = {}
class User(db_app.Model):
__tablename__ = 'user'
userID = db_app.Column(db_app.Integer,
primary_key=True,
autoincrement=True,
index=True)
uName = db_app.Column(db_app.String(500), unique=True)
uPassword = db_app.Column(db_app.String(500))
uDate = db_app.Column(db_app.DateTime, default=datetime.utcnow())
gender = db_app.Column(db_app.String(5))
tel = db_app.Column(db_app.String(500))
address = db_app.Column(db_app.String(500))
imgURL = db_app.Column(db_app.Text())
def return_json(self):
dic = {
'uid': self.userID,
'username': self.uName,
'gender': self.gender,
'tel': self.tel,
'address': self.address,
'date': self.uDate,
'password': self.uPassword
}
return dic
@classmethod
def get_user(cls, args_dic):
relation_dic = {
'uid': 'userID',
'username': '******',
}
args_list = ['username', 'uid']
query_dic = {}
for arg in args_list:
if args_dic.get(arg) is not None:
query_dic[relation_dic[arg]] = args_dic.get(arg)
results = cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
def set_password(self, password):
self.uPassword = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.uPassword, password)
class Product(db_app.Model):
__tablename__ = 'product'
itemID = db_app.Column(db_app.Integer, primary_key=True, autoincrement=True, index=True)
iName = db_app.Column(db_app.String(500))
iPrice = db_app.Column(DOUBLE)
iDate = db_app.Column(db_app.DateTime, default=datetime.utcnow())
remain = db_app.Column(db_app.Integer)
description = db_app.Column(db_app.String(500))
Official_user_official_userID = db_app.Column(db_app.Integer, db_app.ForeignKey('official_user.official_userID'), index=True)
Type_typeID = db_app.Column(db_app.Integer, db_app.ForeignKey('type.typeID'), index=True)
type = relationship('Type', backref='product_type')
def return_json(self):
dic = {}
dic['uid'] = self.itemID
dic['sellerid'] = self.Official_user_official_userID
dic['productname'] = self.iName
dic['price'] = (float)(self.iPrice)
dic['date'] = self.iDate
dic['description'] = self.description
dic['remain'] = self.remain
dic['typeid'] = self.Type_typeID
return dic
@classmethod
def get_product(cls, args_dic):
args_list = ['oid', 'pname', 'types', 'pid']
relation_dic = {
'oid': 'Official_user_official_userID',
'pname': 'iName',
'pid': 'itemID'
}
query_dic = {}
results = None
for arg in args_list:
if args_dic.get(arg) != None:
if arg == 'types':
results = cls.query.filter(Product.Type_typeID.in_(args_dic[arg]))
else:
query_dic[relation_dic[arg]] = args_dic.get(arg)
results = results.filter_by(**query_dic) if results else cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
class OfficialUser(db_app.Model):
__tablename__ = 'official_user'
official_userID = db_app.Column(db_app.Integer,
primary_key=True,
autoincrement=True,
index=True)
oName = db_app.Column(db_app.String(500), unique=True, index=True)
oPassword = db_app.Column(db_app.String(500))
oDate = db_app.Column(db_app.DateTime, default=datetime.utcnow())
isAdmin = db_app.Column(TINYINT)
imgURL = db_app.Column(db_app.Text())
def return_json(self):
dic = {}
dic['uid'] = self.official_userID
dic['username'] = self.oName
dic['isAdmin'] = self.isAdmin
dic['date'] = self.oDate
dic['password'] = self.oPassword
return dic
def set_password(self, password):
self.oPassword = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.oPassword, password)
@classmethod
def get_official_user(cls, args_dic):
args_list = ['oname', 'oid']
relationship_dic = {'oname': 'oName', 'oid': 'official_userID'}
query_dic = {}
for arg in args_list:
if args_dic.get(arg) != None:
query_dic[relationship_dic[arg]] = args_dic.get(arg)
results = cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
class Order(db_app.Model):
__tablename__ = 'all_order'
orderID = db_app.Column(db_app.Integer,
primary_key=True,
autoincrement=True,
index=True)
oPrice = db_app.Column(DOUBLE)
oDate = db_app.Column(db_app.DateTime, default=datetime.utcnow())
address = db_app.Column(db_app.String(500))
User_userID = db_app.Column(db_app.Integer,
db_app.ForeignKey('user.userID'),
index=True)
user = relationship('User', backref='order_of_user')
def return_json(self):
dic = {}
dic['orderid'] = self.orderID
dic['price'] = (float)(self.oPrice)
dic['address'] = self.address
dic['date'] = self.oDate
dic['userid'] = self.User_userID
return dic
@classmethod
def get_order(cls, args_dic):
args_list = ['orderid', 'uid']
relation_dic = {'orderid': 'orderID', 'uid': 'User_userID'}
query_dic = {}
for arg in args_list:
if args_dic.get(arg) != None:
query_dic[relation_dic[arg]] = args_dic.get(arg)
results = cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
@classmethod
def get_period_order(cls, args_dic):
st = datetime.strptime(args_dic['start'], '%Y-%m-%d')
et = datetime.strptime(args_dic['end'], '%Y-%m-%d')
results = cls.query.filter(cls.oDate >= st).filter(cls.oDate < et)
return [result.return_json() for result in results]
@classmethod
def get_year_order(cls, args_dic):
year = args_dic['year']
result_list = []
for i in range(12):
st = datetime.strptime('{:d}-{:d}'.format(year, i + 1), '%Y-%m')
et = None
if i == 11:
et = datetime.strptime('{:d}-{:d}'.format(year + 1, 1),
'%Y-%m')
else:
et = datetime.strptime('{:d}-{:d}'.format(year, i + 2),
'%Y-%m')
results = cls.query.filter(cls.oDate >= st).filter(cls.oDate < et)
result_list.append([result.return_json() for result in results])
return result_list
@classmethod
def get_user_sta(cls, args_dic):
results = db_app.session().query(cls.User_userID, func.sum(cls.oPrice).label('sums')). \
group_by(cls.User_userID). \
filter(cls.orderID.in_(args_dic['orderid_list'])). \
order_by(db_app.desc('sums'))
result_list = []
for i, res in enumerate(results.all()):
if i >= 10: break
args_dic['uid'] = res[0]
user = User.get_user(args_dic)[0]
user['totalprice'] = (float)(res[1])
result_list.append(user)
return result_list
class DetailOrder(db_app.Model):
__tablename__ = 'detailed_order'
detailed_orderID = db_app.Column(db_app.Integer, primary_key=True, autoincrement=True, index=True)
iNumber = db_app.Column(db_app.Integer)
dPrice = db_app.Column(DOUBLE)
status = db_app.Column(db_app.String(50))
Order_orderID = db_app.Column(db_app.Integer, db_app.ForeignKey('all_order.orderID'), index=True)
order = relationship('Order', backref='detail_of_order')
Product_itemID = db_app.Column(db_app.Integer, db_app.ForeignKey('product.itemID'), index=True)
product = relationship('Product', backref='product_detailorder')
Official_user_official_userID = db_app.Column(db_app.Integer, db_app.ForeignKey('official_user.official_userID'), index=True)
official_user = relationship('OfficialUser', backref='official_detailorder')
def return_json(self):
dic = {}
dic['detailorderid'] = self.detailed_orderID
dic['sellerid'] = self.Official_user_official_userID
dic['productid'] = self.Product_itemID
dic['price'] = (float)(self.dPrice)
dic['number'] = self.iNumber
dic['status'] = self.status
dic['order_id'] = self.Order_orderID
return dic
@classmethod
def get_detail_order(cls, args_dic):
args_list = ['detailedoid', 'orderid', 'oid', 'pid', 'status', 'oid_list']
relationship_dic = {
'detailedoid': 'detailed_orderID',
'orderid': 'Order_orderID',
'oid': 'Official_user_official_userID',
'pid': 'Product_itemID',
'status': 'status',
}
query_dic = {}
results = None
for arg in args_list:
if args_dic.get(arg) != None:
if arg == 'oid_list':
results = cls.query.filter(DetailOrder.Order_orderID.in_(args_dic[arg]))
else:
query_dic[relationship_dic[arg]] = args_dic.get(arg)
results = results.filter_by(**query_dic) if results else cls.query.filter_by(**query_dic)
return [result.return_json() for result in results]
@classmethod
def get_popular_product(cls, args_dic):
args_list = ['oid']
relationship_dic = {
'detailedoid': 'detailed_orderID',
'orderid': 'Order_orderID',
'oid': 'Official_user_official_userID',
'pid': 'Product_itemID',
'status': 'status',
}
query_dic = {}
for arg in args_list:
if args_dic.get(arg) != None:
query_dic[relationship_dic[arg]] = args_dic.get(arg)
results = db_app.session().query(cls.Product_itemID,
func.sum(cls.dPrice).label('sums'),
func.sum(cls.iNumber).label('numbers')). \
group_by(cls.Product_itemID). \
filter(cls.Order_orderID.in_(args_dic['orderid_list'])).\
filter(cls.status != 'canceled').\
filter_by(**query_dic).\
order_by(db_app.desc('sums'))
result_list = []
for i, res in enumerate(results.all()):
if i >= 10: break
args_dic['pid'] = res[0]
prod = Product.get_product(args_dic)[0]
prod['totalprice'] = (float)(res[1])
prod['totalnumber'] = (float)(res[2])
result_list.append(prod)
return result_list
@classmethod
def get_seller_sta(cls, args_dic):
args_list = ['oid']
query_dic = {}
for arg in args_list:
if args_dic.get(arg) != None:
query_dic['Official_user_official_userID'] = args_dic.get(arg)
results = db_app.session().query(cls.Official_user_official_userID,
func.sum(cls.dPrice).label('sums'),
func.sum(cls.iNumber).label('numbers')). \
group_by(cls.Official_user_official_userID). \
filter(cls.Order_orderID.in_(args_dic['orderid_list'])).\
filter(cls.status != 'canceled').\
filter_by(**query_dic).\
order_by(db_app.desc('sums'))
result_list = []
for i, res in enumerate(results.all()):
if i >= 10 and args_dic.get('top_flag'): break
args_dic['oid'] = res[0]
seller = OfficialUser.get_official_user(args_dic)[0]
seller['totalprice'] = (float)(res[1])
seller['totalnumber'] = (float)(res[2])
result_list.append(seller)
return result_list
@classmethod
def get_type_product(cls, args_dic):
args_list = ['oid']
query_dic = {}
for arg in args_list:
if args_dic.get(arg) != None:
query_dic['Official_user_official_userID'] = args_dic.get(arg)
results = db_app.session().query(cls.Product_itemID,
func.sum(cls.dPrice).label('sums'),
func.sum(cls.iNumber).label('numbers')). \
group_by(cls.Product_itemID). \
filter(cls.Product_itemID.in_(args_dic['pid_list'])).\
filter(cls.status != 'canceled').\
filter_by(**query_dic).\
order_by(db_app.desc('sums'))
result_list = []
for i, res in enumerate(results.all()):
args_dic['pid'] = res[0]
prod = Product.get_product(args_dic)[0]
prod['totalprice'] = (float)(res[1])
prod['totalnumber'] = (float)(res[2])
result_list.append(prod)
return result_list