16iTsdZddlZddlmZmZmZddlmZddlm Z m Z ede dZ e jd d g e d Zd Ze jdd g e dZe jd dg e dZe jdd g e dZe jdd g e dZe jddg e dZe jdd g e dZe jddg e dZe jddg e dZe jdd g e dZe jdd g e d Ze jd!d g e d"Ze jd!dg e d#Ze jd$d%g e d&Ze jd'd g e d(Zy))zI Inventory Management Routes Parts and part variants with stock tracking N) Blueprintrequestjsonify)Database) require_admin optional_auth inventoryz/api/inventory) url_prefixz/partsGET)methodsctjjd}tjjd}d}g}|r|dz }|j||r|dz }|j||dz } t j ||r|nd}t |dfS#t$r"}t d t|id fcYd}~Sd}~wwxYw) z%Get all parts with optional filtering service_typecategoryz'SELECT * FROM parts WHERE active = truez AND service_type = %sz AND category = %sz ORDER BY service_type, nameNerror) rargsgetappendr execute_queryr Exceptionstr)rrqueryparamspartses H/var/www/html/mobile-medic-2.0/apis/medicore-api/app/routes/inventory.py get_partsrs<<##N3L|| +H 5E F )) l# %% h ++E/&&ufDIu~s"" /Q()3../s9&B C )CC C c|jd}|jd}|jd}|jd}|jd}d}g}|dz }|r|dz }|j||r |d z }d |d }|j||||g|r|d z }|j||r|d z }|j||r&|d k(r |dz }||fS|dk(r |dz }||fS|dk(r|dz }||fS)zFReuse the same filters used by the variants listing for review counts.part_idsearch serviceTypegrade stockStatus*WHERE pv.active = true AND p.active = truezi AND pv.price_reviewed_by IS NOT NULL AND pv.price_reviewed_by <> '' AND pv.price_reviewed_at IS NOT NULL AND pv.part_id = %s AND ( pv.sku ILIKE %s OR p.name ILIKE %s OR p.part_number ILIKE %s OR p.description ILIKE %s )% AND p.service_type = %s AND pv.grade = %sin_stock5 AND (pv.quantity_on_hand - pv.quantity_reserved) > 0 low_stock~ AND (pv.quantity_on_hand - pv.quantity_reserved) > 0 AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold out_of_stock5 AND (pv.quantity_on_hand - pv.quantity_reserved) = 0)rrextend) rr r!rr# stock_statuswhere_conditionsr search_params r(_build_variant_filter_clause_for_reviewsr5+s]hhy!G XXh F88M*L HHW E88M*LC FDD22 g    6(!}  |\<NO66 l# 00 e : %  W W  V ## [ ( !a a  V ##^ +  W W  V ##z /review-statsc:d}d|d} tj|dd}t|r|jddnd|r|jd dnd|r|jd dndd d fS#t$r"}td t |idfcYd}~Sd}~wwxYw)zReturn counts of reviewed variants by reviewer (blue, green, purple) across all pages. Always returns full counts regardless of filters/search.zWHERE pv.active = true AND p.active = true AND pv.price_reviewed_by IS NOT NULL AND pv.price_reviewed_by <> '' AND pv.price_reviewed_at IS NOT NULLa3 SELECT COALESCE(SUM(CASE WHEN slug LIKE '%%blue-parrot%%' THEN 1 ELSE 0 END), 0) AS blue, COALESCE(SUM(CASE WHEN slug LIKE '%%green-ghost%%' OR slug LIKE '%%green-goblin%%' OR slug LIKE '%%jeksaballa%%' THEN 1 ELSE 0 END), 0) AS green, COALESCE(SUM(CASE WHEN slug LIKE '%%purple-dino%%' THEN 1 ELSE 0 END), 0) AS purple FROM ( SELECT lower(regexp_replace(pv.price_reviewed_by, '[\s_]+', '-', 'g')) AS slug FROM part_variants pv JOIN parts p ON pv.part_id = p.id z ) reviewer_slugs; NT fetch_onebluergreenpurple)r:r;r<rrr)rrrrrr)r3rresultrs rget_review_statsr>Zsm    E/''ttD-3FJJvq)/5VZZ+117fjj1-Q      /Q()3../sA$A// B8BBBPOSTc tj} tjd|j d|j d|j d|j d|j d|j dfd }t |d fS#t $r"}t d t|id fcYd }~Sd }~wwxYw)zCreate a new partz INSERT INTO parts (part_number, name, service_type, category, description, manufacturer_part_number) VALUES (%s, %s, %s, %s, %s, %s) RETURNING * part_numbernamerr descriptionmanufacturer_part_numberTr8rrNrget_jsonrrrrrrdatar=rs r create_partrJ{s    D/'') HH] # HHV  HH^ $ HHZ HH] # HH/ 0   v## /Q()3../sBB C$C;CCz/parts/c tjd|fd}|stddidfSt|dfS#t$r"}tdt |idfcYd }~Sd }~wwxYw) zGet a specific part by IDz3SELECT * FROM parts WHERE id = %s AND active = trueTr8rPart not foundrrNrrrrr)r partrs rget_partrPsu /%% A J  G%567< <t}c!! /Q()3../)9 9 A$AA$A$z /variantsc tjjd}tjjd}tjjd}tjjd}tjjd}tjjd}tjjd}tjjd}tjjd d k(}ttjjd d } tjjd xs#tjjdxsd} t| } | d z | z} d} g} g}|r'| dz } | j ||j ||r5| dz } d|d}| j ||||g|j ||||g|r'| dz } | j ||j ||r'| dz } | j ||j ||r |dk(r| dz } n|dk(r| dz } n |dk(r| dz } |r/| dz } | j d|d|j d|d|r| dz } |d k(r| dz } |d k(r| dz } d| d} tj||r|nd}|r|d d!nd }|| zd z | z}| |k}d"| d#}| j | | gtj|| }t||| | ||d$d%fS#t$rd} YwxYw#t$r"}td&t|id'fcYd}~Sd}~wwxYw)(z@Get part variants with inventory levels and device compatibilityr r-r+r!r"r#r$ manufacturerunreviewed_onlytruepagepageSize page_size2r%r&r'r(r)r*r,r.r/r0a* AND EXISTS ( SELECT 1 FROM part_compatibility pc JOIN devices d ON pc.device_id = d.id JOIN device_families df ON d.family_id = df.id JOIN manufacturers m ON df.manufacturer_id = m.id WHERE pc.part_id = p.id AND m.name ILIKE %s )zC AND (pv.price_reviewed_by IS NULL OR pv.price_reviewed_at IS NULL)zI AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_thresholdzi SELECT COUNT(*) FROM part_variants pv JOIN parts p ON pv.part_id = p.id z Nrcounta WITH part_devices AS ( SELECT pc.part_id, d.id as device_id, d.model_name, d.model_number, d.release_date, df.id as family_id, df.name as family_name, m.id as manufacturer_id, m.name as manufacturer_name, dt.id as device_type_id, dt.name as device_type_name FROM part_compatibility pc JOIN devices d ON d.id = pc.device_id JOIN device_families df ON df.id = d.family_id JOIN manufacturers m ON m.id = df.manufacturer_id JOIN device_types dt ON dt.id = df.device_type_id WHERE pc.device_id IS NOT NULL ), part_families_agg AS ( SELECT part_id, family_id, family_name, manufacturer_id, manufacturer_name, device_type_id, device_type_name, json_agg( json_build_object( 'device_id', device_id, 'model_name', model_name, 'model_number', model_number, 'release_date', release_date ) ORDER BY model_name ) as devices FROM part_devices GROUP BY part_id, family_id, family_name, manufacturer_id, manufacturer_name, device_type_id, device_type_name ), part_compatibility_json AS ( SELECT part_id, json_agg( json_build_object( 'family_id', family_id, 'family_name', family_name, 'manufacturer_id', manufacturer_id, 'manufacturer_name', manufacturer_name, 'device_type_id', device_type_id, 'device_type_name', device_type_name, 'devices', devices ) ORDER BY manufacturer_name, family_name ) as compatibility FROM part_families_agg GROUP BY part_id ) SELECT pv.id, pv.part_id, pv.sku, pv.color, pv.grade, pv.cost, pv.quantity_on_hand, pv.quantity_reserved, (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available, pv.reorder_threshold, pv.reorder_quantity, pv.notes, pv.supplier_id, pv.active, pv.created_at, pv.updated_at, pv.price_reviewed_by, pv.price_reviewed_at, p.name as part_name, p.service_type, p.category, p.part_number, p.description, COALESCE(pcj.compatibility, '[]'::json) as compatibility FROM part_variants pv JOIN parts p ON pv.part_id = p.id LEFT JOIN part_compatibility_json pcj ON pcj.part_id = p.id zX ORDER BY p.name, pv.grade, pv.color LIMIT %s OFFSET %s )itemstotalrVrX pageCounthasMorerrr) rrrint ValueErrorrr1rrrrr)r r-r+r!rr#r2rSrTrVrYoffsetr3r count_paramsr4 count_query count_resultr] page_counthas_morervariantsrs r get_variantsrislly)G   -I|| +H \\  h 'F<<##M2L LL  W %E<<##M2L<<##N3Lll&&'89VCO w||* +D   ,S 0@0@0MSQSI N Qh) #FD FL22 gG$    6(!}  |\<NO\<|TU66 l#L) 00 eE" : %  W W  [ ( !a a  ^ +  W W     ,q)*a ~Q/0aaFgg6SS   Kt/--k<<]ab ,8 Q(ai'!+ 9 *$V l  mY x  y&)*))%8!#     k  | /Q()3../s1 M A8M MM M?M:4M?:M?ctj} tjd|j d|j d|j d|j d|j d|j dd|j d d|j d d|j d f d }t |dfS#t $r"}t dt|idfcYd}~Sd}~wwxYw)zCreate a new part variant (SKU)z INSERT INTO part_variants (part_id, sku, color, grade, cost, quantity_on_hand, reorder_threshold, reorder_quantity, supplier_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING * r skucolorr#costquantity_on_handrreorder_thresholdreorder_quantity supplier_idTr8rErrNrFrHs rcreate_variantrr}s    D/'') HHY  HHUO HHW  HHW  HHV  HH' + HH(! , HH' + HH] #  "v## /Q()3../sB7C C9C4.C94C9z/variants/c tjd|fd}|stddidfSt|dfS#t$r"}tdt |idfcYd }~Sd }~wwxYw) z!Get a specific part variant by IDak SELECT pv.*, (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available, p.name as part_name, p.service_type, p.part_number FROM part_variants pv JOIN parts p ON pv.part_id = p.id WHERE pv.id = %s AND pv.active = true Tr8rPart variant not foundrMrrNrN) variant_idvariantrs r get_variantrwsy/(( * ]d ,G%=>?D Dw$$ /Q()3../rQPUTctj} tjd|fd}|st ddidfS|d}g}g}d|vr%|j d |j |dd |vr%|j d |j |d |rE|j |d d j |d}tj|t|g}g}d|vr%|j d|j |dd|vrf|j d|j |d|j d|j tj|j dd|vr%|j d|j |dd|vr%|j d|j |dd|vr%|j d|j |dd|vr%|j d|j |dd|vr%|j d|j |d|rH|j |dd j |d } tj| t|d} nd!} tj| |fd} | st ddidfSt | d"fS#t$r"} t dt| id#fcYd$} ~ Sd$} ~ wwxYw)%z4Update a part variant and optionally its parent partz3SELECT id, part_id FROM part_variants WHERE id = %sTr8rrtrMr rCzdescription = %s part_namez name = %sz2 UPDATE parts SET , z+ WHERE id = %s rlz color = %srmz cost = %szprice_reviewed_by = %sz%price_reviewed_at = CURRENT_TIMESTAMPrnzquantity_on_hand = %srozreorder_threshold = %srpzreorder_quantity = %snotesz notes = %sactivez active = %sz= UPDATE part_variants pv SET a WHERE pv.id = %s RETURNING pv.*, (SELECT name FROM parts WHERE id = pv.part_id) as part_name, (SELECT service_type FROM parts WHERE id = pv.part_id) as service_type, (SELECT category FROM parts WHERE id = pv.part_id) as category, (SELECT part_number FROM parts WHERE id = pv.part_id) as part_number, (SELECT description FROM parts WHERE id = pv.part_id) as description ai SELECT pv.*, p.name as part_name, p.service_type, p.category, p.part_number, p.description FROM part_variants pv JOIN parts p ON p.id = pv.part_id WHERE pv.id = %s rrN) rrGrrrrjointuple user_emailrr) rurIrvr part_fields part_params part_queryfieldsrrr=rs rupdate_variantrs    Dq/(( A M  G%=>?D D)$  D   1 2   tM2 3 $    { +   tK0 1    w 'YY{+,- J  " ":u[/A B d? MM, ' MM$w- ( T> MM+ & MM$v, ' MM2 3 MM',, - MMA B  % MM1 2 MM$12 3 $ & MM2 3 MM$23 4  % MM1 2 MM$12 3 d? MM, ' MM$w- ( t  MM- ( MM$x. )  MM* %YYv&' (  E++E5=DQF E++EJ=DQFG%=>?D Dv## /Q()3../s*)K I;K < K K4K/)K4/K4z!/variants//adjustctj}|jd}|jd}|jdd}|tddidfS|stdd idfSgd }||vr!tdd d j |idfS t j d ||fd}|stddidfStd|d|d||ddfS#t$r"}tdt|idfcYd}~Sd}~wwxYw)z/Adjust inventory quantity (add or remove stock) adjustmentreasonr|NrzAdjustment value requiredzReason required)receiveddamagedreturned correctionusedz Invalid reason. Must be one of: r{z UPDATE part_variants SET quantity_on_hand = quantity_on_hand + %s WHERE id = %s AND active = true RETURNING *, (quantity_on_hand - quantity_reserved) as quantity_available Tr8rtrMzInventory adjusted successfullyrnquantity_available)message new_quantityrrrrr) rrGrrr~rrrr)rurIrrr| valid_reasonsr=rs radjust_inventoryr*sM    D,'J XXh F HHWb !E!<=>CC !234c99MM ]"#CDIImD\C]!^_`beee/'') * % 7G%=>?D D 8"#56"()=">$      /Q()3../s$*CC D"C?9D?Dz /low-stockc tjd}t|dfS#t$r"}tdt |idfcYd}~Sd}~wwxYw)z-Get all part variants below reorder thresholda# SELECT pv.*, (pv.quantity_on_hand - pv.quantity_reserved) as quantity_available, p.name as part_name, p.service_type, p.part_number FROM part_variants pv JOIN parts p ON pv.part_id = p.id WHERE pv.active = true AND p.active = true AND (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold ORDER BY (pv.quantity_on_hand - pv.quantity_reserved) ASC, p.name rrrNrN)r-rs r get_low_stockrWsW/** ,  y!3&& /Q()3../s!$ AA A Az/statsc tjd}|rt|dkDrt|ddfStddddddfS#t$r"}tdt |idfcYd}~Sd}~wwxYw)z"Get inventory statistics dashboarda SELECT COUNT(*) as total_skus, COALESCE(SUM(pv.cost * pv.quantity_on_hand), 0) as inventory_value, SUM(CASE WHEN (pv.quantity_on_hand - pv.quantity_reserved) <= pv.reorder_threshold THEN 1 ELSE 0 END) as low_stock, SUM(CASE WHEN (pv.quantity_on_hand - pv.quantity_reserved) = 0 THEN 1 ELSE 0 END) as out_of_stock FROM part_variants pv JOIN parts p ON pv.part_id = p.id WHERE pv.active = true AND p.active = true rr) total_skusinventory_valuer-r/rrN)rrlenrrr)statsrs rget_inventory_statsros/&& (   SZ!^58$c) )#$ !     /Q()3../s"4A A A4A/)A4/A4z"/parts//compatibilityc tjd|f}t|dfS#t$r"}tdt |idfcYd}~Sd}~wwxYw)z(Get compatibility assignments for a parta@ SELECT pc.id as compatibility_id, pc.part_id, d.id AS device_id, d.model_name, d.model_number, d.release_date, df.id AS family_id, df.name AS family_name, m.id AS manufacturer_id, m.name AS manufacturer_name, dt.id AS device_type_id, dt.name AS device_type_name FROM part_compatibility pc JOIN devices d ON d.id = pc.device_id JOIN device_families df ON df.id = d.family_id JOIN manufacturers m ON m.id = df.manufacturer_id JOIN device_types dt ON dt.id = df.device_type_id WHERE pc.part_id = %s ORDER BY m.name, df.name, d.model_name rrrNrN)r compatibilityrs rget_part_compatibilityrs^/ ..0 *Z+ .}%s** /Q()3../s#& AA A Actj}|jd}|stddidfS t j d|fd}|stddid fSt j d |fd}|stdd id fSt j d ||fd}|rtd didfSt j d||fd}t|dfS#t $r"}tdt|idfcYd}~Sd}~wwxYw)z"Assign a part to a specific device device_idrdevice_id is requiredrz4SELECT id FROM parts WHERE id = %s AND active = trueTr8rLrMz6SELECT id FROM devices WHERE id = %s AND active = truezDevice not foundzGSELECT id FROM part_compatibility WHERE part_id = %s AND device_id = %srzCompatibility already existsrz INSERT INTO part_compatibility (part_id, device_id) VALUES (%s, %s) RETURNING id, part_id, device_id rErN)rrGrrrrrr)r rIrrOdeviceexistingr=rs radd_part_compatibilityrs?    D%I !89:C??%/%% B J  G%567< <'' D L  G%7893> >)) U i   I'EFGL L''  i  v## /Q()3../s/)C")C *C7&C D 'D>D D z%/compatibility/DELETEc tjd|fd}|stddidfStjd|ftdd id fS#t$r"}tdt |id fcYd }~Sd }~wwxYw) z!Delete a compatibility assignmentz/SELECT id FROM part_compatibility WHERE id = %sTr8rz"Compatibility assignment not foundrMz,DELETE FROM part_compatibility WHERE id = %srz"Compatibility deleted successfullyrrNrN)compatibility_idrrs rdelete_compatibilityrs/)) =    G%IJKSP P  :    #GHI3NN /Q()3../s")A%A A=A82A=8A=z/parts/recommendedc tjjdt}tjjd}|st ddidfS|st ddidfSt j d|d |d f}|r|Dcgc] }t|c}ng}t |d fScc}w#t$rC}tjd t|t dt|id fcYd }~Sd }~wwxYw)z Get recommended parts for a device and service combination Query parameters: - device_id: Device ID - service_name: Service name (e.g., "Screen Repair") r)type service_namerrrzservice_name is requireda SELECT DISTINCT pv.id, pv.sku, p.name as part_name, pv.quantity_on_hand as stock_level, pv.cost as price FROM part_variants pv JOIN parts p ON pv.part_id = p.id JOIN service_offer_parts sop ON pv.id = sop.part_variant_id JOIN service_offers so ON sop.service_offer_id = so.id JOIN service_variants sv ON so.service_variant_id = sv.id JOIN service_catalog sc ON sv.service_id = sc.id WHERE so.device_id = %s AND LOWER(sc.name) LIKE LOWER(%s) AND so.active = true ORDER BY p.name r(rz"Error fetching recommended parts: rN) rrrr`rrrdictrloggingrr)rrrrOr=rs rget_recommended_partsrs%/LL$$[s$; ||''7 G%<=>C CG%?@A3F F&&( "1\N!, -#/&49/$t*/bv##0 / :3q6(CDQ()3../s<AB3B3("B3 B.B3.B33 C?<8C:4C?:C?)__doc__rflaskrrrapp.models.databaserapp.middleware.auth_localrr__name__ inventory_bprouterr5r>rJrPrirrrwrrrrrrrrr6rrs--(Bh;KL Hug.///2,$^OeW5/6/>Hvh//0/,*UG</=/(K%1P/2P/dK&2/3/405'B/C/,05'Bu/Cu/n7&J)/K)/VL5'2/3/,Hug.///>85'J/K/>86(K-/L-/^;hZP/Q/0(5':-/;-/r6