<commit_before>import json
from lib import actions, Excel_Reader, ztp_utils
import openpyxl
import pymysql
import re
import logging
from logging.handlers import RotatingFileHandler

class GetInventoryAction(actions.SessionAction):
    def __init__(self, config):
        super(GetInventoryAction, self).__init__(config)

        self._logger = logging.getLogger('cleanup_logger')
	self._logger.setLevel(logging.DEBUG)
        self._fileHandler = RotatingFileHandler("/var/log/cleanuplog_bwc", mode='w', maxBytes=10*1024*1024, backupCount=2, encoding=None, delay=0)
        self._fileHandler.setLevel(logging.DEBUG)
        self._logger.addHandler(self._fileHandler)

        self._connection = pymysql.connect(
                  host=self._db_addr, 
                  user=self._db_user,      
                  passwd=<PASSWORD>,  
                  db=self._db_name)
        self._cursor =  self._connection.cursor()

        #Regex for IP Address
        self._ip_addr_regex = re.compile('(\d+\.\d+\.\d+\.\d+)')
        #Regex for port ex: 1/1/27
        self._port_regex = re.compile('(\d+\/\d+\/\d+)')
        #Regex for ICX Output: "2cc5.d321.b3b3 1/1/23    Dynamic   233"
        self._icx_output_regex = re.compile('([0-9a-fA-F]{4}\.[0-9a-fA-F]{4}\.[0-9a-fA-F]{4})(\s*)(\s\d+\/\d+\/\d+)(\s*)(Dynamic)(\s*)(233)')

    def run(self, filepath, sheetname, ip_column_name, switch_name_column_name):
        results = self.clean(filepath, sheetname, ip_column_name, switch_name_column_name)
        self._cursor.close()
        self._connection.commit()
        self._connection.close()
        for obj in results:
                print obj
        return True

    def clean(self, filepath, sheetname, ip_column_name, switch_name_column_name):
        self._logger.info("***** Cleanup Action Initiated *****")
        
        results = []

        wb = openpyxl.load_workbook(filepath, data_only=True)
        ws = wb.get_sheet_by_name(sheetname)

        ip_index = -1
        name_index = -1
        for row in ws.iter_rows():
                for index, cell in enumerate(row):
                        if(str(cell.value)==ip_column_name):
                                ip_index = index
                        if(str(cell.value)==switch_name_column_name):
                                name_index = index
                break

        print("IP Address Column Index: " + str(ip_index), "Switch Name Column Index: " + str(name_index))

        for row in ws.iter_rows(row_offset=1):
               ip, name = "NULL", "NULL"
               for index, cell in enumerate(row):
                       if index == ip_index and cell.value!=None:
                               ip = cell.value
                       if index == name_index and cell.value!=None:
                               name = cell.value

               if(self._ip_addr_regex.match(ip)==None or name=="NULL"):
                       self._logger.info("Error Invalid IP Address in file or invalid name, IP: '%s' and Name:'%s'" % (ip, name))
                       continue

               results.extend(self.start_icx_session(ip, name))
        return results

    def start_icx_session(self, ip, name):
        command = "show mac-address | inc 233"
        self._logger.info("Starting cleanup for ICX switch with ip: '%s' and name: '%s'" % (ip, name))
        icx_session = ztp_utils.start_session(ip, self._username, self._password,
                                              self._enable_username, self._enable_password, "ssh")
        (success, output) = ztp_utils.send_commands_to_session(icx_session, command, False)
        if(success == False):
                self._logger.info("Error in send Command: '%s' for Device: '%s', Results: '%s', '%s'." % (command, ip, success, output))
                return (False, ip, "Failure, Error in send command")
        return self.parse_output(icx_session, ip, name, output)


    def parse_output(self, icx_session, switch_ip, switch_name, output):
        self._logger.info("Parsing output for ICX switch name: '%s', switch IP: '%s'" % (switch_name, switch_ip))
        self._logger.info("Output: '%s'" % (output))
        results = []
        if len(output)==0:
                results.append(True, switch_ip, "No output");
                return results
        for line in output[0]["output"]:
                match = self._icx_output_regex.match(line.strip())
                if match:
                        self._logger.info("Regex successful match for output line: '%s'" % (line))
                        results.append(self.verify_and_update(icx_session, switch_ip, switch_name, match.group(1).strip(), match.group(3).strip()))
                else:
                        self._logger.info("Regex failed match for output line: '%s'" % (line))
        return presults
        
    def verify_and_update(self, icx_session, switch_ip, switch_name, mac, port):
        self._logger.info("Verifying and updating db for ICX Switch IP: '%s', AP MAC address: '%s', ICX port: '%s'" % (switch_ip, mac, port))
        sql = "select count(*) from authorized where mac='%s'" % (mac)
        self._cursor.execute(sql)
        count = self._cursor.fetchone()[0]
        if count==0:
               #Mac address is not in the database so don't do anything.
               self._logger.info("Warning AP MAC address not in database for AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))
               return (True, switch_ip, "Warning AP MAC address not in database for AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))
        else:
               #Mac Address was found
               self._logger.info("Mac address found in database for AP MAC: '%s'" % (mac))

        sql = "select ip, port, base_mac, ap_name, switch_name from authorized where mac='%s'" % (mac)
        self._cursor.execute(sql)
        row = self._cursor.fetchone()
        db_ip = row[0]
        db_port = row[1]
        db_base_mac = row[2]
        db_ap_name = row[3]
        db_switch_name = row[4]
        
        if(self._ip_addr_regex.match(db_ip)==None or self._port_regex.match(db_port)==None or db_switch_name=="NULL"):
               self._logger.info("Warning Database was missing information for AP MAC:'%s'." % (mac))
        elif(db_ip!=switch_ip or db_port!=port or db_switch_name!=switch_name):
               self._logger.info("Warning Database had invalid information for AP MAC:'%s'." % (mac))
               self._logger.info("Warning Database Information: Switch IP: '%s', Switch Name: '%s', Switch Port: '%s' for device with MAC: '%s'" % (db_ip, db_switch_name, db_port, mac))
               self._logger.info("Warning Switch Information: Switch IP: '%s', Switch IP: '%s', Switch Port: '%s' for device with MAC: '%s'" % (switch_ip, switch_name, port, mac))
        else:
               self._logger.info("Database information for AP MAC: '%s' is up-to-date." % (mac))

        #Updates the port name on the ICX
        (success, description) = self.icx_port_name_update(icx_session, port, db_ap_name)
        if(succcess==False):
               return (False, switch_ip, description, "AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))

        #Updates the DB
        sql = "update authorized set ip='%s', switch_name='%s', port='%s' where mac='%s'" % (switch_ip, switch_name, port, mac)
        self._cursor.execute(sql)
        self._connection.commit()
        self._logger.info("Database updated for AP MAC:'%s'." % (mac))

        #Updates the name on the ruckus controller<commit_msg>Updated db column name<commit_after>import json
from lib import actions, Excel_Reader, ztp_utils
import openpyxl
import pymysql
import re
import logging
from logging.handlers import RotatingFileHandler

class GetInventoryAction(actions.SessionAction):
    def __init__(self, config):
        super(GetInventoryAction, self).__init__(config)

        self._logger = logging.getLogger('cleanup_logger')
	self._logger.setLevel(logging.DEBUG)
        self._fileHandler = RotatingFileHandler("/var/log/cleanuplog_bwc", mode='w', maxBytes=10*1024*1024, backupCount=2, encoding=None, delay=0)
        self._fileHandler.setLevel(logging.DEBUG)
        self._logger.addHandler(self._fileHandler)

        self._connection = pymysql.connect(
                  host=self._db_addr, 
                  user=self._db_user,      
                  passwd=self._<PASSWORD>,  
                  db=self._db_name)
        self._cursor =  self._connection.cursor()

        #Regex for IP Address
        self._ip_addr_regex = re.compile('(\d+\.\d+\.\d+\.\d+)')
        #Regex for port ex: 1/1/27
        self._port_regex = re.compile('(\d+\/\d+\/\d+)')
        #Regex for ICX Output: "2cc5.d321.b3b3 1/1/23    Dynamic   233"
        self._icx_output_regex = re.compile('([0-9a-fA-F]{4}\.[0-9a-fA-F]{4}\.[0-9a-fA-F]{4})(\s*)(\s\d+\/\d+\/\d+)(\s*)(Dynamic)(\s*)(233)')

    def run(self, filepath, sheetname, ip_column_name, switch_name_column_name):
        results = self.clean(filepath, sheetname, ip_column_name, switch_name_column_name)
        self._cursor.close()
        self._connection.commit()
        self._connection.close()
        for obj in results:
                print obj
        return True

    def clean(self, filepath, sheetname, ip_column_name, switch_name_column_name):
        self._logger.info("***** Cleanup Action Initiated *****")
        
        results = []

        wb = openpyxl.load_workbook(filepath, data_only=True)
        ws = wb.get_sheet_by_name(sheetname)

        ip_index = -1
        name_index = -1
        for row in ws.iter_rows():
                for index, cell in enumerate(row):
                        if(str(cell.value)==ip_column_name):
                                ip_index = index
                        if(str(cell.value)==switch_name_column_name):
                                name_index = index
                break

        print("IP Address Column Index: " + str(ip_index), "Switch Name Column Index: " + str(name_index))

        for row in ws.iter_rows(row_offset=1):
               ip, name = "NULL", "NULL"
               for index, cell in enumerate(row):
                       if index == ip_index and cell.value!=None:
                               ip = cell.value
                       if index == name_index and cell.value!=None:
                               name = cell.value

               if(self._ip_addr_regex.match(ip)==None or name=="NULL"):
                       self._logger.info("Error Invalid IP Address in file or invalid name, IP: '%s' and Name:'%s'" % (ip, name))
                       continue

               results.extend(self.start_icx_session(ip, name))
        return results

    def start_icx_session(self, ip, name):
        command = "show mac-address | inc 233"
        self._logger.info("Starting cleanup for ICX switch with ip: '%s' and name: '%s'" % (ip, name))
        icx_session = ztp_utils.start_session(ip, self._username, self._password,
                                              self._enable_username, self._enable_password, "ssh")
        (success, output) = ztp_utils.send_commands_to_session(icx_session, command, False)
        if(success == False):
                self._logger.info("Error in send Command: '%s' for Device: '%s', Results: '%s', '%s'." % (command, ip, success, output))
                return (False, ip, "Failure, Error in send command")
        return self.parse_output(icx_session, ip, name, output)


    def parse_output(self, icx_session, switch_ip, switch_name, output):
        self._logger.info("Parsing output for ICX switch name: '%s', switch IP: '%s'" % (switch_name, switch_ip))
        self._logger.info("Output: '%s'" % (output))
        results = []
        if len(output)==0:
                results.append(True, switch_ip, "No output");
                return results
        for line in output[0]["output"]:
                match = self._icx_output_regex.match(line.strip())
                if match:
                        self._logger.info("Regex successful match for output line: '%s'" % (line))
                        results.append(self.verify_and_update(icx_session, switch_ip, switch_name, match.group(1).strip(), match.group(3).strip()))
                else:
                        self._logger.info("Regex failed match for output line: '%s'" % (line))
        return presults
        
    def verify_and_update(self, icx_session, switch_ip, switch_name, mac, port):
        self._logger.info("Verifying and updating db for ICX Switch IP: '%s', AP MAC address: '%s', ICX port: '%s'" % (switch_ip, mac, port))
        sql = "select count(*) from authorized where mac='%s'" % (mac)
        self._cursor.execute(sql)
        count = self._cursor.fetchone()[0]
        if count==0:
               #Mac address is not in the database so don't do anything.
               self._logger.info("Warning AP MAC address not in database for AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))
               return (True, switch_ip, "Warning AP MAC address not in database for AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))
        else:
               #Mac Address was found
               self._logger.info("Mac address found in database for AP MAC: '%s'" % (mac))

        sql = "select ip, port, base_mac, device, switch_name from authorized where mac='%s'" % (mac)
        self._cursor.execute(sql)
        row = self._cursor.fetchone()
        db_ip = row[0]
        db_port = row[1]
        db_base_mac = row[2]
        db_ap_name = row[3]
        db_switch_name = row[4]
        
        if(self._ip_addr_regex.match(db_ip)==None or self._port_regex.match(db_port)==None or db_switch_name=="NULL"):
               self._logger.info("Warning Database was missing information for AP MAC:'%s'." % (mac))
        elif(db_ip!=switch_ip or db_port!=port or db_switch_name!=switch_name):
               self._logger.info("Warning Database had invalid information for AP MAC:'%s'." % (mac))
               self._logger.info("Warning Database Information: Switch IP: '%s', Switch Name: '%s', Switch Port: '%s' for device with MAC: '%s'" % (db_ip, db_switch_name, db_port, mac))
               self._logger.info("Warning Switch Information: Switch IP: '%s', Switch IP: '%s', Switch Port: '%s' for device with MAC: '%s'" % (switch_ip, switch_name, port, mac))
        else:
               self._logger.info("Database information for AP MAC: '%s' is up-to-date." % (mac))

        #Updates the port name on the ICX
        (success, description) = self.icx_port_name_update(icx_session, port, db_ap_name)
        if(succcess==False):
               return (False, switch_ip, description, "AP MAC: '%s', on ICX port: '%s', on ICX IP:'%s'" % (mac, port, switch_ip))

        #Updates the DB
        sql = "update authorized set ip='%s', switch_name='%s', port='%s' where mac='%s'" % (switch_ip, switch_name, port, mac)
        self._cursor.execute(sql)
        self._connection.commit()
        self._logger.info("Database updated for AP MAC:'%s'." % (mac))

        #Updates the name on the ruckus controller
{'fraction_non_alphanumeric': 0.131485383197261, 'fraction_numerical': 0.007769291545957335, 'mean_word_length': 2.3026744944553164, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3070171', 'n_tokens_mistral': 4654, 'n_tokens_neox': 4483, 'n_words': 1182}