Pipeline report

COEVERA_BASE="https://{region}.api.coevera.com/api/v100/rest/spaces/{space_id}"

const base = `https://{region}.api.coevera.com/api/v100/rest/spaces/{space_id}`;
const auth = Buffer.from(`${env.COEVERA_API_KEY}:${env.COEVERA_API_SECRET}`).toString("base64");

async function coevera(path: string) {
  const res = await fetch(`${base}${path}`, { headers: { Authorization: `Basic ${auth}` } });
  const body = await res.json();
  if (!res.ok) throw new Error(`HTTP ${res.status} ${body.name}: ${String(body.message).trim()}`);
  return body;
}

BASE = "https://{region}.api.coevera.com/api/v100/rest/spaces/{space_id}"
session = requests.Session()
session.auth = (os.environ["COEVERA_API_KEY"], os.environ["COEVERA_API_SECRET"])


def coevera(path, params=None):
    response = session.get(f"{BASE}{path}", params=params, timeout=30)
    body = response.json()
    if not response.ok:
        sys.exit(f"HTTP {response.status_code} {body.get('name')}: {str(body.get('message')).strip()}")
    return body

COEVERA_GRAPHQL="https://{region}.api.coevera.com/api/v100/app/space/{space_id}/graphql/public"

# gql: POST the GraphQL request read from stdin. GraphQL reports errors in the body, often
# with HTTP 200, so fail on those too.
gql() {
  local response
  response=$(curl -sS --fail-with-body "$COEVERA_GRAPHQL" \
    -u "$COEVERA_API_KEY:$COEVERA_API_SECRET" \
    -H "Content-Type: application/json" \
    -d @-)
  if jq -e '.errors' <<<"$response" >/dev/null; then
    echo "$response" >&2
    return 1
  fi
  echo "$response"
}

const endpoint = `https://{region}.api.coevera.com/api/v100/app/space/{space_id}/graphql/public`;
const auth = Buffer.from(`${env.COEVERA_API_KEY}:${env.COEVERA_API_SECRET}`).toString("base64");

// gql: one GraphQL request. GraphQL reports errors in the body, often with HTTP 200.
async function gql(query: string, variables: object = {}) {
  const res = await fetch(endpoint, {
    method: "POST",
    headers: { Authorization: `Basic ${auth}`, "Content-Type": "application/json" },
    body: JSON.stringify({ query, variables }),
  });
  const body = await res.json();
  if (!res.ok || body.errors) throw new Error(`HTTP ${res.status}: ${JSON.stringify(body.errors ?? body)}`);
  return body.data;
}

ENDPOINT = (
    "https://{region}.api.coevera.com/api/v100/app/space/"
    "{space_id}/graphql/public"
)
session = requests.Session()
session.auth = (os.environ["COEVERA_API_KEY"], os.environ["COEVERA_API_SECRET"])


def gql(query, variables=None):
    """One GraphQL request. GraphQL reports errors in the body, often with HTTP 200."""
    response = session.post(ENDPOINT, json={"query": query, "variables": variables or {}}, timeout=30)
    body = response.json()
    if not response.ok or body.get("errors"):
        sys.exit(f"HTTP {response.status_code}: {json.dumps(body.get('errors', body))}")
    return body["data"]

# The pipeline's steps in order, with the probability (percent) of each.
steps=$(curl -sS --fail-with-body -G "$COEVERA_BASE/entities/Steps" \
  --data-urlencode "filter[pipeline.name]=Default pipeline" \
  -d order-by=sort_order -d load-only=id,name,percent \
  -u "$COEVERA_API_KEY:$COEVERA_API_SECRET")
echo "$steps"
STEP_IDS=$(jq -er '[.data[].id] | join(",")' <<<"$steps")

// The pipeline's steps in order, with the probability (percent) of each.
const steps = await coevera(
  `/entities/Steps?${new URLSearchParams({ "filter[pipeline.name]": "Default pipeline", "order-by": "sort_order", "load-only": "id,name,percent" })}`,
);
if (!steps.data.length) throw new Error("No pipeline with that name, or it has no steps.");
console.log("Steps:", steps.data.map((s: { name: string }) => s.name).join(", "));

