Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

8.9 KB
Raw Blame
//! MACRO: GETPIVOTDATA, which upstream does not have.
//!
//! GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)
//!
//! The engine has no pivot tables of its own. The host describes where each
//! one shows its values (`Model::set_pivot_tables`): for every row and every
//! column of values, the items it is for, the data field it shows and whether
//! it is a total. GETPIVOTDATA finds the row and the column for the items it
//! is given and returns the value of the cell where they cross. Like Excel, it
//! gives #REF! for anything the table does not show.

use serde::Deserialize;

use crate::{
    calc_result::CalcResult,
    expressions::{parser::Node, token::Error, types::CellReferenceIndex},
    model::Model,
};

/// A pivot table as GETPIVOTDATA reads it. Names and texts are in lower case
/// and trimmed, as `normalized` leaves them.
#[derive(Clone, Debug, Deserialize)]
pub struct PivotLayout {
    /// The index of its sheet.
    pub sheet: u32,
    /// The cells it covers, `[top, left, bottom, right]`, counted from 1.
    pub range: [i32; 4],
    /// For each data field, the names it goes by: its own, such as "sum of
    /// sales", and its source field's, "sales".
    pub data: Vec<Vec<String>>,
    /// Every field of its source, by index.
    pub fields: Vec<PivotField>,
    /// Report filters: a field, and the one item it shows if it shows one.
    #[serde(default)]
    pub filters: Vec<(usize, Option<usize>)>,
    /// The rows of values, top to bottom.
    pub rows: Vec<PivotLine>,
    /// The columns of values, left to right.
    pub columns: Vec<PivotLine>,
}

#[derive(Clone, Debug, Deserialize)]
pub struct PivotField {
    /// The names it goes by.
    pub names: Vec<String>,
    pub items: Vec<PivotItem>,
}

#[derive(Clone, Debug, Deserialize)]
pub struct PivotItem {
    /// What it shows.
    pub text: String,
    /// Its value, if it is a number or a date.
    #[serde(default)]
    pub number: Option<f64>,
}

/// A row or a column of values.
#[derive(Clone, Debug, Deserialize)]
pub struct PivotLine {
    /// Its row or column number.
    pub at: i32,
    /// The items it is for, as `(field, item)` indexes.
    pub items: Vec<(usize, usize)>,
    /// The data field it shows, when data fields are listed along it.
    #[serde(default)]
    pub data: Option<usize>,
    /// Whether it is a subtotal or a grand total.
    #[serde(default)]
    pub total: bool,
}

/// A name or a text as layouts hold them.
pub(crate) fn normalized(text: &str) -> String {
    text.trim().to_lowercase()
}

/// An item asked for.
enum Wanted {
    Text(String),
    Number(f64),
    Boolean(bool),
}

fn same_number(left: f64, right: f64) -> bool {
    left == right || (left - right).abs() <= 1e-12 * left.abs().max(right.abs())
}

impl Wanted {
    fn matches(&self, item: &PivotItem) -> bool {
        match self {
            Wanted::Number(number) => {
                item.number.is_some_and(|value| same_number(value, *number))
                    || item.text == format!("{number}")
            }
            Wanted::Text(text) => {
                item.text == *text
                    || match (item.number, text.parse::<f64>()) {
                        (Some(value), Ok(number)) => same_number(value, number),
                        _ => false,
                    }
            }
            Wanted::Boolean(value) => item.text == if *value { "true" } else { "false" },
        }
    }
}

/// The line whose items are exactly `wanted`, each field with one of the
/// item indexes listed for it. A total wins over a line of items, which
/// shows nothing when its subtotals are below it; a line of the data field
/// asked for wins over one that shows every data field.
fn find_line(lines: &[PivotLine], wanted: &[(usize, Vec<usize>)], data: usize) -> Option<i32> {
    let mut best: Option<(u8, i32)> = None;
    for line in lines {
        if line.data.is_some_and(|index| index != data) || line.items.len() != wanted.len() {
            continue;
        }
        let matched = wanted.iter().all(|(field, items)| {
            line.items
                .iter()
                .any(|(other, item)| other == field && items.contains(item))
        });
        if !matched {
            continue;
        }
        let score = 2 * u8::from(line.data == Some(data)) + u8::from(line.total);
        if best.is_none_or(|(best_score, _)| score > best_score) {
            best = Some((score, line.at));
        }
    }
    best.map(|(_, at)| at)
}

