예제 #1
0
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
예제 #2
0
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,
    )
예제 #3
0
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()
예제 #4
0
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)
예제 #5
0
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])
예제 #6
0
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
예제 #7
0
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"},
    )
예제 #8
0
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
        }
예제 #9
0
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)   
예제 #10
0
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
예제 #11
0
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"},
    )
예제 #12
0
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
예제 #13
0
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
예제 #14
0
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
예제 #15
0
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']), {})
예제 #16
0
 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],
         ),
     )
예제 #17
0
파일: pickup.py 프로젝트: jaunis/xivo-dao
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')
예제 #18
0
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)
예제 #19
0
    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"))
예제 #20
0
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
예제 #22
0
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),
    )
예제 #23
0
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)
예제 #24
0
파일: schedule.py 프로젝트: jaunis/xivo-dao
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')
예제 #25
0
  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:
예제 #27
0
    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
예제 #28
0
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)
예제 #29
0
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)