Разработка конструктора отчётов (Report Builder) на сайте

Разработка конструктора отчётов (Report Builder) на сайте

Конструктор отчётов — это UI, который позволяет пользователю самостоятельно формировать запрос к данным: выбирать поля, фильтры, группировки, тип визуализации — и сохранять результат как именованный отчёт. Разница с Pivot Table в том, что конструктор работает на уровне абстракции выше: пользователь оперирует бизнес-понятиями (заказы, клиенты, регионы), а не сырыми полями таблицы.

Архитектура

Конструктор отчётов состоит из четырёх слоёв:

Metadata Layer — каталог доступных сущностей и полей с человекочитаемыми именами, типами и допустимыми агрегациями. Хранится на сервере, загружается при инициализации.

Query Builder — UI для составления запроса. На клиенте собирает конфиг запроса.

Query Engine — сервер превращает конфиг в SQL (или запрос к OLAP-кубу) и возвращает данные.

Renderer — клиент рисует таблицу или график по данным.

Метаданные

interface FieldMeta {
  id: string;
  label: string;
  type: 'string' | 'number' | 'date' | 'boolean';
  entity: string;
  aggregatable: boolean;
  filterable: boolean;
  // Допустимые агрегации для числовых полей
  aggregations?: ('sum' | 'avg' | 'count' | 'min' | 'max' | 'count_distinct')[];
}

interface EntityMeta {
  id: string;
  label: string;
  fields: FieldMeta[];
  // Доступные join-пути
  relations?: { entity: string; via: string; label: string }[];
}

// Пример метаданных для e-commerce
const metadata: EntityMeta[] = [
  {
    id: 'orders',
    label: 'Заказы',
    fields: [
      { id: 'orders.created_at', label: 'Дата заказа', type: 'date', entity: 'orders', aggregatable: false, filterable: true },
      { id: 'orders.total', label: 'Сумма заказа', type: 'number', entity: 'orders', aggregatable: true, filterable: true, aggregations: ['sum', 'avg', 'min', 'max'] },
      { id: 'orders.status', label: 'Статус', type: 'string', entity: 'orders', aggregatable: false, filterable: true },
      { id: 'orders.count', label: 'Количество заказов', type: 'number', entity: 'orders', aggregatable: true, filterable: false, aggregations: ['count'] },
    ],
    relations: [
      { entity: 'customers', via: 'customer_id', label: 'Клиент' },
      { entity: 'products', via: 'order_items', label: 'Товары' },
    ],
  },
  {
    id: 'customers',
    label: 'Клиенты',
    fields: [
      { id: 'customers.city', label: 'Город', type: 'string', entity: 'customers', aggregatable: false, filterable: true },
      { id: 'customers.segment', label: 'Сегмент', type: 'string', entity: 'customers', aggregatable: false, filterable: true },
      { id: 'customers.registered_at', label: 'Дата регистрации', type: 'date', entity: 'customers', aggregatable: false, filterable: true },
    ],
  },
];

Конфигурация запроса

interface FilterCondition {
  field: string;
  operator: 'eq' | 'neq' | 'gt' | 'gte' | 'lt' | 'lte' | 'in' | 'contains' | 'between' | 'is_null';
  value: any;
}

interface Dimension {
  field: string;
  // Для дат — группировка по периоду
  dateTrunc?: 'day' | 'week' | 'month' | 'quarter' | 'year';
}

interface Measure {
  field: string;
  aggregation: 'sum' | 'avg' | 'count' | 'min' | 'max' | 'count_distinct';
  label?: string;
}

interface ReportConfig {
  id?: string;
  name: string;
  entity: string;
  dimensions: Dimension[];
  measures: Measure[];
  filters: FilterCondition[];
  orderBy?: { field: string; direction: 'asc' | 'desc' };
  limit?: number;
  visualization: 'table' | 'bar' | 'line' | 'pie' | 'area';
}

Генерация SQL на сервере

