def test_connect(self): try: cont = SQLAlchemyController() matcher_conf = config("matcherdb") cont.connect(host=matcher_conf["host"], database=matcher_conf["database"], user=matcher_conf["user"], password=matcher_conf["password"], port=matcher_conf["port"]) except Exception as e: self.fail()
def __init__(self): """ Konstruktor pro Invoice Provider. Při vytvoření si provider načte přihlašovací údaje z konfiguračního souboru. Není vytvořeno spojení s databází. To je vytvořeno až při vytvoření generátoru. """ opendata_conf = config("opendatadb") self._host = opendata_conf["host"] self._database = opendata_conf["database"] self._user = opendata_conf["user"] self._password = opendata_conf["password"] self._port = opendata_conf.getint("port")
def getAllInvoices(config_file: str): invoices = [] conn = None try: params = config(config_file, "opendatadb") # print('Connecting to the database...') conn = psycopg2.connect(**params) cur = conn.cursor() cur.execute(""" select distinct en_auth.ico as auth_ico, en_auth.name as auth_name, en_part.ico as part_ico, en_part.name as part_name, re.amount_czk, re.currency, re.subject, null as supplier_invoice_identifier, null as document_label, null as document_number, re.variable_symbol, null as date_of_acceptence, re.date_of_payment, re.due_date, re.date_created, re.budget_category as budget_item_code, null as budget_item_name, null as contract_identifier, null as amount_per_item, null as amount_without_tax, null as amount_in_diff_currency from public.record re left join public.entity en_auth on en_auth.entity_id = re.authority left join public.entity en_part on en_part.entity_id = re.partner where re.record_type = 'invoice' """) results = cur.fetchall() for line in results: # print(line) invoices.append(Invoice(*line)) cur.close() except Exception as e: print(e) finally: if conn is not None: conn.close() # print('Database connection closed.') return invoices
def prepare(self): """ Příprava potřebných dat. Načtení limitů. :return: None """ self.conf = config('deciding_pipeline') self.minimal_score = self.conf.getint("minimal_score") self.invoice_count_limit = self.conf.getint("invoice_count_limit") self.amount_test_weight = self.conf.getfloat("amount_test") self.greater_amount_weight = self.conf.getfloat("greater_amount") self.purpose_weight = self.conf.getfloat("purpose") self.contract_num_weight = self.conf.getfloat("contract_num") self.clean_contract_num_weight = self.conf.getfloat( "clean_contract_num") pass
def __init__(self): conf = config("contract_provider") self.base_url = conf["base_url"]
#Vyresi Cross Origin Resource Sharing CORS(app) #Dokumentace - diky kodu nize se generuje API dokumentace na adrese / api = Api(app, version='1.0', title='Matcher API', description='Matcher API documentation') ns_st = api.namespace('statistics', description='Statistics of database data') ns_invoices = api.namespace('invoices', description='Invoices operations') ns_contracts = api.namespace('contracts', description='Contracts operations') ns_relations = api.namespace('relations', description='Relations operations') ns_warnings = api.namespace('warnings', description='Warnings operations') ns_ministry = api.namespace('ministry', description='Ministry operations') db_congig = config("matcherdb") #Databaze - pristup k databazi, namapovani tabulek app.config[ 'SQLALCHEMY_DATABASE_URI'] = f'postgresql://{db_congig["user"]}:{db_congig["password"]}@{db_congig["host"]}/{db_congig["database"]}' app.config['JSON_AS_ASCII'] = False db = SQLAlchemy(app) metadata = MetaData() metadata.reflect(db.engine, only=[ 'invoice', 'contract', 'test_result', 'possible_relation', 'statistics', 'ministry', 'contract_warning' ]) Base = automap_base(metadata=metadata) Base.prepare(name_for_scalar_relationship=name_for_scalar_relationship, name_for_collection_relationship=name_for_collection_relationship)
def get_invoices(config_file: str, page: int, page_size: int) -> list: row_start = (page_size * page) - page_size + 1 row_end = page * page_size invoices = [] conn = None try: params = config(filename=config_file, section="opendatadb") print('Connecting to the database...') conn = psycopg2.connect(**params) cur = conn.cursor() cur.execute( """ select auth_ico, auth_name, part_ico, part_name, amount_czk, currency, subject, supplier_invoice_identifier, document_label, document_number, variable_symbol, date_of_acceptence, date_of_payment, due_date, date_created, budget_item_code, budget_item_name, contract_identifier, amount_per_item, amount_without_tax, amount_in_diff_currency from ( select distinct re.record_id, en_auth.ico as auth_ico, en_auth.name as auth_name, en_part.ico as part_ico, en_part.name as part_name, re.amount_czk, re.currency, re.subject, null as supplier_invoice_identifier, null as document_label, null as document_number, re.variable_symbol, null as date_of_acceptence, re.date_of_payment, re.due_date, re.date_created, null as budget_item_code, re.budget_category as budget_item_name, null as contract_identifier, null as amount_per_item, null as amount_without_tax, null as amount_in_diff_currency, row_number() OVER (ORDER BY re.record_id) AS rn from public.record re left join public.entity en_auth on en_auth.entity_id = re.authority left join public.entity en_part on en_part.entity_id = re.partner where re.record_type = 'invoice' ) as res where res.rn >= %s and res.rn <= %s """, (row_start, row_end)) results = cur.fetchall() cur.close() for line in results: # print(line) invoices.append(Invoice(None, *line)) except Exception as e: print(e) finally: if conn is not None: conn.close() print('Database connection closed.') return invoices
relations = get_relations(invoice=invoice, contracts=contracts) passed_relations, _ = filter_pipeline.process_invoice_relations( relations) final_relations, other_relations = deciding_pipeline.process_invoice_relations( passed_relations) for relation in final_relations: connection.insert_possible_relation(relation) for relation in other_relations: connection.insert_possible_relation(relation) matcher_conn.commit() if __name__ == '__main__': matcher_conn = SQLAlchemyController() matcher_conf = config("matcherdb") logging.debug("Connecting to the database.") matcher_conn.connect(host=matcher_conf["host"], database=matcher_conf["database"], user=matcher_conf["user"], password=matcher_conf["password"], port=matcher_conf["port"], echo=False) clear_relations(matcher_conn) start_matching(matcher_conn) logging.debug("Finished matching.") logging.debug("Creating warnings.") create_warnings(matcher_conn) logging.debug("Finished creating warnings.") logging.debug("Creating statistics.") refresh_statistics(matcher_conn)
from Database.SQLAlchemyController import SQLAlchemyController from Providers.ContractProviderRegistr.CProviderRegistr import ContractProviderRegistr from Providers.InvoiceProviderOpenData.IProviderOpenData import InvoiceProviderOpenData from Configuration.Config import config import logging """ Data Downloader Data Downloader slouží ke stažení smluv a faktur z ministerstva """ if __name__ == '__main__': logging.basicConfig(level=logging.DEBUG) log = logging.getLogger(__name__) matcher_conf = config('matcherdb') matcher_conn = SQLAlchemyController() log.info("Connecting to the matcherdb") matcher_conn.connect(host=matcher_conf["host"], database=matcher_conf["database"], user=matcher_conf["user"], password=matcher_conf["password"], port=matcher_conf["port"]) log.info("Connected to the matcherdb") # log.info("Starting to download invoices from opendata database") # iprovier = InvoiceProviderOpenData() # for i, inv in enumerate(iprovier.get_generator()): # matcher_conn.insert_invoice(inv) # matcher_conn.commit() # log.info("Invoices downloaded.")
relation=rel) print(f"\tEmpty Relation inserted: {possible_relation_id}") def start_matching(connection: PSQLController): page = 1 page_size = 1000 while True: invoices = connection.get_invoices(page=page, page_size=page_size, custom_filter=None) connection.begin_transaction() if invoices is None: break else: page = page + 1 for invoice in tqdm(invoices, desc="Matching invoices"): match_invoice(connection=connection, invoice=invoice, tests=[ic_test_amount, ic_test_purpose]) connection.commit() conn = PSQLController() configuration = config("./Database/database.ini", "matcherdb") conn.connect(host=configuration["host"], database=configuration["database"], user=configuration["user"], password=configuration["password"]) start_matching(conn)