def __init__(self, ORSCalculatorInstance, xlInstance): self.ORS = ORSCalculatorInstance self.xlInst = xlInstance self.Sheet = Sheet(self.xlInst) self.InputSheet = "Input" self.CalculationSheet = "Calculation" self.StatsSheet = "Statistics" self.Graphs = XlGraphs(xlInstance, self.Sheet) self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins) self.srcColumnOffset = 1 # The number of columns that the data is offset by # Set the order of the headings and data in the sheet self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"] self.IndexHeadings = ["SAIDI", "SAIFI"] self.DateOrigin = pos( row=4, col=1) # The postion to place the fist date on the Excel sheet # Graph offsets and dimentions self.ChartDimentions = pos(x=700, y=480) self.ChartOrigins = [] x = 85 y = 100 for network in self.NetworkHeadings: self.ChartOrigins.append(pos(x=x, y=y)) x += self.ChartDimentions.x # Shift the charts for the next network to the right
def YTD_Book(self, filename, *params): """Create a new workbook with PNL Commercial Summary table""" params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) # Open the new file in the same instance for speed self.remove_file(filename) # Don't try opeing in exsting instance if the file does not exist, you get a com_error self.OutputFileHandle = Excel(BookVisible=0, Visible=1, filename=filename) # newinstance=1 xlSheetSrc = Sheet(self.OutputFileHandle) xlSheetSrc.renameSheet(self.OutputFileName) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template( os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", xlSheetSrc._getCell(self.OutputFileName, 1, 1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file self.OutputFileHandle.save(filename) self.OutputFileHandle.closeWorkbook()
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 __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 __init__(self, ORSCalculatorInstance, xlInstance): self.ORS = ORSCalculatorInstance self.xlInst = xlInstance self.Sheet = Sheet(self.xlInst) self.InputSheet = "Input" self.CalculationSheet = "Calculation" self.StatsSheet = "Statistics" self.Graphs = XlGraphs(xlInstance, self.Sheet) self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins) self.srcColumnOffset = 1 # The number of columns that the data is offset by # Set the order of the headings and data in the sheet self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"] self.IndexHeadings = ["SAIDI", "SAIFI"] self.DateOrigin = pos(row=4, col=1) # The postion to place the fist date on the Excel sheet # Graph offsets and dimentions self.ChartDimentions = pos(x=700, y=480) self.ChartOrigins = [] x = 85 y = 100 for network in self.NetworkHeadings: self.ChartOrigins.append(pos(x=x, y=y)) x += self.ChartDimentions.x # Shift the charts for the next network to the right
def YTD_Book(self, filename, *params): """Create a new workbook with PNL Commercial Summary table""" params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) # Open the new file in the same instance for speed self.remove_file(filename) # Don't try opeing in exsting instance if the file does not exist, you get a com_error self.OutputFileHandle = Excel(BookVisible=0, Visible=1, filename=filename) # newinstance=1 xlSheetSrc = Sheet(self.OutputFileHandle) xlSheetSrc.renameSheet(self.OutputFileName) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template(os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", xlSheetSrc._getCell(self.OutputFileName, 1 ,1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file self.OutputFileHandle.save(filename) self.OutputFileHandle.closeWorkbook()
class ORSPlots(object): # ORSCalculator """A class to create the SAIDI/SAIFI charts from the ORS data.""" Timestamp = 'Date' Cap = 'Cap/Limit' Target = 'Target' Collar = 'Collar' Planned = 'Planned' Unplanned = 'Unplanned Normalised' CapUnplanned = 'Unplanned (Normalised Out)' # Set the order of the headings and data in the sheet NetworkHeadings = ["ELIN", "OTPO", "TPCO"] IndexHeadings = ["SAIDI", "SAIFI"] DataHeadings = [Cap, Target, Collar, Planned, Unplanned, CapUnplanned] def __init__(self, ORSCalculatorInstance, xlInstance): self.ORS = ORSCalculatorInstance self.xlInst = xlInstance self.Sheet = Sheet(self.xlInst) self.InputSheet = "Input" self.CalculationSheet = "Calculation" self.StatsSheet = "Statistics" self.Graphs = XlGraphs(xlInstance, self.Sheet) self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins) self.srcColumnOffset = 1 # The number of columns that the data is offset by # Set the order of the headings and data in the sheet self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"] self.IndexHeadings = ["SAIDI", "SAIFI"] self.DateOrigin = pos( row=4, col=1) # The postion to place the fist date on the Excel sheet # Graph offsets and dimentions self.ChartDimentions = pos(x=700, y=480) self.ChartOrigins = [] x = 85 y = 100 for network in self.NetworkHeadings: self.ChartOrigins.append(pos(x=x, y=y)) x += self.ChartDimentions.x # Shift the charts for the next network to the right def __repr__(self): """Return an identifying instance of the network being handled""" return "in the __repr__ for ORSPlots" + self.ORS.networknames[0] def Annual_Sheets(self, dates): """Create a new sheet for every indvidual year in the ORS results""" for date in dates: year = str(date.year) self.Create_Sheet(year) self.Fill_Dates(date, year) self.Populate_Fixed_Stats( year) # Com Com table values scaled linearly self.Populate_Daily_Stats(date, year) # Daily real world SAIDI/SAIDI self.Create_Graphs(date) def Clean_Workbook(self): self.Sheet.rmvSheet(keepList=[self.InputSheet, self.StatsSheet]) def Create_Sheets(self, dates): """Creates all the sheets needed in the workbook. Autofills the dates. All other data is network specific""" for date in dates: year = str(date.year) self.Create_Sheet(year) self.Fill_Dates(date, year) def Create_Sheet(self, suffix): """Create the "Calculation" sheet in excel, delete it if it already exisits""" suffix = " " + suffix # Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks self.Sheet.rmvSheet(removeList=[self.CalculationSheet + suffix]) self.Sheet.addSheet(self.CalculationSheet + suffix) DataOrigin = pos(row=4, col=1) LeftHeadings = [self.Timestamp] TableHeadings = LeftHeadings + \ len(self.NetworkHeadings) * len(self.IndexHeadings) * self.DataHeadings # Assign row 3 data self.Sheet.setRange( self.CalculationSheet + suffix, DataOrigin.row - 1, DataOrigin.col, [TableHeadings]) # Write the row headings into the table self.Sheet.mergeCells(self.CalculationSheet + suffix, 1, DataOrigin.col, DataOrigin.row - 1, DataOrigin.col) # Date Cells # Assign row 2 data col = DataOrigin.col + len(LeftHeadings) columns = list( np.linspace( col, col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 1) * len(self.DataHeadings), len(self.NetworkHeadings) * len(self.IndexHeadings))) index = 0 for col in columns: self.Sheet.setRange( self.CalculationSheet + suffix, DataOrigin.row - 2, int(col), [[self.IndexHeadings[index % len(self.IndexHeadings)]]] ) # self.IndexHeadings[int((col-len(LeftHeadings)) % len(self.IndexHeadings))] index += 1 self.Sheet.mergeCells( self.CalculationSheet + suffix, 2, col, 2, col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / (len(self.NetworkHeadings) * len(self.IndexHeadings))) # Row 2 # Assign row 1 data col = DataOrigin.col + len(LeftHeadings) columns = list( np.linspace( col, col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 2) * len(self.DataHeadings), len(self.NetworkHeadings))) index = 0 for col in columns: self.Sheet.setRange( self.CalculationSheet + suffix, DataOrigin.row - 3, int(col), [[self.NetworkHeadings[index % len(self.NetworkHeadings)]]]) index += 1 self.Sheet.mergeCells( self.CalculationSheet + suffix, 1, col, 1, col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / len(self.NetworkHeadings)) # Row 1 def Generate_Dates(self, startdate, enddate=None): """Generate an array of all the days for 1 fincial year""" if type(enddate) != datetime.datetime: enddate = datetime.datetime(startdate.year + 1, 3, 31) dt = datetime.timedelta(1) # A time delta of 1 day TimeStamps = [] while startdate <= enddate: TimeStamps.append([startdate]) startdate = startdate + dt return TimeStamps def Fill_Dates(self, date, suffix): """Fill the date column in the Excel sheet with the date values read from the parser. Also performs and autofit (width) and freezes panes.""" suffix = " " + suffix row, col = \ self.Sheet.setRange(self.CalculationSheet+suffix, self.DateOrigin.row, self.DateOrigin.col, self.Generate_Dates(date)) self.Sheet.autofit(self.CalculationSheet + suffix, self.DateOrigin.col) ActiveWindow = self.xlInst.xlApp.Windows(self.xlInst.xlBook.Name) ActiveWindow.SplitColumn = 1 ActiveWindow.SplitRow = 3 ActiveWindow.FreezePanes = True def _Correct_Graph_Slope(self, suffix, enddate=datetime.datetime.now()): """When the data is truncated for stacked area charts we need two of the same end dates in a row to create a vertical drop on the graph, otherwise it slopes with delta x = one time interval""" suffix = " " + suffix searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day) searchterm = searchterm.strftime( str(searchterm.day) + "/%m/%Y" ) # Get rid of the zero padding (%e and %-d don't work on Windows) maxrow = self.Sheet.getMaxRow(self.CalculationSheet + suffix, 1, 4) results = self.Sheet.search( shtRange(self.CalculationSheet + suffix, None, 4, 1, maxrow, 1), searchterm) if len(results) == 1: # If there is only one date, we haven't corrected slope yet so do it now self.Sheet.setCell(self.CalculationSheet + suffix, results[0].Row + 1, 1, results[0].Value) def _Correct_Graph_Axis(self, ChartName, enddate=datetime.datetime.now()): """Adds the final end of year date to the x axis e.g. 31/3/xxxx""" self.Graphs.Set_Max_X_Value(ChartName, enddate) def Populate_Fixed_Stats(self, suffix): """Create series values for the Limit, Cap, and Collar. Populate the excel sheet with these values.""" suffix = " " + suffix network = self.ORS.networknames[0] if network == "ELIN": ColOffset = 0 elif network == "OTPO": ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) elif network == "TPCO": ColOffset = len(self.IndexHeadings) * len( self.DataHeadings) * (len(self.NetworkHeadings) - 1) RowHeadings = ["CAP", "TARGET", "COLLAR" ] # The order the rows appear in the Excel spreadsheet self.Sheet.set_calculation_mode("manual") # Assumes that the data will start in col 2 i.e. col 1 is for the dates Column = 2 + ColOffset OriginCol = Column for param in self.IndexHeadings: for heading in RowHeadings: LinearRange = np.linspace( 0, self.ORS._get_CC_stats(heading)[self.IndexHeadings.index( param)], self.Sheet.getMaxRow(self.CalculationSheet + suffix, 1, 4) - 3 + 1)[1:] self.Sheet.setRange(self.CalculationSheet + suffix, 4, Column, [[i] for i in LinearRange]) Column += 1 Column += len(self.DataHeadings) - len(RowHeadings) self.Sheet.set_calculation_mode("automatic") def _Calc_Rows(self, dates, ORS): SAIDIcol = [] SAIFIcol = [] for day in dates: SAIDIrow = [] SAIFIrow = [] x, y = ORS._get_indicies(day, "planned", applyBoundary=True) SAIDIrow.append(x) SAIFIrow.append(y) x, y = ORS._get_indicies(day, "unplanned", applyBoundary=True) SAIDIrow.append(x) SAIFIrow.append(y) x, y = ORS._get_indicies(day, "unplanned", applyBoundary=False) SAIDIrow.append(x - SAIDIrow[1]) SAIFIrow.append(y - SAIFIrow[1]) SAIDIcol.append(SAIDIrow) SAIFIcol.append(SAIFIrow) # Here for debugging only #row=4 #self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIrow]) #self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIrow]) #row += 1 return SAIDIcol, SAIFIcol def Populate_Daily_Stats(self, enddate, FiscalYear): """Create series values for the Planned and Unplanned SAIDI/SAIFI. Populate the excel sheet with these values.""" # enddate = datetime.datetime.now() (default option) #FiscalYear = str(self._get_fiscal_year(enddate)) sheetname = self.CalculationSheet + " " + FiscalYear if enddate > datetime.datetime.now(): enddate = datetime.datetime.now() network = self.ORS.networknames[0] ColOffset = 5 # Magic number: offset of the column in the data table if network == "ELIN": ColOffset += 0 elif network == "OTPO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) elif network == "TPCO": ColOffset += len(self.DataHeadings) * len( self.IndexHeadings) * (len(self.NetworkHeadings) - 1) self.Sheet.set_calculation_mode("manual") startdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, 4, 1)) lastdate = self.Sheet.getDateTime( self.Sheet.getCell(sheetname, self.Sheet.getMaxRow(sheetname, 1, 4), 1)) delta_days = (lastdate - startdate).days + 1 FiscalyearDays = [ startdate + datetime.timedelta(days=i) for i in range(delta_days) ] # Truncate the data if it is for the present year maxrow = self.Sheet.getMaxRow(sheetname, 1, 4) #enddate = datetime.datetime.now() # Use this variable to force the graphs to only display a limited set of information searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day) searchterm = searchterm.strftime( str(searchterm.day) + "/%m/%Y" ) # Get rid of the zero padding (%e and %-d don't work on Windows) searchresult = self.Sheet.search( shtRange(sheetname, None, 4, 1, maxrow, 1), searchterm) if len(searchresult) == 1 or len( searchresult ) == 2: # There could be two dates, if we are duplicating the end date to achieve a vertical truncation on our stacked area chart StopTime = self.Sheet.getDateTime(searchresult[0].Value) else: StopTime = FiscalyearDays[ -1] # update this to be FiscalyearDays[0] now? SAIDIcol, SAIFIcol = self._Calc_Rows(FiscalyearDays, self.ORS) # The table columns need to be cummulative SAIDIsums = [0 for i in self.DataHeadings[3:]] SAIFIsums = [0 for i in self.DataHeadings[3:]] SAIDITable = [] SAIFITable = [] row = 4 # Loop through every row for SAIDIrow, SAIFIrow, day in zip(SAIDIcol, SAIFIcol, FiscalyearDays): ColumnIndex = 0 # Loop through every column for SAIDIval, SAIFIval in zip(SAIDIrow, SAIFIrow): # Add the new rows to the table stored in memmory if day <= StopTime: # means we will stop on the current day, but then fixing graph slopes break SAIDIsums[ColumnIndex] += SAIDIval SAIFIsums[ColumnIndex] += SAIFIval else: SAIDIsums[ColumnIndex] = None SAIFIsums[ColumnIndex] = None ColumnIndex += 1 #self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIsums]) #self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIsums]) SAIDITable.append( SAIDIsums[:]) # This copys by value, not by reference SAIFITable.append( SAIFIsums[:]) # This copys by value, not by reference row += 1 self.Sheet.setRange(sheetname, 4, ColOffset, SAIDITable) self.Sheet.setRange(sheetname, 4, ColOffset + len(self.DataHeadings), SAIFITable) self._Correct_Graph_Slope( FiscalYear, StopTime ) # Makes the area plot look a bit better, but mutates the source data, so must be run last self.Sheet.set_calculation_mode("automatic") def _get_fiscal_year(self, enddate): """Get the fiscal year as defined in NZ. Returns the year as a int""" year = enddate.year if enddate.month - 4 < 0: year -= 1 return year def Create_Graphs(self, enddate, sheetname_suffix): """Create the SAIDI/SAIFI chart @param enddate: Used to set the x axis on the chart and name the chart""" FiscalYear = self._get_fiscal_year( enddate) # (default option for sheetname) # Always set the end date of the graph to be the 1/4/xxxx (you may want to change this in future) if True: graphenddate = datetime.datetime(FiscalYear + 1, 4, 1) # Used to set the name of the chart... if enddate > datetime.datetime.now(): enddate = datetime.datetime.now() network = self.ORS.networknames[0] ColOffset = 2 # Magic number: where the data starts in the table (column 2) FullName = "" if network == "ELIN": ColOffset += 0 chartpath = os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/ORSChartEIL2.crtx') FullName = "Electricity Invercargill" elif network == "OTPO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) chartpath = os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/ORSChartOJV2.crtx') FullName = "OtagoNet+ESL" elif network == "TPCO": ColOffset += len(self.DataHeadings) * len( self.IndexHeadings) * (len(self.NetworkHeadings) - 1) chartpath = os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/ORSChartTPC2.crtx') FullName = "The Power Company" ylables = [ "Average Outage Duration (Minutes/ICP)", "Average No. Outages (Interruptions/ICP)" ] for stat in self.IndexHeadings: ChartName = datetime.datetime(enddate.year, enddate.month, enddate.day) ChartName = ChartName.strftime(str(ChartName.day) + "/%m/%Y") ChartName = FullName + " " + stat + " YTD as at " + ChartName # Make sure there is no zero padding on the day # Check for a duplicate chart name... NumDuplicates = 0 TempName = ChartName while self.Graphs.Chart_Exists(TempName): TempName = ChartName NumDuplicates += 1 TempName += " (" + str(NumDuplicates) + ")" ChartName = TempName self.Graphs.Create_Chart( ChartName, self.Generate_Date_Range(sheetname_suffix), sheetname=self.CalculationSheet + " " + sheetname_suffix) # Add the indvidual series to the chart i = self.IndexHeadings.index(stat) * (len(self.DataHeadings) * (len(self.IndexHeadings) - 1) ) # Add a sub-offset for col in range(i + ColOffset, i + ColOffset + len(self.DataHeadings)): self.Graphs.Add_Series(ChartName, self.Generate_Range( sheetname_suffix, col), serieslabels=True) # Apply the templated style, reapply attributes like ylabel self.Graphs.Apply_Template( ChartName, chartpath, ylabel=ylables[self.IndexHeadings.index(stat)], ylabelsize=15) # Apply a fix to the final dates on the graph self._Correct_Graph_Axis(ChartName, graphenddate) # Make the chart bigger self.Graphs.Set_Dimentions(ChartName, self.ChartDimentions.x, self.ChartDimentions.y) # Stack charts from the same network vetically origin = self.ChartOrigins[self.NetworkHeadings.index(network)] self.Graphs.Set_Position( ChartName, origin.x, origin.y + self.IndexHeadings.index(stat) * self.ChartDimentions.y) def Generate_Date_Range(self, suffix, **kwargs): """@return: String of a formatted excel range. Create an excel formulae for a range""" sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix) return "='%s'!%s%d:%s%d" % ( sheetname, self.Sheet.num_to_let(self.DateOrigin.col), self.DateOrigin.row, self.Sheet.num_to_let(self.DateOrigin.col), self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row)) def Generate_Range(self, suffix, col, **kwargs): """@return: String of a formatted excel range. Create an excel formulae for a range i nthe supplied sheets""" sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix) if type(col) is int: col = self.Sheet.num_to_let( col ) # Convert the column number to a corosponding letter to match an excel table return "='%s'!%s%d:%s%d" % ( sheetname, col, self.DateOrigin.row - 1, col, self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row)) def Analyse_Graphs(self): """Layout and create graphs suitable for comparrison and analysis. So place all the graphs on one sheet.""" # Create a new sheet sheetname = "All Graphs" self.Sheet.rmvSheet(removeList=[sheetname]) self.Sheet.addSheet(sheetname)
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
def __init__(self, xlInstance): self.Sheet = Sheet(xlInstance)
class ORSSheets(ORSPlots): InputSheet = "Input" CalculationSheet = "Calculation" StatsSheet = "Statistics" OutputFileName = "Current Month SAIDI and SAIFI" DateOrigin = pos(row=4, col=1) # The postion to place the fist date def __init__(self, xlInstance): self.Sheet = Sheet(xlInstance) def YTD_Sheet(self, *params): """Use a template table to represent the results. Create a YTD results table for a given date (datetime object).""" # The sheet with the (year) date may not have been created, so we can't rely on reading data from it #year = self.ORS._get_fiscal_year(date) #startdate = datetime.datetime(year, 4, 1) #enddate = datetime.datetime(year+1, 3, 31) #delta_days = (enddate - startdate).days + 1 #Dates = [startdate + datetime.timedelta(days=i) for i in range(delta_days)] #BasicDate = datetime.datetime(date.year, date.month, date.day) #index = Dates.index(BasicDate) params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template( os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", self.Sheet._getCell(self.OutputFileName, 1, 1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file def YTD_Book(self, filename, *params): """Create a new workbook with PNL Commercial Summary table""" params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) # Open the new file in the same instance for speed self.remove_file(filename) # Don't try opeing in exsting instance if the file does not exist, you get a com_error self.OutputFileHandle = Excel(BookVisible=0, Visible=1, filename=filename) # newinstance=1 xlSheetSrc = Sheet(self.OutputFileHandle) xlSheetSrc.renameSheet(self.OutputFileName) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template( os.path.expanduser( '~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", xlSheetSrc._getCell(self.OutputFileName, 1, 1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file self.OutputFileHandle.save(filename) self.OutputFileHandle.closeWorkbook() #self.OutputFileHandle.closeApplication() def Rename_Network(self, networkname): name = "" if networkname == "TPCO": name = "TPC" elif networkname == "OTPO": name = "OJV" elif networkname == "ELIN": name = "EIL" return name def Merge_Dictionaries(self, x1, x2): """Returns the ersults of merging two dictionaries. x1 is master so, any duplicate keys in x2 will be overwritten""" cpy = x2.copy() cpy.update(x1) return cpy def _rm_file(self, filename): # Remove any existing file in the same directory FilePath = self.remove_file(filename) if not os.path.exists(os.path.dirname(FilePath)): try: os.makedirs(os.path.dirname(FilePath)) except OSError as exc: # Guard against race condition if exc.errno != errno.EEXIST: raise return FilePath def remove_file(self, filename): try: os.remove(filename) except: pass
class ORSPlots(object): # ORSCalculator """A class to create the SAIDI/SAIFI charts from the ORS data.""" Timestamp = 'Date' Cap = 'Cap/Limit' Target = 'Target' Collar = 'Collar' Planned = 'Planned' Unplanned = 'Unplanned Normalised' CapUnplanned = 'Unplanned (Normalised Out)' # Set the order of the headings and data in the sheet NetworkHeadings = ["ELIN", "OTPO", "TPCO"] IndexHeadings = ["SAIDI", "SAIFI"] DataHeadings = [Cap, Target, Collar, Planned, Unplanned, CapUnplanned] def __init__(self, ORSCalculatorInstance, xlInstance): self.ORS = ORSCalculatorInstance self.xlInst = xlInstance self.Sheet = Sheet(self.xlInst) self.InputSheet = "Input" self.CalculationSheet = "Calculation" self.StatsSheet = "Statistics" self.Graphs = XlGraphs(xlInstance, self.Sheet) self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins) self.srcColumnOffset = 1 # The number of columns that the data is offset by # Set the order of the headings and data in the sheet self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"] self.IndexHeadings = ["SAIDI", "SAIFI"] self.DateOrigin = pos(row=4, col=1) # The postion to place the fist date on the Excel sheet # Graph offsets and dimentions self.ChartDimentions = pos(x=700, y=480) self.ChartOrigins = [] x = 85 y = 100 for network in self.NetworkHeadings: self.ChartOrigins.append(pos(x=x, y=y)) x += self.ChartDimentions.x # Shift the charts for the next network to the right def __repr__(self): """Return an identifying instance of the network being handled""" return "in the __repr__ for ORSPlots" + self.ORS.networknames[0] def Annual_Sheets(self, dates): """Create a new sheet for every indvidual year in the ORS results""" for date in dates: year = str(date.year) self.Create_Sheet(year) self.Fill_Dates(date, year) self.Populate_Fixed_Stats(year) # Com Com table values scaled linearly self.Populate_Daily_Stats(date, year) # Daily real world SAIDI/SAIDI self.Create_Graphs(date) def Clean_Workbook(self): self.Sheet.rmvSheet(keepList=[self.InputSheet, self.StatsSheet]) def Create_Sheets(self, dates): """Creates all the sheets needed in the workbook. Autofills the dates. All other data is network specific""" for date in dates: year = str(date.year) self.Create_Sheet(year) self.Fill_Dates(date, year) def Create_Sheet(self, suffix): """Create the "Calculation" sheet in excel, delete it if it already exisits""" suffix = " " + suffix # Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks self.Sheet.rmvSheet(removeList=[self.CalculationSheet+suffix]) self.Sheet.addSheet(self.CalculationSheet+suffix) DataOrigin = pos(row=4, col=1) LeftHeadings = [self.Timestamp] TableHeadings = LeftHeadings + \ len(self.NetworkHeadings) * len(self.IndexHeadings) * self.DataHeadings # Assign row 3 data self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-1, DataOrigin.col, [TableHeadings]) # Write the row headings into the table self.Sheet.mergeCells(self.CalculationSheet+suffix, 1, DataOrigin.col, DataOrigin.row-1, DataOrigin.col) # Date Cells # Assign row 2 data col = DataOrigin.col + len(LeftHeadings) columns = list(np.linspace(col, col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 1) * len(self.DataHeadings), len(self.NetworkHeadings) * len(self.IndexHeadings))) index = 0 for col in columns: self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-2, int(col), [[self.IndexHeadings[index % len(self.IndexHeadings)]]]) # self.IndexHeadings[int((col-len(LeftHeadings)) % len(self.IndexHeadings))] index += 1 self.Sheet.mergeCells(self.CalculationSheet+suffix, 2, col, 2, col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / (len(self.NetworkHeadings) * len(self.IndexHeadings))) # Row 2 # Assign row 1 data col = DataOrigin.col + len(LeftHeadings) columns = list(np.linspace(col, col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 2) * len(self.DataHeadings), len(self.NetworkHeadings))) index = 0 for col in columns: self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-3, int(col), [[self.NetworkHeadings[index % len(self.NetworkHeadings)]]]) index += 1 self.Sheet.mergeCells(self.CalculationSheet+suffix, 1, col, 1, col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / len(self.NetworkHeadings)) # Row 1 def Generate_Dates(self, startdate, enddate=None): """Generate an array of all the days for 1 fincial year""" if type(enddate) != datetime.datetime: enddate = datetime.datetime(startdate.year + 1, 3, 31) dt = datetime.timedelta(1) # A time delta of 1 day TimeStamps = [] while startdate <= enddate: TimeStamps.append([startdate]) startdate = startdate + dt return TimeStamps def Fill_Dates(self, date, suffix): """Fill the date column in the Excel sheet with the date values read from the parser. Also performs and autofit (width) and freezes panes.""" suffix = " " + suffix row, col = \ self.Sheet.setRange(self.CalculationSheet+suffix, self.DateOrigin.row, self.DateOrigin.col, self.Generate_Dates(date)) self.Sheet.autofit(self.CalculationSheet+suffix, self.DateOrigin.col) ActiveWindow = self.xlInst.xlApp.Windows(self.xlInst.xlBook.Name) ActiveWindow.SplitColumn = 1 ActiveWindow.SplitRow = 3 ActiveWindow.FreezePanes = True def _Correct_Graph_Slope(self, suffix, enddate=datetime.datetime.now()): """When the data is truncated for stacked area charts we need two of the same end dates in a row to create a vertical drop on the graph, otherwise it slopes with delta x = one time interval""" suffix = " " + suffix searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day) searchterm = searchterm.strftime(str(searchterm.day) + "/%m/%Y") # Get rid of the zero padding (%e and %-d don't work on Windows) maxrow = self.Sheet.getMaxRow(self.CalculationSheet+suffix, 1, 4) results = self.Sheet.search(shtRange(self.CalculationSheet+suffix, None, 4, 1, maxrow, 1), searchterm) if len(results) == 1: # If there is only one date, we haven't corrected slope yet so do it now self.Sheet.setCell(self.CalculationSheet+suffix, results[0].Row+1, 1, results[0].Value) def _Correct_Graph_Axis(self, ChartName, enddate=datetime.datetime.now()): """Adds the final end of year date to the x axis e.g. 31/3/xxxx""" self.Graphs.Set_Max_X_Value(ChartName, enddate) def Populate_Fixed_Stats(self, suffix): """Create series values for the Limit, Cap, and Collar. Populate the excel sheet with these values.""" suffix = " " + suffix network = self.ORS.networknames[0] if network == "ELIN": ColOffset = 0 elif network == "OTPO": ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) elif network == "TPCO": ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) * (len(self.NetworkHeadings) - 1) RowHeadings = ["CAP", "TARGET", "COLLAR"] # The order the rows appear in the Excel spreadsheet self.Sheet.set_calculation_mode("manual") # Assumes that the data will start in col 2 i.e. col 1 is for the dates Column = 2 + ColOffset OriginCol = Column for param in self.IndexHeadings: for heading in RowHeadings: LinearRange = np.linspace(0, self.ORS._get_CC_stats(heading)[self.IndexHeadings.index(param)], self.Sheet.getMaxRow(self.CalculationSheet+suffix, 1, 4) - 3 + 1)[1:] self.Sheet.setRange(self.CalculationSheet+suffix, 4, Column, [[i] for i in LinearRange]) Column += 1 Column += len(self.DataHeadings) - len(RowHeadings) self.Sheet.set_calculation_mode("automatic") def _Calc_Rows(self, dates, ORS): SAIDIcol = [] SAIFIcol = [] for day in dates: SAIDIrow = [] SAIFIrow = [] x, y = ORS._get_indicies(day, "planned", applyBoundary=True) SAIDIrow.append(x) SAIFIrow.append(y) x, y = ORS._get_indicies(day, "unplanned", applyBoundary=True) SAIDIrow.append(x) SAIFIrow.append(y) x, y = ORS._get_indicies(day, "unplanned", applyBoundary=False) SAIDIrow.append(x-SAIDIrow[1]) SAIFIrow.append(y-SAIFIrow[1]) SAIDIcol.append(SAIDIrow) SAIFIcol.append(SAIFIrow) # Here for debugging only #row=4 #self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIrow]) #self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIrow]) #row += 1 return SAIDIcol, SAIFIcol def Populate_Daily_Stats(self, enddate, FiscalYear): """Create series values for the Planned and Unplanned SAIDI/SAIFI. Populate the excel sheet with these values.""" # enddate = datetime.datetime.now() (default option) #FiscalYear = str(self._get_fiscal_year(enddate)) sheetname = self.CalculationSheet + " " + FiscalYear if enddate > datetime.datetime.now(): enddate = datetime.datetime.now() network = self.ORS.networknames[0] ColOffset = 5 # Magic number: offset of the column in the data table if network == "ELIN": ColOffset += 0 elif network == "OTPO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) elif network == "TPCO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) * (len(self.NetworkHeadings) - 1) self.Sheet.set_calculation_mode("manual") startdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, 4, 1)) lastdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, self.Sheet.getMaxRow(sheetname, 1, 4), 1)) delta_days = (lastdate - startdate).days + 1 FiscalyearDays = [startdate + datetime.timedelta(days=i) for i in range(delta_days)] # Truncate the data if it is for the present year maxrow = self.Sheet.getMaxRow(sheetname, 1, 4) #enddate = datetime.datetime.now() # Use this variable to force the graphs to only display a limited set of information searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day) searchterm = searchterm.strftime(str(searchterm.day) + "/%m/%Y") # Get rid of the zero padding (%e and %-d don't work on Windows) searchresult = self.Sheet.search(shtRange(sheetname, None, 4, 1, maxrow, 1), searchterm) if len(searchresult) == 1 or len(searchresult) == 2: # There could be two dates, if we are duplicating the end date to achieve a vertical truncation on our stacked area chart StopTime = self.Sheet.getDateTime(searchresult[0].Value) else: StopTime = FiscalyearDays[-1] # update this to be FiscalyearDays[0] now? SAIDIcol, SAIFIcol = self._Calc_Rows(FiscalyearDays, self.ORS) # The table columns need to be cummulative SAIDIsums = [0 for i in self.DataHeadings[3:]] SAIFIsums = [0 for i in self.DataHeadings[3:]] SAIDITable = [] SAIFITable = [] row = 4 # Loop through every row for SAIDIrow, SAIFIrow, day in zip(SAIDIcol, SAIFIcol, FiscalyearDays): ColumnIndex = 0 # Loop through every column for SAIDIval, SAIFIval in zip(SAIDIrow, SAIFIrow): # Add the new rows to the table stored in memmory if day <= StopTime: # means we will stop on the current day, but then fixing graph slopes break SAIDIsums[ColumnIndex] += SAIDIval SAIFIsums[ColumnIndex] += SAIFIval else: SAIDIsums[ColumnIndex] = None SAIFIsums[ColumnIndex] = None ColumnIndex += 1 #self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIsums]) #self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIsums]) SAIDITable.append(SAIDIsums[:]) # This copys by value, not by reference SAIFITable.append(SAIFIsums[:]) # This copys by value, not by reference row += 1 self.Sheet.setRange(sheetname, 4, ColOffset, SAIDITable) self.Sheet.setRange(sheetname, 4, ColOffset+len(self.DataHeadings), SAIFITable) self._Correct_Graph_Slope(FiscalYear, StopTime) # Makes the area plot look a bit better, but mutates the source data, so must be run last self.Sheet.set_calculation_mode("automatic") def _get_fiscal_year(self, enddate): """Get the fiscal year as defined in NZ. Returns the year as a int""" year = enddate.year if enddate.month - 4 < 0: year -= 1 return year def Create_Graphs(self, enddate, sheetname_suffix): """Create the SAIDI/SAIFI chart @param enddate: Used to set the x axis on the chart and name the chart""" FiscalYear = self._get_fiscal_year(enddate) # (default option for sheetname) # Always set the end date of the graph to be the 1/4/xxxx (you may want to change this in future) if True: graphenddate = datetime.datetime(FiscalYear+1, 4, 1) # Used to set the name of the chart... if enddate > datetime.datetime.now(): enddate = datetime.datetime.now() network = self.ORS.networknames[0] ColOffset = 2 # Magic number: where the data starts in the table (column 2) FullName = "" if network == "ELIN": ColOffset += 0 chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartEIL2.crtx') FullName = "Electricity Invercargill" elif network == "OTPO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartOJV2.crtx') FullName = "OtagoNet+ESL" elif network == "TPCO": ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) * (len(self.NetworkHeadings) - 1) chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartTPC2.crtx') FullName = "The Power Company" ylables = ["Average Outage Duration (Minutes/ICP)", "Average No. Outages (Interruptions/ICP)"] for stat in self.IndexHeadings: ChartName = datetime.datetime(enddate.year, enddate.month, enddate.day) ChartName = ChartName.strftime(str(ChartName.day) + "/%m/%Y") ChartName = FullName + " " + stat + " YTD as at " + ChartName # Make sure there is no zero padding on the day # Check for a duplicate chart name... NumDuplicates = 0 TempName = ChartName while self.Graphs.Chart_Exists(TempName): TempName = ChartName NumDuplicates += 1 TempName += " (" + str(NumDuplicates) + ")" ChartName = TempName self.Graphs.Create_Chart(ChartName, self.Generate_Date_Range(sheetname_suffix), sheetname=self.CalculationSheet + " " + sheetname_suffix) # Add the indvidual series to the chart i = self.IndexHeadings.index(stat) * (len(self.DataHeadings) * (len(self.IndexHeadings) - 1)) # Add a sub-offset for col in range(i + ColOffset, i + ColOffset + len(self.DataHeadings)): self.Graphs.Add_Series(ChartName, self.Generate_Range(sheetname_suffix, col), serieslabels=True) # Apply the templated style, reapply attributes like ylabel self.Graphs.Apply_Template(ChartName, chartpath, ylabel=ylables[self.IndexHeadings.index(stat)], ylabelsize=15) # Apply a fix to the final dates on the graph self._Correct_Graph_Axis(ChartName, graphenddate) # Make the chart bigger self.Graphs.Set_Dimentions(ChartName, self.ChartDimentions.x, self.ChartDimentions.y) # Stack charts from the same network vetically origin = self.ChartOrigins[self.NetworkHeadings.index(network)] self.Graphs.Set_Position(ChartName, origin.x, origin.y + self.IndexHeadings.index(stat) * self.ChartDimentions.y) def Generate_Date_Range(self, suffix, **kwargs): """@return: String of a formatted excel range. Create an excel formulae for a range""" sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix) return "='%s'!%s%d:%s%d" % (sheetname, self.Sheet.num_to_let(self.DateOrigin.col), self.DateOrigin.row, self.Sheet.num_to_let(self.DateOrigin.col), self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row)) def Generate_Range(self, suffix, col, **kwargs): """@return: String of a formatted excel range. Create an excel formulae for a range i nthe supplied sheets""" sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix) if type(col) is int: col = self.Sheet.num_to_let(col) # Convert the column number to a corosponding letter to match an excel table return "='%s'!%s%d:%s%d" % (sheetname, col, self.DateOrigin.row-1, col, self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row)) def Analyse_Graphs(self): """Layout and create graphs suitable for comparrison and analysis. So place all the graphs on one sheet.""" # Create a new sheet sheetname = "All Graphs" self.Sheet.rmvSheet(removeList=[sheetname]) self.Sheet.addSheet(sheetname)
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 ORSSheets(ORSPlots): InputSheet = "Input" CalculationSheet = "Calculation" StatsSheet = "Statistics" OutputFileName = "Current Month SAIDI and SAIFI" DateOrigin = pos(row=4, col=1) # The postion to place the fist date def __init__(self, xlInstance): self.Sheet = Sheet(xlInstance) def YTD_Sheet(self, *params): """Use a template table to represent the results. Create a YTD results table for a given date (datetime object).""" # The sheet with the (year) date may not have been created, so we can't rely on reading data from it #year = self.ORS._get_fiscal_year(date) #startdate = datetime.datetime(year, 4, 1) #enddate = datetime.datetime(year+1, 3, 31) #delta_days = (enddate - startdate).days + 1 #Dates = [startdate + datetime.timedelta(days=i) for i in range(delta_days)] #BasicDate = datetime.datetime(date.year, date.month, date.day) #index = Dates.index(BasicDate) params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template(os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", self.Sheet._getCell(self.OutputFileName, 1 ,1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file def YTD_Book(self, filename, *params): """Create a new workbook with PNL Commercial Summary table""" params = iter(params) out = params.next() for p in params: out = self.Merge_Dictionaries(out, p) # Open the new file in the same instance for speed self.remove_file(filename) # Don't try opeing in exsting instance if the file does not exist, you get a com_error self.OutputFileHandle = Excel(BookVisible=0, Visible=1, filename=filename) # newinstance=1 xlSheetSrc = Sheet(self.OutputFileHandle) xlSheetSrc.renameSheet(self.OutputFileName) #template = Template(r"C:\Users\sdo\Documents\Research and Learning\Git Repos\SAIDI-SAIFI-Calculator\Data\Templates.xlsx") template = Template(os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/Templates.xlsx')) template.Place_Template("Commercial", xlSheetSrc._getCell(self.OutputFileName, 1 ,1)) template.Set_Values(out) template.Auto_Fit() # Handles the closing of the template file self.OutputFileHandle.save(filename) self.OutputFileHandle.closeWorkbook() #self.OutputFileHandle.closeApplication() def Rename_Network(self, networkname): name = "" if networkname == "TPCO": name = "TPC" elif networkname == "OTPO": name = "OJV" elif networkname == "ELIN": name = "EIL" return name def Merge_Dictionaries(self, x1, x2): """Returns the ersults of merging two dictionaries. x1 is master so, any duplicate keys in x2 will be overwritten""" cpy = x2.copy() cpy.update(x1) return cpy def _rm_file(self, filename): # Remove any existing file in the same directory FilePath = self.remove_file(filename) if not os.path.exists(os.path.dirname(FilePath)): try: os.makedirs(os.path.dirname(FilePath)) except OSError as exc: # Guard against race condition if exc.errno != errno.EEXIST: raise return FilePath def remove_file(self, filename): try: os.remove(filename) except: pass
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)])