
    ic                     t   d Z ddlZddlZddlZddlZddlmZ ddlmZ  ee      j                  Z
d Zdadadad Zd Zd Zd	 Zd
 Zd Zd Zd Zd Zd Zd Zd Zd ZdWdZdad Zd Zd Z dXdZ!d Z"ddl#Z$dWdZ%d Z&dWdZ'd Z(d Z)dWdZ*dYdZ+d  Z,e-d!k(  rddl.Z.e.j^                  ja                  d"#        e1d$        e,       Z2 e1d%e2d&            e1d'e2d(            e1d)       e2d*   jg                         D ]9  \  Z4Z5e5d+   dkD  re5d,   e5d+   z  d-z  ndZ6 e1d.e4 d/e5d+    d0e5d,    d1e6d2d3	       ;  e       Z7e7re7d   Z8 e1d4e8d5    d6        e1d7e8d8            ee8d5   d9d:d;d<      Z9d=e9vr e1d>e9d?   d@    dAe9d?   dB    dC        e1dDe9d?   dE            e1dFe9d?   dG            e1dH       e9dI   dJ   D ]'  Z:e:dK   re:dK    dLe:dM    ndNZ; e1d.e:dO    d/e;        )  e1dPe9dQ   dR    dS       e9dQ   dT   D ]  Z< e1d.e<dO    d/e<dU            y e1dVe9d=           yyy)Zu=  
Certificate Manager Engine for MECSU
=====================================
Manages quality certificates (CO/CQ) for fastener products.
Key concept: ONE template format per supplier + standards database = auto-generated certificates

Architecture:
- cert_config.json: Standards database (inspection profiles, mechanical/chemical specs per group)
- fastener_specs.json: Dimensional tolerances from globalfastener.com
- products.json: Product master data (part → category → group mapping)
- template_*.docx: Supplier template formats (one per product group per supplier)
    N)datetime)Pathc                     ddl m}  ||       S )z5Load JSON data via SQLite-backed storage (see db.py).r   load_json_file)dbr   )filenamer   s     &/app/cer_product_mecsu/cert_manager.py	load_jsonr      s    !(##    c                  0    t         t        d      a t         S )Nzcert_config.json)_configr    r   r
   
get_configr   $   s    ./Nr   c                  0    t         t        d      a t         S )Nzproducts.json)	_productsr   r   r   r
   get_productsr   +   s    o.	r   c                  0    t         t        d      a t         S )Nzfastener_specs.json)_specsr   r   r   r
   	get_specsr   2   s    ~01Mr   c                      da daday)z&Reload all config files (after edits).N)r   r   r   r   r   r
   
reload_allr   9   s     GIFr   c                 >    t               }|D ]  }|d   | k(  s|c S  y)zFind a product by part number.partN)r   )part_numberproductsps      r
   find_productr   C   s.    ~H V9#H r   c           	         t               }| j                         }g }|D ]`  }|j                  dd       d|j                  dd       d|j                  dd       j                         }||v sP|j                  |       b |S )z9Search products by part number, description, or category.r     descriptioncategory_name)r   lowergetappend)queryr   query_lowerresultsr   
searchables         r
   search_productsr+   L   s    ~H++-KG fR()155r+B*C1QUU?[]E^D_`ffh
*$NN1 Nr   c                 v   t               }| j                  dd      }|j                  di       }||v r||   S | d| j                  dd       d| j                  dd       j                         }|d   j                         D ]4  \  }}|j                  dg       D ]  }|j                         |v s|c c S  6 y	)
zQDetermine the product group (BOLT/NUT/WASHER/SCREW/THREAD_ROD) from product data.r#   r    category_group_mappingr!   r"   standardproduct_groupskeywordsOTHER)r   r%   upperitems)productconfigcategorymappingdesc	group_key
group_datakws           r
   classify_product_groupr<   Z   s    \F{{?B/H jj126G7x   Zq]B78'++jRT:U9VW]]_D!'(8!9!?!?!A !	:..R0 	!BxxzT!  	!!
 r   c                 >    t               }|d   j                  |       S )z/Get the inspection profile for a product group.r/   r   r%   )r9   r5   s     r
   get_group_profiler?   n   s     \F"#''	22r   c                     | j                  dd       d| j                  dd       j                         t        fddD              rt        fddD              ry	y
y)z7Detect material type from product description/category.r"   r    r!   r#   c              3   &   K   | ]  }|v  
 y wNr   .0r;   r8   s     r
   	<genexpr>z'detect_material_type.<locals>.<genexpr>z   s     
P"2:
P   )inox304316a2a4	stainlessc              3   &   K   | ]  }|v  
 y wrB   r   rC   s     r
   rE   z'detect_material_type.<locals>.<genexpr>{   s     2brTz2rF   )rI   rK   )rL   A4(316))rL   A2(304))carbonCarbon Steel)r%   r$   any)r4   r8   s    @r
   detect_material_typerS   v   sZ    kk-,-Qw{{?B/O.PQWWYD

P O
PP2M22)%#r   c                     | j                  dd      }dD ]
  }||v s|c S  dD ]
  }||v s|c S  | j                  dd      }d|v ryd|v sd|v ryd	|v sd
|v ry	d|v sd|v ryt        |       \  }}|dk(  ryy	)z&Detect grade from product description.r"   r    )zA4-80zA4-70zA2-80A2-70)12.910.98.8z5.64.8r#   rV   rW   10rX   u   Cấp Bền 8rY   u   Cấp Bền 4rL   rU   )r%   rS   )r4   r8   gradecatmat_type_s         r
   detect_grader_      s    ;;}b)D 6 D=L
 7 D=L
 ++or
*C}}|#-|#- 'w/KHa;r   c                     | syt        j                  dd| j                               } t        j                  dd|       } | j                         j                         S )u:   Normalize standard name for matching (DIN933 → DIN 933).r    z-\d{4}$z([A-Za-z])(\d)z\1 \2)resubstripr2   )stds    r
   normalize_standardre      sH    
&&R
-C
&&"Hc
2C99;r   c                    | si S | j                         j                         } i }t        j                  d|       }|rt	        |j                  d            |d<   t        j                  d|       }|rt	        |j                  d            |d<   t        j                  d|       }|rt	        |j                  d            |d<   |S )zDParse size string like 'M8', 'M12x35', 'M8-1.75x35' into components.zM(\d+(?:\.\d+)?)   thread_diameterz-(\d+(?:\.\d+)?)\s*[Xx]pitchz[Xx]\s*(\d+(?:\.\d+)?)length)rc   r2   ra   matchfloatgroupsearch)size_strresultms      r
   
parse_sizerr      s    	~~%%'HF
$h/A$)!''!*$5 !
		,h7A
+w
		+X6A ,x Mr   c                    t               }t        |       }t        |      }|j                         D ]"  \  }}|j	                  dg       D ]  }t        |j	                  dd            }|s"||j                  dd      v s7|j	                  dg       D ]  }	|	j	                  dd      }
|j	                  d      s'|d   t        |d         k(  rd	t        |d          nd	|d    }|d   t        |d         k(  rd
t        |d          nd
|d    }|	j	                  dd      }||
v s
||
v s||k(  s|j	                  d      |
|	|dc c c S  	 % y)z9Find dimensional specifications from fastener_specs.json.	standardsr.   r      r!   
dimensionssizerh   Mu   ΦzFor Nominal Thread Diameter)r.   rw   rawr6   N)r   re   rr   r3   r%   replaceint)r.   rw   specsnorm_stdparsed_sizecat_keycat_data	std_entry	entry_stddimdim_size
thread_strphi_str
thread_refs                 r
   find_dimensional_specsr      s   KE!(+HT"K"[[] !k26 	I*9==R+HIIH	(9(9$(DD$==r: C"wwvr2H"'89R]^oRptw  yD  EV  yW  uX  SXq[9J-K)L(M%N  `a  bm  n  b@  aA  ^B
