from sqlalchemy import func, text
from sqlalchemy import Boolean, Column, Integer, \
String, ForeignKey
from app import db
# Set up user_loader
# @login_manager.user_loader
# def load_user(user_id):
# return User.query.get(int(user_id))
class Node(db.Model):
__tablename__ = 'node'
id = Column(Integer(), primary_key=True)
name = Column(String(200))
parent_id = Column('parent_id', Integer())
lft = Column('lft', Integer())
rgt = Column('rgt', Integer())
deleted = Column('deleted', Boolean, default=False)
def __init__(self, name, parent_id, lft, rgt):
self.name = name
self.parent_id = parent_id
self.lft = lft
self.rgt = rgt
@classmethod
def saveAsRoot(self, name):
cut = self.getLowerBound() + 1
node = self(name, 0, cut, cut + 1)
db.session.add(node)
db.session.commit()
self.preOrder(self, 0, 0)
return node
def addNodeChild(self, parent_id, node_name):
node = self(node_name, parent_id, None, None)
db.session.add(node)
db.session.commit()
self.preOrder(self, 0, 0)
return node
def preOrder(self, parent, left):
right = left + 1
result = Node.query.filter_by(parent_id=parent)
for r in result:
right = self.preOrder(self, r.id, right)
n = Node.query.get(parent)
if n:
n.lft = left
n.rgt = right
db.session.commit()
return right + 1
def getLowerBound():
data = db.session.query(func.max(Node.rgt)).scalar()
if data:
return data
return 1
def path(id):
sql = text(
'SELECT parent.name FROM node AS n, node AS parent WHERE n.lft BETWEEN parent.lft AND parent.rgt AND n.id = :id ORDER BY parent.lft;')
result = db.engine.execute(sql, {'id': id})
names = []
for row in result:
names.append(row[0])
return ">".join(names)
class Goods(db.Model):
__tablename__ = 'goods'
id = Column(Integer(), primary_key=True)
sku = Column(String(200))
name = Column(String(200))
unit = Column(String(20))
photo_path = Column(String(255))
node_id = Column('node_id', Integer(), ForeignKey('node.id'))
deleted = Column('deleted', Boolean, default=False)
class Company(db.Model):
__tablename__ = 'company'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), index=True, unique=True)
phone = db.Column(db.String(60), index=True)
email = db.Column(db.String(60), index=True)
address = db.Column(db.Text)
deleted = db.Column(db.Boolean, default=0)
company_type = Column(String(20))
class Zone(db.Model):
__tablename__ = 'zone'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), index=True, unique=True)
deleted = db.Column(db.Boolean, default=0)
@classmethod
def allArray(self):
zones = []
for n in Zone.query.all():
zones.append({'id': n.id, 'name': n.name})
return zones
class Settings(db.Model):
__tablename__ = 'settings'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(60), index=True, unique=True)
settings_key = db.Column(db.String(60), index=True, unique=True)
value = db.Column(db.Text, index=True, unique=True)
deleted = db.Column(db.Boolean, default=0)
class Location(db.Model):
__tablename__ = 'location'
id = Column(Integer(), primary_key=True)
name = Column(String(200))
parent_id = Column('parent_id', Integer())
lft = Column('lft', Integer())
rgt = Column('rgt', Integer())
zone_id = db.Column(db.Integer, db.ForeignKey('zone.id'))
deleted = Column('deleted', Boolean, default=False)
def __init__(self, name, parent_id, lft, rgt, zone_id):
self.name = name
self.parent_id = parent_id
self.lft = lft
self.rgt = rgt
self.zone_id = zone_id
@classmethod
def saveAsRoot(self, name):
cut = self.getLowerBound() + 1
node = self(name, 0, cut, cut + 1, None)
db.session.add(node)
db.session.commit()
self.preOrder(self, 0, 0)
return node
def addNodeChild(self, parent_id, node_name):
node = self(node_name, parent_id, None, None, None)
db.session.add(node)
db.session.commit()
self.preOrder(self, 0, 0)
return node
def preOrder(self, parent, left):
right = left + 1
result = Location.query.filter_by(parent_id=parent)
for r in result:
right = self.preOrder(self, r.id, right)
n = Location.query.get(parent)
if n:
n.lft = left
n.rgt = right
db.session.commit()
return right + 1
def getLowerBound():
data = db.session.query(func.max(Location.rgt)).scalar()
if data:
return data
return 1
def path(id):
sql = text(
'SELECT parent.name FROM location AS n, location AS parent WHERE n.lft BETWEEN parent.lft AND parent.rgt AND n.id = :id ORDER BY parent.lft;')
result = db.engine.execute(sql, {'id': id})
names = []
for row in result:
names.append(row[0])
return ">".join(names)
def map(cls):
sql = text(
'SELECT parent.name FROM location AS n, location AS parent WHERE n.lft BETWEEN parent.lft AND parent.rgt AND n.id = :id ORDER BY parent.lft;')
result = db.engine.execute(sql, {'id': cls.id})
names = []
for row in result:
names.append(row[0])
return ">".join(names)
def toTree(self):
nodes = Location.query.filter_by(parent_id=0)
tree = []
for n in nodes:
tree.append({'id': n.id, 'name': n.name, 'parent_id': n.parent_id, 'zone_id': n.zone_id,
'children': self.TReverse(self, n)})
return tree
def TReverse(self, node):
nodes = Location.query.filter_by(parent_id=node.id)
if nodes == None:
return []
tree = []
for n in nodes:
tree.append({'id': n.id, 'name': n.name, 'parent_id': n.parent_id, 'zone_id': n.zone_id,
'children': self.TReverse(self, n)})
return tree
class Seller(db.Model):
__tablename__ = 'seller'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120))
email = db.Column(db.String(120))
phone = db.Column(db.String(120))
address = db.Column(db.Text)
company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
location_id = db.Column(db.Integer, db.ForeignKey('location.id'))
company = db.relationship('Company')
location = db.relationship('Location')
users = db.relationship('User', secondary='seller_user')
deleted = db.Column(db.Boolean, default=0)
@classmethod
def covarage_area(cls, id):
return SellerLocationCoverage.query.filter_by(seller_id=id, is_active=1)
class Buyer(db.Model):
__tablename__ = 'buyer'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120))
email = db.Column(db.String(120))
phone = db.Column(db.String(120))
fax = db.Column(db.String(120))
address = db.Column(db.Text)
company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
deleted = db.Column(db.Boolean, default=0)
users = db.relationship('User', secondary='buyer_users', backref=db.backref('buyers', lazy='dynamic'))
@classmethod
def company(self, buyer):
if buyer.company_id:
return Company.query.get(buyer.company_id).name
return "---"
class BuyerUser(db.Model):
__tablename__ = 'buyer_users'
id = db.Column(db.Integer, primary_key=True)
buyer_id = db.Column(db.Integer, db.ForeignKey('buyer.id'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
class SellerLocationCoverage(db.Model):
__tablename__ = 'seller_coverage_area'
id = db.Column(db.Integer, primary_key=True)
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
location_id = db.Column(db.Integer, db.ForeignKey('location.id'))
start_at = db.Column(db.DateTime)
ends_at = db.Column(db.DateTime)
is_active = db.Column(db.Boolean, default=1)
location = db.relationship('Location')
class CompanyBranch(db.Model):
__tablename__ = "company_branch"
id = db.Column(db.Integer, primary_key=True)
company_id = db.Column(db.Integer, db.ForeignKey('company.id'))
location_id = db.Column(db.Integer, db.ForeignKey('location.id'))
company = db.relationship('Company')
location = db.relationship('Location')
branch_name = db.Column(db.String(120))
phone = db.Column(db.String(120))
address = db.Column(db.String(200))
contact = db.Column(db.String(200))
deleted = db.Column(db.Boolean, default=0)
class SellerUser(db.Model):
__tablename__ = "seller_user"
id = db.Column(db.Integer, primary_key=True)
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
seller = db.relationship('Seller')
user = db.relationship('User')
def __init__(self, seller_id, user_id):
self.seller_id = seller_id
self.user_id = user_id
def __repr__(self):
return '<SellerUser {} {}>'.format(self.seller_id, self.user_id)
def __str__(self):
return self.id
class SellerGoods(db.Model):
__tablename__ = "seller_goods"
id = db.Column(db.Integer, primary_key=True)
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
seller = db.relationship('Seller')
goods = db.relationship('Goods')
price = db.Column(db.Integer)
@classmethod
def seller_goods_discount(cls, goods, seller):
result = db.session.execute(text('SELECT sd.* FROM seller_discount sd '
'INNER JOIN seller_goods_discount sgd ON sgd.seller_discount_id = sd.id '
'WHERE sd.seller_id=:seller and sgd.goods_id=:goods and now() BETWEEN start_at and ends_at'),
{'seller': seller,
'goods': goods})
return result
class SellerDiscount(db.Model):
__tablename__ = "seller_discount"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200))
discount_type = db.Column(db.String(200))
discount_amount = db.Column(db.Float)
start_at = db.Column(db.DateTime)
end_at = db.Column(db.DateTime)
is_active = db.Column(db.Boolean)
seller_id = db.Column(db.Integer, db.ForeignKey('Seller.id'))
class SellerGoodsDiscount(db.Model):
__tablename__ = "seller_goods_discount"
id = db.Column(db.Integer, primary_key=True)
seller_discount_id = db.Column(db.Integer, db.ForeignKey('seller_discount.id'))
goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
for_buyer_id = db.Column(db.Integer, db.ForeignKey('buyer.id'))
discount = db.relationship('SellerDiscount')
class GoodsPrice(db.Model):
id = db.Column(db.Integer, primary_key=True)
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
seller = db.relationship('Seller')
goods = db.relationship('Goods')
price = db.Column(db.Integer)
start_data = db.Column(db.DateTime)
end_data = db.Column(db.DateTime)
class Order(db.Model):
id = db.Column(db.Integer, primary_key=True)
order_number = db.Column(db.String(20))
total_price = db.Column(db.Float)
total_discount = db.Column(db.Float)
status = db.Column(db.String(20))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
class OrderItem(db.Model):
id = db.Column(db.Integer, primary_key=True)
quantity = db.Column(db.Integer)
unit_price = db.Column(db.Float)
total_price = db.Column(db.Float)
total_discount = db.Column(db.Float)
total = db.Column(db.Float)
status = db.Column(db.String(20))
order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
seller_goods_id = db.Column(db.Integer, db.ForeignKey('seller_goods.id'))
class OrderItemDiscount(db.Model):
id = db.Column(db.Integer, primary_key=True)
seller_goods_discount_id = db.Column(db.Integer)
goods_id = db.Column(db.Integer, db.ForeignKey('goods.id'))
seller_id = db.Column(db.Integer, db.ForeignKey('seller.id'))
disconut_type = db.Column(db.String(200))
disconut_amount = db.Column(db.Float)