sebd.py

#!/Python36
"""This script checks the SmartEcho database for the set business date with options to fix database"""

# import modules and set initial variables
try:
    import datetime
    import time
    import os
    import sys
    import ctypes
    import logging
    from colorama import init
    from colorama import Fore
    import pypyodbc
    init()
    apptitl = ("sebd.py")
    appvers = ("1.05")
    appname = ("SmartEcho Business Date Checker")
    appupdt = ("06/01/2017 @ 10:17pm")
    logging.basicConfig(filename=apptitl + "." + 'LOG', level=logging.DEBUG, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p -')

except ImportError as ex:
    print("\nImport Error: " + str(ex))
    print("\nAborting application...\n")
    sys.exit()
# set the title of the console
clear = os.system('cls')
title = ctypes.windll.kernel32.SetConsoleTitleW("%s - version %s - updated %s - (%s)" %(appname, appvers, appupdt, apptitl))
logging.debug("started " + apptitl)

# connect to the SmartEcho database
try:
    sesqlconn = pypyodbc.connect('Driver={SQL Server}; Server=localhost; Database=SmartEcho_Fuel_100; Trusted_Connection=yes;')
    sesqlcrsr = sesqlconn.cursor()
except pypyodbc.Error as ex:
    sqlstate = ex.args[1]
    if not sqlstate == '1':
        print(Fore.RED + "Database Error" + sqlstate + "\nUnable to connect to the SmartEcho Database\n")
        logging.debug("Database Error" + sqlstate)
    sys.exit()

# query SmartEcho database for site name and businessdate
try:
    SQLCommand = ("Select name from Site")
    if sesqlcrsr.execute(SQLCommand):
        print(Fore.YELLOW + "\nSmartEcho Database Connected on " + time.strftime("%B %d %Y @ %H:%M%p" + Fore.CYAN))
        logging.debug("SmartEcho Database Connected")
        sitename = sesqlcrsr.fetchone()
    else:
        pass
except pypyodbc.Error as ex:
    sqlstate = ex.args[1]
    if not sqlstate == '1':
        print(Fore.RED + "Database Error" + sqlstate + "\n\nUnable to connect to the SmartEcho Database\n\n")
        logging.debug("Database Error" + sqlstate)
    sys.exit()

try:
    SQLCommand = ("Select businessdate from Site")
    sesqlcrsr.execute(SQLCommand)
    sitebusinessdate = sesqlcrsr.fetchone()
    BusinessDate = sitebusinessdate[0]
    BusinessDateYear = int(BusinessDate // 10000) # year
    BusinessDateMonth = int((BusinessDate // 100) % 100) # month
    BusinessDateDay = int(BusinessDate % 100) # day
    BusinessDateString = str(BusinessDateYear) + "-" + str(BusinessDateMonth)
    BusinessDateString = BusinessDateString + "-" + str(BusinessDateDay)
    now = datetime.date.today()
    nowdate = now.strftime("%Y%m%d")
    FromDate = datetime.date(BusinessDateYear, BusinessDateMonth, BusinessDateDay)
    DateDiff = (now - FromDate).days
except pypyodbc.Error as ex:
    sqlstate = ex.args[1]
    if not sqlstate == '1':
        print("Database Error" + sqlstate + "\n\nUnable to connect to the SmartEcho Database\n\n")
        logging.debug("Database Error" + sqlstate)
        os.system("pause")
        sys.exit()

# format message to display in console
strTextBody1 = "\n" + "At " + sitename[0] + " the SmartEcho Business Date is set to "
strTextBody1 = strTextBody1 + BusinessDateString + "\n"
strTextBody1 = strTextBody1 + Fore.RED + "The SmartEcho Business Date is off by " + str(DateDiff,) + " day(s)"
strTextBody1 = strTextBody1 + Fore.CYAN

# display message if business date is off otherwise exit
try:
    if not DateDiff == 0:
        print(strTextBody1)
        ynchoice = input("\nWould you like to set the business date to todays date now? [Y/n] ")
        if ynchoice.startswith('y'):
            DateUpdate = ("UPDATE Site SET BusinessDate = " + str(nowdate) + " WHERE BusinessDate = " + str(BusinessDate))
            SQLCommand = DateUpdate
            sesqlcrsr.execute(SQLCommand)
            sesqlcrsr.commit()
            dbynchoice = input("\nWould you like to fix the businessdate columns in the SmartEcho Database now? [Y/n] ")
            if dbynchoice.startswith('y'):
                try:
                    daysinput = int(input("\nEnter the number of days to go back: "))
                    if daysinput == "":
                        print("\nYou selected nothing - setting to 1 day ")
                        daysinput = 1
                    else:
                        pass
                except ValueError:
                    print("\nNot a number - setting to 1 day ")
                    daysinput = 1
            else:
                pass
            daysback = datetime.timedelta(days=int(daysinput))
            qtydaysback = now - daysback
            daytostart = str(qtydaysback)
            print("\nDate to start :" + daytostart)
            os.system("pause")
            try:
                SQLFileName = 'FixBusinessDate.SQL'
                if os.path.isfile(SQLFileName):
                    print("\nRunning external SQLCOMMAND... Please Wait")
                    FixBusDateCmd = (r"SQLCMD -E -i .\FixBusinessDate.SQL -o .\BusinessDateFixResults.TXT -v dayendstart=") + daytostart + ("")
                    os.system(FixBusDateCmd)
                    print("\nThe SmartEcho database 'businessdate' columns have been updated.")
                    print("\nYou can check the results in the BusinessDateFixResults.TXT file")
                else:
                    print("\nFixBusinessDate.SQL file does not exist... aborting")
                    logging.debug("FixBusinessDate.SQL file does not exist")
            except IOError:
                print("\nRead error.... aborting")
        else:
            pass
    else:
        print(Fore.GREEN + "\nSmartEcho Business Date is already current")
        logging.debug("SmartEcho Business Date is already current")
except SystemExit:
    pass

# finish up and close connection to database
try:
    sesqlcrsr.close()
    sesqlconn.close()
    print(Fore.YELLOW + "\nSmartEcho Database Connection Closed")
    logging.debug("SmartEcho Database Connection Closed")
    sys.exit(1)
except SystemExit:
    pass