import os
import paramiko
import time
import re
import xlsxwriter
import xlrd





def cmd (command):
	cmdoutput = ""
	ssh.send(command + "\n")
	time.sleep(1)
	cmdoutput = ssh.recv(5000)
	return cmdoutput


#a text file where the show output will go
output_file = open(r'X:\\MC40\output.txt', 'w')



# Opens the spreadsheet for input
workbook = xlrd.open_workbook(r"X:\MC40\deviceinfo.xls")

firsttab = workbook.sheet_by_index(0)


# removes the header row fro being counted
xlsrows = firsttab.nrows - 1


storelist = []
storeiplist = []

while input != "":

	### Begin - part where we turn a store number into an IP --------
	#Get the store number from the user, and pad it with zeros
	input = input ("Enter a number:  ")
	formatinput = input.zfill(4)

	#pull the individual digits from the four digit number
	st_dig_0 = formatinput[0:1]
	st_dig_1 = formatinput[1:2]
	st_dig_2 = formatinput[2:3]
	st_dig_3 = formatinput[3:4]

	#create the second and third octets
	int_st_dig_1 = int(formatinput[0:2])
	int_st_dig_2 = int(formatinput[2:4])

	#define variables that will make up and IP address
	oc0 = "10"
	oc1 = str(int_st_dig_1)
	oc2 = str(int_st_dig_2)
	oc3 = "0"
	dot = "."
	rtr = "160"

	# makes a list out of all of the compoents that make up an IP
	ip_addr_base = [oc0, oc1, oc2, rtr]

	# joins the list together with dots to make a proper IP
	dev_ip = dot.join(ip_addr_base)

	#append the store number to the store number list
	storelist.append(formatinput)

	#append the store IP to the store IP list
	storeiplist.append(dev_ip)
	print("The IP address for Store#" + input + " is " + dev_ip)
	print("\n")
	### END - part where we turn a store number into an IP --------



#creates a counter to keep the store list and the ip list in sync
stcounter = 0

