Automated RQ Invoice Downloader
This Python program takes an Excel workbook containing invoice numbers and downloads PDFs of those invoices from RQ, a point-of-sale system.
Here’s how I did it.
from openpyxl import load_workbook
import pyautogui
import time
from datetime import datetime
from pywinauto.application import Application
I use openpyxl to interact with the Excel workbook that has the invoices to download.
I think I had to use specific versions, downgrading as needed to get things to work so I will list the libraries and the versions I used here:
Library | Version |
---|---|
PyAutoGUI | 0.9.48 |
openpyxl | 3.0.0 |
pywinauto | 0.6.8 |
# open excel spreadsheet
wb = load_workbook('invoicenumbers.xlsx')
ws = wb['Sheet1']
last_row = ws.max_row
I open the Excel spreadsheet named invoicenumbers.xlsx
and store it in a variable named wb
. I take the sheet named Sheet1
and store it in a variable named ws
. I find the value of the last row in the sheet and store it in a variable named last_row
.
commandline = '"C:\Program Files (x86)\iQmetrix\RQ4\RetailiQ.WPF\RetailiQ.Windows.WPF.exe" /component IQ.WPFBrowser 1.0.0.2 "C:\Program Files (x86)\iQmetrix\RQ4\IQ.WPFBrowser"'
app = Application(backend="uia").start(commandline)
I find the command line to launch RQ using Process Explorer and trim it down to what’s needed to launch the application. I store that in a variable named commandline
.
I launch the application using pywinauto’s start
function and hold a reference to the application in a variable named app
.
while not app.RQ.exists():
time.sleep(5)
I wait for the application to launch, checking every 5 seconds, before proceeding.
# txtEmployeeName
app.RQ.child_window(auto_id="txtEmployeeName").set_text("myusername")
# txtPassword
app.RQ.child_window(auto_id="txtPassword").set_text("mypassword")
# btnLogin
app.RQ.child_window(auto_id="btnLogin").click()
I use inspection tools to find the names of the GUI components.
I enter my username and password in the login screen and click on the “Sign In” button. I use child_window
, set_text
, and click
to do this.
while not app.RQ.child_window(title="RQ.Windows.UI.Consoles.Facades.ReportsConsoleFacade").exists():
print('Waiting for home page to appear.')
time.sleep(1)
I wait for the login process to finish, checking every second, before proceeding.
# loop through invoice numbers
for x in range(1, last_row + 1):
if datetime.now() > datetime.fromisoformat('2021-11-15T02:30:00'):
print('exiting...')
exit()
I start the for loop to go through all the invoice numbers in the Excel workbook. I check the current date and time at the beginning of each loop and stop if it’s 2:30 AM of the following day because it seemed like the RQ servers were rebooted at 3:00 AM and caused the program to error out.
sentinel = ws.cell(row=x, column=2).value
I check for a marker indicating that the row has already been processed. I store that in a variable named sentinel
.
if sentinel != 'x':
invoice_number = ws.cell(row=x, column=1).value
# click on search button 1492, 13
pyautogui.click(1492, 13)
time.sleep(3)
If sentinel
does not hold the value ‘x’, the invoice has not yet been downloaded and I start with the meat of the program.
I get the invoice number in the current row and store it in a variable named invoice_number
.
I tried addressing the search bar by name but couldn’t get it to work after several hours of trying. To get around this, I set the monitor resolution to 1920 x 1080 and use pyautogui to click on the screen using XY coordinates. I find the coordinates by taking a screenshot, pasting the image into Paint, and hovering over the target element. I wait 3 seconds after clicking to account for rendering time.
pyautogui.typewrite(invoice_number)
pyautogui.press('enter')
I use pyautogui’s typewrite
and press
functions to enter the invoice number in the search bar and initiate the search.
while not app.RQ.child_window(title="Scan Anywhere").exists():
print("waiting for scan anywhere")
time.sleep(1)
I wait for the invoice dialog to appear, checking every second, before proceeding.
# click on view invoice
pyautogui.click(909, 480)
while not app.window(title='Sale Invoice').exists():
print('waiting for sale invoice')
time.sleep(1)
# click on save button
pyautogui.click(18, 67)
while not app.window(title='Sale Invoice').child_window(title="Save As").exists():
print('waiting for save as')
time.sleep(1)
pyautogui.typewrite(invoice_number)
pyautogui.press('enter')
while not app.window(title='Sale Invoice').child_window(title="Save as PDF").exists():
print('waiting for save as pdf')
time.sleep(1)
# click on ok
pyautogui.click(1116, 694)
time.sleep(5)
pyautogui.hotkey('alt', 'f4')
time.sleep(3)
I use XY coordinates to click on buttons that are not easily accessed by name. I use the invoice number for the file name. I use pywinauto’s exists
function to check that windows have finished rendering before proceeding.
app.window(title='Sale Invoice').WindowPresenterCloseButton.click()
ws.cell(row=x, column=2).value = 'x'
wb.save('invoicenumbers.xlsx')
print('finished row {}'.format(x))
time.sleep(5)
I close all the windows that were opened in the process of retrieving and saving the invoice. I mark the row as finished by writing an ‘x’ in the second column. I save the workbook.
The loop restarts and looks for the next row that hasn’t been processed.
app.kill()
exit()
When all the rows have been processed, I close RQ and exit the program.