import pandas as pdimport mathfor jishu in range1 32 # 加载商品信息表和会员信息表 product_info = pdread_excelDpythonProject3商品信息商品打包xlsx member_info = pdread_excelDpythonProject3打包问题会员信息xlsx sheet_name=f会

import pandas as pd import math

for jishu in range(1, 32): # 加载商品信息表和会员信息表 product_info = pd.read_excel("D:\pythonProject3\商品信息\商品打包.xlsx") member_info = pd.read_excel("D:\pythonProject3\打包问题\会员信息.xlsx", sheet_name=f'会员信息{jishu}')

# 对商品信息表按照经纬度排序
product_info.sort_values(by=['新商品GPS经度', '新商品GPS纬度'], inplace=True)

# 初始化会员挑选到商品的数量
member_info['挑选商品数量'] = 0

# 遍历每个会员
for i in range(len(member_info)):
    member = member_info.iloc[i]

    # 获取会员的GPS经纬度
    member_latitude = member['会员GPS纬度']
    member_longitude = member['会员GPS经度']

    # 获取会员的预订商品限额
    limit = member['预订商品限额']

    # 初始化商品数量和总距离
    selected_quantity = 0
    total_distance = 0

    # 遍历每个商品
    for j in range(len(product_info)):
        product = product_info.iloc[j]

        # 获取商品的GPS经纬度和打包数量
        product_latitude = product['新商品GPS纬度']
        product_longitude = product['新商品GPS经度']
        quantity = product['打包数量']

        # 计算商品与会员之间的距离
        distance = math.sqrt(
            (member_latitude - product_latitude) ** 2 + (member_longitude - product_longitude) ** 2)

        # 判断商品与会员之间的距离是否满足要求
        if distance <= 1:
            # 判断挑选该商品是否会超过预订商品限额
            if selected_quantity + quantity <= limit:
                # 更新商品数量和总距离
                selected_quantity += quantity
                total_distance += distance
            else:
                break

    # 更新会员挑选到商品的数量
    member_info.at[i, '挑选商品数量'] = selected_quantity

# 分配剩余商品给信誉度高且未满限额的会员
remaining_quantity = product_info['打包数量'].sum() - member_info['挑选商品数量'].sum()
if remaining_quantity > 0:
    # 对会员按照信誉值从高到低排序
    member_info.sort_values(by=['信誉值'], ascending=False, inplace=True)
    
    # 遍历会员，分配剩余商品
    for i in range(len(member_info)):
        member = member_info.iloc[i]
        limit = member['预订商品限额']
        selected_quantity = member['挑选商品数量']
        
        # 判断会员是否未满限额
        if selected_quantity < limit:
            remaining_space = limit - selected_quantity
            
            # 判断剩余商品数量是否足够分配给该会员
            if remaining_quantity <= remaining_space:
                member_info.at[i, '挑选商品数量'] += remaining_quantity
                remaining_quantity = 0
                break
            else:
                member_info.at[i, '挑选商品数量'] += remaining_space
                remaining_quantity -= remaining_space

# 输出每个会员挑选到商品的数量
print(member_info['挑选商品数量'])
# member_info.to_excel("D:\pythonProject3\打包问题\会员信息 - 副本.xlsx", index=False, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例', '挑选商品数量'])
outfile = r"D:\pythonProject3\打包问题\会员信息 - 副本.xlsx"
with pd.ExcelWriter(outfile, mode='a', engine='openpyxl') as writer:
    member_info.to_excel(writer, columns=['会员编号', '会员GPS纬度', '会员GPS经度', '信誉值', '预订商品比例',
                                          '挑选商品数量'], sheet_name=f'会员信息{jishu}', index=False