Ihre Record to Report – Journaleintrag Daten-Vorlage.

import pandas as pd
import numpy as np
import datetime

# --- Configuration ---
# Define the input file name from BlackLine
input_filename = 'blackline_report.csv'
# Define the output file for ProcessMind
output_filename = 'event_log.csv'

# --- Column Mapping ---
# Map the BlackLine report column names to the required event log attribute names.
# Adjust the keys in this dictionary to match the exact column headers in your CSV export.
column_mapping = {
    'Journal ID': 'JournalEntryId',
    'Journal Type': 'JournalEntryType',
    'Company': 'CompanyCode',
    'Amount': 'JournalEntryAmount',
    'Current Status': 'JournalEntryStatus',
    'Rejection Reason': 'RejectionReason',
    'Preparer': 'PreparerUserName',
    'Submitter': 'SubmitterUserName',
    'Reviewer': 'ReviewerUserName',
    'Approver': 'ApproverUserName',
    'Poster': 'PosterUserName',
    'Verifier': 'VerifierUserName',
    'Reverser': 'ReverserUserName',
    'Reconciler': 'ReconcilerUserName',
    'Creation Date': 'CreationTimestamp',
    'Attachment Date': 'AttachmentTimestamp',
    'Submission Date': 'SubmissionTimestamp',
    'Review Date': 'ReviewTimestamp',
    'Rejection Date': 'RejectionTimestamp',
    'Resubmission Date': 'ResubmissionTimestamp',
    'Approval Date': 'ApprovalTimestamp',
    'Rescind Date': 'RescindTimestamp',
    'Posting Date': 'PostingTimestamp',
    'Verification Date': 'VerificationTimestamp',
    'Reversal Date': 'ReversalTimestamp',
    'Reconciliation Date': 'ReconciliationTimestamp'
}

# --- Data Loading ---
try:
    df_raw = pd.read_csv(input_filename)
except FileNotFoundError:
    print(f"Error: The file {input_filename} was not found. Please ensure it's in the same directory as the script.")
    exit()

# Rename columns based on the mapping
df = df_raw.rename(columns=column_mapping)

# Ensure all required timestamp columns exist, fill with NaT if not
for col in column_mapping.values():
    if 'Timestamp' in col and col not in df.columns:
        df[col] = pd.NaT

# Convert date columns to datetime objects, coercing errors to NaT (Not a Time)
date_cols = [col for col in df.columns if 'Timestamp' in col]
for col in date_cols:
    df[col] = pd.to_datetime(df[col], errors='coerce')

all_events = []
last_data_update = datetime.datetime.now()

# --- Activity Extraction ---

# 1. Journal Entry Created
event_created = df[df['CreationTimestamp'].notna()].copy()
event_created['ActivityName'] = 'Journal Entry Created'
event_created['EventTime'] = event_created['CreationTimestamp']
event_created['UserName'] = event_created.get('PreparerUserName')
all_events.append(event_created)

# 2. Supporting Documentation Attached
if 'AttachmentTimestamp' in df.columns:
    event_attached = df[df['AttachmentTimestamp'].notna()].copy()
    event_attached['ActivityName'] = 'Supporting Documentation Attached'
    event_attached['EventTime'] = event_attached['AttachmentTimestamp']
    # Assuming the submitter or preparer attached the document
    event_attached['UserName'] = event_attached.get('SubmitterUserName', event_attached.get('PreparerUserName'))
    all_events.append(event_attached)

# 3. Journal Entry Submitted
event_submitted = df[df['SubmissionTimestamp'].notna()].copy()
event_submitted['ActivityName'] = 'Journal Entry Submitted'
event_submitted['EventTime'] = event_submitted['SubmissionTimestamp']
event_submitted['UserName'] = event_submitted.get('SubmitterUserName')
all_events.append(event_submitted)

