171 lines
5.6 KiB
Python
171 lines
5.6 KiB
Python
|
import logging
|
|||
|
|
from datetime import datetime
|
|||
|
|
from utils.sql_utils import SQLUtils
|
|||
|
|
|
|||
|
|
class ReportDAO:
|
|||
|
|
"""报表数据访问对象，处理报表相关的数据库操作"""
|
|||
|
|
|
|||
|
|
def __init__(self):
|
|||
|
|
"""初始化数据访问对象"""
|
|||
|
|
# 不再在初始化时创建数据库连接，而是在需要时创建
|
|||
|
|
pass
|
|||
|
|
|
|||
|
|
def get_production_report(self, start_date, end_date, customer=None, material=None, spec=None):
|
|||
|
|
"""获取生产报表数据
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
start_date: 开始日期，格式为 'YYYY-MM-DD'
|
|||
|
|
end_date: 结束日期，格式为 'YYYY-MM-DD'
|
|||
|
|
customer: 客户名称，用于模糊查询，可选
|
|||
|
|
material: 材质，用于模糊查询，可选
|
|||
|
|
spec: 规格，用于模糊查询，可选
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
try:
|
|||
|
|
# 构建SQL查询
|
|||
|
|
sql = """
|
|||
|
|
SELECT DATE(pack_time) AS 日期
|
|||
|
|
, customerexp AS 客户
|
|||
|
|
, t1.order_id AS 订单号
|
|||
|
|
, COUNT(DISTINCT t1.gc_note) AS '轴数'
|
|||
|
|
, t2.cz AS '材质'
|
|||
|
|
, t2.size AS '规格'
|
|||
|
|
, ROUND(SUM(t1.net_weight), 2) AS '净重'
|
|||
|
|
FROM wsbz_inspection_pack_data t1
|
|||
|
|
LEFT JOIN wsbz_order_info t2 ON t1.order_id = t2.ddmo
|
|||
|
|
WHERE pack_time BETWEEN ? AND ?
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
params = [f"{start_date} 00:00:00", f"{end_date} 23:59:59"]
|
|||
|
|
|
|||
|
|
# 添加可选的筛选条件
|
|||
|
|
if customer:
|
|||
|
|
sql += " AND customerexp LIKE ?"
|
|||
|
|
params.append(f"%{customer}%")
|
|||
|
|
|
|||
|
|
if material:
|
|||
|
|
sql += " AND t2.cz LIKE ?"
|
|||
|
|
params.append(f"%{material}%")
|
|||
|
|
|
|||
|
|
if spec:
|
|||
|
|
sql += " AND t2.size LIKE ?"
|
|||
|
|
params.append(f"%{spec}%")
|
|||
|
|
|
|||
|
|
# 添加分组条件
|
|||
|
|
sql += " GROUP BY DATE(pack_time), t2.cz, t2.size, customerexp"
|
|||
|
|
|
|||
|
|
# 执行查询
|
|||
|
|
with SQLUtils('sqlite', database='db/jtDB.db') as db:
|
|||
|
|
db.cursor.execute(sql, params)
|
|||
|
|
results = db.cursor.fetchall()
|
|||
|
|
|
|||
|
|
# 获取列名
|
|||
|
|
columns = [desc[0] for desc in db.cursor.description]
|
|||
|
|
|
|||
|
|
# 转换为字典列表
|
|||
|
|
data = []
|
|||
|
|
for row in results:
|
|||
|
|
row_dict = {}
|
|||
|
|
for i, col in enumerate(columns):
|
|||
|
|
row_dict[col] = row[i]
|
|||
|
|
data.append(row_dict)
|
|||
|
|
|
|||
|
|
return data
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
logging.error(f"获取生产报表数据失败: {str(e)}")
|
|||
|
|
raise e
|
|||
|
|
|
|||
|
|
def get_daily_report(self, date):
|
|||
|
|
"""获取指定日期的日报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
date: 日期，格式为 'YYYY-MM-DD'
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
return self.get_production_report(date, date)
|
|||
|
|
|
|||
|
|
def get_monthly_report(self, year, month):
|
|||
|
|
"""获取指定月份的月报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
year: 年份，如 2024
|
|||
|
|
month: 月份，如 8
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
# 计算月初和月末
|
|||
|
|
start_date = f"{year}-{month:02d}-01"
|
|||
|
|
|
|||
|
|
# 计算月末日期
|
|||
|
|
if month == 12:
|
|||
|
|
next_year = year + 1
|
|||
|
|
next_month = 1
|
|||
|
|
else:
|
|||
|
|
next_year = year
|
|||
|
|
next_month = month + 1
|
|||
|
|
|
|||
|
|
end_date = f"{next_year}-{next_month:02d}-01"
|
|||
|
|
|
|||
|
|
# 使用日期范围查询
|
|||
|
|
return self.get_production_report(start_date, end_date)
|
|||
|
|
|
|||
|
|
def get_yearly_report(self, year):
|
|||
|
|
"""获取指定年份的年报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
year: 年份，如 2024
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
start_date = f"{year}-01-01"
|
|||
|
|
end_date = f"{year+1}-01-01"
|
|||
|
|
|
|||
|
|
# 使用日期范围查询
|
|||
|
|
return self.get_production_report(start_date, end_date)
|
|||
|
|
|
|||
|
|
def get_customer_report(self, start_date, end_date, customer):
|
|||
|
|
"""获取指定客户的报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
start_date: 开始日期，格式为 'YYYY-MM-DD'
|
|||
|
|
end_date: 结束日期，格式为 'YYYY-MM-DD'
|
|||
|
|
customer: 客户名称
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
return self.get_production_report(start_date, end_date, customer=customer)
|
|||
|
|
|
|||
|
|
def get_material_report(self, start_date, end_date, material):
|
|||
|
|
"""获取指定材质的报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
start_date: 开始日期，格式为 'YYYY-MM-DD'
|
|||
|
|
end_date: 结束日期，格式为 'YYYY-MM-DD'
|
|||
|
|
material: 材质
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
return self.get_production_report(start_date, end_date, material=material)
|
|||
|
|
|
|||
|
|
def get_spec_report(self, start_date, end_date, spec):
|
|||
|
|
"""获取指定规格的报表
|
|||
|
|
|
|||
|
|
Args:
|
|||
|
|
start_date: 开始日期，格式为 'YYYY-MM-DD'
|
|||
|
|
end_date: 结束日期，格式为 'YYYY-MM-DD'
|
|||
|
|
spec: 规格
|
|||
|
|
|
|||
|
|
Returns:
|
|||
|
|
list: 包含报表数据的字典列表
|
|||
|
|
"""
|
|||
|
|
return self.get_production_report(start_date, end_date, spec=spec)
|