unit-price-database-manager

Unit Price Database Manager for Construction

Overview

Manage and maintain construction unit price databases. Update prices from vendors, apply location and time adjustments, track price history, and ensure estimating accuracy.

Business Case

Accurate unit prices are critical for:

• Competitive Bids: Win work with accurate pricing
• Cost Control: Avoid budget surprises
• Vendor Management: Track supplier pricing
• Historical Analysis: Understand price trends

Technical Implementation

from dataclasses import dataclass, field
from typing import List, Dict, Any, Optional
from datetime import datetime, date
from decimal import Decimal
import pandas as pd
import json

@dataclass
class UnitPrice:
    code: str
    description: str
    unit: str
    base_price: Decimal
    labor_cost: Decimal
    material_cost: Decimal
    equipment_cost: Decimal
    effective_date: date
    expiration_date: Optional[date] = None
    source: str = ""
    vendor: str = ""
    location: str = "National Average"
    notes: str = ""
    tags: List[str] = field(default_factory=list)

@dataclass
class PriceUpdate:
    code: str
    old_price: Decimal
    new_price: Decimal
    change_pct: float
    updated_at: datetime
    updated_by: str
    reason: str

@dataclass
class VendorQuote:
    vendor_name: str
    item_code: str
    quoted_price: Decimal
    quote_date: date
    valid_until: date
    quantity_break: Optional[int] = None
    notes: str = ""

