<reponame>jehboyes/planning_system

from planning_system.db.schema.views import _generate_p_string

period_list = _generate_p_string("p{p}", ", ")

def definition():
    calc_total = """
    SELECT claim_id, ISNULL(sum(base_cost+ni_cost+pension_cost),0) as amount
    FROM v_calc_claim GROUP BY claim_id
    """
    main_query = f"""
SELECT i.set_id, i.claim_id, i.account, i.description, i.rate, i.claim_type_id,
       {period_list}, 
       pct.description as claim_type, 
       ISNULL(calc.amount,0) as amount,
       a.account + ' ' + a.description as ACCOUNT_DESCRIPTION
FROM input_pay_claim i
LEFT JOIN ({calc_total}) calc ON calc.claim_id = i.claim_id
LEFT JOIN fs_account a ON a.account = i.account
LEFT JOIN input_pay_claim_type pct ON pct.claim_type_id = i.claim_type_id
"""
    return main_query

        
