lib module

lib.admin module

class lib.admin.Admin

Bases: object

Administrative functions for a session.

check_db_update(path: Optional[pathlib.Path] = None, check_only: bool = False) → Optional[bool]

Checks for any Sqlite DB updates that need to be copied to the user’s cache.

Parameters

path (Path, optional) – The path to a cached Sqlite DB to check. If None, will check all cached versions.
check_only (bool, default=False) – If True, will only return a bool value determining whether or not an update is available.

Returns

A boolean value if ‘check_only’ is passed as True. Otherwise, nothing is returned while the updated DBs are copied to the cache.

Return type

bool, optional

check_reports_update() → bool

Checks if the reports config cache must be updated with a new version.

Returns: True if the cache must be updated, otherwise False.
Return type: bool

get_bin_config(bin_name: str) → Optional[dict[str, Union[str, list[str]]]]

get_db_root(cache: Optional[bool] = None) → pathlib.Path

Returns the path where the Sqlite DBs are saved.

Parameters: cache (bool, optional) – Overrides the ‘cache’ option in the user’s configuration file.
Returns: Path where the Sqlite DBs are saved, either the server’s version or the user’s cached version (if elected).
Return type: Path

lib.admin.create_dirs(dirs: list[pathlib.Path], logger: logging.Logger) → None

Creates the initial directory structure for a new user.

Parameters

dirs (list[Path]) – list of all directories to be created relative to the root.
logger (logging.Logger) – The logger object to log to.

lib.admin.create_files(path: pathlib.Path, f: str, logger: logging.Logger) → dict

Creates the initial configuration files for a new user.

Parameters

path (Path) – The root path where the file will be saved.
f (str) – The name of the config file to be saved.
logger (logging.Logger) – The logger object to log to.

Returns

The serialized dictionary object for the saved configuration file.

Return type

dict

lib.admin.initializor(func)

lib.admin.logger(name: str, log_level=20) → logging.Logger

Creates the logging object used for ade.

Parameters

name (str) – The name of the program for logger to use.
log_level (int, default=20) – The log level to use (defaults to logging.INFO == 20).

Return type

logging.Logger

lib.arguments module

class lib.arguments.Argument(name: tuple[str, ...], params: dict[str, Union[list[str], str, int, type]])

Bases: object

Struct containing the details for each argument.

name

The name(s) of each argument.

Type: tuple[str, …]

params

The argument options.

Type: dict[str, Union[list[str], str, int, type]]

name: tuple[str, ...]

params: dict[str, Union[list[str], str, int, type]]

class lib.arguments.Parser(name: str, parser: gooey.python_bindings.gooey_parser.GooeyParser, report: lib.reports.Report, required: Optional[gooey.python_bindings.gooey_parser.GooeyArgumentGroup] = None, optional: Optional[gooey.python_bindings.gooey_parser.GooeyArgumentGroup] = None)

Bases: object

Struct to organize the groupings of sub-parsers based on each report config.

name

The name of the sub-parser as report-subreport.

Type: str

parser

The parser object.

Type: GooeyParser

report

The Report struct for the specific sub-parser.

Type: rp.Report

required

Collection of required arguments (table, columns, date).

Type: GooeyArgumentGroup, optional

optional

Collection of optional arguments (all others).

Type: GooeyArgumentGroup, optional

name: str

optional: gooey.python_bindings.gooey_parser.GooeyArgumentGroup = None

parser: gooey.python_bindings.gooey_parser.GooeyParser

report: lib.reports.Report

required: gooey.python_bindings.gooey_parser.GooeyArgumentGroup = None

lib.arguments.create_list_of_list_from_list(value: list[Any], len_of_tup: int) → list[list[Any]]

Creates a list of lists.

Required for certain parameters (e.g. joins).

Parameters

value (list[Any]) –
len_of_tup (int) –

Return type

list[list[Any]]

lib.arguments.get_image_path(relative_path: str) → pathlib.Path

Gets the absolute path of the relative path.

Required for binary files to be included in the PyInstaller executable.

Parameters: relative_path (str) – The path (relative to the build) of the inode to be included in the exe.
Returns: Absolute path.
Return type: Path

lib.arguments.get_schema_path() → str

Gets the schema path relative to Admin.reference.

Return type: str

lib.arguments.get_version() → str

Gets the ade version.

If running a git build, will try pulling the version through git describe. Otherwise, will default to version as set under the global VERSION.

Returns: The version number.
Return type: str

lib.arguments.prepare_arguments(args: argparse.Namespace) → argparse.Namespace

Cleans the Gooey-compatible arguments to what ade can read.

There are a couple of parameters that have been key/value pairings, which can’t be typed through an argparse command. The syntax is therefore @KEY VALUE… for each. Once those args are passed this will convert them to the proper format.

The special ors parameter is also converted to its expected list of tuples, append joins to a dictionary with a list of tuples.

Parameters: args (argparse.Namespace) – Arguments as parsed by Gooey
Returns: Cleaned arguments as expected by ade.
Return type: argparse.Namespace

lib.arguments.separate_dict_params(param: dict[str, Union[list[tuple[str, str]], tuple[str, str], list[Union[int, str]]]]) → list[str]

Converts the key/value argument pairings of a report to the Gooey str version.

There are a couple of parameters that have been key/value pairings, which can’t be typed through an argparse command. The syntax is therefore @KEY VALUE… for each.

Parameters: param (dict[str, Union[list[tuple[str, str]], tuple[str, str], list[Union[int, str]]]]) – The report parameters to be converted.
Returns: List of Gooey-compatible @KEY VALUE… pairings.
Return type: list[str]

lib.connections module

lib.dates module

lib.dates.calendar(year: int, month: int = 0, ref: Optional[datetime.date] = None, rng: bool = False) → list[datetime.date]

Outputs a date range of the correct accounting year or month given those values as a reference point.

Parameters

year (int) – An integer value signifying year.
month (int, default=0) – An integer value signifying a month of the year.
ref (d.date, optional) – The reference date. If present, will check if it falls between the date range values set by $year and $month, outputting the correct month range if not.
rng (bool) – If True, will only return the start/end date of the given month instead of including the d.date of the month as well.

Returns

The correct accounting month calendar range for the given month as well as the datetime of year/month linked to that range.

Return type

list[d.date]

lib.dates.close(ytd: bool = False, delta: int = 0, rng: bool = False) → list[datetime.date]

Outputs the most recent start/end accounting close calendar range.

Can either output the most recently closed month or YTD to the most recently closed month (along with X number of years past).

Parameters

ytd (bool, default=False) – Changes the output range from current month close to YTD up to current month close.
delta (int, default=0) – Used in tandem with <ytd>, goes X number of years back.
rng (bool) – If True, will only return the start/end date of the given month instead of including the d.date of the month as well.

Returns

The correct accounting close calendar range for the most recent month as well as the datetime of year/month linked to that range.

Return type

list[d.date]

lib.dates.days_in_month(df: pandas.core.frame.DataFrame, year: str, month: str, week: bool = False) → pandas.core.series.Series

Gets the number of days/weeks in a given month - vectorized.

Parameters

year (pd.Series) – Year of data.
month (pd.Series) – Month of data.
week (bool, default=False) – True will return the number of weeks in a month instead of number of days.

Return type

pd.Series

lib.dates.from_excel(ordinal: int, sql: bool = False, string: bool = False, iso: bool = True) → Union[datetime.date, str, list[datetime.date], list[str]]

