"""ساخت داده گزارش مشتریان/فروشنده‌ها و خروجی Excel و PDF — پورت‌شده از core/reports.py دسکتاپ.""" from collections import Counter from sqlalchemy import func from app.extensions import db from app.models.customer import Customer, Purchase from app.models.call import Call from app.models.tag import CustomerTag from app.models.user import User from app.services.jalali import to_jalali_str _FONTS_REGISTERED = False def build_customer_report(cities=None, tag_ids=None, min_amount=None, max_amount=None, date_from=None, date_to=None, sort="amount_desc"): stats = ( db.session.query( Purchase.customer_id.label("customer_id"), func.sum(Purchase.amount).label("total_amount"), func.sum(Purchase.weight).label("total_weight"), func.count(Purchase.id).label("purchase_count"), ) .group_by(Purchase.customer_id) .subquery() ) query = ( db.session.query( Customer, func.coalesce(stats.c.total_amount, 0).label("total_amount"), func.coalesce(stats.c.total_weight, 0).label("total_weight"), func.coalesce(stats.c.purchase_count, 0).label("purchase_count"), ) .outerjoin(stats, stats.c.customer_id == Customer.id) .filter(Customer.is_deleted.is_(False)) ) if cities: query = query.filter(Customer.city.in_(cities)) if tag_ids: tagged_ids = db.session.query(CustomerTag.customer_id).filter(CustomerTag.tag_id.in_(tag_ids)) query = query.filter(Customer.id.in_(tagged_ids)) rows = [] for customer, total_amount, total_weight, purchase_count in query.all(): row = { "customer": customer, "total_amount": float(total_amount or 0), "total_weight": float(total_weight or 0), "purchase_count": purchase_count or 0, } rows.append(row) if date_from and date_to: range_q = ( db.session.query(Purchase.customer_id, func.coalesce(func.sum(Purchase.amount), 0)) .filter(Purchase.purchase_date.between(date_from, date_to)) .group_by(Purchase.customer_id) .all() ) range_map = {cid: float(amt) for cid, amt in range_q} for r in rows: r["range_amount"] = range_map.get(r["customer"].id, 0) else: for r in rows: r["range_amount"] = r["total_amount"] if min_amount is not None: rows = [r for r in rows if r["total_amount"] >= min_amount] if max_amount is not None: rows = [r for r in rows if r["total_amount"] <= max_amount] sort_key_map = { "amount_desc": lambda r: -r["total_amount"], "amount_asc": lambda r: r["total_amount"], "range_asc": lambda r: r["range_amount"], "range_desc": lambda r: -r["range_amount"], "name": lambda r: r["customer"].first_name or "", } rows.sort(key=sort_key_map.get(sort, sort_key_map["amount_desc"])) return rows def build_salesperson_report(salesperson_ids=None, date_from=None, date_to=None, answered=None, sort="date_desc"): query = Call.query.join(Customer, Call.customer_id == Customer.id) if salesperson_ids: query = query.filter(Call.salesperson_id.in_(salesperson_ids)) if date_from: query = query.filter(Call.call_date >= date_from) if date_to: query = query.filter(func.date(Call.call_date) <= date_to) if answered is not None: query = query.filter(Call.answered.is_(answered)) calls = query.order_by(Call.call_date.desc()).all() if sort in ("salesperson_calls_desc", "salesperson_calls_asc"): counts = Counter(c.salesperson_id for c in calls) calls.sort(key=lambda c: counts[c.salesperson_id], reverse=(sort == "salesperson_calls_desc")) return calls def _persian(text): import arabic_reshaper from bidi.algorithm import get_display return get_display(arabic_reshaper.reshape(str(text))) def _register_pdf_fonts(): global _FONTS_REGISTERED if _FONTS_REGISTERED: return from flask import current_app from reportlab.pdfbase import pdfmetrics from reportlab.pdfbase.ttfonts import TTFont fonts_dir = current_app.static_folder + "/fonts" pdfmetrics.registerFont(TTFont("Vazir", f"{fonts_dir}/Vazirmatn-Regular.ttf")) pdfmetrics.registerFont(TTFont("Vazir-Bold", f"{fonts_dir}/Vazirmatn-Bold.ttf")) _FONTS_REGISTERED = True def _build_pdf(buffer, title, headers, data_rows): from reportlab.lib import colors from reportlab.lib.pagesizes import A4, landscape from reportlab.lib.styles import ParagraphStyle from reportlab.lib.units import cm from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer _register_pdf_fonts() doc = SimpleDocTemplate(buffer, pagesize=landscape(A4), topMargin=1.2 * cm, bottomMargin=1.2 * cm, leftMargin=1 * cm, rightMargin=1 * cm) title_style = ParagraphStyle("title", fontName="Vazir-Bold", fontSize=16, alignment=1, textColor=colors.HexColor("#9C7A22")) elements = [Paragraph(_persian(title), title_style), Spacer(1, 14)] header_row = [_persian(h) for h in reversed(headers)] table_data = [header_row] for row_vals in data_rows: table_data.append([_persian(str(v)) for v in reversed(row_vals)]) table = Table(table_data, repeatRows=1) table.setStyle(TableStyle([ ("FONTNAME", (0, 0), (-1, -1), "Vazir"), ("FONTNAME", (0, 0), (-1, 0), "Vazir-Bold"), ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#D4AF37")), ("TEXTCOLOR", (0, 0), (-1, 0), colors.black), ("GRID", (0, 0), (-1, -1), 0.4, colors.grey), ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("VALIGN", (0, 0), (-1, -1), "MIDDLE"), ("FONTSIZE", (0, 0), (-1, -1), 8.5), ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#F7F0DC")]), ("TOPPADDING", (0, 0), (-1, -1), 5), ("BOTTOMPADDING", (0, 0), (-1, -1), 5), ])) elements.append(table) doc.build(elements) def export_customers_excel(rows, buffer): from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill wb = Workbook() ws = wb.active ws.title = "گزارش مشتریان" ws.sheet_view.rightToLeft = True headers = ["نام", "نام خانوادگی", "شهر", "شماره تماس", "مجموع خرید (تومان)", "وزن خرید (گرم)", "تعداد خرید", "رتبه", "تاریخ عضویت"] ws.append(headers) header_fill = PatternFill(start_color="D4AF37", end_color="D4AF37", fill_type="solid") for cell in ws[1]: cell.font = Font(bold=True) cell.fill = header_fill cell.alignment = Alignment(horizontal="center") for r in rows: c = r["customer"] ws.append([ c.first_name, c.last_name, c.city or "-", c.mobile_phone or "-", r["total_amount"], r["total_weight"], r["purchase_count"], c.rating or "-", to_jalali_str(c.joined_at), ]) for col in ws.columns: max_len = max((len(str(cell.value)) for cell in col if cell.value is not None), default=10) ws.column_dimensions[col[0].column_letter].width = max(12, min(30, max_len + 4)) wb.save(buffer) def export_salespeople_excel(calls, buffer): from openpyxl import Workbook from openpyxl.styles import Font, Alignment, PatternFill wb = Workbook() ws = wb.active ws.title = "گزارش فروشنده‌ها" ws.sheet_view.rightToLeft = True headers = ["تاریخ تماس", "فروشنده", "مشتری", "پاسخ داده شد؟", "نتیجه", "اقدام بعدی"] ws.append(headers) header_fill = PatternFill(start_color="D4AF37", end_color="D4AF37", fill_type="solid") for cell in ws[1]: cell.font = Font(bold=True) cell.fill = header_fill cell.alignment = Alignment(horizontal="center") for c in calls: ws.append([ to_jalali_str(c.call_date, with_time=True), c.salesperson.full_name if c.salesperson else "-", f"{c.customer.first_name} {c.customer.last_name}", "بله" if c.answered else ("خیر" if c.answered is False else "-"), c.result_text or "-", c.next_action or "-", ]) for col in ws.columns: max_len = max((len(str(cell.value)) for cell in col if cell.value is not None), default=10) ws.column_dimensions[col[0].column_letter].width = max(12, min(40, max_len + 4)) wb.save(buffer) def export_customers_pdf(rows, buffer, title="گزارش مشتریان"): headers = ["نام", "نام خانوادگی", "شهر", "تماس", "مجموع خرید", "وزن (گرم)", "تعداد خرید", "رتبه"] data_rows = [ [r["customer"].first_name, r["customer"].last_name, r["customer"].city or "-", r["customer"].mobile_phone or "-", f"{r['total_amount']:,.0f}", f"{r['total_weight']:,.1f}", r["purchase_count"], r["customer"].rating or "-"] for r in rows ] _build_pdf(buffer, title, headers, data_rows) def export_salespeople_pdf(calls, buffer, title="گزارش فروشنده‌ها"): headers = ["تاریخ", "فروشنده", "مشتری", "پاسخ", "نتیجه"] data_rows = [ [to_jalali_str(c.call_date), c.salesperson.full_name if c.salesperson else "-", f"{c.customer.first_name} {c.customer.last_name}", "بله" if c.answered else ("خیر" if c.answered is False else "-"), (c.result_text or "-")[:40]] for c in calls ] _build_pdf(buffer, title, headers, data_rows)