클레임 처리 데이터 템플릿

import pandas as pd
from simple_salesforce import Salesforce
from datetime import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Salesforce Credentials (use environment variables or a secure vault in production)
SF_USERNAME = class="hl-string">'[your_salesforce_username]'
SF_PASSWORD = class="hl-string">'[your_salesforce_password]'
SF_TOKEN = class="hl-string">'[your_salesforce_security_token]'

class=class="hl-string">"hl-comment"># Date Range for Extraction (ISO 8601 format)
START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
END_DATE = class="hl-string">'2023-12-31T23:59:59Z'

class=class="hl-string">"hl-comment"># Salesforce Object & Field API Names (REPLACE with your FSC configuration)
CLAIM_OBJECT_API_NAME = class="hl-string">'Claim__c' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'Case', class="hl-string">'Claim__c'
CLAIM_ID_FIELD = class="hl-string">'Name' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'CaseNumber', class="hl-string">'Name'
STATUS_FIELD_API_NAME = class="hl-string">'Status__c' class=class="hl-string">"hl-comment"># e.g., class="hl-string">'Status', class="hl-string">'ClaimStatus__c'
OWNER_ID_FIELD = class="hl-string">'OwnerId'
CREATED_DATE_FIELD = class="hl-string">'CreatedDate'
LOSS_ESTIMATE_FIELD = class="hl-string">'Loss_Estimate__c' class=class="hl-string">"hl-comment"># Field for class="hl-string">'Loss Assessed' event
CLAIM_TYPE_FIELD = class="hl-string">'Claim_Type__c'
SUBMISSION_CHANNEL_FIELD = class="hl-string">'Submission_Channel__c'
TOTAL_CLAIM_AMOUNT_FIELD = class="hl-string">'Total_Claim_Amount__c'

class=class="hl-string">"hl-comment"># Map your Salesforce status values to standardized Activity Names
class=class="hl-string">"hl-comment"># CRITICAL: This mapping must be configured for your specific Salesforce setup
STATUS_TO_ACTIVITY_MAP = {
    class="hl-string">'New': class="hl-string">'Claim Registered',
    class="hl-string">'Under Review': class="hl-string">'Initial Review Performed',
    class="hl-string">'Pending Customer Information': class="hl-string">'Additional Information Requested',
    class=class="hl-string">"hl-comment"># Use a generic class="hl-string">'Under Review' for info received, as SF history doesn't track the *reason* for a status change
    class=class="hl-string">"hl-comment"># The sequence will show class="hl-string">'Pending' -> class="hl-string">'Under Review', implying receipt of information.
    class="hl-string">'Investigation in Progress': class="hl-string">'Investigation Started',
    class="hl-string">'Pending Decision': class="hl-string">'Investigation Completed',
    class="hl-string">'Approved': class="hl-string">'Claim Decision Made',
    class="hl-string">'Settlement Offered': class="hl-string">'Settlement Offered',
    class="hl-string">'Approved for Payment': class="hl-string">'Payment Authorized',
    class="hl-string">'Paid': class="hl-string">'Payment Issued',
    class="hl-string">'Rejected': class="hl-string">'Claim Rejected',
    class="hl-string">'Denied': class="hl-string">'Claim Rejected',
    class="hl-string">'Closed': class="hl-string">'Claim Closed'
}

