AmazonScriptPopulateDB/AmazonReadFiles.py

import xml.etree.ElementTree as ET
import json
import smtplib
import email.utils
from email.mime.text import MIMEText
import os
import pyodbc as po
file_path = os.path.realpath(__file__)

with open(file_path.replace('AmazonReadFiles.py', '')+"/config/config.json") as json_data_file:
    config = json.load(json_data_file)
server = config['Server']
database = config['Database']
username = config['DBUser']
password = config['DBPassword']
downloadedPath = config['downloadFolder']
files = []


def SendEmail():
    msg = MIMEText(
        'Mediante este correo se le notifica que ha llego una nueva factura de Amazon')
    msg['To'] = email.utils.formataddr(('Recipient', 'agarcia@gemcousa.com'))
    msg['From'] = email.utils.formataddr(
        ('GEMCO informa', 'noreply@gemcousa.mx'))
    msg['Subject'] = 'Llego una nueva factura de AMAZON'
    sender = "noreply@gemcousa.mx"
    receivers = "agarcia@gemcousa.com"
    smtpObj = smtplib.SMTP('smtp.ionos.com', 587)
    smtpObj.ehlo()
    smtpObj.starttls()
    smtpObj.login('noreply@gemcousa.mx', 'Mx77lf%02')
    try:
        smtpObj.sendmail(sender, [receivers], msg.as_string())
        print("Successfully sent email")
    except:
        print("Error: unable to send email")


def isFloat(num):
    try:
        float(num)
        return float(num)
    except ValueError:
        return 0


def ProcessHeader(
    CommercialInvoiceDate,
    InvoiceNumber,
    TrailerNumber,
    TrailerId,
    PortofLoading,
    PortofEntry,
    PaymentTerms,
    Incoterms,
    HAWB,
    TotalInvoiceQuantityUnitOfMesuare,
    Quantity,
    TotalInvoiceWeightUnitOfMesuare,
    WeightValue,
    TotalInvoiceValueChargeOrAllowance,
    TotalInvoiceValueCurrencyISOCode,
    TotalInvoiceValueMonetaryAmount,
    FooterNote,
    Archivo,
):

    try:
        # Connection string
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )
        cursor = cnxn.cursor()
        storedProc = """\
        SET NOCOUNT ON;
        DECLARE @RC int;
        EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.Invoice.Header.Append] @CommercialInvoiceDate = ?, @InvoiceNumber = ?, @TrailerNumber = ?, @TrailerId = ?,
                                                                       @PorOfLoading = ?, @PortOfEntry = ?, @PaymentsTerms = ?, @Incoterms = ?, @HAWB = ?,
                                                                       @TotalInvoiceQuantityUnitOfMeasure = ?, @TotalInvoiceQuantity = ?,
                                                                       @TotalInvoiceWeightUnitOfMeasure = ?, @TotalInvoiceWeight = ?,
                                                                       @TotalInvoiceValueChargeOrAllowance = ?, @TotalInvoiceValueCurrencyISOCode = ?,
                                                                       @TotalInvoiceValueMonetaryAmount = ?, @FootNote = ?, @Archivo =?;
        SELECT @RC AS rc;"""
        params = (
            CommercialInvoiceDate,
            InvoiceNumber,
            TrailerNumber,
            TrailerId,
            PortofLoading,
            PortofEntry,
            PaymentTerms,
            Incoterms,
            HAWB,
            TotalInvoiceQuantityUnitOfMesuare,
            Quantity,
            TotalInvoiceWeightUnitOfMesuare,
            WeightValue,
            TotalInvoiceValueChargeOrAllowance,
            TotalInvoiceValueCurrencyISOCode,
            TotalInvoiceValueMonetaryAmount,
            FooterNote,
            Archivo,
        )
        cursor.execute(storedProc, params)
        rc = cursor.fetchval()
        print(rc)
        cnxn.commit()
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()
        return rc


def ProcessShipFromAddress(
    IDHeader,
    InvoiceNumber,
    AddressType,
    Name,
    AddressLine1,
    City,
    StateProvince,
    Zip,
    CountryCode,
    CountryName,
    Archivo,
):

    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )
        cursor = cnxn.cursor()
        storedProc = """\
        SET NOCOUNT ON;
        DECLARE @RC int;
        EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.Invoice.ShipFromAddress.Append] @IDHeader=?, @InvoiceNumber=?, @AddressType=?, @Name=?, @AddressLine1=?, @City=?, @StateProvince=?,
                                                                                @Zip=?, @CountryCode=?, @CountryName=?, @Archivo=?;
        SELECT @RC AS rc;"""
        params = (
            IDHeader,
            InvoiceNumber,
            AddressType,
            Name,
            AddressLine1,
            City,
            StateProvince,
            Zip,
            CountryCode,
            CountryName,
            Archivo,
        )
        cursor.execute(storedProc, params)
        rc = cursor.fetchval()
        print(rc)
        cnxn.commit()
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()
        return rc


