#!/usr/bin/env python
#-*- coding: utf-8 -*-

import csv
import database as db
from datetime import datetime
import calendar

now = datetime.utcnow()
print now

c = db.Connection("10.0.0.240", "bebeboutik", "bebeboutik", "InSXVqcoTzj7")

versions = ['fr','es'];

for version in versions:
	for year in range(2012, now.year + 1):
		fp = open("extracts/weekly/%d-%02d-%02d_%d_%s.csv" % (now.year, now.month, now.day, year, version), "wb")
		f = csv.DictWriter(
			fp,
			[
				"id_customer", "email", "sponsor_id", "sponsor_email", "date_add", "date_upd", "total_orders_amount", 
				"total_orders", "newsletter", "optin", "first_order", "second_order", "last_order", "nb_orders_3", 
				"nb_orders_6", "nb_orders_9", "nb_orders_12", "balise_ed", 
			],
			delimiter=";", quotechar='"', quoting=csv.QUOTE_MINIMAL
		)

		f.writerow(dict((v, v) for v in f.fieldnames))

		for month in range(1, 13):
			print "%d - %02d -- %s" % (year, month, str(datetime.utcnow()))

			# Si FR on prends tout sauf les ES
			if version == "fr":
				customers = c.query("""
					SELECT c.`id_customer`, c.`email`, c.`date_add`, c.`date_upd`, c.`optin`, c.`newsletter`
					FROM `ps_customer` c
					WHERE c.`date_add` < "%d-%02d-%02d 23:59:59"
					AND c.`date_add` >= "%d-%02d-01 00:00:00"
					AND c.`date_add` < NOW()
					AND c.`id_customer` NOT IN (
						SELECT cv.`id_customer`
						FROM `ps_customer_version` cv
						WHERE cv.`version` = "es"
					)
					ORDER BY c.`date_add`
				""" % (year, month, calendar.monthrange(year, month)[1], year, month))
			# sinon on LEFT JOIN sur la version en cours
			else :
				customers = c.query("""
					SELECT c.`id_customer`, c.`email`, c.`date_add`, c.`date_upd`,  c.`optin`, c.`newsletter`
					FROM `ps_customer` c
					LEFT JOIN `ps_customer_version` cv ON (cv.`id_customer` = c.`id_customer`)
					WHERE c.`date_add` < "%d-%02d-%02d 23:59:59"
					AND c.`date_add` >= "%d-%02d-01 00:00:00"
					AND c.`date_add` < NOW()
					AND cv.`version` = "%s"
					ORDER BY c.`date_add`
				""" % (year, month, calendar.monthrange(year, month)[1], year, month, version))


			if len(customers) > 0:
				customer_ids = [str(x["id_customer"]) for x in customers]

				sponsor_emails = c.query("""
					SELECT i.`id_customer`, i.`id_sponsor`, c.`email`
			                FROM `ps_customer` c
					LEFT JOIN `ps_invite` i
						ON i.`id_sponsor` = c.`id_customer`
			                WHERE i.`id_customer` IN (%s)
				""" % ", ".join(customer_ids))

				sponsors = {}
				for item in sponsor_emails:
					sponsors[item["id_customer"]] = (item["id_sponsor"], item["email"])

				tag_ed = c.query("""
					SELECT t.`id_customer`, t.`value`
	                FROM `ps_ant_trackingtag_customer` t
	                WHERE t.`id_customer` IN (%s)
	                AND t.`id_trackingtag` = 1
				""" % ", ".join(customer_ids))

				tags = {}
				for item in tag_ed:
					tags[item["id_customer"]] = str(item["value"])

				total_orders = c.query("""
					SELECT o.`id_customer`, SUM(o.`total_paid` / o.`conversion_rate`) AS `total_orders`, COUNT(o.`id_order`) AS `nb_orders`
					FROM `ps_orders` o
					WHERE o.`id_customer` IN (%s)
					AND o.valid = 1
					GROUP BY `id_customer`
				""" % ", ".join(customer_ids))


				order_history_date = {}
				for item in c.query("""
					SELECT
						t1.`id_customer`,
						SUM(IF(t1.`date_add` < NOW() AND t1.`date_add` >= DATE_SUB(NOW(), INTERVAL 3 MONTH), 1, 0)) AS `cp_3`,
						SUM(IF(t1.`date_add` < NOW() AND t1.`date_add` >= DATE_SUB(NOW(), INTERVAL 6 MONTH), 1, 0)) AS `cp_6`,
						SUM(IF(t1.`date_add` < NOW() AND t1.`date_add` >= DATE_SUB(NOW(), INTERVAL 9 MONTH), 1, 0)) AS `cp_9`,
						SUM(IF(t1.`date_add` < NOW() AND t1.`date_add` >= DATE_SUB(NOW(), INTERVAL 12 MONTH), 1, 0)) AS `cp_12`
					FROM (
						SELECT o.`id_customer`, MIN(h.`date_add`) AS `date_add`
						FROM `ps_order_history` h
						LEFT JOIN `ps_orders` o
						ON o.`id_order` = h.`id_order`
						WHERE h.`id_order_state` = 2
						AND o.`id_customer` IN (%s)
						GROUP BY h.`id_order`
					) t1
					GROUP BY t1.`id_customer`
				""" % ", ".join(customer_ids)):
					order_history_date[item["id_customer"]] = (int(item["cp_3"]), int(item["cp_6"]), int(item["cp_9"]), int(item["cp_12"]))


				first_orders_query = c.query("""
					SELECT o.`id_customer`, o.`id_order`, o.`date_add`
					FROM `ps_order_history` h
					LEFT JOIN `ps_orders` o
					ON h.`id_order` = o.`id_order`
					WHERE o.`id_customer` IN (%s)
					AND h.`id_order_state` = 2
					GROUP BY `id_customer`
					ORDER BY o.`date_add` ASC
				""" % ", ".join(customer_ids))

				first_orders = {}
				first_orders_ids = {}
				for item in first_orders_query:
					first_orders[item["id_customer"]] = item["date_add"]
					first_orders_ids[item["id_customer"]] = str(item["id_order"])

				second_orders = {}
				second_orders_ids = {}
				last_orders = {}

				if(len(first_orders_ids.keys()) > 0):
					for customer in first_orders.keys():
						second_orders_query = c.query("""
							SELECT o.`id_customer`, o.`id_order`, o.`date_add`
							FROM `ps_order_history` h
							LEFT JOIN `ps_orders` o
							ON h.`id_order` = o.`id_order`
							WHERE o.`id_customer` = %s
							AND h.`id_order_state` = 2
							AND o.`id_order` != %s
							AND CAST(o.`date_add` AS DATE) > "%s"
							GROUP BY `id_customer`
							ORDER BY o.`date_add` ASC
						""" % (customer, first_orders_ids[customer], first_orders[customer]))

						for item in second_orders_query:
							second_orders[item["id_customer"]] = item["date_add"]
							second_orders_ids[item["id_customer"]] = str(item["id_order"])

					last_orders_query = c.query("""
						SELECT o.`id_customer`, MAX(o.`id_order`) as `id_order`, MAX(o.`date_add`) as `date_add`
						FROM `ps_order_history` h
						LEFT JOIN `ps_orders` o
						ON h.`id_order` = o.`id_order`
						WHERE o.`id_customer` IN (%s)
						AND h.`id_order_state` = 2
						GROUP BY o.`id_customer`
						ORDER BY o.`date_add` DESC
					""" % (", ".join([str(x) for x in first_orders.keys()]), ))

					for item in last_orders_query:
						last_orders[item["id_customer"]] = item["date_add"]

				orders = {}
				for item in total_orders:
					orders[item["id_customer"]] = (item["total_orders"], item["nb_orders"])

				for customer in customers:
					f.writerow({
						"id_customer": customer["id_customer"],
						"email": customer["email"],
						"sponsor_id": sponsors.get(customer["id_customer"], ("", ""))[0],
						"sponsor_email": sponsors.get(customer["id_customer"], ("", ""))[1],						
						"date_add": customer["date_add"],
						"date_upd": customer["date_upd"],
						"total_orders_amount": orders.get(customer["id_customer"], ["0.0"])[0],
						"total_orders": orders.get(customer["id_customer"], ["0.0", "0"])[1],
						"newsletter": customer["newsletter"],
						"optin": customer["optin"],
						"first_order": first_orders.get(customer["id_customer"], "N/A"),
						"second_order": second_orders.get(customer["id_customer"], "N/A"),
						"last_order": last_orders.get(customer["id_customer"], "N/A"),
						"nb_orders_3": order_history_date.get(customer["id_customer"], ["0"])[0],
						"nb_orders_6": order_history_date.get(customer["id_customer"], ["0", "0"])[1],
						"nb_orders_9": order_history_date.get(customer["id_customer"], ["0", "0", "0"])[2],
						"nb_orders_12": order_history_date.get(customer["id_customer"], ["0", "0", "0", "0"])[3],
						"balise_ed": tags.get(customer["id_customer"], "N/A"),
					})

		fp.flush()
		fp.close()

c.close()
print datetime.utcnow()