Screenings

Screenings can be performed by an already authenticated user both on and offline.

In order to handle offline screenings and synchronise the data with Supabase a sqlite database is used locally on the user's device. This is made available through the Capacitor module @capacitor-community/sqlite

It is initiated like this:

Set up SQLiteConnection

const sqlite = new SQLiteConnection(CapacitorSQLite);

An asynchronous function is used to initiate the database before the page loads

/pages/en/screen-select.js

...

  useEffect(() => {
    initdb();
  }, []);

  const initdb = async () => {

    try {
      const ret = await sqlite.checkConnectionsConsistency();
      const isConn = (await sqlite.isConnection("db_screen")).result;
      // console.log(isConn)
      var db = null;
      if (ret.result && isConn) {
        db = await sqlite.retrieveConnection("db_screen");
      } else {
        db = await sqlite.createConnection(
          "db_screen",
          false,
          "no-encryption",
          1
        );
      }

      ....

You are then able to run SQL queries to create and read from tables:

/pages/en/screen-select.js

...

      // check if the table exists, if not create it
      let query = `
      CREATE TABLE IF NOT EXISTS screenings (
        id INTEGER PRIMARY KEY NOT NULL,
        user_id TEXT NOT NULL,
        name TEXT NOT NULL,
        role TEXT,
        gender TEXT,
        province TEXT,
        district TEXT,
        health_facility TEXT,
        sex TEXT,
        age TEXT,
        question_1 TEXT,
        answer_1 TEXT,
        question_2 TEXT,
        answer_2 TEXT,
        question_3 TEXT,
        answer_3 TEXT,
        question_4 TEXT,
        answer_4 TEXT,
        question_5 TEXT,
        answer_5 TEXT,
        question_6 TEXT,
        answer_6 TEXT,
        question_7 TEXT,
        answer_7 TEXT,
        question_8 TEXT,
        answer_8 TEXT,
        organisation TEXT,
        project INTEGER,
        type TEXT,
        country TEXT
      );
      `;
      let res = await db.execute(query);
      console.log(`res: ${JSON.stringify(res)}`);

The data stored in sqlite is then synchronised on demand when the user calls the sync function in the UI:

/pages/en/screen-select.js

...

  async function syncDataSupabase(data_record) {
    console.log(data_record);
    try {
      const dataEnter = {
        user_id: data_record.user_id,
        name: data_record.name,
        role: data_record.role,
        gender: data_record.gender,
        province: data_record.province,
        district: data_record.district,
        health_facility: data_record.health_facility,
        sex: data_record.sex,
        age: data_record.age,
        project_id: data_record.project,
        organisation: data_record.organisation,
        type: data_record.type,
        country: data_record.country,
        question_1: data_record.question_1,
        answer_1: data_record.answer_1,
        question_2: data_record.question_2,
        answer_2: data_record.answer_2,
        question_3: data_record.question_3,
        answer_3: data_record.answer_3,
        question_4: data_record.question_4,
        answer_4: data_record.answer_4,
        question_5: data_record.question_5,
        answer_5: data_record.answer_5,
        question_6: data_record.question_6,
        answer_6: data_record.answer_6,
        question_7: data_record.question_7,
        answer_7: data_record.answer_7,
        question_8: data_record.question_8,
        answer_8: data_record.answer_8,
      };
      const { data, error } = await supabase
        .from("screenings")
        .insert(dataEnter)
        .select();
      if (error) {
        throw error;
      }
    } catch (error) {
      console.log(error.message);

      ...