def main():
    class="hl-string">"""Main function to extract, transform, and save the event log."""
    print(class="hl-string">"Connecting to Salesforce...")
    sf = Salesforce(username=SF_USERNAME, password=SF_PASSWORD, security_token=SF_TOKEN)
    print(class="hl-string">"Connection successful.")

    class=class="hl-string">"hl-comment"># 1. Fetch all relevant claims to get base attributes and created date
    print(fclass="hl-string">"Fetching claims created between {START_DATE} and {END_DATE}...")
    claim_query = fclass="hl-string">"""
        SELECT 
            Id, {CLAIM_ID_FIELD}, {CREATED_DATE_FIELD}, {OWNER_ID_FIELD}, Owner.Name,
            {CLAIM_TYPE_FIELD}, {DEPARTMENT_FIELD}, {SUBMISSION_CHANNEL_FIELD}, {TOTAL_CLAIM_AMOUNT_FIELD}, {STATUS_FIELD_API_NAME}
        FROM {CLAIM_OBJECT_API_NAME}
        WHERE {CREATED_DATE_FIELD} >= {START_DATE} AND {CREATED_DATE_FIELD} <= {END_DATE}
    """
    claim_results = sf.query_all(claim_query)
    claims_df = pd.DataFrame(claim_results[class="hl-string">'records']).drop(columns=[class="hl-string">'attributes'])
    if claims_df.empty:
        print(class="hl-string">"No claims found for the specified date range. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Create a lookup dictionary for claim attributes
    class=class="hl-string">"hl-comment"># Handle nested Owner.Name structure
    if class="hl-string">'Owner' in claims_df.columns:
        claims_df[class="hl-string">'AssignedAdjuster'] = claims_df[class="hl-string">'Owner'].apply(lambda x: x[class="hl-string">'Name'] if x else None)
        claims_df = claims_df.drop(columns=[class="hl-string">'Owner'])
    claims_lookup = claims_df.set_index(class="hl-string">'Id').to_dict(class="hl-string">'index')
    claim_ids = tuple(claims_df[class="hl-string">'Id'].unique())

    print(fclass="hl-string">"Found {len(claim_ids)} claims. Now fetching history...")

    class=class="hl-string">"hl-comment"># 2. Fetch all field history for the identified claims
    history_query = fclass="hl-string">"""
        SELECT ParentId, Field, OldValue, NewValue, CreatedDate
        FROM {CLAIM_OBJECT_API_NAME}History
        WHERE ParentId IN {claim_ids}
        ORDER BY CreatedDate ASC
    """
    history_results = sf.query_all(history_query)
    history_df = pd.DataFrame(history_results[class="hl-string">'records']).drop(columns=[class="hl-string">'attributes'])

    class=class="hl-string">"hl-comment"># 3. Generate event log from claims and history data
    event_log = []
    extraction_timestamp = datetime.utcnow().isoformat() + class="hl-string">'Z'

    for claim_id, claim_data in claims_lookup.items():
        class=class="hl-string">"hl-comment"># Event: Claim Submitted
        event_log.append({
            class="hl-string">'ClaimId': claim_data.get(CLAIM_ID_FIELD),
            class="hl-string">'ActivityName': class="hl-string">'Claim Submitted',
            class="hl-string">'EventTime': claim_data.get(CREATED_DATE_FIELD),
            class="hl-string">'AssignedAdjuster': claim_data.get(class="hl-string">'AssignedAdjuster'),
            class="hl-string">'ClaimStatus': class="hl-string">'Submitted', class=class="hl-string">"hl-comment"># Initial status at creation
            class="hl-string">'ClaimType': claim_data.get(CLAIM_TYPE_FIELD),
            class="hl-string">'SubmissionChannel': claim_data.get(SUBMISSION_CHANNEL_FIELD),
            class="hl-string">'TotalClaimAmount': claim_data.get(TOTAL_CLAIM_AMOUNT_FIELD)
        })
        
        class=class="hl-string">"hl-comment"># Filter history for the current claim
        claim_history = history_df[history_df[class="hl-string">'ParentId'] == claim_id]

        for _, row in claim_history.iterrows():
            activity_name = None
            
            class=class="hl-string">"hl-comment"># Map status changes to activities
            if row[class="hl-string">'Field'] == STATUS_FIELD_API_NAME:
                activity_name = STATUS_TO_ACTIVITY_MAP.get(row[class="hl-string">'NewValue'])
                class=class="hl-string">"hl-comment"># Special handling for class="hl-string">'Additional Information Received'
                if activity_name == class="hl-string">'Initial Review Performed' and row[class="hl-string">'OldValue'] == class="hl-string">'Pending Customer Information':
                    activity_name = class="hl-string">'Additional Information Received'

            class=class="hl-string">"hl-comment"># Event: Claim Assigned
            elif row[class="hl-string">'Field'] == class="hl-string">'Owner':
                activity_name = class="hl-string">'Claim Assigned'
            
            class=class="hl-string">"hl-comment"># Event: Loss Assessed
            elif row[class="hl-string">'Field'] == LOSS_ESTIMATE_FIELD and row[class="hl-string">'OldValue'] is None and row[class="hl-string">'NewValue'] is not None:
                activity_name = class="hl-string">'Loss Assessed'

            if activity_name:
                event_log.append({
                    class="hl-string">'ClaimId': claim_data.get(CLAIM_ID_FIELD),
                    class="hl-string">'ActivityName': activity_name,
                    class="hl-string">'EventTime': row[class="hl-string">'CreatedDate'],
                    class="hl-string">'AssignedAdjuster': claims_lookup.get(claim_id, {}).get(class="hl-string">'AssignedAdjuster'),
                    class="hl-string">'ClaimStatus': row[class="hl-string">'NewValue'] if row[class="hl-string">'Field'] == STATUS_FIELD_API_NAME else claims_lookup.get(claim_id, {}).get(STATUS_FIELD_API_NAME),
                    class="hl-string">'ClaimType': claims_lookup.get(claim_id, {}).get(CLAIM_TYPE_FIELD),
                    class="hl-string">'SubmissionChannel': claims_lookup.get(claim_id, {}).get(SUBMISSION_CHANNEL_FIELD),
                    class="hl-string">'TotalClaimAmount': claims_lookup.get(claim_id, {}).get(TOTAL_CLAIM_AMOUNT_FIELD)
                })
    
    if not event_log:
        print(class="hl-string">"No events generated. Check history tracking and status mappings. Exiting.")
        return

    class=class="hl-string">"hl-comment"># 4. Final Transformation and Export
    final_df = pd.DataFrame(event_log)

    class=class="hl-string">"hl-comment"># Add remaining required columns
    final_df[class="hl-string">'SourceSystem'] = class="hl-string">'SalesforceFSC'
    final_df[class="hl-string">'LastDataUpdate'] = extraction_timestamp
    final_df[class="hl-string">'EndTime'] = final_df[class="hl-string">'EventTime'] class=class="hl-string">"hl-comment"># Set EndTime equal to EventTime for point-in-time events
    final_df[class="hl-string">'Department'] = None class=class="hl-string">"hl-comment"># Placeholder - populate if available

    class=class="hl-string">"hl-comment"># Reorder columns to a standard event log format
    column_order = [
        class="hl-string">'ClaimId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EndTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'AssignedAdjuster', class="hl-string">'ClaimStatus', class="hl-string">'ClaimType', class="hl-string">'Department',
        class="hl-string">'SubmissionChannel', class="hl-string">'TotalClaimAmount'
    ]
    final_df = final_df[[col for col in column_order if col in final_df.columns]]
    
    class=class="hl-string">"hl-comment"># Sort by case and time
    final_df = final_df.sort_values(by=[class="hl-string">'ClaimId', class="hl-string">'EventTime']).reset_index(drop=True)

    class=class="hl-string">"hl-comment"># Export to CSV
    output_filename = class="hl-string">'claims_event_log.csv'
    final_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f%z')
    print(fclass="hl-string">"Successfully generated event log with {len(final_df)} events.")
    print(fclass="hl-string">"File saved as: {output_filename}")

if __name__ == class="hl-string">'__main__':
    class=class="hl-string">"hl-comment"># Added placeholders for fields not explicitly requested but common and needed for the script logic
    DEPARTMENT_FIELD = class="hl-string">'[Your_Department_Field__c]'
    main()