@Test
public void testProjectReport() {
SQLSelectQuery rQuery = new SQLSelectQuery();
rQuery
.table("InvoiceDetail", "Attribute")
.field("Attribute.value", "Name")
.field("EXTRACT(YEAR FROM InvoiceDetail.startDate) AS year", "Year")
.field("EXTRACT(MONTH FROM InvoiceDetail.startDate) AS month", "Month")
.field("sum(InvoiceDetail.finalCharge)", "Grand Total")
.field("sum(InvoiceDetail.totalPaid)", "Total Paid")
.cond("Attribute.name = 'restaurant:name'")
.cond("Attribute.invoiceId = InvoiceDetail.id")
.groupBy("year", "month", "Attribute.value")
.orderBy("year", "month");
System.out.println(rQuery.createSQLQuery());
ReportTable[] reportTable = service.reportQuery(new SQLSelectQuery[] {rQuery});
reportTable[0].dump(new String[] {"Name", "Year", "Month", "Grand Total", "Total Paid"});
}
@Test
public void testRevenueEmployeeReport() {
SQLSelectQuery rQuery = new SQLSelectQuery();
rQuery
.table("InvoiceDetail", "Contributor")
.field("Contributor.role", "Role")
.field("InvoiceDetail.activityType", "Activity Type")
.field("Contributor.identifierId", "Employee")
.field("sum(InvoiceDetail.finalCharge)", "Grand Total")
.field("sum(InvoiceDetail.totalPaid)", "Total Paid")
.field("count(InvoiceDetail.type)", "Payment")
.cond("Contributor.invoiceId = InvoiceDetail.id")
.groupBy("Contributor.role, Contributor.identifierId", "InvoiceDetail.activityType")
.orderBy("Contributor.identifierId");
System.out.println(rQuery.createSQLQuery());
ReportTable[] reportTable = service.reportQuery(new SQLSelectQuery[] {rQuery});
reportTable[0].dump(
new String[] {"Employee", "Role", "Grand Total", "Total Paid", "Payment", "Activity Type"});
}
@Test
public void testInvoiceProductReport() {
SQLSelectQuery rQuery = new SQLSelectQuery();
rQuery
.table("InvoiceDetail", "InvoiceItem", "InvoiceItemAttribute")
.field("InvoiceItemAttribute.name", "Product")
.field("InvoiceItemAttribute.value", "")
.field("sum(InvoiceItem.quantity)", "Quantity")
.field("sum(InvoiceItem.total)", "Total")
.field("InvoiceDetail.activityType", "Activity Type")
.cond("InvoiceItem.invoiceId = InvoiceDetail.id")
.cond("InvoiceItemAttribute.invoiceItemId = InvoiceItem.id")
.cond("InvoiceItemAttribute.name = 'Product'")
.groupBy(
"InvoiceItemAttribute.name, InvoiceItemAttribute.value, InvoiceDetail.activityType")
.orderBy("InvoiceItemAttribute.name");
System.out.println(rQuery.createSQLQuery());
ReportTable[] reportTable = service.reportQuery(new SQLSelectQuery[] {rQuery});
reportTable[0].dump(new String[] {"Product", "Quantity", "Total", "Activity Type"});
}
