import pandas as pd
import openpyxl
from datetime import date

todaystr = date.today().strftime('%Y%m%d')

# 下載庫存
import downloadtodaystock

stock_filename = f'艾克爾庫存明細報表_{todaystr}_1726'
outside_filename = f'SAP-{todaystr}外倉庫存'

# 先透過pands將xls格式轉為xlsx格式，因為openpyxl模組不支援xls模式，這個轉檔需要安裝xlrd模組
stock_xls = pd.read_excel(f'{stock_filename}.xls')
stock_xls.to_excel(f'{stock_filename}.xlsx')

# 打開艾克爾庫存明細
stock_wb = openpyxl.load_workbook(f'{stock_filename}.xlsx')

# 打開外倉庫存
outside_stock_wb = openpyxl.load_workbook(f'{outside_filename}.xlsx')

# 設定庫存要使用的工作表sheet
stock_wb.active = stock_wb['Sheet1']
stock_ws = stock_wb.active

# 設定外倉庫存要使用的工作表sheet
outside_stock_wb.active = outside_stock_wb['工作表1']
outside_stock_ws = outside_stock_wb.active

# 取得艾克爾庫存明細，並存成dict格式（key=batch名稱，value=庫存）
# column G = Stock Qty欄位
# column H = Batch欄位
stock_batchs = [cell.value for cell in stock_ws['H'][3:]] 
stock_qty = [cell.value for cell in stock_ws['G'][3:]] 
stock_data = set(zip(stock_batchs, stock_qty))

# 取得外倉庫存明細
# column F = batch欄位
# column I = stock qty欄位
# column J = block qty欄位
# column Q = Qual. Inspection欄位
outside_batchs = [cell.value for cell in outside_stock_ws['F'][1:]] 
outside_stock_qty = [cell.value for cell in outside_stock_ws['I'][1:]] 
outside_block_qty = [cell.value for cell in outside_stock_ws['J'][1:]] 
outside_qual_qty = [cell.value for cell in outside_stock_ws['Q'][1:]]
outside_qty = [outside_stock_qty[i] + outside_block_qty[i] + outside_qual_qty[i] for i in range(0, len(outside_batchs))]
outside_data = set(zip(outside_batchs, outside_qty))

# batch做聯集，取得兩個檔案的所有不重複batch
all_batchs = set(stock_data) ^ set(outside_data)

# 準備新的活頁簿
wb_final = openpyxl.Workbook()
wb_final.active.title = '庫存比對'
wb_final.worksheets[0].sheet_properties.tabColor = '0000ff'
wb_final.worksheets[0].protection.enable()

ws = wb_final.worksheets[0]

# 寫入欄位標題
ws['A1'] = 'batch'
ws['B1'] = '外倉'
ws['C1'] = '庫存'

final_data = set()

for data in all_batchs:
    row = [data[0], None, None]
    
    for s in outside_data:
        if s[0] == data[0]:
            row[1] = s[1]
        
    # left join
    for s in stock_data:
        if s[0] == data[0]:
            row[2] = s[1]

        if tuple(row) in final_data:
            row[2] = None

    final_data.add(tuple(row))

for row in final_data:
    ws.append(row)

# 儲存比對結果
wb_final.save(f'庫存比對-openpylx-aaron-{todaystr}.xlsx')

# 關閉活頁簿
stock_wb.close()
outside_stock_wb.close()

# 上傳結果
import uploadstockresult

print('比對完成')