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')