class UserRoles(BaseModel):
"""holds user roles pairs"""
__tablename__ = "user_roles"
user_id = Column(INTEGER,
nullable=False,
comment="user's table foreign key")
role_id = Column(INTEGER,
nullable=False,
comment="role's table foreign key")
__table_args__ = (
ForeignKeyConstraint(
columns=["user_id"],
refcolumns=["users.id"],
ondelete="CASCADE",
),
ForeignKeyConstraint(
columns=["role_id"],
refcolumns=["roles.id"],
ondelete="CASCADE",
),
)
def __init__(self,
role: "Role",
user: "******" = None,
user_id: int = None) -> None:
id_ = user.id if user is not None else user_id
assert id_ is not None
self.user_id = id_
self.role_id = role.id
class ReportTableInstance(JusticeCountsBase):
"""An instance of a table that contains an actual set of data points along a shared set of dimensions.
It typically maps to a literal table with columns and rows in a report, but in some cases a literal table in the
report may map to multiple tables as defined here.
"""
__tablename__ = "report_table_instance"
id = Column(Integer, autoincrement=True)
report_id = Column(Integer, nullable=False)
report_table_definition_id = Column(Integer, nullable=False)
# The window of time that values in this table cover, represented by a start date (inclusive) and an end date
# (exclusive). The data could represent an instant measurement, where the start and end are equal, or a window, e.g.
# ADP over the last month.
time_window_start = Column(Date, nullable=False)
time_window_end = Column(Date, nullable=False)
# This field can be used to store any text that the source provides describing the methodology used to calculate
# the data. This is stored on instances so that if it changes from report to report, we don't overwrite methodology
# for prior instances.
methodology = Column(String)
__table_args__ = tuple(
[
PrimaryKeyConstraint(id),
# TODO(#4476): We need to include time window as part of the unique
# constraint in case there is data for the same table definition that
# represents multiple time windows within a single report. To make this
# work with updates, I think we will re-ingest all table instances for
# a particular report table definition in an updated report.
UniqueConstraint(
report_id,
report_table_definition_id,
time_window_start,
time_window_end,
),
ForeignKeyConstraint([report_id], [Report.id], ondelete="CASCADE"),
ForeignKeyConstraint(
[report_table_definition_id], [ReportTableDefinition.id]
),
]
)
report = relationship(Report, back_populates="report_table_instances")
report_table_definition = relationship(ReportTableDefinition)
cells = relationship(
"Cell",
back_populates="report_table_instance",
lazy="selectin",
cascade="all, delete",
passive_deletes=True,
)
def drop_all(session):
inspector = reflection.Inspector.from_engine(session.bind)
# gather all data first before dropping anything.
# some DBs lock after things have been dropped in
# a transaction.
metadata = MetaData()
tbs = []
all_fks = []
for table_name in inspector.get_table_names():
fks = []
for fk in inspector.get_foreign_keys(table_name):
if not fk['name']:
continue
fks.append(ForeignKeyConstraint((), (), name=fk['name']))
t = Table(table_name, metadata, *fks)
tbs.append(t)
all_fks.extend(fks)
for fkc in all_fks:
session.execute(DropConstraint(fkc))
for table in tbs:
session.execute(DropTable(table))
session.commit()
class Report(JusticeCountsBase):
"""A document that is published by a source that contains data pertaining to the Justice Counts Framework.
"""
__tablename__ = 'report'
id = Column(Integer, autoincrement=True)
# The source that this report is published by.
source_id = Column(Integer, nullable=False)
# This distinguishes between the many types of reports that a single source may produce, e.g. a Daily Status
# Report or Monthly Fact Sheet, that contain different information and are potentially fetched and parsed using
# different logic.
type = Column(String(255), nullable=False)
# Identifies a specific instance of a given report type. It should be constructed such that it is unique within a
# given report type and source. The combination of report type and instance is used when ingesting a report to
# determine whether this is an update to an existing report or a new report. For PDF reports, this may simply be
# the title of the document after some validation has been performed. For webpages it may need to be dynamically
# generated.
instance = Column(String(255), nullable=False)
# The date the report was published.
publish_date = Column(Date, nullable=False)
# The method used to acquire the data (e.g. scraped).
acquisition_method = Column(Enum(AcquisitionMethod), nullable=False)
# TODO(#4485): Add a list of projects (e.g. Justice Counts, Spark) for which this data was ingested.
__table_args__ = tuple([
PrimaryKeyConstraint(id),
UniqueConstraint(source_id, type, instance),
ForeignKeyConstraint([source_id], [Source.id])])
source = relationship(Source)
class SearchWords(Base):
__tablename__ = "session_search_words"
session_id = Column("session_id", Integer)
word_id = Column("word_id", Integer)
word_temp_pk = PrimaryKeyConstraint(session_id, word_id)
word_srch_word_id_words_id_foreign = ForeignKeyConstraint(
columns=[session_id], refcolumns=[Session.id])
class FOIRequestApplicantMapping(db.Model):
# Name of the table in our database
__tablename__ = 'FOIRequestApplicantMappings'
__table_args__ = (
ForeignKeyConstraint(
["foirequest_id", "foirequestversion_id"], ["FOIRequests.foirequestid", "FOIRequests.version"]
),
)
# Defining the columns
foirequestapplicantmappingid = db.Column(db.Integer, primary_key=True,autoincrement=True)
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, nullable=True)
createdby = db.Column(db.String(120), unique=False, nullable=True)
updatedby = db.Column(db.String(120), unique=False, nullable=True)
#ForeignKey References
requestortypeid = db.Column(db.Integer,ForeignKey('RequestorTypes.requestortypeid'))
requestortype = relationship("RequestorType",backref=backref("RequestorTypes"),uselist=False)
foirequestapplicantid = db.Column(db.Integer,ForeignKey('FOIRequestApplicants.foirequestapplicantid'))
foirequestapplicant = relationship("FOIRequestApplicant",backref=backref("FOIRequestApplicants"),uselist=False)
foirequest_id =db.Column(db.Integer, db.ForeignKey('FOIRequests.foirequestid'))
foirequestversion_id = db.Column(db.Integer, db.ForeignKey('FOIRequests.version'))
foirequestkey = relationship("FOIRequest",foreign_keys="[FOIRequestApplicantMapping.foirequest_id]")
foirequestversion = relationship("FOIRequest",foreign_keys="[FOIRequestApplicantMapping.foirequestversion_id]")
@classmethod
def getrequestapplicants(cls,foirequest_id,foirequestversion):
requestapplicant_schema = FOIRequestApplicantMappingSchema(many=True)
_applicantinfos = db.session.query(FOIRequestApplicantMapping).filter(FOIRequestApplicantMapping.foirequest_id == foirequest_id , FOIRequestApplicantMapping.foirequestversion_id == foirequestversion).order_by(FOIRequestApplicantMapping.foirequestapplicantmappingid.asc()).all()
applicantinfos = requestapplicant_schema.dump(_applicantinfos)
return applicantinfos
class TemptableOfficialNoLocation(Base, _TempOfficial, DataSchemaMixin):
__tablename__ = "temp_official_no_location"
state_fips = Column(Integer)
location_name = Column(String)
__table_args__ = (
ForeignKeyConstraint(
["age", "race", "sex"],
[CovidDemographic.age, CovidDemographic.race, CovidDemographic.sex],
),
ForeignKeyConstraint(
["location_type", state_fips, location_name],
[Location.location_type, Location.state_fips, Location.name],
),
{"schema": "data"},
)
class PatientDao(db.Model):
__tablename__ = 'patients'
__table_args__ = (ForeignKeyConstraint(['patient_type_symbol'],
['patient_type.symbol'],
name="fk_patient_type_sybol"), )
id = Column(Integer, Sequence('patient_id_seq'), primary_key=True)
email = Column(String, unique=True, nullable=False)
name = Column(String, nullable=False)
surname = Column(String, nullable=False)
birthdate = Column(Date, nullable=False)
sex = Column(String, nullable=False)
patient_type_symbol = Column(String, ForeignKey('patient_type.symbol'))
def __init__(self, email: str, name: str, surname: str, birthdate: Date,
sex: SexType, patient_type: PatientTypeDao):
self.email = email
self.name = name
self.surname = surname
self.birthdate = birthdate
self.sex = sex.name
self.patient_type_symbol = patient_type.name
def toParamsDict(self):
return {
'id': self.id,
'first_name': self.name,
'last_name': self.surname,
'birthdate': self.birthdate,
'email': self.email,
'sex': self.sex,
'type': self.patient_type_symbol
}
class FOIRawRequestDocument(db.Model):
# Name of the table in our database
__tablename__ = 'FOIRawRequestDocuments'
__table_args__ = (
ForeignKeyConstraint(
["foirequest_id", "foirequestversion_id"], ["FOIRawRequests.requestid", "FOIRawRequests.version"]
),
)
# Defining the columns
foidocumentid = db.Column(db.Integer, primary_key=True,autoincrement=True)
documentpath = db.Column(db.String(1000), unique=False, nullable=False)
filename = db.Column(db.String(120), unique=False, nullable=True)
category = db.Column(db.String(120), unique=False, nullable=True)
version =db.Column(db.Integer, nullable=True)
isactive = db.Column(db.Boolean, unique=False, nullable=False,default=True)
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, nullable=True)
createdby = db.Column(db.String(120), unique=False, nullable=True)
updatedby = db.Column(db.String(120), unique=False, nullable=True)
#ForeignKey References
foirequest_id =db.Column(db.Integer, unique=False, nullable=False)
foirequestversion_id = db.Column(db.Integer, unique=False, nullable=False)
@classmethod
def getdocuments(cls,requestid, requestversion):
sql = 'SELECT * FROM (SELECT DISTINCT ON (foidocumentid) foidocumentid, filename, documentpath, category, isactive, created_at , createdby FROM "FOIRawRequestDocuments" where foirequest_id =:requestid and foirequestversion_id = :requestversion ORDER BY foidocumentid, version DESC) AS list ORDER BY created_at DESC'
rs = db.session.execute(text(sql), {'requestid': requestid, 'requestversion': requestversion})
documents = []
for row in rs:
if row["isactive"] == True:
documents.append({"foidocumentid": row["foidocumentid"], "filename": row["filename"], "documentpath": row["documentpath"], "category": row["category"], "created_at": row["created_at"].strftime('%Y-%m-%d %H:%M:%S.%f'), "createdby": row["createdby"]})
return documents
@classmethod
def getdocument(cls,foidocumentid):
document_schema = FOIRawRequestDocumentSchema()
request = db.session.query(FOIRawRequestDocument).filter_by(foidocumentid=foidocumentid).order_by(FOIRawRequestDocument.version.desc()).first()
return document_schema.dump(request)
@classmethod
def createdocuments(cls,requestid,requestversion, documents, userid):
newdocuments = []
for document in documents:
createuserid = document['createdby'] if 'createdby' in document and document['createdby'] is not None else userid
createdat = document['created_at'] if 'created_at' in document and document['created_at'] is not None else datetime.now()
newdocuments.append(FOIRawRequestDocument(documentpath=document["documentpath"], version='1', filename=document["filename"], category=document["category"], isactive=True, foirequest_id=requestid, foirequestversion_id=requestversion, created_at=createdat, createdby=createuserid))
db.session.add_all(newdocuments)
db.session.commit()
return DefaultMethodResult(True,'Documents created')
@classmethod
def createdocumentversion(cls,requestid,requestversion, document, userid):
newdocument = FOIRawRequestDocument(documentpath=document["documentpath"], foidocumentid=document["foidocumentid"], version=document["version"], filename=document["filename"], category=document["category"], isactive=document["isactive"], foirequest_id=requestid, foirequestversion_id=requestversion, created_at=datetime.now(), createdby=userid)
db.session.add(newdocument)
db.session.commit()
return DefaultMethodResult(True,'New Document version created', newdocument.foidocumentid)
class Book_Genre(BASE):
__tablename__ = 'book_genre'
book_id = Column(INTEGER, ForeignKey('book.book_id'), nullable=False)
genre_id = Column(INTEGER, ForeignKey('genre.genre_id'), nullable=False)
book = relationship("Book", backref=backref("book_genre"))
genre = relationship("Genre", backref=backref("book_genre"))
__table_args__ = (PrimaryKeyConstraint('book_id',
'genre_id',
name='PRIMARY'),
ForeignKeyConstraint(['book_id'], ['book.book_id']),
ForeignKeyConstraint(['genre_id'], ['genre.genre_id']))
def __init__(self, book=None, genre=None):
self.book = book
self.genre = genre
class TemptableOfficialHasLocation(Base, _TempOfficial, DataSchemaMixin):
__tablename__ = "temp_official_has_location"
location = Column(
BigInteger,
nullable=False,
)
__table_args__ = (
ForeignKeyConstraint(
["age", "race", "sex"],
[CovidDemographic.age, CovidDemographic.race, CovidDemographic.sex],
),
ForeignKeyConstraint(
["location", "location_type"], [Location.location, Location.location_type]
),
{"schema": "data"},
)
class Author_Book(BASE):
__tablename__ = 'author_book'
author_id = Column(INTEGER, ForeignKey('author.author_id'), nullable=False)
book_id = Column(INTEGER, ForeignKey('book.book_id'), nullable=False)
author = relationship("Author", backref=backref("author_book"))
book = relationship("Book", backref=backref("author_book"))
__table_args__ = (PrimaryKeyConstraint('author_id',
'book_id',
name='PRIMARY'),
ForeignKeyConstraint(['author_id'],
['author.author_id']),
ForeignKeyConstraint(['book_id'], ['book.book_id']))
def __init__(self, author=None, book=None):
self.author = author
self.book = book
class FOIMinistryRequestDivision(db.Model):
# Name of the table in our database
__tablename__ = 'FOIMinistryRequestDivisions'
__table_args__ = (ForeignKeyConstraint(
["foiministryrequest_id", "foiministryrequestversion_id"], [
"FOIMinistryRequests.foiministryrequestid",
"FOIMinistryRequests.version"
]), )
# Defining the columns
foiministrydivisionid = db.Column(db.Integer,
primary_key=True,
autoincrement=True)
divisionid = db.Column(db.Integer,
ForeignKey('ProgramAreaDivisions.divisionid'))
division = relationship("ProgramAreaDivision",
backref=backref("ProgramAreaDivisions"),
uselist=False)
stageid = db.Column(db.Integer,
ForeignKey('ProgramAreaDivisionStages.stageid'))
stage = relationship("ProgramAreaDivisionStage",
backref=backref("ProgramAreaDivisionStages"),
uselist=False)
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, nullable=True)
createdby = db.Column(db.String(120), unique=False, nullable=True)
updatedby = db.Column(db.String(120), unique=False, nullable=True)
#ForeignKey References
foiministryrequest_id = db.Column(
db.Integer, db.ForeignKey('FOIMinistryRequests.foiministryrequestid'))
foiministryrequestversion_id = db.Column(
db.Integer, db.ForeignKey('FOIMinistryRequests.version'))
foiministryrequest = relationship(
"FOIMinistryRequest",
foreign_keys="[FOIMinistryRequestDivision.foiministryrequest_id]")
foiministryrequestversion = relationship(
"FOIMinistryRequest",
foreign_keys="[FOIMinistryRequestDivision.foiministryrequestversion_id]"
)
@classmethod
def getdivisions(cls, ministryrequestid, ministryrequestversion):
division_schema = FOIMinistryRequestDivisionSchema(many=True)
_divisions = db.session.query(FOIMinistryRequestDivision).filter(
FOIMinistryRequestDivision.foiministryrequest_id ==
ministryrequestid,
FOIMinistryRequestDivision.foiministryrequestversion_id ==
ministryrequestversion).order_by(
FOIMinistryRequestDivision.foiministrydivisionid.asc()).all()
divisioninfos = division_schema.dump(_divisions)
return divisioninfos
class FOIRequestPersonalAttribute(db.Model):
# Name of the table in our database
__tablename__ = 'FOIRequestPersonalAttributes'
__table_args__ = (ForeignKeyConstraint(
["foirequest_id", "foirequestversion_id"],
["FOIRequests.foirequestid", "FOIRequests.version"]), )
# Defining the columns
foirequestpersonalattributeid = db.Column(db.Integer,
primary_key=True,
autoincrement=True)
attributevalue = db.Column(db.String(256), unique=False, nullable=False)
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, nullable=True)
createdby = db.Column(db.String(120), unique=False, nullable=True)
updatedby = db.Column(db.String(120), unique=False, nullable=True)
#ForeignKey References
personalattributeid = db.Column(
db.Integer, ForeignKey('PersonalInformationAttributes.attributeid'))
personalattribute = relationship(
"PersonalInformationAttribute",
backref=backref("PersonalInformationAttributes"),
uselist=False)
foirequest_id = db.Column(db.Integer,
db.ForeignKey('FOIRequests.foirequestid'))
foirequestversion_id = db.Column(db.Integer,
db.ForeignKey('FOIRequests.version'))
foirequestkey = relationship(
"FOIRequest",
foreign_keys="[FOIRequestPersonalAttribute.foirequest_id]")
foirequestversion = relationship(
"FOIRequest",
foreign_keys="[FOIRequestPersonalAttribute.foirequestversion_id]")
@classmethod
def getrequestpersonalattributes(cls, foirequest_id, foirequestversion):
requestpersonalattribute_schema = FOIRequestPersonalAttributeSchema(
many=True)
_personalattributes = db.session.query(
FOIRequestPersonalAttribute).filter(
FOIRequestPersonalAttribute.foirequest_id == foirequest_id,
FOIRequestPersonalAttribute.foirequestversion_id ==
foirequestversion).order_by(
FOIRequestPersonalAttribute.foirequestpersonalattributeid.
asc()).all()
personalattributes = requestpersonalattribute_schema.dump(
_personalattributes)
return personalattributes
class CommitIssue(Base):
__tablename__ = 'commit_issue'
commit_id = Column('commit_id',
String(40),
ForeignKey('commit.id'),
primary_key=True)
issue_id = Column('issue_id', String(20), primary_key=True)
issue_tracking_id = Column('issue_tracking_id', Integer, primary_key=True)
__table_args__ = (ForeignKeyConstraint(
[issue_id, issue_tracking_id],
['issue.id', 'issue.issue_tracking_id']), {})
def __table_args__(cls):
return (
PrimaryKeyConstraint(
"dt",
"location",
"variable_id",
"demographic_id",
sqlite_on_conflict="REPLACE",
),
ForeignKeyConstraint(
[cls.location, cls.location_type],
[Location.location, Location.location_type],
),
)
class Pickup(Base):
__tablename__ = 'pickup'
__table_args__ = (
PrimaryKeyConstraint('id'),
ForeignKeyConstraint(('entity_id',),
('entity.id',),
ondelete='RESTRICT'),
UniqueConstraint('name')
)
id = Column(Integer, nullable=False, autoincrement=False)
entity_id = Column(Integer)
name = Column(String(128), nullable=False)
commented = Column(Integer, nullable=False, server_default='0')
description = Column(Text)
entity = relationship('Entity')
class Cell(JusticeCountsBase):
"""A single data point within a table.
"""
__tablename__ = 'cell'
id = Column(Integer, autoincrement=True)
report_table_instance_id = Column(Integer, nullable=False)
aggregated_dimension_values = Column(ARRAY(String(255)), nullable=False)
value = Column(Numeric, nullable=False)
__table_args__ = tuple([
PrimaryKeyConstraint(id),
UniqueConstraint(report_table_instance_id, aggregated_dimension_values),
ForeignKeyConstraint([report_table_instance_id], [ReportTableInstance.id])])
report_table_instance = relationship(ReportTableInstance)
def _table_definition(metadata: MetaData) -> Table:
""" Gets the definition of the comments table.
Args:
metadata (MetaData): The database schema metadata.
Returns:
Table: Table following the comments table definition.
"""
return Table(
"comments", metadata,
Column("repo_dir", String(140), primary_key=True),
Column("issue_id", Integer, primary_key=True),
Column("comment_id", Integer, primary_key=True),
Column("author", String(39), nullable=False),
Column("body", LONGTEXT, nullable=False),
ForeignKeyConstraint(["repo_dir", "issue_id"],
["issues.repo_dir", "issues.issue_id"],
ondelete="CASCADE"))
class Cart(BASE):
__tablename__ = 'cart'
item_id = Column(String(100), nullable=False)
customer_id = Column(INTEGER, nullable=False)
quantity = Column(INTEGER(unsigned=True), nullable=False)
customer = relationship("Customer", backref=backref('cart'))
__table_args__ = (PrimaryKeyConstraint('item_id',
'customer_id',
name='PRIMARY'),
ForeignKeyConstraint(['customer_id'],
['customer.customer_id']))
def __init__(self, item_id, customer_id, quantity):
self.item_id = item_id
self.customer_id = customer_id
self.quantity = quantity
class Transaction(BASE):
__tablename__ = 'transaction'
transaction_time = Column(TIMESTAMP, default=datetime.now(), nullable=False)
item_id = Column(String(100), nullable=False)
customer_id = Column(INTEGER, nullable=False)
quantity = Column(INTEGER(unsigned=True), nullable=False)
customer = relationship("Customer", backref=backref('transaction'))
__table_args__ = (
PrimaryKeyConstraint('transaction_time', 'item_id', 'customer_id', name='PRIMARY'),
ForeignKeyConstraint(['customer_id'],['customer.customer_id']))
def __init__(self, item_id, customer_id, quantity):
#self.transaction_time = transaction_time
self.item_id = item_id
self.customer_id = customer_id
self.quantity = quantity
def tables(for_downgrade=False):
import sqlalchemy_jsonfield
global task_instance, rendered_task_instance_fields, dag_run
metadata = sa.MetaData()
task_instance = sa.Table(
'task_instance',
metadata,
sa.Column('task_id', StringID()),
sa.Column('dag_id', StringID()),
sa.Column('run_id', StringID()),
sa.Column('execution_date', TIMESTAMP),
)
rendered_task_instance_fields = sa.Table(
'rendered_task_instance_fields',
metadata,
sa.Column('dag_id', StringID()),
sa.Column('task_id', StringID()),
sa.Column('run_id', StringID()),
sa.Column('execution_date', TIMESTAMP),
sa.Column('rendered_fields', sqlalchemy_jsonfield.JSONField(), nullable=False),
sa.Column('k8s_pod_yaml', sqlalchemy_jsonfield.JSONField(), nullable=True),
)
if for_downgrade:
rendered_task_instance_fields.append_column(
sa.Column('map_index', sa.Integer(), server_default='-1'),
)
rendered_task_instance_fields.append_constraint(
ForeignKeyConstraint(
['dag_id', 'run_id'],
["dag_run.dag_id", "dag_run.run_id"],
name='rtif_dag_run_fkey',
ondelete="CASCADE",
),
)
dag_run = sa.Table(
'dag_run',
metadata,
sa.Column('dag_id', StringID()),
sa.Column('run_id', StringID()),
sa.Column('execution_date', TIMESTAMP),
)
class Book(BASE):
__tablename__ = 'book'
book_id = Column(INTEGER(unsigned=True), nullable=False, primary_key=True)
title = Column(String(100), nullable=False)
description = Column(String(1000), nullable=False)
num_in_stock = Column(INTEGER(unsigned=True), nullable=False)
pages = Column(INTEGER(unsigned=True), nullable=False)
release_year = Column(INTEGER(unsigned=True), nullable=True)
#put this back if things break. Ok, I did
publisher_id = Column(INTEGER(unsigned=True),
ForeignKey('publisher.publisher_id'),
nullable=False)
price = Column(DOUBLE, nullable=False)
publisher = relationship("Publisher", backref=backref('book'))
authors = relationship("Author", secondary='author_book', viewonly=True)
genres = relationship("Genre", secondary='book_genre', viewonly=True)
__table_args__ = (PrimaryKeyConstraint('book_id', name='PRIMARY'),
ForeignKeyConstraint(['publisher_id'],
['publisher.publisher_id']))
def __init__(self, title, description, num_in_stock, pages, release_year,
author, price, genre, publisher):
self.title = title
self.description = description
self.num_in_stock = num_in_stock
self.pages = pages
self.release_year = release_year
self.price = price
self.publisher = publisher
self.addAuthor(author)
self.addGenre(genre)
def addAuthor(self, author):
newEntry = Author.Author_Book(author=author, book=self)
self.author_book.append(newEntry)
def addGenre(self, genre):
newEntry = Genre.Book_Genre(genre=genre, book=self)
self.book_genre.append(newEntry)
class Schedule(Base):
__tablename__ = 'schedule'
__table_args__ = (
PrimaryKeyConstraint('id'),
ForeignKeyConstraint(('entity_id', ), ('entity.id', ),
ondelete='RESTRICT'),
)
id = Column(Integer, nullable=False)
entity_id = Column(Integer)
name = Column(String(255), nullable=False, server_default='')
timezone = Column(String(128))
fallback_action = Column(enum.dialaction_action,
nullable=False,
server_default='none')
fallback_actionid = Column(String(255))
fallback_actionargs = Column(String(255))
description = Column(Text)
commented = Column(Integer, nullable=False, server_default='0')
entity = relationship('Entity')
Column('user_id', String(63), ForeignKey(user.c.user_id), nullable=False),
Column('worker_id', String(127), nullable=False),
# No foreign key constraint on the worker table so that we can create a socket
# for the worker before adding the worker to the worker table.
Column('socket_id', Integer, primary_key=True, nullable=False),
)
# Store information about the bundles currently running on each worker.
worker_run = Table(
'worker_run',
db_metadata,
Column('user_id', String(63), ForeignKey(user.c.user_id), nullable=False),
Column('worker_id', String(127), nullable=False),
ForeignKeyConstraint(['user_id', 'worker_id'], ['worker.user_id', 'worker.worker_id']),
Column('run_uuid', String(63), ForeignKey(bundle.c.uuid), nullable=False),
Index('uuid_index', 'run_uuid'),
)
# Store information about the dependencies available on each worker.
worker_dependency = Table(
'worker_dependency',
db_metadata,
Column('user_id', String(63), ForeignKey(user.c.user_id), nullable=False),
Column('worker_id', String(127), nullable=False),
ForeignKeyConstraint(['user_id', 'worker_id'], ['worker.user_id', 'worker.worker_id']),
# No foreign key here, since we don't have any logic to clean-up bundles that
'systematik', ALEXANDRIA_METADATA,
Column('sort', Integer),
Column('punkt', String),
Column('roemisch', Integer),
Column('sub', Integer),
Column('beschreibung', String),
Column('textsort', String))
DOCUMENT_SYSTEMATIC_REFERENCE_TABLE = Table(
'sverweis',
ALEXANDRIA_METADATA,
Column('systematik', String),
Column('roemisch', Integer),
Column('sub', Integer),
Column('hauptnr', Integer, ForeignKey('dokument.hauptnr')),
ForeignKeyConstraint(['systematik', 'roemisch', 'sub'],
['systematik.punkt', 'systematik.roemisch', 'systematik.sub']),
UniqueConstraint('systematik', 'roemisch', 'sub', 'hauptnr'))
def systematic_string_to_identifier(raw_systematic_id):
'''
Creates a systematic identifier from an identifier string (as stored in database)
'''
if not raw_systematic_id:
return None
systematic_re = re.compile(r"([0-9.]*\d)(\.([IVXLCM]+))?(-(\d+))?")
matcher = systematic_re.match(raw_systematic_id)
if not matcher:
raise DataError("%s is not a valid systematic id!" % raw_systematic_id)
point = matcher.group(1)
roman = matcher.group(3)
if not roman:
def __init__(self, engine, schema=None):
self._metadata = MetaData(schema=schema)
self.mappers = []
_feedinfo_id_column = Column('feed_id', String, primary_key=True)
_agency_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_route_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_feedinfo_mapper = Table('feed_info', self._metadata,
_feedinfo_id_column,
Column('feed_publisher_name', String),
Column('feed_publisher_url', String),
Column('feed_contact_email', String), # Non-standard (yet) field
Column('feed_contact_url', String), # Non-standard (yet) field
Column('feed_lang', String),
Column('feed_start_date', Date),
Column('feed_end_date', Date),
Column('feed_version', String))
self.mappers.append(mapper(FeedInfo, _feedinfo_mapper, properties={
}))
_agency_id_column = Column('agency_id', String, primary_key=True)
_route_agency_id_column = Column('agency_id', String, nullable=False)
_agency_mapper = Table('agency', self._metadata,
_agency_feed_id_column,
_agency_id_column,
Column('agency_name', String, nullable=False),
Column('agency_url', String, nullable=False),
Column('agency_timezone', String, nullable=False),
Column('agency_lang', String),
Column('agency_phone', String),
Column('agency_fare_url', String),
Column('agency_email', String))
self.mappers.append(mapper(Agency, _agency_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('agencies', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_agency_feed_id_column))
}))
_zone_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_zone_id_column = Column('zone_id', String, primary_key=True)
_zone_mapper = Table('zones', self._metadata,
_zone_feed_id_column,
_zone_id_column)
self.mappers.append(mapper(Zone, _zone_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('zones', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_zone_feed_id_column))
}))
_stop_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_id_column = Column('stop_id', String, primary_key=True)
_stop_parent_id_column = Column('parent_station_id', String, nullable=True)
_stop_zone_id_column = Column('zone_id', String, nullable=True)
_stop_mapper = Table('stops', self._metadata,
_stop_feed_id_column,
_stop_id_column,
_stop_parent_id_column,
Column('location_type', Integer, nullable=False),
Column('stop_name', String, nullable=False),
Column('stop_lat', Float, nullable=False),
Column('stop_lon', Float, nullable=False),
Column('wheelchair_boarding', Integer, nullable=False),
Column('stop_code', String),
Column('stop_desc', String),
_stop_zone_id_column,
Column('stop_url', String),
Column('stop_timezone', String),
ForeignKeyConstraint(['feed_id', 'parent_station_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'zone_id'], ['zones.feed_id', 'zones.zone_id']),
# TODO Make those index parametrable
Index('idx_stops_lat', 'stop_lat'),
Index('idx_stops_lon', 'stop_lon'),
Index('idx_stops_code', 'feed_id', 'stop_code'),
Index('idx_stops_zone', 'feed_id', 'zone_id'),
Index('idx_stops_parent', 'feed_id', 'parent_station_id'))
self.mappers.append(mapper(Stop, _stop_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_stop_feed_id_column)),
'sub_stops' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_parent_id_column], uselist=True,
primaryjoin=(_stop_parent_id_column == foreign(_stop_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'parent_station' : relationship(Stop, remote_side=[_stop_feed_id_column, _stop_id_column],
primaryjoin=(_stop_id_column == foreign(_stop_parent_id_column)) & (_stop_feed_id_column == _stop_feed_id_column)),
'zone' : relationship(Zone, backref=backref('stops', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_stop_zone_id_column)) & (_zone_feed_id_column == _stop_feed_id_column))
}))
_transfer_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_transfer_from_stop_id_column = Column('from_stop_id', String, primary_key=True)
_transfer_to_stop_id_column = Column('to_stop_id', String, primary_key=True)
_transfer_mapper = Table('transfers', self._metadata,
_transfer_feed_id_column,
_transfer_from_stop_id_column,
_transfer_to_stop_id_column,
Column('transfer_type', Integer, nullable=False),
Column('min_transfer_time', Integer),
ForeignKeyConstraint(['feed_id', 'from_stop_id'], ['stops.feed_id', 'stops.stop_id']),
ForeignKeyConstraint(['feed_id', 'to_stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_transfer_from', 'feed_id', 'from_stop_id'),
Index('idx_transfer_to', 'feed_id', 'to_stop_id'))
self.mappers.append(mapper(Transfer, _transfer_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('transfers', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_transfer_feed_id_column)),
'from_stop' : relationship(Stop, backref=backref('from_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_from_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column)),
'to_stop' : relationship(Stop, backref=backref('to_transfers', cascade='all', uselist=True), uselist=False,
primaryjoin=(_transfer_to_stop_id_column == foreign(_stop_id_column)) & (_transfer_feed_id_column == _stop_feed_id_column))
}))
_route_id_column = Column('route_id', String, primary_key=True)
_route_mapper = Table('routes', self._metadata,
_route_feed_id_column,
_route_id_column,
_route_agency_id_column,
Column('route_short_name', String),
Column('route_long_name', String),
Column('route_desc', String),
Column('route_type', Integer, nullable=False),
Column('route_url', String),
Column('route_color', String),
Column('route_text_color', String),
ForeignKeyConstraint(['feed_id', 'agency_id'], ['agency.feed_id', 'agency.agency_id']),
Index('idx_routes_agency', 'feed_id', 'agency_id'),
Index('idx_routes_short_name', 'feed_id', 'route_short_name'),
Index('idx_routes_type', 'feed_id', 'route_type'))
self.mappers.append(mapper(Route, _route_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_route_feed_id_column)),
'agency' : relationship(Agency, backref=backref('routes', cascade="all,delete-orphan"),
primaryjoin=(_agency_id_column == foreign(_route_agency_id_column)) & (_agency_feed_id_column == _route_feed_id_column))
}))
_calendar_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_calendar_id_column = Column('service_id', String, primary_key=True)
_calendar_mapper = Table('calendar', self._metadata,
_calendar_feed_id_column,
_calendar_id_column
)
self.mappers.append(mapper(Calendar, _calendar_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('calendars', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_calendar_feed_id_column))
}))
_calendar_date_mapper = Table('calendar_dates', self._metadata,
Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True),
Column('service_id', String, primary_key=True),
Column('date', Date, primary_key=True),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
# TOCHECK It seems a composite primary key on (a,b,c) does not need indexing on left elements,
# such as (a) and (a,b); but need on (a,c) for example.
Index('idx_calendar_dates_date', 'feed_id', 'date'))
self.mappers.append(mapper(CalendarDate, _calendar_date_mapper, properties={
'calendar' : relationship(Calendar, backref=backref('dates', cascade="all,delete-orphan"))
}))
_shape_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_mapper = Table('shapes', self._metadata,
_shape_feed_id_column,
_shape_id_column
)
self.mappers.append(mapper(Shape, _shape_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('shapes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_shape_feed_id_column))
}))
_shape_pt_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_shape_pt_shape_id_column = Column('shape_id', String, primary_key=True)
_shape_pt_seq_column = Column('shape_pt_sequence', Integer, primary_key=True)
_shape_pt_mapper = Table('shape_pts', self._metadata,
_shape_pt_feed_id_column,
_shape_pt_shape_id_column,
_shape_pt_seq_column,
Column('shape_dist_traveled', Float, nullable=False),
Column('shape_pt_lat', Float, nullable=False),
Column('shape_pt_lon', Float, nullable=False),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_shape_pt_shape', 'feed_id', 'shape_id'))
self.mappers.append(mapper(ShapePoint, _shape_pt_mapper, properties={
# Note: here we specify foreign() on shape_pt feed_id column as there is no ownership relation of feed to shape_pts
'shape' : relationship(Shape, backref=backref('points', order_by=_shape_pt_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_shape_pt_shape_id_column)) & (_shape_feed_id_column == foreign(_shape_pt_feed_id_column)))
}))
_trip_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_trip_id_column = Column('trip_id', String, primary_key=True)
_trip_route_id_column = Column('route_id', String, nullable=False)
_trip_calendar_id_column = Column('service_id', String, nullable=False)
_trip_shape_id_column = Column('shape_id', String, nullable=True)
_trip_mapper = Table('trips', self._metadata,
_trip_feed_id_column,
_trip_id_column,
_trip_route_id_column,
_trip_calendar_id_column,
_trip_shape_id_column,
Column('wheelchair_accessible', Integer, nullable=False),
Column('bikes_allowed', Integer, nullable=False),
Column('exact_times', Integer, nullable=False),
Column('frequency_generated', Boolean, nullable=False),
Column('trip_headsign', String),
Column('trip_short_name', String),
Column('direction_id', Integer),
Column('block_id', String),
ForeignKeyConstraint(['feed_id', 'route_id'], ['routes.feed_id', 'routes.route_id']),
ForeignKeyConstraint(['feed_id', 'service_id'], ['calendar.feed_id', 'calendar.service_id']),
ForeignKeyConstraint(['feed_id', 'shape_id'], ['shapes.feed_id', 'shapes.shape_id']),
Index('idx_trips_route', 'feed_id', 'route_id'),
Index('idx_trips_service', 'feed_id', 'service_id'))
self.mappers.append(mapper(Trip, _trip_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_trip_feed_id_column)),
'route' : relationship(Route, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_trip_route_id_column)) & (_route_feed_id_column == _trip_feed_id_column)),
'calendar' : relationship(Calendar, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_calendar_id_column == foreign(_trip_calendar_id_column)) & (_calendar_feed_id_column == _trip_feed_id_column)),
'shape' : relationship(Shape, backref=backref('trips', cascade="all,delete-orphan"),
primaryjoin=(_shape_id_column == foreign(_trip_shape_id_column)) & (_shape_feed_id_column == _trip_feed_id_column))
}))
_stop_times_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_stop_times_trip_id_column = Column('trip_id', String, primary_key=True)
_stop_seq_column = Column('stop_sequence', Integer, primary_key=True)
_stop_times_stop_id_column = Column('stop_id', String, nullable=False)
_stop_times_mapper = Table('stop_times', self._metadata,
_stop_times_feed_id_column,
_stop_times_trip_id_column,
_stop_seq_column,
_stop_times_stop_id_column,
Column('arrival_time', Integer, nullable=True),
Column('departure_time', Integer, nullable=True),
Column('interpolated', Boolean, nullable=False),
Column('shape_dist_traveled', Float, nullable=False),
Column('timepoint', Integer, nullable=False),
Column('pickup_type', Integer, nullable=False),
Column('drop_off_type', Integer, nullable=False),
Column('stop_headsign', String),
ForeignKeyConstraint(['feed_id', 'trip_id'], ['trips.feed_id', 'trips.trip_id']),
ForeignKeyConstraint(['feed_id', 'stop_id'], ['stops.feed_id', 'stops.stop_id']),
Index('idx_stop_times_stop', 'feed_id', 'stop_id'),
Index('idx_stop_times_sequence', 'feed_id', 'stop_sequence'))
self.mappers.append(mapper(StopTime, _stop_times_mapper, properties={
# Note: here we specify foreign() on stop_times feed_id column as there is no ownership relation of feed to stop_times
'trip' : relationship(Trip, backref=backref('stop_times', order_by=_stop_seq_column, cascade="all,delete-orphan"),
primaryjoin=(_trip_id_column == foreign(_stop_times_trip_id_column)) & (_trip_feed_id_column == foreign(_stop_times_feed_id_column))),
'stop' : relationship(Stop, backref=backref('stop_times', cascade="all,delete-orphan"),
primaryjoin=(_stop_id_column == foreign(_stop_times_stop_id_column)) & (_stop_feed_id_column == _stop_times_feed_id_column)),
}))
_fareattr_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'), primary_key=True)
_fareattr_id_column = Column('fare_id', String, primary_key=True)
_fareattr_mapper = Table('fare_attributes', self._metadata,
_fareattr_feed_id_column,
_fareattr_id_column,
Column('price', Float, nullable=False),
Column('currency_type', String, nullable=False),
Column('payment_method', Integer, nullable=False),
Column('transfers', Integer),
Column('transfer_duration', Integer))
self.mappers.append(mapper(FareAttribute, _fareattr_mapper, properties={
'feed' : relationship(FeedInfo, backref=backref('fare_attributes', cascade="all,delete-orphan"),
primaryjoin=_feedinfo_id_column == foreign(_fareattr_feed_id_column))
}))
_farerule_feed_id_column = Column('feed_id', String, ForeignKey('feed_info.feed_id'))
_farerule_id_column = Column('fare_id', String)
# Use a dummy autoincrement numerical field for primary key,
# as a primary key is mandatory, and the natural primary key
# for the model (feed_id, fare_id, route+zones ids) do have
# some fields that can be null.
_farerule_rule_id_column = Column('fare_rule_id', Integer, primary_key=True, autoincrement=True)
_farerule_route_id_column = Column('route_id', String, nullable=True)
_farerule_origin_id_column = Column('origin_id', String, nullable=True)
_farerule_destination_id_column = Column('destination_id', String, nullable=True)
_farerule_contains_id_column = Column('contains_id', String, nullable=True)
_farerule_mapper = Table('fare_rules', self._metadata,
_farerule_feed_id_column,
_farerule_id_column,
_farerule_rule_id_column,
_farerule_route_id_column,
_farerule_origin_id_column,
_farerule_destination_id_column,
_farerule_contains_id_column,
ForeignKeyConstraint(['feed_id', 'fare_id'], ['fare_attributes.feed_id', 'fare_attributes.fare_id']))
self.mappers.append(mapper(FareRule, _farerule_mapper, properties={
'fare_attribute' : relationship(FareAttribute, backref=backref('fare_rules', cascade="all,delete-orphan")),
'route' : relationship(Route, backref=backref('fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_route_id_column == foreign(_farerule_route_id_column)) & (_route_feed_id_column == _farerule_feed_id_column)),
'origin' : relationship(Zone, backref=backref('origin_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_origin_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'destination' : relationship(Zone, backref=backref('destination_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_destination_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column)),
'contains' : relationship(Zone, backref=backref('contains_fare_rules', cascade="all,delete-orphan"),
primaryjoin=(_zone_id_column == foreign(_farerule_contains_id_column)) & (_zone_feed_id_column == _farerule_feed_id_column))
}))
self._metadata.create_all(engine)
self._class_for_table = {}
self._table_for_class = {}
for _mapper in self.mappers:
self._class_for_table[_mapper.mapped_table.name] = _mapper.class_
self._table_for_class[_mapper.class_] = _mapper.mapped_table.name
def _history_mapper(model_mapper):
"""Configure SQLAlchemy mapper and enable history support.
:param model_mapper: SQLAlchemy mapper object for a model.
:type model_mapper: sqlalchemy.orm.mapper.Mapper
:rtype: sqlalchemy.orm.mapper.Mapper
"""
model_class = model_mapper.class_
model_table = model_mapper.local_table
for prop in model_mapper.iterate_properties:
getattr(model_class, prop.key).impl.active_history = True
super_mapper = model_mapper.inherits
super_history_mapper = getattr(model_class, '__history_mapper__', None)
super_fks = []
properties = OrderedDict()
polymorphic_on = None
if not super_mapper or model_table is not super_mapper.local_table:
version_meta = {"version_meta": True}
new_columns = []
for column in model_table.c:
if _is_versioning_column(column):
continue
try:
original_prop = model_mapper.get_property_by_column(column)
except UnmappedColumnError:
continue
new_column = _copy_history_column(column)
new_columns.append(new_column)
if super_mapper and \
_is_fk_column(column, super_mapper.local_table):
super_fks.append(
(new_column.key,
list(super_history_mapper.local_table.primary_key)[0]))
if column is model_mapper.polymorphic_on:
polymorphic_on = new_column
if len(original_prop.columns) > 1 or \
original_prop.columns[0].key != original_prop.key:
properties[original_prop.key] = tuple(
c.info['history_copy'] for c in original_prop.columns)
if super_mapper:
super_fks.append((
'version',
super_history_mapper.local_table.c.version))
new_columns.append(
Column('version',
Integer,
primary_key=True,
autoincrement=False,
nullable=False,
info=version_meta))
new_columns.append(
Column('change_type',
String,
nullable=False,
info=version_meta))
new_columns.append(
Column('changed_at',
DateTime(timezone=True),
default=func.now(),
nullable=False,
info=version_meta))
if super_fks:
new_columns.append(ForeignKeyConstraint(*zip(*super_fks)))
new_table = Table(
model_table.name + '_history',
model_table.metadata,
*new_columns,
schema=model_table.schema)
else:
for column in model_mapper.c:
if column.key not in super_history_mapper.local_table.c:
new_column = _copy_history_column(column)
super_history_mapper.local_table.append_column(new_column)
new_table = None
if super_history_mapper:
bases = (super_history_mapper.class_,)
if new_table is not None:
properties['change_type'] = (
(new_table.c.change_type,) + tuple(
super_history_mapper.attrs.change_type.columns))
properties['changed_at'] = (
(new_table.c.changed_at,) + tuple(
super_history_mapper.attrs.changed_at.columns))
else:
bases = model_mapper.base_mapper.class_.__bases__
model_class.__history_mapper__ = mapper(
type.__new__(type, "%sHistory" % (model_class.__name__), bases, {}),
new_table,
inherits=super_history_mapper,
polymorphic_on=polymorphic_on,
polymorphic_identity=model_mapper.polymorphic_identity,
properties=properties)
if not super_history_mapper:
model_table.append_column(
Column('version',
Integer,
default=1,
nullable=False))
model_mapper.add_property('version', model_table.c.version)
class FOIMinistryRequest(db.Model):
# Name of the table in our database
__tablename__ = 'FOIMinistryRequests'
__table_args__ = (ForeignKeyConstraint(
["foirequest_id", "foirequestversion_id"],
["FOIRequests.foirequestid", "FOIRequests.version"]), )
# Defining the columns
foiministryrequestid = db.Column(db.Integer,
primary_key=True,
autoincrement=True)
version = db.Column(db.Integer, primary_key=True, nullable=False)
isactive = db.Column(db.Boolean,
unique=False,
nullable=False,
default=True)
filenumber = db.Column(db.String(50), unique=False, nullable=False)
description = db.Column(db.Text, unique=False, nullable=False)
recordsearchfromdate = db.Column(db.DateTime, nullable=True)
recordsearchtodate = db.Column(db.DateTime, nullable=True)
startdate = db.Column(db.DateTime, nullable=False, default=datetime.now())
duedate = db.Column(db.DateTime, nullable=False)
cfrduedate = db.Column(db.DateTime, nullable=True)
assignedgroup = db.Column(db.String(250), unique=False, nullable=True)
assignedto = db.Column(db.String(120),
ForeignKey('FOIAssignees.username'),
unique=False,
nullable=True)
created_at = db.Column(db.DateTime, default=datetime.now())
updated_at = db.Column(db.DateTime, nullable=True)
createdby = db.Column(db.String(120), unique=False, nullable=True)
updatedby = db.Column(db.String(120), unique=False, nullable=True)
assignedministryperson = db.Column(db.String(120),
ForeignKey('FOIAssignees.username'),
unique=False,
nullable=True)
assignedministrygroup = db.Column(db.String(120),
unique=False,
nullable=True)
closedate = db.Column(db.DateTime, nullable=True)
#ForeignKey References
closereasonid = db.Column(db.Integer,
ForeignKey('CloseReasons.closereasonid'))
closereason = relationship("CloseReason", uselist=False)
programareaid = db.Column(db.Integer,
ForeignKey('ProgramAreas.programareaid'))
programarea = relationship("ProgramArea",
backref=backref("ProgramAreas"),
uselist=False)
requeststatusid = db.Column(
db.Integer, ForeignKey('FOIRequestStatuses.requeststatusid'))
requeststatus = relationship("FOIRequestStatus",
backref=backref("FOIRequestStatuses"),
uselist=False)
foirequest_id = db.Column(db.Integer,
db.ForeignKey('FOIRequests.foirequestid'))
foirequestversion_id = db.Column(db.Integer,
db.ForeignKey('FOIRequests.version'))
foirequestkey = relationship(
"FOIRequest", foreign_keys="[FOIMinistryRequest.foirequest_id]")
foirequestversion = relationship(
"FOIRequest", foreign_keys="[FOIMinistryRequest.foirequestversion_id]")
divisions = relationship(
'FOIMinistryRequestDivision',
primaryjoin=
"and_(FOIMinistryRequest.foiministryrequestid==FOIMinistryRequestDivision.foiministryrequest_id, "
"FOIMinistryRequest.version==FOIMinistryRequestDivision.foiministryrequestversion_id)"
)
documents = relationship(
'FOIMinistryRequestDocument',
primaryjoin=
"and_(FOIMinistryRequest.foiministryrequestid==FOIMinistryRequestDocument.foiministryrequest_id, "
"FOIMinistryRequest.version==FOIMinistryRequestDocument.foiministryrequestversion_id)"
)
extensions = relationship(
'FOIRequestExtension',
primaryjoin=
"and_(FOIMinistryRequest.foiministryrequestid==FOIRequestExtension.foiministryrequest_id, "
"FOIMinistryRequest.version==FOIRequestExtension.foiministryrequestversion_id)"
)
assignee = relationship('FOIAssignee',
foreign_keys="[FOIMinistryRequest.assignedto]")
ministryassignee = relationship(
'FOIAssignee',
foreign_keys="[FOIMinistryRequest.assignedministryperson]")
@classmethod
def getrequest(cls, ministryrequestid):
request_schema = FOIMinistryRequestSchema(many=True)
query = db.session.query(FOIMinistryRequest).filter_by(
foiministryrequestid=ministryrequestid).order_by(
FOIMinistryRequest.version.desc()).first()
return request_schema.dump(query)
@classmethod
def getLastStatusUpdateDate(cls, foiministryrequestid, requeststatusid):
sql = """select created_at from "FOIMinistryRequests"
where foiministryrequestid = :foiministryrequestid and requeststatusid = :requeststatusid
order by version desc limit 1;"""
rs = db.session.execute(
text(sql), {
'foiministryrequestid': foiministryrequestid,
'requeststatusid': requeststatusid
})
return [row[0] for row in rs][0]
@classmethod
def getassignmenttransition(cls, requestid):
sql = """select version, assignedto, assignedministryperson from "FOIMinistryRequests"
where foiministryrequestid = :requestid
order by version desc limit 2;"""
rs = db.session.execute(text(sql), {'requestid': requestid})
assignments = []
for row in rs:
assignments.append({
"assignedto":
row["assignedto"],
"assignedministryperson":
row["assignedministryperson"],
"version":
row["version"]
})
return assignments
@classmethod
def deActivateFileNumberVersion(cls, ministryid, idnumber, currentversion,
userid) -> DefaultMethodResult:
db.session.query(FOIMinistryRequest).filter(
FOIMinistryRequest.foiministryrequestid == ministryid,
FOIMinistryRequest.filenumber == idnumber,
FOIMinistryRequest.version != currentversion).update(
{
"isactive": False,
"updated_at": datetime.now(),
"updatedby": userid
},
synchronize_session=False)
return DefaultMethodResult(True, 'Request Updated', idnumber)
@classmethod
def getrequests(cls, group=None):
_session = db.session
_ministryrequestids = []
if group is None:
_ministryrequestids = _session.query(
distinct(FOIMinistryRequest.foiministryrequestid)).filter(
FOIMinistryRequest.isactive == True).all()
elif (group == 'Flex Team'):
_ministryrequestids = _session.query(
distinct(FOIMinistryRequest.foiministryrequestid)).filter(
and_(FOIMinistryRequest.isactive == True),
and_(
and_(FOIMinistryRequest.assignedgroup == group),
and_(
FOIMinistryRequest.requeststatusid.in_(
[1, 2, 3, 12, 13, 7, 8, 9, 10, 11,
14])))).all()
elif (group in ProcessingTeamWithKeycloackGroup.list()):
_ministryrequestids = _session.query(
distinct(FOIMinistryRequest.foiministryrequestid)).filter(
and_(FOIMinistryRequest.isactive == True),
and_(
and_(FOIMinistryRequest.assignedgroup == group),
and_(
FOIMinistryRequest.requeststatusid.in_(
[1, 2, 3, 7, 8, 9, 10, 11, 14])))).all()
else:
_ministryrequestids = _session.query(
distinct(FOIMinistryRequest.foiministryrequestid)).filter(
and_(FOIMinistryRequest.isactive == True),
or_(
and_(FOIMinistryRequest.assignedgroup == group),
and_(
FOIMinistryRequest.assignedministrygroup == group,
or_(
FOIMinistryRequest.requeststatusid.in_(
[2, 7, 9, 8, 10, 11, 12, 13,
14]))))).all()
_requests = []
ministryrequest_schema = FOIMinistryRequestSchema()
for _requestid in _ministryrequestids:
_request = {}
ministryrequest = ministryrequest_schema.dump(
_session.query(FOIMinistryRequest).filter(
FOIMinistryRequest.foiministryrequestid ==
_requestid).order_by(
FOIMinistryRequest.version.desc()).first())
parentrequest = _session.query(FOIRequest).filter(
FOIRequest.foirequestid == ministryrequest['foirequest_id']
and FOIRequest.version
== ministryrequest['foirequestversion_id']).order_by(
FOIRequest.version.desc()).first()
requestapplicants = FOIRequestApplicantMapping.getrequestapplicants(
ministryrequest['foirequest_id'],
ministryrequest['foirequestversion_id'])
_receiveddate = parentrequest.receiveddate
_request["firstName"] = requestapplicants[0][
'foirequestapplicant.firstname']
_request["lastName"] = requestapplicants[0][
'foirequestapplicant.lastname']
_request["requestType"] = parentrequest.requesttype
_request["idNumber"] = ministryrequest['filenumber']
_request["currentState"] = ministryrequest["requeststatus.name"]
_request["dueDate"] = ministryrequest["duedate"]
_request["cfrDueDate"] = ministryrequest["cfrduedate"]
_request["receivedDate"] = _receiveddate.strftime('%Y %b, %d')
_request["receivedDateUF"] = str(_receiveddate)
_request["assignedGroup"] = ministryrequest["assignedgroup"]
_request["assignedTo"] = ministryrequest["assignedto"]
_request["assignedministrygroup"] = ministryrequest[
"assignedministrygroup"]
_request["assignedministryperson"] = ministryrequest[
"assignedministryperson"]
_request["xgov"] = 'No'
_request["version"] = ministryrequest['version']
_request["id"] = parentrequest.foirequestid
_request["ministryrequestid"] = ministryrequest[
'foiministryrequestid']
_request[
"applicantcategory"] = parentrequest.applicantcategory.name
_requests.append(_request)
return _requests
@classmethod
def getrequestbyministryrequestid(cls, ministryrequestid):
request_schema = FOIMinistryRequestSchema()
query = db.session.query(FOIMinistryRequest).filter_by(
foiministryrequestid=ministryrequestid).order_by(
FOIMinistryRequest.version.desc()).first()
return request_schema.dump(query)
@classmethod
def getrequestbyfilenumberandversion(cls, filenumber, version):
request_schema = FOIMinistryRequestSchema()
query = db.session.query(FOIMinistryRequest).filter_by(
filenumber=filenumber, version=version).order_by(
FOIMinistryRequest.version.desc()).first()
return request_schema.dump(query)
@classmethod
def getrequestById(cls, ministryrequestid):
request_schema = FOIMinistryRequestSchema(many=True)
query = db.session.query(FOIMinistryRequest).filter_by(
foiministryrequestid=ministryrequestid).order_by(
FOIMinistryRequest.version.asc())
return request_schema.dump(query)
@classmethod
def getrequeststatusById(cls, ministryrequestid):
sql = 'select foirequest_id, version, requeststatusid, created_at from "FOIMinistryRequests" fr where foiministryrequestid = :ministryrequestid and requeststatusid != 3 order by version desc;'
rs = db.session.execute(text(sql),
{'ministryrequestid': ministryrequestid})
summary = []
for row in rs:
summary.append({
"requeststatusid": row["requeststatusid"],
"created_at": row["created_at"],
"foirequest_id": row["foirequest_id"]
})
return summary
@classmethod
def getversionforrequest(cls, ministryrequestid):
return db.session.query(FOIMinistryRequest.version).filter_by(
foiministryrequestid=ministryrequestid).order_by(
FOIMinistryRequest.version.desc()).first()
@classmethod
def getstatesummary(cls, ministryrequestid):
sql = """select status, version from (select distinct on (fs2."name") name as status, version from "FOIMinistryRequests" fm inner join "FOIRequestStatuses" fs2 on fm.requeststatusid = fs2.requeststatusid
where foiministryrequestid=:ministryrequestid order by fs2."name", version asc) as fs3 order by version desc;"""
rs = db.session.execute(text(sql),
{'ministryrequestid': ministryrequestid})
transitions = []
for row in rs:
transitions.append({
"status": row["status"],
"version": row["version"]
})
return transitions
@classmethod
def getstatenavigation(cls, ministryrequestid):
sql = """select fs2."name" as status, version from "FOIMinistryRequests" fm inner join "FOIRequestStatuses" fs2 on fm.requeststatusid = fs2.requeststatusid
where foiministryrequestid=:ministryrequestid order by version desc limit 2"""
rs = db.session.execute(text(sql),
{'ministryrequestid': ministryrequestid})
requeststates = []
for row in rs:
requeststates.append(row["status"])
return requeststates
@classmethod
def getrequestssubquery(cls, groups, filterfields, keyword,
additionalfilter, userid, iaoassignee,
ministryassignee):
_session = db.session
#ministry filter for group/team
ministryfilter = FOIMinistryRequest.getgroupfilters(groups)
#subquery for getting latest version & proper group/team for FOIMinistryRequest
subquery_ministry_maxversion = _session.query(
FOIMinistryRequest.foiministryrequestid,
func.max(
FOIMinistryRequest.version).label('max_version')).group_by(
FOIMinistryRequest.foiministryrequestid).subquery()
joincondition_ministry = [
subquery_ministry_maxversion.c.foiministryrequestid ==
FOIMinistryRequest.foiministryrequestid,
subquery_ministry_maxversion.c.max_version ==
FOIMinistryRequest.version,
]
#subquery for getting the first applicant mapping
subquery_applicantmapping_first = _session.query(
FOIRequestApplicantMapping.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id,
func.min(FOIRequestApplicantMapping.foirequestapplicantid).
label('first_id')).group_by(
FOIRequestApplicantMapping.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id).subquery()
joincondition_applicantmapping = [
subquery_applicantmapping_first.c.foirequest_id ==
FOIRequestApplicantMapping.foirequest_id,
subquery_applicantmapping_first.c.foirequestversion_id ==
FOIRequestApplicantMapping.foirequestversion_id,
subquery_applicantmapping_first.c.first_id ==
FOIRequestApplicantMapping.foirequestapplicantid,
]
#filter/search
if (len(filterfields) > 0 and keyword is not None):
filtercondition = []
for field in filterfields:
filtercondition.append(
FOIMinistryRequest.findfield(
field, iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
selectedcolumns = [
FOIRequest.foirequestid.label('id'), FOIMinistryRequest.version,
literal(None).label('sourceofsubmission'),
FOIRequestApplicant.firstname.label('firstName'),
FOIRequestApplicant.lastname.label('lastName'),
FOIRequest.requesttype.label('requestType'),
cast(FOIRequest.receiveddate, String).label('receivedDate'),
cast(FOIRequest.receiveddate, String).label('receivedDateUF'),
FOIRequestStatus.name.label('currentState'),
FOIMinistryRequest.assignedgroup.label('assignedGroup'),
FOIMinistryRequest.assignedto.label('assignedTo'),
cast(FOIMinistryRequest.filenumber, String).label('idNumber'),
FOIMinistryRequest.foiministryrequestid.label('ministryrequestid'),
FOIMinistryRequest.assignedministrygroup.label(
'assignedministrygroup'),
FOIMinistryRequest.assignedministryperson.label(
'assignedministryperson'),
cast(FOIMinistryRequest.cfrduedate, String).label('cfrduedate'),
cast(FOIMinistryRequest.duedate, String).label('duedate'),
ApplicantCategory.name.label('applicantcategory'),
FOIRequest.created_at.label('created_at'),
func.lower(ProgramArea.bcgovcode).label('bcgovcode'),
iaoassignee.firstname.label('assignedToFirstName'),
iaoassignee.lastname.label('assignedToLastName'),
ministryassignee.firstname.label(
'assignedministrypersonFirstName'),
ministryassignee.lastname.label('assignedministrypersonLastName'),
FOIMinistryRequest.description
]
basequery = _session.query(*selectedcolumns).join(
subquery_ministry_maxversion, and_(*joincondition_ministry)
).join(
FOIRequest,
and_(FOIRequest.foirequestid == FOIMinistryRequest.foirequest_id,
FOIRequest.version == FOIMinistryRequest.foirequestversion_id)
).join(
FOIRequestStatus, FOIRequestStatus.requeststatusid ==
FOIMinistryRequest.requeststatusid).join(
FOIRequestApplicantMapping,
and_(
FOIRequestApplicantMapping.foirequest_id ==
FOIMinistryRequest.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id ==
FOIMinistryRequest.foirequestversion_id)
).join(subquery_applicantmapping_first,
and_(*joincondition_applicantmapping)).join(
FOIRequestApplicant,
FOIRequestApplicant.foirequestapplicantid ==
FOIRequestApplicantMapping.foirequestapplicantid).join(
ApplicantCategory,
and_(
ApplicantCategory.applicantcategoryid ==
FOIRequest.applicantcategoryid,
ApplicantCategory.isactive == True)
).join(
ProgramArea,
FOIMinistryRequest.programareaid ==
ProgramArea.programareaid).join(
iaoassignee,
iaoassignee.username ==
FOIMinistryRequest.assignedto,
isouter=True).join(
ministryassignee,
ministryassignee.username ==
FOIMinistryRequest.assignedministryperson,
isouter=True)
if (additionalfilter == 'watchingRequests'):
#watchby
subquery_watchby = FOIRequestWatcher.getrequestidsbyuserid(userid)
dbquery = basequery.join(
subquery_watchby, subquery_watchby.c.ministryrequestid ==
FOIMinistryRequest.foiministryrequestid).filter(ministryfilter)
elif (additionalfilter == 'myRequests'):
#myrequest
dbquery = basequery.filter(
FOIMinistryRequest.assignedministryperson == userid).filter(
ministryfilter)
else:
dbquery = basequery.filter(ministryfilter)
if (keyword is None):
return dbquery
else:
return dbquery.filter(or_(*filtercondition))
@classmethod
def getrequestspagination(cls, group, page, size, sortingitems,
sortingorders, filterfields, keyword,
additionalfilter, userid):
iaoassignee = aliased(FOIAssignee)
ministryassignee = aliased(FOIAssignee)
subquery = FOIMinistryRequest.getrequestssubquery(
group, filterfields, keyword, additionalfilter, userid,
iaoassignee, ministryassignee)
#sorting
sortingcondition = []
if (len(sortingitems) > 0 and len(sortingorders) > 0
and len(sortingitems) == len(sortingorders)):
for field in sortingitems:
order = sortingorders.pop()
if (order == 'desc'):
sortingcondition.append(
nullslast(
FOIMinistryRequest.findfield(
field, iaoassignee, ministryassignee).desc()))
else:
sortingcondition.append(
nullsfirst(
FOIMinistryRequest.findfield(
field, iaoassignee, ministryassignee).asc()))
#default sorting
if (len(sortingcondition) == 0):
sortingcondition.append(
FOIMinistryRequest.findfield('currentState', iaoassignee,
ministryassignee).asc())
return subquery.order_by(*sortingcondition).paginate(page=page,
per_page=size)
@classmethod
def findfield(cls, x, iaoassignee, ministryassignee):
#add more fields here if need sort/filter/search more columns
return {
'firstName': FOIRequestApplicant.firstname,
'lastName': FOIRequestApplicant.lastname,
'requestType': FOIRequest.requesttype,
'idNumber': FOIMinistryRequest.filenumber,
'idnumber': FOIMinistryRequest.filenumber,
'rawRequestNumber': FOIMinistryRequest.filenumber,
'currentState': FOIRequestStatus.name,
'assignedTo': FOIMinistryRequest.assignedto,
'receivedDate': FOIRequest.receiveddate,
'applicantcategory': ApplicantCategory.name,
'assignedministryperson':
FOIMinistryRequest.assignedministryperson,
'assignedToFirstName': iaoassignee.firstname,
'assignedToLastName': iaoassignee.lastname,
'assignedministrypersonFirstName': ministryassignee.firstname,
'assignedministrypersonLastName': ministryassignee.lastname,
'description': FOIMinistryRequest.description,
'requestdescription': FOIMinistryRequest.description,
'duedate': FOIMinistryRequest.duedate,
'ministry': func.upper(ProgramArea.bcgovcode)
}.get(x, FOIMinistryRequest.filenumber)
@classmethod
def getgroupfilters(cls, groups):
#ministry filter for group/team
if groups is None:
ministryfilter = FOIMinistryRequest.isactive == True
else:
groupfilter = []
for group in groups:
if (group == 'Flex Team'):
groupfilter.append(
and_(
FOIMinistryRequest.assignedgroup == group,
FOIMinistryRequest.requeststatusid.in_(
[1, 2, 3, 12, 13, 7, 8, 9, 10, 11, 14])))
elif (group in ProcessingTeamWithKeycloackGroup.list()):
groupfilter.append(
and_(
FOIMinistryRequest.assignedgroup == group,
FOIMinistryRequest.requeststatusid.in_(
[1, 2, 12, 13, 7, 9, 10, 14, 3])))
elif (group == 'Intake Team'):
groupfilter.append(
or_(FOIMinistryRequest.assignedgroup == group,
FOIMinistryRequest.requeststatusid.in_([1])))
else:
groupfilter.append(
or_(
FOIMinistryRequest.assignedgroup == group,
and_(
FOIMinistryRequest.assignedministrygroup ==
group,
FOIMinistryRequest.requeststatusid.in_(
[2, 7, 9, 8, 10, 11, 12, 13, 14]))))
ministryfilter = and_(FOIMinistryRequest.isactive == True,
FOIRequestStatus.isactive == True,
or_(*groupfilter))
return ministryfilter
@classmethod
def getrequestoriginalduedate(cls, ministryrequestid):
return db.session.query(FOIMinistryRequest.duedate).filter(
FOIMinistryRequest.foiministryrequestid == ministryrequestid,
FOIMinistryRequest.requeststatusid == 1).order_by(
FOIMinistryRequest.version).first()[0]
@classmethod
def getupcomingcfrduerecords(cls):
sql = """select distinct on (filenumber) filenumber, cfrduedate, foiministryrequestid, version, foirequest_id, created_at, createdby from "FOIMinistryRequests" fpa
where isactive = true and cfrduedate is not null and requeststatusid = 2
and cfrduedate between NOW() - INTERVAL '7 DAY' AND NOW() + INTERVAL '7 DAY'
order by filenumber , version desc;"""
rs = db.session.execute(text(sql))
upcomingduerecords = []
for row in rs:
upcomingduerecords.append({
"filenumber":
row["filenumber"],
"cfrduedate":
row["cfrduedate"],
"foiministryrequestid":
row["foiministryrequestid"],
"version":
row["version"],
"foirequest_id":
row["foirequest_id"],
"created_at":
row["created_at"],
"createdby":
row["createdby"]
})
return upcomingduerecords
@classmethod
def getupcominglegislativeduerecords(cls):
sql = """select distinct on (filenumber) filenumber, duedate, foiministryrequestid, version, foirequest_id, created_at, createdby from "FOIMinistryRequests" fpa
where isactive = true and duedate is not null and requeststatusid not in (5,6,4,11,3,15)
and duedate between NOW() - INTERVAL '7 DAY' AND NOW() + INTERVAL '7 DAY'
order by filenumber , version desc;"""
rs = db.session.execute(text(sql))
upcomingduerecords = []
for row in rs:
upcomingduerecords.append({
"filenumber":
row["filenumber"],
"duedate":
row["duedate"],
"foiministryrequestid":
row["foiministryrequestid"],
"version":
row["version"],
"foirequest_id":
row["foirequest_id"],
"created_at":
row["created_at"],
"createdby":
row["createdby"]
})
return upcomingduerecords
@classmethod
def updateduedate(cls, ministryrequestid, duedate,
userid) -> DefaultMethodResult:
currequest = db.session.query(FOIMinistryRequest).filter_by(
foiministryrequestid=ministryrequestid).order_by(
FOIMinistryRequest.version.desc()).first()
setattr(currequest, 'duedate', duedate)
setattr(currequest, 'updated_at', datetime.now().isoformat())
setattr(currequest, 'updatedby', userid)
db.session.commit()
return DefaultMethodResult(True, 'Request updated', ministryrequestid)
@classmethod
def getministriesopenedbyuid(cls, rawrequestid):
sql = """select distinct filenumber, foiministryrequestid, foirequest_id, pa."name" from "FOIMinistryRequests" fpa
inner join "FOIRequests" frt on fpa.foirequest_id = frt.foirequestid and fpa.foirequestversion_id = frt."version"
inner join "ProgramAreas" pa on fpa.programareaid = pa.programareaid
where fpa.isactive = true and frt.isactive =true and frt.foirawrequestid=:rawrequestid;"""
rs = db.session.execute(text(sql), {'rawrequestid': rawrequestid})
ministries = []
for row in rs:
ministries.append({
"filenumber": row["filenumber"],
"name": row["name"],
"requestid": row["foirequest_id"],
"ministryrequestid": row["foiministryrequestid"]
})
return ministries
@classmethod
def getbasequery(cls, iaoassignee, ministryassignee):
_session = db.session
#ministry filter for group/team
ministryfilter = and_(FOIMinistryRequest.isactive == True,
FOIRequestStatus.isactive == True)
#subquery for getting latest version & proper group/team for FOIMinistryRequest
subquery_ministry_maxversion = _session.query(
FOIMinistryRequest.foiministryrequestid,
func.max(
FOIMinistryRequest.version).label('max_version')).group_by(
FOIMinistryRequest.foiministryrequestid).subquery()
joincondition_ministry = [
subquery_ministry_maxversion.c.foiministryrequestid ==
FOIMinistryRequest.foiministryrequestid,
subquery_ministry_maxversion.c.max_version ==
FOIMinistryRequest.version,
]
#subquery for getting the first applicant mapping
subquery_applicantmapping_first = _session.query(
FOIRequestApplicantMapping.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id,
func.min(FOIRequestApplicantMapping.foirequestapplicantid).
label('first_id')).group_by(
FOIRequestApplicantMapping.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id).subquery()
joincondition_applicantmapping = [
subquery_applicantmapping_first.c.foirequest_id ==
FOIRequestApplicantMapping.foirequest_id,
subquery_applicantmapping_first.c.foirequestversion_id ==
FOIRequestApplicantMapping.foirequestversion_id,
subquery_applicantmapping_first.c.first_id ==
FOIRequestApplicantMapping.foirequestapplicantid,
]
selectedcolumns = [
FOIRequest.foirequestid.label('id'), FOIMinistryRequest.version,
literal(None).label('sourceofsubmission'),
FOIRequestApplicant.firstname.label('firstName'),
FOIRequestApplicant.lastname.label('lastName'),
FOIRequest.requesttype.label('requestType'),
cast(FOIRequest.receiveddate, String).label('receivedDate'),
cast(FOIRequest.receiveddate, String).label('receivedDateUF'),
FOIRequestStatus.name.label('currentState'),
FOIMinistryRequest.assignedgroup.label('assignedGroup'),
FOIMinistryRequest.assignedto.label('assignedTo'),
cast(FOIMinistryRequest.filenumber, String).label('idNumber'),
FOIMinistryRequest.foiministryrequestid.label('ministryrequestid'),
FOIMinistryRequest.assignedministrygroup.label(
'assignedministrygroup'),
FOIMinistryRequest.assignedministryperson.label(
'assignedministryperson'),
cast(FOIMinistryRequest.cfrduedate, String).label('cfrduedate'),
cast(FOIMinistryRequest.duedate, String).label('duedate'),
ApplicantCategory.name.label('applicantcategory'),
FOIRequest.created_at.label('created_at'),
func.lower(ProgramArea.bcgovcode).label('bcgovcode'),
iaoassignee.firstname.label('assignedToFirstName'),
iaoassignee.lastname.label('assignedToLastName'),
ministryassignee.firstname.label(
'assignedministrypersonFirstName'),
ministryassignee.lastname.label('assignedministrypersonLastName'),
FOIMinistryRequest.description
]
basequery = _session.query(*selectedcolumns).join(
subquery_ministry_maxversion, and_(*joincondition_ministry)
).join(
FOIRequest,
and_(FOIRequest.foirequestid == FOIMinistryRequest.foirequest_id,
FOIRequest.version == FOIMinistryRequest.foirequestversion_id)
).join(
FOIRequestStatus, FOIRequestStatus.requeststatusid ==
FOIMinistryRequest.requeststatusid).join(
FOIRequestApplicantMapping,
and_(
FOIRequestApplicantMapping.foirequest_id ==
FOIMinistryRequest.foirequest_id,
FOIRequestApplicantMapping.foirequestversion_id ==
FOIMinistryRequest.foirequestversion_id)
).join(subquery_applicantmapping_first,
and_(*joincondition_applicantmapping)).join(
FOIRequestApplicant,
FOIRequestApplicant.foirequestapplicantid ==
FOIRequestApplicantMapping.foirequestapplicantid).join(
ApplicantCategory,
and_(
ApplicantCategory.applicantcategoryid ==
FOIRequest.applicantcategoryid,
ApplicantCategory.isactive == True)
).join(
ProgramArea,
FOIMinistryRequest.programareaid ==
ProgramArea.programareaid).join(
iaoassignee,
iaoassignee.username ==
FOIMinistryRequest.assignedto,
isouter=True).join(
ministryassignee,
ministryassignee.username ==
FOIMinistryRequest.assignedministryperson,
isouter=True)
return basequery.filter(ministryfilter)
@classmethod
def advancedsearch(cls, params, iaoassignee, ministryassignee):
basequery = FOIMinistryRequest.getbasequery(iaoassignee,
ministryassignee)
#filter/search
filtercondition = FOIMinistryRequest.getfilterforadvancedsearch(
params, iaoassignee, ministryassignee)
return basequery.filter(and_(*filtercondition))
@classmethod
def getfilterforadvancedsearch(cls, params, iaoassignee, ministryassignee):
#filter/search
filtercondition = []
includeclosed = False
#request state: unopened, call for records, etc.
if (len(params['requeststate']) > 0):
requeststatecondition = FOIMinistryRequest.getfilterforrequeststate(
params, includeclosed)
filtercondition.append(requeststatecondition['condition'])
includeclosed = requeststatecondition['includeclosed']
#request status: overdue || on time
if (len(params['requeststatus']) == 1):
requeststatuscondition = FOIMinistryRequest.getfilterforrequeststatus(
params, iaoassignee, ministryassignee)
filtercondition.append(requeststatuscondition)
# return all except closed
if (includeclosed == False):
filtercondition.append(FOIMinistryRequest.requeststatusid != 3)
elif (len(params['requeststatus']) > 1 and includeclosed == False):
# return all except closed
filtercondition.append(FOIMinistryRequest.requeststatusid != 3)
#request type: personal, general
if (len(params['requesttype']) > 0):
requesttypecondition = FOIMinistryRequest.getfilterforrequesttype(
params, iaoassignee, ministryassignee)
filtercondition.append(requesttypecondition)
#public body: EDUC, etc.
if (len(params['publicbody']) > 0):
publicbodycondition = FOIMinistryRequest.getfilterforpublicbody(
params, iaoassignee, ministryassignee)
filtercondition.append(publicbodycondition)
#axis request #, raw request #, applicant name, assignee name, request description, subject code
if (len(params['keywords']) > 0 and params['search'] is not None):
searchcondition = FOIMinistryRequest.getfilterforsearch(
params, iaoassignee, ministryassignee)
filtercondition.append(searchcondition)
if (params['fromdate'] is not None):
filtercondition.append(
FOIMinistryRequest.findfield('receivedDate', iaoassignee,
ministryassignee) >=
params['fromdate'])
if (params['todate'] is not None):
filtercondition.append(
FOIMinistryRequest.findfield('duedate', iaoassignee,
ministryassignee) <=
params['todate'])
return filtercondition
@classmethod
def getfilterforrequeststate(cls, params, includeclosed):
#request state: unopened, call for records, etc.
requeststatecondition = []
for stateid in params['requeststate']:
requeststatecondition.append(
FOIMinistryRequest.requeststatusid == stateid)
if (stateid == 3):
includeclosed = True
return {
'condition': or_(*requeststatecondition),
'includeclosed': includeclosed
}
@classmethod
def getfilterforrequeststatus(cls, params, iaoassignee, ministryassignee):
#request status: overdue || on time
if (params['requeststatus'][0] == 'overdue'):
return FOIMinistryRequest.findfield(
'duedate', iaoassignee, ministryassignee) < datetime.now()
else:
return FOIMinistryRequest.findfield(
'duedate', iaoassignee, ministryassignee) >= datetime.now()
@classmethod
def getfilterforrequesttype(cls, params, iaoassignee, ministryassignee):
#request type: personal, general
requesttypecondition = []
for type in params['requesttype']:
requesttypecondition.append(
FOIMinistryRequest.findfield('requestType', iaoassignee,
ministryassignee) == type)
return or_(*requesttypecondition)
@classmethod
def getfilterforpublicbody(cls, params, iaoassignee, ministryassignee):
#public body: EDUC, etc.
publicbodycondition = []
for ministry in params['publicbody']:
publicbodycondition.append(
FOIMinistryRequest.findfield('ministry', iaoassignee,
ministryassignee) == ministry)
return or_(*publicbodycondition)
@classmethod
def getfilterforsearch(cls, params, iaoassignee, ministryassignee):
#axis request #, raw request #, applicant name, assignee name, request description, subject code
if (len(params['keywords']) > 0 and params['search'] is not None):
if (params['search'] == 'applicantname'):
searchcondition1 = []
searchcondition2 = []
for keyword in params['keywords']:
searchcondition1.append(
FOIMinistryRequest.findfield(
'firstName', iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
searchcondition2.append(
FOIMinistryRequest.findfield(
'lastName', iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
return or_(and_(*searchcondition1), and_(*searchcondition2))
elif (params['search'] == 'assigneename'):
searchcondition1 = []
searchcondition2 = []
for keyword in params['keywords']:
searchcondition1.append(
FOIMinistryRequest.findfield(
'assignedToFirstName', iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
searchcondition2.append(
FOIMinistryRequest.findfield(
'assignedToLastName', iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
return or_(and_(*searchcondition1), and_(*searchcondition2))
else:
searchcondition = []
for keyword in params['keywords']:
searchcondition.append(
FOIMinistryRequest.findfield(
params['search'], iaoassignee,
ministryassignee).ilike('%' + keyword + '%'))
return and_(*searchcondition)