referral_dataset_base.json

{
  "name": "referralDataSetbase",
  "version": "1.0",
  "tag": "",
  "description": "",
  "uses": [],
  "sources": [
    {
      "table": "amrs.patient_program",
      "alias": "t3"
    },
    {
      "table": "etl.patient_referral",
      "alias": "t4",
      "join": {
        "type": "LEFT",
        "joinCondition": "t4.patient_program_id  = t3.patient_program_id"
      }
    },
    {
      "table": "amrs.program",
      "alias": "t5",
      "join": {
        "type": "INNER",
        "joinCondition": "t3.program_id = t5.program_id"
      }
    },
    {
      "table": "amrs.location",
      "alias": "t9",
      "join": {
        "type": "INNER",
        "joinCondition": "t3.location_id = t9.location_id"
      }
    },
    {
      "table": "amrs.location",
      "alias": "location",
      "join": {
        "type": "INNER",
        "joinCondition": "t4.referred_from_location_id = location.location_id"
      }
    },
    {
      "table": "amrs.location",
      "alias": "referral_location",
      "join": {
        "type": "INNER",
        "joinCondition": "t4.referred_to_location_id = referral_location.location_id"
      }
    }
  ],
  "columns": [{
      "type": "simple_column",
      "alias": "patient_id",
      "column": "t3.patient_id"
    },
    {
      "type": "simple_column",
      "alias": "program",
      "column": "t5.name"
    },
    {
      "type": "simple_column",
      "alias": "programUuids",
      "column": "t5.uuid"
    },
    {
      "type": "simple_column",
      "alias": "program_id",
      "column": "t5.program_id"
    },
    {
      "type": "simple_column",
      "alias": "location",
      "column": "t9.name"
    },
    {
      "type": "simple_column",
      "alias": "locationUuids",
      "column": "t9.uuid"
    },
    {
      "type": "simple_column",
      "alias": "location_id",
      "column": "t9.location_id"
    },
    {
      "type": "simple_column",
      "alias": "referred_from",
      "column": "location.name"
    },
    {
      "type": "simple_column",
      "alias": "referred_to",
      "column": "referral_location.name"
    },
    {
      "type": "simple_column",
      "alias": "date_referred",
      "column": "DATE_FORMAT(t4.date_created, '%Y-%m-%d')"
    },
    {
      "type": "derived_column",
      "alias": "review_status",
      "expressionType": "simple_expression",
      "expressionOptions": {
        "expression": "if (t4.notification_status IS NULL, 'PENDING', 'DONE')"
      }
    }
  ],
  "orderBy": {
    "orderByParam": "orderByParam",
    "columns": [{
      "column": "t4.notification_status",
      "order": "asc"
    }]
  },
  "filters": {
    "conditionJoinOperator": "and",
    "conditions": [{
        "filterType": "tableColumns",
        "conditionExpression": "DATE_FORMAT(t4.date_created, '%Y-%m-%d') >= ?",
        "parameterName": "startDate"
      },
      {
        "filterType": "tableColumns",
        "conditionExpression": "DATE_FORMAT(t4.date_created, '%Y-%m-%d') <= ?",
        "parameterName": "endDate"
      },
      {
        "filterType": "tableColumns",
        "conditionExpression": "t9.uuid in (?)",
        "parameterName": "locationUuids"
      },
      {
        "filterType": "tableColumns",
        "conditionExpression": "t5.uuid in  (?)",
        "parameterName": "programUuids"
      }
    ]
  }
}