Converts a Microsoft Excel datetime code into a standard datetime.date.

Parameters

ordinal (int) – The date code(s) used by Excel to signify a date object.
sql (bool, default=False) – Set to True if working with a CorporateODS SQL TimeId.
string (bool, default=Fasle) – If True, outputs the string version of the date.
iso (bool, default=True) – If True, the string value if in ISO format; otherwise, it’s %Y%m%d.

Returns

Either the string or datetime version of the date converted from it’s timecode.

Return type

Union[d.date, str, list[d.date], list[str]]

lib.dates.monthcount(d_rng: tuple[datetime.date, datetime.date], detail: bool = False, year: bool = False) → dict[Union[int, datetime.date], int]

Counts the number of days in each month present in the month.

This method does not yet use the accounting month. The month with the highest len() is accounting month for the specified range.

Parameters

d_rng (tuple[d.date, d.date]) – Array containing the start and end date of an accounting month.
detail (bool, default=False) – Outputs the range for each month instead of just the number of days.
year (bool, default=False) – Will output the number of days in the year instead of each month.

Returns

bkdn – Breakdown of each year/month combination along with its quantity in the date range. Only returned over acc_month if detail=True.

Return type

dict[Union[int, d.date], int]

Raises

ValueError – When one of the supplied values in d_rng is not a datetime.
IndexError – When the length of d_rng contains more than 2 values.

lib.dates.to_date_id(date: tuple[str, str]) → tuple[int, int]

Converts the date param from Data into an ordinal range based off of the accounting financial calendar TimeId.

Parameters: date (tuple[str, str]) – The date range to pass through as a SQL parameters. Must be a string in the form of ‘YYYY-MM’.
Return type: tuple[int, int]

lib.dates.to_excel(date: Union[str, datetime.date], sql: bool = False) → Union[int, list[int]]

Converts a datetime into an ordinal Microsoft Excel datetime code.

Parameters

date (Union[str, d.date]) – The date string to convert to ordinal ‘YYYY-MM-DD’
sql (bool, default=False) – Set to True if working with a CorporateODS SQL TimeId.

Return type

Union[int, list[int]]

lib.dates.vector_cal(date: pandas.core.series.Series) → list[datetime.date]

Gets the financial calendar - vectorized.

Parameters: date (pd.Series) – Date series to put through to calendar().
Return type: list[d.date]

lib.formatting module

lib.formatting.adhoc_data(sht: xlwings.main.Sheet, rng: xlwings.main.Range, data: pandas.core.frame.DataFrame)

lib.formatting.adhoc_pivot(app: xlwings.main.App, book: xlwings.main.Book, sht: xlwings.main.Sheet, rng: xlwings.main.Range, data: pandas.core.frame.DataFrame, pivot_struct: lib.reports.PivotArgs)

lib.local module

lib.local.get(db: str, table: str, columns: bool = False, types: bool = False, slicers: Optional[dict[str, Union[tuple, list[str]]]] = None) → Union[list[str], pandas.core.frame.DataFrame]

Pulls in the relevant data given the slicers (if applicable) for a given table in a SQLite3 database.

Parameters

table (str) – The name of the database table to pull.
columns (bool, default=False) – Gets the column names present in the passed <table>.
dtypes (bool, default=False) – Gets the dtypes of the columns for pandas.
slicers (dict[str, list[str]], optional) – Key/Value pairing of the column name to search in with the values to search/go against.

Returns

The properly indexed (if applicable) DataFrame of the passed database table or the columns if columns=True.

Return type

Union[list[str], pd.DataFrame]

lib.local.locations(pull: bool = True, update: Optional[dict[str, list]] = None) → Optional[dict]

Pulls and/or updates the hand-crafted Location typo fixer database.

Parameters

pull (bool, default=True) – If True, pulls the information stored in the local database.
update (dict[str, list], optional) – If passed, will update the database with the new values of the dictionary.

Return type

Optional[dict]

lib.local.products(pull: bool = True, update: bool = False, key: str = 'product')

Pulls and/or updates the product listing from dim.ProductHierarchy.

Less used for reference but necessary for getting the correct product names when pulling data from rev.Revenue or especially from fin.GeneralLedger.

Parameters

pull (bool, default=True) – If True, pulls the information stored in the local database.
update (bool, default=False) – If True, pulls the newest information from CorporateODS and saves the changes locally.
key (str, default="product") – The value to be used as the key in the resulting DataFrame, making sure each value is unique. Necessary for merging.

lib.local.save_file(data: pandas.core.frame.DataFrame, table: str) → None

Saves the given data to a CSV for use as a faux-database in Excel.

Parameters

data (pd.DataFrame) – The prepared DataFrame to be exported as a CSV.
table (str) – The name of the database table from where the data was pulled from, used in the naming scheme (with the help of the dict).

lib.local.schema(pull: bool = True, update: bool = False, columns: list[str] = ['*'], slicers: Optional[dict[str, Union[tuple, list[str]]]] = None) → Optional[pandas.core.frame.DataFrame]

Pulls and/or updates the CorporateODS database schema for easy viewing.

Parameters

pull (bool, default=True) – If True, pulls the information stored in the local database.
update (bool, default=False) – If True, pulls the newest information from CorporateODS and saves the changes locally.
columns (list, default=["*"]) – The list of columns to pull.
slicers (dict[str, list[str]], optional) – Key/Value pairing of the column name to search in with the values to search/go against.

Returns

DataFrame output of the ade SQL schema.

Return type

Optional[pd.DataFrame]

lib.local.sites(pull: bool = True, update: Optional[list[tuple[str, str, str]]] = None) → Optional[pandas.core.frame.DataFrame]

Pulls and/or updates the hand-crafted Axiall/Westlake equipment sites.

Parameters

pull (bool, default=True) – If True, pulls the information stored in the local database.
update (list[tuple[str, str, str]], optional) –
If passed, will update the database with the new values of the list. Each tuple consists of the following order:

customerOrderNo agreementNumber site

Return type

Optional[pd.DataFrame]

lib.local.time() → pandas.core.frame.DataFrame

Pulls the CorporateODS Time table.

Parameters: pull (bool) – If True, pulls the information from ade’s database.
Return type: pd.DataFrame

lib.mail module

lib.mail.email(subject: str = '', to: list[str] = [], cc: list[str] = [], signature: str = '', message: str = '', path: Optional[str] = '', confirmation: bool = None) → bool

Prepares the email message based on the given criteria.

Parameters

subject (str, default="") – The subject of the email.
to (list[str], default=[]) – List of email recipients. Although optional, must be used if no CC.
cc (list[str], default=[]) – List of email CC’d recipients. Although optional, must be used if no TO.
signature (str, default="") – The signature to be used at the end of the email message.
message (str, default="") – The main body of the email.
path (Optional[str], default="") – The path of the file to attach.
confirmation (bool, optional) – Whether or not the user must manually confirm whether to send the email or not. True will output the recipients, message, and attachment to verify.

Returns

If the message was sent or not.

Return type

bool

lib.national module

lib.national.add_hierarchy(nums: list[str], parent: str, date: datetime.datetime = datetime.datetime(2022, 6, 17, 10, 29, 48, 860846), desc: Optional[str] = None, other: str = None, append: bool = True, sector: str = None, manager: str = None, watch: bool = True) → pandas.core.frame.DataFrame

Adds new customer numbers or agreements to the National Account hierarchy database.

Parameters

