""" 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) """ import json import os import re import copy from datetime import datetime from pathlib import Path BASE_DIR = Path(__file__).parent def load_json(filename): """Load JSON data via SQLite-backed storage (see db.py).""" from db import load_json_file return load_json_file(filename) # ─── Lazy-loaded globals ──────────────────────────────────────────────────── _config = None _products = None _specs = None def get_config(): global _config if _config is None: _config = load_json('cert_config.json') return _config def get_products(): global _products if _products is None: _products = load_json('products.json') return _products def get_specs(): global _specs if _specs is None: _specs = load_json('fastener_specs.json') return _specs def reload_all(): """Reload all config files (after edits).""" global _config, _products, _specs _config = None _products = None _specs = None # ─── Product lookup ───────────────────────────────────────────────────────── def find_product(part_number): """Find a product by part number.""" products = get_products() for p in products: if p['part'] == part_number: return p return None def search_products(query): """Search products by part number, description, or category.""" products = get_products() query_lower = query.lower() results = [] for p in products: searchable = f"{p.get('part','')} {p.get('description','')} {p.get('category_name','')}".lower() if query_lower in searchable: results.append(p) return results # ─── Group classification ─────────────────────────────────────────────────── def classify_product_group(product): """Determine the product group (BOLT/NUT/WASHER/SCREW/THREAD_ROD) from product data.""" config = get_config() category = product.get('category_name', '') # Direct mapping first mapping = config.get('category_group_mapping', {}) if category in mapping: return mapping[category] # Keyword-based fallback desc = f"{category} {product.get('description', '')} {product.get('standard', '')}".upper() for group_key, group_data in config['product_groups'].items(): for kw in group_data.get('keywords', []): if kw.upper() in desc: return group_key return 'OTHER' def get_group_profile(group_key): """Get the inspection profile for a product group.""" config = get_config() return config['product_groups'].get(group_key) # ─── Material / Grade detection ───────────────────────────────────────────── def detect_material_type(product): """Detect material type from product description/category.""" desc = f"{product.get('description', '')} {product.get('category_name', '')}".lower() if any(kw in desc for kw in ['inox', '304', '316', 'a2', 'a4', 'stainless']): if any(kw in desc for kw in ['316', 'a4']): return 'stainless', 'A4(316)' return 'stainless', 'A2(304)' return 'carbon', 'Carbon Steel' def detect_grade(product): """Detect grade from product description.""" desc = product.get('description', '') # Check for stainless grades for grade in ['A4-80', 'A4-70', 'A2-80', 'A2-70']: if grade in desc: return grade # Check for carbon grades in description for grade in ['12.9', '10.9', '8.8', '5.6', '4.8']: if grade in desc: return grade # Infer from category name cat = product.get('category_name', '') if '12.9' in cat: return '12.9' if '10.9' in cat or '10' in cat: return '10.9' if '8.8' in cat or 'Cấp Bền 8' in cat: return '8.8' if '4.8' in cat or 'Cấp Bền 4' in cat: return '4.8' # Default based on material mat_type, _ = detect_material_type(product) if mat_type == 'stainless': return 'A2-70' return '8.8' # ─── Dimensional specs lookup ─────────────────────────────────────────────── def normalize_standard(std): """Normalize standard name for matching (DIN933 → DIN 933).""" if not std: return '' # Remove year suffix std = re.sub(r'-\d{4}$', '', std.strip()) # Add space between letters and numbers std = re.sub(r'([A-Za-z])(\d)', r'\1 \2', std) return std.upper().strip() def parse_size(size_str): """Parse size string like 'M8', 'M12x35', 'M8-1.75x35' into components.""" if not size_str: return {} size_str = size_str.strip().upper() result = {} # Match M{d} or M{d}-{pitch} or M{d}x{length} or M{d}-{pitch}x{length} m = re.match(r'M(\d+(?:\.\d+)?)', size_str) if m: result['thread_diameter'] = float(m.group(1)) m = re.search(r'-(\d+(?:\.\d+)?)\s*[Xx]', size_str) if m: result['pitch'] = float(m.group(1)) m = re.search(r'[Xx]\s*(\d+(?:\.\d+)?)', size_str) if m: result['length'] = float(m.group(1)) # For washers, size is just M{d} return result def find_dimensional_specs(standard, size): """Find dimensional specifications from fastener_specs.json.""" specs = get_specs() norm_std = normalize_standard(standard) parsed_size = parse_size(size) for cat_key, cat_data in specs.items(): for std_entry in cat_data.get('standards', []): entry_std = normalize_standard(std_entry.get('standard', '')) # Match standard (e.g., "DIN 933" in "DIN 933 - 1987") if norm_std and norm_std in entry_std.replace(' ', ' '): # Find matching size for dim in std_entry.get('dimensions', []): dim_size = dim.get('size', '') # Match by thread diameter if parsed_size.get('thread_diameter'): thread_str = f"M{int(parsed_size['thread_diameter'])}" if parsed_size['thread_diameter'] == int(parsed_size['thread_diameter']) else f"M{parsed_size['thread_diameter']}" phi_str = f"Φ{int(parsed_size['thread_diameter'])}" if parsed_size['thread_diameter'] == int(parsed_size['thread_diameter']) else f"Φ{parsed_size['thread_diameter']}" thread_ref = dim.get('For Nominal Thread Diameter', '') if thread_str in dim_size or phi_str in dim_size or thread_str == thread_ref: return { 'standard': std_entry.get('standard'), 'size': dim_size, 'raw': dim, 'category': cat_key } return None # ─── Certificate data builder ─────────────────────────────────────────────── def build_certificate_data(part_number, overrides=None): """ 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 """ overrides = overrides or {} # Step 1: Find product product = find_product(part_number) if not product: return {'error': f'Product not found: {part_number}'} # Step 2: Classify group_key = classify_product_group(product) group_profile = get_group_profile(group_key) if not group_profile: return {'error': f'Unknown product group for: {part_number}'} # Step 3: Material & grade mat_type, mat_code = detect_material_type(product) grade = detect_grade(product) config = get_config() grade_spec = config['grade_specs'].get(grade, {}) chem_spec = config['chemical_specs'].get(mat_code, {}) # Step 4: Dimensional specs dim_specs = find_dimensional_specs(product.get('standard'), product.get('size')) # Step 5: Build inspection characteristics characteristics = [] for char in group_profile['dimensional_characteristics']: char_data = { 'key': char['key'], 'name': char['name'], 'name_vi': char['name_vi'], 'spec_min': '', 'spec_max': '', 'spec_nominal': '', 'actual_results': [], 'determinant': '' } # Try to fill from dimensional specs using field_mapping.json if dim_specs and dim_specs.get('raw'): raw = dim_specs['raw'] char_data = _match_dimension_to_characteristic(char, raw, char_data, product.get('standard', '')) characteristics.append(char_data) # Step 6: Build mechanical properties mech_type = mat_type # Special handling for washers if group_key == 'WASHER': desc = product.get('description', '').lower() if any(kw in desc for kw in ['vênh', 'spring', 'lock', 'din127', 'din7980']): mech_type = 'spring_washer' else: mech_type = 'flat_washer' mech_standards = group_profile.get('mechanical_standards', {}).get(mech_type, {}) mechanical_props = [] for prop in mech_standards.get('properties', []): prop_data = { 'key': prop['key'], 'name': prop['name'], 'name_vi': prop['name_vi'], 'specification': _get_mechanical_spec(prop['key'], grade_spec), 'actual_result': '', 'determinant': '' } mechanical_props.append(prop_data) # Step 7: Build chemical elements chemical_elements = [] elements = ['C', 'Si', 'Mn', 'P', 'S', 'Ni', 'Cr', 'Mo', 'Cu', 'N'] for el in elements: el_spec = chem_spec.get(el, {}) spec_str = '' if el_spec.get('min') and el_spec.get('max'): spec_str = f"{el_spec['min']:.3f} - {el_spec['max']:.3f}" elif el_spec.get('max'): spec_str = f"≤ {el_spec['max']:.3f}" elif el_spec.get('min'): spec_str = f"≥ {el_spec['min']:.3f}" chemical_elements.append({ 'element': el, 'specification': spec_str, 'actual_value': '' }) # Step 8: Assemble the certificate cert_data = { 'product': { 'part_number': part_number, 'description': product.get('description', ''), 'standard': product.get('standard', ''), 'size': product.get('size', ''), 'category': product.get('category_name', ''), 'pitch': _extract_pitch(product.get('standard', ''), dim_specs), }, 'classification': { 'group_key': group_key, 'group_name_vi': group_profile['name_vi'], 'group_name_en': group_profile['name_en'], 'material_type': mat_type, 'material_code': mat_code, 'grade': grade, }, 'header': { 'purchaser': overrides.get('purchaser', 'MECSU JOINT STOCK COMPANY'), 'address': overrides.get('address', '39 Tran Hung Dao Street, Tan Thanh Ward, Tan Phu'), 'cert_no': overrides.get('cert_no', ''), 'po_no': overrides.get('po_no', ''), 'quantity': overrides.get('quantity', ''), 'heat_no': overrides.get('heat_no', ''), 'lot_no': overrides.get('lot_no', ''), 'mfr_date': overrides.get('mfr_date', ''), 'surface': overrides.get('surface', 'Passivated' if mat_type == 'stainless' else 'Zinc Plated'), 'marking': overrides.get('marking', f'{grade}' if grade else ''), 'inspection_date': overrides.get('inspection_date', datetime.now().strftime('%Y/%m/%d')), }, 'dimensional': { 'sample_standard': product.get('standard', ''), 'characteristics': characteristics, }, 'mechanical': { 'test_standard': mech_standards.get('test_standard', ''), 'properties': mechanical_props, }, 'chemical': { 'material_name': chem_spec.get('name', mat_code), 'heat_number': overrides.get('heat_no', ''), 'elements': chemical_elements, }, 'metadata': { 'generated_at': datetime.now().isoformat(), 'template_group': group_key.lower(), 'supplier_format': overrides.get('supplier', 'MECSU'), } } return cert_data # ─── Field Mapping (bảng mapping rõ ràng từ field_mapping.json) ────────────── _field_mapping = None def get_field_mapping(): """Load bảng mapping field name → characteristic name.""" global _field_mapping if _field_mapping is None: from db import load_json_file _field_mapping = load_json_file('field_mapping.json') or {} return _field_mapping def _get_standard_mapping(standard_name): """ 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. """ fm = get_field_mapping() # Normalize: "DIN933" hoặc "DIN 933" clean = re.sub(r'\s+', '', standard_name).upper() # Tìm exact match for key in fm: if key.startswith('_'): continue if re.sub(r'\s+', '', key).upper() == clean: entry = fm[key] # Handle inheritance if '_inherit' in entry and 'mapping' not in entry: parent_key = entry['_inherit'] parent = fm.get(parent_key, {}) return parent.get('mapping', {}) return entry.get('mapping', {}) return {} def _extract_pitch(standard_name, dim_specs): """Extract pitch value from dimensional specs (stored in product info, not in dimensional inspection).""" if not dim_specs or not dim_specs.get('raw'): return '' raw = dim_specs['raw'] fm = get_field_mapping() clean = re.sub(r'\s+', '', standard_name).upper() for key in fm: if key.startswith('_'): continue if re.sub(r'\s+', '', key).upper() == clean: pitch_field = fm[key].get('pitch_field', '') if pitch_field and pitch_field in raw: return str(raw[pitch_field]) # Fallback: look for any field with "Pitch" in name for k, v in raw.items(): if 'pitch' in k.lower() and 'coarse' in k.lower(): return str(v) return '' def _match_dimension_to_characteristic(char_def, raw_dims, char_data, standard_name=''): """ 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) """ char_name = char_def['name'] mapping = _get_standard_mapping(standard_name) # Tìm mapping cho characteristic này char_mapping = mapping.get(char_name, None) if char_mapping is None: # Không có mapping → không fill (để trống) return char_data def _find_field_value(raw, field_name): """Find field value with normalization (handles e①, =nominal size, etc.)""" # Exact match first if field_name in raw: return raw[field_name] # Normalized match: strip special chars (①②③, etc.) norm_field = re.sub(r'[①②③④⑤⑥⑦⑧⑨⑩\(\)]', '', field_name).strip() for k, v in raw.items(): norm_k = re.sub(r'[①②③④⑤⑥⑦⑧⑨⑩\(\)]', '', k).strip() if norm_k == norm_field: return v return None # Đọc min value min_field = char_mapping.get('min', '') if min_field: val = _find_field_value(raw_dims, min_field) if val is not None: try: char_data['spec_min'] = str(float(str(val).replace(',', '.'))) except (ValueError, TypeError): pass # Đọc max value max_field = char_mapping.get('max', '') if max_field: val = _find_field_value(raw_dims, max_field) if val is not None: try: char_data['spec_max'] = str(float(str(val).replace(',', '.'))) except (ValueError, TypeError): pass return char_data def _get_mechanical_spec(prop_key, grade_spec): """Get mechanical property specification string from grade spec.""" if not grade_spec: return '' mappings = { 'tensile_strength': ('tensile_strength_min', '{} min'), 'yield_strength': ('yield_strength_min', '{} min'), 'elongation': ('elongation_min_pct', '{} min'), 'hardness': ('hardness_min', None), 'proof_load': ('proof_load_stress', '{} min'), 'free_height': (None, ''), } if prop_key not in mappings: return '' field_key, fmt = mappings[prop_key] if not field_key: return '' val = grade_spec.get(field_key) if val is None: # Try elongation_min for stainless if prop_key == 'elongation': val = grade_spec.get('elongation_min') if val: return f'{val}d min' return '' if prop_key == 'hardness': h_min = grade_spec.get('hardness_min', '') h_max = grade_spec.get('hardness_max', '') if h_min and h_max: return f'{h_min} - {h_max}' if h_max: return f'{h_max} max' return str(h_min) if fmt: return fmt.format(val) return str(val) # ─── DOCX Certificate Generator (TongMing format) ────────────────────────── # # Concept: Chỉ cần 1 mẫu bolt TongMing → học FORMAT → sinh cert cho BẤT KỲ sản phẩm # # Format TongMing gồm: # - PHẦN CHUNG: Header (Company, Purchaser, Address), Product Info (Size, Material, # Marking, Qty, Heat No, Lot No, MFR Date, Surface, Commodity, Product Name) # → Mã nào cũng có, chỉ thay nội dung # - PHẦN RIÊNG: Dimensional Inspections → rows thay đổi theo nhóm SP # Bolt: Width Across Flats, Across Corner, Head Height, Nominal Length, Major Dia... # Nut: Width Across Flats, Across Corner, Thickness, Minor Diameter, Pitch Dia... # Washer: Inner Diameter, Outer Diameter, Thickness, Height... # Screw: Head Diameter, Head Height, Drive Depth, Drive Width, Major Dia... # - Mechanical Properties → thay đổi theo group + grade # - Chemical Composition → thay đổi theo material # - Signature + Footer → giống nhau import random as _random def render_tongming_certificate(cert_data, output_path=None): """ 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. """ from docx import Document from docx.shared import Pt, Inches, Cm, RGBColor from docx.enum.text import WD_ALIGN_PARAGRAPH from docx.enum.table import WD_TABLE_ALIGNMENT from docx.oxml.ns import qn product = cert_data['product'] header = cert_data['header'] classification = cert_data['classification'] dim_chars = cert_data['dimensional']['characteristics'] mech_props = cert_data['mechanical']['properties'] chem_elements = cert_data['chemical']['elements'] today = header.get('inspection_date', datetime.now().strftime('%Y/%m/%d')) group = cert_data['metadata']['template_group'] # Product name based on group prod_names = { 'bolt': 'Hexagon Head BOLTS', 'nut': 'Hex Nuts', 'washer': 'Washers', 'screw': 'Hex Socket Head Cap Screws', 'thread_rod': 'Threaded Rods' } prod_name = prod_names.get(group, 'Fastener') doc = Document() # Page setup section = doc.sections[0] section.page_width = Cm(21) section.page_height = Cm(29.7) section.left_margin = Cm(1.5) section.right_margin = Cm(1.5) section.top_margin = Cm(1.2) section.bottom_margin = Cm(1.0) # Font helper def set_cell_font(cell, text, size=8, bold=False, align='left'): cell.text = '' p = cell.paragraphs[0] run = p.add_run(str(text)) run.font.name = 'Courier New' run.font.size = Pt(size) run.font.bold = bold if align == 'center': p.alignment = WD_ALIGN_PARAGRAPH.CENTER elif align == 'right': p.alignment = WD_ALIGN_PARAGRAPH.RIGHT def set_cell_border(cell, border_type='single'): tc = cell._tc tcPr = tc.get_or_add_tcPr() borders = tcPr.find(qn('w:tcBorders')) if borders is None: borders = cell._tc.get_or_add_tcPr().makeelement(qn('w:tcBorders'), {}) tcPr.append(borders) for edge in ('top', 'left', 'bottom', 'right'): el = borders.makeelement(qn(f'w:{edge}'), { qn('w:val'): border_type, qn('w:sz'): '4', qn('w:space'): '0', qn('w:color'): '000000' }) borders.append(el) # ═══════════════════════════════════════════════════════════════ # COMPANY HEADER # ═══════════════════════════════════════════════════════════════ p = doc.add_paragraph() p.alignment = WD_ALIGN_PARAGRAPH.CENTER run = p.add_run('TONG MING ENTERPRISE CO., LTD') run.font.size = Pt(14) run.font.bold = True run.font.name = 'Courier New' run.underline = True # ═══════════════════════════════════════════════════════════════ # HEADER INFO TABLE (phần CHUNG - mã nào cũng giống format) # ═══════════════════════════════════════════════════════════════ t = doc.add_table(rows=4, cols=6) t.alignment = WD_TABLE_ALIGNMENT.CENTER # Row 0: Purchaser + Cert No set_cell_font(t.cell(0, 0), 'PURCHASER:', 8, True) set_cell_font(t.cell(0, 1), header.get('purchaser', 'MECSU JOINT STOCK COMPANY'), 8) set_cell_font(t.cell(0, 3), f"CERT NO: {header.get('cert_no', '')}", 8, True) set_cell_font(t.cell(0, 5), 'Report Code:', 8) # Row 1: Address + CUST P/N set_cell_font(t.cell(1, 0), 'ADDRESS:', 8, True) set_cell_font(t.cell(1, 1), header.get('address', '39 Tran Hung Dao Street,'), 8) set_cell_font(t.cell(1, 3), f"CUST P/N: {product.get('part_number', '')}", 8, True) set_cell_font(t.cell(1, 5), f"PO# NO: {header.get('po_no', '')}", 8) # Row 2-3: Address cont + empty set_cell_font(t.cell(2, 1), 'Tan Thanh Ward, Tan Phu District,', 8) set_cell_font(t.cell(3, 1), 'Ho Chi Minh City, Vietnam', 8) doc.add_paragraph() # spacing # ═══════════════════════════════════════════════════════════════ # PRODUCT INFO TABLE (phần CHUNG) # ═══════════════════════════════════════════════════════════════ t2 = doc.add_table(rows=6, cols=6) t2.alignment = WD_TABLE_ALIGNMENT.CENTER info_rows = [ ('SIZE:', product.get('size', ''), 'MATERIAL TYPE:', classification.get('material_code', ''), 'ITEM NO:', str(_random.randint(1000, 9999))), ('MARKING:', header.get('marking', ''), 'SHIP QTY:', f"{header.get('quantity', '')} PC", 'HEAT NO:', header.get('heat_no', '')), ('MFR.:', 'TONG MING ENTERPRISE CO.,LTD.', 'COMMODITY:', product.get('standard', ''), 'LOT NO:', header.get('lot_no', '')), ('MFR.DATE:', header.get('mfr_date', today), 'Product Name:', prod_name, 'Surface:', header.get('surface', '')), (f'Sampling Date: {today}', '', f'Test Date: {today}', '', 'Sample Plan spec:', 'ANSI/ASME B18.18'), ] for ri, row_data in enumerate(info_rows): for ci, val in enumerate(row_data): bold = ci % 2 == 0 # Labels bold set_cell_font(t2.cell(ri, ci), val, 8, bold) doc.add_paragraph() # spacing # ═══════════════════════════════════════════════════════════════ # DIMENSIONAL INSPECTIONS (phần RIÊNG - thay đổi theo sản phẩm!) # ═══════════════════════════════════════════════════════════════ dim_sample_std = cert_data['dimensional'].get('sample_standard', '') # Filter: skip characteristics with NO specs (e.g., Flange Diameter for non-flange nuts) # BUT keep Pitch Diameter (always show as PASSED even without specs) dim_chars = [c for c in dim_chars if c.get('spec_min') or c.get('spec_max') or 'pitch' in c['name'].lower()] num_dim_rows = len(dim_chars) + 2 # header + subheader + data rows dt = doc.add_table(rows=num_dim_rows, cols=5) dt.alignment = WD_TABLE_ALIGNMENT.CENTER # Header row set_cell_font(dt.cell(0, 0), 'DIMENSIONAL INSPECTIONS (mm)', 9, True) set_cell_font(dt.cell(0, 3), f'SAMPLE STANDARD: {dim_sample_std}', 9, True, 'right') # Sub-header for ci, h in enumerate(['CHARACTERISTICS', 'SPECIFICATION', 'ACTUAL RESULT', '', 'SAMPLE DETERMINANT']): set_cell_font(dt.cell(1, ci), h, 8, True, 'center') # Data rows - DYNAMIC based on product group's characteristics for i, char in enumerate(dim_chars): ri = i + 2 set_cell_font(dt.cell(ri, 0), char['name'], 8) # Specification spec_min = char.get('spec_min', '') spec_max = char.get('spec_max', '') if spec_min and spec_max: set_cell_font(dt.cell(ri, 1), f'{spec_min} <---> {spec_max}', 8, False, 'center') # Generate random actual result in range try: lo, hi = float(spec_min), float(spec_max) a1 = round(_random.uniform(lo, hi), 2) a2 = round(_random.uniform(lo, hi), 2) actual = f'{min(a1,a2)} <---> {max(a1,a2)}' except ValueError: actual = 'PASSED' elif spec_min: set_cell_font(dt.cell(ri, 1), f'{spec_min} min', 8, False, 'center') try: v = float(spec_min) a1 = round(v * _random.uniform(1.01, 1.08), 2) a2 = round(v * _random.uniform(1.01, 1.08), 2) actual = f'{min(a1,a2)} <---> {max(a1,a2)}' except ValueError: actual = 'PASSED' elif spec_max: set_cell_font(dt.cell(ri, 1), f'{spec_max} max', 8, False, 'center') try: v = float(spec_max) a1 = round(v * _random.uniform(0.92, 0.98), 2) a2 = round(v * _random.uniform(0.92, 0.98), 2) actual = f'{min(a1,a2)} <---> {max(a1,a2)}' except ValueError: actual = 'PASSED' else: set_cell_font(dt.cell(ri, 1), '-', 8, False, 'center') actual = '-' # Pitch Diameter = GO/NOGO gauge check → always PASSED, no min-max range if 'pitch' in char['name'].lower() or 'go/nogo' in char['name'].lower(): actual = 'PASSED' # Override spec: show as range if available, but actual is always PASSED if spec_min and spec_max: set_cell_font(dt.cell(ri, 1), f'{spec_min} <---> {spec_max}', 8, False, 'center') set_cell_font(dt.cell(ri, 2), actual, 8, False, 'center') set_cell_font(dt.cell(ri, 4), 'Qualified', 8, False, 'center') # Borders for dimensional table for row in dt.rows: for cell in row.cells: set_cell_border(cell) # ═══════════════════════════════════════════════════════════════ # MECHANICAL PROPERTIES (phần RIÊNG - thay đổi theo group + grade) # ═══════════════════════════════════════════════════════════════ mech_std = cert_data['mechanical'].get('test_standard', '') num_mech = len(mech_props) + 2 mt = doc.add_table(rows=num_mech, cols=5) mt.alignment = WD_TABLE_ALIGNMENT.CENTER set_cell_font(mt.cell(0, 0), 'MECHANICAL PROPERTIES', 9, True) set_cell_font(mt.cell(0, 3), f'SAMPLE STANDARD: {mech_std}', 9, True, 'right') for ci, h in enumerate(['CHARACTERISTICS', 'SPECIFICATION', 'ACTUAL RESULT', '', 'SAMPLE DETERMINANT']): set_cell_font(mt.cell(1, ci), h, 8, True, 'center') for i, prop in enumerate(mech_props): ri = i + 2 set_cell_font(mt.cell(ri, 0), prop['name'], 8) spec = prop.get('specification', '') set_cell_font(mt.cell(ri, 1), spec, 8, False, 'center') # Generate actual result actual = 'PASSED' if spec: nums = re.findall(r'[\d.]+', spec) if nums: v = float(nums[0]) if 'min' in spec: a1 = round(v * _random.uniform(1.05, 1.20), 0) a2 = round(v * _random.uniform(1.05, 1.20), 0) actual = f'{int(min(a1,a2))} <---> {int(max(a1,a2))}' elif len(nums) >= 2: lo, hi = float(nums[0]), float(nums[1]) actual = str(round(_random.uniform(lo, hi), 1)) set_cell_font(mt.cell(ri, 2), actual, 8, False, 'center') set_cell_font(mt.cell(ri, 4), 'Qualified', 8, False, 'center') for row in mt.rows: for cell in row.cells: set_cell_border(cell) # ═══════════════════════════════════════════════════════════════ # CHEMICAL COMPOSITION (phần chung format, nội dung thay đổi theo material) # ═══════════════════════════════════════════════════════════════ elements = ['C', 'Si', 'Mn', 'P', 'S', 'Ni', 'Cr', 'Mo', 'Cu', 'N'] ct = doc.add_table(rows=3, cols=12) ct.alignment = WD_TABLE_ALIGNMENT.CENTER # Header set_cell_font(ct.cell(0, 0), 'Heat Number', 7, True, 'center') for i, el in enumerate(elements): set_cell_font(ct.cell(0, i + 1), el, 7, True, 'center') set_cell_font(ct.cell(0, 11), 'SAMPLE DETERMINANT', 6, True, 'center') # Values - Random hợp lý dựa trên dữ liệu thực từ CQ TongMing # Range lấy từ 4 mẫu CQ thực: template_bolt (A4/316), template_nut/washer/screw (A2/304) CHEM_RANGES = { 'A2(304)': { 'C': (0.014, 0.053), 'Si': (0.27, 0.42), 'Mn': (0.94, 1.05), 'P': (0.028, 0.038), 'S': (0.001, 0.005), 'Ni': (7.95, 8.50), 'Cr': (17.80, 18.50), 'Mo': (0.02, 0.12), 'Cu': (0.25, 0.85), 'N': (0.025, 0.070), }, 'A4(316)': { 'C': (0.018, 0.040), 'Si': (0.30, 0.50), 'Mn': (0.85, 1.10), 'P': (0.028, 0.038), 'S': (0.001, 0.003), 'Ni': (10.0, 11.0), 'Cr': (16.00, 17.00), 'Mo': (2.00, 2.50), 'Cu': (0.20, 0.45), 'N': (0.025, 0.050), }, 'Carbon Steel': { 'C': (0.18, 0.45), 'Si': (0.15, 0.35), 'Mn': (0.60, 1.20), 'P': (0.020, 0.040), 'S': (0.015, 0.035), 'Ni': None, 'Cr': None, 'Mo': None, 'Cu': None, 'N': None, }, 'Alloy Steel': { 'C': (0.28, 0.45), 'Si': (0.15, 0.35), 'Mn': (0.60, 1.00), 'P': (0.015, 0.030), 'S': (0.010, 0.025), 'Ni': (0.10, 0.50), 'Cr': (0.80, 1.20), 'Mo': (0.15, 0.30), 'Cu': (0.10, 0.30), 'N': None, }, } heat_no = header.get('heat_no', cert_data['chemical'].get('heat_number', '')) set_cell_font(ct.cell(1, 0), heat_no, 7, False, 'center') mat_code = classification.get('material_code', 'A2(304)') ranges = CHEM_RANGES.get(mat_code, CHEM_RANGES.get('A2(304)')) for i, el_data in enumerate(chem_elements): el_name = el_data.get('element', '') rng = ranges.get(el_name) if ranges else None if rng: val = f'{_random.uniform(rng[0], rng[1]):.3f}' # Trim trailing zeros: 0.030 → 0.03, but keep at least 2 decimals if '.' in val: val = val.rstrip('0') if val.endswith('.'): val += '0' # Ensure at least 2 decimal places for consistency parts = val.split('.') if len(parts[1]) < 2: parts[1] = parts[1].ljust(2, '0') val = '.'.join(parts) else: val = '' set_cell_font(ct.cell(1, i + 1), val, 7, False, 'center') set_cell_font(ct.cell(1, 11), 'Qualified', 7, False, 'center') # Signature line in chemical table set_cell_font(ct.cell(2, 11), today, 7, False, 'center') for row in ct.rows: for cell in row.cells: set_cell_border(cell) doc.add_paragraph() # ═══════════════════════════════════════════════════════════════ # SIGNATURE TABLE (phần CHUNG) # ═══════════════════════════════════════════════════════════════ st = doc.add_table(rows=2, cols=4) st.alignment = WD_TABLE_ALIGNMENT.CENTER sigs = ['Authorization', 'Verification', 'Sampler', 'Date'] set_cell_font(st.cell(0, 2), 'Xiaoyang.W', 8, False, 'center') set_cell_font(st.cell(0, 3), today, 8, False, 'center') for i, s in enumerate(sigs): set_cell_font(st.cell(1, i), s, 8, True, 'center') doc.add_paragraph() # ═══════════════════════════════════════════════════════════════ # DISCLAIMER + FOOTER (phần CHUNG) # ═══════════════════════════════════════════════════════════════ disc = doc.add_paragraph() disc_text = ( 'This Lot fit the demands of EN10204 3.1\n' 'INSPECTION RESULT: SAMPLES TESTED CONFORM TO ALL OF SPECIFICATION AS ABOVE. ' 'THIS REPORT MUST NOT BE REPRODUCED EXCEPT IN FULL.' ) run = disc.add_run(disc_text) run.font.size = Pt(7) run.font.name = 'Courier New' footer = doc.add_paragraph() footer_text = ( 'Tel: +86-573-82203125 Fax: +86-573-82207588 Web: www.tongming.com.cn E-mail: export@tongming.com.cn\n' 'ADD: 88 ChangSheng RD., E.D. Zone, JiaXing, ZheJiang, China Zip: 314003' ) run = footer.add_run(footer_text) run.font.size = Pt(6) run.font.name = 'Courier New' run.font.color.rgb = RGBColor(128, 128, 128) # Save if not output_path: output_dir = BASE_DIR / 'certificates' output_dir.mkdir(exist_ok=True) output_path = output_dir / f"cert_{product['part_number']}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.docx" doc.save(str(output_path)) return {'success': True, 'path': str(output_path)} def _find_row_containing(table, text): """Find the first row index containing specific text.""" for i, row in enumerate(table.rows): for cell in row.cells: if text in cell.text: return i return None # ─── MECSU IQC Report generation ──────────────────────────────────────────── def generate_mecsu_iqc(cert_data, output_path=None): """ Generate MECSU's own IQC (Incoming Quality Control) report as Excel. This is for products where NCC doesn't provide CO/CQ. """ from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.utils import get_column_letter wb = Workbook() ws = wb.active ws.title = "IQC Report" # Styles header_font = Font(name='Calibri', size=14, bold=True) section_font = Font(name='Calibri', size=11, bold=True, color='FFFFFF') data_font = Font(name='Calibri', size=10) thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) blue_fill = PatternFill(start_color='2B579A', end_color='2B579A', fill_type='solid') light_fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid') yellow_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid') center = Alignment(horizontal='center', vertical='center', wrap_text=True) left = Alignment(horizontal='left', vertical='center', wrap_text=True) product = cert_data['product'] header = cert_data['header'] classification = cert_data['classification'] # === HEADER === ws.merge_cells('A1:L1') ws['A1'] = 'CÔNG TY CỔ PHẦN MECSU / MECSU JOINT STOCK COMPANY' ws['A1'].font = Font(name='Calibri', size=12, bold=True) ws['A1'].alignment = center ws.merge_cells('A2:L2') ws['A2'] = 'PHIẾU KIỂM TRA CHẤT LƯỢNG ĐẦU VÀO / INCOMING MATERIAL QUALITY INSPECTION REPORT' ws['A2'].font = header_font ws['A2'].alignment = center # === SECTION 1: PRODUCT INFO === row = 4 info_fields = [ ('Mã Hàng / Part Number:', product.get('part_number', '')), ('Tên Hàng / Description:', product.get('description', '')), ('Tiêu Chuẩn / Standard:', product.get('standard', '')), ('Kích Thước / Size:', product.get('size', '')), ('Nhóm SP / Product Group:', f"{classification['group_name_vi']} / {classification['group_name_en']}"), ('Vật Liệu / Material:', classification.get('material_code', '')), ('Cấp Bền / Grade:', classification.get('grade', '')), ('Xử Lý BM / Surface:', header.get('surface', '')), ('Ký Hiệu / Marking:', header.get('marking', '')), ('Số Lượng / Quantity:', header.get('quantity', '')), ('Heat No:', header.get('heat_no', '')), ('Lot No:', header.get('lot_no', '')), ('Ngày KT / Inspection Date:', header.get('inspection_date', '')), ] for i, (label, value) in enumerate(info_fields): r = row + i ws.merge_cells(f'A{r}:C{r}') ws.merge_cells(f'D{r}:F{r}') ws[f'A{r}'] = label ws[f'A{r}'].font = Font(name='Calibri', size=10, bold=True) ws[f'D{r}'] = value ws[f'D{r}'].font = data_font ws[f'D{r}'].fill = yellow_fill for col in range(1, 7): ws.cell(row=r, column=col).border = thin_border row += len(info_fields) + 1 # === SECTION 2: DIMENSIONAL INSPECTION === ws.merge_cells(f'A{row}:L{row}') ws[f'A{row}'] = 'KIỂM TRA KÍCH THƯỚC / DIMENSIONAL INSPECTION' ws[f'A{row}'].font = section_font ws[f'A{row}'].fill = blue_fill ws[f'A{row}'].alignment = center for col in range(1, 13): ws.cell(row=row, column=col).fill = blue_fill row += 1 # Dim header dim_headers = ['Đặc Tính\nCharacteristics', 'Spec Min', '<--->', 'Spec Max', 'Mẫu 1', 'Mẫu 2', 'Mẫu 3', 'Mẫu 4', 'Mẫu 5', 'Min', 'Max', 'Kết Quả\nDeterminant'] for i, h in enumerate(dim_headers): cell = ws.cell(row=row, column=i + 1, value=h) cell.font = Font(name='Calibri', size=9, bold=True) cell.fill = light_fill cell.alignment = center cell.border = thin_border row += 1 # Dim data for char in cert_data['dimensional']['characteristics']: ws.cell(row=row, column=1, value=f"{char['name']}\n{char['name_vi']}").font = data_font ws.cell(row=row, column=2, value=char.get('spec_min', '')).font = data_font ws.cell(row=row, column=3, value='<--->').font = data_font ws.cell(row=row, column=4, value=char.get('spec_max', '')).font = data_font for col in range(5, 10): ws.cell(row=row, column=col).fill = yellow_fill for col in range(1, 13): ws.cell(row=row, column=col).border = thin_border ws.cell(row=row, column=col).alignment = center ws.cell(row=row, column=1).alignment = left row += 1 row += 1 # === SECTION 3: MECHANICAL PROPERTIES === ws.merge_cells(f'A{row}:L{row}') ws[f'A{row}'] = f"TÍNH CHẤT CƠ HỌC / MECHANICAL PROPERTIES — {cert_data['mechanical']['test_standard']}" ws[f'A{row}'].font = section_font ws[f'A{row}'].fill = blue_fill ws[f'A{row}'].alignment = center for col in range(1, 13): ws.cell(row=row, column=col).fill = blue_fill row += 1 # Mech header - set values BEFORE merging ws.cell(row=row, column=1, value='Đặc Tính / Characteristics').font = Font(name='Calibri', size=9, bold=True) ws.cell(row=row, column=4, value='Tiêu Chuẩn / Specification').font = Font(name='Calibri', size=9, bold=True) ws.cell(row=row, column=7, value='Kết Quả / Actual Result').font = Font(name='Calibri', size=9, bold=True) ws.cell(row=row, column=12, value='Đánh Giá / Determinant').font = Font(name='Calibri', size=9, bold=True) for col in range(1, 13): ws.cell(row=row, column=col).fill = light_fill ws.cell(row=row, column=col).alignment = center ws.cell(row=row, column=col).border = thin_border ws.merge_cells(f'A{row}:C{row}') ws.merge_cells(f'D{row}:F{row}') ws.merge_cells(f'G{row}:K{row}') row += 1 for prop in cert_data['mechanical']['properties']: ws.cell(row=row, column=1, value=f"{prop['name']}\n{prop['name_vi']}").font = data_font ws.cell(row=row, column=1).alignment = left ws.cell(row=row, column=4, value=prop.get('specification', '')).font = data_font ws.cell(row=row, column=7).fill = yellow_fill ws.cell(row=row, column=12).font = data_font for col in range(1, 13): ws.cell(row=row, column=col).border = thin_border ws.cell(row=row, column=col).alignment = center ws.cell(row=row, column=1).alignment = left ws.merge_cells(f'A{row}:C{row}') ws.merge_cells(f'D{row}:F{row}') ws.merge_cells(f'G{row}:K{row}') row += 1 row += 1 # === SECTION 4: CHEMICAL COMPOSITION === ws.merge_cells(f'A{row}:L{row}') ws[f'A{row}'] = f"THÀNH PHẦN HÓA HỌC / CHEMICAL COMPOSITION — {cert_data['chemical']['material_name']}" ws[f'A{row}'].font = section_font ws[f'A{row}'].fill = blue_fill ws[f'A{row}'].alignment = center for col in range(1, 13): ws.cell(row=row, column=col).fill = blue_fill row += 1 # Element headers ws.cell(row=row, column=1, value='Heat No').font = Font(name='Calibri', size=9, bold=True) for i, el in enumerate(cert_data['chemical']['elements']): cell = ws.cell(row=row, column=i + 2, value=el['element']) cell.font = Font(name='Calibri', size=9, bold=True) cell.fill = light_fill cell.alignment = center cell.border = thin_border ws.cell(row=row, column=12, value='Đánh Giá').font = Font(name='Calibri', size=9, bold=True) ws.cell(row=row, column=12).fill = light_fill ws.cell(row=row, column=1).fill = light_fill for col in range(1, 13): ws.cell(row=row, column=col).border = thin_border ws.cell(row=row, column=col).alignment = center row += 1 # Spec row ws.cell(row=row, column=1, value='Spec').font = data_font for i, el in enumerate(cert_data['chemical']['elements']): ws.cell(row=row, column=i + 2, value=el.get('specification', '')).font = Font(name='Calibri', size=8) ws.cell(row=row, column=i + 2).alignment = center ws.cell(row=row, column=i + 2).border = thin_border for col in range(1, 13): ws.cell(row=row, column=col).border = thin_border row += 1 # Actual row ws.cell(row=row, column=1, value=cert_data['chemical'].get('heat_number', '')).font = data_font ws.cell(row=row, column=1).fill = yellow_fill for i in range(10): ws.cell(row=row, column=i + 2).fill = yellow_fill ws.cell(row=row, column=i + 2).border = thin_border for col in range(1, 13): ws.cell(row=row, column=col).border = thin_border row += 2 # === CONCLUSION === ws.merge_cells(f'A{row}:L{row}') ws[f'A{row}'] = 'KẾT LUẬN / CONCLUSION: ☐ ĐẠT / QUALIFIED ☐ KHÔNG ĐẠT / NOT QUALIFIED' ws[f'A{row}'].font = Font(name='Calibri', size=11, bold=True) ws[f'A{row}'].alignment = center row += 2 # Signatures sigs = ['Người Kiểm Tra\nQC Inspector', 'Người Xác Nhận\nQA Verification', 'Người Phê Duyệt\nApproval'] for i, sig in enumerate(sigs): col = 1 + i * 4 ws.merge_cells(f'{get_column_letter(col)}{row}:{get_column_letter(col + 3)}{row}') cell = ws.cell(row=row, column=col, value=sig) cell.font = Font(name='Calibri', size=10, bold=True) cell.alignment = center # Column widths widths = [18, 8, 6, 8, 8, 8, 8, 8, 8, 8, 8, 12] for i, w in enumerate(widths): ws.column_dimensions[get_column_letter(i + 1)].width = w # Page setup ws.page_setup.orientation = 'landscape' ws.page_setup.paperSize = ws.PAPERSIZE_A4 ws.page_setup.fitToWidth = 1 ws.page_setup.fitToHeight = 0 if not output_path: output_dir = BASE_DIR / 'certificates' output_dir.mkdir(exist_ok=True) output_path = output_dir / f"iqc_{product['part_number']}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx" wb.save(str(output_path)) return {'success': True, 'path': str(output_path)} # ─── API functions for web interface ──────────────────────────────────────── def get_all_groups(): """Get all product groups with their profiles.""" config = get_config() return config.get('product_groups', {}) def get_group_for_product(part_number): """Get the group classification for a specific product.""" product = find_product(part_number) if not product: return None group_key = classify_product_group(product) return { 'product': product, 'group_key': group_key, 'group_profile': get_group_profile(group_key), 'grade': detect_grade(product), 'material': detect_material_type(product), } def preview_certificate(part_number, overrides=None): """Preview certificate data without generating file.""" return build_certificate_data(part_number, overrides) def generate_certificate(part_number, output_format='mecsu_iqc', overrides=None): """ 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.) """ cert_data = build_certificate_data(part_number, overrides) if 'error' in cert_data: return cert_data if output_format == 'tongming': return render_tongming_certificate(cert_data) else: return generate_mecsu_iqc(cert_data) # ─── Stats ────────────────────────────────────────────────────────────────── def get_coverage_stats(): """Get statistics on how many products have specs coverage.""" products = get_products() config = get_config() stats = { 'total_products': len(products), 'total_categories': len(set(p.get('category_name', '') for p in products)), 'groups': {}, 'unmapped': [] } for p in products: group = classify_product_group(p) if group not in stats['groups']: stats['groups'][group] = {'count': 0, 'with_specs': 0, 'categories': set()} stats['groups'][group]['count'] += 1 stats['groups'][group]['categories'].add(p.get('category_name', '')) dim_specs = find_dimensional_specs(p.get('standard'), p.get('size')) if dim_specs: stats['groups'][group]['with_specs'] += 1 if group == 'OTHER': stats['unmapped'].append({ 'part': p.get('part'), 'category': p.get('category_name'), 'description': p.get('description') }) # Convert sets to lists for JSON serialization for g in stats['groups']: stats['groups'][g]['categories'] = sorted(stats['groups'][g]['categories']) return stats if __name__ == '__main__': import sys sys.stdout.reconfigure(encoding='utf-8') # Demo: preview certificate for a product print("=== Certificate Manager Demo ===\n") # Show coverage stats stats = get_coverage_stats() print(f"Total products: {stats['total_products']}") print(f"Total categories: {stats['total_categories']}") print(f"\nGroups:") for g, data in stats['groups'].items(): pct = (data['with_specs'] / data['count'] * 100) if data['count'] > 0 else 0 print(f" {g}: {data['count']} products, {data['with_specs']} with specs ({pct:.0f}%)") # Demo: build cert data for a sample product products = get_products() if products: sample = products[0] print(f"\n=== Sample: {sample['part']} ===") print(f"Description: {sample['description']}") cert = build_certificate_data(sample['part'], { 'quantity': '5000', 'heat_no': 'TEST123', 'lot_no': 'LOT001', }) if 'error' not in cert: print(f"Group: {cert['classification']['group_name_vi']} ({cert['classification']['group_key']})") print(f"Grade: {cert['classification']['grade']}") print(f"Material: {cert['classification']['material_code']}") print(f"\nDimensional characteristics:") for c in cert['dimensional']['characteristics']: spec = f"{c['spec_min']} ~ {c['spec_max']}" if c['spec_min'] else 'N/A' print(f" {c['name']}: {spec}") print(f"\nMechanical ({cert['mechanical']['test_standard']}):") for p in cert['mechanical']['properties']: print(f" {p['name']}: {p['specification']}") else: print(f"Error: {cert['error']}")