private void loadContacts() throws SQLException {
Query q =
Employee.table
.query()
.select(Employee.table, Im4ixUser.table)
.join(Employee.table.USER)
.where(SQL.eq(Employee.table.IS_CONTACT, true))
.orderBy(Im4ixUser.table.FIRST_NAME, Im4ixUser.table.LAST_NAME);
contactField.addOption(cx().t8("all.contacts"), "", null);
try (Query.Result qr = q.exec(cx())) {
while (qr.next()) {
Employee e = Employee.table.loadRec(cx(), qr);
Employee.table.USER.loadRefRec(e, qr);
contactField.addOption(e.user().fullName(), e.user().name(), e);
}
}
}
private void loadSlips() throws SQLException {
Query q =
PaySlip.table
.query()
.select(PaySlip.table.START_DATE)
.join(Employee.table, PaySlip.table.EMPLOYEE.joinCond())
.join(Im4ixUser.table, Employee.table.USER.joinCond())
.where(Im4ixUser.table.primaryKey().eq(cx().currentUser()))
.orderBy(SQL.desc(PaySlip.table.START_DATE));
Set<Integer> years = new TreeSet<>();
try (Query.Result qr = q.exec(cx())) {
while (qr.next()) {
years.add(qr.getDate(PaySlip.table.START_DATE).getYear() + 1900);
}
}
years.forEach(
(y) -> {
String n = y.toString();
yearField.addOption(n, n, y);
});
}
@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;
}