def get(self):
start_time = get_argument('start_time', default='2019-01-01')
end_time = get_argument('end_time', default='2019-01-29')
pr = pd.period_range(start=start_time, end=end_time, freq='M')
prTupes = tuple([(period.month, period.year) for period in pr])
difference = len(prTupes)
last_month_start_time = \
((datetime.datetime.strptime(start_time, '%Y-%m-%d') -
relativedelta(months=difference))).strftime(
'%Y-%m-%d')
last_month_start_end = \
((datetime.datetime.strptime(end_time, '%Y-%m-%d') -
relativedelta(months=difference))).strftime('%Y-%m-%d')
this_month_data = get_data(start_time, end_time)
last_month_data = get_data(last_month_start_time, last_month_start_end)
month_on_month_ratio = {}
for k, v in this_month_data.items():
if k != 'date':
month_on_month_ratio.update({
'basis_' + k: (this_month_data[k] - last_month_data[k]) / last_month_data[k]
})
return ok(data={'data': [last_month_data, this_month_data], **month_on_month_ratio})
def db_commit(db, msg=''):
try:
db.session.commit()
except DatabaseError as e:
db.session.rollback()
raise SystemException(SystemError.DATABASE_ERROR)
else:
return ok(msg=msg)
def get(self):
start_time = get_argument('start_time', default='2020-10-01')
end_time = get_argument('end_time', default='2020-10-29')
year = re.findall(r'(\d+)', start_time)
year_end = re.findall(r'(\d+)', end_time)
last_year_start_time = str(int(year[0]) - 1) + '-' + year[1] + '-' + year[2]
last_year_end_time = str(int(year_end[0]) - 1) + '-' + year_end[1] + '-' + year_end[2]
this_year_data = get_data(start_time, end_time)
last_year_data = get_data(last_year_start_time, last_year_end_time)
year_on_year_basis = {}
for k, v in this_year_data.items():
if k != 'date':
year_on_year_basis.update({
'basis_' + k: (this_year_data[k] - last_year_data[k]) / this_year_data[k]
})
return ok(data={'data': [last_year_data, this_year_data], **year_on_year_basis})
def get(self):
start_time = get_argument("start_time",
default="2020-10-1")
end_time = get_argument("end_time", default="2020-10-2")
if start_time > end_time:
raise ServiceException(ServiceError.INVALID_VALUE)
sources = ["Monitor mounts", "TV mounts", "Tablet mounts", "TV carts", "Laptopmounts"]
results = SalesAnalysisReportModel.query.filter(
SalesAnalysisReportModel.ord_pay_time.between(start_time, end_time),
SalesAnalysisReportModel.pro_sec_type.in_(sources))
tmp_time = list()
tmp_dic = dict()
for result in results:
obj_dict = obj_to_dict(result, keys=[], display=False)
pay_time = obj_dict['ord_pay_time']
pay_time = re.match(r'(\d+-\d+-\d+)', pay_time)[0]
if pay_time not in tmp_time:
tmp_time.append(pay_time)
tmp_dic[pay_time] = {
"total_num": 0,
"total_amount": 0,
"Monitor mounts": {"sale_amount": 0, "sale_num": 0, "sale_make_up": 0},
"TV mounts": {"sale_amount": 0, "sale_num": 0, "sale_make_up": 0},
"Tablet mounts": {"sale_amount": 0, "sale_num": 0, "sale_make_up": 0},
"TV carts": {"sale_amount": 0, "sale_num": 0, "sale_make_up": 0},
"Laptopmounts": {"sale_amount": 0, "sale_num": 0, "sale_make_up": 0},
}
sale_amount = obj_dict.get("ord_sale_amount", 0)
tmp_dic[pay_time][obj_dict.get("pro_sec_type")]["sale_amount"] += float(sale_amount) if sale_amount else 0
tmp_dic[pay_time]["total_amount"] += float(sale_amount) if sale_amount else 0
sale_num = obj_dict.get("ord_salenum", 0)
tmp_dic[pay_time][obj_dict.get("pro_sec_type")]["sale_num"] += int(sale_num) if sale_num else 0
tmp_dic[pay_time]["total_num"] += float(sale_num) if sale_num else 0
res_data = []
num = 0
for tmp in tmp_dic:
num += 1
total_num = tmp_dic[tmp]['total_num']
total_amount = tmp_dic[tmp]['total_amount']
if not total_amount:
continue
res_dic = {
"id": num,
"data_time": tmp,
"total_num": int(total_num),
"total_amount": round(float(total_amount), 2),
}
for mounts in ["Monitor mounts", "TV mounts", "Tablet mounts", "TV carts", "Laptopmounts"]:
res_key = mounts.lower().replace(' ', '_')
sale_make_up = round(tmp_dic[tmp][mounts]["sale_amount"] / total_amount, 4)
res_dic.update({
"sale_{}_num".format(res_key): tmp_dic[tmp][mounts]["sale_num"],
"sale_{}_amount".format(res_key): round(tmp_dic[tmp][mounts]["sale_amount"], 2),
"sale_{}_make_up".format(res_key): sale_make_up
})
res_data.append(res_dic)
return ok({"result": res_data})
def get(self):
start_time = get_argument('start_time', default='2020-09-01')
end_time = get_argument('end_time', default='2020-09-23')
if start_time > end_time:
raise ServiceException(ServiceError.INVALID_VALUE)
user = SalesAnalysisReportModel.query
if all([start_time, end_time]):
user = user.filter(SalesAnalysisReportModel.ord_pay_time.between(start_time, end_time))
source_code = ['FlexispotUS', 'FleximountsUS', 'FlexiSpotUK', 'FlexiSpotDE', 'FlexiSpotFR', 'FlexiSpotJP',
'FleximountsJP']
user = user.filter(SalesAnalysisReportModel.source_code.in_(source_code)).all()
data = []
tmp = []
for item in user:
arg = obj_to_dict(item, [''], display=False, format_time='%Y-%m-%d')
pay_date = arg['ord_pay_time']
ord_sale_amount = arg['ord_sale_amount']
source = arg['source_code']
args = {
'date': pay_date,
f'ord_sale_amount_{source}': ord_sale_amount,
f'ord_salenum_{source}': arg['ord_salenum'],
f'ord_maoli_{source}': arg['ord_maoli']
}
for k, v in args.items():
if not args[k]:
args[k] = 0
if pay_date not in tmp:
tmp.append(pay_date)
data.append(args)
if f'ord_sale_amount{source}' not in data[tmp.index(pay_date)]:
data[tmp.index(pay_date)].update(args)
else:
data[tmp.index(pay_date)].update({
f'ord_sale_amount_{source}': args['ord_sale_amount_' + source] + data[tmp.index(pay_date)][
'ord_sale_amount_' + source],
f'ord_salenum_{source}': args['ord_salenum_' + source] + data[tmp.index(pay_date)][
'ord_salenum_' + source],
f'ord_maoli_{source}': args['ord_maoli_' + source] + data[tmp.index(pay_date)][
'ord_maoli_' + source],
})
tmp_dic = {}
if not data:
return ok(data={'data': []})
for dic in data:
dic['ord_sale_amount_Global'] = round(
sum([v for k, v in dic.items() if k.startswith('ord_sale_amount_') and v]), 2)
dic['ord_salenum_Global'] = sum([v for k, v in dic.items() if k.startswith('ord_salenum_') and v])
dic['ord_maili_Global'] = round(sum([v for k, v in dic.items() if k.startswith('ord_maoli_') and v]), 2)
dic['ord_maoli_rate_Global'] = round(
sum([v for k, v in dic.items() if k.startswith('ord_maoli_') and v]) / dic[
'ord_sale_amount_Global'] if dic['ord_sale_amount_Global'] else 0, 4)
total_dic = {}
for dic in data:
for source in source_code:
if f'ord_salenum_{source}' in dic.keys(): # ord_maoli / ord_sale_amount
dic.update({
f'ord_maoli_rate_{source}': round(dic.get(f'ord_maoli_{source}', 0) /
dic[f'ord_sale_amount_{source}'] if dic[
f'ord_sale_amount_{source}'] else 0, 4),
f'market_share_{source}': round(dic[f'ord_sale_amount_{source}'] /
dic['ord_sale_amount_Global'] if dic[
'ord_sale_amount_Global'] else 0, 4)
})
total_dic.update({
f'total_ord_sale_amount_{source}': round(dic.get(f'ord_sale_amount_{source}', 0) +
total_dic.get(f'total_ord_sale_amount_{source}', 0),
2),
f'total_ord_salenum_{source}': dic.get(f'ord_salenum_{source}', 0) +
total_dic.get(f'total_ord_salenum_{source}', 0),
f'total_ord_maoli_{source}': dic.get(f'ord_maoli_{source}', 0) +
total_dic.get(f'total_ord_maoli_{source}', 0),
})
total_dic['total_ord_salenum_Global'] = sum(
[v for k, v in total_dic.items() if k.startswith('total_ord_salenum_') and v])
total_dic['total_ord_sale_amount_Global'] = round(sum([v for k, v in total_dic.items() if
k.startswith('total_ord_sale_amount_') and v]), 2)
total_dic['total_ord_maoli_Global'] = sum([v for k, v in total_dic.items() if
k.startswith('total_ord_maoli_') and v])
total_dic['total_ord_maoli_rate_Global'] = round(total_dic['total_ord_maoli_Global'] / \
total_dic['total_ord_sale_amount_Global'], 4)
for source in source_code:
if f'ord_salenum_{source}' in total_dic.keys():
total_dic[f'total_ord_maoli_rate_{source}'] = (
total_dic.get(f'total_ord_maoli_{source}', 0) / total_dic.get(
f'total_ord_sale_amount_{source}', 0))
return ok(data={'data': data, **total_dic})
def get(self):
start_time = get_argument("start_time",
default="2019-07-01",
required=True)
end_time = get_argument("end_time", default="2020-10-01")
results = OrdSourceAdfeeModel.query.filter(
OrdSourceAdfeeModel.order_pay_time.between(start_time, end_time))
city_list = [
"total_us_fee",
"total_japan_fee",
"total_europe_fee",
"total_canada_fee",
"total_loctek_fee",
"total_google_fee",
"total_india_fee",
]
sorts_list = [
"TV mounts",
"Monitor mounts",
"Fitness",
"Spacemaster mounts",
"Desktop Riser",
"height adjustable desk",
"Monitor Stand",
"healthcare",
"offacc",
]
day_list = []
tmp_dic = {}
for result in results:
store_code = obj_to_dict(result, keys=[], display=False)
ord_pay_time = "{}({})".format(
re.match(r'(\d+-\d+-\d+)',
store_code.get("order_pay_time"))[0],
store_code.get("week"))
if ord_pay_time not in day_list:
day_list.append(ord_pay_time)
tmp_dic.update({ord_pay_time: {
"total_amount": 0,
}})
for sorts in sorts_list:
tmp_dic[ord_pay_time].update({
sorts.lower().replace(' ', '_'):
0,
})
for city in city_list:
tmp_dic[ord_pay_time].update({
city: 0,
})
for sort in city_list:
try:
amount = store_code.get(sort)
tmp_dic[ord_pay_time][sort] += float(
amount) if amount else 0
except Exception as e:
pass
total_ad_fee = store_code.get('total_ad_fee')
ad_fee_pro_type = store_code.get('ad_fee_pro_type')
if ad_fee_pro_type in sorts_list:
dmp_key = ad_fee_pro_type.lower().replace(' ', '_')
tmp_dic[ord_pay_time][dmp_key] += float(
total_ad_fee) if total_ad_fee else 0
tmp_dic[ord_pay_time]["total_amount"] += float(
total_ad_fee) if total_ad_fee else 0
res_list = []
num = 0
for tmp in tmp_dic:
num += 1
args = {
"id":
num,
"data_time":
tmp,
"desktop_riser":
round(tmp_dic[tmp]["desktop_riser"], 2),
"fitness":
round(tmp_dic[tmp]["fitness"], 2),
"healthcare":
round(tmp_dic[tmp]["healthcare"], 2),
"height_adjustable_desk":
round(tmp_dic[tmp]["height_adjustable_desk"], 2),
"monitor_mounts":
round(tmp_dic[tmp]["monitor_mounts"], 2),
"monitor_stand":
round(tmp_dic[tmp]["monitor_stand"], 2),
"offacc":
round(tmp_dic[tmp]["offacc"], 2),
"spacemaster_mounts":
round(tmp_dic[tmp]["spacemaster_mounts"], 2),
"total_amount":
round(tmp_dic[tmp]["total_amount"], 2),
"total_canada_fee":
round(tmp_dic[tmp]["total_canada_fee"], 2),
"total_europe_fee":
round(tmp_dic[tmp]["total_europe_fee"], 2),
"total_google_fee":
round(tmp_dic[tmp]["total_google_fee"], 2),
"total_india_fee":
round(tmp_dic[tmp]["total_india_fee"], 2),
"total_japan_fee":
round(tmp_dic[tmp]["total_japan_fee"], 2),
"total_loctek_fee":
round(tmp_dic[tmp]["total_loctek_fee"], 2),
"total_us_fee":
round(tmp_dic[tmp]["total_us_fee"], 2),
"tv_mounts":
round(tmp_dic[tmp]["tv_mounts"], 2),
}
res_list.append(args)
return ok(res_list)
def get(self):
table = db.session.query(SalesM, ProductM).join(SalesM, SalesM.pro_fname == ProductM.bsname)
start_time = get_argument('start_time', default='2019-09-01')
end_time = get_argument('end_time', default='2019-09-21')
# start_time = datetime.date.today() - datetime.timedelta(days=170)
#
# end_time = datetime.date.today()
ord_sitecode = get_argument("ord_sitecode", default="FlexiSpotUSOffline")
results = table.filter(SalesM.ord_pay_time.between(start_time, end_time),
SalesM.ord_sitecode == ord_sitecode)
tmp_list = []
tmp_dic = {}
for sales_table, product_table in results:
obj_dict = obj_to_dict(sales_table, keys=[], display=False)
obj_dict.update({"pro_image_path": product_table.pro_image_path})
model_name = "{}_{}".format(obj_dict.get('pro_fname'), obj_dict.get('pro_bsname'))
if model_name not in tmp_list:
tmp_dic.update({
model_name: {
"image": '', # 图
"sale_num": 0, # 销售数
"sale_amount": 0, # 销售额
"sale_price": 0, # 售价 (销售额 / 销量)
"sale_expfee": 0, # 快递费/均值（快递费/销量）
"sale_platfee": 0, # 平台费
"sale_factoryfee": 0, # 出厂费
"sale_fobfee": 0, # fob费用
"sale_huanhui": 0, # 换汇（出厂价/fob）
"sale_costfee": 0, # 成本费
"sale_rate_platfee": 0, # 成本率（成本费 / 销售额 ）
"sale_rate_expfee": 0, # 物流率（快递费 / 销售额 ）
"sale_rate_materialrate": [], # 材料率
"sale_alone_maoli": 0, # 单毛利（ 毛利 / 销量）
"sale_maoli": 0, # 毛利
}
})
if not tmp_dic[model_name]["image"]:
pass
else:
image = obj_dict.get("pro_image_path")
tmp_dic[model_name]["image"] = image
sale_num = obj_dict.get('ord_salenum')
tmp_dic[model_name]["sale_num"] += int(sale_num) if sale_num else 0
sale_amount = obj_dict.get('ord_sale_amount')
tmp_dic[model_name]["sale_amount"] += round(float(sale_amount), 2) if sale_amount else 0
sale_expfee = obj_dict.get('ord_factoryfee')
tmp_dic[model_name]["sale_expfee"] += round(float(sale_expfee), 2) if sale_expfee else 0
sale_factoryfee = obj_dict.get('ord_salenum')
tmp_dic[model_name]["sale_factoryfee"] += round(float(sale_factoryfee), 2) if sale_amount else 0
sale_fobfee = obj_dict.get('ord_fobfee')
tmp_dic[model_name]["sale_fobfee"] += round(float(sale_fobfee), 2) if sale_fobfee else 0
sale_costfee = obj_dict.get('ord_costfee')
tmp_dic[model_name]["sale_costfee"] += round(float(sale_costfee), 2) if sale_costfee else 0
try:
sale_rate_materialrate = obj_dict.get('pro_materialrate')
tmp_dic[model_name]["sale_rate_materialrate"].append(
round(float(sale_rate_materialrate), 2) if sale_costfee else 0)
except:
tmp_dic[model_name]["sale_rate_materialrate"].append(0)
try:
sale_platfee = obj_dict.get('ord_platfee')
tmp_dic[model_name]["sale_platfee"] += round(float(sale_platfee), 2) if sale_costfee else 0
except Exception as e:
pass
try:
sale_maoli = obj_dict.get('ord_maoli')
tmp_dic[model_name]["sale_maoli"] += round(float(sale_maoli), 2) if sale_maoli else 0
except Exception as e:
pass
res_dic = {"data": []}
for tmp_name in tmp_dic:
sale_amount = tmp_dic[tmp_name]["sale_amount"]
sale_num = tmp_dic[tmp_name]["sale_num"]
sale_expfee = tmp_dic[tmp_name]["sale_expfee"]
sale_factoryfee = tmp_dic[tmp_name]["sale_factoryfee"]
sale_fobfee = tmp_dic[tmp_name]["sale_fobfee"]
sale_costfee = tmp_dic[tmp_name]["sale_costfee"]
sale_maoli = tmp_dic[tmp_name]["sale_maoli"]
sale_materialrate = list(float(i) for i in tmp_dic[tmp_name]["sale_rate_materialrate"])
model_dic = {
"fname": tmp_name.split('_')[0], # 销售型号
"bsname": tmp_name.split('_')[1], # 乐歌型号
"image": tmp_dic[tmp_name]["image"],
'sale_num': sale_num,
'sale_amount': sale_amount,
'sale_price': round(sale_amount / sale_num, 2),
'sale_platfee': round(tmp_dic[tmp_name]["sale_platfee"], 2),
'sale_expfee': round(sale_expfee / sale_num, 2),
'sale_factoryfee': round(sale_factoryfee, 2),
'sale_fobfee': round(sale_fobfee, 2),
'sale_swap': round(sale_factoryfee / sale_fobfee, 2), # 换汇（出厂价/fob）
'sale_rate_platfee': round(sale_costfee / sale_amount, 4), # 成本率（成本费 / 销售额 ）
"sale_rate_expfee": round(sale_expfee / sale_amount, 4), # 物流率
"sale_rate_materialrate": round(sum(sale_materialrate) / len(sale_materialrate), 4), # 材料率
'sale_alone_maoli': round(sale_maoli / sale_amount, 0), # 单毛利（ 毛利 / 销量）
'sale_maoli': round(sale_maoli, 0), # 毛利
}
res_dic["data"].append(model_dic)
data_src = str(res_dic)
total_num = sum(int(i) for i in re.findall('sale_num\': (\\d+)', data_src)) # 总销量
if not total_num:
return ok(res_dic)
total_amount = sum(float(i) for i in re.findall('sale_amount\': (\\d+.\\d+)', data_src))
total_expfee = sum(float(i) for i in re.findall('sale_expfee\': (\\d+.\\d+)', data_src))
total_factoryfee = sum(float(i) for i in re.findall('sale_factoryfee\': (\\d+.\\d+)', data_src))
total_fobfee = sum(float(i) for i in re.findall('sale_fobfee\': (\\d+.\\d+)', data_src))
total_costfee = sum(float(i) for i in re.findall('sale_costfee\': (\\d+.\\d+)', data_src))
total_maoli = sum(float(i) for i in re.findall('sale_maoli\': (\\d+.\\d+)', data_src))
total_materialrate = list(float(i) for i in re.findall('sale_rate_materialrate\': (\\d+.\\d+)', data_src))
res_dic.update({
"total_sale_num": total_num,
"total_sale_amount": round(total_amount, 2),
"total_sale_price": round(total_amount / total_num, 2),
"total_sale_platfee": round(sum(float(i) for i in re.findall('sale_platfee\': (\\d+.\\d+)', data_src)), 2),
"total_sale_expfee": round(
total_expfee / len(list(float(i) for i in re.findall('sale_expfee\': (\\d+.\\d+)', data_src))), 2),
"total_sale_factoryfee": round(total_factoryfee, 2),
"total_sale_fobfee": round(total_fobfee, 2),
"total_sale_swap": round(total_factoryfee / total_fobfee, 2),
'total_sale_rate_platfee': round(total_costfee / total_amount, 4), # 成本率（成本费 / 销售额 ）
'total_sale_rate_expfee': round(total_expfee / total_amount, 4), # 物流率
'total_sale_rate_materialrate': round(sum(total_materialrate) / len(total_materialrate), 4), # 材料率
"total_sale_maoli": round(total_maoli, 2),
'total_sale_alone_maoli': round(total_maoli / total_num, 2), # 单毛利（ 毛利 / 销量）
})
return ok(res_dic)
def get(self):
table = db.session.query(SalesM, ProductM).join(SalesM, SalesM.pro_fname == ProductM.bsname)
start_time = get_argument("start_time", default='2020-10-01')
end_time = get_argument("end_time", default='2020-10-10')
ord_sitecode = get_argument("ord_sitecode", default="Flexispot")
results = table.filter(
SalesM.ord_pay_time.between(start_time, end_time),
SalesM.ord_sitecode == ord_sitecode)
table_dic = {}
table_list = []
ret_dic = {"data": []}
tem_ls = []
self.num = 0
def table_analysis(obj_dict):
"""处理表格数据"""
store_code = obj_dict.get('ord_sitecode', '')
if store_code not in table_list:
table_list.append(store_code)
table_dic[store_code] = {
"model": [], # 执享型号 and 乐歌型号
"model_dic": {},
}
model_key = "{}_{}".format(obj_dict.get('pro_fname'), obj_dict.get('pro_bsname'))
if model_key not in table_dic[store_code]['model']:
table_dic[store_code]['model'].append(model_key)
table_dic[store_code]["model_dic"][model_key] = {
"ord_salenum": 0, # 销量
"ord_sale_amount": 0, # 销售额
"ord_platfee": 0, # 平台扣点
"ord_factoryfee": 0, # 出厂价
"ord_fobfee": 0, # fob费
"pro_materialrate": [], # 材料率
"ord_expfee": 0, # 快递费
"ord_maoli": 0, # 毛利
"image_path": '',
"ord_costfee": 0
}
ord_salenum = float(obj_dict.get("ord_salenum")) if obj_dict.get("ord_salenum") else 0
table_dic[store_code]["model_dic"][model_key]["ord_salenum"] += ord_salenum
ord_sale_amount = float(obj_dict.get("ord_sale_amount")) if obj_dict.get("ord_sale_amount") else 0
table_dic[store_code]["model_dic"][model_key]["ord_sale_amount"] += ord_sale_amount
ord_platfee = float(obj_dict.get("ord_platfee")) if obj_dict.get("ord_platfee") else 0
table_dic[store_code]["model_dic"][model_key]["ord_platfee"] += ord_platfee
ord_factoryfee = float(obj_dict.get("ord_factoryfee")) if obj_dict.get("ord_factoryfee") else 0
table_dic[store_code]["model_dic"][model_key]["ord_factoryfee"] += ord_factoryfee
ord_fobfee = float(obj_dict.get("ord_factoryfee")) if obj_dict.get("ord_fobfee") else 0
table_dic[store_code]["model_dic"][model_key]["ord_fobfee"] += ord_fobfee
pro_materialrate = float(obj_dict.get("pro_materialrate")) if obj_dict.get("pro_materialrate") else 0
table_dic[store_code]["model_dic"][model_key]["pro_materialrate"].append(pro_materialrate)
ord_expfee = float(obj_dict.get("ord_expfee")) if obj_dict.get("ord_expfee") else 0
table_dic[store_code]["model_dic"][model_key]["ord_expfee"] += ord_expfee
ord_maoli = float(obj_dict.get("ord_maoli")) if obj_dict.get("ord_maoli") else 0
table_dic[store_code]["model_dic"][model_key]["ord_maoli"] += ord_maoli
ord_costfee = float(obj_dict.get("ord_costfee")) if obj_dict.get("ord_costfee") else 0
table_dic[store_code]["model_dic"][model_key]["ord_costfee"] += ord_costfee
if table_dic[store_code]["model_dic"][model_key]["image_path"]:
table_dic[store_code]["model_dic"][model_key]["image_path"] = obj_dict.get("pro_image_path")
for sales_table, product_table in results:
obj_dict = obj_to_dict(sales_table, keys=[], display=False)
obj_dict.update({
'pro_image_path': product_table.pro_image_path,
})
table_analysis(obj_dict)
for stop_n in table_dic:
for mode_k in table_dic[stop_n]["model_dic"]:
self.num += 1
shop_name = stop_n
if shop_name in tem_ls:
shop_name = ''
else:
tem_ls.append(shop_name)
sale_num = int(table_dic[stop_n]["model_dic"][mode_k]["ord_salenum"])
sale_amount = float(table_dic[stop_n]["model_dic"][mode_k]["ord_sale_amount"])
sale_expfee = float(table_dic[stop_n]["model_dic"][mode_k]["ord_expfee"])
sale_factoryfee = float(table_dic[stop_n]["model_dic"][mode_k]["ord_factoryfee"])
sale_fobfee = float(table_dic[stop_n]["model_dic"][mode_k]["ord_fobfee"])
sale_materialrate = table_dic[stop_n]["model_dic"][mode_k]["pro_materialrate"]
sale_maoli = table_dic[stop_n]["model_dic"][mode_k]["ord_maoli"]
sale_costfee = table_dic[stop_n]["model_dic"][mode_k]["ord_costfee"]
mode_dic = {
"id": self.num,
"shop_name": shop_name, # 商铺名
"fname": mode_k.split('_')[0], # 销售型号
"bsname": mode_k.split('_')[1], # 乐歌型号
"sale_num": sale_num, # 销售数
"sale_amount": round(sale_amount, 1), # 销售额
"sale_price": round(sale_amount / sale_num, 1), # 售价
"sale_platfee": round(table_dic[stop_n]["model_dic"][mode_k]["ord_platfee"], 1), # 平台扣点
"sale_expfee": round(sale_expfee / sale_num, 1), # 快递费
"sale_factoryfee": round(sale_factoryfee, 1), # 出厂价
"sale_fobfee": round(sale_fobfee, 1), # fob
"sale_swap": round(sale_factoryfee / sale_fobfee, 1), # 换汇
"sale_costfee": round(sale_costfee), # 成本费=fob仓对应的值
"sale_rate_costfee": round(sale_costfee / sale_amount, 3), # 成本率
"sale_rate_expfee": round(sale_expfee / sale_amount, 3), # 物流率
"sale_rate_logistics": round(sum(sale_materialrate) / len(sale_materialrate), 3), # 材料率
"sale_alone_maoli": round(sale_maoli / sale_num, 1), # 单毛利
"sale_maoli": round(sale_maoli, 1), # 毛利
"sale_image": table_dic[stop_n]["model_dic"][mode_k]["image_path"] # 图
}
if mode_dic.get("shop_name"):
mode_dic['children'] = []
ret_dic['data'].append(mode_dic)
else:
ret_dic['data'][-1]['children'].append(mode_dic)
data_src = str(ret_dic)
total_num = sum(float(i) for i in re.findall('sale_num\': (\\d+)', data_src)) # 总销量
if not total_num:
return ok(ret_dic)
total_amount = sum(float(i) for i in re.findall('sale_amount\': (\\d+.\\d+)', data_src)) # 总销售额
total_platfee = sum(float(i) for i in re.findall('sale_platfee\': (\\d+.\\d+)', data_src)) # 总平台费
total_factoryfee = sum(float(i) for i in re.findall('sale_factoryfee\': (\\d+.\\d+)', data_src)) # 总出厂价
total_fobfee = sum(float(i) for i in re.findall('sale_fobfee\': (\\d+.\\d+)', data_src)) # 总fob
total_costfee = sum(float(i) for i in re.findall('sale_costfee\': (\\d+.\\d+)', data_src)) # 成本费=fob仓对应的值
total_expfee = sum(float(i) for i in re.findall('sale_expfee\': (\\d+.\\d+)', data_src))
total_rate_logistics = sum(float(i) for i in re.findall('sale_rate_logistics\': (\\d+.\\d+)', data_src))
total_maoli = sum(float(i) for i in re.findall('sale_maoli\': (\\d+.\\d+)', data_src))
total_sale_swap = round(total_factoryfee / total_fobfee, 2), # 换汇
total_rate_costfee = total_costfee / total_amount, # 成本率
total_rate_expfee = total_expfee / total_amount, # 物流率
# total_rate_logistics = round(sum(total_rate_logistics) / len(total_rate_logistics), 3), # 材料率
ret_dic.update({
"total_sale_num": round(total_num, 1),
"total_sale_amount": round(total_amount, 1),
"total_sale_platfee": round(total_platfee, 1),
"total_sale_factoryfee": round(total_factoryfee, 1),
"total_sale_fobfee": round(total_fobfee, 1),
"total_sale_costfee": round(total_costfee, 1),
"total_sale_expfee": round(total_expfee, 1),
"total_sale_rate_logistics": round(total_rate_logistics, 1),
"total_sale_swap": round(total_sale_swap[0], 3),
"total_sale_rate_costfee": round(total_rate_costfee[0], 3),
"total_sale_rate_expfee": round(total_rate_expfee[0], 3),
"total_sale_alone_maoli": round(total_maoli / total_num, 1),
"total_sale_maoli": round(total_maoli, 1)
})
return ok(ret_dic)
def get(self):
start_time = get_argument('start_time', default='2019-09-01')
end_time = get_argument('end_time', default='2019-09-21')
user = SalesAnalysisReportModel.query
if all([start_time, end_time]):
user = user.filter(
SalesAnalysisReportModel.ord_pay_time.between(
start_time, end_time))
ord_sitecode = ['US', 'EU', 'IN', 'JP', 'CAN']
user = user.filter(
SalesAnalysisReportModel.ord_sitecode.in_(ord_sitecode)).all()
data = []
tmp = []
for item in user:
arg = obj_to_dict(item, [''],
display=False,
format_time='%Y-%m-%d')
pay_date = arg['ord_pay_time']
ord_sale_amount = arg['ord_sale_amount']
sitecode = arg['ord_sitecode']
args = {
'date': pay_date,
f'ord_sale_amount_{sitecode}': ord_sale_amount,
f'ord_salenum_{sitecode}': arg['ord_salenum'],
f'ord_maoli_{sitecode}': arg['ord_maoli']
}
for k, v in args.items():
if not args[k]:
args[k] = 0
if pay_date not in tmp:
tmp.append(pay_date)
data.append(args)
if f'ord_sale_amount{sitecode}' not in data[tmp.index(pay_date)]:
data[tmp.index(pay_date)].update(args)
else:
data[tmp.index(pay_date)].update({
f'ord_sale_amount_{sitecode}':
round(
args['ord_sale_amount_' + sitecode] +
data[tmp.index(pay_date)]['ord_sale_amount_' +
sitecode], 2),
f'ord_salenum_{sitecode}':
args['ord_salenum_' + sitecode] +
data[tmp.index(pay_date)]['ord_salenum_' + sitecode],
f'ord_maoli_{sitecode}':
args['ord_maoli_' + sitecode] +
data[tmp.index(pay_date)]['ord_maoli_' + sitecode],
})
tmp_dic = {}
if not data:
return ok(data={'data': []})
for dic in data:
dic['ord_sale_amount_Global'] = round(
sum([
v for k, v in dic.items()
if k.startswith('ord_sale_amount_') and v
]), 2)
dic['ord_salenum_Global'] = sum([
v for k, v in dic.items() if k.startswith('ord_salenum_') and v
])
dic['ord_maili_Global'] = round(
sum([
v for k, v in dic.items()
if k.startswith('ord_maoli_') and v
]), 2)
dic['ord_maoli_rate_Global'] = round(
sum([
v
for k, v in dic.items() if k.startswith('ord_maoli_') and v
]) / dic['ord_sale_amount_Global']
if dic['ord_sale_amount_Global'] else 0, 4)
total_dic = {}
for dic in data:
for sitecode in ord_sitecode:
if f'ord_salenum_{sitecode}' in dic.keys(
): # ord_maoli / ord_sale_amount
dic.update({
f'ord_maoli_rate_{sitecode}':
round(
dic.get(f'ord_maoli_{sitecode}', 0) /
dic[f'ord_sale_amount_{sitecode}']
if dic[f'ord_sale_amount_{sitecode}'] else 0, 4),
f'market_share_{sitecode}':
round(
dic[f'ord_sale_amount_{sitecode}'] /
dic['ord_sale_amount_Global']
if dic['ord_sale_amount_Global'] else 0, 4)
})
total_dic.update({
f'total_ord_sale_amount_{sitecode}':
round(
dic.get(f'ord_sale_amount_{sitecode}', 0) +
total_dic.get(f'total_ord_sale_amount_{sitecode}',
0), 2),
f'total_ord_salenum_{sitecode}':
dic.get(f'ord_salenum_{sitecode}', 0) +
total_dic.get(f'total_ord_salenum_{sitecode}', 0),
f'total_ord_maoli_{sitecode}':
dic.get(f'ord_maoli_{sitecode}', 0) +
total_dic.get(f'total_ord_maoli_{sitecode}', 0),
})
total_dic['total_ord_salenum_Global'] = sum([
v for k, v in total_dic.items()
if k.startswith('total_ord_salenum_') and v
])
total_dic['total_ord_sale_amount_Global'] = sum([
v for k, v in total_dic.items()
if k.startswith('total_ord_sale_amount_') and v
])
total_dic['total_ord_maoli_Global'] = sum([
v for k, v in total_dic.items()
if k.startswith('total_ord_maoli_') and v
])
total_dic['total_ord_maoli_rate_Global'] = round(total_dic['total_ord_maoli_Global'] / \
total_dic['total_ord_sale_amount_Global'], 2)
for sitecode in ord_sitecode:
if f'total_ord_salenum_{sitecode}' in total_dic.keys():
total_dic[f'total_ord_maoli_rate_{sitecode}'] = round(
(total_dic.get(f'total_ord_maoli_{sitecode}', 0) /
total_dic.get(f'total_ord_sale_amount_{sitecode}', 0)), 4)
total_dic[f'total_market_share_{sitecode}'] = round(total_dic[f'total_ord_sale_amount_{sitecode}'] / \
total_dic['total_ord_sale_amount_Global'], 4)
return ok(data={'data': data, **total_dic})
def get(self):
start_time = get_argument('start_time', default='2019-09-01')
end_time = get_argument('end_time', default='2019-09-30')
user = SalesAnalysisReportModel.query
if all([start_time, end_time]):
user = user.filter(
SalesAnalysisReportModel.ord_pay_time.between(
start_time, end_time))
pro_sec_types = [
'height adjustable desk', 'Desktop Riser', 'Spacemaster mounts',
'Fitness', 'Monitor mounts', 'TV mounts', 'Monitor Stand',
'healthcare'
]
user = user.filter(
SalesAnalysisReportModel.pro_sec_type.in_(pro_sec_types)).all()
print(len(user), 123)
data = []
tmp = []
temp_dict = {}
for item in user:
arg = obj_to_dict(item, [''],
display=False,
format_time='%Y-%m-%d')
pay_date = arg['ord_pay_time']
ord_sale_amount = arg['ord_sale_amount']
pro_sec_type = arg['pro_sec_type'].replace(' ', '_')
args = {
'pay_date': pay_date,
f'ord_sale_amount_{pro_sec_type}': ord_sale_amount,
f'ord_salenum_{pro_sec_type}': arg['ord_salenum'],
}
for k, v in args.items():
if not args[k]:
args[k] = 0
if pay_date not in tmp:
tmp.append(pay_date)
data.append(args)
if f'ord_sale_amount{pro_sec_type}' not in data[tmp.index(
pay_date)]:
data[tmp.index(pay_date)].update(args)
else:
data[tmp.index(pay_date)].update({
f'ord_sale_amount_{pro_sec_type}':
args['ord_sale_amount_' + pro_sec_type] +
data[tmp.index(pay_date)]['ord_sale_amount_' +
pro_sec_type],
f'ord_salenum_{pro_sec_type}':
args['ord_salenum_' + pro_sec_type] +
data[tmp.index(pay_date)]['ord_salenum_' + pro_sec_type],
})
if not data:
return ok(data={[]})
for dic in data:
dic['ord_sale_amount_all'] = sum([
v for k, v in dic.items()
if k.startswith('ord_sale_amount_') and v
])
dic['ord_salenum_all'] = sum([
v for k, v in dic.items() if k.startswith('ord_salenum_') and v
])
total_dic = {}
for dic in data:
for pro_sec_type in pro_sec_types:
pro_sec_type = pro_sec_type.replace(' ', '_')
dic.update({
f'ord_sale_amount_{pro_sec_type}_rate':
round(
dic.get(f'ord_sale_amount_{pro_sec_type}', 0) /
dic['ord_sale_amount_all'], 4)
})
total_dic.update({
f'total_ord_sale_amount_{pro_sec_type}':
round(
dic.get(f'ord_sale_amount_{pro_sec_type}', 0) +
total_dic.get(f'total_ord_sale_amount_{pro_sec_type}',
0), 2),
f'total_ord_salenum_{pro_sec_type}':
round(
dic.get(f'ord_salenum_{pro_sec_type}', 0) +
total_dic.get(f'total_ord_salenum_{pro_sec_type}', 0),
2)
})
total_dic['total_ord_sale_amount_all'] = sum([
v for k, v in total_dic.items()
if k.startswith('total_ord_sale_amount_') and v
])
total_dic['total_ord_salenum_all'] = sum([
v for k, v in total_dic.items()
if k.startswith('total_ord_salenum_') and v
])
for pro_sec_type in pro_sec_types:
pro_sec_type = pro_sec_type.replace(' ', '_')
total_dic[f'total_ord_sale_amount_{pro_sec_type}_rate'] = round(total_dic[
f'total_ord_sale_amount_{pro_sec_type}'] / \
total_dic['total_ord_sale_amount_all'], 4)
return ok(data={'data': data, **total_dic})
def get(self):
start_time = get_argument("start_time", default='2019-9-01')
end_time = get_argument("end_time", default='2019-9-22')
city = get_argument("city", default="Japan")
city_code = {
"Japan": {"ord_sitecode": "JP", "db_field": "all_feejp"}, # 日本
"Canada": {"ord_sitecode": "CAN", "db_field": "all_feecanada"}, # 加拿大
"European": {"ord_sitecode": "EU", "db_field": "all_feeeu"}, # 欧洲
"India": {"ord_sitecode": "INEI", "db_field": "all_feeeu"}, # 印度
"US": {"ord_sitecode": "US", "db_field": "all_feeus"}, # 美国
}
table = db.session.query(SalesM, ProductM).join(SalesM, SalesM.pro_fname == ProductM.bsname)
results = table.filter(
SalesM.ord_pay_time.between(start_time, end_time),
SalesM.ord_sitecode == city_code[city].get("ord_sitecode"))
tmp_list = []
tmp_dic = {
}
sorts_list = ["ord_salenum", "ord_maoli", "ord_sale_amount", "ord_sale_price", "ord_platfee", "ord_factoryfee",
"ord_fobfee"]
for i in sorts_list:
tmp_dic[i] = 0
for sales_table, product_table in results:
obj_dict = obj_to_dict(sales_table, keys=[], display=False)
obj_dict.update({
'image': product_table.pro_image_path,
})
store_code = obj_dict.get('source_code', '')
if store_code not in tmp_list:
tmp_list.append(store_code)
tmp_dic[store_code] = {
"model": [], # 执享型号 and 乐歌型号
"model_dic": {},
}
model_key = "{}_{}".format(obj_dict.get('pro_fname'), obj_dict.get('pro_bsname'))
if model_key not in tmp_dic[store_code]['model']:
tmp_dic[store_code]['model'].append(model_key)
tmp_dic[store_code]["model_dic"] = {
model_key: {"image": ''}
}
for i in ["ord_salenum", "ord_sale_amount", "ord_sale_price", "ord_platfee"]:
tmp_dic[store_code]["model_dic"][model_key][i] = 0
for sort in sorts_list:
try:
tmp_dic[sort] += float(obj_dict.get(sort)) if obj_dict.get(sort) else 0
except Exception as e:
pass
for sales in ["ord_salenum", "ord_sale_amount", "ord_sale_price", "ord_platfee"]:
try:
print(obj_dict.get(sales))
tmp_dic[store_code]["model_dic"][model_key][sales] += float(obj_dict.get(sales)) if obj_dict.get(
sales) else 0
except Exception as e:
pass
if not tmp_dic[store_code]["model_dic"][model_key]['image']:
tmp_dic[store_code]["model_dic"][model_key]['image'] = obj_dict.get("image")
res_list = []
shop_name_list = []
num = 0
if not tmp_dic['ord_sale_amount']:
return ok({"title": '', "data": res_list})
table_txt = "{}-{}{}销售{}美金, 毛利率{}%. FOB金额{}美金,, FOB毛利{}元, FOB毛利占比{}%."
table_txt = table_txt.format(start_time.replace('-', '/'), end_time.replace('-', '/'), city,
round(tmp_dic['ord_sale_amount'], 2),
round((tmp_dic['ord_maoli'] / tmp_dic['ord_sale_amount']) * 100, 2),
round(tmp_dic['ord_fobfee'], 2),
self.fob_maoli(tmp_dic['ord_fobfee'], tmp_dic['ord_factoryfee']),
round(tmp_dic['ord_factoryfee'] / tmp_dic['ord_sale_amount'] * 100, 2))
for shop in tmp_dic:
if shop in sorts_list:
continue
shop_name = shop
if shop_name in shop_name_list:
shop_name = ''
for mede in tmp_dic[shop]["model_dic"]:
num += 1
arge = {
"id": num,
"shop_name": shop_name,
"fname": mede.split('_')[0],
"bsname": mede.split('_')[1],
"image": tmp_dic[shop]["model_dic"][mede]["image"],
"sale_num": int(tmp_dic[shop]["model_dic"][mede]["ord_salenum"]),
"sale_amount": round(tmp_dic[shop]["model_dic"][mede]["ord_sale_amount"], 2),
"sale_platfee": round(tmp_dic[shop]["model_dic"][mede]["ord_platfee"], 2),
}
arge.update({
"sale_average_plaftee": round(arge.get("sale_amount") / arge.get("sale_num"), 2)
})
res_list.append(arge)
return ok({"title": table_txt, "data": res_list})
def get(self):
start_time = get_argument('start_time', default='2019-01-15')
end_time = get_argument('end_time', default='2019-02-16')
source_code = get_argument('source_code')
ord_sitecode = get_argument('ord_sitecode')
pro_sec_type = get_argument('pro_sec_type')
pr = pd.period_range(start=start_time, end=end_time, freq='M')
prTupes = tuple([(period.month, period.year) for period in pr])
data = []
tmp = []
month_date = []
for prTupe in prTupes:
date_time = str(prTupe[1]) + '-' + str(prTupe[0])
if prTupe[0] < 10:
date_time = str(prTupe[1]) + '-0' + str(prTupe[0])
month_date.append(date_time)
user = SalesAnalysisReportModel.query.filter(SalesAnalysisReportModel.yyyymm.in_(month_date))
if source_code:
user = user.filter(SalesAnalysisReportModel.source_code == source_code)
if ord_sitecode:
user = user.filter(SalesAnalysisReportModel.ord_sitecode == ord_sitecode)
if pro_sec_type:
user = user.filter(SalesAnalysisReportModel.pro_sec_type == pro_sec_type)
user = user.all()
total_dic = {}
count_dic = {}
for item in user:
arg = obj_to_dict(item, [''], display=False, format_time='%Y-%m-%d')
date_month = arg['yyyymm']
ord_sale_amount = arg['ord_sale_amount']
args = {
'ord_salenum': arg['ord_salenum'], # 销量
'ord_sale_amount': ord_sale_amount, # 销售额
'ord_platfee': arg['ord_platfee'], # 平台费
'ord_expfee': arg['ord_expfee'], # 运费
'ord_factoryfee': arg['ord_factoryfee'], # 出厂额
'ord_fobfee': arg['ord_fobfee'], # FOB金额
'ord_costfee': arg['ord_costfee'], # 成本
'ord_maoli': arg['ord_maoli'], # 毛利
'ord_volumefee': arg['ord_volumefee'],
'ord_pgrossfee': arg['ord_pgrossfee'],
'pro_materialrate': arg['pro_materialrate'], # 材料率
'count': 1
}
for k, v in args.items():
if not args[k]:
args[k] = 0
if date_month not in tmp:
tmp.append(date_month)
data.append(args)
else:
data[tmp.index(date_month)] = dict(Counter(args) + Counter(data[tmp.index(date_month)]))
if total_dic:
total_dic = dict(Counter(args) + Counter(total_dic))
else:
total_dic.update(args)
if not data:
return ok('没有数据')
for tmp_dic in data:
tmp_dic.update({
'date_month': tmp.pop(0),
'average_price': '%.2f' % (tmp_dic['ord_sale_amount'] / tmp_dic['ord_salenum']),
'ord_huanhui': '%.2f' % (tmp_dic['ord_factoryfee'] / tmp_dic['ord_fobfee']),
'ord_costfee_rate': '%.4f' % (tmp_dic['ord_costfee'] / tmp_dic['ord_sale_amount']), # 成本率
'ord_expfee_rate': '%.4f' % (tmp_dic['ord_expfee'] / tmp_dic['ord_sale_amount']), # 物流率
'ord_maoli_rate': '%.4f' % (tmp_dic['ord_maoli'] / tmp_dic['ord_sale_amount']),
'pro_materialrate_rate': '%.4f' % (tmp_dic['pro_materialrate'] / tmp_dic['count']), # ???
'ord_volumefee': round(tmp_dic['ord_volumefee'] / tmp_dic['count'], 4), # ???
'ord_pgrossfee': round(tmp_dic['ord_pgrossfee'] / tmp_dic['count'], 4), # ???
'ord_costfee': round(tmp_dic['ord_costfee'], 2),
'ord_expfee': round(tmp_dic['ord_expfee'], 2),
'ord_factoryfee': round(tmp_dic['ord_factoryfee'], 2),
'ord_fobfee': round(tmp_dic['ord_fobfee'], 2),
'ord_platfee': round(tmp_dic['ord_platfee'], 2),
'ord_sale_amount': round(tmp_dic['ord_sale_amount'], 2),
'pro_materialrate': round(tmp_dic['pro_materialrate'], 2),
'ord_maoli': round(tmp_dic['ord_maoli'], 2),
})
total_dic.update({
'average_price': '%.2f' % (total_dic['ord_sale_amount'] / total_dic['ord_salenum']),
'ord_huanhui': '%.2f' % (total_dic['ord_factoryfee'] / total_dic['ord_fobfee']),
'ord_costfee_rate': '%.4f' % (total_dic['ord_costfee'] / total_dic['ord_sale_amount']), # 成本率
'ord_expfee_rate': '%.4f' % (total_dic['ord_expfee'] / total_dic['ord_sale_amount']), # 物流率
'ord_maoli_rate': '%.4f' % (total_dic['ord_maoli'] / total_dic['ord_sale_amount']),
'pro_materialrate': round(total_dic['pro_materialrate'] / len(user), 4), # ???
'ord_costfee': round(total_dic['ord_costfee'], 2),
'ord_maoli': round(total_dic['ord_maoli'], 2),
'ord_expfee': round(total_dic['ord_expfee'], 2),
'ord_factoryfee': round(total_dic['ord_factoryfee'], 2),
'ord_fobfee': round(total_dic['ord_fobfee'], 2),
'ord_platfee': round(total_dic['ord_platfee'], 2),
'ord_sale_amount': round(total_dic['ord_sale_amount'], 2),
'ord_volumefee': None,
'ord_pgrossfee': None
})
new_total_dic = {}
for k, v in total_dic.items():
new_total_dic.update({f'total_{k}': v})
return ok(data={'data': data, **new_total_dic})
def get(self):
today_real = datetime.date.today()
today = datetime.date.today() + datetime.timedelta(days=1)
last_month_day = today - datetime.timedelta(days=30)
start_time = last_month_day.strftime('%Y-%m-%d')
end_time = today.strftime('%Y-%m-%d')
user = SalesAnalysisReportModel.query
if all([start_time, end_time]):
user = user.filter(
SalesAnalysisReportModel.ord_pay_time.between(
start_time, end_time))
source_list = [
'RMA', 'sponsor', 'FBA-RMA', 'EMPTY PACKAGE', 'PRODUCT REVIEW',
'StockTransfer', 'FBA', 'SEARS-FBS', 'B2B', 'SampleOrder',
'ODM SAMPLE', 'Free-Trial-Riser', 'Synnex', 'OfflineCA',
'OfflineTN'
]
user = user.filter(
SalesAnalysisReportModel.source_code.notin_(source_list)).all()
data = []
tmp = []
temp_dict = {'month_ord_sale_amount': 0, 'month_ord_salenum': 0}
for item in user:
arg = obj_to_dict(item, [''],
display=False,
format_time='%Y-%m-%d')
pay_date = arg['ord_pay_time']
ord_sale_amount = arg['ord_sale_amount']
args = {
'pay_date': pay_date,
'ord_sale_amount': ord_sale_amount,
'ord_salenum': arg['ord_salenum'],
'count': 1
}
if pay_date not in tmp:
tmp.append(pay_date)
data.append(args)
else:
data[tmp.index(pay_date)].update({
'ord_sale_amount':
round(
args['ord_sale_amount'] +
data[tmp.index(pay_date)]['ord_sale_amount'], 2),
'ord_salenum':
round(
args['ord_salenum'] +
data[tmp.index(pay_date)]['ord_salenum'], 2),
'count':
args['count'] + data[tmp.index(pay_date)]['count'],
})
for dic in data:
temp_dict = {
'month_ord_sale_amount':
round(
temp_dict['month_ord_sale_amount'] +
dic['ord_sale_amount'], 2),
'month_ord_salenum':
temp_dict['month_ord_salenum'] + dic['ord_salenum'],
}
if today_real.strftime('%Y-%m-%d') in tmp:
temp_dict.update({
'month_count':
len(user),
'today_ord_sale_amount':
data[tmp.index(
today_real.strftime('%Y-%m-%d'))]['ord_sale_amount'],
'today_ord_salenum':
data[tmp.index(
today_real.strftime('%Y-%m-%d'))]['ord_salenum'],
'today':
today_real.strftime('%Y-%m-%d')
})
else:
temp_dict.update({
'month_count': len(user),
'today_ord_sale_amount': 0,
'today_ord_salenum': 0,
'today': today_real.strftime('%Y-%m-%d')
})
count = []
ord_sale_amount = []
ord_salenum = []
pay_date = []
for i_dic in data:
count.append(i_dic['count'])
ord_sale_amount.append(i_dic['ord_sale_amount'])
ord_salenum.append(i_dic['ord_salenum'])
pay_date.append(i_dic['pay_date'])
data = {
'count': count,
'ord_sale_amount': ord_sale_amount,
'ord_salenum': ord_salenum,
'pay_date': pay_date
}
temp_list = [temp_dict]
return ok(data={'line': data, 'table': temp_list})
def get(self):
start_time = get_argument('start_time', required=True)
end_time = get_argument('end_time', required=True)
start_time_add = (datetime.datetime.strptime(start_time, '%Y-%m-%d') +
datetime.timedelta(days=1)).strftime('%Y-%m-%d')
end_time_add = (datetime.datetime.strptime(end_time, '%Y-%m-%d') +
datetime.timedelta(days=1)).strftime('%Y-%m-%d')
start_user = SalesAnalysisReportModel.query.filter(
SalesAnalysisReportModel.ord_pay_time >= start_time,
SalesAnalysisReportModel.ord_pay_time <= start_time_add).all()
end_user = SalesAnalysisReportModel.query.filter(
SalesAnalysisReportModel.ord_pay_time >= end_time,
SalesAnalysisReportModel.ord_pay_time <= end_time_add).all()
start_data, start_source = get_salenum_sale_amount(start_user)
end_data, end_source = get_salenum_sale_amount(end_user)
total_dic = {
'total_start_ord_sale_amount': 0,
'total_start_ord_salenum': 0,
'total_end_ord_sale_amount': 0,
'total_end_ord_salenum': 0
}
for source in start_source:
if source in end_source:
start_data[start_source.index(source)].update({
'end_ord_sale_amount':
end_data[end_source.index(source)]['ord_sale_amount'],
'end_ord_salenum':
end_data[end_source.index(source)]['ord_salenum'],
'compare_ord_sale_amount':
end_data[end_source.index(source)]['ord_sale_amount'] -
start_data[start_source.index(source)]['ord_sale_amount'],
'compare_ord_salenum':
end_data[end_source.index(source)]['ord_salenum'] -
start_data[start_source.index(source)]['ord_salenum'],
})
total_dic.update({
'total_start_ord_sale_amount':
total_dic['total_start_ord_sale_amount'] +
start_data[start_source.index(source)]['ord_sale_amount'],
'total_start_ord_salenum':
total_dic['total_start_ord_salenum'] +
start_data[start_source.index(source)]['ord_salenum'],
'total_end_ord_sale_amount':
total_dic['total_end_ord_sale_amount'] +
end_data[end_source.index(source)]['ord_sale_amount'],
'total_end_ord_salenum':
total_dic['total_end_ord_salenum'] +
end_data[end_source.index(source)]['ord_salenum']
})
total_dic.update({
'total_compare_ord_sale_amount':
total_dic['total_end_ord_sale_amount'] -
total_dic['total_start_ord_sale_amount'],
'total_compare_ord_salenum':
total_dic['total_end_ord_salenum'] -
total_dic['total_start_ord_salenum'],
})
return ok(data={'data': start_data, **total_dic})
def get(self):
start_time = get_argument('start_time', default='2020-09-01')
end_time = get_argument('end_time', default='2020-09-20')
if start_time > end_time:
raise ServiceException(ServiceError.INVALID_VALUE)
user = SalesAnalysisReportModel.query
if all([start_time, end_time]):
user = user.filter(
SalesAnalysisReportModel.ord_pay_time.between(
start_time, end_time))
source_code = [
'FlexispotUS', 'FleximountsUS', 'FlexiSpotUK', 'FlexiSpotDE',
'FlexiSpotFR', 'FlexiSpotJP', 'FleximountsJP'
]
user = user.filter(
SalesAnalysisReportModel.source_code.in_(source_code)).all()
data = []
tmp = []
for item in user:
arg = obj_to_dict(item, [''],
display=False,
format_time='%Y-%m-%d')
pay_date = arg['ord_pay_time']
ord_sale_amount = arg['ord_sale_amount']
source = arg['source_code']
args = {
f'pay_date': pay_date,
f'ord_sale_amount_{source}': ord_sale_amount,
f'ord_maoli_{source}': arg['ord_maoli'],
f'ord_salenum': arg['ord_salenum']
}
for k, v in args.items():
if not args[k]:
args[k] = 0
if pay_date not in tmp:
tmp.append(pay_date)
data.append(args)
if f'ord_sale_amount{source}' not in data[tmp.index(pay_date)]:
data[tmp.index(pay_date)].update(args)
else:
data[tmp.index(pay_date)].update({
f'ord_sale_amount_{source}':
round(
args['ord_sale_amount_' + source] +
data[tmp.index(pay_date)]['ord_sale_amount_' + source],
2),
f'ord_salenum_{source}':
args['ord_salenum_' + source] +
data[tmp.index(pay_date)]['ord_salenum_' + source],
f'ord_maoli_{source}':
args['ord_maoli_' + source] +
data[tmp.index(pay_date)]['ord_maoli_' + source],
})
if not data:
return ok(data={'data': []})
for dic in data:
dic['ord_sale_amount_Global'] = sum([
v for k, v in dic.items()
if k.startswith('ord_sale_amount_') and v
])
dic['ord_salenum_Global'] = sum([
v for k, v in dic.items() if k.startswith('ord_salenum_') and v
])
dic['ord_maoli_Global'] = sum([
v for k, v in dic.items() if k.startswith('ord_maoli_') and v
]) / dic['ord_sale_amount_Global'] if dic[
'ord_sale_amount_Global'] else 0
source_code = [
'FlexispotUS', 'FleximountsUS', 'FlexiSpotUK', 'FlexiSpotDE',
'FlexiSpotFR', 'FlexiSpotJP', 'FleximountsJP', 'Global'
]
for dic in data:
for source in source_code:
if f'ord_salenum_{source}' in dic.keys():
dic.update({
f'ord_maoli_rate_{source}':
round(
dic.get(f'ord_maoli_{source}', 0) /
dic[f'ord_sale_amount_{source}']
if dic[f'ord_sale_amount_{source}'] else 0, 4)
})
create_dict = {}
for code in source_code:
line_list = {
'date':
tmp,
code: [{
'keyword': "销售额",
'data': []
}, {
'keyword': "销量",
'data': []
}, {
'keyword': "毛利率",
'data': []
}]
}
create_dict.update(line_list)
for temp_dict in data:
for source in source_code:
create_dict[source][0]['data'].append(
temp_dict.get(f'ord_sale_amount_{source}', 0))
create_dict[source][1]['data'].append(
temp_dict.get(f'ord_salenum_{source}', 0))
create_dict[source][2]['data'].append(
temp_dict.get(f'ord_maoli_rate_{source}', 0))
return ok(create_dict)
def get(self):
start_time = get_argument('start_time', default='2020-09-15')
end_time = get_argument('end_time', default='2020-09-16')
year = re.findall(r'(\d+)', start_time)
year_end = re.findall(r'(\d+)', end_time)
pr = pd.period_range(start=start_time, end=end_time, freq='M')
prTupes = tuple([(period.month, period.year) for period in pr])
difference = len(prTupes)
last_month_start_time = (
(datetime.datetime.strptime(start_time, '%Y-%m-%d') -
relativedelta(months=difference))).strftime('%Y-%m-%d')
last_month_start_end = (
(datetime.datetime.strptime(end_time, '%Y-%m-%d') -
relativedelta(months=difference))).strftime('%Y-%m-%d')
last_year_start_time = str(int(year[0]) -
1) + '-' + year[1] + '-' + year[2]
last_year_end_time = str(int(year_end[0]) -
1) + '-' + year_end[1] + '-' + year_end[2]
this_month_data, tmp_this_month = monthly_sales(start_time, end_time)
last_month_data, tmp_last_month = monthly_sales(
last_month_start_time, last_month_start_end)
last_year_month_data, tmp_last_year = monthly_sales(
last_year_start_time, last_year_end_time)
for tmp in tmp_this_month:
if tmp in tmp_last_month:
this_month_data[tmp_this_month.index(tmp)].update({
'last_month_ord_sale_amount':
last_month_data[tmp_last_month.index(
tmp)]['ord_sale_amount'],
'month_on_month_ratio':
this_month_data[tmp_this_month.index(tmp)]
['ord_sale_amount'] - last_month_data[tmp_last_month.index(
tmp)]['ord_sale_amount'] / last_month_data[
tmp_last_month.index(tmp)]['ord_sale_amount']
})
else:
this_month_data[tmp_this_month.index(tmp)].update({
'last_month_ord_sale_amount':
0,
'month_on_month_ratio':
0
})
if tmp in tmp_last_year:
this_month_data[tmp_this_month.index(tmp)].update({
'last_year_ord_sale_amount':
last_year_month_data[tmp_last_year.index(
tmp)]['ord_sale_amount'],
'year_on_year_basis':
last_year_month_data[tmp_this_month.index(
tmp)]['ord_sale_amount'] -
last_year_month_data[tmp_last_year.index(
tmp)]['ord_sale_amount'] / last_year_month_data[
tmp_last_year.index(tmp)]['ord_sale_amount']
})
else:
this_month_data[tmp_this_month.index(tmp)].update({
'last_year_ord_sale_amount':
0,
'year_on_year_basis':
0
})
total_dic = {}
# 计算客单价、销售占比
for dic in this_month_data:
dic.update({
'solo_price':
dic['ord_sale_amount'] / dic['count'],
'sales_rate':
dic['ord_sale_amount'] / dic.pop('total_ord_sale_amount')
})
if total_dic:
total_dic.update({
'total_count':
total_dic['total_count'] + dic['count'],
'total_ord_salenum':
total_dic['total_ord_salenum'] + dic['ord_salenum'],
'total_ord_sale_amount':
total_dic['total_ord_sale_amount'] +
dic['ord_sale_amount'],
})
else:
total_dic.update({
'total_count':
dic['count'],
'total_ord_salenum':
dic['ord_salenum'],
'total_ord_sale_amount':
dic['ord_sale_amount'],
})
# 总环比和总同比
total_dic.update({
'total_last_year_ord_sale_amount':
last_year_month_data[1]['total_ord_sale_amount'],
'total_year_on_year_basis':
(total_dic['total_ord_sale_amount'] -
last_year_month_data[1]['total_ord_sale_amount']) /
last_year_month_data[1]['total_ord_sale_amount'],
'total_last_month_ord_sale_amount':
last_month_data[1]['total_ord_sale_amount'],
'month_on_month_ratio':
(total_dic['total_ord_sale_amount'] -
last_month_data[1]['total_ord_sale_amount']) /
last_month_data[1]['total_ord_sale_amount'],
'total_true_total_ord_sale_amount':
total_dic['total_ord_sale_amount'],
'total_solo_price':
total_dic['total_ord_sale_amount'] / total_dic['total_count']
})
return ok(data={'data': this_month_data, **total_dic})