P[\mPnru  wB  CT  wU  sV  QVBs;7H+I'J&K"L  ^`  al  m~  a  `@  \A%(WW-JB%O
%1W5HJZdLd,5MM*,E(0'*,3	$ 	( r   c                    |xs i }t        |       }|sdd|  iS t        |      }t        |      }|sdd|  iS t        |      \  }}t	        |      }t               }|d   j                  |i       }	|d   j                  |i       }
t        |j                  d      |j                  d            }g }|d   D ]]  }|d	   |d
   |d   dddg dd}|r4|j                  d      r#|d   }t        ||||j                  dd            }|j                  |       _ |}|dk(  r9|j                  dd      j                         t        fddD              rd}nd}|j                  di       j                  |i       }g }|j                  dg       D ]2  }|d	   |d
   |d   t        |d	   |	      ddd}|j                  |       4 g }g d}|D ]  }|
j                  |i       }d}|j                  d      r!|j                  d      r|d   dd|d   d}n5|j                  d      r
d|d   d}n|j                  d      r	d|d   d}|j                  ||dd        | |j                  dd      |j                  dd      |j                  dd      |j                  d d      t        |j                  dd      |      d!||d   |d"   |||d#|j                  d$d%      |j                  d&d'      |j                  d(d      |j                  d)d      |j                  d*d      |j                  d+d      |j                  d,d      |j                  d-d      |j                  d.|d/k(  rd0nd1      |j                  d2|r| nd      |j                  d3t        j                         j!                  d4            d5|j                  dd      |d6|j                  d7d      |d8|
j                  d
|      |j                  d+d      |d9t        j                         j#                         |j                         |j                  d:d;      d<d=}|S )>uy  
    Build complete certificate data for a product.

    This is the core function: given a part number, it:
    1. Looks up the product → determines group (BOLT/NUT/WASHER/SCREW)
    2. Gets inspection profile → knows WHAT to measure
    3. Looks up dimensional specs → knows the TOLERANCES
    4. Looks up grade/material → knows MECHANICAL requirements
    5. Returns a complete data structure ready for template rendering

    Args:
        part_number: Product part number
        overrides: Dict to override auto-detected values (quantity, lot_no, heat_no, etc.)

    Returns:
        Dict with all certificate data
    errorzProduct not found: zUnknown product group for: grade_specschemical_specsr.   rw   dimensional_characteristicskeynamename_vir    )r   r   r   spec_minspec_maxspec_nominalactual_resultsdeterminantry   WASHERr"   c              3   &   K   | ]  }|v  
 y wrB   r   rC   s     r
   rE   z)build_certificate_data.<locals>.<genexpr>)  s     UbrTzUrF   )u   vênhspringlockdin127din7980spring_washerflat_washermechanical_standards
