Recalculate Target Date and Hours from Days #972
This file contains hidden or 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: Recalculate Target Date and Hours from Days | |
| on: | |
| schedule: | |
| - cron: "*/15 * * * *" | |
| workflow_dispatch: | |
| jobs: | |
| recalc: | |
| runs-on: ubuntu-latest | |
| env: | |
| GH_TOKEN: ${{ secrets.PROJECT_PAT }} | |
| ORG: ella-system | |
| PROJECT_NUMBER: 4 | |
| DAYS_FIELD_NAME: Days | |
| START_FIELD_NAME: Issue start | |
| END_FIELD_NAME: Issue end | |
| HOURS_AVAIL_FIELD_NAME: Hours availability | |
| HOURS_FIELD_NAME: Hours | |
| steps: | |
| - name: Fetch project items and recalc dates/hours | |
| shell: bash | |
| run: | | |
| set -Eeuo pipefail | |
| echo "==> Starting recalculate-target-date-and-hours workflow" | |
| echo "UTC now: $(date -u '+%Y-%m-%dT%H:%M:%SZ')" | |
| echo "ORG=$ORG PROJECT_NUMBER=$PROJECT_NUMBER" | |
| TOTAL_ITEMS=0 | |
| ELIGIBLE_END_ITEMS=0 | |
| UPDATED_END_ITEMS=0 | |
| SKIPPED_SAME_END=0 | |
| SKIPPED_MISSING_END_FIELDS=0 | |
| ELIGIBLE_HOURS_ITEMS=0 | |
| UPDATED_HOURS_ITEMS=0 | |
| SKIPPED_SAME_HOURS=0 | |
| SKIPPED_MISSING_HOURS_FIELDS=0 | |
| ERROR_ITEMS=0 | |
| echo "==> Loading project metadata and field IDs..." | |
| PROJECT_JSON="$(gh api graphql -f query=' | |
| query($org:String!, $number:Int!) { | |
| organization(login:$org) { | |
| projectV2(number:$number) { | |
| id | |
| title | |
| fields(first:100) { | |
| nodes { | |
| ... on ProjectV2FieldCommon { id name } | |
| } | |
| } | |
| } | |
| } | |
| }' -f org="$ORG" -F number="$PROJECT_NUMBER")" | |
| PROJECT_ID="$(echo "$PROJECT_JSON" | jq -r '.data.organization.projectV2.id // empty')" | |
| PROJECT_TITLE="$(echo "$PROJECT_JSON" | jq -r '.data.organization.projectV2.title // empty')" | |
| DAYS_FIELD_ID="$(echo "$PROJECT_JSON" | jq -r --arg n "$DAYS_FIELD_NAME" '.data.organization.projectV2.fields.nodes[] | select(.name==$n) | .id' | head -n1)" | |
| START_FIELD_ID="$(echo "$PROJECT_JSON" | jq -r --arg n "$START_FIELD_NAME" '.data.organization.projectV2.fields.nodes[] | select(.name==$n) | .id' | head -n1)" | |
| END_FIELD_ID="$(echo "$PROJECT_JSON" | jq -r --arg n "$END_FIELD_NAME" '.data.organization.projectV2.fields.nodes[] | select(.name==$n) | .id' | head -n1)" | |
| HOURS_AVAIL_FIELD_ID="$(echo "$PROJECT_JSON" | jq -r --arg n "$HOURS_AVAIL_FIELD_NAME" '.data.organization.projectV2.fields.nodes[] | select(.name==$n) | .id' | head -n1)" | |
| HOURS_FIELD_ID="$(echo "$PROJECT_JSON" | jq -r --arg n "$HOURS_FIELD_NAME" '.data.organization.projectV2.fields.nodes[] | select(.name==$n) | .id' | head -n1)" | |
| echo "Project title: ${PROJECT_TITLE:-<empty>}" | |
| echo "PROJECT_ID=${PROJECT_ID:-<empty>}" | |
| echo "DAYS_FIELD_ID(${DAYS_FIELD_NAME})=${DAYS_FIELD_ID:-<empty>}" | |
| echo "START_FIELD_ID(${START_FIELD_NAME})=${START_FIELD_ID:-<empty>}" | |
| echo "END_FIELD_ID(${END_FIELD_NAME})=${END_FIELD_ID:-<empty>}" | |
| echo "HOURS_AVAIL_FIELD_ID(${HOURS_AVAIL_FIELD_NAME})=${HOURS_AVAIL_FIELD_ID:-<empty>}" | |
| echo "HOURS_FIELD_ID(${HOURS_FIELD_NAME})=${HOURS_FIELD_ID:-<empty>}" | |
| if [ -z "${PROJECT_ID:-}" ] || [ -z "${DAYS_FIELD_ID:-}" ] || [ -z "${START_FIELD_ID:-}" ] || [ -z "${END_FIELD_ID:-}" ] || [ -z "${HOURS_AVAIL_FIELD_ID:-}" ] || [ -z "${HOURS_FIELD_ID:-}" ]; then | |
| echo "::error::Missing required project/field IDs." | |
| echo "Expected field names:" | |
| echo " - $DAYS_FIELD_NAME" | |
| echo " - $START_FIELD_NAME" | |
| echo " - $END_FIELD_NAME" | |
| echo " - $HOURS_AVAIL_FIELD_NAME" | |
| echo " - $HOURS_FIELD_NAME" | |
| echo "Available project fields:" | |
| echo "$PROJECT_JSON" | jq -r '.data.organization.projectV2.fields.nodes[]?.name' | |
| exit 1 | |
| fi | |
| echo "==> Fetching project items with pagination..." | |
| AFTER="" | |
| PAGE=0 | |
| while :; do | |
| PAGE=$((PAGE + 1)) | |
| echo "---- Page $PAGE (after=${AFTER:-<none>})" | |
| if [ -n "$AFTER" ]; then | |
| ITEMS_JSON="$(gh api graphql -f query=' | |
| query($org:String!, $number:Int!, $after:String, $daysField:String!, $startField:String!, $endField:String!, $hoursAvailField:String!, $hoursField:String!) { | |
| organization(login:$org) { | |
| projectV2(number:$number) { | |
| items(first:100, after:$after) { | |
| pageInfo { hasNextPage endCursor } | |
| nodes { | |
| id | |
| daysValue: fieldValueByName(name: $daysField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| startValue: fieldValueByName(name: $startField) { | |
| __typename | |
| ... on ProjectV2ItemFieldDateValue { date } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| endValue: fieldValueByName(name: $endField) { | |
| __typename | |
| ... on ProjectV2ItemFieldDateValue { date } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| hoursAvailValue: fieldValueByName(name: $hoursAvailField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| hoursValue: fieldValueByName(name: $hoursField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| }' \ | |
| -f org="$ORG" \ | |
| -F number="$PROJECT_NUMBER" \ | |
| -f after="$AFTER" \ | |
| -f daysField="$DAYS_FIELD_NAME" \ | |
| -f startField="$START_FIELD_NAME" \ | |
| -f endField="$END_FIELD_NAME" \ | |
| -f hoursAvailField="$HOURS_AVAIL_FIELD_NAME" \ | |
| -f hoursField="$HOURS_FIELD_NAME")" | |
| else | |
| ITEMS_JSON="$(gh api graphql -f query=' | |
| query($org:String!, $number:Int!, $daysField:String!, $startField:String!, $endField:String!, $hoursAvailField:String!, $hoursField:String!) { | |
| organization(login:$org) { | |
| projectV2(number:$number) { | |
| items(first:100) { | |
| pageInfo { hasNextPage endCursor } | |
| nodes { | |
| id | |
| daysValue: fieldValueByName(name: $daysField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| startValue: fieldValueByName(name: $startField) { | |
| __typename | |
| ... on ProjectV2ItemFieldDateValue { date } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| endValue: fieldValueByName(name: $endField) { | |
| __typename | |
| ... on ProjectV2ItemFieldDateValue { date } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| hoursAvailValue: fieldValueByName(name: $hoursAvailField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| hoursValue: fieldValueByName(name: $hoursField) { | |
| __typename | |
| ... on ProjectV2ItemFieldNumberValue { number } | |
| ... on ProjectV2ItemFieldTextValue { text } | |
| } | |
| } | |
| } | |
| } | |
| } | |
| }' \ | |
| -f org="$ORG" \ | |
| -F number="$PROJECT_NUMBER" \ | |
| -f daysField="$DAYS_FIELD_NAME" \ | |
| -f startField="$START_FIELD_NAME" \ | |
| -f endField="$END_FIELD_NAME" \ | |
| -f hoursAvailField="$HOURS_AVAIL_FIELD_NAME" \ | |
| -f hoursField="$HOURS_FIELD_NAME")" | |
| fi | |
| PAGE_COUNT="$(echo "$ITEMS_JSON" | jq '.data.organization.projectV2.items.nodes | length')" | |
| echo "Items on this page: $PAGE_COUNT" | |
| while IFS= read -r ITEM_B64; do | |
| [ -z "$ITEM_B64" ] && continue | |
| TOTAL_ITEMS=$((TOTAL_ITEMS + 1)) | |
| ITEM_JSON="$(echo "$ITEM_B64" | base64 --decode)" | |
| ITEM_ID="$(echo "$ITEM_JSON" | jq -r '.id')" | |
| days_raw="$(echo "$ITEM_JSON" | jq -r ' | |
| if .daysValue == null then "" | |
| elif .daysValue.__typename == "ProjectV2ItemFieldNumberValue" then (.daysValue.number // "") | |
| elif .daysValue.__typename == "ProjectV2ItemFieldTextValue" then (.daysValue.text // "") | |
| else "" end | |
| ')" | |
| start_raw="$(echo "$ITEM_JSON" | jq -r ' | |
| if .startValue == null then "" | |
| elif .startValue.__typename == "ProjectV2ItemFieldDateValue" then (.startValue.date // "") | |
| elif .startValue.__typename == "ProjectV2ItemFieldTextValue" then (.startValue.text // "") | |
| else "" end | |
| ')" | |
| end_raw="$(echo "$ITEM_JSON" | jq -r ' | |
| if .endValue == null then "" | |
| elif .endValue.__typename == "ProjectV2ItemFieldDateValue" then (.endValue.date // "") | |
| elif .endValue.__typename == "ProjectV2ItemFieldTextValue" then (.endValue.text // "") | |
| else "" end | |
| ')" | |
| hours_avail_raw="$(echo "$ITEM_JSON" | jq -r ' | |
| if .hoursAvailValue == null then "" | |
| elif .hoursAvailValue.__typename == "ProjectV2ItemFieldNumberValue" then (.hoursAvailValue.number // "") | |
| elif .hoursAvailValue.__typename == "ProjectV2ItemFieldTextValue" then (.hoursAvailValue.text // "") | |
| else "" end | |
| ')" | |
| hours_raw="$(echo "$ITEM_JSON" | jq -r ' | |
| if .hoursValue == null then "" | |
| elif .hoursValue.__typename == "ProjectV2ItemFieldNumberValue" then (.hoursValue.number // "") | |
| elif .hoursValue.__typename == "ProjectV2ItemFieldTextValue" then (.hoursValue.text // "") | |
| else "" end | |
| ')" | |
| # Recalculate END = START + DAYS | |
| if [ -n "$days_raw" ] && [ -n "$start_raw" ]; then | |
| ELIGIBLE_END_ITEMS=$((ELIGIBLE_END_ITEMS + 1)) | |
| days_int="$(printf '%.0f' "$days_raw" 2>/dev/null || true)" | |
| if [ -z "${days_int:-}" ] || ! [[ "$days_int" =~ ^-?[0-9]+$ ]]; then | |
| echo "WARN: item=$ITEM_ID invalid days='$days_raw' (skipping end recalc)" | |
| ERROR_ITEMS=$((ERROR_ITEMS + 1)) | |
| else | |
| new_end="$(date -u -d "$start_raw +$days_int days" '+%Y-%m-%d' 2>/dev/null || true)" | |
| if [ -z "${new_end:-}" ]; then | |
| echo "WARN: item=$ITEM_ID invalid start date='$start_raw' (skipping end recalc)" | |
| ERROR_ITEMS=$((ERROR_ITEMS + 1)) | |
| else | |
| if [ "$new_end" != "$end_raw" ]; then | |
| gh api graphql -f query=' | |
| mutation($project:ID!, $item:ID!, $field:ID!, $date:Date!) { | |
| updateProjectV2ItemFieldValue(input:{ | |
| projectId:$project | |
| itemId:$item | |
| fieldId:$field | |
| value:{ date:$date } | |
| }) { | |
| projectV2Item { id } | |
| } | |
| }' \ | |
| -f project="$PROJECT_ID" \ | |
| -f item="$ITEM_ID" \ | |
| -f field="$END_FIELD_ID" \ | |
| -f date="$new_end" >/dev/null | |
| UPDATED_END_ITEMS=$((UPDATED_END_ITEMS + 1)) | |
| echo "Updated END: item=$ITEM_ID start=$start_raw days=$days_int old_end=${end_raw:-<empty>} new_end=$new_end" | |
| else | |
| SKIPPED_SAME_END=$((SKIPPED_SAME_END + 1)) | |
| fi | |
| fi | |
| fi | |
| else | |
| SKIPPED_MISSING_END_FIELDS=$((SKIPPED_MISSING_END_FIELDS + 1)) | |
| fi | |
| # Recalculate HOURS = DAYS * HOURS_AVAILABILITY (integers) | |
| if [ -n "$days_raw" ] && [ -n "$hours_avail_raw" ]; then | |
| ELIGIBLE_HOURS_ITEMS=$((ELIGIBLE_HOURS_ITEMS + 1)) | |
| days_int="$(echo "$days_raw" | tr ',' '.' | awk 'BEGIN{ok=1} /^[0-9]+([.][0]+)?$/ {print int($1); ok=0} END{if(ok) exit 1}')" | |
| if [ $? -ne 0 ]; then | |
| echo "WARN: item=$ITEM_ID invalid integer days='$days_raw' (skipping hours recalc)" | |
| ERROR_ITEMS=$((ERROR_ITEMS + 1)) | |
| else | |
| hours_avail_int="$(echo "$hours_avail_raw" | tr ',' '.' | awk 'BEGIN{ok=1} /^[0-9]+([.][0]+)?$/ {print int($1); ok=0} END{if(ok) exit 1}')" | |
| if [ $? -ne 0 ]; then | |
| echo "WARN: item=$ITEM_ID invalid integer hours_availability='$hours_avail_raw' (skipping hours recalc)" | |
| ERROR_ITEMS=$((ERROR_ITEMS + 1)) | |
| else | |
| new_hours_int=$((days_int * hours_avail_int)) | |
| current_hours_int="" | |
| if [[ "${hours_raw:-}" =~ ^[0-9]+([.][0]+)?$ ]]; then | |
| current_hours_int="$(echo "$hours_raw" | tr ',' '.' | awk '{print int($1)}')" | |
| fi | |
| if [ "$new_hours_int" != "${current_hours_int:-}" ]; then | |
| echo "Updating HOURS: item=$ITEM_ID days=$days_int hours_avail=$hours_avail_int old=${hours_raw:-<empty>} new=$new_hours_int" | |
| if ! gh api graphql -f query=' | |
| mutation($project:ID!, $item:ID!, $field:ID!, $value:Float!) { | |
| updateProjectV2ItemFieldValue(input:{ | |
| projectId:$project | |
| itemId:$item | |
| fieldId:$field | |
| value:{ number:$value } | |
| }) { | |
| projectV2Item { id } | |
| } | |
| }' \ | |
| -f project="$PROJECT_ID" \ | |
| -f item="$ITEM_ID" \ | |
| -f field="$HOURS_FIELD_ID" \ | |
| -F value="$new_hours_int" \ | |
| --jq '.data.updateProjectV2ItemFieldValue.projectV2Item.id' >/dev/null; then | |
| echo "WARN: failed updating HOURS for item=$ITEM_ID (value=$new_hours_int)" | |
| ERROR_ITEMS=$((ERROR_ITEMS + 1)) | |
| else | |
| UPDATED_HOURS_ITEMS=$((UPDATED_HOURS_ITEMS + 1)) | |
| fi | |
| else | |
| SKIPPED_SAME_HOURS=$((SKIPPED_SAME_HOURS + 1)) | |
| fi | |
| fi | |
| fi | |
| else | |
| SKIPPED_MISSING_HOURS_FIELDS=$((SKIPPED_MISSING_HOURS_FIELDS + 1)) | |
| fi | |
| done < <(echo "$ITEMS_JSON" | jq -r '.data.organization.projectV2.items.nodes[] | @base64') | |
| HAS_NEXT="$(echo "$ITEMS_JSON" | jq -r '.data.organization.projectV2.items.pageInfo.hasNextPage')" | |
| END_CURSOR="$(echo "$ITEMS_JSON" | jq -r '.data.organization.projectV2.items.pageInfo.endCursor // empty')" | |
| if [ "$HAS_NEXT" = "true" ] && [ -n "$END_CURSOR" ]; then | |
| AFTER="$END_CURSOR" | |
| else | |
| break | |
| fi | |
| done | |
| echo "==> Done" | |
| echo "TOTAL_ITEMS=$TOTAL_ITEMS" | |
| echo "ELIGIBLE_END_ITEMS=$ELIGIBLE_END_ITEMS UPDATED_END_ITEMS=$UPDATED_END_ITEMS SKIPPED_SAME_END=$SKIPPED_SAME_END SKIPPED_MISSING_END_FIELDS=$SKIPPED_MISSING_END_FIELDS" | |
| echo "ELIGIBLE_HOURS_ITEMS=$ELIGIBLE_HOURS_ITEMS UPDATED_HOURS_ITEMS=$UPDATED_HOURS_ITEMS SKIPPED_SAME_HOURS=$SKIPPED_SAME_HOURS SKIPPED_MISSING_HOURS_FIELDS=$SKIPPED_MISSING_HOURS_FIELDS" | |
| echo "ERROR_ITEMS=$ERROR_ITEMS" |