# 4. Journal Entry Reviewed
if 'ReviewTimestamp' in df.columns:
    event_reviewed = df[df['ReviewTimestamp'].notna()].copy()
    event_reviewed['ActivityName'] = 'Journal Entry Reviewed'
    event_reviewed['EventTime'] = event_reviewed['ReviewTimestamp']
    event_reviewed['UserName'] = event_reviewed.get('ReviewerUserName')
    all_events.append(event_reviewed)

# 5. Journal Entry Rejected
if 'RejectionTimestamp' in df.columns:
    event_rejected = df[df['RejectionTimestamp'].notna()].copy()
    event_rejected['ActivityName'] = 'Journal Entry Rejected'
    event_rejected['EventTime'] = event_rejected['RejectionTimestamp']
    event_rejected['UserName'] = event_rejected.get('ReviewerUserName', event_rejected.get('ApproverUserName'))
    all_events.append(event_rejected)

# 6. Entry Corrected and Resubmitted
if 'ResubmissionTimestamp' in df.columns:
    event_resubmitted = df[df['ResubmissionTimestamp'].notna()].copy()
    event_resubmitted = event_resubmitted[event_resubmitted['ResubmissionTimestamp'] > event_resubmitted.get('RejectionTimestamp', pd.NaT)]
    event_resubmitted['ActivityName'] = 'Entry Corrected and Resubmitted'
    event_resubmitted['EventTime'] = event_resubmitted['ResubmissionTimestamp']
    event_resubmitted['UserName'] = event_resubmitted.get('SubmitterUserName')
    all_events.append(event_resubmitted)

# 7. Journal Entry Approved
event_approved = df[df['ApprovalTimestamp'].notna()].copy()
event_approved['ActivityName'] = 'Journal Entry Approved'
event_approved['EventTime'] = event_approved['ApprovalTimestamp']
event_approved['UserName'] = event_approved.get('ApproverUserName')
all_events.append(event_approved)

# 8. Approval Rescinded
if 'RescindTimestamp' in df.columns:
    event_rescinded = df[df['RescindTimestamp'].notna()].copy()
    event_rescinded['ActivityName'] = 'Approval Rescinded'
    event_rescinded['EventTime'] = event_rescinded['RescindTimestamp']
    event_rescinded['UserName'] = event_rescinded.get('ApproverUserName')
    all_events.append(event_rescinded)

# 9. Journal Entry Posted
event_posted = df[df['PostingTimestamp'].notna()].copy()
event_posted['ActivityName'] = 'Journal Entry Posted'
event_posted['EventTime'] = event_posted['PostingTimestamp']
event_posted['UserName'] = event_posted.get('PosterUserName')
all_events.append(event_posted)

# 10. Posting Verified
if 'VerificationTimestamp' in df.columns:
    event_verified = df[df['VerificationTimestamp'].notna()].copy()
    event_verified['ActivityName'] = 'Posting Verified'
    event_verified['EventTime'] = event_verified['VerificationTimestamp']
    event_verified['UserName'] = event_verified.get('VerifierUserName')
    all_events.append(event_verified)

# 11. Journal Entry Reversal Processed
if 'ReversalTimestamp' in df.columns:
    event_reversal = df[df['ReversalTimestamp'].notna()].copy()
    event_reversal['ActivityName'] = 'Journal Entry Reversal Processed'
    event_reversal['EventTime'] = event_reversal['ReversalTimestamp']
    event_reversal['UserName'] = event_reversal.get('ReverserUserName')
    all_events.append(event_reversal)

# 12. Journal Entry Reconciled
if 'ReconciliationTimestamp' in df.columns:
    event_reconciled = df[df['ReconciliationTimestamp'].notna()].copy()
    event_reconciled['ActivityName'] = 'Journal Entry Reconciled'
    event_reconciled['EventTime'] = event_reconciled['ReconciliationTimestamp']
    event_reconciled['UserName'] = event_reconciled.get('ReconcilerUserName')
    all_events.append(event_reconciled)