nums (list[str]) – The Agreement or Customer numbers to be added to the hierarchy.
parent (str) – The parent account of the addition.
date (dt.datetime, default=dt.datetime.today()) – The datetime of the addition.
desc (str, optional) – The reasoning behind the addition.
other (str, optional) – An additional value to be used at user discretion to go along with the addition (only used for Customer).
append (bool, default=True) – Declares whether the <num> will be added to the hierarchy or removed (only used for Agreement, special case due to need to remove an agreement from one parent but add it to another).
sector (str, optional) – The sector (or industry) associated with the parent account of the addition (only used if the parent account does not yet exist).
manager (str, optional) – The manager associated with the parent account of the addition (only used if the parent account does not yet exist).
watch (bool, default=True) – If True, the parent account is set to be watched and therefore used in associated monthly reports (only used if the parent account does not yet exist).

Returns

The properly indexed (if applicable) DataFrame of the passed database table pulled with the local.get(‘national’, ) method.

Return type

pd.DataFrame

Raises

ValueError – Missing values needed for the correct functioning of the method.
IndexError – The passed <nums> is not valid and cannot be found in the ade database.

lib.national.add_parents(parent: str, manager: str, sector: str = 'Other', watch: bool = True) → pandas.core.frame.DataFrame

Adds new parent accounts to the National Account database.

Parameters

parent (str) – The name of the parent account to be added.
manager (str) – The manager of the parent account to be added.
sector (str, default="Other") – The sector (industry) of the parent account to be added.
watch (bool, default=True) – If True, the parent account is set to be watched and therefore used in associated monthly reports.

Returns

Dataframe of the Parents table.

Return type

pd.DataFrame

lib.national.del_hierarchy(table: str, slicers: dict[str, Union[tuple, list[str]]]) → Optional[pandas.core.frame.DataFrame]

Deletes from the Hierarchy table in the National Account database.

Parameters

table (str) – The name of the database table to pull.
slicers (dict[str, Union[tuple, list[str]]]) – Key/Value pairing of the column name to search in with the values to search/go against.

Returns

The updated Hierarchy dataframe with the deletions.

Return type

Optional[pd.DataFrame]

lib.national.mod_budget(table: str, data: list = None, slicers: dict[str, Union[tuple, list[str]]] = None, setters: dict[str, str] = None) → pandas.core.frame.DataFrame

Modifies the Budget or Stretch table in the National Account database.

Parameters

table (str) – The name of the database table to pull.
data (list) – Nested list consisting of the cols/rows with the budget values to update. Consistent with xlwings.
slicers (dict[str, Union[tuple, list[str]]], optional) – Key/Value pairing of the column name to search in with the values to search/go against.
setters (dict[str, str], optional) – Key/Value pairing of the value to set to the column name given the slicers.

Returns

The updated Budget or Stretch DataFrame.

Return type

pd.DataFrame

Raises

ValueError – If neither data nor both slicers and setters are passed.

lib.national.mod_hierarchy(table: str, slicers: dict[str, Union[tuple, list[str]]], setters: dict[str, str]) → pandas.core.frame.DataFrame

Modifies the Hierarchy table in the National Account database.

Parameters

table (str) – The name of the database table to pull.
slicers (dict[str, Union[tuple, list[str]]]) – Key/Value pairing of the column name to search in with the values to search/go against.
setters (dict[str, str]) – Key/Value pairing of the value to set to the column name given the slicers.

Returns

Dataframe of the passed table (Hierarchy, Agreements).

Return type

pd.DataFrame

Raises

KeyError – If the modification timestamp and description aren’t included.

lib.national.mod_parents(slicers: dict[str, list[str]], setters: dict[str, str]) → list[pandas.core.frame.DataFrame]

Modifies the Parents table in the National Account database.

Parameters

slicers (dict[str, list[str]]) – Key/Value pairing of the column name to search in with the values to search/go against.
setters (dict[str, str]) – Key/Value pairing of the value to set to the column name given the slicers.

Returns

list consisting of the DataFrames of the Hierarchy, Agreements, and Parents tables.

Return type

list[pd.DataFrame]

Raises

KeyError – If the modification timestamp and description aren’t included.

lib.natural_keys module

lib.natural_keys.atoi(text)

Helper function for natural_keys.

Parameters: text (Union[int, str]) –

lib.natural_keys.natural_keys(text)

alist.sort(key=natural_keys) sorts in human order

http://nedbatchelder.com/blog/200712/human_sorting.html

Parameters: text (Union[int, str]) – The text to sort.

lib.remote module

class lib.remote.Compile(output: lib.remote.Output, include_data: bool = False)

Bases: object

Runs the main report functions that modify/aggreate/create Output to prep for File.

__init__(output: lib.remote.Output, include_data: bool = False)

Manages the main report function and sets up the data along with its sheet struct.

Parameters

output (Output) – The Output object.
pivot_args (list[rep.PivotArgs], optional) – The arguments to pass in order to create a PivotTable.

output

The Output object.

Type: Output

construct

The Construct object.

Type: Union[Construct, Super]

report

The Report specification.

Type: rep.Report

data

The amalgamation of the compiled report and its spreadsheet output location.

Type: dict[str, DataStructure]

_metadata() → dict[str, Union[str, dict[str, Any]]]: Prepares the metadata of the class to easily insert into the spreadsheet output.

adhoc_pivot() → None

Creates pivot tables based on the criteria given.

Parameters: pivot_args (list[rep.PivotArgs]) – The arguments to pass in order to create a PivotTable.

client_slides() → dict[str, lib.remote.DataStructure]

Compiles a client_slides report.

Return type: dict[str, DataStructure]

equipment_report(style: Optional[str] = None, cols: Optional[list] = None, agg: Optional[dict] = None, sort: Optional[list] = None) → dict[str, lib.remote.DataStructure]

Compiles an equipment report.

Parameters

style (str, optional) – Specific report style. Changes how parts of the report are created; legacy from the old repo. The style options are axiall, teresa, exxon, serialized, standard, total, and nototal.
cols (list, optional) – The columns to include in the final output. If None, all columns present in the initial data[“df”].df are used in the final pivot table.
agg (dict, optional) – The options used to pivot the data by (index and values).
sort (list, optional) – Will sort the final output by the specified columns.

Return type

dict[str, DataStructure]

file(open_file: bool = False, filename: str = '') → lib.remote.File

Calls File to finalize the report into a spreadsheet and save the file.

Parameters

open_file (bool) – To specify if the report should be created and saved in the background or opened up for the user to modify.
filename (str, optional) – The override for the report filename. If “”, will default.

Return type

File

national_account(is_recursion: bool = False, pull_previous: bool = False) → dict[str, lib.remote.DataStructure]

Compiles the national account report.

Parameters

is_recursion (bool) – If True, the method is being run recursively in order to pull the previous year’s revenue. Should not be set to True, it is done in the method itself.
pull_previous (bool) – If True, forces the previous year revenue to be pulled regardless of the circumstances.

Return type

dict[str, DataStructure]

product_revenue() → dict[str, lib.remote.DataStructure]

Compiles a product revenue report.

Return type: dict[str, DataStructure]

rebate() → dict[str, lib.remote.DataStructure]

Compiles a rebate report.

Return type: dict[str, DataStructure]

rentals_rates() → dict[str, lib.remote.DataStructure]

Compiles a rentals and rates report.

Return type: dict[str, DataStructure]

