従業員オンボーディング用データテンプレート

The following describes the configuration for the export project. Since the Data Management Framework exports entities separately, you must create an export for each source entity listed below, then combine the outputs into a single event log file.

**Source 1: Worker Entity**
This entity is used to generate the 'HR Profile Created' event.
*   **Entity Name**: `Worker`
*   **Field Mapping**:
    *   `OnboardingCase`: Map from `PersonnelNumber`
    *   `ActivityName`: Set as a fixed value: "HR Profile Created"
    *   `EventTime`: Map from `CreatedDateTime`
    *   `EventEndTime`: Map from `CreatedDateTime`
    *   `DepartmentName`: Map from `PrimaryAddressLocation` or related party information.
    *   `HiringManagerName`: Map from `ReportsToPerson` or related worker information.
    *   `PositionTitle`: Map from primary position details linked to the worker.
    *   `OnboardingStatus`: Set as a fixed value: "In Progress"
    *   `ComplianceDueDate`: Set to null

**Source 2: Employment Entity**
This entity generates the 'Offer Accepted' event, typically using the employment start date as the timestamp.
*   **Entity Name**: `Employment`
*   **Field Mapping**:
    *   `OnboardingCase`: Map from `PersonnelNumber`
    *   `ActivityName`: Set as a fixed value: "Offer Accepted"
    *   `EventTime`: Map from `EmploymentStartDate`
    *   `EventEndTime`: Map from `EmploymentStartDate`
    *   `DepartmentName`: Map from `DepartmentNumber`
    *   `HiringManagerName`: Join to worker entity to find manager name.
    *   `PositionTitle`: Join to position entity to find position title.
    *   `OnboardingStatus`: Set as a fixed value: "Initiated"
    *   `ComplianceDueDate`: Set to null

**Source 3: Onboarding Checklist Task Entity**
This is the primary source for most task-based activities. One export from this entity will generate rows for multiple activity types. A transformation step will be needed to map the task name to the correct standard activity name.
*   **Entity Name**: `Onboarding checklist task` (or a similar custom name like `HcmOnboardingGuideTask`)
*   **Filter Logic**: Only include tasks with a `CompletionDateTime`.
*   **Field Mapping**:
    *   `OnboardingCase`: Map from `PersonnelNumber`
    *   `ActivityName`: Map from `TaskName`. You must then apply logic to transform source values (e.g., 'Initiate employee background check') into the required standard names (e.g., 'Background Check Initiated'). This mapping should cover:
        *   "Background Check Initiated"
        *   "Background Check Completed"
        *   "Paperwork Sent To New Hire"
        *   "Paperwork Completed By New Hire"
        *   "IT Account Provisioning Initiated"
        *   "Equipment Ordered"
        *   "Equipment Delivered"
        *   "Workspace Setup Completed"
        *   "First Day Orientation Conducted"
        *   "Team Introduction Completed"
        *   "Onboarding Plan Completed"
        *   "30-Day Check-in Performed"
    *   `EventTime`: Map from `CompletionDateTime` for completion events. For initiation events, map from `CreationDateTime` or `StartDate`.
    *   `EventEndTime`: Map from `CompletionDateTime`.
    *   `DepartmentName`: Join to worker or employment entities.
    *   `HiringManagerName`: Join to worker or employment entities.
    *   `PositionTitle`: Join to worker or employment entities.
    *   `OnboardingStatus`: Map from a status field on the checklist or task.
    *   `ComplianceDueDate`: Map from `DueDate`.

**Source 4: Course Tables Entity**
This source tracks training-related activities.
*   **Entity Name**: `Course tables` (or a similar learning management entity)
*   **Logic**: You will need to create two transformations from this source: one for assignment and one for completion.
*   **Field Mapping (Assignment Event)**:
    *   `OnboardingCase`: Map from `PersonnelNumber`
    *   `ActivityName`: Set as a fixed value: "Mandatory Training Assigned"
    *   `EventTime`: Map from `AssignmentDate`
    *   `EventEndTime`: Map from `AssignmentDate`
    *   Other Attributes: Populate from related entities as needed.
*   **Field Mapping (Completion Event)**:
    *   `OnboardingCase`: Map from `PersonnelNumber`
    *   `ActivityName`: Set as a fixed value: "Mandatory Training Completed"
    *   `EventTime`: Map from `CompletionDate`
    *   `EventEndTime`: Map from `CompletionDate`
    *   Other Attributes: Populate from related entities as needed.