# The pipeline's steps in order, with the probability (percent) of each.
steps = coevera(
    "/entities/Steps",
    {"filter[pipeline.name]": "Default pipeline", "order-by": "sort_order", "load-only": "id,name,percent"},
)["data"]
if not steps:
    sys.exit("No pipeline with that name, or it has no steps.")
print("Steps:", ", ".join(step["name"] for step in steps))

# The pipeline's steps in order, with the probability (percent) of each.
steps=$(jq -n '{
  query: "{ entities { step { getByCriteria(filter: { pipeline: { name: { eq: [\"Default pipeline\"] } } }, orderBy: [{ sortOrder: Asc }]) { edges { node { id name percent } } } } } }"
}' | gql)
echo "$steps"
STEPS=$(jq -c '[.data.entities.step.getByCriteria.edges[].node]' <<<"$steps")

// The pipeline's steps in order, with the probability (percent) of each.
const stepsResult = await gql(`{
  entities {
    step {
      getByCriteria(filter: { pipeline: { name: { eq: ["Default pipeline"] } } }, orderBy: [{ sortOrder: Asc }]) {
        edges { node { id name percent } }
      }
    }
  }
}`);
const steps: { id: string; name: string; percent: number }[] = stepsResult.entities.step.getByCriteria.edges.map(
  (e: { node: { id: string; name: string; percent: number } }) => e.node,
);
if (!steps.length) throw new Error("No pipeline with that name, or it has no steps.");
console.log("Steps:", steps.map((s) => s.name).join(", "));

# The pipeline's steps in order, with the probability (percent) of each.
steps_result = gql("""
{
  entities {
    step {
      getByCriteria(filter: { pipeline: { name: { eq: ["Default pipeline"] } } }, orderBy: [{ sortOrder: Asc }]) {
        edges { node { id name percent } }
      }
    }
  }
}
""")
steps = [edge["node"] for edge in steps_result["entities"]["step"]["getByCriteria"]["edges"]]
if not steps:
    sys.exit("No pipeline with that name, or it has no steps.")
print("Steps:", ", ".join(step["name"] for step in steps))

# Opportunities in those steps: a raw comma between the ids is an OR. Step and account come
# expanded, and load-only keeps the rows small. Two per page here, so the cursor shows.
rows='[]'
cursor=""
while :; do
  # --data-urlencode encodes the cursor: a raw "+" in it would arrive as a space.
  page=$(curl -sS --fail-with-body -G "$COEVERA_BASE/entities/Opportunities" \
    -d "filter[step_id]=$STEP_IDS" \
    -d order-by=closing_date -d first=2 -d expand=step,primary_account \
    -d load-only=id,name,closing_date,status,value,step.name,primary_account.name \
    ${cursor:+--data-urlencode "after=$cursor"} \
    -u "$COEVERA_API_KEY:$COEVERA_API_SECRET")
  echo "$page"
  rows=$(jq -c --argjson rows "$rows" '$rows + .data' <<<"$page")
  next=$(jq -r 'if .page_info.has_next_page then .page_info.end_cursor else "" end' <<<"$page")
  # An unchanged cursor would ask for the same page again: stop rather than loop forever.
  if [ -z "$next" ] || [ "$next" = "$cursor" ]; then break; fi
  cursor="$next"
done

// Opportunities in those steps: a raw comma between the ids is an OR (%2C, which
// URLSearchParams sends, would be a literal comma). Step and account come expanded, and
// load-only keeps the rows small. Two per page here, so the cursor shows; send up to 100.
const stepIds = steps.data.map((s: { id: string }) => s.id).join(",");
const query =
  `filter[step_id]=${stepIds}&order-by=closing_date&first=2&expand=step,primary_account` +
  "&load-only=id,name,closing_date,status,value,step.name,primary_account.name";
interface Row {
  name: string;
  closing_date: string;
  value: { base_value: number } | null;
  step: { name: string } | null;
  primary_account: { name: string } | null;
}
const rows: Row[] = [];
let cursor: string | null = null;
do {
  // encodeURIComponent: a raw "+" in the cursor would arrive as a space.
  const page = await coevera(`/entities/Opportunities?${query}${cursor ? `&after=${encodeURIComponent(cursor)}` : ""}`);
  rows.push(...page.data);
  const next: string | null = page.page_info.has_next_page ? page.page_info.end_cursor : null;
  // An unchanged cursor would ask for the same page again: stop rather than loop forever.
  cursor = next === cursor ? null : next;
} while (cursor);
console.log(rows.length, "opportunities");

