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(' ')