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