Mẫu dữ liệu quản lý chi phí

/*
 This script represents the data transformation logic to be applied to the downloaded
 Brex CSV file. Treat the CSV as a source table named 'brex_export'.
 Column names like 'ExpenseReportId', 'CreatedAt', 'SubmittedAt', 'ManagerApprovedAt',
 'PolicyViolationReason', 'FinanceApprovedAt', 'RejectedAt', 'RevisionRequestedAt',
 'ReimbursementScheduledAt', 'ReimbursementExecutedAt', and 'PostedToLedgerAt' are
 representative and should be mapped to the actual column names in your export file.
*/

SELECT
  ExpenseReportId,
  'Expense Report Created' AS ActivityName,
  CreatedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE CreatedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Receipts Attached' AS ActivityName,
  ReceiptAttachedAt AS EventTime, -- Note: This timestamp may not be available and might need to be inferred.
  TotalAmount,
  EmployeeDepartment,
  EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE ReceiptAttachedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Expense Report Submitted' AS ActivityName,
  SubmittedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE SubmittedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Policy Violation Flagged' AS ActivityName,
  PolicyViolationFlaggedAt AS EventTime, -- Note: This specific timestamp may need to be inferred from the submission or review time.
  TotalAmount,
  EmployeeDepartment,
  'System' AS EventUser,
  ReportStatus,
  'true' AS PolicyViolationFlag
FROM brex_export
WHERE PolicyViolationReason IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Manager Review Started' AS ActivityName,
  SubmittedAt AS EventTime, -- This event is inferred to start immediately upon submission.
  TotalAmount,
  EmployeeDepartment,
  ManagerName AS EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE SubmittedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Manager Approved' AS ActivityName,
  ManagerApprovedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  ManagerName AS EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE ManagerApprovedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Manager Rejected' AS ActivityName,
  RejectedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  ManagerName AS EventUser,
  'Rejected' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE RejectedAt IS NOT NULL AND FinanceApprovedAt IS NULL -- Assumes rejection before finance approval is a manager rejection.

UNION ALL

SELECT
  ExpenseReportId,
  'Report Sent For Revision' AS ActivityName,
  RevisionRequestedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  ApproverName AS EventUser,
  'Needs Revision' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE RevisionRequestedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Finance Review Started' AS ActivityName,
  ManagerApprovedAt AS EventTime, -- This event is inferred to start immediately after manager approval.
  TotalAmount,
  EmployeeDepartment,
  '[Configure Finance Reviewer]' AS EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE ManagerApprovedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Finance Approved' AS ActivityName,
  FinanceApprovedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  '[Configure Finance Reviewer]' AS EventUser,
  ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE FinanceApprovedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Finance Rejected' AS ActivityName,
  RejectedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  '[Configure Finance Reviewer]' AS EventUser,
  'Rejected' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE RejectedAt IS NOT NULL AND ManagerApprovedAt IS NOT NULL -- Assumes rejection after manager approval is a finance rejection.

UNION ALL

SELECT
  ExpenseReportId,
  'Reimbursement Scheduled' AS ActivityName,
  ReimbursementScheduledAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  'System' AS EventUser,
  'Processing Payment' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE ReimbursementScheduledAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Reimbursement Executed' AS ActivityName,
  ReimbursementExecutedAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  'System' AS EventUser,
  'Paid' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE ReimbursementExecutedAt IS NOT NULL

UNION ALL

SELECT
  ExpenseReportId,
  'Accounting Posted' AS ActivityName,
  PostedToLedgerAt AS EventTime,
  TotalAmount,
  EmployeeDepartment,
  'System' AS EventUser,
  'Closed' AS ReportStatus,
  CASE WHEN PolicyViolationReason IS NOT NULL THEN 'true' ELSE 'false' END AS PolicyViolationFlag
FROM brex_export
WHERE PostedToLedgerAt IS NOT NULL;

import requests
import pandas as pd
import os
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Brex User Token
API_TOKEN = class="hl-string">"[Your Brex API Token]"
class=class="hl-string">"hl-comment"># Set the desired date range for the extraction (YYYY-MM-DD)
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"
OUTPUT_FILENAME = class="hl-string">"brex_expense_event_log.csv"