# Opportunities in those steps: a raw comma between the ids is an OR. requests would send the
# comma as %2C, a literal comma, so the filter goes into the path by hand. Step and account come
# expanded, and load-only keeps the rows small. Two per page here, so the cursor shows.
step_ids = ",".join(step["id"] for step in steps)
params = {
    "order-by": "closing_date",
    "first": 2,
    "expand": "step,primary_account",
    "load-only": "id,name,closing_date,status,value,step.name,primary_account.name",
}
rows = []
cursor = None
while True:
    # requests encodes the cursor: a raw "+" in it would arrive as a space.
    page = coevera(f"/entities/Opportunities?filter[step_id]={step_ids}", {**params, "after": cursor})
    rows.extend(page["data"])
    next_cursor = page["page_info"]["end_cursor"] if page["page_info"]["has_next_page"] else None
    # An unchanged cursor would ask for the same page again: stop rather than loop forever.
    if not next_cursor or next_cursor == cursor:
        break
    cursor = next_cursor
print(len(rows), "opportunities")

# Opportunities in those steps, with step and account in the same query. Two per page here,
# so the cursor shows; ask for up to 100.
rows='[]'
cursor=""
while :; do
  page=$(jq -n --argjson steps "$STEPS" --arg after "$cursor" '{
    query: "query Page($steps: [String]!, $after: String) { entities { opportunity { getByCriteria(first: 2, after: $after, filter: { stepId: { eq: $steps } }, orderBy: [{ closingDate: Asc }]) { totalCount pageInfo { hasNextPage endCursor } edges { node { name closingDate status value { baseValue } step { name } primaryAccount { name } } } } } } }",
    variables: { steps: [$steps[].id], after: (if $after == "" then null else $after end) }
  }' | gql)
  echo "$page"
  rows=$(jq -c --argjson rows "$rows" '$rows + [.data.entities.opportunity.getByCriteria.edges[].node]' <<<"$page")
  next=$(jq -r '.data.entities.opportunity.getByCriteria.pageInfo | if .hasNextPage then .endCursor else "" end' <<<"$page")
  # An unchanged cursor would ask for the same page again: stop rather than loop forever.
  if [ -z "$next" ] || [ "$next" = "$cursor" ]; then break; fi
  cursor="$next"
done

// Opportunities in those steps, with step and account in the same query. Two per page here,
// so the cursor shows; ask for up to 100.
interface Row {
  name: string;
  closingDate: string;
  status: string;
  value: { baseValue: number } | null;
  step: { name: string } | null;
  primaryAccount: { name: string } | null;
}
const PAGE = `query Page($steps: [String]!, $after: String) {
  entities {
    opportunity {
      getByCriteria(first: 2, after: $after, filter: { stepId: { eq: $steps } }, orderBy: [{ closingDate: Asc }]) {
        totalCount
        pageInfo { hasNextPage endCursor }
        edges { node { name closingDate status value { baseValue } step { name } primaryAccount { name } } }
      }
    }
  }
}`;
const rows: Row[] = [];
let cursor: string | null = null;
do {
  const data = await gql(PAGE, { steps: steps.map((s) => s.id), after: cursor });
  const page = data.entities.opportunity.getByCriteria;
  rows.push(...page.edges.map((e: { node: Row }) => e.node));
  const next: string | null = page.pageInfo.hasNextPage ? page.pageInfo.endCursor : null;
  // An unchanged cursor would ask for the same page again: stop rather than loop forever.
  cursor = next === cursor ? null : next;
} while (cursor);
console.log(rows.length, "opportunities");

