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 loadAssignments(final Date startDate, final Date endDate) throws SQLException {
try (Query.Result qr =
Assignment.table
.query()
.select(Assignment.table)
.join(Assignment.table.EMPLOYEE)
.where(
Employee.table.USER.eq(cx.currentUser()),
SQL.gte(Assignment.table.END_DATE, startDate),
SQL.lte(Assignment.table.START_DATE, endDate),
SQL.not(SQL.eq(Assignment.table.PAY_RATE, BigDecimal.ZERO)))
.orderBy(SQL.desc(Assignment.table.START_DATE), SQL.desc(Assignment.table.END_DATE))
.exec(cx)) {
while (qr.next()) {
assignments.add(Assignment.table.loadRec(cx, qr));
}
}
}
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;
}
private void createCanvas(final DOM container) throws SQLException {
d = container.div();
c = d.canvas();
c.style(CSS.HEIGHT, "30em").style(CSS.MAX_WIDTH, "1160px");
d.br();
final int year = yearField.selectedObject();
final Date startDate = cx.tz().newDate(year, 1, 1);
final Date endDate = cx.tz().newDate(year, 12, 31);
loadAssignments(startDate, endDate);
final List<String> labels = new ArrayList<>();
final List<BigDecimal> maxPayBuffers = new ArrayList<>();
final List<BigDecimal> payCosts = new ArrayList<>();
final List<BigDecimal> payBuffers = new ArrayList<>();
final List<BigDecimal> vacationDeductions = new ArrayList<>();
final List<BigDecimal> otherCosts = new ArrayList<>();
try (Query.Result qr =
PaySlip.table
.query()
.select(
PaySlip.table.START_DATE,
PaySlip.table.END_DATE,
PaySlip.table.GROSS_PAY,
PaySlip.table.GENERAL_PAYROLL_TAX,
PaySlip.table.OUTGOING_PAY_BUFFER,
PaySlip.table.INCOMING_PAY_BUFFER,
PaySlip.table.VACATION_DEDUCTION,
PaySlip.table.TOTAL_COST)
.join(Employee.table, PaySlip.table.EMPLOYEE.joinCond())
.join(Im4ixUser.table, Employee.table.USER.joinCond())
.where(
Im4ixUser.table.primaryKey().eq(cx().currentUser()),
SQL.lte(PaySlip.table.START_DATE, endDate),
SQL.gte(PaySlip.table.END_DATE, startDate),
SQL.eq(PaySlip.table.IS_APPROVED, true))
.orderBy(PaySlip.table.START_DATE)
.exec(cx)) {
while (qr.next()) {
labels.add(String.format("'%s'", cx.formatDate(qr.getDate(PaySlip.table.START_DATE))));
BigDecimal maxPay = BigDecimal.ZERO;
for (Date d = qr.getDate(PaySlip.table.START_DATE);
d.compareTo(qr.getDate(PaySlip.table.END_DATE)) <= 0;
d = cx.tz().addDays(d, 1)) {
final boolean isHoliday = cx.isHoliday(d);
if (!isHoliday) {
maxPay = maxPay.add(getMaxPayRate(d).multiply(BigDecimal.valueOf(8)));
}
}
maxPayBuffers.add(maxPay);
payCosts.add(
qr.getBigDecimal(PaySlip.table.GROSS_PAY)
.add(qr.getBigDecimal(PaySlip.table.GENERAL_PAYROLL_TAX)));
payBuffers.add(
qr.getBigDecimal(PaySlip.table.OUTGOING_PAY_BUFFER)
.subtract(qr.getBigDecimal(PaySlip.table.INCOMING_PAY_BUFFER)));
vacationDeductions.add(qr.getBigDecimal(PaySlip.table.VACATION_DEDUCTION));
otherCosts.add(
qr.getBigDecimal(PaySlip.table.TOTAL_COST)
.subtract(qr.getBigDecimal(PaySlip.table.GROSS_PAY))
.subtract(qr.getBigDecimal(PaySlip.table.GENERAL_PAYROLL_TAX))
.subtract(qr.getBigDecimal(PaySlip.table.VACATION_DEDUCTION)));
}
}
final StringBuffer labelData = new StringBuffer();
final StringBuffer maxPayBufferData = new StringBuffer();
final StringBuffer payCostData = new StringBuffer();
final StringBuffer payBufferData = new StringBuffer();
final StringBuffer vacationDeductionData = new StringBuffer();
final StringBuffer otherCostData = new StringBuffer();
String sep = "";
for (int i = 0; i < payCosts.size(); i++) {
labelData.append(sep).append(labels.get(i));
maxPayBufferData.append(sep).append(cx().formatDecimalRaw(maxPayBuffers.get(i)));
payCostData.append(sep).append(cx().formatDecimalRaw(payCosts.get(i)));
payBufferData.append(sep).append(cx().formatDecimalRaw(payBuffers.get(i)));
vacationDeductionData.append(sep).append(cx().formatDecimalRaw(vacationDeductions.get(i)));
otherCostData.append(sep).append(cx().formatDecimalRaw(otherCosts.get(i)));
sep = ", ";
}
JS(String.format("var ctx = Coredom.tags[%d].getContext('2d');", c.id()));
JS("var data = {");
JS("labels: [");
JS(labelData.toString());
JS("],");
JS("datasets: [");
JS("{backgroundColor: 'rgba(151,187,187,1)', ");
JS("data: [");
JS(payCostData.toString());
JS("]},");
JS("{backgroundColor: 'rgba(151,205,187,1)', ");
JS("data: [");
JS(maxPayBufferData.toString());
JS("]},");
JS("{backgroundColor: 'rgba(151,187,205,1)', ");
JS("data: [");
JS(payBufferData.toString());
JS("]},");
JS("{backgroundColor: 'rgba(205,187,151,1)', ");
JS("data: [");
JS(vacationDeductionData.toString());
JS("]},");
JS("{backgroundColor: 'rgba(205,151,151,1)', ");
JS("data: [");
JS(otherCostData.toString());
JS("]}]};");
JS("document.Im4ix = {};");
JS(
"document.Im4ix.chart = new Chart(ctx, {type: 'bar', data: data, "
+ "options: {legend: {display: false}, scaleStepWidth: 1, scaleStartValue: 0, "
+ "scales: {xAxes:[{gridLines: {display: false}}], "
+ "yAxes:[{gridLines: {display: false}}]}, "
+ "animation: false, responsive: true, showTooltips: false}});");
}