def query(self, startPeriod, endPeriod, dealerCode, cceInterval): queryTypeDict=self.queryTypes queryType=queryTypeDict[self.whatwePullin] data=self.queryWriter(startPeriod, endPeriod, dealerCode, cceInterval, queryType) tempDF=func.runAS400Query(queryText=data, odbcVarName='DSN=as400') print(tempDF.head()) self.outputDF=pd.concat([self.outputDF, tempDF])
def as400Print(self, yyyyMMStr): self.saveFileName=self.saveFileNameFunc(yyyyMMStr) as400TestList=func.getUpdatedThisMonthResults(self.outputLoc, self.saveFileName, self.reportMonthCheckFlag) if as400TestList[0]==0: self.sqlDataReturn=func.runAS400Query(queryText=self.sqlData, odbcVarName=self.odbcVar) func.df_tocsv(self.sqlDataReturn, [self.outputLoc], self.saveFileName) func.errorLog(self.historyLogLoc, self.historyLogFileName\ , as400TestList[1]) else: pass func.errorLog(self.historyLogLoc, self.historyLogFileName\ , as400TestList[1])
def fromODBC(self): return func.runAS400Query(self.accessTableInput,\ self.msAccessodbcName['local'])
def runSamplesQuery(self): self.fullDf=func.runAS400Query(\ queryText=self.queryText,\ odbcVarName=self.odbcName)
def main(): print('\n') final = pyc.savefinancialStatementResults() queryTypes = final.queryTypes saveLocations=[ 'C://Users//HMA91571//desktop//git//financialStatements_output',\ '//hke.local/hma/Dept/Customer_Satisfaction/Service Business Development/PS_Merchandising/Contractor/Database/CCE DB Source Files' ] #iterate through financial statement parameters for x in queryTypes: #generate a class x = pyc.financialStatementsClass(x) #local variables (corresponding to class) rptYear = x.reportYYYY rptPrevYear = x.report_prevYr varPreFix = x.varPreFix bmCode = x.BFFLID #query generation queryData = func.financialStatementReportQuery(rptYear, varPreFix, bmCode) prevYearQueryData = func.financialStatementReportQuery( rptPrevYear, varPreFix, bmCode) #query execution reportYearDF = func.runAS400Query(queryText=queryData, odbcVarName=final.odbcName) reportPrevYearDF = func.runAS400Query(queryText=prevYearQueryData, odbcVarName=final.odbcName) print(reportYearDF) #split results by year, delete duplicate months reportYearDrops = x.prevYearMonths reportPrevYearDrops = x.curYearMonths #print('-----') #print(reportPrevYearDF.describe()) #print(reportPrevYearDrops) reportYearDF = reportYearDF.drop(reportYearDrops, axis=1) reportPrevYearDF = reportPrevYearDF.drop(reportPrevYearDrops, axis=1) #print(reportYearDrops) #print('-----') #print(reportPrevYearDrops) print('-----') #print(reportPrevYearDF.head()) #print('-----') #print(reportPrevYearDF.describe()) #filling out class variables x.financialStatementSplits['left'] = reportYearDF x.financialStatementSplits['right'] = reportPrevYearDF #rejoin years x.financialStatementTypeDF = func.joinDataFrames( x.financialStatementSplits, x.joinKeys) #add current month and national columns #print(x.financialStatementTypeDF.describe()) x.financialStatementTypeDF[x.curMonthName]=\ x.financialStatementTypeDF[x.curMonthVar] #print(x.financialStatementTypeDF.head()) reportTrimClass = pyc.financialStatementsTrimAndSummary( varPreFix, x.financialStatementTypeDF) #print(reportTrimClass.reportMonth) #print(reportTrimClass.reportMonthTxt) #print(list(reportTrimClass.calendarConversionKeys.values())) #print(reportTrimClass.sumMonths) #print(reportTrimClass.monthInclude) #print(reportTrimClass.outputResultsDF) trimmedParamDF = reportTrimClass.outputResultsDF del reportTrimClass #join financial statement parameter reports #create if none found if final.finalResults.empty: final.finalResults = trimmedParamDF else: final.finalResults=func.joinDataFrames({'left':final.finalResults,\ 'right':trimmedParamDF,},\ x.joinKeys) final.finalResults[final.nlColName] = final.nlColName print(final.finalResults.head()) final.finalResults=func.joinDataFrames({'left':final.accessTableDf,\ 'right':final.finalResults,},\ final.joinToAccessKeys,\ final.joinToAccessType) final.finalResults[final.dealerNameColumnName]\ =final.removeDealerNameCommas(final.finalResults[final.dealerNameColumnName]) final.saveFileNameList = final.getSaveFileName(x.reportYYYYmm) final.saveToCsv() print('\n') print('-----') print('-----') print('-----') print('-----') print('-----') print('-----') print('-----')