Report Service调试时显示的错误及原因_AX

最新推荐文章于 2025-04-28 16:46:04 发布
最新推荐文章于 2025-04-28 16:46:04 发布
阅读量724 收藏
点赞数
博客内容提及将项目的输出修改为 http://localhost/Reporting Services，涉及项目输出设置的相关信息技术操作。

把项目的输出改为: http://localhost/Reporting Services.
跨境电商业务中使用 AI 大模型分析英文文本，提炼关键词并可视化
AI天才研究院
11-22 582
在全球化的浪潮中，跨境电商已经成为国际贸易的重要组成部分。然而，面对海量的英文文本数据，如何快速准确地提取有价值的信息成为了跨境电商企业面临的一大挑战。本文旨在探讨如何利用 AI 大模型技术，特别是自然语言处理（NLP）领域的最新进展，来解决这一问题。我们将重点关注如何从英文文本中提炼关键词，按照预定义的维度进行归类汇总，并通过可视化技术直观地呈现分析结果。AI 大模型在文本分析中的应用英文文本的预处理技术关键词提取算法多维度归类方法数据可视化技术完整的 Python 实现方案。
微信小程序出现wx.navigateTo页面不跳转问题的解决方法
08-28
主要介绍了微信小程序出现wx.navigateTo页面不跳转问题的解决方法,简单分析了微信小程序出现wx.navigateTo页面不跳转情况的原因及相应的解决方法,需要的朋友可以参考下
Reporting Service 使用方法
04-18
利用SQL Server 2008自带的Reporting Service服务从简单到复杂的教您建报表 我就用这个学习的
使用uni-app开发微信小程序的实现
10-15
主要介绍了使用uni-app开发微信小程序的实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
How to: Enable Remote Errors (Reporting Services Configuration)
IT Tech
04-03 508
You can edit the ConfigurationInfo table in the report server database to set EnableRemoteErrors to True, but if the report server is actively used, you should use script to modify the settings.
如何启用远程错误（Reporting Services 配置）
KXLF 学习笔记
12-26 1545
可以将报表服务器上的服务器属性设置为返回有关出现在远程服务器上的错误条件的其他信息。如果错误消息中包含文本“有关此错误的详细信息，请导航到本地服务器上的报表服务器或启用远程错误”，则可以将 EnableRemoteErrors 属性设置为访问可帮助您解决问题的其他信息。注意： 您可以编辑报表服务器数据库中的 ConfigurationInfo 表，将 EnableRem
AX3000T TTL
03-30
### AX3000T 设备与 TTL 配置或问题的技术分析 AX3000T 是一种高性能网络设备，通常用于无线通信领域。关于其与 TTL（Time To Live）相关的配置和技术细节，可以从以下几个方面展开讨论： #### 1. **TTL 的基本...
大数据领域数据产品的运营管理要点
AI天才研究院
04-28 668
随着大数据技术的快速发展和广泛应用，数据产品已成为企业数字化转型的核心资产。然而，许多企业在数据产品运营管理方面仍面临诸多挑战。本文旨在系统性地阐述大数据领域数据产品运营管理的关键要点，帮助企业和团队建立科学有效的运营管理体系。本文范围涵盖数据产品的全生命周期管理，从概念定义到实际运营，重点关注数据治理、质量管理和价值实现等核心环节。本文首先介绍数据产品的基本概念和特点，然后深入分析运营管理的核心要点，包括数据治理、质量管理、用户需求把握等。接着提供实际案例和工具推荐，最后展望未来发展趋势。数据产品。
from flask import Flask, request, jsonify, send_from_directory from flask_cors import CORS import os import numpy as np import pandas as pd import logging import time import json import traceback from werkzeug.utils import secure_filename from new_algorithm import GasSensorDataAnalyzer, AlgorithmSelector, detect_dataset_type, extract_gas_type, extract_concentration, extract_sensor_type # 配置日志 logging.basicConfig( level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', handlers=[ logging.StreamHandler(), # 输出到控制台 logging.FileHandler('app.log', encoding='utf-8') # 输出到文件 ] ) logger = logging.getLogger(__name__) app = Flask(__name__) app.config['MAX_CONTENT_LENGTH'] = 100 * 1024 * 1024 # 100MB 文件大小限制 # 配置CORS允许Vue前端访问 CORS(app, resources={ r"/*": { "origins": "http://localhost:5177", # 指定Vue前端地址 "methods": ["GET", "POST", "PUT", "DELETE", "OPTIONS"], "allow_headers": ["Content-Type", "Authorization"], "supports_credentials": True } }) # 配置上传文件夹 UPLOAD_FOLDER = 'uploads' if not os.path.exists(UPLOAD_FOLDER): os.makedirs(UPLOAD_FOLDER) app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER # 允许的文件扩展名 ALLOWED_EXTENSIONS = {'csv', 'xlsx', 'xls'} # 全局数据集存储 data_analyzer = GasSensorDataAnalyzer() X_combined = None y_combined = None gas_types = [] concentrations = [] sensor_types = [] last_activity = time.time() algorithm_selector = AlgorithmSelector(use_chinese=True) def allowed_file(filename): """检查文件扩展名是否合法""" return '.' in filename and \ filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS def save_and_extract_file_info(file): """保存文件并提取气体信息""" try: filename = secure_filename(file.filename) file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename) file.save(file_path) logger.info(f"Saved file: {file_path}") # 从文件名提取信息 sensor_type = extract_sensor_type(filename) gas_type = extract_gas_type(filename) concentration = extract_concentration(filename) return file_path, sensor_type, gas_type, concentration except Exception as e: logger.error(f"Error processing file {file.filename}: {str(e)}\n{traceback.format_exc()}") return None, None, None, None @app.route('/') def index(): """健康检查端点""" global last_activity status = { 'status': 'running', 'version': '1.0.0', 'last_activity': time.ctime(last_activity), 'endpoints': { '/upload': 'POST - Upload data files', '/analyze': 'POST - Analyze data', '/reset': 'POST - Reset data', '/columns': 'GET - Get dataset columns', '/status': 'GET - Service status' } } logger.info(f"Status request: {status}") return jsonify(status) @app.route('/status') def status(): """服务状态检查""" global X_combined, last_activity return jsonify({ 'status': 'active', 'timestamp': time.time(), 'dataset_loaded': X_combined is not None, 'dataset_shape': X_combined.shape if X_combined is not None else None, 'num_classes': len(np.unique(y_combined)) if y_combined is not None else 0 }) @app.route('/upload', methods=['POST']) def upload_files(): """处理文件上传""" global X_combined, y_combined, gas_types, concentrations, sensor_types, last_activity logger.info("Received upload request") # 检查是否有文件 if 'files' not in request.files: logger.error("No file part in request") return jsonify({'error': 'No file part'}), 400 files = request.files.getlist('files') if len(files) == 0 or files[0].filename == '': logger.error("No selected files") return jsonify({'error': 'No selected files'}), 400 # 过滤合法文件 valid_files = [f for f in files if allowed_file(f.filename)] if len(valid_files) < 2: logger.error("Need at least two files for analysis") return jsonify({'error': '需要至少两个文件进行分析'}), 400 # 保存文件并提取信息 file_paths = [] extracted_info = [] for file in valid_files: file_path, sensor_type, gas_type, concentration = save_and_extract_file_info(file) if file_path: file_paths.append(file_path) extracted_info.append({ 'sensor_type': sensor_type, 'gas_type': gas_type, 'concentration': concentration, 'filename': file.filename }) else: logger.error(f"Failed to process file: {file.filename}") if len(file_paths) < 2: logger.error("Failed to process enough files") return jsonify({ 'error': '文件处理失败，请检查文件格式', 'processed_files': [info['filename'] for info in extracted_info], 'details': extracted_info }), 500 # 准备数据加载参数 sensor_types = [info['sensor_type'] for info in extracted_info] gas_types = [info['gas_type'] for info in extracted_info] concentrations = [info['concentration'] for info in extracted_info] # 加载数据 try: X_combined, y_combined = data_analyzer.load_multiple_gas_data( file_paths, gas_types, concentrations, sensor_types ) if X_combined is None or len(X_combined) == 0: logger.error("Failed to load data from files") return jsonify({ 'error': '数据加载失败，请检查文件内容', 'file_details': extracted_info }), 500 logger.info(f"Loaded combined data: {len(X_combined)} samples, {X_combined.shape[1]} features, {len(np.unique(y_combined))} classes") # 获取多维度标签信息 label_info = [] for label in np.unique(y_combined): # 查找对应的标签信息 for key, label_id in data_analyzer.multi_dimension_labels.items(): if label_id == label: parts = key.split('_') if len(parts) >= 3: sensor = parts[0] gas = parts[1] conc = parts[2].replace('ppm', '') try: _, name_dict = data_analyzer.get_or_create_multi_dimension_label(sensor, gas, int(conc)) label_info.append({ 'id': int(label), 'sensor': sensor, 'gas': gas, 'concentration': conc, 'name_cn': name_dict['cn'], 'name_en': name_dict['en'] }) except Exception as e: logger.error(f"Error getting label info for {key}: {str(e)}") label_info.append({ 'id': int(label), 'sensor': sensor, 'gas': gas, 'concentration': conc, 'name_cn': f"未知标签 {label}", 'name_en': f"Unknown Label {label}" }) else: logger.warning(f"Invalid key format: {key}") # 更新最后活动时间 last_activity = time.time() # 返回成功响应 response = { 'message': f'成功上传并合并 {len(file_paths)} 个文件', 'sample_count': len(X_combined), 'feature_count': X_combined.shape[1], 'num_classes': len(np.unique(y_combined)), 'gas_types': gas_types, 'concentrations': concentrations, 'sensor_types': sensor_types, 'label_info': label_info } return jsonify(response), 200 except Exception as e: logger.error(f"Error loading data: {str(e)}\n{traceback.format_exc()}") return jsonify({ 'error': f'数据加载错误: {str(e)}', 'traceback': traceback.format_exc().splitlines() }), 500 @app.route('/analyze', methods=['POST']) def analyze_data(): """执行数据分析""" global X_combined, y_combined, algorithm_selector, last_activity logger.info("Received analyze request") # 检查数据是否已加载 if X_combined is None or y_combined is None: logger.error("No dataset available") return jsonify({'error': '没有可用数据，请先上传文件'}), 400 # 获取前端传递的算法参数 try: data = request.get_json() if not data or 'params' not in data: logger.error("Invalid request parameters") return jsonify({'error': '无效请求参数'}), 400 params = data.get('params', {}) # 设置算法参数 for algo_name, algo_params in params.items(): if algo_name in algorithm_selector.algorithms: logger.info(f"Setting parameters for {algo_name}: {algo_params}") algorithm_selector.set_algorithm_params(algo_name, algo_params) except Exception as e: logger.error(f"Error parsing JSON data: {str(e)}\n{traceback.format_exc()}") return jsonify({'error': 'JSON数据解析错误'}), 400 # 训练模型 try: logger.info(f"Starting model training with {len(X_combined)} samples and {len(np.unique(y_combined))} classes") results = algorithm_selector.train_models(X_combined, y_combined) logger.info("Algorithm training completed") except Exception as e: logger.error(f"Error training models: {str(e)}\n{traceback.format_exc()}") return jsonify({'error': f'模型训练错误: {str(e)}'}), 500 # 提取需要返回的结果（关键修复） response_results = {} for algo_name, result in results.items(): # 如果训练出错，记录错误信息 if 'error' in result: response_results[algo_name] = { 'name': result['name'], 'error': result['error'] } else: # 转换分类报告为字符串（如果存在） if 'classification_report' in result: report = result['classification_report'] if isinstance(report, dict): # 将分类报告字典转换为格式化的字符串 report_str = "\n".join([f"{key}: {value}" for key, value in report.items()]) else: report_str = str(report) else: report_str = "无分类报告" # 提取特征重要性（如果存在） feature_importances = None if 'feature_importances' in result and result['feature_importances'] is not None: # 确保特征重要性是字典格式 if isinstance(result['feature_importances'], dict): feature_importances = result['feature_importances'] elif hasattr(result['model'], 'feature_importances_'): # 尝试从模型中提取特征重要性 try: feature_importances = dict(zip( result['model'].feature_names_in_, result['model'].feature_importances_ )) except Exception as e: logger.warning(f"无法提取特征重要性: {str(e)}") feature_importances = None response_results[algo_name] = { 'name': result['name'], 'train_accuracy': result['train_accuracy'], 'test_accuracy': result['test_accuracy'], 'classification_report': report_str, 'feature_importances': feature_importances } # 更新最后活动时间 last_activity = time.time() return jsonify({ 'message': '分析成功完成', 'results': response_results, 'num_classes': len(np.unique(y_combined)), 'sample_count': len(X_combined) }), 200 @app.route('/reset', methods=['POST']) def reset_data(): """重置数据集""" global X_combined, y_combined, gas_types, concentrations, sensor_types, last_activity X_combined = None y_combined = None gas_types = [] concentrations = [] sensor_types = [] last_activity = time.time() logger.info("Data reset") return jsonify({'message': '数据已重置'}), 200 @app.route('/uploads/<filename>', methods=['GET']) def uploaded_file(filename): """访问上传的文件""" return send_from_directory(app.config['UPLOAD_FOLDER'], filename) if __name__ == '__main__': # 开发环境 - 允许Vue前端访问 app.run(host='0.0.0.0', port=5000, debug=True) 这是你上次修改的代码，上次的代码还能够上传数据，这个功能也要保留的同时，能够分析数据
06-22
但是，如果用户在上传后分析时仍然遇到问题，可能是算法选择器（AlgorithmSelector）在训练过程中出现了错误，这需要查看日志（app.log）来排查。我们已经在关键位置记录了错误日志（try-except块中打印traceback）...
import cx_Oracle import pandas as pd from reportlab.lib import colors from reportlab.lib.pagesizes import landscape, letter from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, PageBreak, Spacer from reportlab.lib.units import inch import matplotlib.pyplot as plt from io import BytesIO import numpy as np from sqlalchemy import create_engine from sqlalchemy.exc import SQLAlchemyError def fetch_store_data(store_id): """从数据库获取门店数据""" engine = create_engine('oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata') # 执行四个SQL查询 QUERIES = [ # 整体销售数据 """ ------整体销售数据 select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店, nvl(a1.jshj,0) 本周总销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.zhml,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周综合毛利率,nvl(a1.kds,0) 本周总客单,case when nvl(a1.kds,0)=0 then 0 else round(nvl(a1.jshj,0)/nvl(a1.kds,0),2) end 本周客单价, nvl(a1.dsxse,0) 本周线上销售,nvl(a1.dskds,0) 本周线上客单,nvl(a1.jshj,0)-nvl(a1.dsxse,0) 本周线下销售,nvl(a1.kds,0)-nvl(a1.dskds,0) 本周线下客单, nvl(a1.dtp,0) 本周DTP销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.dtp,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周DTP销售占比, nvl(b1.jshj,0) 本周排除DTP销售,case when nvl(b1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2) else 0 end || '%' 本周排除DTP综合毛利率,nvl(b1.kds,0) 本周排除DTP客单,case when nvl(b1.kds,0)=0 then 0 else round(nvl(b1.jshj,0)/nvl(b1.kds,0),2) end 本周排除DTP客单价, nvl(a1.cyxse,0) 本周中药销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.cyxse,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周中药销售占比, nvl(a1.nf,0) 本周内方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.nf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周内方销售占比, nvl(a1.gf,0) 本周膏方销售,case when nvl(a1.jshj,0)<>0 then round(nvl(a1.gf,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周膏方销售占比, nvl(c1.jshj,0) 本周会员销售,case when nvl(a1.jshj,0)<>0 then round(nvl(c1.jshj,0)*100/nvl(a1.jshj,0),2) else 0 end || '%' 本周会员销售占比, nvl(c1.kds,0) 本周会员客单,nvl(c1.hys,0) 本周会员数,case when nvl(c1.kds,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.kds,0),2) end 本周会员客单价, case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.jshj,0)/nvl(c1.hys,0),2) end 本周会员人均消费,case when nvl(c1.hys,0)=0 then 0 else round(nvl(c1.kds,0)/nvl(c1.hys,0),2) end 本周会员消费频次, nvl(d1.jshj,0) 本周医保会员销售,case when nvl(c1.jshj,0)<>0 then round(nvl(d1.jshj,0)*100/nvl(c1.jshj,0),2) else 0 end || '%' 本周医保会员销售占比, nvl(d1.kds,0) 本周医保会员客单,nvl(d1.hys,0) 本周医保会员数,case when nvl(d1.kds,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.kds,0),2) end 本周医保会员客单价, case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.jshj,0)/nvl(d1.hys,0),2) end 本周医保会员人均消费,case when nvl(d1.hys,0)=0 then 0 else round(nvl(d1.kds,0)/nvl(d1.hys,0),2) end 本周医保会员消费频次, nvl(a2.jshj,0) 上周总销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.zhml,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周综合毛利率,nvl(a2.kds,0) 上周总客单,case when nvl(a2.kds,0)=0 then 0 else round(nvl(a2.jshj,0)/nvl(a2.kds,0),2) end 上周客单价, nvl(a2.dsxse,0) 上周线上销售,nvl(a2.dskds,0) 上周线上客单,nvl(a2.jshj,0)-nvl(a2.dsxse,0) 上周线下销售,nvl(a2.kds,0)-nvl(a2.dskds,0) 上周线下客单, nvl(a2.dtp,0) 上周DTP销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.dtp,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周DTP销售占比, nvl(b2.jshj,0) 上周排除DTP销售,case when nvl(b2.jshj,0)<>0 then round(nvl(b2.ml,0)*100/nvl(b2.jshj,0),2) else 0 end || '%' 上周排除DTP综合毛利率,nvl(b2.kds,0) 上周排除DTP客单,case when nvl(b2.kds,0)=0 then 0 else round(nvl(b2.jshj,0)/nvl(b2.kds,0),2) end 上周排除DTP客单价, nvl(a2.cyxse,0) 上周中药销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.cyxse,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周中药销售占比, nvl(a2.nf,0) 上周内方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.nf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周内方销售占比, nvl(a2.gf,0) 上周膏方销售,case when nvl(a2.jshj,0)<>0 then round(nvl(a2.gf,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周膏方销售占比, nvl(c2.jshj,0) 上周会员销售,case when nvl(a2.jshj,0)<>0 then round(nvl(c2.jshj,0)*100/nvl(a2.jshj,0),2) else 0 end || '%' 上周会员销售占比, nvl(c2.kds,0) 上周会员客单,nvl(c2.hys,0) 上周会员数,case when nvl(c2.kds,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.kds,0),2) end 上周会员客单价, case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.jshj,0)/nvl(c2.hys,0),2) end 上周会员人均消费,case when nvl(c2.hys,0)=0 then 0 else round(nvl(c2.kds,0)/nvl(c2.hys,0),2) end 上周会员消费频次, nvl(d2.jshj,0) 上周医保会员销售,case when nvl(c2.jshj,0)<>0 then round(nvl(d2.jshj,0)*100/nvl(c2.jshj,0),2) else 0 end || '%' 上周医保会员销售占比, nvl(d2.kds,0) 上周医保会员客单,nvl(d2.hys,0) 上周医保会员数,case when nvl(d2.kds,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.kds,0),2) end 上周医保会员客单价, case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.jshj,0)/nvl(d2.hys,0),2) end 上周医保会员人均消费,case when nvl(d2.hys,0)=0 then 0 else round(nvl(d2.kds,0)/nvl(d2.hys,0),2) end 上周医保会员消费频次, round(nvl(a3.jshj,0)/10000 ,2) 今年总销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.zhml,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年综合毛利率,nvl(a3.kds,0) 今年总客单,case when nvl(a3.kds,0)=0 then 0 else round(nvl(a3.jshj,0)/nvl(a3.kds,0),2) end 今年客单价, round(nvl(a3.dsxse,0)/10000 ,2) 今年线上销售,nvl(a3.dskds,0) 今年线上客单,round((nvl(a3.jshj,0)-nvl(a3.dsxse,0))/10000 ,2) 今年线下销售,nvl(a3.kds,0)-nvl(a3.dskds,0) 今年线下客单, round(nvl(a3.dtp,0)/10000 ,2) 今年DTP销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.dtp,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年DTP销售占比, round(nvl(b3.jshj,0)/10000 ,2) 今年排除DTP销售,case when nvl(b3.jshj,0)<>0 then round(nvl(b3.ml,0)*100/nvl(b3.jshj,0),2) else 0 end || '%' 今年排除DTP综合毛利率,nvl(b3.kds,0) 今年排除DTP客单,case when nvl(b3.kds,0)=0 then 0 else round(nvl(b3.jshj,0)/nvl(b3.kds,0),2) end 今年排除DTP客单价, round(nvl(a3.cyxse,0)/10000 ,2) 今年中药销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.cyxse,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年中药销售占比, round(nvl(a3.nf,0)/10000 ,2) 今年内方销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.nf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年内方销售占比, round(nvl(a3.gf,0)/10000 ,2) 今年膏方销售,case when nvl(a3.jshj,0)<>0 then round(nvl(a3.gf,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年膏方销售占比, round(nvl(c3.jshj,0)/10000 ,2) 今年会员销售,case when nvl(a3.jshj,0)<>0 then round(nvl(c3.jshj,0)*100/nvl(a3.jshj,0),2) else 0 end || '%' 今年会员销售占比, nvl(c3.kds,0) 今年会员客单,nvl(c3.hys,0) 今年会员数,case when nvl(c3.kds,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.kds,0),2) end 今年会员客单价, case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.jshj,0)/nvl(c3.hys,0),2) end 今年会员人均消费,case when nvl(c3.hys,0)=0 then 0 else round(nvl(c3.kds,0)/nvl(c3.hys,0),2) end 今年会员消费频次, round(nvl(d3.jshj,0)/10000 ,2) 今年医保会员销售,case when nvl(c3.jshj,0)<>0 then round(nvl(d3.jshj,0)*100/nvl(c3.jshj,0),2) else 0 end || '%' 今年医保会员销售占比, nvl(d3.kds,0) 今年医保会员客单,nvl(d3.hys,0) 今年医保会员数,case when nvl(d3.kds,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.kds,0),2) end 今年医保会员客单价, case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.jshj,0)/nvl(d3.hys,0),2) end 今年医保会员人均消费,case when nvl(d3.hys,0)=0 then 0 else round(nvl(d3.kds,0)/nvl(d3.hys,0),2) end 今年医保会员消费频次, round(nvl(a4.jshj,0)/10000 ,2) 去年总销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.zhml,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年综合毛利率,nvl(a4.kds,0) 去年总客单,case when nvl(a4.kds,0)=0 then 0 else round(nvl(a4.jshj,0)/nvl(a4.kds,0),2) end 去年客单价, round(nvl(a4.dsxse,0)/10000 ,2) 去年线上销售,nvl(a4.dskds,0) 去年线上客单,round((nvl(a4.jshj,0)-nvl(a4.dsxse,0))/10000 ,2) 去年线下销售,nvl(a4.kds,0)-nvl(a4.dskds,0) 去年线下客单, round(nvl(a4.dtp,0)/10000 ,2) 去年DTP销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.dtp,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年DTP销售占比, round(nvl(b4.jshj,0)/10000 ,2) 去年排除DTP销售,case when nvl(b4.jshj,0)<>0 then round(nvl(b4.ml,0)*100/nvl(b4.jshj,0),2) else 0 end || '%' 去年排除DTP综合毛利率,nvl(b4.kds,0) 去年排除DTP客单,case when nvl(b4.kds,0)=0 then 0 else round(nvl(b4.jshj,0)/nvl(b4.kds,0),2) end 去年排除DTP客单价, round(nvl(a4.cyxse,0)/10000 ,2) 去年中药销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.cyxse,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年中药销售占比, round(nvl(a4.nf,0)/10000 ,2) 去年内方销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.nf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年内方销售占比, round(nvl(a4.gf,0)/10000 ,2) 去年膏方销售,case when nvl(a4.jshj,0)<>0 then round(nvl(a4.gf,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年膏方销售占比, round(nvl(c4.jshj,0)/10000 ,2) 去年会员销售,case when nvl(a4.jshj,0)<>0 then round(nvl(c4.jshj,0)*100/nvl(a4.jshj,0),2) else 0 end || '%' 去年会员销售占比, nvl(c4.kds,0) 去年会员客单,nvl(c4.hys,0) 去年会员数,case when nvl(c4.kds,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.kds,0),2) end 去年会员客单价, case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.jshj,0)/nvl(c4.hys,0),2) end 去年会员人均消费,case when nvl(c4.hys,0)=0 then 0 else round(nvl(c4.kds,0)/nvl(c4.hys,0),2) end 去年会员消费频次, round(nvl(d4.jshj,0)/10000 ,2) 去年医保会员销售,case when nvl(c4.jshj,0)<>0 then round(nvl(d4.jshj,0)*100/nvl(c4.jshj,0),2) else 0 end || '%' 去年医保会员销售占比, nvl(d4.kds,0) 去年医保会员客单,nvl(d4.hys,0) 去年医保会员数,case when nvl(d4.kds,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.kds,0),2) end 去年医保会员客单价, case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.jshj,0)/nvl(d4.hys,0),2) end 去年医保会员人均消费,case when nvl(d4.hys,0)=0 then 0 else round(nvl(d4.kds,0)/nvl(d4.hys,0),2) end 去年医保会员消费频次 from (select * from gl_custom where tjbh='03711033')e left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' group by a.subbh)a1 on a1.subbh= e.tjbh----整体 left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' group by a.subbh)a2 on a2.subbh= e.tjbh----整体 left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml from c_zkmdxshzb a where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' group by a.subbh)a3 on a3.subbh= e.tjbh----整体 left join (select a.subbh,sum(a.jshj) jshj,sum(a.zhml) zhml,sum(a.kds) kds,sum(a.dsxse) dsxse,sum(a.dskds) dskds,sum(a.cyxse) cyxse,sum(a.nfxs) nf,sum(nvl(a.gfxs,0)+nvl(a.spgf,0)+nvl(a.gfxsa,0)) gf,sum(a.dtp) dtp,sum(a.dtpzhml) dtpzhml from c_zkmdxshzb a where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' group by a.subbh)a4 on a4.subbh= e.tjbh----整体 left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)b1 on b1.subbh=e.tjbh ---非dtp left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)b2 on b2.subbh=e.tjbh ---非dtp left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)b3 on b3.subbh=e.tjbh ---非dtp left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(b.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) b on a.subbh=b.subbh and a.hedgehh=b.hh and a.ph1=b.ph and a.pcdh=b.dh left join yw_kck d on a.hh=d.hh left join (select * from spjxlbz where lbz='1' ) e on e.hh=d.hh where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(e.sx,' ') <> 'D' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)b4 on b4.subbh=e.tjbh ---非dtp left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)c1 on c1.subbh=e.tjbh ---会员 left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)c2 on c2.subbh=e.tjbh ---会员 left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)c3 on c3.subbh=e.tjbh ---会员 left join (select a.subbh,sum(a.jshj) jshj,count(distinct a.yhkh) hys,count(distinct a.lsh) kds from subfhd a where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' 'and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)c4 on c4.subbh=e.tjbh ---会员 left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname like '%医保%')b on b.lsh=a.lsh where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)d1 on d1.subbh=e.tjbh ---医保会员 left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like '%医保%')b on b.lsh=a.lsh where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)d2 on d2.subbh=e.tjbh ---医保会员 left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')b on b.lsh=a.lsh where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)d3 on d3.subbh=e.tjbh ---医保会员 left join (select a.subbh,count(distinct a.yhkh) hys,count(distinct a.lsh) kds,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')b on b.lsh=a.lsh where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh)d4 on d4.subbh=e.tjbh ---医保会员 """, # 医保付款数据 """ -----二、医保付款数据 select e.c_mdfq 大区,e.c_dq 地市,e.c_mdfq1 地区,e.tjbh 编码,e.mc 门店, fk.fkfsname 付款方式,nvl(a1.je,0) 本周付款金额,nvl(a2.je,0) 上周付款金额,nvl(a3.je,0) 今年付款金额,nvl(a4.je,0) 去年付款金额 from (select * from gl_custom where tjbh='03711033')e cross join (select distinct a.fkfsname from subdfkfs a where a.fkfsname like '%医保%'and a.subbh ='03711033' and ((a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 ) or (a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1))) ) fk left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a1 on a1.subbh= e.tjbh and a1.fkfsname = fk.fkfsname left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a2 on a2.subbh= e.tjbh and a2.fkfsname = fk.fkfsname left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a3 on a3.subbh= e.tjbh and a3.fkfsname = fk.fkfsname left join (select a.subbh,a.fkfsname ,sum(a.fkje) je from subdfkfs a where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.fkfsname like '%医保%' and a.subbh ='03711033' group by a.subbh,a.fkfsname)a4 on a4.subbh= e.tjbh and a4.fkfsname = fk.fkfsname """, # 品类数据 """ -------品类数据 select a.c_mdfq 大区,a.c_dq 地市,a.c_mdfq1 地区,a.tjbh 编码,a.mc 门店,a.name 类别, nvl(b.jshj,0) 本周销售,nvl(b.jshj,0)-nvl(c.jshj,0) 周销售环比,nvl(b.kds,0) 本周客流,nvl(b.kds,0)-nvl(c.kds,0) 周客流环比, case when nvl(b.jshj,0)<>0 and nvl(c.jshj,0)<>0 then round(nvl(b.ml,0)*100/nvl(b.jshj,0),2)- round(nvl(c.ml,0)*100/nvl(c.jshj,0),2) else 0 end || '%' 周毛利率环比, nvl(d1.jshj,0)-nvl(d2.jshj,0) 医保会员销售周销售环比,nvl(d3.jshj,0)-nvl(d4.jshj,0) 非医保会员周销售环比,nvl(d5.jshj,0)-nvl(d6.jshj,0) 非会员周销售环比, nvl(b1.jshj,0) 今年销售,nvl(b1.jshj,0)-nvl(c1.jshj,0) 年销售同比,nvl(b1.kds,0) 今年客流,nvl(b1.kds,0)-nvl(c1.kds,0) 年客流同比, case when nvl(b1.jshj,0)<>0 and nvl(c1.jshj,0)<>0 then round(nvl(b1.ml,0)*100/nvl(b1.jshj,0),2)- round(nvl(c1.ml,0)*100/nvl(c1.jshj,0),2) else 0 end || '%' 年毛利率同比, nvl(e1.jshj,0)-nvl(e2.jshj,0) 医保会员销售年销售同比,nvl(e3.jshj,0)-nvl(e4.jshj,0) 非医保会员年销售同比,nvl(e5.jshj,0)-nvl(e6.jshj,0) 非会员年销售同比 from (select a.c_mdfq,a.c_dq,a.c_mdfq1,a.tjbh,a.mc,b.name from gl_custom a cross join (select distinct name from dataitem where dataitemtypeguid='313') b where a.tjbh='03711033') a left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(c.name,'未分类')) b on a.tjbh=b.subbh and a.name=b.name left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(c.name,'未分类')) c on a.tjbh=c.subbh and a.name=c.name left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(c.name,'未分类')) b1 on a.tjbh=b1.subbh and a.name=b1.name left join (select a.subbh,nvl(c.name,'未分类') name,sum(a.jshj) jshj,count(distinct a.lsh) kds,sum(a.je-a.sl*nvl(d.dyfjj,0)) ml from subfhd a left join (select subbh,hh,ph,dh,jhdj,c_dyfjj,(case when c_dyfjj is not null then c_dyfjj else jhdj end) dyfjj from subphk) d on a.subbh=d.subbh and a.hedgehh=d.hh and a.ph1=d.ph and a.pcdh=d.dh left join yw_kck b on a.hedgehh=b.hh left join (select * from dataitem where dataitemtypeguid='313') c on b.fzflsx2=c.code where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(c.name,'未分类')) c1 on a.tjbh=c1.subbh and a.name=c1.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d1 on a.tjbh=d1.subbh and a.name=d1.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d2 on a.tjbh=d2.subbh and a.name=d2.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and fkfsname not like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d3 on a.tjbh=d3.subbh and a.name=d3.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and fkfsname not like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d4 on a.tjbh=d4.subbh and a.name=d4.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d5 on a.tjbh=d5.subbh and a.name=d5.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))d6 on a.tjbh=d6.subbh and a.name=d6.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e1 on a.tjbh=e1.subbh and a.name=e1.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e2 on a.tjbh=e2.subbh and a.name=e2.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and fkfsname not like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e3 on a.tjbh=e3.subbh and a.name=e3.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a inner join (select distinct xhlsh lsh from subdfkfs where kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and fkfsname not like '%医保%')d on d.lsh=a.lsh left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.subbh ='03711033' and nvl(a.yhkh,' ')<>' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e4 on a.tjbh=e4.subbh and a.name=e4.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e5 on a.tjbh=e5.subbh and a.name=e5.name left join (select a.subbh,nvl(e.name,'未分类') name,sum(a.jshj) jshj from subfhd a left join yw_kck b on a.hh=b.hh left join (select code,name from dataitem where dataitemtypeguid='313') e on b.fzflsx2=e.code where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and a.subbh ='03711033' and nvl(a.yhkh,' ')=' ' and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' group by a.subbh,nvl(e.name,'未分类'))e6 on a.tjbh=e6.subbh and a.name=e6.name order by 周销售环比 """, # 销售能力 """ ------销售能力 with subfhdls as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh left join gl_custom d on a.subbh=d.tjbh where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') AND TRUNC(SYSDATE-1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000' and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033' and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ), a1 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls a group by a.subbh) a left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from subfhdls a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls group by subbh) c on c.subbh=a.subbh left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls a group by a.subbh,a.lsh) group by subbh) d on d.subbh=a.subbh), subfhdls1 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh left join gl_custom d on a.subbh=d.tjbh where a.kdrq BETWEEN TRUNC(SYSDATE, 'IW') - 7 AND TRUNC(SYSDATE) - 1 - 7 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000' and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033' and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ), a2 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from subfhdls1 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh) group by subbh) d on d.subbh=a.subbh), subfhdls2 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh left join gl_custom d on a.subbh=d.tjbh where a.kdrq BETWEEN TRUNC(SYSDATE, 'YEAR') AND TRUNC(SYSDATE- 1) and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000' and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033' and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ), a3 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from subfhdls2 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh) group by subbh) d on d.subbh=a.subbh), subfhdls3 as (select a.subbh,a.lsh,a.hedgehh,a.jshj,a.xsr,a.sl from subfhd a left join yw_kck b on b.hh=a.hedgehh left join gl_custom d on a.subbh=d.tjbh where a.kdrq BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AND ADD_MONTHS(TRUNC(SYSDATE), -12) - 1 and nvl(a.bz,' ')<>'会员礼品成本调整单' and nvl(a.bz,' ')<>'￥' and nvl(a.bm,' ')<>'积分兑换' and nvl(a.posguid,' ')<>'10000' and b.fzflsx1<>'14'and b.fzflsx2<>'08031' and a.subbh ='03711033' and b.fzflsx2<>'08032' and b.fzflsx2<>'08034' and b.fzflsx2<>'08035' and b.fzflsx2<>'08038' and b.fzflsx2<>'08039' and b.fzflsx2<>'08040' and b.fzflsx2<>'08041' ), a4 as (select a.subbh,nvl(a.jshj,0) jshj,nvl(a.pzs,0) zkds,nvl(b.lsh,0) dpkds,case when nvl(a.pzs,0)<>0 then round(b.lsh/a.pzs,4) else 0 end dpl,c.kps kps,d.kp kpc from (select a.subbh,count(distinct a.lsh) pzs,sum(a.jshj) jshj from subfhdls1 a group by a.subbh) a left join (select subbh,count(distinct lsh) lsh from (select a.subbh,a.lsh,count(a.hedgehh) pzs from subfhdls3 a group by a.subbh,a.lsh having count(a.hedgehh)=1) group by subbh) b on b.subbh=a.subbh left join (select subbh,round(sum(sl)/count(distinct lsh),2) kps from subfhdls1 group by subbh) c on c.subbh=a.subbh left join (select subbh,round(sum(kpc)/count(distinct lsh),2) kp from (select a.subbh,a.lsh,count(distinct a.hedgehh) kpc from subfhdls1 a group by a.subbh,a.lsh) group by subbh) d on d.subbh=a.subbh) select a.subbh 编码,e.mc 门店,a.dpl "本周一单一品率",a.kps "本周客品数",a.kpc "本周客品次",b.dpl "上周一单一品率",b.kps "上周客品数",b.kpc "上周客品次", c.dpl "今年一单一品率",c.kps "今年客品数",c.kpc "今年客品次",d.dpl "去年一单一品率",d.kps "去年客品数",d.kpc "去年客品次" from a1 a left join a2 b on a.subbh=b.subbh left join a3 c on a.subbh=c.subbh left join a4 d on a.subbh=d.subbh left join gl_custom e on a.subbh=e.tjbh """ ] results = [] for query in QUERIES: formatted_query = query.replace("'03711033'", f"'{store_id}'") with engine.connect() as conn: df = pd.read_sql(formatted_query, conn) results.append(df) return results def create_pdf_report(store_id, store_name, data, filename): """创建PDF报告""" doc = SimpleDocTemplate( filename, pagesize=landscape(letter), rightMargin=30, leftMargin=30, topMargin=30, bottomMargin=30 ) styles = getSampleStyleSheet() elements = [] # 标题 title_style = ParagraphStyle( 'Title', parent=styles['Heading1'], alignment=1, spaceAfter=12 ) elements.append(Paragraph(f"{store_name}销售分析报告", title_style)) # 表格1: 整体周销售数据 df1 = data[0] weekly_columns = ['指标', '本周', '本周占比', '上周', '上周占比', '环比'] weekly_data = [ ['总销售', df1['本周总销售'][0], '', df1['上周总销售'][0], '', df1['本周总销售'][0] - df1['上周总销售'][0]], ['综合毛利率', df1['本周综合毛利率'][0], '', df1['上周综合毛利率'][0], '', ''], ['总客单', df1['本周总客单'][0], '', df1['上周总客单'][0], '', df1['本周总客单'][0] - df1['上周总客单'][0]], ['客单价', df1['本周客单价'][0], '', df1['上周客单价'][0], '', ''], ['线上销售', df1['本周线上销售'][0], '', df1['上周线上销售'][0], '', df1['本周线上销售'][0] - df1['上周线上销售'][0]], ['线上客单', df1['本周线上客单'][0], '', df1['上周线上客单'][0], '', ''], ['线下销售', df1['本周线下销售'][0], '', df1['上周线下销售'][0], '', df1['本周线下销售'][0] - df1['上周线下销售'][0]], ['线下客单', df1['本周线下客单'][0], '', df1['上周线下客单'][0], '', ''], ['DTP销售', df1['本周DTP销售'][0], df1['本周DTP销售占比'][0], df1['上周DTP销售'][0], df1['上周DTP销售占比'][0], df1['本周DTP销售'][0] - df1['上周DTP销售'][0]], ['排除DTP销售', df1['本周排除DTP销售'][0], '', df1['上周排除DTP销售'][0], '', df1['本周排除DTP销售'][0] - df1['上周排除DTP销售'][0]], ['排除DTP综合毛利率', df1['本周排除DTP综合毛利率'][0], '', df1['上周排除DTP综合毛利率'][0], '', ''], ['排除DTP客单', df1['本周排除DTP客单'][0], '', df1['上周排除DTP客单'][0], '', df1['本周排除DTP客单'][0] - df1['上周排除DTP客单'][0]], ['排除DTP客单价', df1['本周排除DTP客单价'][0], '', df1['上周排除DTP客单价'][0], '', ''], ['中药销售', df1['本周中药销售'][0], df1['本周中药销售占比'][0], df1['上周中药销售'][0], df1['上周中药销售占比'][0], df1['本周中药销售'][0] - df1['上周中药销售'][0]], ['内方销售', df1['本周内方销售'][0], df1['本周内方销售占比'][0], df1['上周内方销售'][0], df1['上周内方销售占比'][0], df1['本周内方销售'][0] - df1['上周内方销售'][0]], ['膏方销售', df1['本周膏方销售'][0], df1['本周膏方销售占比'][0], df1['上周膏方销售'][0], df1['上周膏方销售占比'][0], df1['本周膏方销售'][0] - df1['上周膏方销售'][0]], ['会员销售', df1['本周会员销售'][0], df1['本周会员销售占比'][0], df1['上周会员销售'][0], df1['上周会员销售占比'][0], df1['本周会员销售'][0] - df1['上周会员销售'][0]], ['会员客单', df1['本周会员客单'][0], '', df1['上周会员客单'][0], '', df1['本周会员客单'][0] - df1['上周会员客单'][0]], ['会员数', df1['本周会员数'][0], '', df1['上周会员数'][0], '', df1['本周会员数'][0] - df1['上周会员数'][0]], ['会员客单价', df1['本周会员客单价'][0], '', df1['上周会员客单价'][0], '', ''], ['会员人均消费', df1['本周会员人均消费'][0], '', df1['上周会员人均消费'][0], '', ''], ['会员消费频次', df1['本周会员消费频次'][0], '', df1['上周会员消费频次'][0], '', ''], ['医保会员销售', df1['本周医保会员销售'][0], df1['本周医保会员销售占比'][0], df1['上周医保会员销售'][0], df1['上周医保会员销售占比'][0], df1['本周医保会员销售'][0] - df1['上周医保会员销售'][0]], ['医保会员客单', df1['本周医保会员客单'][0], '', df1['上周医保会员客单'][0], '', df1['本周医保会员客单'][0] - df1['上周医保会员客单'][0]], ['医保会员数', df1['本周医保会员数'][0], '', df1['上周医保会员数'][0], '', df1['本周医保会员数'][0] - df1['上周医保会员数'][0]], ['医保会员客单价', df1['本周医保会员客单价'][0], '', df1['上周医保会员客单价'][0], '', ''], ['医保会员人均消费', df1['本周医保会员人均消费'][0], '', df1['上周医保会员人均消费'][0], '', ''], ['医保会员消费频次', df1['本周医保会员消费频次'][0], '', df1['上周医保会员消费频次'][0], '', ''], ] # 创建表格1 weekly_table = Table([weekly_columns] + weekly_data) weekly_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4F81BD')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#DCE6F1')), ('GRID', (0, 0), (-1, -1), 1, colors.black), ('VALIGN', (0, 0), (-1, -1), 'MIDDLE') ])) elements.append(Paragraph("<b>表1: 整体周销售数据</b>", styles['Heading2'])) elements.append(weekly_table) elements.append(Spacer(1, 0.2 * inch)) # 表格2: 医保付款周数据 df2 = data[1] payment_columns = ['付款方式', '本周付款金额', '上周付款金额', '环比'] payment_data = [] for _, row in df2.iterrows(): payment_data.append([ row['付款方式'], row['本周付款金额'], row['上周付款金额'], row['本周付款金额'] - row['上周付款金额'] ]) payment_table = Table([payment_columns] + payment_data) payment_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#C0504D')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#F2DCDB')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表2: 医保付款周数据</b>", styles['Heading2'])) elements.append(payment_table) elements.append(Spacer(1, 0.2 * inch)) # 表格3: 品类周数据 df3 = data[2] category_columns = ['类别', '本周销售', '本周占比', '周销售环比', '本周客流', '周客流环比', '医保会员销售周销售环比', '非医保会员周销售环比', '非会员周销售环比'] category_data = [] for _, row in df3.iterrows(): total_sales = df1['本周总销售'][0] category_data.append([ row['类别'], row['本周销售'], f"{row['本周销售'] / total_sales:.2%}" if total_sales > 0 else "0.00%", row['周销售环比'], row['本周客流'], row['周客流环比'], row['医保会员销售周销售环比'], row['非医保会员周销售环比'], row['非会员周销售环比'] ]) category_table = Table([category_columns] + category_data) category_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#9BBB59')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 9), ('BOTTOMPADDING', (0, 0), (-1, 0), 10), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#EBF1DE')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表3: 品类周数据</b>", styles['Heading2'])) elements.append(category_table) elements.append(Spacer(1, 0.2 * inch)) # 表格4: 销售能力环比 df4 = data[3] capability_columns = ['时间', '一单一品率', '客品数', '客品次'] capability_data = [ ['本周', df4['本周一单一品率'][0], df4['本周客品数'][0], df4['本周客品次'][0]], ['上周', df4['上周一单一品率'][0], df4['上周客品数'][0], df4['上周客品次'][0]], ['环比', df4['本周一单一品率'][0] - df4['上周一单一品率'][0], df4['本周客品数'][0] - df4['上周客品数'][0], df4['本周客品次'][0] - df4['上周客品次'][0]] ] capability_table = Table([capability_columns] + capability_data) capability_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#8064A2')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#E5E0EC')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表4: 销售能力环比</b>", styles['Heading2'])) elements.append(capability_table) elements.append(PageBreak()) # 表格5: 整体销售数据（年度） annual_columns = ['指标', '今年', '今年占比', '去年', '去年占比', '同比'] annual_data = [ ['总销售', df1['今年总销售'][0], '', df1['去年总销售'][0], '', df1['今年总销售'][0] - df1['去年总销售'][0]], ['综合毛利率', df1['今年综合毛利率'][0], '', df1['去年综合毛利率'][0], '', ''], ['总客单', df1['今年总客单'][0], '', df1['去年总客单'][0], '', df1['今年总客单'][0] - df1['去年总客单'][0]], # 其他行...（类似表格1的结构） ] annual_table = Table([annual_columns] + annual_data) annual_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#4F81BD')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#DCE6F1')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表5: 整体销售数据（年度）</b>", styles['Heading2'])) elements.append(annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格6: 医保付款数据（年度） payment_annual_columns = ['付款方式', '今年付款金额', '去年付款金额', '同比'] payment_annual_data = [] for _, row in df2.iterrows(): payment_annual_data.append([ row['付款方式'], row['今年付款金额'], row['去年付款金额'], row['今年付款金额'] - row['去年付款金额'] ]) payment_annual_table = Table([payment_annual_columns] + payment_annual_data) payment_annual_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#C0504D')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#F2DCDB')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表6: 医保付款数据（年度）</b>", styles['Heading2'])) elements.append(payment_annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格7: 品类数据（年度） category_annual_columns = ['类别', '今年销售', '今年占比', '年销售同比', '今年客流', '年客流同比', '医保会员销售年销售同比', '非医保会员年销售同比', '非会员年销售同比'] category_annual_data = [] for _, row in df3.iterrows(): total_annual_sales = df1['今年总销售'][0] category_annual_data.append([ row['类别'], row['今年销售'], f"{row['今年销售'] / total_annual_sales:.2%}" if total_annual_sales > 0 else "0.00%", row['年销售同比'], row['今年客流'], row['年客流同比'], row['医保会员销售年销售同比'], row['非医保会员年销售同比'], row['非会员年销售同比'] ]) category_annual_table = Table([category_annual_columns] + category_annual_data) category_annual_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#9BBB59')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 9), ('BOTTOMPADDING', (0, 0), (-1, 0), 10), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#EBF1DE')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表7: 品类数据（年度）</b>", styles['Heading2'])) elements.append(category_annual_table) elements.append(Spacer(1, 0.2 * inch)) # 表格8: 销售能力同比 capability_annual_columns = ['时间', '一单一品率', '客品数', '客品次'] capability_annual_data = [ ['今年', df4['今年一单一品率'][0], df4['今年客品数'][0], df4['今年客品次'][0]], ['去年', df4['去年一单一品率'][0], df4['去年客品数'][0], df4['去年客品次'][0]], ['同比', df4['今年一单一品率'][0] - df4['去年一单一品率'][0], df4['今年客品数'][0] - df4['去年客品数'][0], df4['今年客品次'][0] - df4['去年客品次'][0]] ] capability_annual_table = Table([capability_annual_columns] + capability_annual_data) capability_annual_table.setStyle(TableStyle([ ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#8064A2')), ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke), ('ALIGN', (0, 0), (-1, -1), 'CENTER'), ('FONTSIZE', (0, 0), (-1, 0), 10), ('BOTTOMPADDING', (0, 0), (-1, 0), 12), ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#E5E0EC')), ('GRID', (0, 0), (-1, -1), 1, colors.black) ])) elements.append(Paragraph("<b>表8: 销售能力同比</b>", styles['Heading2'])) elements.append(capability_annual_table) # 分析页 elements.append(PageBreak()) analysis_title = Paragraph("销售数据分析报告", styles['Heading1']) elements.append(analysis_title) # 添加分析内容 analysis_content = [ "1. <b>总体销售趋势</b>: 本周总销售额为¥{:.2f}，环比{:.2%}。今年累计销售额¥{:.2f}，同比{:.2%}。".format( df1['本周总销售'][0], (df1['本周总销售'][0] - df1['上周总销售'][0]) / df1['上周总销售'][0] if df1['上周总销售'][0] > 0 else 0, df1['今年总销售'][0], (df1['今年总销售'][0] - df1['去年总销售'][0]) / df1['去年总销售'][0] if df1['去年总销售'][0] > 0 else 0 ), "2. <b>会员分析</b>: 会员销售占比{}，较上周{}。医保会员贡献率{}，人均消费¥{:.2f}。".format( df1['本周会员销售占比'][0], "上升" if float(df1['本周会员销售占比'][0].strip('%')) > float( df1['上周会员销售占比'][0].strip('%')) else "下降", df1['本周医保会员销售占比'][0], df1['本周医保会员人均消费'][0] ), "3. <b>渠道表现</b>: 线上销售占比{:.2%}，线下销售占比{:.2%}。DTP渠道占比{}，环比{}。".format( df1['本周线上销售'][0] / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0, (df1['本周总销售'][0] - df1['本周线上销售'][0]) / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0, df1['本周DTP销售占比'][0], "上升" if float(df1['本周DTP销售占比'][0].strip('%')) > float( df1['上周DTP销售占比'][0].strip('%')) else "下降" ), "4. <b>品类表现</b>: 销售最好的品类是{}，贡献率{:.2%}。增长最快的品类是{}，环比增长{:.2%}。".format( df3.loc[df3['本周销售'].idxmax()]['类别'], df3['本周销售'].max() / df1['本周总销售'][0] if df1['本周总销售'][0] > 0 else 0, df3.loc[df3['周销售环比'].idxmax()]['类别'], df3['周销售环比'].max() / df3['本周销售'].max() if df3['本周销售'].max() > 0 else 0 ), "5. <b>销售能力</b>: 客品数{}件/单，客品次{}次/单。一单一品率{:.2%}，较上周{}。".format( df4['本周客品数'][0], df4['本周客品次'][0], df4['本周一单一品率'][0], "上升" if df4['本周一单一品率'][0] > df4['上周一单一品率'][0] else "下降" ) ] for content in analysis_content: elements.append(Paragraph(content, styles['Normal'])) elements.append(Spacer(1, 0.1 * inch)) # 生成图表 fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 5)) # 销售渠道占比图 channels = ['线上', '线下', 'DTP', '其他'] sales = [ df1['本周线上销售'][0], df1['本周线下销售'][0], df1['本周DTP销售'][0], df1['本周总销售'][0] - df1['本周线上销售'][0] - df1['本周DTP销售'][0] - df1['本周线下销售'][0] ] ax1.pie(sales, labels=channels, autopct='%1.1f%%', startangle=90) ax1.set_title('销售渠道占比') # 品类销售TOP5 top_categories = df3.nlargest(5, '本周销售') ax2.bar(top_categories['类别'], top_categories['本周销售'], color='skyblue') ax2.set_title('品类销售TOP5') ax2.tick_params(axis='x', rotation=45) plt.tight_layout() chart_buffer = BytesIO() plt.savefig(chart_buffer, format='png', dpi=150) plt.close() # 将图表添加到PDF from reportlab.platypus import Image elements.append(Image(chart_buffer, width=10 * inch, height=5 * inch)) # 生成PDF doc.build(elements) def main(): engine = create_engine('oracle+cx_oracle://admin:xxb20140415@192.168.3.16:1521/?service_name=zzjdata') with engine.connect() as conn: store_df = pd.read_sql("SELECT tjbh, mc FROM gl_custom WHERE tjbh = '03711033'", conn) for _, row in store_df.iterrows(): store_id = row['tjbh'] store_name = row['mc'] print(f"正在处理门店: {store_name} ({store_id})") try: data = fetch_store_data(store_id) filename = f"{store_name}_销售分析报告.pdf" create_pdf_report(store_id, store_name, data, filename) print(f"已生成: {filename}") except Exception as e: print(f"处理门店 {store_name} 时出错: {str(e)}") if __name__ == "__main__": main() E:\Anaconda3-2024.02.1\python.exe E:\Pycharm-2023.3.4\project\pythonProject\venv\门店周数据3.py 正在处理门店: 郑州康复店G (03711033) 处理门店 郑州康复店G 时出错: Wedge sizes 'x' must be non negative values Process finished with exit code 0
最新发布
08-10
这个错误通常出现在绘制饼图时，数据中存在负数。我们需要检查销售渠道占比图中的数据是否非负。 在创建饼图的部分，我们使用了四个数据： sales = [ df1['本周线上销售'][0], df1['本周线下销售'][0], df1['...
快速解决程序运行中突然异常的问题，并找到根源
05-11
程序员通常都会遇到： 程序运行一段时间之后，莫名其妙突然弹一个对话框提示:XXXXXX内存地址无法访问。点 “确定”关闭。 尤其是产品发布之后，出现了这样的问题，可能是用户太多，可能是网络不给力，可能是。 异常了，怎么办？怎么快速解决？怎么快速找到根源？ 今天就告诉大家一个快捷的方法，保证5分钟之内找到引发这个问题的代码。
reportService
08-08
SSRS、 ReportService 微软Report Service
uniapp 微信小程序 uni.navigateTo（{}）页面不跳转
May_zero的博客
03-17 7204
项目结构文件名错误，即page包下.vue文件命名与父级目录名字不一致，无法跳转页面。
uniapp中按钮点击跳转页面失效，纠正错误（亲测可用）
qq_39858177的博客
01-12 6411
在这里插入图片描述](https://img-blog.csdnimg.cn/direct/bb24bf6e7e8945648a069f3dd6adb332.png。在pages.json中把你需要跳转的那个路劲的页面在这里注册一下。如果你的错误和我的差不多 ，那我的方法你应该也可用适用。就是需要当前跳转页面的路劲需要注册，类似备案的意思差不多。加上之后我这边就可以跳转成功了 ，伙伴试试把。不知道伙伴你的错误和我是否一致？
uni-app开发微信小程序真机调试请求超时&请求被拒绝
pshhjj1314的博客
04-11 9636
uni-app开发微信小程序真机调试请求超时&请求被拒绝
unicloud中上传图片
06-26 3423
addImage/index.js: 'use strict'; const db = uniCloud.database() exports.main = async (event, context) => { const collection = db.collection('tableImages') //云数据库里的表名 ，记录上传图片到云存储后返回的fileID const res = await collection.add(event) //event为客户端上传的参数...
小程序开发常见问题
hq123897的博客
12-13 1309
小程序
微信小程序失败服务器请求失败,微信小程序 Request faild 请求后台失败
weixin_36340890的博客
08-03 1091
Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols][HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCH...
深入解析MS SQL Report Service的全面资料
#### Report_Builder_3.0实用手册.pdf - **Report Builder版本说明**：提供了对Report Builder 3.0版本的详细指导，特别关注这个版本中新增的功能和改进点，如与SQL Server 2008 R2的集成。 #### SSRS报表服务笔记...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值