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
