Janady13/missionmatch / internal/modules/recruiting/report_service.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / internal/modules/recruiting/report_service.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	2178e7298d0e5d9af83afa12402fd587f0ba6ed8
Branch	feature/aioe-upgrade
Path or archive member	internal/modules/recruiting/report_service.go
Original SHA-256	f33b6ecf19ca264f97809f3699bce28b4de7c773ff5afaa1ca93e242136b5fed
Git blob SHA	2ba1e30bb55d9a4c56d5e1fea8f019ab0b96fdaa
Reading-copy SHA-256	f33b6ecf19ca264f97809f3699bce28b4de7c773ff5afaa1ca93e242136b5fed
Original source ↗
Recovered source transcript

Download complete source text ↓288 lines · 10.2 KB
package recruiting

import (
	"context"
	"encoding/json"
	"strconv"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/shopspring/decimal"

	"github.com/missionworkforce/missionmatch/internal/platform/audit"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

// PeriodRow is one bucket of a time series report.
type PeriodRow struct {
	Start string `json:"start"`
	Label string `json:"label"`
	// Opened, Filled and so on are counted on the period the EVENT falls in, in the organization's
	// timezone. A requisition opened in one period and filled in another appears in both, once each,
	// which is what makes a period series add up to the totals.
	Opened      int    `json:"opened"`
	Filled      int    `json:"filled"`
	Submissions int    `json:"submissions"`
	Placements  int    `json:"placements"`
	Activities  int    `json:"activities"`
	Connects    int    `json:"connects"`
	ConnectRate string `json:"connect_rate,omitempty"`
}

type ReportView struct {
	Period   Period      `json:"period"`
	Since    string      `json:"since"`
	TimeZone string      `json:"time_zone"`
	Rows     []PeriodRow `json:"rows"`
	Totals   PeriodRow   `json:"totals"`
}

// Report builds a time series at the requested grain.
//
// Each metric is counted on the period its own event falls in, then the buckets are merged. Counting
// everything on the requisition's open date instead would be simpler and wrong: a placement in March
// against an order opened in January is March's placement, and attributing it to January makes every
// month look empty until the quarter it was sold in.
func (s *Service) Report(ctx context.Context, p Period, sinceRaw string) (ReportView, error) {
	if s.db == nil {
		return ReportView{}, ErrNoDatabase
	}
	if p == "" {
		p = PeriodMonth
	}
	if !periods[p] {
		return ReportView{}, &ValidationError{Field: "period",
			Message: "must be day, week, month, quarter or fiscal_quarter"}
	}
	since, err := optTime("since", sinceRaw)
	if err != nil {
		return ReportView{}, err
	}
	if since == nil {
		t := time.Now().UTC().AddDate(0, 0, -365)
		since = &t
	}
	out := ReportView{Period: p, Since: ts(*since), Rows: []PeriodRow{}}

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if e := tx.QueryRow(ctx, `SELECT rm_org_tz()`).Scan(&out.TimeZone); e != nil {
			return translatePG(e)
		}
		// Five independent series, each bucketed on its own event date, then full outer joined on the
		// bucket so a period with activity but no placements still appears.
		q := `
			WITH opened AS (
			  SELECT ` + periodExpr("r.opened_at", p) + ` AS b, count(*) AS n
			    FROM requisitions r WHERE r.deleted_at IS NULL AND r.opened_at >= $1 GROUP BY 1),
			filled AS (
			  SELECT ` + periodExpr("r.closed_at", p) + ` AS b, count(*) AS n
			    FROM requisitions r WHERE r.deleted_at IS NULL AND r.status = 'filled'
			     AND r.closed_at IS NOT NULL AND r.closed_at >= $1 GROUP BY 1),
			subs AS (
			  SELECT ` + periodExpr("a.created_at", p) + ` AS b, count(*) AS n
			    FROM applications a WHERE a.created_at >= $1 GROUP BY 1),
			placed AS (
			  SELECT ` + periodExpr("pl.created_at", p) + ` AS b, count(*) AS n
			    FROM placements pl WHERE pl.created_at >= $1 GROUP BY 1),
			acts AS (
			  SELECT ` + periodExpr("ra.occurred_at", p) + ` AS b,
			         count(*) AS n,
			         count(*) FILTER (WHERE ra.outcome IN ('connected','replied')) AS c
			    FROM recruiter_activities ra
			   WHERE ra.occurred_at >= $1 AND NOT ra.auto_captured GROUP BY 1)
			SELECT b,
			       COALESCE(max(o), 0), COALESCE(max(f), 0), COALESCE(max(s), 0),
			       COALESCE(max(pp), 0), COALESCE(max(an), 0), COALESCE(max(ac), 0)
			  FROM (
			    SELECT b, n AS o, NULL::bigint f, NULL::bigint s, NULL::bigint pp, NULL::bigint an, NULL::bigint ac FROM opened
			    UNION ALL SELECT b, NULL, n, NULL, NULL, NULL, NULL FROM filled
			    UNION ALL SELECT b, NULL, NULL, n, NULL, NULL, NULL FROM subs
			    UNION ALL SELECT b, NULL, NULL, NULL, n, NULL, NULL FROM placed
			    UNION ALL SELECT b, NULL, NULL, NULL, NULL, n, c FROM acts) u
			 GROUP BY b ORDER BY b ASC`
		rows, e := tx.Query(ctx, q, *since)
		if e != nil {
			return translatePG(e)
		}
		defer rows.Close()
		for rows.Next() {
			var start time.Time
			var r PeriodRow
			if e := rows.Scan(&start, &r.Opened, &r.Filled, &r.Submissions,
				&r.Placements, &r.Activities, &r.Connects); e != nil {
				return translatePG(e)
			}
			r.Start = start.Format(dateLayout)
			r.Label = periodLabel(start, p)
			r.ConnectRate = rateOf(r.Connects, r.Activities)
			out.Rows = append(out.Rows, r)

			out.Totals.Opened += r.Opened
			out.Totals.Filled += r.Filled
			out.Totals.Submissions += r.Submissions
			out.Totals.Placements += r.Placements
			out.Totals.Activities += r.Activities
			out.Totals.Connects += r.Connects
		}
		if e := rows.Err(); e != nil {
			return e
		}
		out.Totals.Label = "Total"
		out.Totals.ConnectRate = rateOf(out.Totals.Connects, out.Totals.Activities)
		return nil
	})
	if err != nil {
		return ReportView{}, err
	}
	return out, nil
}

