auto-estimate-generator

What this skill does

# Auto Estimate Generator

## Business Case

### Problem Statement
Manual estimate creation challenges:
- Time-consuming quantity mapping
- Inconsistent pricing rules
- Errors in calculations
- Difficulty updating estimates

### Solution
Automated estimate generation from BIM/QTO data using configurable pricing rules and assembly mappings.

## Technical Implementation

```python
import pandas as pd
from typing import Dict, Any, List, Optional, Callable
from dataclasses import dataclass, field
from enum import Enum


class ElementType(Enum):
    WALL = "wall"
    FLOOR = "floor"
    CEILING = "ceiling"
    DOOR = "door"
    WINDOW = "window"
    COLUMN = "column"
    BEAM = "beam"
    FOUNDATION = "foundation"
    ROOF = "roof"
    STAIR = "stair"
    MEP = "mep"


@dataclass
class QTOItem:
    element_id: str
    element_type: ElementType
    name: str
    quantity: float
    unit: str
    properties: Dict[str, Any] = field(default_factory=dict)


@dataclass
class PricingRule:
    rule_id: str
    name: str
    element_type: ElementType
    conditions: Dict[str, Any] = field(default_factory=dict)
    unit_cost: float = 0
    assembly_code: str = ""
    cost_breakdown: Dict[str, float] = field(default_factory=dict)


@dataclass
class EstimateItem:
    qto_element_id: str
    description: str
    quantity: float
    unit: str
    unit_cost: float
    total_cost: float
    rule_applied: str
    wbs_code: str = ""


class AutoEstimateGenerator:
    """Generate estimates from QTO data automatically."""

    def __init__(self, project_name: str):
        self.project_name = project_name
        self.pricing_rules: List[PricingRule] = []
        self.qto_items: List[QTOItem] = []
        self.estimate_items: List[EstimateItem] = []
        self.unmapped_items: List[QTOItem] = []

    def add_pricing_rule(self, rule: PricingRule):
        """Add pricing rule."""
        self.pricing_rules.append(rule)

    def load_pricing_rules_from_df(self, df: pd.DataFrame):
        """Load pricing rules from DataFrame."""

        for _, row in df.iterrows():
            conditions = {}
            if 'material' in row:
                conditions['material'] = row['material']
            if 'thickness_min' in row:
                conditions['thickness_min'] = row['thickness_min']
            if 'thickness_max' in row:
                conditions['thickness_max'] = row['thickness_max']

            rule = PricingRule(
                rule_id=row['rule_id'],
                name=row['name'],
                element_type=ElementType(row['element_type'].lower()),
                conditions=conditions,
                unit_cost=float(row['unit_cost']),
                assembly_code=row.get('assembly_code', ''),
                cost_breakdown={
                    'labor': float(row.get('labor_pct', 0.4)),
                    'material': float(row.get('material_pct', 0.5)),
                    'equipment': float(row.get('equipment_pct', 0.1))
                }
            )
            self.add_pricing_rule(rule)

    def load_qto_from_df(self, df: pd.DataFrame):
        """Load QTO items from DataFrame."""

        for _, row in df.iterrows():
            properties = {}
            for col in df.columns:
                if col not in ['element_id', 'element_type', 'name', 'quantity', 'unit']:
                    properties[col] = row[col]

            qto = QTOItem(
                element_id=str(row['element_id']),
                element_type=ElementType(row['element_type'].lower()),
                name=row['name'],
                quantity=float(row['quantity']),
                unit=row['unit'],
                properties=properties
            )
            self.qto_items.append(qto)

    def find_matching_rule(self, qto_item: QTOItem) -> Optional[PricingRule]:
        """Find pricing rule that matches QTO item."""

        matching_rules = []

        for rule in self.pricing_rules:
            if rule.element_type != qto_item.element_type:
                continue

            # Check conditions
            match = True
            for key, value in rule.conditions.items():
                if key.endswith('_min'):
                    prop_name = key[:-4]
                    if prop_name in qto_item.properties:
                        if qto_item.properties[prop_name] < value:
                            match = False
                elif key.endswith('_max'):
                    prop_name = key[:-4]
                    if prop_name in qto_item.properties:
                        if qto_item.properties[prop_name] > value:
                            match = False
                else:
                    if key in qto_item.properties:
                        if qto_item.properties[key] != value:
                            match = False

            if match:
                matching_rules.append(rule)

        # Return most specific rule (most conditions)
        if matching_rules:
            return max(matching_rules, key=lambda r: len(r.conditions))
        return None

    def generate_estimate(self) -> Dict[str, Any]:
        """Generate estimate from QTO items."""

        self.estimate_items = []
        self.unmapped_items = []
        total_cost = 0

        for qto in self.qto_items:
            rule = self.find_matching_rule(qto)

            if rule:
                item_cost = qto.quantity * rule.unit_cost

                self.estimate_items.append(EstimateItem(
                    qto_element_id=qto.element_id,
                    description=f"{qto.name} ({rule.name})",
                    quantity=qto.quantity,
                    unit=qto.unit,
                    unit_cost=rule.unit_cost,
                    total_cost=round(item_cost, 2),
                    rule_applied=rule.rule_id,
                    wbs_code=rule.assembly_code
                ))
                total_cost += item_cost
            else:
                self.unmapped_items.append(qto)

        return {
            'project': self.project_name,
            'total_qto_items': len(self.qto_items),
            'mapped_items': len(self.estimate_items),
            'unmapped_items': len(self.unmapped_items),
            'mapping_rate': round(len(self.estimate_items) / len(self.qto_items) * 100, 1) if self.qto_items else 0,
            'total_cost': round(total_cost, 2),
            'items': self.estimate_items
        }

    def get_cost_by_element_type(self) -> Dict[str, float]:
        """Get cost breakdown by element type."""

        by_type = {}
        for qto in self.qto_items:
            for est_item in self.estimate_items:
                if est_item.qto_element_id == qto.element_id:
                    type_name = qto.element_type.value
                    by_type[type_name] = by_type.get(type_name, 0) + est_item.total_cost

        return {k: round(v, 2) for k, v in by_type.items()}

    def get_unmapped_summary(self) -> pd.DataFrame:
        """Get summary of unmapped items."""

        if not self.unmapped_items:
            return pd.DataFrame()

        data = []
        for item in self.unmapped_items:
            data.append({
                'Element ID': item.element_id,
                'Type': item.element_type.value,
                'Name': item.name,
                'Quantity': item.quantity,
                'Unit': item.unit,
                'Properties': str(item.properties)
            })

        return pd.DataFrame(data)

    def export_to_excel(self, output_path: str) -> str:
        """Export estimate to Excel."""

        result = self.generate_estimate()

        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # Summary
            summary_df = pd.DataFrame([{
                'Project': self.project_name,
                'Total QTO Items': result['total_qto_items'],
                'Mapped Items': result['mapped_items'],
                'Unmapped Items': result['unmapped_items'],
                'Mapping Rate %': result['ma