properties)r   r   r   specificationactual_resultr   
CSiMnPSNiCrMoCuNminmax.3f - u   ≤ u   ≥ )elementr   actual_valuer#   )r   r"   r.   rw   r6   ri   name_en)r9   group_name_vigroup_name_enmaterial_typematerial_coder[   	purchaserMECSU JOINT STOCK COMPANYaddressz039 Tran Hung Dao Street, Tan Thanh Ward, Tan Phucert_nopo_noquantityheat_nolot_nomfr_datesurfacerL   
PassivatedzZinc Platedmarkinginspection_date%Y/%m/%d)r   r   r   r   r   r   r   r   r   r   r   )sample_standardcharacteristicstest_standard)r   r   )material_nameheat_numberelementssupplierMECSU)generated_attemplate_groupsupplier_format)r4   classificationheaderdimensional
mechanicalchemicalmetadata)r   r<   r?   rS   r_   r   r%   r   "_match_dimension_to_characteristicr&   r$   rR   _get_mechanical_spec_extract_pitchr   nowstrftime	isoformat)r   	overridesr4   r9   group_profiler]   mat_coder[   r5   
grade_spec	chem_spec	dim_specsr   char	char_datary   	mech_typemech_standardsmechanical_propsprop	prop_datachemical_elementsr   elel_specspec_str	cert_datar8   s                              @r
   build_certificate_datar      s   $ RI ;'G.{m<== 'w/I%i0M6{mDEE .g6Hh!E\F&**5"5J'(,,Xr:I 'w{{:'>F@STI O;< *;LI 	
	 u-E"C:4iQXQ\Q\]gikQlmIy)#*( IH{{="-335U$TUU'I%I"&&'=rBFFyRTUN""<4 	+;LI1$u+zJ
	 		*	+ GH --B';;u'++e"4!%.-S0DEH[[genS12H[[genS12H  %"
 	& '";;}b9J3KK+OR8#GKK
B$?K
 #*95*95%%
 #{4OP }}Y0bc }}Y3]]7B/!j"5 }}Y3mmHb1!j"5 }}YK@W]jk }}Ye5'L(}}->@W@WXb@cd
  '{{:r:.

 ,//D*

 ']]68<$==B7)
 %LLN446'oo/(}}ZA
W0Id r   c                  >    t         ddlm}   | d      xs i a t         S )u7   Load bảng mapping field name → characteristic name.r   r   zfield_mapping.json)_field_mappingr   r   r   s    r
   get_field_mappingr     s#     %'(<=Cr   c                 ~   t               }t        j                  dd|       j                         }|D ]  }|j	                  d      rt        j                  dd|      j                         |k(  s>||   }d|v r/d|vr+|d   }|j                  |i       }|j                  di       c S |j                  di       c S  i S )u   
    Lấy mapping cho 1 tiêu chuẩn cụ thể.
    Hỗ trợ _inherit: nếu DIN985 inherit DIN934, dùng mapping của DIN934.
    \s+r    r^   _inheritr7   )r   ra   rb   r2   
startswithr%   )standard_namefmcleanr   entry
parent_keyparents          r
   _get_standard_mappingr     s    
 
	BFF62}-335E  
,>>#66&"c"((*e3sGEU"y'=":.

B/zz)R0099Y++
, Ir   c                    |r|j                  d      sy|d   }t               }t        j                  dd|       j	                         }|D ]i  }|j                  d      rt        j                  dd|      j	                         |k(  s>||   j                  dd      }|sV||v s[t        ||         c S  |j                         D ]7  \  }}d|j                         v sd|j                         v s,t        |      c S  y)zcExtract pitch value from dimensional specs (stored in product info, not in dimensional inspection).ry   r    r   r^   pitch_fieldri   coarse)	r%   r   ra   rb   r2   r   strr3   r$   )	r   r   ry   r   r   r   r   kvs	            r
   r   r     s    IMM%0
E
C		BFF62}-335E ->>#66&"c"((*e3S'++mR8K{c13{+,,- 		 1aggiH	$9q6M r   c                    | d   }t        |      }|j                  |d      }||S d }|j                  dd      }|r< |||      }	|	1	 t        t        t        |	      j	                  dd                  |d<   |j                  d	d      }
|
r> |||
      }	|	3	 t        t        t        |	      j	                  dd                  |d
<   |S |S # t
        t        f$ r Y ew xY w# t
        t        f$ r Y |S w xY w)u  
    Match characteristic to raw dimensional data using field_mapping.json.

    Cơ chế:
    1. Tìm mapping cho tiêu chuẩn (VD: DIN934 → {"Width Across Flats": {"min": "s min", "max": "s max=nominal size"}})
    2. Lấy mapping cho characteristic name (VD: "Width Across Flats")
    3. Đọc giá trị từ raw_dims theo field name chính xác (VD: raw_dims["s min"] = 18.67)
    r   Nc                     || v r| |   S t        j                  dd|      j                         }| j                         D ]3  \  }}t        j                  dd|      j                         }||k(  s1|c S  y)uG   Find field value with normalization (handles e①, =nominal size, etc.)u$   [①②③④⑤⑥⑦⑧⑨⑩\(\)]r    N)ra   rb   rc   r3   )ry   
field_name
norm_fieldr  r  norm_ks         r
   _find_field_valuez=_match_dimension_to_characteristic.<locals>._find_field_value  sy     z?"VVCRTZZ\
IIK 	DAqVVCRKQQSF#	 r   r   r    ,.r   r   r   )r   r%   r  rl   rz   
ValueError	TypeError)char_defraw_dimsr   r   	char_namer7   char_mappingr	  	min_fieldval	max_fields              r
   r   r     s#     I#M2G ;;y$/L   +I)4?(+E#c(2B2B32L,M(N	*%
   +I)4?(+E#c(2B2B32L,M(N	*% 9 	*  	* s$   
0C 0C# C C #C65C6c                 d   |syddddddd}| |vry||    \  }}|sy|j                  |      }|| d	k(  r|j                  d
      }|r| dS y| dk(  rA|j                  dd      }|j                  dd      }|r	|r| d| S |r| dS t        |      S |r|j                  |      S t        |      S )z=Get mechanical property specification string from grade spec.r    )tensile_strength_min{} min)yield_strength_minr  )elongation_min_pctr  )hardness_minN)proof_load_stressr  )Nr    )tensile_strengthyield_strength
elongationhardness
proof_loadfree_heightr  elongation_minzd minr  r  hardness_maxr    max)r%   r  format)prop_keyr   mappings	field_keyfmtr  h_minh_maxs           r
   r   r     s     ?:6*5!H xh'NIs
..
#C
{|#..!12Ce}$:~r2~r2UWCw''WD>!5z
zz#s8Or   c                   IJK ddl m} ddlmIm}m}m} ddlmJ ddl	m
} ddlmK | d   }| d   }| d	   }	| d
   d   }