def ProcessDetail(
    IDHeader,
    InvoiceNumber,
    AmazonShipmentReferenceId,
    TrailerId,
    ItemId,
    ItemIdType,
    ItemDescription,
    PedimentoDescription,
    DestinationHTSCode,
    SourceHTSCode,
    ECCN,
    LicEx,
    CountryOfOrigin,
    ProductGroup,
    UPC,
    Brand,
    Model,
    ItemQuantityUnitOfMeasure,
    Quantity,
    NetWeightUnitOfMeasure,
    WeightValue,
    ActualUnitCostChargeOrAllowance,
    ActualUnitCostCurrencyISOCode,
    ActualUnitCostMonetaryAmount,
    TotalUnitValueChargeOrAllowance,
    TotalUnitValueCurrencyISOCode,
    TotalUnitValueMonetaryAmount,
    Archivo,
):

    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )
        cursor = cnxn.cursor()
        storedProc = """\
        SET NOCOUNT ON;
        DECLARE @RC int;
        EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.Invoice.Detail.Append]  @IDHeader=?, @InvoiceNumber=?, @AmazonShipmentReferenceId=?, @TrailerId=?, @ItemId=?, @ItemIdType=?, 
                                                                        @ItemDescription=?, @PedimentoDescription=?, @DestinationHTSCode=?, @SourceHTSCode=?, @ECCN=?, 
                                                                        @LicEx=?, @CountryOfOrigin=?, @ProductGroup=?, @UPC=?, @Brand=?, 
                                                                        @Model=?, @ItemQuantityUnitOfMeasure=?, @Quantity=?, @NetWeightUnitOfMeasure=?, @WeightValue=?,
                                                                        @ActualUnitCostChargeOrAllowance=?, @ActualUnitCostCurrencyISOCode=?, @ActualUnitCostMonetaryAmount=?,
                                                                        @TotalUnitValueChargeOrAllowance=?, @TotalUnitValueCurrencyISOCode=?,  @TotalUnitValueMonetaryAmount=?,
                                                                        @Archivo=?;
        SELECT @RC AS rc;"""
        params = (
            IDHeader,
            InvoiceNumber,
            AmazonShipmentReferenceId,
            TrailerId,
            ItemId,
            ItemIdType,
            ItemDescription,
            PedimentoDescription,
            DestinationHTSCode,
            SourceHTSCode,
            ECCN,
            LicEx,
            CountryOfOrigin,
            ProductGroup,
            UPC,
            Brand,
            Model,
            ItemQuantityUnitOfMeasure,
            Quantity,
            NetWeightUnitOfMeasure,
            WeightValue,
            ActualUnitCostChargeOrAllowance,
            ActualUnitCostCurrencyISOCode,
            ActualUnitCostMonetaryAmount,
            TotalUnitValueChargeOrAllowance,
            TotalUnitValueCurrencyISOCode,
            TotalUnitValueMonetaryAmount,
            Archivo,
        )
        cursor.execute(storedProc, params)
        rc = cursor.fetchval()
        print(rc)
        cnxn.commit()
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()
        return rc


def ProcessCorrelacion():
    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )
        cursor = cnxn.cursor()
        storedProc = """\
        SET NOCOUNT ON;
        DECLARE @RC int;
        EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.Invoice.Detail.Correlacion.Validate];"""
        cursor.execute(storedProc)
        print('Correlacion executed')
        cnxn.commit()
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()


def ProcessCorrelacionPartesGEMCO():
    try:
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
            + server
            + ";DATABASE="
            + database
            + ";UID="
            + username
            + ";PWD="
            + password
        )
        cursor = cnxn.cursor()
        storedProc = """\
        SET NOCOUNT ON;
        DECLARE @RC int;
        EXEC @RC = [GEMCO].dbo.[Clientes.Amazon.Invoice.Detail.Correlacion.PartesGEMCO];"""
        cursor.execute(storedProc)
        print('Correlacion executed')
        cnxn.commit()
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()


