APC.py

import os
import openpyxl
from openpyxl import load_workbook
import csv
from openpyxl import Workbook
import datetime
import operator
from operator import itemgetter
from decimal import *


headings = ["REF", "CUSTOMER:JOB", "SHIP DATE", "DUE DATE", "ITEM", "GROSS VOL", "GROSS EXP",
            "GROSS OWNER"]

product_codes = {"100": " Texas Oil", "103": " Texas Oil", "300": "Condensate", "303": "Texas Oil", "200": "Texas Gas",
                 "201": "Texas Gas",
                 "202": "Texas Gas", "203": "Texas Gas", "204": "Texas Gas", "205": "Texas Gas", "206": "Texas Gas",
                 "209": "HELIUM", "400": "Liquid Products", "401": "Liquid Products", "402": "Liquid Products",
                 "403": "Liquid Products", "404": "Liquid Products", "405": "Liquid Products", "406": "Liquid Products",
                 "407": "Liquid Products", "40C": "Plant Condensate", "40D": "Plant Sulphur", "409": "Gasoline"}

col_list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O']

needed_col_list = ['A', 'B', 'D', 'H', 'K', 'L', 'N', 'O']

row_list = []
tags = []
sheets = []
property_names = []
product_rows = []
only_value_rows = []
final_sort = []
interest_rows = []



os.chdir('C:\\Users\porte\Desktop')
#
# file_name = input("Please enter your file name: ")

file_name = 'mpi-apc.xlsx'

wb = load_workbook(filename=file_name)
ws = wb.active

print("Processing file...........................")

# Create Dates

for row in ws["A"]:
    try:
        if type(row.value) is datetime.datetime:
            month = row.value.month
            day = row.value.day
            new_date = str(month) + "/" + "01" + "/" + str(day)
            new_date = datetime.date.strftime(new_date, '%m/%d/%y')
            row.value = new_date
    except TypeError:
        continue


# grab property names and codes
for cell in ws['A']:
    try:
        if "PROPERTY" in cell.value:
            tags.append(cell)
            d_cell = ws['D' + str(cell.row)].value
            if ws['F' + str(cell.row)].value is not None:
                d_cell += " " + ws['F' + str(cell.row)].value
            if ws['G' + str(cell.row)].value is not None:
                d_cell += " " + ws['G' + str(cell.row)].value
            property_names.append(d_cell)

    except TypeError:
        continue

# Adds property names to end of rows for later sort
for idx, tag in enumerate(tags):
    try:
        row_count = tags[idx + 1].row - tag.row - 1
        for x in range(1, row_count + 1):
            ws['O' + str(tag.row + x)] = property_names[idx]
    except IndexError:
        row_count = ws.max_row - tag.row
        for x in range(1, row_count + 1):
            ws['O' + str(tag.row + x)] = property_names[idx]

# Get row number for rows with products
for idx, tag in enumerate(tags):
    try:
        row_count = tags[idx + 1].row - tag.row - 1
        for x in range(1, row_count + 1):
            if ws['B' + str(tag.row + x)].value is not None:
                product_rows.append(tag.row + x)
    except IndexError:
        row_count = ws.max_row - tag.row
        for x in range(1, row_count + 1):
            product_rows.append(tag.row + x)


# eliminate empty rows
for idx, row in enumerate(product_rows):
    if ws['B' + str(row)].value is None:
        product_rows.pop(idx)

# read amended values back into lists
for row in range(1, ws.max_row + 1):
    cells = []
    for col in needed_col_list:
        cells.append(ws[col + str(row)].value)

    cells = [x for x in cells if x is not None]

    only_value_rows.append(cells)


# clean non-product rows out of list
only_value_rows = [item for item in only_value_rows if len(item) >= 6]


# special handling for interest rows
for row in ws['O']:
    try:
        if "INTEREST" in row.value:
            interest_row = list()
            interest_row.append(ws['A' + str(row.row)].value)
            interest_row.append(ws['B' + str(row.row)].value)
            interest_row.append(ws['N' + str(row.row)].value)
            interest_row.append(ws['O' + str(row.row)].value)
            if interest_row[0] is not None:
                interest_rows.append(interest_row)
            else:
                continue
    except TypeError:
        continue