class=class="hl-string">"hl-comment"># --- API and Headers ---
API_URL = class="hl-string">"https://platform.brex.com/v2/expenses"
HEADERS = {
    class="hl-string">"Authorization": fclass="hl-string">"Bearer {API_TOKEN}",
    class="hl-string">"Content-Type": class="hl-string">"application/json",
}

def fetch_expenses(start_date, end_date):
    class="hl-string">"""Fetches all expenses within a date range, handling pagination."""
    all_expenses = []
    cursor = None
    print(fclass="hl-string">"Fetching expenses from {start_date} to {end_date}...")

    while True:
        params = {
            class="hl-string">"limit": 100,
            class="hl-string">"updated_at_start": fclass="hl-string">"{start_date}T00:00:00Z",
        }
        if cursor:
            params[class="hl-string">'cursor'] = cursor

        response = requests.get(API_URL, headers=HEADERS, params=params)
        response.raise_for_status()  class=class="hl-string">"hl-comment"># Will raise an exception for HTTP error codes
        data = response.json()
        
        expenses = data.get(class="hl-string">'items', [])
        if not expenses:
            break

        for expense in expenses:
            class=class="hl-string">"hl-comment"># Filter expenses to be within the specified end date
            updated_at = datetime.fromisoformat(expense[class="hl-string">'updated_at'].replace(class="hl-string">'Z', class="hl-string">'+00:00'))
            if updated_at.strftime(class="hl-string">'%Y-%m-%d') <= end_date:
                all_expenses.append(expense)
        
        class=class="hl-string">"hl-comment"># Stop if we've gone past our end date or there are no more pages
        last_expense_date = datetime.fromisoformat(expenses[-1][class="hl-string">'updated_at'].replace(class="hl-string">'Z', class="hl-string">'+00:00')).strftime(class="hl-string">'%Y-%m-%d')
        if last_expense_date > end_date or not data.get(class="hl-string">'next_cursor'):
            break

        cursor = data.get(class="hl-string">'next_cursor')
        print(fclass="hl-string">"Fetched {len(all_expenses)} expenses so far...")

    print(fclass="hl-string">"Total expenses fetched: {len(all_expenses)}")
    return all_expenses

