<gh_stars>0
import os, re
from flask import Blueprint, render_template, request, send_file, flash, redirect, current_app as app
from datetime import datetime
from openpyxl import load_workbook
from werkzeug.utils import secure_filename

pbt_bp = Blueprint('pbt_bp', __name__)

def allowed_file(filename):
    ALLOWED_EXTENSIONS = ['csv', 'xlsx']
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

@pbt_bp.route("/pbt/main", methods=['GET', 'POST'])
def pbt_portal_report():
    if request.method == 'POST':
        # check if the post request has the file part
        if 'file' not in request.files:
            flash('No file part')
            return redirect(request.url)
        file = request.files['file']
        # if user does not select file, browser also
        # submit an empty part without filename
        if file.filename == '':
            flash('No selected file')
            return redirect(request.url)
        if file and allowed_file(file.filename):
            filename = secure_filename(file.filename)
            file_path = os.path.join(os.getcwd(),app.config["UPLOAD_FOLDER"],filename)
            file.save(file_path)

            # Open & setup excel workbook
            workbook = load_workbook(filename=file_path)
            sheet = workbook.active
            sheet.delete_cols(6,2)
            sheet.delete_cols(7,2)
            sheet.delete_cols(10,5)
            sheet.delete_cols(11,17)
            sheet.insert_cols(1)

            # Convert date to right format
            for datecell in sheet['F'][1:]:
                date_time_obj = datetime.strptime(datecell.value, '%d-%m-%Y %H:%M')
                datecell.value = str(date_time_obj.day)+'/'+str(date_time_obj.month)+'/'+str(date_time_obj.year)
            
            # Convert receiver to right format
            for receiver in sheet['G'][1:]:
                receiver.value = receiver.value.upper()
            
            # Get area
            for area in sheet['K'][1:]:
                areas = area.value.split()
                areas_to_remove = ['Depot', 'West', 'Central', 'PBT', 'North', 'Shore', 'Couriers']
                for remove in areas_to_remove:
                    if remove in areas: areas.remove(remove)
                area.value = ' '.join(areas).upper()
            
            # Get receiver
            for receiver in sheet['G'][1:]:
                try:
                    receiver.value = receiver.value.upper()
                except AttributeError:
                    pass
                except IndexError:
                    pass

            sheet.move_range("F1:F9999", rows=0, cols=-5)
            sheet.move_range("K1:K9999", rows=0, cols=-5)
            sheet.insert_cols(2)
            sheet.move_range("F1:F9999", rows=0, cols=-4)
            sheet.move_range("H1:H9999", rows=0, cols=-2)
            sheet.move_range("D1:D9999", rows=0, cols=4)
            sheet.delete_cols(4)
            sheet.delete_cols(8)
            sheet.insert_cols(8)

            # Add sortby column + data
            sort_row = 1
            sort_values = sort_values = {'DMLDSCH': 'DIVERS', 'DMHDSCH': 'DIVERS', 'DMCFO': 'DIVERS', 'GNZ LCL': 'GUNZ', 'DMFCFO': 'DIVERS', 'DMHDSB': 'DIVERS', 'AIRNZ S': 'AIRNZ', 'DG': 'REMARKETIG', 'DMSMESB': 'DIVERS', 'DMHDSCH': 'DIVERS', 'NTT': 'DIMENSION', 'S&T': 'ST', 'ST': 'ST', 'TT': 'TOMTOM', 'TTNZ': 'TOMTOM', 'GNZ': 'GUNZ', 'SME': 'E COMMERCE', 'FBL': 'FBL', 'ECM': 'E COMMERCE', 'EC': 'SALES', 'AIRNZ': 'AIRNZ', 'MU': 'MASSEY', 'INFODS': 'DIVERS', 'PBT ADMIN CHARGE': 'DIVERS', 'PBT': 'DIVERS', 'KEVS': 'DIVERS', 'GUNZ': 'GUNZ', 'JENNI': 'MASSEY', '<NAME>': 'MASSEY'}
            for sorter in sheet['N'][1:]:
                try:
                    sort_row += 1
                    sorter_value = sheet.cell(sort_row, 4).value.upper()
                    if sorter_value == '':
                        pass
                    else:
                        sorter.value = sort_values.get(re.findall('.+[A-Za-z\&](?=\s?\d?)', sorter_value)[0])
                except AttributeError:
                    pass
                except IndexError:
                    pass

            workbook.save(filename=file_path)
            
            return send_file(os.path.join(file_path), as_attachment=True)
    else:
        return render_template(
            "code.html",
            title = "Upload PBT portal report",
            form_param=['file']
        )

def get_pbt_prices(add_file):
    if add_file.filename == '':
        flash('No selected file')
        return redirect(request.url)
    if add_file and allowed_file(add_file.filename):
        filename = secure_filename(add_file.filename)
        file_path = os.path.join(os.getcwd(),app.config["UPLOAD_FOLDER"],filename)
        add_file.save(file_path)

        # Open & setup excel workbook
        add_workbook = load_workbook(filename=file_path)
        add_sheet = workbook.active
        
        workbook.save(filename=file_path)
        
        return add_file