Add "inactive" flag to clients

[AlexGilleran]

Currently each instance of Carpal has a bunch of clients that are always available to have rides created for them. Clients can sometimes become "inactive" - i.e. they don't currently need the services of Hills Carpal, but they might come back in the future. In this case we don't want to delete them (in case they come back), but we also don't want to make it so that they're able to have rides created for them.

In this case we want to maintain an "inactive" flag on clients, so that they can be marked inactive, or marked active again later. This should be able to be set alongside the other fields in the client edit screen:

Given that this is a boolean, probably the most logical way to represent it would be as a checkbox, just like the current "Has Mobility Parking Sticker" field.

How to do it

Database

Because clients don't actually log into the system, none of their details are in Auth0, they're all in the database. In order to store more details about them, we'll need to create a new database column.

To make sure that the database has the correct schema everywhere the app is run, we use db-migrate. To make a new migration, you'll need to run db-migrate create add-clients-inactive-status --sql-file (docs). This will create a new blank SQL file in the migrations directory, with a filename like 2022012312312123-add-clients-inactive-status.sql. Open this up and add SQL to add a new column to the clients directory. This should be something like:

ALTER TABLE `carpal`.`clients` 
ADD COLUMN `inactive` TINYINT NOT NULL DEFAULT 0;

Then run npm run refresh-db to update your local database with the changes. When we deploy this will automatically run.

If this fails then you might need to directly connect to the database and add the column there to see what's wrong, then delete it to test the migrate script.

Add new field to types

First, add a new field for inactive to our definition for Client . Something like

export interface OptionalClient {
  id?: number;
  name?: string;
  clientDescription?: string;
  phoneNumber?: string;
  preferredDriverGender?: Gender;
  preferredCarType?: CarType;
  homeLocation?: Location;
  hasMps?: boolean;
  inactive?: boolean;
}

Add new field to client list api

We'll need to make sure that this new field comes back when we request the clients. This happens in the GET /api/clients endpoint (code here). This uses clientRespository.list to get a list of clients from the DB. We'll have to change the SQL Query used by this method to get the new column - something like:

SELECT 
        clients.id,
        clients.name,
        clients.phoneNumber,
        clients.description,
        clients.driverGender,
        clients.carType,
        clients.hasMps,
        clients.inactive, -- (change here)
        locations.point,
        locations.name AS locationName,
        locations.suburb,
        locations.postCode
-- (etc)

then in the line below where the result set is getting mapped to JS objects, add another line for our new field:

// ...
hasMps: result.hasMps,
inactive: result.inactive,
homeLocation: {
// ...

At this point if you run the app and look at the request made when you look at the clients screen, it should now be returning inactive: false for all the clients.

Add new field to client create api

We'll need to be able to set the new field when creating clients too. Clients are created with PUT /api/clients (code here), which calls clientRepository.create. You'll need to change the SQL so that its inserting the new column, something like:

let query = `
        INSERT INTO ${this.dbName}.clients(
          -- etc
          hasMps,
          inactive
        ) VALUES (
        ${[
          // etc
          client.hasMps ? 'true' : 'false',
          client.inactive ? 'true' : 'false',
        ].join(',')})`;

Add new field to client update api

We need to make sure this field can be set too. Client updates are done with PUT /api/clients/{clientId} (code here), which calls clientRepository.update.

We need to add our new field to the SQL, something like:

    let query = `
      UPDATE ${this.dbName}.clients AS clients
        INNER JOIN ${this.dbName}.locations AS locations
        ON clients.homeLocation = locations.id
      SET
        -- ...
        clients.hasMps = ${escape(client.hasMps)},
        clients.inactive = ${escape(client.inactive)},
        -- ...
      WHERE
        clients.id = ${escape(id)};
    `;

Allow client list to be filtered

Previously we added the extra field to the client list API, we also need to make it so that it can be filtered to exclude inactive clients (for the ride creation screen).

Once again, the code for this endpoint is here.

We'll need to add the ability for it to accept an inactive query parameter. So it'll look something like:

 case 'GET':
          const inactive = typeof req.query.inactive !== 'undefined' && req.query.inactive === 'true' ? true : false;
          const clients = await clientRepository.list(connection, inactive);
          res.status(200).json(clients);

          break;

Then we'll need to change the clientRepository.list function to accept this parameter:

  async list(inactive?: boolean, connection): Promise<Client[]> {

then change the SQL to use it:

const query = `
  SELECT -- etc
  FROM --etc
  ${typeof inactive !== 'undefined' ? `WHERE inactive = ${inactive ? '1' : '0'}` : ''}
  ORDER BY --etc
`

Front-End: Clients form

Now we'll change the clients form to show and set this new param.

The code for the form is here. You can see an existing checkbox for hasMps here - copy it and change it so that instead of hasMps it's displaying/setting inactive, and change the id and label so that it's displaying "Inactive" too.

You'll also need to change the default client so that it has inactive: false and updateClientsWithCurrent so that it copies across the inactive value.

Front-End: Use filtering in clients drop-down

In the ride creation/edit screen, we get the list of clients here. Add ?inactive=false in order to make sure it only displays active clients.