Report Generation

 Root
  ├── Client
  └── Contacts

<h1>Client Profile</h1>
<div class="subtitle">Generated: ${.now?string("yyyy-MM-dd HH:mm")}</div> (1)

<#assign Client = Root.bands.Client[0]> (2)
<#assign Contacts = Root.bands.Contacts> (3)

<div class="section">
  <h2>Summary</h2>
  <dl class="kv">
    <dt>Name</dt><dd>${Client.fields.name!}</dd> (4)
    <dt>Full name</dt><dd>${Client.fields.fullName!}</dd>
    <dt>Type</dt><dd>${Client.fields.type!}</dd>
    <dt>Address</dt><dd>${Client.fields.address!}</dd>
    <dt>VAT number</dt><dd>${Client.fields.vatNumber!}</dd>
    <dt>Registration number</dt><dd>${Client.fields.regNumber!}</dd>
    <dt>Website</dt><dd>${Client.fields.website!}</dd>
    <dt>Account manager</dt><dd>${Client.fields.accountManager!}</dd>
  </dl>
</div>

<#if Contacts?has_content>
  <div class="section">
    <h2>Contacts</h2>
    <table>
      <thead>
        <tr>
          <th>#</th>
          <th>Person</th>
          <th>Position</th>
          <th>Email</th>
          <th>Phone</th>
        </tr>
      </thead>
      <tbody>
        <#list Contacts as c> (5)
          <tr>
            <td>${c?index + 1}</td> (6)
            <td>${c.fields.person!}</td> (7)
            <td>${c.fields.position!}</td>
            <td>${c.fields.email!}</td>
            <td>${c.fields.phone!}</td>
          </tr>
        </#list>
      </tbody>
    </table>
  </div>
</#if>

def metadataTools = applicationContext.getBean(io.jmix.core.MetadataTools)

def client = params['client']

return [[
    'name': client.name,
    'fullName': client.fullName,
    'type': client.type.name(),
    'address': client.address,
    'vatNumber': client.vatNumber,
    'regNumber': client.regNumber,
    'website': client.website,
    'accountManager': client.accountManager ? metadataTools.getInstanceName(client.accountManager) : ''
    ]]

<hbox id="buttonsPanel" classNames="buttons-panel">
    <!-- ... -->
    <comboButton text="Reports">
        <items>
            <actionItem id="reportForEntityActionItem" ref="clientsDataGrid.runListEntityReportAction"/>
            <actionItem id="reportActionItem" ref="clientsDataGrid.runReportAction"/>
        </items>
    </comboButton>
</hbox>
<dataGrid id="clientsDataGrid" width="100%" minHeight="20em"
          dataContainer="clientsDc" columnReorderingAllowed="true">
    <actions>
        <!-- ... -->
        <action id="runListEntityReportAction" text="Reports for entity"
                type="report_runListEntityReport"/> (1)
        <action id="runReportAction" text="Reports" icon="vaadin:table"
                type="report_runReport"/> (2)
    </actions>

<actions>
    <!-- ... -->
    <action id="runSingleEntityReportAction" text="Reports for entity"
            type="report_runSingleEntityReport"/> (1)
</actions>
<layout>
    <hbox id="detailActions">
        <button id="saveAndCloseButton" action="saveAction"/>
        <button id="closeButton" action="closeAction"/>
        <button action="runSingleEntityReportAction"/> (1)
        <button id="printClientProfileButton" text="Print client profile"
                icon="vaadin:file-text"/> (2)
    </hbox>
</layout>

public class ClientDetailView extends StandardDetailView<Client> {

    @Autowired
    private UiReportRunner uiReportRunner; (1)

    @Subscribe(id = "printClientProfileButton", subject = "clickListener")
    public void onPrintClientProfileButtonClick(final ClickEvent<JmixButton> event) {
        uiReportRunner.byReportCode("client-profile")
                .addParam("client", getEditedEntity())
                .withTemplateCode("HTML → HTML")
                .withParametersDialogShowMode(ParametersDialogShowMode.NO)
                .runAndShow();
    }
}

Root
  ├── Header
  ├── Client
  │     ├── OrderStatus
  │     │     └── Order
  │     └── ClientTotal
  └── GrandTotal

