def export_member_coupon_order_data_csv(cls, qo):
"""
导出会员-券-订单关联数据
:param qo:
:return:
"""
sql = member_coupon_order_mapper(query_sql.EXPORT, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con).astype(dtype=dtypes.EXPORT)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.columns = const.MemberCouponOrder.DF_RESULT_COLUMNS
now = datetime.datetime.now().strftime('%Y%m%d_%T:%f')
dir_path = current_app.config['TMP_PATH']
filename = const.MemberCouponOrder.CSV_FILE_NAME + now + '.csv'
file_url = current_app.config['FILE_SERVER_URL_PREFIX'] + filename
df.to_csv(dir_path + filename, index=False, encoding='utf_8_sig')
resp = dict(success=True, data=file_url, message="success")
return resp
def get_member_grouping_detail_csv(cls, qo):
"""
导出分组会员详情
:param qo:
:return:
"""
sql = csv_mapper(qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
df.columns = CSV.COLUMNS
now = datetime.datetime.now().strftime('%Y%m%d_%T_%f')
dir_path = current_app.config['TMP_PATH']
filename = CSV.FILE_NAME + now + '.csv'
file_url = current_app.config['FILE_SERVER_URL_PREFIX'] + filename
df.to_csv(dir_path + filename, index=False, encoding='utf_8_sig')
except Exception as e:
current_app.logger.exception(e)
return dict(success=False, message=e)
else:
return dict(success=True, data=file_url, message="success")
def get_store_recruit_amount_report_data(cls, qo):
"""
查询门店招募会员详情
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.all.store.ALL, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
resp = dict(success=True, data=df.to_dict(orient='records'), message="success")
return resp
def get_member_grouping_count(cls, qo):
"""
查询分组总人数
:param qo:
:return:
"""
sql = count_mapper(qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
return dict(success=True, data=df['total'], message="success")
def get_monthly_report_active_data(cls, qo):
"""
月报-有效会员 查询
:param qo:
:return:
"""
sql = mr_active_mapper(query_sql.monthly.MONTHLY_ACTIVE, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
else:
df.sort_values(by=['brand_code', 'channel_type_num'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_daily_report_data(cls, qo):
"""
查询日报
:param qo:
:return:
"""
sql = dr_mapper(query_sql.daily.DAILY, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
else:
df.sort_values(by=['sales_area_num', 'city', 'company_name', 'store_code', 'member_type_num'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_store_total_daily_income_detail_data(cls, qo):
"""
查询门店整体每日收入取数
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.total.store.DAILY, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.sort_values(by=['brand', 'member_type', 'date'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_store_member_monthly_register_proportion_detail_data(cls, qo):
"""
查询门店每月登记率
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.member.register_proportion.store.MONTHLY, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.sort_values(by=['brand', 'zone', 'year_month'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_coupon_denomination_sum(cls, qo):
"""
查询扣券金额
:param qo:
:return:
"""
sql = coupon_denomination_sum_mapper(query_sql.COUPON_DEMONINATION_SUM, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
resp = dict(success=True, data=df.to_dict(orient='records'), message="success")
return resp
def get_member_coupon_order_data(cls, qo):
"""
查询会员-券-订单关联数据
:param qo:
:return:
"""
sql = member_coupon_order_mapper(query_sql.QUERY, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
resp = dict(success=True, data=df.to_dict(orient='records'), message="success")
return resp
def get_cic_static_detail_data(cls, qo):
"""
查询 cic 首页静态数据
:param qo:
:return:
"""
sql = mapper(query_sql.MAIN_PAGE, qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
return dict(success=True,
data=df.to_dict(orient='records'),
message="success")
def get_member_grouping_detail(cls, qo):
"""
查询会员分组详情, 带分页
:param qo:
:return:
"""
sql = mapper(qo)
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
return dict(success=True,
data=df.to_dict(orient='records'),
message="success")
def get_member_mul_dim_monthly_income_detail_data(cls, qo):
"""
查询多维度每月收入趋势
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.member.mul_dim.zone.MONTHLY, qo)
if sql is None:
return dict(success=False, message="区域查询参数错误")
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.sort_values(by=['brand', 'member_newold_type', 'member_level_type', 'year_month'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_total_income_report_data(cls, qo):
"""
查询整体收入分析
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.total.zone.ALL, qo)
if sql is None:
return dict(success=False, message="区域查询参数错误")
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.sort_values(by=['brand', 'member_type'], inplace=True)
return dict(success=True, data=df.to_dict(orient='records'), message="success")
def get_recruit_amount_daily_detail_data(cls, qo):
"""
查询每天招募会员详情
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.all.zone.DAILY, qo)
if sql is None:
return dict(success=False, message="查询区域参数错误")
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
df.sort_values(by=['brand', 'date'], inplace=True)
resp = dict(success=True, data=df.to_dict(orient='records'), message="success")
return resp
def get_member_new_old_amount_report_data(cls, qo):
"""
查询新老会员数
:param qo: restplus.Api.payload
:return: response dict
"""
sql = mapper(query_sql.zone.NEW_OLD, qo)
if sql is None:
return dict(success=False, message="区域查询参数错误")
current_app.logger.info("Execute SQL: " + sql)
con = engine().connect()
try:
df = pd.read_sql_query(sql=sql, con=con)
except (DatabaseError, TypeError) as e:
current_app.logger.exception(e)
return dict(success=False, message="Internal Server Error")
else:
resp_dict = dict(success=True,
data=df.to_dict(orient='records'),
message="success")
return resp_dict