File size: 13,015 Bytes
a311739
6ec1187
a66ab92
 
82752bf
a66ab92
 
 
 
 
 
a311739
 
a66ab92
a311739
 
 
 
 
 
a66ab92
a311739
 
a66ab92
a311739
 
a66ab92
a311739
a66ab92
a311739
 
 
 
 
 
a66ab92
 
a311739
a66ab92
a311739
 
a66ab92
 
a311739
 
 
a66ab92
a311739
 
a66ab92
a311739
 
 
a66ab92
a311739
 
a66ab92
a311739
 
 
 
 
 
 
 
a66ab92
a311739
 
 
f8e2c68
a311739
 
 
 
 
 
a66ab92
a311739
 
 
f8e2c68
a311739
 
 
 
 
 
 
 
 
 
 
a66ab92
 
a311739
 
 
 
a66ab92
 
 
 
 
a311739
a66ab92
a311739
a66ab92
 
 
a311739
 
 
 
 
 
 
 
 
 
 
a66ab92
6ec1187
 
a66ab92
6ec1187
 
a66ab92
f8e2c68
 
a66ab92
a311739
 
a66ab92
f8e2c68
6ec1187
a66ab92
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6ec1187
 
a66ab92
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
6ec1187
 
 
f8e2c68
a66ab92
a311739
 
 
 
 
 
6ec1187
 
a66ab92
 
 
a311739
 
6ec1187
 
 
a311739
 
a66ab92
a311739
 
a66ab92
 
 
 
 
a311739
a66ab92
 
 
 
 
 
 
a311739
 
82752bf
a311739
82752bf
a311739
82752bf
 
a311739
82752bf
 
 
 
a311739
a66ab92
 
a311739
a66ab92
a311739
a66ab92
a311739
 
 
 
a66ab92
82752bf
 
 
 
 
 
a66ab92
a311739
 
 
6ec1187
a66ab92
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
import json
import math
import os
import re
from datetime import datetime

import requests
from dotenv import load_dotenv
from langchain_core.prompts import PromptTemplate
from langchain_google_genai import ChatGoogleGenerativeAI

load_dotenv()


def get_llm():
    """Create and return a ChatGoogleGenerativeAI instance with proper authentication."""
    google_api_key = os.getenv("GOOGLE_API_KEY")
    if not google_api_key:
        raise ValueError("GOOGLE_API_KEY environment variable is not set")
    return ChatGoogleGenerativeAI(
        model="gemini-2.0-flash", temperature=0, google_api_key=google_api_key
    )


def extract_json_from_text(text):
    """Extract JSON from text that might contain explanation text or code blocks."""
    json_pattern = r"\{.*\}"
    match = re.search(json_pattern, text, re.DOTALL)

    if match:
        potential_json = match.group(0)
        try:
            return json.loads(potential_json)
        except json.JSONDecodeError:
            pass

    code_block_pattern = r"```(?:json)?\s*([\s\S]*?)\s*```"
    matches = re.findall(code_block_pattern, text, re.DOTALL)

    for potential_json in matches:
        try:
            cleaned = potential_json.strip().replace("```", "")
            if cleaned.startswith("{") and cleaned.endswith("}"):
                return json.loads(cleaned)
        except json.JSONDecodeError:
            continue

    return None


def parse_data_with_llm(extracted_text):
    """Extract structured JSON from extracted text but do calculations in Python."""
    llm = get_llm()

    prompt_template = PromptTemplate(
        input_variables=["extracted_data"],
        template="""
        You are a data extraction assistant. Given the following extracted text, structure the output as valid JSON. 
        **Do NOT perform any calculations.** Just extract the numeric values exactly as they appear.

        **Extracted data:**
        {extracted_data}

        ### **Extract the Following Fields (Raw Data Only)**:
        - **Customer Ref**: Extract "Customer PO".
        - **Accessory Type**: IMPORTANT! Look for the jewelry category or type. Extract this information and classify it as one of these types: pendant, ringband, earring, bracelet, necklace. This is often found after "Category:" or similar heading in the document.
        - **Wt. in gram 14K**: Extract all "14K WG" weight values of parts and return as a list.
        - **Wt. in gram 18K**: Extract all "18K WG" weight values of parts and return as a list.
        - **Wt. in gram PLT**: Extract all "PLT" weight values of parts and return as a list.
        - **Rhodium**: Look for any mentions of "Rhodium" or "RH" and extract the associated numeric value. If not found, return 0.
        - **Diamonds**: Extract each diamond entry as an object with "Dia Qlty"(is the name under the shape column under diamond details), "MM Size", "Dia Qty", and "Dia Wt.".

        **Output STRICTLY as JSON:**
        ```json
        {{
            "Customer Ref": "...",
            "Accessory Type": "...",
            "Wt. in gram 14K": ...,
            "Wt. in gram 18K": ...,
            "Wt. in gram PLT": ...,
            "Rhodium": ...,
            "Diamonds": [
                {{
                    "Dia Qlty": "...",
                    "MM Size": "...",
                    "Dia Qty": ...,
                    "Dia Wt.": ...
                }},
                ...
            ]
        }}
        ```
        """,
    )

    prompt = prompt_template.format(extracted_data=extracted_text)
    response = llm.invoke(prompt)

    response_text = (
        response.content.strip()
        if hasattr(response, "content")
        else str(response).strip()
    )
    json_data = extract_json_from_text(response_text)

    if not json_data:
        cleaned_response = (
            response_text.replace("```json", "").replace("```", "").strip()
        )
        try:
            json_data = json.loads(cleaned_response)
        except json.JSONDecodeError as e:
            raise ValueError(f"LLM response is not in valid JSON format: {str(e)}")

    return json_data