class UnitPriceDatabaseManager:
    """Manage construction unit price databases."""

    # Location adjustment factors
    LOCATION_FACTORS = {
        'New York': 1.32, 'San Francisco': 1.28, 'Los Angeles': 1.15,
        'Chicago': 1.12, 'Boston': 1.18, 'Seattle': 1.08,
        'Denver': 1.02, 'National Average': 1.00,
        'Houston': 0.92, 'Dallas': 0.89, 'Phoenix': 0.93,
        'Atlanta': 0.91, 'Miami': 0.95
    }

    def __init__(self, db_path: str = None):
        self.prices: Dict[str, UnitPrice] = {}
        self.price_history: Dict[str, List[UnitPrice]] = {}
        self.vendor_quotes: Dict[str, List[VendorQuote]] = {}
        self.updates: List[PriceUpdate] = []
        self.db_path = db_path

    def add_price(self, price: UnitPrice) -> str:
        """Add or update a unit price."""
        code = price.code

        # Track history
        if code in self.prices:
            if code not in self.price_history:
                self.price_history[code] = []
            self.price_history[code].append(self.prices[code])

            # Record update
            old_price = self.prices[code].base_price
            if old_price != price.base_price:
                change_pct = float((price.base_price - old_price) / old_price * 100)
                self.updates.append(PriceUpdate(
                    code=code,
                    old_price=old_price,
                    new_price=price.base_price,
                    change_pct=change_pct,
                    updated_at=datetime.now(),
                    updated_by="system",
                    reason="Price update"
                ))

        self.prices[code] = price
        return code

    def get_price(self, code: str, location: str = None,
                  as_of_date: date = None) -> Optional[UnitPrice]:
        """Get unit price with optional location adjustment."""
        if code not in self.prices:
            return None

        price = self.prices[code]

        # Check date validity
        if as_of_date:
            if price.effective_date > as_of_date:
                # Look in history
                if code in self.price_history:
                    for hist_price in reversed(self.price_history[code]):
                        if hist_price.effective_date <= as_of_date:
                            if hist_price.expiration_date is None or hist_price.expiration_date >= as_of_date:
                                price = hist_price
                                break

            if price.expiration_date and price.expiration_date < as_of_date:
                return None

        # Apply location factor
        if location and location != price.location:
            adjusted = UnitPrice(
                code=price.code,
                description=price.description,
                unit=price.unit,
                base_price=self._apply_location_factor(price.base_price, price.location, location),
                labor_cost=self._apply_location_factor(price.labor_cost, price.location, location),
                material_cost=price.material_cost,  # Materials less location-sensitive
                equipment_cost=self._apply_location_factor(price.equipment_cost, price.location, location),
                effective_date=price.effective_date,
                expiration_date=price.expiration_date,
                source=price.source,
                vendor=price.vendor,
                location=location,
                notes=f"Adjusted from {price.location}",
                tags=price.tags
            )
            return adjusted

        return price

    def _apply_location_factor(self, amount: Decimal, from_loc: str, to_loc: str) -> Decimal:
        """Apply location adjustment factor."""
        from_factor = self.LOCATION_FACTORS.get(from_loc, 1.0)
        to_factor = self.LOCATION_FACTORS.get(to_loc, 1.0)
        return Decimal(str(float(amount) * to_factor / from_factor))

    def apply_escalation(self, percentage: float, categories: List[str] = None,
                         effective_date: date = None) -> int:
        """Apply escalation to prices."""
        if effective_date is None:
            effective_date = date.today()

        count = 0
        factor = Decimal(str(1 + percentage / 100))

        for code, price in self.prices.items():
            if categories and not any(tag in price.tags for tag in categories):
                continue

            old_price = price.base_price
            new_price = UnitPrice(
                code=price.code,
                description=price.description,
                unit=price.unit,
                base_price=price.base_price * factor,
                labor_cost=price.labor_cost * factor,
                material_cost=price.material_cost * factor,
                equipment_cost=price.equipment_cost * factor,
                effective_date=effective_date,
                source=f"Escalated {percentage}% from {price.source}",
                vendor=price.vendor,
                location=price.location,
                tags=price.tags
            )

            self.add_price(new_price)
            count += 1

        return count

    def add_vendor_quote(self, quote: VendorQuote):
        """Add a vendor quote."""
        code = quote.item_code
        if code not in self.vendor_quotes:
            self.vendor_quotes[code] = []
        self.vendor_quotes[code].append(quote)

    def get_best_price(self, code: str, quantity: int = 1) -> Optional[Dict]:
        """Get best available price from vendors."""
        if code not in self.vendor_quotes:
            return None

        valid_quotes = []
        today = date.today()

        for quote in self.vendor_quotes[code]:
            if quote.valid_until >= today:
                if quote.quantity_break is None or quantity >= quote.quantity_break:
                    valid_quotes.append(quote)

        if not valid_quotes:
            return None

        best = min(valid_quotes, key=lambda q: q.quoted_price)

        return {
            'vendor': best.vendor_name,
            'price': best.quoted_price,
            'valid_until': best.valid_until,
            'all_quotes': [
                {'vendor': q.vendor_name, 'price': q.quoted_price}
                for q in sorted(valid_quotes, key=lambda x: x.quoted_price)
            ]
        }

    def search_prices(self, query: str = None, category: str = None,
                       min_price: float = None, max_price: float = None) -> List[UnitPrice]:
        """Search prices by various criteria."""
        results = []

        for code, price in self.prices.items():
            # Text search
            if query:
                query_lower = query.lower()
                if (query_lower not in code.lower() and
                    query_lower not in price.description.lower()):
                    continue

            # Category filter
            if category and category not in price.tags:
                continue

            # Price range
            if min_price and float(price.base_price) < min_price:
                continue
            if max_price and float(price.base_price) > max_price:
                continue

            results.append(price)

        return results

    def get_price_history(self, code: str) -> List[Dict]:
        """Get price history for an item."""
        history = []

        if code in self.price_history:
            for price in self.price_history[code]:
                history.append({
                    'date': price.effective_date,
                    'price': float(price.base_price),
                    'source': price.source
                })

        if code in self.prices:
            history.append({
                'date': self.prices[code].effective_date,
                'price': float(self.prices[code].base_price),
                'source': self.prices[code].source
            })

        return sorted(history, key=lambda x: x['date'])

    def analyze_price_trends(self, code: str) -> Dict:
        """Analyze price trends for an item."""
        history = self.get_price_history(code)

        if len(history) < 2:
            return {'trend': 'insufficient_data'}

        prices = [h['price'] for h in history]
        dates = [h['date'] for h in history]

        # Calculate changes
        first_price = prices[0]
        last_price = prices[-1]
        total_change = (last_price - first_price) / first_price * 100

        # Calculate annualized rate
        days = (dates[-1] - dates[0]).days
        years = days / 365.25
        if years > 0:
            annual_rate = ((last_price / first_price) ** (1 / years) - 1) * 100
        else:
            annual_rate = 0

        return {
            'code': code,
            'first_price': first_price,
            'last_price': last_price,
            'total_change_pct': total_change,
            'annual_rate_pct': annual_rate,
            'data_points': len(history),
            'period_years': years,
            'trend': 'increasing' if total_change > 5 else 'decreasing' if total_change < -5 else 'stable'
        }

    def import_from_csv(self, file_path: str) -> int:
        """Import prices from CSV file."""
        df = pd.read_csv(file_path)
        count = 0

        for _, row in df.iterrows():
            price = UnitPrice(
                code=row['code'],
                description=row['description'],
                unit=row['unit'],
                base_price=Decimal(str(row['base_price'])),
                labor_cost=Decimal(str(row.get('labor_cost', 0))),
                material_cost=Decimal(str(row.get('material_cost', 0))),
                equipment_cost=Decimal(str(row.get('equipment_cost', 0))),
                effective_date=date.today() if 'effective_date' not in row else pd.to_datetime(row['effective_date']).date(),
                source=row.get('source', 'CSV Import'),
                tags=row.get('tags', '').split(',') if 'tags' in row else []
            )
            self.add_price(price)
            count += 1

        return count

    def export_to_csv(self, file_path: str, location: str = None) -> int:
        """Export prices to CSV file."""
        data = []

        for code, price in self.prices.items():
            if location:
                price = self.get_price(code, location)

            data.append({
                'code': price.code,
                'description': price.description,
                'unit': price.unit,
                'base_price': float(price.base_price),
                'labor_cost': float(price.labor_cost),
                'material_cost': float(price.material_cost),
                'equipment_cost': float(price.equipment_cost),
                'location': price.location,
                'effective_date': price.effective_date.isoformat(),
                'source': price.source,
                'tags': ','.join(price.tags)
            })

        df = pd.DataFrame(data)
        df.to_csv(file_path, index=False)
        return len(data)

    def validate_prices(self) -> List[Dict]:
        """Validate prices for issues."""
        issues = []

        for code, price in self.prices.items():
            # Check for expired prices
            if price.expiration_date and price.expiration_date < date.today():
                issues.append({
                    'code': code,
                    'issue': 'expired',
                    'message': f"Price expired on {price.expiration_date}"
                })

            # Check for old prices
            age_days = (date.today() - price.effective_date).days
            if age_days > 365:
                issues.append({
                    'code': code,
                    'issue': 'stale',
                    'message': f"Price is {age_days} days old"
                })

            # Check for zero prices
            if price.base_price <= 0:
                issues.append({
                    'code': code,
                    'issue': 'invalid',
                    'message': "Zero or negative price"
                })

            # Check component breakdown
            total_components = price.labor_cost + price.material_cost + price.equipment_cost
            if total_components > 0 and abs(float(price.base_price - total_components)) > 0.01:
                issues.append({
                    'code': code,
                    'issue': 'mismatch',
                    'message': f"Component costs don't match total: {total_components} vs {price.base_price}"
                })

        return issues

    def generate_report(self) -> str:
        """Generate database status report."""
        lines = ["# Unit Price Database Report", ""]
        lines.append(f"**Generated:** {datetime.now().strftime('%Y-%m-%d %H:%M')}")
        lines.append(f"**Total Items:** {len(self.prices):,}")
        lines.append("")

        # Category breakdown
        categories = {}
        for price in self.prices.values():
            for tag in price.tags:
                categories[tag] = categories.get(tag, 0) + 1

        if categories:
            lines.append("## Items by Category")
            for cat, count in sorted(categories.items(), key=lambda x: -x[1]):
                lines.append(f"- {cat}: {count}")
            lines.append("")

        # Recent updates
        recent_updates = sorted(self.updates, key=lambda x: x.updated_at, reverse=True)[:10]
        if recent_updates:
            lines.append("## Recent Updates")
            for update in recent_updates:
                lines.append(f"- {update.code}: {update.change_pct:+.1f}% on {update.updated_at.strftime('%Y-%m-%d')}")
            lines.append("")

        # Validation issues
        issues = self.validate_prices()
        if issues:
            lines.append("## Validation Issues")
            lines.append(f"Total issues: {len(issues)}")
            for issue in issues[:10]:
                lines.append(f"- {issue['code']}: {issue['message']}")

        return "\n".join(lines)