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()