import pyautogui
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
from pywinauto.application import Application
import time
import csv
import os
import sys
import pywinauto
from datetime import datetime
from functions.functions_utils import tm_init
## get the appliation handler from the init function
templa = tm_init()[0]
app = tm_init()[1]
print("Starting...")
## start
mainContractsTab = templa.child_window(title='Contracts', control_type='TabItem')
mainContractsTab.click_input()
mainContractsWindow = templa.child_window(title='Contracts', control_type='Window')
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'BackToLastQA'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
import pyautogui
import pandas as pd
import time
import pywinauto
from datetime import datetime
from functions.functions_utils import tm_init
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
## start
templa.child_window(title='Product List', control_type='TabItem').click_input()
mainProductsWindow = templa.child_window(title='Product List', control_type='Window')
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'Product Cost'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
productCode = df['PRODUCT-CODE']
supplierCode = df['SUPPLIER-CODE']
supplierCodeRe = df['SUPPLIER-CODE-RE']
itemName = df['ITEMS']
cost = df['COST']
def Create_Site_Structures():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
# start
print("Starting...")
mainSiteStructuresTab = templa.child_window(title='Site Structures',
control_type='TabItem')
mainSiteStructuresTab.click_input()
mainSiteStructuresWindow = templa.child_window(title='Site Structures',
control_type='Window')
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'Create Site Structures'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
print("Reading Excel...")
for i in df.index:
structureCode = df['STRUCTURE CODE']
description = df['DESCRIPTION']
area_items = df['AREAS']
section_end = df['END SECTION']
status = df['STATUS']
if status[i] == "Done":
print(description[i] + " is Done")
continue
if status[i] == "Same Section":
## no need print out infomation
## undless in debug mode
# print(description[i] + " is under Same Section")
continue
if status[i] == "Skip":
print(description[i] + " is Skipped")
continue
if status[i] == "Stop":
print("Stop here")
break
# click on the Code Edit Box
# mainSiteStructuresWindow.window(title='Description', control_type='ComboBox').click_input()
print("click New button ...")
new_button = templa.child_window(
title="New",
auto_id="[Group : row Tools] Tool : list_New - Index : 1 ",
control_type="Button")
new_button.click_input()
SiteStructureWindow = app.window(title_re='Site Structure*')
SiteStructureWindow.wait('exists', timeout=35)
print("Site Structure Window opened...")
pyautogui.PAUSE = 1.5
pyautogui.press('tab')
pyautogui.typewrite(description[i])
SiteStructureWindow.child_window(
title='Service areas', control_type='TabItem').click_input()
## loop to input area items
while not section_end[i]:
print("more area item exists ...")
print("i inside while loop now is: " + str(i))
SiteStructureWindow.child_window(
title="Add area", control_type="Button").click_input()
pyautogui.typewrite(str(area_items[i]))
print("added area: " + str(area_items[i]))
i = i + 1
print('no more area item left ... ready to save')
SiteStructureWindow.Save.click_input()
def Site_Reassign():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
mainSitesTab = templa.child_window(title='Sites', control_type='TabItem')
mainSitesTab.click_input()
mainSitesWindow = templa.child_window(title='Sites', control_type='Window')
########################
#
# Setup Excel Sheet
#
########################
site_reallocate_sheet = 'Sites Re-Allocate'
df = pd.read_excel('test.xlsx', sheet_name=site_reallocate_sheet)
print("starting...")
for i in df.index:
siteCode = df['CODE']
siteName = df['SITE']
csm = df['CSM']
tablet = df['TABLET']
status = df['STATUS']
site_code_string = str(siteCode[i])
site_name_string= str(siteName[i])
#print("Site Name:" + siteName[i])
#print("CSM: " + csm[i])
#print("iPad: " + ipad[i])
if status[i] == "Done":
print(site_code_string + " is Done")
continue
if status[i] == "Skip":
print(site_code_string + " is Skipped")
continue
if status[i] == "Stop":
print("Stop here")
break
## convert nan to N/A so that
## we can type this into The System
tablet_name = str(tablet[i])
if tablet_name == 'nan':
tablet_name = 'N/A'
# click on the Code Edit Box
mainSitesWindow.window(title='Code', control_type='ComboBox').click_input()
pyautogui.typewrite(site_code_string)
#####################################
# before open the site,
# check if the site already set up correctly
# check the CSM Name
#####################################
# mainSitesWindow.child_window(title="CSM", control_type="ComboBox").click_input()
# pyautogui.typewrite(csm[i])
# check if the CSM already assigned to this site
#
# MUST make the CSM on the first Column
#
## below function does not work
# regex_string_for_csm = '/' + csm[i] + '/i'
csmExists = mainSitesWindow.child_window(title=csm[i], control_type="DataItem")
if csmExists.exists():
print("site Code: " + site_code_string)
print("site Name: " + site_name_string)
print("Already assigned to " + csm[i])
print("#################################")
print(" ")
pyautogui.moveRel(-25, 25)
pyautogui.click() # reset the select status
else:
print("CSM Different, need to change")
print("New CSM: " + csm[i])
pyautogui.moveRel(-25, 25)
pyautogui.doubleClick() # open the site by double click
# # open analysis details dialouge window
# #siteDetailWindow = app.window(title_re='Site Detail - *')
siteDetailWindow = app.window(title_re='Site Detail - *')
siteDetailWindow.wait('exists', timeout=15)
siteDetailWindow.window(title='Analysis versions', control_type='TabItem').click_input()
print("site name: " + site_name_string)
########################
#
# Need to check if the month is current month
# if True: double click on itself
# if False: click Add button
#
#######################
#siteDetailWindow.print_control_identifiers()
currentYearFull = datetime.now().strftime('%Y') # 2018
currentMonth = datetime.now().strftime('%m') # month in number with 0 padding
itemExist = False
for j in range(1,32): # loop from 1 to 31
day = str(j)
if j <= 9:
day = '0' + day
titleDate= "%s.%s.%s" %(day,currentMonth,currentYearFull)
lastAnalysisItem = siteDetailWindow.window(title=str(titleDate))
# print('date of today: ' + titleDate)
if lastAnalysisItem.exists():
itemExist = True
break
if itemExist: # if the current month entry exists
lastAnalysisItem.click_input(double=True)
print ("open the last item")
else:
siteDetailWindow['Add'].click_input()
print ("add new entry")
## operate the site details analysis window
siteAnalysisWindow = siteDetailWindow.child_window(title_re='Site Analysis Detail - *')
siteAnalysisWindow.wait('exists', timeout=125)
siteAnalysisWindow.window(title='Business analysis', control_type='TabItem').click_input()
# change CSM and Tablet Number
# Edit 39 = CSM, Edit 43 = Tablet
siteAnalysisWindow.Edit39.click_input()
# pyautogui.moveRel(60,0)
time.sleep(1.5)
pyautogui.dragRel(-500,0)
pyautogui.typewrite(csm[i])
pyautogui.press("tab")
time.sleep(1.5)
# print("Located now to: " + str(csm[i]))
pyautogui.moveRel(500,15)
pyautogui.click()
# siteAnalysisWindow.Edit43.click_input()
pyautogui.dragRel(-500,0)
time.sleep(1.5)
pyautogui.typewrite(tablet_name)
pyautogui.press("tab")
time.sleep(1.5)
pyautogui.moveRel(300,20)
pyautogui.click()
pyautogui.press("tab")
# press Accept button
# Save
siteAnalysisWindow.Accept.click_input()
time.sleep(1.5)
siteDetailWindow.Save.click_input()
#siteDetailWindow.window(title='Save', control_type='Button')[0].click_input()
time.sleep(1.5)
print(site_code_string + ": is Done now")
print("###############################")
print(" ")
def KPI_Analysis():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
########################
#
# Setup Excel Sheet
#
########################
analysis_month = datetime.now().month - 1
if analysis_month == 0:
analysis_month = 12
analysis_year = datetime.now().year - 1
else:
analysis_year = datetime.now().year
if analysis_month == 1:
analysis_month_text = 'Jan'
if analysis_month == 2:
analysis_month_text = 'Feb'
if analysis_month == 3:
analysis_month_text = 'Mar'
if analysis_month == 4:
analysis_month_text = 'Apr'
if analysis_month == 5:
analysis_month_text = 'May'
if analysis_month == 6:
analysis_month_text = 'Jun'
if analysis_month == 7:
analysis_month_text = 'Jul'
if analysis_month == 8:
analysis_month_text = 'Aug'
if analysis_month == 9:
analysis_month_text = 'Sep'
if analysis_month == 10:
analysis_month_text = 'Oct'
if analysis_month == 11:
analysis_month_text = 'Nov'
if analysis_month == 12:
analysis_month_text = 'Dec'
monthName = analysis_month_text
yearName = str(analysis_year)
site_reallocate_sheet = 'KPI Analysis'
df = pd.read_excel('test.xlsx', sheet_name=site_reallocate_sheet)
print('starting...')
print('analysis month: ' + monthName + ' analysis year: ' + yearName)
print('analysis month text: ' + analysis_month_text)
########################################################################
#### ####
############ ANALYSIS & GENERATE REPORT #############
## recursively generate analysis report and export to local drive ######
##
########################################################################
for i in df.index:
reportTitle = df['TITLE']
#monthName = df['MONTH']
#yearName = df['YEAR']
fileNameSiteTotals = df['FILE_NAME_SITE_TOTALS']
fileNameAllItems = df['FILE_NAME_ALL_ITEMS']
filePath = df['PATH']
status = df['STATUS']
if status[i] == 'Done':
print(str(reportTitle[i]) + ' is Done')
continue
if status[i] == 'Skip':
print(str(reportTitle[i]) + ' is Skipped')
continue
if status[i] == 'Stop':
print('Stop here')
## if stopped the last Analysis window will not close
## due to the counter will stop counting and not reach the bottom code.
break
analysis_window = app.window(title_re='.*Monthly',
control_type='Window')
## open the report selection window
## auto_id='[Group : report Tools] Tool : Select - Index : 5 ',
liveReportButton = analysis_window.child_window(
title='Select live report',
control_type='Button').click_input()
report_config_window = analysis_window.child_window(
title='QA Analysis Report Configurations',
control_type='Window')
report_config_window.wait('exists', timeout=55)
## type report title
report_config_window.window(title='Description',
control_type='ComboBox').click_input()
pyautogui.typewrite(str(reportTitle[i]))
pyautogui.moveRel(0, 25)
pyautogui.click() # open the site by double click
analysis_window.Select.click_input()
## Press Run report button
analysis_window['Run report'].click_input()
## calculate the loading time
start = time.time()
print('Data loading ...')
## Header defined
siteHeader = analysis_window.child_window(title='Site',
control_type='ComboBox')
siteHeader.wait('exists',
timeout=280) ## wait for the analysis result
end = time.time()
print('Time taken for loading the result: ',
str(int(end - start)) + ' sec')
## once the report loaded, start generating...
dragArea = analysis_window.child_window(auto_id='GroupByBox',
control_type='Group')
qaItemHeader = analysis_window.child_window(
title='QA Item', control_type='ComboBox')
qaSiteAreaHeader = analysis_window.child_window(
title='QA Site Area', control_type='ComboBox')
print('Data loaded, report generating ...')
## Drag area title defined
siteDragArea = analysis_window.child_window(title='Site',
control_type='Button')
qaItemDragArea = analysis_window.child_window(
title='QA Item', control_type='Button')
## drag 'Site' Label up
siteHeader.click_input(button='left', double='true')
time.sleep(1)
pyautogui.moveRel(0, -20)
time.sleep(1)
pyautogui.dragRel(0, -70)
## read below from excel sheet
folderName = monthName + '-' + yearName
print('folder name: ' + folderName)
## save to the folder
## setup a flag, if the same site, no need to change the saving path
save_as_Excel_analysis(window=analysis_window, pathName=filePath[i], folderName=folderName, \
fileName=fileNameSiteTotals[i], flag='first time save to this folder')
#analysis_window.click_input()
## drag 'Site' or other Label down back
time.sleep(1)
siteDragArea.click_input()
pyautogui.dragRel(50, 60)
## drag 'QA Item' Label up
if reportTitle[i] == "DAWR Monthly" or reportTitle[
i] == "PMC Monthly" or reportTitle[i] == "TK MAXX Monthly":
qaSiteAreaHeader.click_input()
time.sleep(1)
pyautogui.moveRel(0, -20)
time.sleep(1)
pyautogui.dragRel(0, -70)
time.sleep(1)
## drag 'QA item' Label to the left
qaItemHeader.click_input(button='left', double='true')
pyautogui.moveRel(0, -20)
## click on Site label to sort it
print('sort by Site name ...')
pyautogui.click()
else:
print('drag QA item to top ...')
qaItemHeader.click_input(button='left', double='true')
time.sleep(1)
pyautogui.moveRel(0, -20)
time.sleep(1)
pyautogui.dragRel(0, -70)
## save to the folder
save_as_Excel_analysis(window=analysis_window, pathName=filePath[i], folderName=folderName , \
fileName=fileNameAllItems[i])
print(str(reportTitle[i]) + ': is Done now')
print('###############################')
print(' ')
## after all analysed, close the analysis window, by pressing Alt+ F4
keyboard.send_keys('%{F4}')
print('finishing analysis...')
def QA_Recipients():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
## start
print("Starting...")
mainContractsTab = templa.child_window(title='Contracts',
control_type='TabItem')
mainContractsTab.click_input()
mainContractsWindow = templa.child_window(title='Contracts',
control_type='Window')
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'QA-Recipient'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
for i in df.index:
siteCode = df['CODE']
siteName = df['SITE']
recipient1 = df['RECIPIENT1']
recipient2 = df['RECIPIENT2']
recipient3 = df['RECIPIENT3']
recipient4 = df['RECIPIENT4']
recipient5 = df['RECIPIENT5']
check1 = df['CHECK1']
check2 = df['CHECK2']
check3 = df['CHECK3']
check4 = df['CHECK4']
check5 = df['CHECK5']
email1 = df['EMAIL1']
email2 = df['EMAIL2']
email3 = df['EMAIL3']
email4 = df['EMAIL4']
email5 = df['EMAIL5']
jobTitle1 = df['JOB TITLE1']
jobTitle2 = df['JOB TITLE2']
jobTitle3 = df['JOB TITLE3']
jobTitle4 = df['JOB TITLE4']
jobTitle5 = df['JOB TITLE5']
isFailRecipient = df['FAIL RECIPIENT']
status = df['STATUS']
#print("Site Name:" + siteName[i])
#print("CSM: " + csm[i])
#print("iPad: " + ipad[i])
if status[i] == "Done" or status[i] == "Skip":
print(str(siteCode[i]) + " is Done")
continue
if status[i] == "Stop":
print("Stop here")
break
# click on the Code Edit Box
mainContractsWindow.window(title='Site',
control_type='ComboBox').click_input()
pyautogui.typewrite(str(siteCode[i]))
pyautogui.moveRel(0, 25)
pyautogui.doubleClick() # open the site by double click
print("starting...")
# # open analysis details dialouge window
contractDetailWindow = app.window(title_re='Contract - *')
contractDetailWindow.wait('exists', timeout=25)
contractDetailWindow.window(title='QA',
control_type='TabItem').click_input()
# Check if there is QA Items
# if no qa at all, then no need to change qa recipient
if contractDetailWindow.window(title='30').exists():
print("This QA is Monthly currently." + str(siteCode[i]))
elif contractDetailWindow.window(title='90').exists():
print("This QA is Quaterly currently." + str(siteCode[i]))
elif contractDetailWindow.window(title='7').exists():
print("This QA is Weekly currently." + str(siteCode[i]))
elif contractDetailWindow.window(title='14').exists():
print("This QA is Forenightly currently." + str(siteCode[i]))
elif contractDetailWindow.window(title='365').exists():
print("This QA is Yearly currently." + str(siteCode[i]))
## if not match above, there must exist an error
else:
print("----------------------------------------------")
print("This QA is UNKNOWN frequency or QA NOT Exist." +
str(siteCode[i]))
print("Check this please ..." + str(siteCode[i]))
print("----------------------------------------------")
## if you want add the recipient without checking the qa exists or not
## comment below 2 lines
contractDetailWindow.Close.click_input()
continue
## QA failure recipients button click.
if str(isFailRecipient[i]).lower() == 'yes':
contractDetailWindow['QA failure recipients'].click_input()
## QA recipients button click.
else:
contractDetailWindow['QA form recipients'].click_input()
contractRecipientsWindow = contractDetailWindow.child_window(
title_re='Contract Recipients - *')
contractRecipientsWindow.wait('exists', timeout=135)
#contractRecipientsWindow.wait('exists', timeout=15)
print("check recipient")
recipientExitEither = False
isChecked = False
recipientsAddingList = [recipient1[i]]
checkStateList = [check1[i]]
emailList = [email1[i]]
jobTitleList = [jobTitle1[i]]
# you need add more recipient if recipient2 in excel not empty
if str(recipient2[i]) != "nan":
recipientsAddingList.append(recipient2[i])
checkStateList.append(check2[i])
emailList.append(email2[i])
jobTitleList.append(jobTitle2[i])
print('recipient 2 added: ' + str(recipient2[i]))
if str(recipient3[i]) != "nan":
recipientsAddingList.append(recipient3[i])
checkStateList.append(check3[i])
emailList.append(email3[i])
jobTitleList.append(jobTitle3[i])
print('recipient 3 added: ' + str(recipient3[i]))
if str(recipient4[i]) != "nan":
recipientsAddingList.append(recipient4[i])
checkStateList.append(check4[i])
emailList.append(email4[i])
jobTitleList.append(jobTitle4[i])
print('recipient 4 added: ' + str(recipient4[i]))
if str(recipient5[i]) != "nan":
recipientsAddingList.append(recipient5[i])
checkStateList.append(check5[i])
emailList.append(email5[i])
jobTitleList.append(jobTitle5[i])
print('recipient 5 added: ' + str(recipient5[i]))
arrayCount = len(recipientsAddingList)
print('now is row: ' + str(i + 2) + ' in Excel Sheet')
## loop over the recipient names
for k in range(arrayCount):
print("recipient's name array total: " + str(arrayCount) +
', now is on ' + str(k + 1))
## Need to check 2 ways around
## firstname lastname
## lastname firstname
##
nameArray = str(recipientsAddingList[k]).split(" ")
for name in nameArray:
print('the name in array is: ' + name)
if len(nameArray) == 1: ## name only contains one word
recipient_name = nameArray[0]
print('Name must be 2x words, and currently is ',
recipient_name)
if len(nameArray) == 2: ## name contains two words
nameFirstLast = nameArray[0] + " " + nameArray[1]
nameLastFirst = nameArray[1] + " " + nameArray[0]
recipientEntryFL = contractRecipientsWindow.window(
title=nameFirstLast)
recipientEntryLF = contractRecipientsWindow.window(
title=nameLastFirst)
print("check name exist or not: " +
recipientsAddingList[k])
recipientExitEither = recipientEntryFL.exists(
) or recipientEntryLF.exists()
recipientExitBoth = recipientEntryFL.exists(
) and recipientEntryLF.exists()
checkStateOnExcel = str(int(checkStateList[k]))
## Recipient Exist in the List First Name Last Name
if recipientExitBoth:
if checkStateOnExcel == "0":
print("duplicate name exist: " + nameFirstLast)
recipientEntryCheckboxLF = recipientEntryLF.child_window(
title="Receive documents?",
control_type="CheckBox")
recipientEntryCheckboxFL = recipientEntryFL.child_window(
title="Receive documents?",
control_type="CheckBox")
isCheckedLF = recipientEntryCheckboxLF.get_toggle_state(
)
isCheckedFL = recipientEntryCheckboxFL.get_toggle_state(
)
if str(isCheckedFL) != checkStateOnExcel:
recipientEntryCheckboxLF.toggle()
recipientEntryCheckboxFL.toggle()
print("check state CHANGED to: " +
checkStateOnExcel)
else:
print("check state SAME as before: " +
checkStateOnExcel)
if checkStateOnExcel == "1":
print("duplicate name exist: " + nameFirstLast)
recipientEntryCheckboxLF = recipientEntryLF.child_window(
title="Receive documents?",
control_type="CheckBox")
recipientEntryCheckboxFL = recipientEntryFL.child_window(
title="Receive documents?",
control_type="CheckBox")
isCheckedLF = recipientEntryCheckboxLF.get_toggle_state(
)
isCheckedFL = recipientEntryCheckboxFL.get_toggle_state(
)
# If currently not checked, let Firstname Lastname check
if str(isCheckedFL) != checkStateOnExcel:
recipientEntryCheckboxFL.toggle()
print("First Last, state CHANGED to: " +
checkStateOnExcel)
# If currently checked, let Firstname Lastname remained
if str(isCheckedFL) == checkStateOnExcel:
print("First Last, state Same as: " +
checkStateOnExcel)
# If currently checked, let Lastname Firstname off check
if str(isCheckedLF) == checkStateOnExcel:
recipientEntryCheckboxFL.toggle()
print("duplicate name turned off")
# If currently not checked, let Lastname Firstname keep un-checked
if str(isCheckedLF) != checkStateOnExcel:
print("duplicated name no need change")
elif recipientEntryFL.exists():
print("exist: " + nameFirstLast)
recipientEntryCheckboxFL = recipientEntryFL.child_window(
title="Receive documents?",
control_type="CheckBox")
isCheckedFL = recipientEntryCheckboxFL.get_toggle_state(
)
print('state in system now: ' + str(isCheckedFL))
print(" ")
print('recipient state should be: ' +
checkStateOnExcel)
if str(isCheckedFL) != checkStateOnExcel:
recipientEntryCheckboxFL.toggle()
print("check state CHANGED to: " +
checkStateOnExcel)
else:
print("check state SAME as before: " +
checkStateOnExcel)
## Recipient Exist in the List, Last Name First Name
elif recipientEntryLF.exists():
print("exist: " + nameLastFirst)
recipientEntryCheckboxLF = recipientEntryLF.child_window(
title="Receive documents?",
control_type="CheckBox")
isCheckedLF = recipientEntryCheckboxLF.get_toggle_state(
)
print('state in system now: ' + str(isCheckedLF))
print(" ")
print('recipient state should be: ' +
checkStateOnExcel)
if str(isCheckedLF) != checkStateOnExcel:
recipientEntryCheckboxLF.toggle()
print("check state CHANGED to: " +
checkStateOnExcel)
else:
print("check state SAME as before: " +
checkStateOnExcel)
## Recipient NOT exist, we need to add a new item
## but also, need to know if add a CSM or Client
elif not recipientExitEither and checkStateOnExcel == "0":
print('recipient not exist, and no need add')
## If need to add a CSM User
elif not recipientExitEither and checkStateOnExcel == "1" and jobTitleList[
k] == "CSM":
print('recipient not exist, and need add')
# click on the add contact button
contractRecipientsWindow.child_window(
title="Add user",
control_type="Button").click_input()
# find the email
print("adding new CSM user to list...")
usersSelectWindow = app.window(title='Users')
usersSelectWindow.wait('exists', timeout=15)
usersSelectWindow.window(
title='Email',
control_type='ComboBox').click_input()
pyautogui.typewrite(str(emailList[k]))
pyautogui.moveRel(0, 25)
pyautogui.click()
usersSelectWindow.Select.click_input()
# if need more to add, continue above
usersSelectWindow.Close.click_input()
## If need to add a Client
elif not recipientExitEither and checkStateOnExcel == "1" and jobTitleList[
k] == "Client":
# click on the add contact button
contractRecipientsWindow.child_window(
title="Add contact",
control_type="Button").click_input()
# find the email
print("adding new Contact to list...")
contactsSelectWindow = app.window(
title='Contacts Select')
contactsSelectWindow.wait('exists', timeout=95)
contactsSelectWindow.window(
title='Email',
control_type='ComboBox').click_input()
pyautogui.typewrite(emailList[k])
pyautogui.moveRel(0, 25)
pyautogui.click()
contactsSelectWindow.Select.click_input()
# if need more to add, continue above
contactsSelectWindow.Close.click_input()
else:
print('recipient exist either:', recipientExitEither)
print('recipient exist both:', recipientExitBoth)
print('need to add:', checkStateOnExcel)
print("something wrong, no conditions is matched")
# Save
contractRecipientsWindow.Save.click_input()
time.sleep(2.5)
contractDetailWindow.Close.click_input()
time.sleep(2.5)
print(str(siteCode[i]) + ": Done now")
print("######################################")
print(" ")
print("All Done")
print(" ")
import pyautogui
import pandas as pd
import time
from datetime import datetime, timedelta, date
import dateutil.relativedelta as relativedelta
import calendar
import pywinauto
from functions.functions_utils import tm_init
##
## need to active the site and open the qa window
## get the appliation handler from the init function
app = tm_init()[1]
print("Starting...")
next_month_today = date.today() + relativedelta.relativedelta(months=1)
next_month = next_month_today.strftime("%m") # type of String
current_year = datetime.now().year
lastday_next_month = calendar.monthrange(current_year, int(next_month))[1]
dateStartString = '01' + next_month + str(current_year)
dateEndString = str(lastday_next_month) + next_month + str(current_year)
# # open analysis details dialouge window
contractDetailWindow = app.window(title_re='Contract - *')
contractDetailWindow.wait('exists', timeout=15)
## Start a New Version
# contractDetailWindow.window(title='New version').click_input(double=True)
## and confirm you want to start a new version
def Create_QA_Templates():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
# start
print("Starting...")
mainQATemplateTab = templa.child_window(title='QA Templates', control_type='TabItem')
mainQATemplateTab.click_input()
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'Create QA Templates'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
print("Reading Excel...")
for i in df.index:
site_structure = df['SITE STRUCTURE']
description = df['DESCRIPTION']
override_score_card = df['OVERRIDE SCORE CARD']
sections = df['SECTIONS']
qa_items = df['QA ITEMS']
qa_item_groups = df['QA ITEM GROUPS']
status = df['STATUS']
if status[i] == "Done":
print(description[i] + " is Done")
continue
if status[i] == "Same Template":
## no need print out infomation
## unless in debug mode
# print(description[i] + " is under Same Section")
continue
if status[i] == "Skip":
print(description[i] + " is Skipped")
continue
if status[i] == "Stop":
print("Stop here")
break
print("click New button ...")
new_button = templa.child_window(title="New", auto_id="[Group : row Tools] Tool : list_New - Index : 1 ", control_type="Button")
new_button.click_input()
QATemplateWindow = app.window(title_re='QA Template*')
QATemplateWindow.wait('exists', timeout=25)
print('New Template Creating ...')
description_textbox = QATemplateWindow.child_window(auto_id="txtDescription", control_type="Edit")
description_textbox.click_input()
pyautogui.typewrite(description[i])
time.sleep(1)
pyautogui.press('tab')
pyautogui.typewrite("Standard")
time.sleep(1)
pyautogui.press('tab')
pyautogui.typewrite(site_structure[i])
time.sleep(1)
pyautogui.press('tab')
print_score_last_page_checkbox = QATemplateWindow.child_window(auto_id="chkPrintScoreCardOnLastPageOnly", control_type="CheckBox")
print_score_last_page_status = print_score_last_page_checkbox.get_toggle_state()
if not print_score_last_page_status:
print_score_last_page_checkbox.toggle()
pyautogui.press('tab')
pyautogui.typewrite(override_score_card[i])
print_image_checkbox = QATemplateWindow.child_window(auto_id="chkPrintImages", control_type="CheckBox")
print_image_status = print_image_checkbox.get_toggle_state()
if not print_image_status:
print_image_checkbox.toggle()
auto_email_complete_checkbox = QATemplateWindow.child_window(auto_id="chkAutoEmailOnComplete", control_type="CheckBox")
auto_email_complete_status = auto_email_complete_checkbox.get_toggle_state()
if not auto_email_complete_status:
auto_email_complete_checkbox.toggle()
QATemplateWindow.child_window(title="QA items", auto_id="TabItem Key items", control_type="TabItem").click_input()
## Start "QA items" tab section
## add Overall criteria
print('Adding QA Items to every Section ...')
QATemplateWindow.child_window(title="Overall criteria", auto_id="[Node] 0", control_type="DataItem").click_input()
QATemplateWindow.child_window(title="Add QA criteria", auto_id="[Group : structure Tools] Tool : Item_AddCriteria - Index : 7 ", control_type="Button").click_input()
QACriteriaWindow = app.window(title_re='QA Criteria')
QACriteriaWindow.wait('exists', timeout=25)
QACriteriaWindow.child_window(title="Description", control_type="ComboBox").click_input()
pyautogui.typewrite(qa_items[i])
pyautogui.moveRel(-25, 25)
pyautogui.click() # open the site by double click
QACriteriaWindow.Select.click_input()
QACriteriaWindow.Close.click_input()
## add qa items for every section
add_qa_item_button = QATemplateWindow.child_window(title="Add QA item", auto_id="[Group : structure Tools] Tool : Item_AddItem - Index : 6 ", control_type="Button")
## Loop to every section here
while True:
i = i + 1 # i need +1 because, the qa item from the 2nd line
section_title = QATemplateWindow.child_window(title=str(sections[i]), control_type="DataItem")
section_title.click_input()
add_qa_item_button.click_input()
QAItemsWindow = app.window(title_re='QA Items')
QAItemsWindow.wait('exists', timeout=25)
QAItemsWindow.child_window(title="Group", control_type="ComboBox").click_input()
pyautogui.typewrite(str(qa_item_groups[i]))
print('Adding Items under section: ' + str(sections[i]))
## Loop to add QA items for each section here
while True:
QAItemsWindow.child_window(title="Coded structure", control_type="ComboBox").click_input()
pyautogui.typewrite(str(qa_items[i]))
#pyautogui.moveRel(-25, 25)
## select the exact item, not just move the mouse down a bit to select.
QAItemRequired = QAItemsWindow.child_window(title=str(qa_items[i]), control_type="DataItem")
QAItemRequired.wait('exists', timeout=10)
QAItemRequired.click_input()
#pyautogui.click() # open the site by selected the item
QAItemsWindow.Select.click_input()
print('Added item: ' + qa_items[i])
## check if next items are in the same seciont,
## if not, jump out of the loop
try: ## check if next line exists in spreadsheet
next_section = sections[i+1]
except: ## not exist, then must be the last item already
print('last line, no more section !')
print('---------------------------')
QAItemsWindow.Close.click_input()
break
else:
## if next line is not the same section,
## jump out of the while loop, need to add to a new section
if sections[i] != sections[i+1]:
QAItemsWindow.Close.click_input()
break # note, once break i will not plus one
## next line still int the same section, Go to next line
i = i + 1
QATemplateWindow.wait('exists', timeout=10)
section_title.click_input(button='left', double=True) ## to collapse items save screen space
try: ## check if next line exists in spreadsheet
next_template = description[i+1]
except: ## not exist, then must be the last item already
print('no more template need to be created, exit !')
QATemplateWindow.Save.click_input()
break
else:
if description[i] != description[i+1]:
QATemplateWindow.Save.click_input()
break
def Add_QA_Items():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
# start
mainQAItemsTab = templa.child_window(title='QA Items', control_type='TabItem')
mainQAItemsTab.click_input()
templa.child_window(title="New", control_type="Button").click_input()
QAItemDetailWindow = app.window(title_re='QA Item *')
QAItemDetailWindow.wait('exists', timeout=15)
print("QA Item Window opened...")
########################
#
# Setup Excel Sheet
#
########################
sheetLoader = 'Add QA Items'
df = pd.read_excel('test.xlsx', sheet_name=sheetLoader)
# print("Reading Excel...")
for i in df.index:
details = df['DETAILS']
itemGroup = df['ITEM GROUP']
#scoreCard = df['SCORE CARD']
status = df['STATUS']
if status[i] == "Stop":
print("Stop here")
break
if status[i] == "Done":
print(details[i]+ " is Done")
continue
if status[i] == "Skip":
print(details[i]+ " is Skipped")
continue
# #########################
# # add new QA Item
# #########################
time.sleep(2)
pyautogui.press('tab')
# print('click on details')
#QAItemDetailWindow.child_window(title="Details", control_type="Text").click_input()
pyautogui.typewrite(details[i])
pyautogui.press('tab')
#QAItemDetailWindow.child_window(title="Item group", control_type="Text")
pyautogui.typewrite(itemGroup[i])
pyautogui.press('tab')
# QAItemDetailWindow.Save.click_input()
time.sleep(2)
print(details[i] +" Done.")
QAItemDetailWindow.child_window(title="Save and new", control_type="Button").click()
QAItemDetailWindow.Close.click_input()
print("All QA Item Created now")
print("##################")
def KPI_FaildedQA():
if tm_init() is None:
print("Can't find Templa on your computer")
else:
templa = tm_init()[0]
app = tm_init()[1]
completedQAWindow = templa.child_window(title='Completed QA Items',
control_type='TabItem')
if not completedQAWindow.exists():
print('Completed QA Items NOT found?')
print('Have your open the Complete QA Items Window Tab/Window?')
print('Exit.')
else:
## start
print("found the 'Completed QA Items', continue ...")
completedQAWindow.click_input()
##### defined a function for save report into specific forlder repeatively ######
########################
#
# Setup Excel Sheet
#
########################
Work_Sheet = 'KPI QA Completed Items'
df = pd.read_excel('test.xlsx', sheet_name=Work_Sheet)
if datetime.now().month - 1 == 0:
analysis_month = 12
analysis_year = datetime.now().year - 1
else:
analysis_month = datetime.now().month - 1
analysis_year = datetime.now().year
analysis_month_string = '01'
if analysis_month == 1:
analysis_month_text = 'Jan'
if analysis_month == 2:
analysis_month_string = '02'
analysis_month_text = 'Feb'
if analysis_month == 3:
analysis_month_string = '03'
analysis_month_text = 'Mar'
if analysis_month == 4:
analysis_month_string = '04'
analysis_month_text = 'Apr'
if analysis_month == 5:
analysis_month_string = '05'
analysis_month_text = 'May'
if analysis_month == 6:
analysis_month_string = '06'
analysis_month_text = 'Jun'
if analysis_month == 7:
analysis_month_string = '07'
analysis_month_text = 'Jul'
if analysis_month == 8:
analysis_month_string = '08'
analysis_month_text = 'Aug'
if analysis_month == 9:
analysis_month_string = '09'
analysis_month_text = 'Sep'
if analysis_month == 10:
analysis_month_string = str(analysis_month)
analysis_month_text = 'Oct'
if analysis_month == 11:
analysis_month_string = str(analysis_month)
analysis_month_text = 'Nov'
if analysis_month == 12:
analysis_month_string = str(analysis_month)
analysis_month_text = 'Dec'
monthName_string = analysis_month_text
yearName_string = str(analysis_year)
lastday_analysis_month_string = str(
calendar.monthrange(analysis_year, analysis_month)[1])
dateStartString = '01' + analysis_month_string + yearName_string
dateEndString = lastday_analysis_month_string + analysis_month_string + yearName_string
print('starting...')
print('analysis month: ' + monthName_string + ' analysis year: ' +
yearName_string)
print('analysis month text: ' + analysis_month_text)
for i in df.index:
constracts = df['CONTRACTS']
siteName = df['SITE NAME']
siteName_string = str(siteName[i])
site = df['SITE']
client = df['CLIENT']
template = df['TEMPLATE']
filePath = df['PATH']
fileName = df['FILE_NAME_FAILED_QA_ITEMS']
status = df['STATUS']
useContracts = df['USE CONTRACTS']
useSite = df['USE SITE']
useClient = df['USE CLIENT']
useTemplate = df['USE TEMPLATE']
if status[i] == 'Done':
print(siteName_string + ' is Done')
continue
if status[i] == 'Skip':
print(siteName_string + ' is Skipped')
continue
if status[i] == 'Stop':
print('Stop here')
break
# app.window(title='Change filter', control_type='Button').click_input()
templa.child_window(title='Change filter',
control_type='Button').click_input()
filterWindow = templa.child_window(
title_re='QA Completed Item Filter Detail - *')
filterWindow.wait('exists', timeout=15)
## default filter
print('Default the Filter.')
filterWindow.child_window(
title='Default criteria').click_input()
if useTemplate[i] == 'Yes':
print('Use Template')
## Use Template Filter
filterWindow.child_window(
auto_id='cslQATemplate',
control_type='Pane').click_input()
pyautogui.typewrite(str(int(template[i])))
pyautogui.press('tab')
# ###############################################
# ############ ###############
# ############ Basic Filtering ###############
## filter on date range of audited date
print('date from ', dateStartString)
filterWindow.child_window(auto_id='datAuditDateFrom',
control_type='Edit').click_input()
pyautogui.typewrite(dateStartString)
##filterWindow.child_window(auto_id='datAuditDateTo', control_type='Edit').click_input()
pyautogui.press('tab')
print('date end ', dateEndString)
pyautogui.typewrite(dateEndString)
####
## print out the current site
print('site analytics: ', siteName[i])
# ## if the site is Special case, use below
# if siteName_string == 'DAWR Monthly' or siteName_string == 'PMC Monthly':
# print('Ignore the failed Items')
# ##
# pyautogui.press('right')
# pyautogui.press('right')
# pyautogui.press('space')
# # ## ## click on Failed Items button to YES
# else:
pyautogui.press('tab')
pyautogui.press('tab')
pyautogui.press('tab')
pyautogui.press('right')
pyautogui.press('space')
# ########### End of Basic Filtering #########
# #################################################
## change the site filters criteria
siteFilterCriteria = filterWindow.child_window(
title='Site filtering criteria', control_type='TabItem')
siteFilterCriteria.click_input()
if useContracts[i] == 'Yes':
## Use Contracts filter
print('Use Contracts')
filterWindow.child_window(
title='Contracts',
auto_id='5',
control_type='DataItem').click_input()
pyautogui.typewrite(str(constracts[i]))
pyautogui.press('tab')
if useSite[i] == 'Yes':
## Use Site Filter
print('Use Site')
filterWindow.child_window(
auto_id='cslSite', control_type='Pane').click_input()
pyautogui.typewrite(str(site[i]))
pyautogui.press('tab')
if useClient[i] == 'Yes':
## Use Client Filter
print('Use Client')
filterWindow.child_window(
auto_id='cslClient',
control_type='Pane').click_input()
pyautogui.typewrite(str(client[i]))
pyautogui.press('tab')
## check the other tab filtering
if siteName_string == 'Redcape':
protertyFilterCriteria = filterWindow.child_window(
title='Property filtering criteria',
control_type='TabItem')
protertyFilterCriteria.click_input()
## get the handle of Group filter
groupItem = filterWindow.child_window(
title='Group', auto_id='2', control_type='DataItem')
matchTypeSection = groupItem.child_window(
title='Match type',
auto_id='3',
control_type='ComboBox')
valueSection = groupItem.child_window(
title='Value', auto_id='1', control_type='ComboBox')
## click and change the filter Equal to ...
matchTypeSection.click_input()
pyautogui.typewrite('e') # e, for Equal to
pyautogui.press('tab')
time.sleep(2)
valueSection.click_input()
pyautogui.typewrite('r') # filter the site name
pyautogui.press('tab')
# ## Save the filter
print('Saving the filter ...')
filterWindow.Save.click_input()
#siteDescriptionTab = completedQAWindow.child_window(title='Site description', control_type='DataItem')
mainCompletedWindow = templa.child_window(
title='Completed QA Items', control_type='Window')
csmWindow = mainCompletedWindow.child_window(
title='CSM', auto_id='56', control_type='ComboBox')
csmWindow.wait('exists', 180)
templa.child_window(title='Select format',
control_type='Button').click_input()
filterFormatsWindow = templa.window(
title='Filtered List Formats')
filterFormatsWindow.wait('exists', timeout=15)
## type the format name
filterFormatsWindow.window(
title='Description',
control_type='ComboBox').click_input()
## if the site is Special case, use below
if siteName_string == 'DAWR Monthly' or siteName_string == 'PMC Monthly' or siteName_string == 'TK MAXX Monthly':
pyautogui.typewrite('Special Format')
pyautogui.moveRel(-25, 25)
pyautogui.doubleClick() # apply the format
else:
pyautogui.typewrite('Standard Format')
pyautogui.moveRel(-25, 25)
pyautogui.doubleClick() # apply the format
## read below from excel sheet
folderName = monthName_string + '-' + yearName_string
##
print('Ready to Export to Excel File ...')
save_as_Excel_analysis(window=templa, pathName=filePath[i], folderName=folderName, \
fileName=fileName[i], flag='first time save to this folder')
print(siteName_string + ' is Done.')
print('#######################')
print(' ')