def GetFileList():
    for file in os.listdir(downloadedPath):
        if os.path.isfile(os.path.join(downloadedPath, file)) and file.endswith('.xml'):
            files.append(downloadedPath + "/" + file)


GetFileList()

for file in files:
    Archivo = file
    print("archivo=" + Archivo)
    tree = ET.parse(Archivo)
    root = tree.getroot()

    HeaderData = []
    for child in root.iter("manifestHeader"):
        for child2 in child:
            if "commercialInvoiceDate" in child2.tag:
                CommercialInvoiceDate = child2.text
            if "invoiceNumber" in child2.tag:
                InvoiceNumber = child2.text
            if "TrailerNumber" in child2.tag:
                TrailerNumber = child2.text
            if "TrailerId" in child2.tag:
                TrailerId = child2.text
            if "portofLoading" in child2.tag:
                PortofLoading = child2.text
            if "portofEntry" in child2.tag:
                PortofEntry = child2.text
            if "paymentTerms" in child2.tag:
                PaymentTerms = child2.text
            if "incoterms" in child2.tag:
                Incoterms = child2.text
            if "HAWB" in child2.tag:
                HAWB = child2.text
        HeaderData.append(
            (
                CommercialInvoiceDate,
                InvoiceNumber,
                TrailerNumber,
                TrailerId,
                PortofLoading,
                PortofEntry,
                PaymentTerms,
                Incoterms,
                HAWB,
            )
        )

        ManifestSummaryData = []
        ShipFromAddress = []
        DetailData = []
        IDHeader = 0
        for child in root.iter("manifestSummary"):
            for child2 in child:
                if "footNote" in child2.tag:
                    FooterNote = child2.text
                for child3 in child2:
                    if "quantity" in child3.tag:
                        Quantity = child3.text
                        TotalInvoiceQuantityUnitOfMesuare = child3.attrib[
                            "unitOfMeasure"
                        ]
                    if "weightValue" in child3.tag:
                        WeightValue = child3.text
                        TotalInvoiceWeightUnitOfMesuare = child3.attrib[
                            "unitOfMeasure"
                        ]
                    if "chargeOrAllowance" in child3.tag:
                        ChargeOrAllowance = child3.text
                    if "monetaryAmount" in child3.tag:
                        MonetaryAmmount = child3.text
                        CurrencyISOCode = child3.attrib["currencyISOCode"]
            ManifestSummaryData.append(
                (
                    Quantity,
                    TotalInvoiceQuantityUnitOfMesuare,
                    WeightValue,
                    TotalInvoiceWeightUnitOfMesuare,
                    ChargeOrAllowance,
                    MonetaryAmmount,
                    CurrencyISOCode,
                    FooterNote,
                )
            )

            IDHeader = ProcessHeader(
                CommercialInvoiceDate,
                InvoiceNumber,
                TrailerNumber,
                TrailerId,
                PortofLoading,
                PortofEntry,
                PaymentTerms,
                Incoterms,
                HAWB,
                TotalInvoiceQuantityUnitOfMesuare,
                isFloat(Quantity),
                TotalInvoiceWeightUnitOfMesuare,
                isFloat(WeightValue),
                ChargeOrAllowance,
                CurrencyISOCode,
                isFloat(MonetaryAmmount),
                FooterNote,
                Archivo,
            )
            print("Id del nuevo header=", IDHeader)

            if IDHeader > 0:
                SendEmail()
                for child in root.iter("manifestHeader"):
                    for child2 in child:
                        AddressType = child2.attrib
                        for child3 in child2:
                            if "name" in child3.tag:
                                Name = child3.text
                            if "addressLine1" in child3.tag:
                                AddressLine1 = child3.text
                            if "city" in child3.tag:
                                City = child3.text
                            if "zip" in child3.tag:
                                Zip = child3.text
                            if "countryCode" in child3.tag:
                                CountryCode = child3.text
                            if "countryName" in child3.tag:
                                CountryName = child3.text
                            for child4 in child3:
                                if "stateProvince" in child4.tag:
                                    StateProvince = child4.text
                        if "AddressType" in AddressType:
                            ShipFromAddress.append(
                                (
                                    AddressType["AddressType"],
                                    Name,
                                    AddressLine1,
                                    City,
                                    StateProvince,
                                    Zip,
                                    CountryCode,
                                    CountryName,
                                )
                            )
                for row in ShipFromAddress:
                    print(row)
                    ProcessShipFromAddress(
                        IDHeader,
                        InvoiceNumber,
                        row[0],
                        row[1],
                        row[2],
                        row[3],
                        row[4],
                        row[5],
                        row[6],
                        row[7],
                        Archivo,
                    )

                    for child in root.iter("shipmentDetail"):
                        for child2 in child:
                            if "AmazonShipmentReferenceId" in child2.tag:
                                AmazonShipmentId = child2.text
                            if "trailerId" in child2.tag:
                                TrailerId = child2.text
                            if "itemID" in child2.tag:
                                ItemID = child2.text
                                ItemIDType = child2.attrib["type"]
                            if "itemDescription" in child2.tag:
                                ItemDescription = child2.text
                            if "pedimentoDescription" in child2.tag:
                                PedimentoDescription = child2.text
                            if "destinationHTSCode" in child2.tag:
                                DestinationHTSCode = child2.text
                            if "sourceHTSCode" in child2.tag:
                                SouceHTSCode = child2.text
                            if "ECCN" in child2.tag:
                                Eccn = child2.text
                            if "LicEx" in child2.tag:
                                LicEx = child2.text
                            if "countryOfOrigin" in child2.tag:
                                CountryOfOrigin = child2.text
                            if "productGroup" in child2.tag:
                                ProductGroup = child2.text
                            if "upc" in child2.tag:
                                Upc = child2.text
                            if "brand" in child2.tag:
                                Brand = child2.text
                            if "model" in child2.tag:
                                Model = child2.text
                            for child3 in child2:
                                if "quantity" in child3.tag:
                                    Quantity = child3.text
                                    ItemQuantityUnitOfMeasure = child3.attrib["unitOfMeasure"]
                                if "weightValue" in child3.tag:
                                    WeightValue = child3.text
                                    NetWeightUnitOfMeasure = child3.attrib["unitOfMeasure"]
                                if "actualUnitCost" in child2.tag:
                                    #print(child3.attrib, ' ',child3.tag, ' ',child3.text )
                                    if "chargeOrAllowance" in child3.tag:
                                        ActualUnitCostChargeOrAllowance = child3.text
                                    if "currencyISOCode" in child3.attrib:
                                        ActualUnitCostCurrencyISOCode = child3.attrib[
                                            "currencyISOCode"
                                        ]
                                        ActualUnitCostMonetaryAmount = child3.text
                                if "totalUnitValue" in child2.tag:
                                    if "chargeOrAllowance" in child3.tag:
                                        TotalUnitValueChargeOrAllowance = child3.text
                                    if "currencyISOCode" in child3.attrib:
                                        TotalUnitValueCurrencyISOCode = child3.attrib[
                                            "currencyISOCode"
                                        ]
                                        TotalUnitValueMonetaryAmount = child3.text

                        DetailData.append(
                            (
                                AmazonShipmentId,
                                TrailerId,
                                ItemID,
                                ItemIDType,
                                ItemDescription,
                                PedimentoDescription,
                                DestinationHTSCode,
                                SouceHTSCode,
                                Eccn,
                                LicEx,
                                CountryOfOrigin,
                                ProductGroup,
                                Upc,
                                Brand,
                                Model,
                                ItemQuantityUnitOfMeasure,
                                int(Quantity),
                                NetWeightUnitOfMeasure,
                                isFloat(WeightValue),
                                ActualUnitCostChargeOrAllowance,
                                ActualUnitCostCurrencyISOCode,
                                isFloat(ActualUnitCostMonetaryAmount),
                                TotalUnitValueChargeOrAllowance,
                                TotalUnitValueCurrencyISOCode,
                                isFloat(TotalUnitValueMonetaryAmount),
                            )
                        )

                    for row in DetailData:
                        print(row, "\n")
                        ProcessDetail(
                            IDHeader,
                            InvoiceNumber,
                            row[0],
                            row[1],
                            row[2],
                            row[3],
                            row[4],
                            row[5],
                            row[6],
                            row[7],
                            row[8],
                            row[9],
                            row[10],
                            row[11],
                            row[12],
                            row[13],
                            row[14],
                            row[15],
                            row[16],
                            row[17],
                            row[18],
                            row[19],
                            row[20],
                            row[21],
                            row[22],
                            row[23],
                            row[24],
                            Archivo
                        )
                        
ProcessCorrelacionPartesGEMCO()
ProcessCorrelacion()