+
    ¬�ëi5%  ã                  óò   € R t ^ RIHt ^ RIt^ RIHt ^ RIHtHtH	t	H
t
 ^ RIt^ RIHt ^ RIHt R R ltR	 tR
 R ltR R ltR R ltR R ltR R ltRR R lltR R ltR t]R8X  d
   ]! 4        R# R# )aŠ  
Pull selected fields from PartKeepr and merge into parts_inventory parts.

Fields merged into parts_inventory.parts:
  - average_price
  - minimum_stock_level
  - comment

Matching strategy (in order):
  1) source_partkeepr_id
  2) storage location name + part_number
  3) storage location name + part name

Usage example:
  python scripts/merge_partkeepr_part_fields.py ^
    --source-host 192.168.50.5 --source-port 3306 ^
    --source-db PartKeepr --source-user partkeepr --source-password partkeepr ^
    --target-host 192.168.50.5 --target-port 3306 ^
    --target-db parts_inventory --target-user root --target-password secret ^
    --dry-run
)ÚannotationsN)Údefaultdict)ÚDictÚListÚOptionalÚTuple)Ú
DictCursor)Ú_ensure_tablesc          
     ó,   € V ^8„  d   QhRRRRRRRRRR/# )é   ÚhostÚstrÚportÚintÚuserÚpasswordÚdatabase© )Úformats   "ÚDc:\Users\Bill\Software\Server\scripts\merge_partkeepr_part_fields.pyÚ__annotate__r   "   s/   € ÷ 	ñ 	�#ð 	˜Sð 	¨ð 	°sð 	Àcñ 	ó    c           
     óD   € \         P                  ! V VVVV\        R R7      # )F)r   r   r   r   r   ÚcursorclassÚ
autocommit)ÚpymysqlÚconnectr   )r   r   r   r   r   s   &&&&&r   r   r   "   s(   € Ü�?Š?ØØØØØÜØôð r   c                 ó  € \         P                  ! R R7      p V P                  RRR7       V P                  R\        RR7       V P                  RRR7       V P                  R	RR7       V P                  R
RR7       V P                  RRR7       V P                  R\        RR7       V P                  RRR7       V P                  RRR7       V P                  RRR7       V P                  RRR7       V P	                  4       # )zZMerge average price, minimum stock level, and comment from PartKeepr into parts_inventory.)Údescriptionz--source-hostT)Úrequiredz--source-portiê  )ÚtypeÚdefaultz--source-dbz--source-userz--source-passwordz--target-hostz--target-portz--target-dbz--target-userz--target-passwordz	--dry-runÚ
store_true)Úaction)ÚargparseÚArgumentParserÚadd_argumentr   Ú
parse_args)Úparsers    r   r'   r'   .   sý   € Ü×$Ò$Øpô€Fð ×Ñ˜°$ÐÔ7Ø
×Ñ˜¬c¸4ÐÔ@Ø
×Ñ˜°ÐÔ5Ø
×Ñ˜°$ÐÔ7Ø
×ÑÐ+°dÐÔ;Ø
×Ñ˜°$ÐÔ7Ø
×Ñ˜¬c¸4ÐÔ@Ø
×Ñ˜°ÐÔ5Ø
×Ñ˜°$ÐÔ7Ø
×ÑÐ+°dÐÔ;Ø
×Ñ˜¨LÐÔ9Ø×ÑÓÐr   c               ó    € V ^8„  d   QhRRRR/# )r   Ú
table_namer   Úreturnúset[str]r   )r   s   "r   r   r   @   s   € ÷ :ñ :¨ð :°ñ :r   c                óì   € V P                  4       ;_uu_ 4       pVP                  R V34       VP                  4        Uu0 uF  q3R,          kK  	  upuuRRR4       # u upi   + '       g   i     R# ; i)z¦
            SELECT COLUMN_NAME
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = %s
            ÚCOLUMN_NAMEN©ÚcursorÚexecuteÚfetchall)Úconnr*   ÚcurÚrs   &&  r   Úget_table_columnsr6   @   s]   € Ø	�‰�Œ˜#Ø�‰ðð ˆMô	
