import openpyxl from openpyxl.styles import Font, Alignment, PatternFill, Border, Side from openpyxl.utils import get_column_letter from datetime import datetime, date import re import os import sys sys.stdout.reconfigure(encoding='utf-8') class DataCleanerAndMergerT37: def __init__(self): # Comprehensive technician mapping dictionary self.kythuat_map = { 'trần dũng': 'Trần Dũng', 'trần dung': 'Trần Dũng', 'trần thanh dung': 'Trần Thanh Dũng', 'trần thanh dũng': 'Trần Thanh Dũng', 'nguyễn văn tuân': 'Nguyễn Văn Tuân', 'nguyễn văn tuấn': 'Nguyễn Văn Tuấn', 'nguyên hưu lợi': 'Nguyễn Hữu Lợi', 'nguyên hữu lợi': 'Nguyễn Hữu Lợi', 'nguyễn hưu lợi': 'Nguyễn Hữu Lợi', 'nguyễn hữu lợi': 'Nguyễn Hữu Lợi', 'nguyễn hưu lơi': 'Nguyễn Hữu Lợi', 'nguyên hưu lơi': 'Nguyễn Hữu Lợi', 'lợi': 'Nguyễn Hữu Lợi', 'kt cắm tại nha máy làm': 'Nguyễn Hữu Lợi', 'nông đưc trường': 'Nông Đức Trường', 'nông đức trường': 'Nông Đức Trường', 'đỗ quang thuỷ': 'Đỗ Quang Thủy', 'đỗ quang thủy': 'Đỗ Quang Thủy', 'đõ quang thuỷ': 'Đỗ Quang Thủy', 'nguyễn văn tùng': 'Nguyễn Văn Tùng', 'đoàn thanh nhân': 'Đoàn Thanh Nhân', 'đoàn thang nhân': 'Đoàn Thanh Nhân', 'dương tấn hiền': 'Dương Tấn Hiền', 'dươg tấn hiền': 'Dương Tấn Hiền', 'trịnh hữu hải': 'Trịnh Hữu Hải', 'trinh hữu hải': 'Trịnh Hữu Hải', 'hải': 'Trịnh Hữu Hải', 'phan văn hiếu': 'Phan Văn Hiếu', 'lê văn hoa': 'Lê Văn Hoa', 'lê văn hòa': 'Lê Văn Hòa', 'lê văn hoà': 'Lê Văn Hòa', 'bùi văn tuấn': 'Bùi Văn Tuấn', 'phạm thanh hoàn': 'Phạm Thanh Hoàn', 'lê xuân tương': 'Lê Xuân Tương', 'lê xuân tuong': 'Lê Xuân Tương', 'lê xuan tương': 'Lê Xuân Tương', 'hồ tiến sĩ': 'Hồ Tiến Sĩ', 'hồ tiến sỹ': 'Hồ Tiến Sĩ', 'sỹ chí': 'Sỹ Chí', 'ngọc dương': 'Ngọc Dương', 'cao ngọc dương': 'Ngọc Dương', 'trần văn thưởng': 'Trần Văn Thưởng', 'trần thưởng': 'Trần Văn Thưởng', 'nguyễn văn hoan': 'Nguyễn Văn Hoan', 'hoàng phong': 'Bùi Hoàng Phong', 'bùi hoàng phong': 'Bùi Hoàng Phong', 'hữu nhựt': 'Hữu Nhựt', 'trần hữu nhựt': 'Hữu Nhựt', 'hữu nghị': 'Hữu Nghị', 'xuân tương': 'Lê Xuân Tương', 'thanh hoàn': 'Phạm Thanh Hoàn', 'phú vĩnh': 'Nguyễn Phú Vĩnh', 'nguyễn phú vĩnh': 'Nguyễn Phú Vĩnh', 'văn lịnh': 'Văn Lịnh', 'đông (sơn nước)': 'Đông (Sơn Nước)', 'đông , thưởng': 'Đông , Trần Văn Thưởng', 'thưởng': 'Trần Văn Thưởng', 'trần văn hát': 'Trần Văn Hát', 'nho xuân': 'Nho Xuân', 'nguyến văn ân': 'Nguyễn Văn Ân', 'văn khương': 'Văn Khương', 'nguyễn trường hận': 'Nguyễn Trường Hận', 'bảo': 'Bảo', 'chinh': 'Chinh', 'coi': 'Còi', 'còi': 'Còi', 'dương': 'Ngọc Dương', 'hiên': 'Hiên', 'quốc hiên': 'Quốc Hiên', 'hoàn': 'Phạm Thanh Hoàn', 'khoi': 'Võ Quốc Khởi', 'khởi': 'Võ Quốc Khởi', 'khoqir': 'Võ Quốc Khởi', 'khơi': 'Võ Quốc Khởi', 'khương duy long': 'Khương Duy Long', 'lê văn như': 'Lê Văn Như', 'lê đăng duyên': 'Lê Đăng Duyên', 'lưu thanh nhật tảo': 'Lưu Thanh Nhật Tảo', 'nguyễn như quyền': 'Nguyễn Như Quyền', 'nguyễn hư quyền': 'Nguyễn Như Quyền', 'nguyễn quốc khánh': 'Nguyễn Quốc Khánh', 'nguyễn thái bảo': 'Nguyễn Thái Bảo', 'nguyễn thị thu': 'Nguyễn Thị Thu', 'phạm văn linh': 'Phạm Văn Linh', 'phạm văn tùng': 'Phạm Văn Tùng', 'trương công minh': 'Trương Công Minh', 'trương văn nghĩa': 'Trương Văn Nghĩa', 'trần văn tùng': 'Trần Văn Tùng', 'đào hóa em': 'Đào Hóa Em', 'huỳnh công nông': 'Huỳnh Công Nông', 'công nông': 'Huỳnh Công Nông', 'lâm văn nông': 'Lâm Văn Nông', 'vũ duy hậu': 'Vũ Duy Hậu', 'đại phước': 'Đại Phước', 'phước': 'Đại Phước', 'đạt': 'Đạt', 'vịnh': 'Vịnh', 'giáp văn sáu': 'Giáp Văn Sáu', 'sáu': 'Giáp Văn Sáu', 'quách văn vũ': 'Quách Văn Vũ', 'quach văn vũ': 'Quách Văn Vũ', 'thái thành lưu': 'Thái Thành Lưu', 'thái văn tiến': 'Thái Văn Tiến', 'đăng hiệp': 'Đăng Hiệp', 'hiệp': 'Đăng Hiệp', 'nguyễn đăng hiệp': 'Đăng Hiệp', 'duy tài': 'Nguyễn Duy Tài', 'nguyễn duy tài': 'Nguyễn Duy Tài', 'nguyễn minh thoại': 'Nguyễn Minh Thoại', 'minh thoại': 'Nguyễn Minh Thoại', 'hồ văn tròn em': 'Hồ Văn Tròn Em', 'vay lương thuận': 'Vay Lương Thuận', 'vay lượng thuận': 'Vay Lương Thuận', 'hồ văn việt': 'Hồ Văn Việt', 'lê văn thành': 'Lê Văn Thành', 'lê văn thành-kt mới': 'Lê Văn Thành', 'phan văn việt-kt mới': 'Phan Văn Việt', 'phan văn việt': 'Phan Văn Việt', 'việt': 'Phan Văn Việt', 'bùi văn giang': 'Bùi Văn Giang', 'quang khái chỉnh': 'Quang Khải', 'quang khải': 'Quang Khải', 'thanh tuyến': 'Bành Thanh Tuyến', 'bành thanh tuyến': 'Bành Thanh Tuyến', 'duy khánh': 'Hồ Duy Khánh', 'hồ duy khánh': 'Hồ Duy Khánh', 'khánh': 'Hồ Duy Khánh', 'nguyễn văn năm': 'Nguyễn Văn Năm', 'trương vũ linh': 'Trương Vũ Linh', 'trinh bá quyền': 'Trịnh Bá Quyền', 'trịnh bá quyền': 'Trịnh Bá Quyền', 'lê văn hạnh': 'Lê Văn Hạnh', 'phạm văn vọng': 'Phạm Văn Vọng', 'phạm văn vong': 'Phạm Văn Vọng', 'quang tân': 'Nguyễn Quang Tân', 'nguyễn quang tân': 'Nguyễn Quang Tân', 'lê thành': 'Lê Thành', 'nguyễn ngọc thạch': 'Nguyễn Ngọc Thạch', 'bùi văn chất': 'Bùi Văn Chất', 'hoàn xuân': 'Hoàn Xuân', 'lâm hồng thông': 'Lâm Hồng Thông', 'trần văn tuấn': 'Trần Văn Tuấn', 'lê': 'Lê', 'nguyễn hoàng khang': 'Nguyễn Hoàng Khang', 'nguyễn kim giang': 'Nguyễn Kim Giang', 'nguyễn đình vinh': 'Nguyễn Đình Vinh', 'nguyễn đinh vinh': 'Nguyễn Đình Vinh', 'nguyễn văn ninh': 'Nguyễn Văn Ninh', 'thiêm': 'Phạm Văn Thiêm', 'phạm văn thiêm': 'Phạm Văn Thiêm', 'triệu văn đông': 'Triệu Văn Đông', 'trần văn lê': 'Trần Văn Lê', 'trung': 'Trung', 'nguyễn trọng phong': 'Nguyễn Trọng Phong', 'trọng phong': 'Nguyễn Trọng Phong', 'dương minh tường': 'Dương Minh Tường', 'duy khương': 'Duy Khương', 'đặng văn chiến': 'Đặng Văn Chiến', 'tuấn': 'Bùi Văn Tuấn', 'lương': 'Lương', 'lâm thành công': 'Lâm Thành Công', 'nguyễn minh nhí': 'Nguyễn Minh Nhí', 'phạm văn thạch': 'Phạm Văn Thạch', 'bùi quốc qui': 'Bùi Quốc Quy', 'bùi quốc quy': 'Bùi Quốc Quy', 'nguyễn trọng nhân': 'Nguyễn Trọng Nhân', 'trần văn kép': 'Trần Văn Kép', 'trinh văn thằng': 'Trịnh Văn Thắng', 'trịnh văn thắng': 'Trịnh Văn Thắng', 'tạo': 'Tạo', 'thơm': 'Thơm', 'đoàn': 'Đoàn', 'thanh xuân': 'Thanh Xuân', 'hoàng văn sang': 'Hoàng Văn Sang', 'hoàn văn sang': 'Hoàng Văn Sang', 'lê văn bụng': 'Lê Văn Bụng', 'trần quốc khang': 'Trần Quốc Khang', 'ngô trọng nhân': 'Ngô Trọng Nhân', 'nông quốc toản': 'Nông Quốc Toản', 'hùng': 'Hùng', 'nguyễn minh khang': 'Nguyễn Minh Khang', 'nguyễn hoàng giang': 'Nguyễn Hoàng Giang', 'xuân': 'Nho Xuân', 'đông': 'Đông', 'phạm văn dũng': 'Phạm Văn Dũng', 'trình': 'Trình', # Week 37 additions 'đỗ trọng long': 'Đỗ Trọng Long', 'độ trọng long': 'Đỗ Trọng Long' } def parse_date(self, val): """Converts Excel cell date values to DD/MM/YYYY string format""" if val is None: return None if isinstance(val, datetime): year = val.year if year > 2026: year = 2026 return f"{val.day:02d}/{val.month:02d}/{year}" if isinstance(val, date): year = val.year if year > 2026: year = 2026 return f"{val.day:02d}/{val.month:02d}/{year}" if isinstance(val, (int, float)): try: base = datetime(1899, 12, 30) d = base + __import__('datetime').timedelta(days=int(val)) return d.strftime("%d/%m/%Y") except: return str(int(val)) s = str(val).strip() if not s: return None if re.match(r'^\d{2}/\d{2}/\d{4}$', s): return s m = re.match(r'^(\d{4})-(\d{2})-(\d{2})', s) if m: yr = int(m.group(1)) if yr > 2026: yr = 2026 return f"{m.group(3)}/{m.group(2)}/{yr}" m2 = re.match(r'^(\d{1,2})/(\d{1,2})$', s) if m2: return f"{int(m2.group(1)):02d}/{int(m2.group(2)):02d}/2026" return s def normalize_kythuat(self, name): """Clean and normalize technician names""" if name is None: return None s = str(name).strip() if not s: return None # Remove trailing commas or punctuation s = s.rstrip(',') # Special case for compound strings s_lower = s.lower().strip() if s_lower == 'trung hùng hải lợi': return 'Trung , Hùng , Trịnh Hữu Hải , Nguyễn Hữu Lợi' if s_lower == 'lợi hùng': return 'Nguyễn Hữu Lợi , Hùng' if s_lower == 'trung lợi': return 'Trung , Nguyễn Hữu Lợi' if s_lower == 'nhựt + qui': return 'Hữu Nhựt , Bùi Quốc Quy' if s_lower == 'hoàn trung': return 'Phạm Thanh Hoàn , Trung' # Split on separators: comma, plus, newline, slash, '&', 'and', 'và', '.' parts = re.split(r'[,+\n/&.]+|\band\b|\bvà\b', s, flags=re.IGNORECASE) normalized_parts = [] for part in parts: part = part.strip() if not part: continue part = re.sub(r'\s+', ' ', part) part_clean = re.sub(r'\(.*?\)', '', part).strip() if not part_clean: normalized_parts.append(part.strip()) continue part_clean = re.sub(r'kt\s+c[ăa]m\s+t[aii].*$', '', part_clean, flags=re.IGNORECASE).strip() if not part_clean: continue p_lower = part_clean.lower().strip() if p_lower in self.kythuat_map: normalized_parts.append(self.kythuat_map[p_lower]) else: words = part_clean.split() normalized_words = [w[0].upper() + w[1:].lower() if len(w) > 1 else w.upper() for w in words] normalized_parts.append(' '.join(normalized_words)) if not normalized_parts: return None return ' , '.join(normalized_parts) def clean_file1(self, file_path): """Cleans File 1: TUẦN 37 (07.09-12.09).xlsx (Custom col 13=HT, 14=DK, 15=DH, 17=Note)""" wb = openpyxl.load_workbook(file_path, data_only=True) ws = wb.active data_rows = [] current_nha_may = None start_row = 12 for r in range(start_row, ws.max_row + 1): stt = ws.cell(r, 1).value nha_may = ws.cell(r, 2).value ma_pt = ws.cell(r, 3).value ma_nm = ws.cell(r, 4).value khach_hang = ws.cell(r, 5).value ngay_nhan = ws.cell(r, 6).value ngay_ht = ws.cell(r, 7).value loai_moi = ws.cell(r, 8).value loai_nhan = ws.cell(r, 9).value loai_bo = ws.cell(r, 10).value so_luong = ws.cell(r, 11).value ky_thuat = ws.cell(r, 12).value trang_thai_ht = ws.cell(r, 13).value trang_thai_dk = ws.cell(r, 14).value co_dh = ws.cell(r, 15).value ghi_chu = ws.cell(r, 17).value if (stt is None and nha_may is None and ma_pt is None and ma_nm is None and khach_hang is None and ngay_nhan is None and ngay_ht is None and loai_moi is None and loai_nhan is None and loai_bo is None and so_luong is None and ky_thuat is None and ghi_chu is None): continue nha_may_str = str(nha_may).strip() if nha_may is not None else '' if nha_may_str.upper() in ('TỔNG', 'TỔNG CỘNG'): continue if nha_may_str: current_nha_may = nha_may_str loai_mau = None sp = None loai_moi_str = str(loai_moi).strip() if loai_moi is not None else '' loai_nhan_str = str(loai_nhan).strip() if loai_nhan is not None else '' loai_bo_str = str(loai_bo).strip() if loai_bo is not None else '' if loai_moi_str.upper() == 'X': loai_mau = 'Mới' elif loai_nhan_str.upper() == 'X': loai_mau = 'Nhân' elif loai_bo_str and loai_bo_str.upper() != 'X': loai_mau = 'Bộ' sp = loai_bo_str trang_thai = None tt_ht_str = str(trang_thai_ht).strip() if trang_thai_ht is not None else '' tt_dk_str = str(trang_thai_dk).strip() if trang_thai_dk is not None else '' if tt_dk_str.upper() == 'X': trang_thai = 'DK' elif tt_ht_str.upper() == 'X': trang_thai = 'HT' else: trang_thai = 'ĐL' ky_thuat_clean = self.normalize_kythuat(ky_thuat) if ky_thuat else None ngay_nhan_clean = self.parse_date(ngay_nhan) ngay_ht_clean = self.parse_date(ngay_ht) nha_may_final = nha_may_str if nha_may_str else current_nha_may co_dh_final = None if co_dh is not None and str(co_dh).strip().upper() == 'X': co_dh_final = 'X' sl_final = so_luong if sl_final is not None: if isinstance(sl_final, str): try: sl_final = int(float(sl_final.replace(',', '').strip())) except: pass else: try: sl_final = int(sl_final) except: pass stt_final = stt if stt is not None: try: stt_final = int(float(str(stt).replace('=', ''))) except: pass ma_pt_final = str(ma_pt).strip() if ma_pt is not None else None if ma_pt_final in ('None', ''): ma_pt_final = None ma_nm_final = str(ma_nm).strip() if ma_nm is not None else None if ma_nm_final in ('None', ''): ma_nm_final = None ghi_chu_final = str(ghi_chu).strip() if ghi_chu is not None else None if ghi_chu_final in ('None', ''): ghi_chu_final = None if ma_pt_final in ('KHÔNG CÓ MẪU', 'KHÔNG LÀM MẪU', 'CHƯA GỬI KẾT QUẢ MẪU'): if not ghi_chu_final: ghi_chu_final = ma_pt_final ma_pt_final = None if stt_final is not None and nha_may_final and not ma_pt_final and not ma_nm_final and not khach_hang and not loai_mau and not sl_final and not ky_thuat_clean: if not ghi_chu_final: ghi_chu_final = 'KHÔNG LÀM MẪU' data_rows.append({ 'stt': stt_final, 'nha_may': nha_may_final, 'ma_pt': ma_pt_final, 'ma_nm': ma_nm_final, 'khach_hang': str(khach_hang).strip() if khach_hang is not None else None, 'ngay_nhan': ngay_nhan_clean, 'ngay_ht': ngay_ht_clean, 'loai_mau': loai_mau, 'sp': sp, 'so_luong': sl_final, 'ky_thuat': ky_thuat_clean, 'trang_thai': trang_thai, 'co_dh': co_dh_final, 'ghi_chu': ghi_chu_final }) return data_rows def clean_file2(self, file_path): """Cleans File 2: KẾT QUẢ MẪU TUẦN 37.xlsx (col 13=DL, 14=HT, 15=DK, 16=DH, 19=Note)""" wb = openpyxl.load_workbook(file_path, data_only=True) ws = wb.active data_rows = [] current_nha_may = None start_row = 5 for r in range(start_row, ws.max_row + 1): stt = ws.cell(r, 1).value nha_may = ws.cell(r, 2).value ma_pt = ws.cell(r, 3).value ma_nm = ws.cell(r, 4).value khach_hang = ws.cell(r, 5).value ngay_nhan = ws.cell(r, 6).value ngay_ht = ws.cell(r, 7).value loai_moi = ws.cell(r, 8).value loai_nhan = ws.cell(r, 9).value loai_bo = ws.cell(r, 10).value so_luong = ws.cell(r, 11).value ky_thuat = ws.cell(r, 12).value trang_thai_dl = ws.cell(r, 13).value trang_thai_ht = ws.cell(r, 14).value trang_thai_dk = ws.cell(r, 15).value co_dh = ws.cell(r, 16).value ghi_chu = ws.cell(r, 19).value if (stt is None and nha_may is None and ma_pt is None and ma_nm is None and khach_hang is None and ngay_nhan is None and ngay_ht is None and loai_moi is None and loai_nhan is None and loai_bo is None and so_luong is None and ky_thuat is None and ghi_chu is None): continue if (str(loai_bo).strip() == 'Người lập' or str(ky_thuat).strip() == 'Nguyễn Thị Thu') and stt is None and nha_may is None and ma_pt is None and ma_nm is None: continue nha_may_str = str(nha_may).strip() if nha_may is not None else '' if nha_may_str.upper() in ('TỔNG', 'TỔNG CỘNG'): continue if nha_may_str: current_nha_may = nha_may_str loai_mau = None sp = None loai_moi_str = str(loai_moi).strip() if loai_moi is not None else '' loai_nhan_str = str(loai_nhan).strip() if loai_nhan is not None else '' loai_bo_str = str(loai_bo).strip() if loai_bo is not None else '' if loai_moi_str.upper() == 'X': loai_mau = 'Mới' elif loai_nhan_str.upper() == 'X': loai_mau = 'Nhân' elif loai_bo_str and loai_bo_str.upper() != 'X': loai_mau = 'Bộ' sp = loai_bo_str trang_thai = None tt_dl_str = str(trang_thai_dl).strip() if trang_thai_dl is not None else '' tt_ht_str = str(trang_thai_ht).strip() if trang_thai_ht is not None else '' tt_dk_str = str(trang_thai_dk).strip() if trang_thai_dk is not None else '' if tt_dk_str.upper() == 'X': trang_thai = 'DK' elif tt_ht_str.upper() == 'X': trang_thai = 'HT' elif tt_dl_str.upper() == 'X': trang_thai = 'ĐL' ky_thuat_clean = self.normalize_kythuat(ky_thuat) if ky_thuat else None ngay_nhan_clean = self.parse_date(ngay_nhan) ngay_ht_clean = self.parse_date(ngay_ht) nha_may_final = nha_may_str if nha_may_str else current_nha_may co_dh_final = None if co_dh is not None and str(co_dh).strip().upper() == 'X': co_dh_final = 'X' sl_final = so_luong if sl_final is not None: if isinstance(sl_final, str): try: sl_final = int(float(sl_final.replace(',', '').strip())) except: pass else: try: sl_final = int(sl_final) except: pass stt_final = stt if stt is not None: try: stt_final = int(float(str(stt).replace('=', ''))) except: pass ma_pt_final = str(ma_pt).strip() if ma_pt is not None else None if ma_pt_final in ('None', ''): ma_pt_final = None ma_nm_final = str(ma_nm).strip() if ma_nm is not None else None if ma_nm_final in ('None', ''): ma_nm_final = None ghi_chu_final = str(ghi_chu).strip() if ghi_chu is not None else None if ghi_chu_final in ('None', ''): ghi_chu_final = None if ma_pt_final in ('KHÔNG CÓ MẪU', 'KHÔNG LÀM MẪU', 'CHƯA GỬI KẾT QUẢ MẪU'): if not ghi_chu_final: ghi_chu_final = ma_pt_final ma_pt_final = None if stt_final is not None and nha_may_final and not ma_pt_final and not ma_nm_final and not khach_hang and not loai_mau and not sl_final and not ky_thuat_clean: if not ghi_chu_final: ghi_chu_final = 'KHÔNG LÀM MẪU' data_rows.append({ 'stt': stt_final, 'nha_may': nha_may_final, 'ma_pt': ma_pt_final, 'ma_nm': ma_nm_final, 'khach_hang': str(khach_hang).strip() if khach_hang is not None else None, 'ngay_nhan': ngay_nhan_clean, 'ngay_ht': ngay_ht_clean, 'loai_mau': loai_mau, 'sp': sp, 'so_luong': sl_final, 'ky_thuat': ky_thuat_clean, 'trang_thai': trang_thai, 'co_dh': co_dh_final, 'ghi_chu': ghi_chu_final }) return data_rows def clean_w37_file(self, file_path): """Cleans W37.xlsx sheet W37 from row 536 to 548""" wb = openpyxl.load_workbook(file_path, data_only=True) sheet_name = 'W37' if 'W37' in wb.sheetnames else ('nhập' if 'nhập' in wb.sheetnames else wb.sheetnames[0]) ws = wb[sheet_name] data_rows = [] start_row = 536 end_row = 548 current_nha_may = None for r in range(start_row, end_row + 1): row_vals = [ws.cell(r, col).value for col in range(1, ws.max_column + 1)] if all(x is None or str(x).strip() == "" for x in row_vals): continue stt = row_vals[0] ngay_nhan = row_vals[1] nha_may = row_vals[2] ma_so_mau = row_vals[3] khach_hang = row_vals[5] # Col 6 (F) hien_trang = row_vals[7] # Col 8 (H) loai_moi = row_vals[9] # Col 10 (J) loai_nhan = row_vals[10] # Col 11 (K) loai_dc = row_vals[11] # Col 12 (L) loai_lailai = row_vals[12] # Col 13 (M) ghi_chu = row_vals[13] # Col 14 (N) go_val = row_vals[16] # Col 17 (Q) ky_thuat = row_vals[18] # Col 19 (S) ngay_xong = row_vals[20] if len(row_vals) > 20 and row_vals[20] is not None else (row_vals[19] if len(row_vals) > 19 else None) # Col 21 (U) or Col 20 (T) status_ky = row_vals[23] if len(row_vals) > 23 else None # Col 24 (X) if nha_may is not None and str(nha_may).strip(): current_nha_may = str(nha_may).strip() is_support_only = False if ma_so_mau is None and khach_hang is None and loai_moi is None and loai_nhan is None and loai_dc is None and loai_lailai is None and ky_thuat is None: is_support_only = True loai_mau = None if not is_support_only: if loai_moi is not None and str(loai_moi).strip().upper() == 'X': loai_mau = 'Mới' elif loai_nhan is not None and str(loai_nhan).strip().upper() == 'X': loai_mau = 'Nhân' elif loai_dc is not None and str(loai_dc).strip().upper() == 'X': loai_mau = 'Mới' elif loai_lailai is not None: loai_mau = 'Mới' trang_thai = None if not is_support_only: hien_trang_str = str(hien_trang).strip().upper() if hien_trang else "" status_ky_str = str(status_ky).strip().upper() if status_ky else "" if status_ky_str == 'X': trang_thai = 'DK' elif hien_trang_str == 'XONG': trang_thai = 'HT' elif hien_trang_str in ('ĐANG LÀM', 'CHƯA LÀM'): trang_thai = 'ĐL' else: trang_thai = 'HT' tech_clean = self.normalize_kythuat(ky_thuat) if ky_thuat else None data_rows.append({ 'stt': None, 'nha_may': current_nha_may, 'ma_pt': None, 'ma_nm': str(ma_so_mau).strip() if ma_so_mau is not None else None, 'khach_hang': str(khach_hang).strip() if khach_hang is not None else None, 'ngay_nhan': self.parse_date(ngay_nhan), 'ngay_ht': self.parse_date(ngay_xong), 'loai_mau': loai_mau, 'sp': str(go_val).strip() if go_val is not None else None, 'so_luong': 1 if not is_support_only else None, 'ky_thuat': tech_clean, 'trang_thai': trang_thai, 'co_dh': None, 'ghi_chu': str(ghi_chu).strip() if ghi_chu is not None else None }) return data_rows def save_to_excel(self, data_rows, output_path): """Helper to write data rows to standardized Excel file""" os.makedirs(os.path.dirname(output_path), exist_ok=True) wb_out = openpyxl.Workbook() ws_out = wb_out.active ws_out.title = "Làm sạch" headers = ["Stt", "Nhà máy", "Mã PT/KH", "Mã NM", "Khách hàng", "Ngày nhận", "Ngày HT", "Loại mẫu", "SP", "Số lượng", "Kỹ thuật", "Trạng thái", "Có ĐH", "Ghi chú"] header_font = Font(bold=True, size=11) header_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid") header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) for col_idx, h in enumerate(headers, 1): cell = ws_out.cell(1, col_idx, h) cell.font = header_font cell.fill = header_fill cell.alignment = header_alignment cell.border = thin_border for row_idx, r in enumerate(data_rows, 2): row_data = [ r['stt'], r['nha_may'], r['ma_pt'], r['ma_nm'], r['khach_hang'], r['ngay_nhan'], r['ngay_ht'], r['loai_mau'], r['sp'], r['so_luong'], r['ky_thuat'], r['trang_thai'], r['co_dh'], r['ghi_chu'] ] for col_idx, val in enumerate(row_data, 1): cell = ws_out.cell(row_idx, col_idx, val) cell.border = thin_border if col_idx == 1: cell.alignment = Alignment(horizontal='center') elif col_idx == 10: cell.alignment = Alignment(horizontal='center') col_widths = [5, 18, 18, 20, 18, 14, 14, 10, 25, 8, 30, 10, 8, 40] for i, w in enumerate(col_widths, 1): ws_out.column_dimensions[get_column_letter(i)].width = w wb_out.save(output_path) print(f"File saved to: {output_path}") def run(self): f1 = "TUẦN 37 (07.09-12.09).xlsx" f2 = "KẾT QUẢ MẪU TUẦN 37.xlsx" f3 = "W37.xlsx" output_dir = r"da lam sach" os.makedirs(output_dir, exist_ok=True) print("Cleaning file 1:", f1) rows1 = self.clean_file1(f1) print(f"Cleaned {len(rows1)} rows from file 1") self.save_to_excel(rows1, os.path.join(output_dir, "Tuan_37_File1_Chinh_DA-LAM-SACH.xlsx")) print("Cleaning file 2:", f2) rows2 = self.clean_file2(f2) print(f"Cleaned {len(rows2)} rows from file 2") self.save_to_excel(rows2, os.path.join(output_dir, "Tuan_37_File2_KQM_DA-LAM-SACH.xlsx")) print("Cleaning file 3 (W37):", f3) rows3 = self.clean_w37_file(f3) print(f"Cleaned {len(rows3)} rows from file 3") self.save_to_excel(rows3, os.path.join(output_dir, "Tuan_37_File3_W37_DA-LAM-SACH.xlsx")) # Merge all 3 files into Master Cleaned file combined_rows = [] current_new_stt = 0 # File 1 rows last_seen_orig_stt = None for r in rows1: if r['stt'] is not None: if r['stt'] != last_seen_orig_stt: current_new_stt += 1 last_seen_orig_stt = r['stt'] r['stt'] = current_new_stt else: r['stt'] = None combined_rows.append(r) # File 2 rows last_seen_orig_stt = None for r in rows2: if r['stt'] is not None: if r['stt'] != last_seen_orig_stt: current_new_stt += 1 last_seen_orig_stt = r['stt'] r['stt'] = current_new_stt else: r['stt'] = None combined_rows.append(r) # File 3 rows factory_to_stt = {} for r in combined_rows: if r['nha_may'] and r['stt'] is not None: factory_to_stt[r['nha_may'].strip().upper()] = r['stt'] for r in rows3: nm_key = r['nha_may'].strip().upper() if r['nha_may'] else "" if nm_key: if nm_key in factory_to_stt: r['stt'] = factory_to_stt[nm_key] else: current_new_stt += 1 factory_to_stt[nm_key] = current_new_stt r['stt'] = current_new_stt else: r['stt'] = None combined_rows.append(r) total_factories = current_new_stt print(f"Combined data. Total factories: {total_factories}, Total rows: {len(combined_rows)}") # Save unified master file master_output_path = os.path.join(output_dir, "Tuan_37_07.09-12.09_DA-LAM-SACH.xlsx") self.save_to_excel(combined_rows, master_output_path) print(f"Master Cleaned and merged data saved to: {master_output_path}") # Generate Markdown Report report_path = os.path.join(output_dir, "Bao_cao_lam_sach_Tuan37.md") types_count = {'Mới': 0, 'Nhân': 0, 'Bộ': 0, None: 0} status_count = {'DK': 0, 'HT': 0, 'ĐL': 0, None: 0} co_dh_count = 0 total_qty = 0 for r in combined_rows: lm = r['loai_mau'] tt = r['trang_thai'] qty = r['so_luong'] dh = r['co_dh'] if lm in types_count: types_count[lm] += 1 else: types_count[lm] = types_count.get(lm, 0) + 1 if tt in status_count: status_count[tt] += 1 else: status_count[tt] = status_count.get(tt, 0) + 1 if dh == 'X': co_dh_count += 1 if qty: total_qty += qty report_content = f"""# BÁO CÁO LÀM SẠCH DỮ LIỆU - TUẦN 37 ## Thông tin chung - **File gốc 1 (Chính)**: TUẦN 37 (07.09-12.09).xlsx - **File gốc 2 (Khách lẻ)**: KẾT QUẢ MẪU TUẦN 37.xlsx - **File gốc 3 (Nhật ký W37)**: W37.xlsx (Sheet: W37, Dòng 536-548) - **File sạch đầu ra tổng hợp**: `Tuan_37_07.09-12.09_DA-LAM-SACH.xlsx` - **Các file sạch từng phần**: - `Tuan_37_File1_Chinh_DA-LAM-SACH.xlsx` - `Tuan_37_File2_KQM_DA-LAM-SACH.xlsx` - `Tuan_37_File3_W37_DA-LAM-SACH.xlsx` - **Ngày xử lý**: {datetime.now().strftime('%d/%m/%Y')} ## Thống kê dữ liệu gộp - **Tổng số nhà máy**: {total_factories} - **Tổng số dòng dữ liệu**: {len(combined_rows)} - **Tổng sản lượng sản phẩm làm mẫu**: {total_qty} cái - **Số lượng mẫu đã có đơn hàng**: {co_dh_count} mẫu | Loại mẫu | Số lượng dòng | Trạng thái | Số lượng dòng | |----------|---------------|------------|---------------| | Mẫu Mới | {types_count.get('Mới', 0)} | Đã Ký (DK) | {status_count.get('DK', 0)} | | Mẫu Nhân | {types_count.get('Nhân', 0)} | Hoàn Thành (HT) | {status_count.get('HT', 0)} | | Mẫu Bộ | {types_count.get('Bộ', 0)} | Đang làm (ĐL) | {status_count.get('ĐL', 0)} | | Không làm mẫu / Chưa rõ | {types_count.get(None, 0)} | Chưa rõ / Trống | {status_count.get(None, 0)} | ## Các thao tác đã thực hiện 1. **Làm sạch riêng biệt & gộp chuẩn hóa 3 file nguồn**: - Đọc dữ liệu từ file chính `TUẦN 37 (07.09-12.09).xlsx` (từ dòng 12, tự động nhận diện cấu trúc cột đặc thù). - Đọc dữ liệu từ file khách lẻ `KẾT QUẢ MẪU TUẦN 37.xlsx` (từ dòng 5, loại bỏ dòng tổng kết và dòng ký tên). - Đọc dữ liệu từ sổ theo dõi `W37.xlsx` (Sheet "W37", các dòng mới từ 536 đến 548). 2. **Đánh lại số thứ tự (Stt) nhà máy liên tục**: Đảm bảo số thứ tự nhà máy tăng liên tục từ 1 đến {total_factories}. 3. **Chuẩn hóa kỹ thuật viên**: Sử dụng bộ quy tắc chuẩn hóa tên kỹ thuật viên (xử lý tách gộp nhóm, chuẩn hóa lỗi chính tả và định dạng danh xưng). 4. **Phân tách cột Loại mẫu và Trạng thái**: Ánh xạ đúng các trường tương ứng từ cột ban đầu về định dạng chuẩn (DK, HT, ĐL và Mới, Nhân, Bộ). 5. **Xử lý các nhà máy không làm mẫu**: Tự động chuyển các bản ghi "KHÔNG CÓ MẪU" / "KHÔNG LÀM MẪU" sang cột Ghi chú một cách chuẩn xác. """ with open(report_path, "w", encoding="utf-8") as f: f.write(report_content) print(f"Markdown report written to: {report_path}") if __name__ == "__main__": cleaner = DataCleanerAndMergerT37() cleaner.run()