impl PivotLayout {
    fn contains(&self, cell: CellReferenceIndex) -> bool {
        let [top, left, bottom, right] = self.range;
        cell.sheet == self.sheet
            && (top..=bottom).contains(&cell.row)
            && (left..=right).contains(&cell.column)
    }

    /// The cell showing `data_field` for the items asked for, if the table
    /// shows it.
    fn find(&self, data_field: &str, wanted: &[(String, Wanted)]) -> Option<(i32, i32)> {
        let data = self
            .data
            .iter()
            .position(|names| names.iter().any(|name| name == data_field))?;
        let on = |lines: &[PivotLine], field: usize| {
            lines
                .iter()
                .any(|line| line.items.iter().any(|(other, _)| *other == field))
        };
        let mut rows = Vec::new();
        let mut columns = Vec::new();
        for (name, item) in wanted {
            let field = self
                .fields
                .iter()
                .position(|field| field.names.iter().any(|other| other == name))?;
            let items: Vec<usize> = self.fields[field]
                .items
                .iter()
                .enumerate()
                .filter(|(_, candidate)| item.matches(candidate))
                .map(|(index, _)| index)
                .collect();
            if items.is_empty() {
                return None;
            }
            if on(&self.rows, field) {
                rows.push((field, items));
            } else if on(&self.columns, field) {
                columns.push((field, items));
            } else {
                // A report filter only shows the one item it is set to.
                let (_, shown) = self.filters.iter().find(|(other, _)| *other == field)?;
                if !shown.is_some_and(|shown| items.contains(&shown)) {
                    return None;
                }
            }
        }
        let row = find_line(&self.rows, &rows, data)?;
        let column = find_line(&self.columns, &columns, data)?;
        Some((row, column))
    }
}

impl Model<'_> {
    /// MACRO: describes the workbook's pivot tables for GETPIVOTDATA.
    pub fn set_pivot_tables(&mut self, pivot_tables: Vec<PivotLayout>) {
        self.pivot_tables = pivot_tables;
    }

    pub(crate) fn fn_getpivotdata(
        &mut self,
        args: &[Node],
        cell: CellReferenceIndex,
    ) -> CalcResult {
        if args.len() < 2 || !args.len().is_multiple_of(2) {
            return CalcResult::new_args_number_error(cell);
        }
        let data_field = match self.get_string(&args[0], cell) {
            Ok(name) => normalized(&name),
            Err(error) => return error,
        };
        let reference = match self.get_reference(&args[1], cell) {
            Ok(range) => range.left,
            Err(error) => return error,
        };
        let mut wanted = Vec::new();
        for pair in args[2..].chunks(2) {
            let field = match self.get_string(&pair[0], cell) {
                Ok(name) => normalized(&name),
                Err(error) => return error,
            };
            let value = self.evaluate_node_in_context(&pair[1], cell);
            let item = match self.dereference(value, cell) {
                CalcResult::Number(number) => Wanted::Number(number),
                CalcResult::String(text) => Wanted::Text(normalized(&text)),
                CalcResult::Boolean(value) => Wanted::Boolean(value),
                CalcResult::EmptyCell | CalcResult::EmptyArg => Wanted::Text(String::new()),
                error @ CalcResult::Error { .. } => return error,
                _ => return CalcResult::new_error(Error::VALUE, cell, "Invalid item".to_string()),
            };
            wanted.push((field, item));
        }
        let not_shown = || {
            CalcResult::new_error(
                Error::REF,
                cell,
                "The pivot table does not show this value".to_string(),
            )
        };
        let Some(pivot) = self
            .pivot_tables
            .iter()
            .find(|pivot| pivot.contains(reference))
        else {
            return CalcResult::new_error(Error::REF, cell, "Not a pivot table".to_string());
        };
        let Some((row, column)) = pivot.find(&data_field, &wanted) else {
            return not_shown();
        };
        match self.evaluate_cell(CellReferenceIndex {
            sheet: reference.sheet,
            row,
            column,
        }) {
            CalcResult::EmptyCell => not_shown(),
            value => value,
        }
    }
}