应付账款支付处理数据 template

import pandas as pd
import numpy as np
from datetime import datetime

# CONFIGURATION
# Update these filenames to match your exported files
FILE_BILLS = 'AP_Bills.csv'
FILE_PAYMENTS = 'AP_Payments.csv'
# Set to None if Audit History is unavailable
FILE_AUDIT = 'AP_Audit_History.csv' 

# COLUMNS MAPPING (Adjust based on your View column headers)
COL_BILL_NUM = 'Invoice Number'
COL_VENDOR = 'Vendor Name'
COL_AMOUNT = 'Total Amount'
COL_CREATED = 'Date Created'
COL_POSTED = 'Date Posted'
COL_DUE = 'Due Date'
COL_STATE = 'State'
COL_PO = 'PO Number'
COL_HOLD = 'On Hold'

COL_PAY_DATE = 'Payment Date'
COL_PAY_REF = 'Check/Payment Number'
COL_PAY_BILL_REF = 'Bill Number'
COL_PAY_STATE = 'State'

# OUTPUT STRUCTURE
event_log_columns = [
    'InvoiceNumber', 'ActivityName', 'EventTime', 'SourceSystem', 
    'LastDataUpdate', 'VendorName', 'InvoiceAmount', 'PurchaseOrderNumber', 
    'InvoiceDate', 'DueDate', 'InvoiceStatus'
]

def run_extraction():
    events = []
    current_time = datetime.now().isoformat()

    # 1. LOAD BILLS
    try:
        df_bills = pd.read_csv(FILE_BILLS)
        # Clean formatting (remove currency symbols if present)
        df_bills[COL_AMOUNT] = df_bills[COL_AMOUNT].replace('[\\$,]', '', regex=True).astype(float)
    except FileNotFoundError:
        print('Bills file not found.')
        return

    # 2. LOAD PAYMENTS
    try:
        df_pay = pd.read_csv(FILE_PAYMENTS)
    except FileNotFoundError:
        print('Payments file not found.')
        df_pay = pd.DataFrame()

    # PROCESSING BILLS
    for index, row in df_bills.iterrows():
        invoice_num = str(row[COL_BILL_NUM])
        vendor = row[COL_VENDOR]
        amount = row[COL_AMOUNT]
        po_num = str(row[COL_PO]) if pd.notna(row[COL_PO]) else None
        due_date = row[COL_DUE]
        status = row[COL_STATE]

        # Common attributes for this invoice
        base_attr = {
            'InvoiceNumber': invoice_num,
            'SourceSystem': 'Sage Intacct',
            'LastDataUpdate': current_time,
            'VendorName': vendor,
            'InvoiceAmount': amount,
            'PurchaseOrderNumber': po_num,
            'DueDate': due_date,
            'InvoiceStatus': status
        }

        # Activity: Invoice Draft Created
        if pd.notna(row[COL_CREATED]):
            events.append({**base_attr, 
                           'ActivityName': 'Invoice Draft Created', 
                           'EventTime': row[COL_CREATED], 
                           'InvoiceDate': row[COL_CREATED]})

        # Activity: Invoice Matched to PO
        if po_num and pd.notna(row[COL_CREATED]):
            events.append({**base_attr, 
                           'ActivityName': 'Invoice Matched to PO', 
                           'EventTime': row[COL_CREATED], 
                           'InvoiceDate': row[COL_CREATED]})

        # Activity: Invoice Posted
        if pd.notna(row[COL_POSTED]):
            events.append({**base_attr, 
                           'ActivityName': 'Invoice Posted', 
                           'EventTime': row[COL_POSTED]})
            # In standard views, Posted often implies Submission and Approval if no audit trail
            # We generate proxy events if Audit Trail is missing
            if not FILE_AUDIT:
                events.append({**base_attr, 
                               'ActivityName': 'Invoice Submitted', 
                               'EventTime': row[COL_POSTED]})
                events.append({**base_attr, 
                               'ActivityName': 'Invoice Approved', 
                               'EventTime': row[COL_POSTED]})

        # Activity: Invoice Placed on Hold
        if str(row[COL_HOLD]).lower() in ['true', 'yes', 'on hold']:
             # Time is approximate (Last Modified) as View doesn't track hold time
             events.append({**base_attr, 
                            'ActivityName': 'Invoice Placed on Hold', 
                            'EventTime': row.get('Date Modified', row[COL_CREATED])})

    # PROCESSING PAYMENTS
    # Intacct payment export might list multiple bills per check line or require join logic
    # Assuming direct link via Bill Number
    if not df_pay.empty:
        for index, row in df_pay.iterrows():
            bill_ref = str(row[COL_PAY_BILL_REF])
            # Only process if this payment links to a bill we know
            # (In a real scenario, merge on Bill Number)
            
            pay_attr = {
                'InvoiceNumber': bill_ref,
                'SourceSystem': 'Sage Intacct',
                'LastDataUpdate': current_time,
                'ActivityName': 'Payment Generated',
                'EventTime': row[COL_PAY_DATE]
            }
            events.append(pay_attr)
            
            if str(row[COL_PAY_STATE]).lower() == 'void':
                events.append({**pay_attr, 'ActivityName': 'Payment Voided', 'EventTime': row[COL_PAY_DATE]})
                
            if 'Date Reconciled' in row and pd.notna(row['Date Reconciled']):
                events.append({**pay_attr, 'ActivityName': 'Payment Reconciled', 'EventTime': row['Date Reconciled']})

    # 3. OUTPUT
    df_events = pd.DataFrame(events)
    # Ensure dates are ISO format
    df_events['EventTime'] = pd.to_datetime(df_events['EventTime']).dt.strftime('%Y-%m-%d %H:%M:%S')
    
    # Final Select
    df_final = df_events[event_log_columns]
    df_final.to_csv('Event_Log_Output.csv', index=False)
    print(f'Successfully exported {len(df_final)} events.')

