tjT( "dZddlmZddlZddlZddlmZddlmZddl Z ddl m Z ddddddd d d d Z d d dddddZejdejZd/dZed0dZd1dZd2dZd2dZd3dZd4d$Zd2d%Zd5d6d*Zd2d+Zd2d,Zd2d-Zd2d.ZdS)7zStructured pandas queries over the finance CSV (Phase 3). Handles filter-style questions that vector search alone answers poorly, e.g.: - AP transactions over $500 in Q3 2023 - Companies in oil drilling - Average debt-to-equity from analytics ) annotationsN) lru_cache)Path) get_settingszAccounts PayablezAccounts ReceivableCash InventoryRevenue) apzaccounts payableza/parzaccounts receivableza/rcash inventoryrevenueSalePurchaseTransfer)salesalespurchase purchasestransfer transfersz\b(over|above|under|below|greater than|less than|between|q[1-4]|quarter|202[0-9]|accounts?\s+payable|accounts?\s+receivable|\ba/?p\b|\ba/?r\b|industry|companies?\s+in|debt[- ]to[- ]equity|profit margin|average|sum|total|count|how many)\bquestionstrreturnboolcPtt|SN)r_STRUCTURED_HINTsearch)rs ./var/www/html/CA-Chatbot/app/rag/structured.pyis_structured_queryr!2s  ''11 2 22/tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]c(ttj}tj|d}dd}|d|}tj|}tj|d |gd }||d dk }|j sYtj | dd|d<dD]*}||j vrtj||d||<+||d dk }||d dk } dD]*}|| j vrtj| |d| |<+||| fS)NF) low_memoryrawrrdictc t|trtj|niS#tj$ricYSwxYwr) isinstancerjsonloadsJSONDecodeError)r&s r parse_metaz _load_frames..parse_meta;sO &0c&:&:B4:c??? B#   III s*-AA metadata_jsonT)drop)axis data_categoryaccounting_transactionDatecoerce)errors)DebitCreditcompany_financial_statementsaccounting_analytics)Debt-to-Equity Ratio Profit Marginr z Net Income)r&rrr')rr dataset_pathpdread_csvmapjson_normalizeconcat reset_indexcopyempty to_datetimegetcolumns to_numeric) pathdfr-metasmeta_dffulltxcol companies analyticss r _load_framesrS6s  + , ,D Te , , ,B   # #J / /E&&G 9bnn$n//9 B B BD d?#'?? @ E E G GB 8B^BFF6NN8DDD6 & B BCbj  -3AAA3T/*.LLMRRTTIT/*.DDEJJLLIQLL )# # #]9S>(KKKIcN y) ##r"tuple[str | None, float | None]cB|dd}tjd|}|r$dt |dfStjd|}|r$dt |dfSdS) N,z?(?:over|above|greater than|more than|>)\s*\$?\s*(\d+(?:\.\d+)?)gtr0z3(?:under|below|less than|<)\s*\$?\s*(\d+(?:\.\d+)?)lt)NN)lowerreplacererfloatgroup)rqms r _extract_amount_thresholdraUs  b))A J   A 'U1771::&&&& >   A 'U1771::&&&& :r" str | Nonec|}ttdD]4\}}t jdt j|d|r|cS5dS)Nc.t|d S)Nr)len)xs r z"_extract_account..hsc!A$iiZr")key\b)rZsorted_ACCOUNT_ALIASESitemsr\rescaperr_rhvalues r _extract_accountrpfs}A-3355;O;OPPP U 9-29S>>---q 1 1 LLL  4r"c|}tD]4\}}tjdtj|d|r|cS5dS)Nri)rZ _TYPE_ALIASESrlr\rrmrns r _extract_tx_typersnskA#))++ U 9-29S>>---q 1 1 LLL  4r"tuple[int, int] | NonecL|}d}tjd|}|r"t|d}tjd|}|r=t|dp|d}|pd|fSdS)z"Return (year, quarter) if present.Nz\b(20[12]\d)\br0z"\bq([1-4])\b|\bquarter\s*([1-4])\bi)rZr\rintr^)rr_yearymqmquarters r _extract_quarterr|vsA D $a ( (B  288A;; 8! < Timestamp Timedelta)rxr{ start_monthstartends r _quarter_rangersQ;!#a'K Ld+1 = = =E!||lBB777lK!OCCCblXYFZFZFZZ #:r"c|}tjd|}|r(|ddSdD] }||vr|cS dS)NzY(?:companies?\s+in|industry(?:\s+of)?|in the)\s+([a-z0-9 /&\-]+?)(?:\s+industry)?(?:\?|$)r0z .?)z oil drillingoilbankingsoftwarepharmasteel)rZr\rr^strip)rr_r`phrases r _extract_industryrs{A d   A 'wwqzz&&&S Q;;MMM  4r"rows pd.DataFramelimitc g}||D]\}}|d}tj|r|dnd}|d|d|ddd|ddd |d dd|d dd |d d d|S)Nr4z%Y-%m-%d?- z | Accountz | Debit r7z | Type Transaction_TypeCustomer_Vendorz | Payment Payment_Method )headiterrowsrGr>notnastrftimeappendjoin)rrlines_rdatedate_ss r _format_tx_rowsrs- E %  ))++  1uuV}}.0htnnEz***#  6 6 6AEE)S11 6 6AEE'3< Qr0z8Structured transaction search found 0 matching records (z, zfilters appliedz).gF) ascendingzStructured transaction search: rVz matching recordsz (amount )z on z in Qz. Total debit of matches: z,.2f.z Examples: )rSrErprHastyperrZrsrar|ranyrerrr]sumr sort_values)rrOrfilteredaccounttx_typeopamountr{rrappliedcountbits total_debitsampleheaders r query_transactionsrsq~~HB1 xtHx((G\9 000HY/66s;;?EEGG7==??Z[x((GQ%)999H%78??DDOP*844JB ;>>>>>F + +6 + ++r"ct\}}}|js d|jvrdSt|}|sdS||dt j|dd}|jrd|dSg}|dD]\}}| d| dd d | dd d | d d d | dd d| dd d| dd  dt|d|dd |zS)NIndustryF)casenaz?Structured company search found 0 companies matching industry 'z'. rNamerz | Industry: z | Price: z Current Pricez | Sales: Salesz | PAT: zProfit after taxz | ROCE: zReturn on capital employedzStructured company search: z companies matching 'z'. r) rSrErHrrrcontainsrrrrGrer)rrrQindustrymatchesrrs r query_companiesrs"nnOAy!*I,===t **H t*$$S))-66xePU6VVG}^]QY]]]] E R  ))++  1  hvs## h h!%% C2H2H h heeOS11 h h=>UU7C=P=P h hEE,c22 h h=>UUC_ad=e=e h h    Xc'llWWWWW ))E   r"c t\}}}|jrdS|dvodv}dvpdv}tfddD}|s|sdSg}|rd|jvr|d}|js~|dt|d |d d | d d | d d | d d |rd|jvr|d}|js~|dt|d |d d | d d | d d | d d |sdS|r|sdSdd |zS)Ndebtequityz profit marginmarginc3 K|]}|vV dSr).0wr_s r z"query_analytics..s'JJqAFJJJJJJr")averageavgmeantypicalr;z/Debt-to-Equity across analytics records: count=z , average=z.3fz , median=z, min=z, max=rr<z.Profit Margin across analytics records: count=zStructured analytics summary: r) rSrErZrrHdropnarrermedianminmaxr) rrrR wants_dte wants_margin wants_avgrseriesr_s @r query_analyticsrsu"nnOAq)tA! -A I"a'88q=LJJJJ$IJJJJJI t E+y/@@@1299;;|  LLC#f++CC!;;==NCC7=}}NCCzz||BCC/5zz||BCCC    9+<<<?+2244|  LLCVCC!;;==NCC7=}}NCCzz||BCC/5zz||BCCC    tt ,tyy/?/? ??r"ct|sdSg}tttfD]8} ||}n#t$rd}YnwxYw|r||9|sdSd|S)z?Return a compact structured summary, or None if not applicable.Nz )r!rrr Exceptionrr)rpartsfnresults r run_structured_queryr&s x ( (tE!?OD!! R\\FF   FFF   ! LL t ;;u  s 7 AA)rrrr)rr#)rrrrT)rrrrb)rrrrt)rxrwr{rwrr})r)rrrrwrr)__doc__ __future__rr*r\ functoolsrpathlibrpandasr> app.configrrkrrcompile IGNORECASErr!rSrarprsr|rrrrrrrrr"r rs#""""" ###### *  0       2:9M 3333 $$$ $<"             7,7,7,7,t8#@#@#@#@Lr"