# -*- coding: utf-8 -*-
"""
Created on Mon Jun 19 17:39:48 2017

@author: Dylan Keyer
"""

import pyodbc
import logging
import os
import datetime
import itertools


# uncomment below and specify working directory, if desired
# os.chdir(<ENTER WORKING DIRECTORY FOR SCRIPT HERE>)

# set-up the log file
logging.basicConfig(filename='quickbooks.log', level=logging.DEBUG)


# initiating the an object of the class QuickBooks does not read the data. You need to call the
# class method, self.ReadData() to do that.

class QuickBooks():
    # initialize our lists -- Pandas could be integrated easily in the future be passing lists to
    # the Pandas method, DataFrame, as a dictionary with column names as the keys and the lists as the values
    def __init__(self):
        self.TransactionID = []
        self.PONumber = []
        self.TransactionDate = []
        self.CustomerName = []
        self.Email = []
        self.TimeCreated = []
        self.InvoiceLineType = []
        self.InvoiceLineDesc = []
        self.Quantity = []
        self.UnitOfMeasure = []
        self.Rate = []
        self.Amount = []
        self.ShipDate = []
        self.BillingAddress1 = []
        self.BillingAddress2 = []
        self.BillingAddress3 = []
        self.BillingAddress4 = []
        self.BillingAddress5 = []
        self.BillingAddressCity = []
        self.BillingAddressState = []
        self.BillingAddressPostalCode = []
        self.BillingAddressCountry = []
        self.ShipAddress1 = []
        self.ShipAddress2 = []
        self.ShipAddress3 = []
        self.ShipAddress4 = []
        self.ShipAddress5 = []
        self.ShipAddressCity = []
        self.ShipAddressState = []
        self.ShipAddressPostalCode = []
        self.ShipAddressCountry = []
        self.ShippingMethod = []
        self.SalesRepName = []
        self.Memo = []

        # this is a paramterized string, the data will be passed in from self.LookBackDate, defined below
        self.SQL = """SELECT InvoiceLine.TxnID, InvoiceLine.PONumber, InvoiceLine.TxnDate,
                           InvoiceLine.CustomerRefFullName, Customer.Email,
                           InvoiceLine.TimeCreated, InvoiceLine.InvoiceLineType, InvoiceLine.InvoiceLineDesc,
                           InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineUnitOfMeasure,
                           InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount, InvoiceLine.ShipDate,
                           InvoiceLine.BillAddressAddr1, InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3,
                           InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressAddr5,
                           InvoiceLine.BillAddressCity, InvoiceLine.BillAddressState,
                           InvoiceLine.BillAddressPostalCode, InvoiceLine.BillAddressCountry,
                           InvoiceLine.ShipAddressAddr1,
                           InvoiceLine.ShipAddressAddr2, InvoiceLine.ShipAddressAddr3, InvoiceLine.ShipAddressAddr4,
                           InvoiceLine.ShipAddressAddr5, InvoiceLine.ShipAddressCity, InvoiceLine.ShipAddressState,
                           InvoiceLine.ShipAddressCountry, InvoiceLine.ShipAddressPostalCode, InvoiceLine.ShipMethodRefFullName,
                           InvoiceLine.SalesRepRefFullName, InvoiceLine.Memo
                           FROM InvoiceLine
                           LEFT outer JOIN Customer
                           ON InvoiceLine.CustomerRefListID = Customer.ListID
                           WHERE (TxnDate >= {d '%s'})"""

        # LookBackDate default to five days ago -- change the value of X in timedelta.(days=X) to the number of
        # days that you want to look back in the database
        self.LookBackDate = (datetime.datetime.today() - datetime.timedelta(days=5)).strftime("%Y-%m-%d")

    # this class method initiates the log file and the DB connection. It also kicks off the reading of the data
    # using self.SQL as the query
    def ReadData(self):
        # write to log that DB connection is about to occur
        logging.info('Checking for new invoices at {}\n'.format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        logging.info('Connecting to the Quickbooks Enterprise Database...\n')
        # connect string is not necessary since we have a DSN setup on the local machine that is
        # going to execute the script (either manually or as a Scheduled Task)

        try:
            conn = pyodbc.connect('DSN=QuickBooks Data', autocommit=True)
            cursor = conn.cursor()

            # query grabs most data from table InvoiceLine, which is left joined with
            # table Customer, to get email info
            logging.info('Querying the database for invoices from the past FIVE days...')
            cursor.execute(str(self.SQL % self.LookBackDate))

        except Exception as e:
            logging.error('The query failed with the following error : {}'.format(e))

        # initiate row counter, simpler than enumerating over the fetchall method
        # this will count number of records returned
        ct = 0

        # print results from the cursor


        all_data = (cursor.fetchall())
        key_function = lambda row: row[0]
        grouped_data = itertools.groupby(all_data, key_function)
        for group in grouped_data:
            order_title = ''
            order_body = ''
            for line in group[1]:
                order_body += (str(int(line[8])) + ' X ' + str((line[7])) + '\n')
                order_title = (line[3] + ' order ' + line[1])
                order_method = ('For ' + line[-3] + '\n')
                ship_date = line[12]

        cursor.close()
        conn.close()

        logging.info('Retrieved {} records...\n\n'.format(str(ct)))


qb = QuickBooks()
qb.ReadData()