def process_expenses_to_events(expenses):
    class="hl-string">"""Transforms raw expense data into a structured event log."""
    event_log = []

    for expense in expenses:
        report_id = expense[class="hl-string">'id']
        total_amount = expense.get(class="hl-string">'original_amount', {}).get(class="hl-string">'amount', 0) / 100.0
        employee_department = expense.get(class="hl-string">'user', {}).get(class="hl-string">'department', {}).get(class="hl-string">'name')
        submitter_user = expense.get(class="hl-string">'user', {}).get(class="hl-string">'name')

        common_attributes = {
            class="hl-string">'ExpenseReportId': report_id,
            class="hl-string">'TotalAmount': total_amount,
            class="hl-string">'EmployeeDepartment': employee_department,
            class="hl-string">'ReportStatus': expense.get(class="hl-string">'status'),
            class="hl-string">'PolicyViolationFlag': bool(expense.get(class="hl-string">'policy_violations'))
        }

        class=class="hl-string">"hl-comment"># 1. Expense Report Created
        event_log.append({
            **common_attributes,
            class="hl-string">'ActivityName': class="hl-string">'Expense Report Created',
            class="hl-string">'EventTime': expense[class="hl-string">'created_at'],
            class="hl-string">'EventUser': submitter_user
        })

        class=class="hl-string">"hl-comment"># 2. Receipts Attached
        if expense.get(class="hl-string">'receipts'):
            class=class="hl-string">"hl-comment"># Use the memo_updated_at as a proxy for when receipts/memos were last touched
            event_time = expense.get(class="hl-string">'memo_updated_at') or expense[class="hl-string">'updated_at']
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Receipts Attached',
                class="hl-string">'EventTime': event_time,
                class="hl-string">'EventUser': submitter_user
            })

        class=class="hl-string">"hl-comment"># 3. Policy Violation Flagged
        if common_attributes[class="hl-string">'PolicyViolationFlag']:
            event_log.append({
                **common_attributes,
                class="hl-string">'ActivityName': class="hl-string">'Policy Violation Flagged',
                class="hl-string">'EventTime': expense[class="hl-string">'updated_at'], class=class="hl-string">"hl-comment"># Inferred timestamp
                class="hl-string">'EventUser': class="hl-string">'System'
            })

        class=class="hl-string">"hl-comment"># Process status history for workflow events
        if expense.get(class="hl-string">'status_history'):
            for history_item in sorted(expense[class="hl-string">'status_history'], key=lambda x: x[class="hl-string">'commented_at']):
                activity_name = None
                status = history_item[class="hl-string">'status']
                event_time = history_item[class="hl-string">'commented_at']
                event_user = history_item.get(class="hl-string">'user', {}).get(class="hl-string">'name', class="hl-string">'System')

                if status == class="hl-string">'PENDING_APPROVAL':
                    event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Expense Report Submitted', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': submitter_user})
                    event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Manager Review Started', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
                elif status == class="hl-string">'APPROVED':
                    class=class="hl-string">"hl-comment"># Differentiate Manager vs Finance approval by sequence, a common simplification
                    manager_approved_exists = any(e[class="hl-string">'ActivityName'] == class="hl-string">'Manager Approved' and e[class="hl-string">'ExpenseReportId'] == report_id for e in event_log)
                    if not manager_approved_exists:
                        event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Manager Approved', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
                        class=class="hl-string">"hl-comment"># Infer Finance Review Started right after manager approval
                        event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Finance Review Started', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': None})
                    else:
                        event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Finance Approved', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
                elif status == class="hl-string">'REJECTED':
                    class=class="hl-string">"hl-comment"># Differentiate Manager vs Finance rejection by sequence
                    manager_approved_exists = any(e[class="hl-string">'ActivityName'] == class="hl-string">'Manager Approved' and e[class="hl-string">'ExpenseReportId'] == report_id for e in event_log)
                    if not manager_approved_exists:
                        event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Manager Rejected', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
                    else:
                        event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Finance Rejected', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
                elif status == class="hl-string">'NEEDS_REVIEW':
                    event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Report Sent For Revision', class="hl-string">'EventTime': event_time, class="hl-string">'EventUser': event_user})
        
        class=class="hl-string">"hl-comment"># Reimbursement and Accounting Events
        payment_status = expense.get(class="hl-string">'payment_status')
        if payment_status in [class="hl-string">'PROCESSING', class="hl-string">'PAID']:
             event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Reimbursement Scheduled', class="hl-string">'EventTime': expense[class="hl-string">'updated_at'], class="hl-string">'EventUser': class="hl-string">'System'})
        if payment_status == class="hl-string">'PAID':
             event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Reimbursement Executed', class="hl-string">'EventTime': expense.get(class="hl-string">'payment_details',{}).get(class="hl-string">'paid_at') or expense[class="hl-string">'updated_at'], class="hl-string">'EventUser': class="hl-string">'System'})

        if expense.get(class="hl-string">'accounting_sync_status') == class="hl-string">'SYNCED':
            event_log.append({**common_attributes, class="hl-string">'ActivityName': class="hl-string">'Accounting Posted', class="hl-string">'EventTime': expense.get(class="hl-string">'synced_at') or expense[class="hl-string">'updated_at'], class="hl-string">'EventUser': class="hl-string">'System'})

    return event_log

if __name__ == class="hl-string">"__main__":
    try:
        raw_expenses = fetch_expenses(START_DATE, END_DATE)
        if raw_expenses:
            event_data = process_expenses_to_events(raw_expenses)
            df = pd.DataFrame(event_data)
            class=class="hl-string">"hl-comment"># Ensure required columns are present and ordered correctly
            df = df[[class="hl-string">'ExpenseReportId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'TotalAmount', class="hl-string">'EmployeeDepartment', class="hl-string">'EventUser', class="hl-string">'ReportStatus', class="hl-string">'PolicyViolationFlag']]
            df.sort_values(by=[class="hl-string">'ExpenseReportId', class="hl-string">'EventTime'], inplace=True)
            df.to_csv(OUTPUT_FILENAME, index=False)
            print(fclass="hl-string">"Successfully created event log: {OUTPUT_FILENAME}")
        else:
            print(class="hl-string">"No expenses found for the given date range.")
    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"An HTTP error occurred: {e}")
        print(fclass="hl-string">"Response body: {e.response.text}")
    except Exception as e:
        print(fclass="hl-string">"An unexpected error occurred: {e}")