# --- Final Assembly ---
if all_events:
    event_log = pd.concat(all_events, ignore_index=True)

    # Add required constant value attributes
    event_log['SourceSystem'] = 'BlackLine'
    event_log['LastDataUpdate'] = last_data_update

    # Select and order columns for the final output
    final_columns = [
        'JournalEntryId',
        'ActivityName',
        'EventTime',
        'SourceSystem',
        'LastDataUpdate',
        'UserName',
        'JournalEntryType',
        'CompanyCode',
        'JournalEntryAmount',
        'JournalEntryStatus',
        'RejectionReason'
    ]

    # Filter for only the columns that exist in the dataframe to avoid errors
    existing_columns = [col for col in final_columns if col in event_log.columns]
    event_log = event_log[existing_columns]

    # Sort the event log
    event_log = event_log.sort_values(by=['JournalEntryId', 'EventTime'])

    # Save to CSV
    event_log.to_csv(output_filename, index=False, date_format='%Y-%m-%d %H:%M:%S')
    print(f"Successfully created event log: {output_filename}")
else:
    print("No events were generated. Please check your input file and column mappings.")

import requests
import pandas as pd
from datetime import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your BlackLine API credentials and base URL
CLIENT_ID = class="hl-string">'[Your Client ID]'
CLIENT_SECRET = class="hl-string">'[Your Client Secret]'
REFRESH_TOKEN = class="hl-string">'[Your Refresh Token]'
BASE_URL = class="hl-string">'[Your BlackLine API Base URL, e.g., https://api.blackline.com]'

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
START_DATE = class="hl-string">'2023-01-01' class=class="hl-string">"hl-comment"># Format: YYYY-MM-DD
END_DATE = class="hl-string">'2023-12-31'   class=class="hl-string">"hl-comment"># Format: YYYY-MM-DD
LIMIT = 100 class=class="hl-string">"hl-comment"># Records per page

def get_access_token():
    class="hl-string">"""Authenticates with BlackLine to get an access token."""
    auth_url = fclass="hl-string">"{BASE_URL}/api/oauth/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'refresh_token',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET,
        class="hl-string">'refresh_token': REFRESH_TOKEN
    }
    response = requests.post(auth_url, data=payload)
    response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
    return response.json()[class="hl-string">'access_token']

