class Todo(db.Model):
task_id = db.Column(db.Integer, primary_key=True)
task = db.Column(db.String(100))
class JzkUser(db.Model):
__tablename__ = 'jzk_user'
uid = db.Column(db.Integer, primary_key=True)
unionid = db.Column(db.Integer)
openid = db.Column(db.Integer)
nickname = db.Column(db.String(50))
sex = db.Column(db.String(20))
province = db.Column(db.String(50))
city = db.Column(db.String(50))
country = db.Column(db.String(50))
avator = db.Column(db.String(255))
account = db.Column(db.String(255))
password = db.Column(db.String(255))
birthday = db.Column(db.String(255))
address = db.Column(db.String(255))
tel = db.Column(db.String(12))
mail = db.Column(db.String(50))
name = db.Column(db.String(255))
experience = db.Column(db.String(500))
freetime = db.Column(db.String(255))
payzhifubao = db.Column(db.String(255))
education = db.Column(db.String(255))
qqnum = db.Column(db.Integer)
credit = db.Column(db.Integer, server_default=db.FetchedValue())
group = db.Column(db.ForeignKey('jzk_user_groups.name'), index=True)
is_merchant = db.Column(db.Integer, server_default=db.FetchedValue())
jzk_user_group = db.relationship(
'JzkUserGroup',
primaryjoin='JzkUser.group == JzkUserGroup.name',
backref='jzk_users')
class JzkFeedback(db.Model):
__tablename__ = 'jzk_feedback'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.Integer)
content = db.Column(db.String(800))
class Tasks(db.Model):
id = db.Column(db.Integer, primary_key=True)
task = db.Column(db.String(50))
is_complete = db.Column(db.Boolean, default=False)
class User(db.Model):
__tablename__ = "account"
id = db.Column(db.Integer, primary_key=True)
date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
date_modified = db.Column(db.DateTime,
default=db.func.current_timestamp(),
onupdate=db.func.current_timestamp())
name = db.Column(db.String(144), nullable=False)
username = db.Column(db.String(144), nullable=False)
password = db.Column(db.String(144), nullable=False)
tasks = db.relationship("Task", backref='account', lazy=True)
stairs = db.relationship("Stair", backref='account', lazy=True)
def __init__(self, name, username, password):
self.name = name
self.username = username
self.password = password
def get_id(self):
return self.id
def is_active(self):
return True
def is_anonymous(self):
return False
def is_authenticated(self):
return True
@staticmethod
def find_users_with_no_tasks():
stmt = text("SELECT Account.id, Account.name FROM Account"
" LEFT JOIN Task ON Task.account_id = Account.id"
" WHERE (Task.done IS null OR Task.done = true)"
" GROUP BY Account.id"
" HAVING COUNT(Task.id) = 0")
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"id": row[0], "name": row[1]})
return response
@staticmethod
def find_tasks_stairs():
stmt = text(
"SELECT Stair.stair_letter, Task.name FROM Account, Stair, Task"
" WHERE Account.id = Stair.account_id AND Account.id = Task.account_id"
)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"stair": row[0], "name": row[1]})
return response
@staticmethod
def find_users_tasks():
stmt = text(
"SELECT account.name, task.name, task.done FROM Account, Task"
" WHERE Account.id = Task.account_id")
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({
"name": row[0],
"task_name": row[1],
"done": row[2]
})
return response
@staticmethod
def find_users():
stmt = text(
"SELECT Account.name, Stair.stair_letter FROM Account, Stair"
" WHERE Account.id = Stair.account_id")
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"name": row[0], "stair": row[1]})
return response
class Tournament(Base):
name = db.Column(db.String(144), nullable=False)
playerCount = db.Column(db.Integer, nullable=False)
done = db.Column(db.Boolean, nullable=False)
account_id = db.Column(db.Integer,
db.ForeignKey('account.id'),
nullable=False)
players = db.relationship('User',
secondary=tournamentPlayers,
backref='tournamentPlayers',
lazy=True)
games = db.relationship('Game',
secondary=tournamentGames,
backref='tournamentGames',
lazy=True,
cascade="delete")
def __init__(self, name):
self.name = name
self.done = False
@staticmethod
def find_how_many_players_in(tournamentid):
stmt = text(
"SELECT COUNT(*) FROM Account"
" INNER JOIN tournament_players ON tournament_players.account_id = Account.id"
" LEFT JOIN Tournament ON Tournament.id = tournament_players.tournament_id"
" WHERE Tournament.id = :tournamentid").params(
tournamentid=tournamentid)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"players": row[0]})
return response
@staticmethod
def find_players_in_tournaments():
stmt = text(
"SELECT Tournament.id, COUNT(*) FROM Tournament"
" INNER JOIN tournament_players ON tournament_players.tournament_id = Tournament.id"
" LEFT JOIN Account ON Account.id = tournament_players.account_id"
" GROUP BY Tournament.id")
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"tournamentid": row[0], "players": row[1]})
return response
@staticmethod
def find_players_in_single_tournament(tournamentid):
stmt = text(
"SELECT * FROM Tournament"
" INNER JOIN tournament_players ON tournament_players.tournament_id = Tournament.id"
" LEFT JOIN Account ON Account.id = tournament_players.account_id"
" WHERE Tournament.id = :tournamentid").params(
tournamentid=tournamentid)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"name": row[13]})
return response
@staticmethod
def find_games_in_tournament(tournamentid):
stmt = text(
"SELECT * FROM Game"
" INNER JOIN tournament_games ON tournament_games.games_id = Game.id"
" WHERE tournament_id = :tournamentid").params(
tournamentid=tournamentid)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"tournamentname": row[3], "gameid": row[10]})
return response
class Order(db.Model):
__tablename__ = "order"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(100), unique=True, nullable=False)
phone = db.Column(db.String(20), nullable=False)
address = db.Column(db.String(200), nullable=False)
price = db.Column(db.Integer, nullable=False)
key_holder = db.Column(db.String(20), nullable=False)
item_type = db.Column(db.String(20), nullable=False)
letters = db.Column(db.String(100), nullable=False)
def __init__(self, name, email, phone, address, letters, key_holder, item_type):
self.name = name
self.email = email
self.phone = phone
self.address = address
self.letters = letters
self.key_holder = key_holder
self.item_type = item_type
self.num_letter = len(letters)
self.price = None
def single_letter_calc_price(self):
price = 0
if self.key_holder == 'true':
price = price + KEY_PRICE
price = price + self.num_letter * SINGLE_LETTER_PRICE
if self.num_letter > 5:
price = int(price * 0.9)
self.price = price
return price
def multi_letter_calc_price(self):
price = MULTI_LETTER_PRICE * 2 + BASE_PRICE
num = self.num_letter - 2
if self.key_holder == 'true':
price = price + KEY_PRICE
if num == 0: # if just 2 letter
self.price = price
return price
price = price + num * MULTI_LETTER_PRICE
self.price = price
return price
def get_item_detail(self):
if self.item_type == 'single':
return list(self.letters)
else:
return self.letters
def charge_with_stripe(self, card):
try:
stripe.api_key = os.environ['SECRET_KEY']
stripe.Charge.create(
amount=self.price,
currency='usd',
card=card, # get the token from buyer form
description='Stripe Flask')
return True
except:
return False
def email_to_seller(self):
# recipients=["*****@*****.**"]
msg = Message(f'訂單, {self.email}, {self.name}',
sender='*****@*****.**',
recipients=["*****@*****.**"])
msg.body = f"""
買家資料：
姓名：{self.name}
Email：{self.email}
電話：{self.phone}
商品：{self.get_item_detail()}
類型：{'單字' if self.item_type == "single" else '字串'}
鑰匙圈：{'加' if self.key_holder == "true" else '不加'}
郵寄地址：{self.address}
價錢：{self.price}
"""
mail.send(msg)
def email_to_buyer(self):
msg = Message(f'Sing Color 認證函',
sender='*****@*****.**',
recipients=[f"{self.email}"])
msg.body = f"""
姓名：{self.name}
Email：{self.email}
電話：{self.phone}
商品：{self.get_item_detail()}
類型：{'單字' if self.item_type == "single" else '字串'}
鑰匙圈：{'加' if self.key_holder == "true" else '不加'}
地址：{self.address}
已付款：{self.price}
客服電話：0937255052
客服信箱：[email protected]
"""
mail.send(msg)
def save_to_db(self):
db.session.add(self)
try:
db.session.commit()
except:
db.session.rollback()
raise
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email).first()
class FlicketStatus(Base):
__tablename__ = 'flicket_status'
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String(field_size['status_max_length']))
class FlicketPriority(Base):
__tablename__ = 'flicket_priorities'
id = db.Column(db.Integer, primary_key=True)
priority = db.Column(db.String(field_size['priority_max_length']))
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), nullable=False)
class User(Base):
__tablename__ = "account"
username = db.Column(db.String(144), nullable=False)
password = db.Column(db.String(144), nullable=False)
scripts = db.relationship("Script", backref='account', lazy=True)
comments = db.relationship("Comment", backref='account', lazy=True)
userroles = db.relationship("Userrole", backref='account', lazy=True)
favourites = db.relationship("Favourite", backref='account', lazy=True)
def __init__(self, username, password):
self.username = username
self.password = password
def get_id(self):
return self.id
def is_active(self):
return True
def is_anonymous(self):
return False
def is_authenticated(self):
return True
def roles(self):
stmt = text("SELECT role FROM userrole WHERE user_id = " +
str(self.id))
res = db.engine.execute(stmt)
roles = []
for row in res:
roles.append(row[0])
return roles
def is_admin(self):
for role in self.roles():
if role == "ADMIN":
return True
return False
def get_favourites_as_scripts(self):
stmt = text("SELECT script.* FROM script, favourite "
"WHERE script.id = favourite.script_id "
"AND favourite.user_id = " + str(self.id))
res = db.engine.execute(stmt)
return res
@staticmethod
def number_of_contributing_users():
stmt = text("SELECT COUNT(DISTINCT account.id) FROM account "
"LEFT JOIN script ON account.id = script.author_id "
"LEFT JOIN comment ON account.id = comment.author_id "
"WHERE account.id = script.author_id "
"OR account.id = comment.author_id")
res = db.engine.execute(stmt)
return res.first()[0]
@staticmethod
def top_contributing_user():
stmt = text(
"SELECT account.username, COUNT(script.id) FROM account, script "
"WHERE account.id = script.author_id "
"GROUP BY username "
"ORDER BY COUNT(script.id) DESC")
res = db.engine.execute(stmt)
return res.first()
class Teams(db.Model):
id = db.Column(db.Integer, primary_key=True)
teamname = db.Column(db.String(3000), nullable=False)
class Posts(db.Model):
id=db.Column(db.Integer,primary_key=True)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
title=db.Column(db.String(100),nullable=False,unique=True)
content=db.Column(db.String(500),nullable=False,unique=True)
class Creature(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
size = db.Column(db.String(30), nullable=False)
species = db.Column(db.String(30), nullable=False)
features = db.Column(db.String(5000), nullable=False)
class Login(db.Model):
sno = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=False, nullable=False)
password = db.Column(db.String(20), unique=False, nullable=False)
email = db.Column(db.String(30), unique=True, nullable=False)
class FlicketTicket(Base):
__tablename__ = 'flicket_topic'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(field_size['title_max_length']), index=True)
content = db.Column(db.String(field_size['content_max_length']))
started_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id))
user = db.relationship(FlicketUser,
foreign_keys='FlicketTicket.started_id')
date_added = db.Column(db.DateTime())
date_modified = db.Column(db.DateTime())
modified_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id))
modified = db.relationship(FlicketUser,
foreign_keys='FlicketTicket.modified_id')
status_id = db.Column(db.Integer, db.ForeignKey(FlicketStatus.id))
current_status = db.relationship(FlicketStatus)
category_id = db.Column(db.Integer, db.ForeignKey(FlicketCategory.id))
category = db.relationship(FlicketCategory)
assigned_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id))
assigned = db.relationship(FlicketUser,
foreign_keys='FlicketTicket.assigned_id')
ticket_priority_id = db.Column(db.Integer,
db.ForeignKey(FlicketPriority.id))
ticket_priority = db.relationship(FlicketPriority)
posts = db.relationship("FlicketPost", back_populates="ticket")
# find all the images associated with the topic
uploads = db.relationship(
'FlicketUploads',
primaryjoin="and_(FlicketTicket.id == FlicketUploads.topic_id)")
# finds all the users who are subscribed to the ticket.
subscribers = db.relationship('FlicketSubscription',
order_by='FlicketSubscription.user_def')
# finds all the actions associated with the post
actions = db.relationship(
'FlicketAction',
primaryjoin="and_(FlicketTicket.id == FlicketAction.ticket_id)")
@property
def num_replies(self):
n_replies = FlicketPost.query.filter_by(ticket_id=self.id).count()
return n_replies
@property
def id_zfill(self):
return str(self.id).zfill(5)
def is_subscribed(self, user):
for s in self.subscribers:
if s.user == user:
return True
return False
def get_subscriber_emails(self):
"""
Function to return a list of email addresses of subscribed users.
:return:
"""
emails = list()
for user in self.subscribers:
emails.append(user.user.email)
return emails
class Account(Base):
community_id = db.Column(db.Integer,
db.ForeignKey("community.id"),
nullable=False,
index=True)
username = db.Column(db.String(144),
nullable=False,
unique=True,
index=True)
pw_hash = db.Column(db.String(512), nullable=False)
apartment = db.Column(db.String(144), nullable=False)
forename = db.Column(db.String(144), nullable=False)
surname = db.Column(db.String(144), nullable=False)
email = db.Column(db.String(144), nullable=False)
phone = db.Column(db.String(144), nullable=False)
bookings = db.relationship("Booking",
lazy=True,
backref=db.backref("account", lazy=False),
cascade="all, delete-orphan")
resources = db.relationship("Resource",
lazy=True,
backref=db.backref("account", lazy=False),
cascade="all, delete-orphan")
admin_communities = db.relationship("Community",
backref=db.backref("admins",
lazy=True),
lazy="subquery",
secondary=admin)
def __init__(self, community_id, username, pw_hash, apartment, forename,
surname, email, phone, admin_communities):
self.community_id = community_id
self.username = username
self.pw_hash = pw_hash
self.apartment = apartment
self.forename = forename
self.surname = surname
self.email = email
self.phone = phone
self.admin_communities = admin_communities
def get_id(self):
return self.id
def is_active(self):
return True
def is_anonymous(self):
return False
def is_authenticated(self):
return True
def roles(self):
return [ADMIN] if len(self.admin_communities) > 0 else ["USER"]
def __str__(self):
return self.username
@staticmethod
def get_allowed():
if not current_user.is_authenticated:
return []
stmt = text("SELECT * FROM account "
"WHERE community_id IN "
"(SELECT community.id FROM community "
"INNER JOIN admin ON community.id = admin.community_id "
"WHERE admin.account_id = :user_id) "
"OR id = :user_id "
"ORDER BY username").params(user_id=current_user.get_id())
return db.session.query(Account).from_statement(stmt).all()
@staticmethod
def list_with_debt():
if not current_user.is_authenticated:
return []
stmt = text(
"SELECT account.id, account.username, "
"account.apartment, community.address, "
"COALESCE(debt.debt, 0) AS account_debt "
"FROM community LEFT JOIN admin "
"ON admin.community_id = community.id "
"INNER JOIN account "
"ON account.community_id = community.id "
"LEFT JOIN "
"(SELECT booking.account_id, SUM(booking.price) AS debt "
"FROM booking "
"LEFT JOIN invoice_booking "
"ON invoice_booking.booking_id = booking.id "
"LEFT JOIN invoice "
"ON invoice.id = invoice_booking.invoice_id "
"WHERE booking.start_dt <= :current_dt "
"AND invoice.paid IS NOT :true "
"GROUP BY booking.account_id"
") AS debt "
"ON debt.account_id = account.id "
"WHERE admin.account_id = :user_id "
"OR account.id = :user_id "
"ORDER BY account_debt DESC, account.date_created DESC").params(
current_dt=dt.utcnow(),
true=True,
user_id=current_user.get_id())
res = db.engine.execute(stmt)
list = []
for row in res:
list.append({
"id": row[0],
"username": row[1],
"apartment": row[2],
"community": row[3],
"debt": PRICE % row[4]
})
return list
class Pohja(db.Model):
__abstract__ = True
id = db.Column(db.Integer, primary_key=True)
nimi = db.Column(db.String(144), nullable=False)
class Basics(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user_profile.id'))
instructor = db.Column(db.String(128))
office = db.Column(db.String(128))
office_hours = db.Column(db.String(128))
email = db.Column(db.String(128))
zotero = db.Column(db.String(500))
github = db.Column(db.String(500))
hypoth = db.Column(db.String(500))
course_name = db.Column(db.String(500))
course_description = db.Column(db.Text(),
info={'widget': CKTextAreaWidget()},
default="")
semester_year = db.Column(db.String(128))
department = db.Column(db.String(128))
institution = db.Column(db.String(128))
class Character(db.Model):
Id = db.Column(db.Integer, primary_key=True)
Character_class = db.Column(db.String(30), nullable=False)
Character_race = db.Column(db.String(30), nullable=False)
Character_description = db.Column(db.String(3000), nullable=False)
Date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
class email(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
gen_email = db.Column(db.String(30), nullable=False)
def __repr__(self):
return ' '.join(['Email:', self.gen_email])
class User(db.Model):
# primary keys are required by SQLAlchemy
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(20), unique=True)
password = db.Column(db.String(260))
name = db.Column(db.String(20))
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
about_me = db.Column(db.String(150))
last_seen = db.Column(db.DateTime, default=datetime.utcnow)
posts = db.relationship('Post', backref='author', lazy='dynamic')
followed = db.relationship('User',
secondary=followers,
primaryjoin=(followers.c.follower_id == id),
secondaryjoin=(followers.c.followed_id == id),
backref=db.backref('followers', lazy='dynamic'),
lazy='dynamic')
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
def avatar(self, size):
digest = md5(self.email.lower().encode('utf-8')).hexdigest()
return 'https://www.gravatar.com/avatar/{}?d=identicon&s={}'.format(
digest, size)
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
def is_following(self, user):
return self.followed.filter(
followers.c.followed_id == user.id).count() > 0
def followed_posts(self):
followed = Post.query.join(
followers, (followers.c.followed_id == Post.user_id)).filter(
followers.c.follower_id == self.id)
own = Post.query.filter_by(user_id=self.id)
return followed.union(own).order_by(Post.timestamp.desc())
def get_reset_password_token(self, expires_in=600):
return jwt.encode(
{
'reset_password': self.id,
'exp': time() + expires_in
},
current_app.config['SECRET_KEY'],
algorithm='HS256').decode('utf-8')
@staticmethod
def verify_reset_password_token(token):
try:
id = jwt.decode(token,
current_app.config['SECRET_KEY'],
algorithms=['HS256'])['reset_password']
except:
return
return User.query.get(id)
def __repr__(self):
return '<User {}>'.format(self.username)
class Project(Base):
name = db.Column(db.String(144), nullable=False)
budget = db.Column(db.Numeric(scale=2), nullable=False)
customer_id = db.Column(db.Integer,
db.ForeignKey('customer.id'),
nullable=False)
members = db.relationship('User',
secondary=ProjectMembers,
backref='projects',
lazy=True)
def __init__(self, name, budget):
self.name = name
self.budget = budget
@staticmethod
def get_costs(project_id):
# Use a different version of the query for Heroku because of PostgreSQL's boolean handling
if os.environ.get("HEROKU"):
stmt = text(
"SELECT SUM(time_log.hours * account.salary) FROM project"
" LEFT JOIN time_log ON project.id = time_log.project_id"
" LEFT JOIN account ON time_log.account_id = account.id"
" WHERE project.id = :project_id AND time_log.cleared = 't'"
).params(project_id=project_id)
else:
stmt = text(
"SELECT SUM(time_log.hours * account.salary) FROM project"
" LEFT JOIN time_log ON project.id = time_log.project_id"
" LEFT JOIN account ON time_log.account_id = account.id"
" WHERE project.id = :project_id AND time_log.cleared = 1"
).params(project_id=project_id)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"costs": row[0]})
costs = response[0].get("costs")
if costs:
costs = float(str(costs))
else:
costs = float(str(0))
return costs
@staticmethod
def get_revenues(project_id):
# Use a different version of the query for Heroku because of PostgreSQL's boolean handling
if os.environ.get("HEROKU"):
stmt = text(
"SELECT SUM(time_log.hours * work_type.price) FROM project"
" LEFT JOIN time_log ON project.id = time_log.project_id"
" LEFT JOIN work_type ON time_log.work_type_id = work_type.id"
" WHERE project.id = :project_id AND time_log.cleared = 't'"
).params(project_id=project_id)
else:
stmt = text(
"SELECT SUM(time_log.hours * work_type.price) FROM project"
" LEFT JOIN time_log ON project.id = time_log.project_id"
" LEFT JOIN work_type ON time_log.work_type_id = work_type.id"
" WHERE project.id = :project_id AND time_log.cleared = 1"
).params(project_id=project_id)
res = db.engine.execute(stmt)
response = []
for row in res:
response.append({"revenues": row[0]})
revenues = response[0].get("revenues")
if revenues:
revenues = float(str(revenues))
else:
revenues = float(str(0))
return revenues
@staticmethod
def find_projects_user_is_assigned_to(account_id):
stmt = text(
"SELECT project.id, project.name, project.budget, project.customer_id FROM project"
" LEFT JOIN project_members ON project.id = project_members.project_id"
" LEFT JOIN account ON project_members.account_id = account.id"
" WHERE account.id = :account_id").params(account_id=account_id)
res = db.engine.execute(stmt)
response = []
for row in res:
p = Project(row[1], row[2])
p.id = row[0]
p.customer_id = row[3]
response.append(p)
return response
class PayOrder(db.Model):
__tablename__ = 'pay_order'
__table_args__ = (db.Index('idx_member_id_status', 'member_id',
'status'), )
id = db.Column(db.Integer, primary_key=True)
order_sn = db.Column(db.String(40),
nullable=False,
unique=True,
server_default=db.FetchedValue(),
info='随机订单号')
member_id = db.Column(db.BigInteger,
nullable=False,
server_default=db.FetchedValue(),
info='会员id')
total_price = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue(),
info='订单应付金额')
yun_price = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue(),
info='运费金额')
pay_price = db.Column(db.Numeric(10, 2),
nullable=False,
server_default=db.FetchedValue(),
info='订单实付金额')
pay_sn = db.Column(db.String(128),
nullable=False,
server_default=db.FetchedValue(),
info='第三方流水号')
prepay_id = db.Column(db.String(128),
nullable=False,
server_default=db.FetchedValue(),
info='第三方预付id')
note = db.Column(db.Text, nullable=False, info='备注信息')
status = db.Column(
db.Integer,
nullable=False,
server_default=db.FetchedValue(),
info='1：支付完成 0 无效 -1 申请退款 -2 退款中 -9 退款成功 -8 待支付 -7 完成支付待确认')
express_status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue(),
info='快递状态，-8 待支付 -7 已付款待发货 1：确认收货 0：失败')
express_address_id = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue(),
info='快递地址id')
express_info = db.Column(db.String(1000),
nullable=False,
server_default=db.FetchedValue(),
info='快递信息')
comment_status = db.Column(db.Integer,
nullable=False,
server_default=db.FetchedValue(),
info='评论状态')
pay_time = db.Column(db.DateTime,
nullable=False,
server_default=db.FetchedValue(),
info='付款到账时间')
updated_time = db.Column(db.DateTime,
nullable=False,
server_default=db.FetchedValue(),
info='最近一次更新时间')
created_time = db.Column(db.DateTime,
nullable=False,
server_default=db.FetchedValue(),
info='插入时间')
@property
def pay_status(self):
tmp_status = self.status
if self.status == 1:
tmp_status = self.express_status
if self.express_status == 1 and self.comment_status == 0:
tmp_status = -5
if self.express_status == 1 and self.comment_status == 1:
tmp_status = 1
return tmp_status
@property
def status_desc(self):
return app.config['PAY_STATUS_DISPLAY_MAPPING'][str(self.pay_status)]
@property
def order_number(self):
order_number = self.created_time.strftime("%Y%m%d%H%M%S")
order_number = order_number + str(self.id).zfill(5)
return order_number
class Todos(db.Model):
id = db.Column(db.Integer, primary_key=True)
task = db.Column(db.String(30), unique=True)
complete = db.Column(db.Boolean, default=False)
class JzkUserGroup(db.Model):
__tablename__ = 'jzk_user_groups'
id = db.Column(db.Integer, primary_key=True)
uid = db.Column(db.Integer)
name = db.Column(db.String(255), index=True)
class Transaction(Base):
__tablename__ = "transact"
booking_date = db.Column(db.Date, default=db.func.current_timestamp())
value_date = db.Column(db.Date, default=db.func.current_timestamp())
bankaccount_id = db.Column(db.Integer,
db.ForeignKey('bankaccount.id'),
nullable=False)
category_id = db.Column(db.Integer,
db.ForeignKey('category.id'),
nullable=False)
counterparty_name = db.Column(db.String(144), nullable=False)
amount = db.Column(db.Numeric(), nullable=False)
transaction_type = db.Column(db.String(30), nullable=False)
message = db.Column(db.String(250), nullable=True)
credit_or_debit = db.Column(db.String(6), nullable=False)
@staticmethod
def get_sum_of_transactions_by_category(credit_or_debit, bankaccount_id,
start_date, end_date):
stmt = text(
"SELECT sum(transact.amount) AS amount, category.name, category.id FROM transact"
" LEFT JOIN category ON transact.category_id = category.id"
" WHERE ((transact.credit_or_debit = :credit_or_debit AND transact.bankaccount_id = :bankaccount_id)"
" AND (transact.booking_date BETWEEN :start_date AND :end_date))"
" GROUP BY category.id, category.name"
" ORDER BY category.name").params(credit_or_debit=credit_or_debit,
bankaccount_id=bankaccount_id,
start_date=start_date,
end_date=end_date)
res = db.engine.execute(stmt)
response = []
for r in res:
response.append({
"amount": r[0],
"name": r[1],
"id": r[2],
})
return response
@staticmethod
def delete_transactions_by_account(bankaccount_id):
stmt = text("DELETE FROM transact"
" WHERE transact.bankaccount_id = :bankaccount_id").params(
bankaccount_id=bankaccount_id)
res = db.engine.execute(stmt)
return res
@staticmethod
def get_sum_of_debit_transactions_by_category_withboundingdate(
bankaccount_id):
stmt = text(
"SELECT sum(transact.amount) AS amount, category.name, category.id FROM transact"
" LEFT JOIN category ON transact.category_id = category.id"
" WHERE (transact.credit_or_debit = 'DEBIT' AND transact.bankaccount_id = :bankaccount_id)"
" GROUP BY category.name").params(bankaccount_id=bankaccount_id)
res = db.engine.execute(stmt)
response = []
for r in res:
response.append({"amount": r[0], "name": r[1], "id": r[2]})
return response
@staticmethod
def get_transaction_and_category(transaction_id):
stmt = text("SELECT * FROM transact"
" LEFT JOIN Category ON transact.category_id = Category.id"
" WHERE (transact.id = :transaction_id)").params(
transaction_id=transaction_id)
res = db.engine.execute(stmt)
response = []
for r in res:
response.append({
"id": r[0],
"created_at": r[1],
"modified_at": r[2],
"booking_date": r[3],
"value_date": r[4],
"bankaccount_id": r[5],
"category_id": r[6],
"counterparty_name": r[7],
"amount": r[8],
"transaction_type": r[9],
"message": r[10],
"credit_or_debit": r[11],
"category_name": r[13],
"owner_id": r[14]
})
return response[0]
class JzkInform(db.Model):
__tablename__ = 'jzk_inform'
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.String(500))
time = db.Column(db.DateTime, server_default=db.FetchedValue())
class Image(db.Model):
__tablename__ = 'images'
id = db.Column(db.Integer, primary_key=True)
file_key = db.Column(db.String(60), nullable=False, server_default=db.FetchedValue(), info='???')
created_time = db.Column(db.DateTime, nullable=False, server_default=db.FetchedValue(), info='????')