from beancount import loader

from beancount.query.query import run_query

entries, errors, options = loader.load_file('2020.beancount')

year_start = '2020-01-01'

start_date = '2020-11-16'

end_date = '2020-12-01'

queries = {

    "00: Payroll Funds Report -> Direct Deposit":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' --sort d bal '/^(Expenses:Conservancy:Payroll:Salary:Vacation|Accrued:Vacation Payable|Expenses.*Salary$|Assets:Prepaid Vacation:Conservancy)/'

    f"""SELECT SUM(VALUE(position)) AS aa

        WHERE account ~ "^(Expenses:Payroll:Salary:Vacation|Liabilities:Payable:Vacation|Expenses.*Salary\$|Assets:Prepaid:Vacation)"

        AND ANY_META("payroll-type") ~ "CA:General"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "01: Payroll Funds Report -> CRA REMITTANCE -> Remuneration":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/ and tag("TaxImplication") =~ /T4/' --sort d bal '/^Assets/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^Assets" AND any_meta("tax-implication") ~ "T4"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "02: Payroll Funds Report -> CRA REMITTANCE -> Income Tax":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' --sort d -d 'T&l<=1' bal '/Expenses.*Payroll.*IncomeTax/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "Expenses:Payroll" AND ANY_META("payroll-type") ~ "^CA:Tax:Income"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "03: Payroll Funds Report -> CRA REMITTANCE -> CPP":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' --sort d -d 'T&l<=1' bal '/Expenses.*Payroll.*CPP/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "Expenses:Payroll" AND ANY_META("payroll-type") ~ "^CA:(Tax:)?PP"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "04: Payroll Funds Report -> CRA REMITTANCE -> EI":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' -d 'T&l<=1' --sort d bal '/Expenses.*Payroll.*EI/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "Expenses:Payroll" AND ANY_META("payroll-type") ~ "^CA(:Tax)?:EI"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "05: Payroll Funds Report -> CRA REMITTANCE -> TOTAL":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' --sort d  -d 'T&l<=1' bal '/Expenses.*Payroll:(Taxes|Salary:(EI|CPP|IncomeTax))/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE ( (account ~ "Expenses:Payroll:Tax" AND ANY_META("payroll-type") ~ "^CA:")

        OR (account ~ "Expenses:Payroll:Salary" AND  ANY_META("payroll-type") ~ "^CA:Tax") )

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "06: Payroll Funds Report -> Fees/Misc":

    # -b $startDate -e $endDate  --limit 'tag("Entity") =~ /(Deluxe-Payroll|Canada)/' bal '/Expenses.*(Payroll:Fees|Sales Tax:CA:ON)/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "Expenses.*(Payroll:Fees|Tax:Sales)"

        AND (any_meta("entity") ~ "(Gingerich|Deluxe|Canada|CA:ON)" or (any_meta("entity") = NULL and payee ~ "(Gingerich|Deluxe|Canada|CA:ON)"))

        AND date >= {start_date} AND date < {end_date}""",

    "07: Payroll Funds Report -> TOTAL FUNDS REQUIRED (sign may be wrong)":

    # -e $endDate --limit 'tag("Entity") =~ /(Deluxe|Gingerich)/' --sort d bal 'Assets:Prepaid Expenses:Conservancy'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "Assets:Prepaid:Expenses"

        AND (any_meta("entity") ~ "(Gingerich-Denver|Deluxe)" or (any_meta("entity") = NULL and payee ~ "(Gingerich-Denver|Deluxe)"))

        AND date < {end_date}""",

    "08: Statement of Earnings and Deductions -> Current Amount: Regular + Retro":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/' -d 'T&l<=1' bal '/^Expenses.*Salary/' and not /Vacation/

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^Expenses.*Salary" AND NOT ANY_META("payroll-type") ~ "CA:PTO"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}""",

    "09: Statement of Earnings and Deductions -> YTD Amount: Regular":

    # -b $yearStart -e $endDate --limit 'tag("Entity") =~ /Gingerich/' -d 'T&l<=1' bal '/^Expenses.*Salary/' and not /Vacation/

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^Expenses.*Salary" AND NOT account ~ ":Vacation" AND NOT ANY_META("payroll-type") ~ "CA:PTO"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {year_start} AND date < {end_date}""",

    "10: Statement of Earnings and Deductions -> YTD Amount: Vacation":

    # -b $yearStart -e $endDate --limit 'tag("Entity") =~ /Gingerich/ and a > 0' --sort d bal '/^(Accrued.*Vacation Payable|Assets:Prepaid Vacation)/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^(Liabilities:Payable:Vacation|Assets:Prepaid:Vacation)"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {year_start} AND date < {end_date}

        AND number(cost(position)) > 0""",

    "11: Statement of Earnings and Deductions -> YTD Total Earnings: Total":

    # -b $yearStart -e $endDate --limit 'tag("Entity") =~ /Gingerich/ and tag("TaxImplication") =~ /T4/' --sort d bal '/^Assets/' 

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^Assets" AND any_meta("tax-implication") ~ "T4"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {year_start} AND date < {end_date}""",

    "12: Statement of Earnings and Deductions: Vacation Accrual: Current Earned":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/ and a < 0' bal '/^(Accrued.*Vacation Payable|Assets:Prepaid Vacation:Conservancy)/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^(Liabilities:Payable:Vacation|Assets:Prepaid:Vacation)"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}

        AND number(cost(position)) < 0""",

    "13: Statement of Earnings and Deductions: Vacation Accrual: Current Taken":

    # -b $startDate -e $endDate --limit 'tag("Entity") =~ /Gingerich/ and a > 0' --sort d bal '/^(Assets:Prepaid Vacation|Accrued.*Vacation Payable)/' > $takenVacationFile 2>&1

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^(Liabilities:Payable:Vacation|Assets:Prepaid:Vacation)"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date >= {start_date} AND date < {end_date}

        AND number(cost(position)) > 0""",

    "14: Statement of Earnings and Deductions, Vaction Untaken":

    # -e $endDate --limit 'tag("Entity") =~ /Ginger/' bal '/(Vacation Payable|Prepaid Vacation)/'

    f"""SELECT SUM(VALUE(position)) AS aa WHERE account ~ "^(Liabilities:Payable:Vacation|Assets:Prepaid:Vacation)"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date < {end_date}""",

    "15: Verify currency loss is not *too* much, (typo check)":

    f"""SELECT SUM(COST(position)) AS aa WHERE narration ~ "ayroll" AND account ~ "Equity:Realized:CurrencyConversion"

        AND (any_meta("entity") ~ "(Gingerich-Denver|Deluxe)" or (any_meta("entity") = NULL and payee ~ "(Gingerich-Denver|Deluxe)"))

        AND date >= {start_date} AND date < {end_date}""",

    "16: Invoices Balance?":

    # --group-by 'tag("Invoice")' -e $endDate --limit 'payee =~ /ayroll/ and tag("Entity") =~ /(Gingerich|Deluxe)/' --sort d bal '/Accrued:Accounts/' > $unbalancedInvoiceOutputFile 2>&1

    f"""SELECT ANY_META("invoice") as invoice, SUM(cost(position)) AS total

        WHERE account ~ "^Liabilities:.*:Accounts" AND narration ~ "ayroll"

        AND (any_meta("entity") ~ "Gingerich-Denver" or (any_meta("entity") = NULL and payee ~ "Gingerich-Denver"))

        AND date < {end_date}

        GROUP BY invoice ORDER by invoice"""

}

for desc, query in queries.items():

    result = run_query(entries, options, query)[1]

    if result:

        print(desc + ': ' + str(result[0].aa.get_positions()[0]))

    else:

        print(desc + ': ' + "N/A")