class lib.remote.Construct(table: str, columns: list[str], date: tuple[str, str], joins: dict[str, list[tuple[str, str]]] = {}, clients: Optional[list[str]] = None, agreements: Optional[list[str]] = None, invoices: Optional[list[str]] = None, departments: Optional[list[str]] = None, pos: Optional[dict[str, list[str]]] = None, regions: Optional[dict[str, list[str]]] = None, accounts: Optional[dict[str, list[str]]] = None, products: Optional[dict[str, list[str]]] = None, sectors: Optional[dict[str, list[str]]] = None, other: Optional[dict[str, list[Union[str, int]]]] = None, ors: Optional[list[tuple[str, str]]] = None, sql: Optional[dict[str, list[str]]] = None)

Bases: object

Parses, validates, and constructs the passed SQL parameters and columns.

__init__(table: str, columns: list[str], date: tuple[str, str], joins: dict[str, list[tuple[str, str]]] = {}, clients: Optional[list[str]] = None, agreements: Optional[list[str]] = None, invoices: Optional[list[str]] = None, departments: Optional[list[str]] = None, pos: Optional[dict[str, list[str]]] = None, regions: Optional[dict[str, list[str]]] = None, accounts: Optional[dict[str, list[str]]] = None, products: Optional[dict[str, list[str]]] = None, sectors: Optional[dict[str, list[str]]] = None, other: Optional[dict[str, list[Union[str, int]]]] = None, ors: Optional[list[tuple[str, str]]] = None, sql: Optional[dict[str, list[str]]] = None)

Sets up the argument validation based on given criteria, further setting up the SQL query to run.

Parameters

table (str) – The name of the table to pull from the CorporateODS SQL server
columns (list[str]) – Collection of table columns to be pulled (includes columns obtained through JOINs).
date (tuple[str, str]) – The date range for the SQL pull. Must be in YYYY-MM format, and will be further converted to create the params.ordate attribute.
joins (dict[str, tuple[str, str]], optional) – Collection of the column to join and a tuple of the two columns to be joined on.
clients (list[str], optional) – Collection of customerNumber to be parameterized.
agreements (list[str], optional) – Collection of agreementNumber to be parameterized.
invoices (list[str], optional) – Collection of invoiceNumber to be parameterized.
departments (list[str], optional) – Collection of department to be parameterized.
divisions (list[str], optional) – Collection of division to be parameterized.
pos (dict[str, list[str]], optional) – Collection of customerOrderNo OR purchaseOrderNo to be parameterized.
regions (dict[str, list[str]], optional) – Collection of the specific region column and a list of its values to be parameterized. The keys can be any of the following: region, area, businessUnit, or costCenterCode.
accounts (dict[str, list[str]], optional) – Collection of the specific accounting code column and a list of its values to be parameterized. The keys can be any of the following: category, categoryDesc, accountGroup, accountGroupDesc, account, or accountIdDesc.
products (dict[str, list[str]], optional) – Collection of the specific product column and a list of its values to be parameterized. The keys can be any of the following: productFamily, productLine, productCategory, productGroup, productCode, itemCode, or individualItemNumber.
sectors (dict[str, list[str]], optional) – Collection of the specific sector column and a list of its values to be parameterized. The keys can be any of the following: industryGroup, industry, or aicCode.
other (dict[str, list[Union[str, int]]], optional) – Collection of any other column and the values to be parameterized.
ors (list[tuple[str, str]], optional) – Special case parameter. WHERE statements for a column are joined with OR, while those columns WHEREs are combined with an AND to other WHEREs. This overrides it so that the listed columns in the tuple are combined with an OR instead. Required for certain SQL queries (e.g. national hierarchy).
sql (dict[str, list[str]], optional) – Specific modifiers to be used in the SQL query. e.g. SELECT : [DISTINCT] or GROUP BY: [<columns>…]

params

The cleaned parameters of _Parameters.

Type: _Parameters

query

The verified parameters of _Query.

Type: _Query

_metadata() → dict[str, Union[str, list[str]]]: Prepares the metadata of the class to easily insert into the spreadsheet output.

pull(pull_methods: Optional[list[str]] = None, post_methods: Optional[list[str]] = None) → lib.remote.Output

Calls Output and pulls the SQL data given the Construct.

Parameters

pull_methods (list[str], optional) – Additional pull methods to run with Output. Overrides Report if initialized with Super.
post_methods (list[str], optional) – Additional post methods to run with Output. Overrides Report if initialized with Super.

class lib.remote.DataStructure(structure: lib.remote.SheetStructure, data: pandas.core.frame.DataFrame, output: lib.remote.Output, pivot: Optional[lib.reports.PivotArgs])

Bases: object

Struct that organizes the SheetStructure along with data itself and its Output.

structure

Struct detailing the Excel output location.

Type: SheetStructure

data

The Output.df that will be pasted in the structure location.

Type: pd.DataFrame

output

The class itself in order to keep a log of what was run for metadata to paste.

Type: Output

__init__(structure: lib.remote.SheetStructure, data: pandas.core.frame.DataFrame, output: lib.remote.Output, pivot: Optional[lib.reports.PivotArgs]) → None

class lib.remote.MetadataConverter

Bases: xlwings.conversion.framework.Converter

Custom xlwings converter for reading/writing either dictionaries with greater than single values (e.g. lists) or passing through standard DataFrames.

class lib.remote.Output(construct: Union[lib.remote.Construct, lib.remote.Super], pull_methods: Optional[list[str]] = None, post_methods: Optional[list[str]] = None)

Bases: object

Runs the SQL query and any additional functions to complement the data.

__init__(construct: Union[lib.remote.Construct, lib.remote.Super], pull_methods: Optional[list[str]] = None, post_methods: Optional[list[str]] = None)

Manages the initial SQL query pull and queues the pull/post methods to run.

Parameters

construct (Union[Construct, Super]) – The Construct object.
pull_methods (list[str], optional) – Additional pull methods to run.
post_methods (list[str], optional) – Additional post methods to run.

construct

The Construct object.

Type: Union[Construct, Super]

pull_methods

Additional pull methods to run.

Type: list[str], optional

post_methods

Additional post methods to run.

Type: list[str], optional

df

The data of the main Construct query pull.

Type: pd.DataFrame

report

The Report if initialized with Super. Otherwise a generic adhoc Report is generated with the Construct parameters specified.

Type: rep.Report

_assert_column_existence(cols: tuple, built: str, method: str) → bool

Verifies if the needed columns are present in Output.df for the pull method.

Parameters

cols (tuple) – The columns required for the pull method.
build (str) – The builtin to verify the columns vs Output.df.columns (any, all).
method (str) – The method this function was called from. Needed for the logger warning.

Returns

True if the columns exist in Output.df, False if not.

Return type

bool

Raises

AttributeError – The builtin function does not exist. Needs to be any or all.

_change_data_types()

Changes the data types to their correct versions as per the schema.

This functionality isn’t part of pd.read_sql in a way that’s comparable to pd.read_csv so it must be done after the fact.

_join_methods(queue: list[tuple[pandas.core.frame.DataFrame, Union[tuple[str, str], str], str]])

Takes the finalized queue to merge onto Output.df and cleanup.

Parameters: queue (list[tuple[pd.DataFrame, Union[tuple[str, str], str], str]]) – Includes the data output as well as the columns to merge onto Output.df and additional cleanup functions.

_metadata() → dict[str, Union[str, list[str], tuple[str, str], dict[str, Union[list[Union[int, str]], tuple[str, str]]]]]: Prepares the metadata of the class to easily insert into the spreadsheet output.