| d   d   }| d   d   }|j                  dt        j                         j!                  d            }| d   d   }dddddd}|j                  |d      } |       }|j"                  d   } |d      |_         |d      |_         |d      |_         |d      |_         |d      |_         |d      |_        dIJfd"	}dKfd#	}|j1                         }Jj2                  |_        |j7                  d$      } Id%      |j8                  _        d&|j8                  _        d'|j8                  _        d&|_         |jC                  d(d)*      }|j2                  |_         ||jE                  dd      d+d d&        ||jE                  dd,      |j                  d-d.      d         ||jE                  dd/      d0|j                  d1d2       d d&        ||jE                  dd3      d4d         ||jE                  d,d      d5d d&        ||jE                  d,d,      |j                  d6d7      d         ||jE                  d,d/      d8|j                  d9d2       d d&        ||jE                  d,d3      d:|j                  d;d2       d         ||jE                  d<d,      d=d         ||jE                  d/d,      d>d        |j1                          |jC                  d)d)*      }|j2                  |_        d?|j                  d@d2      dA|	j                  dBd2      dCtG        tI        jJ                  dDdE            fdF|j                  dGd2      dH|j                  dId2       dJdK|j                  dLd2      fdMdNdO|j                  dPd2      dQ|j                  dRd2      fdS|j                  dT|      dU|dV|j                  dWd2      fdX| d2dY| d2dZd[fg}tM        |      D ];  \  }}tM        |      D ](  \  }}|d<z  dk(  } ||jE                  ||      |d |       * = |j1                          | d
   j                  d\d2      }|
D  cg c]<  } | j                  d]      s'| j                  d^      sd_| d`   jO                         v s;| > }
} tQ        |
      d<z   }!|jC                  |!d3*      }"|j2                  |"_         ||"jE                  dd      dadbd&        ||"jE                  dd/      dc| dbd&dd       tM        g de      D ]!  \  }}# ||"jE                  d,|      |#d d&df       # tM        |
      D ]  \  }$}%|$d<z   } ||"jE                  |d      |%d`   d        |%j                  d]d2      }&|%j                  d^d2      }'|&r|'r ||"jE                  |d,      |& dg|' d d!df       	 tS        |&      tS        |'      })}(tU        tI        jV                  |(|)      d<      }*tU        tI        jV                  |(|)      d<      }+tY        |*|+       dgt[        |*|+       },n<|&r ||"jE                  |d,      |& did d!df       	 tS        |&      }-tU        |-tI        jV                  djdk      z  d<      }*tU        |-tI        jV                  djdk      z  d<      }+tY        |*|+       dgt[        |*|+       },n|'r ||"jE                  |d,      |' dld d!df       	 tS        |'      }-tU        |-tI        jV                  dmdn      z  d<      }*tU        |-tI        jV                  dmdn      z  d<      }+tY        |*|+       dgt[        |*|+       },n ||"jE                  |d,      dod d!df       do},d_|%d`   jO                         v sdp|%d`   jO                         v r'dh},|&r#|'r! ||"jE                  |d,      |& dg|' d d!df        ||"jE                  |d<      |,d d!df        ||"jE                  |d(      dqd d!df        |"j^                  D ]  }.|.j`                  D ]
  }/ ||/         | d   j                  drd2      }0tQ        |      d<z   }1|jC                  |1d3*      }2|j2                  |2_         ||2jE                  dd      dsdbd&        ||2jE                  dd/      dc|0 dbd&dd       tM        g de      D ]!  \  }}# ||2jE                  d,|      |#d d&df       # tM        |      D ]  \  }$}3|$d<z   } ||2jE                  |d      |3d`   d        |3j                  dtd2      }4 ||2jE                  |d,      |4d d!df       dh},|4rtc        jd                  du|4      }5|5rtS        |5d         }-dv|4v rttU        |-tI        jV                  dwd      z  d      }*tU        |-tI        jV                  dwd      z  d      }+tg        tY        |*|+             dgtg        t[        |*|+             },nStQ        |5      d<k\  rEtS        |5d         tS        |5d,         })}(tG        tU        tI        jV                  |(|)      d,            }, ||2jE                  |d<      |,d d!df        ||2jE                  |d(      dqd d!df        |2j^                  D ]  }.|.j`                  D ]
  }/ ||/         g dx}6|jC                  d/dy*      }7|j2                  |7_         ||7jE                  dd      dzd{d&df       tM        |6      D ]$  \  }$}8 ||7jE                  d|$d,z         |8d{d&df       &  ||7jE                  dd|      d}d)d&df       d~ddddddddddx