# make sure product code is str
for item in interest_rows:
    item[1] = str(item[1])
# remove small unneeded rows
for idx, item in enumerate(interest_rows):
    try:
        if None in item:
            interest_rows.pop(idx)
    except TypeError:
        continue
# add to final sort list
final_sort.append(interest_rows)

# Sort value rows with the same date and same product code together
holder = list([only_value_rows[0]])
hold_counter = 0
for idx in range(1, len(only_value_rows) + 1):
    try:
        value_list = only_value_rows[idx]
    except IndexError:
        final_sort.append(holder)
        continue

    if value_list[0] == holder[hold_counter][0] and value_list[1] == holder[hold_counter][1] and holder[hold_counter][
        -1] == value_list[-1]:
        holder.append(value_list)
        hold_counter += 1
    else:
        final_sort.append(holder)
        holder = [value_list]
        hold_counter = 0

final_sort = [x for x in final_sort if x != []]


for idx, lst in enumerate(final_sort):
    if len(lst) == 1 and "INTEREST" in lst[0]:
        del final_sort[idx]

# Create new sheet in workbook
wb.create_sheet("totals")
active_ws = wb['totals']

# Create titles on sheet
for idx, title in enumerate(headings):
    active_ws.cell(row=1, column=idx + 1, value=title)

row_counter = 2

same_prop = ""

total = 0
for item in final_sort:
    try:

        final_gross_vol = 0
        final_exp = 0
        final_owner_gross = 0
        final_owner_adj = 0

        if len(item) == 1 and "INTEREST" not in item:
            active_ws.cell(row=row_counter, column=2, value="APC:" + item[0][-1])  # Property Name
            active_ws.cell(row=row_counter + 1, column=2, value="APC:" + item[0][-1])  # Property Name
            active_ws.cell(row=row_counter, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + 1, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + 1, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=5, value=product_codes[str(item[0][1])])  # Product Code
            active_ws.cell(row=row_counter + 1, column=5, value="Texas Severance Exp")
            active_ws.cell(row=row_counter, column=6, value=item[0][2])  # Gross VOL
            active_ws.cell(row=row_counter + 1, column=7, value=item[0][3])  # Gross EXP
            active_ws.cell(row=row_counter, column=8, value=item[0][4])  # Gross Owner
            active_ws.cell(row=row_counter + 1, column=8, value=item[0][5] * -1)  # Owner Adj
            row_counter += 2

        else:
            for lst in item:
                if len(lst) > 4:
                    gross_vol = lst[2]
                    final_gross_vol += gross_vol
                    gross_exp = lst[3]
                    final_exp += gross_exp
                    gross_owner = lst[4]
                    final_owner_gross += gross_owner
                    owner_adj = lst[5]
                    final_owner_adj += owner_adj
                else:
                    owner_gross = lst[2]
                    final_owner_gross += Decimal(owner_gross)

            active_ws.cell(row=row_counter, column=2, value="APC:" + item[0][-1])  # Property Name
            active_ws.cell(row=row_counter + 1, column=2, value="APC:" + item[0][-1])  # Property Name
            active_ws.cell(row=row_counter, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + 1, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + 1, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=5, value=product_codes[str(item[0][1])])  # Product Code
            active_ws.cell(row=row_counter + 1, column=5, value="Texas Severance Exp")
            active_ws.cell(row=row_counter, column=6, value=final_gross_vol)  # Gross VOL
            active_ws.cell(row=row_counter + 1, column=7, value=final_exp)  # Gross EXP
            active_ws.cell(row=row_counter, column=8, value=final_owner_gross)  # Gross Owner
            active_ws.cell(row=row_counter + 1, column=8, value=final_owner_adj * -1)  # Owner Adj
            row_counter += 2
    except TypeError:
        continue

active_ws['I' + str(active_ws.max_row)] = "=Sum(H1:H" + str(active_ws.max_row) + ")"

wb.save('2018-09-25-V-APC-MPI-975,251.54.xlsx')

# save_file = input("What name would you like to save the file under?: ")
#
# wb.save(save_file + '.xlsx')
#
# print("File saved to " + os.getcwd())