-- The following query reconstructs the Employee Onboarding process by querying the Dataverse TDS endpoint.
-- It assumes that most onboarding activities are tracked in a task entity like 'msdyn_onboardingtaskbase'.
-- Placeholders to be reviewed and updated:
-- 1. Date filter: The 'WHERE emp.startdate >= ...' line should be set to your desired date range.
-- 2. Task Subjects: The text literals in the WHERE clauses (e.g., 'Background Check', 'Order Equipment') must match the subject lines used in your system's onboarding tasks.
-- 3. Table and Column Names: If your environment uses custom entities or fields, update the table and column names accordingly.

WITH WorkerInfo AS (
  SELECT
    w.hcmworkerid AS workerid,
    w.personnelnumber AS personnelnumber,
    emp.startdate AS employmentstartdate,
    d.name AS departmentname,
    pos.description AS positiontitle,
    mgr.fullname AS managername,
    w.createdon AS workercreatedon
  FROM hcmworkerbase AS w
  LEFT JOIN hcmemploymentbase AS emp ON w.hcmworkerid = emp._hcmworkerid_value
  LEFT JOIN hcmdepartmentbase AS d ON emp._hcmdepartmentid_value = d.hcmdepartmentid
  LEFT JOIN hcmpositionworkerassignmentbase AS pwa ON w.hcmworkerid = pwa._hcmworkerid_value AND pwa.isprimaryposition = 1
  LEFT JOIN hcmpositiondetailbase AS posd ON pwa._hcmpositionid_value = posd._hcmpositionid_value
  LEFT JOIN hcmpositionbase AS pos ON pwa._hcmpositionid_value = pos.hcmpositionid
  LEFT JOIN systemuserbase AS mgr ON pos.reports_to_position_id = mgr._hcmpositionid_value
  WHERE emp.startdate >= DATEADD(month, -6, GETUTCDATE()) -- Placeholder: Filter for the last 6 months
    AND emp.employmenttype = 0 -- Example: Filter for Employees, not Contractors
)
-- 1. Offer Accepted (proxied by employment record creation)
SELECT
  w.personnelnumber AS OnboardingCase,
  'Offer Accepted' AS ActivityName,
  w.employmentstartdate AS EventTime,
  w.employmentstartdate AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM WorkerInfo AS w

UNION ALL

-- 2. HR Profile Created
SELECT
  w.personnelnumber AS OnboardingCase,
  'HR Profile Created' AS ActivityName,
  w.workercreatedon AS EventTime,
  w.workercreatedon AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM WorkerInfo AS w

UNION ALL

-- 3. Background Check Initiated
SELECT
  w.personnelnumber AS OnboardingCase,
  'Background Check Initiated' AS ActivityName,
  t.createdon AS EventTime,
  NULL AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  t.scheduledend AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Background Check%'

UNION ALL

-- 4. Background Check Completed
SELECT
  w.personnelnumber AS OnboardingCase,
  'Background Check Completed' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Background Check%' AND t.statecode = 1

UNION ALL

-- 5. Paperwork Sent To New Hire
SELECT
  w.personnelnumber AS OnboardingCase,
  'Paperwork Sent To New Hire' AS ActivityName,
  t.createdon AS EventTime,
  NULL AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  t.scheduledend AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Send Paperwork%'

UNION ALL

-- 6. Paperwork Completed By New Hire
SELECT
  w.personnelnumber AS OnboardingCase,
  'Paperwork Completed By New Hire' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Complete Paperwork%' AND t.statecode = 1

UNION ALL

-- 7. IT Account Provisioning Initiated
SELECT
  w.personnelnumber AS OnboardingCase,
  'IT Account Provisioning Initiated' AS ActivityName,
  t.createdon AS EventTime,
  NULL AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  t.scheduledend AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%IT Account Provisioning%'

UNION ALL

-- 8. Equipment Ordered
SELECT
  w.personnelnumber AS OnboardingCase,
  'Equipment Ordered' AS ActivityName,
  t.actualend AS EventTime, -- Assuming task completion means ordered
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Order Equipment%' AND t.statecode = 1

UNION ALL

-- 9. Equipment Delivered
SELECT
  w.personnelnumber AS OnboardingCase,
  'Equipment Delivered' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Confirm Equipment Delivery%' AND t.statecode = 1

UNION ALL

