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
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
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_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
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
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_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 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)
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)
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
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 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
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
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
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 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
def get_all_with_pri_on_top(cls): sql = "SELECT * FROM {0} ORDER BY CASE WHEN NAME = 'PRDC' THEN 1 WHEN NAME = 'PR-Lux' THEN 2 ELSE 3 END, name".format( cls.__tablename__) results = db.session.query(cls).from_statement(db.text(sql)).all() return results
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
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 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