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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 getCustomFactValue5(self, fillStrategy = '', ticker = '', session = None):
try:
if (session is None):
dbconnector = DBConnector()
session = dbconnector.getNewSession()
objectResult = session.query(CustomFactValue)\
.join(CustomFactValue.customFact)\
.join(CustomFactValue.fileData)\
.join(CustomFact.customConcept)\
.join(FileData.company)\
.join(Company.tickerList)\
.filter(and_(or_(fillStrategy == '', CustomConcept.fillStrategy.__eq__(fillStrategy)), or_(ticker == '', Ticker.ticker.__eq__(ticker))))\
.all()
return objectResult
except NoResultFound:
return None
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
def getOneResult(self,
objectClazz,
condition=(1 == 1),
session=None,
raiseNoResultFound=True):
if (session is None):
dbconnector = DBConnector()
session = dbconnector.getNewSession()
try:
objectResult = session.query(objectClazz)\
.filter(condition)\
.one()
except NoResultFound as e:
if (raiseNoResultFound):
raise Exception(str(e) + " " + str(objectClazz))
return None
return objectResult
def getEntityFactValueForReport(self, CIK, session=None):
if (session is None):
dbconnector = DBConnector()
session = dbconnector.getNewSession()
try:
query = session.query(EntityFactValue)\
.join(EntityFactValue.explicitMember)\
.join(EntityFactValue.fileData)\
.join(EntityFactValue.period)\
.join(FileData.company)\
.filter(and_(Company.CIK.__eq__(CIK)))\
.order_by(func.ifnull(Period.endDate, Period.instant),ExplicitMember.order_)\
.with_entities(func.date_format(func.ifnull(Period.endDate, Period.instant), '%Y-%m-%d').label("period"),func.format(EntityFactValue.value, 0).label("value"), ExplicitMember.explicitMemberValue, ExplicitMember.order_)
objectResult = query.all()
except NoResultFound:
objectResult = None
return objectResult
def importMasterIndexFor(self,
period,
replaceMasterFile,
session=None,
threadNumber=1):
dbconnector = DBConnector()
if (session is None):
session = dbconnector.getNewSession()
localURL = Constant.CACHE_FOLDER + 'master' + str(
period.year) + "-Q" + str(period.quarter) + '.gz'
secURL = "https://www.sec.gov/Archives/edgar/full-index/" + str(
period.year) + "/QTR" + str(period.quarter) + "/master.gz"
print(localURL, secURL)
file = getBinaryFileFromCache(localURL, secURL, replaceMasterFile)
with gzip.open(BytesIO(file), 'rb') as f:
file_content = f.read()
text = file_content.decode("ISO-8859-1")
text = text[text.find("CIK", 0, len(text)):len(text)]
point1 = text.find("\n")
point2 = text.find("\n", point1 + 1)
text2 = text[0:point1] + text[point2:len(text)]
df = pandas.read_csv(StringIO(text2), sep="|")
df.set_index("CIK", inplace=True)
df.head()
print("STARTED FOR PERIOD " + str(period.year) + "-" +
str(period.quarter))
for row in df.iterrows():
CIK = row[0]
filename = row[1]["Filename"]
formType = row[1]["Form Type"]
if (formType == "10-Q" or formType == "10-K"):
fd = FileDataDao.getFileData(filename, session)
if (fd is None):
company = CompanyEngine().getOrCreateCompany(
CIK=CIK, session=session)
fd = FileData()
fd.fileName = filename
fd.company = company
Dao().addObject(objectToAdd=fd,
session=session,
doCommit=True)
print("FD Added " + filename)
print("FINISHED")