-- 10. Workspace Setup Completed
SELECT
  w.personnelnumber AS OnboardingCase,
  'Workspace Setup Completed' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Workspace Setup%' AND t.statecode = 1

UNION ALL

-- 11. First Day Orientation Conducted
SELECT
  w.personnelnumber AS OnboardingCase,
  'First Day Orientation Conducted' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%First Day Orientation%' AND t.statecode = 1

UNION ALL

-- 12. Mandatory Training Assigned
SELECT
  w.personnelnumber AS OnboardingCase,
  'Mandatory Training Assigned' AS ActivityName,
  t.createdon AS EventTime,
  NULL AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  t.scheduledend AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Mandatory Training%'

UNION ALL

-- 13. Mandatory Training Completed
SELECT
  w.personnelnumber AS OnboardingCase,
  'Mandatory Training Completed' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Mandatory Training%' AND t.statecode = 1

UNION ALL

-- 14. Team Introduction Completed
SELECT
  w.personnelnumber AS OnboardingCase,
  'Team Introduction Completed' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'In Progress' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%Team Introduction%' AND t.statecode = 1

UNION ALL

-- 15. Onboarding Plan Completed
SELECT
  w.personnelnumber AS OnboardingCase,
  'Onboarding Plan Completed' AS ActivityName,
  g.actualend AS EventTime,
  g.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'Completed' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingguidebase AS g
JOIN WorkerInfo AS w ON g._msdyn_workerid_value = w.workerid
WHERE g.statecode = 1

UNION ALL

-- 16. 30-Day Check-in Performed
SELECT
  w.personnelnumber AS OnboardingCase,
  '30-Day Check-in Performed' AS ActivityName,
  t.actualend AS EventTime,
  t.actualend AS EventEndTime,
  w.departmentname AS DepartmentName,
  w.managername AS HiringManagerName,
  w.positiontitle AS PositionTitle,
  'Completed' AS OnboardingStatus,
  NULL AS ComplianceDueDate
FROM msdyn_onboardingtaskbase AS t
JOIN WorkerInfo AS w ON t._msdyn_workerid_value = w.workerid
WHERE t.subject LIKE '%30-Day Check-in%' AND t.statecode = 1

ORDER BY OnboardingCase, EventTime;

import requests
import pandas as pd
import msal
import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION ---
class=class="hl-string">"hl-comment"># Replace with your Azure AD and Dynamics 365 environment details
TENANT_ID = class="hl-string">"your_tenant_id"
CLIENT_ID = class="hl-string">"your_client_id"
CLIENT_SECRET = class="hl-string">"your_client_secret"
RESOURCE_URL = class="hl-string">"https://your-org.hr.d365.dynamics.com" class=class="hl-string">"hl-comment"># Base D365 HR URL
API_ENDPOINT = fclass="hl-string">"{RESOURCE_URL}/data"

class=class="hl-string">"hl-comment"># Define the date range for the extraction
start_date_str = class="hl-string">"2023-01-01"
end_date_str = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># CRITICAL: Map standard activity names to the exact task names in your D365 Onboarding Checklists
class=class="hl-string">"hl-comment"># The script will query OnboardingChecklistItems based on the class="hl-string">'Task' field values.
activity_task_mapping = {
    class="hl-string">"Background Check Initiated": class="hl-string">"[Background Check Task Name]",
    class="hl-string">"Background Check Completed": class="hl-string">"[Background Check Completed Task Name]",
    class="hl-string">"Paperwork Sent To New Hire": class="hl-string">"[Send Paperwork Task Name]",
    class="hl-string">"Paperwork Completed By New Hire": class="hl-string">"[Complete Paperwork Task Name]",
    class="hl-string">"IT Account Provisioning Initiated": class="hl-string">"[Request IT Account Task Name]",
    class="hl-string">"Equipment Ordered": class="hl-string">"[Order Laptop and Equipment]",
    class="hl-string">"Equipment Delivered": class="hl-string">"[Confirm Equipment Delivery]",
    class="hl-string">"Workspace Setup Completed": class="hl-string">"[Complete Workspace Setup]",
    class="hl-string">"First Day Orientation Conducted": class="hl-string">"[Complete First Day Orientation]",
    class="hl-string">"Mandatory Training Assigned": class="hl-string">"[Assign Mandatory Training]",
    class="hl-string">"Mandatory Training Completed": class="hl-string">"[Confirm Mandatory Training Completion]",
    class="hl-string">"Team Introduction Completed": class="hl-string">"[Introduce New Hire to Team]",
    class="hl-string">"Onboarding Plan Completed": class="hl-string">"[Complete Full Onboarding Plan]",
    class="hl-string">"30-Day Check-in Performed": class="hl-string">"[Conduct 30-Day Check-in]"
}