_process_methods() → Optional[list[tuple[pandas.core.frame.DataFrame, Union[tuple[str, str], str], str]]]

Loads the queue with the output of any pull methods to be run.

The queue is run in the order the methods are specified. Returns None if no pull methods were given.

Returns: Includes the data output as well as the columns to merge onto Output.df and additional cleanup functions.
Return type: Optional[list[tuple[pd.DataFrame, Union[tuple[str, str], str], str]]]

compile(compilate_args: Optional[lib.reports.CompilateArgs] = None, pivot_args: Optional[list[lib.reports.PivotArgs]] = None, include_data: bool = False) → lib.remote.Compile

Calls Compile to modify the data given its Report specification.

Parameters

compilate_args (rep.CompilateArgs, optional) – The args required for Compile. If None, uses the specified Construct.report.CompilateArgs.
pivot_args (list[rep.PivotArgs], optional) – The arguments to pass in order to create a PivotTable.
include_data (bool) – Saves the original dataframe in the compiled output.

Return type

Compile

daily_agreement(init: bool = False) → Optional[str]

Creates the dailyAgreementRate column to merge into Output.df.

The lineStatusDesc and dailyItemRate columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

file(open_file: bool = False, filename: str = '') → lib.remote.File

Calls File to finalize the report into a spreadsheet and save the file.

In reality a helper to quicky get to File as this method still calls compile().

Parameters

open_file (bool) – To specify if the report should be created and saved in the background or opened up for the user to modify.
filename (str, optional) – The override for the report filename. If “”, will default.

Return type

File

fuel_tank(init: bool = False) → Optional[str]

Creates the fuelTank column to merge into Output.df.

The itemDescription, agreementNumber, and individualItemNumber columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

get() → pandas.core.frame.DataFrame

Pulls the SQL query from the given Construct.

Return type: pd.DataFrame

get_rate(output: lib.remote.Output) → Optional[pandas.core.frame.DataFrame]

Calculates a rate dataframe IN PROGRESS.

Parameters: output (Output) –
Return type: pd.DataFrame, optional

get_usage(index: list) → Optional[pandas.core.frame.DataFrame]

Calculates a usage dataframe.

Will only run if Construct.query.table == FleetUtilisation as that data is required in order to calculate usage.

Parameters: index (list) – The columns to be used as the index in the outputted PivotTable.
Returns: The calculated usage dataframe. Will return None if unable to calculate.
Return type: pd.DataFrame, optional

itemrate_split(init: bool = False) → Optional[str]

Creates the daily, weekly, and montly rate columns to merge into Output.df.

The rateTypeDescription and netRate columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

location(init: bool = False) → Optional[tuple[Optional[pandas.core.frame.DataFrame], Optional[str], str]]

Pulls the agreement location/address information into Output.df.

The agreementNumber and customerNumber columns must be present in the dataframe in order to pull.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, Optional[str], str]]

opptype(init: bool = False) → Optional[tuple[Optional[pandas.core.frame.DataFrame], Optional[str], str]]

Pulls the Salesforce opportunity type into Output.df.

The agreementNumber column must be present in the dataframe in order to pull.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, Optional[str], str]]

parent(init: bool = False) → Optional[tuple[pandas.core.frame.DataFrame, str, str]]

Pulls the national account hierarchy into Output.df.

The customerNumber column must be present in the dataframe in order to pull.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, str, str]]

po_expiry(init: bool = False) → Optional[tuple[pandas.core.frame.DataFrame, str, str]]

Pulls the PO Expiry information into Output.df.

The agreementNumber column must be present in the dataframe in order to pull. This is the only method in ade which calls the old ABIODSQuery.adenet.biz SQL database instead of the new CorporateODS database. This old DB is less reliable and prone to timeouts.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, str, str]]

power_source(init: bool = False) → Optional[str]

Creates the powerSource column to merge into Output.df.

The productLine and productGroup columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

product(init: bool = False) → Optional[tuple[pandas.core.frame.DataFrame, str, str]]

Pulls the product information into Output.df.

Either the productCode, itemCode, or individualItemNumber columns must be present in the dataframe in order to pull.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, str, str]]

product_description(init: bool = False) → Optional[str]

Creates the productDescription column to merge into Output.df.

The productGroup and productCategory columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

rate_by_days(init: bool = False) → Optional[str]

Creates the rateByDays column to merge into Output.df.

The noOfOnhireDays and dailyItemRate columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

recurring_monthly(init: bool = False) → Optional[str]

Creates the recurringMonthly column to merge into Output.df.

The lineStatusDesc, monthlyItemRate, and orderedQuantity columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

site(init: bool = False) → Optional[tuple[pandas.core.frame.DataFrame, tuple[str, str], str]]

Pulls the agreement site into Output.df.

The customerNumber and agreementNumber columns must be present in the dataframe in order to pull.

Parameters: init (bool, default=False) – If True, the data and its instruction set are queued by __init__ to be processed. False will join the output immediately with the dataframe.
Return type: Optional[tuple[pd.DataFrame, tuple[str, str], str]]

triple_shift(init: bool = False) → Optional[str]

Adds columns checking if triple shift has been charged on agremeent line equipment.

The rateTypeDescription, netRate, fullWeeks, noOfOnhireDays, lineAmount, and shifts columns must be present in the dataframe in order to create.

Parameters: init (bool, default=False) – If True, None is outputted as it directly modifies Output.df and does not need to be processed as a pull_method. False will not return anything for the same reason.
Return type: Optional[str]

class lib.remote.SheetStructure(sheet: str, cell: str)

Bases: object

Struct detailing the location of the output of a report to Excel.

sheet

The name of the Excel sheet to print the output to.

Type: str

cell

The cell location (e.g. A1) to print the output to.

Type: str

__init__(sheet: str, cell: str) → None

class lib.remote.Super(report: lib.reports.Report)

Bases: lib.remote.Construct

Overrider for Construct to make an easy way to Construct off a given Report config.

__init__(report: lib.reports.Report)

Initializes Construct with the prepared arguments of a Report.

Parameters: report (rep.Report) – A Report configuration.

report

A Report configuration.

Type: rep.Report

class lib.remote._Parameters

Bases: object

Subclass for Construct parameters, verifies the string pattern.

operators

Pattern for allowed operators that map to their SQL equivalents.

Type: str

date_pt

Allowed date format (YYYY-MM) as ade works by close date.

Type: re.Pattern

str_pt

General string pattern for “names” as <region> and <area>.

Type: re.Pattern

int_pt

Integers are passed through initially as strings, this verifies they are actual ints.

Type: re.Pattern

code_pt

General purpose pattern that includes any word character.

Type: re.Pattern

cost_center_pt

BCs have a specific pattern that this verifies for.

Type: re.Pattern

dept_pt

Departments have a specific pattern that this verifies for.

Type: re.Pattern

division_pt

Divisions have a specific pattern that this verifies for.

Type: re.Pattern

client_pt

Not currently used as the client pattern of US00001234 is not followed well with non-NAM clients.

Type: re.Pattern

agreement_pt

Agreements have a specific pattern that this verifies for.

Type: re.Pattern

acc_category_desc_pt

Accounting category descriptions have a specific pattern that this verifies for.

Type: re.Pattern

account_pt

Accounting codes have a specific pattern that this verifies for.

Type: re.Pattern

account_group_pt

Accounting groups have a specific pattern that this verifies for.

Type: re.Pattern

account_category_pt

Accounting categories have a specific pattern that this verifies for.

Type: re.Pattern

types

