#!/usr/bin/python
import sys
import re

if len(sys.argv) != 2:
    print("Provide an account number!")
    quit()

account_no = sys.argv[1]
db_no = int(account_no[re.search(r'[^0]', account_no).start()]) + 1

if not 1 < db_no < 6:
	print('Wrong database number, check account no!')
	quit()

query = """
select e.poid_id0, e.poid_type, b.poid_id0, b.bill_no, it.item_total, pin00s{0}.unix2datev(e.created_t), eb.amount, eb.resource_id, e.sys_descr, e.descr, eb.gl_id,
    pin00s{0}.unix2datev(e.earned_start_t), pin00s{0}.unix2datev(e.earned_end_t), e.program_name, ofg.poid_id0, ofg.poid_type, src.poid_id0, src.code
    from pin00s{0}.event_t              e
    join pin00s{0}.event_bal_impacts_t eb on e.poid_id0        = eb.obj_id0
    join pin00s{0}.account_t          acc on e.account_obj_id0 = acc.poid_id0
    join pin00s{0}.item_t              it on it.poid_id0       = e.item_obj_id0
    join pin00s{0}.bill_t               b on b.poid_id0        = it.bill_obj_id0
    left join pin00s{0}.tp_bill_t      tb on tb.bill_obj_id0   = b.poid_id0
    left join (
        select poid_id0, code from pin00s{0}.product_t
      union
        select poid_id0, code from pin00s{0}.discount_t
    ) src on src.poid_id0  = eb.product_obj_id0
    left join (
        select poid_id0, poid_type from pin00s{0}.purchased_product_t
      union
        select poid_id0, poid_type from pin00s{0}.purchased_discount_t
    ) ofg on ofg.poid_id0 = eb.offering_obj_id0
    where acc.account_no = '{1}'
    and e.poid_type not in ('/event/delayed/session/telco', '/event/billing/adjustment/event')
    order by e.created_t asc;
""".format(db_no, account_no)

print(query)