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 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 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 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 CompanyContact(Model):
__tablename__ = 'company_contact'
id = Column(db.Integer, primary_key=True, index=True)
company = db.relationship(
'Company',
backref=backref('contacts', lazy='dynamic', cascade='all, delete-orphan')
)
company_id = ReferenceCol('company', ondelete='cascade')
first_name = Column(db.String(255))
last_name = Column(db.String(255))
addr1 = Column(db.String(255))
addr2 = Column(db.String(255))
city = Column(db.String(255))
state = Column(db.String(255))
zip_code = Column(db.Integer)
phone_number = Column(db.String(255))
fax_number = Column(db.String(255))
email = Column(db.String(255))
def __unicode__(self):
return '{first} {last} - {email}'.format(
first=self.first_name, last=self.last_name,
email=self.email
)
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 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 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 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 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 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 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 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 CompanyContact(RefreshSearchViewMixin, Model):
'''Model for Company Contacts
Attributes:
id: Primary key unique ID
company_id: Foreign key relationship to a :py:class:`~purchasing.data.companies.Company`
company: Sqlalchemy relationship with a :py:class:`~purchasing.data.companies.Company`
first_name: First name of the contact
last_name: Last name of the contact
addr1: First line of the contact's address
addr2: Second line of the contract's address
city: Contact address city
state: Contact address state
zip_code: Contact address zip code
phone_number: Contact phone number
fax_number: Contact fax number
email: Contact email
'''
__tablename__ = 'company_contact'
id = Column(db.Integer, primary_key=True, index=True)
company = db.relationship(
'Company',
backref=backref('contacts', lazy='dynamic', cascade='all, delete-orphan')
)
company_id = ReferenceCol('company', ondelete='cascade')
first_name = Column(db.String(255))
last_name = Column(db.String(255))
addr1 = Column(db.String(255))
addr2 = Column(db.String(255))
city = Column(db.String(255))
state = Column(db.String(255))
zip_code = Column(db.String(255))
phone_number = Column(db.String(255))
fax_number = Column(db.String(255))
email = Column(db.String(255))
def __unicode__(self):
return '{first} {last}'.format(
first=self.first_name, last=self.last_name
)
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 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 Vendor(Model):
__tablename__ = 'vendor'
id = Column(db.Integer, primary_key=True, index=True)
business_name = Column(db.String(255), nullable=False)
email = Column(db.String(80), unique=True, nullable=False)
created_at = Column(db.DateTime,
nullable=False,
default=datetime.datetime.utcnow)
first_name = Column(db.String(30), nullable=True)
last_name = Column(db.String(30), nullable=True)
phone_number = Column(db.String(20))
fax_number = Column(db.String(20))
minority_owned = Column(db.Boolean())
veteran_owned = Column(db.Boolean())
woman_owned = Column(db.Boolean())
disadvantaged_owned = Column(db.Boolean())
categories = db.relationship('Category',
secondary=category_vendor_association_table,
backref='vendors')
def __unicode__(self):
return self.email
class ContractStageActionItem(Model):
'''Action logs for various actions that take place on a contract stage
Attributes:
id: Primary key unique ID
contract_stage_id: Foreign key to
:py:class:`~purchasing.data.contract_stages.ContractStage`
contract_stage: Sqlalchemy relationship to
:py:class:`~purchasing.data.contract_stages.ContractStage`
action_type: A string describing the type of action taken
action_detail: A JSON blob representing details pertinent
to the action in question
taken_at: Timestamp for when the action was taken
taken_by: Foriegn key to
:py:class:`~purchasing.users.models.User`
taken_by_user: Sqlalchemy relationship to
:py:class:`~purchasing.users.models.User`
'''
__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.utcnow())
taken_by = ReferenceCol('users', ondelete='SET NULL', nullable=True)
taken_by_user = db.relationship('User',
backref=backref('contract_stage_actions',
lazy='dynamic'),
foreign_keys=taken_by)
def __unicode__(self):
return self.action_type
@property
def non_null_items(self):
'''Return the filtered actions where the action's value is not none
'''
return dict(
(k, v) for (k, v) in self.action_detail.items() if v is not None)
@property
def non_null_items_count(self):
'''Return a count of the non-null items in an action's detailed log
'''
return len(self.non_null_items)
@property
def is_start_type(self):
'''Return true if the action type is either entered or reverted
'''
return self.action_type in ['entered', 'reversion']
@property
def is_exited_type(self):
'''Return true if the action type is exited
'''
return self.action_type == 'exited'
@property
def is_other_type(self):
'''Return true if the action type is not start or exited type
'''
return not any([self.is_start_type, self.is_exited_type])
def get_sort_key(self):
'''Return the date field for sorting the action log
See Also:
:py:meth:`purchasing.data.contracts.ContractBase.filter_action_log`
'''
# if we are reversion, we need to get the timestamps from there
if self.is_start_type or self.is_exited_type:
return (datetime.datetime.strptime(self.action_detail['timestamp'],
'%Y-%m-%dT%H:%M:%S'),
self.taken_at)
# otherwise, return the taken_at time
else:
return (self.taken_at, self.taken_at)
class User(UserMixin, SurrogatePK, Model):
'''User model
Attributes:
id: primary key
email: user email address
first_name: first name of user
last_name: last name of user
active: whether user is currently active or not
role_id: foreign key of user's role
role: relationship of user to role table
department_id: foreign key of user's department
department: relationship of user to department table
'''
__tablename__ = 'users'
email = Column(db.String(80), unique=True, nullable=False, index=True)
first_name = Column(db.String(30), nullable=True)
last_name = Column(db.String(30), nullable=True)
active = Column(db.Boolean(), default=True)
role_id = ReferenceCol('roles', ondelete='SET NULL', nullable=True)
role = db.relationship('Role',
backref=backref('users', lazy='dynamic'),
foreign_keys=role_id,
primaryjoin='User.role_id==Role.id')
department_id = ReferenceCol('department',
ondelete='SET NULL',
nullable=True)
department = db.relationship(
'Department',
backref=backref('users', lazy='dynamic'),
foreign_keys=department_id,
primaryjoin='User.department_id==Department.id')
def __repr__(self):
return '<User({email!r})>'.format(email=self.email)
def __unicode__(self):
return self.email
@property
def full_name(self):
'''Build full name of user
Returns:
concatenated string of first_name and last_name values
'''
return "{0} {1}".format(self.first_name, self.last_name)
@classmethod
def department_user_factory(cls, department_id):
return cls.query.filter(
cls.department_id == department_id,
db.func.lower(Department.name) !=
'equal opportunity review commission')
@classmethod
def county_purchaser_factory(cls):
return cls.query.join(
Role, User.role_id == Role.id).filter(Role.name == 'county')
@classmethod
def eorc_user_factory(cls):
return cls.query.join(Department,
User.department_id == Department.id).filter(
db.func.lower(Department.name) ==
'equal opportunity review commission')
@classmethod
def get_subscriber_groups(cls, department_id):
return [
cls.department_user_factory(department_id).all(),
cls.county_purchaser_factory().all(),
cls.eorc_user_factory().all()
]
def get_following(self):
'''Generate user contract subscriptions
Returns:
list of ids for contracts followed by user
'''
return [i.id for i in self.contracts_following]
def is_conductor(self):
'''Check if user can access conductor application
Returns:
True if user's role is either conductor, admin, or superadmin,
False otherwise
'''
return self.role.name in ('conductor', 'admin', 'superadmin')
def print_pretty_name(self):
'''Generate long version text representation of user
Returns:
full_name if first_name and last_name exist, email otherwise
'''
if self.first_name and self.last_name:
return self.full_name
else:
return self.email
def print_pretty_first_name(self):
'''Generate abbreviated text representation of user
Returns:
first_name if first_name exists,
`localpart <https://en.wikipedia.org/wiki/Email_address#Local_part>`_
otherwise
'''
if self.first_name:
return self.first_name
else:
return self.email.split('@')[0]
@classmethod
def conductor_users_query(cls):
'''Query users with access to conductor
Returns:
list of users with ``is_conductor`` value of True
'''
return [i for i in cls.query.all() if i.is_conductor()]
class ContractBase(RefreshSearchViewMixin, Model):
'''Base contract model
Attributes:
id: Primary key unique ID
financial_id: Financial identifier for the contract.
In Pittsburgh, this is called the "controller number"
because it is assigned by the City Controller's office
expiration_date: Date when the contract expires
description: Short description of what the contract covers
contract_href: Link to the actual contract document
followers: A many-to-many relationship with
:py:class:`~purchasing.users.models.User` objects
for people who will receive updates about when the
contract will be updated
is_archived: Whether the contract is archived. Archived
contracts do not appear by default on Scout search
contract_type_id: Foreign key to
:py:class:`~purchasing.data.contracts.ContractType`
contract_type: Sqlalchemy relationship to
:py:class:`~purchasing.data.contracts.ContractType`
department_id: Foreign key to
:py:class:`~purchasing.users.models.Department`
department: Sqlalchemy relationship to
:py:class:`~purchasing.users.models.Department`
opportunity: An :py:class:`~purchasing.opportunities.models.Opportunity`
created via conductor for this contract
is_visible: A flag as to whether or not the contract should
be visible in Conductro
assigned_to: Foreign key to
:py:class:`~purchasing.users.models.User`
assigned: Sqlalchemy relationship to
:py:class:`~purchasing.users.models.User`
flow_id: Foreign key to
:py:class:`~purchasing.data.flows.Flow`
current_flow: Sqlalchemy relationship to
:py:class:`~purchasing.data.flows.Flow`
current_stage_id: Foreign key to
:py:class:`~purchasing.data.stages.Stage`
current_stage: Sqlalchemy relationship to
:py:class:`~purchasing.data.stages.Stage`
parent_id: Contract self-reference. When new work is started
on a contract, a clone of that contract is made and
the contract that was cloned is assigned as the new
contract's ``parent``
children: A list of all of this object's children
(all contracts) that have this contract's id as
their ``parent_id``
'''
__tablename__ = 'contract'
# base contract information
id = Column(db.Integer, primary_key=True)
financial_id = Column(db.String(255))
expiration_date = Column(db.Date)
description = Column(db.Text, index=True)
contract_href = Column(db.Text)
followers = db.relationship(
'User',
secondary=contract_user_association_table,
backref='contracts_following',
)
is_archived = Column(db.Boolean, default=False, nullable=False)
# contract type/department relationships
contract_type_id = ReferenceCol('contract_type',
ondelete='SET NULL',
nullable=True)
contract_type = db.relationship('ContractType',
backref=backref('contracts',
lazy='dynamic'))
department_id = ReferenceCol('department',
ondelete='SET NULL',
nullable=True,
index=True)
department = db.relationship('Department',
backref=backref('contracts',
lazy='dynamic',
cascade='none'))
opportunity = db.relationship('Opportunity',
uselist=False,
backref='opportunity')
# conductor information
is_visible = Column(db.Boolean, default=True, nullable=False)
has_metrics = Column(db.Boolean, default=True, nullable=False)
assigned_to = ReferenceCol('users', ondelete='SET NULL', nullable=True)
assigned = db.relationship('User',
backref=backref('assignments',
lazy='dynamic',
cascade='none'),
foreign_keys=assigned_to)
flow_id = ReferenceCol('flow', ondelete='SET NULL', nullable=True)
current_flow = db.relationship('Flow', lazy='joined')
current_stage_id = ReferenceCol('stage',
ondelete='SET NULL',
nullable=True,
index=True)
current_stage = db.relationship('Stage', lazy='joined')
parent_id = Column(db.Integer, db.ForeignKey('contract.id'))
children = db.relationship('ContractBase',
backref=backref('parent',
remote_side=[id],
lazy='subquery'))
def __unicode__(self):
return '{} (ID: {})'.format(self.description, self.id)
@property
def scout_contract_status(self):
'''Returns a string with the contract's status.
'''
if self.expiration_date:
if days_from_today(self.expiration_date) <= 0 and \
self.children and self.is_archived:
return 'expired_replaced'
elif days_from_today(self.expiration_date) <= 0:
return 'expired'
elif self.children and self.is_archived:
return 'replaced'
elif self.is_archived:
return 'archived'
elif self.children and self.is_archived:
return 'replaced'
elif self.is_archived:
return 'archived'
return 'active'
@property
def current_contract_stage(self):
'''The contract's current stage
Because the :py:class:`~purchasing.data.contract_stages.ContractStage` model
has a three-part compound primary key, we pass the contract's ID, the
contract's :py:class:`~purchasing.data.flows.Flow` id and its
:py:class:`~purchasing.data.stages.Stage` id
'''
return ContractStage.get_one(self.id, self.flow.id,
self.current_stage.id)
def get_spec_number(self):
'''Returns the spec number for a given contract
The spec number is a somewhat unique identifier for contracts used by
Allegheny County. Because of the history of purchasing between the City
and the County, the City uses spec numbers when they are available (
this tends to be contracts with County, A-Bid, and B-Bid
:py:class:`~purchasing.data.contracts.ContractType`.
The majority of contracts do not have spec numbers, but these numbers
are quite important and used regularly for the contracts that do have them.
Returns:
A :py:class:`~purchasing.data.contracts.ContractProperty` object, either
with the key of "Spec Number" or an empty object if none with that name
exists
'''
try:
return [
i for i in self.properties if i.key.lower() == 'spec number'
][0]
except IndexError:
return ContractProperty()
def update_with_spec_number(self, data, company=None):
'''Action to update both a contract and its spec number
Because a spec number is not a direct property of a contract,
we have to go through some extra steps to update it.
Arguments:
data: Form data to use in updating a contract
Keyword Arguments:
company: A :py:class:`~purchasing.data.companies.Company` to
add to the companies that are servicing the contract
'''
spec_number = self.get_spec_number()
new_spec = data.pop('spec_number', None)
if new_spec:
spec_number.key = 'Spec Number'
spec_number.value = new_spec
else:
spec_number.key = 'Spec Number'
spec_number.value = None
self.properties.append(spec_number)
if company and company not in self.companies:
self.companies.append(company)
self.update(**data)
def build_complete_action_log(self):
'''Returns the complete action log for this contract
'''
return ContractStageActionItem.query.join(ContractStage).filter(
ContractStage.contract_id == self.id).order_by(
ContractStageActionItem.taken_at, ContractStage.id,
ContractStageActionItem.id).all()
def filter_action_log(self):
'''Returns a filtered action log for this contract
Because stages can be restarted, simple ordering by time an action was
taken will lead to incorrectly ordered (and far too many) actions. Filtering
these down is a multi-step process, which proceeds roughly as follows:
1. Sort all actions based on the time that they were taken. This ensures
that when we filter, we will get the most recent action. Putting them
into proper time order for display takes place later
2. Group actions by their respective :py:class:`~purchasing.data.stages.Stage`
3. For each group of actions that takes place in each stage:
a. Grab the most recent start or restart action for that stage (filtered
by whether that action was taken on a stage prior to our current stage
in our flow's stage order)
b. Grab the most recent end action for that stage (filtered
by whether that action was taken on a stage prior to our current stage
in our flow's stage order, or the same stage)
c. Grab all other actions that took place on that stage
4. Re-sort them based on the action's sort key, which will put them into the
proper order for display
'''
all_actions = sorted(
self.build_complete_action_log(),
key=lambda x:
(x.contract_stage.stage_id, -time.mktime(x.taken_at.timetuple())))
filtered_actions = []
for stage_id, group_of_actions in groupby(
all_actions, lambda x: x.contract_stage.stage_id):
actions = list(group_of_actions)
# append start types
filtered_actions.append(
next(
ifilter(
lambda x: x.is_start_type and x.contract_stage.
happens_before_or_on(self.current_stage_id), actions),
[]))
# append end types
filtered_actions.append(
next(
ifilter(
lambda x: x.is_exited_type and x.contract_stage.
happens_before(self.current_stage_id), actions), []))
# extend with all other types
filtered_actions.extend([x for x in actions if x.is_other_type])
# return the resorted
return sorted(ifilter(lambda x: hasattr(x, 'taken_at'),
filtered_actions),
key=lambda x: x.get_sort_key(),
reverse=True)
def get_contract_stages(self):
'''Returns the appropriate stages and their metadata based on a contract id
'''
return db.session.query(
ContractStage.contract_id, ContractStage.stage_id,
ContractStage.id, ContractStage.entered, ContractStage.exited,
Stage.name, Stage.default_message, Stage.post_opportunities,
ContractBase.description, Stage.id.label('stage_id'),
db.func.extract(db.text('DAYS'), ContractStage.exited -
ContractStage.entered).label('days_spent'),
db.func.extract(
db.text('HOURS'), ContractStage.exited -
ContractStage.entered).label('hours_spent')).join(
Stage, Stage.id == ContractStage.stage_id).join(
ContractBase,
ContractBase.id == ContractStage.contract_id).filter(
ContractStage.contract_id == self.id,
ContractStage.flow_id == self.flow_id).order_by(
ContractStage.id).all()
def get_current_stage(self):
'''Returns the details for the current contract stage
'''
return ContractStage.query.filter(
ContractStage.contract_id == self.id,
ContractStage.stage_id == self.current_stage_id,
ContractStage.flow_id == self.flow_id).first()
def get_first_stage(self):
'''Get the first ContractStage for this contract
Returns:
:py:class:`~purchasing.data.contract_stage.ContractStage` object
representing the first stage, or None if no stage exists
'''
if self.flow:
return ContractStage.query.filter(
ContractStage.contract_id == self.id,
ContractStage.stage_id == self.flow.stage_order[0],
ContractStage.flow_id == self.flow_id).first()
return None
def completed_last_stage(self):
'''Boolean to check if we have completed the last stage of our flow
'''
return self.flow is None or \
self.current_stage_id == self.flow.stage_order[-1] and \
self.get_current_stage().exited is not None
def add_follower(self, user):
'''Add a follower from a contract's list of followers
Arguments:
user: A :py:class:`~purchasing.users.models.User`
Returns:
A two-tuple to use to flash an alert of (the message to display,
the class to style the message with)
'''
if user not in self.followers:
self.followers.append(user)
return ('Successfully subscribed!', 'alert-success')
return ('Already subscribed!', 'alert-info')
def remove_follower(self, user):
'''Remove a follower from a contract's list of followers
Arguments:
user: A :py:class:`~purchasing.users.models.User`
Returns:
A two-tuple to use to flash an alert of (the message to display,
the class to style the message with)
'''
if user in self.followers:
self.followers.remove(user)
return ('Successfully unsubscribed', 'alert-success')
return ('You haven\'t subscribed to this contract!', 'alert-warning')
def transfer_followers_to_children(self):
'''Transfer relationships from parent to all children and reset parent's followers
'''
for child in self.children:
child.followers = self.followers
self.followers = []
return self.followers
def extend(self, delete_children=True):
'''Extends a contract.
Because conductor clones existing contracts when work begins,
when we get an "extend" signal, we actually want to extend the
parent conract of the clone. Optionally (by default), we also
want to delete the child (cloned) contract.
'''
self.expiration_date = None
if delete_children:
for child in self.children:
child.delete()
self.children = []
def complete(self):
'''Do the steps to mark a contract as complete
1. Transfer the followers to children
2. Modify description to make contract explicitly completed/archived
3. Mark self as archived and not visible
4. Mark children as not archived and visible
'''
self.transfer_followers_to_children()
self.kill()
for child in self.children:
child.is_archived = False
child.is_visible = True
def kill(self):
'''Remove the contract from the conductor visiblility list
'''
self.is_visible = False
self.is_archived = True
if not self.description.endswith(' [Archived]'):
self.description += ' [Archived]'
@classmethod
def clone(cls,
instance,
parent_id=None,
strip=True,
new_conductor_contract=True):
'''Takes a contract object and clones it
The clone always strips the following properties:
* Current Stage
If the strip flag is set to true, the following are also stripped
* Contract HREF
* Financial ID
* Expiration Date
If the new_conductor_contract flag is set to true, the following are set:
* is_visible set to False
* is_archived set to False
Relationships are handled as follows:
* Stage, Flow - Duplicated
* Properties, Notes, Line Items, Companies, Stars, Follows kept on old
Arguments:
instance: The instance of the contract to clone, will become
the parent of the cloned contract unless a different
``parent_id`` is passed as a keyword argument
Keyword Arguments:
parent_id: The parent id of the contract to be passed, defaults to None
strip: Boolean, if true, the contract href, financial id and expiration
date of the cloned contract will all be stripped. Defaults to True
new_conductor_contract: Boolean to mark if we are going to be starting
new work in Conductor with the clone. If true, set both
``is_visible`` and ``is_archived`` to False. Defaults to True
Returns:
The cloned contract created from the passed instance
'''
clone = cls(**instance.as_dict())
clone.id, clone.current_stage = None, None
clone.parent_id = parent_id if parent_id else instance.id
if strip:
clone.contract_href, clone.financial_id, clone.expiration_date = None, None, None
if new_conductor_contract:
clone.is_archived, clone.is_visible = False, False
return clone
def _transition_to_first(self, user, complete_time):
contract_stage = ContractStage.get_one(self.id, self.flow.id,
self.flow.stage_order[0])
self.current_stage_id = self.flow.stage_order[0]
return [contract_stage.log_enter(user, complete_time)]
def _transition_to_next(self, user, complete_time):
stages = self.flow.stage_order
current_stage_idx = stages.index(self.current_stage.id)
current_stage = self.current_contract_stage
next_stage = ContractStage.get_one(
self.id, self.flow.id,
self.flow.stage_order[current_stage_idx + 1])
self.current_stage_id = next_stage.stage.id
actions = current_stage._fix_start_time()
actions.extend([
current_stage.log_exit(user, complete_time),
next_stage.log_enter(user, complete_time)
])
return actions
def _transition_to_last(self, user, complete_time):
exit = self.current_contract_stage.log_exit(user, complete_time)
return [exit]
def _transition_backwards_to_destination(self, user, destination,
complete_time):
destination_idx = self.flow.stage_order.index(destination)
current_stage_idx = self.flow.stage_order.index(self.current_stage_id)
if destination_idx > current_stage_idx:
raise Exception('Skipping stages is not currently supported')
stages = self.flow.stage_order[destination_idx:current_stage_idx + 1]
to_revert = ContractStage.get_multiple(self.id, self.flow_id, stages)
actions = []
for ix, contract_stage in enumerate(to_revert):
if ix == 0:
actions.append(contract_stage.log_reopen(user, complete_time))
contract_stage.entered = complete_time
contract_stage.exited = None
self.current_stage_id = contract_stage.stage.id
else:
contract_stage.full_revert()
return actions
def transition(self, user, destination=None, complete_time=None):
'''Transition the contract to the appropriate stage.
* If the contract has no current stage, transition it to the first
stage
* If the contract has a "destination", transition it to that destination
* If the current stage of the contract is the last stage of the contract's
flow order, exit the last stage and move to completion
* If it is anything else, transition forward one stage in the flow order
Arguments:
user: The user taking the actions
Keyword Arguments:
destination: An optional revere destination to allow for rewinding
to any point in time. Defaults to None
complete_time: A time other than the current time to perform
the transitions. If one is given, the relevant
:py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
datetime fields
and :py:class:`~purchasing.data.contract_stages.ContractStage`
enter and exit times are marked with the passed time. The actions'
taken_at times are still marked with the current time, however.
Returns:
A list of :py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
objects which describe the actions in transition
'''
complete_time = complete_time if complete_time else datetime.datetime.utcnow(
)
if self.current_stage_id is None:
actions = self._transition_to_first(user, complete_time)
elif destination is not None:
actions = self._transition_backwards_to_destination(
user, destination, complete_time)
elif self.current_stage_id == self.flow.stage_order[-1]:
actions = self._transition_to_last(user, complete_time)
else:
actions = self._transition_to_next(user, complete_time)
return actions
def switch_flow(self, new_flow_id, user):
'''Switch the contract's progress from one flow to another
Instead of trying to do anything too smart, we prefer instead
to be dumb -- it is better to force the user to click ahead
through a bunch of stages than it is to incorrectly fast-forward
them to an incorrect state.
There are five concrete actions here:
1. Fully revert all stages in the old flow
2. Rebuild our flow/stage model for the new order.
3. Attach the complete log of the old flow into the first stage
of the new order.
4. Strip the contract's current stage id.
5. Transition into the first stage of the new order. This will
ensure that everything is being logged in the correct order.
Arguments:
new_flow_id: ID of the new flow to switch to
user: The user performing the switch
'''
old_flow = self.flow.flow_name
old_action_log = self.filter_action_log()
new_flow = Flow.query.get(new_flow_id)
# fully revert all used stages in the old flow
for contract_stage in ContractStage.query.filter(
ContractStage.contract_id == self.id,
ContractStage.flow_id == self.flow_id,
ContractStage.entered != None).all():
contract_stage.full_revert()
contract_stage.strip_actions()
db.session.commit()
# create the new stages
new_stages, new_contract_stages, revert = new_flow.create_contract_stages(
self)
# log that we are switching flows into the first stage
switch_log = ContractStageActionItem(
contract_stage_id=new_contract_stages[0].id,
action_type='flow_switch',
taken_by=user.id,
taken_at=datetime.datetime.utcnow(),
action_detail={
'timestamp':
datetime.datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S'),
'date':
datetime.datetime.utcnow().strftime('%Y-%m-%d'),
'type':
'flow_switched',
'old_flow':
old_flow,
'new_flow':
self.flow.flow_name,
'old_flow_actions': [i.as_dict() for i in old_action_log]
})
db.session.add(switch_log)
db.session.commit()
# remove the current_stage_id from the contract
# so we can start the new flow
self.current_stage_id = None
self.flow_id = new_flow_id
destination = None
if revert:
destination = new_stages[0]
# transition into the first stage of the new flow
actions = self.transition(user, destination=destination)
for i in actions:
db.session.add(i)
db.session.commit()
return new_contract_stages[0], self
def build_subscribers(self):
'''Build a list of subscribers and others to populate contacts in conductor
'''
department_users, county_purchasers, eorc = User.get_subscriber_groups(
self.department_id)
if self.parent is None:
followers = []
else:
followers = [
i for i in self.parent.followers if i not in department_users
]
subscribers = {
'Department Users':
department_users,
'Followers':
followers,
'County Purchasers':
[i for i in county_purchasers if i not in department_users],
'EORC':
eorc
}
return subscribers, sum([len(i) for i in subscribers.values()])
class ContractStage(Model):
'''Model for contract stages
A Contract Stage is the term for a step that a
:py:class:`~purchasing.data.contracts.ContractBase` will
occupy while going through a
:py:class:`~purchasing.data.flows.Flow`. It has a
three-part compound primary key of ``contract_id``,
``stage_id``, and ``flow_id``. A contract stage's primary role
is to keep track of how long things take, which is accomplished
through the object's ``enter`` and ``exit`` attributes.
Attributes:
id: Unique ID for each contract/stage/flow contract stage
contract_id: Part of compound primary key, foreign key to
:py:class:`~purchasing.data.contracts.ContractBase`
contract: Sqlalchemy relationship to
:py:class:`~purchasing.data.contracts.ContractBase`
stage_id: Part of compound primary key, foreign key to
:py:class:`~purchasing.data.stages.Stage`
stage: Sqlalchemy relationship to
:py:class:`~purchasing.data.stages.Stage`
flow_id: Part of compound primary key, foreign key to
:py:class:`~purchasing.data.flows.Flow`
flow: Sqlalchemy relationship to
:py:class:`~purchasing.data.flows.Flow`
entered: When work started for this particular contract stage
exited: When work completed for this particular contract stage
'''
__tablename__ = 'contract_stage'
__table_args__ = (db.Index('ix_contrage_stage_combined_id', 'contract_id',
'stage_id', 'flow_id'), )
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'))
flow_id = ReferenceCol('flow',
ondelete='CASCADE',
index=True,
primary_key=True)
flow = db.relationship('Flow',
backref=backref('contract_stages',
lazy='dynamic',
cascade='all, delete-orphan'))
entered = Column(db.DateTime)
exited = Column(db.DateTime)
@property
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
@classmethod
def get_one(cls, contract_id, flow_id, stage_id):
'''Get one contract stage based on its three primary key elements
Arguments:
contract_id: ID of the relevant
:py:class:`~purchasing.data.contracts.ContractBase`
flow_id: ID of the relevant
:py:class:`~purchasing.data.flows.Flow`
stage_id: ID of the relevant
:py:class:`~purchasing.data.stages.Stage`
'''
return cls.query.filter(cls.contract_id == contract_id,
cls.stage_id == stage_id,
cls.flow_id == flow_id).first()
@classmethod
def get_multiple(cls, contract_id, flow_id, stage_ids):
'''Get multiple contract stages based on multiple flow ids
Multiple only takes a single contract id and flow id because
in Conductor, you would have multiple
:py:class:`~purchasing.data.stages.Stage` per
:py:class:`~purchasing.data.contracts.ContractBase`/
:py:class:`~purchasing.data.flows.Flow` combination.
Arguments:
contract_id: ID of the relevant
:py:class:`~purchasing.data.contracts.ContractBase`
flow_id: ID of the relevant
:py:class:`~purchasing.data.flows.Flow`
stage_id: IDs of the relevant
:py:class:`~purchasing.data.stages.Stage`
'''
return cls.query.filter(cls.contract_id == contract_id,
cls.stage_id.in_(stage_ids),
cls.flow_id == flow_id).order_by(cls.id).all()
def _fix_start_time(self):
actions = []
previous_stage_exit = self._get_previous_stage_exit_time()
if previous_stage_exit is None or self.entered == previous_stage_exit:
pass
else:
enter_actions = self.contract_stage_actions.filter(
ContractStageActionItem.action_type.in_([
'entered', 'reversion'
]), ContractStageActionItem.action_detail['timestamp'].astext
== self.entered.strftime('%Y-%m-%dT%H:%M:%S')).all()
for action in enter_actions:
action.action_detail[
'timestamp'] = previous_stage_exit.strftime(
'%Y-%m-%dT%H:%M:%S')
actions.append(action)
self.enter(previous_stage_exit)
return actions
def _get_previous_stage_exit_time(self):
current_stage_idx = self.contract.flow.stage_order.index(self.stage_id)
previous = ContractStage.get_one(
self.contract.id, self.flow.id,
self.flow.stage_order[current_stage_idx - 1])
return previous.exited
def enter(self, enter_time=None):
'''Set the contract stage's enter time
Arguments:
enter_time: A datetime for this stage's enter attribute.
Defaults to utcnow.
'''
enter_time = enter_time if enter_time else datetime.datetime.utcnow()
self.entered = enter_time
def log_enter(self, user, enter_time):
'''Enter the contract stage and log its entry
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
who triggered the enter event.
enter_time: A datetime for this stage's enter attribute.
Returns:
A :py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
that represents the log of the action item.
'''
self.enter(enter_time=enter_time)
return ContractStageActionItem(
contract_stage_id=self.id,
action_type='entered',
taken_by=user.id,
taken_at=datetime.datetime.utcnow(),
action_detail={
'timestamp': self.entered.strftime('%Y-%m-%dT%H:%M:%S'),
'date': self.entered.strftime('%Y-%m-%d'),
'type': 'entered',
'label': 'Started work',
'stage_name': self.stage.name
})
def happens_before(self, target_stage_id):
'''Check if this contract stage happens before a target stage
"Before" refers to the relative positions of these stages
in their linked flow's stage order based on the contract stage's
``stage_id``. If the passed ``target_stage_id``
is not in the flow's stage order, this always returns False.
Arguments:
target_stage_id: A :py:class:`~purchasing.data.stages.Stage` ID
'''
if target_stage_id not in self.flow.stage_order:
return False
return self.flow.stage_order.index(self.stage_id) < \
self.flow.stage_order.index(target_stage_id)
def happens_before_or_on(self, target_stage_id):
'''Check if this contract stage happens before or is a target stage
"Before" refers to the relative positions of these stages
in their linked flow's stage order based on the contract stage's
``stage_id``. "On" refers to whether or not
the passed ``target_stage_id`` shares an index with the contract
stage's ``stage_id``. If the passed ``target_stage_id``
is not in the flow's stage order, this always returns False.
Arguments:
target_stage_id: A :py:class:`purchasing.data.stages.Stage` ID
'''
if target_stage_id not in self.flow.stage_order:
return False
return self.flow.stage_order.index(self.stage_id) <= \
self.flow.stage_order.index(target_stage_id)
def happens_after(self, target_stage_id):
'''Check if this contract stage happens after a target stage
"after" refers to the relative positions of these stages
in their linked flow's stage order based on the contract stage's
``stage_id``. If the passed ``target_stage_id``
is not in the flow's stage order, this always returns False.
Arguments:
target_stage_id: A :py:class:`purchasing.data.stages.Stage` ID
'''
if target_stage_id not in self.flow.stage_order:
return False
return self.flow.stage_order.index(self.stage_id) > \
self.flow.stage_order.index(target_stage_id)
def exit(self, exit_time=None):
'''Set the contract stage's exit time
Arguments:
exit_time: A datetime for this stage's exit attribute.
Defaults to utcnow.
'''
exit_time = exit_time if exit_time else datetime.datetime.utcnow()
self.exited = exit_time
def log_exit(self, user, exit_time):
'''Exit the contract stage and log its exit
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
who triggered the exit event.
exit_time: A datetime for this stage's exit attribute.
Returns:
A :py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
that represents the log of the action item.
'''
self.exit(exit_time=exit_time)
return ContractStageActionItem(
contract_stage_id=self.id,
action_type='exited',
taken_by=user.id,
taken_at=datetime.datetime.utcnow(),
action_detail={
'timestamp': self.exited.strftime('%Y-%m-%dT%H:%M:%S'),
'date': self.exited.strftime('%Y-%m-%d'),
'type': 'exited',
'label': 'Completed work',
'stage_name': self.stage.name
})
def log_reopen(self, user, reopen_time):
'''Reopen the contract stage and log that re-opening
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
who triggered the reopen event.
reopen_time: A datetime for this stage's reopen attribute.
Returns:
A :py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
that represents the log of the action item.
'''
return ContractStageActionItem(
contract_stage_id=self.id,
action_type='reversion',
taken_by=user.id,
taken_at=datetime.datetime.utcnow(),
action_detail={
'timestamp': reopen_time.strftime('%Y-%m-%dT%H:%M:%S'),
'date': datetime.datetime.utcnow().strftime('%Y-%m-%d'),
'type': 'reopened',
'label': 'Restarted work',
'stage_name': self.stage.name,
})
def log_extension(self, user):
'''Log an extension event
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
who triggered the extension event.
Returns:
A :py:class:`~purchasing.data.contract_stages.ContractStageActionItem`
that represents the log of the action item.
'''
return ContractStageActionItem(
contract_stage_id=self.id,
action_type='extension',
taken_by=user.id,
taken_at=datetime.datetime.utcnow(),
action_detail={
'timestamp':
datetime.datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S'),
'date':
datetime.datetime.utcnow().strftime('%Y-%m-%d'),
'type':
'extension',
'stage_name':
self.stage.name
})
def full_revert(self):
'''Clear timestamps for both enter and exit for this contract stage
'''
self.entered = None
self.exited = None
def strip_actions(self):
'''Clear out non-stage-switch actions
This will prevent duplicate actions from piling up
in the stream that is presented to the user
'''
for action in self.contract_stage_actions:
if action.action_type != 'flow_switch':
action.delete()
def __unicode__(self):
return '{} - {}'.format(self.contract.description, self.stage.name)
class Opportunity(Model):
'''Base Opportunity Model -- the central point for Beacon
The Beacon model is centered around three dates:
:py:attr:`~purchasing.opportunities.models.Opportunity.planned_publish`,
:py:attr:`~purchasing.opportunities.models.Opportunity.planned_submission_start`,
and :py:attr:`~purchasing.opportunities.models.Opportunity.planned_submission_end`.
The publish date is when opportunities that are approved appear on Beacon. The
publication date also is when vendors are notified via email.
Attributes:
id: Primary key unique ID
title: Title of the Opportunity
description: Short (maximum 500-word) description of the opportunity
planned_publish: Date when the opportunity should show up on Beacon
planned_submission_start: Date when vendors can begin submitting
responses to the opportunity
planned_submission_end: Deadline for submitted responses to the
Opportunity
vendor_documents_needed: Array of integers that relate to
:py:class:`~purchasing.opportunities.models.RequiredBidDocument` ids
is_public: True if opportunity is approved (publicly visible), False otherwise
is_archived: True if opportunity is archived (not visible), False otherwise
published_at: Date when an alert email was sent out to relevant vendors
publish_notification_sent: True is notification sent, False otherwise
department_id: ID of primary :py:class:`~purchasing.users.models.Department`
for this opportunity
department: Sqlalchemy relationship to primary
:py:class:`~purchasing.users.models.Department`
for this opportunity
contact_id: ID of the :py:class:`~purchasing.users.models.User` for this opportunity
contact: Sqlalchemy relationship to :py:class:`~purchasing.users.models.User`
for this opportunity
categories: Many-to-many relationship of the
:py:class:`~purchasing.opportunities.models.Category` objects
for this opportunity
created_from_id: ID of the :py:class:`~purchasing.data.models.ContractBase`
this opportunity was created from through Conductor
opportunity_type_id: ID of the :py:class:`~purchasing.data.models.ContractType`
opportunity_type: Sqlalchemy relationship to the :py:class:`~purchasing.data.models.ContractType`
See Also:
For more on the Conductor <--> Beacon relationship, look at the
:py:func:`~purchasing.conductor.handle_form()` Conductor utility method and the
:py:class:`~purchasing.conductor.forms.PostOpportunityForm` Conductor Form
'''
__tablename__ = 'opportunity'
id = Column(db.Integer, primary_key=True)
title = Column(db.String(255))
description = Column(db.Text)
planned_publish = Column(db.DateTime, nullable=False)
planned_submission_start = Column(db.DateTime, nullable=False)
planned_submission_end = Column(db.DateTime, nullable=False)
vendor_documents_needed = Column(ARRAY(db.Integer()))
is_public = Column(db.Boolean(), default=False)
is_archived = Column(db.Boolean(), default=False, nullable=False)
published_at = Column(db.DateTime, nullable=True)
publish_notification_sent = Column(db.Boolean,
default=False,
nullable=False)
department_id = ReferenceCol('department',
ondelete='SET NULL',
nullable=True)
department = db.relationship('Department',
backref=backref('opportunities',
lazy='dynamic'))
contact_id = ReferenceCol('users', ondelete='SET NULL')
contact = db.relationship('User',
backref=backref('opportunities', lazy='dynamic'),
foreign_keys='Opportunity.contact_id')
categories = db.relationship(
'Category',
secondary=category_opportunity_association_table,
backref='opportunities',
collection_class=set)
created_from_id = ReferenceCol('contract',
ondelete='cascade',
nullable=True)
opportunity_type_id = ReferenceCol('contract_type',
ondelete='SET NULL',
nullable=True)
opportunity_type = db.relationship(
'ContractType',
backref=backref('opportunities', lazy='dynamic'),
)
@classmethod
def create(cls, data, user, documents, publish=False):
'''Create a new opportunity
Arguments:
data: dictionary of fields needed to populate new
opportunity object
user: :py:class:`~purchasing.users.models.User` object
creating the new opportunity
documents: The documents FieldList from the
:py:class:`~purchasing.opportunities.forms.OpportunityForm`
Keyword Arguments:
publish: Boolean as to whether to publish this document. If
True, it will set ``is_public`` to True.
See Also:
The :py:class:`~purchasing.opportunities.forms.OpportunityForm`
and :py:class:`~purchasing.opportunities.forms.OpportunityDocumentForm`
have more information about the documents.
'''
opportunity = Opportunity(**data)
current_app.logger.info(
u'''BEACON NEW - New Opportunity Created: Department: {} | Title: {} | Publish Date: {} | Submission Start Date: {} | Submission End Date: {}
'''.format(
opportunity.id,
opportunity.department.name if opportunity.department else '',
opportunity.title, str(opportunity.planned_publish),
str(opportunity.planned_submission_start),
str(opportunity.planned_submission_end)))
if not (user.is_conductor() or publish):
# only send 'your post has been sent/a new post needs review'
# emails when 1. the submitter isn't from OMB and 2. they are
# saving a draft as opposed to publishing the opportunity
opportunity.notify_approvals(user)
opportunity._handle_uploads(documents)
opportunity._publish(publish)
return opportunity
def raw_update(self, **kwargs):
'''Performs a basic update based on the passed kwargs.
Arguments:
**kwargs: Keyword arguments of fields to be updated in
the existing Opportunity model
'''
super(Opportunity, self).update(**kwargs)
def update(self, data, user, documents, publish=False):
'''Performs an update, uploads new documents, and publishes
Arguments:
data: dictionary of fields needed to populate new
opportunity object
user: :py:class:`~purchasing.users.models.User` object
updating the opportunity
documents: The documents FieldList from the
:py:class:`~purchasing.opportunities.forms.OpportunityForm`
Keyword Arguments:
publish: Boolean as to whether to publish this document. If
True, it will set ``is_public`` to True.
'''
data.pop('publish_notification_sent', None)
for attr, value in data.iteritems():
setattr(self, attr, value)
current_app.logger.info(
u'''BEACON Update - Opportunity Updated: ID: {} | Title: {} | Publish Date: {} | Submission Start Date: {} | Submission End Date: {}
'''.format(self.id, self.title, str(self.planned_publish),
str(self.planned_submission_start),
str(self.planned_submission_end)))
self._handle_uploads(documents)
self._publish(publish)
@property
def is_published(self):
'''Determine if an opportunity can be displayed
Returns:
True if the planned publish date is before or on today,
and the opportunity is approved, False otherwise
'''
return self.coerce_to_date(
self.planned_publish) <= localize_today() and self.is_public
@property
def is_upcoming(self):
'''Determine if an opportunity is upcoming
Returns:
True if the planned publish date is before or on today, is approved,
is not accepting submissions, and is not closed; False otherwise
'''
return self.coerce_to_date(self.planned_publish) <= localize_today() and \
not self.is_submission_start and not self.is_submission_end and self.is_public
@property
def is_submission_start(self):
'''Determine if the oppportunity is accepting submissions
Returns:
True if the submission start date and planned publish date are
before or on today, is approved, and the opportunity is not closed;
False otherwise
'''
return self.coerce_to_date(self.planned_submission_start) <= localize_today() and \
self.coerce_to_date(self.planned_publish) <= localize_today() and \
not self.is_submission_end and self.is_public
@property
def is_submission_end(self):
'''Determine if an opportunity is closed to new submissions
Returns:
True if the submission end date is on or before today,
and it is approved
'''
return pytz.UTC.localize(self.planned_submission_end).astimezone(
current_app.config['DISPLAY_TIMEZONE']
) <= localize_now() and \
self.is_public
@property
def has_docs(self):
'''True if the opportunity has at least one document, False otherwise
'''
return self.opportunity_documents.count() > 0
def estimate_submission_start(self):
'''Returns the month/year based on submission start date
'''
return self.planned_submission_start.strftime('%B %d, %Y')
def estimate_submission_end(self):
'''Returns the localized date and time based on submission end date
'''
return pytz.UTC.localize(self.planned_submission_end).astimezone(
current_app.config['DISPLAY_TIMEZONE']).strftime(
'%B %d, %Y at %I:%M%p %Z')
def can_view(self, user):
'''Check if a user can see opportunity detail
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
Returns:
Boolean indiciating if the user can view this opportunity
'''
return False if user.is_anonymous and not self.is_published else True
def can_edit(self, user):
'''Check if a user can edit the contract
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
Returns:
Boolean indiciating if the user can edit this opportunity.
Conductors, the opportunity creator, and the primary opportunity
contact can all edit the opportunity before it is published. After
it is published, only conductors can edit it.
'''
if self.is_public and user.is_conductor():
return True
elif not self.is_public and \
(user.is_conductor() or
user.id in (self.created_by_id, self.contact_id)):
return True
return False
def coerce_to_date(self, field):
'''Coerces the input field to a datetime.date object
Arguments:
field: A datetime.datetime or datetime.date object
Returns:
A datetime.date object
'''
if isinstance(field, datetime.datetime):
return field.date()
if isinstance(field, datetime.date):
return field
return field
def get_vendor_emails(self):
'''Return list of all signed up vendors
'''
return [i.email for i in self.vendors]
def has_vendor_documents(self):
'''Returns a Boolean for whether there are required bid documents
See Also:
:py:class:`~purchasing.opportunities.models.RequiredBidDocument`
'''
return self.vendor_documents_needed and len(
self.vendor_documents_needed) > 0
def get_vendor_documents(self):
'''Returns a list of documents the the vendor will need to provide
See Also:
:py:class:`~purchasing.opportunities.models.RequiredBidDocument`
'''
if self.has_vendor_documents():
return RequiredBidDocument.query.filter(
RequiredBidDocument.id.in_(
self.vendor_documents_needed)).all()
return []
def get_events(self):
'''Returns the opportunity dates out as a nice ordered list for rendering
'''
return [{
'event': 'bid_submission_start',
'classes': 'event event-submission_start',
'date': self.estimate_submission_start(),
'description': 'Opportunity opens for submissions.'
}, {
'event': 'bid_submission_end',
'classes': 'event event-submission_end',
'date': self.estimate_submission_end(),
'description': 'Deadline to submit proposals.'
}]
def _handle_uploads(self, documents):
opp_documents = self.opportunity_documents.all()
for document in documents.entries:
if document.title.data == '':
continue
_id = self.id if self.id else random_id(6)
_file = document.document.data
if _file.filename in [i.name for i in opp_documents]:
continue
filename, filepath = document.upload_document(_id)
if filepath:
self.opportunity_documents.append(
OpportunityDocument(name=document.title.data,
href=filepath))
def _publish(self, publish):
if not self.is_public:
if publish:
self.is_public = True
def notify_approvals(self, user):
'''Send the approval notifications to everyone with approval rights
Arguments:
user: A :py:class:`~purchasing.users.models.User` object
'''
Notification(
to_email=[user.email],
subject='Your post has been sent to OMB for approval',
html_template='opportunities/emails/staff_postsubmitted.html',
txt_template='opportunities/emails/staff_postsubmitted.txt',
opportunity=self).send(multi=True)
Notification(
to_email=db.session.query(User.email).filter(
User.roles.any(
Role.name.in_(['conductor', 'admin',
'superadmin']))).all(),
subject='A new Beacon post needs review',
html_template='opportunities/emails/admin_postforapproval.html',
txt_template='opportunities/emails/admin_postforapproval.txt',
opportunity=self).send(multi=True)
def send_publish_email(self):
'''Sends the "new opportunity available" email to subscribed vendors
If a new Opportunity is created and it has a publish date before or
on today's date, it will trigger an immediate publish email send. This
operates in a very similar way to the nightly
:py:class:`~purchasing.jobs.beacon_nightly.BeaconNewOppotunityOpenJob`.
It will build a list of all vendors signed up to the Opportunity
or to any of the categories that describe the Opportunity.
'''
if self.is_published and not self.publish_notification_sent:
opp_categories = [i.id for i in self.categories]
vendors = Vendor.query.filter(
Vendor.categories.any(Category.id.in_(opp_categories))).all()
Notification(
to_email=[i.email for i in vendors],
subject='A new City of Pittsburgh opportunity from Beacon!',
html_template='opportunities/emails/newopp.html',
txt_template='opportunities/emails/newopp.txt',
opportunity=self).send(multi=True)
self.publish_notification_sent = True
self.published_at = datetime.datetime.utcnow()
current_app.logger.info(
u'''BEACON PUBLISHED: ID: {} | Title: {} | Publish Date: {} | Submission Start Date: {} | Submission End Date: {}
'''.format(self.id, self.title, str(self.planned_publish),
str(self.planned_submission_start),
str(self.planned_submission_end)))
return True
return False
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()
class Vendor(Model):
'''Base Vendor model for businesses interested in Beacon
The primary driving thought behind Beacon is that it should be as
easy as possible to sign up to receive updates about new opportunities.
Therefore, there are no Vendor accounts or anything like that, just
email addresses and business names.
Attributes:
id: Primary key unique ID
business_name: Name of the business, required
email: Email address for the vendor, required
first_name: First name of the vendor
last_name: Last name of the vendor
phone_number: Phone number for the vendor
fax_number: Fax number for the vendor
minority_owned: Whether the vendor is minority owned
veteran_owned: Whether the vendor is veteran owned
woman_owned: Whether the vendor is woman owned
disadvantaged_owned: Whether the vendor is any class
of Disadvantaged Business Enterprise (DBE)
categories: Many-to-many relationship with
:py:class:`~purchasing.opportunities.models.Category`;
describes what the vendor is subscribed to
opportunities: Many-to-many relationship with
:py:class:`~purchasing.opportunities.models.Opportunity`;
describes what opportunities the vendor is subscribed to
subscribed_to_newsletter: Whether the vendor is subscribed to
receive the biweekly newsletter of all opportunities
'''
__tablename__ = 'vendor'
id = Column(db.Integer, primary_key=True, index=True)
business_name = Column(db.String(255), nullable=False)
email = Column(db.String(80), unique=True, nullable=False)
first_name = Column(db.String(30), nullable=True)
last_name = Column(db.String(30), nullable=True)
phone_number = Column(db.String(20))
fax_number = Column(db.String(20))
minority_owned = Column(db.Boolean())
veteran_owned = Column(db.Boolean())
woman_owned = Column(db.Boolean())
disadvantaged_owned = Column(db.Boolean())
categories = db.relationship('Category',
secondary=category_vendor_association_table,
backref='vendors',
collection_class=set)
opportunities = db.relationship(
'Opportunity',
secondary=opportunity_vendor_association_table,
backref='vendors',
collection_class=set)
subscribed_to_newsletter = Column(db.Boolean(),
default=False,
nullable=False)
@classmethod
def newsletter_subscribers(cls):
'''Query to return all vendors signed up to the newsletter
'''
return cls.query.filter(cls.subscribed_to_newsletter == True).all()
def build_downloadable_row(self):
'''Take a Vendor object and build a list for a .tsv download
Returns:
List of all vendor fields in order for a bulk vendor download
'''
return [
self.first_name, self.last_name, self.business_name, self.email,
self.phone_number, self.minority_owned, self.woman_owned,
self.veteran_owned, self.disadvantaged_owned,
build_downloadable_groups('category_friendly_name',
self.categories),
build_downloadable_groups('title', self.opportunities)
]
def __unicode__(self):
return self.email
