File size: 6,107 Bytes
64e5ee2
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
import pandas as pd
import numpy as np
from rapidfuzz import fuzz
from sentence_transformers import SentenceTransformer
import faiss
import os
import pickle
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class ReconciliationEngine:
    def __init__(self, threshold=85.0, model_name='all-MiniLM-L6-v2', index_path='vendor_index.faiss'):
        self.threshold = threshold
        self.model = SentenceTransformer(model_name)
        self.index_path = index_path
        self.vendor_mapping_path = 'vendor_mapping.pkl'
        self.index = None
        self.vendor_names = []
        self._load_or_create_index()

    def _load_or_create_index(self):
        # We need dimension size for the chosen model. MiniLM-L6-v2 is 384
        d = self.model.get_sentence_embedding_dimension()
        
        if os.path.exists(self.index_path) and os.path.exists(self.vendor_mapping_path):
            logger.info("Loading existing FAISS index.")
            self.index = faiss.read_index(self.index_path)
            with open(self.vendor_mapping_path, 'rb') as f:
                self.vendor_names = pickle.load(f)
        else:
            logger.info("Creating new FAISS index.")
            self.index = faiss.IndexFlatL2(d)
            self.vendor_names = []

    def _save_index(self):
        faiss.write_index(self.index, self.index_path)
        with open(self.vendor_mapping_path, 'wb') as f:
            pickle.dump(self.vendor_names, f)

    def learn_vendors(self, vendors):
        """Adds new vendors to the FAISS index."""
        if not hasattr(self, 'embedding_cache'):
            self.embedding_cache = {}
            
        new_vendors = [v for v in set(vendors) if pd.notna(v) and v not in self.vendor_names]
        if new_vendors:
            logger.info(f"Learning {len(new_vendors)} new vendors.")
            embeddings = self.model.encode(new_vendors)
            self.index.add(np.array(embeddings).astype('float32'))
            self.vendor_names.extend(new_vendors)
            
            # Pre-cache to speed up pair-wise matching later
            for v, emb in zip(new_vendors, embeddings):
                self.embedding_cache[v] = emb / np.linalg.norm(emb)
                
            self._save_index()

    def get_embedding(self, vendor):
        if not hasattr(self, 'embedding_cache'):
            self.embedding_cache = {}
        if vendor not in self.embedding_cache:
            emb = self.model.encode([vendor])[0]
            self.embedding_cache[vendor] = emb / np.linalg.norm(emb)
        return self.embedding_cache[vendor]

    def get_semantic_similarity(self, vendor1, vendor2):
        if pd.isna(vendor1) or pd.isna(vendor2):
            return 0.0
        emb1_norm = self.get_embedding(vendor1)
        emb2_norm = self.get_embedding(vendor2)
        sim = np.dot(emb1_norm, emb2_norm)
        return max(0.0, sim * 100)

    def search_similar_vendor(self, query_vendor, top_k=1):
        if not self.vendor_names or pd.isna(query_vendor):
            return None, 0.0
        
        query_emb = self.model.encode([query_vendor]).astype('float32')
        distances, indices = self.index.search(query_emb, top_k)
        
        best_idx = indices[0][0]
        if best_idx != -1:
            best_match = self.vendor_names[best_idx]
            # Calculate a normalized score based on L2 distance
            # For normalized vectors, L2 distance squared is 2 - 2*cos(theta)
            # This is a rough proxy; let's combine with fuzz for the final score
            fuzz_score = fuzz.ratio(query_vendor.lower(), best_match.lower())
            return best_match, fuzz_score
        return None, 0.0

    def reconcile(self, source_df, target_df, source_key='VendorName', target_key='VendorName', amount_col='Amount'):
        logger.info("Starting reconciliation process.")
        
        # Learn vendors from both datasets
        self.learn_vendors(source_df[source_key].tolist())
        self.learn_vendors(target_df[target_key].tolist())

        # Basic exact match on InvoiceID if it exists, otherwise we match on VendorName and Amount
        if 'InvoiceID' in source_df.columns and 'InvoiceID' in target_df.columns:
            source_df = source_df.drop_duplicates(subset=['InvoiceID'])
            target_df = target_df.drop_duplicates(subset=['InvoiceID'])
            merged = pd.merge(source_df, target_df, on='InvoiceID', how='outer', suffixes=('_books', '_gst'))
            
            def determine_status(row):
                if pd.isna(row.get(f'{amount_col}_books')):
                    return "Missing in Books"
                if pd.isna(row.get(f'{amount_col}_gst')):
                    return "Missing in GST"
                
                b_amt = float(row.get(f'{amount_col}_books', 0))
                g_amt = float(row.get(f'{amount_col}_gst', 0))
                
                if abs(b_amt - g_amt) > 0.01:
                    return "Amount Mismatch"
                
                b_vendor_val = row.get(f'{source_key}_books')
                g_vendor_val = row.get(f'{target_key}_gst')
                b_vendor = str(b_vendor_val) if pd.notna(b_vendor_val) else ''
                g_vendor = str(g_vendor_val) if pd.notna(g_vendor_val) else ''
                
                if b_vendor.lower() == g_vendor.lower() and b_vendor != '':
                    return "Exact Match"
                
                fuzz_score = fuzz.ratio(b_vendor.lower(), g_vendor.lower())
                if fuzz_score >= self.threshold:
                    return f"Fuzzy Match ({fuzz_score:.1f}%)"
                
                sem_score = self.get_semantic_similarity(b_vendor, g_vendor)
                if sem_score >= self.threshold:
                     return f"Semantic Match ({sem_score:.1f}%)"

                return "Vendor Mismatch"
            
            merged['MatchStatus'] = merged.apply(determine_status, axis=1)
            return merged
        else:
            raise ValueError("InvoiceID column is required for current reconciliation logic.")