def sendPRReports():
def filteringLatin():
excel.gotoCell("E10")
time.sleep(5)
pyautogui.hotkey("alt","down")
pyautogui.press("down")
pyautogui.press("right")
pyautogui.press("right")
pyautogui.press("t")
pyautogui.press("enter")
time.sleep(2)
# print(utils.msgbox("teste","teste",4))
# sys.exit("")
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Client Support\Pipeline Management\Weekly PR Reports"
fileName = os.path.join(saveDir , utils.getStampedStr("Latin America PRs as of {}.xlsx","%b %d"))
dateStr = utils.getStampedStr("{}","%b %d")
excel.openPBR(pars.pbReportsABT,"")
excel.runReportByName(reportName=RPT_LPR_BOTH)
# filteringLatin()
excel.saveReportToFile(fileName,excel.AutofitColumns)
excel.copyReportToClipBoard()
subject = "Latin America PR Report as of " + dateStr
body = utils.getEmailTemplate("LatamPRs.txt")
sendVerseEmail(subject, pars.distro02, body, fileName,11, True)
def geraEspelhoDePonto(prev):
start()
login()
saveDir = "C:\\AlessandroBAM\\2017m01 - Abbott DPE-PgM-PM\\IBM Brazil\\RH\\Aprovacao Mensal de Horas Extras e Standby\\2019m01 - Melhoria do processo de validacao de Certponto vs ILC\\Certponto"
if prev:
fileName = saveDir + "\\" + utils.prevMonth().strftime("%Ym%m") + " - Espelho Certponto.csv"
else:
fileName = saveDir + "\\" + utils.today().strftime("%Ym%m") + " - Espelho Certponto.csv"
pyautogui.click(125,192, interval=7) #click em tratamento de ponto
pyautogui.click(112,349, interval=3) #click espelho de ponto
pyautogui.click(561,489, interval=1) #click on periodo simplificado
if prev:
pyautogui.click(303,632, interval=1) #click on mes passado
else:
pyautogui.click(297,607, interval=1) #click on mes atual
pyautogui.click(297,699, interval=2) #click on gerar
pyautogui.click(1852,770, interval=1) #click on Acoes
pyautogui.click(1773,882, interval=1) #click Exportar CSV reduzido-
pyautogui.click(545,219, interval=10) #click SIM
pyautogui.hotkey("alt","s")
pyautogui.press("enter",interval=6)
if os.path.isfile(fileName ):
os.remove(fileName) #deleting existing file
pyautogui.typewrite(fileName)
time.sleep(2)
pyautogui.press("tab",interval=1)
pyautogui.press("enter",interval=1)
excel.openPBR(pars.pbReportsCPG,"")
def sendMissingRoster():
def none():
excel.AutofitColumns()
excel.setColumnSize("E", 30)
excel.setColumnSize("F", 30)
excel.setColumnSize("G", 30)
def file_massage():
# Highlighting the columns
excel.gotoCell("H13")
pyautogui.keyDown("shift")
pyautogui.press("right")
pyautogui.press("right")
pyautogui.keyUp("shift")
pyautogui.keyDown("alt")
pyautogui.press("h")
pyautogui.press("h")
pyautogui.keyUp("alt")
pyautogui.press("right",presses=5,interval=1)
pyautogui.press("enter")
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Governance\Plan & Build\Weekly Missing Roster Report"
fileName = os.path.join(saveDir , utils.getStampedStr("Roster Status as of {}.xlsx","%b %d"))
dateStr = utils.getStampedStr("{}","%b %d")
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName=RPT_MRO_BOTH)
file_massage()
excel.saveReportToFile(fileName, none)
excel.copyReportToClipBoard()
subject = "Roster Status as of " + dateStr
body = utils.getEmailTemplate("Missing Roster.txt")
sendVerseEmail(subject, pars.distro01, body, fileName, 11, True)
def sendPBReportsByEmail(pbrfile,
reportName,
emailSubject,
distro,
body,
saveExcelReportTo,
timeStampFileFormat="today",
IncludeReportInBodyReport=False):
# timeStampFileFormat - today, last_friday, none
excel.openPBR(pbrfile)
fileName = excel.runReportByName(reportName, saveExcelReportTo,
timeStampFileFormat)
# utils.alert(fileName)
if IncludeReportInBodyReport:
excel.copyReportToClipBoard()
emailSubject = emailSubject + " as of " + utils.getTimeStampStr(
"%b %d", timeStampFileFormat)
verse.sendVerseEmail(emailSubject, distro, body, fileName, True)
# sendPBReportsByEmail(pars.pbReportsABT,"CGP - Labor vs Non Labor","Test Report", ["*****@*****.**"],"This is a automatic email", r"C:\Temp\report01.xlsx","today", True)
# sendPBReportsByEmail(pbrfile = pars.pbReportsABT,
# reportName = "CP - Hours, Cost, Rev, Profit",
# emailSubject = "CP - Hours, Cost, Rev, Profit - WE",
# distro = ["*****@*****.**"],
# body = utils.getEmailTemplate("IPPF vs Cost Email.txt"),
# saveExcelReportTo = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Governance\Plan & Build\AUTO - Weekly CP - Hours, Cost, Rev, Profit report\CP - Hours, Cost, Rev, Profit.xlsx",
# timeStampFileFormat = "last_friday",
# IncludeReportInBodyReport = True)
# excel.AutofitColumns()
def sendCPHoursCostRevProfit():
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Governance\Plan & Build\AUTO - Weekly CP - Hours, Cost, Rev, Profit report"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - CP - Hours, Cost, Rev, Profit.xlsx",utils.YYmMM_DD, lastFriday=True))
dateStr = utils.getStampedStr("{}","%b %d",lastFriday=True)
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName="CP - Hours, Cost, Rev, Profit")
excel.saveReportToFile(fileName, excel.AutofitColumns)
subject = "CP - Hours, Cost, Rev, Profit (Confirmed) - WE " + dateStr
body = utils.getEmailTemplate("IPPF vs Cost Email.txt").format(dateStr)
sendVerseEmail(subject, ["*****@*****.**"], body, fileName,10,True)
def sendWrongContractFlag():
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Governance\Plan & Build\2019m07 - Fixed Projects - Daily Report on Wrong Contract Type"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - Possibly Wrong Contract Type.xlsx",utils.YYmMM_DD))
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName="FP Projects Possibly with Incorrect Contract Type on CP")
excel.saveReportToFile(fileName,excel.AutofitColumns)
excel.copyReportToClipBoard()
subject = "Daily Report - Possibly Wrong Contract Type in CP"
body = utils.getEmailTemplate("Wrong Contract.txt")
sendVerseEmail(subject, pars.distroTest, body, fileName,11, True)
def send2019DMCApprovedPRs():
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Client Support\Pipeline Management\Weekly PR Reports"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - 2019 DMC Approved PRs.xlsx",utils.YYmMM_DD))
dateStr = utils.getStampedStr("{}","%b %d",lastFriday=False)
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName="All 2019 DMC Approved PRs")
excel.saveReportToFile(fileName, excel.AutofitColumns)
# excel.copyReportToClipBoard()
subject = "2019 DMC Approved PRs - " + dateStr
body = utils.getEmailTemplate("Weekly 2019 DMC PRs.txt").format(dateStr)
sendVerseEmail(subject, ["*****@*****.**"], body, fileName,11,True)
def sendJNJBreachedTickets():
saveDir = r"C:\Users\ALESSANDROAlves\Box\Ticket Data J&J Latam\Automation\00 - SLA Management\Daily Breached Tickets Report"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - Breached tickets in Current Tableau Month.xlsx",utils.YYmMM_DD))
dateStr = utils.getStampedStr("{}","%b %d")
excel.openPBR(pars.pbReportsJNJ,"")
excel.runReportByName(reportName="Breached Incidents - Current Month")
excel.saveReportToFile(fileName,excel.AutofitColumns)
excel.copyReportToClipBoard()
subject = "JnJ Breached Tickets for the Current Tableau Month as of " + dateStr
body = utils.getEmailTemplate("JNJ Breached Tickets.txt")
sendVerseEmail(subject, ["*****@*****.**","*****@*****.**","*****@*****.**","*****@*****.**","*****@*****.**","*****@*****.**","*****@*****.**","*****@*****.**"], body, fileName,20, True)
def sendCNBReport():
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Staffing\Non Billable Hours\Weekly Update to Leadership (email)"
fileName = os.path.join(saveDir , utils.getStampedStr("CNB Weekly Report for {}.xlsx","%b %d", lastFriday=True))
dateStr = utils.getStampedStr("{}","%b %d",lastFriday=True)
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName=RPT_CNB_ATTA)
excel.saveReportToFile(fileName, excel.AutofitColumns)
excel.runReportByName(reportName="Non Billable Hours Variance")
pyautogui.press("enter")
# time.sleep(10)
excel.copyReportToClipBoard()
subject = "Weekly CNB Variance Report - WE " + dateStr
body = utils.getEmailTemplate("CNB.txt").format(dateStr)
sendVerseEmail(subject, pars.distro01, body, fileName,11,True)
def sendClaimWatingReport():
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Staffing\Non Billable Hours\Weekly Claim Waiting Variance Report - WE Month Day"
fileName = os.path.join(saveDir , utils.getStampedStr("Claim Waiting week of {}.xlsx","%b %d", lastFriday=True))
dateStr = utils.getStampedStr("{}","%b %d",lastFriday=True)
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName=RPT_CWA_ATTA)
excel.saveReportToFile(fileName, excel.AutofitColumns)
excel.runReportByName(reportName=RPT_CWA_BODY)
excel.copyReportToClipBoard()
subject = "Weekly Claim Waiting Report - WE " + dateStr
body = utils.getEmailTemplate("ClaimWaiting.txt").format(dateStr)
sendVerseEmail(subject, pars.distro01, body, fileName,11,True)
def saveLaborHours(self):
saveDir = r"C:\Users\ALESSANDROAlves\Box\Plan & Build\Governance\Companion Agreements\Brazil\04 - Actuals - Labor"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - IPPF Actuals Extract.csv",utils.YYmMM_DD))
utils.automate("IPPF Actual Hours Report",1,10)
if os.path.isfile(fileName ):
os.remove(fileName) #deleting existing file
utils.automate("IPPF Actual Hours Report",11,11)
pyautogui.typewrite(fileName)
time.sleep(2)
pyautogui.press("enter")
# Refresh PBReports
excel.openPBR(pars.pbReportsLocal, utils.image(r"excel\files\PB Reports Brazil.xlsm.PNG"))
utils.automate("Refresh PowerQueries",10,30)
def sendBPCSCategorizationReport():
template =r'"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\CIC Brazil\Automation\F0001 - Create a automatic process for Ticket Categorization in BPCS\UncatTemplate.xlsx"'
saveDir = r"C:\Users\ALESSANDROAlves\Box\Abbott Latam\Abbott Latam - Workspace\BPCS ITSM Categorization"
fileName = os.path.join(saveDir , utils.getStampedStr("{} - Uncategorized tickets.xlsx",utils.YYmMM_DD))
dateStr = utils.getStampedStr("{}","%b %d")
excel.openPBR(pars.pbReportsABT, "")
excel.runReportByName(reportName=RPT_ITMS_CAT)
# excel.saveReportToFile(fileName)
excel.gotoCell("D14") #selecting data in the pivot table
excel.endSelect("down") #selecting data in the pivot table
excel.endSelect("right") #selecting data in the pivot table
pyautogui.hotkey("shift","left") #selecting data in the pivot table
excel.copy() #selecting data in the pivot table
excel.open(template,4)
excel.gotoCell("B2")
excel.pasteValues()
excel.AutofitColumns()
pyautogui.press("up")
excel.endSelect("right")
excel.activateAutoFilter()
excel.gotoCell("A1")
excel.setColumnSize("F",95)
utils.moveFilesToArchive(fileName)
excel.saveAs(fileName)
excel.close()
excel.runReportByName(reportName="ITSM - Uncategorized Tickets Summary - Latam")
excel.findText("Grand Total",0)
pyautogui.press("right")
pyautogui.press("right")
excel.copy()
ticketCount = pyperclip.paste().replace('\n', '').replace('\r', '')
excel.copyReportToClipBoard()
subject = ("Weekly Ticket Categorization - There are {} tickets pending categorization as of " + dateStr).format(ticketCount)
body = utils.getEmailTemplate("Categorized Tickets.txt")
sendVerseEmail(subject, pars.distroBPCS+pars.distroADAM, body.format(ticketCount), "", 20,True)
def updateSLATrackerFile():
excel.openPBR(pars.pbReportsJNJ)
excel.runReportByName("SLA - Tracker")
excel.copyTable("E14",ul_offsetX=1, lr_offsetX=-1)
excel.openPBR(pars.slaTracker)
excel.gotoCell("D5")
excel.pasteValues()
excel.openPBR(pars.pbReportsJNJ)
excel.filterPivot("E9","All")
excel.copyTable("E14",ul_offsetX=1, lr_offsetX=-1)
excel.openPBR(pars.slaTracker)
excel.gotoCell("H5")
excel.pasteValues()
excel.gotoCell("A17")
def postMonthlyReport(reportName, targetCell):
excel.openPBR(pars.pbReportsJNJ)
excel.runReportByName(reportName)
excel.copyTable("D13")
pasteValuesToSheet(pars.pbReportsJNJ, dataFile, targetCell)
def runPBReportRefresh():
excel.openPBR(pars.pbReportsCPG,"")
def getETimeRequests():
# print (dumpDir)
start()
login()
runAndSaveReport()
excel.openPBR(pars.pbReportsCPG),
def refreshPBReports(file):
excel.openPBR(file)
excel.PBReportsRefresh()
def updateRosterWithCGPRates():
formulaCol = ""
ratesRange = ""
def formatCGPReport():
nonlocal formulaCol
nonlocal ratesRange
# excel.AutofitColumns()
limitCol = excel.getlimitY("A2")
excel.right()
excel.enterFormula("New Rate")
formulaCol = excel.getCurrentCol()
limitRow = int(excel.getlimitX("A2",1))-1
rowRange = "A{}:{}{}".format(limitRow,limitCol,limitRow)
excel.gotoCell(limitCol+str(limitRow))
excel.right()
excel.enterFormula(r'=LOOKUP(2,1/({}<>""),{})'.format(rowRange,rowRange))
excel.copy()
excel.up()
excel.endSelect("up")
pyautogui.hotkey("shift","down")
excel.paste()
excel.gotoCell("a2")
ratesRange = "$A$3:${}${}".format(excel.nextColLetter(limitCol),limitRow)
def applyRatesToRoster():
nonlocal formulaCol
nonlocal ratesRange
excel.open(pars.pbRoster, r"excel\files\Roster.xlsx.PNG")
time.sleep(2)
excel.gotoCell("A2000")
excel.end( "up",0,0)
limitRow = excel.getCurrentRow()
rateCol = 9
excel.gotoCell("i2")
excel.addCol()
print ("formulaCol: " + formulaCol)
lookupFormula = "=IFERROR(VLOOKUP(A{},{}{},{},FALSE),J{})".format(limitRow,formulaFileName,ratesRange,int(excel.colNameToNum(formulaCol)),limitRow)
print(lookupFormula)
excel.gotoCell("i" + limitRow)
# fill up
excel.enterFormula(lookupFormula)
excel.copy()
excel.up()
excel.endSelect("up")
excel.paste()
excel.addCol()
excel.right()
excel.end("down")
excel.left()
excel.enterFormula("=IF(J{}>0,J{},K{})".format(limitRow,limitRow,limitRow))
# fill up
excel.copy()
excel.up()
excel.endSelect("up")
excel.paste()
excel.gotoCell("i2")
pyautogui.hotkey("ctrl", "shift","down")
excel.copy()
excel.gotoCell("K2")
excel.pasteValues()
excel.left()
excel.deleteCol()
excel.left()
excel.deleteCol()
# excel.addCol()
# lookupFormula = "=IFERROR(VLOOKUP(A{},{}{},{},FALSE),J{})".format(limitRow,formulaFileName,ratesRange,int(excel.colNameToNum(formulaCol)),limitRow)
# excel.deleteCol()
saveDir = r"C:\AlessandroBAM\2017m01 - Abbott DPE-PgM-PM\Governance\Plan & Build\AUTO - Update Roster Rates Based on Latest CGP"
fileName = utils.getStampedStr("{} - CGP Rates by Emp.xlsx",utils.YYmMM_DD)
fullFileName = os.path.join(saveDir , fileName)
formulaFileName = os.path.join("' " + saveDir , "["+ fileName + "]Sheet1'!")
dateStr = utils.getStampedStr("{}","%b %d",lastFriday=False)
excel.openPBR(pars.pbReportsABT )
excel.runReportByName(reportName="CGP - Cost Rates by Employee")
excel.saveReportToFile(fullFileName, formatCGPReport)
applyRatesToRoster()
def pasteValuesToSheet(sourceFile, targetfile, cell):
excel.openDataSheet(targetfile)
excel.gotoCell(cell)
excel.pasteValues()
excel.openPBR(sourceFile)
pyautogui.press("esc")