if __name__ == '__main__':
    run_extraction()

/* 
  PROCESSMIND TRANSFORMATION SCRIPT
  Prerequisite: Data from APBILL, APPYMTDETAIL, and AUDITTRAIL 
  must be extracted via API and staged in tables with those names.
*/

/* 1. Invoice Draft Created */
SELECT
    H.DOCNUMBER AS InvoiceNumber,
    'Invoice Draft Created' AS ActivityName,
    H.WHENCREATED AS EventTime,
    'Sage Intacct' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    H.VENDORNAME AS VendorName,
    H.TOTALENTERED AS InvoiceAmount,
    H.PONUMBER AS PurchaseOrderNumber,
    H.WHENCREATED AS InvoiceDate,
    H.DUE_DATE AS DueDate,
    NULL AS Department,
    H.CREATEDBY AS CreatedBy,
    H.STATE AS InvoiceStatus,
    NULL AS PaymentDate
FROM APBILL H

UNION ALL

/* 2. Invoice Matched to PO (Derived from existence of PO Number at creation) */
SELECT
    H.DOCNUMBER,
    'Invoice Matched to PO',
    H.WHENCREATED, -- Occurs effectively at creation if PO links
    'Sage Intacct',
    GETDATE(),
    H.VENDORNAME,
    H.TOTALENTERED,
    H.PONUMBER,
    H.WHENCREATED,
    H.DUE_DATE,
    NULL,
    H.CREATEDBY,
    H.STATE,
    NULL
FROM APBILL H
WHERE H.PONUMBER IS NOT NULL AND H.PONUMBER <> ''

UNION ALL

/* 3. Invoice Posted (Header Timestamp) */
SELECT
    H.DOCNUMBER,
    'Invoice Posted',
    H.WHENPOSTED,
    'Sage Intacct',
    GETDATE(),
    H.VENDORNAME,
    H.TOTALENTERED,
    H.PONUMBER,
    H.WHENCREATED,
    H.DUE_DATE,
    NULL,
    H.CREATEDBY,
    H.STATE,
    NULL
