def getValuesForApp4(self, CIK, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() params = {'CIK': CIK} query = text( """ select conceptName, value, endDate, explicitMemberValue from (select concept.conceptName, efv.value, p.instant as endDate, em.explicitMemberValue FROM fa_entity_fact ef join fa_entity_fact_value efv on ef.oid = efv.entityFactOID join fa_concept concept on ef.conceptOID = concept.OID join fa_period p on p.oid = efv.periodOID join fa_file_Data fd on fd.oid = efv.fileDataOID join fa_company c on c.oid = fd.companyOID join fa_ticker t on t.companyOID = c.OID join fa_explicit_member em on em.oid = efv.explicitMemberOID where c.CIK = :CIK union select 'PRICE', pri.value, p.instant as endDate, null as explicitMemberValue from fa_price pri join fa_period p on p.oid = pri.periodOID join fa_file_Data fd on fd.oid = pri.fileDataOID join fa_company c on c.oid = fd.companyOID join fa_ticker t on t.companyOID = c.OID where c.CIK = :CIK) as a order by conceptName, explicitMemberValue""") return session.execute(query, params)
def getStatusList(self, statusAttr, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .with_entities(getattr(FileData, statusAttr).distinct()) objectResult = query.all() return objectResult
def getExpressionList2(self, isCurrent, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Expression)\ .filter(Expression.isCurrent == isCurrent)\ .all() return objectResult
def getAllTicker(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Ticker)\ .with_entities(Ticker.ticker)\ .all() return objectResult
def getTickerLike(self, tickerLike, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Ticker)\ .filter(Ticker.ticker.like(tickerLike))\ .all() return objectResult
def getStatusCount2(self): session = DBConnector().getNewSession() query = text(""" SELECT fileStatus, companyStatus, if(listed=1,'LISTED','NOT_LISTED') as listedStatus, entityStatus, priceStatus, factStatus, count(*) as values_ FROM fa_file_data fd join fa_company c on c.oid = fd.companyOID group by fileStatus, companyStatus, listed, entityStatus, priceStatus, factStatus""") return session.execute(query, '')
def getErrorKeyList(self, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(ErrorMessage)\ .with_entities(ErrorMessage.errorKey.distinct()) objectResult = query.all() return objectResult
def getExpressionForReport(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Expression)\ .join(Expression.customConcept)\ .with_entities(CustomConcept.conceptName, Expression.defaultOrder, Expression.periodType, Expression.expression)\ .all() return objectResult
def getCompanyListForReport(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(Company)\ .join(Company.tickerList)\ .with_entities(Company.CIK, Company.entityRegistrantName, Company.listed, Company.notListedDescription,Ticker.ticker, Ticker.tickerOrigin, Ticker.active) objectResult = query.all() return objectResult
def getEntityFactValueList(self, fileDataOID, conceptOID, session): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(EntityFactValue)\ .join(EntityFactValue.fileData)\ .filter(and_(Concept.OID.__eq__(conceptOID), EntityFactValue.fileDataOID == fileDataOID)) objectResult = query.all() return objectResult
def getQuarterPeriodList(self, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(QuarterPeriod)\ .with_entities(QuarterPeriod.OID, functions.concat(QuarterPeriod.year, '-' ,QuarterPeriod.quarter))\ .order_by(QuarterPeriod.year.desc(), QuarterPeriod.quarter.desc()) objectResult = query.all() return objectResult
def getStatusCount3(self): session = DBConnector().getNewSession() query = text(""" SELECT factStatus, copyStatus, calculateStatus, expressionStatus, count(*) as values_ FROM fa_file_data fd join fa_company c on c.oid = fd.companyOID where c.listed = 1 group by factStatus, copyStatus, calculateStatus, expressionStatus""") return session.execute(query, '')
def getFirstResult(self, objectClazz, condition, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(objectClazz)\ .filter(condition)\ .first() return objectResult
def getLastFileData(self, session): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(func.max(FileData.fileName))\ .with_entities(FileData.fileName)\ .group_by(FileData.companyOID) objectResult = query.all() return objectResult
def getErrorList(self, fileName, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(ErrorMessage.errorKey)\ .filter(FileData.fileName == fileName)\ .with_entities(ErrorMessage.errorKey, ErrorMessage.errorMessage) return query.all()
def getCustomFact3(self, customConceptOID, customReportOID, session): try: if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(CustomFact)\ .filter(and_(CustomFact.customReportOID == customReportOID, CustomFact.customConceptOID == customConceptOID))\ .one() return objectResult except NoResultFound: return None
def getCompanyListByTicker(self, tickerList, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(Company)\ .join(Company.tickerList)\ .filter(and_(Ticker.ticker.in_(tickerList), Ticker.active == True))\ .with_entities(Company.CIK, Company.entityRegistrantName, Ticker.ticker) objectResult = query.all() return objectResult
def getErrorMessageGroup(self, errorKey, statusKey, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(ErrorMessage)\ .join(ErrorMessage.fileData)\ .filter(ErrorMessage.errorKey == errorKey)\ .with_entities(getattr(FileData, statusKey), ErrorMessage.errorMessage, func.count().label('Count'))\ .order_by(ErrorMessage.errorMessage)\ .group_by(FileData.companyStatus, ErrorMessage.errorMessage) objectResult = query.all() return objectResult
def getFileDataList4(self, ticker, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .join(FileData.company)\ .join(Company.tickerList)\ .with_entities(Company.CIK, Ticker.ticker, FileData.fileName, FileData.documentPeriodEndDate, FileData.documentType, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, FileData.fileStatus, FileData.companyStatus, FileData.entityStatus, FileData.priceStatus, FileData.factStatus, FileData.copyStatus, FileData.calculateStatus, FileData.expressionStatus)\ .order_by(FileData.documentPeriodEndDate)\ .filter(Ticker.ticker == ticker) objectResult = query.all() return objectResult
def getFileData6(self, statusAttr, statusValue, session=None, limit=None): """get FD by one attribute""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .order_by(FileData.documentPeriodEndDate)\ .filter(and_(getattr(FileData, statusAttr) == statusValue))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getCConceptAndConcept(self, session = None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(CustomConcept)\ .join(CustomConcept.relationConceptList)\ .join(RelCustomConceptConcept.concept)\ .join(CustomConcept.defaultCustomReport)\ .with_entities(CustomReport.shortName,CustomConcept.conceptName.label("CustomConceptName"), Concept.conceptName, RelCustomConceptConcept.order_, RelCustomConceptConcept.customConceptOID, RelCustomConceptConcept.conceptOID)\ .order_by(CustomReport.shortName, CustomConcept.conceptName, RelCustomConceptConcept.order_) objectResult = query.all() return objectResult
def getAllResult(self, objectClazz, condition=(1 == 1), session=None, limit=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(objectClazz)\ .filter(condition)\ .limit(limit)\ .all() return objectResult
def getFileData5(self, statusAttr, statusValue, session=None, limit=None, errorMessage2 = ''): """get FD by one attribute and error message""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(FileData.documentPeriodEndDate.desc())\ .filter(and_(getattr(FileData, statusAttr) == statusValue, or_(errorMessage2 == '', ErrorMessage.errorMessage.like(errorMessage2))))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getFileDataByError(self, errorKey, errorMessage, session=None, limit=None): """get FD by error key and error message""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(FileData.documentPeriodEndDate.desc())\ .filter(and_(ErrorMessage.errorKey== errorKey, ErrorMessage.errorMessage.like(errorMessage)))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def doImport(self): try: logging.info("START") time1 = datetime.now() if (self.skipOrProcess()): self.addOrModifyInit() if (self.replace): self.deleteImportedObject() self.logger.debug("**********START - Processing filename " + self.filename) voList = self.doImport2() persistentList = self.getPersistentList(voList) Dao().addObjectList(persistentList, self.session) self.setFileDataStatus(voList) if (voList is None): self.logger.info("***********FINISH AT " + str(datetime.now() - time1) + " " + self.filename) else: self.logger.info("***********FINISH AT " + str(datetime.now() - time1) + " " + self.filename + " objects added " + str(len(voList))) else: self.logger.info("Skipped " + self.filename) except (CustomException) as e: self.logger.error(self.filename + " " + e.status + " " + str(e)) self.addOrModifyFDError1(e) except MemoryError as e: self.logger.error(self.filename + " " + str(e)) FileDataDao().addOrModifyFileData( statusKey=self.actualStatus, statusValue=Constant.STATUS_ERROR, filename=self.filename, errorMessage='MemoryError', errorKey=self.errorKey) except ExpatError as e: self.logger.error(self.filename + " " + str(e)) self.session.rollback() self.session.close() self.session = DBConnector().getNewSession() self.addOrModifyFDError2('not well-formed') except Exception as e: self.logger.error(self.filename + " " + str(e)) self.session.rollback() self.session.close() self.session = DBConnector().getNewSession() self.addOrModifyFDError2(str(e)[0:149]) finally: self.session.commit() self.session.close()
def getFirstEntityFact(self, fileDataOID, conceptName, session): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() try: query = session.query(EntityFactValue)\ .join(EntityFactValue.explicitMember)\ .filter(and_(Concept.conceptName.__eq__(conceptName), EntityFactValue.fileDataOID == fileDataOID))\ .order_by(ExplicitMember.order_) objectResult = query.first() except NoResultFound: objectResult = None return objectResult
def getFileData3(self, statusAttr, statusValue, statusAttr2, statusValue2, session=None, limit=None, listed = ''): """get FD by two attributes and listed""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .join(FileData.company)\ .order_by(FileData.documentPeriodEndDate)\ .filter(and_(getattr(FileData, statusAttr) == statusValue, getattr(FileData, statusAttr2) == statusValue2, or_(listed == '', Company.listed == listed)))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getCustomFactValue4(self, companyOID, periodType, documentFiscalYearFocus, session = None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(CustomFactValue)\ .join(CustomFactValue.customFact)\ .join(CustomFactValue.fileData)\ .join(CustomFactValue.period)\ .filter(and_(FileData.documentFiscalYearFocus == documentFiscalYearFocus, \ FileData.companyOID == companyOID, \ Period.type.__eq__(periodType)))\ .with_entities(CustomFactValue.value, CustomFactValue.periodOID, Period.endDate, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, CustomFactValue.fileDataOID, CustomFact.customConceptOID)\ .order_by(FileData.documentPeriodEndDate) objectResult = query.all() return objectResult
def getFactValues(CIK=None, ticker=None, conceptName=None, periodType=None): dbconnector = DBConnector() with dbconnector.engine.connect() as con: params = { 'conceptName': conceptName, 'CIK': CIK, 'ticker': ticker, 'periodType': periodType } query = text( """select distinct concept.conceptName, factValue.value, IFNULL(period.endDate, period.instant) date_ FROM fa_fact fact left join fa_company company on fact.companyOID = company.OID left join fa_concept concept on fact.conceptOID = concept.OID left join fa_file_data fileData on fact.fileDataOID = fileData.OID left join fa_fact_value factValue on factValue.factOID = fact.OID left join fa_period period on factValue.periodOID = period.OID left join fa_ticker t on t.companyOID = company.OID where concept.conceptName = :conceptName and (company.CIK = :CIK or :CIK is null) and (t.ticker = :ticker or :ticker is null) and (period.type= :periodType or :periodType is null or period.type is null) order by IFNULL(period.endDate, period.instant)""" ) rs = con.execute(query, params) return rs
def getFactValue3(self, periodTypeList=None, fileDataOID=None, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() objectResult = session.query(FactValue)\ .join(FactValue.factList)\ .join(FactValue.period)\ .join(Fact.fileData)\ .filter(and_(Period.type.in_(periodTypeList), \ FileData.OID == fileDataOID))\ .with_entities(FactValue.value, FactValue.periodOID, Period.endDate, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, Fact.fileDataOID, Fact.conceptOID, Period.type)\ .all()#.distinct()\#.order_by(Period.endDate)\ return objectResult