jp dZddlZddlZddlZddlZddlmZddlmZddl Z edZ e dz Z e dz Z dZ id gd d d d gdddgdddgdgddddgdgddgddddgd d!d"gd#d$d%gd&gd'd(d)d*gd+d)d*gd,d-gd.d-gd/d0d1gid2gd3d4gd5d6d7d8gd9gd:d;gd<d=d>gd?d@dAgdBgdCdDdEgdFdGdHgdIdJgdKdLdMgdNdOdPgdQgdRdSdTgdUgdVdWdXdYggdZgd[gd\gd]gd^d_gd`dagdbgdcZgdddedfggdgdhZdiZdjZdkZdlZdmZdnZdoZdydpZdqZdrZdsZdtZduZdvZdwZedxk(reyy)zu TMED-II Phase III — Package List vs Master Schedule Cross-Reference Parses master_schedule.tsv (our plan) and 6 packing list xlsx files (Daniel Lee), cross-references them, and outputs results to Google Sheets. Audit fixes applied: F-01 through F-09. N) defaultdict)Pathz3/home/borbolla/clawd/projects/HTS/TMED-II-Phase-IIIz0bidding/our-quote/breakdowns/master_schedule.tsvzproject/package_lists,1Tvt3fAZdwR7DAhBmnJ3AbzWdUJi2wagcG2P4xLQvgUQu보빈공급기)z Bobbin LifterzBobbin Carrierz BOBBIN LIFTERzBOBBIN CARRIERz BOBIN LIFTERz Bobin Lifteru 권선기zWinding MachineWINDING MACHINEu 탈피기zDecoating MachinezDECOATING MACHINEu리턴 컨베이어z\d+\..*RETURN CONVEYORzDECOATING CONVEYORu 대차류)z\d+\..*ROLLER CONVEYORz \d+\..*SCARAz\d+\..*SKID CONVEYORzPalletizer(?!.*PANEL)u 보빈 정렬zBobbin alignmentzBobbin Alignmentu서포트링 열간압입) Hot Pressingz Hot pressingzSupporting.*HotSupporting \(Pu보빈 에어블로우)zAir BlowAirblowzAir blowu보빈 단차 측정zTerminal measurementu단차u팔레트 에어블로우z Pallet Airz Pallet.*blowu권선라인 이재로더zProcess connection reloadereloaderu레이져마킹)zMarking machinez Laser MarkingMarkingu바니시 예열zPreheating the varnish Preheatingu바니쉬 예열u바니시 함침zVarnish applicationu바니쉬 도포u1차 고주파 경화zVarnish curingzHigh frequencyu겐트리로더)KentryGantry Autoloaderu바니쉬 경화 & 냉각)zVarnish drying ovenzVarnish cooling furnacezVarnish.*coolingzVarnish.*dryingzVarnish.*tank coverz Varnish.*ductzChiller.*Varnishu 포밍기zWire Forming(?!.*Vision)zWire Forming #u포밍 비젼검사)zWireforming VisionzForming Visionz Wire.*Visionu포밍 에어블로우)z Forming.*AirzAir.*blow.*formz Wire.*Airu터미널 조립기zTerminal Assemblyu코킹 & 컷팅zCaulking.*CuttingzCaulking(?!.*Vision)u코킹 & 컷팅 비젼검사)zCaulking.*Cutting.*VisionzCaulking.*VisionzCutting.*VisionzCaulking.*Inspectionu퓨징 & 검사zFusing(?!.*Air)u퓨징 에어블로우z Fusing.*Airz Fusing.*blowu 트래버스Traverseu온도센서 조립zTemperature sensor(?!.*vision)zTemperature sensor assemblyu 온도센서 조립 비젼검사zTemperature sensor visionzTemperature.*visionu에폭시 몰딩기)zEpoxy Molding(?!.*Vision)zEpoxy Molding #z Epoxy Tanku에폭시 몰딩 비젼검사z Epoxy.*Visionu에폭시 경화 & 냉각)zEpoxy drying furnacezEpoxy cooling furnacezEpoxy.*coolingz Epoxy.*dryingu지그 탈거 장치z Jig removalz Jig.*robot)zElectrical.*testzElectric.*testz Elect.*Inspec)zCompletion inspectionzFinished.*inspectionzFinal inspection)zmulti-joint robotzcompleted.*elevatorzRobot controller)z NG Conveyorz Master CarzNG CartzNG replacement)zControl Panel(?!.*stand) RTB PanelzElectric ControlzElectrical supplieszELECTRIC CONTROL UNITz CONTROL PANELPANELzPC RACKResolverz Leak.*testz Leak TesterzPerformance.*test)u전기 특성 검사u 완성검사u 완성이재uNG대차u 제어반u레졸버 체결u리크테스트u성능 특성 검사) zConveyor(?!.*Finished)zconveyor(?!.*finished)z Profile FencezFence(?!.*Autoloader)Stairz Lift.*tableStopperzInput NGzAir conditioner.*hosezProcess.*Palletrr)ConveyorconveyorFenceProfilezFinished PalletzControl panel standz Vision PCzAir conditionerzdust collectorrzAutoloader FenceChillerzBobbin.*[Pp]alletzMain.*[Pp]alletz Main.*palettezBobbin.*palletz Air.*blowzVision PC Spare)u공정연결장치u컨베어 外 (열간압입u컨베어 外 (메인)c Vg}t}t|dd5}|j}dddd}tdD]\}}|dk(r |j d}|j s/d|vrd }|j d rG|jd } | r| d j nd } |rIt| dk\r:d| vr6t| dkDr| dj nd } | r|j| | dvrt| dkrt| dkDr| dj nd } | dk7r| dj } t| dj } t| dkDr| dj nd }d|vsd|vrd}n |dk\rd}nd}t| dkDr| dj nd }|j||t| dkDr| dj nd | | |gdd d ||fS#1swYxYw#ttf$rd} YwxYw)z&Extract INCLUDED Motor items from TSV.rutf-8encodingNF EXCLUDEDTz--- r)MotorINCLUDEDz06-15z6/15jun15;may20) linenosection item_no_raw korean_nameweight_tarrival matched_pkgsflagnotes)setopen readlines enumeraterstripstrip startswithsplitlenaddfloat ValueError IndexErrorappend)pathitemsexcluded_namesflines in_excludedr0rawlinecolsareanamestatusweight arrival_rawr5r1s N/home/borbolla/clawd/projects/HTS/TMED-II-Phase-III/project/cross_reference.pyparse_master_schedulerVqs3 EUN dC' *a K *8  Q; zz$zz|   Ku%zz$"&tAw}}B 4yA~*"4*-d)a-tAw}}R"&&t,  z !  t9q= $'IMar Z  Aw}} 47==?+F *-TQd1gmmoB k !V{%:G r\GG%(Y]$q'--/ .1$i!m47==?  ]8 t .  }HJ' F sH HHH('H(c ddddddddddd }|D]Q}t|D]?\}}|jrAt|jjddj j nd}|sY|dk(r d|vsd|vr||d<|d kr d |vr||d <d |vrd |vs|j d r||d<d|vrd |vs|j d r||d<d|vr||d<|dk(r |d||d<|d k(r"|d||jdxsdkDr||d<|dk(r"|d||jdxsdkDr||d<d|vr d|vr||d<d|vs.d|vs4|dkDs;||d<BT|S)zIAuto-detect column positions by scanning header rows for keywords (F-01).N) containerfacilitynet_wtgross_wtlengthwidthheightcontainer_specs container_iddescription_enr! r$rrXzc/tr*rYnetrSwrZgrossr[zdescription in englishralr\r]hr^specr_nor`)r<valuestrreplacer>lowerendswithget) header_rowsrOrowcol_idxcellvals rUdetect_columnsrvstD  D/&s^ /MGTHL #djj/))$4::<BBDXZC!|!3u|$+[!!| c 1#*Z |SCLL4E!(X#~8s?cll36G#*Z '3.)0%&czd8n4!(Xczd7m3488HCUCZYZ8[ 'W czd8n4DHHWDUDZYZ9[!(Xc!fm*1&'c!ts{" '.^$= //B KcJ|y t|S#ttf$rYywxYw)Nr+)rCrD TypeError)rus rU safe_floatrzs0 {Sz  "s ""c||}t|jd}t|dkrgSt|dd}|dd|d<|dd|d<|d[d D]L}|t|d kst |d |j xsd j }d |vsG||d<n|dd |d<|d |dd z|d<g}d} d} t|D]\} } | dkr | D cgc]} | j }} t|}|dr0t |djjdk(r|S|d}||kr!||rt ||jnd }|r|dk(r|d}||kr||nd}|jt |jrQt |jdk7r5 t ttt |j} |dr|d|krz||d}|rpt |jrWt |jdk7r;t |jjddj}|r|} |d}|d}||krt||nd}||krt||nd}|dk(r|dk(r|j!d}|j!d}|j!d}|r||krt||nd}|r||krt||nd}|r||krt||nd}|j!d} d }!| r&| |kr!|| rt || j}!| r|d| n|d}"|j#||"| | xsd ||!|||||fdd |Scc} w#ttf$rt |j} YwxYw)z>Parse a PACKING DETAIL sheet, auto-detecting column positions.F values_onlyr&NrXrrYrZ) r r$rcrr[ENDNoner_r!rbr+r\r]r^ra-z-?) supplierrX container_rawr_ facility_namera net_wt_kg gross_wt_kgdims_mm matched_to)list iter_rowsrArvrlrkrnr<r>upperintrCrDryrmrzrprF)#wb sheet_namesupplier_prefixwsall_rowscol_maptry_colhdrrHcurrent_containercurrent_container_specsrow_idxrrccellsmax_colfac_colrYct_colct_val specs_val raw_specsnw_colgw_colrZr[l_colw_colh_colr\r]r^ desc_en_coldesc_en prefixed_cts# rUparse_packing_detailrs` JBBLLUL34H 8}q Xbq\*G{#  z" x # GXa[))(1+g.44:;AACC<(/GH%   8  $ "GH z"%h/!3  E"!(+E  Q; "%&Q&&e* 8E!H ++-335> ~ L{*%29G2Cg3uW~&,,.\^8v- %"(7"2v  #f+"3"3"5#f+:K:K:MQW:W 8$'E#f+2C2C2E,F(G$H! $ %'2C*Dw*Ng&789IS^113I8L8L8NRX8X N002::4EKKM .7+"$.4w.>E&M*C060@:eFm,c Q;8q=  H% G$ H%-2uwE%L)C,1ego 5<(3-2uwE%L)Ckk"23  ;0U;5G% ,-335GCT)+<*=>\k[llnYo  '$.6<"%%#v.  uE N LG'" * 8$'K$5$5$7! 8s%N84N!!(O  O c g}ddddddd}|jD]\}}||z }|jstd|d+tjt |dd}d}|j D] }|d k(s |}n|rGt|||} | D]} || d < |j| td |d t| d ||j|S)zParse all 6 xlsx files.DKHWIA)zDKTIND-2604-01.xlsxzDKTIND-2604-02.xlsxHWIA-EM260408-01.xlsxHWIA-EM260408-02.xlsxHWIA-EM260413-03.xlsxHWIA-EM260413-04.xlsxz WARNING: z not foundT read_only data_onlyNzPACKING DETAIL source_filez z: z items from ) rHexistsprintopenpyxl load_workbookrl sheetnamesrextendrAclose) pkg_dir all_items files_infofnameprefixfpathr detail_sheetsnrHitems rUparse_all_packagesrXsI##!'!'!'!' J$))+ v%||~ IeWJ/ 0   # #CJ$$ O -- B%%!   (\6BE ,&+]# ,   U # BugRE |< ~F G  +. rwc2i}gd}|D] }||z }|jstjt|dd}d|jvr|j Z|d}|j dD]}|D]}|t|j} tjd| } | s8| jdj} | jdj} | sy| s|| || <|j  |S) z8Extract English(Korean) pairs from Machine PHOTO sheets.)rrrrTrz Machine PHOTOr|u&^(.+?)\s*[\(（]([가-힣\s&]+)[\)）]r r/) rrrrlrrrr>rematchgroup) rpairs hwia_filesrrrrrrrtrumenkrs rUscrape_bilingual_captionsrs EJ %||~   # #CJ$$ O "-- / HHJ   <)r3s rU get_base_namers 66+r; / 5 5 77rwc\g}|D]W}|r|d |dd|d}|D];} tj||tjr|j|V=Y|S#tj$r7|j |j vr|j|YYwxYw)z6Find all unclaimed package items matching any pattern.rrrbra)rsearch IGNORECASErFerrorrn)patterns pkg_itemsexclude_claimedmatchespkg search_textpats rUfind_matching_packagesrsG  s<0< _-.a4D0E/FG  C 99S+r}}=NN3'>   N 88 99;+"3"3"55NN3'6 s6A!!AB+*B+c^t|}g}g}|D]}||vs||vs |j|g}|D]*tfd|D}|r|j,|D]}|j||g} |j D]=\} |vs|vr| j| f"|vs|vs+| j| f?| D cgc]} | d } } | D]@\} tfd| D} | r|jt j | B|Scc} w)uGet all English search patterns for a Korean equipment name. Uses longest-match-wins: if 'A' and 'A B' both match, only 'A B' patterns are used. This prevents '코킹 & 컷팅 비젼검사' from inheriting '코킹 & 컷팅' patterns. c34K|]}|k7xr|vywN).0otherks rU z#get_patterns_for..s)%  J %1: %% rc34K|]}|k7xr|vywrr)rr kr_captions rUrz#get_patterns_for..s#[UZ5(@Z5-@@[r)rrFanyrrHrescape)r3 kr_en_map auto_captionsbaser matching_keyskr_key filtered_keysis_substring_of_longermatching_captions en_captionr caption_keysis_subrrs @@rUget_patterns_forrss  %DHM) [ FdN   () M $!$% &% " &   # $ + &)*+"/"5"5"7? J  $ z(A  $ $j*%= > 4 4:#5  $ $j*%= > ?#44QAaD4L4"33 J[l[[ OOBIIj1 23 O 5s D*c  (g}ttj((fd}|Dcgc]}||dr|}}|Dcgc]}||ds|}}tt}|D]7}|ddk(r d|d<d|d<t |d} || j |9t|jd } | D]} || } t| t|} | D]3}t|dt|}|D]}|| vs| j |5| s3| D]-}d |d<d |d<|j d |dd |ddddd/t| |}|s:| D]4}d |d<d| ddd|d<|j d |dd |ddddd6t| }t|}|dk(r|D] }| dd|d<|| dd<nt|dd }t|Dcgc]}g}}d!g|z}|D]@}|jt|}||j |||xx|d"z cc<Bt| D]\}}||D] }|d|d< |||d<!| D]k}|d}|s d |d<d#|d<t!d$|Dd%z }|d}|dkDrt#||z |z d&z}nd}|d'krd(|d<Vd)|d<d*|d+d,|d-|d.d/|d<m|D]!}|ddk(r d|d<d|d<|d}g} tj%D]\} }!| |vs | j'|!tj%D]\}"}#|"|vs | j'|#| sd |d<t| |}$|$D] }|d|d< |$|d<|$s d |d<d0|d<t!d1|$Dd%z }|d}|dkDrt#||z |z d&z}nd}|d'krd(|d<d)|d<d2|d+d,|d-|d.d3|d<|d4k\s|dxxd5z cc<$|D]4}|d |j d6|d7d8|d9d:|d"d;|d<d=d>d6|D]}|j)d?d@}%dA|%j+vs(t-j.dB|%}|sAt1|j3d}&hdC}'|&|'vsd|j dDdE|&dF|d<dG|d9dHdId|D](}|dd)k(s |j d)|d|ddJdKd*|Scc}wcc}wcc}w)LzCTwo-pass matching with group-aware distribution for numbered items.c.tfdDS)Nc3&K|]}|v ywrr)rckrQs rUrz4run_matching..is_catchall..s9"2:9s)r)rQcatchall_kr_keyss`rU is_catchallz!run_matching..is_catchalls9(8999rwr3r5r,NOT_IN_PACKAGES_EXPECTEDr7z"Jun 15 arrival (separate shipment)r8ct| Sr)rA)rs rUzrun_matching..s SVGrw)key NOT_FOUNDz%No mapping found for this Korean namezWeight r4zT, no English pattern mappedz"Cannot verify against package listu%Add Korean→English mapping manually)typerdetailimpactactionz Patterns Nr&z found no matcheszT, patterns tried but no matchz-May be packed under different name or missingzVerify with Daniel Leer rrr6c |dS)Nrr)ps rUrzrun_matching..7s Q{^rwT)rreverser+rz3Group had matches but none distributed to this itemc3&K|] }|d ywrNrrr s rUrzrun_matching..Ns9qAkN9@@d MATCH WEIGHT_DIFFzDelta .0fz% (plan z T vs pkg .2fzT net)z,Catch-all found no remaining unmatched itemsc3&K|] }|d ywr rr s rUrzrun_matching..}s81+8rzCatch-all delta zT)u. — expected large delta for catch-all bucketEXTRA_IN_PACKAGESrz Container rXz, Net z kg, Source: rz/Item in packages but not in our master schedulez2Determine if scope addition or ancillary equipmentrar$rz(\d+)\.\s*WINDING> r%r( rr~EXCLUDED_IN_PACKAGESzWinding Machine #z Found in z , container u3EXCLUDED item (5만 미포함) appears in packages!u0Verify scope — may be shipped for future phaseu.Weight discrepancy — may affect rigging planz)Verify weight with supplier documentation)r9CATCHALL_ITEMSkeysrrrrFsortedr KR_EN_MAPrrArangeindexminr<sumabsrHrrprrrrr)) master_itemsrrflagsrrspecific_itemscatchall_itemsgroupsrsorted_group_keys base_namerrextrar r6 n_mastersn_pkgsr sorted_pkgs_bucketsbucket_weightsmin_idxipkgs pkg_net_t planned_t delta_pctmasterkr_name catchall_kr catchall_patsren_patsmatcheddescpkg_numexcluded_wm_numsrs) @rU run_matchingrFs E>..01:".SA[=AQ5RaSNS!-OAQ}=M1NaONO F  Y<7 "2AfI=AgJ Q}-.t A v{{}2CD&Tg y!#Iy-H 'A$Q}%5y-PE 'H$OOA& ' '  '& D'  'm, '* 6RSBE   .h B  '& ("16GH'  'm, '* 6TUM6   J \" ># <$)!H]$;L! <'3E!H^ $!3KUYZK#(#34ar4G4"UY.N" <(..s>/BC '',w'3{+;;' < "%( /1"1:9C()-(8C %9$+AJ.! /  gA^$D'& R' 9D99FBI* I1} I 56BSH  B#& )& %i_HYKyQZ[^P__ef' % gETgn!.T )  '7F6NBF7O '+9*>*>*@ / &Kg% . / )0 )OFG ( )(F6N (9= 6C &} 5C  6!(~(F6NLF7O 8886A :& q=I 12Y>DII ?$F6N*F6N 03x {R[\efi[jjlmF7OBw#SS].Tb |  $ LL+O,&s;'7&8s;?O>PP\]`an]o\pqKN  ww',  , .5Aaggaj/#F ..LL 6"3G9 =$-c-.@-AcR]N^M_"`"W"T "" V9 % LL%-(G*JE   LATOH5sS9S9S>S>& Tctd}|D]}|d}||}|d|d<|dj||dxx|dz cc<|dxx|dz cc<|jd d }|r |d s||d <|d|d kDss|d|d <|d |d<t|j }g}d}d} |D] } || }|d j } |d dz } d| vsd| vrd} n"| dkrd} n| dkrd} n| dkrd} n | dkrd} nd} | dz } | dkDr|dz }d} dj d|dd dD}t|ddkDr|d!t|ddz d"z }|j| |dd#k(rd$nd%|d xsd&t|dt|dt|dt|d |d| ||d'  |S)(zAGroup items by container, compute forklift class and unload days.cddgdddddS)Nr$r+)rcontainer_typerHrZr[ heaviest_kg heaviest_namerrrwrUrz)build_container_summary..s"r3R&rwrXrrHrZrr[rr_r$rIrJrrKr rrFRFLATCraner&z3.5Tr%5Tr(z7.5Tr17Tr; c3,K|] }|dddyw)rNrrr s rUrz*build_container_summary..sO1q1#26OsN (+z more)rDKTECz Hyundai WIA40HQ) rXrrI num_itemsrrrJrKforklift_class unload_day items_list) rrFrpr"r!rjoinrAround)r containersrctrspecssorted_containerssummaryday count_todayct_numct_type heaviest_tforkliftrYs rUbuild_container_summaryrgseJ 6   rNJ*  ' # ( s;''  * ]++ )2. +,"'A  } - 0 0"=1Am !$_!5Ao 6 z01G CK#(  v $%++-}%.  7?f/H 1_H 1_H 1_H 2 HHq   1HCKYYO' 2AOO qz?Q  CAgJ 12&9 9J#$Z=D#8m 01;VQwZq{+ :/ =!12/&$  9( T Nrwcgd}|g}t|dD]\}}|jdg}ttd|D}|rdj |ddnd}t |dkDr|d t |dz d z }ttd |D}|rd j t |nd } td|Ddz } td|Ddz } |ddkDr|r| |dz |dz dz} | dd} nd } td|Dddz }d }|rm|Dcgc]}td|dDs|d}}|rCtd|D}td|D}td|D}|dd|dd|d}|jt||d||dd |d!|d"xsd | |r| d nd |r| d nd | |dkDr|d nd ||d"xsd |d#g|Scc}w)$z$Build Sheet 1: Cross-Reference data.)zItem Noz Korean Namez English NamezOur Weight NET (T)ArrivalzFound?z Container #szPackage Net (T)zPackage Gross (T)zDelta %z Max Piece (T)zMax Dims LxWxH (mm)FlagNotesr r6c3&K|] }|d yw)rNrr s rUrz'build_crossref_sheet..s=1Ao.=rrQNr&z (not matched)rS)c3&K|] }|d yw)rXNrr s rUrz'build_crossref_sheet..s8a1[>8rz, r$c3&K|] }|d ywr rr s rUrz'build_crossref_sheet..s3a n3rrc3&K|] }|d ywrNrr s rUrz'build_crossref_sheet..s7Q-(7rr4rrz+.0f%c3&K|] }|d ywrqrr s rUrz'build_crossref_sheet.."s8a=)8rdefaultc3&K|] }|dkD ywrNrrds rUrz'build_crossref_sheet..&s:WQ1q5:Wrrc3&K|] }|d ywrwrrxs rUrz'build_crossref_sheet..(4QAaD4rc3&K|] }|d yw)r Nrrxs rUrz'build_crossref_sheet..)r{rc3&K|] }|d yw)r/Nrrxs rUrz'build_crossref_sheet..*r{rrxr3rr5r7r8) r<rprr9rZrAr"r'maxrrFrl)r)headersrowsr8rr9en_namesen_namect_numsct_strpkg_net pkg_grossdelta delta_str max_piecemax_dimsr  dims_listmax_lmax_wmax_hs rUbuild_crossref_sheetrsBG 9D,*( 1uu^R(===>-5$))HRaL)? x=1  S]1_-Q/ /Gs84889/66'?+B3d33f<7$77&@ Z=1 q},* =CE ,a(II848!DvM  /3X!s:W!I,:W7W9XIX4)444)444)44#C[%AeC[A FAm$g}S!AiL fIOV $wsm""&yoB "+a-yoR aio2qz  ?( T K%Ys )G0G0cgd}|g}|D]l}|j|d|d|dt|dt|dt|dt|d|d |d t|d |d g n|S) N) Supplierz Container #zContainer TypeItemsz Net Wt (kg)z Gross Wt (kg)zHeaviest Item (kg)z Heaviest NamezForklift Classz Unload Dayz Items ListrrXrIrVrrrJrKrWrXrY)rFrl)r`rrrs rUbuild_container_sheetr;sG 9D   jM1[>1-=+> + Q{^!4c!M:J6K - !1_#5  Q|_!5q     Krwc lgd}|g}|D]'}|j|d|d|d|d|dg)|S)N)z Flag TypeItemDetailImpactActionrrrrr)rF)r*rrrJs rUbuild_flags_sheetrLsOAG 9D S QvY& 1X;( Qx[QRS Krwc tdtdtdtdtt\}}|Dcgc] }|ddk(s |}}|Dcgc] }|ddk(s |}}td|D}tdt |d |d d td t |d td |Dd dtdt |d td|Dd dtdt |tdt t }td|Ddz }td|Ddz }tdt |td|d dtd|d dtdtt } tdt | dtdt||| } tt} |D]}| |dxsdxxdz cc<td t| |D cgc] } | d! |  } } td"t | | rT| dd#D]#} td$| d%dd&d'| d(d)| d*d+%t | d#kDrtd,t | d#z d-td.t|}t |}td/|Dd01}td2|D}td3|td4|td5|d6td7t|}t|}t!| }td8t |dz d9td:t |dz d9td;t |dz d9|||t |t |t#|d<t#|d<t#|d<|||t | t | d= d>}t$d?z }t'|d@dAB5}t)j*||dCd.`s.b6Tq}6TrTz Jun 15 arrival: c3&K|] }|d ywrrrs rUrzmain..crrz Excluded names tracked: z( [1b] Parsing package list xlsx files...c3&K|] }|d ywr rr s rUrzmain..is6qAkN6rrc3&K|] }|d ywrqrr s rUrzmain..js:1a &:rz Total package items: z Total net weight: z Total gross weight: z> [1c] Scraping bilingual captions from Machine PHOTO sheets...z Found u Korean→English pairsz! [2] Running two-pass matching...r7NONEr z Results: rz Unmatched package items: rz - r2z (rzkg) [r]z ... and z morez" [3] Building container summary...c3&K|] }|d yw)rXNrrrs rUrzmain..sB1qBrrrtc32K|]}|ddk(s dyw)rWrNr Nrrs rUrzmain..sVQq9I7Jg7UqVs z Containers: z Unload days (10/day): z Crane required: z containersz [4] Building sheet data...z Cross-Reference: z rowsz Container Summary: z Flags: r/) r)rtotal_planned_ttotal_pkg_net_ttotal_pkg_gross_tnum_containers unload_dayscrane_containers flags_countunmatched_pkg_items)crossrefr\r*statsz#project/cross_reference_output.jsonrdrrF) ensure_asciiindentz Output saved to: zG ====================================================================== VERIFICATIONz! 1. Total package gross weight: z.1fzT (expected ~324T)z 2. Non-assembly planned: c3&K|] }|d ywrrrs rUrzmain..s+IaAjM+IrzT vs packages zT grossz, 3. Jun 15 items NOT_IN_PACKAGES_EXPECTED: rz (expected 7)z 4. Unmatched package items: z (target: 0)z 5. Containers: z / 10/day = z working days)rrVTSV_PATHr'rArPKG_DIRrrFrrdictrgrrrrr[BASEr:jsondumprp)r)rIrr.r, total_plannedr total_net total_grossrr*by_flagr unmatched_pkgscontainer_summaryrr crane_needed crossref_rowscontainer_rows flags_rowsoutput json_pathrJs rUmainrVs) (O QR (O 12#8#B L.$ @1) (?Q @E @$ @1) (?Q @E @<|<':&; <= 56"7+I6I66?I: ::VCK #C N#3 45 3q 12 ";s"31 56 KL-g6M HS'((? @A ./ y- @E#G *& #V$)$* KW  '(!*FAa o.EaFNF 'N(;'< =>$ `A F1_-cr232a n5EU1]K[J\\]^ _ ` ~  # L^!4R!7 8> ? /0/ :*+NB0ABANKV"3VVL N>* +, $[M 23 |nK 89 ()(6M*+<=N"5)J M 21 45U ;< !#n"5a"7!8 >? Ic*oa'( ./"$ -Y$]A6$Y2!&{A!6,& ,u:#&~#6  F$<>P QR '+I5+I(I#'Nn]hil\mmt uv 8E_ab9c8ddq rs *3~+>*?| LM n-\+m TU M{ A @<G`;;s0 R>R> S)S S!S.S  S__main__)T) __doc__osrmathr collectionsrpathlibrrrrrOUTPUT_SHEET_IDr#r rVrvrzrrrrrrrFrgrrrr__name__rrwrUrsR # AB D D ( (@ ; 2; #%67 ;  %':; ;  68LM ; &; (*<=;  "+; A; 3X>;  ,!?;   "> !K!; "F#; $1<@%; &1<@'; (./); *./+; ,.0@A-; .9/; 0 ".1; 8,.>?9; :S;; <N=; >/0?; @+-CDA; B#%GC; F)*G; H}n=I; JZLK; L<>[\M; N')DF[(\O; P"Q; T$o%6U; V "=W; Z]L9[; \T##J2$ $m401u;  ~ >$*B!N+\fR#PF8 &+\J^GX4n"eP zFrw