class=class="hl-string">"hl-comment"># --- AUTHENTICATION ---
def get_access_token():
    authority = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
    app = msal.ConfidentialClientApplication(
        CLIENT_ID,
        authority=authority,
        client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=[fclass="hl-string">"{RESOURCE_URL}/.default"])
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        print(result.get(class="hl-string">"error"))
        print(result.get(class="hl-string">"error_description"))
        raise Exception(class="hl-string">"Could not obtain access token")

class=class="hl-string">"hl-comment"># --- DATA FETCHING AND PROCESSING ---
def fetch_data(access_token, entity_url):
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    full_url = fclass="hl-string">"{API_ENDPOINT}/{entity_url}"
    response = requests.get(full_url, headers=headers)
    if response.status_code == 200:
        return response.json()[class="hl-string">'value']
    else:
        print(fclass="hl-string">"Error fetching {entity_url}: {response.status_code} - {response.text}")
        return []

def main():
    print(class="hl-string">"Acquiring access token...")
    token = get_access_token()
    all_events = []

    class=class="hl-string">"hl-comment"># Fetch base worker and onboarding guide data for enrichment
    print(class="hl-string">"Fetching base worker and onboarding data...")
    worker_details_url = fclass="hl-string">"Workers?$select=PersonnelNumber,Name,PrimaryContactEmail,PersonBirthDate&$expand=Employments($select=DepartmentNumber,HiringManagerPersonnelNumber,PositionId),Positions($select=PositionId,Title)"
    workers_data = fetch_data(token, worker_details_url)
    workers_df = pd.json_normalize(workers_data)

    class=class="hl-string">"hl-comment"># A simplified enrichment lookup dictionary
    enrichment_map = {}
    for _, row in workers_df.iterrows():
        enrichment_map[row[class="hl-string">'PersonnelNumber']] = {
            class="hl-string">'DepartmentName': row.get(class="hl-string">'Employments.DepartmentNumber', None),
            class="hl-string">'HiringManagerName': row.get(class="hl-string">'Employments.HiringManagerPersonnelNumber', None),
            class="hl-string">'PositionTitle': row.get(class="hl-string">'Positions.Title', None)
        }

    print(class="hl-string">"Processing activities...")
    
    class=class="hl-string">"hl-comment"># 1. Offer Accepted / HR Profile Created (approximated by worker creation date)
    print(class="hl-string">"Extracting 'Offer Accepted' and 'HR Profile Created'...")
    offer_accepted_url = fclass="hl-string">"Workers?$filter=createdDateTime ge {start_date_str}T00:00:00Z and createdDateTime le {end_date_str}T23:59:59Z&$select=PersonnelNumber,createdDateTime"
    offer_data = fetch_data(token, offer_accepted_url)
    for item in offer_data:
        personnel_number = item.get(class="hl-string">'PersonnelNumber')
        event_time = item.get(class="hl-string">'createdDateTime')
        enrich_data = enrichment_map.get(personnel_number, {})
        all_events.append({class="hl-string">'OnboardingCase': personnel_number, class="hl-string">'ActivityName': class="hl-string">'Offer Accepted', class="hl-string">'EventTime': event_time, **enrich_data})
        all_events.append({class="hl-string">'OnboardingCase': personnel_number, class="hl-string">'ActivityName': class="hl-string">'HR Profile Created', class="hl-string">'EventTime': event_time, **enrich_data})

    class=class="hl-string">"hl-comment"># 2. Checklist-based activities
    for activity_name, task_name in activity_task_mapping.items():
        print(fclass="hl-string">"Extracting '{activity_name}'...")
        checklist_items_url = fclass="hl-string">"OnboardingChecklistItems?$filter=Task eq '{task_name}' and CompletedDateTime ge {start_date_str}T00:00:00Z and CompletedDateTime le {end_date_str}T23:59:59Z&$select=OnboardingGuidePersonnelNumber,CompletedDateTime,DueDate&$expand=OnboardingGuide($select=OverallStatus)"
        checklist_data = fetch_data(token, checklist_items_url)
        for item in checklist_data:
            personnel_number = item.get(class="hl-string">'OnboardingGuidePersonnelNumber')
            event_time = item.get(class="hl-string">'CompletedDateTime')
            enrich_data = enrichment_map.get(personnel_number, {})
            all_events.append({
                class="hl-string">'OnboardingCase': personnel_number,
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventTime': event_time,
                class="hl-string">'EventEndTime': event_time,
                class="hl-string">'ComplianceDueDate': item.get(class="hl-string">'DueDate'),
                class="hl-string">'OnboardingStatus': item.get(class="hl-string">'OnboardingGuide', {}).get(class="hl-string">'OverallStatus'),
                **enrich_data
            })

    class=class="hl-string">"hl-comment"># Convert to DataFrame
    if not all_events:
        print(class="hl-string">"No events found for the specified period.")
        return

    event_log_df = pd.DataFrame(all_events)

    class=class="hl-string">"hl-comment"># Final data transformation
    event_log_df[class="hl-string">'EventTime'] = pd.to_datetime(event_log_df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
    event_log_df = event_log_df.dropna(subset=[class="hl-string">'OnboardingCase', class="hl-string">'EventTime'])
    event_log_df = event_log_df.sort_values(by=[class="hl-string">'OnboardingCase', class="hl-string">'EventTime'], ascending=[True, True])
    
    class=class="hl-string">"hl-comment"># Ensure all required and recommended columns exist
    final_columns = [
        class="hl-string">'OnboardingCase', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EventEndTime',
        class="hl-string">'DepartmentName', class="hl-string">'HiringManagerName', class="hl-string">'PositionTitle',
        class="hl-string">'OnboardingStatus', class="hl-string">'ComplianceDueDate'
    ]
    for col in final_columns:
        if col not in event_log_df.columns:
            event_log_df[col] = None
    
    event_log_df = event_log_df[final_columns]

    class=class="hl-string">"hl-comment"># Export to CSV
    output_filename = class="hl-string">'d365_onboarding_event_log.csv'
    event_log_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%fZ')
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

if __name__ == class="hl-string">"__main__":
    main()

const CONFIG = {
  startDate: '[Start date and time]',
  endDate: '[End date and time]',
  companyFilter: '[Company or legal entity filter]',
  sourceTimeZone: '[Source time zone]',
  targetTimeZone: 'UTC'
};

const ACTIVITIES = [
  'Offer Accepted',
  'HR Profile Created',
  'Background Check Initiated',
  'Background Check Completed',
  'Paperwork Sent To New Hire',
  'Paperwork Completed By New Hire',
  'IT Account Provisioning Initiated',
  'Equipment Ordered',
  'Equipment Delivered',
  'Workspace Setup Completed',
  'First Day Orientation Conducted',
  'Mandatory Training Assigned',
  'Mandatory Training Completed',
  'Team Introduction Completed',
  'Onboarding Plan Completed',
  '30-Day Check-in Performed'
];

const SOURCE_MAPPINGS = {
  offerAccepted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your offer accepted timestamp column]',
    endColumn: '[Your offer accepted end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  hrProfileCreated: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your HR profile created timestamp column]',
    endColumn: '[Your HR profile created end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  backgroundCheckInitiated: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your background check initiated timestamp column]',
    endColumn: '[Your background check initiated end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  backgroundCheckCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your background check completed timestamp column]',
    endColumn: '[Your background check completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  paperworkSent: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your paperwork sent timestamp column]',
    endColumn: '[Your paperwork sent end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  paperworkCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your paperwork completed timestamp column]',
    endColumn: '[Your paperwork completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  itProvisioningInitiated: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your IT provisioning initiated timestamp column]',
    endColumn: '[Your IT provisioning initiated end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  equipmentOrdered: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your equipment ordered timestamp column]',
    endColumn: '[Your equipment ordered end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  equipmentDelivered: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your equipment delivered timestamp column]',
    endColumn: '[Your equipment delivered end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  workspaceSetupCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your workspace setup completed timestamp column]',
    endColumn: '[Your workspace setup completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  orientationConducted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your orientation conducted timestamp column]',
    endColumn: '[Your orientation conducted end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  trainingAssigned: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your mandatory training assigned timestamp column]',
    endColumn: '[Your mandatory training assigned end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  trainingCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your mandatory training completed timestamp column]',
    endColumn: '[Your mandatory training completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  teamIntroductionCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your team introduction completed timestamp column]',
    endColumn: '[Your team introduction completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  onboardingPlanCompleted: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your onboarding plan completed timestamp column]',
    endColumn: '[Your onboarding plan completed end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  },
  checkIn30Day: {
    table: '[Your table name]',
    caseColumn: '[Your onboarding case column]',
    eventColumn: '[Your 30-day check-in timestamp column]',
    endColumn: '[Your 30-day check-in end timestamp column]',
    departmentColumn: '[Your department column]',
    managerColumn: '[Your hiring manager column]',
    positionColumn: '[Your position title column]',
    statusColumn: '[Your onboarding status column]',
    complianceColumn: '[Your compliance due date column]',
    companyColumn: '[Your company or legal entity column]'
  }
};

