"""Formulas from sheet 'P&L' of 'fpna_month_end.xlsx' (pandas target).

Generated by Power Migrate 0.1.0a1. Copied formulas are column, row or
table operations on the sheet's DataFrame; other blocks run cell by cell over
the same frame. ``model.compute`` runs them in dependency order.
"""

from .. import xl
from ..frames import FrameBook, abs_, across, and_, average, averageif, averageifs, ceiling, cells, choose, col, concat, concatenate, count, counta, countblank, countif, countifs, date, datedif, day, div, edate, eomonth, eq, exact, exp, find, floor, fv, ge, gt, hlookup, iferror, ifna, ifs, index, int_, ipmt, irr, isblank, iserr, iserror, islogical, isna, isnumber, istext, le, left, len_, ln, log, log10, lower, lt, match, max_, maxifs, mid, min_, minifs, mod, month, n, ne, networkdays, not_, nper, npv, num, or_, pi, pmt, power, ppmt, product, proper, pv, rate, rept, right, round_, rounddown, roundup, search, shifted, sign, sqrt, substitute, sum_, sumif, sumifs, sumproduct, switch, text, textjoin, trim, trunc, upper, value, vlookup, weekday, where, workday, xlookup, xor, year, yearfrac


def month_b2(book: FrameBook) -> None:
    r"""Month

    'P&L'!B2:M2 (12 columns, copied across).
    Excel (B2): =Revenue!B2
    Pattern (R1C1): =Revenue!RC
    Reads: Revenue!B2
    """
    df = book.frame('P&L')
    revenue = book.frame('Revenue')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[2, cols] = across(revenue, 2, cols)


def revenue(book: FrameBook) -> None:
    r"""Revenue

    'P&L'!B3:M3 (12 columns, copied across).
    Excel (B3): =Revenue!B12
    Pattern (R1C1): =Revenue!R[9]C
    Reads: Revenue!B12
    """
    df = book.frame('P&L')
    revenue = book.frame('Revenue')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[3, cols] = across(revenue, 12, cols)


def costs(book: FrameBook) -> None:
    r"""Costs

    'P&L'!B4:M4 (12 columns, copied across).
    Excel (B4): =Costs!B6
    Pattern (R1C1): =Costs!R[2]C
    Reads: Costs!B6
    """
    df = book.frame('P&L')
    costs = book.frame('Costs')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[4, cols] = across(costs, 6, cols)


def gross_margin(book: FrameBook) -> None:
    r"""Gross margin

    'P&L'!B5:M5 (12 columns, copied across).
    Excel (B5): =B3-B4
    Pattern (R1C1): =R[-2]C-R[-1]C
    Reads: 'P&L'!B3, 'P&L'!B4
    """
    df = book.frame('P&L')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[5, cols] = (num(across(df, 3, cols)) - num(across(df, 4, cols)))


def margin(book: FrameBook) -> None:
    r"""Margin %

    'P&L'!B6:M6 (12 columns, copied across).
    Excel (B6): =IFERROR(B5/B3,"n/a")
    Pattern (R1C1): =IFERROR(R[-1]C/R[-3]C,"n/a")
    Reads: 'P&L'!B3, 'P&L'!B5
    """
    df = book.frame('P&L')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[6, cols] = iferror(div(num(across(df, 5, cols)), num(across(df, 3, cols))), 'n/a')


def tax(book: FrameBook) -> None:
    r"""Tax

    'P&L'!B7:M7 (12 columns, copied across).
    Excel (B7): =MAX(0,B5)*TaxRate
    Pattern (R1C1): =MAX(0,R[-2]C)*TaxRate
    Reads: Assumptions!B6, 'P&L'!B5, name TaxRate
    """
    df = book.frame('P&L')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[7, cols] = (max_(0, across(df, 5, cols)) * num(book.name_table('TaxRate')))


def net_result(book: FrameBook) -> None:
    r"""Net result

    'P&L'!B8:M8 (12 columns, copied across).
    Excel (B8): =B5-B7
    Pattern (R1C1): =R[-3]C-R[-1]C
    Reads: 'P&L'!B5, 'P&L'!B7
    """
    df = book.frame('P&L')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[8, cols] = (num(across(df, 5, cols)) - num(across(df, 7, cols)))


def net_ytd(book: FrameBook) -> None:
    r"""Net YTD

    'P&L'!B9 (one cell).
    Excel (B9): =B8
    Reads: 'P&L'!B8
    """
    p_l = book['P&L']
    p_l["B9"] = p_l["B8"]


def net_ytd_c9(book: FrameBook) -> None:
    r"""Net YTD

    'P&L'!C9:M9 (11 columns, copied across).
    Excel (C9): =B9+C8
    Pattern (R1C1): =RC[-1]+R[-1]C
    Reads: 'P&L'!C8, 'P&L'!B9
    """
    p_l = book['P&L']
    for c in range(3, 14):
        p_l[9, c] = p_l[9, c - 1] + p_l[8, c]


def quarter(book: FrameBook) -> None:
    r"""Quarter

    'P&L'!B10:M10 (12 columns, copied across).
    Excel (B10): ="Q"&ROUNDUP(Costs!B2/3,0)
    Pattern (R1C1): ="Q"&ROUNDUP(Costs!R[-8]C/3,0)
    Reads: Costs!B2
    """
    df = book.frame('P&L')
    costs = book.frame('Costs')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[10, cols] = concat('Q', roundup(div(num(across(costs, 2, cols)), 3), 0))


def revenue_b13(book: FrameBook) -> None:
    r"""Revenue

    'P&L'!B13:B16 (4 rows, copied down).
    Excel (B13): =SUMIFS($B$3:$M$3,$B$10:$M$10,$A13)
    Pattern (R1C1): =SUMIFS(R3C2:R3C13,R10C2:R10C13,RC1)
    Reads: 'P&L'!A13, 'P&L'!B3:M3, 'P&L'!B10:M10
    """
    df = book.frame('P&L')
    rows = slice(13, 16)
    df.loc[rows, 'B'] = sumifs(book.table('P&L', "B3:M3"), book.table('P&L', "B10:M10"), col(df, 'A', rows))


def net_result_c13(book: FrameBook) -> None:
    r"""Net result

    'P&L'!C13:C16 (4 rows, copied down).
    Excel (C13): =SUMIFS($B$8:$M$8,$B$10:$M$10,$A13)
    Pattern (R1C1): =SUMIFS(R8C2:R8C13,R10C2:R10C13,RC1)
    Reads: 'P&L'!A13, 'P&L'!B8:M8, 'P&L'!B10:M10
    """
    df = book.frame('P&L')
    rows = slice(13, 16)
    df.loc[rows, 'C'] = sumifs(book.table('P&L', "B8:M8"), book.table('P&L', "B10:M10"), col(df, 'A', rows))