
########################################################################################
########################################################################################
########################################################################################
####              Company Name:  AUTOMATECH  LLC                
####              Software:      MACHINE DESIGNER   
####              Generator:     QUOTATION_PER_DEV                      
####              Library:       API                
####              Copyright (c) 2022 AUTOMATECH LLC         
####              All Rights Reserved.                              
####
####  Unauthorized use of this file, via any medium, is strictly prohibited.
####  This script is ONLY authorized to run with MACHINE DESIGNER by AUTOMATECH.      
####  Any attempt to execute it on unauthorized platforms is        
####  strictly forbidden and may result in legal consequences.  
########################################################################################
########################################################################################
########################################################################################


import os
import datetime
import xlsxwriter

class Script():
    FOLDER_PATH = ""
    FOLDER_PATH_INPUTS = ""
    FOLDER_PATH_OUTPUTS = ""

    @staticmethod
    def generateAll():
        QuotationEngine.GenerateQuotation()

class Quotation():
    LIST_OF_COMPONENTS = []
    LIST_OF_IDS = []
    LIST_OF_QUOTES = []
    QTY_DICT = {}
    QUOTES_DICT = {}

    def __init__(self):
        Quotation.LIST_OF_COMPONENTS.append(self)
        self.machine = "SYSTEM"
        self.id = "TEMPLATE"
        self.quote_dict = {}
        self.quote_per_unit = 0

    def addGlobalQty(self):
        if self.id in Quotation.QTY_DICT.keys():
            Quotation.QTY_DICT[self.id] += 1
        else:
            Quotation.QTY_DICT[self.id] = 1

    def addGlobalQuote(self):
        for quote in self.quote_dict.keys():
            temp_key = self.id + quote
            if temp_key not in Quotation.QUOTES_DICT.keys():
                Quotation.QUOTES_DICT[temp_key] = self.quote_dict[quote]

    def addQuote(self, key, value):
        self.quote_dict[str(key)] = value

