def query_get_hsid(query_id):
"""enter a substance id and get matching hs id"""
session = connections.get_session()
for instance in session.query(models.ChemScanHsSubstance).filter(
models.ChemScanHsSubstance.substance_id == query_id):
return instance
session.close()
def query_get_scopes(testorg_id):
"""list of scopes for given organization"""
session = connections.get_session()
query = session.query(models.ChemScanScope).filter(
models.ChemScanScope.organization_id == testorg_id).all()
session.close()
return query
def query_get_substance(testchem):
"""enter substance name and get substance_id from chem_scan_substance"""
session = connections.get_session()
for instance in session.query(models.ChemScanSubstance).filter(
models.ChemScanSubstance.name == testchem):
return instance.name
session.close()
def testquery(testchem):
session = connections.get_session()
for instance in session.query(models.ChemScanSubstance).filter(
models.ChemScanSubstance.name == testchem):
return instance
session.close()
def query_get_hsnumber(query_id):
"""get hsnumber"""
session = connections.get_session()
#query = session.query(models.ChemScanH.hs_number)
for instance in session.query(
models.ChemScanH).filter(models.ChemScanH.id == query_id):
return instance.hs_number
def query_get_plant2(testorg_id):
"""enter an organization and get all plants"""
session = connections.get_session()
query = session.query(models.ChemScanPlant).join(
models.OroOrganization).filter(
models.OroOrganization.id == models.ChemScanPlant.organization_id
).filter(models.OroOrganization.id == testorg_id).all()
session.close()
return query
def create_usage(hs_id, org_id, plant_ids, active, scope_id, proc_id,
purpose_id, material_id, procedure_id, qty, excrete,
frequency, surface, duration, air_supply, flammable,
closed_system, dusting):
"""all inserts necessary to make a new usage:
- connection usage to organization: entry to chem_scan_hs_organization
- new usage: entry to chem_scan_hs_usage
- connection between usage and plant: entry to chem_scan_hs_usage_plant
- necessary entry to chem_scan_hs_substitution
- connection between usage and hazard substance: entry to chem_scan_hs_substitute
"""
#active = PARAMETER[active]
excrete = PARAMETER[excrete]
qty = PARAMETER[qty]
frequency = PARAMETER[frequency]
surface = PARAMETER[surface]
duration = PARAMETER[duration]
air_supply = PARAMETER[air_supply]
flammable = PARAMETER[flammable]
closed_system = PARAMETER[closed_system]
dusting = PARAMETER[dusting]
session = connections.get_session()
new_hs_org = insert_new_hs_org(hs_id, org_id, active)
session.add(new_hs_org)
session.commit()
new_id = new_hs_org.id # usage needs id from chem_scan_hs_org
new_usage = insert_new_usage(new_id, scope_id, proc_id, purpose_id,
material_id, procedure_id, qty, excrete,
frequency, surface, duration, air_supply,
flammable, closed_system, dusting)
session.add(new_usage)
session.commit()
new_hs_usage_id = new_usage.id
for plant_id in plant_ids:
new_hs_plant = hs_plant(new_hs_usage_id, plant_id, qty)
session.add(new_hs_plant)
session.commit()
new_hs_substitution = hs_substitution(hs_id, new_hs_usage_id, org_id,
purpose_id, material_id,
procedure_id, proc_id, qty, excrete,
frequency, surface, duration,
air_supply)
session.add(new_hs_substitution)
session.commit()
new_hs_sub = new_substitute(new_hs_substitution, org_id, hs_id,
new_hs_usage_id)
session.add(new_hs_sub)
session.commit()
session.close()
def query_get_hs_id(query_id):
""" enter substance name and get hs_id from chem_scan_hs"""
session = connections.get_session()
for instance in session.query(models.ChemScanH).join(
models.ChemScanHsSubstance).filter(
models.ChemScanH.id == models.ChemScanHsSubstance.hs_id).join(
models.ChemScanSubstance).filter(
models.ChemScanHsSubstance.substance_id ==
models.ChemScanSubstance.id).filter(
models.ChemScanSubstance.id == query_id):
return instance.id
session.close()
def query_get_all_hs():
"""joins chem_scan_hs and chem_scan_substance"""
session = connections.get_session()
query = session.query(models.ChemScanH, models.ChemScanSubstance).join(
models.ChemScanHsSubstance).filter(
models.ChemScanH.id == models.ChemScanHsSubstance.hs_id).join(
models.ChemScanSubstance).filter(
models.ChemScanHsSubstance.substance_id ==
models.ChemScanSubstance.id).all()
session.close()
return query
def query_get_plant1(testorg_id, testunit_id):
"""enter organization AND unit and get all plants ->
test data plant = Lackierroboter AL-Pig I"""
session = connections.get_session()
query = session.query(models.ChemScanPlant).join(
models.OroOrganization).filter(
models.OroOrganization.id == models.ChemScanPlant.organization_id
).join(models.ChemScanOrganizationUnit).filter(
models.ChemScanOrganizationUnit.id ==
models.ChemScanPlant.organization_unit_id).filter(
models.OroOrganization.id == testorg_id,
models.ChemScanOrganizationUnit.id == testunit_id).all()
session.close()
return query
def query_get_sds(query_id):
"""enter a substance name and get the filename for the sds"""
session = connections.get_session()
# first get hs_id for given substance_id (query_id)
hs_id = query_get_hs_id(query_id)
for instance in session.query(models.OroAttachmentFile).join(
models.ChemScanSafetyDatasheet).filter(
models.OroAttachmentFile.id == models.ChemScanSafetyDatasheet.
document_id).join(models.ChemScanH).filter(
models.ChemScanSafetyDatasheet.hs_id ==
models.ChemScanH.id).filter(models.ChemScanH.id == hs_id):
# add a path to find document with filename
instance.filename = '/documents/datasheets/' + instance.filename
return instance
session.close()
def query_get_all_substances():
"""get a list of all chemicals in chem_scan_substance"""
session = connections.get_session()
query = session.query(models.ChemScanSubstance).all()
session.close()
return query
def query_get_materials():
"""list of all materials"""
session = connections.get_session()
query = session.query(models.ChemScanMaterial).all()
session.close()
return query
def query_get_procedures():
"""list of all procedures"""
session = connections.get_session()
query = session.query(models.ChemScanProcedure).all()
session.close()
return query
