@Override
protected Query createQuery() throws SQLException {
assignmentCountQuery =
Assignment.table
.query()
.select(SQL.count())
.where(
Assignment.table.EMPLOYEE.joinCond(),
SQL.lte(Assignment.table.START_DATE, cx().tz().today()),
SQL.gte(Assignment.table.END_DATE, cx().tz().today()),
SQL.gt(Assignment.table.CHARGE_RATE, 0));
Query q =
Employee.table
.query()
.select(Employee.table, PaySlip.table)
.join(Employee.table.USER)
.leftJoin(
PaySlip.table,
SQL.and(
PaySlip.table.EMPLOYEE.joinCond(),
SQL.eq(
PaySlip.table.END_DATE,
PaySlip.table
.query()
.select(SQL.max(PaySlip.table.END_DATE))
.where(PaySlip.table.EMPLOYEE.joinCond()))))
.orderBy(Im4ixUser.table.FIRST_NAME, Im4ixUser.table.LAST_NAME);
q.select(assignmentCountQuery);
if (!showFixedBox.isChecked()) {
q.where(SQL.neq(Employee.table.EMPLOYMENT_TYPE, Employee.EmploymentType.FIXED));
}
if (!showCommissionableBox.isChecked()) {
q.where(SQL.neq(Employee.table.EMPLOYMENT_TYPE, Employee.EmploymentType.COMMISSIONABLE));
}
if (!showSubconsultantBox.isChecked()) {
q.where(SQL.neq(Employee.table.EMPLOYMENT_TYPE, Employee.EmploymentType.SUBCONSULTANT));
}
if (!showAvailableBox.isChecked()) {
q.where(SQL.or(SQL.neq(assignmentCountQuery, 0), SQL.eq(Employee.table.IS_ACTIVE, false)));
}
if (!showBusyBox.isChecked()) {
q.where(SQL.or(SQL.eq(assignmentCountQuery, 0), SQL.eq(Employee.table.IS_ACTIVE, false)));
}
if (!showInactiveBox.isChecked()) {
q.where(SQL.eq(Employee.table.IS_ACTIVE, true));
}
if (contactField.selectedObject() != null) {
Query sq =
EmployeeContact.table
.query()
.where(
SQL.eq(
EmployeeContact.table.EMPLOYEE.getCol(
Employee.table.USER.getCol(Im4ixUser.table.NAME)),
Im4ixUser.table.NAME),
SQL.eq(
EmployeeContact.table.CONTACT.getCol(
Employee.table.USER.getCol(Im4ixUser.table.NAME)),
contactField.selectedValue()));
q.where(SQL.exists(sq));
}
return q;
}
