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 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 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 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 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 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)
def revision_log_id(cls): return reference_col('revision_logs')
def change_log_id(cls): return reference_col('change_logs')
def thumbnail_id(cls): return reference_col('images', nullable=True)
def created_by_id(cls): return reference_col('users')
def owner_id(cls): return reference_col('users')
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 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 ExtraProductComponent(SurrogatePK, Model): __tablename__ = 'extra_product_components' parent_id = reference_col('products') parent = relationship('Product', foreign_keys=[parent_id]) 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') vendor_product_id = reference_col('vendor_products', nullable=True) vendor_product = relationship('VendorProduct') product_id = reference_col('products', nullable=True) product = relationship('Product', foreign_keys=[product_id]) ordering = Column(db.Integer) __mapper_args__ = { "order_by": ['ordering', 'id'] } def __init__(self, **kwargs): """Create instance.""" db.Model.__init__(self, **kwargs) def get_product(self): return self.product if self.product else self.vendor_product def get_part(self): return self.part if self.part else self.vendor_part def get_all_unassigned_extra_product_components_like_this(self): results = None if self.part: results = ExtraProductComponent.query.filter_by(part=self.part, parent=self.parent, product=None) elif self.vendor_part: results = ExtraProductComponent.query.filter_by(vendor_part=self.vendor_part, parent=self.parent, vendor_product=None) return results def get_all_assigned_extra_product_components_like_this(self): results = None if self.part: results = ExtraProductComponent.query.filter(ExtraProductComponent.part == self.part, ExtraProductComponent.parent == self.parent, ExtraProductComponent.product != None) # noqa elif self.vendor_part: results = ExtraProductComponent.query.filter(ExtraProductComponent.vendor_part == self.vendor_part, ExtraProductComponent.parent == self.parent, ExtraProductComponent.vendor_product != None) # noqa return results def update_all_unassigned_extra_product_components_for_part(self, product): rowcount = ExtraProductComponent.query.filter_by(part=self.part, parent=self.parent, product=None).update({'product_id': product.id}) db.session.commit() return rowcount def update_all_unassigned_extra_product_components_for_vendor_part(self, vendor_product): rowcount = ExtraProductComponent.query.filter_by(vendor_part=self.vendor_part, parent=self.parent, vendor_product=None).update({'vendor_product_id': vendor_product.id}) db.session.commit() return rowcount def get_products_for_extra_product_component(self): # Get all products for this parts design, and then all components for this parts design (not product, as they can be in use across products) 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' products = db.session.query(Product).from_statement(db.text(sql).params(design_number=self.part.design.design_number, part_identifier=self.part.part_identifier)).all() sql = 'SELECT pc.* FROM product_components pc, parts p, designs d WHERE pc.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier' components = db.session.query(ProductComponent).from_statement(db.text(sql).params(design_number=self.part.design.design_number, part_identifier=self.part.part_identifier)).all() sql = 'SELECT epc.* FROM extra_product_components epc, parts p, designs d WHERE epc.part_id = p.id AND p.design_id = d.id AND d.design_number = :design_number AND p.part_identifier = :part_identifier' extra_components = db.session.query(ExtraProductComponent).from_statement(db.text(sql).params(design_number=self.part.design.design_number, part_identifier=self.part.part_identifier)).all() # Remove products that are already in use, but keep LOT and STCK for component in components: if component.product and component is not self and component.product.product_type == 'SN' and component.product in products: products.remove(component.product) for component in extra_components: if component.product and component is not self and component.product.product_type == 'SN' and component.product in products: products.remove(component.product) return products def get_vendor_products_for_extra_product_component(self): # Get all products for this part, and then all components for this part vendor_products = VendorProduct.query.filter_by(vendor_part=self.vendor_part).all() components = ProductComponent.query.filter_by(vendor_part=self.vendor_part).all() extra_components = ExtraProductComponent.query.filter_by(vendor_part=self.vendor_part).all() # Remove products that are already in use, but keep LOT and STCK for component in components: if component.vendor_product and component is not self and component.vendor_product.product_type == 'SN' and component.vendor_product in vendor_products: vendor_products.remove(component.vendor_product) for component in extra_components: if component.vendor_product and component is not self and component.vendor_product.product_type == 'SN' and component.vendor_product in vendor_products: vendor_products.remove(component.vendor_product) return vendor_products def can_user_edit(self, field_name): return self.parent.can_user_edit('components') def __str__(self): if (self.part): return '{0} {1}'.format(self.parent.product_number, self.part.part_number) else: return '{0} {1}'.format(self.parent.product_number, self.vendor_part.part_number) def __repr__(self): """Represent instance as a unique string.""" return '<ExtraProductComponent({0})>'.format(self.id)
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 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 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 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)
def workflow_log_id(cls): return reference_col('workflow_logs')
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)
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 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 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 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 Build(BaseRecord): __tablename__ = 'builds' build_identifier = Column(db.String, nullable=False, default='001') part_id = reference_col('parts') part = relationship('Part') notes = Column(db.Text) purchase_order = Column(db.String) products = relationship('Product', back_populates='build', order_by="Product.serial_number") documents = relationship('Document', secondary='builds_documents') discrepancies = relationship('Discrepancy', secondary='builds_discrepancies') vendor_id = reference_col('companies') vendor = relationship('Company') permissions = BuildPermissions() __table_args__ = (db.UniqueConstraint('build_identifier', 'part_id', name='build_identifier_part_unique'),) def __init__(self, **kwargs): super().__init__() db.Model.__init__(self, **kwargs) @property def build_number(self): return '{0}.{1}'.format(self.part.part_identifier, self.build_identifier) @property def discrepancy_number(self): return '{0}-B{1}'.format(self.part.part_number, self.build_identifier) @classmethod def get_next_build_identifier_for_design_number_and_part_identifier(cls, design_number, part_identifier): 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' results = db.session.query(cls).from_statement(db.text(sql).params(design_number=design_number, part_identifier=part_identifier)).all() if len(results) == 0: build_identifier = 1 else: # resultset = [row[0] for row in results] resultset = [b.build_identifier for b in results] resultset.sort() build_identifier = int(resultset[-1]) + 1 return '{0:03d}'.format(build_identifier) @classmethod def get_build_by_build_number(cls, design_number, part_identifier, build_identifier): 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 AND b.build_identifier = :build_identifier' results = db.session.query(cls).from_statement(db.text(sql).params(design_number=design_number, part_identifier=part_identifier, build_identifier=build_identifier)).first() return results def find_all_build_identifiers(self): results = Build.query.with_entities(Build.build_identifier).order_by(Build.build_identifier.asc()).filter_by(part=self.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 '<Build({0})>'.format(self.build_number)