@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"}); }