QE Statistics Notification #82
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: QE Statistics Notification | |
| on: | |
| schedule: | |
| - cron: '1 4 * * *' | |
| jobs: | |
| send-email: | |
| runs-on: techbd-sql | |
| steps: | |
| - name: Checkout repository | |
| uses: actions/checkout@v4 | |
| - name: Create .pgpass file | |
| run: | | |
| # Write the metadata comment to the .pgpass file | |
| echo "# { id: \"PROD_TECHBD_UDI_DS_PRIME\", description: \"UDI Prime database\", boundary: \"Production\" }" > ~/.pgpass | |
| # Append the database connection details to the .pgpass file | |
| echo "${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }}:${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }}:${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }}:${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }}:${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PASSWORD }}" >> ~/.pgpass | |
| # Set the .pgpass file permissions to 600 (read and write for owner only) | |
| chmod 600 ~/.pgpass | |
| - name: Set previous day's date in New York time (EST/EDT) | |
| run: | | |
| PREV_DATE=$(TZ="America/New_York" date -d "yesterday" +"%m-%d-%Y") | |
| echo "PREV_DATE=${PREV_DATE}" >> $GITHUB_ENV | |
| - name: Fetch combined data from PostgreSQL | |
| id: fetch-data-combined | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv" \ | |
| --no-align --tuples-only) | |
| echo "combined_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data combined | |
| id: format-data-combined | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.combined_data }}" | |
| echo "formatted_combined_data<<EOF" >> $GITHUB_ENV | |
| echo "Combined QE FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Fetch Rochester data from PostgreSQL | |
| id: fetch-data-rochester | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv where qe_name='grrhio'" \ | |
| --no-align --tuples-only) | |
| echo "rochester_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data Rochester | |
| id: format-data-rochester | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.rochester_data }}" | |
| echo "formatted_rochester_data<<EOF" >> $GITHUB_ENV | |
| echo "Rochester FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Fetch Healtheconnections data from PostgreSQL | |
| id: fetch-data-healtheconn | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv where qe_name='healtheconn'" \ | |
| --no-align --tuples-only) | |
| echo "healtheconn_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data Healtheconnections | |
| id: format-data-healtheconn | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.healtheconn_data }}" | |
| echo "formatted_healtheconn_data<<EOF" >> $GITHUB_ENV | |
| echo "Healtheconnections FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Fetch Healthelink data from PostgreSQL | |
| id: fetch-data-healthelink | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv where qe_name='healthelink'" \ | |
| --no-align --tuples-only) | |
| echo "healthelink_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data Healthelink | |
| id: format-data-healthelink | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.healthelink_data }}" | |
| echo "formatted_healthelink_data<<EOF" >> $GITHUB_ENV | |
| echo "Healthelink FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Fetch Healthix data from PostgreSQL | |
| id: fetch-data-healthix | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv where qe_name='healthix'" \ | |
| --no-align --tuples-only) | |
| echo "healthix_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data Healthix | |
| id: format-data-healthix | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.healthix_data }}" | |
| echo "formatted_healthix_data<<EOF" >> $GITHUB_ENV | |
| echo "Healthix FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Fetch Hixny data from PostgreSQL | |
| id: fetch-data-hixny | |
| run: | | |
| DATA=$(psql -h ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_HOST }} \ | |
| -p ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_PORT }} \ | |
| -U ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_USER }} \ | |
| -d ${{ secrets.PROD_TECHBD_UDI_DS_PRIME_NAME }} \ | |
| -c "SELECT SUM(qe_fhir_submission_count) AS tenant_submission_count, SUM(qe_fhir_pass_count) AS tenant_pass_count, SUM(qe_fhir_fail_count) AS tenant_fail_count, SUM(qe_fhir_pass_count + qe_fhir_fail_count) AS overall_response_count FROM techbd_udi_ingress.fhir_tenant_stats_view as ftsv where qe_name='hixny'" \ | |
| --no-align --tuples-only) | |
| echo "hixny_data=${DATA}" >> $GITHUB_ENV | |
| - name: Format fetched data Hixny | |
| id: format-data-hixny | |
| run: | | |
| IFS='|' read -r tenant_submission_count tenant_pass_count tenant_fail_count overall_response_count <<< "${{ env.hixny_data }}" | |
| echo "formatted_hixny_data<<EOF" >> $GITHUB_ENV | |
| echo "Hixny FHIR Submission Status:" >> $GITHUB_ENV | |
| echo "" >> $GITHUB_ENV | |
| echo "- Total Submissions: $tenant_submission_count" >> $GITHUB_ENV | |
| echo "- Successful Submissions: $tenant_pass_count" >> $GITHUB_ENV | |
| echo "- Failed Submissions: $tenant_fail_count" >> $GITHUB_ENV | |
| echo "EOF" >> $GITHUB_ENV | |
| - name: Send email | |
| uses: dawidd6/action-send-mail@v3 | |
| with: | |
| server_address: email-smtp.us-east-1.amazonaws.com | |
| server_port: 587 | |
| username: ${{ secrets.SES_SMTP_USERNAME }} | |
| password: ${{ secrets.SES_SMTP_PASSWORD }} | |
| subject: "TechBD FHIR Submission Status Notification" | |
| body: | | |
| The following are the QE FHIR Submission Status for the day ${{ env.PREV_DATE }}: | |
| ${{ env.formatted_combined_data }} | |
| ${{ env.formatted_rochester_data }} | |
| ${{ env.formatted_healtheconn_data }} | |
| ${{ env.formatted_healthelink_data }} | |
| ${{ env.formatted_healthix_data }} | |
| ${{ env.formatted_hixny_data }} | |
| content_type: "text/html" | |
| from: "Tech by Design FHIR Submission Status Notification <[email protected]>" | |
| to: [email protected],[email protected] |