package analytics import ( _ "embed" "encoding/json" "fmt" "strings" ) // JoinStep defines a single JOIN in a cross-object report type. type JoinStep struct { Table string // Physical table name (e.g. "campaignmember") Alias string // SQL alias (e.g. "cm") JoinType string // "INNER JOIN" or "LEFT JOIN" OnLeft string // e.g. "cm.CampaignId" OnRight string // e.g. "c.Id" } // QualifiedColumn maps an SF report alias to a specific table.column. type QualifiedColumn struct { TableAlias string // SQL alias (e.g. "c" for campaign) Column string // Physical column name (e.g. "Name") // ResultAlias controls the row key returned from raw SQL. // Leave empty to derive a stable alias from the report column reference. ResultAlias string // SelectExpr is an optional hardcoded SQL expression for display columns // that cannot be read from a single physical column. SelectExpr string } // JoinTemplate defines the complete JOIN structure for a cross-object report type. type JoinTemplate struct { PrimaryTable string // Physical table name (e.g. "campaign") PrimaryAlias string // SQL alias (e.g. "c") Joins []JoinStep // Columns maps SF SCREAMING_SNAKE aliases AND dot-notation refs to qualified columns. // Keys are case-sensitive as they appear in report definitions. Columns map[string]QualifiedColumn } //go:embed report_join_templates.standard.json var embeddedStandardJoinTemplatesJSON []byte // resolveQualifiedColumn resolves an SF column alias to a table-qualified column // using the JOIN template. Returns (tableAlias, columnName, true) if found. // Falls back to the primary table if not found in the mapping. func resolveQualifiedColumn(sfAlias string, tmpl *JoinTemplate) (tableAlias string, columnName string, found bool) { qc, found := resolveQualifiedColumnDef(sfAlias, tmpl) if !found { return "", "", false } // Decorate-installed projection entries (SelectExpr set, no // TableAlias/Column) are valid for the SELECT projection path via // joinSelectExpression but cannot supply a bare (alias, column) for // WHERE / GROUP BY — callers that need that tuple should treat them as // unresolved so the filter / grouping is skipped instead of emitting // ``.`` and hitting MySQL 1054. if qc.SelectExpr != "" && qc.TableAlias == "" && qc.Column == "" { return "", "", false } return qc.TableAlias, qc.Column, true } func resolveQualifiedColumnDef(sfAlias string, tmpl *JoinTemplate) (QualifiedColumn, bool) { // Direct lookup if qc, ok := tmpl.Columns[sfAlias]; ok { return qc, true } // Try dot-notation stripping: "Lead.Region__c" → look up "Lead.Region__c" first (done above), // then try just "Region__c" on the object matching the prefix. if idx := strings.Index(sfAlias, "."); idx >= 0 { prefix := sfAlias[:idx] field := sfAlias[idx+1:] // 3-segment cross-object paths (e.g. "Campaign.Parent.Name") cannot be // resolved against a flat join template — bail out and let the caller // emit NULL / skip. Without this guard the field below would contain // an embedded dot and be emitted as an invalid SQL column name. if strings.Contains(field, ".") { return QualifiedColumn{}, false } // Find the join step whose table name matches the prefix (case-insensitive) if strings.EqualFold(prefix, tmpl.PrimaryTable) || strings.EqualFold(prefix, tmpl.PrimaryAlias) { return QualifiedColumn{TableAlias: tmpl.PrimaryAlias, Column: field}, true } for _, j := range tmpl.Joins { if strings.EqualFold(prefix, j.Table) || strings.EqualFold(prefix, j.Alias) { return QualifiedColumn{TableAlias: j.Alias, Column: field}, true } } // Prefix didn't match any table — try the field part as a regular alias if qc, ok := tmpl.Columns[field]; ok { return qc, true } } // Not found — caller should fall back to resolveSFColumn on the primary table return QualifiedColumn{}, false } func userDisplaySelectExpr(alias string) string { return fmt.Sprintf( "COALESCE(NULLIF(TRIM(CONCAT(COALESCE(`%s`.`FirstName`, ''), ' ', COALESCE(`%s`.`LastName`, ''))), ''), NULLIF(`%s`.`Username`, ''), `%s`.`Id`)", alias, alias, alias, alias, ) } // standardJoinTemplates maps SF report type identifiers to their JOIN definitions. // Standard report types are built-in to Salesforce and cannot be exported via Metadata API. var standardJoinTemplates = func() map[string]*JoinTemplate { templates := map[string]*JoinTemplate{ // ────────────────────────────────────────────────────────────────────── // CampaignLeadLifetime: Campaign → CampaignMember → Lead → Opportunity // 681 reports use this type. // ────────────────────────────────────────────────────────────────────── "CampaignLeadLifetime": { PrimaryTable: "campaign", PrimaryAlias: "c", Joins: []JoinStep{ {Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"}, {Table: "lead", Alias: "l", JoinType: "INNER JOIN", OnLeft: "l.Id", OnRight: "cm.LeadId"}, {Table: "opportunity", Alias: "o", JoinType: "LEFT JOIN", OnLeft: "o.Id", OnRight: "l.ConvertedOpportunityId"}, }, Columns: map[string]QualifiedColumn{ // Campaign fields "CAMPAIGN_NAME": {TableAlias: "c", Column: "Name"}, "CAMPAIGN_CREATED_DATE": {TableAlias: "c", Column: "CreatedDate"}, "CAMPAIGN_PARENT_NAME": {TableAlias: "c", Column: "ParentId"}, // TODO: needs parent Campaign join "CDF1": {TableAlias: "c", Column: "StartDate"}, // CampaignMember fields "MEMBER_STATUS": {TableAlias: "cm", Column: "Status"}, "MEMBER_CREATED_DATE": {TableAlias: "cm", Column: "CreatedDate"}, // Lead fields "FIRST_NAME": {TableAlias: "l", Column: "FirstName"}, "LAST_NAME": {TableAlias: "l", Column: "LastName"}, "EMAIL": {TableAlias: "l", Column: "Email"}, "PHONE": {TableAlias: "l", Column: "Phone"}, "MOBILE_PHONE": {TableAlias: "l", Column: "MobilePhone"}, "TITLE": {TableAlias: "l", Column: "Title"}, "COMPANY_ACCT": {TableAlias: "l", Column: "Company"}, "LEAD_SOURCE": {TableAlias: "l", Column: "LeadSource"}, "LEAD_OWNER": {TableAlias: "l", Column: "OwnerId"}, "STATUS": {TableAlias: "l", Column: "Status"}, "INDUSTRY": {TableAlias: "l", Column: "Industry"}, "ANNUAL_REVENUE": {TableAlias: "l", Column: "AnnualRevenue"}, "CREATED_DATE": {TableAlias: "l", Column: "CreatedDate"}, "LAST_UPDATE": {TableAlias: "l", Column: "LastModifiedDate"}, "CONVERTED_DATE": {TableAlias: "l", Column: "ConvertedDate"}, // Opportunity fields "OPP_NAME": {TableAlias: "o", Column: "Name"}, "OPP_ACCT_NAME": {TableAlias: "o", Column: "AccountId"}, // TODO: needs Account join for name "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, }, }, // ────────────────────────────────────────────────────────────────────── // CampaignWithCampaignMembers: Campaign → CampaignMember // 640 reports. // ────────────────────────────────────────────────────────────────────── "CampaignWithCampaignMembers": { PrimaryTable: "campaign", PrimaryAlias: "c", Joins: []JoinStep{ {Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"}, {Table: "campaign", Alias: "c_parent", JoinType: "LEFT JOIN", OnLeft: "c_parent.Id", OnRight: "c.ParentId"}, }, Columns: map[string]QualifiedColumn{ "CAMPAIGN_NAME": {TableAlias: "c", Column: "Name"}, "CAMPAIGN_PARENT_NAME": {TableAlias: "c_parent", Column: "Name"}, "CAMPAIGN_CREATED_DATE": {TableAlias: "c", Column: "CreatedDate"}, "MEMBER_STATUS": {TableAlias: "cm", Column: "Status"}, "MEMBER_CREATED_DATE": {TableAlias: "cm", Column: "CreatedDate"}, "MEMBER_LAST_UPDATE": {TableAlias: "cm", Column: "LastModifiedDate"}, "MEMBER_FIRST_RESPONDED_DATE": {TableAlias: "cm", Column: "FirstRespondedDate"}, "MEMBER_TYPE": {TableAlias: "cm", Column: "Type"}, "MEMBER_ID": {TableAlias: "cm", Column: "Id"}, "HAS_RESPONDED": {TableAlias: "cm", Column: "HasResponded"}, "FIRST_NAME": {TableAlias: "cm", Column: "FirstName"}, "LAST_NAME": {TableAlias: "cm", Column: "LastName"}, "EMAIL": {TableAlias: "cm", Column: "Email"}, "PHONE": {TableAlias: "cm", Column: "Phone"}, "MOBILE_PHONE": {TableAlias: "cm", Column: "MobilePhone"}, "TITLE": {TableAlias: "cm", Column: "Title"}, "COMPANY_ACCT": {TableAlias: "cm", Column: "CompanyOrAccount"}, "COMPANY": {TableAlias: "cm", Column: "CompanyOrAccount"}, "LEAD_SOURCE": {TableAlias: "cm", Column: "LeadSource"}, "LC_OWNER": {TableAlias: "cm", Column: "LeadOrContactOwnerId"}, "OWNER": {TableAlias: "cm", Column: "OwnerId"}, "CITY": {TableAlias: "cm", Column: "City"}, "STATE": {TableAlias: "cm", Column: "State"}, "ZIP": {TableAlias: "cm", Column: "PostalCode"}, "COUNTRY": {TableAlias: "cm", Column: "Country"}, "COUNTRY_CODE": {TableAlias: "cm", Column: "Country"}, "LEAD_OR_CONTACT_ID": {TableAlias: "cm", Column: "LeadOrContactId"}, }, }, // ────────────────────────────────────────────────────────────────────── // OpportunityLead: Opportunity ← Lead (via ConvertedOpportunityId) // 636 reports. // ────────────────────────────────────────────────────────────────────── "OpportunityLead": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "lead", Alias: "l", JoinType: "LEFT JOIN", OnLeft: "l.ConvertedOpportunityId", OnRight: "o.Id"}, {Table: "recordtype", Alias: "o_rt", JoinType: "LEFT JOIN", OnLeft: "o_rt.Id", OnRight: "o.RecordTypeId"}, }, Columns: map[string]QualifiedColumn{ // Opportunity fields "OPP_NAME": {TableAlias: "o", Column: "Name"}, "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "OPP_ACCT_NAME": {TableAlias: "o", Column: "AccountId"}, "OPP_OWNER": {TableAlias: "o", Column: "OwnerId"}, "OPP_CREATED_DATE": {TableAlias: "o", Column: "CreatedDate"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, // LEAD_SOURCE in an OpportunityLead report refers to the Lead's // LeadSource, not the Opportunity's. Map to the lead side so // filters like "LEAD_SOURCE equals 'Direct Traffic'" match the // converted lead rather than the opportunity — this is the SF // column the report builder exposes as "Lead Source" under // the Lead folder. "LEAD_SOURCE": {TableAlias: "l", Column: "LeadSource"}, "CREATED_DATE": {TableAlias: "o", Column: "CreatedDate"}, "PROBABILITY": {TableAlias: "o", Column: "Probability"}, "ACCOUNT_NAME": {TableAlias: "o", Column: "AccountId"}, "OPP_RECORDTYPE": {TableAlias: "o_rt", Column: "Name"}, // Lead fields "FIRST_NAME": {TableAlias: "l", Column: "FirstName"}, "LAST_NAME": {TableAlias: "l", Column: "LastName"}, "COMPANY_ACCT": {TableAlias: "l", Column: "Company"}, "LEAD_OWNER": {TableAlias: "l", Column: "OwnerId"}, "LEAD_ID": {TableAlias: "l", Column: "Id"}, "EMAIL": {TableAlias: "l", Column: "Email"}, "TITLE": {TableAlias: "l", Column: "Title"}, "STATUS": {TableAlias: "l", Column: "Status"}, "INDUSTRY": {TableAlias: "l", Column: "Industry"}, "CONVERTED": {TableAlias: "l", Column: "IsConverted"}, "CONVERTED_DATE": {TableAlias: "l", Column: "ConvertedDate"}, "CON_NAME": {TableAlias: "l", Column: "ConvertedContactId"}, "LAST_UPDATE": {TableAlias: "l", Column: "LastModifiedDate"}, "COUNTRY_CODE": {TableAlias: "l", Column: "Country"}, "STATE_CODE": {TableAlias: "l", Column: "State"}, }, }, // ────────────────────────────────────────────────────────────────────── // CampaignLead: Campaign → CampaignMember → Lead // 239 reports. // ────────────────────────────────────────────────────────────────────── "CampaignLead": { PrimaryTable: "campaign", PrimaryAlias: "c", Joins: []JoinStep{ {Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"}, {Table: "lead", Alias: "l", JoinType: "INNER JOIN", OnLeft: "l.Id", OnRight: "cm.LeadId"}, }, Columns: map[string]QualifiedColumn{ // Campaign fields "CAMPAIGN_NAME": {TableAlias: "c", Column: "Name"}, "CAMPAIGN_CREATED_DATE": {TableAlias: "c", Column: "CreatedDate"}, // CampaignMember fields "MEMBER_STATUS": {TableAlias: "cm", Column: "Status"}, "MEMBER_CREATED_DATE": {TableAlias: "cm", Column: "CreatedDate"}, // Lead fields "FIRST_NAME": {TableAlias: "l", Column: "FirstName"}, "LAST_NAME": {TableAlias: "l", Column: "LastName"}, "EMAIL": {TableAlias: "l", Column: "Email"}, "PHONE": {TableAlias: "l", Column: "Phone"}, "TITLE": {TableAlias: "l", Column: "Title"}, "COMPANY_ACCT": {TableAlias: "l", Column: "Company"}, "COMPANY": {TableAlias: "l", Column: "Company"}, "LEAD_SOURCE": {TableAlias: "l", Column: "LeadSource"}, "LEAD_OWNER": {TableAlias: "l", Column: "OwnerId"}, "LEAD_CREATED_DATE": {TableAlias: "l", Column: "CreatedDate"}, "STATUS": {TableAlias: "l", Column: "Status"}, "CREATED_DATE": {TableAlias: "l", Column: "CreatedDate"}, "LAST_UPDATE": {TableAlias: "l", Column: "LastModifiedDate"}, "CONVERTED_DATE": {TableAlias: "l", Column: "ConvertedDate"}, }, }, // ────────────────────────────────────────────────────────────────────── // OpportunityCampaign: Opportunity → Campaign (via CampaignId) // 147 reports. // ────────────────────────────────────────────────────────────────────── "OpportunityCampaign": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "campaign", Alias: "c", JoinType: "LEFT JOIN", OnLeft: "c.Id", OnRight: "o.CampaignId"}, {Table: "campaign", Alias: "c_parent", JoinType: "LEFT JOIN", OnLeft: "c_parent.Id", OnRight: "c.ParentId"}, }, Columns: map[string]QualifiedColumn{ // Opportunity fields "OPP_NAME": {TableAlias: "o", Column: "Name"}, "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "ACCOUNT_NAME": {TableAlias: "o", Column: "AccountId"}, "OPP_OWNER": {TableAlias: "o", Column: "OwnerId"}, "FULL_NAME": {TableAlias: "o", Column: "OwnerId"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, "LEAD_SOURCE": {TableAlias: "o", Column: "LeadSource"}, "CREATED_DATE": {TableAlias: "o", Column: "CreatedDate"}, "PROBABILITY": {TableAlias: "o", Column: "Probability"}, "NEXT_STEP": {TableAlias: "o", Column: "NextStep"}, "FISCAL_QUARTER": {TableAlias: "o", Column: "FiscalQuarter"}, "FISCAL_YEAR": {TableAlias: "o", Column: "FiscalYear"}, "AGE": {TableAlias: "o", Column: "CreatedDate"}, // age computed from created "FAMILY": {TableAlias: "o", Column: "Type"}, "CONTACT": {TableAlias: "o", Column: "ContactId"}, "ADDRESS1_COUNTRY_CODE": {TableAlias: "o", Column: "BillingCountry"}, // Campaign fields "CAMPAIGN_NAME": {TableAlias: "c", Column: "Name"}, "CAMPAIGN_PARENT_NAME": {TableAlias: "c_parent", Column: "Name"}, "CAMPAIGN_STARTDATE": {TableAlias: "c", Column: "StartDate"}, "CAMPAIGN_SOURCE": {TableAlias: "c", Column: "Type"}, "CAMPAIGN_OWNER": {TableAlias: "c", Column: "OwnerId"}, }, }, // ────────────────────────────────────────────────────────────────────── // OpportunityProduct: Opportunity → OpportunityLineItem // 42 reports. // ────────────────────────────────────────────────────────────────────── "OpportunityProduct": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "opportunitylineitem", Alias: "oli", JoinType: "INNER JOIN", OnLeft: "oli.OpportunityId", OnRight: "o.Id"}, }, Columns: map[string]QualifiedColumn{ "OPP_NAME": {TableAlias: "o", Column: "Name"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, "PRODUCT_NAME": {TableAlias: "oli", Column: "Name"}, "QUANTITY": {TableAlias: "oli", Column: "Quantity"}, "TOTAL_PRICE": {TableAlias: "oli", Column: "TotalPrice"}, "UNIT_PRICE": {TableAlias: "oli", Column: "UnitPrice"}, }, }, // ────────────────────────────────────────────────────────────────────── // CampaignContact: Campaign → CampaignMember → Contact // 8 reports. // ────────────────────────────────────────────────────────────────────── "CampaignContact": { PrimaryTable: "campaign", PrimaryAlias: "c", Joins: []JoinStep{ {Table: "campaignmember", Alias: "cm", JoinType: "INNER JOIN", OnLeft: "cm.CampaignId", OnRight: "c.Id"}, {Table: "contact", Alias: "ct", JoinType: "INNER JOIN", OnLeft: "ct.Id", OnRight: "cm.ContactId"}, }, Columns: map[string]QualifiedColumn{ "CAMPAIGN_NAME": {TableAlias: "c", Column: "Name"}, "MEMBER_STATUS": {TableAlias: "cm", Column: "Status"}, "MEMBER_CREATED_DATE": {TableAlias: "cm", Column: "CreatedDate"}, "FIRST_NAME": {TableAlias: "ct", Column: "FirstName"}, "LAST_NAME": {TableAlias: "ct", Column: "LastName"}, "EMAIL": {TableAlias: "ct", Column: "Email"}, "PHONE": {TableAlias: "ct", Column: "Phone"}, "TITLE": {TableAlias: "ct", Column: "Title"}, }, }, // ────────────────────────────────────────────────────────────────────── // Activity report types: SF "Activity" is a polymorphic union of Task + Event. // We use UNION ALL of task and event joined via WhoId/WhatId. // However, the query builder doesn't support UNION, so for these types // we JOIN task (as the primary activity source) to the parent object. // This covers the majority of activity reports (tasks dominate events). // ────────────────────────────────────────────────────────────────────── // LeadActivity: Lead → Task (via WhoId) // 157 reports. "LeadActivity": { PrimaryTable: "lead", PrimaryAlias: "l", Joins: []JoinStep{ {Table: "task", Alias: "t", JoinType: "INNER JOIN", OnLeft: "t.WhoId", OnRight: "l.Id"}, }, Columns: activityLeadColumns, }, // ContactActivity: Contact → Task (via WhoId) // 76 reports. "ContactActivity": { PrimaryTable: "contact", PrimaryAlias: "ct", Joins: []JoinStep{ {Table: "task", Alias: "t", JoinType: "INNER JOIN", OnLeft: "t.WhoId", OnRight: "ct.Id"}, }, Columns: activityContactColumns, }, // OpportunityActivity: Opportunity → Task (via WhatId) // 104 reports. "OpportunityActivity": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "task", Alias: "t", JoinType: "INNER JOIN", OnLeft: "t.WhatId", OnRight: "o.Id"}, }, Columns: activityOpportunityColumns, }, // AccountActivity: Account → Task (via AccountId on Task, or WhatId) // 33 reports. "AccountActivity": { PrimaryTable: "account", PrimaryAlias: "a", Joins: []JoinStep{ {Table: "task", Alias: "t", JoinType: "INNER JOIN", OnLeft: "t.AccountId", OnRight: "a.Id"}, }, Columns: activityAccountColumns, }, // Activity (standalone): Task as primary — no parent join needed. // 54 reports. SF "Activity" report type queries tasks/events directly. "Activity": { PrimaryTable: "task", PrimaryAlias: "t", Joins: []JoinStep{}, Columns: activityStandaloneColumns, }, // ────────────────────────────────────────────────────────────────────── // OpportunityHistory: Opportunity → OpportunityHistory // 24 reports. // ────────────────────────────────────────────────────────────────────── "OpportunityHistory": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "opportunityhistory", Alias: "oh", JoinType: "INNER JOIN", OnLeft: "oh.OpportunityId", OnRight: "o.Id"}, }, Columns: map[string]QualifiedColumn{ "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "ACCOUNT_NAME": {TableAlias: "o", Column: "AccountId"}, "OWNER_NAME": {TableAlias: "o", Column: "OwnerId"}, "LEAD_SOURCE": {TableAlias: "o", Column: "LeadSource"}, "LAST_UPDATE": {TableAlias: "o", Column: "LastModifiedDate"}, "LAST_UPDATE_NAME": {TableAlias: "o", Column: "LastModifiedById"}, "LAST_STAGE_CHANGE_DATE": {TableAlias: "o", Column: "LastStageChangeDate"}, "FROM_OPPORTUNITY_STAGE_NAME": {TableAlias: "oh", Column: "StageName"}, "AMOUNT": {TableAlias: "oh", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "oh", Column: "CloseDate"}, "CREATED_DATE": {TableAlias: "oh", Column: "CreatedDate"}, "PROBABILITY": {TableAlias: "oh", Column: "Probability"}, "FORECAST_CATEGORY": {TableAlias: "oh", Column: "ForecastCategory"}, "STAGE_NAME": {TableAlias: "oh", Column: "StageName"}, }, }, // ────────────────────────────────────────────────────────────────────── // OpportunityFieldAuditHistory: Opportunity → opportunity__hd // 63 reports. // ────────────────────────────────────────────────────────────────────── "OpportunityFieldAuditHistory": { PrimaryTable: "opportunity", PrimaryAlias: "o", Joins: []JoinStep{ {Table: "opportunity__hd", Alias: "hd", JoinType: "INNER JOIN", OnLeft: "hd.ParentId", OnRight: "o.Id"}, }, Columns: map[string]QualifiedColumn{ "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "OPPORUNITY.NAME": {TableAlias: "o", Column: "Name"}, // SF typo in real data "OPP_ACCOUNT": {TableAlias: "o", Column: "AccountId"}, "OPP_CREATED": {TableAlias: "o", Column: "CreatedDate"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, "LEAD_SOURCE": {TableAlias: "o", Column: "LeadSource"}, "FISCAL_QUARTER": {TableAlias: "o", Column: "FiscalQuarter"}, "FORECAST_CATEGORY": {TableAlias: "o", Column: "ForecastCategoryName"}, "CAMPAIGN_SOURCE": {TableAlias: "o", Column: "CampaignId"}, "AGE": {TableAlias: "o", Column: "CreatedDate"}, "CREATED": {TableAlias: "hd", Column: "CreatedDate"}, "CREATED_DATE": {TableAlias: "hd", Column: "CreatedDate"}, }, }, // ────────────────────────────────────────────────────────────────────── // QuoteProduct: Quote → QuoteLineItem (with Opportunity context) // 8 reports. // ────────────────────────────────────────────────────────────────────── "QuoteProduct": { PrimaryTable: "quote", PrimaryAlias: "q", Joins: []JoinStep{ {Table: "quotelineitem", Alias: "qli", JoinType: "INNER JOIN", OnLeft: "qli.QuoteId", OnRight: "q.Id"}, {Table: "opportunity", Alias: "o", JoinType: "LEFT JOIN", OnLeft: "o.Id", OnRight: "q.OpportunityId"}, }, Columns: map[string]QualifiedColumn{ "QUOTE_NAME": {TableAlias: "q", Column: "Name"}, "Q.CREATED_DATE": {TableAlias: "q", Column: "CreatedDate"}, "Q.STATUS": {TableAlias: "q", Column: "Status"}, "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "ACCOUNT_NAME": {TableAlias: "o", Column: "AccountId"}, "ACCOUNT_OWNER": {TableAlias: "o", Column: "OwnerId"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, "FISCAL_QUARTER": {TableAlias: "o", Column: "FiscalQuarter"}, "PRODUCT_NAME": {TableAlias: "qli", Column: "Name"}, "QUANTITY": {TableAlias: "qli", Column: "Quantity"}, "TOTAL_PRICE": {TableAlias: "qli", Column: "TotalPrice"}, "UNIT_PRICE": {TableAlias: "qli", Column: "UnitPrice"}, }, }, // ────────────────────────────────────────────────────────────────────── // ContractOrder: Contract → Order // 5 reports. // ────────────────────────────────────────────────────────────────────── "ContractOrder": { PrimaryTable: "contract", PrimaryAlias: "ct", Joins: []JoinStep{ {Table: "order", Alias: "ord", JoinType: "INNER JOIN", OnLeft: "ord.ContractId", OnRight: "ct.Id"}, {Table: "account", Alias: "acc", JoinType: "LEFT JOIN", OnLeft: "acc.Id", OnRight: "ct.AccountId"}, {Table: "user", Alias: "acc_owner", JoinType: "LEFT JOIN", OnLeft: "acc_owner.Id", OnRight: "acc.OwnerId"}, {Table: "recordtype", Alias: "ct_rt", JoinType: "LEFT JOIN", OnLeft: "ct_rt.Id", OnRight: "ct.RecordTypeId"}, }, Columns: map[string]QualifiedColumn{ "CONTRACT_ID": {TableAlias: "ct", Column: "Id"}, "CONTRACT_NAME": {TableAlias: "ct", Column: "ContractNumber"}, "CONTRACT_NUMBER": {TableAlias: "ct", Column: "ContractNumber"}, "STATUS": {TableAlias: "ct", Column: "Status"}, "START_DATE": {TableAlias: "ct", Column: "StartDate"}, "END_DATE": {TableAlias: "ct", Column: "EndDate"}, "ACCOUNT_NAME": {TableAlias: "acc", Column: "Name", ResultAlias: "AccountId_Name"}, "ACCOUNT_ID": {TableAlias: "ct", Column: "AccountId"}, "ACCOUNT_OWNER": {TableAlias: "acc_owner", Column: "Name", ResultAlias: "AccountId_OwnerId_Name", SelectExpr: userDisplaySelectExpr("acc_owner")}, "ORDER_ID": {TableAlias: "ord", Column: "Id"}, "ORDER_NUMBER": {TableAlias: "ord", Column: "OrderNumber"}, "ORDER_STATUS": {TableAlias: "ord", Column: "Status"}, "ORDER_OWNER": {TableAlias: "ord", Column: "OwnerId"}, "ORDER_TYPE": {TableAlias: "ord", Column: "Type"}, "ORDER_TOTAL_AMOUNT": {TableAlias: "ord", Column: "TotalAmount"}, "ORDER_EFFECTIVE_DATE": {TableAlias: "ord", Column: "EffectiveDate"}, "RECORDTYPE": {TableAlias: "ct_rt", Column: "Name", ResultAlias: "RecordTypeId_Name"}, }, }, // ────────────────────────────────────────────────────────────────────── // ContractOrderItem: Contract → Order → OrderItem // 2 reports. // ────────────────────────────────────────────────────────────────────── "ContractOrderItem": { PrimaryTable: "contract", PrimaryAlias: "ct", Joins: []JoinStep{ {Table: "order", Alias: "ord", JoinType: "INNER JOIN", OnLeft: "ord.ContractId", OnRight: "ct.Id"}, {Table: "orderitem", Alias: "oi", JoinType: "INNER JOIN", OnLeft: "oi.OrderId", OnRight: "ord.Id"}, }, Columns: map[string]QualifiedColumn{ "CONTRACT_NAME": {TableAlias: "ct", Column: "ContractNumber"}, "CONTRACT_NUMBER": {TableAlias: "ct", Column: "ContractNumber"}, "STATUS": {TableAlias: "ct", Column: "Status"}, "START_DATE": {TableAlias: "ct", Column: "StartDate"}, "END_DATE": {TableAlias: "ct", Column: "EndDate"}, "ACCOUNT_NAME": {TableAlias: "ct", Column: "AccountId"}, "ACCOUNT_OWNER": {TableAlias: "ct", Column: "OwnerId"}, "INDUSTRY": {TableAlias: "ct", Column: "AccountId"}, // needs Account join "ORDER_NAME": {TableAlias: "ord", Column: "OrderNumber"}, "ORDER_NUMBER": {TableAlias: "ord", Column: "OrderNumber"}, "ORDER_STATUS": {TableAlias: "ord", Column: "Status"}, "ORDER_TYPE": {TableAlias: "ord", Column: "Type"}, "ORDER_TOTAL_AMOUNT": {TableAlias: "ord", Column: "TotalAmount"}, "ORDER_EFFECTIVE_DATE": {TableAlias: "ord", Column: "EffectiveDate"}, "ORDER_ITEM_ID": {TableAlias: "oi", Column: "Id"}, "ORDER_ITEM_NAME": {TableAlias: "oi", Column: "Description"}, "ORDER_ITEM_QUANTITY": {TableAlias: "oi", Column: "Quantity"}, "ORDER_ITEM_UNITPRICE": {TableAlias: "oi", Column: "UnitPrice"}, "ORDER_ITEM_TOTALPRICE": {TableAlias: "oi", Column: "TotalPrice"}, "ORDER_ITEM_LISTPRICE": {TableAlias: "oi", Column: "ListPrice"}, "ORDER_ITEM_CUSTOMER_PROD_ID": {TableAlias: "oi", Column: "Product2Id"}, }, }, } mustMergeEmbeddedJoinTemplates(templates) return templates }() // ── Shared column maps for Activity-based report types ────────────────── // Common activity (Task) columns shared across all Activity report types. var activityCommonColumns = map[string]QualifiedColumn{ "SUBJECT": {TableAlias: "t", Column: "Subject"}, "DUE_DATE": {TableAlias: "t", Column: "ActivityDate"}, "STATUS": {TableAlias: "t", Column: "Status"}, "PRIORITY": {TableAlias: "t", Column: "Priority"}, "DESCRIPTION": {TableAlias: "t", Column: "Description"}, "FULL_DESCRIPTION": {TableAlias: "t", Column: "Description"}, "ASSIGNED": {TableAlias: "t", Column: "OwnerId"}, "ASSIGNED_ALIAS": {TableAlias: "t", Column: "OwnerId"}, "CALLTYPE": {TableAlias: "t", Column: "CallType"}, "COMPLETED_DATE_TIME": {TableAlias: "t", Column: "CompletedDateTime"}, "CREATED_DATE": {TableAlias: "t", Column: "CreatedDate"}, "LAST_UPDATE": {TableAlias: "t", Column: "LastModifiedDate"}, } var activityLeadColumns = mergeColumns(activityCommonColumns, map[string]QualifiedColumn{ "FIRST_NAME": {TableAlias: "l", Column: "FirstName"}, "LAST_NAME": {TableAlias: "l", Column: "LastName"}, "EMAIL": {TableAlias: "l", Column: "Email"}, "PHONE": {TableAlias: "l", Column: "Phone"}, "COMPANY": {TableAlias: "l", Column: "Company"}, "COMPANY_ACCT": {TableAlias: "l", Column: "Company"}, "LEAD_OWNER": {TableAlias: "l", Column: "OwnerId"}, "LEAD_SOURCE": {TableAlias: "l", Column: "LeadSource"}, "CONVERTED": {TableAlias: "l", Column: "IsConverted"}, "DO_NOT_CALL": {TableAlias: "l", Column: "DoNotCall"}, "EMAIL_OPT_OUT": {TableAlias: "l", Column: "HasOptedOutOfEmail"}, "LAST_UPDATE_BY": {TableAlias: "l", Column: "LastModifiedById"}, }) var activityContactColumns = mergeColumns(activityCommonColumns, map[string]QualifiedColumn{ "FIRST_NAME": {TableAlias: "ct", Column: "FirstName"}, "LAST_NAME": {TableAlias: "ct", Column: "LastName"}, "EMAIL": {TableAlias: "ct", Column: "Email"}, "PHONE": {TableAlias: "ct", Column: "Phone"}, "TITLE": {TableAlias: "ct", Column: "Title"}, "CONTACT_OWNER": {TableAlias: "ct", Column: "OwnerId"}, "ACCOUNT_OWNER_NAME": {TableAlias: "ct", Column: "OwnerId"}, "CONTACT_CREATED_DATE": {TableAlias: "ct", Column: "CreatedDate"}, "CONTACT_LAST_UPDATE": {TableAlias: "ct", Column: "LastModifiedDate"}, "CONTACT_LAST_ACTIVITY": {TableAlias: "ct", Column: "LastActivityDate"}, }) var activityOpportunityColumns = mergeColumns(activityCommonColumns, map[string]QualifiedColumn{ "OPPORTUNITY_NAME": {TableAlias: "o", Column: "Name"}, "AMOUNT": {TableAlias: "o", Column: "Amount"}, "CLOSE_DATE": {TableAlias: "o", Column: "CloseDate"}, "STAGE_NAME": {TableAlias: "o", Column: "StageName"}, "FISCAL_QUARTER": {TableAlias: "o", Column: "FiscalQuarter"}, "ACCOUNT_NAME": {TableAlias: "o", Column: "AccountId"}, "LEAD_SOURCE": {TableAlias: "o", Column: "LeadSource"}, }) var activityAccountColumns = mergeColumns(activityCommonColumns, map[string]QualifiedColumn{ "ACCOUNT_NAME": {TableAlias: "a", Column: "Name"}, "ACCOUNT_OWNER": {TableAlias: "a", Column: "OwnerId"}, "INDUSTRY": {TableAlias: "a", Column: "Industry"}, }) var activityStandaloneColumns = map[string]QualifiedColumn{ "SUBJECT": {TableAlias: "t", Column: "Subject"}, "DUE_DATE": {TableAlias: "t", Column: "ActivityDate"}, "STATUS": {TableAlias: "t", Column: "Status"}, "PRIORITY": {TableAlias: "t", Column: "Priority"}, "DESCRIPTION": {TableAlias: "t", Column: "Description"}, "ASSIGNED": {TableAlias: "t", Column: "OwnerId"}, "CREATED_DATE": {TableAlias: "t", Column: "CreatedDate"}, "ACCOUNT": {TableAlias: "t", Column: "AccountId"}, "CONTACT": {TableAlias: "t", Column: "WhoId"}, "LEAD": {TableAlias: "t", Column: "WhoId"}, "OPPORTUNITY": {TableAlias: "t", Column: "WhatId"}, "LEAD_SOURCE": {TableAlias: "t", Column: "CallType"}, } // mergeColumns creates a new column map by combining base with overrides. func mergeColumns(base, overrides map[string]QualifiedColumn) map[string]QualifiedColumn { result := make(map[string]QualifiedColumn, len(base)+len(overrides)) for k, v := range base { result[k] = v } for k, v := range overrides { result[k] = v } return result } func mustMergeEmbeddedJoinTemplates(dst map[string]*JoinTemplate) { if len(embeddedStandardJoinTemplatesJSON) == 0 { return } var embedded map[string]*JoinTemplate if err := json.Unmarshal(embeddedStandardJoinTemplatesJSON, &embedded); err != nil { panic(fmt.Errorf("unmarshal embedded standard join templates: %w", err)) } for name, tmpl := range embedded { dst[name] = tmpl } } // LookupJoinTemplate returns the JOIN template for a report type, or nil if single-table. func LookupJoinTemplate(reportType string) *JoinTemplate { return standardJoinTemplates[reportType] }