class Settings(SurrogatePK, Model):
'''
Single row settings. Considered key/value pair entries, but figured this would be easier as settings should be limited.
'''
__tablename__ = 'plaid_settings'
efab_user_id = reference_col('users')
efab_user = relationship('User', foreign_keys=[efab_user_id])
mfab_user_id = reference_col('users')
mfab_user = relationship('User', foreign_keys=[mfab_user_id])
plaid_admin_id = reference_col('users')
plaid_admin = relationship('User', foreign_keys=[plaid_admin_id])
name_order_options = [('last_name_first_name', 'Last, First'),
('full_name', 'First Last'),
('username', 'Username'), ('email', 'Email')]
name_order = Column(db.String, default='last_name_first_name')
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def __str__(self):
return str(self.as_dict())
@classmethod
def get_settings(cls):
return cls.query.first()
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
def __repr__(self):
return '<Settings>'
class RevisionLogEntry(SurrogatePK, Model):
__tablename__ = 'revision_log_entries'
parent_id = reference_col('revision_logs')
parent = relationship('RevisionLog')
revision = Column(db.String)
reason = Column(db.Text)
revisioned_by_id = reference_col('users')
revisioned_by = relationship('User')
revisioned_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
__mapper_args__ = {"order_by": desc(revisioned_at)}
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def can_user_edit(self, field_name):
# TODO: Redo this logic, this is currently handled in Jinja2 template
return True
def __str__(self):
return str(self.id)
def __repr__(self):
return '<RevisionLogEntry({0})>'.format(self.id)
class Discrepancy(SurrogatePK, Model):
__tablename__ = 'discrepancies'
descriptor = 'Discrepancy'
discrepancy_number = Column(db.String, nullable=False, default='01')
description = Column(db.Text)
justification = Column(db.Text)
disposition_id = reference_col('dispositions', nullable=True)
disposition = relationship('Disposition')
allowed_states = ['Open', 'Closed']
state = Column(db.String, default='Open')
created_by_id = reference_col('users')
created_by = relationship('User', foreign_keys=[created_by_id])
created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
__mapper_args__ = {
"order_by": discrepancy_number
}
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def is_open(self):
return self.state == 'Open'
def __str__(self):
return self.discrepancy_number
def __repr__(self):
return '<Discrepancy({0})>'.format(self.discrepancy_number)
class AdvancedSearch(SurrogatePK, Model):
__tablename__ = 'advanced_searches'
user_id = reference_col('users')
user = relationship('User', foreign_keys=[user_id])
search_parameters = Column(db.String, nullable=True)
name = Column(db.String, nullable=True)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
class WorkflowLogEntry(SurrogatePK, Model):
__tablename__ = 'workflow_log_entries'
parent_id = reference_col('workflow_logs')
parent = relationship('WorkflowLog')
changed_by_id = reference_col('users')
changed_by = relationship('User')
changed_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
capacity = Column(db.String)
action = Column(db.String)
comment = Column(db.Text)
__mapper_args__ = {"order_by": asc(changed_at)}
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def __str__(self):
return str(self.id)
def __repr__(self):
return '<WorkflowLogEntry({0})>'.format(self.id)
class Document(SurrogatePK, Model):
"""A document that can be tied to a design item, part, ECO, anomaly...etc"""
__tablename__ = 'documents'
path = Column(db.String, nullable=False, unique=True)
title = Column(db.String)
description = Column(db.Text)
uploaded_by_id = reference_col('users', nullable=False)
uploaded_by = relationship('User')
uploaded_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
def __init__(self, uploaded_by=current_user, **kwargs):
"""Create instance."""
db.Model.__init__(self, uploaded_by=uploaded_by, **kwargs)
def get_url(self):
return url_for('api.documents',
document_id=self.id,
document_title=self.title)
def clone_document(self, record):
old_path = os.path.join(current_app.config['UPLOAD_FOLDER'], self.path)
new_basepath = os.path.join(current_app.config['UPLOAD_FOLDER'],
record.get_class_name(), str(record.id),
'documents')
new_path = os.path.join(new_basepath, self.path.split('/')[-1])
if not os.path.exists(new_basepath):
os.makedirs(new_basepath)
copyfile(old_path, new_path)
document = Document.create(path=new_path,
title=self.title,
description=self.description,
uploaded_by=self.uploaded_by,
uploaded_at=self.uploaded_at)
extension = ''.join(Path(document.title).suffixes
) # Should get extensions like .tar.gz as well
filename = '{0}{1}'.format(document.id, extension)
filepath = os.path.join(new_basepath, filename)
os.rename(new_path, filepath)
document.path = os.path.relpath(
new_basepath, current_app.config['UPLOAD_FOLDER']) + '/' + filename
document.save()
return document
def __repr__(self):
"""Represent instance as a unique string."""
return '<Document({title!r},{path!r})>'.format(title=self.title,
path=self.path)
class ChangeLogEntry(SurrogatePK, Model):
__tablename__ = 'change_log_entries'
parent_id = reference_col('change_logs')
parent = relationship('ChangeLog')
action = Column(db.String)
field = Column(db.String)
original_value = Column(db.Text)
new_value = Column(db.Text)
changed_by_id = reference_col('users')
changed_by = relationship('User')
changed_at = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
__mapper_args__ = {"order_by": desc(changed_at)}
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def __str__(self):
return str(self.id)
def __repr__(self):
return '<ChangeLogEntry({0})>'.format(self.id)
class MaterialSpecification(SurrogatePK, Model):
__tablename__ = 'material_specifications'
name = Column(db.String, nullable=False)
# description = Column(db.Text)
material_id = reference_col('materials', nullable=False)
material = relationship('Material', backref='specifications')
__mapper_args__ = {"order_by": name}
def __init__(self, name='Name', description=None, **kwargs):
db.Model.__init__(self, name=name, **kwargs)
def __str__(self):
return self.name
def __repr__(self):
return '<MaterialSpecification({name!r})>'.format(name=self.name)
class ChangeLog(SurrogatePK, Model):
__tablename__ = 'change_logs'
entries = relationship('ChangeLogEntry')
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
self.add_entry(action='Create', changed_by=current_user)
def add_entry(self, **kwargs):
entry = ChangeLogEntry.create(parent=self, **kwargs)
self.entries.append(entry)
self.save()
def __str__(self):
return '{0} entries'.format(len(self.entries))
def __repr__(self):
return '<ChangeLog({0})>'.format(self.id)
class Link(SurrogatePK, Model):
"""A link that can be tied to a design item, part, ECO, anomaly...etc"""
__tablename__ = 'links'
url = Column(db.String, nullable=False)
description = Column(db.Text)
created_by_id = reference_col('users', nullable=False)
created_by = relationship('User')
def __init__(self, url, title, description, created_by_id, **kwargs):
"""Create instance."""
db.Model.__init__(self,
url=url,
description=description,
created_by_id=created_by_id,
**kwargs)
def __repr__(self):
"""Represent instance as a unique string."""
return '<Link({url!r})>'.format(url=self.url)
class RevisionLog(SurrogatePK, Model):
__tablename__ = 'revision_logs'
entries = relationship('RevisionLogEntry')
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def add_entry(self, revision, reason, revisioned_by):
entry = RevisionLogEntry.create(parent=self,
revision=revision,
reason=reason,
revisioned_by=revisioned_by)
self.entries.append(entry)
self.save()
def __str__(self):
return '{0} entries'.format(len(self.entries))
def __repr__(self):
return '<RevisionLog({0})>'.format(self.id)
class Approver(SurrogatePK, Model):
__tablename__ = 'approvers'
approver_id = reference_col('users')
approver = relationship('User', foreign_keys=[approver_id])
capacity = Column(db.String, nullable=False)
approved_at = Column(db.DateTime)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
@classmethod
def get_open_approvals_for_user(cls, user=current_user):
query_results = db.session.query(cls).filter(
and_(cls.approver == user, cls.approved_at == None)).all()
results = []
for approver in query_results:
record = approver.get_record()
if record.state == record.workflow.get_approval_state():
results.append(approver)
return results
def get_record(self):
# TODO: Current relationship is many-to-many, should try to make this a many-to-one
if self.design:
return self.design[0]
elif self.vendor_part:
return self.vendor_part[0]
elif self.product:
return self.product[0]
elif self.vendor_product:
return self.vendor_product[0]
elif self.eco:
return self.eco[0]
elif self.anomaly:
return self.anomaly[0]
elif self.procedure:
return self.procedure[0]
elif self.specification:
return self.specification[0]
elif self.as_run:
return self.as_run[0]
class Bookmark(SurrogatePK, Model):
"""A reference that can be tied to a design item, part, ECO, anomaly...etc"""
__tablename__ = 'bookmarks'
user_id = reference_col('users')
user = relationship('User', foreign_keys=[user_id])
bookmarked_id = Column(db.BigInteger, nullable=False)
bookmarked_class = Column(db.String, nullable=False)
def __init__(self, **kwargs):
db.Model.__init__(self, **kwargs)
def get_name(self):
bookmarked_object = self.get_bookmarked_object()
return bookmarked_object.get_name()
def get_state(self):
bookmarked_object = self.get_bookmarked_object()
return bookmarked_object.state
def get_unique_identifier(self):
bookmarked_object = self.get_bookmarked_object()
return bookmarked_object.get_unique_identifier()
def get_url(self):
bookmarked_object = self.get_bookmarked_object()
return bookmarked_object.get_url()
def get_bookmarked_object(self):
bookmarked_class = self.bookmarked_class
bookmarked_id = self.bookmarked_id
bookmarked_object = get_record_by_id_and_class(bookmarked_id,
bookmarked_class)
return bookmarked_object
def __repr__(self):
"""Represent instance as a unique string."""
return '<Bookmark({0})>'.format(self.id)
class ECO(Record):
__tablename__ = 'ecos'
descriptor = 'ECO'
key = Column(db.String, default=get_next_eco_key, nullable=False)
designs = relationship('Design',
secondary='designs_ecos',
back_populates='ecos')
summary = Column(db.String)
approvers = relationship('Approver',
secondary='ecos_approvers',
order_by='asc(Approver.id)',
backref='eco')
analysis = Column(db.String)
corrective_action = Column(db.String)
documents = relationship('Document', secondary='ecos_documents')
links = relationship('Link', secondary='ecos_links')
images = relationship('Image', secondary='ecos_images')
project_id = reference_col('projects')
project = relationship('Project')
workflow = ECOWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = ECOPermissions()
__mapper_args__ = {"order_by": key}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@classmethod
def get_by_key(cls, key):
return cls.query.filter_by(key=key).first()
@classmethod
def find_all_ecos_for_user(cls, user):
results = cls.query.filter_by(owner=user).order_by(cls.key).all()
return results
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
sql = "SELECT * FROM {0} WHERE (SELECT CONCAT(key, ' ', name) ILIKE :query)".format(
cls.__tablename__)
results = db.session.query(cls).from_statement(
db.text(sql).params(query=query)).all()
return results
@classmethod
def advanced_search(cls, params):
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'eco_number_query':
formatted_query = format_match_query(
params['eco_number_query_type'],
params['eco_number_query'])
query = query.filter(cls.key.ilike(formatted_query))
elif attr == 'text_fields_query':
formatted_query = format_match_query(
'includes', params['text_fields_query'])
query = query.filter(
cls.name.ilike(formatted_query)
| cls.summary.ilike(formatted_query))
elif attr == 'created_on_start':
query = query.filter(
cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(
cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(
cls.state.in_(cls.workflow.open_states))
elif attr == 'exclude_obsolete':
query = query.filter(
cls.state != cls.workflow.obsolete_state)
return query.all()
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
return approval_errors
def get_unique_identifier(self):
return self.key
def get_url(self, external=False):
return url_for('eco.view_eco', key=self.key, _external=external)
def __repr__(self):
"""Represent instance as a unique string."""
return '<ECO({id!r},{key!r})>'.format(id=self.id, key=self.key)
class Part(BaseRecord):
__tablename__ = 'parts'
part_identifier = Column(db.Integer, nullable=False)
name = Column(db.String, nullable=True)
components = relationship('PartComponent',
foreign_keys='PartComponent.parent_id')
current_best_estimate = Column(db.Float, default=0.0, nullable=False)
uncertainty = Column(db.Float, default=0.0, nullable=False)
predicted_best_estimate = Column(db.Float, default=0.0, nullable=False)
design_id = reference_col('designs')
design = relationship('Design', back_populates='parts')
material_id = reference_col('materials', nullable=True)
material = relationship('Material')
material_specification_id = reference_col('material_specifications',
nullable=True)
material_specification = relationship('MaterialSpecification')
inseparable_component = Column(db.Boolean, default=False)
procedures = relationship('Procedure', secondary='procedures_parts')
__table_args__ = (db.UniqueConstraint(
'part_identifier', 'design_id',
name='part_identifier_design_unique'), )
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def part_number(self):
return '{0}-{1}'.format(self.design.design_number,
self.part_identifier)
@property
def revision(self):
return self.design.revision
@classmethod
def get_all_parts(cls):
results = cls.query.all()
return results
@classmethod
def typeahead_search(cls, query, part_id):
query = '%{0}%'.format(
query) # Pad query for an ILIKE search for design_number and
# Search in parts for design_numbers, part_identifiers or associated design names that match
# Union with inseparable components for this specific revision of design
sql_part_ids = '''
WITH matching_designs AS (
SELECT d.design_number AS dn, MAX(d.revision) AS rev
FROM parts p, designs d
WHERE p.design_id = d.id
AND (SELECT CONCAT(d.design_number, '-', cast(p.part_identifier as text), ' ', d.name) ILIKE :query)
GROUP BY d.design_number
)
SELECT p.id
FROM designs d, parts p, matching_designs md
WHERE p.design_id = d.id
AND d.design_number = md.dn
AND d.revision = md.rev
AND NOT inseparable_component
UNION
SELECT p.id
FROM parts p
WHERE inseparable_component
AND design_id = (SELECT design_id FROM parts WHERE id = :part_id)
'''
# Get ids of parts belonging to different revs of same design number
sql_design_number_part_ids = 'SELECT id FROM designs WHERE design_number = (SELECT design_number FROM designs WHERE id = (SELECT design_id FROM parts WHERE id = :part_id))'
# Get ids of parts belonging to inseparable components for this specific rev of design, EXCEPT
sql_inseparable_components_part_ids = 'SELECT id FROM parts WHERE inseparable_component AND design_id = (SELECT design_id FROM parts WHERE id = :part_id)'
# Get ids of parts already added as part component to this part, UNION
sql_already_added_part_ids = 'SELECT part_id FROM part_components WHERE parent_id = :part_id AND part_id IS NOT NULL'
# Get ids of parts where this part is an NLA, UNION
sql_nla_part_ids = 'SELECT parent_id FROM part_components WHERE part_id = :part_id'
# Use the above selects to get part ids to exclude
sql_exclude_part_ids = 'SELECT id FROM parts WHERE design_id IN ({0}) EXCEPT {1} UNION {2} UNION {3}'.format(
sql_design_number_part_ids, sql_inseparable_components_part_ids,
sql_already_added_part_ids, sql_nla_part_ids)
# Search in parts for matches excluding self and already added parts
sql = 'SELECT * FROM parts WHERE id != :part_id AND id NOT in ({0}) AND id IN ({1})'.format(
sql_exclude_part_ids, sql_part_ids)
results = db.session.query(cls).from_statement(
db.text(sql).params(query=query, part_id=part_id)).all()
return results
@classmethod
def typeahead_search_all_but_self(cls, query, part_id):
query = '%{0}%'.format(
query) # Pad query for an ILIKE search for design_number and
# Search in parts for design_numbers, part_identifiers or associated design names that match, excluding self
sql_part_ids = "SELECT DISTINCT ON (d.design_number, p.part_identifier) p.id FROM parts p, designs d WHERE p.design_id = d.id AND (SELECT CONCAT(d.design_number, '-', cast(p.part_identifier as text), ' ', d.name) ILIKE :query) ORDER BY d.design_number, p.part_identifier, d.revision DESC"
sql = 'SELECT * FROM parts p WHERE p.id != :part_id AND p.id IN ({0})'.format(
sql_part_ids)
results = db.session.query(cls).from_statement(
db.text(sql).params(query=query, part_id=part_id)).all()
return results
@classmethod
def typeahead_search_all(cls, query):
query = '%{0}%'.format(
query) # Pad query for an ILIKE search for design_number and
# Search in parts for design_numbers, part_identifiers or associated design names that match
sql = "SELECT DISTINCT ON (d.design_number, p.part_identifier) p.* FROM parts p, designs d WHERE p.design_id = d.id AND (SELECT CONCAT(d.design_number, '-', cast(p.part_identifier as text), ' ', d.name) ILIKE :query) ORDER BY d.design_number, p.part_identifier, d.revision DESC"
results = db.session.query(cls).from_statement(
db.text(sql).params(query=query)).all()
return results
def get_nlas_for_part(self):
sql_design_ids = 'SELECT DISTINCT ON (design_number) id FROM designs ORDER BY design_number, revision DESC' # Unique design_number, highest revision
sql_pc_ids = 'SELECT parent_id FROM part_components WHERE part_id = :part_id'
sql = 'SELECT * FROM parts WHERE id IN ({0}) AND design_id IN ({1})'.format(
sql_pc_ids, sql_design_ids)
parents = db.session.query(Part).from_statement(
db.text(sql).params(part_id=self.id)).all()
return parents
def get_products_for_part(self):
# TODO: Remove this and change to for_design / for_design_number
from pid.product.models import Product
results = Product.query.filter_by(part_id=self.id).all()
return results
def get_parts_for_design_revisions(self):
from pid.design.models import Design
revisions = Design.query.filter_by(
design_number=self.design.design_number).all()
revisions.sort(key=lambda x: (len(x.revision), x.revision)
) # Sort them first alphabetically, then by length
design_ids = [x.id for x in revisions]
results = Part.query.filter(
and_(Part.design_id.in_(design_ids),
Part.part_identifier == self.part_identifier)).all()
return results
def get_builds_for_design_number_and_part_identifier(self):
from pid.product.models import Build
sql = 'SELECT b.* FROM builds b, parts p, designs d WHERE b.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier ORDER BY b.build_identifier'
results = db.session.query(Build).from_statement(
db.text(sql).params(design_number=self.design.design_number,
part_identifier=self.part_identifier)).all()
return results
@classmethod
def get_nlas_for_vendor_part(cls, vendor_part):
sql_design_ids = 'SELECT DISTINCT ON (design_number) id FROM designs ORDER BY design_number, revision DESC' # Unique design_number, highest revision
sql_pc_ids = 'SELECT parent_id FROM part_components WHERE vendor_part_id = :vendor_part_id'
sql = 'SELECT * FROM parts WHERE id IN ({0}) AND design_id IN ({1})'.format(
sql_pc_ids, sql_design_ids)
parents = db.session.query(cls).from_statement(
db.text(sql).params(vendor_part_id=vendor_part.id)).all()
return parents
def add_component(self, part_component):
self.components.append(part_component)
db.session.add(self)
db.session.commit()
def update_mass(self):
# %Unc = (PBE/CBE) - 1 (*100)
sum_cbe = 0.0
sum_pbe = 0.0
if self.components:
for component in self.components:
if component.part:
sum_cbe += (component.part.current_best_estimate *
component.quantity)
sum_pbe += (component.part.predicted_best_estimate *
component.quantity)
elif component.vendor_part:
sum_cbe += (component.vendor_part.current_best_estimate *
component.quantity)
sum_pbe += (component.vendor_part.predicted_best_estimate *
component.quantity)
if sum_cbe == 0.0:
unc = 0.0
else:
unc = ((sum_pbe / sum_cbe) - 1) * 100
else:
sum_cbe = self.current_best_estimate
unc = self.uncertainty
sum_pbe = self.predicted_best_estimate
self.update(current_best_estimate=sum_cbe,
uncertainty=unc,
predicted_best_estimate=sum_pbe)
# TODO: In a large tree, this might take a while, should figure out a queue to do this with
self.update_parents_mass(
) # In case there are parts depending on this as a part_component
def update_parents_mass(self):
""" Update the mass of all parents of this part. Call this when updating mass of a part """
part_components = PartComponent.query.filter_by(part=self).all()
for part_component in part_components:
part_component.parent.update_mass()
def can_user_edit(self, field_name):
return self.design.can_user_edit(field_name)
def get_name(self):
if self.name:
return self.name
return self.design.name
def get_unique_identifier(self):
return self.part_number
def get_url(self):
return url_for('design.view_design',
design_number=self.design.design_number,
revision=self.design.revision)
def __str__(self):
return '{0}-{1} {2}'.format(self.design.design_number,
self.design.revision, self.part_identifier)
def __repr__(self):
"""Represent instance as a unique string."""
return '<Part({0})>'.format(self.part_number)
class PartComponent(SurrogatePK, Model):
__tablename__ = 'part_components'
parent_id = reference_col('parts')
parent = relationship('Part', foreign_keys=[parent_id])
quantity = Column(db.Integer, nullable=False, default=1)
part_id = reference_col('parts', nullable=True)
part = relationship('Part', foreign_keys=[part_id])
vendor_part_id = reference_col('vendor_parts', nullable=True)
vendor_part = relationship('VendorPart')
ordering = Column(db.Integer)
__mapper_args__ = {"order_by": ordering}
def __init__(self, **kwargs):
"""Create instance."""
lowest_part_component = PartComponent.query.filter_by(
parent_id=kwargs['parent_id']).order_by(
PartComponent.ordering.desc()).first()
if lowest_part_component:
ordering = lowest_part_component.ordering + 1
else:
ordering = 0
db.Model.__init__(self, ordering=ordering, **kwargs)
@property
def component(self):
return self.part if self.part else self.vendor_part
@classmethod
def find_highest_free_ordering_plus_one(cls):
results = db.session.query(db.func.max(cls.ordering)).first()
if results[0] is None:
return 1
return int(results[0]) + 1
@classmethod
def get_components_by_part_id(cls, part_id):
# Ensures part, part.design, and vendor_part references are all loaded
return cls.query.filter_by(parent_id=part_id).options(
subqueryload('part'), joinedload('part.design'),
subqueryload('vendor_part')).all() # noqa
@classmethod
def update_part_component_references(cls, design):
"""
for new_part in new_design.parts:
find old_versions of this new_part
for part_component in (select part_components where part_id in old_versions):
part_component.part = new_part
"""
for part in design.parts:
sql_design_ids = 'SELECT id FROM designs WHERE design_number = :design_number'
sql_parts = 'SELECT * FROM parts WHERE part_identifier = :part_identifier AND design_id IN ({0})'.format(
sql_design_ids)
parts = db.session.query(Part).from_statement(
db.text(sql_parts).params(
design_number=design.design_number,
part_identifier=part.part_identifier)).all()
for old_part in parts:
sql = 'SELECT * FROM part_components WHERE part_id = :part_id'
part_components = db.session.query(cls).from_statement(
db.text(sql).params(part_id=old_part.id)).all()
for part_component in part_components:
part_component.part = part
part_component.save()
def can_user_edit(self, field_name):
return self.parent.can_user_edit(field_name)
def __str__(self):
if (self.part):
return '{0} ({1})'.format(self.part.part_number, self.quantity)
else:
return '{0} ({1})'.format(self.vendor_part.part_number,
self.quantity)
def __repr__(self):
"""Represent instance as a unique string."""
return '<PartComponent({0})>'.format(self.id)
def workflow_log(cls):
return relationship('WorkflowLog')
def revision_log(cls):
return relationship('RevisionLog')
def change_log(cls):
return relationship('ChangeLog')
def thumbnail(cls):
return relationship('Image')
def created_by(cls):
return relationship('User', foreign_keys=cls.created_by_id)
def owner(cls):
return relationship('User', foreign_keys=cls.owner_id)
class Task(ThumbnailMixin, ChangeLogMixin, SurrogatePK, Model):
__tablename__ = 'tasks'
descriptor = 'Task'
task_number = Column(db.String,
default=get_next_task_number,
nullable=False)
title = Column(db.String)
summary = Column(db.String)
urgency_states = ["At Your Leisure", "Important", "Urgent", "SoF"]
urgency = Column(db.String, default='At Your Leisure')
allowed_states = [
'Requested', 'Acknowledged', 'In Work', 'Complete', 'Rejected'
]
state = Column(db.String, default='Requested')
documents = relationship('Document', secondary='tasks_documents')
links = relationship('Link', secondary='tasks_links')
images = relationship('Image', secondary='tasks_images')
assigned_to_id = reference_col('users', nullable=False)
assigned_to = relationship('User', foreign_keys=[assigned_to_id])
requested_by_id = reference_col('users', nullable=False)
requested_by = relationship('User', foreign_keys=[requested_by_id])
requested_on = Column(db.DateTime,
nullable=False,
default=dt.datetime.utcnow)
need_date = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
permissions = TaskPermissions()
def __init__(self, requested_by, **kwargs):
ChangeLogMixin.__init__(self)
db.Model.__init__(self, requested_by=requested_by, **kwargs)
@property
def references_to(self):
results = Reference.query.filter_by(
by_id=self.id, by_class=self.get_class_name()).all()
return results
@classmethod
def get_by_task_number(cls, task_number):
return cls.query.filter_by(task_number=task_number).first()
@classmethod
def find_all_tasks_for_user(cls, user, type):
if type == 'assigned':
results = cls.query.filter_by(assigned_to=user).order_by(
cls.need_date).all()
else:
results = cls.query.filter_by(requested_by=user).order_by(
cls.need_date).all()
return results
def can_user_edit(self, field_name):
if current_user.is_admin():
return True # Admins can do anything always
role = 'all'
if current_user == self.assigned_to or current_user == self.requested_by:
role = 'owner'
elif current_user.is_superuser():
role = 'superuser'
state = 'open'
return self.permissions.get_permissions().get(state, False).get(
role, False).get(field_name)
def get_name(self):
return self.title
def get_unique_identifier(self):
return '{0}'.format(self.task_number)
def get_url(self, external=False):
return url_for('task.view_task',
task_number=self.task_number,
_external=external)
def get_descriptive_url(self):
return '<a href="{0}">{1} - {2}</a>'.format(
self.get_url(), self.get_unique_identifier(), self.get_name())
def __str__(self):
return str(self.id)
def __repr__(self):
return '<Task({0})>'.format(self.id)
class VendorBuild(BaseRecord):
__tablename__ = 'vendor_builds'
build_identifier = Column(db.String, nullable=False, default='001')
vendor_part_id = reference_col('vendor_parts')
vendor_part = relationship('VendorPart')
notes = Column(db.Text)
purchase_order = Column(db.String)
vendor_products = relationship('VendorProduct', back_populates='vendor_build', order_by="VendorProduct.serial_number")
documents = relationship('Document', secondary='vendor_builds_documents')
discrepancies = relationship('Discrepancy', secondary='vendor_builds_discrepancies')
vendor_id = reference_col('companies')
vendor = relationship('Company', foreign_keys=[vendor_id])
manufacturer_id = reference_col('companies')
manufacturer = relationship('Company', foreign_keys=[manufacturer_id])
permissions = VendorBuildPermissions()
__table_args__ = (db.UniqueConstraint('build_identifier', 'vendor_part_id', name='build_identifier_vendor_part_unique'),)
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def build_number(self):
return '{0}.{1}'.format(self.vendor_part.part_number, self.build_identifier)
@property
def discrepancy_number(self):
return '{0}-{1}'.format(self.vendor_part.part_number, self.build_identifier)
@classmethod
def get_next_build_identifier_for_vendor_part(cls, vendor_part):
results = cls.query.with_entities(cls.build_identifier).filter_by(vendor_part=vendor_part).all()
if len(results) == 0:
build_identifier = 1
else:
resultset = [row[0] for row in results]
resultset.sort()
build_identifier = int(resultset[-1]) + 1
return '{0:03d}'.format(build_identifier)
def find_all_build_identifiers(self):
results = VendorBuild.query.with_entities(VendorBuild.build_identifier).order_by(VendorBuild.build_identifier.asc()).filter_by(vendor_part=self.vendor_part).all()
resultset = [row[0] for row in results]
return resultset
def can_user_edit(self, field_name):
# Build state is always open, so need to override can_user_edit
if current_user.is_admin():
return True # Admins can do anything always
role = 'all'
if current_user == self.owner:
role = 'owner'
elif current_user.is_superuser():
role = 'superuser'
state = 'open'
return self.permissions.get_permissions().get(state, False).get(role, False).get(field_name)
def __str__(self):
return self.build_number
def __repr__(self):
return '<VendorBuild({0})>'.format(self.build_number)
class Procedure(RevisionRecord):
__tablename__ = 'procedures'
descriptor = 'Procedure'
procedure_number = Column(db.String,
default=get_next_procedure_number,
nullable=False)
summary = Column(db.String)
approvers = relationship('Approver',
secondary='procedures_approvers',
order_by='asc(Approver.id)',
backref='procedure')
parts = relationship('Part', secondary='procedures_parts')
vendor_parts = relationship('VendorPart',
secondary='procedures_vendor_parts')
documents = relationship('Document', secondary='procedures_documents')
links = relationship('Link', secondary='procedures_links')
images = relationship('Image', secondary='procedures_images')
as_runs = relationship('AsRun', back_populates='procedure')
project_id = reference_col('projects')
project = relationship('Project')
workflow = ProcedureWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = ProcedurePermissions()
__table_args__ = (db.UniqueConstraint(
'procedure_number',
'revision',
name='procedure_number_revision_unique'), )
__mapper_args__ = {"order_by": procedure_number}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def identifier(self):
return '{0}-{1}'.format(self.procedure_number, self.revision)
@property
def references_by(self):
# Override base method due to revisions
sql_revision_ids = 'SELECT id FROM procedures WHERE procedure_number = :procedure_number ORDER BY revision'
sql = 'SELECT * FROM "references" WHERE to_id IN ({0}) AND to_class = :class_name'.format(
sql_revision_ids)
query_results = db.session.query(Reference).from_statement(
db.text(sql).params(procedure_number=self.procedure_number,
class_name=self.get_class_name())).all()
results = {r.get_url_by(): r for r in query_results}.values()
return results
@classmethod
def get_by_procedure_number(cls, procedure_number):
return cls.query.filter_by(procedure_number=procedure_number).first()
@classmethod
def get_by_procedure_number_and_revision(cls, procedure_number, revision):
return cls.query.filter_by(procedure_number=procedure_number,
revision=revision).first()
@classmethod
def find_all_procedures_for_user(cls, user):
results = cls.query.filter_by(owner=user).order_by(
cls.procedure_number).all()
return results
@classmethod
def find_all_distinct_procedures_for_user(cls, user):
results = cls.query.filter_by(owner=user).distinct(
cls.procedure_number).order_by(cls.procedure_number,
cls.revision.desc()).all()
return results
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
sql = "SELECT DISTINCT ON (procedure_number) * FROM {0} WHERE (SELECT CONCAT(procedure_number, ' ', name) ILIKE :query) ORDER BY procedure_number, revision DESC".format(
cls.__tablename__)
results = db.session.query(cls).from_statement(
db.text(sql).params(query=query)).all()
return results
@classmethod
def advanced_search(cls, params):
from pid.part.models import Part
from pid.design.models import Design
from pid.vendorpart.models import VendorPart
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'proc_number_query':
formatted_query = Utils.format_match_query(
params['proc_number_query_type'],
params['proc_number_query'])
query = query.filter(
cls.procedure_number.ilike(formatted_query))
elif attr == 'part_number_query':
formatted_query = Utils.format_match_query(
params['part_number_query_type'],
params['part_number_query'])
query = query.filter(
cls.parts.any(
Part.design.has(
Design.design_number.ilike(formatted_query)))
| cls.vendor_parts.any(
VendorPart.part_number.ilike(formatted_query)))
elif attr == 'text_fields_query':
formatted_query = Utils.format_match_query(
'includes', params['text_fields_query'])
query = query.filter(
cls.name.ilike(formatted_query)
| cls.summary.ilike(formatted_query))
elif attr == 'created_on_start':
query = query.filter(
cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(
cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(
cls.state.in_(cls.workflow.open_states))
elif attr == 'exclude_obsolete':
query = query.filter(
cls.state != cls.workflow.obsolete_state)
return query.distinct(cls.procedure_number).order_by(
cls.procedure_number.desc(), cls.revision.desc()).all()
def find_all_revisions(self):
results = Procedure.query.filter_by(
procedure_number=self.procedure_number).all()
return Utils.find_all_revisions(results)
def find_latest_revision(self):
results = Procedure.query.with_entities(Procedure.revision).filter_by(
procedure_number=self.procedure_number).all()
return Utils.find_latest_revision(results)
def find_next_revision(self):
results = Procedure.query.with_entities(Procedure.revision).filter_by(
procedure_number=self.procedure_number).order_by(
Procedure.revision).all()
return Utils.find_next_revision(results)
def find_all_as_runs_numbers(self):
from pid.asrun.models import AsRun
as_runs = AsRun.query.filter_by(
procedure_number=self.procedure_number).all()
as_run_numbers = []
for as_run in as_runs:
as_run_numbers.append({
'id': as_run.id,
'number': str(as_run.as_run_number).zfill(3)
})
return as_run_numbers
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
return approval_errors
def get_latest_revision_unique_identifier(self):
return '{0}-{1}'.format(self.procedure_number,
self.find_latest_revision())
def get_latest_revision_url(self):
# BEWARE: This function will always point to latest revision of design
return url_for('procedure.view_procedure',
procedure_number=self.procedure_number,
revision=self.find_latest_revision())
def get_unique_identifier(self):
return '{0}-{1}'.format(self.procedure_number, self.revision)
def get_url(self, external=False):
return url_for('procedure.view_procedure',
procedure_number=self.procedure_number,
revision=self.revision,
_external=external)
def find_all_as_runs(self):
from pid.asrun.models import AsRun
return AsRun.query.filter_by(
procedure_number=self.procedure_number).all()
def __repr__(self):
"""Represent instance as a unique string."""
return '<Procedure({id!r},{procedure_number!r})>'.format(
id=self.id, procedure_number=self.procedure_number)
class Product(NamelessRecord):
__tablename__ = 'products'
descriptor = 'Product'
serial_number = Column(db.String, nullable=False)
part_id = reference_col('parts')
part = relationship('Part')
revision = Column(db.String, nullable=False)
summary = Column(db.String)
notes = Column(db.Text)
approvers = relationship('Approver', secondary='products_approvers', order_by='asc(Approver.id)', backref='product')
allowed_types = ['SN', 'LOT', 'STOCK']
product_type = Column(db.String, default='SN')
measured_mass = Column(db.Float, default=0.0)
hardware_type_id = reference_col('hardware_types')
hardware_type = relationship('HardwareType')
project_id = reference_col('projects')
project = relationship('Project')
build_id = reference_col('builds')
build = relationship('Build', back_populates='products')
documents = relationship('Document', secondary='products_documents')
images = relationship('Image', secondary='products_images')
links = relationship('Link', secondary='products_links')
components = relationship('ProductComponent', foreign_keys='ProductComponent.parent_id')
extra_components = relationship('ExtraProductComponent', foreign_keys='ExtraProductComponent.parent_id')
discrepancies = relationship('Discrepancy', secondary='products_discrepancies')
as_runs = relationship('AsRun', secondary='as_runs_products', order_by='desc(AsRun.created_at)')
workflow = ProductWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = ProductPermissions()
__table_args__ = (db.UniqueConstraint('serial_number', 'part_id', name='serial_number_part_unique'),)
__mapper_args__ = {
"order_by": serial_number
}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def product_number(self):
return '{0} {1}'.format(self.part.part_number, self.serial_number)
@property
def discrepancy_number(self):
return '{0}-{1}'.format(self.part.part_number, self.serial_number)
@classmethod
def get_product_by_product_number(cls, design_number, part_identifier, serial_number):
sql = 'SELECT prod.* FROM products prod, parts p, designs d WHERE prod.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier AND prod.serial_number = :serial_number'
results = db.session.query(cls).from_statement(db.text(sql).params(design_number=design_number, part_identifier=part_identifier, serial_number=serial_number)).first()
return results
@classmethod
def get_serial_numbers_for_design_number_and_part_identifier(cls, design_number, part_identifier):
sql = 'SELECT prod.* FROM products prod, parts p, designs d WHERE prod.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier'
results = db.session.query(cls).from_statement(db.text(sql).params(design_number=design_number, part_identifier=part_identifier)).all()
resultset = [p.serial_number for p in results]
resultset.sort()
return resultset
@classmethod
def get_next_lot_number_for_design_number_and_part_identifier(cls, design_number, part_identifier):
sql = 'SELECT prod.* FROM products prod, parts p, designs d WHERE prod.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier AND prod.product_type = \'LOT\''
results = db.session.query(cls).from_statement(db.text(sql).params(design_number=design_number, part_identifier=part_identifier)).all()
if len(results) == 0:
lot_number = 1
else:
resultset = [p.serial_number for p in results]
resultset.sort()
lot_number = int(resultset[-1].replace('L', '')) + 1
return 'L{0:03d}'.format(lot_number)
@classmethod
def find_all_products_for_user(cls, user):
results = Product.query.filter_by(owner=user).join(cls.part).join(Part.design).order_by(Design.design_number, Part.part_identifier, cls.serial_number).all()
return results
def get_installed_ins(self):
results = ProductComponent.query.filter_by(product=self).all()
results.extend(ExtraProductComponent.query.filter_by(product=self).all())
return results
def get_product_components(self):
return ProductComponent.query.filter_by(parent_id=self.id).all()
def get_extra_product_components(self):
return ExtraProductComponent.query.filter_by(parent_id=self.id).all()
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
sql_name = "SELECT CASE WHEN p.name IS NOT NULL THEN p.name ELSE d.name END AS concat_name"
sql = "SELECT prod.* FROM products prod, parts p, designs d WHERE prod.part_id = p.id AND p.design_id = d.id AND (SELECT CONCAT(d.design_number, '-', p.part_identifier, '-', prod.serial_number, ' ', ({0})) ILIKE :query)".format(sql_name)
results = db.session.query(cls).from_statement(db.text(sql).params(query=query)).all()
return results
@classmethod
def advanced_search(cls, params):
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'product_part_number_query':
formatted_query = format_match_query(params['product_part_number_query_type'], params[attr])
query = query.filter(cls.part.has(Part.design.has(Design.design_number.ilike(formatted_query))))
elif attr == 'product_serial_number_query':
formatted_query = format_match_query(params['product_serial_number_query_type'], params[attr])
query = query.filter(func.cast(cls.serial_number, types.Text).ilike(formatted_query))
elif attr == 'text_fields_query':
formatted_query = format_match_query('includes', params['text_fields_query'])
query = query.filter(cls.summary.ilike(formatted_query) | cls.notes.ilike(formatted_query))
elif attr == 'open_discrepancies':
query = query.filter(cls.discrepancies.any(Discrepancy.state.in_(['Open'])))
elif attr == 'created_on_start':
query = query.filter(cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(cls.state.in_(cls.workflow.open_states))
elif attr =='exclude_obsolete':
query = query.filter(cls.state != cls.workflow.obsolete_state)
elif attr == 'material_id':
query = query.filter(cls.part.has(Part.material_id == params[attr]))
return query.all()
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
# Check if open discrepancies
for discrepancy in self.discrepancies:
if discrepancy.is_open():
approval_errors.append('Discrepancy {0} must be resolved.'.format(discrepancy.discrepancy_number))
return approval_errors
def get_name(self):
return self.part.get_name()
def get_unique_identifier(self):
return '{0}-{1}'.format(self.part.part_number, self.serial_number)
def get_url(self, external=False):
return url_for('product.view_product', design_number=self.part.design.design_number,
part_identifier=self.part.part_identifier, serial_number=self.serial_number, _external=external)
def __str__(self):
return self.product_number
def __repr__(self):
return '<Product({0})>'.format(self.product_number)
class VendorPart(Record):
__tablename__ = 'vendor_parts'
descriptor = 'Vendor Part'
part_number = Column(db.String, nullable=False)
current_best_estimate = Column(db.Float, default=0.0, nullable=False)
uncertainty = Column(db.Float, default=0.0, nullable=False)
predicted_best_estimate = Column(db.Float, default=0.0, nullable=False)
material_id = reference_col('materials', nullable=True)
material = relationship('Material')
material_specification_id = reference_col('material_specifications', nullable=True)
material_specification = relationship('MaterialSpecification')
approvers = relationship('Approver', secondary='vendor_parts_approvers',
order_by='asc(Approver.id)', backref='vendor_part')
summary = Column(db.String, nullable=True)
notes = Column(db.Text, nullable=True)
project_id = reference_col('projects')
project = relationship('Project')
vendor_id = reference_col('companies')
vendor = relationship('Company')
procedures = relationship('Procedure', secondary='procedures_vendor_parts')
documents = relationship('Document', secondary='vendor_parts_documents')
images = relationship('Image', secondary='vendor_parts_images')
links = relationship('Link', secondary='vendor_parts_links')
anomalies = relationship('Anomaly', secondary='vendor_parts_anomalies',
order_by='desc(Anomaly.created_at)', back_populates='vendor_parts')
workflow = VendorPartWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = VendorPartPermissions()
__table_args__ = (db.UniqueConstraint('part_number', name='part_number_unique'),)
__mapper_args__ = {
"order_by": part_number
}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def design_number(self):
return self.part_number
@classmethod
def get_by_part_number(cls, part_number):
return cls.query.filter_by(part_number=part_number).first()
@classmethod
def get_all_vendor_parts(cls):
results = cls.query.all()
return results
@classmethod
def find_all_vendor_parts_for_user(cls, user):
results = cls.query.filter_by(owner=user).order_by(cls.part_number).all()
return results
@classmethod
def typeahead_search(cls, query, part_id):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
# Search in vendor parts for part_number or name that matches
sql_vendor_ids = "SELECT id FROM {0} WHERE (SELECT CONCAT(part_number, ' ', name) ILIKE :query)".format(cls.__tablename__)
# Get ids of vendor parts already added as part_components, excluding part_components made up of parts
sql_pc_ids = 'SELECT vendor_part_id FROM part_components WHERE parent_id = :part_id AND vendor_part_id IS NOT NULL'
# Search in vendor parts, excluding self and already added parts
sql = 'SELECT * FROM {0} WHERE id NOT in ({1}) AND id IN ({2})'.format(cls.__tablename__, sql_pc_ids, sql_vendor_ids)
results = db.session.query(cls).from_statement(db.text(sql).params(query=query, part_id=part_id)).all()
# results = VendorPart.query.whooshee_search(query).all()
return results
@classmethod
def advanced_search(cls, params):
from pid.anomaly.models import Anomaly
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'part_number_query':
formatted_query = format_match_query(params['part_number_query_type'], params['part_number_query'])
query = query.filter(cls.part_number.ilike(formatted_query))
elif attr == 'text_fields_query':
formatted_query = format_match_query('includes', params['text_fields_query'])
query = query.filter(cls.name.ilike(formatted_query) | cls.notes.ilike(formatted_query) |
cls.summary.ilike(formatted_query))
elif attr == 'open_anomalies':
query = query.filter(cls.anomalies.any(Anomaly.state.in_(Anomaly.workflow.open_states)))
elif attr == 'created_on_start':
query = query.filter(cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(cls.state.in_(cls.workflow.open_states))
elif attr =='exclude_obsolete':
query = query.filter(cls.state != cls.workflow.obsolete_state)
if 'open_anomalies' not in params:
query = query.distinct(cls.part_number)
return query.order_by(cls.part_number.desc()).all()
def get_vendor_builds_for_vendor_part(self):
from pid.vendorproduct.models import VendorBuild
results = VendorBuild.query.filter_by(vendor_part=self).all()
return results
def update_parents_mass(self):
""" Update the mass of all parents of this part. Call this when updating mass of a part """
from pid.part.models import PartComponent
# TODO: rename
part_components = PartComponent.query.filter_by(vendor_part=self).all()
for part_component in part_components:
part_component.parent.update_mass()
def get_procedures(self):
return self.get_distinct_procedures()
def get_distinct_procedures(self):
from pid.procedure.models import Procedure
procedures = Procedure.query.filter(Procedure.vendor_parts.contains(self))\
.order_by(Procedure.procedure_number, Procedure.revision.desc()).distinct(Procedure.procedure_number).all()
procedures.sort(key=lambda x: x.created_at, reverse=True) # Sort by newest first
return procedures
def get_products_for_part(self):
from pid.product.models import VendorProduct
results = VendorProduct.query.filter_by(vendor_part_id=self.id).all()
return results
def get_nlas_for_vendor_part(self):
return Part.get_nlas_for_vendor_part(self)
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
# Check if open anomalies
for anomaly in self.anomalies:
if anomaly.is_open():
approval_errors.append('{0} must be resolved.'.format(anomaly.get_unique_identifier()))
return approval_errors
def get_unique_identifier(self):
return self.part_number
def get_url(self, external=False):
return url_for('vendorpart.view_vendor_part', part_number=self.part_number, _external=external)
def __str__(self):
return self.part_number
def __repr__(self):
"""Represent instance as a unique string."""
return '<VendorPart({0})>'.format(self.part_number)
class Design(RevisionRecord):
__tablename__ = 'designs'
descriptor = 'Design'
design_number = Column(db.String, nullable=False)
summary = Column(db.String)
notes = Column(db.Text)
documents = relationship('Document', secondary='designs_documents')
anomalies = relationship('Anomaly', secondary='designs_anomalies',
order_by='desc(Anomaly.created_at)', back_populates='designs')
ecos = relationship('ECO', secondary='designs_ecos', order_by='desc(ECO.created_at)', back_populates='designs')
approvers = relationship('Approver', secondary='designs_approvers', order_by='asc(Approver.id)', backref='design')
project_id = reference_col('projects')
project = relationship('Project')
parts = relationship('Part', back_populates='design', order_by="Part.part_identifier")
links = relationship('Link', secondary='designs_links')
images = relationship('Image', secondary='designs_images')
export_control = Column(db.Boolean(), default=False)
workflow = DesignWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = DesignPermissions()
__table_args__ = (db.UniqueConstraint('design_number', 'revision', name='design_number_revision_unique'),)
__mapper_args__ = {
"order_by": design_number
}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def references_by(self):
# Override base method due to revisions
sql_revision_ids = 'SELECT id FROM designs WHERE design_number = :design_number ORDER BY revision'
sql = 'SELECT * FROM "references" WHERE to_id IN ({0}) AND to_class = :class_name'.format(sql_revision_ids)
query_results = db.session.query(Reference).from_statement(db.text(sql).params(design_number=self.design_number, class_name=self.get_class_name())).all()
results = {r.get_url_by(): r for r in query_results}.values()
return results
@classmethod
def find_all_design_numbers(cls):
results = cls.query.with_entities(cls.design_number).order_by(cls.design_number.asc()).distinct().all()
resultset = [int(row[0]) for row in results]
return resultset
@classmethod
def find_all_designs_for_user(cls, user):
results = cls.query.filter_by(owner=user).distinct(cls.design_number).order_by(cls.design_number, cls.revision.desc()).all()
return results
@classmethod
def get_by_design_number_and_revision(cls, design_number, revision):
return cls.query.filter_by(design_number=design_number, revision=revision).first()
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
sql = "SELECT DISTINCT ON (design_number) * FROM {0} WHERE (SELECT CONCAT(design_number, ' ', name) ILIKE :query) ORDER BY design_number, revision DESC".format(cls.__tablename__)
results = db.session.query(cls).from_statement(db.text(sql).params(query=query)).all()
return results
@classmethod
def advanced_search(cls, params):
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'design_number_query':
formatted_query = format_match_query(params['design_number_query_type'], params['design_number_query'])
query = query.filter(cls.design_number.ilike(formatted_query))
elif attr == 'text_fields_query':
formatted_query = format_match_query('includes', params['text_fields_query'])
query = query.filter(cls.name.ilike(formatted_query) | cls.notes.ilike(formatted_query) |
cls.summary.ilike(formatted_query))
elif attr == 'open_anomalies':
from pid.anomaly.models import Anomaly
query = query.filter(cls.anomalies.any(Anomaly.state.in_(Anomaly.workflow.open_states)))
elif attr == 'open_ecos':
from pid.eco.models import ECO
query = query.filter(cls.ecos.any(ECO.state.in_(ECO.workflow.open_states)))
elif attr == 'created_on_start':
query = query.filter(cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(cls.state.in_(cls.workflow.open_states))
elif attr =='exclude_obsolete':
query = query.filter(cls.state != cls.workflow.obsolete_state)
elif attr == 'material_id':
from pid.part.models import Part
query = query.filter(cls.parts.any(Part.material_id == params['material_id']))
if 'open_anomalies' not in params and 'open_ecos' not in params:
query = query.distinct(cls.design_number)
return query.order_by(cls.design_number.desc(), cls.revision.desc()).all()
def find_all_revisions(self):
results = Design.query.filter_by(design_number=self.design_number).all()
# Sort them first alphabetically, then by length
results.sort(key=lambda x: (len(x.revision), x.revision))
return results
def find_latest_revision(self):
results = Design.query.with_entities(Design.revision).filter_by(design_number=self.design_number).all()
# Sort them first alphabetically, then by length, in reverse. First element will have the highest revision.
resultset = [row[0] for row in results]
resultset.sort(key=lambda x: (len(x), x), reverse=True)
return resultset[0]
def find_next_revision(self):
# Doing separate lists that we then concat, due to sorting issues
all_possible_single_revisions = []
all_possible_double_revisions = []
# See: https://stackoverflow.com/questions/23686398/iterate-a-to-zzz-in-python
for chars in AUC:
all_possible_single_revisions.append(''.join(chars))
for chars in product(AUC, repeat=2):
all_possible_double_revisions.append(''.join(chars))
results = Design.query.with_entities(Design.revision).filter_by(design_number=self.design_number).order_by(Design.revision).all()
used_revisions = [str(row[0]) for row in results]
free_single_revisions = list(set(all_possible_single_revisions) - set(used_revisions) - set(FORBIDDEN_REVISIONS))
free_double_revisions = list(set(all_possible_double_revisions) - set(used_revisions))
return (sorted(free_single_revisions) + sorted(free_double_revisions))[0]
def find_next_part_number(self):
# https://stackoverflow.com/a/16974075
part_numbers = [int(part.part_identifier) for part in self.parts]
difference = sorted(set(range(part_numbers[0], part_numbers[-1] + 1)).difference(part_numbers))
next_part_number = difference[0] if difference else part_numbers[-1] + 1
return next_part_number
def find_next_inseparable_part_number(self):
part_numbers = [int(part.part_identifier) for part in self.parts]
start = part_numbers[0] if part_numbers[0] > 100 else 101
end = part_numbers[-1] if part_numbers[-1] > 100 else 102
difference = sorted(set(range(start, end)).difference(part_numbers))
next_part_number = difference[0] if difference else part_numbers[-1] + 1
return next_part_number
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
# Check if open anomalies
for anomaly in self.anomalies:
if anomaly.is_open():
approval_errors.append('{0} must be resolved.'.format(anomaly.get_unique_identifier()))
# Check if open ecos
for eco in self.ecos:
if eco.is_open():
approval_errors.append('{0} must be resolved.'.format(eco.get_unique_identifier()))
return approval_errors
def get_procedures(self):
return self.get_all_procedures()
def get_all_procedures(self):
from pid.procedure.models import Procedure
procedures = []
for part in self.parts:
part_procedures = defaultdict(str)
for procedure in part.procedures:
if part_procedures[procedure.procedure_number] < procedure.revision:
part_procedures[procedure.procedure_number] = procedure.revision
for key, value in part_procedures.items():
procedures.append(Procedure.get_by_procedure_number_and_revision(key, value))
procedures.sort(key=lambda x: x.created_at, reverse=True) # Sort by newest first
return procedures
def as_dict(self):
return {
'id': self.id,
'design_number': self.design_number,
'revision': self.revision,
'url': self.get_url()
}
def get_latest_revision_unique_identifier(self):
return '{0}-{1}'.format(self.design_number, self.find_latest_revision())
def get_latest_revision_url(self):
# BEWARE: This function will always point to latest revision of design
return url_for('design.view_design', design_number=self.design_number, revision=self.find_latest_revision())
def get_unique_identifier(self):
return '{0}-{1}'.format(self.design_number, self.revision)
def get_url(self, external=False):
return url_for('design.view_design', design_number=self.design_number,
revision=self.revision, _external=external)
def __str__(self):
return '{0}-{1}'.format(self.design_number, self.revision)
def __repr__(self):
"""Represent instance as a unique string."""
return '<Design({0} {1})>'.format(self.design_number, self.revision)
class AsRun(NamelessRecord):
__tablename__ = 'as_runs'
descriptor = 'As-Run'
name = Column(db.String, nullable=True)
as_run_number = Column(db.Integer, nullable=False)
approvers = relationship('Approver', secondary='as_runs_approvers', order_by='asc(Approver.id)', backref='as_run')
procedure_id = reference_col('procedures')
procedure = relationship('Procedure') # Inherit revision, name from procedure
notes = Column(db.String)
software_version = Column(db.String)
products = relationship('Product', secondary='as_runs_products')
vendor_products = relationship('VendorProduct', secondary='as_runs_vendor_products')
anomalies = relationship('Anomaly', secondary='as_runs_anomalies',
order_by='desc(Anomaly.created_at)', back_populates='as_runs')
documents = relationship('Document', secondary='as_runs_documents')
links = relationship('Link', secondary='as_runs_links')
images = relationship('Image', secondary='as_runs_images')
project_id = reference_col('projects')
project = relationship('Project')
procedure_number = association_proxy('procedure', 'procedure_number')
workflow = AsRunWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = AsRunPermissions()
__mapper_args__ = {
"order_by": [procedure_id, as_run_number]
}
def __init__(self, **kwargs):
"""Create instance with change log."""
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def identifier(self):
return '{0}.{1}'.format(self.procedure.procedure_number, str(self.as_run_number).zfill(3))
@classmethod
def get_by_procedure_id_as_run_number(cls, procedure_id, as_run_number):
return cls.query.filter_by(as_run_number=as_run_number, procedure_id=procedure_id).first()
@classmethod
def find_all_as_runs_for_user(cls, user):
from pid.procedure.models import Procedure
results = cls.query.filter_by(owner=user).join(cls.procedure).order_by(Procedure.procedure_number, cls.as_run_number).all()
return results
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
# Need to zero pad as_run_number in following query
sql = "SELECT ar.* FROM as_runs ar, procedures p WHERE ar.procedure_id = p.id AND (SELECT CONCAT(p.procedure_number, '-', lpad(cast(ar.as_run_number as text), 3, '0'), ' ', ar.name) ILIKE :query)"
results = db.session.query(cls).from_statement(db.text(sql).params(query=query)).all()
return results
def find_all_revisions(self):
results = AsRun.query.filter_by(as_run_number=self.as_run_number).all()
return Utils.find_all_revisions(results)
def find_latest_revision(self):
results = AsRun.query.with_entities(AsRun.revision).filter_by(as_run_number=self.as_run_number).all()
return Utils.find_latest_revision(results)
def find_next_revision(self):
results = AsRun.query.with_entities(AsRun.revision).filter_by(as_run_number=self.procedure_number).order_by(AsRun.revision).all()
return Utils.find_next_revision(results)
@classmethod
def find_next_as_run_number(cls, procedure):
as_runs = cls.query.filter_by(procedure_number=procedure.procedure_number).all()
highest_as_run_number = 0
for as_run in as_runs:
if int(as_run.as_run_number) > highest_as_run_number:
highest_as_run_number = int(as_run.as_run_number)
return highest_as_run_number + 1
@classmethod
def find_all_procedure_as_runs_numbers(cls, procedure_number):
as_runs = cls.query.filter_by(procedure_number=procedure_number).all()
as_run_numbers = []
for as_run in as_runs:
as_run_numbers.append(as_run.as_run_number)
return as_run_numbers
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
# Check if open anomalies
for anomaly in self.anomalies:
if anomaly.is_open():
approval_errors.append('{0} must be resolved.'.format(anomaly.get_unique_identifier()))
return approval_errors
def get_name(self):
return self.name if self.name else self.procedure.name
def get_unique_identifier(self):
return '{0}-{1}'.format(self.procedure.procedure_number, str(self.as_run_number).zfill(3))
def get_url(self, external=False):
return url_for('asrun.view_as_run', procedure_number=self.procedure.procedure_number,
as_run_number=self.as_run_number, _external=external)
def __repr__(self):
"""Represent instance as a unique string."""
return '<AsRun({id!r},{as_run_number!r})>'.format(id=self.id, as_run_number=self.as_run_number)
class VendorProduct(NamelessRecord):
__tablename__ = 'vendor_products'
descriptor = 'Vendor Product'
serial_number = Column(db.String, nullable=False)
vendor_part_id = reference_col('vendor_parts')
vendor_part = relationship('VendorPart')
summary = Column(db.String)
notes = Column(db.Text)
approvers = relationship('Approver', secondary='vendor_products_approvers',
order_by='asc(Approver.id)', backref='vendor_product')
allowed_types = ['SN', 'LOT', 'STOCK']
product_type = Column(db.String, default='SN')
measured_mass = Column(db.Float, default=0.0)
hardware_type_id = reference_col('hardware_types')
hardware_type = relationship('HardwareType')
project_id = reference_col('projects')
project = relationship('Project')
vendor_build_id = reference_col('vendor_builds')
vendor_build = relationship('VendorBuild', back_populates='vendor_products')
documents = relationship('Document', secondary='vendor_products_documents')
images = relationship('Image', secondary='vendor_products_images')
links = relationship('Link', secondary='vendor_products_links')
discrepancies = relationship('Discrepancy', secondary='vendor_products_discrepancies')
as_runs = relationship('AsRun', secondary='as_runs_vendor_products', order_by='desc(AsRun.created_at)')
workflow = ProductWorkflow()
state = Column(db.String, default=workflow.initial_state)
permissions = VendorProductPermissions()
__table_args__ = (db.UniqueConstraint('serial_number', 'vendor_part_id', name='serial_number_vendor_part_unique'),)
__mapper_args__ = {
"order_by": serial_number
}
def __init__(self, **kwargs):
super().__init__()
db.Model.__init__(self, **kwargs)
@property
def product_number(self):
return '{0} {1}'.format(self.vendor_part.part_number, self.serial_number)
@property
def discrepancy_number(self):
return '{0}-{1}'.format(self.vendor_part.part_number, self.serial_number)
@classmethod
def get_next_lot_number_for_vendor_part(cls, vendor_part):
results = cls.query.with_entities(cls.serial_number).filter_by(vendor_part=vendor_part, product_type='LOT').distinct().all()
if len(results) == 0:
lot_number = 1
else:
resultset = [row[0] for row in results]
resultset.sort(reverse=True)
lot_number = None
index = 0
while not lot_number and index < len(resultset):
if resultset[index].replace('L', '').isdigit():
lot_number = int(resultset[index].replace('L', '')) + 1
index = index + 1
if not lot_number:
lot_number = 1
return 'L{0:03d}'.format(lot_number)
@classmethod
def get_serial_numbers_for_vendor_part(cls, vendor_part):
results = cls.query.with_entities(cls.serial_number).filter_by(vendor_part=vendor_part).distinct().all()
resultset = [row[0] for row in results]
resultset.sort()
return resultset
@classmethod
def get_vendor_product_by_product_number(cls, part_number, serial_number):
sql = 'SELECT vprod.* FROM vendor_products vprod, vendor_parts vpart WHERE vprod.vendor_part_id = vpart.id AND vpart.part_number = :part_number AND vprod.serial_number = :serial_number'
results = db.session.query(cls).from_statement(db.text(sql).params(part_number=part_number, serial_number=serial_number)).first()
return results
@classmethod
def find_all_vendor_products_for_user(cls, user):
results = cls.query.filter_by(owner=user).join(cls.vendor_part).order_by(VendorPart.part_number, cls.serial_number).all()
return results
@classmethod
def typeahead_search(cls, query):
query = '%{0}%'.format(query) # Pad query for an ILIKE search
sql = "SELECT vprod.* FROM vendor_products vprod, vendor_parts vpart WHERE vprod.vendor_part_id = vpart.id AND (SELECT CONCAT(vpart.part_number, '-', vprod.serial_number, ' ', vpart.name) ILIKE :query)"
results = db.session.query(cls).from_statement(db.text(sql).params(query=query)).all()
return results
@classmethod
def advanced_search(cls, params):
from pid.product.models import Discrepancy
query = cls.query
columns = cls.__table__.columns.keys()
for attr in params:
if params[attr] != "" and attr in columns:
query = query.filter(getattr(cls, attr) == params[attr])
elif params[attr] != "":
if attr == 'vprod_part_number_query':
formatted_query = format_match_query(params['vprod_part_number_query_type'], params['vprod_part_number_query'])
query = query.filter(cls.vendor_part.has(VendorPart.part_number.ilike(formatted_query)))
elif attr == 'vprod_serial_number_query':
formatted_query = format_match_query(params['vprod_serial_number_query_type'], params['vprod_serial_number_query'])
query = query.filter(cls.serial_number.ilike(formatted_query))
elif attr == 'text_fields_query':
formatted_query = format_match_query('includes', params['text_fields_query'])
query = query.filter(cls.summary.ilike(formatted_query) | cls.notes.ilike(formatted_query))
elif attr == 'open_discrepancies':
query = query.filter(cls.discrepancies.any(Discrepancy.state.in_(['Open'])))
elif attr == 'created_on_start':
query = query.filter(cls.created_at >= params['created_on_start'])
elif attr == 'created_on_end':
query = query.filter(cls.created_at <= params['created_on_end'])
elif attr == 'in_open_state':
query = query.filter(cls.state.in_(cls.workflow.open_states))
elif attr =='exclude_obsolete':
query = query.filter(cls.state != cls.workflow.obsolete_state)
elif attr == 'vendor_id':
query = query.filter(cls.vendor_part.has(VendorPart.vendor_id == params[attr]))
elif attr == 'material_id':
query = query.filter(cls.vendor_part.has(VendorPart.material_id == params[attr]))
return query.all()
def get_installed_ins(self):
from pid.product.models import ProductComponent, ExtraProductComponent
results = ProductComponent.query.filter_by(vendor_product=self).all()
results.extend(ExtraProductComponent.query.filter_by(vendor_product=self).all())
return results
def get_approval_errors(self):
approval_errors = []
if self.state == self.workflow.get_approval_state():
# Already in approval state, no need to do further checks
return approval_errors
# Check if not self_approved and either no approvers added or all approvers have already approved somehow.
if not self.self_approved:
if not self.approvers:
approval_errors.append('You must add at least one approver.')
elif all([approver.approved_at for approver in self.approvers]):
approval_errors.append('You must add at least one approver.')
# Check if open discrepancies
for discrepancy in self.discrepancies:
if discrepancy.is_open():
approval_errors.append('Discrepancy {0} must be resolved.'.format(discrepancy.discrepancy_number))
return approval_errors
def get_name(self):
return self.vendor_part.name
def get_unique_identifier(self):
return self.product_number
def get_url(self, external=False):
return url_for('vendorproduct.view_vendor_product', part_number=self.vendor_part.part_number,
serial_number=self.serial_number, _external=external)
def __str__(self):
return self.product_number
def __repr__(self):
return '<VendorProduct({0})>'.format(self.product_number)