dddddddddddx
dddddddddddx
dddddddddddx
d}9|j                  dL| d   j                  dd2            }: ||7jE                  d,d      |:d{d!df       |	j                  dBd      };|9j                  |;|9j                  d            }<tM        |      D ]  \  }$}=|=j                  dd2      }>|<r|<j                  |>      nd}?|?rtI        jV                  |?d   |?d,         d}d|v ru|ji                  d      }|jk                  d      r|dz  }|jm                  d      }@tQ        |@d,         d<k  r@d,   jo                  d<d      |@d,<   djq                  @      }nd2} ||7jE                  d,|$d,z         |d{d!df         ||7jE                  d,d|      dqd{d!df        ||7jE                  d<d|      |d{d!df       |7j^                  D ]  }.|.j`                  D ]
  }/ ||/         |j1                          |jC                  d<d(*      }A|j2                  |A_        g d}B ||AjE                  dd<      dd d!df        ||AjE                  dd/      |d d!df       tM        |B      D ]!  \  }$}C |AjE                  d,|$      |Cd d&df       # |j1                          |j1                         }Dd}E|Dj7                  |E      } Id{      |j8                  _        d'|j8                  _        |j1                         }Fd}G|Fj7                  |G      } Id)      |j8                  _        d'|j8                  _         |ddd      |j8                  jr                  _:        |sKtv        dz  }H|Hjy                  d&       |Hd|d9    dt        j                         j!                  d       dz  }|j{                  tG        |             d&tG        |      dS c c} w # t\        $ r dh},Y w xY w# t\        $ r dh},Y w xY w# t\        $ r dh},Y w xY w)z
    Generate a TongMing-format certificate DOCX from scratch.
    Uses bolt template as FORMAT reference, dynamically builds content
    for ANY product group based on standards database.
    r   )Document)PtInchesCmRGBColor)WD_ALIGN_PARAGRAPH)WD_TABLE_ALIGNMENT)qnr4   r   r   r   r   r   r   r   r   r   r   r   r   zHexagon Head BOLTSzHex NutsWasherszHex Socket Head Cap ScrewszThreaded Rods)boltnutwasherscrew
thread_rodFastener   g33333=@g      ?333333?      ?   Fc                 4   d| _         | j                  d   }|j                  t        |            }d|j                  _         |      |j                  _        ||j                  _        |dk(  rj                  |_	        y |dk(  rj                  |_	        y y )Nr    r   Courier Newcenterright)text
paragraphsadd_runr  fontr   rw   boldCENTER	alignmentRIGHT)	cellrD  rw   rH  alignr   runr.  r2  s	          r
   set_cell_fontz2render_tongming_certificate.<locals>.set_cell_font_  s~    	OOAiiD	"%4H,33AKg,22AK r   c                    | j                   }|j                         }|j                   d            }|A| j                   j                         j                   d      i       }|j	                  |       dD ]N  }|j                   d|        d      | d      d d      d d	      d
i      }|j	                  |       P y )Nzw:tcBorders)topleftbottomrC  zw:zw:valzw:sz4zw:space0zw:color000000)_tcget_or_add_tcPrfindmakeelementr&   )rL  border_typetctcPrbordersedger   r4  s          r
   set_cell_borderz4render_tongming_certificate.<locals>.set_cell_borderk  s    XX!!#))B}-.?hh..0<<R=NPRSGKK 6 	D$$R"TF_7["V*c9sByM87 B NN2	r   z!TONG  MING  ENTERPRISE  CO.,  LTD   TrA        )rowscolsz
PURCHASER:rg   r   r      z	CERT NO: r   r       zReport Code:zADDRESS:r   z39 Tran Hung Dao Street,z
CUST P/N: r   zPO# NO: r      z!Tan Thanh Ward, Tan Phu District,zHo Chi Minh City, VietnamzSIZE:rw   zMATERIAL TYPE:r   zITEM NO:i  i'  zMARKING:r   z	SHIP QTY:r   z  PCzHEAT NO:r   zMFR.:zTONG MING ENTERPRISE CO.,LTD.z
COMMODITY:r.   zLOT NO:r   z	MFR.DATE:r   zProduct Name:zSurface:r   zSampling Date: zTest Date: zSample Plan spec:zANSI/ASME B18.18r   r   r   ri   r   zDIMENSIONAL INSPECTIONS    (mm)	   zSAMPLE STANDARD:  rC  )CHARACTERISTICSSPECIFICATIONzACTUAL RESULTr    SAMPLE DETERMINANTrB  z	  <--->  PASSEDz ming)\(?gHzG?r$  gq=
ףp?g\(\?-zgo/nogo	Qualifiedr   zMECHANICAL PROPERTIESr   z[\d.]+r   ?r      zHeat Number      rl  )gy&1?gA`"?)gHzG?gzG?)gGz?rp  )gy&1?g~jt?)MbP?g{Gzt?)g@g      !@)g1@g     2@){Gz?gQ?)g      ?333333?)皙?gQ?)g;On?{Gz?)333333?      ?)rv  g?)rt  g~jth?)g      $@g      &@)g      0@g      1@)g       @g      @)g??)rw  g?)g
ףp=
?r{  )333333?gffffff?)333333?r=  )ru  rx  )Q?gQ?N)gQ?r{  )r}  r>  )r~  gQ?)g{Gz?rw  )皙?rz  )g?r=  )r|  ry  )r  ry  )rO   rN   rQ   zAlloy Steelr   rO   r   r   r  rU  )AuthorizationVerificationSamplerDatez
Xiaoyang.WzThis Lot fit the demands of EN10204 3.1
INSPECTION RESULT: SAMPLES TESTED CONFORM TO ALL OF SPECIFICATION AS ABOVE. THIS REPORT MUST NOT BE REPRODUCED EXCEPT IN FULL.zTel: +86-573-82203125  Fax: +86-573-82207588  Web: www.tongming.com.cn  E-mail: export@tongming.com.cn
ADD: 88 ChangSheng RD., E.D. Zone, JiaXing, ZheJiang, China  Zip: 314003   certificatesexist_okcert_r^   %Y%m%d_%H%M%Sz.docxsuccesspath)r?  FrR  )single)>docxr-  docx.sharedr.  r/  r0  r1  docx.enum.textr2  docx.enum.tabler3  docx.oxml.nsr4  r%   r   r   r   sections
page_widthpage_heightleft_marginright_margin
top_marginbottom_marginadd_paragraphrI  rJ  rF  rG  rw   rH  r   	underline	add_tablerL  r  _randomrandint	enumerater$   lenrl   rounduniformr   r   r  rd  cellsra   findallr{   rstripendswithsplitljustjoincolorrgbBASE_DIRmkdirsave)Lr   output_pathr-  r/  r0  r1  r3  r4   r   r   	dim_chars
mech_propschem_elementstodayrm   
prod_names	prod_namedocsectionrO  r`  r   rN  tt2	info_rowsrirow_datacir  rH  dim_sample_stdcnum_dim_rowsdthir   r   r   lohia1rJ   actualr  rowrL  mech_stdnum_mechmtr   specnumsr   ctr   CHEM_RANGESr   r   rangesel_datael_namerngpartsstsigssdisc	disc_textfooterfooter_text
output_dirr.  r2  r4  sL                                                                            @@@r
   render_tongming_certificater  5  s    4412	"Gx F/0N-():;I<(6Jj)*5MJJ((,,.*A*A**MNEj!"23E %Z&B%J
 uj1I
*C ll1oGBGT(GS'Gc7GCGsGG
3" 	A$++AK
))7
8CrFCHHMCHHM!CHHMCM
 	11%A$++AK !&&A,a6!&&A,

;8S TVWX!&&A,)FJJy",E+F GDQ!&&A,2 !&&A,
At4!&&A,

96P QSTU!&&A,*W[[-K,L MqRVW!&&A,(6::gr+B*C DaH !&&A, CQG!&&A, ;Q?
 
AA	&B%,,BL 
'++fb)+;^=O=OP_ac=dfpruv}  wF  wF  GK  MQ  wR  sS  	T	VZZ	2.

:WY@Z?[[_=`blntnxnx  zC  EG  oH  	I	1<ZY[A\^gioisist|  A  jB  	C	fjjU3_iQ[]c]g]ghqsu]vw5'	"B+eW(=rCVXjkI "), 9H * 	9GB6Q;D"''"b/348	99
 
 }-112CRHN &pqz):aeeJ>OSZ^_`f^g^m^m^oSopIpy>A%L	Lq	1B%,,BL "''!Q-!BAtL"''!Q-#5n5E!F4QXY jk <AbgganaD(;< Y' /G4Ubggb!nd6lA6 88J+88J+"''"a.XJiz*JAuV^_"x%/B7??2r2A67??2r2A62J<yR= "''"a.XJd*;QxP"(O1wtT::A>1wtT::A>2J<yR= "''"a.XJd*;QxP"(O1wtT::A>1wtT::A>2J<yR= "''"a.#q%BF d6l((**i4<;M;M;O.OFHbggb!n
)H:.NPQSXZbcbggb!nfaAbggb!nk1eXF_/Gd ww "II 	"DD!	"" &**?B?H:"H	H1	-B%,,BL"''!Q-!8!TB"''!Q-#5hZ!@!T7Sjk <AbgganaD(;< Z( G4Ubggb!nd6lA6xx,bggb!ndAuh? ::i.D$q'ND=q7??4#>>BBq7??4#>>BB #C2J0	#c"Rj/9JKFY!^"47^U47^B wr2'>!BCFbggb!nfaAbggb!nk1eXF+G. ww "II 	"DD!	"" HH	AB	'B%,,BL "''!Q-4B8$ @2bggaQ'Qh?@"''!R."64J !L l L 	
 !L l L 	
 l, dd$T
 l, ll,	
%K4 jjIj$9$=$=mR$PQG"''!Q-!UH=!!/9=H__X{y'ABF. B
7++i,%+fjj!__SVSV4S9Cczjjo<<$cSjc		#uQx=1$q31GeAhhhuoCbggaQ'aAB  "''!R.+q%B "''!R.%E8<ww "II 	"DD!	"" 
 
AA	&B%,,BL?D"''!Q-q%B"''!Q-5(;$ ;1bggamQ4:; 
 D	= 
 ,,y
!CqECHHM!CHHM F	S  ..
%CqECHHM!CHHM!#sC0CHHNN .
$' U7=+A*B!HLLND[D[\kDlCmmr#ssHHSS%566A q:  "!"  "!"  "!"sE   <|(
|(+A1|- A,|?A,}-|<;|<?}}} } c                     t        | j                        D ]*  \  }}|j                  D ]  }||j                  v s|c c S  , y)z2Find the first row index containing specific text.N)r  rd  r  rD  )tablerD  r  r  rL  s        r
   _find_row_containingr    sG    EJJ' 3II 	Dtyy 	 r   c                    ddl m} ddlm}m}m}m}m} ddlm	}  |       }	|	j                  }
d|
_         |ddd	      } |dd
dd      } |dd      } | |d       |d       |d       |d            } |ddd      } |ddd      } |ddd      } |ddd      } |ddd      }| d   }| d   }| d   }|
j                  d       d|
d<    |dd d	      |
d   _        ||
d   _        |
j                  d!       d"|
d#<   ||
d#   _        ||
d#   _        d$}d%|j                  d&d'      fd(|j                  d)d'      fd*|j                  d+d'      fd,|j                  d-d'      fd.|d/    d0|d1    fd2|j                  d3d'      fd4|j                  d5d'      fd6|j                  d7d'      fd8|j                  d9d'      fd:|j                  d;d'      fd<|j                  d=d'      fd>|j                  d?d'      fd@|j                  dAd'      fg}t!        |      D ]  \  }\  }}||z   }|
j                  dB| dC|        |
j                  dD| dE|        ||
dB| <    |ddd	      |
dB|    _        ||
dD| <   ||
dD|    _        ||
dD|    _        t%        dFdG      D ]  }||
j'                  ||H      _          |t+        |      dFz   z  }|
j                  dB| dI|        dJ|
dB| <   ||
dB|    _        ||
dB|    _        ||
dB|    _        t%        dFdK      D ]  }||
j'                  ||H      _         |dFz  }g dL}t!        |      D ]A  \  }}|
j'                  ||dFz   |M      }  |ddNd	      | _        || _        || _        || _        C |dFz  }| dO   dP   D ]  }!||
j'                  |dF|!dQ    dR|!dS    M      _        ||
j'                  |dT|!j                  dUd'      M      _        ||
j'                  |dVdWM      _        ||
j'                  |d$|!j                  dXd'      M      _        t%        dYd      D ]  }||
j'                  ||H      _         t%        dFdK      D ]2  }||
j'                  ||H      _        ||
j'                  ||H      _        4 ||
j'                  |dFH      _        |dFz  } |dFz  }|
j                  dB| dI|        dZ| d[   d\    |
dB| <   ||
dB|    _        ||
dB|    _        ||
dB|    _        t%        dFdK      D ]  }||
j'                  ||H      _         |dFz  } |ddNd	      |
j'                  |dFd]M      _         |ddNd	      |
j'                  |d$d^M      _         |ddNd	      |
j'                  |dGd_M      _         |ddNd	      |
j'                  |d d`M      _        t%        dFdK      D ]J  }||
j'                  ||H      _        ||
j'                  ||H      _        ||
j'                  ||H      _        L |
j                  dB| dC|        |
j                  dD| dE|        |
j                  da| db|        |dFz  }| d[   dc   D ];  }"||
j'                  |dF|"dQ    dR|"dS    M      _        ||
j'                  |dFH      _        ||
j'                  |d$|"j                  ddd'      M      _        ||
j'                  |dGH      _        ||
j'                  |d H      _        t%        dFdK      D ]2  }||
j'                  ||H      _        ||
j'                  ||H      _        4 ||
j'                  |dFH      _        |
j                  dB| dC|        |
j                  dD| dE|        |
j                  da| db|        |dFz  }> |dFz  }|
j                  dB| dI|        de| df   dg    |
dB| <   ||
dB|    _        ||
dB|    _        ||
dB|    _        t%        dFdK      D ]  }||
j'                  ||H      _         |dFz  } |ddNd	      |
j'                  |dFdhM      _        t!        | df   di         D ]D  \  }}#|
j'                  ||dTz   |#dj   M      }  |ddNd	      | _        || _        || _        || _        F  |ddNd	      |
j'                  |d dkM      _        ||
j'                  |d H      _        ||
j'                  |dFH      _        t%        dFdK      D ]2  }||
j'                  ||H      _        ||
j'                  ||H      _        4 |dFz  }||
j'                  |dFdlM      _        t!        | df   di         D ]o  \  }}# |ddm      |
j'                  ||dTz   |#j                  ddd'      M      _        ||
j'                  ||dTz   H      _        ||
j'                  ||dTz   H      _        q t%        dFdK      D ]  }||
j'                  ||H      _         |dFz  }||
j'                  |dF| df   j                  dnd'      M      _        ||
j'                  |dFH      _        t%        d      D ]8  }||
j'                  ||dTz   H      _        ||
j'                  ||dTz   H      _        : t%        dFdK      D ]  }||
j'                  ||H      _         |dTz  }|
j                  dB| dI|        do|
dB| <    |dd
d	      |
dB|    _        ||
dB|    _        |dTz  }g dp}$t!        |$      D ]a  \  }}%dF|d$z  z   }|
j                   ||       | dq ||dVz          |        |
j'                  |||%M      }  |ddd	      | _        || _        c g dr}&t!        |&      D ]"  \  }}'|'|
j,                   ||dFz            _        $ ds|
j0                  _        |
j4                  |
j0                  _        dF|
j0                  _        d|
j0                  _        |sKt<        dtz  }(|(j?                  du       |(dv|d&    dwtA        jB                         jE                  dx       dyz  }|	jG                  tI        |             dtI        |      dzS ){z
    Generate MECSU's own IQC (Incoming Quality Control) report as Excel.
    This is for products where NCC doesn't provide CO/CQ.
    r   )Workbook)Font	AlignmentBorderSidePatternFill)get_column_letterz