// ExportKind names what a CSV contains.
type ExportKind string

const (
	ExportPeriods    ExportKind = "periods"
	ExportPlacements ExportKind = "placements"
	ExportPipeline   ExportKind = "pipeline"
	ExportActivity   ExportKind = "activity"
)

var exportKinds = map[ExportKind]bool{
	ExportPeriods: true, ExportPlacements: true, ExportPipeline: true, ExportActivity: true,
}

// Export renders a report as CSV and records that it happened.
//
// Every cell passes through csvCell, so a candidate name carrying a spreadsheet formula is neutralised
// before it reaches a file a client will open.
//
// The audit row is not optional bookkeeping. Export logging is a named requirement of the security
// baseline this product is built to, and an export is the moment tenant data leaves the system: it is
// precisely the event an incident review needs and the one nothing else records.
func (s *Service) Export(ctx context.Context, kind ExportKind, p Period, sinceRaw string) (string, string, error) {
	if s.db == nil {
		return "", "", ErrNoDatabase
	}
	if !exportKinds[kind] {
		return "", "", &ValidationError{Field: "kind", Message: "is not a permitted export"}
	}
	actor := actorPtr(ctx)
	if actor == nil {
		return "", "", tenant.ErrNoTenant
	}

	var body, filename string
	var rowCount int
	stamp := time.Now().UTC().Format("20060102")

	switch kind {
	case ExportPeriods:
		v, err := s.Report(ctx, p, sinceRaw)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("Period", "Start", "Opened", "Filled", "Submissions", "Placements", "Activities", "Connects", "Connect rate %")
		for _, r := range v.Rows {
			b.row(r.Label, r.Start, itoa(r.Opened), itoa(r.Filled), itoa(r.Submissions),
				itoa(r.Placements), itoa(r.Activities), itoa(r.Connects), r.ConnectRate)
		}
		b.row("Total", "", itoa(v.Totals.Opened), itoa(v.Totals.Filled), itoa(v.Totals.Submissions),
			itoa(v.Totals.Placements), itoa(v.Totals.Activities), itoa(v.Totals.Connects), v.Totals.ConnectRate)
		body, rowCount = b.String(), b.Rows()
		filename = "recruiting-" + string(p) + "-" + stamp + ".csv"

	case ExportPlacements:
		list, err := s.ListPlacements(ctx, "", 200, 0)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("Candidate", "Job order", "Client", "Status", "Type", "Start", "End",
			"Bill rate", "Pay rate", "Margin per hour", "Margin %", "Fee", "Guarantee ends", "Guarantee breached")
		for _, x := range list.Placements {
			gross, pct, fee := "", "", ""
			if x.Margin.GrossPerHour != nil {
				gross = x.Margin.GrossPerHour.String()
			}
			if x.Margin.Pct != nil {
				pct = x.Margin.Pct.String()
			}
			if x.Margin.Fee != nil {
				fee = x.Margin.Fee.String()
			}
			breached := "no"
			if x.Guarantee.Breached {
				breached = "yes"
			}
			b.row(x.CandidateName, x.RequisitionTitle, x.ClientName, x.Status, x.EmploymentType,
				x.StartDate, x.EndDate, decStr(x.BillRate), decStr(x.PayRate), gross, pct, fee,
				x.Guarantee.EndsOn, breached)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "placements-" + stamp + ".csv"

	case ExportPipeline:
		list, err := s.ListApplications(ctx, "", "", 200, 0)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("Candidate", "Job order", "Client", "Stage", "Furthest stage", "Days in stage",
			"Disposition", "Source", "Owner")
		for _, a := range list.Applications {
			b.row(a.CandidateName, a.RequisitionTitle, a.ClientName, a.Stage, a.FurthestStage,
				formatDays(a.DaysInStage), a.DispositionCode, a.SourceFirstTouch, a.OwnerName)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "pipeline-" + stamp + ".csv"

	case ExportActivity:
		list, err := s.ListActivities(ctx, ActivityFilter{Since: sinceRaw}, 200, 0)
		if err != nil {
			return "", "", err
		}
		b := newCSV()
		b.row("When", "Recruiter", "Type", "Direction", "Outcome", "Candidate", "Job order", "Note")
		for _, x := range list.Activities {
			b.row(x.OccurredAt, x.ActorName, x.Kind, x.Direction, x.Outcome,
				x.CandidateName, x.RequisitionTitle, x.Body)
		}
		body, rowCount = b.String(), b.Rows()
		filename = "activity-" + stamp + ".csv"
	}

	// Record the export. Written after the content is assembled so a failed report is not logged as a
	// successful disclosure, and the row count is included because "who exported what, and how much"
	// is the question an incident review actually asks.
	if err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		nv, _ := json.Marshal(map[string]any{
			"kind": string(kind), "period": string(p), "since": sinceRaw,
			"rows": rowCount, "bytes": len(body), "filename": filename,
		})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "export.run", ActorUserID: actor,
			EntityType: "export", EntityID: string(kind), NewValues: nv,
		})
	}); err != nil {
		return "", "", err
	}
	return body, filename, nil
}

// decStr renders an optional money value. Empty rather than "0" when absent, because a rate that was
// never agreed and a rate of zero are different facts and a spreadsheet cannot tell them apart once
// they both read zero.
func decStr(d *decimal.Decimal) string {
	if d == nil {
		return ""
	}
	return d.String()
}

// formatDays renders a day count for a spreadsheet: fixed notation, never scientific, because a cell
// reading 1.2e+02 is not a number anyone can sum.
func formatDays(f float64) string {
	return strconv.FormatFloat(f, 'f', 1, 64)
}