
    /i(                        d dl mZ d dlmZmZ d dlmZ d dlmZ d dl	m
Z
 d dlmZ d dlmZmZmZmZmZ d dlmZmZ ej,                  ddd
       Zy	)    )annotations)date	timedelta)func)Session)SessionLocal)
celery_app)InventorySnapshotDaily
DailySalesDemandForecastReorderPolicyInventoryPlanningSnapshot)compute_safety_stockcompute_reorder_pointNc                (   | rt        j                  |       }nt        j                         }t               }	 |j	                  t
              j                  t
        j                  |k(        j                  d       |j                          |j	                  t        j                  j                  d      t        j                  j                  d      t        j                  j                  d      t        j                  j                  d      t        j                   j                  d            j                  t        j                  |k(        j#                         }d}|t%        |	      z
  }|j	                  t&        j                  j                  d      t&        j                  j                  d      t&        j                  j                  d      t)        j*                  t)        j,                  t&        j.                        d
      j                  d      t)        j*                  t)        j0                  t&        j.                        d
      j                  d            j                  t&        j2                  |k\        j                  t&        j2                  |k        j5                  t&        j                  t&        j                  t&        j                        j#                         }d}|t%        d	      z   }|t%        |	      z   }	|j	                  t6        j                  j                  d      t6        j                  j                  d      t6        j                  j                  d      t)        j*                  t)        j8                  t6        j:                        d
      j                  d            j                  t6        j<                  |k\        j                  t6        j<                  |	k        j5                  t6        j                  t6        j                  t6        j                        j#                         }
|j	                  |j>                  j                  |j>                  j                  |j>                  j                  |j>                  j                  |j>                  j                   t)        j*                  |j>                  j@                  d
      j                  d      t)        j*                  |j>                  jB                  d
      j                  d      t)        j*                  tD        jF                  d      j                  d      t)        j*                  tD        jH                  d      j                  d      t)        j*                  tD        jJ                  d      j                  d      t)        j*                  |
j>                  jL                  d
      j                  d            jO                  ||j>                  j                  |j>                  j                  k(  |j>                  j                  |j>                  j                  k(  z  |j>                  j                  |j>                  j                  k(  z        jO                  tD        |j>                  j                  tD        j                  k(  |j>                  j                  tD        j                  k(  z  |j>                  j                  tD        j                  k(  z        jO                  |
|j>                  j                  |
j>                  j                  k(  |j>                  j                  |
j>                  j                  k(  z  |j>                  j                  |
j>                  j                  k(  z        }g }|jQ                         D ]  }|j                  }|j                  }|j                  }tS        |j                  xs d
      }tS        |j                   xs d
      }tS        |jT                  xs d
      }tS        |jB                  xs d
      }tW        |jF                  xs d      }tW        |jH                  xs d      }tS        |jJ                  xs d      }tS        |jL                  xs d
      }tY        ||||      }t[        |||      }|dkD  r|dkD  r|tS        |      z  }n|}tY        ||||      }t[        |||      }||z   }|}|||z  z   } ||k  rd}!n
|| kD  rd}!nd}!| }"t]        |"|z
  d
      }#d}$||k  xr
 |#dkD  xr |$ }%t        d'i d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|d|d |d!|d"| d#|!d$|#d%|%}&|j_                  |&        |r!|ja                  |       |j                          |jc                         te        |      d&|jg                          S # |jg                          w xY w)(a  
    Build inventory_planning_snapshot for a given date with:

      - current_reorder_point (history-based)
      - forecasted_reorder_point_90d (forecast-based)
      - stock_status (Under/Over/On Target)
      - recommended_order_qty, should_reorder
    F)synchronize_session
company_idlocation_id
product_idon_hand_qtyinbound_qtyZ   )daysg        avg_daily_demandsigma_daily_demand   forecast_90d_totalavg_daily_demand_histr   lead_time_daysreview_period_daysgffffff?service_level_target)r   r   r   service_level)r   safety_stockr   UnderstockedOverstockedz	On Targetsnapshot_dateavailable_stockcurrent_safety_stockcurrent_reorder_pointforecast_avg_daily_demand_90dforecast_safety_stock_90dforecasted_reorder_point_90d
min_target
max_targetstock_statusrecommended_order_qtyshould_reorder)r&   rows_inserted )4r   fromisoformattodayr   queryr   filterr&   deletecommitr
   r   labelr   r   r   r   subqueryr   r   r   coalesceavgquantity_soldstddev_samp	sale_dategroup_byr   sumforecast_qtytarget_datecr   r   r   r   r    r!   r   	outerjoinallfloatr   intr   r   maxappendbulk_save_objects	isoformatlenclose)'snapshot_date_strr&   dbinv_snap_subqhist_window_dayshist_start_datesales_stats_subqforecast_horizon_dayshorizon_starthorizon_endforecast_90d_subqqplanning_rowsrowr   r   r   r   r   r   r   r   r    r!   r   r(   r)   forecast_avg_daily_90dr+   r,   r'   r-   r.   r/   target_stockr0   has_open_por1   plannings'                                          Q/var/www/html/hubwallet-dev/src/smart_inventory/tasks/tasks_inventory_planning.py#compute_inventory_planning_snapshotrb      s    **+<=

.BQ
*+22%33}D	

&U&
+
		 HH&1177E&2288G&1177E&2288G&2288G V*88MIJXZ 	 ')9I*JJ HH%%++L9&&,,];%%++L9dhhz'?'?@#FLLM_`d..z/G/GH#NTTUij VJ((O;<VJ((=89X%%&&%%
 XZ 	& !#%	q(99#i5J&KK HH))//=**00?))//=dhh~'B'BCSIOOPde	 VN..-?@VN..+=>X))**))
 XZ 	& HH**++**++++.00AA3GMMNef.00CCSIOOPdem::A>DDEUVm>>BHHI]^m@@$GMMNde/11DDcJPPQef Y ++/?/A/A/L/LL ??..2B2D2D2P2PPR ??--1A1C1C1N1NNP Y++}/G/GG ??..-2K2KKM ??--1I1IIK Y!++/@/B/B/M/MM ??..2C2E2E2Q2QQS ??--1B1D1D1O1OOQ5 	
D :<557 `	+CJ//KJ 637K 637K$)#*C*C*Js$K!!&s'='='D!E !3!3!8q9N!$S%;%;%@q!A#()A)A)IT#J !&s'='='D!E $8!6#5-2	$  %:!61-%! "A%*?!*C);eDY>Z)Z&)>& )=!7#5-2	)% ,A!76-,( *K7O.J.2GJ\2\]J+- :-,* &L$'(F$L!  K $99 $*Q.$#O  1 +% ( &	
 ( ( !0 "7 $6  . $6 &: &: '< /E  +D!" .J#$ &%& &'( *)* '<+,  .-H2   *A`	+D   /IIK +446 /
 	

s   f6g? ?h)N)rP   z
str | None)
__future__r   datetimer   r   
sqlalchemyr   sqlalchemy.ormr   src.utils.dbr   src.utils.celery_workerr	   )src.smart_inventory.apps.inventory.modelsr
   r   r   r   r   Asrc.smart_inventory.apps.inventory.utils.inventory_planning_utilsr   r   taskrb   r3       ra   <module>rm      s@    " $  " % .  a arl   