IQC ReportCalibrira  T)r   rw   rH  rs  FFFFFF)r   rw   rH  r  
   )r   rw   thin)style)rR  rC  rQ  rS  2B579Asolid)start_color	end_color	fill_typeD6E4F0FFF2CCrB  )
horizontalvertical	wrap_textrR  r4   r   r   zA1:L1u6   CÔNG TY CỔ PHẦN MECSU / MECSU JOINT STOCK COMPANYA1rq  zA2:L2u\   PHIẾU KIỂM TRA CHẤT LƯỢNG ĐẦU VÀO / INCOMING MATERIAL QUALITY INSPECTION REPORTA2rb  u   Mã Hàng / Part Number:r   r    u   Tên Hàng / Description:r"   u   Tiêu Chuẩn / Standard:r.   u   Kích Thước / Size:rw   u   Nhóm SP / Product Group:r   z / r   u   Vật Liệu / Material:r   u   Cấp Bền / Grade:r[   u   Xử Lý BM / Surface:r   u   Ký Hiệu / Marking:r   u   Số Lượng / Quantity:r   zHeat No:r   zLot No:r   u   Ngày KT / Inspection Date:r   Az:CDz:Frg   rr  )r  columnz:Lu2   KIỂM TRA KÍCH THƯỚC / DIMENSIONAL INSPECTION   )u   Đặc Tính
CharacteristicszSpec Min<--->zSpec Maxu   Mẫu 1u   Mẫu 2u   Mẫu 3u   Mẫu 4u   Mẫu 5MinMaxu   Kết Quả
Determinant)r  r  valueri  r   r   r   
r   rh  r   rf  r  r   rg  u3   TÍNH CHẤT CƠ HỌC / MECHANICAL PROPERTIES — r   r   u   Đặc Tính / Characteristicsu   Tiêu Chuẩn / Specificationu   Kết Quả / Actual Resultu   Đánh Giá / DeterminantGz:Kr   r   u4   THÀNH PHẦN HÓA HỌC / CHEMICAL COMPOSITION — r   r   zHeat Nor   r   u   Đánh GiáSpecr?  r   uY   KẾT LUẬN / CONCLUSION:    ☐ ĐẠT / QUALIFIED    ☐ KHÔNG ĐẠT / NOT QUALIFIED)u    Người Kiểm Tra
QC Inspectoru$   Người Xác Nhận
QA Verificationu   Người Phê Duyệt
Approval:)   r?  rc  r?  r?  r?  r?  r?  r?  r?  r?  rq  	landscaper  r  iqc_r^   r  z.xlsxr  )%openpyxlr  openpyxl.stylesr  r  r  r  r  openpyxl.utilsr  activetitlemerge_cellsrG  rJ  r%   r  fillrangerL  borderr  column_dimensionswidth
page_setuporientationPAPERSIZE_A4	paperSize
fitToWidthfitToHeightr  r  r   r   r   r  r  ))r   r  r  r  r  r  r  r  r  wbwsheader_fontsection_font	data_fontthin_border	blue_fill
light_fillyellow_fillrB  rR  r4   r   r   r  info_fieldsr  labelr  rcoldim_headersr  rL  r   r   r   r  sigwidthswr  s)                                            r
   generate_mecsu_iqcr+    s   
 "JJ0	B	BBH IBT:KYRd(KL)"-I v&!	K HPWXIXQXYJ(hRYZK(XNFTJD	"Gx F/0N NN7GBtHibt<BtHMBtHNN7mBtHBtHMBtH C	#W[[%CD	$gkk-&DE	$gkk*b&AB	!7;;vr#:;	$)H(I^\kMlLm&no	#^%7%7%LM	!3!3GR!@A	!6::i#<=	 &**Y";<	$fjjR&@A	VZZ	2./	FJJx,-	&

3Db(IJK  '{3 
<>E5!G
1#Rs|$
1#Rs|$Qqc7YRdCQqc7Qqc7$Qqc7&Qqc7A; 	<C0;BGG#G&-	<
< 3{aC NNQse2cU#$HB3%yM%B3%yM"B3%yM$B3%yMQ| 6,5C$)61HC=K +& "1ww3q1uAw6iad;		!" 1HC -():; V_CDL>DO;L)MNSJSC*b)ABG9BC16JSC*b)ABGB< 	<C0;BGGCG(-	<B< 	<C2=BGGCG(/5;BGGCG(2	< 04C",q 1HC NNQse2cU#$I)T`JabqJrIstB3%yM%B3%yM"B3%yM$B3%yMQ| 6,5C$)61HC OSXahiptNuBGGA%EGFKMQW`ghosMtBGGA%DGEJKOU^efmqKrBGGA%BGCHJNT]delpJqBGGB&AGBGQ| :,6C$)17C$..9C$+: NNQse2cU#$NNQse2cU#$NNQse2cU#$1HC,'5 V_CDL>DO;L)MNS/3C",OXC/2)FGL*5C"'+4C#(B< 	<C2=BGGCG(/5;BGGCG(2	< 04C",
3%r#'(
3%r#'(
3%r#'(q 1HC NNQse2cU#$J9U_K`apKqJrsB3%yM%B3%yM"B3%yM$B3%yMQ| 6,5C$)61HC 8<QRY]7^BGGAYG/49Z0<= "2ww3q1uByMwBiad;		!" =AiVW^b<cBGGBmG49'1BGGBG$&0BGGAG#Q| 8.9C$+17C$.8 1HC 5>BGGAVG,19Z0<= <2QU[dklQmCARVVOR-HIN39CA&00;CA&-< Q| :.9C$+:1HC [dBGGAYz%:%>%>}b%QGRW&1BGGAG#2Y <.9CA&+0;CA&-< Q| :.9C$+:1HC NNQse2cU#$oB3%yM92DAB3%yM$B3%yM1HC }DD/  3!a%i
+C01#a8I#PQ'8R7STWSXYZww3s#w6ibt<	  4F&! A1?@.q1u56<A !,BMM ooBMM BMM !BMM.
$' T'-*@)A8<<>CZCZ[jCkBllq#rrGGCS%566r   c                  :    t               } | j                  di       S )z+Get all product groups with their profiles.r/   r>   )r5   s    r
   get_all_groupsr-    s    \F::&++r   c                 z    t        |       }|syt        |      }||t        |      t        |      t	        |      dS )z4Get the group classification for a specific product.N)r4   r9   r   r[   material)r   r<   r?   r_   rS   )r   r4   r9   s      r
   get_group_for_productr0    sD    ;'G&w/I*95g&(1 r   c                     t        | |      S )z1Preview certificate data without generating file.)r   )r   r   s     r
   preview_certificater2    s    !+y99r   c                 \    t        | |      }d|v r|S |dk(  rt        |      S t        |      S )z
    Generate a certificate file.

    Args:
        part_number: Product part number
        output_format: 'mecsu_iqc' (Excel) or 'tongming' (DOCX)
        overrides: Dict with additional data (quantity, heat_no, lot_no, etc.)
    r   tongming)r   r  r+  )r   output_formatr   r   s       r
   generate_certificater6    s<     '{I>I)
