Total sales SQL, different figures

[khinester]

Hello, I have this python code, which I use to link with my Google Adwords and which gives me a graph of sales over time and advertising costs over time.

 

#!/usr/bin/python2.6
# -*- coding: utf-8 -*-
# Norman Khine

# import from Standard Library
import os, sys, getpass
import calendar
import datetime
from decimal import *
import itertools

# import from MySQLdb
import MySQLdb

# import form itools http://www.hforge.org/itools
import itools.csv
from itools.handlers import RWDatabase

# import from matplotlib
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from matplotlib.transforms import offset_copy


# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'root'
from_db = 'boutique'

if os.environ.has_key('MYSQL_PASSWD'):
dbpasswd = os.environ['MYSQL_PASSWD']
else:
dbpasswd = getpass.getpass("Enter MySQL password: ")
try:
org = MySQLdb.connect (host = dbhost, user = dbuser, passwd = dbpasswd)	

except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)

main = org.cursor()

# select the database and set the sql query
main.execute('USE %s' % from_db)
daily_total_orders = "SELECT COUNT(DISTINCT o.id_order) as orders, SUM(o.total_paid / c.conversion_rate) as ttc, \
               ((SUM(o.total_products) - SUM(o.total_discounts)) / c.conversion_rate) as ht, o.invoice_date\
                   FROM ps_orders o INNER JOIN ps_currency c ON o.id_currency = c.id_currency \
                   WHERE o.valid = 1 AND o.invoice_date BETWEEN (%s) AND (%s)"

# my date range
orders = []
x = []
y = []
z = []
commission = []
daily_total_clicks = []
charges = []


def mkDateTime(date_string, str_format="%m/%d/%y"):
   """ Return datetime object from string in given format  """
   return datetime.datetime.strptime(date_string, str_format)

# pull the Google AdWords data and format
rw_database = RWDatabase()

adwords = rw_database.get_handler('../account_activities_200908_201108.csv')

for row in adwords.get_rows():
   if row[2] != '':
       # add the date range and convert it to datetime format
       x.append(mkDateTime(row[0]))
       # clean up the CSV by removing 'Acount Activity'
       try:
           description, clicks = row[1].split(':')
           daily_clicks = clicks.split(' ')
           daily_total_clicks.append(daily_clicks[1])
           charges.append(Decimal(row[2]))
       except:
           pass
total_days = x[-1] - x[0]

for date in x:
   date = str(date.isoformat(' ').split(' ')[0])
   main.execute(daily_total_orders, (date + ' 00:00:00', date + ' 23:59:59'))
   results = main.fetchall()
   try:	
       for row in results:
           if row[2] != None:
               print row[0],row[1], row[2], str(row[3].isoformat(' ').split(' ')[0])
               orders.append(row[0])
               y.append(row[1])
               z.append(row[2])
               commission.append(int(row[2])*0.2)
           else:
               orders.append(int(0))
               commission.append(int(0))
               y.append(int(0))
               z.append(int(0))
   except MySQLdb.Error, e:
       print "Error %s" % e
       sys.exit (1)
# close the database connection
main.close()

# get the totals
total_orders = sum(orders)
total_income = round(sum(z), 2)
total_adwords = sum(charges)
total_commission = sum(commission)

# plot the data
plt.rc('axes', grid=True)
plt.rc('font', family='Arial')
fig = plt.figure()
fig.suptitle('Revenue over Time', fontsize=12, fontweight='bold')
ax = fig.add_subplot(111)
ax.set_title('Analysis for the last %s days from a total of %s orders' % (total_days, total_orders))
ax.set_xlabel('Date range from %s to %s' % (x[0], x[-1]))
ax.set_ylabel('daily total', fontdict={'family': 'Cambria'})
plt.plot(x,z,'-o',x,charges,'-o', ms=5, lw=2, alpha=0.7)
plt.plot(x,commission, '-o', ms=5, lw=2, alpha=0.7)
plt.legend(('Income - %s GBP' % total_income,
           'Google AdWords Costs - %s GBP' % total_adwords,
           'Commission - %s GBP' % total_commission),
               'upper right', shadow=True)

plt.savefig('out.png')
plt.show()

 

The problem is that the SQL on my code returns different figure than what it is in the Prestashop back-office.

 

I have used the same date range on both, but the figures are not correct. What am I missing?

 

Thanks

 

Norman