o 5)i<@sbdZddlmZmZmZmZmZddlZddl m Z m Z ddl m Z ddl mZGdddZdS) zS OMS Data Service Service for extracting time series data from OMS database tables )ListDictAnyOptionalTupleN)datetime timedelta)DatabaseService) get_loggerc@sfeZdZdZddZ    d%dedeedeed eeeefd eed e eeeff d d Z deeefdeedeed ed ef ddZ d e efddZ ded eeeeffddZ    d&dedededededee edeed eeeeffddZded eeeffdd Z !  d'ded"edeedeed e eeeff d#d$ZdS)(OMSDataServicez9Service for extracting time series data from OMS databasec Cst|_td|_dddddgiddddd dgidd d ddd d gidddddddgiddd dddgidddddddgiddddddgidd|_dS)Noms_data_serviceorders order_date order_totalSUMzdeleted_at IS NULLtable date_column value_column value_funcextra_conditionsfiltersidCOUNTtransaction_sales bill_datez status = 1zdeleted_by IS NULL collection credited_dateamountz status = 17zcredited_date IS NOT NULLtransaction_cnzstatus IN (12, 13, 14) product_stock created_at change_stockzstock_type = 2zentity_type = 2purchase_orderquantityz status != 9)r order_countrevenue collections credit_notes stock_outwardpurchase_orders)r db_servicer logger table_configsselfr0;/var/www/html/oms-prod-mcp/app/services/oms_data_service.py__init__sh  zOMSDataService.__init__Ndaily table_key start_dateend_dater aggregationreturnc Cszd||jvrtd|dt|j|j|}i|di|p&i|d<|||||}|jd|d||j |}|sS|j d|dgWS|jdt |d |d |WSt y} z|jd |dt| d } ~ ww) a Extract time series data from an OMS table. Args: table_key: Key in table_configs (e.g. 'revenue', 'collections') start_date: Inclusive lower bound on the date column (YYYY-MM-DD) end_date: Inclusive upper bound on the date column (YYYY-MM-DD) filters: Additional col=value conditions merged at request time. Pass {"company_id": } for per-company scoping. Omit or pass {} for all-companies (collective) data. aggregation: 'daily' | 'weekly' | 'monthly' Returns: List of {"ds": , "y": } dicts ready for Prophet. Unknown table configuration: 'z'. Available: rzExecuting query for '': zNo data found for 'z' with the given filtersz Retrieved z records for ''z'Error extracting time series data for 'N)r- ValueErrorlistkeyscopyget _build_queryr,infor+execute_mysql_querywarninglen Exceptionerrorstr) r/r4r5r6rr7configqueryresultser0r0r1get_time_series_datas(    z#OMSDataService.get_time_series_datarIc Cs|d}|d}|d}|dd}|dkr)d|d|d |d } d |d } n>|d krCd|d|d|d |d } d|d} n$|dkr]d|d|d |d } d|d|d } n |d|d} d} d| d|} g} |dgD]} | | qw|r| |d|d|r| |d |d|d!iD]:\}}t|trd"d#d$|D}| |d%|d qt|tr| |d&|dq| |d'|q| r| d(d)| 7} | r| d*| 7} | d+7} | S),ul Build a parameterised aggregation query for Prophet input. Aggregation levels: daily → DATE(date_col) GROUP BY DATE(date_col) weekly → Monday of ISO week GROUP BY YEARWEEK(date_col) monthly → first day of month GROUP BY YEAR, MONTH rrrrrr3zDATE(z ) AS ds, (z) AS yzGROUP BY DATE()weeklyzDATE(DATE_SUB(z, INTERVAL WEEKDAY(z) DAY)) AS ds, zGROUP BY YEARWEEK(z, 1)monthlyz DATE_FORMAT(z, '%Y-%m-01') AS ds, zGROUP BY YEAR(z ), MONTH( AS ds, z AS ySELECT z FROM r >= 'r; <= 'rz, css.|]}t|trd|dnt|VqdS)r;N) isinstancerH).0vr0r0r1 s,z.OMSDataService._build_query..z IN (z = 'z =  WHERE  AND  z ORDER BY ds)r@appenditemsrWr=joinrH)r/rIr5r6r7rdate_col value_colr select_clause group_clauserJwhere_conditionscondkeyvaluevalsr0r0r1rAs\    zOMSDataService._build_querycCst|jS)z)Return the list of configured table keys.)r=r-r>r.r0r0r1get_available_tablessz#OMSDataService.get_available_tablescCs||jvr |j|SdS)z2Return a copy of the config for a given table key.N)r-r?)r/r4r0r0r1get_table_infos zOMSDataService.get_table_inforrgrrrrrgroup_byc Cs<|||||pg|p id|j|<|jd|d|dS)a9 Register a custom table configuration at runtime. Args: key: Lookup key used in API calls table: Physical table name date_column: Column used as the time axis value_column: Column to aggregate value_func: 'SUM' or 'COUNT' extra_conditions: List of raw SQL predicates always added to WHERE group_by: Unused legacy parameter (kept for compatibility) filters: Default col=value filters rz!Registered table configuration: 'u' → N)r-r,rB) r/rgrrrrrrlrr0r0r1add_table_configs zOMSDataService.add_table_configc Csz7||jvrt|j}dd|d|fWS|j|}|jd|d}|s5dd|ddfWSWdStyR}zdd t|fWYd }~Sd }~ww) z Validate that a table key exists and its underlying table is reachable. Returns: (True, "") on success, (False, reason) on failure. FzTable configuration 'z' not found. Available: zSELECT COUNT(*) AS cnt FROM rzTable 'z&' does not exist or returned no result)TrSzValidation error: N)r-r=r>r+rCrFrH)r/r4 availablerIresultrLr0r0r1validate_table_config)s"    z$OMSDataService.validate_table_config limitc Cszk||jvrtd|d|j|}t|dg}|r*||dd|d|r9||dd|dd|dd|d d |d }|rU|d d |7}|d|dd|7}|j|}|pjgWSty} z|j d|dt | d} ~ ww)uL Return a raw (non-aggregated) preview of the most recent rows for a table key. extra_conditions from the config are applied so the preview reflects the same filtered dataset that would be used for training. Args: table_key: Configured table key limit: Number of rows to return (1–100) start_date: Optional lower bound on the date column end_date: Optional upper bound on the date column Returns: List of {"ds": , "y": } dicts (raw, not aggregated). r9r;rrrUrVrTrRrz AS y FROM rr[r\z ORDER BY z DESC LIMIT z Error getting data preview for 'r:N) r-r<r=r@r^r`r+rCrFr,rGrH) r/r4rrr5r6rIrerJrKrLr0r0r1get_data_previewCs.    zOMSDataService.get_data_preview)NNNr3)rNNN)rqNN)__name__ __module__ __qualname____doc__r2rHrrrrrMrArjrkrmrboolrpintrsr0r0r0r1r sv 1  I   "r )rwtypingrrrrrpandaspdrrapp.services.database_servicer app.utils.helpersr r r0r0r0r1s