class ReportQueryBuilder {
  build(config: ReportConfig): { sql: string; params: any[] } {
    const params: any[] = [];
    let paramIdx = 1;

    const addParam = (v: any) => { params.push(v); return `$${paramIdx++}`; };

    // SELECT
    const selectParts: string[] = [];

    config.dimensions.forEach(dim => {
      const col = this.resolveColumn(dim.field);
      if (dim.dateTrunc) {
        selectParts.push(`DATE_TRUNC('${dim.dateTrunc}', ${col}) AS "${dim.field}"`);
      } else {
        selectParts.push(`${col} AS "${dim.field}"`);
      }
    });

    config.measures.forEach(m => {
      const col = this.resolveColumn(m.field);
      const aggExpr = m.aggregation === 'count_distinct'
        ? `COUNT(DISTINCT ${col})`
        : `${m.aggregation.toUpperCase()}(${col})`;
      const label = m.label ?? `${m.aggregation}(${m.field})`;
      selectParts.push(`${aggExpr} AS "${label}"`);
    });

    // FROM + JOINs
    const fromClause = this.buildFromClause(config);

    // WHERE
    const whereParts = config.filters.map(f => {
      const col = this.resolveColumn(f.field);
      switch (f.operator) {
        case 'eq':       return `${col} = ${addParam(f.value)}`;
        case 'neq':      return `${col} != ${addParam(f.value)}`;
        case 'gt':       return `${col} > ${addParam(f.value)}`;
        case 'gte':      return `${col} >= ${addParam(f.value)}`;
        case 'lt':       return `${col} < ${addParam(f.value)}`;
        case 'lte':      return `${col} <= ${addParam(f.value)}`;
        case 'in':       return `${col} = ANY(${addParam(f.value)})`;
        case 'contains': return `${col} ILIKE ${addParam(`%${f.value}%`)}`;
        case 'between':  return `${col} BETWEEN ${addParam(f.value[0])} AND ${addParam(f.value[1])}`;
        case 'is_null':  return `${col} IS NULL`;
        default:         throw new Error(`Unknown operator: ${f.operator}`);
      }
    });

    // GROUP BY
    const groupByParts = config.dimensions.map((dim, i) => String(i + 1));

    // ORDER BY
    let orderByClause = '';
    if (config.orderBy) {
      orderByClause = `ORDER BY "${config.orderBy.field}" ${config.orderBy.direction.toUpperCase()}`;
    }

    const sql = [
      `SELECT ${selectParts.join(', ')}`,
      `FROM ${fromClause}`,
      whereParts.length ? `WHERE ${whereParts.join(' AND ')}` : '',
      groupByParts.length ? `GROUP BY ${groupByParts.join(', ')}` : '',
      orderByClause,
      config.limit ? `LIMIT ${config.limit}` : 'LIMIT 10000',
    ].filter(Boolean).join('\n');

    return { sql, params };
  }

  private resolveColumn(field: string): string {
    // field = 'orders.total' -> '"orders"."total"'
    const [table, col] = field.split('.');
    return col ? `"${table}"."${col}"` : `"${field}"`;
  }

  private buildFromClause(config: ReportConfig): string {
    // Упрощённая версия — реальная реализация строит JOIN-граф
    return `"${config.entity}"`;
  }
}

Сохранение и версионирование отчётов

CREATE TABLE saved_reports (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  description TEXT,
  config      JSONB NOT NULL,
  created_by  UUID NOT NULL REFERENCES users(id),
  is_public   BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- История версий конфигураций
CREATE TABLE report_versions (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id  UUID NOT NULL REFERENCES saved_reports(id),
  config     JSONB NOT NULL,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_saved_reports_created_by ON saved_reports(created_by);
CREATE INDEX idx_saved_reports_public ON saved_reports(is_public) WHERE is_public = true;

Расписание и экспорт

Пользователи часто хотят получать отчёт по email автоматически:

// Cron job — ежедневная рассылка отчётов
async function sendScheduledReports() {
  const scheduled = await db.query(`
    SELECT sr.*, u.email
    FROM saved_reports sr
    JOIN users u ON u.id = sr.created_by
    WHERE sr.config->>'schedule' IS NOT NULL
      AND sr.config->>'schedule' != 'none'
  `);

  for (const report of scheduled) {
    const { sql, params } = queryBuilder.build(report.config);
    const data = await db.query(sql, params);

    const xlsx = await generateExcel(data.rows, report.config);
    await mailer.send({
      to: report.email,
      subject: `Отчёт: ${report.name}`,
      attachments: [{ filename: `${report.name}.xlsx`, content: xlsx }],
    });
  }
}

Безопасность

Генерация SQL из пользовательского ввода опасна без правильной валидации:

function validateReportConfig(config: ReportConfig, metadata: EntityMeta[]): void {
  const allowedFieldIds = new Set(
    metadata.flatMap(e => e.fields.map(f => f.id))
  );

  // Проверяем что все поля существуют в метаданных
  [...config.dimensions.map(d => d.field), ...config.measures.map(m => m.field), ...config.filters.map(f => f.field)]
    .forEach(field => {
      if (!allowedFieldIds.has(field)) {
        throw new Error(`Unknown field: ${field}`);
      }
    });

  // Проверяем агрегации
  config.measures.forEach(m => {
    const fieldMeta = metadata.flatMap(e => e.fields).find(f => f.id === m.field);
    if (!fieldMeta?.aggregations?.includes(m.aggregation)) {
      throw new Error(`Aggregation ${m.aggregation} not allowed for field ${m.field}`);
    }
  });
}

Поля и таблицы в SQL-запросе берутся только из белого списка метаданных — прямая интерполяция строк из запроса пользователя недопустима.

Сроки

Базовый конструктор с одной сущностью, пятью-десятью полями, таблицей и одним графиком — 3–4 недели. Полноценный конструктор с несколькими сущностями, join-ами, произвольными фильтрами, расписанием и версионированием — 2–3 месяца.