ð +.¯,©,¬.Ó9©. Q�-× Ð ©.Ñ9÷ 
Šùò :÷ 
�ˆús   ™&A"¿AÁA"ÁA"Á"A3	c               ó$   € V ^8„  d   QhRRRRRR/# )r   Ú	availabler,   Ú
candidatesz	List[str]r+   zOptional[str]r   )r   s   "r   r   r   N   s!   € ÷ ñ ˜Xð °9ð Àñ r   c                ó,   € V F  pW 9   g   K  Vu # 	  R # ©Nr   )r8   r9   Úcs   && r   Úchoose_columnr=   N   s   € ÛˆØŽ>ØŠHñ ñ r   c               ó   € V ^8„  d   QhRR/# )r   r+   zTuple[List[dict], str, str]r   )r   s   "r   r   r   U   s   € ÷ "ñ "Ð,Gñ "r   c                ób  € \        V R 4      p\        V. RO4      p\        V. RO4      pV'       g   \        R4      hV'       g   \        R4      hRV RV R2pV P                  4       ;_uu_ 4       pVP	                  V4       VP                  4       pRRR4       XW#3#   + '       g   i     L; i)	ÚPartz;PartKeepr Part table has no average price column candidate.zAPartKeepr Part table has no minimum stock level column candidate.zš
        SELECT
          p.id AS source_partkeepr_id,
          p.name,
          p.internalPartNumber AS part_number,
          p.comment,
          p.`z!` AS average_price,
          p.`zµ` AS minimum_stock_level,
          s.name AS storage_location_name
        FROM Part p
        LEFT JOIN StorageLocation s ON p.storageLocation_id = s.id
        ORDER BY p.id
    N)ÚaveragePriceÚaverage_priceÚprice)ÚminStockLevelÚminimumStockLevelÚminimum_stock_levelÚmin_stock_level)r6   r=   ÚRuntimeErrorr0   r1   r2   )Úsrc_connÚpart_columnsÚavg_colÚmin_colÚsqlr4   Úrowss   &      r   Úload_source_partkeepr_rowsrO   U   s«   € Ü$ X¨vÓ6€LÜ˜LÒ*TÓU€GÜ˜LÒ*zÓ{€GßÜÐXÓYÐYßÜÐ^Ó_Ð_ðð ˆYð ØˆYð ð€Cð 
�‰×	Ô	˜cØ�‰�CÔØ�|‰|‹~ˆ÷ 
ð �Ð!Ð!÷ 
×	ús   Á0"BÂB.	c               ó   € V ^8„  d   QhRR/# )r   r+   z
List[dict]r   )r   s   "r   r   r   q   s   € ÷ ñ  *ñ r   c                ó²   € V P                  4       ;_uu_ 4       pVP                  R 4       VP                  4       uuRRR4       #   + '       g   i     R# ; i)a7  
            SELECT
              p.id,
              p.source_partkeepr_id,
              p.name,
              p.part_number,
              s.name AS storage_location_name
            FROM parts p
            LEFT JOIN storage_locations s ON s.id = p.storage_location_id
            ORDER BY p.id
            Nr/   )Údst_connr4   s   & r   Úload_target_rowsrS   q   s<   € Ø	�‰×	Ô	˜cØ�‰ð
ô	
ð �|‰|‹~÷ 
×	×	Ó	ús   ™!AÁA	c               ó   € V ^8„  d   QhRR/# )r   r+   r   r   )r   s   "r   r   r   ƒ   s   € ÷ ñ ˜Sñ r   c                ó@   € V f   R# \        V 4      P                  4       # )NÚ )r   Ústrip)Úvalues   &r   Únormalize_textrY   ƒ   s   € Ø‚}ÙÜˆu‹:×ÑÓÐr   c               ó    € V ^8„  d   QhRRRR/# )r   r!   Úfloatr+   r   )r   s   "r   r   r   ‰   s   € ÷ ñ  Uð °Uñ r   c                óX   €  V f   V# \        V 4      #   \        \        3 d    Tu # i ; ir;   )r[   Ú	TypeErrorÚ
ValueError)rX   r!   s   &&r   Únormalize_numberr_   ‰   s4   € ðØŠ=ØˆNÜ�U‹|ÐøÜ”zÐ"ô ØŠðús   ‚ ˆ
 “)¨)c          
     ó,   € V ^8„  d   QhRRRRRRRRRR	/# )
