Magento Missing Order Finder


/ Published in: Python
Save to your folder(s)



Copy this code and paste it in your HTML
  1. import os
  2. import sys
  3. import csv
  4. import MySQLdb
  5.  
  6. class batchOrderFind(object):
  7.  
  8. def __init__(self, fileToRun, host, user, passwd, db):
  9. self.csvFolder= "\\csv\\"
  10. self.exportFolder = "\\export\\"
  11. self.progPath = os.path.realpath(os.path.dirname(sys.argv[0]))
  12. self.fileToRun = fileToRun
  13. 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}
  14. self.host = host
  15. self.user = user
  16. self.passwd = passwd
  17. self.db = db
  18. self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db)
  19. self.cur = self.conn.cursor()
  20. self.rowDump = []
  21. pass
  22.  
  23. def processCsv(self):
  24. fileName = self.progPath + self.csvFolder + self.fileToRun
  25. thisCsv = csv
  26. #Add Header Manually
  27. self.rowDump.append(["email", "firstname", "lastname", "company", "street", "state", "city",
  28. "zipcode", "phone", "sku", "productname", "qty", "price", "date", "invoice"])
  29. with open(fileName, "rb") as f:
  30. rownum = 0
  31. for row in thisCsv.reader(f, delimiter = ",", quotechar = '"'):
  32. if rownum == 0:
  33. header = row
  34. else:
  35. colnum = 0
  36. for col in row:
  37. #print '%-8s: %s' % (header[colnum], col)
  38. if (header[colnum] == "Invoice Number"):
  39. invoice = col
  40. if (header[colnum] == "Submit Date"):
  41. orderdate = self.extractDate(col)
  42. if (header[colnum] == "Customer"):
  43. processedName = self.splitName(col)
  44. firstname = processedName[1]
  45. lastname = processedName[0]
  46. colnum += 1
  47. self.findOrder(firstname, lastname, orderdate, invoice)
  48. rownum += 1
  49. pass
  50.  
  51. def findOrder(self, firstName, lastName, orderDate, invoiceNo):
  52. lastName = lastName.strip(',')
  53. #TODO: Check where missing values go - DISTINCT removed currently
  54. query = "SELECT DISTINCT \
  55. sales_flat_quote_address.email,\
  56. sales_flat_quote_address.firstname,\
  57. sales_flat_quote_address.lastname,\
  58. sales_flat_quote_address.company,\
  59. sales_flat_quote_address.street,\
  60. sales_flat_quote_address.region,\
  61. sales_flat_quote_address.city,\
  62. sales_flat_quote_address.postcode,\
  63. sales_flat_quote_address.telephone,\
  64. sales_flat_quote_item.sku,\
  65. sales_flat_quote_item.name,\
  66. sales_flat_quote_item.qty,\
  67. sales_flat_quote_item.price,\
  68. sales_flat_quote_address.updated_at\
  69. FROM\
  70. sales_flat_quote_address\
  71. Left Join sales_flat_quote_item ON sales_flat_quote_address.quote_id = sales_flat_quote_item.quote_id\
  72. WHERE sales_flat_quote_address.firstname = '" + firstName + "'\
  73. AND sales_flat_quote_address.lastname = '" + lastName + "'\
  74. AND sales_flat_quote_address.address_type = 'shipping'"
  75. #AND DATE(sales_flat_quote_address.updated_at) = '" + orderDate + "'"
  76. self.cur.execute(query)
  77. for row in self.cur:
  78. tupList = list(row)
  79. tupList.append(invoiceNo)
  80. self.rowDump.append(tupList)
  81. print row
  82. pass
  83.  
  84. def dumpCSV(self):
  85. fileName = self.progPath + self.exportFolder + "export.csv"
  86. f = open(fileName, 'wb')
  87. writer = csv.writer(f, delimiter = ",", quotechar = '"')
  88. writer.writerows(self.rowDump)
  89. self.conn.close()
  90. pass
  91.  
  92. def splitName(self, fullName):
  93. return fullName.split(' ')
  94. pass
  95.  
  96. def extractDate(self, csvDT):
  97. thedate = csvDT.split(' ')[0]
  98. day, monabb, year = thedate.split('-')
  99. return year + "-" + str(self.monthDict[monabb]) + "-" + day
  100. pass
  101.  
  102.  
  103.  
  104. if __name__ == "__main__":
  105. thisBatch = batchOrderFind("authorizecsv", "dbhost", "dbuser", "dbpass", "dbtable")
  106. thisBatch.processCsv()
  107. thisBatch.dumpCSV()
  108. pass
  109.  

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.