]i3dZddlmZddlmZddlmZddlmZm Z ddl m Z m Z ddl mZddlmZmZmZmZmZdd lmZmZdd lmZmZdd lmZmZmZdd lm Z dd l!m"Z"m#Z#m$Z$ddZ%ddZ&ddZ'ddZ( ddZ) d dZ* d!dZ+ d!dZ, d" d#dZ- d$dZ. d%dZ/ d%dZ0y)&uRekonsiliasi Harian service — daily sounding-based reconciliation logic. Formula per tangki: Stok Awal = volume_final dari sounding shift pertama hari ini + Penerimaan = BBM diterima hari itu untuk tangki ini + Pemindahan In - Penjualan = Volume dari nozzle bersumber tangki ini - Pemindahan Out = Stok Teoritis Stok Aktual = volume_final dari sounding shift terakhir hari ini Losses / Gain = Stok Aktual - Stok Teoritis Prerequisite: all shifts for the day must be at least SUBMITTED. ) annotations)date)Decimal)funcselect)IntegrityErrorSQLAlchemyError) AsyncSession) LaporanShiftPenjualanNozzle StatusLaporanStockAdjustmentStockAdjustmentItem) PenerimaanPenerimaanItem)RekonsiliasiHarianStatusRekonsiliasi)NozzleShiftTangki)rekonsiliasi_repository)RekonsiliasiHarianResponseRekonsiliasiListResponseRekonsiliasiTangkiResponsec\g}|jD]}|jt|j|j|j r|j j nd|j rLt|j dr6|j jr |j jj nd|j|j|j|j|j|j|j|j   t#|j|j$|j&|j(|j*r|j*j,nd|j.r|j.j,nd||j0|j2 S)Nproduk) id tangki_id tangki_nama produk_nama stok_awal penerimaan pemindahan_inpemindahan_out penjualan stok_teoritis stok_aktuallosses) rspbu_idtanggalstatus run_by_nameapproved_by_nameitems created_at updated_at)r.appendrrrtangkinamahasattrrr!r"r#r$r%r&r'r(rr)r*r+run_byname approved_byr/r0)rr.items C/var/www/html/spbu.com/backend/app/services/rekonsiliasi_service.py_build_detail_responser;1s* E /wwnn,0KK ((T;;74;;#AdkkFXFX ""''nn,,..nn,,((;;!  & & 44  xx%&XXAHHMM4/0}}++$<<<<  c td|jDtd}t|j|j |j |jt|j||jr|jjnd|jr|jjnd|j|j S)Nc34K|]}|jyw)N)r().0r9s r: z'_build_list_response..Ts8 s0) rr)r*r+ jumlah_tangki total_lossesr,r-r/r0)sumr.rrrr)r*r+lenr5r6r7r/r0)r8rCs r:_build_list_responserFSs88'#,GL # 44  xx!''l!%&XXAHHMM4/0}}++$<<<<  r<c K|jttjtj|k(tj j dtjj djtjd{}t|jjS7+w)z4Get active shifts for an SPBU, ordered by jam_mulai.TN) executerrwherer) is_activeis_ deleted_atorder_by jam_mulailistscalarsalldbr)results r:_get_active_shiftsrUcs::u u}}')<)*>t*DfFWFWF[F[\`Fa bF  $$& '' sA>B.B,,B.c  Kg}tjtjtjh}|D]D}|j t t jjt j|k(t j|jk(t j|k(d{}|j}|j t tjjtj|k(tj|jk(tj|k(d{} | j} ||vs| |vs*|j|j G|S77@w)zBReturn list of shift names that are NOT yet submitted (or higher).N)r SUBMITTEDAPPROVEDLOCKEDrHrrr+rIr)shift_idrr*scalar_one_or_noner r1r3) rSr)r*shifts not_submittedacceptedshift sa_result sa_status ls_result ls_statuss r:_check_all_shifts_submittedrfvs?M'')?)?AUAUVH** ?)) * 0 0''72((EHH4''72   002 ** <&& ' - -$$/%%1$$/   002 H $ (A   ,-0 -  s+B1F3F4B FFF !FFc K|jttjj t tj t jk(jt j|k(t j|k(t j|k(tj|k(d{}|jS7w)zCGet volume_final_liter for a specific tank from a stock adjustment.N)rHrrvolume_final_literjoinrstock_adjustment_idrrIr)r\r*rr])rSr)r\r*rrTs r:_get_sounding_volumerks::"556 o2FF/J\J\\ ]   # #w .  $ $ 0  # #w .  ) )Y 6    F  $ $ && sB5C7C 8Cc K|jttjtjt j tdjtt jtjk(jtj|k(tj|k(t j|k(d{}|j!xs tdS7!w)z.Sum of BBM received for a tank on a given day.rAN)rHrrcoalescerDrvolume_diterimarrir penerimaan_idrrIr)r*r scalar_onerSr)r*rrTs r:_get_penerimaan_volumerrs::t}}TXXn&D&DEws|TU j.66*--G H    ' )   ' )  $ $ 1 F     .'#,.sCC9C7"C9c \K|jttjtjt j tdjtt jtjk(jtt jtjk(jtj|k(tj |k(tj"|k(d{}|j%xs tdS7!w)zDSum of sales volume from nozzles sourced from a tank on a given day.rAN)rHrrrmrDr volumerrir laporan_shift_idrr nozzle_idrIr)r*rrprqs r:_get_penjualan_volumerws::t}}TXXo&<&<=ws|LM lO<< O P fo//699< =   G +  G +    )  F     .'#,. sDD,D* "D,Nc Ktj|||||||d{\}}|D cgc] } t| c} |fS7"cc} ww)N) tanggal_mulai tanggal_akhirr+skiplimit)rget_allrF) rSr)ryrzr+r{r|rowstotalr8s r:list_rekonsiliasirsa077 G##  KD%.2 2T  #T 2E 99 3s A A A AA A chKtj|||d{}|yt|S7w)z?Get reconciliation for a specific date, or None if not yet run.N)rget_by_tanggalr;)rSr)r*rekons r:get_rekonsiliasirs8*88WgN NE } !% (( Os 202c |Kt||d{}|s tdt||||d{}|rtddj|dt ||d{}|s td|d}|d}g} |D]1} t |||j || j d{} | td } t |||j || j d{} | td } t|||| j d{} t|||| j d{}td }td }| | z|z|z |z }| |z }| j| j | | ||||| |d dd l m }|||| j || || t|d{4 tj|||d{}|Ptj ||t"j$|d d{tj&||| d{}nPtj(|||t"j$|d d{}tj&||| d{}|j+d{tj|||d{}t3|S777_77777!77777g7Q74#t,$r%|j/d{7tdt0$r|j/d{7wxYww)aRun (or re-run) daily reconciliation for a given date. Steps: 1. Validate all shifts have submitted data 2. For each active tank, calculate: stok_awal, penerimaan, penjualan, stok_teoritis, stok_aktual, losses 3. Create or update RekonsiliasiHarian + items Nz#SPBU ini tidak memiliki shift aktifz$Shift berikut belum submit laporan: z, zI. Semua shift harus sudah disubmit sebelum bisa menjalankan rekonsiliasi.z$SPBU ini tidak memiliki tangki aktifrrA) rr!r"r#r$r%r&r'r()run_rekonsiliasi_checks)r+ run_by_id)r)r*r+rz,Rekonsiliasi sudah ada atau constraint error)rU ValueErrorrfrirWrkrrrrrwr1app.utils.anomalirstrrr update_statusrPENDINGupsertcreatecommitrrollbackr r;)rSr)r*user_idr^r_ tangki_list first_shift last_shift items_datar2r!r'r"r%r#r$r&r(rexistingrs r:run_rekonsiliasirsZ&b'2 2F >??5b'7FSSM2499]3K2LMV V  +2w77K ?@@)KJJ. &))     I 1     !#,K1"gw RR /GWfiiPP    !J.>J^[ },"$*,"*&   >% M; z3w<   S^0??GWUU  )77H);)C)CRYZ  288XzRRE188"",44$ >E 288UJOOEiik-<1\ ]  /== "4"="=QXY  iik/>>r7GTT "' **% O  T kkm sjDCAD&+C CC )C*C C C DC C C D<C?=DD)r8rreturnr)r8rrr)rSr r)intr list[Shift])rSr r)rrz list[Tangki]) rSr r)rr*rr^rrz list[str]) rSr r)rr\rr*rrrrzDecimal | None) rSr r)rr*rrrrr)NNNr2)rSr r)rry date | Nonerzrr+z str | Noner{rr|rrz*tuple[list[RekonsiliasiListResponse], int])rSr r)rr*rrz!RekonsiliasiHarianResponse | None) rSr r)rr*rrrrr)1__doc__ __future__rdatetimerdecimalr sqlalchemyrrsqlalchemy.excrr sqlalchemy.ext.asyncior app.models.operationalr r r rrapp.models.penerimaanrrapp.models.rekonsiliasirrapp.models.spburrrapp.repositoriesrapp.schemas.rekonsiliasirrrr;rFrUrWrfrkrrrwrrrrr<r:rs ##:/=J114D  (("-1;FD''"'.1'<@'MP''" / /" /-1 />A /  / //"/-1/>A/ /2"&!%:: :: :  :  : :0:())")-1)&)i)i)"i)-1i)