r   Úsrc_rowÚdictÚby_source_idzDict[int, dict]Ú	by_loc_pnz!Dict[Tuple[str, str], List[dict]]Úby_loc_namer+   zTuple[Optional[dict], str]r   )r   s   "r   r   r   ’   s:   € ÷ ñ ˜tð °?ð ÐOpð ð  @að ð  f@ñ r   c                óÜ  € V P                  R 4      pWA9   d   W,          R 3# \        V P                  R4      4      p\        V P                  R4      4      p\        V P                  R4      4      pV'       d7   V'       d/   VP                  WV3. 4      p\        V4      ^8X  d   V^ ,          R3# V'       d7   V'       d/   VP                  WW3. 4      p\        V4      ^8X  d   V^ ,          R3# R# )Úsource_partkeepr_idÚstorage_location_nameÚpart_numberÚnameúlocation+part_numberúlocation+name)NÚ	unmatched)ÚgetrY   Úlen)	ra   rc   rd   re   Úsrc_idÚloc_nameri   rj   r9   s	   &&&&     r   Úchoose_target_rowrr   ’   sÍ   € Ø�[‰[Ð.Ó/€FØÔØÕ#Ð%:Ð:Ð:ä˜gŸk™kÐ*AÓBÓC€HÜ  §¡¨]Ó!;Ó<€KÜ˜'Ÿ+™+ fÓ-Ó.€Dç—KØ—]‘] HÐ#:¸BÓ?ˆ
Üˆz‹?˜aÔØ˜a•=Ð"8Ð8Ð8ç—DØ —_‘_ hÐ%5°rÓ:ˆ
Üˆz‹?˜aÔØ˜a•= /Ð1Ð1àÐr   c                 óÞ  € \        4       p \        V P                  V P                  V P                  V P
                  V P                  4      p\        V P                  V P                  V P                  V P                  V P                  4      p \        V4       \        V4      w  r4p\        V4      p/ p\        \         4      p\        \         4      p	V FÎ  p
V
P#                  R 4      e   W§\%        V
R ,          4      &   \'        V
P#                  R4      4      p\'        V
P#                  R4      4      p\'        V
P#                  R4      4      pV'       d"   V'       d   W‹V3,          P)                  V
4       V'       g   K«  V'       g   Kµ  W›V3,          P)                  V
4       KÐ  	  ^ p^ p^ p\        \$        4      pVP+                  4       ;_uu_ 4       pV EFƒ  p\-        VWxV	4      w  ppV'       gÔ   \'        VP#                  R4      4      p\'        VP#                  R4      4      p\'        VP#                  R4      4      pRpV'       d,   V'       d$   \/        VP#                  W¼3. 4      4      ^8”  d   RpV'       d,   V'       d$   \/        V	P#                  W½3. 4      4      ^8”  d   RpV'       d   V^,          pM	V^,          pKï  VV;;,          ^,          uu&   \1        VP#                  R4      R4      p\%        \1        VP#                  R	4      ^ 4      4      p\'        VP#                  R
4      4      pVP3                  RVVVVR,          34       V^,          pEK†  	  RRR4       V P4                  '       d   VP7                  4        MVP9                  4        \;        RR\/        V4       2RV 2RVR ,           2RVR,           2RVR,           2RV 2RV 2RV 2RV 2RV P4                   24        TP?                  4        TP?                  4        R#   + '       g   i     LÁ; i  \<         d    TP7                  4        h i ; i  TP?                  4        TP?                  4        i ; i)rg   Nrh   ri   rj   FTrB   ç        rF   Úcommenta  
                    UPDATE parts
                    SET average_price = %s,
                        minimum_stock_level = %s,
                        comment = %s,
                        updated_at = CURRENT_TIMESTAMP
                    WHERE id = %s
                    ÚidzMerge complete:zsource_rows=zupdated=zmatched_by_source_id=z matched_by_location_part_number=rk   zmatched_by_location_name=rl   zskipped_unmatched=zskipped_ambiguous=zsource_avg_col=zsource_min_col=zdry_run=) r'   r   Úsource_hostÚsource_portÚsource_userÚsource_passwordÚ	source_dbÚtarget_hostÚtarget_portÚtarget_userÚtarget_passwordÚ	target_dbr	   rO   rS   r   Úlistrn   r   rY   Úappendr0   rr   ro   r_   r1   Údry_runÚrollbackÚcommitÚprintÚ	ExceptionÚclose)ÚargsrI   rR   Úsource_rowsrK   rL   Útarget_rowsrc   rd   re   Úrowrq   ri   rj   ÚupdatedÚskipped_unmatchedÚskipped_ambiguousÚ