public class OrdersByClientReport {
    @Autowired
    private DataManager dataManager;

    private final ThreadLocal<BigDecimal> runningClientTotal =
            ThreadLocal.withInitial(() -> BigDecimal.ZERO); (1)
    private final ThreadLocal<BigDecimal> runningGrandTotal =
            ThreadLocal.withInitial(() -> BigDecimal.ZERO); (1)

    @DataSetDelegate(name = "client")
    public ReportDataLoader clientDataLoader() {
        return (reportQuery, parentBand, params) -> {

            // Initialize thread locals on report start
            runningClientTotal.set(BigDecimal.ZERO);
            runningGrandTotal.set(BigDecimal.ZERO);

            List<Client> clients = dataManager.load(Client.class) (2)
                    .query("""
                            select c from Client c
                            where exists (
                                            select 1 from Order_ o where o.client.id = c.id and
                                                (:dateFrom is null or o.date >= :dateFrom) and
                                                (:dateTo is null or o.date <= :dateTo)
                                         )
                            order by c.name""")
                    .parameter("dateFrom", params.get("dateFrom"))
                    .parameter("dateTo", params.get("dateTo"))
                    .list();
            return clients.stream()
                    .map(client -> Map.of("id", client.getId(), "name", (Object) client.getName()))
                    .toList();
        };
    }
    @DataSetDelegate(name = "order")
    public ReportDataLoader orderDataLoader() {
        return (reportQuery, parentBand, params) -> {
            Object clientId = parentBand.getParentBand().getData().get("id");  (3)
            Object statusId = parentBand.getData().get("statusId");
            List<Order> orderList = dataManager.load(Order.class)
                    .query("""
                            select o from Order_ o
                            where o.client.id = :clientId and o.status = :status and
                                (:dateFrom is null or o.date >= :dateFrom) and
                                (:dateTo is null or o.date <= :dateTo)
                            order by o.date""")
                    .parameter("clientId", clientId)
                    .parameter("status", statusId)
                    .parameter("dateFrom", params.get("dateFrom"))
                    .parameter("dateTo", params.get("dateTo"))
                    .list();
            return orderList.stream()
                    .map(order -> {
                        runningClientTotal.set(
                                runningClientTotal.get().add(order.getTotal())
                        ); (4)
                        Map<String, Object> map = new HashMap<>();
                        map.put("order", order);
                        map.put("date", order.getDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                        map.put("quote", order.getQuote());
                        map.put("comment", order.getComment());
                        map.put("total", order.getTotal());
                        return map;
                    })
                    .toList();
        };
    }

    @DataSetDelegate(name = "clientTotal")
    public ReportDataLoader clientTotalDataLoader() {
        return (reportQuery, parentBand, params) -> {
            Map<String, Object> map = new HashMap<>();
            map.put("clientTotal", runningClientTotal.get()); (5)

            runningGrandTotal.set(
                    runningGrandTotal.get().add(runningClientTotal.get())
            ); (6)
            runningClientTotal.set(BigDecimal.ZERO);

            return List.of(map);
        };
    }

    @DataSetDelegate(name = "grandTotal")
    public ReportDataLoader grandTotalDataLoader() {
        return (reportQuery, parentBand, params) -> {
            Map<String, Object> map = new HashMap<>();
            map.put("grandTotal", runningGrandTotal.get()); (7)

            // Clean up thread locals on report finish
            runningClientTotal.remove();
            runningGrandTotal.remove();

            return List.of(map);
        };
    }
}

select sum(o.total) as "clientTotal"
from Order_ o
where o.client.id = ${Client.id} and
    (${dateFrom} is null or o.date >= ${dateFrom}) and
    (${dateTo} is null or o.date <= ${dateTo})

select sum(o.total) as "grandTotal"
from Order_ o
where (${dateFrom} is null or o.date >= ${dateFrom}) and
    (${dateTo} is null or o.date <= ${dateTo})

Root
  ├── Header
  ├── OrderStatus
  └── Chart

@BandDef(
        name = "Chart",
        parent = "Root"
)
public class OrdersByStatusReport {
}

@BandDef(
        name = "OrderStatus",
        parent = "Root",
        dataSets = @DataSetDef(name = "orderStatus", type = DataSetType.DELEGATE)
)
public class OrdersByStatusReport {