def compute_values(data):
    """Perform metal calculations and format diamonds separately."""
    gold_price, platinum_price = fetch_metal_prices()

    gold_lock = gold_price if gold_price > 0 else 2800
    platinum_lock = platinum_price if platinum_price > 0 else 1040

    gold_lock_column = f"Gold Lock @ $ {gold_lock:.2f}"
    platinum_lock_column = f"Platinum Lock $ {platinum_lock:.2f}"

    # Extract rhodium value from LLM data
    rhodium_value = float(data.get("Rhodium", 0))

    metal_data = {
        "Customer Ref": data.get("Customer Ref", ""),
        "Accessory Type": data.get("Accessory Type", ""),
        gold_lock_column: gold_lock,
        platinum_lock_column: platinum_lock,
        "Wt. in gram 14K": (
            sum(float(x) for x in data.get("Wt. in gram 14K", []))
            if isinstance(data.get("Wt. in gram 14K"), list)
            else float(data.get("Wt. in gram 14K", 0))
        ),
        "Wt. in gram 18K": (
            sum(float(x) for x in data.get("Wt. in gram 18K", []))
            if isinstance(data.get("Wt. in gram 18K"), list)
            else float(data.get("Wt. in gram 18K", 0))
        ),
        "Wt. in gram PLT": (
            sum(float(x) for x in data.get("Wt. in gram PLT", []))
            if isinstance(data.get("Wt. in gram PLT"), list)
            else float(data.get("Wt. in gram PLT", 0))
        ),
        "Gold Rate (including loss) 14KT": round(
            (gold_lock / 31.1035) * 0.585 * 1.1, 2
        ),
        "Gold Rate (including loss) 18KT": round((gold_lock / 31.1035) * 0.75 * 1.1, 2),
        "Plat rate (including Lab)": round((platinum_lock / 31.1035) * 0.95 * 1.2, 2),
        "Gold Value 14K": round(
            sum(float(x) for x in data.get("Wt. in gram 14K", []))
            * round((gold_lock / 31.1035) * 0.585 * 1.1, 2),
            2,
        ),
        "Gold Value 18K": round(
            sum(float(x) for x in data.get("Wt. in gram 18K", []))
            * round((gold_lock / 31.1035) * 0.75 * 1.1, 2),
            2,
        ),
        "Plat Value": round(
            sum(float(x) for x in data.get("Wt. in gram PLT", []))
            * round((platinum_lock / 31.1035) * 0.95 * 1.2, 2),
            2,
        ),
        "Labour 14K": round(
            (
                12
                if (sum(float(x) for x in data.get("Wt. in gram 14K", [])) * 7) < 12
                else (sum(float(x) for x in data.get("Wt. in gram 14K", [])) * 7)
            ),
            2,
        ),
        "Labour 18K": round(
            (
                12
                if (sum(float(x) for x in data.get("Wt. in gram 18K", [])) * 7) < 12
                else (sum(float(x) for x in data.get("Wt. in gram 18K", [])) * 7)
            ),
            2,
        ),
        "Labour PT": round(
            (
                15
                if (sum(float(x) for x in data.get("Wt. in gram PLT", [])) * 11) < 15
                else (sum(float(x) for x in data.get("Wt. in gram PLT", [])) * 11)
            ),
            2,
        ),
        "Finding For Gold 14KT": 0,
        "Finding For Gold 18KT": 0,
        "Finding for Plt.": 0,
        "Rhodium": rhodium_value,  # Use the extracted rhodium value
        "Stamping": 1,
    }

    diamonds = data.get("Diamonds", [])
    for diamond in diamonds:
        if "Dia Qty" in diamond and "Dia Wt." in diamond:
            try:
                qty = float(diamond["Dia Qty"])
                wt = float(diamond["Dia Wt."])
                diamond["Diamond Weight"] = round(qty * wt, 3)
                diamond["Diamond Rate"] = 0
                diamond["Diamond Value"] = 0
            except (ValueError, TypeError):
                diamond["Diamond Weight"] = 0
                diamond["Diamond Rate"] = 0
                diamond["Diamond Value"] = 0

    return metal_data, diamonds


