#!/usr/bin/python
## Make Imports
from subprocess import Popen, PIPE
from pprint import pprint
import os
import logging
import sys
import getopt
import traceback
import cx_Oracle
import xlsxwriter
import ConfigParser
#########################################################
#### Define Global Variables#############################
def loadGlobalVars(out_file, date_f, font_name):
global wb
global ws
global bold_format
global red_color_format
global date_format
global full_border_format
global heading_format
global pass_format
global fail_format
global seq_format
global merge_format
# Define a Global Workbook Object
wb = xlsxwriter.Workbook(out_file) # Create Excel Object for new workbook
ws = wb.add_worksheet("Summary") # Create Summary Sheet
# Define Formatting Formats
bold_format = wb.add_format({'bold': True})
bold_format.set_border()
bold_format.set_font_name(font_name)
red_color_format = wb.add_format()
red_color_format.set_font_color('red')
red_color_format.set_border()
red_color_format.set_font_name(font_name)
date_format = wb.add_format({'num_format': date_f })
date_format.set_border()
date_format.set_font_name(font_name)
full_border_format = wb.add_format()
full_border_format.set_border()
full_border_format.set_font_name(font_name)
fail_format = wb.add_format({'bold': True})
fail_format.set_bg_color('red')
fail_format.set_align('center')
fail_format.set_font_color('white')
fail_format.set_border()
fail_format.set_font_name(font_name)
pass_format = wb.add_format({'bold': True})
pass_format.set_bg_color('green')
pass_format.set_align('center')
pass_format.set_font_color('white')
pass_format.set_border()
pass_format.set_font_name(font_name)
heading_format = wb.add_format({'bold': True})
heading_format.set_bg_color('black')
heading_format.set_align('center')
heading_format.set_font_color('white')
heading_format.set_font_name(font_name)
seq_format = wb.add_format({'bold': True})
seq_format.set_align('center')
seq_format.set_font_color('black')
seq_format.set_border()
seq_format.set_font_name(font_name)
merge_format = wb.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter'})
merge_format.set_bg_color('black')
merge_format.set_font_color('white')
merge_format.set_font_size(14)
def load_main(ini_file):
Config = ConfigParser.ConfigParser()
try:
Config.read(ini_file)
except Exception as e:
logging.info('Error Parsing ini file : ' + ini_file )
logging.info(traceback.print_exc())
sys.exit(2)
# Get Global Properties as Defined in Global Properties Section
out_file = Config.get('GlobalProperties','output_file')
username = Config.get('GlobalProperties','Username')
password = Config.get('GlobalProperties','Password')
hostName = Config.get('GlobalProperties','HostName')
portNumber = Config.get('GlobalProperties','PortNumber')
sID = Config.get('GlobalProperties','SID')
date_f = Config.get('GlobalProperties','date_format')
report_title = Config.get('GlobalProperties','report_title')
font_name = Config.get('GlobalProperties','font_name')
loadGlobalVars(out_file, date_f, font_name)
# this is Summary Sheet Configuration
ws.merge_range('B1:D1',report_title, merge_format)
ws.write(2,1,"#",heading_format)
ws.write(2,2,"Check_Name",heading_format)
ws.write(2,3,"Description",heading_format)
ws.write(2,4,"Status",heading_format)
ws.set_column(2,2,25)
ws.set_column(3,3,60)
ws.set_column(4,4,10)
# Get the Connection Object
db = createConnectionObject(username, password , hostName, portNumber, sID)
rw_cnt = 3
section_list = Config.sections()
for section in section_list:
if section == 'GlobalProperties':
logging.info ( 'Skipping Global Properties')
else:
sheet = Config.get(section,'Sheet')
sQL = Config.get(section,'SQL')
column = int(Config.get(section,'Column'))
threshold = float(Config.get(section,'Threshold'))
date_col = Config.get(section,'Date_cols')
dtList = date_col.split(',')
rptOnly = Config.get(section,'Report_Only')
description = Config.get(section,'Description')
threshold_type = Config.get(section,'threshold_type')
try:
logging.info('Processing Section: ' + section)
flg = write_to_excel(db, sheet, sQL, column, threshold, threshold_type, dtList, rptOnly) # Send the Cursor to writetoExcel Function
ws.write(rw_cnt,1,rw_cnt - 2, seq_format)
ws.write(rw_cnt,2,sheet, bold_format)
ws.write(rw_cnt,3,description, full_border_format)
if flg == 'false':
ws.write(rw_cnt,4,"Fail", fail_format)
else:
ws.write(rw_cnt,4,"Pass", pass_format)
rw_cnt = rw_cnt + 1
except Exception as e:
logging.info('Function - writeToExcel In Exception')
logging.info(traceback.print_exc())
db.close() # Finally Close DB Object
## Define Function
def write_to_excel(db, sheet, sQL, column, threshold, threshold_type, dtList, rptOnly):
cur_sor = db.cursor()
cur_sor.execute(sQL)
worksheet = wb.add_worksheet(sheet)
format_dict = {}
# Provide row and column offsets i.e from where output should be printed in the sheet
row_offset = 1
col_offset = 1
row = 0 + row_offset
col = 0 + col_offset
# Set flag to true as default
flag = 'true'
# Print Headings to Excel Sheet using cursor.description
for i in range(0, len(cur_sor.description)):
worksheet.write(row,i + col_offset, cur_sor.description[i][0], heading_format)
# Initialize dict for column width setting
format_dict[i + col_offset] = len(cur_sor.description[i][0]) + 4
# Increment row to work from next row after headings
row = row + 1
for tupple_row in cur_sor:
col = 0 + col_offset
for list_item in tupple_row:
worksheet.write(row, col, list_item, full_border_format)
# Logic to verify threshold as per column given and less than, greater than given in config sheet
if (col == column - 1 + col_offset and rptOnly == 'N'):
if threshold_type == 'lt':
if list_item < threshold:
worksheet.write(row, col, list_item, red_color_format)
flag = 'false'
if threshold_type == 'gt':
if list_item > threshold:
worksheet.write(row, col, list_item, red_color_format)
flag = 'false'
# Now check for Date format
for dt in dtList:
if col == int(dt) - 1 + col_offset:
worksheet.write(row, col, list_item, date_format)
max_len_temp = len(str(list_item))
if max_len_temp > format_dict[col]:
format_dict[col] = max_len_temp
col = col + 1
row = row + 1
# routine to adjust Column Widhts as Captured in the Dict
for key in format_dict:
worksheet.set_column(key,key,format_dict[key])
# Now return flag as true/false based on checking - This is updated in Summary Sheet
return flag
# Function to return Connection Object for Query Executions
def createConnectionObject (username, password , hostName, portNumber, sID):
dsn_tns = cx_Oracle.makedsn(hostName, portNumber, sID)
db = cx_Oracle.connect(username, password, dsn_tns)
return db
#
# Program Execution Starts here
############################################################################################################
def main(argv):
#Setup Logging Information
if len(argv) < 2:
print "#########################################"
print "######Correct Usage of Script is ########"
print "###### <script_name> ini_file log_file###"
print "#########################################"
sys.exit(2)
try:
ini_file = argv[1]
log_file = argv[2]
if os.path.isfile(ini_file):
print 'Success - Verified Ini File Exists'
else:
print 'Error - Ini_file Does not Exists - Exiting'
sys.exit(2)
except Exception as e:
logging.info('Parsing Sys Arguements')
logging.info(traceback.print_exc())
logging.basicConfig(filename=log_file, level=logging.INFO, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p')
logging.info(' Script Execution - Started')
try:
load_main(ini_file)
# Make sure to close workbook
wb.close()
except Exception as e:
logging.info('Function - Load_Main Exception')
logging.info(traceback.print_exc())
logging.info('Script - Execution Ended')
if __name__ == "__main__":
main(sys.argv)