function toUtc(value) {
  if (value === null || value === undefined || value === '') return null;
  const date = new Date(value);
  if (Number.isNaN(date.getTime())) return null;
  return date.toISOString();
}

function inRange(value) {
  const timestamp = toUtc(value);
  if (!timestamp) return false;
  return timestamp >= toUtc(CONFIG.startDate) && timestamp <= toUtc(CONFIG.endDate);
}

function getValue(record, column) {
  if (!column || column.startsWith('[')) return null;
  return record[column] ?? null;
}

function createEvent(record, mapping, activity) {
  const eventTime = getValue(record, mapping.eventColumn);
  const onboardingCase = getValue(record, mapping.caseColumn);
  if (!onboardingCase || !inRange(eventTime)) return null;
  return {
    OnboardingCase: String(onboardingCase),
    ActivityName: activity,
    EventTime: toUtc(eventTime),
    EventEndTime: toUtc(getValue(record, mapping.endColumn)) || toUtc(eventTime),
    DepartmentName: getValue(record, mapping.departmentColumn),
    HiringManagerName: getValue(record, mapping.managerColumn),
    PositionTitle: getValue(record, mapping.positionColumn),
    OnboardingStatus: getValue(record, mapping.statusColumn),
    ComplianceDueDate: toUtc(getValue(record, mapping.complianceColumn)),
    SourceRecordId: record.id || record[`${mapping.table}id`] || null
  };
}

