bullhorn-applicant-importer 0.3.5
Import applicants and their experience to MariaDB from a Bullhorn CSV dump
Requires Python: >=3.10

# bullhorn-applicant-importer

The purpose of this tool is to import candidate and job placements data from CSV files export from the Adecco US
Bullhorn/AdeccoGS database into the QAPA MariaDB database.

The tool can either operate on local CSV files (specified by the `--candidates-file` and `--placements-file` parameters)
or files pulled from Azure Storage (specified by the `--sftp-private-link` and `--sftp-azure-api-key` parameters).

The tool first reads the candidates data from the input file. Deduplication logic is applied to candidates based on
first name, last name and email and picks the record with the most recent job submission, if any, or the most recent
record otherwise.

Then placements data is read, cleaned up and pre-processed to assign a Standard Occupational Classification (SOC)
code to each placements. Placements that could not be matched to a SOC code are discarded.

Then the tool inserts and/or updates candidates and their work experience, trying to correct or discard mismatches
between emails and Adecco candidate ids, as well as matching their address with the locations known to the Qapa
database.

Candidate updates are based on the last modification date. Placements are never updated.

No changes are pushed to the database unless the `--exec` parameter is present. Otherwise the tool runs in dry-run mode.

Parameters can be read from a configuration file in the `ini` format specified by the `--config` parameter.
All parameters in the file can be overridden by the command line.

## Reading from Azure Storage

Files are expected to be stored in a container called `applicants-data` at the endpoint designated by
the `--sftp-private-link` parameter and authenticated using the `--sftp-azure-api-key` parameter.

Depending on the value of the `--environment` parameter, files will be read from a `Prod` or `Test` sub-directory.

The tool will read the first file matching the patterns `ADECCO_CANDIDATES_QAPA_<yyyymmddhhmmss>`
and `ADECCO_PLACEMENTS_QAPA__<yyyymmddhhmmss>` for candidates and placements respectively, sorted by reverse lexical
order as such as only the latest file is read.

Files are deleted from the container after processing if the `--delete-files-from-azure` parameter is present.


## Expected CSV format

The expected CSV format is:
* comma (,) as delimiter
* double-quote (") as quoting character
* UTF-8 encoded

### Candidates file format

The expected headers are:
* CandidateId (Bullhorn Id)
* FirstName (string)
* LastName (string)
* Email (string)
* Phone ((aaa)bbb-cccc)
* EmailOptOut (true/false)
* SMSOptIn (true/false)
* OnboardingStatus (string, see below)
* City (string)
* State (string)
* Zip (string)
* Latitude (float)
* Longitude (float)
* DateAdded (yyyy-MM-dd hh:mm:ss.SSS)
* DateModified (yyyy-MM-dd hh:mm:ss.SSS)
* DateMostRecentJobSubmission (yyyy-MM-dd hh:mm:ss.SSS)

Onboarding statuses eligible for import are:
* archive
* available
* called in available
* in process
* inactive
* inactive - pending authorization
* not needed at this time

### Placements file format

The expected headers are:
* CandidateId (Bullhorn Id)
* JobPlacementId (Bullhorn Id)
* IsQapaRecord (true/false)
* JobClientName (string)
* JobTitle (string)
* JobCategoryNameList (comma-separated string)
* JobSkillNameList (comma-separated string)
* JobSkillCustomText (string)
* JobPlacementDateAdded (yyyy-MM-dd hh:mm:ss.SSS)
* JobPlacementDateModified (yyyy-MM-dd hh:mm:ss.SSS)
* JobEstimatedDateStart (yyyy-MM-dd hh:mm:ss.SSS)
* JobEstimatedDateEnd (yyyy-MM-dd hh:mm:ss.SSS)

## GPT-based SOC code classification

Job titles (+ category + skill) read from the CSV are looked up in database to get the corresponding SOC code, unless
the `--skip-job-title-db-lookup` parameter is present.

Because a valid SOC code is required for placemement import, placements without a code are then processed by a GPT model
to try and guess the best matching SOC code, unless the `--skip-gpt-job-title-to-soc-conversion` parameter is present.

The GPT classification step requires access to an Azure OpenAI endpoint which has to be specified using
the `--openai-endpoint` and `--openai-apikey` parameters. A `gpt-35` model has to be deployed on the endpoint.

## Prometheus metrics

Metrics for the amount of candidates and placements imported/updated, missing locations and success/failure time
are pushed to a Prometheus gateway if the `--prometheus-push-gateway-host` and `--prometheus-push-gateway-port`
parameters are provided.

## Contributing

The Python project management tool for this application is [Rye](https://rye-up.com/). The app itself is mostly based
on the following libraries:
* [click](https://click.palletsprojects.com/)
* [pandas](https://pandas.pydata.org/)
* [peewee](https://docs.peewee-orm.com/)

### Development environment setup

* Install [Rye](https://rye-up.com/) using Homebrew or the install script provided on Rye's homepage
* Run `rye sync` at the root of the project. This will create a Python virtual environment at the project root, 
  pull the right Python interpreter version and required dependencies.

The tool requires a connection to a working MariaDB server. For local testing purpose, it is recommended to use the
environment defined in the `qapa` repository that can be started using the `qapa/startServices.sh` script.

The tool expects the DB to contain data related to trades and locations. Specifically, the following tables:
* ref_countries
* ref_geo_adm_area_level_1
* ref_geo_adm_area_level_2
* ref_geo_localities
* ref_trades

Data should be pulled from either `testing` or `prod` environments and loaded in the local MariaDB container. CSV, SQL
or any other format is up to preference, but make sure the exports contain the row ids.

### Pycharm configuration
It is recommended to mark the newly created `.venv` directory as "Excluded" to prevent Pycharm to add the whole
Python environment to the project index.

#### Project settings
In the "Settings - Project - Python interpreter" section, define the interpreter from the virtualenv Rye created as the
project interpreter.
In the "Settings - Project - Project structure" section, make sure to define the `src/` directory as a "Source folder".

#### Run configuration 
For running the application, create a new "Python" run configuration. Change "script" to "module" if necessary and
define the application entry point as `bullhorn_applicant_importer.main`. It is recommended to define the project root
as the "Working directory".

### Updating and publishing the application

Proceed to the required code changes, including increasing the version number in `pyproject.toml`, then run from the project root:
```
$ rye sync                      # re-generates requirements files and builds local "editable" build
$ rye build --clean             # builds release artefacts
$ rye publish -r pypi-internal  # pushes artefact to internal PyPi repo (https://nexus.qapa.us/#browse/browse:pypi-internal:bullhorn-applicant-importer)
```