"""Formulas from sheet 'Revenue' 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 product_b2(book: FrameBook) -> None:
    r"""Product

    Revenue!B2 (one cell).
    Excel (B2): =EOMONTH(Assumptions!$B$4,0)
    Reads: Assumptions!B4
    """
    revenue = book['Revenue']
    assumptions = book['Assumptions']
    revenue["B2"] = xl.EOMONTH(assumptions["B4"], 0)


def product_c2(book: FrameBook) -> None:
    r"""Product

    Revenue!C2:M2 (11 columns, copied across).
    Excel (C2): =EOMONTH(B2,1)
    Pattern (R1C1): =EOMONTH(RC[-1],1)
    Reads: Revenue!B2
    """
    revenue = book['Revenue']
    for c in range(3, 14):
        revenue[2, c] = xl.EOMONTH(revenue[2, c - 1], 1)


def p_100(book: FrameBook) -> None:
    r"""P-100

    Revenue!B3:M11 (9 x 12 cells, filled).
    Excel (B3): =SUMIFS(Orders!$K:$K,Orders!$F:$F,B$2,Orders!$D:$D,$A3)
    Pattern (R1C1): =SUMIFS(Orders!C11:C11,Orders!C6:C6,R2C,Orders!C4:C4,RC1)
    Reads: Revenue!B2, Revenue!A3, Orders!D1:D301, Orders!F1:F301, Orders!K1:K301
    """
    df = book.frame('Revenue')
    rows = slice(3, 11)
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[rows, cols] = sumifs(book.table('Orders', "K1:K301"), book.table('Orders', "F1:F301"), cells(df, rows, cols, fixed_row=2), book.table('Orders', "D1:D301"), cells(df, rows, cols, fixed_col='A'))


def total(book: FrameBook) -> None:
    r"""Total

    Revenue!N3:N11 (9 rows, copied down).
    Excel (N3): =SUM(B3:M3)
    Pattern (R1C1): =SUM(RC[-12]:RC[-1])
    Reads: Revenue!B3:M3
    """
    revenue = book['Revenue']
    for r in range(3, 12):
        revenue[r, 'N'] = xl.SUM(revenue.rng(r, 'B', r, 'M'))


def total_b12(book: FrameBook) -> None:
    r"""Total

    Revenue!B12:N12 (13 columns, copied across).
    Excel (B12): =SUM(B3:B11)
    Pattern (R1C1): =SUM(R[-9]C:R[-1]C)
    Reads: Revenue!B3:B11
    """
    revenue = book['Revenue']
    for c in range(2, 15):
        revenue[12, c] = xl.SUM(revenue.rng(3, c, 11, c))


def uk_i(book: FrameBook) -> None:
    r"""UK&I

    Revenue!B15:M17 (3 x 12 cells, filled).
    Excel (B15): =SUMIFS(Orders!$K:$K,Orders!$F:$F,B$2,Orders!$G:$G,$A15)
    Pattern (R1C1): =SUMIFS(Orders!C11:C11,Orders!C6:C6,R2C,Orders!C7:C7,RC1)
    Reads: Revenue!B2, Revenue!A15, Orders!F1:F301, Orders!G1:G301, Orders!K1:K301
    """
    df = book.frame('Revenue')
    rows = slice(15, 17)
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[rows, cols] = sumifs(book.table('Orders', "K1:K301"), book.table('Orders', "F1:F301"), cells(df, rows, cols, fixed_row=2), book.table('Orders', "G1:G301"), cells(df, rows, cols, fixed_col='A'))


def range_n15_n17(book: FrameBook) -> None:
    r"""Revenue!N15:N17

    Revenue!N15:N17 (3 rows, copied down).
    Excel (N15): =SUM(B15:M15)
    Pattern (R1C1): =SUM(RC[-12]:RC[-1])
    Reads: Revenue!B15:M15
    """
    revenue = book['Revenue']
    for r in range(15, 18):
        revenue[r, 'N'] = xl.SUM(revenue.rng(r, 'B', r, 'M'))


def orders_in_month(book: FrameBook) -> None:
    r"""Orders in month

    Revenue!B19:M19 (12 columns, copied across).
    Excel (B19): =COUNTIF(Orders!$F:$F,B$2)
    Pattern (R1C1): =COUNTIF(Orders!C6:C6,R2C)
    Reads: Revenue!B2, Orders!F1:F301
    """
    df = book.frame('Revenue')
    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M']
    df.loc[19, cols] = countif(book.table('Orders', "F1:F301"), across(df, 2, cols))