# DB TimeBlock Management # TimeBlock-IntegrityCheck.py # # @author Jack Rogerson (jrogerson@clarku.edu) (jrogerson.com) # @version Spring 2022 # PHYS 243 Spring 2022 - Professor Charles Agosta # # Previous Versions: # # About: # This script ensures that all expected time block entries are present. If any time blocks are missing, # they will be added via this script. The time blocks checked are seven days in the future from the day # it is being run on, and 30 days into the past from said day. This script should be run daily and after # the TimeBlock-Generate.py script completes. # # Basic email functionality will alert regarding invalid row counts. # However, if a DB error occurs during execution, the program may exit before an email notification can send. # 'Operation successful' emails will be sent (assuming email is enabled in .env). # # Invoke from cron, and save output as desired. import os from dotenv import load_dotenv import mysql.connector import calendar import datetime from datetime import time from shlex import quote def sendEmailAlert(programError, additionalInformation): """ Sends an email with the specified parameters in the .env file using OS level systems. :param programError: The primary error resulting in email communication. :param additionalInformation: Additional information that will be sent in the email message within the email body. :return: None. At return, the email notification will be sent at the OS level. """ if (os.environ.get('EMAILENABLED')): #If email functionality is enabled in the .env file. subject = quote(os.environ.get('EMAILSUBJECT')) #Get the subject from the .env file. body = str(os.environ.get('EMAILBODY') + "\n \n \n Program Error Message: \n \n \n" + str(programError) + " \n \n \n Additional Information: \n \n \n" + str(additionalInformation)) #Construct email body. sender = os.environ.get('EMAILSENDER') #Get the sender from the .env file. recipientsCSV = quote(os.environ.get('EMAILRECIPIENTSCSV')) #Get all recipients, by CSV, from the .env file. #As a result of excessively long email messages, write data to the filesystem first. emailTemporaryFilename = "email-TimeBlock-IntegrityCheck" + ".temp" #We need unique email files. with open(emailTemporaryFilename,"w") as tempFile: #Open the temporary email file for writing. Write at the start of the file and erase all previous content. tempFile.write(body) #Write email content. tempFile.close() #Close OS file with email content. os.system("cat " + emailTemporaryFilename + " | mail -s " + subject + " -aFrom:" + sender + " " + recipientsCSV) #Send the email using mail at the OS level. print("Email Notification Sent.") return if __name__ == "__main__": load_dotenv() #Load the DB info/credentials from the .env file. emailNULLDataErrors = [] #Used to track messages for the Summary Report email. emailOtherAlerts = [] #Used to track messages for the Summary Report email. microgridDBInstance = mysql.connector.connect( #Login to the database. host = os.environ.get('DBHOST'), #From .env File. user = os.environ.get('DBUSER'), #From .env File. password = os.environ.get('DBPASSWORD'), #From .env File. database = os.environ.get('DBDATABASENAME') #From .env File. ) microgridDBCursor = microgridDBInstance.cursor() #Establish DB cursor to execute commands. numberOfDaysToCheck = int(os.environ.get('NUMBEROFDAYSTOCHECK')) #Number of days that will be checked. From .env File. dateTimeNow = datetime.datetime.now() #Identify the current time. dateTimePlus7 = dateTimeNow + datetime.timedelta(days = 7) #Calculate time delta points for +7 day. dateTimeMin30 = dateTimePlus7 + datetime.timedelta(days = - numberOfDaysToCheck) #Calculate time delta points for number of days. dateTimeStartOfDayMin30 = dateTimeMin30.combine(dateTimeMin30, time.min) #Calculate the earliest moment of day. dateTimeStartOfDayMin30GMTAdj = dateTimeStartOfDayMin30 + datetime.timedelta(hours = 4) #Adjust for GMT -4 Offset. errorAlertFlag = 0 #Used to track if any errors that need corrective action have occurred during execution. #Check all expected timeblocks exist: for timeOffset in range(1440 * numberOfDaysToCheck): #1440 minutes per day. Range is endpoint exclusive, but we want 1439 * number days. timeBlockMinute = dateTimeStartOfDayMin30GMTAdj + datetime.timedelta(minutes = timeOffset) #Calculate time delta points for days. utcTimeTimeBlockMinute = calendar.timegm(timeBlockMinute.utctimetuple()) #Get UTC of calculated timeBlockMinute. print("Checking Existence For: " + str(timeBlockMinute) + " GMT ---> " + str(utcTimeTimeBlockMinute)) #Attempt to select the expected Time_Block_ID from the DB. microgridDBCursor.execute( "SELECT count(*) FROM Time_Block_ID WHERE Time_Block_ID = %s", [utcTimeTimeBlockMinute] ) #Note: Be warned this is a tuple data type. This is why we index 0. numberOfRows = microgridDBCursor.fetchone()[0] #Assuming the block ID is here, this should always and only be 1. if (numberOfRows != 1): #If we have a problem with data at the Time_Block_ID. errorAlertFlag = 1 #Alert user at the end of program that errors have attempted to be fixed. print("*** Attempting insert for: " + str(timeBlockMinute) + " GMT ---> " + str(utcTimeTimeBlockMinute) + " ***") emailOtherAlerts.append("*** Attempting insert for: " + str(timeBlockMinute) + " GMT ---> " + str(utcTimeTimeBlockMinute) + " ***") #Add a component of the final email report message. #Run the DB insert to fix issue. microgridDBCursor.execute( "INSERT INTO Time_Block_ID (Time_Block_ID, time_stamp) VALUES (%s, %s)", [utcTimeTimeBlockMinute, utcTimeTimeBlockMinute] ) if (microgridDBCursor.rowcount == 1): microgridDBInstance.commit() #At this point, safe to commit new Time_Block_IDs. else: microgridDBInstance.rollback() #Something is not right, rollback and fail. microgridDBInstance.close() #Close and exit DB connection. print("All expected DBCursor rows are not present! DB has not been modified!") sendEmailAlert("All expected DBCursor rows are not present! DB has not been modified!", emailOtherAlerts[-1]) #Send an email and provide -1 for the last operation description. raise Exception("All expected DBCursor rows are not present! DB has not been modified!") #Exit with error code. if (errorAlertFlag): #If we had any errors occur during execution. print("Missing Time Blocks were detected. Errors have attempted to be fixed. Please see logs.") if (emailNULLDataErrors != [] or emailOtherAlerts != []): #If the program messages are not null, proceed with sending the email. emailAdditionalInformation = " ~ ~ ~ ~ The following errors are non-recoverable at this time!! ~ ~ ~ ~\n\n\n" for reportItem in emailNULLDataErrors: #Add each reportItem from emailNULLDataErrors that has been collected during program execution. emailAdditionalInformation = emailAdditionalInformation + reportItem + "\n\n" emailAdditionalInformation = emailAdditionalInformation + "---\n\n" emailAdditionalInformation = emailAdditionalInformation + "\n\n\n\n***** ***** *****\n\n\n\n" emailAdditionalInformation = emailAdditionalInformation + "The following corrective actions were attempted during this integrity check.\n\n" for reportItem in emailOtherAlerts: #Add each reportItem from emailOtherAlerts that has been collected during program execution. emailAdditionalInformation = emailAdditionalInformation + reportItem + "\n" reportTitle = "Integrity Check Summary Report" + "\n" + "Missing Time Blocks were detected. Errors have attempted to be fixed. Please see logs." sendEmailAlert(reportTitle, emailAdditionalInformation) #Call sendEmailAlert for remaining email processes. if (os.environ.get('EMAILENABLED')): #If email enabled, send email to confirm script finished successfully. sender = os.environ.get('EMAILSENDER') #Get the sender from the .env file. subject = quote("TimeBlock-IntegrityCheck.py Successful!") #Set a custom subject. recipientsCSV = quote(os.environ.get('EMAILRECIPIENTSCSV')) #Get all recipients, by CSV, from the .env file. os.system("echo " + "TimeBlock-IntegrityCheck.py exit 0" + " | mail -s " + subject + " -aFrom:" + sender + " " + recipientsCSV) #Send the email using mail at the OS level. exit(0) #Clean exit. All set here.