FROM APBILL H
WHERE H.WHENPOSTED IS NOT NULL

UNION ALL

/* 4. Payment Generated */
SELECT
    H.DOCNUMBER,
    'Payment Generated',
    P.WHENCREATED,
    'Sage Intacct',
    GETDATE(),
    H.VENDORNAME,
    P.TRX_AMOUNT,
    H.PONUMBER,
    H.WHENCREATED,
    H.DUE_DATE,
    NULL,
    P.CREATEDBY,
    H.STATE,
    P.PAYMENTDATE
FROM APPYMTDETAIL P
JOIN APBILL H ON P.BILLRECORDNO = H.RECORDNO

UNION ALL

/* 5. Payment Reconciled */
SELECT
    H.DOCNUMBER,
    'Payment Reconciled',
    P.WHENCLEARED,
    'Sage Intacct',
    GETDATE(),
    H.VENDORNAME,
    P.TRX_AMOUNT,
    H.PONUMBER,
    H.WHENCREATED,
    H.DUE_DATE,
    NULL,
    P.CREATEDBY,
    H.STATE,
    P.PAYMENTDATE
FROM APPYMTDETAIL P
JOIN APBILL H ON P.BILLRECORDNO = H.RECORDNO
WHERE P.WHENCLEARED IS NOT NULL

UNION ALL

/* 6. Workflow Activities from Audit Trail */
/* Covers: Updated, Submitted, Approved, Declined, Hold, Released, Voided, Selected */
SELECT
    H.DOCNUMBER,
    CASE 
        WHEN A.MESSAGE LIKE '%Submitted%' THEN 'Invoice Submitted'
        WHEN A.MESSAGE LIKE '%Approved%' THEN 'Invoice Approved'
        WHEN A.MESSAGE LIKE '%Declined%' THEN 'Invoice Declined'
        WHEN A.MESSAGE LIKE '%Hold%' AND A.LOGTYPE = 'Add' THEN 'Invoice Placed on Hold'
        WHEN A.MESSAGE LIKE '%Hold%' AND A.LOGTYPE = 'Remove' THEN 'Invoice Released from Hold'
        WHEN A.MESSAGE LIKE '%Voided%' THEN 'Payment Voided'
        WHEN A.MESSAGE LIKE '%Selected for payment%' THEN 'Invoice Selected for Payment'
        WHEN A.MESSAGE LIKE '%Modified%' THEN 'Invoice Updated'
        ELSE 'System Activity'
    END AS ActivityName,
    A.WHENDOCCURRED AS EventTime,
    'Sage Intacct' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    H.VENDORNAME,
    H.TOTALENTERED,
    H.PONUMBER,
    H.WHENCREATED,
    H.DUE_DATE,
    NULL,
    A.USERID AS CreatedBy,
    H.STATE,
    NULL
FROM AUDITTRAIL A
JOIN APBILL H ON A.OBJECTKEY = CAST(H.RECORDNO AS VARCHAR(50))
WHERE A.OBJECTTYPE = 'APBILL'
  AND (
       A.MESSAGE LIKE '%Submitted%'
    OR A.MESSAGE LIKE '%Approved%'
    OR A.MESSAGE LIKE '%Declined%'
    OR A.MESSAGE LIKE '%Hold%'
    OR A.MESSAGE LIKE '%Voided%'
    OR A.MESSAGE LIKE '%Selected for payment%'
    OR A.MESSAGE LIKE '%Modified%'
  )
  /* Exclude creation updates to avoid duplication with Invoice Draft Created */
  AND NOT (A.MESSAGE LIKE '%Modified%' AND ABS(DATEDIFF(second, A.WHENDOCCURRED, H.WHENCREATED)) < 60)