from time import strftime
import time, re, xlwt, xlrd, sys, os.path
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from xlutils.copy import copy

'''
Tallyman Auto Excel Scraper
Written By Ben Reich
Version 1.0

This system takes an input (program -u username -p password -f file_location) and then
logs into Tallyman and scrapes the account data for the week

TODO:
- Set up the file format to be that which adheres to what the leads want
- Set up templates so that it conforms to a template they desire
- Monitor Mohammads queues (Catch All etc.)
- Error reporting when username and password are incorrect
- Log errors to file
'''

i = 0
username = None
password = None
file_location = None
for arg in sys.argv:
	if arg.strip() == '-u':
		try:
			if sys.argv[i+1].strip() != '-p' and sys.argv[i+1].strip() != '-f':
				username = sys.argv[i+1]
			else:
				sys.exit('No username set')
		except IndexError:
			sys.exit('No username set')
	elif arg.strip() == '-p':
		try:
			if sys.argv[i+1].strip() != '-f' and sys.argv[i+1].strip() != '-u':
				password = sys.argv[i+1]
			else:
				sys.exit('No password set')
		except IndexError:
			sys.exit('No password set')
	elif arg.strip() == '-f':
		try:
			if sys.argv[i+1].strip() != '-p' and sys.argv[i+1].strip() != '-u':
				file_location = sys.argv[i+1]
				if os.path.exists(file_location) == False:
					sys.exit('File location is invalid')
			else:
				sys.exit('No file location set')
		except IndexError:
			sys.exit('No file location set')
	i += 1

if username == None:
	sys.exit('Username not set')
if password == None:
	sys.exit('Password not set')
if file_location == None:
	sys.exit('File location not set')

start_date = strftime("%d-%m-%Y", time.localtime())
filename = file_location + start_date + ".xls"

def find_by_xpath(locator, driver):
	element = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.XPATH, locator)))
	return element

def close_windows(driver, window_title=None):
	handle_switch = False
	if window_title == None:
		for handle in driver.window_handles:
			driver.switch_to_window(handle)
			driver.close()
	else:
		for handle in driver.window_handles:
			driver.switch_to_window(handle)
			if window_title == driver.title:
				handle_switch = handle
		if handle_switch != False:
			driver.switch_to_window(handle_switch)

def wait_popup(driver):
	try:
		WebDriverWait(driver, 3).until(EC.alert_is_present())
		alert = driver.switch_to_alert()
		alert.accept()
		print "alert accepted"
	except TimeoutException:
		print "no alert"

driver = webdriver.Firefox()
driver.get("https://10.7.148.205/collections/Login.do")
wait_popup(driver)

find_by_xpath('//input[@name="user"]', driver).send_keys(username)
find_by_xpath('//input[@name="password"]', driver).send_keys(password)
find_by_xpath('//input[@name="btnOk"]', driver).click()

wait_popup(driver)
close_windows(driver, "Tallyman")

driver.get("https://10.7.148.205/collections/ShowUserWorklists.do")
wait_popup(driver)
close_windows(driver, "User Work Lists")

find_by_xpath('//*[@id="showAccountCount"]', driver).click()

close_windows(driver, "User Work Lists")

html = driver.page_source
matches = re.findall(r'<td class="label">([A-Za-z0-9\s\)\(]+)<\/td>([\s]+)<td>([\s]+)\(([0-9]+)', html)
close_windows(driver)

lists = {'Arrangement Completed In Arrears': 0,
		 'Adv C1 FPD Call': 0,
		 'Adv C1 High Val Call': 0,
		 'Adv C1 HR Call': 0,
		 'Adv C1 LR Call': 0,
		 'Adv C1 MR Call': 0,
		 'Adv C1 Repeat Call': 0,
		 'Adv C1 Short Ten Call': 0,
		 'Adv C2 High Val Call': 0,
		 'Adv C2 Pre NOD': 0,
		 'Adv C2 HR Call': 0,
		 'Adv C2 MR Call': 0,
		 'Adv C2 Repeat Call': 0,
		 'Adv C2 Short Ten Call': 0,
		 'Adv C3 Call': 0,
		 'Chall C1 High Vall Call': 0,
		 'Chall C1 HR Call': 0,
		 'Chall C1 Repeat Call': 0,
		 'Chall C2 High Val Call': 0,
		 'Chall C2 Pre NOD': 0,
		 'Chall C2 HR Call': 0,
		 'Chall C2 Repeat Call': 0,
		 'Chall C3 Call': 0,
		 'Arr Broken (Collections)': 0,
		 'Arr Incomplete (Collections)': 0}

total_accounts = 0

for match in matches:
	lists[match[0].strip()] = match[3].strip()
	total_accounts += int(match[3].strip())

try:
	workbook = xlrd.open_workbook(filename, formatting_info=True)
	temp_sheet = workbook.sheet_by_name("Hourly Track")
	col_idx = temp_sheet.ncols
	workbook_exists = True
	book = copy(workbook)
	sheet1 = book.get_sheet(0)
except IOError:
	book = xlwt.Workbook(encoding="utf-8")
	sheet1 = book.add_sheet("Hourly Track")
	col_idx = 1
	workbook_exists = False

