Skip to main content

quickbooks_gui_automation.py

import csv
import datetime
import openpyxl
import pyautogui

 class CustomerListParser(object):

     def __init__(self):
        self.customer_dict = {}
        self._FIRST_CUSTOMER_ROW = 4
        self._CUSTOMER_NAME_COLUMN = 1
        self._EMAIL_COLUMN = 2
        self._PAYMENT_EMAIL_COLUMN = 3
        self.customer_sheet = None

     def parse_customer_list(self, file_name):
        customer_wb = openpyxl.load_workbook(file_name)
        self.customer_sheet = customer_wb.active
        for row in range(self._FIRST_CUSTOMER_ROW, self.customer_sheet.max_row + 1):
            self.parse_customer_entry(row)

         return self.customer_dict

     def parse_customer_entry(self, row):
        customer_pay_email = self.customer_sheet.cell(row=row, column=self._PAYMENT_EMAIL_COLUMN).value
        customer_name = self.customer_sheet.cell(row=row, column=self._CUSTOMER_NAME_COLUMN).value
        if customer_pay_email:
            self.customer_dict[customer_pay_email.lower()] = customer_name

         customer_email = self.customer_sheet.cell(row=row, column=self._EMAIL_COLUMN).value
        if customer_email:
            self.customer_dict.setdefault(customer_email, customer_name)
class PaymentListParser(object):

     def __init__(self, customer_dict):
        self.customer_dict = customer_dict
        self._TRANS_ID_COLUMN = 1
        self._AMOUNT_COLUMN = 2
        self._FEE_COLUMN = 4
        self._NET_AMOUNT_COLUMN = 5
        self._EMAIL_COLUMN = 8
        self._NAME_COLUMN = 9
        self._DATE_COLUMN = 10
        self._PAYMENT_METHOD_COLUMN = 15
        self._SHORT_ID_LENGTH = 11
        self._DATE_LENGTH = 10
        self.unidentified_customers = set()
        self.payments = []

     def parse_payment_list(self, file_name):
        payment_list_file = open(file_name, 'r')
        payment_list_reader = csv.reader(payment_list_file)
        for row in payment_list_reader:
            if payment_list_reader.line_num <= 1:
                continue
            pay_email = row[self._EMAIL_COLUMN].lower()
            if pay_email in self.customer_dict:
                customer_name = self.customer_dict[pay_email]
                payment_info = self.parse_payment(row)
                payment_info.customer_name = customer_name
                self.payments.append(payment_info)
            else:
                self.unidentified_customers.add(pay_email + ', ' + row[self._NAME_COLUMN])

         payment_list_file.close()

     def parse_payment(self, payment_row):
        date_in_str = payment_row[self._DATE_COLUMN][:10]
        date = datetime.datetime.strptime(date_in_str, '%Y-%m-%d')
        date_out_str = date.strftime('%m/%d/%y')
        amount = payment_row[self._AMOUNT_COLUMN]
        payment_method = payment_row[self._PAYMENT_METHOD_COLUMN]
        trans_id = payment_row[self._TRANS_ID_COLUMN]
        short_id_start = len(trans_id) - self._SHORT_ID_LENGTH
        short_id = trans_id[short_id_start:]
        fee = payment_row[self._FEE_COLUMN]
        net_amount = payment_row[self._NET_AMOUNT_COLUMN]
        return PaymentInfo(date_out_str, amount, payment_method, short_id, fee, net_amount)

     def get_payments(self):
        return self.payments

     def get_unidentified_customers(self):
        return self.unidentified_customers
class PaymentInfo(object):

     def __init__(self, date, amount, payment_method, trans_id, fee, net_amount):
        self._date = date
        self._amount = amount
        self._payment_method = payment_method
        self._trans_id = trans_id
        self._fee = fee
        self._net_amount = net_amount

     @property
    def customer_name(self):
        return self._customer_name

     @customer_name.setter
    def customer_name(self, value):
        self._customer_name = value

     @property
    def date(self):
        return self._date

     @date.setter
    def date(self, value):
        self._date = value

     @property
    def amount(self):
        return self._amount

     @amount.setter
    def amount(self, value):
        self._amount = value

     @property
    def payment_method(self):
        return self._payment_method

     @payment_method.setter
    def payment_method(self, value):
        self._payment_method = value

     @property
    def trans_id(self):
        return self._trans_id

     @trans_id.setter
    def trans_id(self, value):
        self._trans_id = value

     @property
    def fee(self):
        return self._fee

     @fee.setter
    def fee(self, value):
        self._fee = value

     @property
    def net_amount(self):
        return self._net_amount

     @net_amount.setter
    def net_amount(self, value):
        self._net_amount = value
