class ORSOutput(ORSSheets):
"""A module to publish results tables, and restore default values to
user input fields."""
def __init__(self, ORSCalculatorInstance, xlInstance):
self.ORS = ORSCalculatorInstance
#self.xlInst = xlInstance
self.Sheet = Sheet(xlInstance)
self.InputSheet = "Input"
self.CalculationSheet = "Calculation"
self.StatsSheet = "Statistics"
self.NetworkName = self.ORS.networknames[0]
self.DateOrigin = pos(row=4, col=1) # The postion to place the fist date
def Populate_Reliability_Stats(self):
"""Write the 2015 determination statistics to a prebuilt table in excel.
Used on the 'Statistics' sheet."""
# Table origin is the top left
ComComOrigin = pos(row=4, col=1)
CalcOrigin = pos(row=4, col=9)
# The row order of the values being calcualted by the ORSCalculator
# TODO: The row text must match the values _get_stats() is expecting, we may need to introduce some aliases to make it more readable
RowHeadings = [["UBV"], ["LIMIT"], ["TARGET"], ["COLLAR"], ["CAP"]]
CalcRowValues = [self.ORS._get_stats(heading[0]) for heading in RowHeadings] # ORS calcualted values
CCRowValues = [self.ORS._get_CC_stats(heading[0]) for heading in RowHeadings] # Com Com hard coded values
# Work out how many columns we need to offset these SAIDI, SAIFI results
NetworkOrder = ["ELIN", "OTPO", "TPCO"] # The order the networks are presented going across the table in excel
try:
Offset = NetworkOrder.index(self.NetworkName)
except ValueError:
print "%s is not a recognised network" % self.NetworkName
Offset = 0 # Default option
Offset *= 2
# Table 1
self.Sheet.setRange(self.StatsSheet, ComComOrigin.row, ComComOrigin.col, RowHeadings) # Write the row headings into the table
ComComOrigin.col += 1 # Incriment the next column (i.e. don't overwrite row headings)
row = ComComOrigin.row
for rowValue in CalcRowValues:
# The SAIDI/SAIFI values are written to excel in the order that they are returned by the ORSCalculator function
self.Sheet.setRange(self.StatsSheet, row, ComComOrigin.col+Offset, [rowValue])
row += 1
# Table 2
self.Sheet.setRange(self.StatsSheet, CalcOrigin.row, CalcOrigin.col, RowHeadings)
CalcOrigin.col += 1 # Incriment the next column (i.e. don't overwrite row headings)
row = CalcOrigin.row
for rowValue in CCRowValues:
# The SAIDI/SAIFI values are written to excel in the order that they are returned by the ORSCalculator function
self.Sheet.setRange(self.StatsSheet, row, CalcOrigin.col+Offset, [rowValue])
row += 1
def Create_Summary_Table(self):
"""Create a summary sheet in excel, delete it if it already exisits"""
# Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks
self.Sheet.rmvSheet(removeList=[self.OutputFileName])
self.Sheet.addSheet(self.OutputFileName)
def Summary_Table(self, suffix):
"""Publish a summary of the year-to-date stats at the bottom of every sheet"""
suffix = " " + suffix
network = self.ORS.networknames[0]
currentDate = datetime.datetime.now()
RowHeadings = ["CAP", "TARGET", "COLLAR"] # The order the rows appear in the Excel spreadsheet
TableHeadings = ["YTD Cap", "YTD Target", "YTD Collar", "YTD Total", "YTD Planned", "YTD Unplanned", "Projected Incentive/Penalty"]
columns = [1, 2, 3, 4, 5]
if network == "ELIN":
RowOffset = 2
ColOffset = 1
elif network == "OTPO":
RowOffset = 2 + 12
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) + 1
elif network == "TPCO":
RowOffset = 2 + 2*12
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) * (len(self.NetworkHeadings) - 1) + 1
maxrow = self.Sheet.getMaxRow(self.CalculationSheet+suffix, 1, 4)
self.Sheet.setRange("Summary", maxrow + RowOffset, 1, [[network]+TableHeadings]) # Write the heading data
# Find the row that corrosponds to the current date
Dates = self.Sheet.getRange(self.CalculationSheet+suffix, 4, 1, maxrow, 1)
Dates = [self.Sheet.getDateTime(Date[0]) for Date in Dates] # Convert a 2D list of tuples to a 1D list
try:
index = Dates.index( datetime.datetime(currentDate.year, currentDate.month, currentDate.day) )
except ValueError:
index = len(Dates) - 1
for param in self.IndexHeadings:
# Read the entire row of data
YTD_row = self.Sheet.getRange(self.CalculationSheet+suffix, index+4, 1, index+4,
self.Sheet.getMaxCol(self.CalculationSheet+suffix, 2, 3))[0]
# Convert the row data to: CAP, TARGET, COLLAR, YTD Total, YTD Planned, YTD Unplanned
#YTD_row[ColOffset : len(DataHeadings)+ColOffset+1]
i = self.IndexHeadings.index(param)
TableRow = [YTD_row[ColOffset], YTD_row[ColOffset+1], YTD_row[ColOffset+2],
YTD_row[ColOffset+3] + YTD_row[ColOffset+4], YTD_row[ColOffset+3],
[0.5*CC_Revenue_At_Risk.get(network, 0)/(self.ORS._get_stats("CAP")[i] - self.ORS._get_stats("TARGET")[i])]]
RowOffset += 1
self.Sheet.setRange("Summary", maxrow + RowOffset, 1, [[param]+TableRow]) # Write the heading data
ColOffset += len(self.DataHeadings)
Table = []
Table.append(["Revenue at risk", CC_Revenue_At_Risk.get(network, "No Revenue Found")]) # Revenue at Risk
Table.append(["Total Number of ICPs", self.ORS._get_total_customers(Dates[index])]) # Total Number of ICPs
Table.append(["Year to date figures as of", Dates[index]]) # Date
self.Sheet.setRange("Summary", maxrow + RowOffset+1, 1, Table)
def Generate_Values(self, enddate, startdate=None):
"""
Generate dictionary key value pairs for "Rob's" template - suitable for commercial team.
@param enddate: Date to stop counting SAIDI and SAIFI for the YTD figures.
@param startdate: Defaults to the 1/4/<1 year less than end>. Date to start counting SAIDI and SAIFI for YTD figures.
@return: A dictionary of the keyword spesfic to the ORS instance currently being handled
This functoin will work with date ranges larger than 1 year, but the CC_SAIDI_YTD/CC_SAIFI_YTD interpolations will
be meainingless as these are intended to be scaled over 1 whole year starting on 1/4/n and ending on 31/3/n+1.
"""
if not startdate:
year = self.ORS._get_fiscal_year(enddate) - 1 # Function returns the fiscal year (int) for the given date
startdate = datetime.datetime(year, 4, 1)
else:
year = self.ORS._get_fiscal_year(startdate) - 1
# Some checks... any future dates will be redfined as the present date
assert startdate < enddate, "The end date is set before the startdate"
now = datetime.datetime.now()
if now < enddate:
enddate = datetime.datetime(now.year, now.month, now.day)
year = self.ORS._get_fiscal_year(enddate) - 1
# Fixed sheet data
name = self.Rename_Network(self.NetworkName) + "_"
params = {}
params[name+"DATE_END"] = enddate
params[name+"CUST_NUM"] = self.ORS._get_total_customers(enddate)
# Annual data
Dates = self.Generate_Dates(startdate, enddate)
Dates = [Date[0] for Date in Dates]
SAIDI_, SAIFI_ = self._Calc_Rows(Dates, self.ORS) # (planned, unplanned, unplanned normed) for the given dates
params[name+"SAIDI_NORMED_OUT"] = np.sum(SAIDI_, 0)[2]
params[name+"SAIFI_NORMED_OUT"] = np.sum(SAIFI_, 0)[2]
params[name+"SAIDI_UNPLANNED"] = np.sum(SAIDI_, 0)[1]
params[name+"SAIFI_UNPLANNED"] = np.sum(SAIFI_, 0)[1]
params[name+"SAIDI_PLANNED"] = np.sum(SAIDI_, 0)[0]
params[name+"SAIFI_PLANNED"] = np.sum(SAIFI_, 0)[0]
params[name+"RAW_PLANNED"] = np.sum([self.ORS._get_num_faults(date, "planned") for date in Dates])
params[name+"RAW_UNPLANNED"] = np.sum([self.ORS._get_num_faults(date, "unplanned") for date in Dates])
params[name+"RAW_NUM_MAJOR_EVENTS_SAIDI"] = len(self.ORS.get_capped_days(Dates[0], Dates[-1])[0])
params[name+"RAW_NUM_MAJOR_EVENTS_SAIFI"] = len(self.ORS.get_capped_days(Dates[0], Dates[-1])[1])
# Monthly data (present month)
Dates = self.Generate_Dates(datetime.datetime(enddate.year, enddate.month, 1), enddate)
Dates = [Date[0] for Date in Dates]
SAIDI_, SAIFI_ = self._Calc_Rows(Dates, self.ORS) # (planned, unplanned, unplanned normed) for the given dates
params[name+"SAIDI_MONTH_NORMED_OUT"] = np.sum(SAIDI_, 0)[2]
params[name+"SAIFI_MONTH_NORMED_OUT"] = np.sum(SAIFI_, 0)[2]
params[name+"SAIDI_MONTH_UNPLANNED"] = np.sum(SAIDI_, 0)[1]
params[name+"SAIFI_MONTH_UNPLANNED"] = np.sum(SAIFI_, 0)[1]
params[name+"SAIDI_MONTH_PLANNED"] = np.sum(SAIDI_, 0)[0]
params[name+"SAIFI_MONTH_PLANNED"] = np.sum(SAIFI_, 0)[0]
params[name+"RAW_MONTH_PLANNED"] = np.sum([self.ORS._get_num_faults(date, "planned") for date in Dates])
params[name+"RAW_MONTH_UNPLANNED"] = np.sum([self.ORS._get_num_faults(date, "unplanned") for date in Dates])
params[name+"RAW_MONTH_NUM_MAJOR_EVENTS_SAIDI"] = len(self.ORS.get_capped_days(Dates[0], Dates[-1])[0])
params[name+"RAW_MONTH_NUM_MAJOR_EVENTS_SAIFI"] = len(self.ORS.get_capped_days(Dates[0], Dates[-1])[1])
# Com Com Interpolations (could use np.linspace)
SAIDI_TARGET, SAIFI_TARGET = self.ORS._get_CC_stats("TARGET")
num_days = (datetime.datetime(year+1, 3, 31) - datetime.datetime(year, 3, 31)).days
x_days = (enddate - startdate).days
SAIDI_M = SAIDI_TARGET/num_days
SAIFI_M = SAIFI_TARGET/num_days
params[name+"CC_SAIDI_YTD"] = SAIDI_M * (1 + x_days)
params[name+"CC_SAIFI_YTD"] = SAIFI_M * (1 + x_days)
params[name+"CC_SAIDI_TARGET"] = CC_Vals.get(self.NetworkName).get("SAIDI_TARGET")
params[name+"CC_SAIFI_TARGET"] = CC_Vals.get(self.NetworkName).get("SAIFI_TARGET")
params[name+"CC_SAIDI_CAP"] = CC_Vals.get(self.NetworkName).get("SAIDI_CAP")
params[name+"CC_SAIFI_CAP"] = CC_Vals.get(self.NetworkName).get("SAIFI_CAP")
params[name+"CC_SAIDI_COLLAR"] = CC_Vals.get(self.NetworkName).get("SAIDI_COLLAR")
params[name+"CC_SAIFI_COLLAR"] = CC_Vals.get(self.NetworkName).get("SAIFI_COLLAR")
params[name+"CC_REV_RISK"] = CC_Revenue_At_Risk.get(self.NetworkName) * 0.01
return params
class ORSOutput(ORSSheets):
"""A module to publish results tables, and restore default values to
user input fields."""
def __init__(self, ORSCalculatorInstance, xlInstance):
self.ORS = ORSCalculatorInstance
#self.xlInst = xlInstance
self.Sheet = Sheet(xlInstance)
self.InputSheet = "Input"
self.CalculationSheet = "Calculation"
self.StatsSheet = "Statistics"
self.NetworkName = self.ORS.networknames[0]
self.DateOrigin = pos(row=4,
col=1) # The postion to place the fist date
def Populate_Reliability_Stats(self):
"""Write the 2015 determination statistics to a prebuilt table in excel.
Used on the 'Statistics' sheet."""
# Table origin is the top left
ComComOrigin = pos(row=4, col=1)
CalcOrigin = pos(row=4, col=9)
# The row order of the values being calcualted by the ORSCalculator
# TODO: The row text must match the values _get_stats() is expecting, we may need to introduce some aliases to make it more readable
RowHeadings = [["UBV"], ["LIMIT"], ["TARGET"], ["COLLAR"], ["CAP"]]
CalcRowValues = [
self.ORS._get_stats(heading[0]) for heading in RowHeadings
] # ORS calcualted values
CCRowValues = [
self.ORS._get_CC_stats(heading[0]) for heading in RowHeadings
] # Com Com hard coded values
# Work out how many columns we need to offset these SAIDI, SAIFI results
NetworkOrder = [
"ELIN", "OTPO", "TPCO"
] # The order the networks are presented going across the table in excel
try:
Offset = NetworkOrder.index(self.NetworkName)
except ValueError:
print "%s is not a recognised network" % self.NetworkName
Offset = 0 # Default option
Offset *= 2
# Table 1
self.Sheet.setRange(
self.StatsSheet, ComComOrigin.row, ComComOrigin.col,
RowHeadings) # Write the row headings into the table
ComComOrigin.col += 1 # Incriment the next column (i.e. don't overwrite row headings)
row = ComComOrigin.row
for rowValue in CalcRowValues:
# The SAIDI/SAIFI values are written to excel in the order that they are returned by the ORSCalculator function
self.Sheet.setRange(self.StatsSheet, row,
ComComOrigin.col + Offset, [rowValue])
row += 1
# Table 2
self.Sheet.setRange(self.StatsSheet, CalcOrigin.row, CalcOrigin.col,
RowHeadings)
CalcOrigin.col += 1 # Incriment the next column (i.e. don't overwrite row headings)
row = CalcOrigin.row
for rowValue in CCRowValues:
# The SAIDI/SAIFI values are written to excel in the order that they are returned by the ORSCalculator function
self.Sheet.setRange(self.StatsSheet, row, CalcOrigin.col + Offset,
[rowValue])
row += 1
def Create_Summary_Table(self):
"""Create a summary sheet in excel, delete it if it already exisits"""
# Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks
self.Sheet.rmvSheet(removeList=[self.OutputFileName])
self.Sheet.addSheet(self.OutputFileName)
def Summary_Table(self, suffix):
"""Publish a summary of the year-to-date stats at the bottom of every sheet"""
suffix = " " + suffix
network = self.ORS.networknames[0]
currentDate = datetime.datetime.now()
RowHeadings = ["CAP", "TARGET", "COLLAR"
] # The order the rows appear in the Excel spreadsheet
TableHeadings = [
"YTD Cap", "YTD Target", "YTD Collar", "YTD Total", "YTD Planned",
"YTD Unplanned", "Projected Incentive/Penalty"
]
columns = [1, 2, 3, 4, 5]
if network == "ELIN":
RowOffset = 2
ColOffset = 1
elif network == "OTPO":
RowOffset = 2 + 12
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) + 1
elif network == "TPCO":
RowOffset = 2 + 2 * 12
ColOffset = len(self.IndexHeadings) * len(
self.DataHeadings) * (len(self.NetworkHeadings) - 1) + 1
maxrow = self.Sheet.getMaxRow(self.CalculationSheet + suffix, 1, 4)
self.Sheet.setRange(
"Summary", maxrow + RowOffset, 1,
[[network] + TableHeadings]) # Write the heading data
# Find the row that corrosponds to the current date
Dates = self.Sheet.getRange(self.CalculationSheet + suffix, 4, 1,
maxrow, 1)
Dates = [self.Sheet.getDateTime(Date[0])
for Date in Dates] # Convert a 2D list of tuples to a 1D list
try:
index = Dates.index(
datetime.datetime(currentDate.year, currentDate.month,
currentDate.day))
except ValueError:
index = len(Dates) - 1
for param in self.IndexHeadings:
# Read the entire row of data
YTD_row = self.Sheet.getRange(
self.CalculationSheet + suffix, index + 4, 1, index + 4,
self.Sheet.getMaxCol(self.CalculationSheet + suffix, 2, 3))[0]
# Convert the row data to: CAP, TARGET, COLLAR, YTD Total, YTD Planned, YTD Unplanned
#YTD_row[ColOffset : len(DataHeadings)+ColOffset+1]
i = self.IndexHeadings.index(param)
TableRow = [
YTD_row[ColOffset], YTD_row[ColOffset + 1],
YTD_row[ColOffset + 2],
YTD_row[ColOffset + 3] + YTD_row[ColOffset + 4],
YTD_row[ColOffset + 3],
[
0.5 * CC_Revenue_At_Risk.get(network, 0) /
(self.ORS._get_stats("CAP")[i] -
self.ORS._get_stats("TARGET")[i])
]
]
RowOffset += 1
self.Sheet.setRange("Summary", maxrow + RowOffset, 1,
[[param] + TableRow]) # Write the heading data
ColOffset += len(self.DataHeadings)
Table = []
Table.append([
"Revenue at risk",
CC_Revenue_At_Risk.get(network, "No Revenue Found")
]) # Revenue at Risk
Table.append([
"Total Number of ICPs",
self.ORS._get_total_customers(Dates[index])
]) # Total Number of ICPs
Table.append(["Year to date figures as of", Dates[index]]) # Date
self.Sheet.setRange("Summary", maxrow + RowOffset + 1, 1, Table)
def Generate_Values(self, enddate, startdate=None):
"""
Generate dictionary key value pairs for "Rob's" template - suitable for commercial team.
@param enddate: Date to stop counting SAIDI and SAIFI for the YTD figures.
@param startdate: Defaults to the 1/4/<1 year less than end>. Date to start counting SAIDI and SAIFI for YTD figures.
@return: A dictionary of the keyword spesfic to the ORS instance currently being handled
This functoin will work with date ranges larger than 1 year, but the CC_SAIDI_YTD/CC_SAIFI_YTD interpolations will
be meainingless as these are intended to be scaled over 1 whole year starting on 1/4/n and ending on 31/3/n+1.
"""
if not startdate:
year = self.ORS._get_fiscal_year(
enddate
) - 1 # Function returns the fiscal year (int) for the given date
startdate = datetime.datetime(year, 4, 1)
else:
year = self.ORS._get_fiscal_year(startdate) - 1
# Some checks... any future dates will be redfined as the present date
assert startdate < enddate, "The end date is set before the startdate"
now = datetime.datetime.now()
if now < enddate:
enddate = datetime.datetime(now.year, now.month, now.day)
year = self.ORS._get_fiscal_year(enddate) - 1
# Fixed sheet data
name = self.Rename_Network(self.NetworkName) + "_"
params = {}
params[name + "DATE_END"] = enddate
params[name + "CUST_NUM"] = self.ORS._get_total_customers(enddate)
# Annual data
Dates = self.Generate_Dates(startdate, enddate)
Dates = [Date[0] for Date in Dates]
SAIDI_, SAIFI_ = self._Calc_Rows(
Dates, self.ORS
) # (planned, unplanned, unplanned normed) for the given dates
params[name + "SAIDI_NORMED_OUT"] = np.sum(SAIDI_, 0)[2]
params[name + "SAIFI_NORMED_OUT"] = np.sum(SAIFI_, 0)[2]
params[name + "SAIDI_UNPLANNED"] = np.sum(SAIDI_, 0)[1]
params[name + "SAIFI_UNPLANNED"] = np.sum(SAIFI_, 0)[1]
params[name + "SAIDI_PLANNED"] = np.sum(SAIDI_, 0)[0]
params[name + "SAIFI_PLANNED"] = np.sum(SAIFI_, 0)[0]
params[name + "RAW_PLANNED"] = np.sum(
[self.ORS._get_num_faults(date, "planned") for date in Dates])
params[name + "RAW_UNPLANNED"] = np.sum(
[self.ORS._get_num_faults(date, "unplanned") for date in Dates])
params[name + "RAW_NUM_MAJOR_EVENTS_SAIDI"] = len(
self.ORS.get_capped_days(Dates[0], Dates[-1])[0])
params[name + "RAW_NUM_MAJOR_EVENTS_SAIFI"] = len(
self.ORS.get_capped_days(Dates[0], Dates[-1])[1])
# Monthly data (present month)
Dates = self.Generate_Dates(
datetime.datetime(enddate.year, enddate.month, 1), enddate)
Dates = [Date[0] for Date in Dates]
SAIDI_, SAIFI_ = self._Calc_Rows(
Dates, self.ORS
) # (planned, unplanned, unplanned normed) for the given dates
params[name + "SAIDI_MONTH_NORMED_OUT"] = np.sum(SAIDI_, 0)[2]
params[name + "SAIFI_MONTH_NORMED_OUT"] = np.sum(SAIFI_, 0)[2]
params[name + "SAIDI_MONTH_UNPLANNED"] = np.sum(SAIDI_, 0)[1]
params[name + "SAIFI_MONTH_UNPLANNED"] = np.sum(SAIFI_, 0)[1]
params[name + "SAIDI_MONTH_PLANNED"] = np.sum(SAIDI_, 0)[0]
params[name + "SAIFI_MONTH_PLANNED"] = np.sum(SAIFI_, 0)[0]
params[name + "RAW_MONTH_PLANNED"] = np.sum(
[self.ORS._get_num_faults(date, "planned") for date in Dates])
params[name + "RAW_MONTH_UNPLANNED"] = np.sum(
[self.ORS._get_num_faults(date, "unplanned") for date in Dates])
params[name + "RAW_MONTH_NUM_MAJOR_EVENTS_SAIDI"] = len(
self.ORS.get_capped_days(Dates[0], Dates[-1])[0])
params[name + "RAW_MONTH_NUM_MAJOR_EVENTS_SAIFI"] = len(
self.ORS.get_capped_days(Dates[0], Dates[-1])[1])
# Com Com Interpolations (could use np.linspace)
SAIDI_TARGET, SAIFI_TARGET = self.ORS._get_CC_stats("TARGET")
num_days = (datetime.datetime(year + 1, 3, 31) -
datetime.datetime(year, 3, 31)).days
x_days = (enddate - startdate).days
SAIDI_M = SAIDI_TARGET / num_days
SAIFI_M = SAIFI_TARGET / num_days
params[name + "CC_SAIDI_YTD"] = SAIDI_M * (1 + x_days)
params[name + "CC_SAIFI_YTD"] = SAIFI_M * (1 + x_days)
params[name + "CC_SAIDI_TARGET"] = CC_Vals.get(
self.NetworkName).get("SAIDI_TARGET")
params[name + "CC_SAIFI_TARGET"] = CC_Vals.get(
self.NetworkName).get("SAIFI_TARGET")
params[name + "CC_SAIDI_CAP"] = CC_Vals.get(
self.NetworkName).get("SAIDI_CAP")
params[name + "CC_SAIFI_CAP"] = CC_Vals.get(
self.NetworkName).get("SAIFI_CAP")
params[name + "CC_SAIDI_COLLAR"] = CC_Vals.get(
self.NetworkName).get("SAIDI_COLLAR")
params[name + "CC_SAIFI_COLLAR"] = CC_Vals.get(
self.NetworkName).get("SAIFI_COLLAR")
params[name +
"CC_REV_RISK"] = CC_Revenue_At_Risk.get(self.NetworkName) * 0.01
return params
class ParseORS(object):
NetworkNames = ["ELIN", "TPCO", "OTPO", "LLNW"] # The order the network names appear in the input spreadsheet
InputSheet = "Input" # The name of the Excel sheet used for user input
Months = ["March (yr 0)", "April", "May", "June", "July", "August", "September", "October",
"November", "December", "January", "February", "March (yr 1)"] # The months in a fiscal year
def __init__(self, xlInstance):
self.xlInst = xlInstance
self.Sheet = Sheet(xlInstance)
# Create some dictionaries to hold customer numbers
self.Customer_Nums = {}
for name in self.NetworkNames:
self.Customer_Nums[name] = {}
# Create a ordered list of network charts/tables to display
self.StartDates = []
def Read_Num_Cust(self):
"""Read the average number of customers"""
o = pos(row=3, col=1)
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
# Get the number of customers - fill up the dictionaries
for row in Table:
cell = row.__iter__()
year = cell.next().year # Skip to column B
cell.next() # Skip over column C
for name in self.NetworkNames:
self.Customer_Nums[name][year] = cell.next() # Get the value of the current cell and incriment the address pointer
return self.Customer_Nums
def _Read_Last_Date(self):
"""Read the last date the user has input data for"""
o = pos(row=3, col=1)
# Read the last date in the yearly table
FinalDate = self.Sheet.getCell(self.InputSheet, o.col,
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
return self.Sheet.getDateTime(FinalDate)
def Read_Num_Cust_Final(self):
"""Read the number of customers in the final year only.
This is data from the by month table."""
o = pos(row=3, col=1)
MonthlyICPs = {}
# Read the last date in the yearly table
FinalDate = self._Read_Last_Date()
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
def Read_Dates_To_Publish(self):
"""Read the dates that require results to be outputted.
Returns the date range expected by the ORS calcualtor."""
o = pos(row=3, col=1)
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
# Get the number of customers - fill up the dictionaries
for row in Table:
if row[-1] == "Y" or row[-1] == "y":
self.StartDates.append(datetime.datetime(row[0].year-1, 4, 1)) # Substract 1 from the end of fiscal year to get the start of the year
if len(self.StartDates):
return min(self.StartDates), datetime.datetime(max(self.StartDates).year+1, 3, 31) # Add 1 back to the year, so we get the end of fiscal year
else:
return None, None
def Read_Last_Date(self):
"""Read (get) the last date field"""
o = pos(row=17, col=12)
lastdate = self.Sheet.getCell(self.InputSheet, o.row, o.col)
try:
return self.Sheet.getDateTime(lastdate)
except AttributeError:
# The user entered an invalid date
return datetime.datetime.now()
def Restore_Input_Default(self):
"""Restore the default state of the table that has all the ICP counts for each network"""
o = pos(row=3, col=1)
OutputTable = []
for year in CUST_NUMS.get(self.NetworkNames[0]):
RowData = [datetime.datetime(year, 3, 31), year-1]
for name in self.NetworkNames:
RowData.append(CUST_NUMS.get(name).get(year))
#RowData.append("Y") # The default option is to display the sheet
OutputTable.append(RowData)
# Sort the rows in the table so that we have the dates in cronological order
OutputTable = sorted(OutputTable, key=lambda e: e[0], reverse=False)
# Set the output range in excel
self.Sheet.setRange(self.InputSheet, o.row-1, o.col+2, [self.NetworkNames])
self.Sheet.setRange(self.InputSheet, o.row, o.col, OutputTable)
def Set_Mean_ICPs(self, lastyear):
"""Sets the mean (average) number of ICPS in the annual table from
records in the monthly table. The date 31/03/XXXX must appear in the
main annual data table, otherwise no updates will occur."""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Table Rows: [Month name, Year of months occurance, self.NetworkNames[]]
# Find the average number of ICPs
coloffset = 2 # Offset of ICP data from the left most column in the table
maxrow = 15 # Last row in the table
for network in self.NetworkNames:
# Find the number of records in the monthly table
lastrow = self.Sheet.getMaxRow(self.InputSheet, coloffset + o_table2.col, o_table2.row)
if lastrow > maxrow:
lastrow = o_table2.row
# Compute the average number of ICPs (using the first and last row in the monthly table,
# in the case of only a single month, then the first and last row are the same).
avrg = (self.Sheet.getCell(self.InputSheet, lastrow, coloffset + o_table2.col) + \
self.Sheet.getCell(self.InputSheet, o_table2.row, coloffset + o_table2.col)) / 2
# Place the average in the specified record
# Input param replcaed the line below
#lastyear = int(self.Sheet.getCell(self.InputSheet, o_table2.row, o_table2.col + 1)) + 1
try:
lastrow = self.Sheet.brief_search(self.InputSheet, "31/03/"+str(lastyear+1)).Row
self.Sheet.setCell(self.InputSheet, lastrow, coloffset + o_table1.col, avrg)
except:
pass
coloffset += 1
def Restore_Table_2(self, lastyear=None):
"""Builds the table for gathering ICP data by month"""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Get the row for the last date in the annual data table (table1)
lastrow = self.Sheet.getMaxRow(self.InputSheet, o_table1.col, o_table1.row)
if lastrow > 10000:
lastrow = o_table1.row
# If lastyear is None, then the last row in year in the last row of the
# annual data table (table1) is used.
if not lastyear:
lastyear = self.Sheet.getDateTime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col)).year
# Build the two left most column of table 2 (Months names, Fiscal year)
rowindex = 0
# Iterate over an array of months (arranged in order for a fiscal year)
for month in self.Months:
fiscalyear = lastyear
if self.Months.index("December") >= self.Months.index(month):
# We are in first 3/4 of the year
fiscalyear -= 1
# Write each row in table2 (first two columns only), for example, "March (yr0), 2016"
self.Sheet.setRange(self.InputSheet, o_table2.row+rowindex, o_table2.col, [(month, fiscalyear)])
rowindex += 1
# DON'T DO THIS! This will copy the last years average... we want the number of ICPs
# as of the 31st March on the previous fiscal year instead...
# Automatically copy the previous years data to record 0
##lastrow -= 1 # Get the previous years data
##if lastrow <= o_table1.row:
## lastrow = o_table1.row
##previousrange= self.Sheet.getRange(self.InputSheet, lastrow, o_table1.col+2, lastrow, o_table1.col+5)
##self.Sheet.setRange(self.InputSheet, o_table2.row, o_table2.col+2, previousrange)
def Set_Year(self, year):
"""Used to update the table labels at the end/start of a new fiscal year"""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Try and find the year in table1, otherwise create a new row(s)
lastrow = self.Sheet.getMaxRow(self.InputSheet, o_table1.col, o_table1.row)
if lastrow > 10000:
lastrow = o_table1.row
try:
lastyear = self.Sheet.getDateTime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col)).year
except:
lastyear = datetime.datetime.strptime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col),
"%d/%m/%Y").year
# Change the labels on table 2 to match the selected year
self.Restore_Table_2(year+1)
while lastyear <= year:
# We need to create some new rows in table1
lastrow += 1
lastyear += 1
self.Sheet.setRange(self.InputSheet, lastrow, o_table1.col, [(datetime.datetime(lastyear, 3, 31), lastyear-1)])