from sqlalchemy import String
from app.main import db
truck_shipment = db.Table(
'truck_shipment', db.Column('truck_id', String, db.ForeignKey('truck.id')),
db.Column('shipment_id', String, db.ForeignKey('shipment.id'),
unique=True))
class sdUser(db.Model):
__tablename__ = "sd11_users"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), nullable=False, comment="Name")
display_name = db.Column(db.String(50), comment="Display name")
comment = db.Column(db.Text, comment="Comment")
status = db.Column(db.Integer, server_default="0", comment="Status")
create_time = db.Column(db.DateTime,
server_default=func.now(),
comment="Create time")
update_time = db.Column(db.DateTime,
server_default=func.now(),
comment="Update time")
password_hash = db.Column("password", db.String(64), comment="Password")
cust_id = db.Column(db.Integer,
db.ForeignKey("sd10_customers.id"),
comment="Customer id")
email = db.Column(db.String(100), comment="Email")
telephone = db.Column(db.String(30), comment="Telephone number")
line_id = db.Column(db.String(30), comment="LINE id")
__table_args__ = (db.UniqueConstraint("cust_id", "name"), )
def __repr__(self):
return f"<sdUser id={self.id}/name={self.name}/display_name={self.display_name}>"
@property
def password(self):
raise AttributeError("password field cannot be read")
@password.setter
def password(self, new_password):
self.password_hash = bcrypt.generate_password_hash(
new_password).decode("utf-8")
def check_password(self, password):
return bcrypt.check_password_hash(self.password_hash, password)
@staticmethod
def search(cust_id, user_name):
return sdUser.query.filter_by(cust_id=cust_id).filter_by(
name=user_name).first()
@staticmethod
def getall(cust_id):
users = db.session.query(sdUser).filter(
sdUser.cust_id == cust_id).all()
return users
@staticmethod
def getdetail(cust_id, user_list):
users = db.session.query(sdUser).filter(
sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all()
return users
@staticmethod
def add(cust_id, name, display_name, password, email, telephone, comment):
obj = sdUser()
obj.cust_id = cust_id
obj.name = name
obj.display_name = display_name
obj.password = password
obj.email = email
obj.telephone = telephone
obj.comment = comment
return obj
# HINT delete_all_user_groups: delete all user groups record corresponding to the user from m2m table
@staticmethod
def delete_all_user_groups(cust_id, user_list):
users = db.session.query(sdUser).filter(
sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all()
for user in users:
ug_rels = user.user_groups
ug_rels.clear()
@staticmethod
def delete(cust_id, user_list):
sdUser.query.filter(
sdUser.cust_id == cust_id,
sdUser.id.in_(user_list)).delete(synchronize_session=False)
@staticmethod
def update(cust_id, user_id, name, display_name, password, email,
telephone, comment):
obj = db.session.query(sdUser).filter(sdUser.id == user_id).first()
if obj:
# HINT This method has too much if/else,
# here should handle by Frontend, or use this method
obj.password = password if password else obj.password
obj.name = name if name else obj.name
obj.display_name = display_name if display_name else obj.display_name
obj.email = email if email else obj.email
obj.telephone = telephone if telephone else obj.telephone
obj.comment = comment if comment else obj.comment
else:
obj = sdUser()
obj.cust_id = cust_id
obj.name = name
obj.display_name = display_name
obj.password = password
obj.email = email
obj.telephone = telephone
obj.comment = comment
return obj
@staticmethod
def get_admin(cust_id):
admin = db.session.query(sdUser).filter(
sdUser.cust_id.in_(cust_id), sdUser.name == Constant.ADMIN).all()
return admin
remove_from_index(obj.__tablename__, obj)
session._changes = None
@classmethod
def reindex(cls):
for obj in cls.query:
add_to_index(cls.__tablename__, obj)
db.event.listen(db.session, "before_commit", SearchableMixin.before_commit)
db.event.listen(db.session, "after_commit", SearchableMixin.after_commit)
followers = db.Table(
"followers",
db.Column("follower_id", db.Integer, db.ForeignKey("user.id")),
db.Column("followed_id", db.Integer, db.ForeignKey("user.id")),
)
likes = db.Table(
"likes",
db.Column("post_id", db.Integer, db.ForeignKey("post.id")),
db.Column("user_id", db.Integer, db.ForeignKey("user.id")),
)
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)
class File(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
directory = db.Column(db.String, nullable=False)
url = db.Column(db.String, nullable=False)
status_id = db.Column(db.Integer,
db.ForeignKey("status.id"),
nullable=False,
index=True)
type_id = db.Column(db.Integer,
db.ForeignKey("type.id"),
nullable=False,
index=True)
size = db.Column(db.String)
speed = db.Column(db.String)
percent = db.Column(db.Float, nullable=False)
time_remaining = db.Column(db.String)
@classmethod
def get_all_files(cls, status=None, page=None, limit=None):
page = int(
page) if page is not None and page.isdigit() else INITIAL_PAGE
limit = int(
limit) if limit is not None and limit.isdigit() else DEFAULT_LIMIT
query = File.filter_status(File.query, status)
query = query.order_by(File.id.desc()).paginate(page, limit,
False).items
return {
"downloads": [item.marshal() for item in query],
"totalPages": File.total_pages(status, limit),
}
@classmethod
def get_file(cls, file_id):
query = File.query.filter_by(id=file_id).first()
return query
@classmethod
@retry
def new_file(cls, url, type_id, directory, name=None):
new_file = File(
url=url,
name=name,
directory=directory,
type_id=type_id,
status_id=Status.STATUS_IN_PROGRESS_ID,
percent=0.0,
)
db.session.add(new_file)
db.session.commit()
logger.info(f"New download ID: {new_file.id}")
return new_file
@retry
def update_db(self, stats):
if "name" in stats:
self.name = stats["name"]
if "percent" in stats:
self.percent = stats["percent"]
if "size" in stats:
self.size = stats["size"]
if "speed" in stats:
self.speed = stats["speed"]
if "time_remaining" in stats:
self.time_remaining = stats["time_remaining"]
db.session.commit()
@retry
def complete(self):
self.percent = 100
self.status_id = Status.STATUS_COMPLETED_ID
logger.info(f"{self.name} completed")
db.session.commit()
@retry
def error(self):
self.status_id = Status.STATUS_ERROR_ID
logger.error(f"{self.name} errored")
db.session.commit()
@staticmethod
def filter_status(query, status):
if status is None:
return query
elif isinstance(status, list):
status_ids = [Status.name_to_id(item) for item in status]
return query.filter(File.status_id.in_(status_ids))
else:
return query.filter(File.status_id == Status.name_to_id(status))
@staticmethod
def total_pages(status, limit):
total_count = File.filter_status(
db.session.query(db.func.count(File.id)), status).scalar()
return math.ceil(total_count / limit) or 1
def marshal(self):
return {
"id": self.id,
"name": self.name,
"url": self.url,
"directory": self.directory,
"status": Status.id_to_name(self.status_id),
"type": Type.id_to_name(self.type_id),
"percent": self.percent,
"size": self.size,
"speed": self.speed,
"timeRemaining": self.time_remaining,
}
class ProductCommentsModel(db.Model):
__tablename__ = 'productcomments'
id = db.Column(db.Integer, primary_key=True)
product_id = db.Column(db.Integer, db.ForeignKey('products.id'))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
comment = db.Column(db.String(256))
class Post(Base, SearchableMixin):
"""
Description of User model.
Columns
-----------
:id: int [pk]
:title: Text [not NULL]
:author_id: int [Foreign Key]
:creation_time: DateTime [not NULL]
:last_edit_time: DateTime [not NULL]
:post_body: Text
# Relationships
:comments: Relationship -> Comments (one to many)
"""
# Columns
id = db.Column(db.Integer, db.ForeignKey("base.id"), primary_key=True)
post_id = db.Column(db.Integer,
autoincrement=True,
primary_key=True,
unique=True)
title = db.Column(db.Text, nullable=False)
post_movie = db.Column(db.String(20))
tags = db.Column(db.JSON)
__searchable__ = ['title', 'body', 'tags']
__mapper_args__ = {
'polymorphic_identity': 'post',
'inherit_condition': (id == Base.id)
}
comments = db.relationship(
'Comment',
primaryjoin="(Post.post_id == Comment.parent_post_id)",
backref=db.backref('post'),
lazy='dynamic')
def __init__(self, author_id, post_movie, title, post_body, tags):
super().__init__(author_id, post_body, "post")
self.title = title
self.post_movie = post_movie
self.tags = tags
db.session.add(self)
db.session.commit()
def add_comment(self, author_id, comment_body):
parent_post_id = self.id
comment = Comment(author_id, parent_post_id, comment_body)
self.comments.append(comment)
db.session.commit()
return comment.id
def update_col(self, key, value):
setattr(self, key, value)
db.session.commit()
def delete_post(self, post_id):
post = Post.query.filter_by(id=post_id).delete()
db.session.commit()
import datetime
from app.main import db, flask_bcrypt
import jwt
from app.main.model.blacklist import BlacklistToken
from ..config import key
# many to many table between users and card lists
user_card_list = db.Table(
'user_card_list',
db.Column('user_id',
db.Integer,
db.ForeignKey('user.id'),
primary_key=True),
db.Column('card_list_id',
db.Integer,
db.ForeignKey('card_list.id'),
primary_key=True),
)
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
email = db.Column(db.String(255), unique=True, nullable=False)
username = db.Column(db.String(50), unique=True)
password_hash = db.Column(db.String(100))
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
class Post(db.Model):
"""
Description of Post model.
Rows
-----------
:post_id: int [pk]
:authorId: varchar [ref: > users.id]
:title: varchar
:body: text
:post_time: timestamp [not null]
:avg_rating: float
:num_rating: int
"""
# Columns
post_id = db.Column(db.Integer, primary_key=True)
_author_id = db.Column(db.String(256),
db.ForeignKey('user.username'),
nullable=False)
title = db.Column(db.String(128), nullable=False)
body = db.Column(db.Text, nullable=False)
post_time = db.Column(db.DateTime, default=datetime.datetime.now())
avg_rating = db.Column(db.Float, default=0.0)
num_rating = db.Column(db.Integer, default=0)
# Relationships
author = db.relationship('User', backref='posts', lazy=False)
tags = db.relationship('Tag',
secondary=postTagJunction,
lazy='subquery',
backref=db.backref('posts', lazy=True))
# savers = db.relationship('User', secondary=postSaves, lazy=True,
# backref=db.backref('posts', lazy='subquery'))
images = db.relationship('ImgLink',
secondary=imgPostJunction,
lazy='subquery')
def __init__(self, author, title, body):
self.author = author
self.title = title
self.body = body
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
# Getters and Setters for the fields
# @hybrid_property
# def author_id(self):
# return self.author
# @author_id.setter
# def author_id(self, authorId):
# try:
# user = User.query.filter_by(id=authorId).first()
# if user.last_logout != None:
# raise LoginError
# else:
# self._author_id = authorId
# except:
# # Stub to be handled later
# print("Get back to login page")
@classmethod
def getArticlesByTags(cls, tagList, connector='AND'):
"""
Get all articles that have a tag in tagList.
If connector is AND intersection of all posts set for each tag will be
returned. If it is OR, union will be returned
"""
if connector == 'AND':
posts = set()
for tag in tagList:
if len(posts) == 0:
posts = set(cls.query.filter(tag in cls.tags).all())
else:
posts.intersection(
set(cls.query.filter(tag in cls.tags).all()))
return list(posts)
elif connector == 'OR':
posts = set()
for tag in tagList:
if len(posts) == 0:
posts = set(cls.query.filter(tag in cls.tags).all())
else:
posts.union(set(cls.query.filter(tag in cls.tags).all()))
return list(posts)
@staticmethod
def getArticles(post_id):
return Post.query.filter_by(post_id=post_id).first()
@staticmethod
def getRandomizedArticles(size):
return sample(Post.query.all(), size)
class UserAddressModel(db.Model):
__tablename__ = "useraddress"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
address = db.Column(db.String(50))
class Wishlist(db.Model):
#creating user wishlist table in database
__tablename__ = "wishlist"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
product_id = db.Column(db.Integer, db.ForeignKey("products.product_id"))
from random import sample
from sqlalchemy.ext.hybrid import hybrid_property
# from . import *
from app.main import db
from app.main.models.enums import PostType
# from app.main.models.users import User
from app.main.models.errors import LoginError
from app.main.models.imgLinks import imgPostJunction
postTagJunction = db.Table(
'postTagJunction',
db.Column('post_id',
db.Integer,
db.ForeignKey('post.post_id'),
primary_key=True),
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True))
class Post(db.Model):
"""
Description of Post model.
Rows
-----------
:post_id: int [pk]
:authorId: varchar [ref: > users.id]
:title: varchar
:body: text
:post_time: timestamp [not null]
:avg_rating: float
# -*- coding:utf-8 -*-
from app.main import db
from app.staff.models import StaffAccount
course_instructors = db.Table('eduqa_course_instructor_assoc',
db.Column('course_id', db.Integer, db.ForeignKey('eduqa_courses.id')),
db.Column('instructor_id', db.Integer, db.ForeignKey('eduqa_course_instructors.id'))
)
session_instructors = db.Table('eduqa_session_instructor_assoc',
db.Column('session_id', db.Integer, db.ForeignKey('eduqa_course_sessions.id')),
db.Column('instructor_id', db.Integer, db.ForeignKey('eduqa_course_instructors.id'))
)
class EduQAProgram(db.Model):
__tablename__ = 'eduqa_programs'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(255), nullable=False,
info={'label': u'ชื่อ'})
degree = db.Column(db.String(), nullable=False,
info={'label': u'ระดับ',
'choices': (('undergraduate', 'undergraduate'),
('graudate', 'graduate'))
})
class EduQACurriculum(db.Model):
__tablename__ = 'eduqa_curriculums'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
program_id = db.Column(db.ForeignKey('eduqa_programs.id'),
# coding:utf-8
from app.main import db
from sqlalchemy.sql import func
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import UserMixin
from datetime import datetime
from flask import session, abort, globals
import functools
sys_user_role = db.Table(
'sys_user_role', # 用户角色关联表
db.Column('userId', db.Integer, db.ForeignKey('sys_user.id')),
db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')),
db.Column('createTime', db.DateTime, default=datetime.now),
)
sys_role_permission = db.Table(
'sys_role_permission', # 角色权限关联表
db.Column('permissionId', db.Integer, db.ForeignKey('sys_permission.id')),
db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')),
db.Column('createTime', db.DateTime, default=datetime.now),
)
sys_role_menu = db.Table(
'sys_role_menu', # 用户菜单关联表
db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')),
db.Column('menuId', db.Integer, db.ForeignKey('sys_menu.id')),
db.Column('createdTime', db.DateTime, default=datetime.now),
db.Column('isDelete', db.Boolean, default=False),
)
class UpdateForm(db.Model):
"""Namings as in Odoo.
"""
__tablename__ = "update_form"
form_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
form_status = db.Column(db.String(100))
name = db.Column(db.String(100)) # full name in ukrainian
birth_date = db.Column(db.Date())
image_1920 = db.Column(
db.String(500000)
) # Odoo saves image as base64 encoded string, f*cking large str
# email = db.Column(db.String(100)) # Do not allow to update email here, because we use email for login
contact_country = db.Column(db.String(100)) # selection field
contact_city = db.Column(db.String(100))
mobile = db.Column(db.String(15))
skype = db.Column(db.String(100))
telegram = db.Column(db.String(100))
viber = db.Column(db.String(100))
facebook_link = db.Column(db.String(100))
linkedin_link = db.Column(db.String(100))
diploma_naukma = db.Column(db.Boolean)
bachelor_degree = db.Column(db.Boolean())
bachelor_faculty = db.Column(db.String(100)) # selection field
bachelor_speciality = db.Column(db.String(100)) # selection field
bachelor_year_in = db.Column(db.String(100)) # selection field
bachelor_year_out = db.Column(db.String(100)) # selection field
master_degree = db.Column(db.Boolean())
master_faculty = db.Column(db.String(100)) # selection field
master_speciality = db.Column(db.String(100)) # selection field
master_year_in = db.Column(db.String(100)) # selection field
master_year_out = db.Column(db.String(100)) # selection field
parent_id = db.Column(
db.Integer) # company id in Odoo, many2one field in Odoo
company_name = db.Column(db.String(100))
function = db.Column(db.String(100)) # job position
# foreign keys
alumni_id = db.Column(db.Integer,
db.ForeignKey('alumni.alumni_id',
onupdate="CASCADE",
ondelete="NO ACTION"),
nullable=False)
alumni = db.relationship("Alumni", back_populates="update_form")
operator_id = db.Column(db.Integer,
db.ForeignKey('operator.operator_id',
onupdate="CASCADE",
ondelete="NO ACTION"),
nullable=True)
operator = db.relationship("Operator", back_populates="update_form")
def __init__(self, name, birth_date, image_1920, contact_country,
contact_city, mobile, skype, telegram, viber, facebook_link,
linkedin_link, diploma_naukma, bachelor_degree,
bachelor_faculty, bachelor_speciality, bachelor_year_in,
bachelor_year_out, master_degree, master_faculty,
master_speciality, master_year_in, master_year_out, parent_id,
company_name, function, alumni_id, operator_id):
self.form_status = 'new' # TODO: create enum for the form statuses
self.name = name
self.birth_date = datetime.strptime(birth_date, '%Y-%m-%d').date()
self.image_1920 = image_1920
self.contact_country = contact_country
self.contact_city = contact_city
self.mobile = mobile
self.skype = skype
self.telegram = telegram
self.viber = viber
self.facebook_link = facebook_link
self.linkedin_link = linkedin_link
self.diploma_naukma = diploma_naukma
self.bachelor_degree = bachelor_degree
self.bachelor_faculty = bachelor_faculty
self.bachelor_speciality = bachelor_speciality
self.bachelor_year_in = bachelor_year_in
self.bachelor_year_out = bachelor_year_out
self.master_degree = master_degree
self.master_faculty = master_faculty
self.master_speciality = master_speciality
self.master_year_in = master_year_in
self.master_year_out = master_year_out
self.parent_id = parent_id
self.company_name = company_name
self.function = function
self.alumni_id = alumni_id
self.operator_id = operator_id
def update(self, data):
for key, item in data.items():
if hasattr(self, key):
setattr(self, key, item)
db.session.commit()
class Cart(db.Model):
#create class table in sql
__tablename__ = "cart"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey("users.id"))
product_id = db.Column(db.Integer, db.ForeignKey("products.product_id"))
class sdDevice(db.Model):
__tablename__ = "sd21_devices"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), comment="Name")
display_name = db.Column(db.String(50), comment="Display name")
comment = db.Column(db.Text, comment="Comment")
create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time")
update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time")
dev_type = db.Column(db.Integer, server_default="0",
comment="Device type: 0: unknown, 1: traffic light, 2: others")
cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id")
vendor_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Vendor id")
status = db.Column(db.Integer, server_default="0",
comment="Device status flag(bit). 0: warning, bit 1: error，可持續新增")
power_status = db.Column(db.Integer, comment="Device power status. null: unknown, 0: off, 1: on")
device_group_id = db.Column(db.Integer, db.ForeignKey("sd22_device_groups.id"),
comment="Device group id, null means ungroup")
controller_id = db.Column(db.Integer, db.ForeignKey(sdController.id), comment="Controller id")
led_id = db.Column(db.Integer, db.ForeignKey(sdLed.id), comment="LED id")
vender_device_id = db.Column(db.String(25), comment="Vender device id")
wgs_x = db.Column(db.Float, comment="GPS X")
wgs_y = db.Column(db.Float, comment="GPS Y")
address = db.Column(db.String(100), comment="Address")
device_info = db.relationship("sdDeviceInfo", uselist=False, backref="device")
__table_args__ = (db.UniqueConstraint("cust_id", "name"),)
def __repr__(self):
return (
f"<sdDevice id={self.id}/name={self.name}/display_name={self.display_name}/cust_id={self.cust_id}"
f"/wgs_x={self.wgs_x}/wgs_y={self.wgs_y}/address={self.address}>"
f"/controller_id={self.controller_id}/led_id={self.led_id}"
)
@staticmethod
def delete(cust_id, device_list):
sdDevice.query.filter(sdDevice.cust_id == cust_id,
sdDevice.id.in_(device_list)).delete(synchronize_session=False)
@staticmethod
def get_detail(cust_id, device_id_list):
devices = db.session.query(sdDevice).filter(or_(sdDevice.cust_id == cust_id,
sdDevice.vendor_id == cust_id),
sdDevice.id.in_(device_id_list)).all()
return devices
@staticmethod
def get_devices_in_groups(cust_id, group_id_list):
id_list = []
device_id_tuple = db.session.query(sdDevice.id).filter(sdDevice.cust_id == cust_id,
sdDevice.device_group_id.in_(group_id_list)).all()
for device_id in device_id_tuple:
id_list.append(device_id[0])
return id_list
@staticmethod
def add(cust_id, device, led, controller):
obj = sdDevice()
obj.cust_id = cust_id
obj.name = device.get("name")
obj.display_name = device.get("display_name")
obj.comment = device.get("comment")
obj.controller = controller
obj.led = led
obj.wgs_x = device.get('wgs_x')
obj.wgs_y = device.get('wgs_y')
obj.address = device.get('address')
return obj
@staticmethod
def update(cust_id, device, data):
obj = db.session.query(sdDevice).filter(sdDevice.id == device["id"]).first()
if obj:
obj.cust_id = cust_id
obj.name = device.get("name")
obj.display_name = device.get("display_name")
obj.comment = device.get("comment")
obj.wgs_x = device.get("wgs_x")
obj.wgs_y = device.get("wgs_y")
obj.address = device.get("address")
# if threre is led or controller then update.
led = data.get("led")
if led:
obj.led = led
controller = data.get("controller")
if controller:
obj.controller = controller
return obj
class CartModel(db.Model):
__tablename__ = 'cart'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
class Movie(SearchableMixin, db.Model):
"""
Description of User model.
Columns
-----------
:id: int [pk]
:user_id: int [Foreign Key -> User.id]
:imdb_ID: varchar(128) [not NULL]
:title: Text [not NULL]
:year: int
:release_date: DateTime
:runtime: int
:genre: JSON
:director: JSON
:writer: JSON
:actors: JSON
:plot: Text
:language: JSON
:country: JSON
:awards: Text
:ratings: JSON
:imdb_rating: Float
:rotten_tomatoes: int
:metascore: int
:poster_URL: varchar(255)
:box_office: varchar(255)
:added_to_db: DateTime
"""
# Columns
id = db.Column(db.Integer, primary_key=True)
parent_user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
imdb_ID = db.Column(db.String(128), unique=True, nullable=False)
title = db.Column(db.Text, nullable=False)
year = db.Column(db.Integer)
release_date = db.Column(db.String(128))
runtime = db.Column(db.String(128))
plot = db.Column(db.Text)
genre = db.Column(db.JSON)
director = db.Column(db.JSON)
writer = db.Column(db.JSON)
actors = db.Column(db.JSON)
language = db.Column(db.JSON)
country = db.Column(db.JSON)
awards = db.Column(db.Text)
imdb_rating = db.Column(db.String(128))
rotten_tomatoes = db.Column(db.String(128))
metascore = db.Column(db.String(128))
poster_url = db.Column(db.String(255))
box_office = db.Column(db.String(128))
added_time = db.Column(db.DateTime)
__searchable__ = [
'title', 'year', 'genre', 'director', 'actors', 'language', 'country'
]
def __init__(self, imdb_ID, title, year, release_date, runtime, genre,
director, writer, actors, plot, language, country, awards,
imdb_rating, rotten_tomatoes, metascore, poster_url,
box_office):
self.imdb_ID = imdb_ID
self.title = title
self.year = year
self.release_date = release_date
self.runtime = runtime
self.genre = genre
self.director = director
self.writer = writer
self.actors = actors
self.plot = plot
self.language = language
self.country = country
self.awards = awards
self.imdb_rating = imdb_rating
self.rotten_tomatoes = rotten_tomatoes
self.metascore = metascore
self.poster_url = poster_url
self.box_office = box_office
self.added_time = datetime.datetime.now()
db.session.add(self)
db.session.commit()
def update_col(self, key, value):
setattr(self, key, value)
db.session.commit()
class Recipe(db.Model):
__tablename__ = "recipes"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text(), nullable=False)
description = db.Column(db.Text(), nullable=False)
steps = db.Column(db.Text(), nullable=False)
account_id = db.Column(db.Integer, db.ForeignKey(
"accounts.id",
ondelete="CASCADE",
), nullable=False, index=True)
ingredient_amounts = db.relationship(
"RecipeIngredient", backref="recipe", lazy=True,
passive_deletes=True)
def __init__(self, name, description, steps):
self.name = name
self.description = description
self.steps = steps
def get_ingredients(self):
stmt = text("""
SELECT
ri.amount AS amount,
ri.amount_unit AS amount_unit,
ri.group_name AS group_name,
i.id AS id,
i.name AS name
FROM ingredients i, recipe_ingredient ri
WHERE
ri.recipe_id = :recipe_id
AND i.account_id = :account_id
AND ri.ingredient_id = i.id
ORDER BY ri.id
""").bindparams(recipe_id=self.id, account_id=self.account_id)
return db.session().execute(stmt)
def get_shopping_list_amounts(self):
stmt = text("""
SELECT
i.id AS id,
SUM(sli.amount) AS amount,
sli.amount_unit AS unit
FROM (
SELECT DISTINCT
i.id AS id,
i.name AS name
FROM ingredients i, recipe_ingredient ri
WHERE
i.account_id = :account_id
AND i.id = ri.ingredient_id
AND ri.recipe_id = :recipe_id
) i
LEFT JOIN shopping_list_items sli
ON sli.ingredient_id = i.id
WHERE sli.account_id = :account_id
GROUP BY i.id, sli.amount_unit
""").bindparams(recipe_id=self.id, account_id=self.account_id)
return db.session().execute(stmt)
def insert_ingredients_from_form(self, form):
ingredients = []
missing_ingredients = []
ingredients_by_name = {}
for recipe_ingredient_form in form.ingredient_amounts:
name = recipe_ingredient_form.data["name"].strip()
lower_name = name.lower()
existing_ingredient = ingredients_by_name.get(lower_name)
if not existing_ingredient:
existing_ingredient = Ingredient.query.filter(
Ingredient.account_id == self.account_id,
func.lower(Ingredient.name) == lower_name,
).first()
if not existing_ingredient:
existing_ingredient = Ingredient(name)
existing_ingredient.account_id = self.account_id
missing_ingredients.append(existing_ingredient)
ingredients_by_name[lower_name] = existing_ingredient
ingredients.append(existing_ingredient)
db.session().bulk_save_objects(missing_ingredients, return_defaults=True)
db.session().flush()
recipe_ingredients = []
for index, recipe_ingredient_form in enumerate(form.ingredient_amounts):
amount, unit = recipe_ingredient_form.parse_amount()
recipe_ingredients.append(RecipeIngredient(
amount=amount,
amount_unit=unit,
ingredient_id=ingredients[index].id,
recipe_id=self.id,
group_name=recipe_ingredient_form.group.data,
))
db.session().bulk_save_objects(recipe_ingredients)
db.session().flush()
class Recipe(db.Model):
""" Recipe Model for storing recipes """
__tablename__ = "recipe"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(255), nullable=False)
created_on = db.Column(db.DateTime, nullable=False)
cooktime = db.Column(db.String(255), nullable=False)
preptime = db.Column(db.String(255), nullable=False)
totaltime = db.Column(db.String(255), nullable=False)
username = db.Column(db.String(50),
db.ForeignKey('user.username'),
nullable=False)
remixes = db.relationship("Recipe",
backref=db.backref('parent', remote_side=[id]))
parent_id = db.Column(db.Integer, db.ForeignKey('recipe.id'))
public = db.Column(db.Boolean)
difficulty = db.Column(db.Integer, nullable=True)
servings = db.Column(db.String(255), nullable=False)
source = db.Column(db.String(255), nullable=False)
calories = db.Column(db.Integer, nullable=True)
cost = db.Column(db.Float, nullable=True)
description = db.Column(db.Text)
f_image = db.Column(db.Integer, nullable=True)
images = db.relationship('Image',
order_by='Image.id',
backref='recipe',
cascade='all,delete,delete-orphan')
ingredients = db.relationship('Ingredient',
order_by='Ingredient.number',
collection_class=ordering_list('number',
count_from=1),
backref='recipe',
lazy=True,
cascade='all,delete,delete-orphan')
steps = db.relationship('Step',
order_by='Step.number',
collection_class=ordering_list('number',
count_from=1),
backref='recipe',
lazy=True,
cascade='all,delete,delete-orphan')
@hybrid_property
def remix_count(self):
return len(self.remixes)
@remix_count.expression
def _remix_count_expression(cls):
q = db.select([db.func.count(Recipe.parent_id)]).\
where(Recipe.parent_id == cls.id).\
label("remix_count")
return q
@hybrid_property
def featured_image(self):
if self.f_image is not None:
return self.images[self.f_image]
return None
@hybrid_property
def community_images(self):
return (list(
filter(lambda img: img.username != self.username, self.images)) + [
r.featured_image
for r in self.remixes if r.featured_image is not None
])
@hybrid_property
def owner_images(self):
return list(
filter(lambda img: img.username == self.username, self.images))
@hybrid_property
def likes_count(self):
return len(self.likers)
#TODO make has_liked work when nested, as in viewing other user's recipes
@hybrid_method
def has_liked(self, username):
return db.session.query(likes).filter(
likes.c.username == username).filter(
likes.c.recipe_id == self.id).first() != None
@likes_count.expression
def _likes_count_expression(cls):
return (db.select([
db.func.count(likes.c.username).label("likes_count")
]).where(likes.c.recipe_id == cls.id).label("sum_likes"))
def __repr__(self):
return "<Recipe '{}'>".format(self.title)
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
email = db.Column(db.String(255), unique=True, nullable=False)
username = db.Column(db.String(50), unique=True)
password_hash = db.Column(db.String(100))
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
card_lists = db.relationship('CardListModel',
secondary=user_card_list,
backref=db.backref('users', lazy='dynamic'))
cards = db.relationship('CardModel', backref='user', lazy='dynamic')
comments = db.relationship('CommentModel', backref='user', lazy='dynamic')
@property
def password(self):
raise AttributeError('password: write-only field')
@password.setter
def password(self, password):
self.password_hash = flask_bcrypt.generate_password_hash(
password).decode('utf-8')
def check_password(self, password):
return flask_bcrypt.check_password_hash(self.password_hash, password)
def json(self):
return {'id': self.id, 'email': self.email, 'username': self.username}
@staticmethod
def encode_auth_token(user_id):
"""
Generates the Auth Token
:return: string
"""
try:
payload = {
'exp':
datetime.datetime.utcnow() +
datetime.timedelta(days=1, seconds=5),
'iat':
datetime.datetime.utcnow(),
'sub':
user_id
}
return jwt.encode(payload, key, algorithm='HS256')
except Exception as e:
return e
@staticmethod
def decode_auth_token(auth_token):
"""
Decodes the auth token
:param auth_token:
:return: integer|string
"""
try:
payload = jwt.decode(auth_token, key, algorithms='HS256')
is_blacklisted_token = BlacklistToken.check_blacklist(auth_token)
if is_blacklisted_token:
return 'Token blacklisted. Please log in again.'
else:
return payload['sub']
except jwt.ExpiredSignatureError:
return 'Signature expired. Please log in again.'
except jwt.InvalidTokenError:
return 'Invalid token. Please log in again.'
def __repr__(self):
return f"<User name:'{self.username}', id:'{self.id}', role: {self.role}>"
from app.main import db
import datetime
from sqlalchemy.sql import func
from app.main import db
import datetime
from sqlalchemy.sql import func
association_table = db.Table(
'ASSOCIATION_CC_TT', db.Model.metadata,
db.Column('tt_code', db.String(32), db.ForeignKey('CONTENT.id')),
db.Column('cc_code', db.String(32), db.ForeignKey('COMPANY.id')))
class Content(db.Model):
__tablename__ = "CONTENT"
id = db.Column(db.String(32), primary_key=True)
companies = db.relationship("Company",
secondary=association_table,
back_populates="contents")
class Company(db.Model):
__tablename__ = "COMPANY"
id = db.Column(db.String(32), primary_key=True)
name = db.Column(db.String(256), index=True, unique=True)
link = db.Column(db.String(256), index=True, unique=True)
contents = db.relationship("Content",
secondary=association_table,
back_populates="companies")
class sdLed(db.Model):
__tablename__ = "sd32_leds"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20), comment="Name")
display_name = db.Column(db.String(50), comment="Display name")
comment = db.Column(db.Text, comment="Comment")
create_time = db.Column(db.DateTime,
server_default=func.now(),
comment="Create time")
update_time = db.Column(db.DateTime,
server_default=func.now(),
comment="Update time")
cust_id = db.Column(db.Integer,
db.ForeignKey(sdCustomer.id),
comment="Customer id")
status = db.Column(
db.Integer,
server_default="0",
comment="Device status flag. bit 0: warning, bit 1: error")
model_id = db.Column(db.Integer,
db.ForeignKey(sdDeviceModel.id),
server_default="0",
comment="Traffic Light model/part no")
serial_no = db.Column(db.String(50), nullable=False, comment="Serial No")
device = db.relationship("sdDevice", uselist=False, backref="led")
__table_args__ = (db.UniqueConstraint("serial_no"),
db.UniqueConstraint("cust_id", "name"))
def __repr__(self):
return f"<sdLed id={self.id}/name={self.name}/display_name={self.display_name}/sn={self.serial_no}>"
@staticmethod
def add(cust_id, led):
model = db.session.query(sdDeviceModel) \
.filter(sdDeviceModel.name == led.get("model_name")).first()
obj = sdLed()
obj.cust_id = cust_id
obj.name = led.get("name")
obj.display_name = led.get("display_name")
obj.comment = led.get("comment")
obj.model = model
obj.serial_no = led.get("serial_no")
return obj
@staticmethod
def update(cust_id, led):
model = db.session.query(sdDeviceModel) \
.filter(sdDeviceModel.name == led.get("model_name")).first()
sdn = led["serial_no"]
obj = db.session.query(sdLed).filter(sdLed.serial_no == sdn).first()
if obj:
obj.cust_id = cust_id
obj.name = led.get("name")
obj.display_name = led.get("display_name")
obj.comment = led.get("comment")
obj.model = model
obj.serial_no = led.get("serial_no")
else:
obj = sdLed.add(cust_id, led)
return obj
class sdCustomer(db.Model):
__tablename__ = "sd10_customers"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(20),
unique=True,
nullable=False,
index=True,
comment="Name")
display_name = db.Column(db.String(50),
nullable=False,
comment="Display name")
comment = db.Column(db.Text, comment="Comment")
status = db.Column(db.Integer,
nullable=False,
server_default="0",
comment="Status")
create_time = db.Column(db.DateTime,
nullable=False,
server_default=func.now(),
comment="Create time")
update_time = db.Column(db.DateTime,
nullable=False,
server_default=func.now(),
comment="Update time")
cust_id = db.Column(db.Integer,
db.ForeignKey("sd10_customers.id"),
comment="Vendor of customer")
vendors = db.relationship("sdCustomer", lazy="dynamic")
users = db.relationship("sdUser", backref="cust", lazy="dynamic")
controllers = db.relationship("sdController",
backref="cust",
lazy="dynamic")
leds = db.relationship("sdLed", backref="cust", lazy="dynamic")
device_groups = db.relationship("sdDeviceGroup",
backref="cust",
lazy="dynamic")
schedules = db.relationship("sdSchedule", backref="cust", lazy="dynamic")
codes = db.relationship("sdCode", backref="cust", lazy="dynamic")
user_groups = db.relationship("sdUserGroup",
backref="cust",
lazy="dynamic")
status_privilege = db.relationship("sdStatusPrivilege",
backref="cust",
lazy="dynamic")
device_info = db.relationship("sdDeviceInfo",
backref="cust",
lazy="dynamic")
cust_devices = db.relationship("sdDevice",
foreign_keys="sdDevice.cust_id",
backref="cust",
lazy="dynamic")
vendor_devices = db.relationship("sdDevice",
foreign_keys="sdDevice.vendor_id",
backref="vendor",
lazy="dynamic")
def __repr__(self):
return f"<sdCustomer id={self.id}/name={self.name}/display_name={self.display_name}>"
@staticmethod
def search(cust_id):
customer = db.session.query(
sdCustomer.id).filter(sdCustomer.id == cust_id).first()
return customer
@staticmethod
def search_by_name(cust_name):
customer = sdCustomer.query.filter_by(name=cust_name).first()
return customer
@staticmethod
def search_cust(cust_id):
customer = db.session.query(sdCustomer).filter(
sdCustomer.id == cust_id, sdCustomer.cust_id.is_(None)).all()
return customer
@staticmethod
def get_vendors(cust_id):
vendors = db.session.query(sdCustomer).filter(
sdCustomer.cust_id == cust_id).all()
return vendors
@staticmethod
def search_cust_obj(cust_id):
customer = db.session.query(sdCustomer).filter(
sdCustomer.id == cust_id).first()
return customer
"""
import datetime
from flask_bcrypt import check_password_hash, generate_password_hash
from flask_login import UserMixin
from sqlalchemy.sql import and_, select
from app.main import db, login_manager
from app.main.models.enums import PriorityType
from app.main.models.payments import Payment
from app.main.models.posts import Post
from app.main.models.tags import Tag
userTagJunction = db.Table('userTagJunction',
db.Column('user_id', db.Integer,
db.ForeignKey('user.id'), primary_key=True),
db.Column('keyword_id', db.Integer, db.ForeignKey('tag.id'),
primary_key=True),
db.Column('priority', db.Enum(PriorityType),
default=PriorityType.follow)
)
userPostInteraction = db.Table('userPostInteraction',
db.Column('user_id', db.Integer,
db.ForeignKey('user.id'), primary_key=True),
db.Column('post_id', db.Integer,
db.ForeignKey('post.post_id'), primary_key=True),
db.Column('rating', db.Integer, default=0),
db.Column('save', db.Boolean, default=True)
)
from app.main import db
user_follows_course = db.Table(
'user_follows_course',
db.Column('user_id',
db.String(255),
db.ForeignKey('user.id'),
primary_key=True),
db.Column('course_id',
db.Integer,
db.ForeignKey('course.id'),
primary_key=True),
)
lazy='dynamic',
cascade='all, delete-orphan'))
def get_recipients(self, round_org_id):
receipt = self.doc_receipts.filter_by(round_org_id=round_org_id,
doc_id=self.id).first()
if receipt:
return receipt.members
else:
return []
receipt_receivers = db.Table(
'doc_receipt_receivers_assoc',
db.Column('receipt_id', db.Integer,
db.ForeignKey('doc_receive_records.id')),
db.Column('personal_info_id', db.Integer,
db.ForeignKey('staff_personal_info.id')))
class DocReceiveRecord(db.Model):
__tablename__ = 'doc_receive_records'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
predefined_comment = db.Column(
db.String(255),
info={
'label':
'Predefined Comment',
'choices':
[(c, c) for c in
[u'แจ้งเพื่อทราบ', u'แจ้งเพื่อพิจารณา', u'ขอความร่วมมือเข้าร่วม']]
class OrderProduct(db.Model):
#creating relationship table with order table and product table
__tablename__ = "order_product"
id = db.Column(db.Integer, primary_key=True)
order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))
product_id = db.Column(db.Integer, db.ForeignKey('products.product_id'))
"""DB Model for imgLink table"""
import datetime
import os
from logging import getLogger
from flask import current_app
from app.main import db
LOG = getLogger(__name__)
imgPostJunction = db.Table(
'imgPostJunction',
db.Column('img_id',
db.Integer,
db.ForeignKey('imgLink.id'),
primary_key=True),
db.Column('post_id', db.Integer, db.ForeignKey('post.post_id')))
class ImgLink(db.Model):
"""
Description of ImgLink Model
Rows
-----------
:id: int [pk]
:link: varchar (url)
"""
__tablename__ = 'imgLink'
id = db.Column(db.Integer, primary_key=True)
class Resource(TimestampMixin, db.Model):
__tablename__ = 'resources'
id = db.Column(db.BigInteger, primary_key=True)
category_id = db.Column(db.BigInteger,
db.ForeignKey('categories.id',
onupdate='CASCADE',
ondelete='RESTRICT'),
nullable=False)
category = db.relationship('Category', backref='resources')
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
user = db.relationship('User', backref='resources')
description = db.Column(db.UnicodeText(), nullable=True)
type = db.Column(sau.ChoiceType(USER_RESOURCE_TYPES), index=True)
quantity_available = db.Column(db.BigInteger)
quantity_needed = db.Column(db.BigInteger)
fulfilled = db.Column(db.Boolean,
nullable=False,
default=False,
server_default='0')
name = db.Column(db.UnicodeText(), nullable=False)
picture = db.Column(db.UnicodeText())
requested = db.Column(db.Boolean(), nullable=True, default=False)
def __setattr__(self, name, value):
if name == 'picture':
if value:
filename = '{}.{}'.format(self.id,
value.filename.split('.')[-1])
super().__setattr__(
'picture', upload_file(value, 'resource_pictures',
filename))
else:
super().__setattr__(name, value)
@property
def picture_url(self):
if not self.picture:
return None
return file_url(self.picture)
@property
def quantity_remaining(self):
if self.type != 'HAVE':
return None
fulfillment = ResourceFulfillment.query.filter(
ResourceFulfillment.fulfilling_resource == self,
ResourceFulfillment.confirmed_by_recipient == True,
)
return self.quantity_available - sum(
[f.fulfilled_quantity for f in fulfillment])
@property
def quantity_fulfilled(self):
if self.type != 'NEED':
return None
fulfillment = ResourceFulfillment.query.filter(
ResourceFulfillment.fulfilled_resource == self,
ResourceFulfillment.confirmed_by_recipient == True,
)
return self.quantity_needed - sum(
[f.fulfilled_quantity for f in fulfillment])
def serialize(self):
return {
'id': self.id,
'name': self.name,
'category': self.category.name,
'user_id': self.user.id,
'type': self.type.code,
'quantity_available': self.quantity_available,
'quantity_needed': self.quantity_needed,
'fulfilled': self.fulfilled,
}
