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)))()
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'))))
return o(_relate(self.expr[0], other.expr[0], 'LT'))
return
def __gt__(self, other):
if isinstance(other, _ConditionalClause):
if self._is_atom() and other._is_atom():
return o(_relate(self.expr[0], other.expr[0], 'GT'))
return
def __le__(self, other):
if isinstance(other, _ConditionalClause):
if self._is_atom() and other._is_atom():
return o(_relate(self.expr[0], other.expr[0], 'LE'))
return
def __ge__(self, other):
if isinstance(other, _ConditionalClause):
if self._is_atom() and other._is_atom():
return o(_relate(self.expr[0], other.expr[0], 'GE'))
return
# MISCELLANEOUS
def __str__(self):
return str(self.expr)
if __name__ == '__main__':
z = o('a', 'b', 'c') * o(1, 2, 3) | o(hello=literal("goodbye"))
print(z.x(AS_PYE))
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