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