class ContractNote(Model):
'''Model for contract notes
Attributes:
id: Primary key unique ID
contract: Sqlalchemy relationship to
:py:class:`~purchasing.data.contracts.ContractBase`
contract_id: Foreign key to
:py:class:`~purchasing.data.contracts.ContractBase`
note: Text of the note to be taken
taken_by_id: Foreign key to
:py:class:`~purchasing.users.models.User`
taken_by: Sqlalchemy relationship to
:py:class:`~purchasing.users.models.User`
'''
__tablename__ = 'contract_note'
id = Column(db.Integer, primary_key=True, index=True)
contract = db.relationship('ContractBase',
backref=backref('notes',
lazy='dynamic',
cascade='all, delete-orphan'))
contract_id = ReferenceCol('contract', ondelete='CASCADE')
note = Column(db.Text)
taken_by_id = ReferenceCol('users', ondelete='SET NULL', nullable=True)
taken_by = db.relationship('User',
backref=backref('contract_note',
lazy='dynamic',
cascade=None),
foreign_keys=taken_by_id)
def __unicode__(self):
return self.note
class Stage(Model):
'''Model for individual conductor stages
Attributes:
id: Primary key unique ID
name: Name of the stage
post_opportunities: Whether you can post
:py:class:`~purchasing.opportunities.models.Opportunity`
objects to :doc:`/beacon` from this stage
default_message: Message to autopopulate the
:py:class:`~purchasing.conductor.forms.SendUpdateForm`
message body
'''
__tablename__ = 'stage'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(255))
post_opportunities = Column(db.Boolean, default=False, nullable=False)
default_message = Column(db.Text)
def __unicode__(self):
return self.name
@classmethod
def choices_factory(cls):
'''Return a two-tuple of (stage id, stage name) for all stages
'''
return [(i.id, i.name) for i in cls.query.all()]
class ContractStageActionItem(Model):
__tablename__ = 'contract_stage_action_item'
id = Column(db.Integer, primary_key=True, index=True)
contract_stage_id = ReferenceCol('contract_stage', ondelete='CASCADE', index=True)
contract_stage = db.relationship('ContractStage', backref=backref(
'contract_stage_actions', lazy='dynamic', cascade='all, delete-orphan'
))
action_type = Column(db.String(255))
action_detail = Column(JSON)
taken_at = Column(db.DateTime, default=datetime.datetime.now())
taken_by = ReferenceCol('users', ondelete='SET NULL', nullable=True)
def __unicode__(self):
return self.action
def get_sort_key(self):
# if we are reversion, we need to get the timestamps from there
if self.action_type == 'reversion':
return datetime.datetime.strptime(
self.action_detail['timestamp'],
'%Y-%m-%dT%H:%M:%S'
)
# otherwise, return the taken_at time
else:
return self.taken_at if self.taken_at else datetime.datetime(1970, 1, 1)
class Company(RefreshSearchViewMixin, Model):
'''Model for individual Compnaies
Attributes:
id: Primary key unique ID
company_name: Name of the company
contracts: Many-to-many relationship with the :py:class:`
purchasing.data.contracts.ContractBase` model
'''
__tablename__ = 'company'
id = Column(db.Integer, primary_key=True, index=True)
company_name = Column(db.String(255),
nullable=False,
unique=True,
index=True)
contracts = db.relationship(
'ContractBase',
secondary=company_contract_association_table,
backref='companies',
)
def __repr__(self):
return self.company_name
def __unicode__(self):
return self.company_name
@classmethod
def all_companies_query_factory(cls):
'''Query factory of all company ids and names ordered by name
'''
return db.session.query(
db.distinct(cls.id).label('id'),
cls.company_name).order_by(cls.company_name)
class ContractProperty(RefreshSearchViewMixin, Model):
'''Model for contract properties
The contract property model effectively serves as a key-value
storage unit for properties that exist on a subset of contracts.
For example, a common unit for County contracts is the so-called
"spec number", an identified used by Allegheny County for their
electronic bidding system. Other contract types (such as PA state and
COSTARS contracts), do not have this property but do have others
(such as manufacturers offered, etc.). Therefore, we use this
model as an extended key-value store for the base
:py:class:`~purchasing.data.contracts.ContractBase` model
Attributes:
id: Primary key unique ID
contract: Sqlalchemy relationship to
:py:class:`~purchasing.data.contracts.ContractBase`
contract_id: Foreign key to
:py:class:`~purchasing.data.contracts.ContractBase`
key: The key for the property (for example, Spec Number)
value: The value for the property (for example, 7137)
'''
__tablename__ = 'contract_property'
id = Column(db.Integer, primary_key=True, index=True)
contract = db.relationship('ContractBase',
backref=backref('properties',
lazy='joined',
cascade='all, delete-orphan'))
contract_id = ReferenceCol('contract', ondelete='CASCADE')
key = Column(db.String(255), nullable=False)
value = Column(db.Text)
def __unicode__(self):
return u'{key}: {value}'.format(key=self.key, value=self.value)
class AppStatus(Model):
__tablename__ = 'app_status'
id = Column(db.Integer, primary_key=True)
status = Column(db.String(255))
last_updated = Column(db.DateTime)
county_max_deadline = Column(db.DateTime)
message = Column(db.Text)
class Stage(Model):
__tablename__ = 'stage'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(255))
send_notifs = Column(db.Boolean, default=False, nullable=False)
post_opportunities = Column(db.Boolean, default=False, nullable=False)
def __unicode__(self):
return self.name
class Flow(Model):
__tablename__ = 'flow'
id = Column(db.Integer, primary_key=True, index=True)
flow_name = Column(db.Text, unique=True)
contract = db.relationship('ContractBase', backref='flow', lazy='subquery')
stage_order = Column(ARRAY(db.Integer))
def __unicode__(self):
return self.flow_name
class Category(Model):
__tablename__ = 'category'
id = Column(db.Integer, primary_key=True, index=True)
nigp_code = Column(db.Integer)
category = Column(db.String(255))
subcategory = Column(db.String(255))
def __unicode__(self):
return '{sub} (in {main})'.format(sub=self.subcategory,
main=self.category)
class Company(Model):
__tablename__ = 'company'
id = Column(db.Integer, primary_key=True, index=True)
company_name = Column(db.String(255), nullable=False, unique=True, index=True)
contracts = db.relationship(
'ContractBase',
secondary=company_contract_association_table,
backref='companies',
)
def __unicode__(self):
return self.company_name
class StageProperty(Model):
__tablename__ = 'stage_property'
id = Column(db.Integer, primary_key=True, index=True)
stage = db.relationship('Stage', backref=backref(
'properties', lazy='dynamic', cascade='all, delete-orphan'
))
stage_id = ReferenceCol('stage', ondelete='CASCADE')
key = Column(db.String(255), nullable=False)
value = Column(db.String(255))
def __unicode__(self):
return '{key}: {value}'.format(key=self.key, value=self.value)
class ContractNote(Model):
__tablename__ = 'contract_note'
id = Column(db.Integer, primary_key=True, index=True)
contract = db.relationship('ContractBase', backref=backref(
'notes', lazy='dynamic', cascade='all, delete-orphan'
))
contract_id = ReferenceCol('contract', ondelete='CASCADE')
note = Column(db.Text)
created_at = Column(db.DateTime, default=datetime.datetime.utcnow())
updated_at = Column(db.DateTime, default=datetime.datetime.utcnow(), onupdate=db.func.now())
def __unicode__(self):
return self.note
class ContractType(Model):
'''Model for contract types
Attributes:
id: Primary key unique ID
name: Name of the contract type
allow_opportunities: Boolean flag as to whether to allow
opportunities to be posted
managed_by_conductor: Boolean flag as to whether contracts
of these types are managed through :doc:`/conductor`
opportunity_response_instructions: HTML string of instructions
for bidders on how to respond to opportunities of this
type
'''
__tablename__ = 'contract_type'
id = Column(db.Integer, primary_key=True, index=True)
name = Column(db.String(255))
allow_opportunities = Column(db.Boolean, default=False)
managed_by_conductor = Column(db.Boolean, default=False)
opportunity_response_instructions = Column(db.Text)
def __unicode__(self):
return self.name if self.name else ''
@classmethod
def opportunity_type_query(cls):
'''Query factory filtered to include only types that allow opportunities
'''
return cls.query.filter(cls.allow_opportunities == True)
@classmethod
def query_factory_all(cls):
'''Query factory to return all contract types
'''
return cls.query.order_by(cls.name)
@classmethod
def get_type(cls, type_name):
'''Get an individual type based on a passed type name
Arguments:
type_name: Name of the type to look up
Returns:
One :py:class:`~purchasing.data.contracts.ContractType` object
'''
return cls.query.filter(
db.func.lower(cls.name) == type_name.lower()).first()
class Role(SurrogatePK, Model):
'''Model to handle view-based permissions
Attributes:
id: primary key
name: role name
'''
__tablename__ = 'roles'
name = Column(db.String(80), unique=True, nullable=False)
def __repr__(self):
return '<Role({name})>'.format(name=self.name)
def __unicode__(self):
return self.name
@classmethod
def query_factory(cls):
'''Generates a query of all roles
Returns:
`sqla query`_ of all roles
'''
return cls.query
@classmethod
def no_admins(cls):
'''Generates a query of non-admin roles
Returns:
`sqla query`_ of roles without administrative access
'''
return cls.query.filter(cls.name != 'superadmin')
class RequiredBidDocument(Model):
'''Model for documents that a vendor would be required to provide
There are two types of documents associated with an opportunity -- documents
that the City will provide (RFP/IFB/RFQ, Q&A documents, etc.), and documents
that the bidder will need to provide upon bidding (Insurance certificates,
Bid bonds, etc.). This model describes the latter.
See Also:
These models get rendered into a select multi with the descriptions rendered
in tooltips. For more on how this works, see the
:py:func:`~purchasing.opportunities.utils.select_multi_checkbox`.
Attributes:
id: Primary key unique ID
display_name: Display name for the document
description: Description of what the document is, rendered in a tooltip
form_href: A link to an example document
'''
__tablename__ = 'document'
id = Column(db.Integer, primary_key=True, index=True)
display_name = Column(db.String(255), nullable=False)
description = Column(db.Text, nullable=False)
form_href = Column(db.String(255))
def get_choices(self):
'''Builds a custom two-tuple for the CHOICES.
Returns:
Two-tuple of (ID, [name, description, href]), which can then be
passed to :py:func:`~purchasing.opportunities.utils.select_multi_checkbox`
to generate multi-checkbox fields
'''
return (self.id, [self.display_name, self.description, self.form_href])
@classmethod
def generate_choices(cls):
'''Builds a list of custom CHOICES
Returns:
List of two-tuples described in the
:py:meth:`RequiredBidDocument.get_choices`
method
'''
return [i.get_choices() for i in cls.query.all()]
class ContractBase(Model):
__tablename__ = 'contract'
id = Column(db.Integer, primary_key=True)
financial_id = Column(db.Integer)
created_at = Column(db.DateTime, default=datetime.datetime.utcnow())
updated_at = Column(db.DateTime, default=datetime.datetime.utcnow(), onupdate=db.func.now())
contract_type = Column(db.String(255))
expiration_date = Column(db.Date)
description = Column(db.Text, index=True)
contract_href = Column(db.Text)
current_flow = db.relationship('Flow', lazy='subquery')
flow_id = ReferenceCol('flow', ondelete='SET NULL', nullable=True)
current_stage = db.relationship('Stage', lazy='subquery')
current_stage_id = ReferenceCol('stage', ondelete='SET NULL', nullable=True)
followers = db.relationship(
'User',
secondary=contract_user_association_table,
backref='contracts_following',
)
starred = db.relationship(
'User',
secondary=contract_starred_association_table,
backref='contracts_starred',
)
assigned_to = ReferenceCol('users', ondelete='SET NULL', nullable=True)
assigned = db.relationship('User', backref=backref(
'assignments', lazy='dynamic', cascade='none'
))
is_archived = Column(db.Boolean, default=False, nullable=False)
parent_id = Column(db.Integer, db.ForeignKey('contract.id'))
children = db.relationship('ContractBase', backref=backref(
'parent', remote_side=[id]
))
def __unicode__(self):
return self.description
def get_spec_number(self):
'''Returns the spec number for a given contract
'''
try:
return [i for i in self.properties if i.key.lower() == 'spec number'][0]
except IndexError:
return ContractProperty()
class LineItem(RefreshSearchViewMixin, Model):
'''Model for contract line items
Attributes:
id: Primary key unique ID
contract: Sqlalchemy relationship to
:py:class:`~purchasing.data.contracts.ContractBase`
contract_id: Foreign key to
:py:class:`~purchasing.data.contracts.ContractBase`
description: Description of the line item in question
manufacturer: Name of the manufacturer of the line item
model_number: A model number for the item
quantity: The quantity of the item on contract
unit_of_measure: The unit of measure (for example EACH)
unit_cost: Cost on a per-unit basis
total_cost: Total cost (unit_cost * quantity)
percentage: Whether or not the unit cost should be represented
as a percentage (NOTE: on the BidNet system, there is no
differentiation between a percentage discount off of an item
and actual unit cost for an item)
company_name: Name of the company that is providing the good
company_id: Foreign key to
:py:class:`~purchasing.data.companies.Company`
'''
__tablename__ = 'line_item'
id = Column(db.Integer, primary_key=True, index=True)
contract = db.relationship('ContractBase',
backref=backref('line_items',
lazy='dynamic',
cascade='all, delete-orphan'))
contract_id = ReferenceCol('contract', ondelete='CASCADE')
description = Column(db.Text, nullable=False, index=True)
manufacturer = Column(db.Text)
model_number = Column(db.Text)
quantity = Column(db.Integer)
unit_of_measure = Column(db.String(255))
unit_cost = Column(db.Float)
total_cost = Column(db.Float)
percentage = Column(db.Boolean)
company_name = Column(db.String(255), nullable=True)
company_id = ReferenceCol('company', nullable=True)
def __unicode__(self):
return self.description
class Role(SurrogatePK, RoleMixin, Model):
'''Model to handle view-based permissions
Attributes:
id: primary key
name: role name
description: description of an individual role
'''
__tablename__ = 'roles'
name = Column(db.String(80), unique=True, nullable=False)
description = Column(db.String(255), nullable=True)
def __repr__(self):
return '<Role({name})>'.format(name=self.name)
def __unicode__(self):
return self.name
@classmethod
def query_factory(cls):
'''Generates a query of all roles
Returns:
`sqla query`_ of all roles
'''
return cls.query
@classmethod
def no_admins(cls):
'''Generates a query of non-admin roles
Returns:
`sqla query`_ of roles without administrative access
'''
return cls.query.filter(cls.name != 'superadmin')
@classmethod
def staff_factory(cls):
'''Factory to return the staff role
Returns:
Role object with the name 'staff'
'''
return cls.query.filter(cls.name == 'staff')
class Role(SurrogatePK, Model):
__tablename__ = 'roles'
name = Column(db.String(80), unique=True, nullable=False)
users = db.relationship('User', lazy='dynamic', backref='role')
def __repr__(self):
return '<Role({name})>'.format(name=self.name)
def __unicode__(self):
return self.name
class OpportunityDocument(Model):
'''Model for bid documents associated with opportunities
Attributes:
id: Primary key unique ID
opportunity_id: Foreign Key relationship back to the related
:py:class:`~purchasing.opportunities.models.Opportunity`
opportunity: Sqlalchemy relationship back to the related
:py:class:`~purchasing.opportunities.models.Opportunity`
name: Name of the document for display
href: Link to the document
'''
__tablename__ = 'opportunity_document'
id = Column(db.Integer, primary_key=True, index=True)
opportunity_id = ReferenceCol('opportunity', ondelete='cascade')
opportunity = db.relationship('Opportunity',
backref=backref(
'opportunity_documents',
lazy='dynamic',
cascade='all, delete-orphan'))
name = Column(db.String(255))
href = Column(db.Text())
def get_href(self):
'''Builds link to the file
Returns:
S3 link if using S3, local filesystem link otherwise
'''
if current_app.config['UPLOAD_S3']:
return self.href
else:
if self.href.startswith('http'):
return self.href
return 'file://{}'.format(self.href)
def clean_name(self):
'''Replaces underscores with spaces
'''
return self.name.replace('_', ' ')
class LineItem(Model):
__tablename__ = 'line_item'
id = Column(db.Integer, primary_key=True, index=True)
contract = db.relationship('ContractBase', backref=backref(
'line_items', lazy='dynamic', cascade='all, delete-orphan'
))
contract_id = ReferenceCol('contract', ondelete='CASCADE')
description = Column(db.Text, nullable=False, index=True)
manufacturer = Column(db.Text)
model_number = Column(db.Text)
quantity = Column(db.Integer)
unit_of_measure = Column(db.String(255))
unit_cost = Column(db.Float)
total_cost = Column(db.Float)
percentage = Column(db.Boolean)
company_name = Column(db.String(255), nullable=True)
company_id = ReferenceCol('company', nullable=True)
def __unicode__(self):
return self.description
class FakeModel(RefreshSearchViewMixin, Model):
__tablename__ = 'fakefake'
__table_args__ = {'extend_existing': True}
id = Column(db.Integer, primary_key=True)
description = Column(db.String(255))
def __init__(self, *args, **kwargs):
super(FakeModel, self).__init__(*args, **kwargs)
@classmethod
def record_called(cls):
cls.called = True
@classmethod
def reset_called(cls):
cls.called = False
@classmethod
def event_handler(cls, *args, **kwargs):
return cls.record_called()
class User(UserMixin, SurrogatePK, Model):
__tablename__ = 'users'
email = Column(db.String(80), unique=True, nullable=False, index=True)
created_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
first_name = Column(db.String(30), nullable=True)
last_name = Column(db.String(30), nullable=True)
department = Column(db.String(255), nullable=False)
active = Column(db.Boolean(), default=True)
role_id = ReferenceCol('roles', ondelete='SET NULL', nullable=True)
@property
def full_name(self):
return "{0} {1}".format(self.first_name, self.last_name)
def __repr__(self):
return '<User({email!r})>'.format(email=self.email)
def __unicode__(self):
return self.email
def get_starred(self):
return [i.id for i in self.contracts_starred]
def get_following(self):
return [i.id for i in self.contracts_following]
class Department(SurrogatePK, Model):
'''Department model
Attributes:
name: Name of department
'''
__tablename__ = 'department'
name = Column(db.String(255), nullable=False, unique=True)
def __unicode__(self):
return self.name
@classmethod
def query_factory(cls):
'''Generate a department query factory.
Returns:
Department query with new users filtered out
'''
return cls.query.filter(cls.name != 'New User')
@classmethod
def get_dept(cls, dept_name):
'''Query Department by name.
Arguments:
dept_name: name used for query
Returns:
an instance of Department
'''
return cls.query.filter(
db.func.lower(cls.name) == dept_name.lower()).first()
@classmethod
def choices(cls, blank=False):
'''Query available departments by name and id.
Arguments:
blank: adds none choice to list when True,
only returns Departments when False. Defaults to False.
Returns:
list of (department id, department name) tuples
'''
departments = [(i.id, i.name) for i in cls.query_factory().all()]
if blank:
departments = [(None, '-----')] + departments
return departments
class AcceptedEmailDomains(Model):
'''Model of permitted email domains for new user creation
Because authentication is handled by `persona
<https://login.persona.org/about>`_, we still need to control
some level of authorization. We do this on two levels. First,
we use Role-based permissions using the :py:class:`~purchasing.data.models.Role`
class and the :py:func:`~purchasing.decorators.requires_roles` method.
We also do this by restricting new user creation to people who have
a certain set of email domains.
See Also:
:ref:`persona`
Attributes:
id (int): Primary key
domain (str): string of an acceptable domain (for example, ``pittsburghpa.gov``)
'''
__tablename__ = 'accepted_domains'
id = Column(db.Integer, primary_key=True)
domain = Column(db.String(255), unique=True)
@classmethod
def valid_domain(cls, domain_to_lookup):
'''Check if a domain is in the valid domains
Args:
domain_to_lookup (str): string of domain to be checked
Returns:
bool: True if domain is valid, False otherwise
'''
return cls.query.filter(
str(domain_to_lookup).lower() == db.func.lower(
cls.domain)).count() > 0
class Category(Model):
'''Category model for opportunities and Vendor signups
Categories are based on the codes created by the `National Institute
of Government Purchasing (NIGP) <http://www.nigp.org/eweb/StartPage.aspx>`_.
The names of the categories have been re-written a bit to make them more
human-readable and in some cases a bit more modern.
Attributes:
id: Primary key unique ID
nigp_codes: Array of integers refering to NIGP codes.
category: parent top-level category
subcategory: NIGP designated subcategory name
category_friendly_name: Rewritten, more human-readable subcategory name
examples: Pipe-delimited examples of items that fall in each subcategory
examples_tsv: TSVECTOR of the examples for that subcategory
See Also:
The :ref:`nigp-importer` contains more information about how NIGP codes
are imported into the system.
'''
__tablename__ = 'category'
id = Column(db.Integer, primary_key=True, index=True)
nigp_codes = Column(ARRAY(db.Integer()))
category = Column(db.String(255))
subcategory = Column(db.String(255))
category_friendly_name = Column(db.Text)
examples = Column(db.Text)
examples_tsv = Column(TSVECTOR)
def __unicode__(self):
return u'{sub} (in {main})'.format(sub=self.category_friendly_name,
main=self.category)
@classmethod
def parent_category_query_factory(cls):
'''Query factory to return a query of all of the distinct top-level categories
'''
return db.session.query(db.distinct(
cls.category).label('category')).order_by('category')
@classmethod
def query_factory(cls):
'''Query factory that returns all category/subcategory pairs
'''
return cls.query
class ContractStage(Model):
__tablename__ = 'contract_stage'
id = Column(
db.Integer, Sequence('autoincr_contract_stage_id', start=1, increment=1),
index=True, unique=True
)
contract_id = ReferenceCol('contract', ondelete='CASCADE', index=True, primary_key=True)
contract = db.relationship('ContractBase', backref=backref(
'stages', lazy='dynamic', cascade='all, delete-orphan'
))
stage_id = ReferenceCol('stage', ondelete='CASCADE', index=True, primary_key=True)
stage = db.relationship('Stage', backref=backref(
'contracts', lazy='dynamic', cascade='all, delete-orphan'
))
created_at = Column(db.DateTime, default=datetime.datetime.now())
updated_at = Column(db.DateTime, default=datetime.datetime.now(), onupdate=datetime.datetime.now())
entered = Column(db.DateTime)
exited = Column(db.DateTime)
notes = Column(db.Text)
def enter(self):
'''Enter the stage at this point
'''
self.entered = datetime.datetime.now()
def exit(self):
'''Exit the stage
'''
self.exited = datetime.datetime.now()
def full_revert(self):
'''Clear timestamps for both enter and exit
'''
self.entered = None
self.exited = None
def is_current_stage(self):
'''Checks to see if this is the current stage
'''
return True if self.entered and not self.exited else False
class AppStatus(Model):
'''Model of current application status
Attributes:
id: Primary key
status: Current application status
last_updated: Datetime of the last time the status was updated
county_max_deadline: Datetime of the last time the county scraper
was updated
message: If the status is an error, the message will have more
information about the nature of the error
last_beacon_newsletter: Datetime of the last time a beacon
newsletter was sent
'''
__tablename__ = 'app_status'
id = Column(db.Integer, primary_key=True)
status = Column(db.String(255))
last_updated = Column(db.DateTime)
county_max_deadline = Column(db.DateTime)
message = Column(db.Text)
last_beacon_newsletter = Column(db.DateTime)
class Opportunity(Model):
__tablename__ = 'opportunity'
id = Column(db.Integer, primary_key=True)
contract_id = ReferenceCol('contract', ondelete='cascade')
created_at = Column(db.DateTime, default=datetime.datetime.utcnow())
# Also the opportunity open date
title = Column(db.String(255))
department = Column(db.String(255))
# Autopopulated using title and department plus boilerplate copy?
description = Column(db.Text)
category_id = ReferenceCol('category', ondelete='SET NULL')
category = db.relationship('Category', lazy='subquery')
# Date department opens bids
bid_open = Column(db.DateTime)
# Created from contract
created_from = db.relationship('ContractBase',
lazy='subquery',
backref='opportunities')
class Flow(Model):
'''Model for flows
A Flow is the series of :py:class:`~purchasing.data.stages.Stage` objects
that a contract will go through as part of Conductor. It is meant to be
as configurable and flexible as possible. Because of the nature of Flows,
it is best to not allow them to be edited or deleted once they are in use.
Instead, there is an ``is_archived`` flag. This is because of the difficulty
of knowing how to handle contracts that are currently in the middle of a flow
if that flow is edited. Instead, it is better to create a new flow.
Attributes:
id: Primary key unique ID
flow_name: Name of this flow
contract: Many-to-one relationship with
:py:class:`~purchasing.data.contracts.ContractBase` (many
contracts can share a flow)
stage_order: Array of stage_id integers
is_archived: Boolean of whether the flow is archived or active
'''
__tablename__ = 'flow'
id = Column(db.Integer, primary_key=True, index=True)
flow_name = Column(db.Text, unique=True)
contract = db.relationship('ContractBase', backref='flow', lazy='subquery')
stage_order = Column(ARRAY(db.Integer))
is_archived = Column(db.Boolean, default=False, nullable=False)
def __unicode__(self):
return self.flow_name
@classmethod
def all_flow_query_factory(cls):
'''Query factory that returns query of all flows
'''
return cls.query
@classmethod
def nonarchived_query_factory(cls):
'''Query factory that returns query of all non-archived flows
'''
return cls.query.filter(cls.is_archived == False)
def get_ordered_stages(self):
'''Turns the flow's stage_order attribute into Stage objects
Returns:
Ordered list of :py:class:`~purchasing.data.stages.Stage` objects
in the flow's ``stage_order``
'''
return [Stage.query.get(i) for i in self.stage_order]
def create_contract_stages(self, contract):
'''Creates new rows in contract_stage table.
Extracts the rows out of the given flow, and creates new rows
in the contract_stage table for each of them.
If the stages already exist, that means that the contract
is switching back into a flow that it had already been in.
To handle this, the "revert" flag is set to true, which
should signal to a downstream process to roll the stages
back to the first one in the current flow.
Arguments:
contract: A :py:class:`~purchasing.data.contracts.ContractBase` object
Returns:
A three-tuple of (the flow's stage order, a list of the flow's
:py:class:`~purchasing.data.contract_stages.ContractStage` objects,
whether the we are "reverting")
'''
revert = False
contract_stages = []
for stage_id in self.stage_order:
try:
contract_stages.append(
ContractStage.create(
contract_id=contract.id,
flow_id=self.id,
stage_id=stage_id,
))
except (IntegrityError, FlushError):
revert = True
db.session.rollback()
stage = ContractStage.query.filter(
ContractStage.contract_id == contract.id,
ContractStage.flow_id == self.id,
ContractStage.stage_id == stage_id).first()
if stage:
contract_stages.append(stage)
else:
raise IntegrityError
except Exception:
raise
contract.flow_id = self.id
db.session.commit()
return self.stage_order, contract_stages, revert
def _build_row(self, row, exited, data_dict):
try:
data_dict[row.contract_id]['stages'].append({
'name':
row.stage_name,
'id':
row.stage_id,
'entered':
localize_datetime(row.entered).isoformat(),
'exited':
localize_datetime(exited).isoformat(),
'seconds':
max([(exited - row.entered).total_seconds(), 0]),
})
except KeyError:
data_dict[row.contract_id] = {
'description':
row.description,
'email':
row.email,
'department':
row.department,
'contract_id':
row.contract_id,
'stages': [{
'name':
row.stage_name,
'id':
row.stage_id,
'entered':
localize_datetime(row.entered).isoformat(),
'exited':
localize_datetime(exited).isoformat(),
'seconds':
max([(exited - row.entered).total_seconds(), 0]),
}]
}
return data_dict
def build_metrics_data(self):
'''Build the raw data sets to be transformed client-side for metrics charts
Example:
.. code-block:: python
results = {
'current': { 'contract id': {
'description': 'a contract description',
'email': 'the contract is assigned to this email',
'department': 'the primary department for the contract',
'contract_id': 'the contract id',
'stages': [{
'name': 'the stage name', 'id': 'the stage id',
'entered': 'when the stage was entered',
'exited': 'when the stage was exited',
'seconds': 'number of seconds the contract spent in this stage',
}, ...]
}, ... },
'complete': { 'contract id': {
}, ... }
}
Returns:
A results dictionary described in the example above.
'''
raw_data = self.get_metrics_csv_data()
results = {'current': {}, 'complete': {}}
for ix, row in enumerate(raw_data):
exited = row.exited if row.exited else datetime.datetime.utcnow()
if row.exited is None and row.is_archived:
pass
elif row.exited is None:
results['current'] = self._build_row(row, exited,
results['current'])
else:
results['complete'] = self._build_row(row, exited,
results['complete'])
return results
def reshape_metrics_granular(self, enter_and_exit=False):
'''Transform long data from database into wide data for consumption
Take in a result set (list of tuples), return a dictionary of results.
The key for the dictionary is the contract id, and the values are a list
of (fieldname, value). Metadata (common to all rows) is listed first, and
timing information from each stage is listed afterwords. Sorting is assumed
to be done on the database layer
Arguments:
enter_and_exit: A boolean option of whether to add both the
enter and exit times to the results list
Returns:
* Results - a dictionary of lists which can be used to generate
a .csv or .tsv file to be downloaded by the client
* Headers - A list of strings which can be used to create the
headers for the downloadable file
'''
raw_data = self.get_metrics_csv_data()
results = defaultdict(list)
headers = []
for ix, row in enumerate(raw_data):
if ix == 0:
headers.extend([
'item_number', 'description', 'assigned_to', 'department'
])
# if this is a new contract row, append metadata
if len(results[row.contract_id]) == 0:
results[row.contract_id].extend([
row.contract_id,
row.description,
row.email,
row.department,
])
# append the stage date data
if enter_and_exit and row.exited:
results[row.contract_id].extend([
localize_datetime(row.exited),
localize_datetime(row.entered)
])
if row.stage_name + '_exit' not in headers:
headers.append(row.stage_name.replace(' ', '_') + '_exit')
headers.append(row.stage_name.replace(' ', '_') + '_enter')
else:
results[row.contract_id].extend(
[localize_datetime(row.exited)])
if row.stage_name not in headers:
headers.append(row.stage_name)
return results, headers
def get_metrics_csv_data(self):
'''Raw SQL query that returns the raw data to be reshaped for download or charting
'''
return db.session.execute(
'''
select
x.contract_id, x.description, x.department,
x.email, x.stage_name, x.rn, x.stage_id,
x.is_archived,
min(x.entered) as entered,
max(x.exited) as exited
from (
select
c.id as contract_id, c.description, d.name as department, c.is_archived,
u.email, s.name as stage_name, s.id as stage_id, cs.exited, cs.entered,
row_number() over (partition by c.id order by cs.entered asc, cs.id asc) as rn,
f.stage_order[s.id] as pos
from contract_stage cs
join stage s on cs.stage_id = s.id
join contract c on cs.contract_id = c.id
join users u on c.assigned_to = u.id
left join department d on c.department_id = d.id
join flow f on cs.flow_id = f.id
where cs.entered is not null
and cs.flow_id = :flow_id
and c.has_metrics is true
) x
group by 1,2,3,4,5,6,7,8, pos
order by contract_id, pos, rn asc
''', {
'flow_id': self.id
}).fetchall()