    @Autowired
    private DataManager dataManager;

    @DataSetDelegate(name = "orderStatus")
    public ReportDataLoader orderStatusDataLoader() {
        return (reportQuery, parentBand, params) -> {
            List<KeyValueEntity> keyValueEntities = dataManager.loadValues("""
                            select o.status, count(o), sum(o.total) from Order_ o
                            where (:dateFrom is null or o.date >= :dateFrom) and
                                (:dateTo is null or o.date <= :dateTo)
                            group by o.status order by o.status""")
                    .properties("statusId", "count", "total")
                    .parameter("dateFrom", params.get("dateFrom"))
                    .parameter("dateTo", params.get("dateTo"))
                    .list();
            return keyValueEntities.stream()
                    .map(kve -> {
                        Map<String, Object> map = new HashMap<>();
                        map.put("status", OrderStatus.fromId(kve.getValue("statusId")));
                        map.put("count", kve.getValue("count"));
                        map.put("total", kve.getValue("total"));
                        return map;
                    })
                    .toList();
        };
    }
}

select o.status as "status",
    count(o) as "count", sum(o.total) as "total"
from Order_ o
where (${dateFrom} is null or o.date >= ${dateFrom}) and
    (${dateTo} is null or o.date <= ${dateTo})
group by o.status
order by o.status

Root
  ├── Header
  └── Revenue

@InputParameterDef(
        alias = "dateFrom",
        name = "From",
        type = ParameterType.DATE,
        required = true (1)
)

@InputParameterDef(
        alias = "dateTo",
        name = "To",
        type = ParameterType.DATE,
        required = true (1)
)
public class RevenueByMonthReport {

    @InputParameterDelegate(alias = "dateFrom") (2)
    public ParameterValidator<Date> dateFromValidator() {
        return value -> {
            if (!Objects.equals(value, ReportUtils.getFirstDayOfMonth(value))) {
                throw new ReportParametersValidationException("'From' date must be the first day of month");
            }
        };
    }
    
    @InputParameterDelegate(alias = "dateTo") (2)
    public ParameterValidator<Date> dateToValidator() {
        return value -> {
            if (!Objects.equals(value, ReportUtils.getLastDayOfMonth(value))) {
                throw new ReportParametersValidationException("'To' date must be the last day of month");
            }
        };
    }

    @ReportDelegate (3)
    public ParametersCrossValidator parametersCrossValidator() {
        return params -> {
            Date dateFrom = getParam(params, "dateFrom");
            Date dateTo = getParam(params, "dateTo");
            if (dateFrom.after(dateTo)) {
                throw new ReportParametersValidationException("'From' date must be before 'To' date");
            }
        };
    }
}

@BandDef(
        name = "Revenue",
        parent = "Root",
        orientation = Orientation.CROSS, (1)
        dataSets = {
                @DataSetDef(name = "Revenue_dynamic_header", (2)
                        type = DataSetType.DELEGATE),
                @DataSetDef(name = "Revenue_master_data", (3)
                        type = DataSetType.DELEGATE),
                @DataSetDef(name = "Revenue", (4)
                        type = DataSetType.DELEGATE)
        }
)
public class RevenueByMonthReport {

    @DataSetDelegate(name = "Revenue_dynamic_header") (2)
    public ReportDataLoader revenueDynamicHeaderDataLoader() {
        return (reportQuery, parentBand, params) -> {
            Date dateFrom = getParam(params, "dateFrom");
            Date dateTo = getParam(params, "dateTo");

            YearMonth start = YearMonth.from(ReportUtils.dateToLocalDate(dateFrom));
            YearMonth end = YearMonth.from(ReportUtils.dateToLocalDate(dateTo));

            List<Map<String, Object>> result = new ArrayList<>();
            YearMonth current = start;
            while (!current.isAfter(end)) {
                Map<String, Object> map = new HashMap<>();
                map.put("monthId", current.getYear() + "-" + current.getMonthValue());
                map.put("monthName", current.getMonth().getDisplayName(TextStyle.FULL, Locale.ENGLISH) + " " + current.getYear());
                map.put("year", current.getYear());
                map.put("month", current.getMonthValue());
                result.add(map);
                current = current.plusMonths(1);
            }
            return result;
        };
    }

