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

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

# import from MySQLdb
import MySQLdb

# import form itools
import itools.csv
from itools.handlers import RWDatabase

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


# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'user'
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 \
                    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
total_days = 30
today = datetime.datetime.today()
date_generator = (today - datetime.timedelta(days=i) for i in itertools.count())
dates = itertools.islice(date_generator, total_days)
x = (list(dates))
y = []
z = []
commission = []
daily_total_clicks = []
charges = []
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:
                y.append(row[1])
                z.append(row[2])
                print type(row[2]), row[2]
                commission.append(int(row[2])*0.2)
            else:
                commission.append(int(0))
                y.append(int(0))
                z.append(int(0))
    except MySQLdb.Error, e:
        print "Error %s" % e
        sys.exit (1)
print commission
# close the database connection
main.close()

# pull the Google AdWords data and format
rw_database = RWDatabase()
adwords = rw_database.get_handler('/Users/khinester/Desktop/account_activities_201105_201106.csv')
for row in adwords.get_rows():
    if row[2] != '':
        # clean up the CSV by removing 'Acount Activity'
        description, clicks = row[1].split(':')
        daily_clicks = clicks.split(' ')
        daily_total_clicks.append(daily_clicks[1])
        charges.append(Decimal(row[2]))
# get the totals
total_income = sum(y)
total_adwords = sum(charges)
total_commission = sum(commission)
# plot the data
fig = plt.figure()
fig.suptitle('Revenue over Time', fontsize=12, fontweight='bold')
ax = fig.add_subplot(111)
ax.set_title('for the last %s days' % total_days)
ax.set_xlabel('dates')
ax.set_ylabel('daily total')
plt.plot(x,y,'-o',x,z,'-o',x,charges,'-o', ms=5, lw=2, alpha=0.7)
plt.plot(x,commission, 'k:', color = 'r')
plt.legend(('Income - %s GBP' % total_income, 'Discounts', 'Google AdWords - %s GBP'% total_adwords, 'Commission - %s GBP' % total_commission),
           'upper right', shadow=True)
plt.show()