#Loop that iterates through each store IP that was generated
for st in storelist:
	print("-------------------------------CURRENTLY WORKING ON STORE# " + st)

	#the ierator for crawling the spreadsheet
	xlscounter = 2


	#create a list that will be populated with the mac addresses of the MC40s
	maclist = []

	#this is the loop that runs through the spreadsheet
	while xlscounter <= xlsrows:
		#get the mac address
		devicemac = firsttab.cell_value(xlscounter,13)
		formattedmac = devicemac[:4] + "." + devicemac[4:8] + "." + devicemac[8:]

		### BEGIN - part where we turn the IP into a store number--------
		#grab the IP addres
		ipaddr = firsttab.cell_value(xlscounter,10)
		#split the ip based on the periods
		octets = ipaddr.split(".")

		#convert the second and third octets into two digit numbers
		dub_secondoctet = octets[1].zfill(2)
		dub_thirdoctet = octets[2].zfill(2)

		#access the second and third octets, convert them to integers
		secondoctet = int(dub_secondoctet)
		thirdoctet = int(dub_thirdoctet)

		#convert the integer into a string so we can test
		thirdoctet = str(thirdoctet)

		#if the third octet is three digits, only use the last two digits
		if len(thirdoctet) ==3:
			thirdoctet = thirdoctet[1:3]
		#if the third octet is 1 digis, prepend a zero
		elif len(thirdoctet) ==1:
			thirdoctet = thirdoctet.zfill(2)
		#anything else should be 2 digits... fine
		else:
			thirdoctet = thirdoctet

		#add then together to get the store number
		storenum = str(secondoctet) + thirdoctet
		#make the store number four digits
		four_store = storenum.zfill(4)
		### END - part where we turn the IP into a store number--------

		output_file.write("2nd = ")
		output_file.write(str(dub_secondoctet))
		output_file.write("\n")
		output_file.write("3rd = ")
		output_file.write(str(dub_thirdoctet))
		output_file.write("\n")
		output_file.write(ipaddr)
		output_file.write(" = ")
		output_file.write(four_store)
		output_file.write("\n")
		output_file.write("\n")


		#check to see if the store number we pulled from the spreadsheet matches what the user entered
		if four_store == st:
			maclist.append(formattedmac)

		xlscounter += 1



	# show the user the list of mac addresses

	print("\n")
	print("*" * 100)
	print("*" * 100)
	print("These are *** "  + str(len(maclist)) + " *** the MACs that SOTI has for Store#" + st)
	print(maclist)
	print("*" * 100)
	print("*" * 100)
	print("\n")


	#Quit the program if there are no mac addresses found for the store number that you entered
	if str(len(maclist)) == "0":
		print("\n")
		print("*" * 100)
		print("THERE ARE NO MC40s REGISTERED TO THIS STORE - I QUIT")
		print("*" * 100)
		print("\n")
		print()
		quit()




	###this is wher we connect and do stuff
	ip_addr = storeiplist[stcounter]
	#ip_addr = "10.18.62.160"
	username = 'admin'
	password = 'farkfark'



	# create the SSH client
	ssh_pre = paramiko.SSHClient()

	# add the server's keys automatically
	ssh_pre.set_missing_host_key_policy(paramiko.AutoAddPolicy())

	# connects to the server
	ssh_pre.connect(ip_addr, username=username, password=password)

	#invoke a shell
	ssh = ssh_pre.invoke_shell(term='vt100', width=80, height=24, width_pixels=0, height_pixels=0)

	#turn off the pager so that the output is not broken apart
	ssh.send("term len 0\n")
	time.sleep(0.75)










	###----------get details about the voice register pool
	#the register pool table
	regpoolbr = cmd("sh voice register pool all br")
	print(regpoolbr)
	print("\n")

	#number of clients in register pools
	regpoolcount = cmd("sh voice register pool all br | count REGISTER")
	#print regpoolcount
	#print "\n"

	#find the number returned by the show command with count
	vpreg = "Number of lines which match regexp = (.+)\r"
	vp = re.findall(vpreg, regpoolcount)
	vpcount = int(vp[0])
	print("Number of Register Pools configured for this store: " + str(vpcount))
	#print vpcount
	print("\n")




	###----------get details about the voice register dns
	#number of clients in register dns
	regdncount = cmd("sh run | count voice register dn")
	#print regdncount
	#print "\n"


	#find the number returned by the show command with count
	dnreg = "Number of lines which match regexp = (.+)\r"
	dn = re.findall(dnreg, regdncount)
	dncount = int(dn[0])
	#print "Number of Register DNs configured for this store: " + str(dncount)
	#print dncount
	#print "\n"




	###
	###enter global config mode
	###
	cmd("conf t")




	#delete as many voice register pools as were found in the router
	counter = 1
	while counter <= vpcount:
		cmd("no voice register pool " + str(counter))
		print("no voice register pool " + str(counter))
		counter += 1



	#delete as many voice register dns as were found in the router
	counter = 1
	while counter <= dncount:
		cmd("no voice register dn " + str(counter))
		print("no voice register dn " + str(counter))
		counter += 1






	#generate the code based on the macs in the list
	iterator = 1
	for m in maclist:
		iteratorx5 = iterator + 500

		cmd("voice register dn " + str(iterator))
		print("voice register dn " + str(iterator))
		cmd("number " + str(iteratorx5))
		print("number " + str(iteratorx5))
		cmd("allow watch")
		print("allow watch")
		cmd("name MC40 " + str(iterator))
		print("name MC40 " + str(iterator))
		cmd("label " + str(iteratorx5))
		print("label " + str(iteratorx5))

		cmd("voice register pool " + str(iterator))
		print("voice register pool " + str(iterator))
		cmd("busy-trigger-per-button 1")
		print("busy-trigger-per-button 1")
		cmd("id mac " + m)
		print("id mac " + m)
		cmd("type 9971")
		print("type 9971")
		cmd("number 1 dn " + str(iterator))
		print("number 1 dn " + str(iterator))
		cmd("dtmf-relay sip-notify")
		print("dtmf-relay sip-notify")
		cmd("username " + str(iteratorx5) + " password " + str(iteratorx5))
		print("username " + str(iteratorx5) + " password " + str(iteratorx5))
		cmd("no call-waiting")
		print("no call-waiting")
		cmd("codec g711ulaw")
		print("codec g711ulaw")
		iterator += 1

	print("\n")
	print("*" * 80)
	print("Creating Vocie Register profiles")
	print("*" * 80)
	print("\n")
	cmd("voice register global")
	cmd("create profile")
	cmd("end")
	print("\n")
	print("*" * 80)
	print("Saving Configuration")
	print("*" * 80)
	print("\n")
	cmd("wr")
	cmd("")

	#the register pool table
	regpoolbr = cmd("sh voice register pool all br")
	print(regpoolbr)

	#number of clients in register pools
	regpoolcount = cmd("sh voice register pool all br | count REGISTER")
	print(regpoolcount)

	#Put everything back and get out
	ssh.send("term len 24\n")
	time.sleep(0.75)
	ssh.close()




	#increment the coutner, moving on to the next store in the list
	stcounter +=1