"*955!),,r   c                     t               } t               }t        |       t        t        d | D                    i g d}| D ]  }t	        |      }||d   vrddt               d|d   |<   |d   |   dxx   dz  cc<   |d   |   d   j                  |j                  d	d
             t        |j                  d      |j                  d            }|r|d   |   dxx   dz  cc<   |dk(  s|d   j                  |j                  d      |j                  d	      |j                  d      d        |d   D ]  }t        |d   |   d         |d   |   d<   ! |S )z8Get statistics on how many products have specs coverage.c              3   @   K   | ]  }|j                  d d        yw)r#   r    N)r%   )rD   r   s     r
   rE   z%get_coverage_stats.<locals>.<genexpr>  s     #Q1AEE/2$>#Qs   )total_productstotal_categoriesgroupsunmappedr;  r   )count
with_specs
categoriesr=  rg   r?  r#   r    r.   rw   r>  r1   r<  r   r"   )r   r6   r"   )
r   r   r  setr<   addr%   r   r&   sorted)r   r5   statsr   rm   r   gs          r
   get_coverage_statsrE    sy   ~H\F h-#Q#Q QR	E  &q)h'/0QTQV%WE(OE"hw'1,'h|,001KL*155+<aeeFmL	(OE"<0A50G*$$fEE/2 uu]3& ( 8_ T+1%/!2D\2R+Sh<(T Lr   __main__zutf-8)encodingz!=== Certificate Manager Demo ===
