class community_info(db.Model): id = db.Column(db.INT, primary_key=True) community_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) classification = db.Column(db.INT, nullable=False) population = db.Column(db.INT, nullable=False) economic = db.Column(db.VARCHAR(50), nullable=False) problem = db.Column(db.VARCHAR(50), nullable=False) need = db.Column(db.VARCHAR(50), nullable=False) def add(value): record = community_info(id=value[0], community_id=value[1], classification=value[2], population=value[3], economic=value[4], problem=value[5], need=value[6]) db.session.add(record) db.session.commit() def show(value): record = community_info.query.filter( community_info.id == value).first() return record
class Cluster(db.Model): __tablename__ = 'cluster' id = db.Column(db.VARCHAR(32), default=generate_uuid, primary_key=True) org_id = db.Column(db.ForeignKey('organization.id', ondelete='CASCADE'), comment='组织id') user_id = db.Column(db.ForeignKey('user.id'), comment='添加cluster的用户id') name = db.Column(db.VARCHAR(64), comment='集群名称') host = db.Column(db.VARCHAR(64), comment='集群主机ip') port = db.Column(db.SmallInteger(), comment='集群端口(6443..)') token = db.Column(db.Text(), comment='远程连接授权的token') create_time = db.Column(db.BigInteger(), default=get_timestamp, comment='用户添加集群的时间')
class community_member(db.Model): id = db.Column(db.INT, primary_key=True) member_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) community_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) occupation = db.Column(db.VARCHAR(30), nullable=True) income = db.Column(db.NUMERIC(10, 2), nullable=False) religion = db.Column(db.VARCHAR(20), nullable=False) status = db.Column(db.CHAR(1), nullable=False) def add(value): record = community_member(id=value[0], member_id=value[1], community_id=value[2], occupation=value[3], income=value[4], religion=value[5], status=value[6]) db.session.add(record) db.session.commit() def retrieve_member(value): record = community_member.query.filter( community_member.member_id == value).first() return record def update_status(id, status): user = community_member.query.filter(community_member.id == id).first() user.status = status db.session.commit() def member_count(): record = community_member.query.add_columns( community_member.community_id, func.COUNT(community_member.member_id).label('count')).group_by( community_member.community_id).all() return record
class event_attachment(db.Model): id = db.Column(db.INT, primary_key=True) event_id = db.Column(db.INT, db.ForeignKey('event_information.id'), nullable=False) path = db.Column(db.VARCHAR(200), nullable=False) type = db.Column(db.INT, nullable=False) def add(value): record = event_attachment(id=value[0], event_id=value[1], path=value[2], type=value[3]) db.session.add(record) db.session.commit() def retrieve_files(value): record = event_attachment.query.filter( event_attachment.event_id == value).all() return record def letter_attached(): record = event_attachment.query.filter( event_attachment.type == 3).all() return record
class user_photo(db.Model): id = db.Column(db.INT, primary_key=True) user_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable='False') path = db.Column(db.VARCHAR(200), nullable=False) def add(value): record = user_photo(id=value[0], user_id=value[1], path=value[2]) db.session.add(record) db.session.commit() def update(value): record = user_photo.query.filter_by(user_id=value[0]).first() record.path = value[1] db.session.commit() def photo(value): record = user_photo.query.filter_by(user_id=value).first() return record
class Topic(db.Model): __tablename__ = 't_topics' id = db.Column(db.Integer(), primary_key=True, unique=True, nullable=False, index=True, autoincrement=True) title = db.Column(db.VARCHAR(50), nullable=False, index=True, primary_key=True) contents = db.Column(db.VARCHAR(500), nullable=False, index=True, primary_key=True) japanese_level = db.Column(db.VARCHAR(50), nullable=False) published_date = db.Column(db.DateTime(), nullable=False, server_default=db.func.now()) author_id = db.Column(db.Integer(), db.ForeignKey("m_user.id"), nullable=True) answers = db.relationship("Answer", backref='t_topics') votes = db.relationship("Votes", backref="t_topics") @classmethod def get_all_topics(cls): return cls.query.all() def save(self): db.session.add(self) db.session.commit() @classmethod def get_by_topic_id(cls, topic_id): return cls.query.filter_by(id=topic_id).first() @classmethod def get_all_topics(cls): return cls.query.all()
class event_photo(db.Model): id = db.Column(db.INT, primary_key=True) event_id = db.Column(db.INT, db.ForeignKey('event_information.id')) photo = db.Column(db.VARCHAR(200), nullable=False) description = db.Column(db.VARCHAR(140)) is_used = db.Column(db.CHAR(1), nullable=False) def add(value): record = event_photo(id=value[0], event_id=value[1], photo=value[2], description=value[3], is_used=value[4]) db.session.add(record) db.session.commit() def show(value): record = event_photo.query.filter( and_(event_photo.event_id == value, event_photo.is_used == 'Y')).order_by( event_photo.id.desc()).all() return record def caption(value): record = event_photo.query.filter(event_photo.id == value[0]).first() record.description = value[1] db.session.commit() def delete(value): record = event_photo.query.filter(event_photo.id == value).first() record.is_used = 'N' db.session.commit()
class User(db.Model): __tablename__ = "m_user" id = db.Column(db.Integer(), primary_key=True, unique=True, nullable=False, index=True, autoincrement=True) # ID username = db.Column(db.VARCHAR(255), nullable=False, unique=True, index=True) # ユーザー名 password = db.Column(db.VARCHAR(200), nullable=False) # パスワード first_name = db.Column(db.VARCHAR(255), nullable=False) last_name = db.Column(db.VARCHAR(255), nullable=False) created = db.Column(db.DateTime(), nullable=False, server_default=db.func.now()) # 登録した日時 modified = db.Column(db.DateTime(), nullable=False, server_default=db.func.now(), onupdate=db.func.now()) # 更新した日時 is_admin = db.Column(db.BOOLEAN(), nullable=True, default=False) topics = db.relationship("Topic", backref="m_user") answers = db.relationship("Answer", backref="m_answer") votes = db.relationship("Votes", backref="m_user") # your_leader_name = db.Column(db.VARCHAR(255), nullable=False, default=None) # your_first_student_name = db.Column(db.VARCHAR(255), nullable=False, default=None) # your_second_student_name =db.Column(db.VARCHAR(255), nullable=False, default=None) # your_third_student_name =db.Column(db.VARCHAR(255), nullable=False, default=None) # rank = db.Column(db.Integer(),default=None, nullable=False) # points= db.Column(db.VARCHAR(50), default=0, nullable=True) @classmethod def get_by_username(cls, username): return cls.query.filter_by(username=username).first() def save(self): db.session.add(self) db.session.commit()
class referral(db.Model): id = db.Column(db.INT, primary_key=True) referrer_id = db.Column(db.INT) name = db.Column(db.VARCHAR(50), nullable=False) email_address = db.Column(db.VARCHAR(30), nullable=False) type = db.Column(db.INT, nullable=False) status = db.Column(db.CHAR(1), nullable=False) def add(value): record = referral(id=value[0], referrer_id=value[1], name=value[2], email_address=value[3], type=value[4], status=value[5]) db.session.add(record) db.session.commit()
class Alarm(BaseModel): __tablename__ = 'alarm' aid = db.Column(db.Integer, primary_key=True) name = db.Column(db.VARCHAR(15), nullable=False) description = db.Column(db.VARCHAR(1024), nullable=True) phones = relationship("PhoneAlarm", back_populates="alarm") def __init__(self, name): self.name = name def serialize(self): return { "aid": self.aid, "name": self.name, "description": self.description if self.description is not None else None }
class FanTask(BaseModel, db.Model): """文章列表""" # __tablename__ = "fan_task" task_id = db.Column(db.Integer, primary_key=True) uuid = db.Column(db.String(64), unique=True) t_author = db.Column(db.String(256)) # 原文作者 t_author_id = db.Column(db.String(64)) # 作者id t_author_img = db.Column(db.String(1024)) # 作者头像链接 t_title = db.Column(db.String(256), nullable=False) # 不允许为空 t_desc = db.Column(db.String(512)) # 简介 不允许为空 t_image = db.Column(db.String(1024)) # 封面图 t_url = db.Column(db.String(1024)) # 原文链接 先去掉 t_cate = db.Column(db.String(8)) # 添加分类id 和 分类表关联 total_click_count = db.Column(db.Integer) # 总点击数 current_click_count = db.Column(db.Integer) # 当前点击数每天的点击量 status = db.Column(db.SmallInteger, default="0") # 任务状态(0关闭1开启2待审核-1已删除) memo = db.Column(db.VARCHAR(128)) # 备注 t_key = db.Column(db.VARCHAR(164), unique=True) # 唯一key 抓取文章源key t_read_count = db.Column(db.Integer) # 文章阅读数 t_time = db.Column(db.String(64), default=int(time.time() * 1000)) # 文章发布时间 p_time = db.Column(db.String(64), default=int(time.time() * 1000)) # 推送时间 article_type = db.Column(db.Integer, nullable=False) # 1:普通类型 2:视频类型 is_get = db.Column(db.Boolean, default=False) # 是否已推送 is_hot = db.Column(db.Boolean, default=False) # 是否是热点 video_second = db.Column(db.SmallInteger) # 播放时长 毫秒 video_url = db.Column(db.VARCHAR(255)) # 视频地址 tags = db.Column(db.String(1024)) # 文章标签 task_platform = db.Column(db.SmallInteger) # 文章来源 grad_read_count = db.Column(db.SmallInteger) # 抓取阅读数 grad_forward_count = db.Column(db.SmallInteger) # 抓取转发数 grad_comments_count = db.Column(db.SmallInteger) # 抓取评论数 def __init__(self, **kwargs): super(FanTask, self).__init__(**kwargs) def __repr__(self): return f"<FanTask: {self.t_title}>"
class proposal_tracker(db.Model): id = db.Column(db.INT, primary_key=True) event_id = db.Column(db.INT, db.ForeignKey('event_information.id'), nullable=False) proposed_on = db.Column(db.DATETIME, nullable=False) recop_accepted = db.Column(db.DATETIME) acad_signed = db.Column(db.DATETIME) fmi_signed = db.Column(db.DATETIME) approved_on = db.Column(db.DATETIME) comment = db.Column(db.VARCHAR(20)) status = db.Column(db.CHAR(1), nullable=False) def add(value): record = proposal_tracker(id=value[0], event_id=value[1], proposed_on=datetime.now(), recop_accepted=None, acad_signed=None, fmi_signed=None, approved_on=None, comment=None, status=value[2]) db.session.add(record) db.session.commit() def update_status(id, status): proposal = proposal_tracker.query.filter( proposal_tracker.event_id == id).first() proposal.status = status if status == 'A': proposal.recop_accepted = datetime.now() elif status == 'F': proposal.acad_signed = datetime.now() elif status == 'P': proposal.fmi_signed = datetime.now() elif status == 'S': proposal.approved_on = datetime.now() else: proposal.comment = 'Declined' db.session.commit()
class audit_trail(db.Model): id = db.Column(db.INT, primary_key=True) user_id = db.Column(db.INT, db.ForeignKey('user_account.id'), nullable=False) affected_id = db.Column(db.INT, nullable=False) target = db.Column(db.VARCHAR(25), nullable=False) date_created = db.Column(db.DATETIME, nullable=False) type = db.Column(db.INT, nullable=False) def add(value): record = audit_trail(id=value[0], user_id=value[1], affected_id=value[2], target=value[3], date_created=datetime.now(), type=value[4]) db.session.add(record) db.session.commit()
class inventory_type(db.Model): id = db.Column(db.INT, primary_key=True) name = db.Column(db.VARCHAR(20), nullable=False) status = db.Column(db.CHAR(1), nullable=False) inventory_type_id = db.relationship('inventory', backref='inventory_type', lazy=True) def add(value): record = inventory_type(id=value[0], name=value[1], status=value[2]) db.session.add(record) db.session.commit() def show_list(): record = inventory_type.query.filter( inventory_type.status == 'A').order_by( inventory_type.name.asc()).all() return record def last_added(): record = inventory_type.query.count() return record def duplicate(value): record = inventory_type.query.filter( inventory_type.name.like('%' + value + '%')).first() return record
class Moving(BaseModel): __tablename__ = 'moving' pid = db.Column(db.CHAR(36), db.ForeignKey(Phone.pid), primary_key=True) ip = db.Column(db.VARCHAR(36), nullable=False) type = db.Column(db.Enum(Types), nullable=False) utc = db.Column(db.VARCHAR(6), nullable=False) lat = db.Column(db.DECIMAL(9, 6), nullable=True) lon = db.Column(db.DECIMAL(9, 6), nullable=True) alt = db.Column(db.DECIMAL(9, 3), nullable=True) timestamp = db.Column(db.DateTime(), nullable=True) phone = relationship("Phone", back_populates="moving") def __init__(self, pid, ip, _type, utc, lat, lon, alt, timestamp): self.pid = pid self.ip = ip self.type = _type self.utc = utc self.lat = lat self.lon = lon self.alt = alt self.timestamp = timestamp def serialize(self): return {"pid": self.pid, "ip": self.ip, "type": self.type.name, "utc": self.utc, "lat": float(self.lat) if self.lat is not None else None, "lon": float(self.lon) if self.lon is not None else None, "alt": float(self.alt) if self.lon is not None else None, "timestamp": re.sub('[-:+]', '', self.timestamp.isoformat()) + 'Z' if self.timestamp is not None else None} @validates('lat') def validate_lat(self, key, value): if value is None: return value if value < -90 or value > 90: raise ValueError('Latitud invalida') return value @validates('lon') def validate_lon(self, key, value): if value is None: return value if value < -180 or value > 180: raise ValueError('Longitud invalida') return value @validates('pid') def validate_pid(self, key, value): check = string.hexdigits + '-' split: str = value.split('-') if len(split) != 5: raise ValueError("Invalid PID") if len(split[0]) != 8 or len(split[1]) != 4 or len(split[2]) != 4 or len(split[3]) != 4 or len(split[4]) != 12: raise ValueError("Invalid PID") for letter in value: if letter not in check: raise ValueError("Invalid PID") print("Llega a return value") return value
class user_information(db.Model): id = db.Column(db.INT, primary_key=True) first_name = db.Column(db.VARCHAR(30), nullable=False) middle_name = db.Column(db.VARCHAR(20), nullable=False) last_name = db.Column(db.VARCHAR(20), nullable=False) company_name = db.Column(db.VARCHAR(50), nullable=False) bio = db.Column(db.VARCHAR(160), nullable=True) gender = db.Column(db.CHAR(1), nullable=False) birthday = db.Column(db.DATE, nullable=False) address = db.Column(db.VARCHAR(50), nullable=False) telephone = db.Column(db.VARCHAR(15)) mobile_number = db.Column(db.VARCHAR(15)) partner_thrust = db.Column(db.INT, nullable=False) account_info_id = db.relationship('user_account', backref='user_information', lazy=True) sponsee_info_id = db.relationship('donation', foreign_keys=[donation.sponsee_id], backref='user_information_sponsee', lazy=True) sponsor_info_id = db.relationship('donation', foreign_keys=[donation.sponsor_id], backref='user_information_sponsor', lazy=True) comm_id = db.relationship('community_info', foreign_keys=[community_info.community_id], backref='user_information_community', lazy=True) comm_info_id = db.relationship( 'community_member', foreign_keys=[community_member.community_id], backref='user_information_community', lazy=True) mem_info_id = db.relationship('community_member', foreign_keys=[community_member.member_id], backref='user_information_member', lazy=True) organizer_info_id = db.relationship('event_information', backref='user_information', lazy=True) photo_info_id = db.relationship('user_photo', backref='user_information', lazy=True) def add(value): record = user_information(id=value[0], first_name=value[1], middle_name=value[2], last_name=value[3], company_name=value[4], bio=value[5], gender=value[6], birthday=value[7], address=value[8], telephone=value[9], mobile_number=value[10], partner_thrust=value[11]) db.session.add(record) db.session.commit() def reserve_id(): record = user_information.query.count() return record def linkage_info(value): record = user_information.query.filter( user_information.id == value).first() return record def profile_info_update(value): record = user_information.query.filter_by(id=value).first() return record def retrieve_user(value): record = user_information.query.filter( user_information.id == value).first() return record def thrusts(): record = user_information.query.add_columns( user_information.thrust, func.COUNT(user_information.thrust).label('count')).group_by( user_information.thrust).filter( user_information.thrust != 0).all() return record
class donation(db.Model): id = db.Column(db.INT, primary_key=True) sponsee_id = db.Column(db.INT, db.ForeignKey('user_information.id')) event_id = db.Column(db.INT, db.ForeignKey('event_information.id')) sponsor_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) amount = db.Column(db.NUMERIC(10, 2), nullable=False) date_given = db.Column(db.DATETIME, nullable=False) transaction_slip = db.Column(db.VARCHAR(200), nullable=False) status = db.Column(db.CHAR(1), nullable=False) inventory_donation_id = db.relationship('inventory', backref='donation', lazy=True) def add(value): record = donation(id=value[0], sponsee_id=value[1], event_id=value[2], sponsor_id=value[3], amount=value[4], date_given=datetime.now(), transaction_slip=value[5], status=value[6]) db.session.add(record) db.session.commit() def last_added(): record = donation.query.count() record += 1 return record def retrieve_donation(id): record = donation.query.filter_by(id=id).first() return record def update_status(value): record = donation.query.filter_by(id=value[0]).first() record.status = value[1] db.session.commit() def d_status(): record = donation.query.add_columns( donation.status, func.COUNT(donation.id).label('count')).group_by( donation.status).all() return record
class user_account(db.Model, UserMixin): id = db.Column(db.INT, primary_key=True) info_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) username = db.Column(db.VARCHAR(20), nullable=False) password = db.Column(db.VARCHAR(20), nullable=False) email_address = db.Column(db.VARCHAR(30), nullable=False) type = db.Column(db.INT, nullable=False) last_active = db.Column(db.DATETIME, nullable=False) status = db.Column(db.CHAR(1), nullable=False) audit_account_id = db.relationship('audit_trail', backref='user_account', lazy=True) def add(value): record = user_account(id=value[0], info_id=value[1], username=value[2], password=bcrypt.generate_password_hash( value[3]).decode('utf-8'), email_address=value[4], type=value[5], last_active=value[6], status=value[7]) db.session.add(record) db.session.commit() def retrieve_user(value): record = user_account.query.filter( user_account.info_id == value).first() return record def update_status(id, status): user = user_account.query.filter(user_account.id == id).first() user.status = status db.session.commit() def login(value): user = user_account.query.filter( user_account.username == value[0]).first() if user: password = bcrypt.check_password_hash( user.password.encode('utf-8'), value[1].encode('utf-8')) if password == False: user = None return user def logout(): user = user_account.query.filter( user_account.id == current_user.id).first() user.last_active = datetime.now() db.session.commit() def profile_acc_update(value): record = user_account.query.filter_by(id=value).first() return record def user_type(): record = user_account.query.add_columns( user_account.type, func.COUNT(user_account.id).label('count')).group_by( user_account.type).filter( and_(user_account.type != 1, user_account.type != 5)).all() return record
class event_information(db.Model): id = db.Column(db.INT, primary_key=True) organizer_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) name = db.Column(db.VARCHAR(30), nullable=False) description = db.Column(db.VARCHAR(140), nullable=False) objective = db.Column(db.VARCHAR(140), nullable=False) budget = db.Column(db.NUMERIC(10, 2), nullable=False) location = db.Column(db.VARCHAR(50), nullable=False) event_date = db.Column(db.DATETIME, nullable=False) participant_no = db.Column(db.INT, nullable=False) min_age = db.Column(db.INT, nullable=False) max_age = db.Column(db.INT, nullable=False) thrust = db.Column(db.INT, nullable=False) type = db.Column(db.INT, nullable=False) event_status = db.Column(db.CHAR(1), nullable=False) event_info_id = db.relationship('proposal_tracker', backref='event_information', lazy=True) event_part_id = db.relationship('event_participation', backref='event_information', lazy=True) event_att_id = db.relationship('event_attachment', backref='event_information', lazy=True) event_donate_id = db.relationship('donation', backref='event_information', lazy=True) event_photo_id = db.relationship('event_photo', backref='event_information', lazy=True) def add(value): record = event_information(id=value[0], organizer_id=value[1], name=value[2], description=value[3], objective=value[4], budget=value[5], location=value[6], event_date=value[7], participant_no=value[8], min_age=value[9], max_age=value[10], thrust=value[11], type=value[12], event_status=value[13]) db.session.add(record) db.session.commit() def last_added(value): record = event_information.query.filter( event_information.organizer_id == value).order_by( event_information.id.desc()).first() return record def retrieve_event(value): record = event_information.query.filter( event_information.id == value).first() return record def select_list(): record = event_information.query.filter( event_information.event_status == 'S').all() return record def calendar(): record = event_information.query.filter( or_(event_information.event_status == 'S', event_information.event_status == 'F')).all() return record def update_status(id, status): event = event_information.query.filter( event_information.id == id).first() event.event_status = status db.session.commit() def reschedule(value): record = event_information.query.filter_by(id=value).first() return record def thrusts(): record = event_information.query.add_columns( event_information.thrust, func.COUNT(event_information.thrust).label('count')).group_by( event_information.thrust).all() return record def status(): record = event_information.query.add_columns( event_information.status, func.COUNT(event_information.id).label('count')).group_by( event_information.status).all() return record
class event_participation(db.Model): id = db.Column(db.INT, primary_key=True) event_id = db.Column(db.INT, db.ForeignKey('event_information.id'), nullable=False) participant_id = db.Column(db.INT, db.ForeignKey('user_information.id'), nullable=False) rating = db.Column(db.INT, nullable=True) comment = db.Column(db.VARCHAR(140), nullable=False) is_target = db.Column(db.CHAR(1), nullable=False) status = db.Column(db.CHAR(1), nullable=False) def add(value): record = event_participation(id=value[0], event_id=value[1], participant_id=value[2], rating=None, comment=None, is_target=value[3], status='J') db.session.add(record) db.session.commit() def show_status(value): record = event_participation.query.filter( and_(event_participation.event_id == value[0], event_participation.participant_id == value[1])).first() return record def user_joined(value): record = event_participation.query.filter( and_(event_participation.participant_id == value, event_participation.status == 'J')).all() return record def show_joined(value): record = event_participation.query.filter( and_(event_participation.event_id == value, event_participation.is_target != 'Y', event_participation.status != 'R')).count() return record def update(value): record = event_participation.query.filter( event_participation.event_id == value[0], event_participation.participant_id == value[1]).first() record.status = value[2] db.session.commit() def evaluate(value): record = event_participation.query.filter( event_participation.event_id == value[0], event_participation.participant_id == value[1]).first() record.rating = value[2] record.comment = value[3] db.session.commit() def ratings(value): record = event_participation.query.add_columns( event_participation.rating, func.COUNT(event_participation.rating).label('count')).group_by( event_participation.rating).filter( event_participation.event_id == value).all() return record def average_rating(value): record = event_participation.query.add_columns( func.AVG(event_participation.rating).label('average')).filter( event_participation.event_id == value).first() return record