def double_check(db):
report_id = 24
helper.backup_table(db, 'tblInvestmentReport')
remove_report(db, report_id)
data = db.get_data(qry_get_all_recommendation_funds())
fund_ids = []
for row in data:
fund_id = row[1]
report_url = row[2]
if report_url:
fund_ids += [[fund_id]]
#logger.info('{}\t{}'.format(fund_id, report_url))
#count = add_report(db, fund_id, report_id, report_url, analyst_id=56526, authoriser_id=56036)
#logger.info('Adding report for {} - {}'.format(fund_id, count))
create_table_query = '''
create table {table_name} (
id int
)
'''
tt_name = TempTable.create_from_data(db, fund_ids, create_table_query)
data = db.get_data('''
select tt.ID, ve.stockCode as reportURL
from {tt_name} tt
left join vewEquities ve on tt.ID = ve.StockID
left join tblInvestmentReport ir on tt.ID = ir.InvestmentID and ir.reportID={report_id} and ir.isActive=1
where ir.reportURL is null
'''.format(tt_name=tt_name, report_id=report_id))
for row in data:
fund_id = row[0]
report_url = row[1]
count = add_report(db, fund_id, report_id, report_url, analyst_id=56526, authoriser_id=56036)
logger.info('Adding report for {} - {}'.format(fund_id, count))
def delists(db, investment_code, recommendation, investment_type_id, approved_by):
today = datetime.date.today()
today = today.strftime('%Y-%m-%d')
helper.backup_table(db, 'ExternalData..tblStockEarningsDividends')
helper.backup_table(db, 'ExternalData..tblStockEarnings')
investment_codes = investment_code.split(',')
investment_ids = [get_investment_id(db, x) for x in investment_codes]
params = (zip(investment_ids, investment_codes, repeat(recommendation), repeat(investment_type_id), repeat(today), repeat(approved_by)))
return any([delist(db, *param) for param in params])
def double_check(db):
report_id = 33
helper.backup_table(db, 'tblFundReport')
remove_report(db, report_id)
data = db.get_data(qry_get_all_recommendation_funds())
fund_ids = []
for row in data:
fund_id = row[2]
inherit_from_fund = row[3]
report_url = row[-2]
if report_url:
fund_ids += [[fund_id]]
#logger.info('{}\t{}'.format(fund_id, report_url))
count = add_report(db, fund_id, report_id, report_url, analyst_id=56526, authoriser_id=56036)
logger.info('Adding report for {} - {}'.format(fund_id, count))
def process(db, excel_file, sheet_name_or_idx):
helper.backup_table(db, 'tblInvestmentGrowthSeries')
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
unique_investmentids = db.get_one_value(qry_unique_investment_id(tt_name))
logger.info(unique_investmentids)
current_gs_tt_name = TempTable.create_from_query(db, qry_current_growth_series(tt_name))
qry_turn_off_gs = partial(qry_update_gs, 0, current_gs_tt_name)
qry_turn_on_gs = partial(qry_update_gs, 1, current_gs_tt_name)
assert unique_investmentids == len(current_gs_tt_name)
db.execute(qry_turn_off_gs()) # ignore count
db.execute(qry_turn_on_gs()) # ignore count
def upload(db, excel_file, sheet_name_or_idx, data_provider, upload_type):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
fy = helper.get_financial_year()
logger.info(tt_name)
if 'hybrids' in upload_type:
rename(db, tt_name, 'Code', 'StockCode')
rename(db, tt_name, 'Annual DPS', 'DPS')
rename(db, tt_name, '% FRANKED', 'PERCENT_FRANKED')
elif 'model' in upload_type:
rename(db, tt_name, 'Stock', 'StockCode')
rename(db, tt_name, 'FY16 DPS', 'DPS')
rename(db, tt_name, 'FY16 EPS', 'EPS')
rename(db, tt_name, 'FY16 EPS GROWTH', 'EPSGrowth')
rename(db, tt_name, '% FRANKED', 'PERCENT_FRANKED')
rename(db, tt_name, 'FY16 PER', 'PER')
elif 'ubs' in upload_type:
rename(db, tt_name, 'Franking', 'PERCENT_FRANKED')
count = db.get_one_value('''
select count(*)
from {tt_name}
where PERCENT_FRANKED > 1
'''.format(tt_name=tt_name))
if count > 0:
db.execute('''
update {tt_name}
set PERCENT_FRANKED = PERCENT_FRANKED / 100
'''.format(tt_name=tt_name))
# back up ExternalData.dbo.tblGrowthSeries
helper.backup_table(db, 'ExternalData.dbo.tblStockEarnings')
rows = db.get_data('''
select *
from ExternalData.dbo.tblStockEarnings
where investmentCode in (select top 2 stockCode from {tt_name}) and FinancialYear={fy}
'''.format(tt_name=tt_name, fy=fy))
for row in rows:
logger.info(row)
qry_update = qry_update_model_stock_code if 'model' in upload_type else qry_update_stock_code
qry_update = qry_update_ubs_stock_code if 'ubs' in upload_type else qry_update
logger.info('Data provider {}'.format(data_provider))
count = db.execute(qry_update(tt_name, fy, data_provider))
logger.info(count)
if upload_type in ['model']:
count = db.execute(qry_update(tt_name, fy, data_provider=1))
logger.info('{} updated with 1'.format(count))
count1 = db.execute(qry_delete_lonsec_if_ubs(tt_name, fy))
logger.info(str(count1)+' deleted')
logger.info('{} updated/inserted into External.dbo.tblStockEarnings'.format(count))
rows = db.get_data('''
select *
from ExternalData.dbo.tblStockEarnings
where investmentCode in (select top 2 stockCode from {tt_name}) and FinancialYear={fy}
'''.format(tt_name=tt_name, fy=fy))
for row in rows:
logger.info(row)
if count < len(tt_name):
logger.info('Some stock codes are not available on vewEquities, they are needed to added')
rows = db.get_data('''
select tt_name.StockCode
from {tt_name} tt_name
left join vewEquities ve on tt_name.StockCode = ve.StockCode
where ve.StockID is null
'''.format(tt_name=tt_name))
stocks = [row[0] for row in rows]
logger.info('They are: {}'.format(', '.join(stocks)))
def add(db, from_report_id, to_report_id, report_type):
table_name = 'tbl{report_type}'.format(report_type=report_type)
helper.backup_table(db, table_name)
if from_report_id != to_report_id:
# from report 1 to report 35
# update report 1 to
direct_property_sector_tt_name = TempTable.create_from_query(db, qry_get_direct_property_sector())
report_tt_name = TempTable.create_from_query(db, qry_get_existing_fund_update(table_name, from_report_id,
direct_property_sector_tt_name))
data = db.get_data('''
select tt.*, isnull(f.APIRCode, f.instrumentid) as newReportURL
from {tt_name} tt
left join vewISF_Fund f on tt.fundid = f.fundid and f.IsMainTaxStructure=1
'''.format(tt_name=report_tt_name))
assert len(report_tt_name) == len(data)
ok = []
errors = []
no_recommd = []
#data = data[:1]
for row in data:
fund_id = row.FundID
parent_fund_id = get_parent_id(db, fund_id)
report_url = get_report_url(db, parent_fund_id)
logger.info('{} has parent ID of {}'.format(fund_id, parent_fund_id))
if report_url is None:
logger.info('{} {} has less than 1 main tax structure'.format('-'*80, parent_fund_id))
errors += [fund_id]
else:
urllink = 'https://reports.lonsec.com.au/FV/{}'.format(report_url)
logger.info('Checking {} exists'.format(urllink))
is_link_exists = link_exists(urllink) if fund_id in [9356,8416,8432,14703,12883,548,15933,16086] else True
if is_link_exists:
logger.info('----> OK')
ok += [fund_id]
#Lonsec..prcFundReportPut @fundid=4912, @reportid=33, @reportURL='YOC0100AU',@IsActive=1
# ,@AnalystID=56526, @AuthoriserID=56036
count = add_report(db, fund_id, to_report_id, report_url, analyst_id=56526, authoriser_id=56036)
logger.info('{} inserted'.format(count))
elif not does_fund_has_recom(db, fund_id):
no_recommd += [fund_id]
else:
logger.info('----> REPORT MISSING')
errors += [fund_id]
logger.info('GOOD: {}'.format(len(ok)))
logger.info('ERROR: {}'.format(len(errors)))
logger.info('---> {}'.format(','.join(map(str, errors))))
logger.info('NO RECOMENDATIONS OR SCREENOUT: {}'.format(len(no_recommd)))
logger.info('---> {}'.format(','.join(map(str, no_recommd))))
assert len(ok) + len(errors) + len(no_recommd) == len(data)
# mark all of fund_id from from_report_id to deactive
all_fund_ids = ok + errors + no_recommd
count = db.execute(qry_deactive_old_report(from_report_id, all_fund_ids))
logger.info('{} is marked inactive'.format(count))
# due to trigger, count may not return correct value
# ignore for now
#assert count == len(data)
elif from_report_id == 24: # etf view point
# investmentreport, just change to stock code
data = db.get_data(qry_get_existing_investment_viewpoints(table_name, from_report_id))
data = [(row.InvestmentID, row.StockCode) for row in data]
errors = []
ok = []
no_reports = []
for investment_id, stock_code in data:
if stock_code:
urllink = 'https://reports.lonsec.com.au/FV/{}'.format(stock_code)
logger.info('Checking {} exists'.format(urllink))
is_link_exists = link_exists(urllink)
if is_link_exists:
logger.info('----> OK')
count = db.execute(qry_update_investment_viewpoint(investment_id, stock_code, to_report_id))
logger.info('{} updated'.format(count))
ok += [investment_id]
else:
logger.info('----> REPORT MISSING')
no_reports += [investment_id]
else:
errors += [investment_id]
logger.info('GOOD: {}'.format(len(ok)))
logger.info('NO REPORTS: {}'.format(len(no_reports)))
logger.info('---> {}'.format(','.join(map(str, no_reports))))
logger.info('ERROR: {} (No stock codes)'.format(len(errors)))
logger.info('---> {}'.format(','.join(map(str, errors))))
assert len(ok) + len(errors) + len(no_reports) == len(data)
# mark all of fund_id from from_report_id to deactive
all_investment_ids = errors + no_reports
count = db.execute(qry_deactive_invalid_investment_report(from_report_id, all_investment_ids))
logger.info('{} is marked inactive'.format(count))
elif from_report_id == 11: # fund profile
data = db.get_data(qry_get_existing_fund_profile(table_name, from_report_id))
data = [(row.InvestmentID, row.StockCode) for row in data]
errors = []
ok = []
no_reports = []
for investment_id, stock_code in data:
if stock_code:
urllink = 'https://reports.lonsec.com.au/FP/{}'.format(stock_code)
logger.info('Checking {} exists'.format(urllink))
#is_link_exists = link_exists(urllink)
is_link_exists = True
if is_link_exists:
logger.info('----> OK')
count = db.execute(qry_update_investment_viewpoint(investment_id, stock_code, to_report_id))
logger.info('{} updated'.format(count))
ok += [investment_id]
else:
logger.info('----> REPORT MISSING')
no_reports += [investment_id]
else:
errors += [investment_id]
logger.info('GOOD: {}'.format(len(ok)))
logger.info('NO REPORTS: {}'.format(len(no_reports)))
logger.info('---> {}'.format(','.join(map(str, no_reports))))
logger.info('ERROR: {} (No stock codes)'.format(len(errors)))
logger.info('---> {}'.format(','.join(map(str, errors))))
assert len(ok) + len(errors) + len(no_reports) == len(data)
# mark all of fund_id from from_report_id to deactive
all_investment_ids = errors + no_reports
if all_investment_ids:
count = db.execute(qry_deactive_invalid_investment_report(from_report_id, all_investment_ids))
logger.info('{} is marked inactive'.format(count))
else:
raise Exception('not implemented')
