"""Formulas from sheet 'Orders' 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_f2(book: FrameBook) -> None:
    r"""Month

    Orders!F2:F301 (300 rows, copied down).
    Excel (F2): =EOMONTH(B2,0)
    Pattern (R1C1): =EOMONTH(RC[-4],0)
    Reads: Orders!B2
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'F'] = eomonth(col(df, 'B', rows), 0)


def region(book: FrameBook) -> None:
    r"""Region

    Orders!G2:G301 (300 rows, copied down).
    Excel (G2): =VLOOKUP(C2,Lookups!$E$3:$F$8,2,FALSE)
    Pattern (R1C1): =VLOOKUP(RC[-4],Lookups!R3C5:R8C6,2,FALSE)
    Reads: Orders!C2, Lookups!E3:F8
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'G'] = vlookup(col(df, 'C', rows), book.table('Lookups', "E3:F8"), 2, exact=True)


def unit_price(book: FrameBook) -> None:
    r"""Unit price

    Orders!H2:H301 (300 rows, copied down).
    Excel (H2): =VLOOKUP(D2,Lookups!$A$3:$C$12,3,FALSE)
    Pattern (R1C1): =VLOOKUP(RC[-4],Lookups!R3C1:R12C3,3,FALSE)
    Reads: Orders!D2, Lookups!A3:C12
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'H'] = vlookup(col(df, 'D', rows), book.table('Lookups', "A3:C12"), 3, exact=True)


def gross_eur(book: FrameBook) -> None:
    r"""Gross EUR

    Orders!I2:I301 (300 rows, copied down).
    Excel (I2): =E2*H2
    Pattern (R1C1): =RC[-4]*RC[-1]
    Reads: Orders!E2, Orders!H2
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'I'] = (num(col(df, 'E', rows)) * num(col(df, 'H', rows)))


def discount_eur(book: FrameBook) -> None:
    r"""Discount EUR

    Orders!J2:J301 (300 rows, copied down).
    Excel (J2): =IF(I2>Assumptions!$B$11,I2*Assumptions!$B$12,0)
    Pattern (R1C1): =IF(RC[-1]>Assumptions!R11C2,RC[-1]*Assumptions!R12C2,0)
    Reads: Assumptions!B11, Assumptions!B12, Orders!I2
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'J'] = where(gt(col(df, 'I', rows), book.at('Assumptions', "B11")), (num(col(df, 'I', rows)) * num(book.at('Assumptions', "B12"))), 0)


def net_gbp(book: FrameBook) -> None:
    r"""Net GBP

    Orders!K2:K149 (148 rows, copied down).
    Excel (K2): =(I2-J2)*FX_EUR_GBP
    Pattern (R1C1): =(RC[-2]-RC[-1])*FX_EUR_GBP
    Reads: Assumptions!B7, Orders!I2, Orders!J2, name FX_EUR_GBP
    """
    df = book.frame('Orders')
    rows = slice(2, 149)
    df.loc[rows, 'K'] = ((num(col(df, 'I', rows)) - num(col(df, 'J', rows))) * num(book.name_table('FX_EUR_GBP')))


def period(book: FrameBook) -> None:
    r"""Period

    Orders!L2:L301 (300 rows, copied down).
    Excel (L2): =TEXT(B2,"yyyy-mm")
    Pattern (R1C1): =TEXT(RC[-10],"yyyy-mm")
    Reads: Orders!B2
    """
    df = book.frame('Orders')
    rows = slice(2, 301)
    df.loc[rows, 'L'] = text(col(df, 'B', rows), 'yyyy-mm')


def range_k151_k301(book: FrameBook) -> None:
    r"""Orders!K151:K301

    Orders!K151:K301 (151 rows, copied down).
    Excel (K151): =(I151-J151)*FX_EUR_GBP
    Pattern (R1C1): =(RC[-2]-RC[-1])*FX_EUR_GBP
    Reads: Assumptions!B7, Orders!I151, Orders!J151, name FX_EUR_GBP
    """
    df = book.frame('Orders')
    rows = slice(151, 301)
    df.loc[rows, 'K'] = ((num(col(df, 'I', rows)) - num(col(df, 'J', rows))) * num(book.name_table('FX_EUR_GBP')))