#!c:/python27/python
#filename : GetPaetecReport.py
#Version 1.3
'''
Created on Oct 18, 2012
@author: chu
'''
import time, os, getpass, win32com.client
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import Select, WebDriverWait
def endprg():
print ""
raw_input ("Press Enter to close.")
#Download Reports
def getrept(accounts):
id = raw_input("Login ID:")
pw = getpass.getpass()
filepath=os.path.join(os.getcwd(),'Paetec')
# Setup FirefoxProfile.
fp = webdriver.FirefoxProfile()
fp.set_preference("browser.download.folderList",2)
fp.set_preference("browser.download.manager.showWhenStarting",False)
fp.set_preference("browser.download.manager.closeWhenDone",True)
fp.set_preference("browser.helperApps.alwaysAsk.force",False)
fp.set_preference("browser.download.dir",filepath)
fp.set_preference("browser.helperApps.neverAsk.saveToDisk","application/vnd.ms-excel")
# use Firefox to download reports
print ""
print "Downloading reports from wxxxstreamonline.com....."
print ""
driver = webdriver.Firefox(firefox_profile=fp)
driver.get('https://www.wxxxstreamonline.com/pol/Home.action')
time.sleep(2)
# Login to website
driver.find_element_by_id("username").clear()
driver.find_element_by_id("username").send_keys(id)
driver.find_element_by_id("password").clear()
driver.find_element_by_id("password").send_keys(pw)
driver.find_element_by_id("submitButton").click()
time.sleep(3)
driver.find_element_by_link_text('View My Billing Reports').click()
time.sleep(1)
# Get into different accounts
for account in accounts:
Select(driver.find_element_by_name("selectedCustomer")).select_by_visible_text(account)
time.sleep(1)
Select(driver.find_element_by_id("reportList")).select_by_visible_text("Usage Billed Outbound")
driver.find_element_by_id("viewReport").click()
time.sleep(15)
for handle in driver.window_handles:
driver.switch_to_window(handle)
if driver.title == 'Wxxxstream Online' :
break
Select(driver.find_element_by_id("ReportViewer1_ctl01_ctl05_ctl00")).select_by_visible_text("Excel")
driver.find_element_by_link_text('Export').click()
time.sleep(10)
while True:
if len(driver.window_handles) > 2:
time.sleep(5)
else:
break
driver.close()
driver.switch_to_window(driver.window_handles[0])
time.sleep(8)
#rename downloaded file
if os.path.exists(os.path.join(filepath,'Billed Usage.xls')):
filename = account[-7:] + '.xls'
os.rename(os.path.join(filepath,'Billed Usage.xls'), os.path.join(filepath,filename))
time.sleep(2)
#Logout from Paeteconline.com
driver.find_element_by_partial_link_text('Logout').click()
time.sleep(1)
driver.close()
#Generate Excel Report
def genrept(accounts):
xl=win32com.client.Dispatch('Excel.Application')
xl.Visible=0
filepath=os.path.join(os.getcwd(),'Paetec')
reportname = time.strftime("%Y%m") + "01"
#Copy sheets
i=1
for account in accounts:
filename=os.path.join(filepath,(account[-7:]+'.xls'))
if account[-7:] == '4xxxx91':
wb=xl.Workbooks.Open(filename)
sh=wb.Worksheets(i)
sh.Name=account[-7:]
else:
wbt=xl.Workbooks.Open(filename)
sht=wbt.Worksheets(1)
sht.Copy (None,wb.Worksheets(i-1))
wbt.Close()
sh=wb.Worksheets(i)
sh.Name=account[-7:]
i+=1
shnew=wb.Worksheets.Add(None,wb.Worksheets(i-1))
shnew.Name=reportname
#Copy Cells
for i in range(1,wb.Worksheets.Count):
sh=wb.Worksheets(i)
if i==1:
startrow=5
else:
startrow=6
sh.Range(sh.Cells(startrow,1),sh.Cells(sh.UsedRange.Rows.Count,sh.UsedRange.Columns.Count)).Copy()
shnew.Paste(shnew.Cells(shnew.UsedRange.Rows.Count+startrow-5,1))
#Adjust new sheet
shnew.Columns("D:D").Delete()
shnew.UsedRange.WrapText=False
shnew.Columns("A:I").AutoFit()
shnew.Sort.SortFields.Clear()
shnew.Range(shnew.Cells(2,1),shnew.Cells(shnew.UsedRange.Rows.Count,shnew.UsedRange.Columns.Count)).Sort(Key1=shnew.Range("A2"), Order1=1, Key2=shnew.Range("F2"), Order2=1,Key3=shnew.Range("C2"), Order3=1)
newreportpath = os.path.join(filepath,(reportname+'.xls'))
wb.SaveAs(newreportpath)
wb.Close()
xl.Application.Quit()
#Print to console
print ""
print "New report is saved on %s." %newreportpath
print ""
print "*******************************************************************"
print "** This program will download the last month **"
print "** Usage Billed Outbound reports from wxxxxstreamonline.com **"
print "** And will generate a new Excel Report. **"
print "** Version 1.3 **"
print "*******************************************************************"
print ""
accounts = ["Mxxx - 4xxxx91", "Mxxx - Hoboken, NJ - 4xxxx36", "Mxxx - Kent, WA - 4xxxx11","Mxxx - NY, NY - 4xxxx34"]
goodtogo = 'yes'
try:
getrept(accounts)
except:
print "Failed to download reports from wxxxstreamonline.com."
goodtogo = "no"
if goodtogo == 'yes':
try:
genrept(accounts)
except:
print "Failed to generate new excel reports."
endprg()
