%pip install binpacking

# Check the csv data
import pandas as pd

CSV_URL = 'https://raw.githubusercontent.com/inigmat/exupery/main/files/bbs.csv'
data = pd.read_csv(CSV_URL)
data.head(5)

from binpacking import to_constant_volume

# Constants
BAR_LENGTH = 11700
CSV_URL = CSV_URL


def calculate_required_bars(bar_schedule: pd.DataFrame, bar_length: int) -> tuple:
    """
    Calculates the cutting schemes based on input data (bar schedule)
    containing the length and quantity of bars and the length of the bars in stock.
    """
    items = []
    # Extracting data from DataFrame
    for length, quantity in zip(bar_schedule['Length'], bar_schedule['Qty']):
        items.extend([length] * quantity)
    # Calculating the cutting schemes using bin packing algorithm
    cutting_scheme = to_constant_volume(items, bar_length)
    return cutting_scheme


def generate_cutting_table(cutting_data: dict) -> pd.DataFrame:
    """
    Generates a dataframe (cutting chart) with quantity,
    cuts, utilization, scrap, and diameter columns
    based on the cutting data (cutting schemes).
    """
    cutting_chart = pd.DataFrame(columns=["Qty", "Cuts", "Utilization", "Scrap", "Dia"])
    # Processing cutting data for each diameter
    for dia, cutting_schemes in cutting_data.items():
        df_temp = pd.DataFrame(zip(cutting_schemes), columns=["Cuts"])
        # Calculate the quantity of each cutting scheme
        df_temp["Qty"] = df_temp["Cuts"].apply(lambda x: f"{cutting_schemes.count(x)}x")
        # Calculate the total length used by each cutting scheme
        df_temp["Utilization"] = df_temp["Cuts"].apply(sum)
        # Calculate the amount of scrap for each cutting scheme
        df_temp["Scrap"] = BAR_LENGTH - df_temp["Utilization"]
        # Add the diameter column
        df_temp["Dia"] = dia
        # Change the type of values in the Cuts column to drop duplicates
        df_temp["Cuts"] = df_temp["Cuts"].astype(str)
        # Concatenate df_temp with cutting_scheme
        cutting_chart = pd.concat([cutting_chart, df_temp], ignore_index=True)
    # Drop duplicate rows
    cutting_chart = cutting_chart.drop_duplicates()
    # Reset the index
    cutting_chart = cutting_chart.reset_index(drop=True)
    return cutting_chart


def main() -> pd.DataFrame:
    """
    Executes the main workflow to calculate cutting schemes and generate a cutting chart.
    """
    # Read the data from CSV
    data = pd.read_csv(CSV_URL)
    cutting_data = {}
    # Process data for each unique diameter
    for dia in data['Dia'].unique():
        data_dia = data[data['Dia'] == dia]
        # Calculate the cutting schemes and store them in a dictionary
        cutting_patterns = calculate_required_bars(data_dia, BAR_LENGTH)
        cutting_data[dia] = cutting_patterns
        # Print results
        print(f'Required number of reinforcement bars with diameter {dia} mm: {len(cutting_patterns)} pcs.')
        percent = ((data_dia['Qty'] * data_dia['Length']).sum()/(len(cutting_patterns) * BAR_LENGTH)) * 100
        print(f'Total scrap: {round(100 - percent, 2)}%')
    # Generate the cutting table
    cutting_chart = generate_cutting_table(cutting_data)
    return cutting_chart


if __name__ == "__main__":
    # Execute the main function and store the resulting cutting table
    cutting_chart = main()


cutting_chart

from google.colab import files

# determining the name of the file
file_name = 'cutting_chart.xlsx'

# saving the excel
cutting_chart.to_excel(file_name)

# downloading the file
files.download('cutting_chart.xlsx')
print('DataFrame is written to Excel File successfully. Save the file')