# Opportunities in those steps, with step and account in the same query. Two per page here,
# so the cursor shows; ask for up to 100.
PAGE = """
query Page($steps: [String]!, $after: String) {
  entities {
    opportunity {
      getByCriteria(first: 2, after: $after, filter: { stepId: { eq: $steps } }, orderBy: [{ closingDate: Asc }]) {
        totalCount
        pageInfo { hasNextPage endCursor }
        edges { node { name closingDate status value { baseValue } step { name } primaryAccount { name } } }
      }
    }
  }
}
"""
rows = []
cursor = None
while True:
    page = gql(PAGE, {"steps": [step["id"] for step in steps], "after": cursor})["entities"]["opportunity"]["getByCriteria"]
    rows.extend(edge["node"] for edge in page["edges"])
    next_cursor = page["pageInfo"]["endCursor"] if page["pageInfo"]["hasNextPage"] else None
    # An unchanged cursor would ask for the same page again: stop rather than loop forever.
    if not next_cursor or next_cursor == cursor:
        break
    cursor = next_cursor
print(len(rows), "opportunities")

# Per step: how many, their value, and the value weighted by the step's probability.
# An expanded relation is an object; without expand, the API sends its URL instead (hence .name?).
jq -r --argjson rows "$rows" '.data[] as $step
  | [$rows[] | select(.step.name? == $step.name)] as $in
  | ([$in[] | .value.base_value // 0] | add // 0) as $value
  | "\($step.name)  \($in | length)  value \($value)  weighted \($value * $step.percent / 100)",
    ($in[] | "  \(.closing_date)  \(.name) (\(.primary_account.name? // ""))")' <<<"$steps"

// Per step: how many, their value, and the value weighted by the step's probability.
for (const step of steps.data) {
  const inStep = rows.filter((o) => o.step?.name === step.name);
  const value = inStep.reduce((sum, o) => sum + (o.value?.base_value ?? 0), 0);
  console.log(`${step.name.padEnd(10)} ${String(inStep.length).padStart(3)}  value ${value}  weighted ${(value * step.percent) / 100}`);
  for (const o of inStep) console.log(`  ${o.closing_date}  ${o.name} (${o.primary_account?.name})`);
}

# Per step: how many, their value, and the value weighted by the step's probability.


def name_of(relation):
    """An expanded relation is an object; without expand, the API sends its URL instead."""
    return relation.get("name") if isinstance(relation, dict) else None


for step in steps:
    in_step = [o for o in rows if name_of(o.get("step")) == step["name"]]
    value = sum((o.get("value") or {}).get("base_value") or 0 for o in in_step)
    print(f"{step['name']:<10} {len(in_step):>3}  value {value}  weighted {value * step['percent'] / 100}")
    for o in in_step:
        print(f"  {o['closing_date']}  {o['name']} ({name_of(o.get('primary_account'))})")

# Per step: how many, their value, and the value weighted by the step's probability.
jq -rn --argjson steps "$STEPS" --argjson rows "$rows" '$steps[] as $step
  | [$rows[] | select(.step.name == $step.name)] as $in
  | ([$in[] | .value.baseValue // 0] | add // 0) as $value
  | "\($step.name)  \($in | length)  value \($value)  weighted \($value * $step.percent / 100)",
    ($in[] | "  \(.closingDate)  \(.name) (\(.primaryAccount.name), \(.status))")'

// Per step: how many, their value, and the value weighted by the step's probability.
for (const step of steps) {
  const inStep = rows.filter((o) => o.step?.name === step.name);
  const value = inStep.reduce((sum, o) => sum + (o.value?.baseValue ?? 0), 0);
  console.log(`${step.name.padEnd(10)} ${String(inStep.length).padStart(3)}  value ${value}  weighted ${(value * step.percent) / 100}`);
  for (const o of inStep) console.log(`  ${o.closingDate}  ${o.name} (${o.primaryAccount?.name}, ${o.status})`);
}

# Per step: how many, their value, and the value weighted by the step's probability.
for step in steps:
    in_step = [o for o in rows if (o.get("step") or {}).get("name") == step["name"]]
    value = sum((o.get("value") or {}).get("baseValue") or 0 for o in in_step)
    print(f"{step['name']:<10} {len(in_step):>3}  value {value}  weighted {value * step['percent'] / 100}")
    for o in in_step:
        account = (o.get("primaryAccount") or {}).get("name")
        print(f"  {o['closingDate']}  {o['name']} ({account}, {o['status']})")