def run_report(reporttype, sheetname: str, org_name: str, xlsx_file: str): # Make a sqlalchemy session - Name of database is read from settings session = sessionmaker(bind=get_engine(), autoflush=False)() # Make the query data = reporttype(session, org_name) # write data as excel file workbook = xlsxwriter.Workbook(xlsx_file) excel = XLSXExporter(xlsx_file) excel.add_sheet(workbook, sheetname, data) workbook.close()
def test_get_engine(self): testnumber = random.randint(1, 1000) with tempfile.TemporaryDirectory() as d: pd = pathlib.Path(d) dbpath = pd / "testdb" myengine = sqlalchemy.create_engine( "sqlite:///{}.db".format(dbpath)) myengine.execute("create table x(x integer);") myengine.execute("insert into x values(%d)" % testnumber) myengine.dispose() yourengine = get_engine(dbpath) result = yourengine.execute("select x from x") self.assertEqual(result.first(), (testnumber, ))
def setUp(self): """ setup db and populate with quite minimal data """ self.engine = get_engine(dbpath=":memory:") self.session = sessionmaker(bind=self.engine, autoflush=False)() # Lav tables via tabledefs fra LoraCache og fyld dataen ind Base.metadata.create_all(self.engine) bruger = Bruger( fornavn="fornavn", efternavn="efternavn", uuid="b1", bvn="b1bvn", cpr="cpr1", ) self.session.add(bruger) it = ItSystem(navn=AD_it_system, uuid="ItSystem1") self.session.add(it) it = ItSystem(navn="it_navn2", uuid="ItSystem2") self.session.add(it) it = ItForbindelse( id=1, uuid="if1", it_system_uuid="ItSystem1", bruger_uuid="b1", enhed_uuid="e1", brugernavn="AD-logon", startdato="0", slutdato="1", primær_boolean=True, ) self.session.add(it) it = ItForbindelse( id=2, uuid="if2", it_system_uuid="ItSystem2", bruger_uuid="b1", enhed_uuid="e1", brugernavn="if_bvn2", startdato="0", slutdato="1", primær_boolean=True, ) self.session.add(it) self.session.commit()
def main(delete): engine = get_engine() # Prepare session Session = sessionmaker(bind=engine, autoflush=False) session = Session() # List of tuples, it_sys_uuid, bruger_uuid, enhed_uuid, brugernavn, count duplicates = find_duplicate_it_connections(session) # List of dicts from id --> uuid (for rows to be deleted) duplicate_maps = map(partial(construct_duplicate_dict, session), duplicates) # One combined dict from id --> uuid (for rows to be deleted) output = dict(ChainMap(*duplicate_maps)) if delete: settings = get_settings() delete_from_lora(settings["mox.base"], output.values()) else: # Output delete-map print(json.dumps(output, indent=4, sort_keys=True))
def main(emus_xml_file, settings): session = get_session(get_engine()) nodes = read_ou_tree(session, settings["MORA_ROOT_ORG_UNIT_UUID"]) # Write employees to temp file, counting to determine if ou included temp_file = io.StringIO() export_e_emus(session, settings, nodes, temp_file) # Begin the xml file emus_xml_file.write('<?xml version="1.0" encoding="utf-8"?>\n') emus_xml_file.write("<OS2MO>\n") # write included units to xml-file export_ou_emus(session, nodes, temp_file) # Write employees to xml file emus_xml_file.write(temp_file.getvalue()) # End xml file emus_xml_file.write("</OS2MO>")
if response.status_code == 400: assert response.text.find("raise to a new registration") > 0 else: response.raise_for_status() def run_it(self): for i in (session.query(ItForbindelse.uuid).filter( and_(ItForbindelse.bruger_uuid != None)).all()): try: print(i) self.terminate("it", i[0]) except: pass def run_adresse(self): for i in (session.query(Adresse.uuid).filter( and_(Adresse.adressetype_scope == "E-mail", Adresse.bruger_uuid != None)).all()): try: print(i) self.terminate("address", i[0]) except: pass if __name__ == "__main__": session = get_session(get_engine()) SnurreBasse(session).run_adresse() SnurreBasse(session).run_it()
def generate_json(): # TODO: Async database access engine = get_engine() # Prepare session Session = sessionmaker(bind=engine, autoflush=False) session = Session() # Count number of queries def query_counter(*_): query_counter.count += 1 query_counter.count = 0 event.listen(engine, "before_cursor_execute", query_counter) # Print number of employees total_number_of_employees = session.query(Bruger).count() print("Total employees:", total_number_of_employees) def filter_missing_entry(entry_map, entry_type, unit_uuid, entry): if unit_uuid not in entry_map: logger.error(entry_type + " not found in map: " + str(unit_uuid)) return False return True def enrich_org_unit_with_x(org_unit_map, entry_type, entry_gen, entries): def gen_entry(x, bruger): return x.enhed_uuid, entry_gen(x, bruger) # Bind two arguments so the function only takes unit_uuid, entry. # Then apply_tuple to the function takes a tuple(unit_uuid, entry). missing_entry_filter = apply_tuple( partial(filter_missing_entry, org_unit_map, entry_type.capitalize())) entries = starmap(gen_entry, entries) entries = filter(missing_entry_filter, entries) for unit_uuid, entry in entries: org_unit_map[unit_uuid][entry_type].append(entry) return org_unit_map def enrich_employees_with_x(employee_map, entry_type, entry_gen, entries): def gen_entry(x, enhed): return x.bruger_uuid, entry_gen(x, enhed) # Bind two arguments so the function only takes unit_uuid, entry. # Then apply_tuple to the function takes a tuple(unit_uuid, entry). missing_entry_filter = apply_tuple( partial(filter_missing_entry, employee_map, entry_type.capitalize())) # Add org-units to queue as side-effect entries = side_effect(lambda x_enhed: add_org_unit(x_enhed[1]), entries) entries = starmap(gen_entry, entries) entries = filter(missing_entry_filter, entries) for bruger_uuid, entry in entries: employee_map[bruger_uuid][entry_type].append(entry) return employee_map def enrich_org_units_with_engagements(org_unit_map): def gen_engagement(engagement, bruger): return { "title": engagement.stillingsbetegnelse_titel, "name": bruger.fornavn + " " + bruger.efternavn, "uuid": bruger.uuid, } engagements = session.query( Engagement, Bruger).filter(Engagement.bruger_uuid == Bruger.uuid).all() return enrich_org_unit_with_x(org_unit_map, "engagements", gen_engagement, engagements) def enrich_org_units_with_associations(org_unit_map): def gen_association(tilknytning, bruger): return { "title": tilknytning.tilknytningstype_titel, "name": bruger.fornavn + " " + bruger.efternavn, "uuid": bruger.uuid, } associations = session.query( Tilknytning, Bruger).filter(Tilknytning.bruger_uuid == Bruger.uuid).all() return enrich_org_unit_with_x(org_unit_map, "associations", gen_association, associations) def enrich_org_units_with_management(org_unit_map): def gen_management(leder, bruger): return { "title": leder.ledertype_titel, "name": bruger.fornavn + " " + bruger.efternavn, "uuid": bruger.uuid, } managements = session.query( Leder, Bruger).filter(Leder.bruger_uuid == Bruger.uuid).all() return enrich_org_unit_with_x(org_unit_map, "management", gen_management, managements) def enrich_org_units_with_kles(org_unit_map): def gen_kle(kle): return kle.enhed_uuid, { "title": kle.kle_nummer_titel, # "name": kle.kle_aspekt_titel, "uuid": kle.uuid, } # Bind two arguments so the function only takes unit_uuid, entry. # Then apply_tuple to the function takes a tuple(unit_uuid, entry). missing_entry_filter = apply_tuple( partial(filter_missing_entry, org_unit_map, "KLE")) kles = session.query(KLE).all() kles = filter(lambda kle: kle.kle_aspekt_titel == 'Udførende', kles) kles = map(gen_kle, kles) kles = filter(missing_entry_filter, kles) for unit_uuid, kle in kles: org_unit_map[unit_uuid]["kles"].append(kle) return org_unit_map org_unit_map = {} org_unit_queue = set() def queue_org_unit(uuid=None): if uuid is None: return org_unit_queue.add(uuid) def fetch_parent_org_units(): # We trust that heirarchies are somewhat shallow, and thus a query per layer is okay. while org_unit_queue: query_queue = list(org_unit_queue) org_unit_queue.clear() queryset = session.query(Enhed).filter( Enhed.uuid.in_(query_queue)).all() for enhed in queryset: add_org_unit(enhed) def add_org_unit(enhed): # Assuming it has already been added, do not read if enhed.uuid in org_unit_map: return unit = { "uuid": enhed.uuid, "name": enhed.navn, "parent": enhed.forældreenhed_uuid, "engagements": [], "associations": [], "management": [], "kles": [], "addresses": { "DAR": [], "PHONE": [], "EMAIL": [], "EAN": [], "PNUMBER": [], "WWW": [], }, } org_unit_map[enhed.uuid] = unit # Add parent to queue for bulk fetching later (if any) queue_org_unit(enhed.forældreenhed_uuid) def fetch_employees(): def employee_to_dict(employee): return { "uuid": employee.uuid, "surname": employee.efternavn, "givenname": employee.fornavn, "name": employee.fornavn + " " + employee.efternavn, "engagements": [], "associations": [], "management": [], "addresses": { "DAR": [], "PHONE": [], "EMAIL": [], "EAN": [], "PNUMBER": [], "WWW": [] } } def create_uuid_tuple(entry): return entry["uuid"], entry employees = map(employee_to_dict, session.query(Bruger).all()) employee_map = dict(map(create_uuid_tuple, employees)) return employee_map def enrich_employees_with_engagements(employee_map): def gen_engagement(engagement, enhed): return { "title": engagement.stillingsbetegnelse_titel, "name": enhed.navn, "uuid": enhed.uuid, } engagements = session.query( Engagement, Enhed).filter(Engagement.enhed_uuid == Enhed.uuid).all() return enrich_employees_with_x(employee_map, "engagements", gen_engagement, engagements) def enrich_employees_with_associations(employee_map): def gen_association(tilknytning, enhed): return { "title": tilknytning.tilknytningstype_titel, "name": enhed.navn, "uuid": enhed.uuid, } associations = session.query( Tilknytning, Enhed).filter(Tilknytning.enhed_uuid == Enhed.uuid).all() return enrich_employees_with_x(employee_map, "associations", gen_association, associations) def enrich_employees_with_management(employee_map): def gen_management(leder, enhed): return { "title": leder.ledertype_titel, "name": enhed.navn, "uuid": enhed.uuid, } managements = session.query( Leder, Enhed).filter(Leder.enhed_uuid == Enhed.uuid).filter( # Filter vacant leders Leder.bruger_uuid != None).all() return enrich_employees_with_x(employee_map, "management", gen_management, managements) def filter_employees(employee_map): def filter_function(phonebook_entry): # Do NOT import employees without an engagement or association # https://redmine.magenta-aps.dk/issues/34812 # We do however want to import employees with management roles. # As an external employee may be a manager for an organisation unit. if (not phonebook_entry["associations"] and not phonebook_entry["engagements"] and not phonebook_entry["management"]): logger.info( "OS2MO_IMPORT_ROUTINE Skip employee due to missing engagements, associations, management" ) # Reference to the skipped employee to debug log logger.debug( "OS2MO_IMPORT_ROUTINE - NO_RELATIONS_TO_ORG_UNIT employee={phonebook_entry['uuid']}" ) return False return True filtered_map = { uuid: entry for uuid, entry in employee_map.items() if filter_function(entry) } return filtered_map def enrich_org_units_with_addresses(org_unit_map): # Enrich with adresses queryset = session.query(Adresse).filter(Adresse.enhed_uuid != None) return address_helper(queryset, org_unit_map, lambda address: address.enhed_uuid) def enrich_employees_with_addresses(employee_map): # Enrich with adresses queryset = session.query(Adresse).filter(Adresse.bruger_uuid != None) return address_helper(queryset, employee_map, lambda address: address.bruger_uuid) def address_helper(queryset, entry_map, address_to_uuid): da_address_types = { "DAR": "DAR", "Telefon": "PHONE", "E-mail": "EMAIL", "EAN": "EAN", "P-nummer": "PNUMBER", "Url": "WWW", } dawa_queue = {} def process_address(address): entry_uuid = address_to_uuid(address) if entry_uuid not in entry_map: return atype = da_address_types[address.adressetype_scope] if address.værdi: value = address.værdi elif address.dar_uuid is not None: dawa_queue[address.dar_uuid] = dawa_queue.get( address.dar_uuid, []) dawa_queue[address.dar_uuid].append(address) return else: logger.warning("Address: {address.uuid} does not have a value") return formatted_address = { "description": address.adressetype_titel, "value": value, } entry_map[entry_uuid]["addresses"][atype].append(formatted_address) queryset = queryset.filter( # Only include address types we care about Adresse.adressetype_scope.in_(da_address_types.keys())).filter( # Do not include secret addresses or_(Adresse.synlighed_titel == None, Adresse.synlighed_titel != "Hemmelig")) for address in queryset.all(): process_address(address) uuids = set(dawa_queue.keys()) queryset = session.query(DARAdresse).filter(DARAdresse.uuid.in_(uuids)) betegnelser = map(attrgetter('betegnelse'), queryset.all()) betegnelser = filter(lambda x: x is not None, betegnelser) for value in betegnelser: for address in dawa_queue[dar_uuid]: entry_uuid = address_to_uuid(address) atype = da_address_types[address.adressetype_scope] formatted_address = { "description": address.adressetype_titel, "value": value, } entry_map[entry_uuid]["addresses"][atype].append( formatted_address) found = set(map(attrgetter('uuid'), queryset.all())) missing = uuids - found if missing: print(missing, "not found in DAWA") return entry_map # Employees # ---------- employee_map = None with elapsedtime("fetch_employees"): employee_map = fetch_employees() # NOTE: These 3 queries can run in parallel with elapsedtime("enrich_employees_with_engagements"): employee_map = enrich_employees_with_engagements(employee_map) with elapsedtime("enrich_employees_with_associations"): employee_map = enrich_employees_with_associations(employee_map) with elapsedtime("enrich_employees_with_management"): employee_map = enrich_employees_with_management(employee_map) # Filter off employees without engagements, assoications and management with elapsedtime("filter_employees"): employee_map = filter_employees(employee_map) with elapsedtime("enrich_employees_with_addresses"): employee_map = enrich_employees_with_addresses(employee_map) # Org Units # ---------- with elapsedtime("fetch_parent_org_units"): fetch_parent_org_units() # NOTE: These 3 queries can run in parallel with elapsedtime("enrich_org_units_with_engagements"): org_unit_map = enrich_org_units_with_engagements(org_unit_map) with elapsedtime("enrich_org_units_with_associations"): org_unit_map = enrich_org_units_with_associations(org_unit_map) with elapsedtime("enrich_org_units_with_management"): org_unit_map = enrich_org_units_with_management(org_unit_map) with elapsedtime("enrich_org_units_with_kles"): org_unit_map = enrich_org_units_with_kles(org_unit_map) with elapsedtime("enrich_org_units_with_addresses"): org_unit_map = enrich_org_units_with_addresses(org_unit_map) print("Processing took", query_counter.count, "queries") # Write files # ------------ # TODO: Asyncio to write both files at once? with open("tmp/employees.json", "w") as employees_out: json.dump(employee_map, employees_out) with open("tmp/org_units.json", "w") as org_units_out: json.dump(org_unit_map, org_units_out)