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 upload_cost(db, excel_file, sheet_name_or_idx):
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
# create other quries for attribute 19
tt_name = TempTable.create_from_query(db, qry_clone_date_to_attribute_id_19(tt_name, "Fees & Indirect Costs Date"))
count = db.execute(qry_update_last_date_of_month(tt_name))
logger.info("{} updated to ensure [date] column is last day of the month\n".format(count))
cols = db.sp_columns("{}".format(tt_name))
attribute_lookup = {col: db.get_one_value(qry_get_attribute_id(), col) for col in cols}
# remove columns not matched tblAttribute
attribute_lookup = {k: v for k, v in attribute_lookup.items() if v is not None}
# back up tblInvestmentAttribute
backup_table(db, "tblInvestmentAttribute")
for attribute_name, attribute_id in attribute_lookup.items():
attribute_tt_name = TempTable.create_from_query(
db, qry_create_temp_table_for_attribute_name(tt_name, attribute_name, attribute_id)
)
logger.info(
"create table [{}] for attribute name [{}][{}] - Qty {}".format(
attribute_tt_name, attribute_name, attribute_id, len(attribute_tt_name)
)
)
count = db.execute(qry_update_DateTo_of_tbInvestmentAttribute(attribute_tt_name))
logger.info("{} updated by closing off current attributes".format(count))
count = db.execute(qry_insert_new_investment_attributes(attribute_tt_name))
logger.info("{} inserted into tblInvestmentAttribute".format(count))
assert count == len(attribute_tt_name)
logger.info("\n")
# cleaning up junk data due to re-run
count = db.execute(qry_delete_rerun_data("tblInvestmentAttribute"))
logger.info("{} deleted in tblInvestmentAttribute due to re-run".format(count))
count = db.execute(qry_regenerate_report(tt_name, code_field="code"))
logger.info("{} updated for regenerating report".format(count))
def extract(db, excel_file, sheet_name_or_idx, data_provider, output):
# Import excel file into temp table
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
another_tt_name = TempTable.create_from_query(db, qry_get_apircode_from_citicode(tt_name))
orig_citi_codes = db.get_data('select citicode from {}'.format(tt_name))
orig_citi_codes = {code.citicode for code in orig_citi_codes}
logger.info(orig_citi_codes)
assert len(tt_name) == len(another_tt_name)
holding_tt_name = TempTable.create_from_query(db, qry_get_top_holdings(another_tt_name, data_provider))
rows = db.get_data('select * from {}'.format(holding_tt_name))
unique_citi_codes = {row[0] for row in rows}
logger.info(orig_citi_codes-unique_citi_codes)
csvwriter = csv.writer(output, lineterminator='\n')
header = 'FundProductCode,HoldingCode,HoldingName,Weight,DateFrom'
csvwriter.writerow(header.split(','))
csvwriter.writerows(rows)
def upload_cost(db, excel_file, sheet_name_or_idx):
tt_name = upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
# create other quries for attribute 19
tt_name = TempTable.create_from_query(db, qry_clone_date_to_attribute_id_19(tt_name, 'Fees & Indirect Costs Date'))
count = db.execute(qry_update_last_date_of_month(tt_name))
logger.info('{} updated to ensure [date] column is last day of the month\n'.format(count))
cols = db.sp_columns('{}'.format(tt_name))
attribute_lookup = {col: db.get_one_value(qry_get_attribute_id(), col) for col in cols}
# remove columns not matched tblAttribute
attribute_lookup = {k: v for k, v in attribute_lookup.items() if v is not None}
# back up tblInvestmentAttribute
backup_table(db, 'tblInvestmentAttribute')
for attribute_name, attribute_id in attribute_lookup.items():
attribute_tt_name = TempTable.create_from_query(db, qry_create_temp_table_for_attribute_name(tt_name
, attribute_name
, attribute_id))
logger.info('create table [{}] for attribute name [{}][{}] - Qty {}'.format(attribute_tt_name, attribute_name
, attribute_id
, len(attribute_tt_name)))
count = db.execute(qry_update_DateTo_of_tbInvestmentAttribute(attribute_tt_name))
logger.info('{} updated by closing off current attributes'.format(count))
count = db.execute(qry_insert_new_investment_attributes(attribute_tt_name))
logger.info('{} inserted into tblInvestmentAttribute'.format(count))
assert count == len(attribute_tt_name)
logger.info('\n')
# cleaning up junk data due to re-run
count = db.execute(qry_delete_rerun_data('tblInvestmentAttribute'))
logger.info('{} deleted in tblInvestmentAttribute due to re-run'.format(count))
count = db.execute(qry_regenerate_report(tt_name, code_field='code'))
logger.info('{} updated for regenerating report'.format(count))
def double_check(db, output):
all_fe_tt = TempTable.create_from_query(db, qry_get_all_fe_current_gs())
all_latest_gs_tt = TempTable.create_from_query(db, qry_get_latest_gs(all_fe_tt))
logger.info(len(all_fe_tt))
logger.info(len(all_latest_gs_tt))
data = db.get_data('''
select investmentID, investmentCode from {tt_name}
except
select investmentID, investmentCode from {tt_name_1}
'''.format(tt_name=all_fe_tt, tt_name_1=all_latest_gs_tt))
no_gs_why = [row for row in data]
logger.info('-'*40)
logger.info('Below codes do not have current growth series')
logger.info(no_gs_why)
logger.info('-'*40)
data = db.get_data(qry_compare_last_gs_with_external(all_latest_gs_tt))
data = [row for row in data]
dict_data = collections.defaultdict(list)
for row in data:
dict_data[row[0]] += [row]
final_data = []
for investmentid, row in dict_data.items():
if len(row) > 1:
logger.info(row)
continue
row = list(row[0])
row = row + [row[-3] == row[-4], row[-1] == row[-2]]
#logger.info(row)
final_data += [row]
csvwriter = csv.writer(output, lineterminator='\n')
csvwriter.writerow(['investmentID', 'investmentCode', 'Date', 'Value', 'ExternalValue', 'ValueExDiv', 'ExternalValueExDiv', 'APIRCode', 'ValueDif', 'ExDivDif'])
csvwriter.writerows(final_data)
def get_recommendation(db, etf_stock_list, output):
csvreader = csv.reader(etf_stock_list)
stocks = [x for x in csvreader]
create_table_qry = """
create table {table_name} (
stockCode varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS
)
"""
tt_name = TempTable.create_from_data(db, stocks, create_table_qry)
logger.info(tt_name)
rows = db.get_data(qry_get_recommendation(tt_name))
csvwriter = csv.writer(output, lineterminator="\n")
csvwriter.writerow(["StockCode", "Recommendation", "DateFrom"])
csvwriter.writerows(rows)
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 double_check(db, output):
report_id = 11
tt_name = TempTable.create_from_query(db, qry_get_fund_hierarchy())
data = db.get_data(qry_get_investment_detail(tt_name))
csvwriter = csv.writer(output, lineterminator='\n')
header = ['FundID', 'ParentFundID', 'Instrumentid', 'Apircode', 'InvestmentFullName', 'InvestmentStatus'
, 'IsWholesale', 'isMainTaxStructure', 'Recommendation', 'RecommendationDateFrom', 'RecommendationStatus']
csvwriter.writerow(header)
def cleanup(row):
if row[0] == row[1]:
row[1] = ''
return row
data = [cleanup(row) for row in data]
csvwriter.writerows(data)
def upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx):
def get_data_from_excel():
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(sheet_name_or_idx)
return rows, reader.create_qry
tt_name = TempTable.create_from_data(db, *get_data_from_excel())
try:
# Extract records in [ExternalData].dbo.tblGrowthSeries that matches InvestmentID in temp table imported above
date_value = db.get_one_value('select top 1 [date] from {}'.format(tt_name))
if not isinstance(date_value, datetime.datetime):
if isinstance(date_value, str):
logger.info('Convert varchar to datetime for [date] column')
# update table to yyyy-mm-dd format before convert to datetime type
db.execute('''
update {}
set [date]=right([date],4)+'-'+SUBSTRING([date], 4, 2) + '-' + left([date],2)
'''.format(tt_name))
elif isinstance(date_value, float):
logger.info('Convert float to datetime for [date] column')
# SQL Server counts its dates from 01/01/1900 and Excel from 12/30/1899 = 2 days less.
# update table to yyyy-mm-dd format before convert to datetime type
db.execute('''
alter table {tt_name}
alter column [date] varchar(20)
'''.format(tt_name=tt_name))
db.execute('''
update {tt_name}
set date=cast(date - 2 as datetime)
'''.format(tt_name=tt_name))
db.execute('''
alter table {}
alter column [date] date
'''.format(tt_name))
except pyodbc.ProgrammingError:
logger.info("{tt_name} does not have date column".format(tt_name=tt_name))
return tt_name
def upload_top10(db, excel_file, sheet_name_or_idx, data_provider):
tt_name = helper.upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx)
unique_tt_name = TempTable.create_from_query(db, qry_get_distinct_code_date(tt_name))
# back up ExternalData.dbo.tblUnderlyingFund
backup_table(db, 'ExternalData.dbo.tblUnderlyingFund')
count = db.execute(qry_update_fund_name_tblUnderlyingFund(unique_tt_name))
logger.info('{} fund name updated in tblUnderlyingFund'.format(count))
count = db.execute(qry_insert_new_fund_tblUnderlyingFund(unique_tt_name, data_provider))
logger.info('{} fund name inserted in tblUnderlyingFund'.format(count))
count = db.execute(qry_update_DateFrom_tblUnderlyingFund(unique_tt_name))
logger.info('{} fund name updated with latest date in tblUnderlyingFund'.format(count))
assert count == len(unique_tt_name)
# close off any open holdings for funds where changed
# back up ExternalData.dbo.tblTopHoldings
backup_table(db, 'ExternalData.dbo.tblTopHoldings')
count = db.execute(qry_update_DateTo_of_current_holdings(unique_tt_name))
logger.info('{} updated by closing off any open holdings for funds where changed'.format(count))
# add the new holding code
backup_table(db, 'ExternalData.dbo.tblHolding')
count = db.execute(qry_add_new_holding_name(tt_name, data_provider))
logger.info('{} updated by adding the new holding code'.format(count))
# add new holding
count = db.execute(qry_add_new_holdings(tt_name, data_provider))
logger.info('{} updated by adding the new holding'.format(count))
assert count == len(tt_name)
count = db.execute(qry_regenerate_report(tt_name))
logger.info('{} updated for regenerating report'.format(count))
# cleaning up junk data due to re-run
count = db.execute(qry_delete_rerun_data('ExternalData..tblTopHoldings'))
logger.info('{} deleted in ExternalData..tblTopHoldings due to re-run'.format(count))
def process(db, excel_file, dry_run):
# Import excel file into temp table
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(0)
tt_name = TempTable.create_from_data(db, rows, reader.create_qry)
# Extract records in [ExternalData].dbo.tblGrowthSeries that matches InvestmentID in temp table imported above
count = db.execute(import_growth_series_query(tt_name))
assert len(tt_name) == count
# back up tblInvestmentGrowthSeries
today = datetime.date.today()
today = today.strftime('%Y%m%d')
if not db.sp_columns('tblInvestmentGrowthSeries_{date}'.format(date=today)):
# if table does not exist
logger.info('Creating tblInvestmentGrowthSeries_{date}'.format(date=today))
db.execute('select * into tblInvestmentGrowthSeries_{date} from tblInvestmentGrowthSeries'.format(date=today))
# get investment id from [ExternalData].dbo.tblGrowthSeries
investment_id = db.get_one_value('select top 1 code from [ExternalData].dbo.ImportGrowthSeries')
logger.info(investment_id)
logger.info('Before updating')
logger.info(db.get_data('select top 1 * from tblInvestmentGrowthSeries where investmentid=? order by [date] desc'
, investment_id))
count = db.execute(merge_to_tblInvestmentGrowthSeries_query())
logger.info("{} records updated in tblInvestmentGrowthSeries".format(count))
logger.info('After updating')
logger.info(db.get_data('select top 1 * from tblInvestmentGrowthSeries where investmentid=? order by [date] desc'
, investment_id))
if not dry_run:
logger.info('Commit changes')
db.commit()
else:
logger.info('All changes did not commit')
def upload_excel_to_tempdb(db, excel_file, sheet_name_or_idx):
def get_data_from_excel():
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(sheet_name_or_idx)
return rows, reader.create_qry
tt_name = TempTable.create_from_data(db, *get_data_from_excel())
# Extract records in [ExternalData].dbo.tblGrowthSeries that matches InvestmentID in temp table imported above
date_value = db.get_one_value('select top 1 [date] from {}'.format(tt_name))
if not isinstance(date_value, datetime.datetime):
if isinstance(date_value, str):
logger.info('Convert varchar to datetime for [date] column')
# update table to yyyy-mm-dd format before convert to datetime type
db.execute('''
update {}
set [date]=right([date],4)+'-'+SUBSTRING([date], 4, 2) + '-' + left([date],2)
'''.format(tt_name))
elif isinstance(date_value, float):
logger.info('Convert float to datetime for [date] column')
# SQL Server counts its dates from 01/01/1900 and Excel from 12/30/1899 = 2 days less.
# update table to yyyy-mm-dd format before convert to datetime type
db.execute('''
alter table {tt_name}
alter column [date] varchar(20)
'''.format(tt_name=tt_name))
db.execute('''
update {tt_name}
set date=cast(date - 2 as datetime)
'''.format(tt_name=tt_name))
db.execute('''
alter table {}
alter column [date] date
'''.format(tt_name))
return tt_name
header = 'ClientID,ModuleID,ModuleName'
write_data(r'C:\Users\Lmai\Documents\Workspaces\users_subscription_20150917.csv', data, header)
# get client advisor codes
advisor_codes = db.get_data(qry_get_broker_advisor_codes(clientids))
advisor_codes = [list(zip(repeat(code[0]), code[1].split(','))) for code in advisor_codes]
print(advisor_codes)
advisor_codes = list(chain(*advisor_codes))
if advisor_codes:
create_qry = '''
create table {table_name} (
clientid int,
advisercode varchar(10) collate Latin1_General_CI_AS
)
'''
tt_name = TempTable.create_from_data(octopus_db, advisor_codes, create_qry)
rows = octopus_db.get_data(qry_get_advcode(tt_name))
orig_clientids = {x for x in clientids}
out_clientids = {row[0] for row in rows}
print("Below users do not have advcode")
print(orig_clientids - out_clientids)
with open(r'C:\Users\Lmai\Documents\Workspaces\test_clientadvcodes.csv', 'w') as f:
csvwriter = csv.writer(f, lineterminator='\n')
csvwriter.writerow(['userid', 'advcode'])
csvwriter.writerows(rows)
clientmoduleids_lookup = defaultdict(list)
rows = []
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')