def main():
    class="hl-string">"""Main function to extract, transform, and save journal entry event data."""
    print(class="hl-string">"Starting BlackLine Journal Entry extraction...")
    
    try:
        access_token = get_access_token()
        headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}'}
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Authentication failed: {e}")
        return

    events = []
    last_data_update = datetime.utcnow().isoformat() + class="hl-string">'Z'

    class=class="hl-string">"hl-comment"># 1. Fetch all journals within the date range
    print(fclass="hl-string">"Fetching journals created between {START_DATE} and {END_DATE}...")
    journals = []
    offset = 0
    while True:
        journals_url = fclass="hl-string">"{BASE_URL}/api/v2/journals"
        params = {
            class="hl-string">'createdStartDate': START_DATE,
            class="hl-string">'createdEndDate': END_DATE,
            class="hl-string">'limit': LIMIT,
            class="hl-string">'offset': offset
        }
        try:
            response = requests.get(journals_url, headers=headers, params=params)
            response.raise_for_status()
            data = response.json()
            journals.extend(data.get(class="hl-string">'data', []))
            if class="hl-string">'nextLink' not in data or not data.get(class="hl-string">'data'):
                break
            offset += LIMIT
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching journals: {e}")
            break

    print(fclass="hl-string">"Found {len(journals)} journals. Now fetching detailed events for each...")

    class=class="hl-string">"hl-comment"># 2. For each journal, fetch history and attachments
    for journal in journals:
        journal_id = journal.get(class="hl-string">'id')
        if not journal_id:
            continue

        class=class="hl-string">"hl-comment"># --- Activity: Journal Entry Created ---
        events.append({
            class="hl-string">'JournalEntryId': journal_id,
            class="hl-string">'ActivityName': class="hl-string">'Journal Entry Created',
            class="hl-string">'EventTime': journal.get(class="hl-string">'createdDate'),
            class="hl-string">'UserName': journal.get(class="hl-string">'createdBy', {}).get(class="hl-string">'username'),
            class="hl-string">'JournalEntryType': journal.get(class="hl-string">'type'),
            class="hl-string">'CompanyCode': journal.get(class="hl-string">'companyCode'),
            class="hl-string">'JournalEntryAmount': journal.get(class="hl-string">'totalDebit'),
            class="hl-string">'JournalEntryStatus': journal.get(class="hl-string">'status'),
            class="hl-string">'RejectionReason': None
        })

        class=class="hl-string">"hl-comment"># Fetch history for other activities
        try:
            history_url = fclass="hl-string">"{BASE_URL}/api/v2/journals/{journal_id}/history"
            history_response = requests.get(history_url, headers=headers)
            history_response.raise_for_status()
            history_items = history_response.json().get(class="hl-string">'data', [])
            
            for item in history_items:
                activity_name = None
                rejection_reason = None
                
                class=class="hl-string">"hl-comment"># This mapping requires knowledge of your specific BlackLine history event descriptions
                class=class="hl-string">"hl-comment"># The following are common examples. Adjust them based on your BlackLine configuration.
                event_description = item.get(class="hl-string">'description', class="hl-string">'').lower()
                if class="hl-string">'submitted' in event_description:
                    activity_name = class="hl-string">'Journal Entry Submitted'
                elif class="hl-string">'reviewed' in event_description:
                    activity_name = class="hl-string">'Journal Entry Reviewed'
                elif class="hl-string">'rejected' in event_description:
                    activity_name = class="hl-string">'Journal Entry Rejected'
                    rejection_reason = item.get(class="hl-string">'comment') class=class="hl-string">"hl-comment"># Assuming comment contains the reason
                elif class="hl-string">'resubmitted' in event_description:
                    activity_name = class="hl-string">'Entry Corrected and Resubmitted'
                elif class="hl-string">'approved' in event_description and class="hl-string">'final' in event_description: class=class="hl-string">"hl-comment"># Example for final approval
                    activity_name = class="hl-string">'Journal Entry Approved'
                elif class="hl-string">'approval rescinded' in event_description:
                    activity_name = class="hl-string">'Approval Rescinded'
                elif class="hl-string">'posted' in event_description:
                    activity_name = class="hl-string">'Journal Entry Posted'
                elif class="hl-string">'posting verified' in event_description:
                     activity_name = class="hl-string">'Posting Verified'
                
                if activity_name:
                    events.append({
                        class="hl-string">'JournalEntryId': journal_id,
                        class="hl-string">'ActivityName': activity_name,
                        class="hl-string">'EventTime': item.get(class="hl-string">'eventDate'),
                        class="hl-string">'UserName': item.get(class="hl-string">'user', {}).get(class="hl-string">'username'),
                        class="hl-string">'JournalEntryType': journal.get(class="hl-string">'type'),
                        class="hl-string">'CompanyCode': journal.get(class="hl-string">'companyCode'),
                        class="hl-string">'JournalEntryAmount': journal.get(class="hl-string">'totalDebit'),
                        class="hl-string">'JournalEntryStatus': journal.get(class="hl-string">'status'),
                        class="hl-string">'RejectionReason': rejection_reason
                    })
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Could not fetch history for Journal ID {journal_id}: {e}")

        class=class="hl-string">"hl-comment"># Fetch attachments
        try:
            attachments_url = fclass="hl-string">"{BASE_URL}/api/v2/journals/{journal_id}/attachments"
            attachments_response = requests.get(attachments_url, headers=headers)
            attachments_response.raise_for_status()
            attachments = attachments_response.json().get(class="hl-string">'data', [])
            for attachment in attachments:
                events.append({
                    class="hl-string">'JournalEntryId': journal_id,
                    class="hl-string">'ActivityName': class="hl-string">'Supporting Documentation Attached',
                    class="hl-string">'EventTime': attachment.get(class="hl-string">'uploadedDate'),
                    class="hl-string">'UserName': attachment.get(class="hl-string">'uploadedBy', {}).get(class="hl-string">'username'),
                    class="hl-string">'JournalEntryType': journal.get(class="hl-string">'type'),
                    class="hl-string">'CompanyCode': journal.get(class="hl-string">'companyCode'),
                    class="hl-string">'JournalEntryAmount': journal.get(class="hl-string">'totalDebit'),
                    class="hl-string">'JournalEntryStatus': journal.get(class="hl-string">'status'),
                    class="hl-string">'RejectionReason': None
                })
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Could not fetch attachments for Journal ID {journal_id}: {e}")

        class=class="hl-string">"hl-comment"># --- Activities based on journal fields (may need customization) ---
        if journal.get(class="hl-string">'isReversed'):
            events.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Entry Reversal Processed',
                class="hl-string">'EventTime': journal.get(class="hl-string">'[reversalDate]'), class=class="hl-string">"hl-comment"># Placeholder for reversal date field
                class="hl-string">'UserName': journal.get(class="hl-string">'[reversalUser]'), class=class="hl-string">"hl-comment"># Placeholder for user field
                class="hl-string">'JournalEntryType': journal.get(class="hl-string">'type'),
                class="hl-string">'CompanyCode': journal.get(class="hl-string">'companyCode'),
                class="hl-string">'JournalEntryAmount': journal.get(class="hl-string">'totalDebit'),
                class="hl-string">'JournalEntryStatus': class="hl-string">'Reversed',
                class="hl-string">'RejectionReason': None
            })

        if journal.get(class="hl-string">'reconciliationId'): class=class="hl-string">"hl-comment"># Assuming this indicates reconciliation
            events.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Entry Reconciled',
                class="hl-string">'EventTime': journal.get(class="hl-string">'[reconciliationCertifiedDate]'), class=class="hl-string">"hl-comment"># Placeholder for certified date
                class="hl-string">'UserName': journal.get(class="hl-string">'[reconciliationCertifier]'), class=class="hl-string">"hl-comment"># Placeholder for certifier user
                class="hl-string">'JournalEntryType': journal.get(class="hl-string">'type'),
                class="hl-string">'CompanyCode': journal.get(class="hl-string">'companyCode'),
                class="hl-string">'JournalEntryAmount': journal.get(class="hl-string">'totalDebit'),
                class="hl-string">'JournalEntryStatus': journal.get(class="hl-string">'status'),
                class="hl-string">'RejectionReason': None
            })
        class=class="hl-string">"hl-comment"># Introduce a small delay to respect API rate limits
        time.sleep(0.1)

    if not events:
        print(class="hl-string">"No events were generated. Please check your date range and API access.")
        return

    class=class="hl-string">"hl-comment"># 3. Create DataFrame and save to CSV
    print(class="hl-string">"Transforming data and saving to CSV...")
    df = pd.DataFrame(events)

    class=class="hl-string">"hl-comment"># Add metadata columns
    df[class="hl-string">'SourceSystem'] = class="hl-string">'BlackLine'
    df[class="hl-string">'LastDataUpdate'] = last_data_update
    
    class=class="hl-string">"hl-comment"># Ensure required columns are present
    final_columns = [
        class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'UserName', class="hl-string">'JournalEntryType', class="hl-string">'CompanyCode', class="hl-string">'JournalEntryAmount', class="hl-string">'JournalEntryStatus', class="hl-string">'RejectionReason'
    ]
    for col in final_columns:
        if col not in df.columns:
            df[col] = None
            
    df = df[final_columns] class=class="hl-string">"hl-comment"># Order columns

    output_filename = class="hl-string">'blackline_journal_entry_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")

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