def sale_product(workbook, mbrand, mtype, sheet_name, version_dict,
memory_dict, color_dict):
sku_sql = '''
SELECT sws.key_props FROM panda.`stg_warning_sku` sws
WHERE sws.type_id = {}
AND sws.brand_id= {}
'''
conf.product_cursor.execute(sku_sql.format(mtype, mbrand))
sku_result = conf.product_cursor.fetchall()
sku_dict = conf.product_count(sku_result, version_dict, memory_dict,
color_dict)
sale_sql = '''
SELECT pp.`key_props`,pp.`product_name` FROM panda.`odi_order` oo
LEFT JOIN panda.`pdi_product` pp
ON oo.`product_id` = pp.`product_id`
WHERE oo.`order_status` IN (1,2,4,5)
AND oo.`order_type` IN (1,2)
AND pp.`brand_id` = {}
AND pp.`type_id` = {}
AND oo.`pay_at` > '{}'
AND oo.`pay_at` < '{}'
AND pp.`product_name` NOT LIKE '测试%'
AND pp.`product_name` NOT LIKE '配件%'
'''
conf.product_cursor.execute(
sale_sql.format(mbrand, mtype,
conf.last_week_day.strftime(conf.date_format),
conf.today.strftime(conf.date_format)))
sale_result = conf.product_cursor.fetchall()
sale_dict = conf.product_count(sale_result, version_dict, memory_dict,
color_dict)
store_sql = '''
SELECT sw.key_props FROM panda.`stg_warehouse` sw
left join panda.pdi_product pp
on sw.product_id = pp.product_id
WHERE sw.warehouse_num IN (1,2,4,7)
AND sw.warehouse_status = 1
AND sw.brand_id = {}
and pp.type_id = {}
'''
conf.product_cursor.execute(store_sql.format(mbrand, mtype))
store_result = conf.product_cursor.fetchall()
store_dict = conf.product_count(store_result, version_dict, memory_dict,
color_dict)
sheet = workbook.add_sheet(sheet_name)
sheet.write(0, 0, '型号')
sheet.write(0, 1, '内存')
sheet.write(0, 2, '颜色')
sheet.write(0, 3, '7天销售')
sheet.write(0, 4, '库存')
for s in sku_dict:
pv, pm, pc = s.split(':')
row = len(sheet.rows)
sheet.write(row, 0, pv)
sheet.write(row, 1, pm)
sheet.write(row, 2, pc)
sheet.write(row, 3, sale_dict[s] if s in sale_dict else 0)
sheet.write(row, 4, store_dict[s] if s in store_dict else 0)
for s in sale_dict:
if s not in sku_dict:
pv, pm, pc = s.split(':')
row = len(sheet.rows)
sheet.write(row, 0, pv)
sheet.write(row, 1, pm)
sheet.write(row, 2, pc)
sheet.write(row, 3, sale_dict[s])
sheet.write(row, 4, store_dict[s] if s in store_dict else 0)
for s in store_dict:
if s not in sku_dict and s not in sale_dict:
pv, pm, pc = s.split(':')
row = len(sheet.rows)
sheet.write(row, 0, pv)
sheet.write(row, 1, pm)
sheet.write(row, 2, pc)
sheet.write(row, 3, sale_dict[s] if s in sale_dict else 0)
sheet.write(row, 4, store_dict[s])
'''
version_dict = config.properties_dict(src_cur, props_sql, 5)
memory_dict = config.properties_dict(src_cur, props_sql, 11)
color_dict = config.properties_dict(src_cur, props_sql, 10)
print('start...', time.time() - start_time)
sale_sql = '''
SELECT pp.`key_props` FROM panda.`odi_order` oo
LEFT JOIN panda.`pdi_product` pp
ON oo.`product_id` = pp.`product_id`
WHERE oo.`order_status` IN (1,2,4,5)
AND oo.`pay_at` > '2017-11-1'
'''
src_cur.execute(sale_sql)
result = src_cur.fetchall()
sale_dict = config.product_count(result, version_dict, memory_dict, color_dict)
sheet = workbook.add_sheet('sheet')
# store_sql = '''
# SELECT sw.`key_props` FROM panda.`stg_warehouse` sw
# LEFT JOIN panda.`pdi_model` pm
# ON pm.`model_id` = sw.`model_id`
# WHERE sw.`warehouse_status`=1
# and sw.warehouse_num in (1,2,4,7)
# '''
store_sql = '''
SELECT sw.key_props FROM panda.`stg_warehouse` sw
LEFT JOIN panda.`pdi_product` pp
ON sw.`product_id` = pp.product_id
WHERE sw.`warehouse_status` = 1
and pp.status != 3
memory_dict = conf.properties_dict(src_cur, properties_sql, 11)
rate_dict = conf.properties_dict(src_cur, properties_sql, 12)
products_sql = '''
SELECT pp.key_props FROM panda.pdi_product pp
LEFT JOIN panda.pdi_model pm
ON pp.model_id = pm.model_id
WHERE pm.model_name NOT LIKE '%iphone%'
and pp.type_id = 1
'''
src_cur.execute(products_sql)
products_result = src_cur.fetchall()
product_dict = conf.product_count(products_result, version_dict, memory_dict,
color_dict, rate_dict)
version_color_dict = conf.version_color_dict
version_memory_dict = conf.version_memory_dict
sheet = workbook.add_sheet('iphone')
sheet.write(0, 0, '型号')
sheet.write(0, 1, '内存')
sheet.write(0, 2, '颜色')
sheet.write(0, 3, '成色')
sheet.write(0, 4, '数量')
for rate in rate_dict:
for version in version_color_dict:
for color in version_color_dict[version]:
for memory in version_memory_dict[version]:
row = len(sheet.rows)
sheet.write(row, 0, version_dict[str(version)])
def sales_sku(cursor, workbook, start, end, sale_condition, sku_condition,
sheet, high_level, mid_level):
count_sql = '''
select count(1) from panda.odi_order oo
where oo.order_status in (1,2,4,5)
and oo.order_type in (1,2)
and oo.pay_at > '{}'
and oo.pay_at < '{}'
'''
cursor.execute(count_sql.format(start, end))
count_result = cursor.fetchone()
count = count_result[0]
sale_sql = '''
select pp.key_props from panda.odi_order oo
left join panda.pdi_product pp
on oo.product_id = pp.product_id
left join panda.pdi_model pm
on pp.model_id = pm.model_id
where oo.order_status in (1,2,4,5)
and oo.order_type in (1,2)
and oo.pay_at < '{}'
and oo.pay_at > '{}'
and {}
'''
cursor.execute(sale_sql.format(end, start, sale_condition))
sales_result = cursor.fetchall()
sales_dict = conf.product_count(sales_result, vd, md, cd)
sku_sql = '''
select sws.key_props,sws.sku_id from panda.stg_warning_sku sws
where {}
'''
cursor.execute(sku_sql.format(sku_condition))
sku_result = cursor.fetchall()
sku_dict = product_count(sku_result, vd, md, cd)
sheet = workbook.add_sheet(sheet)
sheet.write(0, 0, 'SKUID')
sheet.write(0, 1, '型号')
sheet.write(0, 2, '内存')
sheet.write(0, 3, '颜色')
sheet.write(0, 4, '数量')
sheet.write(0, 5, '占比')
for i, s in enumerate(sku_dict):
pv, pm, pc = s.split(':')
sheet.write(i + 1, 0, sku_dict[s])
sheet.write(i + 1, 1, pv)
sheet.write(i + 1, 2, pm)
sheet.write(i + 1, 3, pc)
sheet.write(i + 1, 4, sales_dict[s] if s in sales_dict else 0)
sheet.write(i + 1, 5, sales_dict[s] / count if s in sales_dict else 0)
level = sales_dict[s] / count if s in sales_dict else 0
if level > high_level:
resp = re.get(conf.warning_sku.format(sku_dict[s], 2))
print(s, level, high_level,
conf.warning_sku.format(sku_dict[s], 2), resp)
elif mid_level < level < high_level:
resp = re.get(conf.warning_sku.format(sku_dict[s], 1))
print(s, level, high_level,
conf.warning_sku.format(sku_dict[s], 1), resp)
elif level < mid_level:
resp = re.get(conf.warning_sku.format(sku_dict[s], 3))
print(s, level, high_level,
conf.warning_sku.format(sku_dict[s], 3), resp)
WHERE ppv.pnid = {}
'''
vd = conf.properties_dict(conf.product_cursor, propsql, 5)
md = conf.properties_dict(conf.product_cursor, propsql, 11)
cd = conf.properties_dict(conf.product_cursor, propsql, 10)
shangjia_sql = '''
SELECT ppt.`key_props` FROM panda.`pdi_product_track` ppt
WHERE ppt.`track_type`=1
AND ppt.`product_status`!=3
AND ppt.`created_at`>'{}'
{}
'''
conf.product_cursor.execute(shangjia_sql.format('2018-1-1', ''))
result2018 = conf.product_cursor.fetchall()
shangjia2018dict = conf.product_count(result2018, vd, md, cd)
conf.product_cursor.execute(
shangjia_sql.format('2017-12-1', 'and ppt.created_at < \'2018-1-1\''))
result2017 = conf.product_cursor.fetchall()
shangjia2017dict = conf.product_count(result2017, vd, md, cd)
wb = xlwt.Workbook()
sheet = wb.add_sheet("12yue")
sheet.write(0, 0, '型号')
sheet.write(0, 1, '内存')
sheet.write(0, 2, '颜色')
sheet.write(0, 3, '数量')
for r in shangjia2017dict:
row = len(sheet.rows)
pv, pm, pc = r.split(':')
prop_sql = '''
SELECT pp.`key_props` FROM panda.`odi_order` oo
LEFT JOIN panda.`pdi_product` pp
ON oo.`product_id` = pp.`product_id`
WHERE oo.`order_status` IN (1,2,4,5)
AND oo.`order_type` IN (1,2)
AND oo.`pay_at`>'{}'
AND oo.`pay_at`<'{}'
'''
fifteen_day = conf.today - datetime.timedelta(15)
cursor.execute(
prop_sql.format(fifteen_day.strftime(conf.date_format),
conf.today.strftime(conf.date_format)))
grounding_result = cursor.fetchall()
grounding_dict = conf.product_count(grounding_result, vd, md, cd)
sheet = wb.add_sheet('sheet')
sheet.write(0, 0, '型号')
sheet.write(0, 1, '内存')
sheet.write(0, 2, '颜色')
sheet.write(0, 3, '数量')
for i, r in enumerate(grounding_dict):
pv, pm, pc = r.split(':')
sheet.write(i + 1, 0, pv)
sheet.write(i + 1, 1, pm)
sheet.write(i + 1, 2, pc)
sheet.write(i + 1, 3, grounding_dict[r])
wb.save(conf.path + '15daysale.xls')
cursor.close()
connect.close()