def _step1(dbname):
"""
:return: A List of Tuples, where each tuple 0 index is the id_customer and 1 is sku
"""
query = """
SELECT a.fk_customer, b.sku, a.order_nr, a.grand_total
FROM sales_order a JOIN sales_order_item b
ON a.id_sales_order = b.fk_sales_order
"""
cursor = aux.execute_query(query, dbname)
'''
for tpl in result:
tpl[1] = tpl[1].split('-')[1] # Remove -config from sku
'''
def mapper(tpl):
res = list(tpl)
res[1] = res[1].split('-')[0]
return res
result = map(
mapper,
list(cursor)
)
return result
def get_origination(months):
query = """
SELECT id_customer, created_at
FROM customer
WHERE EXTRACT(YEAR_MONTH FROM created_at) in ('%s')""" % "','".join(months)
cursor = aux.execute_query(query, 'jerry_live')
keys, result = aux.convert_to_id_dict(cursor)
return keys, result, ['Origination Date']
def get_customer(mode, language):
""" Follows the query_event specifications
Get customers from the database
:param mode: Any one of
1. 'all': no discrimination on region, default
2. 'uae': UAE customers
3. 'ksa': KSA customers
4. 'both': Not implemented
4. 'others': Not implemented
:param language: An optional language
:return: A tuple (
keys: A set of id_customer,
result: A dictionary { id_customer, [phone, language] },
headers: A list of headers
)
"""
if mode == 'uae':
where_clause = 'where cust.fk_country = 3'
elif mode == 'ksa':
where_clause = 'where cust.fk_country = 193'
else:
where_clause = ''
query = """
SELECT distinct cust.id_customer, phone.number, if(cust.fk_language=1, 'English', 'Arabic') as language, if(cust.fk_country=3, 'UAE', 'KSA') as country
FROM customer cust INNER JOIN customer_phone phone
ON phone.fk_customer = cust.id_customer
%s
""" % where_clause
if language is not None:
fquery = """
SELECT * FROM (%s) AS T WHERE language = '%s'""" % (query, language)
else:
fquery = query
cursor = aux.execute_query(fquery, 'jerry_live')
result = {}
keys = set()
keys, result = aux.convert_to_id_dict(cursor)
return keys, result, ['Phone', 'language', 'Country']
def _step2(cat_list):
"""
:return: A dict of { sku: category }
"""
where_clause = 'where cat.name in (%s)' % json.dumps(cat_list).strip('[]')
query = """
SELECT conf.sku, cat.name FROM catalog_config conf
JOIN catalog_config_has_catalog_category cc ON cc.fk_catalog_config = conf.id_catalog_config
JOIN catalog_category cat on cc.fk_catalog_category = cat.id_catalog_category
%(where_clause)s
""" % locals()
cursor = aux.execute_query(query, 'bob_live_ae') # this one only in ae
result = {}
for tpl in list(cursor):
result[tpl[0]] = tpl[1] # Will ovewrite some skus
return result
def _step2(cat_list):
"""
:return: A dict of { sku: category }
"""
where_clause = 'where cat.name in (%s)' % json.dumps(cat_list).strip('[]')
query = """
SELECT conf.sku, cat.name FROM catalog_config conf
JOIN catalog_config_has_catalog_category cc ON cc.fk_catalog_config = conf.id_catalog_config
JOIN catalog_category cat on cc.fk_catalog_category = cat.id_catalog_category
%(where_clause)s
""" % locals()
cursor = aux.execute_query(query, 'bob_live_ae') # this one only in ae
result = {}
for tpl in list(cursor):
result[tpl[0]] = tpl[1] # Will ovewrite some skus
return result
def _step1(dbname):
"""
:return: A List of Tuples, where each tuple 0 index is the id_customer and 1 is sku
"""
query = """
SELECT a.fk_customer, b.sku, a.order_nr, a.grand_total
FROM sales_order a JOIN sales_order_item b
ON a.id_sales_order = b.fk_sales_order
"""
cursor = aux.execute_query(query, dbname)
'''
for tpl in result:
tpl[1] = tpl[1].split('-')[1] # Remove -config from sku
'''
def mapper(tpl):
res = list(tpl)
res[1] = res[1].split('-')[0]
return res
result = map(mapper, list(cursor))
return result
def execute_fn(dbname):
cursor = aux.execute_query(query, dbname)
return map(lambda k: list(k), list(cursor))
def execute_fn(dbname):
cursor = aux.execute_query(query, dbname)
return map(
lambda k: list(k),
list(cursor)
)