Collection of parameters that _Parameters checks for along with its required pattern.

Type: dict[str, re.Pattern]

_verify(arguments: dict) → None

Verifies each parameter value against its pattern and sets the attribute.

Parameters: arguments (dict) – The parameters to verify.

\*attributes

Set dynamically with setattr, an attribute for each parameter initialized.

Type: Any

Raises: AssertionError – The specified column/value is not a valid entry.

to_dict() → dict

Converts all of the attributes into a key: value dictionary.

Returns: Parameters values.
Return type: dict

to_list() → list

Converts all of the attributes into a (key, value) nested list.

Returns: Parameters values.
Return type: list

class lib.remote._Query

Bases: object

Subclass for Construct query, prepares the SQL statement.

matches

Parameters whose names do not match the SQL column; the value is the match.

Type: dict[str, Union[str, list[tuple[Union[str, re.Pattern]]]]]

not_where

The parameters that are not to be constructed into WHERE statements.

Type: list[str]

_create(args: lib.remote._Parameters) → None

The main work in _Query. Calls the other helper functions and creates the SQL.

Parameters: args (_Parameters) – The cleaned parameters.

tables

All of the SQL tables to be accessed in order to construct the query.

Type: tuple[str]

string

The SQL query string itself to be built off of.

Type: str

columns

The ade column name and its SELECT statement equivalent

Type: dict[str, str]

joins

The table name and its JOIN statement equivalent

Type: dict[str, str]

where

The SQL column name along with both the parameter values to be injected and its corresponding WHERE statement.

Type: dict[str, list[Union[tuple[int, int], tuple[str, …]]]]

params

The full list of parameters to be injected into the SQL statement, in the correct order to match the WHERE statement.

Type: list[Union[int, str]]

schema

The SQL schema of each table being accessed.

Type: pd.DataFrame

table

The full SQL name of the main table being accessed (which includes its catalog and its modifier).

Type: str

prefix

The alias of <table> to be used in the SQL statement. Taken from the first 3 letters of each word as split by PascalCase (alias pascas).

Type: str

Raises: AssertionError – Whenever an error comes up in validating the SQL statement as correct. Validations are handled locally vs relying on the SQL server in order to minimize calls.

_verify_column(arg_table: str, arg_cols: Union[list, dict], arg_where: bool = False, arg_joins: Optional[dict] = None) → None

Creates the SQL select statements based off _Parameters.columns.

Also verifies if columns being selected through a JOIN are valid, as well as the columns being used in the WHERE statement.

Parameters

arg_table (str) – The base table used in the SQL query. From _Parameters.table.
arg_cols (Union[list, dict]) – Can either be the list of SELECT columns from _Parameters.columns or the dictionary of WHERE columns from the full list of Parameters.to_dict() (excluding the keys of ‘table’, ‘columns’, and ‘joins’).
arg_where (bool) – The needed flag to allow for arg_cols to be parsed as the parameters for the WHERE statement instead of as SELECT columns.
arg_joins (dict) – The dictionary of JOINs from _Parameters.joins. Required for the preliminary checking of JOINs for columns in the SELECT statement that are brought in through that join. Not needed if running the verification for WHERE.

Raises

AssertionError – The column is not valid.

_verify_join(arg_joins: dict[str, list[tuple[str, str]]]) → None

Creates the SQL join statements based off of _Parameters.joins.

Parameters: arg_joins (dict[str, tuple[str, str]]) – The collection of JOINs to be used in the SQL query. Brought together through the Parameters class, consisting of the table to join (key) and the join values (tuple).
Raises: AssertionError – The JOIN statement is not valid.

_verify_where(key: str, column: str, value: Union[list, tuple], secondary: Union[None, tuple[str, str]]) → None

Creates the SQL where statements based off of _Parameters.

Run while still in the column loop of _verify_column, therefore each argument is a single value.

Parameters

key (str) – The parameter name of the dictionary for the given WHERE statement e.g. the <dates> argument from Construct is the key for the date parameter.
column (str) – The actual column name for the given parameter e.g. depending on the table, the <dates> argument’s column could be re.accDateId i.e. the actual column in the ABI DB schema.
value (Union[tuple, list]) – The actual parameter values to search for with the WHERE statement e.g. <dates> value would be (‘1917-02’, ‘1917-10’).
secondary (tuple[str, str]) – The second value fom self.matches for the corresponding date column, consisting of the strings to sub to create the second column.

lib.remote.customer_search(search: list[str], world: bool = False, glob: bool = False) → Optional[pandas.core.frame.DataFrame]

Returns a table of ade customers abiding by the search parameter.

Parameters

search (list[str]) – The list of SQL matches to search for.
world (bool, default=False) – To expand the search outside of NAM.
glob (bool, default=False) – To search wildcard SQL strings.

Returns

Will return None if <search> is empty.

Return type

Optional[pd.DataFrame]

lib.remote.days_to_pay(df: pandas.core.frame.DataFrame, inv: Optional[pandas.core.frame.DataFrame] = None) → pandas.core.frame.DataFrame

Creation of the Days To Pay series.

Parameters

df (pd.DataFrame) – Equipment Report data.
inv (pd.DataFrame, optional) – Invoice data from invoice_base

Returns

Equipment Report data with the Outstanding series.

Return type

pd.DataFrame

lib.remote.invoice_base(df: pandas.core.frame.DataFrame, cols: list) → pandas.core.frame.DataFrame

Preparation of the invoice query to further calculate DTP and Outstanding.

Parameters

df (pandas.DataFrame) – Pre-existing data.
cols (list) – Columns to pull.

Returns

Prepared invoice data.

Return type

pd.DataFrame

lib.remote.location_search(search: list[str], world: bool = False, city: bool = False, glob: bool = False) → Optional[pandas.core.frame.DataFrame]

Returns a table of ade customer locations abiding by the search parameter.

Parameters

search (list[str]) – The list of SQL matches to search for.
world (bool, default=False) – To expand the search outside of NAM.
city (bool, default=False) – If searching by city instead of street address.
glob (bool, default=False) – To search wildcard SQL strings.

Returns

Will return None if <search> is empty.

Return type

Optional[pd.DataFrame]

lib.remote.outstanding(df: pandas.core.frame.DataFrame, inv: pandas.core.frame.DataFrame = None) → pandas.core.frame.DataFrame

Creation of the Outstanding series for the Equipment Report.

Parameters

df (pd.DataFrame) – Equipment Report data.
inv (pd.DataFrame, optional) – Invoice data created by invoice_base

Returns

Equipment Report data with the Outstanding series.

Return type

pd.DataFrame

lib.remote.pull(repkey: str, subkey: str, slicers: dict = {}, comp: dict = {}, pivots: Optional[list[lib.reports.PivotArgs]] = None, include_data: bool = False) → lib.remote.Compile

Creates the query and pulls the data for the given report structure.

Parameters

report (string) – The name of the report specification as set by config.yaml
sub (string) – The name of the sub-report as set by config.yaml
slicers (dict, optional) – The keyword arguments for the query. Allows you to overwrite report specifications before running the SQL query. Date is also an allowed field to replace.
comp (dict, optional) – Additional values used for specific reports in this module. For instance, equipment_report has a value “style” which can be modified here.
pivots (list[rep.PivotArgs], optional) – The arguments to pass in order to create a PivotTable.
include_data (bool) – Saves the original dataframe in the compiled output.

Returns

If the report specification has an excel configuration or further modification to the data, it will output a dictionary with the data, excel config, and the original args. Otherwise, only the DataFrame will be returned.