    @DataSetDelegate(name = "Revenue_master_data") (3)
    public ReportDataLoader revenueMasterDataDataLoader() {
        return (reportQuery, parentBand, params) -> {
            List<Client> clients = dataManager.load(Client.class)
                    .query("""
                            select c from Client c
                            where exists (
                                    select 1 from Payment p where p.invoice.client.id = c.id and
                                        p.date >= :dateFrom and p.date <= :dateTo
                                    )
                            order by c.name""")
                    .parameter("dateFrom", params.get("dateFrom"))
                    .parameter("dateTo", params.get("dateTo"))
                    .list();
            return clients.stream()
                    .map(client -> Map.of("clientId", client.getId(), "name", (Object) client.getName()))
                    .toList();
        };
    }

     @DataSetDelegate(name = "Revenue") (4)
     public ReportDataLoader revenueDataLoader() {
        return (reportQuery, parentBand, params) -> {
            List<Map<String, Object>> headerList = getParam(params, "Revenue_dynamic_header");

            List<Map<String, Object>> masterDataList = getParam(params, "Revenue_master_data");
            List<Object> clientIds = masterDataList.stream()
                    .map(m -> m.get("clientId"))
                    .toList();

            List<Map<String, Object>> result = new ArrayList<>();
            for (Map<String, Object> headerData : headerList) {
                dataManager.loadValues("""
                            select p.invoice.client.id, sum(p.amount) from Payment p
                            where extract(month from p.date) = :month and
                                extract(year from p.date) = :year and
                                p.invoice.client.id in :clientIds
                            group by p.invoice.client.id
                            """)
                        .properties("clientId", "amount")
                        .parameter("month", headerData.get("month"))
                        .parameter("year", headerData.get("year"))
                        .parameter("clientIds", clientIds)
                        .list()
                        .forEach(kv -> {
                            Map<String, Object> map = new HashMap<>();
                            map.put("Revenue_dynamic_header@monthId", (5)
                                    headerData.get("monthId"));
                            map.put("Revenue_master_data@clientId", (5)
                                    kv.getValue("clientId"));
                            map.put("amount", kv.getValue("amount"));
                            result.add(map);
                        });
            }
            return result;
        };
    }
}

import com.company.crm.report.ReportUtils
import java.time.YearMonth
import java.time.format.TextStyle

def dateFrom = params["dateFrom"]
def dateTo = params["dateTo"]

def start = YearMonth.from(ReportUtils.dateToLocalDate(dateFrom));
def end = YearMonth.from(ReportUtils.dateToLocalDate(dateTo));

def result = []
def current = start
while (!current.isAfter(end)) {
    result.add([
        'monthId': current.getYear() + "-" + current.getMonthValue(),
        'monthName': current.getMonth().getDisplayName(TextStyle.FULL, Locale.ENGLISH) + " " + current.getYear(),
        'year': current.getYear(),
        'month': current.getMonthValue()
    ])
    current = current.plusMonths(1)
}
return result

select c.id as "clientId", c.name as "name"
from Client c
where exists (
        select 1 from Payment p where p.invoice.client.id = c.id and
            p.date >= ${dateFrom} and p.date <= ${dateTo}
        )
order by c.name

def headerList = params["Revenue_dynamic_header"]

def masterDataList = params["Revenue_master_data"]
def clientIds = masterDataList.collect { it['clientId'] }

def result = []
headerList.each { headerData ->
    dataManager.loadValues('''
                select p.invoice.client.id, sum(p.amount) from Payment p
                where extract(month from p.date) = :month and
                    extract(year from p.date) = :year and
                    p.invoice.client.id in :clientIds
                group by p.invoice.client.id
                ''')
            .properties("clientId", "amount")
            .parameter("month", headerData["month"])
            .parameter("year", headerData["year"])
            .parameter("clientIds", clientIds)
            .list()
            .each { kv ->
                result.add([
                    "Revenue_dynamic_header@monthId": headerData["monthId"],
                    "Revenue_master_data@clientId": kv.getValue("clientId"),
                    "amount": kv.getValue("amount")
                ])
            }
}
return result