Skip to main content

Task

Configure all EntityFields, whose values are calculated, i.e., not directly read from a database column. Particularly, these are the following EntityFields:

  • Car_entity:
    • mileage (mileage at return of latest completed reservation)
    • damages (simply concatenate single damages)
    • carValue (The value of the car depends on its price and on its age. Per day, the value decreases by 0.03%. Furthermore, if the car has any damage, its value should be reduced by another 10%.)
    • availability (current availability; calculated from reservations)
  • CarDriver_entity:
    • age (calculated from PERSON.DATEOFBIRTH)
    • drivingExperience (calculated from DRIVINGLICENSEISSUEDATE)
    • parkingTicketFinesSum
    • speedingFinesSum
  • CarReservation_entity:
    • mileageStart (mileage at return of previous reservation)
tip

Here you can find a generic description of calculated fields.

important

Always keep in mind that a value/displayValue calculated via the RecordContainer (RecordFieldMapping's property expression) shows, in most cases, a better performance than a calculation via the EntityField's valueProcess/displayValueProcess. However, you still need the valueProcess, if an EntityField should be preselected (initialized) with a specific value in the EditView, when inserting a new dataset that is not yet in the database.

Solution

Car_entity​

mileage​

The current milage of a car can be calculated in various ways. One would be to retrieve the maximum MILEAGERETURN value of all reservations of a specific car. Enter the following valueProcess process in CarReservation_entity's field mileageStart:

import { result, vars } from "@aditosoftware/jdito-types";
var carId = vars.get("$field.CARID");
if (carId)
{
  var maxMileage = newSelect("max(MILEAGERETURN)")
    .from("CARRESERVATION")
    .where("CARRESERVATION.CAR_ID", carId)
    .cell();
  result.string(maxMileage);
}

Car_entity.mileage.valueProcess.js

An alternative would be to search for the MILEAGERETURN of the latest reservation of a specific car, having a MILEAGERETURN value:

import { result, vars } from "@aditosoftware/jdito-types";
import { newSelect } from "SqlBuilder_lib";

var carId = vars.get("$field.CARID");
if (carId) {

    var maxMileage = newSelect("MILEAGERETURN")
        .from("CARRESERVATION")
        .where("CARRESERVATION.CAR_ID", carId)
        .and("CARRESERVATION.ENDDATE is not null")
        .and("CARRESERVATION.ENDDATE", 
          newSelect("max(CARRESERVATION.ENDDATE)")
          .from("CARRESERVATION")
          .where("CARRESERVATION.CAR_ID", carId))
        .cell();

    result.string(maxMileage);
}

Car_entity.mileage.valueProcess.js (alternative)

damages​

We simply concatenate the single damages included in CARRESERVATION:

import { result } from "@aditosoftware/jdito-types";
import { newSelect } from "SqlBuilder_lib";

let subSelect = newSelect("GROUP_CONCAT(CARRESERVATION.DAMAGE)")
    .from("CARRESERVATION")
    .where("CARRESERVATION.CAR_ID like CAR.CARID");

result.string(subSelect);

Car_entity.db.damages.value.expression.js

carValue​

As given in the task description: The value of the car depends on its price and on its age. Per day, the value decreases by 0.03%. Furthermore, if the car has any damage, its value should be reduced by another 10% (overall).

import { result, vars } from "@aditosoftware/jdito-types";
import { DateUtils } from "DateUtils_lib";

let price = vars.get("$field.PRICE");
let dateOfManufacture = vars.get("$field.MANUFACTUREDATE");
let damages = vars.get("$field.damages");
if (price && dateOfManufacture) {
    let value = price;
    let ageInDays = Math.floor(DateUtils.getDayDifference(dateOfManufacture));
    // just a random percentage as example
    let valueLossPercent = -0.03;
    // random formula, similar to
    // calculation of "compound interest"
    value *= Math.pow((1 + (valueLossPercent / 100)), ageInDays);
    if (damages) {
        // just a random factor as example
        value *= 0.9;
    }
    result.string(value);
}

Car_entity.carValue.valueProcess.js

import { result, text, vars } from "@aditosoftware/jdito-types";

let carValue = vars.get("$this.value");
let currency = vars.get("$field.CURRENCY");

if (carValue && currency) {
    result.string(text.formatDouble(carValue, "#,##0.00") + " " + currency);
}

Car_entity.carValue.displayValueProcess.js

availability​

As given in the task description: This is meant to be the current availability, which we can calculate from car reservations:

import { datetime, result, translate } from "@aditosoftware/jdito-types";
import { newSelect, SqlBuilder } from "SqlBuilder_lib";

let currentDate = datetime.date();

let subSelect = SqlBuilder.caseWhen("((" +
    newSelect("count(*)")
    .from("CARRESERVATION")
    .where("CARRESERVATION.CAR_ID = CAR.CARID")
    .and("CARRESERVATION.STARTDATE", currentDate, SqlBuilder.LESS)
    .and("CARRESERVATION.ENDDATE", currentDate, SqlBuilder.GREATER)
    + ") = 0 )")
    .thenString(translate.text("Yes"))
    .elseString(translate.text("No"))
    .toString();
  
result.string(subSelect);

Car_entity.db.availability.value.expression.js

CarDriver_entity​

age​

note

If you test the below code, first make sure that the contact persons you use have all a date of birth set.

The age of the car driver can be calculated from Person_entity's field DATEOFBIRTH. To simplify the calculation via SQL, we modify CarDriver_entity's RecordContainer by

  • adding the database table PERSON in property linkInformation;
  • joining the database tables CARDRIVER, CONTACT, and PERSON in property fromClauseProcess:
import { result } from "@aditosoftware/jdito-types";
import { SqlBuilder } from "SqlBuilder_lib";

var sql = new SqlBuilder()
    .from("CARDRIVER")
    .join("CONTACT", "CARDRIVER.CONTACT_ID = CONTACT.CONTACTID")
    .join("PERSON", "CONTACT.PERSON_ID = PERSON.PERSONID");

result.string(sql.toString());

After this preparation, we can now use the following expression process:

import { result } from "@aditosoftware/jdito-types";
import { SqlBuilder } from "SqlBuilder_lib";
import { SqlMaskingUtils } from "SqlMaskingUtils_lib";

var sqlUtils = new SqlMaskingUtils();
var sql = sqlUtils.yearFromDate("CURRENT_DATE")
    + " - "
    // Prerequisite: tables CONTACT and PERSON are joined
    + sqlUtils.yearFromDate("DATEOFBIRTH")
    + " - "
    + SqlBuilder.caseWhen(
        "("
        + sqlUtils.monthFromDate("CURRENT_DATE")
        + " < " + sqlUtils.monthFromDate("DATEOFBIRTH")
        + "OR ("
        + sqlUtils.monthFromDate("CURRENT_DATE")
        + " = " + sqlUtils.monthFromDate("DATEOFBIRTH")
        + " AND "
        + sqlUtils.dayFromDate("CURRENT_DATE")
        + " < " + sqlUtils.dayFromDate("DATEOFBIRTH")
        + ")"
        + ")"
    )
        .then("1")
        .elseValue("0")
        .toString();
result.string(sql);

CarDriver_entity.db.age.value.expression.js

As the age should also be shown in CarDriverEdit_view, after selecting the contact person (CONTACT_ID), we also insert a valueProcess:

import { result, vars } from "@aditosoftware/jdito-types";
import { DateUtils } from "DateUtils_lib";
import { newSelect } from "SqlBuilder_lib";

let contactId = vars.get("$field.CONTACT_ID");

if (contactId)
{
    let dateOfBirth = newSelect("DATEOFBIRTH")
        .from("PERSON")
        .join("CONTACT", "PERSON.PERSONID = CONTACT.PERSON_ID")
        .where("CONTACT.CONTACTID", contactId)
        .cell();

    if (dateOfBirth)
    {   // just a rough approximation
        let age = Math.floor(DateUtils.getDayDifference(dateOfBirth) / 365);
        result.string(age);
    }
}

CarDriver_entity.age.valueProcess.js

drivingExperience​

The EntityField "drivingExperience" is handled very similar to "age" (see previous sub-chapter): The driving experience can be calculated from DRIVINGLICENSEISSUEDATE, both via SQL (expression) as well as via JDito (valueProcess):

import { result } from "@aditosoftware/jdito-types";
import { SqlBuilder } from "SqlBuilder_lib";
import { SqlMaskingUtils } from "SqlMaskingUtils_lib";

var sqlUtils = new SqlMaskingUtils();
var sql = sqlUtils.yearFromDate("CURRENT_DATE")
    + " - "
    + sqlUtils.yearFromDate("DRIVINGLICENSEISSUEDATE")
    + " - "
    + SqlBuilder.caseWhen(
        "("
        + sqlUtils.monthFromDate("CURRENT_DATE")
        + " < " + sqlUtils.monthFromDate("DRIVINGLICENSEISSUEDATE")
        + "OR ("
        + sqlUtils.monthFromDate("CURRENT_DATE")
        + " = " + sqlUtils.monthFromDate("DRIVINGLICENSEISSUEDATE")
        + " AND "
        + sqlUtils.dayFromDate("CURRENT_DATE")
        + " < " + sqlUtils.dayFromDate("DRIVINGLICENSEISSUEDATE")
        + ")"
        + ")"
    )
        .then("1")
        .elseValue("0")
        .toString();
result.string(sql);

CarDriver_entity.db.drivingExperience.value.expression.js

As the driving experience should also be shown in CarDriverEdit_view, after selecting the issue date of the driving license (DRIVINGLICENSEISSUEDATE), we also insert a valueProcess:

import { result, vars } from "@aditosoftware/jdito-types";
import { DateUtils } from "DateUtils_lib";

let drivingLicenseIssueDate = vars.get("$field.DRIVINGLICENSEISSUEDATE");

if (drivingLicenseIssueDate)
{   // just a rough approximation
    let drivingExperience = Math.floor(DateUtils.getDayDifference(drivingLicenseIssueDate) / 365);
    result.string(drivingExperience);
}

CarDriver_entity.drivingExperience.valueProcess.js

parkingTicketFinesSum​

import { result } from "@aditosoftware/jdito-types";
import { newSelect } from "SqlBuilder_lib";

let parkingTicketFinesSum = newSelect("SUM(CARRESERVATION.PARKINGTICKETFINE)")
    .from("CARRESERVATION")
    .where("CARRESERVATION.CARDRIVER_ID = CARDRIVER.CARDRIVERID");

result.string(parkingTicketFinesSum);

CarDriver_entity.db.parkingTicketFinesSum.value.expression.js

A valueProcess is not required in this case, as this EntityField is not required for the EditView.

speedingFinesSum​

The calculation of EntityField "speedingFinesSum" is the same as for "parkingTicketFinesSum":

import { result } from "@aditosoftware/jdito-types";
import { newSelect } from "SqlBuilder_lib";

let speedingFinesSum = newSelect("SUM(CARRESERVATION.SPEEDINGFINE)")
    .from("CARRESERVATION")
    .where("CARRESERVATION.CARDRIVER_ID = CARDRIVER.CARDRIVERID");

result.string(speedingFinesSum);

CarDriver_entity.db.speedingFinesSum.value.expression.js

A valueProcess is not required in this case, as this EntityField is not required for the EditView.

CarReservation_entity​

mileageStart​

EntityField "mileageStart" is the mileage at return of the previous reservation of the same car:

import { result, vars } from "@aditosoftware/jdito-types";
import { newSelect, SqlBuilder } from "SqlBuilder_lib";

let mileageStartSql = newSelect("IFNULL(MAX(cr.MILEAGERETURN), 0)")
    .from("CARRESERVATION", "cr")
    .where("cr.CAR_ID = CAR.CARID")
    .and("cr.ENDDATE < CARRESERVATION.STARTDATE")
    .toString();

result.string(mileageStartSql);

CarReservation_entity.db.mileageStart.value.expression.js

In order to see the mileage at start also when creating a new dataset (as soon as a car has been selected), we need a valueProcess:

import { result, vars } from "@aditosoftware/jdito-types";
import { newSelect, SqlBuilder } from "SqlBuilder_lib";

let carId = vars.get("$field.CAR_ID");
let startDate = vars.get("$field.STARTDATE");

if (carId && startDate)
{
    let mileageStart = newSelect("MAX(MILEAGERETURN)")
        .from("CARRESERVATION")
        .where("CARRESERVATION.CAR_ID", carId)
        .and("CARRESERVATION.ENDDATE", startDate, SqlBuilder.LESS)
        .cell();

    result.string(mileageStart || 0);
}

CarReservation_entity.mileageStart.valueProcess.js