class QuotationEngine():
    FILE_SYSTEM = "SYSTEM"
    FILE_NAME = "QUOTATION"
    PATH_IMAGE = 'LOGO_AUTOMATECH.png'

    TITLE_QUOTATION = "QUOTATION PER FULL DEVELOPMENT"
    TITLE_QUOTE_UNIT_COST = 'COST P/HR'
    TITLE_QUOTES_PER_COMPONENTS = 'HRS P/COMP'
    TITLE_QUOTES_TOTAL_PER_COMPONENT = 'TOTAL COST'
    TITLE_QUOTES = 'DEVELOPMENT QUOTE- HOUR'

    UNIT_COST = 100

    COLOR_GREY_LIGHT = "#AEAEAE"
    COLOR_GREY_DARK = "#BFBFBF"
    COLOR_GREY_MEDIUM = "#6F6F6F"

    @staticmethod
    def get_date():
        # set file name
        now = str(datetime.datetime.now())
        year = now.split("-")[0]
        month = now.split("-")[1]
        day = now.split(" ")[0].split("-")[2]
        hour = now.split(" ")[1].split(":")[0]
        minute = now.split(" ")[1].split(":")[1]
        date = "_" + year + month + day + "_" + hour + minute
        return date

    @staticmethod
    def UpdateGlobals():
        for component in Quotation.LIST_OF_COMPONENTS:
            component.addGlobalQty()
        for component in Quotation.LIST_OF_COMPONENTS:
            component.addGlobalQuote()

    @staticmethod
    def UpdateLists():
        Quotation.LIST_OF_IDS = []
        Quotation.LIST_OF_QUOTES = []
        temp_quotes_dict = {}
        for component in Quotation.LIST_OF_COMPONENTS:
            if component.id not in Quotation.LIST_OF_IDS:
                Quotation.LIST_OF_IDS.append(component.id)
                temp_quotes_dict.update(component.quote_dict)
        for key in temp_quotes_dict.keys():
            Quotation.LIST_OF_QUOTES.append(key)

    @staticmethod
    def GenerateWorksheet():
        alphabet = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                    'U', 'V', 'W', 'X', 'Y', 'Z']
        # components = ["CS", "PE", "SS", "PSO", "VFD"]
        # quotes = ["HMI", "ELECTRICAL", "PLC", "MECHANICAL"]
        components = Quotation.LIST_OF_IDS
        components.sort()
        quotes = Quotation.LIST_OF_QUOTES
        if len(quotes) < 1:
            quotes = ["NONE"]

        offset_x = 0
        offset_y = 4
        # Create an new Excel file and add a worksheet.
        file_name = QuotationEngine.FILE_NAME
        workbook = xlsxwriter.Workbook(Script.FOLDER_PATH_OUTPUTS + file_name + QuotationEngine.get_date() + ".xlsx")
        #workbook = xlsxwriter.Workbook(Script.FOLDER_PATH_OUTPUTS + file_name + ".xlsx")
        worksheet = workbook.add_worksheet("QUOTATION")
        try:
            worksheet.insert_image('A1', Script.FOLDER_PATH_INPUTS + QuotationEngine.PATH_IMAGE)
        except:
            pass
        worksheet.set_row(0, 70)
        worksheet.set_row(1, 70)
        worksheet.set_row(2, 15)
        worksheet.set_row(3, 15)

        worksheet.set_column('A:A', 45)
        worksheet.set_column('B:B', .01)
        worksheet.set_column('C:C', 20)
        worksheet.set_column('D:D', .01)
        worksheet.set_column('E:E', 10)
        worksheet.set_column('F:Z', 18)

        # FIRST ROW
        def get_format(type):
            cell_format = workbook.add_format()
            cell_format.set_bold()
            cell_format.set_font_size(18)
            cell_format.set_align("vcenter")
            cell_format.set_shrink()
            if type == "A": cell_format.set_font_size(28)
            if type == "A": cell_format.set_bg_color(QuotationEngine.COLOR_GREY_DARK)
            if type == "B": cell_format.set_bg_color(QuotationEngine.COLOR_GREY_MEDIUM)
            if type == "C": cell_format.set_bg_color(QuotationEngine.COLOR_GREY_LIGHT)
            return cell_format

        cell_format = get_format("A")
        for index in [0,1]:
            worksheet.merge_range(f"""A{index}:{alphabet[len(quotes)+4]}{index}""", '', cell_format)
        for index in [2]:
            worksheet.merge_range(f"""A{index}:{alphabet[len(quotes)+4]}{index}""", QuotationEngine.TITLE_QUOTATION, cell_format)
        for index in [3]:
            worksheet.merge_range(f"""A{index}:{alphabet[len(quotes)+4]}{index}""", '', cell_format)


        cell_format = get_format("B")
        worksheet.merge_range(f"""A{offset_y}:A{offset_y + 1}""", 'COMPONENTS', cell_format)
        worksheet.merge_range(f"""B{offset_y}:B{offset_y + 1}""", 'QTY', cell_format)
        worksheet.merge_range(f"""C{offset_y}:C{offset_y + 1}""", QuotationEngine.TITLE_QUOTES_PER_COMPONENTS, cell_format)
        worksheet.merge_range(f"""D{offset_y}:D{offset_y + 1}""", QuotationEngine.TITLE_QUOTE_UNIT_COST, cell_format)
        worksheet.merge_range(f"""E{offset_y}:E{offset_y + 1}""", QuotationEngine.TITLE_QUOTES_TOTAL_PER_COMPONENT, cell_format)
        worksheet.merge_range(f"""F{offset_y}:{alphabet[len(quotes) + 4]}{offset_y}""", QuotationEngine.TITLE_QUOTES, cell_format)
        cell_format = get_format("C")
        for index in range(len(quotes)):
            worksheet.write(f"""{alphabet[index + 5]}{offset_y + 1}""", quotes[index], cell_format)

        # ADD COMPONENTS
        def get_format(type, left=False):
            cell_format = workbook.add_format()
            cell_format.set_bold()
            cell_format.set_font_size(15)
            cell_format.set_align("center")
            if left:
                cell_format.set_align("left")
            cell_format.set_align("vcenter")
            if type == "A": cell_format.set_bg_color("#D8E4BC")
            if type == "B": cell_format.set_bg_color("#EBF1DE")
            if type == "C": cell_format.set_bg_color("green")
            if type == "C": cell_format.set_font_size(20)
            return cell_format

        for index in range(len(components)):
            if index % 2 == 0:    cell_format = get_format("A", True)
            if index % 2 != 0:    cell_format = get_format("B", True)
            index_offset_y = index + offset_y + 2
            # ADD COMPONENTS
            cmp_cell = 'A' + str(index_offset_y)
            worksheet.write(cmp_cell, components[index], cell_format)

            if index % 2 == 0:    cell_format = get_format("A")
            if index % 2 != 0:    cell_format = get_format("B")
            # ADD QTY
            qty = Quotation.QTY_DICT[components[index]]
            qty_cell = 'B' + str(index_offset_y)
            worksheet.write(qty_cell, qty, cell_format)

            # ADD UNITS PER COMPONENT
            units_per_component = 'C' + str(index_offset_y)
            formula = f"""=sum(F{index_offset_y}:{alphabet[len(quotes) + 5]}{index_offset_y}) """
            worksheet.write(units_per_component, formula, cell_format)
            # ADD UNIT COST
            cost_per_unit_cell = 'D' + str(index_offset_y)
            formula = f"""{QuotationEngine.UNIT_COST} """
            worksheet.write(cost_per_unit_cell, formula, cell_format)

            # ADD TOTAL COST
            total_cost_unit_cell = 'E' + str(index_offset_y)
            worksheet.write(total_cost_unit_cell, f"""={qty_cell} * {cost_per_unit_cell} * {units_per_component} """, cell_format)


            # ADD QUOTES
            for index_q in range(len(quotes)):
                temp_key = components[index] + quotes[index_q]
                temp_dict = Quotation.QUOTES_DICT
                if temp_key in Quotation.QUOTES_DICT.keys():
                    value_per_unit = Quotation.QUOTES_DICT[temp_key]
                else:
                    value_per_unit = 0
                worksheet.write(f"""{alphabet[index_q + 5]}{index_offset_y}""", value_per_unit, cell_format)

            # ADD TOTAL
            if len(components) - 1 == index:
                cell_format = get_format("C")
                worksheet.write('A' + str(index_offset_y + 1), "TOTAL", cell_format)
                formula = f"""=sum(C{str(offset_y + 2)}:C{str(offset_y + 1 + len(components))})"""
                worksheet.write('C' + str(index_offset_y + 1), formula, cell_format)
                formula = f"""=sum(E{str(offset_y + 2)}:E{ str(offset_y + 1 + len(components) )})"""
                worksheet.write('E' + str(index_offset_y + 1), formula, cell_format)

        workbook.close()

    @staticmethod
    def GenerateQuotation():
        QuotationEngine.UpdateGlobals()
        QuotationEngine.UpdateLists()
        QuotationEngine.GenerateWorksheet()




