import pandas as pd

# Load BIM element data
df = pd.read_csv("revit_export.csv")

# Generate QTO by category
qto = df.groupby('Category').agg({
    'Volume': 'sum',
    'Area': 'sum',
    'ElementId': 'count'
}).rename(columns={'ElementId': 'Count'})

# Calculate cost (if unit prices available)
qto['Unit_Price'] = [150, 80, 450, 200]  # $/m³
qto['Total_Cost'] = qto['Volume'] * qto['Unit_Price']

qto.to_excel("qto_report.xlsx")

import pandas as pd

def generate_qto(df, group_by='Category'):
    """
    Generate Quantity Take-Off grouped by specified column

    Args:
        df: DataFrame with BIM elements
        group_by: Column(s) to group by

    Returns:
        QTO summary DataFrame
    """
    # Define aggregations based on available columns
    agg_dict = {}

    if 'Volume' in df.columns:
        agg_dict['Volume'] = 'sum'
    if 'Area' in df.columns:
        agg_dict['Area'] = 'sum'
    if 'Length' in df.columns:
        agg_dict['Length'] = 'sum'
    if 'Count' in df.columns:
        agg_dict['Count'] = 'sum'
    else:
        agg_dict['ElementId'] = 'count'

    qto = df.groupby(group_by).agg(agg_dict)

    if 'ElementId' in agg_dict:
        qto = qto.rename(columns={'ElementId': 'Count'})

    return qto.round(2)

# Usage
qto = generate_qto(df, group_by='Category')
print(qto)

def generate_multi_level_qto(df):
    """Generate QTO grouped by multiple levels"""
    qto = df.groupby(['Level', 'Category', 'Material']).agg({
        'Volume': ['sum', 'count'],
        'Area': 'sum'
    }).round(2)

    # Flatten column names
    qto.columns = ['Volume_m3', 'Element_Count', 'Area_m2']

    # Add percentages
    qto['Volume_Pct'] = (qto['Volume_m3'] /
                          qto['Volume_m3'].sum() * 100).round(1)

    return qto.sort_values('Volume_m3', ascending=False)

# Usage
qto = generate_multi_level_qto(df)
qto.to_excel("qto_multi_level.xlsx")

def generate_qto_pivot(df, values='Volume', index='Level', columns='Category'):
    """Generate QTO as pivot table"""
    pivot = pd.pivot_table(
        df,
        values=values,
        index=index,
        columns=columns,
        aggfunc='sum',
        fill_value=0,
        margins=True,
        margins_name='TOTAL'
    ).round(2)

    return pivot

# Usage - Volume by Level and Category
qto_pivot = generate_qto_pivot(df, values='Volume')
qto_pivot.to_excel("qto_pivot.xlsx")

def calculate_cost_from_qto(qto_df, prices_df, quantity_col='Volume'):
    """
    Calculate costs by applying unit prices to quantities

    Args:
        qto_df: QTO DataFrame with quantities
        prices_df: DataFrame with Category and Unit_Price
        quantity_col: Column containing quantities
    """
    # Merge with prices
    result = qto_df.reset_index().merge(
        prices_df, on='Category', how='left'
    )

    # Calculate costs
    result['Total_Cost'] = result[quantity_col] * result['Unit_Price']
    result['Cost_Pct'] = (result['Total_Cost'] /
                          result['Total_Cost'].sum() * 100).round(1)

    # Summary
    grand_total = result['Total_Cost'].sum()
    print(f"Grand Total: ${grand_total:,.2f}")

    return result

# Unit prices database
prices = pd.DataFrame({
    'Category': ['Wall', 'Floor', 'Column', 'Beam', 'Foundation'],
    'Unit_Price': [150, 80, 450, 200, 120],  # $/m³
    'Unit': ['m³', 'm³', 'm³', 'm³', 'm³']
})

# Calculate
cost_estimate = calculate_cost_from_qto(qto, prices)
cost_estimate.to_excel("cost_estimate.xlsx", index=False)

def apply_excel_rules(df, rules_path):
    """
    Apply calculation rules defined in Excel file

    Excel format:
    | Category | Formula_Type | Factor | Unit |
    | Wall     | volume       | 1.05   | m³   |
    | Floor    | area         | 1.10   | m²   |
    """
    rules = pd.read_excel(rules_path)

    results = []
    for _, rule in rules.iterrows():
        category = rule['Category']
        formula_type = rule['Formula_Type']
        factor = rule['Factor']

        category_data = df[df['Category'] == category].copy()

        if formula_type == 'volume':
            category_data['Quantity'] = category_data['Volume'] * factor
        elif formula_type == 'area':
            category_data['Quantity'] = category_data['Area'] * factor
        elif formula_type == 'length':
            category_data['Quantity'] = category_data['Length'] * factor
        elif formula_type == 'count':
            category_data['Quantity'] = category_data.groupby('Category').ngroup() + 1

        category_data['Unit'] = rule['Unit']
        results.append(category_data)

    return pd.concat(results, ignore_index=True)

# Usage
df_with_quantities = apply_excel_rules(df, "calculation_rules.xlsx")