def process_extracted_text(extracted_text):
    extracted_data = parse_data_with_llm(extracted_text)

    # Get and map the accessory type
    raw_accessory_type = extracted_data.get("Accessory Type", "")
    accessory_type = map_accessory_type(raw_accessory_type)

    metal_data, diamonds = compute_values(extracted_data)

    return {
        "Metal Details": metal_data,
        "Diamonds": diamonds,
        "Accessory Type": accessory_type,
    }


def fetch_metal_prices():
    """Fetch current gold and platinum prices from Kitco London Fix API."""
    url = "https://kdb-gw.prod.kitco.com/"
    current_year = str(datetime.now().year)
    payload = {
        "query": """fragment LondonQuoteFragment on LondonQuote{
            ID goldAM goldPM timestamp silver platinumAM platinumPM palladiumAM palladiumPM
        }
        query LondonFixByYear($year:String!,$currency:String!){
            GetLondonFixByYearV3(year:$year,currency:$currency){
                ID currency results{...LondonQuoteFragment}
            }
        }""",
        "variables": {"year": current_year, "currency": "USD"},
        "operationName": "LondonFixByYear",
    }

    headers = {"Content-Type": "application/json"}

    try:
        response = requests.post(url, json=payload, headers=headers)
        response.raise_for_status()
        data = response.json()

        results = data["data"]["GetLondonFixByYearV3"]["results"]
        if results and len(results) > 0:
            gold_price = float(results[0]["goldPM"])
            platinum_price = float(results[0]["platinumPM"])
        else:
            raise KeyError("No results found in the response")

        return gold_price, platinum_price
    except (requests.exceptions.RequestException, KeyError, ValueError) as e:
        print(f"Error fetching metal prices: {e}")
        return 0, 0


def map_accessory_type(detected_type):
    """Map the detected accessory type to one of the standard types using LLM."""
    if not detected_type or detected_type.strip() == "":
        return "pendant"  # Default if no type detected

    llm = get_llm()

    prompt_template = PromptTemplate(
        input_variables=["detected_type"],
        template="""
        You are a jewelry categorization expert. Your task is to classify the given jewelry description 
        into exactly one of these categories: pendant, ringband, earring, bracelet, necklace.
        
        Description: {detected_type}
        
        Analyze the description and determine which category it belongs to. If it's ambiguous, 
        use your knowledge of jewelry to make the best classification.
        
        Respond with ONLY one of these exact words: pendant, ringband, earring, bracelet, necklace.
        """,
    )

    prompt = prompt_template.format(detected_type=detected_type)

    try:
        response = llm.invoke(prompt)
        response_text = (
            response.content.strip()
            if hasattr(response, "content")
            else str(response).strip()
        )

        # Extract just the category name, removing any extra text
        response_text = response_text.lower()

        valid_types = ["pendant", "ringband", "earring", "bracelet", "necklace"]

        # Check if response contains a valid type
        for valid_type in valid_types:
            if valid_type in response_text:
                return valid_type

        # If we couldn't find an exact match, check if there's something close
        if "ring" in response_text or "band" in response_text:
            return "ringband"
        elif "ear" in response_text:
            return "earring"
        elif "neck" in response_text or "chain" in response_text:
            return "necklace"
        elif "brace" in response_text or "cuff" in response_text:
            return "bracelet"
        elif "pend" in response_text or "locket" in response_text:
            return "pendant"

        # Default to pendant if we can't determine
        return "pendant"
    except Exception as e:
        print(f"Error in LLM accessory type mapping: {str(e)}")

        # Fall back to basic matching if LLM fails
        detected_type = detected_type.lower()

        # Direct matches
        if detected_type in ["pendant", "ringband", "earring", "bracelet", "necklace"]:
            return detected_type

        # Handle common variations and synonyms
        mapping = {
            "pend": "pendant",
            "locket": "pendant",
            "charm": "pendant",
            "ring": "ringband",
            "band": "ringband",
            "ear": "earring",
            "stud": "earring",
            "hoop": "earring",
            "bangle": "bracelet",
            "cuff": "bracelet",
            "wrist": "bracelet",
            "chain": "necklace",
            "choker": "necklace",
            "collar": "necklace",
        }

        for key, value in mapping.items():
            if key in detected_type:
                return value

        # Default to pendant if we can't determine the type
        return "pendant"