import openpyxl
area = {"PBT Couriers Napier Depot": "NAPIER", "PBT Couriers Hamilton Depot": "HAMILTON", "PBT Couriers Auckland West Depot": "AUCKLAND", "PBT Couriers Whangarei Depot": "WHANGAREI", "PBT Couriers Auckland Central Depot": "AUCKLAND", "PBT Couriers Christchurch Depot": "CHRISTCHURCH"}

reportOnline = input("Input file (Online PBT report: ")
reportAccounts = input("Input destination accounts file: ")

# opens the online workbook
workbookOnline = load_workbook(filename=reportOnline, read_only=True)
sheetOnline = workbookOnline.active

# insert lines
sheetOnline.delete_rows(idx=1, amount=1)
totalitems = len(sheetOnline["A"])

# reorder online report
sheetOnline.move_range("S:S", rows=0, cols=-13)
for to in sheetOnline.iter_cols(min_row=1, min_col = 6, max_col=6, values_only=True):
    cell.value = area[to]
sheetOnline.move_range("B:B", rows=0, cols=5)
sheetOnline.move_range("D:D", rows=0, cols=-2)
sheetOnline.move_range("C:C", rows=0, cols=1)
sheetOnline.move_range("A:A", rows=0, cols=2)
sheetOnline.move_range("E:E", rows=0, cols=-4)
for date in sheetOnline.iter_cols(min_row=1, min_col=1, max_col=1, values_only=True):
    cell.value = "=TEXT(" + date + ", 'dd/mm/yyyy/'")
sheetOnline.move_range("H:H", rows=0, cols=-3)
sheetOnline.move_range("I:I", rows=0, cols=-1)
sheetOnline.move_range("L:M", rows=0, cols=-3)
for kl in sheetOnline.iter_cols(min_row=1, min_col=11, max_col=12, values_only=True):
    cell.value = 0

sheetOnline.save(reportOnline)

#opens the accounts workbook
workbookAccounts = load_workbook(filename=reportAccounts, read_only=True)
sheetAccounts = workbookAccounts.active
sheetAccounts.insert_rows(idx=4, amount=totalitems)

maxRow = sheetAccounts.max_row
minRow = sheetAccounts.min_row

for i in range(1, maxRow + 1):
    for j in range(1, minRow + 1):
        c = sheetOnline.cell(row = i, column = j)

        sheetAccounts(row = i+1, column = j).value = c.value


sheetAccounts.save(str(reportAccounts))