NoBackground = xlwt.easyxf('font: name Calibri, height 220, colour black')
NoBackgroundBold = xlwt.easyxf('font: name Calibri, height 220, colour black, bold on')
RedBackground = xlwt.easyxf('font: name Calibri, height 220, colour black, bold on; pattern: pattern solid, fore_colour red')
GreenBackground = xlwt.easyxf('font: name Calibri, height 220, colour black, bold on; pattern: pattern solid, fore_colour light_green')
YellowBackground = xlwt.easyxf('font: name Calibri, height 220, colour black, bold on; pattern: pattern solid, fore_colour yellow')

sheet1.col(0).width = 4800
sheet1.col(1).width = 2500

if workbook_exists == False:
	sheet1.write(1, 0, label = "Arr Comp in Arrs", style = NoBackground)
	sheet1.write(2, 0, label = "ADV C1 FPD Call", style = NoBackground)
	sheet1.write(3, 0, label = "ADV C1 High Val", style = NoBackground)
	sheet1.write(4, 0, label = "ADV C1 HR Call", style = NoBackground)
	sheet1.write(5, 0, label = "ADV C1 LR Call", style = NoBackground)
	sheet1.write(6, 0, label = "ADV C1 MR Call", style = NoBackground)
	sheet1.write(7, 0, label = "ADV C1 Repeat Call", style = NoBackground)
	sheet1.write(8, 0, label = "Adv C1 Short Ten", style = NoBackground)
	sheet1.write(9, 0, label = "Adv C2 High Val", style = NoBackground)
	sheet1.write(10, 0, label = "Adv C2 Pre NOD", style = NoBackground)
	sheet1.write(11, 0, label = "Adv C2 HR Call", style = NoBackground)
	sheet1.write(12, 0, label = "Adv C2 MR Call", style = NoBackground)
	sheet1.write(13, 0, label = "Adv C2 Repeat Call", style = NoBackground)
	sheet1.write(14, 0, label = "Adv C2 Short Ten", style = NoBackground)
	sheet1.write(15, 0, label = "Adv C3 Call", style = NoBackground)
	sheet1.write(16, 0, label = "Chall C1 High Val Call", style = NoBackground)
	sheet1.write(17, 0, label = "Chall C1 HR Call", style = NoBackground)
	sheet1.write(18, 0, label = "Chall C1 Repeat Call", style = NoBackground)
	sheet1.write(19, 0, label = "Chall C2 High Val Call", style = NoBackground)
	sheet1.write(20, 0, label = "Chall C2 Pre NOD", style = NoBackground)
	sheet1.write(21, 0, label = "Chall C2 HR Call", style = NoBackground)
	sheet1.write(22, 0, label = "Chall C2 Repeat Call", style = NoBackground)
	sheet1.write(23, 0, label = "Chall C3 Call", style = NoBackground)
	sheet1.write(24, 0, label = "Arr Broken", style = NoBackground)
	sheet1.write(25, 0, label = "Arr Incomplete", style = NoBackground)

def write_num(row_idx, col_idx, list_name):
	if col_idx > 1:
		if int(lists[list_name])==0 && int(temp_sheet.cell_value(row_idx, col_idx).value) > 0:
			sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = GreenBackground)
		else:
			difference = int(temp_sheet.cell_value(row_idx, col_idx).value)-int(lists[list_name])
			if difference > 0:
				sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = RedBackground)
			elif difference < 0:
				sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = YellowBackground)
			else:
				sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = NoBackground)
	else:
		if int(lists[list_name])==0:
			sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = GreenBackground)
		else
			sheet1.write(row_idx, col_idx, label = int(lists[list_name]), style = NoBackground)

sheet1.write(0, col_idx, label = strftime("%H:%M", time.localtime()), style = NoBackground)
write_num(1, col_idx, 'Arrangement Completed In Arrears')
write_num(2, col_idx, 'Adv C1 FPD Call')
write_num(3, col_idx, 'Adv C1 High Val Call')
write_num(4, col_idx, 'Adv C1 HR Call')
write_num(5, col_idx, 'Adv C1 LR Call')
write_num(6, col_idx, 'Adv C1 MR Call')
write_num(7, col_idx, 'Adv C1 Repeat Call')
write_num(8, col_idx, 'Adv C1 Short Ten Call')
write_num(9, col_idx, 'Adv C2 High Val Call')
write_num(10, col_idx, 'Adv C2 Pre NOD')
write_num(11, col_idx, 'Adv C2 HR Call')
write_num(12, col_idx, 'Adv C2 MR Call')
write_num(13, col_idx, 'Adv C2 Repeat Call')
write_num(14, col_idx, 'Adv C2 Short Ten Call')
write_num(15, col_idx, 'Adv C3 Call')
write_num(16, col_idx, 'Chall C1 High Val Call')
write_num(17, col_idx, 'Chall C1 HR Call')
write_num(18, col_idx, 'Chall C1 Repeat Call')
write_num(19, col_idx, 'Chall C2 High Val Call')
write_num(20, col_idx, 'Chall C2 Pre NOD')
write_num(21, col_idx, 'Chall C2 HR Call')
write_num(22, col_idx, 'Chall C2 Repeat Call')
write_num(23, col_idx, 'Chall C3 Call')
write_num(24, col_idx, 'Arr Broken (Collections)')
write_num(25, col_idx, 'Arr Incomplete (Collections)')

sheet1.write(26, col_idx, total_accounts, style = NoBackground)
if workbook_exists == True:
	sheet1.write(27, col_idx, label = (temp_sheet.cell_value(rowx=26, colx=(col_idx-1))-total_accounts), style = NoBackground)

book.save(filename)