def process_revit_export(csv_path):
    """Process standard Revit schedule export"""
    df = pd.read_csv(csv_path)

    # Standardize column names
    column_mapping = {
        'Family and Type': 'Type',
        'Volume': 'Volume',
        'Area': 'Area',
        'Count': 'Count',
        'Level': 'Level',
        'Category': 'Category'
    }

    df = df.rename(columns={
        k: v for k, v in column_mapping.items()
        if k in df.columns
    })

    # Convert volume from cubic feet to cubic meters (if needed)
    if 'Volume' in df.columns:
        # Revit exports in cubic feet by default
        df['Volume_m3'] = df['Volume'] * 0.0283168

    return df

# Usage
df = process_revit_export("revit_schedule.csv")
qto = generate_qto(df)

# Using IfcOpenShell
import ifcopenshell
import pandas as pd

def extract_qto_from_ifc(ifc_path):
    """Extract quantities from IFC file"""
    ifc = ifcopenshell.open(ifc_path)

    elements = []
    for element in ifc.by_type("IfcBuildingElement"):
        # Get properties
        props = {
            'GlobalId': element.GlobalId,
            'Name': element.Name,
            'Type': element.is_a(),
            'Material': None,
            'Volume': None,
            'Area': None
        }

        # Extract quantities from property sets
        for definition in element.IsDefinedBy:
            if definition.is_a('IfcRelDefinesByProperties'):
                pset = definition.RelatingPropertyDefinition
                if pset.is_a('IfcElementQuantity'):
                    for qty in pset.Quantities:
                        if qty.is_a('IfcQuantityVolume'):
                            props['Volume'] = qty.VolumeValue
                        elif qty.is_a('IfcQuantityArea'):
                            props['Area'] = qty.AreaValue

        elements.append(props)

    return pd.DataFrame(elements)

# Usage
df = extract_qto_from_ifc("model.ifc")
qto = generate_qto(df, group_by='Type')

def material_breakdown_qto(df):
    """Detailed breakdown by material type"""
    breakdown = df.groupby(['Category', 'Material', 'Type']).agg({
        'Volume': 'sum',
        'Area': 'sum',
        'ElementId': 'nunique'
    }).rename(columns={'ElementId': 'Unique_Elements'})

    # Add subtotals for each category
    category_totals = df.groupby('Category').agg({
        'Volume': 'sum',
        'Area': 'sum'
    })

    breakdown['Category_Volume_Pct'] = breakdown.apply(
        lambda row: (row['Volume'] /
                    category_totals.loc[row.name[0], 'Volume'] * 100),
        axis=1
    ).round(1)

    return breakdown

# Usage
material_qto = material_breakdown_qto(df)
material_qto.to_excel("material_breakdown.xlsx")

def qto_with_waste(df, waste_factors):
    """
    Apply waste factors to quantities

    Args:
        waste_factors: dict like {'Concrete': 1.05, 'Steel': 1.03}
    """
    qto = df.groupby(['Category', 'Material']).agg({
        'Volume': 'sum'
    }).reset_index()

    # Apply waste factors
    qto['Waste_Factor'] = qto['Material'].map(waste_factors).fillna(1.0)
    qto['Net_Volume'] = qto['Volume']
    qto['Gross_Volume'] = qto['Volume'] * qto['Waste_Factor']
    qto['Waste_Volume'] = qto['Gross_Volume'] - qto['Net_Volume']

    return qto

# Usage
waste = {'Concrete': 1.05, 'Brick': 1.08, 'Steel': 1.03}
qto = qto_with_waste(df, waste)

def compare_qto(design_df, asbuilt_df, group_by='Category'):
    """Compare designed vs as-built quantities"""
    design_qto = design_df.groupby(group_by)['Volume'].sum()
    asbuilt_qto = asbuilt_df.groupby(group_by)['Volume'].sum()

    comparison = pd.DataFrame({
        'Design': design_qto,
        'AsBuilt': asbuilt_qto
    })

    comparison['Difference'] = comparison['AsBuilt'] - comparison['Design']
    comparison['Variance_%'] = (
        (comparison['AsBuilt'] - comparison['Design']) /
        comparison['Design'] * 100
    ).round(1)

    return comparison

# Usage
comparison = compare_qto(design_df, asbuilt_df)
print(comparison)

def export_qto_report(qto_df, base_name, include_charts=True):
    """Export QTO to Excel with formatting and charts"""
    from openpyxl import Workbook
    from openpyxl.chart import BarChart, Reference

    # Excel with multiple sheets
    with pd.ExcelWriter(f"{base_name}.xlsx", engine='openpyxl') as writer:
        # Summary sheet
        qto_df.to_excel(writer, sheet_name='Summary')

        # Detailed data
        if hasattr(qto_df, 'reset_index'):
            qto_df.reset_index().to_excel(
                writer, sheet_name='Details', index=False
            )

    # CSV for integration
    qto_df.to_csv(f"{base_name}.csv")

    # JSON for API
    qto_df.reset_index().to_json(
        f"{base_name}.json", orient='records', indent=2
    )

    print(f"Exported: {base_name}.xlsx, .csv, .json")

# Usage
export_qto_report(qto, "project_qto")