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 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 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'
]) +
'--------------------------------------------------------------------------------'
'----------------------------------------------------------')
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'
]) +
'--------------------------------------------------------------------------------'
'-----------------------------------------------')
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
def LoadDF():
#coded for manual overide of source for evaluation purpose
if os.path.isfile(r'./ManualOverrideSource/ConsumerComplaints.csv'):
DataFrame = pd.read_csv(
r'./ManualOverrideSource/ConsumerComplaints.csv')
messagebox.showinfo(
"Alert !!!",
'Fetching Data from csv file-Manual Override In Progress')
else:
DataFrame = FirebirdDB.GetViewData()
messagebox.showinfo("Alert !!!",
'Fetching Data from Firebird Database')
#DataFrame.to_csv('ConsumerComplaints.csv',index=False)
DataFrame.DateReceived = pd.to_datetime(DataFrame.DateReceived)
DataFrame.DateSentCompany = pd.to_datetime(DataFrame.DateSentCompany)
DataFrame.ConsumerDisputedSts = DataFrame.ConsumerDisputedSts.fillna(
'Data Unavailable')
return DataFrame
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