matched_byr4   ra   Údst_rowÚmethodÚ	ambiguousÚ	avg_priceÚ	min_stockru   s                             r   Úmainr–   ¨   sÑ  € Ü‹<€DÜ�t×'Ñ'¨×)9Ñ)9¸4×;KÑ;KÈT×MaÑMaÐcg×cqÑcqÓr€HÜ�t×'Ñ'¨×)9Ñ)9¸4×;KÑ;KÈT×MaÑMaÐcg×cqÑcqÓr€HðUÜ�xÔ Ü(BÀ8Ó(LÑ%ˆ˜gÜ& xÓ0ˆà(*ˆÜ7BÄ4Ó7Hˆ	Ü9DÄTÓ9JˆãˆCØ�w‰wÐ,Ó-Ò9Ø@CœS Ð%:Õ!;Ó<Ñ=Ü% c§g¡gÐ.EÓ&FÓGˆHÜ(¨¯©°Ó)?Ó@ˆKÜ! #§'¡'¨&£/Ó2ˆDßŸKØ [Ð1Õ2×9Ñ9¸#Ô>ß‰xŸD™DØ tÐ,Õ-×4Ñ4°SÖ9ñ ð ˆØÐØÐÜ ¤Ó%ˆ
à�_‰_×Ô #Ü&�Ü"3°G¸\ÐVaÓ"b‘�˜ßä-¨g¯k©kÐ:QÓ.RÓS�HÜ"0°·±¸]Ó1KÓ"L�KÜ)¨'¯+©+°fÓ*=Ó>�DØ %�Iß§K´C¸	¿¹ÀxÐF]Ð_aÓ8bÓ4cÐfgÔ4gØ$(˜	ß§D¬S°·±À(ÐAQÐSUÓ1VÓ-WÐZ[Ô-[Ø$(˜	ß Ø)¨QÕ.Ñ)à)¨QÕ.Ð)Ùà˜6×" aÕ'Ó"Ü,¨W¯[©[¸Ó-IÈ3ÓO�	ÜÔ 0°·±Ð=RÓ1SÐUVÓ WÓX�	Ü(¨¯©°YÓ)?Ó@�à—‘ðð  	¨7°G¸DµMÐBô
ð ˜1•“ñE '÷ ðJ �<�<ˆ<Ø×ÑÕà�O‰OÔäØØœ3˜{Ó+Ð,Ð-Ø�w�iÐ Ø# JÐ/DÕ$EÐ#FÐGØ.¨zÐ:PÕ/QÐ.RÐSØ'¨
°?Õ(CÐ'DÐEØ Ð!2Ð 3Ð4Ø Ð!2Ð 3Ð4Ø˜g˜YÐ'Ø˜g˜YÐ'Ø�t—|‘|�nÐ%õ	
ð" 	�‰ÔØ�‰Ö÷y ×ûôn ô Ø×ÑÔØðûð 	�‰ÔØ�‰Õúse   ÂCP* ÅP* Å?P* Æ	A
P* ÇA>PÉ*PÉ=PÊ*PÊ0B.PÍP* Í8A<P* ÐP'	Ð"P* Ð*QÑQ
 Ñ
"Q,Ú__main__)rt   )Ú__doc__Ú
__future__r   r$   Úcollectionsr   Útypingr   r   r   r   r   Úpymysql.cursorsr   Úblueprints.partsinventoryr	   r   r'   r6   r=   rO   rS   rY   r_   rr   r–   Ú__name__r   r   r   Ú<module>rŸ      sl   ðñõ* #ã Ý #ß .Ó .ã Ý &å 4õ	òõ$:õõ"õ8õ$÷õò,Zðz ˆzÔÙ†Fñ r   