﻿import logging
import logging.config
import subprocess
import mysql.connector
import pymssql
from mysql.connector import Error
#import pyodbc
def macinc(pmac):
	i=pmac.split(".")
        if len(i[1])>0:
            mac=int(i[1],16)
            mac=mac+1
            mac=hex(mac).rstrip("L")
            macx=str(mac[1:]).replace('x','')
            macx=macx.rjust(12,'0')
            i[1]=macx.upper()
            macadded=".".join(i)
        else:
            macadded=pmac
        return macadded
try:
    vardebug=1
    #logger = logging.getLogger(__name__)
    #logging.basicConfig(format = u'%(filename)s[LINE:%(lineno)d]# %(levelname)-8s [%(asctime)s]  %(message)s', level = logging.DEBUG)
    #logging.basicConfig(filename='bng.log',level=logging.DEBUG)
    #logging.basicConfig(format='%(asctime)s | %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p', filemode='w', filename="bng.log",level=logging.DEBUG)
    logging.config.fileConfig('/home/spo/logging.conf')
    log=logging.getLogger('main')
    connmssql = pymssql.connect('xxx.x.xxx.x','user','passwd','bill')
    cursormssql = connmssql.cursor()
    conn = mysql.connector.connect(host='127.0.0.1',database='radius',user='user',password='pwd')
    if conn.is_connected():
            log.info('Connected to MySQL database')
    cursorUpd = conn.cursor()
    cursormssqlUpd = connmssql.cursor()
    cursormssql.execute("exec mda_BNGChangesLSt_ip")
    rows = cursormssql.fetchall()
    log.info('Total Row(s):'+ str(cursormssql.rowcount))
    for row in rows:
          if row[7]==2:
                username=macinc(row[1])
          else:
                username=row[1]
          opertype=row[10];
          if vardebug==1:
            log.debug('opertype='+str(opertype))
          if opertype==1:
                #sql="call radius.RadcheckAdd('"+str(username)+"','"+str(row[0])+"',"+str(row[4])+",'"+str(row[5])+"','"+str(row[6])+"','"+str(row[7])+"','"+str(row[8])+"','"+str(row[9])+"');"
                args=(str(username),str(row[0]),row[4],str(row[5]),str(row[6]),row[7],str(row[8]),str(row[9]),str(row[15]),str(row[16]),1)
                #args=("'"+str(row[1])+"','"+str(row[0])+"',"+str(row[4])+",'"+str(row[5])+"','"+str(row[6])+"',"+str(row[7])+",'"+str(row[8])+"',8")
                #args=(1,2,3,4,5,6,7,8)
                if vardebug==1:
                    log.debug(args)
                cursorUpd.callproc('radius.RadcheckAdd',args)

          if opertype==2:
                sql="DELETE FROM radius.radcheck WHERE username='"+str(username)+"';"
                if vardebug==1:
                    log.info(sql)
                cursorUpd.execute(sql)
          sqlfix=" update mda_BNGChanges set ProcessFlag=1, DateProcess=getdate() where id="+str(row[12])
          if vardebug==1:
                    log.debug(sqlfix)
          cursormssqlUpd.execute(sqlfix)
          conn.commit()
          connmssql.commit()
    cursorUpd.close()
    conn.close()
    cursormssql.close()
    connmssql.close()
    log.info('Finish')

except Error as e:
        #cursorUpd.close()
        conn.rollback()
        conn.close()
        cursormssqlUpd.close()
        connmssql.rollback()
        connmssql.close()
        log.critical(e)

