Esempio n. 1
0
    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);
          });
    }
Esempio n. 3
0
    @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;
    }