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 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 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 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 = []