import os
import sys
import csv
import MySQLdb

class batchOrderFind(object):
    
    def __init__(self, fileToRun, host, user, passwd, db):
        self.csvFolder= "\\csv\\"
        self.exportFolder = "\\export\\"
        self.progPath = os.path.realpath(os.path.dirname(sys.argv[0]))
        self.fileToRun = fileToRun
        self.monthDict = {"Jan":1, "Feb" :2, "Mar":3, "Apr":4, "May":5, "Jun":6, "Jul":7, "Aug":8, "Sep":9,  "Oct":10,  "Nov":11,  "Dec":12}
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db)
        self.cur = self.conn.cursor()
        self.rowDump = []
        pass
        
    def processCsv(self):
        fileName = self.progPath + self.csvFolder  + self.fileToRun
        thisCsv = csv
        #Add Header Manually
        self.rowDump.append(["email", "firstname", "lastname", "company", "street", "state", "city", 
            "zipcode", "phone", "sku", "productname", "qty", "price", "date", "invoice"])
        with open(fileName,  "rb") as f:
            rownum = 0
            for row in thisCsv.reader(f,  delimiter = ",",  quotechar = '"'):
                if rownum == 0:
                    header = row
                else:
                    colnum = 0
                    for col in row:
                        #print '%-8s: %s' % (header[colnum], col)
                        if (header[colnum] == "Invoice Number"):
                            invoice = col
                        if (header[colnum] == "Submit Date"):
                            orderdate = self.extractDate(col)
                        if (header[colnum] == "Customer"):
                            processedName = self.splitName(col)
                            firstname = processedName[1]
                            lastname = processedName[0]
                        colnum += 1
                    self.findOrder(firstname, lastname, orderdate, invoice)
                rownum += 1
        pass
        
    def findOrder(self, firstName, lastName, orderDate, invoiceNo):
        lastName = lastName.strip(',')
        #TODO: Check where missing values go - DISTINCT removed currently
        query = "SELECT DISTINCT \
            sales_flat_quote_address.email,\
            sales_flat_quote_address.firstname,\
            sales_flat_quote_address.lastname,\
            sales_flat_quote_address.company,\
            sales_flat_quote_address.street,\
            sales_flat_quote_address.region,\
            sales_flat_quote_address.city,\
            sales_flat_quote_address.postcode,\
            sales_flat_quote_address.telephone,\
            sales_flat_quote_item.sku,\
            sales_flat_quote_item.name,\
            sales_flat_quote_item.qty,\
            sales_flat_quote_item.price,\
            sales_flat_quote_address.updated_at\
            FROM\
            sales_flat_quote_address\
            Left Join sales_flat_quote_item ON sales_flat_quote_address.quote_id = sales_flat_quote_item.quote_id\
            WHERE sales_flat_quote_address.firstname = '" + firstName + "'\
            AND sales_flat_quote_address.lastname = '" + lastName + "'\
            AND sales_flat_quote_address.address_type = 'shipping'"
                    #AND DATE(sales_flat_quote_address.updated_at) = '" + orderDate + "'"
        self.cur.execute(query)
        for row in self.cur:
            tupList = list(row)
            tupList.append(invoiceNo)
            self.rowDump.append(tupList)
            print row
        pass
        
    def dumpCSV(self):
        fileName = self.progPath + self.exportFolder  + "export.csv"
        f = open(fileName, 'wb')
        writer = csv.writer(f, delimiter = ",",  quotechar = '"')
        writer.writerows(self.rowDump)
        self.conn.close()
        pass
        
    def splitName(self, fullName):
        return fullName.split(' ')
        pass
        
    def extractDate(self, csvDT):
        thedate = csvDT.split(' ')[0]
        day,  monabb,  year = thedate.split('-')
        return year + "-" + str(self.monthDict[monabb]) + "-" + day
        pass
        
        
        
if __name__ == "__main__":
    thisBatch = batchOrderFind("authorizecsv", "dbhost", "dbuser", "dbpass", "dbtable")
    thisBatch.processCsv()
    thisBatch.dumpCSV()
    pass

Magento Missing Order Finder

python, csv, magento

Python