def internet_solution_adsl():
print(
default_report(
'IS Per Account Reconciliation',
o(type=(literal('peracc'), literal('uncapped')),
supplier=literal('is0001'))))
print()
print(
default_report('IS Per GB Reconciliation',
o(type=literal('pergb'), supplier=literal('is0001'))))
def fetch_statement(client, reseller):
reseller = CONN.stmt()\
('SELECT')('code', 'first_name', 'last_name', 'company', 'vat', 'physical_address', 'postal_address')\
('FROM')('entity')\
('WHERE')(o(code=literal(reseller)))()
client = CONN.stmt()\
('SELECT')('code', 'first_name', 'last_name', 'company', 'vat', 'physical_address', 'postal_address')\
('FROM')('entity')\
('WHERE')(o(code=literal(client)))()
account = CONN.stmt()\
('SELECT')('owner', 'id', 'name')\
('FROM')('account')\
('WHERE')(o(owner=literal(reseller[0]['code'])))()
print(*account)
def delete_invoice(invoice_nr):
tax_invoice = CONN.stmt()\
('SELECT')('*')\
('FROM')('tax_invoice')\
('WHERE')(o(code=literal(invoice_nr)))()
journal_nr = tax_invoice[0]['journal']
CONN.stmt()\
('DELETE')()\
('FROM')('account_line_item')\
('WHERE')(o(journal=literal(journal_nr)))()
CONN.stmt()\
('DELETE')()\
('FROM')('tax_invoice')\
('WHERE')(o(journal=literal(journal_nr)))()
CONN.stmt()\
('DELETE')()\
('FROM')('journal')\
('WHERE')(o(id=literal(journal_nr)))()
stmt = '{} VALUES ({});'.format(stmt, ', '.join(['%s'] * len(values)))
self._cursor.executemany(stmt, values)
return self._cursor.getlastrowid()
CONN = Connection(username=U_NAME,
password=P_WORD,
hostname=H_NAME,
db_name=DB_NAME)
if __name__ == '__main__':
thing = CONN.stmt()\
('SELECT')('name', 'service')\
('FROM')('clients')\
('MEMES')('DAT', 'BOI')\
('WHERE')(o(name='Benoit', surname='Balls'))(';')()
print('\n$', thing)
print('\npath:')
for e in CONN.stmt().path:
print('-', e)
print('\nloci:')
for e in CONN.stmt().loci:
print('-', e)
print('\nlogs:')
for e in CONN.stmt().logs:
print('-', e)
print('\ndump:')
def axxess():
print(
default_report('Axxess Reconciliation', o(supplier=literal('axx001'))))
def internet_solutions_mobile():
print(
default_report('IS Mobile Reconciliation',
o(type=literal('mobile'), supplier=literal('is0001'))))
def internet_solutions_domain():
print(
default_report('IS Domain Reconciliation',
o(type=literal('domain'), supplier=literal('is0001'))))
def create_invoice(client, me):
curr_invoice_nr, date = document_id(), datetime.datetime.now()
# """
result = CONN.stmt()\
('SELECT')('service.code', 'service.description',
'cost_price', 'sales_price',
'subscription.qty', 'first_name',
'last_name', 'company',
'entity.code', 'subscription.service',
'service.supplier', 'service.type')\
('FROM')('entity', 'subscription',
'service', 'service_type')\
('WHERE')(o('entity.code', 'service.type', 'subscription.service', 'entity.code') *
o('subscription.client', 'service_type.type', 'service.code', '{}'.format(literal(client))))()
# """
# """
accounts = CONN.stmt()\
('SELECT')('id', 'name', 'owner')\
('FROM')('account')()
# """
"""
ledger_a = accounts.filter(o(owner=literal(client),
name=literal('Supplier Control')))[0]['id']
ledger_b = accounts.filter(o(owner=literal(me),
name=literal('Customer Control')))[0]['id']
ledger_c = accounts.filter(o(owner=literal(client),
name=literal('VAT Control')))[0]['id']
ledger_d = accounts.filter(o(owner=literal(me),
name=literal('VAT Control')))[0]['id']
ledgers = []
for row in result:
ledgers.append(ledger(row['sales_price'],
row['description'],
ledger_a))
ledgers.append(ledger(row['sales_price'] * -1,
row['description'],
ledger_b))
ledgers.append(ledger(row['sales_price'] * VAT_RATE,
'VAT on {}'.format(row['description']),
ledger_c))
ledgers.append(ledger(row['sales_price'] * -VAT_RATE,
'VAT on {}'.format(row['description']),
ledger_d))
# """
"""
if len(ledgers) > 0:
journal = journal_entry(str(date), 'Tax Invoice Nr.{}'.format(curr_invoice_nr), *ledgers)
if journal is not None:
CONN.stmt()\
('INSERT_INTO')('tax_invoice')\
('COLUMNS')('code', 'reseller', 'client', 'journal')\
('VALUES')(curr_invoice_nr, me, client, journal)()
CONN.update()
CONN.commit()
return print_invoice(curr_invoice_nr)
# """
return False
def fetch_invoice(invoice_nr):
tax_invoice = CONN.stmt()\
('SELECT')('*')\
('FROM')('tax_invoice')\
('WHERE')(o(code=literal(invoice_nr)))()
reseller = CONN.stmt()\
('SELECT')('code', 'first_name', 'last_name', 'company', 'vat', 'physical_address', 'postal_address')\
('FROM')('entity')\
('WHERE')(o(code=literal(tax_invoice[0]['reseller'])))()
client = CONN.stmt()\
('SELECT')('code', 'first_name', 'last_name', 'company', 'vat', 'physical_address', 'postal_address')\
('FROM')('entity')\
('WHERE')(o(code=literal(tax_invoice[0]['client'])))()
journal = CONN.stmt()\
('SELECT')('id', 'date', 'description')\
('FROM')('journal')\
('WHERE')(o(id=literal(tax_invoice[0]['journal'])))()
lines = CONN.stmt()\
('SELECT')('journal', 'description', 'qty', 'value')\
('FROM')('account_line_item')\
('WHERE')((o('value') > o(0)) & o(journal=literal(journal[0]['id'])))\
('ORDER_BY')('description', 'DESC', 'value', 'DESC')()
""" To Be implemented:
This is an improved query for lines, that groups each transaction and its VAT, eliminating the need for
pre-processing in Python.
It required support for join syntax and the AS keyword
SELECT
DISTINCT l.id,
l.journal,
l.account AS ACCOUNT,
l.value as EX_VAT,
r.value as VAT,
r.account as CONTRA,
l.description,
l.qty
FROM
ecn.account_line_item AS l
JOIN
ecn.account_line_item AS r
WHERE
l.value*0.14 = r.value
and l.value > 0
and l.journal = r.journal;
"""
lines = CONN.query("SELECT\
DISTINCT l.id,\
l.journal,\
l.account AS ACCOUNT,\
l.value as EX_VAT,\
r.value as VAT,\
r.account as CONTRA,\
l.description,\
l.qty\
FROM\
ecn.account_line_item AS l\
JOIN\
ecn.account_line_item AS r\
WHERE\
l.value*0.14 = r.value\
and l.value > 0\
and l.journal = r.journal\
and l.journal = {};".format(literal(tax_invoice[0]['journal'])))
return reseller, client, journal, lines