def GetData():
try:
InitialParameters = Initialize.getParam()
StagingPath = InitialParameters[2]
APIView = InitialParameters[3].split(',')
APICategory = InitialParameters[4].split(',')
BaseURl = InitialParameters[5].replace("'", "")
type_param = 'csv'
row_param = 'rows'
RequestURLList = []
WriteToLog('\nFetching Files form http://data.consumerfinance.gov using API')
WriteToLog('------------------------------------------------------------------------------------------------------------------')
for i in range(0, len(APIView)):
# print(APICategory[i].replace("'",""),'\t\t',APIView[i].replace("'",""))
RequestURLList.append(BaseURl + APIView[i].replace("'", "") + '/' + row_param + '.' + type_param)
#print(RequestURLList[i])
URLData = request.urlopen(RequestURLList[i])
CSVRaw = URLData.read()
CSVData = str(CSVRaw).strip("b'")
lines = CSVData.split("\\n")
CSVFileName = StagingPath + APICategory[i].replace("'", "") + '.' + type_param
CSVFile = open(CSVFileName, 'w')
WriteToLog('Loading : '+APICategory[i].replace("'", "") + '.' + type_param+' <-- '+ RequestURLList[i])
#print(CSVFileName)
for line in lines:
CSVFile.write(line + "\n")
CSVFile.close()
WriteToLog('------------------------------------------------------------------------------------------------------------------')
except Exception as e:
FailureRevert(3,e)
def WriteToLog(msg):
InitialParameters = Initialize.getParam()
LogFileName = InitialParameters[14]
LogFile = open(LogFileName, 'a')
LogFile.write(msg+'\n')
LogFile.close()
print(msg)
def PreLoadTasks():
InitialParameters = Initialize.getParam()
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
TableName = InitialParameters[10]
CloneTableName=InitialParameters[12]
ViewName=InitialParameters[16]
MainLogTable = InitialParameters[18]
global STEP_COUNT
# Clone Main Table
try:
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
FirebirdDB.CloneTable(TableName,CloneTableName)
TableCount=FirebirdDB.GetTableCount(CloneTableName)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
except Exception as e:
FailureRevert(1,e)
else:
WriteToLog('\nClone Table ('+CloneTableName.upper()+') Created.')
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Pre-Pre',CloneTableName,'Clone Table Created',TableCount,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
#Swap Views
try:
FirebirdDB.ViewSwap(CloneTableName,ViewName)
ViewCount=FirebirdDB.GetTableCount(ViewName)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
except Exception as e2:
FailureRevert(2,e2)
else:
WriteToLog('View Swap ('+ViewName.upper()+' --> '+CloneTableName.upper()+') Completed.')
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Pre-Pre',ViewName,'Swap Main view to Clone Table',ViewCount,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
def DataUpdateFlag():
# check if the file is present if not then create
InitialParameters = Initialize.getParam()
LoadFileName = InitialParameters[15]
LogFileName = InitialParameters[14]
MainLogTable = InitialParameters[18]
if os.path.isfile(LoadFileName) == True:
print('Update Already in Progress !!!')
exit()
else:
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
LoadFile = open(LoadFileName, 'w')
LoadFile.close()
LogFile = open(LogFileName, 'w')
LogFile.close()
WriteToLog('Update Process Initiated . . . '+time.strftime('%d %b %Y %X'))
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
LoadIndex+=1
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
global STEP_COUNT
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Pre-Pre',LogFileName.split('\\')[-1],'Created Log File to Capture Update Progress Status',0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Pre-Pre',LoadFileName.split('\\')[-1],'Created Trig File to Indicate \'Update in Progress\'',0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
def DisplayLogContent():
#global LogValue
CurrentLogValue = ''
InitialParameters = Initialize.getParam()
LogFileName = InitialParameters[14]
try:
LogFile = open(LogFileName, 'r')
except Exception as e:
#print('Log File Error : ',e)
CurrentLogValue = 'Unable to Display Log.Please Wait . . .'
else:
CurrentLogValue = LogFile.read()
LogContentDisplay.config(text=CurrentLogValue)
LogContentDisplay.after(200, DisplayLogContent)
def DisplayLogContent():
#global LogValue
CurrentLogValue=''
InitialParameters = Initialize.getParam()
LogFileName = InitialParameters[14]
try:
LogFile = open(LogFileName, 'r')
except Exception as e:
#print('Log File Error : ',e)
CurrentLogValue='Unable to Display Log.Please Wait . . .'
else:
CurrentLogValue = LogFile.read()
LogContentDisplay.config(text=CurrentLogValue)
LogContentDisplay.after(200,DisplayLogContent)
def FailureRevert(Step,Error):
global FAIL_FLAG,STEP_COUNT
ErrorString={1:'Creating Clone Table',
2:'Swapping View to Clone Table',
3:'Fetching Data from Online API',
4:'Creating Reject File',
5:'Truncating Staging Table',
6:'Loading Data into Staging Table',
7:'Appending Data to Main Table',
8:'Swapping View to Main Table',
9:'Archiving Reject File',
10:'Deleting Trig File',
11:'Archiving Log File'}
WriteToLog('Error During : '+str(ErrorString.get(Step)))
WriteToLog('Description :'+str(Error))
InitialParameters = Initialize.getParam()
MainLogTable = InitialParameters[18]
TableName = InitialParameters[10]
CloneTableName=InitialParameters[12]
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Error',str(ErrorString.get(Step)),str(Error),0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
#Revert Tasks if needed
if Step==1:
RevertList=['T','L','F']
elif Step in (3,4,5,6):
RevertList=['S','T','L','F']
elif Step == 7:
FirebirdDB.CloneTable(CloneTableName,TableName)
RevertList=['S','T','L','F']
else:
messagebox.showinfo("Alert !!!", "Error in Swapping View.Please check Logs!!!\n\n\nTerminating Upload Process.")
exit()
#fun the post function
PostLoadTasks(*RevertList)
def MainTableAppend():
try:
InitialParameters = Initialize.getParam()
MainLogTable = InitialParameters[18]
MainLoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
StagTableName = InitialParameters[11]
TableName = InitialParameters[10]
WriteToLog('\nAdding new Records to Main Table ('+TableName.upper()+')')
WriteToLog('----------------------------------------------------------------------------------------------------------')
MainRowCount=FirebirdDB.GetMainRecordCount(TableName,StagTableName)
FirebirdDB.UpdateMainTable(TableName,StagTableName)
WriteToLog('Loading : '+TableName.upper()+'<-- '+StagTableName.upper()+' [Count('+str(MainRowCount)+')]')
WriteToLog('----------------------------------------------------------------------------------------------------------')
#Update the Main Log
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
TableCount=FirebirdDB.GetTableCount(TableName)
MainLoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
except Exception as e:
FailureRevert(7,e)
else:
global STEP_COUNT
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Data-Load',TableName,'Main Table Loaded',TableCount,MainLoadStartDTTM,MainLoadEndDTTM)
STEP_COUNT+=1
import fdb
import Initialize
InitialParameters=Initialize.getParam()
DBLocation=InitialParameters[1]
DBName=InitialParameters[6]
UserName=InitialParameters[7]
Password=InitialParameters[8]
DBPath=DBLocation+DBName
CreateComment="create database '"+str(DBPath)+"' user '"+str(UserName)+"' password '"+str(Password)+"'"
print('DatabasePath :',DBPath)
con = fdb.create_database(CreateComment)
print("Database (",DBName,") created Sucessfully .")
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
cur.execute("recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)")
con.commit()
print("Table ConsumerComplaint created Sucessfully .")
cur.execute("create unique index unique_ComplaintID on consumercomplaint(ComplaintID)")
con.commit()
print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .")
cur.execute("recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)")
con.commit()
print("Table consumercomplaint_staging created Sucessfully .")
cur.execute("recreate view ConsumerComplaintView as select DateReceived,Product,SubProduct,Issue,SubIssue,ConsumerComplaint,CompanyPublicResponse,Company,State,ZIPCode,SubmittedVia,DateSentCompany,CompanyResponseConsumer,TimelyResponseSts,ConsumerDisputedSts,ComplaintID from consumercomplaint")
con.commit()
print("View ConsumerComplaintView created Sucessfully .")
cur.execute("recreate table consumercomplaint_copy (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)")
con.commit()
def ViewLog(root, ImageFrame, ImageLabel, photo, MainPageFrame,
ViewLogButton, FetchDataButton):
#Disable the Button and destroy the Image
ViewLogButton.config(state=DISABLED)
FetchDataButton.config(state=DISABLED)
ImageLabel.destroy()
#Create Top and Bottom Frame
TopViewFrame = Frame(ImageFrame)
TopViewFrame.pack(side=TOP)
BottopViewFrame = Frame(ImageFrame)
BottopViewFrame.pack(side=BOTTOM)
#Label to Display Log with Scroll Bar
#Function for Scroll Bar
def ScrollLogFunc(event):
ViewCanvas.configure(scrollregion=ViewCanvas.bbox("all"),
height=370,
width=800)
#canvas to Implement Scroll Bar for Log
ViewCanvas = Canvas(TopViewFrame)
CanvasFrame = Frame(ViewCanvas)
ScrollLogY = Scrollbar(TopViewFrame,
orient="vertical",
command=ViewCanvas.yview)
ViewCanvas.configure(yscrollcommand=ScrollLogY.set)
ViewCanvas.pack(side=LEFT)
ScrollLogY.pack(side=RIGHT, fill=Y)
ViewCanvas.create_window((0, 0), window=CanvasFrame, anchor=NW)
#Label to display the Log Details
ViewLogDisplay = Label(CanvasFrame,
text='Please Select a Log Category.',
anchor=NW,
justify=LEFT,
wraplength=700)
CanvasFrame.bind("<Configure>", ScrollLogFunc)
ViewLogDisplay.pack(side=BOTTOM)
#DropDown Button For Archive Log
var = StringVar()
var.set('')
InitialParameters = Initialize.getParam()
LogPath = InitialParameters[19]
#LogPath=r'C:\Users\Priyank\Desktop\MIS\Scripting Languages\DataAnalyst-Project(Python)\Coding\Logs'
LogList = []
for filename in os.listdir(LogPath):
#if filename.split('.')[-1]=='log' and filename.split('_')[0]=='DataUpdateLog':
LogList.append(filename)
def func(value):
LogFile = LogPath + '\\' + str(value)
Log = open(LogFile, 'r')
LogValue = Log.read()
ViewLogDisplay.config(text=LogValue)
ChoiceLabel.config(text='Archived Process Logs -')
ChoiceLabel = Label(CanvasFrame, text='LOGS !!!')
ChoiceLabel.pack(side=LEFT)
ChoiceDropDown = OptionMenu(CanvasFrame, var, *LogList, command=func)
ChoiceDropDown.pack(side=RIGHT)
#Function to Display the Logs based on selection
#APi and Source file info from Log table
def APILog(ChoiceLabel):
ChoiceLabel.config(text='Source File and API Logs -')
var.set('')
APLDataFrame = FirebirdDB.DisplayAPILog()
APLDataFrame['FileLen'] = APLDataFrame.FileName.map(len)
MaxLength = 0
for i in range(0, len(APLDataFrame)):
if APLDataFrame.loc[i, 'FileLen'] > MaxLength:
MaxLength = APLDataFrame.loc[i, 'FileLen']
for i in range(0, len(APLDataFrame)):
APLDataFrame.loc[i, 'FileName'] = str(
APLDataFrame.loc[i, 'FileName']).ljust(MaxLength, '_')
m, s = divmod(APLDataFrame.loc[i, 'Duration'], 60)
h, m = divmod(m, 60)
DurationTime = str(int(h)).rjust(2, '0') + ':' + str(
int(m)).rjust(2, '0') + ':' + str(int(s)).rjust(2, '0')
APLDataFrame.loc[i, 'Duration'] = DurationTime
#print(APLDataFrame.loc[i,'LoadStartDTTM'])
APLDataFrame.loc[
i, 'LoadStartDTTM'] = datetime.datetime.strptime(
APLDataFrame.loc[i, 'LoadStartDTTM'],
"%Y-%m-%d %H:%M:%S.%f").strftime("%d %b %y %H:%M:%S")
APLDataFrame.loc[i, 'LoadStartDTTM'] = str(
APLDataFrame.loc[i, 'LoadStartDTTM']).ljust(30,
' ').upper()
#print(datetime.datetime.strptime(APLDataFrame.loc[i,'LoadStartDTTM']),"%d/%m/%Y").strftime("%d%b%y"))
APLDataFrame.loc[i, 'LoadIndex'] = str(
int(APLDataFrame.loc[i, 'LoadIndex']))
APLDataFrame.loc[i, 'LoadOrder'] = str(
int(APLDataFrame.loc[i, 'LoadOrder']))
APLDataFrame.loc[i, 'RecordCount'] = str(
int(APLDataFrame.loc[i, 'RecordCount']))
ViewLogDisplay.config(
text=
'--------------------------------------------------------------------------------'
'-----------------------------------------------\n'
'Index\tOrder\t' + 'FileName'.ljust(MaxLength, '_') +
'\tCount\tStartTime\t\tDuration\n' +
'--------------------------------------------------------------------------------'
'-----------------------------------------------\n' +
APLDataFrame.to_csv(
sep='\t',
index=False,
header=False,
columns=[
'LoadIndex', 'LoadOrder', 'FileName', 'RecordCount',
'LoadStartDTTM', 'Duration'
]) +
'--------------------------------------------------------------------------------'
'-----------------------------------------------')
#Load Step info from Main Log
def LoadStep(ChoiceLabel):
ChoiceLabel.config(text='Load Step Logs -')
var.set('')
LoadStepDataFrame = FirebirdDB.DisplayLoadStepLog()
LoadStepDataFrame['StatusLen'] = LoadStepDataFrame.Status.map(len)
MaxStatus = 0
for i in range(0, len(LoadStepDataFrame)):
if LoadStepDataFrame.loc[i, 'StatusLen'] > MaxStatus:
MaxStatus = LoadStepDataFrame.loc[i, 'StatusLen']
for i in range(0, len(LoadStepDataFrame)):
LoadStepDataFrame.loc[i, 'Status'] = str(
LoadStepDataFrame.loc[i, 'Status']).ljust(MaxStatus, '_')
LoadStepDataFrame.loc[i, 'LoadIndex'] = str(
int(LoadStepDataFrame.loc[i, 'LoadIndex']))
LoadStepDataFrame.loc[i, 'LoadOrder'] = str(
int(LoadStepDataFrame.loc[i, 'LoadOrder']))
LoadStepDataFrame.loc[i, 'RecordCount'] = str(
int(LoadStepDataFrame.loc[i, 'RecordCount']))
LoadStepDataFrame.loc[
i, 'LoadStartDTTM'] = datetime.datetime.strptime(
LoadStepDataFrame.loc[i, 'LoadStartDTTM'],
"%Y-%m-%d %H:%M:%S.%f").strftime("%d %b %y %H:%M:%S")
LoadStepDataFrame.loc[i, 'LoadStartDTTM'] = str(
LoadStepDataFrame.loc[i,
'LoadStartDTTM']).ljust(30,
' ').upper()
m, s = divmod(LoadStepDataFrame.loc[i, 'Duration'], 60)
h, m = divmod(m, 60)
DurationTime = str(int(h)).rjust(2, '0') + ':' + str(
int(m)).rjust(2, '0') + ':' + str(int(s)).rjust(2, '0')
LoadStepDataFrame.loc[i, 'Duration'] = DurationTime
ViewLogDisplay.config(
text=
'--------------------------------------------------------------------------------'
'----------------------------------------------------------\n'
'Index\tOrder\t' + 'Status'.ljust(MaxStatus, '_') +
'\tCount\tStartTime\t\tDuration\n' +
'--------------------------------------------------------------------------------'
'----------------------------------------------------------\n'
+ LoadStepDataFrame.to_csv(
sep='\t',
index=False,
header=False,
columns=[
'LoadIndex', 'LoadOrder', 'Status', 'RecordCount',
'LoadStartDTTM', 'Duration'
]) +
'--------------------------------------------------------------------------------'
'----------------------------------------------------------')
#Display Log details from Archived files/Rejects
def ProcessLog():
ChoiceLabel.config(text='Archived Process Logs -')
ViewLogDisplay.config(text='Please Select an Archive File !!!')
var.set('')
#Clear the View Log Screen and revert to Default Main Page
def DisplayLogClear(root, photo, ImageFrame, MainPageFrame):
ImageFrame.destroy()
MainPageFrame.destroy()
MainPage(root, photo)
#Buttions to choose log type
APILogButton = ttk.Button(BottopViewFrame,
text='API Logs',
width=20,
command=lambda: APILog(ChoiceLabel))
LoadStepButton = ttk.Button(BottopViewFrame,
text='Load Step Logs',
width=20,
command=lambda: LoadStep(ChoiceLabel))
ProcessLogButton = ttk.Button(BottopViewFrame,
text='Process Logs/Rejects',
width=20,
command=ProcessLog)
BackButton = ttk.Button(BottopViewFrame,
text='Back',
width=20,
command=lambda: DisplayLogClear(
root, photo, ImageFrame, MainPageFrame))
#pack Buttons
APILogButton.pack(side=LEFT, padx=20)
LoadStepButton.pack(side=LEFT, padx=20)
ProcessLogButton.pack(side=LEFT, padx=20)
BackButton.pack(side=LEFT, padx=20)
def FetchData(root, ImageFrame, ImageLabel, photo, MainPageFrame,
FetchDataButton, ViewLogButton):
#Start the Fetching Process
InitialParameters = Initialize.getParam()
LoadFileName = InitialParameters[15]
#Disable the Button and destroy the Image
FetchDataButton.config(state=DISABLED)
ViewLogButton.config(state=DISABLED)
ImageLabel.destroy()
#Create Top and Bottom Frame
TopFetchFrame = Frame(ImageFrame)
TopFetchFrame.pack(side=TOP)
BottopFetchFrame = Frame(ImageFrame)
BottopFetchFrame.pack(side=BOTTOM)
#Label to Display Log with Scroll Bar
#Function for Scroll Bar
def ScrollLogFunc(event):
FetchCanvas.configure(scrollregion=FetchCanvas.bbox("all"),
height=370,
width=700)
#canvas to Implement Scroll Bar for Log
FetchCanvas = Canvas(TopFetchFrame)
CanvasFrame = Frame(FetchCanvas)
ScrollLogY = Scrollbar(TopFetchFrame,
orient="vertical",
command=FetchCanvas.yview)
FetchCanvas.configure(yscrollcommand=ScrollLogY.set)
FetchCanvas.pack(side=LEFT)
ScrollLogY.pack(side=RIGHT, fill=Y)
FetchCanvas.create_window((0, 0), window=CanvasFrame, anchor=NW)
#Label to display the Log Details
LogContentDisplay = Label(CanvasFrame,
text='Loading Log Details . . ..',
anchor=NW,
justify=LEFT,
wraplength=650)
CanvasFrame.bind("<Configure>", ScrollLogFunc)
LogContentDisplay.pack()
#Function to Fetch Log Details from Log File - continuesly
def DisplayLogContent():
#global LogValue
CurrentLogValue = ''
InitialParameters = Initialize.getParam()
LogFileName = InitialParameters[14]
try:
LogFile = open(LogFileName, 'r')
except Exception as e:
#print('Log File Error : ',e)
CurrentLogValue = 'Unable to Display Log.Please Wait . . .'
else:
CurrentLogValue = LogFile.read()
LogContentDisplay.config(text=CurrentLogValue)
LogContentDisplay.after(200, DisplayLogContent)
#run the Fetch Log function
DisplayLogContent()
#Clear the Fetch Data Screen and revert to Default Main Page
def DisplayLogClear(root, photo, ImageFrame, MainPageFrame):
ImageFrame.destroy()
MainPageFrame.destroy()
MainPage(root, photo)
#Button to Clear the Screen and revert back
BackButton = ttk.Button(BottopFetchFrame,
text='Back',
width=20,
command=lambda: DisplayLogClear(
root, photo, ImageFrame, MainPageFrame))
BackButton.pack(side=LEFT, padx=50)
#Runthe Update Process using thread
if os.path.isfile(LoadFileName) == True:
popupmsg('Update Already in Progress !!!')
else:
Choice = messagebox.askquestion(
"Update",
"Are You sure you want to update the Main DB Tables?",
icon='warning')
if Choice == 'yes':
threading.Thread(target=FD.main).start()
else:
DisplayLogClear(root, photo, ImageFrame, MainPageFrame)
def ViewLog(root, ImageFrame, ImageLabel, photo, MainPageFrame,ViewLogButton,FetchDataButton):
#Disable the Button and destroy the Image
ViewLogButton.config(state=DISABLED)
FetchDataButton.config(state=DISABLED)
ImageLabel.destroy()
#Create Top and Bottom Frame
TopViewFrame=Frame(ImageFrame)
TopViewFrame.pack(side=TOP)
BottopViewFrame=Frame(ImageFrame)
BottopViewFrame.pack(side=BOTTOM)
#Label to Display Log with Scroll Bar
#Function for Scroll Bar
def ScrollLogFunc(event):
ViewCanvas.configure(scrollregion=ViewCanvas.bbox("all"),height=370,width=800)
#canvas to Implement Scroll Bar for Log
ViewCanvas=Canvas(TopViewFrame)
CanvasFrame=Frame(ViewCanvas)
ScrollLogY=Scrollbar(TopViewFrame, orient="vertical",command=ViewCanvas.yview)
ViewCanvas.configure(yscrollcommand=ScrollLogY.set)
ViewCanvas.pack(side=LEFT)
ScrollLogY.pack(side=RIGHT,fill=Y)
ViewCanvas.create_window((0,0),window=CanvasFrame,anchor=NW)
#Label to display the Log Details
ViewLogDisplay=Label(CanvasFrame,text='Please Select a Log Category.',anchor=NW,justify=LEFT,wraplength=700)
CanvasFrame.bind("<Configure>",ScrollLogFunc)
ViewLogDisplay.pack(side=BOTTOM)
#DropDown Button For Archive Log
var = StringVar()
var.set('')
InitialParameters=Initialize.getParam()
LogPath=InitialParameters[19]
#LogPath=r'C:\Users\Priyank\Desktop\MIS\Scripting Languages\DataAnalyst-Project(Python)\Coding\Logs'
LogList=[]
for filename in os.listdir(LogPath):
#if filename.split('.')[-1]=='log' and filename.split('_')[0]=='DataUpdateLog':
LogList.append(filename)
def func(value):
LogFile=LogPath +'\\' + str(value)
Log = open(LogFile, 'r')
LogValue = Log.read()
ViewLogDisplay.config(text=LogValue)
ChoiceLabel.config(text='Archived Process Logs -')
ChoiceLabel=Label(CanvasFrame,text='LOGS !!!')
ChoiceLabel.pack(side=LEFT)
ChoiceDropDown = OptionMenu(CanvasFrame, var, *LogList, command=func)
ChoiceDropDown.pack(side=RIGHT)
#Function to Display the Logs based on selection
#APi and Source file info from Log table
def APILog(ChoiceLabel):
ChoiceLabel.config(text='Source File and API Logs -')
var.set('')
APLDataFrame=FirebirdDB.DisplayAPILog()
APLDataFrame['FileLen']=APLDataFrame.FileName.map(len)
MaxLength=0
for i in range(0,len(APLDataFrame)):
if APLDataFrame.loc[i,'FileLen'] >MaxLength:
MaxLength=APLDataFrame.loc[i,'FileLen']
for i in range(0,len(APLDataFrame)):
APLDataFrame.loc[i,'FileName']=str(APLDataFrame.loc[i,'FileName']).ljust(MaxLength,'_')
m,s=divmod(APLDataFrame.loc[i,'Duration'],60)
h,m=divmod(m,60)
DurationTime=str(int(h)).rjust(2,'0')+':'+str(int(m)).rjust(2,'0')+':'+str(int(s)).rjust(2,'0')
APLDataFrame.loc[i,'Duration']=DurationTime
#print(APLDataFrame.loc[i,'LoadStartDTTM'])
APLDataFrame.loc[i,'LoadStartDTTM']=datetime.datetime.strptime(APLDataFrame.loc[i,'LoadStartDTTM'], "%Y-%m-%d %H:%M:%S.%f").strftime("%d %b %y %H:%M:%S")
APLDataFrame.loc[i,'LoadStartDTTM']=str(APLDataFrame.loc[i,'LoadStartDTTM']).ljust(30,' ').upper()
#print(datetime.datetime.strptime(APLDataFrame.loc[i,'LoadStartDTTM']),"%d/%m/%Y").strftime("%d%b%y"))
APLDataFrame.loc[i,'LoadIndex']=str(int(APLDataFrame.loc[i,'LoadIndex']))
APLDataFrame.loc[i,'LoadOrder']=str(int(APLDataFrame.loc[i,'LoadOrder']))
APLDataFrame.loc[i,'RecordCount']=str(int(APLDataFrame.loc[i,'RecordCount']))
ViewLogDisplay.config(text='--------------------------------------------------------------------------------'
'-----------------------------------------------\n'
'Index\tOrder\t'+
'FileName'.ljust(MaxLength,'_')
+'\tCount\tStartTime\t\tDuration\n'+
'--------------------------------------------------------------------------------'
'-----------------------------------------------\n'+
APLDataFrame.to_csv(sep='\t',index=False,header=False,
columns=['LoadIndex','LoadOrder','FileName','RecordCount','LoadStartDTTM','Duration'])
+'--------------------------------------------------------------------------------'
'-----------------------------------------------')
#Load Step info from Main Log
def LoadStep(ChoiceLabel):
ChoiceLabel.config(text='Load Step Logs -')
var.set('')
LoadStepDataFrame=FirebirdDB.DisplayLoadStepLog()
LoadStepDataFrame['StatusLen']=LoadStepDataFrame.Status.map(len)
MaxStatus=0
for i in range(0,len(LoadStepDataFrame)):
if LoadStepDataFrame.loc[i,'StatusLen'] > MaxStatus:
MaxStatus=LoadStepDataFrame.loc[i,'StatusLen']
for i in range(0,len(LoadStepDataFrame)):
LoadStepDataFrame.loc[i,'Status']=str(LoadStepDataFrame.loc[i,'Status']).ljust(MaxStatus,'_')
LoadStepDataFrame.loc[i,'LoadIndex']=str(int(LoadStepDataFrame.loc[i,'LoadIndex']))
LoadStepDataFrame.loc[i,'LoadOrder']=str(int(LoadStepDataFrame.loc[i,'LoadOrder']))
LoadStepDataFrame.loc[i,'RecordCount']=str(int(LoadStepDataFrame.loc[i,'RecordCount']))
LoadStepDataFrame.loc[i,'LoadStartDTTM']=datetime.datetime.strptime(LoadStepDataFrame.loc[i,'LoadStartDTTM'], "%Y-%m-%d %H:%M:%S.%f").strftime("%d %b %y %H:%M:%S")
LoadStepDataFrame.loc[i,'LoadStartDTTM']=str(LoadStepDataFrame.loc[i,'LoadStartDTTM']).ljust(30,' ').upper()
m,s=divmod(LoadStepDataFrame.loc[i,'Duration'],60)
h,m=divmod(m,60)
DurationTime=str(int(h)).rjust(2,'0')+':'+str(int(m)).rjust(2,'0')+':'+str(int(s)).rjust(2,'0')
LoadStepDataFrame.loc[i,'Duration']=DurationTime
ViewLogDisplay.config(text='--------------------------------------------------------------------------------'
'----------------------------------------------------------\n'
'Index\tOrder\t'+'Status'.ljust(MaxStatus,'_')+'\tCount\tStartTime\t\tDuration\n'+
'--------------------------------------------------------------------------------'
'----------------------------------------------------------\n'+
LoadStepDataFrame.to_csv(sep='\t',index=False,header=False,
columns=['LoadIndex','LoadOrder','Status','RecordCount','LoadStartDTTM','Duration'])
+'--------------------------------------------------------------------------------'
'----------------------------------------------------------')
#Display Log details from Archived files/Rejects
def ProcessLog():
ChoiceLabel.config(text='Archived Process Logs -')
ViewLogDisplay.config(text='Please Select an Archive File !!!')
var.set('')
#Clear the View Log Screen and revert to Default Main Page
def DisplayLogClear(root, photo, ImageFrame, MainPageFrame):
ImageFrame.destroy()
MainPageFrame.destroy()
MainPage(root, photo)
#Buttions to choose log type
APILogButton = ttk.Button(BottopViewFrame, text='API Logs', width=20,
command=lambda:APILog(ChoiceLabel))
LoadStepButton = ttk.Button(BottopViewFrame, text='Load Step Logs', width=20,
command=lambda:LoadStep(ChoiceLabel))
ProcessLogButton = ttk.Button(BottopViewFrame, text='Process Logs/Rejects', width=20,
command=ProcessLog)
BackButton = ttk.Button(BottopViewFrame, text='Back', width=20,
command=lambda: DisplayLogClear(root, photo, ImageFrame, MainPageFrame))
#pack Buttons
APILogButton.pack(side=LEFT,padx=20)
LoadStepButton.pack(side=LEFT,padx=20)
ProcessLogButton.pack(side=LEFT,padx=20)
BackButton.pack(side=LEFT,padx=20)
def FetchData(root, ImageFrame, ImageLabel, photo, MainPageFrame,FetchDataButton,ViewLogButton):
#Start the Fetching Process
InitialParameters = Initialize.getParam()
LoadFileName = InitialParameters[15]
#Disable the Button and destroy the Image
FetchDataButton.config(state=DISABLED)
ViewLogButton.config(state=DISABLED)
ImageLabel.destroy()
#Create Top and Bottom Frame
TopFetchFrame=Frame(ImageFrame)
TopFetchFrame.pack(side=TOP)
BottopFetchFrame=Frame(ImageFrame)
BottopFetchFrame.pack(side=BOTTOM)
#Label to Display Log with Scroll Bar
#Function for Scroll Bar
def ScrollLogFunc(event):
FetchCanvas.configure(scrollregion=FetchCanvas.bbox("all"),height=370,width=700)
#canvas to Implement Scroll Bar for Log
FetchCanvas=Canvas(TopFetchFrame)
CanvasFrame=Frame(FetchCanvas)
ScrollLogY=Scrollbar(TopFetchFrame, orient="vertical",command=FetchCanvas.yview)
FetchCanvas.configure(yscrollcommand=ScrollLogY.set)
FetchCanvas.pack(side=LEFT)
ScrollLogY.pack(side=RIGHT,fill=Y)
FetchCanvas.create_window((0,0),window=CanvasFrame,anchor=NW)
#Label to display the Log Details
LogContentDisplay=Label(CanvasFrame,text='Loading Log Details . . ..',anchor=NW,justify=LEFT,wraplength=650)
CanvasFrame.bind("<Configure>",ScrollLogFunc)
LogContentDisplay.pack()
#Function to Fetch Log Details from Log File - continuesly
def DisplayLogContent():
#global LogValue
CurrentLogValue=''
InitialParameters = Initialize.getParam()
LogFileName = InitialParameters[14]
try:
LogFile = open(LogFileName, 'r')
except Exception as e:
#print('Log File Error : ',e)
CurrentLogValue='Unable to Display Log.Please Wait . . .'
else:
CurrentLogValue = LogFile.read()
LogContentDisplay.config(text=CurrentLogValue)
LogContentDisplay.after(200,DisplayLogContent)
#run the Fetch Log function
DisplayLogContent()
#Clear the Fetch Data Screen and revert to Default Main Page
def DisplayLogClear(root, photo, ImageFrame, MainPageFrame):
ImageFrame.destroy()
MainPageFrame.destroy()
MainPage(root, photo)
#Button to Clear the Screen and revert back
BackButton = ttk.Button(BottopFetchFrame, text='Back', width=20,
command=lambda: DisplayLogClear(root, photo, ImageFrame, MainPageFrame))
BackButton.pack(side=LEFT,padx=50)
#Runthe Update Process using thread
if os.path.isfile(LoadFileName) == True:
popupmsg('Update Already in Progress !!!')
else:
Choice=messagebox.askquestion("Update", "Are You sure you want to update the Main DB Tables?", icon='warning')
if Choice=='yes':
threading.Thread(target=FD.main).start()
else:
DisplayLogClear(root, photo, ImageFrame, MainPageFrame)
import fdb
import Initialize
InitialParameters = Initialize.getParam()
DBLocation = InitialParameters[1]
DBName = InitialParameters[6]
UserName = InitialParameters[7]
Password = InitialParameters[8]
DBPath = DBLocation + DBName
CreateComment = "create database '" + str(DBPath) + "' user '" + str(
UserName) + "' password '" + str(Password) + "'"
print('DatabasePath :', DBPath)
con = fdb.create_database(CreateComment)
print("Database (", DBName, ") created Sucessfully .")
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
cur.execute(
"recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)"
)
con.commit()
print("Table ConsumerComplaint created Sucessfully .")
cur.execute(
"create unique index unique_ComplaintID on consumercomplaint(ComplaintID)")
con.commit()
print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .")
cur.execute(
"recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)"
)
con.commit()
def PostLoadTasks(*RevertList):
if len(RevertList) == 0 :
RevertList=['S','T','L','R']
InitialParameters = Initialize.getParam()
MainLogTable = InitialParameters[18]
TableName = InitialParameters[10]
ViewName=InitialParameters[16]
LogPath=InitialParameters[19]
LoadFileName = InitialParameters[15]
LogFileName = InitialParameters[14]
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
LogTime=time.strftime("%d%b%Y_%X")
global STEP_COUNT,FAIL_FLAG
if 'S' in RevertList:
try:
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
#Swap View
FirebirdDB.ViewSwap(TableName,ViewName)
ViewCount=FirebirdDB.GetTableCount(ViewName)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Post-Post',ViewName,'View Swap back to Main Table',ViewCount,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
except Exception as e:
FailureRevert(8,e)
else :
WriteToLog('\nView Swap ('+ViewName.upper()+' --> '+TableName.upper()+') Completed.')
if 'R' in RevertList:
try:
#Checkif the Rejects Exists if so then move then to log dir
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
RejectFileName = InitialParameters[9]
StagingPath = InitialParameters[2]
RejectFile = StagingPath + RejectFileName
if sum(1 for line in open(RejectFile))==1:
WriteToLog('Zero Reject Records Found !!! ')
RejectStatus='No Rejects Found'
TotalRejectCount=0
os.remove(RejectFile)
else:
TotalRejectCount=sum(1 for line in open(RejectFile))-1
WriteToLog('Rejects Records Captured in - '+RejectFileName+' Count: '+str(TotalRejectCount))
ArchiveReject=LogPath+'RejectFile_'+LogTime.replace(':','')+'.csv'
RejectStatus='Rejects Found...Archiving the Reject File as '+ ArchiveReject.split('\\')[-1]
shutil.move(RejectFile,ArchiveReject)
WriteToLog('Reject File ('+RejectFileName+') Sucessfully Archived.')
except Exception as e:
FailureRevert(9,e)
else:
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Post-Post',RejectFileName,RejectStatus,TotalRejectCount,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
if 'T' in RevertList:
try:
#Delete Trig File
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
#remove the file
os.remove(LoadFileName)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
if 'F' in RevertList:
TrigStatus='Trig File deleted .Process Terminated due to Error !!!'
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Post-Post',LoadFileName.split('\\')[-1],TrigStatus,0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
WriteToLog('\nTrig File Deleted -- Process Terminated.')
else:
TrigStatus='Trig File deleted to indicate Data Load Status as Completed'
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Post-Post',LoadFileName.split('\\')[-1],TrigStatus,0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
WriteToLog('\nTrig File Deleted to Indicate Data Load Complete.')
except Exception as e:
FailureRevert(10,e)
if 'L' in RevertList:
try:
##Move log file
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
ArchiveLog=LogPath+'DataUpdateLog_'+LogTime.replace(':','')+'.log'
WriteToLog('Log File ('+LogFileName.split('\\')[-1]+') Archived.')
if 'F' in RevertList:
WriteToLog('\nUpdate Process Terminated due to Error !!! '+time.strftime('%d %b %Y %X'))
else:
WriteToLog('\nUpdate Process Sucessfully Completed. . . '+time.strftime('%d %b %Y %X'))
WriteToLog('\n\n\n*Note-Display will be cleared in 10 seconds.')
time.sleep(10)
shutil.move(LogFileName,ArchiveLog)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Post-Post',LogFileName.split('\\')[-1],'Log File Archived as '+ArchiveLog.split('\\')[-1],0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
WriteToLog('-- Log File Cleared/Archived !!! ---')
except Exception as e:
FailureRevert(11,e)
FAIL_FLAG=True
def LoadDataStaging():
InitialParameters = Initialize.getParam()
MainLogTable = InitialParameters[18]
MainLoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
APIView = InitialParameters[3].split(',')
APICategory = InitialParameters[4].split(',')
FilterValue = InitialParameters[17].split(',')
type_param = 'csv'
StagTableName = InitialParameters[11]
TableName = InitialParameters[10]
RejectFileName = InitialParameters[9]
StagingPath = InitialParameters[2]
RejectFile = StagingPath + RejectFileName
LogTableName=InitialParameters[13]
# create a Reject List
try:
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
RFile = open(RejectFile, 'w')
#RejectComment = "Datereceived,Product,Subproduct,Issue,Subissue,Consumercomplaintnarrative,Companypublicresponse,Company,State,ZIPcode,Submittedvia,Datesenttocompany,Companyresponsetoconsumer,Timelyresponse,Consumerdisputed,ComplaintID"
RejectComment = "Company,Companypublicresponse,Companyresponsetoconsumer,ComplaintID,Consumercomplaintnarrative,Consumerdisputed,Datereceived,Datesenttocompany,Issue,Product,State,Subissue,Submittedvia,Subproduct,Timelyresponse,ZIPcode"
RFile.write(RejectComment)
RFile.close
except Exception as e:
FailureRevert(4,e)
else:
WriteToLog('\nReject File ('+RejectFileName+') Created.')
#Update the Main Log
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
global STEP_COUNT
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Pre-Pre',RejectFileName,'Created Reject File to Capture Rejects',0,LoadStartDTTM,LoadEndDTTM)
STEP_COUNT+=1
try:
# Truncate staging db
FirebirdDB.DeleteData(StagTableName)
except Exception as e:
FailureRevert(5,e)
else :
WriteToLog('Staging Table '+StagTableName.upper()+' Truncated. \n')
try:
#Get Load Index
LoadIndex=FirebirdDB.GetLoadIndex(LogTableName)
WriteToLog('Staging Table '+StagTableName.upper()+' Loading Initiated . . . ')
WriteToLog('------------------------------------------------------------------------------------------------------------------')
# load data from files
for i in range(0, len(APICategory)):
LoadStartDTTM=str(datetime.datetime.now()).split('.')[0]
#print('InsertData(',StagTableName,',',APICategory[i].replace("'", ""))
FirebirdDB.InsertData(StagTableName, APICategory[i].replace("'", ""))
LoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
RowCount=FirebirdDB.GetRecordCount(StagTableName,FilterValue[i].replace("'", ""))
WriteToLog('Loading : '+StagTableName.upper()+' <-- '+ APICategory[i].replace("'", "")+' [Count('+str(RowCount)+')]')
FirebirdDB.UpdateLog(LogTableName,(LoadIndex+1),(i+1),APIView[i].replace("'", ""),APICategory[i].replace("'", ""),RowCount,LoadStartDTTM,LoadEndDTTM)
WriteToLog('------------------------------------------------------------------------------------------------------------------')
except Exception as e:
FailureRevert(6,e)
else:
#Update the Main Log
LoadIndex=FirebirdDB.GetLoadIndex(MainLogTable)
TableCount=FirebirdDB.GetTableCount(StagTableName)
MainLoadEndDTTM=str(datetime.datetime.now()).split('.')[0]
FirebirdDB.UpdateMainLog(MainLogTable,LoadIndex,STEP_COUNT,'Data-Load',StagTableName,'Staging Table Loaded',TableCount,MainLoadStartDTTM,MainLoadEndDTTM)
STEP_COUNT+=1