Return type

Union[pd.DataFrame, Dict[str, pd.DataFrame]]

Raises

AssertionError – If there is no data present after the SQL query is run.

lib.remote.run(repkey: str, subkey: str, slicers: dict = {}, comp: dict = {}, filename: str = '', open_file: bool = False) → None

Creates the query and pulls the data for the given report structure.

Parameters

report (string) – The name of the report specification as set by config.yaml
sub (string) – The name of the sub-report as set by config.yaml
slicers (dict, optional) – The keyword arguments for the query. Allows you to overwrite report specifications before running the SQL query. Date is also an allowed field to replace.
comp (dict, optional) – Additional values used for specific reports in this module. For instance, equipment_report has a value “style” which can be modified here.
filename (str, optional) – The override for the report filename. If “”, will default.

Returns

If the report specification has an excel configuration or further modification to the data, it will output a dictionary with the data, excel config, and the original args. Otherwise, only the DataFrame will be returned.

Return type

Union[pd.DataFrame, Dict[str, pd.DataFrame]]

Raises

AssertionError – If there is no data present after the SQL query is run.

lib.reports module

class lib.reports.CompilateArgs(args: dict)

Bases: object

Loose struct to organize args for Compilate reports.

As each report has a different and unique set of optional parameters, the struct just holds a dynamic dictionary of these parameters. The class was created to fall in line with the other Args structs.

args

Type: dict

args: dict

class lib.reports.Email(to: list[str], cc: Optional[list[str]], subject: str, message: Optional[str])

Bases: object

Struct detailing the email metadata.

to

Type: list[str]

cc

Type: Optional[list[str]]

subject

Type: str

message

Type: Optional[str]

cc: Optional[list[str]]

message: Optional[str]

subject: str

to: list[str]

class lib.reports.FunctionArgs(pull: list[str], post: list[str])

Bases: object

Struct to organize the Output pull and post methods to run on the data.

pull : list[str] post : list[str]

post: list[str]

pull: list[str]

class lib.reports.OptionalArgs(joins: dict[str, list[tuple[str, str]]], clients: list[str], agreements: list[str], invoices: list[str], departments: list[str], pos: dict[str, list[str]], regions: dict[str, list[str]], accounts: dict[str, list[str]], products: dict[str, list[str]], sectors: dict[str, list[str]], other: dict[str, list[Union[int, str]]], ors: list[tuple[str, str]])

Bases: object

Struct to organize the optional arguments for Construct.

joins

Type: dict[str, tuple[str, str]]

clients

Type: list[str]

clients

Type: list[str]

agreements

Type: list[str]

invoices

Type: list[str]

departments

Type: list[str]

pos

Type: dict[str, list[str]]

regions

Type: dict[str, list[str]]

accounts

Type: dict[str, list[str]]

products

Type: dict[str, list[str]]

sectors

Type: dict[str, list[str]]

other

Type: dict[str, list[Union[int, str]]]

ors

Type: list[tuple[str, str]]

accounts: dict[str, list[str]]

agreements: list[str]

clients: list[str]

departments: list[str]

invoices: list[str]

joins: dict[str, list[tuple[str, str]]]

ors: list[tuple[str, str]]

other: dict[str, list[Union[int, str]]]

pos: dict[str, list[str]]

products: dict[str, list[str]]

regions: dict[str, list[str]]

sectors: dict[str, list[str]]

class lib.reports.Parent(name: str, key: str, ext: str, description: str)

Bases: object

Struct to organize the parent details of a Report.

name

The Human Name of the report.

Type: str

key

They report_key used by ade to distinguish the report.

Type: str

ext

The suffix for the report to use when saved.

Type: str

description

Additional details for the report.

Type: str

description: str

ext: str

key: str

name: str

class lib.reports.PivotArgs(name: str, subs: list[str], index: list[str], columns: list[str], values: list[str], aggfunc: dict[str, str], margins: bool, query: str)

Bases: object

Struct detailing the pieces needed to create a pandas pivot table.

This struct is for adhoc pivot tables, unrelated to any potential pivot table arguments for a Compilate report (e.g. equipment report).

name

Type: str

subs

Type: list[str]

index

Type: list[str]

columns

Type: list[str]

values

Type: list[str]

aggfunc

The function used to aggregate the values. Key=column, value=function e.g. {“usdRevenue”: “sum”}.

Type: dict[str, str]

margins

Type: bool

aggfunc: dict[str, str]

columns: list[str]

index: list[str]

margins: bool

name: str

query: str

subs: list[str]

values: list[str]

class lib.reports.Report(parent: lib.reports.Parent, name: str, key: str, filename: str, email: Optional[lib.reports.Email], required: lib.reports.RequiredArgs, optional: lib.reports.OptionalArgs, function: lib.reports.FunctionArgs, pivot: Optional[list[lib.reports.PivotArgs]], compilate: lib.reports.CompilateArgs)

Bases: object

Struct to organize all Report details.

parent

The Parent this Report is under.

Type: Parent

name

The Human Name of the report.

Type: str

key

They report_key used by ade to distinguish the report.

Type: str

filename

The additional string to use for the filename. This string is used for the first half of the name e.g. if Parent.name=”equipment_report” and Parent.ext=”.xlsx” and Report.filename=”adhoc”, the full filename as saved on disk would be “adhoc_equipment_report.xlsx”.

Type: str

email

Type: Optional[Email]

required

Type: RequiredArgs

optional

Type: OptionalArgs

function

Type: FunctionalArgs

pivot

Type: list[PivotArgs]

compilate

Type: CompilateArgs

compilate: lib.reports.CompilateArgs

email: Optional[lib.reports.Email]

filename: str

function: lib.reports.FunctionArgs

key: str

name: str

optional: lib.reports.OptionalArgs

parent: lib.reports.Parent

pivot: Optional[list[lib.reports.PivotArgs]]

required: lib.reports.RequiredArgs

class lib.reports.RequiredArgs(table: str, columns: list[str], date: tuple[str, str])

Bases: object

Struct to organize the required arguments for Construct.

table

Type: str

columns

Type: list[str]

date

Type: tuple[str, str]

columns: list[str]

date: tuple[str, str]

table: str

lib.reports.counter_subset(subset: list, total: list) → bool

Compares 2 lists, checks if all contents of the first are in the second.

Parameters

subset (list) – List consisting of items to check against the second list.
total (list) – List where all items in <subset> should be present.

Return type

bool

lib.reports.fill_unused_optional_args(optional: dict) → dict

Fills unused optional args with blank collections.

The legacy code prior to using structs relies on having a collection value, even if empty. This will make sure no arguments are left as None before creating the OptionalArgs struct.

Parameters: optional (dict) – Optional arguments prior to being created as a OptionalArgs.
Returns: All non-existing arguments have been replaced with their correct empty collection.
Return type: dict

lib.reports.get_existing_reports() → list[lib.reports.Report]

Pulls all existing entries in reports.yaml as Reports.

To quicken the wait, will check if the report cache is up-to-date, deserializing the info instead of loading the unparsed yaml. If out-of-date, loads the yaml and updates the cache.

Return type: list[Report]

lib.reports.parse(report_key: str, sub_key: str) → lib.reports.Report

Overall manager for parsing a report configuration.

Parameters

report_key (str) – Report key from reports.yaml
sub_key (str) – Sub-report key from reports.yaml

Return type

Report