async function listRows(dataverse, mapping) {
  const select = [
    mapping.caseColumn,
    mapping.eventColumn,
    mapping.endColumn,
    mapping.departmentColumn,
    mapping.managerColumn,
    mapping.positionColumn,
    mapping.statusColumn,
    mapping.complianceColumn,
    mapping.companyColumn
  ].filter(column => column && !column.startsWith('['));
  const filter = {
    startDate: CONFIG.startDate,
    endDate: CONFIG.endDate,
    companyFilter: CONFIG.companyFilter,
    eventColumn: mapping.eventColumn,
    companyColumn: mapping.companyColumn
  };
  return dataverse.listRows({
    table: mapping.table,
    select,
    filter,
    pagination: true
  });
}

async function extractOnboardingEvents(dataverse) {
  const events = [];
  const offerAcceptedRows = await listRows(dataverse, SOURCE_MAPPINGS.offerAccepted);
  for (const record of offerAcceptedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.offerAccepted, 'Offer Accepted');
    if (event) events.push(event);
  }
  const hrProfileCreatedRows = await listRows(dataverse, SOURCE_MAPPINGS.hrProfileCreated);
  for (const record of hrProfileCreatedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.hrProfileCreated, 'HR Profile Created');
    if (event) events.push(event);
  }
  const backgroundCheckInitiatedRows = await listRows(dataverse, SOURCE_MAPPINGS.backgroundCheckInitiated);
  for (const record of backgroundCheckInitiatedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.backgroundCheckInitiated, 'Background Check Initiated');
    if (event) events.push(event);
  }
  const backgroundCheckCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.backgroundCheckCompleted);
  for (const record of backgroundCheckCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.backgroundCheckCompleted, 'Background Check Completed');
    if (event) events.push(event);
  }
  const paperworkSentRows = await listRows(dataverse, SOURCE_MAPPINGS.paperworkSent);
  for (const record of paperworkSentRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.paperworkSent, 'Paperwork Sent To New Hire');
    if (event) events.push(event);
  }
  const paperworkCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.paperworkCompleted);
  for (const record of paperworkCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.paperworkCompleted, 'Paperwork Completed By New Hire');
    if (event) events.push(event);
  }
  const itProvisioningInitiatedRows = await listRows(dataverse, SOURCE_MAPPINGS.itProvisioningInitiated);
  for (const record of itProvisioningInitiatedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.itProvisioningInitiated, 'IT Account Provisioning Initiated');
    if (event) events.push(event);
  }
  const equipmentOrderedRows = await listRows(dataverse, SOURCE_MAPPINGS.equipmentOrdered);
  for (const record of equipmentOrderedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.equipmentOrdered, 'Equipment Ordered');
    if (event) events.push(event);
  }
  const equipmentDeliveredRows = await listRows(dataverse, SOURCE_MAPPINGS.equipmentDelivered);
  for (const record of equipmentDeliveredRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.equipmentDelivered, 'Equipment Delivered');
    if (event) events.push(event);
  }
  const workspaceSetupCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.workspaceSetupCompleted);
  for (const record of workspaceSetupCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.workspaceSetupCompleted, 'Workspace Setup Completed');
    if (event) events.push(event);
  }
  const orientationConductedRows = await listRows(dataverse, SOURCE_MAPPINGS.orientationConducted);
  for (const record of orientationConductedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.orientationConducted, 'First Day Orientation Conducted');
    if (event) events.push(event);
  }
  const trainingAssignedRows = await listRows(dataverse, SOURCE_MAPPINGS.trainingAssigned);
  for (const record of trainingAssignedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.trainingAssigned, 'Mandatory Training Assigned');
    if (event) events.push(event);
  }
  const trainingCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.trainingCompleted);
  for (const record of trainingCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.trainingCompleted, 'Mandatory Training Completed');
    if (event) events.push(event);
  }
  const teamIntroductionCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.teamIntroductionCompleted);
  for (const record of teamIntroductionCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.teamIntroductionCompleted, 'Team Introduction Completed');
    if (event) events.push(event);
  }
  const onboardingPlanCompletedRows = await listRows(dataverse, SOURCE_MAPPINGS.onboardingPlanCompleted);
  for (const record of onboardingPlanCompletedRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.onboardingPlanCompleted, 'Onboarding Plan Completed');
    if (event) events.push(event);
  }
  const checkIn30DayRows = await listRows(dataverse, SOURCE_MAPPINGS.checkIn30Day);
  for (const record of checkIn30DayRows) {
    const event = createEvent(record, SOURCE_MAPPINGS.checkIn30Day, '30-Day Check-in Performed');
    if (event) events.push(event);
  }
  const unique = new Map();
  for (const event of events) {
    const key = [event.OnboardingCase, event.ActivityName, event.SourceRecordId, event.EventTime].join('|');
    if (!unique.has(key)) unique.set(key, event);
  }
  return Array.from(unique.values()).sort((a, b) => {
    const caseOrder = a.OnboardingCase.localeCompare(b.OnboardingCase);
    return caseOrder || a.EventTime.localeCompare(b.EventTime) || a.ActivityName.localeCompare(b.ActivityName);
  });
}