zTotal products: r9  zTotal categories: r:  z
Groups:r;  r=  r>  d   ru   z: z products, z with specs (z.0fz%)z
=== Sample: r   z ===zDescription: r"   5000TEST123LOT001)r   r   r   r   zGroup: r   r   z (r9   )zGrade: r[   z
Material: r   z
Dimensional characteristics:r   r   r   z ~ r   zN/Ar   z
Mechanical (r   r   z):r   r   zError: rB   )r    )	mecsu_iqcN)=__doc__jsonosra   copyr   pathlibr   __file__r   r  r   r   r   r   r   r   r   r   r   r+   r<   r?   rS   r_   re   rr   r   r   r   r   r   r   r   r   randomr  r  r  r+  r-  r0  r2  r6  rE  __name__sysstdoutreconfigureprintrC  r3   rD  datapctr   samplecertr  r  r   r   r   r
   <module>r^     si    	 	   >  $ 			(3	$D0>[@ 2*4n)z a7Hj7^,:
-*#L zJJG, 

./  E	U#345
67	u%789
:;	I?((* `4<@MA<MtL!DM1C7ST1#RWk$|2D1E]SVWZR[[]^_`
 ~H!vf~.d34f]3456%fVn 7
  $GD!12?CDBtL\G]^iGjFkklmnGD!127;<=>Jt$45oFGHI24-():; 0?@}!J-AjM?;RW1V9+Rv./0 N4#5o#F"GrJK,'5 >1V9+R/(:';<=> GDM?+,/ % r   