lib.reports.parse_params(report: dict, sub: dict, cur: list, nxt: list, tdy: list, today: datetime.date) → tuple[lib.reports.RequiredArgs, lib.reports.OptionalArgs]

Parses the query parameters of a saved report.

Parameters

report (dict) – Report parameters from reports.yaml.
sub (dict) – Sub-report parameters from reports.yaml.
cur (list) – Collection of output from dates.close().
nxt (list) – Collection of output from dates.calendar() for the following close after <cur>.
tdy (list) – Full output of dates.calendar().
today (dt.date) – Today’s date.

Return type

tuple[RequiredArgs, OptionalArgs]

lib.reports.replace(key: str, cur: list, nxt: list, tdy: list, today: datetime.date) → str

Replaces date keywords in a given string with their actual value.

Parameters

key (str) – String consisting of keywords needing to be replaced.
cur (list) – Collection of output from dates.close().
nxt (list) – Collection of output from dates.calendar() for the following close after <cur>.
today (dt.date) – Today’s date.

Returns

key – String with keywords replaced.

Return type

str

Raises

ValueError – If more than one operator exists in the keyword.

lib.util.cleanup module

lib.util.cleanup.location(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up the SQL output for Agreement locations.

Parameters

df (pd.DataFrame) –

The frame must contain the following 3 columns:: SiteAddress1 SiteAddress3 SiteAddress4

Return type

pd.DataFrame

lib.util.cleanup.na(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up N/A values for compatibility with Excel.

Parameters: df (pandas.DataFrame) –
Return type: pd.DataFrame

lib.util.cleanup.opportunity(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up the SQL output for Opportunity types.

Parameters

df (pandas.DataFrame) –

The frame must contain the following 2 columns:: opportunityType opportunityTypeDesc

Returns

Additional overallType column in the DataFrame.

Return type

pd.DataFrame

lib.util.cleanup.parent(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up the SQL output for Parent by checking for agreements that need to change hierarchy.

Parameters

df (pd.DataFrame) –
cols (list[str]) – The column names to clean up.

Return type

pd.DataFrame

lib.util.cleanup.po_expiry(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up the SQL output for po_expiry by setting blank dates to the agreement’s validToDate.

Parameters

df (pd.DataFrame) –
cols (list[str]) – The column names to clean up.

Return type

pd.DataFrame

lib.util.cleanup.product(df: pandas.core.frame.DataFrame, combine_other: bool = False) → pandas.core.frame.DataFrame

Cleans up the SQL output for the ProductFamily column from the Revenue table.

The product families Other, Unknown, and Not Found are all under the “Other” label for simplification of the data output.

Parameters

df (pd.DataFrame) – Frame origination from a SQL query that includes the CorporateODS.rev.Revenue.ProductFamily column.
combine_other (bool, default=False) – Flag to choose whether or not to combine the Other, Unknown, and Not Found product families under one “Other” umbrella.

Returns

Cleaned frame in the ‘ProductFamily’ column.

Return type

pd.DataFrame

lib.util.cleanup.site(df: pandas.core.frame.DataFrame) → pandas.core.frame.DataFrame

Cleans up the SQL output for Agreement Site with both regex and a lookup off of a master list of Sites originally used for the Axiall Equipment Report.

Parameters

df (pd.DataFrame) – The frame must contain the SiteAddress1 series.
pickle (bool) – If True, merges the original Axiall site pickle to the data.

Return type

pd.DataFrame

lib.util.cleanup.timedelta(df: pandas.core.frame.DataFrame, cols: list[str]) → pandas.core.frame.DataFrame

Cleans up the SQL output for timedeltas types to be used in Excel.

Parameters

df (pd.DataFrame) –
cols (list[str]) – The column names to clean up.

Return type

pd.DataFrame

lib.util.data module

lib.util.data.convert_decimal(df: Union[pandas.core.frame.DataFrame, pandas.core.series.Series], cols: Optional[list[str]] = None) → Union[pandas.core.frame.DataFrame, pandas.core.series.Series]

Converts Pandas float to Decimal to keep currency accurate.

Parameters

df (Union[pd.DataFrame, pd.Series]) – DataFrame to convert.
cols (list[str], optional) – List of column names to convert to Decimal.

Return type

Union[pd.DataFrame, pd.Series]

lib.util.data.convert_pascal(df: pandas.core.frame.DataFrame, cols: dict[str, str]) → pandas.core.frame.DataFrame

Converts columns of PascalText to camelText.

Parameters

df (Union[pd.DataFrame, pd.Series]) – DataFrame to convert.
cols (dict[str, str]) – Collection of column names to convert.

Return type

pd.DataFrame

lib.util.data.remove_column_duplicates(df: pandas.core.frame.DataFrame, reverse: bool = False) → pandas.core.frame.DataFrame

Clean up duplicate DF values (_x, _y columns).

Parameters

df (pandas.DataFrame) – The frame must contain duplicate values.
reverse (bool, default=False) – If True, deletes the first duplicate (_x) instead of the second.

Return type

pd.DataFrame

lib.util.data.subtotal(df: pandas.core.frame.DataFrame, subs: list[str], values: list[str], aggfunc: Union[str, dict[str, str]], index: Optional[list[str]] = None, columns: Optional[list[str]] = None, margins: bool = False) → pandas.core.frame.DataFrame

Create subtotals by a column for a DataFrame and pivot the data.

Parameters

df (pd.DataFrame) –
subs (list[str]) – The columns to create subtotals for. Must be listed as reversed the index e.g. index=[“parent”, “customerName”, “location”] subs=[“customerName”, “parent”].
values (list[str]) –
aggfunc (Union[str, dict[str, str]]) –
index (list[str], default=None) –
columns (list[str], default=None) –
margins (bool, default=False) –

Return type

pd.DataFrame

lib.util.sql module

lib.util.sql.mod(slicers: Optional[dict[str, Union[tuple, list[str]]]] = None, setters: Optional[dict[str, str]] = None) → tuple[str, str, list[str]]

Prepares the modify statements for SQL given the passed slicers/setters.

Parameters

slicers (dict[str, list[str]], optional) – Key/Value pairing of the column name to search in with the values to search/go against.
setters (dict[str, str], optional) – Key/Value pairing of the value to set to the column name given the slicers.

Returns

Contains the applicable SET and WHERE statements, along with the paramters to be used in the SQL query.

Return type

tuple[str, str, list[str]]

lib.util.sql.operator(key: str, text: str, multiple: bool = False) → tuple[str, str]

Checks to see if an operator exists at the start of the text for SQL.

If there does, the method will use that operator in order to create the WHERE SQL statement e.g. the text ‘>=1917-10-25’ will output a SQL statement of ‘<key> >= 1917-10-25’ with a parameter of ‘1917-10-25’.

Parameters

key (str) – The name of the column to be used in the WHERE statement.
text (str) – The value to be used in the WHERE statement.
multiple (bool, default=False) – If True, prepares the statement as if there will be multiple values to take into consideration in a loop from the main method _mod() e.g. ‘<key> >= 1917-10-25 OR <key> <= 1991-12-26’

Returns

The part of the WHERE statement attributed to the passed key and text AND one of the parameters to be passed in the SQL query.

Return type

tuple[str, str]

lib.util.sql.split_parameter_list(search_queries: list, chunk_size: int = 2100) → list

Splits the parameter list for large queries past the SQL Server limit of 2100.

Parameters

search_queries (list) – The parameter list to be checked.
single_chunks (bool, default=False) – For special cases if each chunk should be len == 1.

Return type

list