def apply_model(self, file_path: str, years: int):
if file_path is None:
return None
Tools.print_log_line('Generating initial inventory', logging.INFO)
reader: ExcelReader = ExcelReader(file_path,
DEFAULT_EXCEL_FILE_STRUCTURE)
return Inventory(reader)
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 get_data_from_excel():
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(sheet_name_or_idx)
return rows, reader.create_qry
def get_data_from_excel():
reader = ExcelReader(excel_file)
rows = reader.get_data_from_sheet(sheet_name_or_idx)
return rows, reader.create_qry
def __init__(self, config):
reader = ExcelReader(config)
dmus_2006 = reader.read_dmus("2006")
dmus_2007 = reader.read_dmus("2007")
dmus_2008 = reader.read_dmus("2008")
dmus_2009 = reader.read_dmus("2009")
dmus_2010 = reader.read_dmus("2010")
dmus_2011 = reader.read_dmus("2011")
dmus_2012 = reader.read_dmus("2012")
dmus_2013 = reader.read_dmus("2013")
dmus_2014 = reader.read_dmus("2014")
cefs = reader.read_cef("2006", "2007", "2008", "2009", "2010",
"2011", "2012", "2013", '2014')
global_dmus = [dmus_2006, dmus_2007, dmus_2008, dmus_2009, dmus_2010,
dmus_2011, dmus_2012, dmus_2013, dmus_2014]
self.lmdi_2006_2007 = Lmdi.build(dmus_2006, dmus_2007, '2006-2007', global_dmus, cefs)
self.lmdi_2007_2008 = Lmdi.build(dmus_2007, dmus_2008, '2007-2008', global_dmus, cefs)
self.lmdi_2008_2009 = Lmdi.build(dmus_2008, dmus_2009, '2008-2009', global_dmus, cefs)
self.lmdi_2009_2010 = Lmdi.build(dmus_2009, dmus_2010, '2009-2010', global_dmus, cefs)
self.lmdi_2010_2011 = Lmdi.build(dmus_2010, dmus_2011, '2010-2011', global_dmus, cefs)
self.lmdi_2011_2012 = Lmdi.build(dmus_2011, dmus_2012, '2011-2012', global_dmus, cefs)
self.lmdi_2012_2013 = Lmdi.build(dmus_2012, dmus_2013, '2012-2013', global_dmus, cefs)
self.lmdi_2013_2014 = Lmdi.build(dmus_2013, dmus_2014, '2013-2014', global_dmus, cefs)
self.spaam_2006_2007 = Spaam.build(dmus_2006, dmus_2007, '2006-2007', global_dmus, cefs)
self.spaam_2007_2008 = Spaam.build(dmus_2007, dmus_2008, '2007-2008', global_dmus, cefs)
self.spaam_2008_2009 = Spaam.build(dmus_2008, dmus_2009, '2008-2009', global_dmus, cefs)
self.spaam_2009_2010 = Spaam.build(dmus_2009, dmus_2010, '2009-2010', global_dmus, cefs)
self.spaam_2010_2011 = Spaam.build(dmus_2010, dmus_2011, '2010-2011', global_dmus, cefs)
self.spaam_2011_2012 = Spaam.build(dmus_2011, dmus_2012, '2011-2012', global_dmus, cefs)
self.spaam_2012_2013 = Spaam.build(dmus_2012, dmus_2013, '2012-2013', global_dmus, cefs)
self.spaam_2013_2014 = Spaam.build(dmus_2013, dmus_2014, '2013-2014', global_dmus, cefs)
years = {
0: '2006',
1: '2007',
2: '2008',
3: '2009',
4: '2010',
5: '2011',
6: '2012',
7: '2013',
8: '2014',
}
self.mpaam_2006_2007 = Mpaam([dmus_2006, dmus_2007], '2006-2007', global_dmus, cefs, years)
self.mpaam_2006_2008 = Mpaam([dmus_2006, dmus_2007, dmus_2008], '2006-2008', global_dmus, cefs, years)
self.mpaam_2006_2009 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
], '2006-2009', global_dmus, cefs, years)
self.mpaam_2006_2010 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
dmus_2010], '2006-2010', global_dmus, cefs, years)
self.mpaam_2006_2011 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
dmus_2010, dmus_2011], '2006-2011', global_dmus, cefs, years)
self.mpaam_2006_2012 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
dmus_2010, dmus_2011, dmus_2012], '2006-2012', global_dmus, cefs, years)
self.mpaam_2006_2013 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
dmus_2010, dmus_2011, dmus_2012, dmus_2013], '2006-2013', global_dmus, cefs, years)
self.mpaam_2006_2014 = Mpaam([dmus_2006, dmus_2007, dmus_2008, dmus_2009,
dmus_2010, dmus_2011, dmus_2012, dmus_2013,
dmus_2014], '2006-2014', global_dmus, cefs, years)
self.province_names = self.lmdi_2006_2007.province_names