205 lines
7.4 KiB
Python
205 lines
7.4 KiB
Python
#!/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()
|