class PaymentGUIEnterer(object):

     def __init__(self):
        self._QUICK_BOOKS_SIDE_MENU_LOC = (26, 30)
        self._CUSTOMERS_MENU_LOC = (402, 12)
        self._RECEIVE_PAYMENTS_MENU_CMD_LOC = (433, 267)
        self._AUTO_APPLY_BUTTON_LOC = (856, 873)
        self._DISCOUNT_INFO_BUTTON_LOC = (856, 846)
        self._PAYMENT_AMOUNT_LOC = (390, 237)
        self._SAVE_BUTTON_LOC = (1060, 975)
        pyautogui.PAUSE=0.1

     def enter_payment(self, payment_info):
        self.activate_quickbooks()
        self.open_receive_payments_window()
        self.enter_payment_info(payment_info)
        self.apply_payment_to_invoices()
        self.enter_credit_card_fee(payment_info.fee)
        self.subtract_fee_from_payment(payment_info.net_amount)
        self.record_transaction()
        self.close_receive_payments_window()

     def activate_quickbooks(self):
        pyautogui.click(*self._QUICK_BOOKS_SIDE_MENU_LOC)

     def open_receive_payments_window(self):
        pyautogui.click(*self._CUSTOMERS_MENU_LOC)
        pyautogui.click(*self._RECEIVE_PAYMENTS_MENU_CMD_LOC)

     def enter_payment_info(self, payment_info):
        pyautogui.typewrite(payment_info.customer_name)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.date)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.amount)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.payment_method)
        pyautogui.typewrite(['tab'])
        pyautogui.typewrite(payment_info.trans_id)
        pyautogui.typewrite(['tab'])

     def apply_payment_to_invoices(self):
        pyautogui.click(*self._AUTO_APPLY_BUTTON_LOC)

     def enter_credit_card_fee(self, fee):
        pyautogui.click(*self._DISCOUNT_INFO_BUTTON_LOC)
        pyautogui.typewrite(fee)
        pyautogui.typewrite(['enter'])

     def subtract_fee_from_payment(self, net_amount):
        pyautogui.click(*self._PAYMENT_AMOUNT_LOC)
        pyautogui.keyDown('ctrl')
        pyautogui.press('k')
        pyautogui.keyUp('ctrl')
        pyautogui.typewrite(net_amount)

     def record_transaction(self):
        pyautogui.click(*self._SAVE_BUTTON_LOC)

     def close_receive_payments_window(self):
        pyautogui.keyDown('command')
        pyautogui.press('w')
        pyautogui.keyUp('command')
if __name__ == "__main__":
    customer_parser = CustomerListParser()
    customer_dict = customer_parser.parse_customer_list('customer_list.xlsx')

     payment_parser = PaymentListParser(customer_dict)
    payment_parser.parse_payment_list('payments-2017-04-28-065748.csv')

     payment_enterer = PaymentGUIEnterer()
    for payment_info in payment_parser.get_payments():
        payment_enterer.enter_payment(payment_info)

     print '\nUnidentified Customers:\n'
    for customer in payment_parser.get_unidentified_customers():
        print customer

Comments

Post a Comment

Popular posts from this blog

Books That Have Influenced Me and Why

A mantra that I often repeat to myself is, "Don't abandon the behaviors and habits that made you successful." I believe this trap is actually much easier to fall into than most people realize. You can sometimes observe it in the context of a professional sporting event such as American football. One team might dominate the game, playing exceptionally well for the first three quarters. Then, as they sit with a comfortable lead, you see a shift in their strategy. They start to play more conservatively, running the ball more often than they had. Their defense shifts to a "prevent" formation, designed to emphasize stopping any big plays by the other team while putting less pressure on the short game. The leading team often looks awkward in this mode. They have switched their perspective from that of pursuing victory to that of avoiding defeat. They have stopped executing in the way that gained them the lead in the first place. I have seen more than one game ult...
Post a Comment
Read more

Notions of Debugging

Bugs can be really evasive and produce some surprising, even "impossible" behaviors. Part of a software developer's job is to relentlessly hunt them down and destroy them. However, often at least half the battle is finding them to begin with. The best debuggers I have seen rely a lot on experience, both experience with the application and experience with debugging in general. The former is situation-specific, and there many not be any good ways to advance in that regard other than practice. However, we can extract some information pertaining to good debugging practices in general. Dig for Clues Using All Available Resources The first step in debugging an issue is ideally to reproduce the problem. This isn't always possible, but if you want any significant level of confidence that you have fixed a bug, you need to observe the broken behavior and understand how to trigger it from a user's perspective. Sometimes we're lucky, and a tester has taken the time to...
Post a Comment
Read more

When All Else Fails, Use the GUI

In this blog post, I show you how I automated entering credit card transactions in QuickBooks using the PyAutoGUI package for Python. Automating routine tasks is a great way to save yourself a lot of time. Whenever I spend a significant amount of time doing a partiuclar task on a computer, I ask myself if there is a way to automate it. Generally, as long as the input to the task is located on the computer, the answer is yes. The kinds of tasks that evade automation are those that have some kind of physical medium as an imput. While there are technologies that could help automate those tasks, it is much more complicated and difficult to do so. For tasks that can be automated, my favorite language for doing so is Python. Python has a wonderfully readable, expressive syntax, and there are an astoundingly large number of libraries and packages available that make the job of automating jobs much easier. One of my favorite simple examples of how Python saved me a lot of time is when I...
Post a Comment
Read more