ISI Middleware

GCM

Overview

The script is designed to periodically fetch data from a SQL Server database (ISI_Stock table) using OCR and send this data to an external API via POST requests. Each row from the ISI_Stock table populates a template payload, which is then sent as a POST request to the specified API.

If there’s any error during the POST request (like a failed addition of material to the ISI database), an email alert is sent to a specified developer email to notify about the error. The script keeps track of which parts have been emailed about, ensuring that multiple emails aren’t sent about the same part.

The script runs indefinitely with a 100-minute wait period between each run. There’s also commented-out code for a scheduled run at a specific time, but that functionality is currently not in use.

Key Features

Imports:
- Essential libraries are imported like pyodbc for database connectivity, requests for API requests, json for handling JSON data, pandas for data manipulation, and email-related libraries for sending emails.
Global Variables:
- headers: Contains an API key and content type for API requests.
- url: Endpoint URL for API requests.
- TempPayload: A template payload for the API request.
- Database connection credentials: server, database, username, and password.
SMTP Class:
- Provides features to send email alerts using SMTP protocol.
- Contains attributes like port, password, smtp_server, sender_email, and various recipient emails.
- Contains methods ClearErrors, EmailAlert for email error handling.
ISIPost Function:
- Makes a POST request to the specified url.
- If the POST request results in an error, it sends an email alert using the SMTP class.
PostData Function:
- Reads data from the SQL database table ISI_Stock.
- Iterates through the dataframe, populates the TempPayload dictionary, and sends a POST request using the ISIPost function for each row.
Scheduling:
- Commented out code suggests that the PostData function was intended to be scheduled to run every day at 06:00.
- Currently, the PostData function runs in an infinite loop with a 6000-second (100 minutes) wait between each run.

Below are snippets and simplifications of the Middleware that was purchased.

Main.py

from email.message import EmailMessage
from xml.dom.pulldom import ErrorHandler
import pyodbc
import requests
import json
import pandas as pd
import time
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib, ssl
import schedule

headers = {
  'apiKey': 'XXX',
  'Content-Type': 'application/json'
}

url = "XXX" # redacted URL

TempPayload = {
    # ... (keeping the payload structure but redacted actual values if any)
}

server = 'XXX' 
database = 'XXX' 
username = 'XXX' 
password = 'XXX' 
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

class SMTP:
    def __init__(self):
        self.port = 587 
        self.password = "XXX"
        self.PartError = []
        self.smtp_server = "XXX"
        self.sender_email = "XXX"
        self.alias_email = "XXX"
        self.Engineer_email = "XXX"
        self.Quality_email = "XXX"
        self.MastProgrammer = "XXX"
        self.Developer_email = "XXX"
        self.msg = MIMEMultipart("Alternative")
        self.message = "Initialize."

    def ClearErrors(self):
       self.PartError = []
def EmailAlert(self,Part,error):
        if Part  not in self.PartError: 

                        self.msg['subject'] = "Could not add %s"%(Part)
                        self.msg['From'] = self.alias_email
                        self.msg['To'] = self.Developer_email

                        message = ("""\
                                Hello,

                                 %s Could not be added to ISI database. Error is: %s """)%(Part,error)

                        

                        bodytext = MIMEText (message, "plain")
                        self.msg.attach(bodytext)
                        # Create a secure SSL context
                        context = ssl.create_default_context()

                        # Try to log in to server and send email
                        try:
                            server = smtplib.SMTP(self.smtp_server,self.port)
                            server.ehlo() # Can be omitted
                            server.starttls(context=context) # Secure the connection
                            server.ehlo() # Can be omitted
                            server.login(self.sender_email, self.password)
                            # TODO: Send email here
                            server.sendmail(self.msg['From'], self.Developer_email, self.msg.as_string())
                        except Exception as e:
                            # Print any error messages to stdout
                            print(e)
                        finally:
                            self.PartError.insert(len(self.PartError),Part)
                            print(self.PartError)
                            server.quit() 
        else:
            print ("Part Already has been emailed to user ")


ErrorHandle = SMTP()
def ISIPost(payload):
        print("\nCurrent URL " + url + "\n")
        #print(json=payload)
        #ISIpayload = json.dumps(payload)
        response = requests.request("POST", url, headers=headers, data=payload)
        print("\n*******ISI Post Response*******\n")
        print(response)
        print("\n\n\n")
        print(payload)
        ISIPost.Package = response.json()
        print(ISIPost.Package)
        try: 
            response.raise_for_status()
        except requests.exceptions.HTTPError as e:
            print("%s Cannot be added because: %s" % (TempPayload["ISIMaterial"],response.json()))
            ErrorHandle.EmailAlert(TempPayload["ISIMaterial"],response.json())
            return "Error: " + str(e)
            

        return response.json()

def PostData():
    DF = pd.read_sql_query("SELECT * from GCM.dbo.ISI_Stock", cnxn)
    print (DF)

    for index, row in DF.iterrows():
        TempPayload["ISIMaterial"] = row["ISIMaterial"]
        TempPayload["SupplerMaterial"] = row["SupplerMaterial"]
        TempPayload["OnHandInventory"] = row["OnHandInventory"]
        TempPayload["WIPInventory"] = row["WIPInventory"]
        TempPayload["SupplierMfgSite"] = row["SupplierMfgSite"]
        TempPayload["ClearToBuild"] = row["ClearToBuild"]
        TempPayload["ClearToPlan"] = row["ClearToPlan"]
        TempPayload["Comments"] = row["Comments"]
        print(TempPayload)
        ISIPost(json.dumps([TempPayload]))
        #payload.append(TempPayload.copy())


#schedule.every().day.at("06:00").do(PostData)
while True:
    PostData()
    #schedule.run_pending()
    ErrorHandle.EmailAlert("Eric","Working")
    time.sleep(6000) #wait 30 minutes