function toCsv(rows) {
  const columns = [
    'OnboardingCase',
    'ActivityName',
    'EventTime',
    'EventEndTime',
    'DepartmentName',
    'HiringManagerName',
    'PositionTitle',
    'OnboardingStatus',
    'ComplianceDueDate'
  ];
  const escape = value => {
    if (value === null || value === undefined) return '';
    const text = String(value);
    return /[",\n\r]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
  };
  return [columns.join(','), ...rows.map(row => columns.map(column => escape(row[column])).join(','))].join('\n');
}

async function run(dataverse, blobStorage) {
  const rows = await extractOnboardingEvents(dataverse);
  const missingRequired = rows.filter(row => !row.OnboardingCase || !row.ActivityName || !row.EventTime);
  if (missingRequired.length > 0) throw new Error('Required event attributes are missing.');
  const invalidActivities = rows.filter(row => !ACTIVITIES.includes(row.ActivityName));
  if (invalidActivities.length > 0) throw new Error('An activity outside the approved activity list was produced.');
  const csv = toCsv(rows);
  const path = `[Your container]/Employee Onboarding/${new Date().toISOString().slice(0, 7)}/onboarding_events_${Date.now()}.csv`;
  await blobStorage.writeText(path, csv, { contentType: 'text/csv', encoding: 'utf-8' });
  return { path, rowCount: rows.length, activities: ACTIVITIES };
}

module.exports = { run, extractOnboardingEvents, toCsv };