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)
