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