coi/app/report/views.py

from django.shortcuts import render, redirect, get_object_or_404
from django.core.paginator import Paginator
from django.contrib import messages
from core.models import Report, AllProductDimensionForInsProcess, CustomerTemplateMapping, \
                ProductDrawing, MksCodeMap
from core.forms import ReportForm, CustomerTemplateMappingForm, ProductDrawingForm
from core.utils import ConfigurableCRUDView, queryFromMaster, SHEET_NAMES, convert_mgt_to_mks
from .filters import ReportFilter, CustomerTemplateFilter, ProductDrawingFilter, MksCodeMapFilter
from .forms import ExportOptionsForm
from pprint import pprint

from legacy.models import Data, DataMs, DataRl, DataWb, LotSummary, LotSummaryRl, LotSummaryWb, PressCal, RotateData, TbFgPressinfoLotlist,\
        Manualsize

from .gen_report import gen_xlsx
from django.core.files.base import File
from pathlib import Path
from django.views.decorators.csrf import csrf_exempt
from django.http import JsonResponse, HttpResponseBadRequest
import json
from django.contrib.auth.decorators import login_required
from django.contrib.auth.models import User
from sysadmin.models import UserProfile
from legacy.models import Data
from django.conf import settings

from itertools import chain

from django_filters.views import FilterView
from django.db.models import Q, Func, Value, F


from django.views.generic import (
    ListView,)

COI_TEMPLATE_FILES = {
    "japanese": "coi_templates.xlsx",
    "asean": "coi_templates_en.xlsx",
}


def get_coi_template_file(template_name):
    template_key = (template_name or "japanese").lower()
    filename = COI_TEMPLATE_FILES.get(template_key, COI_TEMPLATE_FILES["japanese"])
    return f"{settings.BASE_DIR}/report/{filename}"


def get_customer_template_mapping(master_row):
    if not master_row or not master_row.PRO1C:
        return None
    return CustomerTemplateMapping.objects.filter(
        customer_name__icontains=master_row.PRO1C
    ).first()


def index(request):
    reports = Report.objects.all()
    report_filter = ReportFilter(request.GET, queryset=reports)
    
    # Paginate the filtered queryset
    paginator = Paginator(report_filter.qs, 10)  # Show 10 reports per page
    page_number = request.GET.get('page')
    page_obj = paginator.get_page(page_number)
    
    context = {
        'filter': report_filter,
        'page_obj': page_obj,
    }
    return render(request, 'report/index.html', context)

def report_create_view(request):
    if request.method == "POST":
        form = ReportForm(request.POST)
        if form.is_valid():
            form.save()
            messages.success(request, "Report created successfully!")
            return redirect("report:report_index")  # Adjust with your report list view name
    else:
        form = ReportForm()

    return render(request, "report/create.html", {"form": form})


class ReportCRUDView(ConfigurableCRUDView):
    model = Report
    list_template_name = 'legacy/datacrud_list.html'
    detail_template_name = 'legacy/datacrud_detail.html'
    form_template_name = 'report/report_form.html'
    confirm_delete_template_name = 'legacy/datacrud_confirm_delete.html'
    filterset_class = ReportFilter

    page_title = "Reports"

    # URL name mappings
    list_url_name = 'report:report-list'
    create_url_name = 'report:report-create'
    update_url_name = 'report:report-update'
    delete_url_name = 'report:report-delete'
    config_fields = ["name", "file", "created_by", "created_at"] 
    config_field_orders = ["id", "name", "created_by"]
    # config_readonly_fields = ["lot_no"]
    # config_edit_fields = ["lot_no", "code"]
    ordering = ["-created_at", "-id",]

def convert_sheet_data(sheet_data):
    """
    Convert sheet_data to the required form with prefixed keys.
    
    :param sheet_data: Dictionary with sheet names as keys and their data as values.
    :return: Dictionary in the required key-value format.
    """
    converted_data = {}

    for sheet_name, data in sheet_data.items():
        for key, value in data.items():
            # Prefix each key with the sheet name
            converted_key = f"{sheet_name}.{key}"
            converted_data[converted_key] = value

    return converted_data

def hide_con(placeholders, mark_value, hide_rows):
    """
    Updates the 'placeholders' dictionary with a mark value and hide rows range.

    :param placeholders: The dictionary to update.
    :param mark_value: The key to check or update in the placeholders.
    :param hide_rows: The row range to append in the format '[start:end]'.
    """
    if mark_value in placeholders:
        placeholders[mark_value] = f"{placeholders[mark_value]}[{hide_rows}]"
    else:
        placeholders[mark_value] = f"0[{hide_rows}]"

def clear_values(n, m):
    placeholders = {}
    for i in range(1, n + 1):
        for j in range(1, m + 1):
            placeholders[f'v{i}_{j}'] = " "
    return placeholders

def generate_hardness_out_values(lot_no, code):
    """
    Generate a dictionary of placeholder values for a given lot_no.

    :param lot_no: The lot number to query data for.
    :return: A dictionary with placeholders (e.g., v1_1, v1_2, ...) as keys and their respective values.
    """
    # Query the Data model for records matching the given lot_no
    # records = Data.objects.filter(lot_no=lot_no).order_by('row_no')
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no'))

    records = list(chain(data_h1, data_h2))
    
    # print(f"records {lot_no} = {records.values()}")
    # Initialize an empty dictionary to store placeholder values
    placeholders = clear_values(2,5)

    # Iterate over the records to populate placeholder values
    inspect_date = None
    for record_idx, record in enumerate(records, start=1):
        if record_idx == 1:
            inspect_date = record.created_at
        placeholders[f'v{record_idx}_1'] = record.p1  # Checkpoint 1 value
        placeholders[f'v{record_idx}_2'] = record.p2  # Checkpoint 2 value
        placeholders[f'v{record_idx}_3'] = record.p3  # Checkpoint 3 value
        placeholders[f'v{record_idx}_4'] = record.avg  # Average value
        placeholders[f'v{record_idx}_5'] = record.rgrade  # Judgment value

    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_hardness_out_in_values(lot_no, code):
    # Fetch records from the Data model
    # records = Data.objects.filter(lot_no=lot_no).order_by('row_no')
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no'))

    records = list(chain(data_h1, data_h2))

    out_data = []
    in_data = []

    # Separate OUT and IN data
    for record in records:
        if record.r_type.upper() in ["OUT", "TOP", "FA1", "UPP", "UPPE", "RIM"]:
            out_data.append(record)
        elif record.r_type.upper() in ["IN", "UNDER", "UND", "FA2", "LOW", "LOWE", "BASE", "SOKO", "IN*", "INNER"]:
            in_data.append(record)

    # Prepare placeholders
    # placeholders = {}
    placeholders = clear_values(4,6)
    inspect_date = None
    for idx, record in enumerate(out_data, start=0):
        if idx == 1:
            inspect_date = record.created_at

        placeholders[f'v{2*idx+1}_1'] = record.p1
        placeholders[f'v{2*idx+1}_2'] = record.p2
        placeholders[f'v{2*idx+1}_3'] = record.p3
        placeholders[f'v{2*idx+1}_6'] = record.p4  # Checkpoint 3 value
        placeholders[f'v{2*idx+1}_4'] = record.avg
        placeholders[f'v{2*idx+1}_5'] = record.rgrade

    for idx, record in enumerate(in_data, start=1):
        placeholders[f'v{idx*2}_1'] = record.p1
        placeholders[f'v{idx*2}_2'] = record.p2
        placeholders[f'v{idx*2}_3'] = record.p3
        placeholders[f'v{idx*2}_6'] = record.p4
        placeholders[f'v{idx*2}_4'] = record.avg
        placeholders[f'v{idx*2}_5'] = record.rgrade

    # if "v3_1" in placeholders:
        # placeholders["v3_1"] = f"{placeholders['v3_1']}[25:28]"
    # else:
        # placeholders[f"v3_1"] = "0[25:28]"

    hide_con(placeholders, "v3_1", "26:29")
    
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_hardness_both_size_values(lot_no, ms,code):
    # Fetch records from the Data model
    # records = Data.objects.filter(lot_no=lot_no).order_by('row_no')
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no', 'sub_order'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no', 'sub_order'))

    records = list(chain(data_h1, data_h2))

    out_data = []
    in_data = []

    placeholders = clear_values(20,5)
    # Separate OUT and IN data
    inspect_date = None
    for idx, record in enumerate(records, start=0):
        if idx == 1:
            inspect_date = record.created_at

        if record.sub_order == 1:
            for i,v in enumerate(["p1", "p2", "p3", "avg", "rgrade"],start=1):
                placeholders[f"v{idx*2+1}_{i}"] = getattr(record,v)
        if record.sub_order == 2:
            for i,v in enumerate(["p1", "p2", "p3", "avg", "rgrade"],start=1):
                placeholders[f"v{idx*2+2}_{i}"] = getattr(record,v)
    # Prepare placeholders
    # placeholders = {}
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders


# # Example usage:
# placeholders_dict = clear_values(6, 3)
# print(placeholders_dict)
def generate_dimension_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)
    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(7,4)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records) 
    for m in dimens:
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v4_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v5_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v6_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'



    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at
        
        if r.row_no == 2:
            placeholders[f'v4_2'] = r.dsize
            placeholders[f'v4_3'] = r.dsizeok
            
            placeholders[f'v5_2'] = r.tsize
            placeholders[f'v5_3'] = r.tsizeok
            
            placeholders[f'v6_2'] = r.hsize
            placeholders[f'v6_3'] = r.hsizeok

    hide_con(placeholders, "v4_2", "25:29")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_dimension_weight_warp_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)
    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')
    data_wb = DataWb.objects.filter(lot_no=lot_no).order_by('row_no')
    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(7,4)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records) 
    for m in dimens:
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v4_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v5_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v6_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'



    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at
        
        if r.row_no == 2:
            placeholders[f'v4_2'] = r.dsize
            placeholders[f'v4_3'] = r.dsizeok
            
            placeholders[f'v5_2'] = r.tsize
            placeholders[f'v5_3'] = r.tsizeok
            
            placeholders[f'v6_2'] = r.hsize
            placeholders[f'v6_3'] = r.hsizeok


    for r in data_wb:
        if r.row_no == 1:
            placeholders["v7_1"] = r.weight
            placeholders["v7_2"] = r.judgement
        if r.row_no == 2:
            placeholders["v8_1"] = r.weight
            placeholders["v8_2"] = r.judgement
    
    hide_con(placeholders, "v4_2", "29:37")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def is_ok(instance):
    fields_and_values = {}
    for field in instance._meta.get_fields():
        field_name = field.name
        if field_name.endswith('ok'):
            v = getattr(instance, field_name)
            if v is not None and v != "OK":
                return False
    return True

def generate_dimension_app_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(8,3)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records)

    # for m in manual_size_records:
        # if m.size_name == "D":
            # placeholders['v1_1'] = placeholders['v5_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        # if m.size_name == "T":
            # placeholders['v2_1'] = placeholders['v6_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        # if m.size_name == "H":
            # placeholders['v3_1'] = placeholders['v7_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v5_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v6_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v7_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'



    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at

            # if is_ok(r):
                # placeholders[f'v4_1'] = 'OK'
                # placeholders[f'v4_2'] = 'OK'
            # else:
                # placeholders[f'v4_1'] = 'NG'
                # placeholders[f'v4_2'] = 'OK'
        
        if r.row_no == 2:
            placeholders[f'v5_2'] = r.dsize
            placeholders[f'v5_3'] = r.dsizeok
            
            placeholders[f'v6_2'] = r.tsize
            placeholders[f'v6_3'] = r.tsizeok
            
            placeholders[f'v7_2'] = r.hsize
            placeholders[f'v7_3'] = r.hsizeok

            # if is_ok(r):
                # placeholders[f'v8_1'] = 'OK'
                # placeholders[f'v8_2'] = 'OK'
            # else:
                # placeholders[f'v8_1'] = 'NG'
                # placeholders[f'v8_2'] = 'NG'

    hide_con(placeholders, "v5_1", "29:37")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def safe_strip(val):
    """Remove '-' or return '' if None."""
    return val.replace('-', '') if val else ''

def generate_dimension_app_drawing_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(8,3)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records)

    # for m in manual_size_records:
        # if m.size_name == "D":
            # placeholders['v1_1'] = placeholders['v5_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        # if m.size_name == "T":
            # placeholders['v2_1'] = placeholders['v6_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        # if m.size_name == "H":
            # placeholders['v3_1'] = placeholders['v7_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v5_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v6_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v7_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'



    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at

            # if is_ok(r):
                # placeholders[f'v4_1'] = 'OK'
                # placeholders[f'v4_2'] = 'OK'
            # else:
                # placeholders[f'v4_1'] = 'NG'
                # placeholders[f'v4_2'] = 'OK'
        
        if r.row_no == 2:
            placeholders[f'v5_2'] = r.dsize
            placeholders[f'v5_3'] = r.dsizeok
            
            placeholders[f'v6_2'] = r.tsize
            placeholders[f'v6_3'] = r.tsizeok
            
            placeholders[f'v7_2'] = r.hsize
            placeholders[f'v7_3'] = r.hsizeok

            # if is_ok(r):
                # placeholders[f'v8_1'] = 'OK'
                # placeholders[f'v8_2'] = 'OK'
            # else:
                # placeholders[f'v8_1'] = 'NG'
                # placeholders[f'v8_2'] = 'NG'

    hide_con(placeholders, "v5_1", "27:33")
    

    norm_lot_no = safe_strip(lot_no)
    norm_code = safe_strip(code)
    
    drawing_qs = ProductDrawing.objects.annotate(
        norm_lot_no=Func(F('lot_no'), Value('-'), Value(''), function='replace'),
        norm_code_no=Func(F('code_no'), Value('-'), Value(''), function='replace')
    )

    query = Q()
    if norm_lot_no:
        query |= Q(norm_lot_no=norm_lot_no)
    if norm_code:
        query |= Q(norm_code_no=norm_code)

    drawing_data = drawing_qs.filter(query).first()
    
    pprint(f"1.1 = {drawing_data}") 
    pprint(f"1.2 = {lot_no} {code}")
    if drawing_data:
        placeholders["drawing"] = drawing_data.drawing if drawing_data.drawing else "-"

    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_dimension_bal_weight_values(lot_no, ms, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    data_wb = DataWb.objects.filter(lot_no=lot_no).order_by('row_no')
    
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no'))

    data_ho = list(chain(data_h1, data_h2))
    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(10,3)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records) 
    
    if ms:
        try:
            w = ms.PRO6
            placeholders['v4_1'] = placeholders['v9_1'] = w
        except:
            print("no PRO6")

    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v6_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v7_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v8_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'

    inspect_date = None

    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at

            # if is_ok(r):
                # placeholders[f'v4_1'] = 'OK'
                # placeholders[f'v4_2'] = 'OK'
            # else:
                # placeholders[f'v4_1'] = 'NG'
                # placeholders[f'v4_2'] = 'OK'
        
        if r.row_no == 2:
            placeholders[f'v6_2'] = r.dsize
            placeholders[f'v6_3'] = r.dsizeok
            
            placeholders[f'v7_2'] = r.tsize
            placeholders[f'v7_3'] = r.tsizeok
            
            placeholders[f'v8_2'] = r.hsize
            placeholders[f'v8_3'] = r.hsizeok

            # if is_ok(r):
                # placeholders[f'v8_1'] = 'OK'
                # placeholders[f'v8_2'] = 'OK'
            # else:
                # placeholders[f'v8_1'] = 'NG'
                # placeholders[f'v8_2'] = 'NG'

    for r in data_wb:
        if r.row_no == 1:
            placeholders["v4_2"] = r.result
            placeholders["v4_3"] = r.judgement
            placeholders["v5_2"] = r.weight
            placeholders["v5_3"] = r.judgement
        if r.row_no == 2:
            placeholders["v9_2"] = r.result
            placeholders["v9_3"] = r.judgement
            placeholders["v10_2"] = r.weight
            placeholders["v10_3"] = r.judgement
    # hide_con(placeholders, "v5_1", "26:32")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_dim_bal_app_hard_values(lot_no, first_result, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')
    data_wb = DataWb.objects.filter(lot_no=lot_no).order_by('row_no')
    
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no'))

    data_ho = list(chain(data_h1, data_h2))

    if first_result:
        # out_limit = f"Out 外  ({first_result.MI18} - {first_result.MI19})"
        # in_limit = f"In 内 ({first_result.MI22} - {first_result.MI23})"
        try:
            out_limit = f"Out 外 ({float(first_result.MI18):.2f} - {float(first_result.MI19):.2f})"
        except (TypeError, ValueError):
            out_limit = "Out 外"

        mi22 = getattr(first_result, "MI22", None)
        mi23 = getattr(first_result, "MI23", None)

        # If MI22 is missing, None, 0, or ""
        if mi22 in (None, 0, "", "0"):
            in_limit = "In 内: -"
        else:
            try:
                in_limit = f"In 内 ({float(mi22):.2f} - {float(mi23 or 0):.2f})"
            except (TypeError, ValueError):
                in_limit = "In 内: -"
        mid_limit = f"Middle   中  -"
    else:
        out_limit = f"Out 外"
        in_limit = f"In 内"
        mid_limit = f"Middle   中"
    
    

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(16,5)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    placeholders['v6_0'] = placeholders['v14_0'] = out_limit
    placeholders['v7_0'] = placeholders['v15_0'] = mid_limit
    placeholders['v8_0'] = placeholders['v16_0'] = in_limit

    pprint(placeholders)
    pprint(manual_size_records) 
    if first_result:
        try:
            w = first_result.MI24
            placeholders['v4_1'] = placeholders['v12_1'] = f"{w}"
        except:
            print("No PRO6")

    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v9_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f}/{m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v10_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f}/{m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v11_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f}/{m.TolUn:.2f}'

    inspect_date = None
    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at

            # if is_ok(r):
                # placeholders[f'v4_1'] = 'OK'
                # placeholders[f'v4_2'] = 'OK'
            # else:
                # placeholders[f'v4_1'] = 'NG'
                # placeholders[f'v4_2'] = 'OK'
        
        if r.row_no == 2:
            placeholders[f'v9_2'] = r.dsize
            placeholders[f'v9_3'] = r.dsizeok
            
            placeholders[f'v10_2'] = r.tsize
            placeholders[f'v10_3'] = r.tsizeok
            
            placeholders[f'v11_2'] = r.hsize
            placeholders[f'v11_3'] = r.hsizeok

            # if is_ok(r):
                # placeholders[f'v8_1'] = 'OK'
                # placeholders[f'v8_2'] = 'OK'
            # else:
                # placeholders[f'v8_1'] = 'NG'
                # placeholders[f'v8_2'] = 'NG'

    for r in data_wb:
        if r.row_no == 1:
            placeholders["v4_2"] = f"{r.weight:.1f}"
            placeholders["v4_3"] = r.judgement
        if r.row_no == 2:
            placeholders["v12_2"] = f"{r.weight:.1f}"
            placeholders["v12_3"] = r.judgement

    for r in data_ho:
        if r.row_no == 1:
            rmap = {'OUT': 6, 'MID': 7, 'IN': 8}
            for index, v in enumerate(["p1", "p2", "p3", "avg", "rgrade"], start=1):
                idx = rmap.get(r.r_type, None)
                if idx:
                    placeholders[f'v{idx}_{index}']  = getattr(r, v, "-") 
        if r.row_no == 2:
            rmap = {'OUT': 14, 'MID': 15, 'IN': 16}
            for index, v in enumerate(["p1", "p2", "p3", "avg", "rgrade"], start=1):
                idx = rmap.get(r.r_type, None)
                if idx:
                    placeholders[f'v{idx}_{index}']  = getattr(r, v, "-")

    # hide_con(placeholders, "v5_1", "26:32")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_dim_bal_app_rot_hard_values(lot_no, first_result, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')
    data_wb = DataWb.objects.filter(lot_no=lot_no).order_by('row_no')
    
    data_h1 = list(Data.objects.filter(lot_no=lot_no).order_by('row_no'))
    data_h2 = list(DataRl.objects.filter(lot_no=lot_no).order_by('row_no'))

    data_ho = list(chain(data_h1, data_h2))


    rotates = RotateData.objects.filter(lot_no=lot_no).order_by('row_no')

    if first_result:
        # out_limit = f"Out 外  ({first_result.MI18} - {first_result.MI19})"
        # in_limit = f"In 内 ({first_result.MI22} - {first_result.MI23})"

        try:
            out_limit = f"Out 外 ({float(first_result.MI18):.2f} - {float(first_result.MI19):.2f})"
        except (TypeError, ValueError):
            out_limit = "Out 外"

        mi22 = getattr(first_result, "MI22", None)
        mi23 = getattr(first_result, "MI23", None)

        # If MI22 is missing, None, 0, or ""
        if mi22 in (None, 0, "", "0"):
            in_limit = "In 内"
        else:
            try:
                in_limit = f"In 内 ({float(mi22):.2f} - {float(mi23 or 0):.2f})"
            except (TypeError, ValueError):
                in_limit = "In 内"

        mid_limit = f"Middle   中  -"
    else:
        out_limit = f"Out 外"
        in_limit = f"In 内"
        mid_limit = f"Middle   中"
    
    

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(18,5)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    placeholders['v6_0'] = placeholders['v14_0'] = out_limit
    placeholders['v7_0'] = placeholders['v15_0'] = mid_limit
    placeholders['v18_0'] = placeholders['v16_0'] = in_limit

    pprint(placeholders)
    pprint(manual_size_records) 
    if first_result:
        try:
            w = first_result.PRO6
            placeholders['v4_1'] = placeholders['v13_1'] = w
        except:
            print("no PRO6")

    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "D":
            placeholders['v1_1'] = placeholders['v10_1'] = f'D{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "T":
            placeholders['v2_1'] = placeholders['v11_1'] = f'T{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
        if m.Size_Name == "H":
            placeholders['v3_1'] = placeholders['v12_1'] = f'H{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'



    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for r in data_ms_records:
        if r.row_no == 1:
            placeholders[f'v1_2'] = r.dsize
            placeholders[f'v1_3'] = r.dsizeok
            
            placeholders[f'v2_2'] = r.tsize
            placeholders[f'v2_3'] = r.tsizeok
            
            placeholders[f'v3_2'] = r.hsize
            placeholders[f'v3_3'] = r.hsizeok
            inspect_date = r.created_at

            # if is_ok(r):
                # placeholders[f'v4_1'] = 'OK'
                # placeholders[f'v4_2'] = 'OK'
            # else:
                # placeholders[f'v4_1'] = 'NG'
                # placeholders[f'v4_2'] = 'OK'
        
        if r.row_no == 2:
            placeholders[f'v10_2'] = r.dsize
            placeholders[f'v10_3'] = r.dsizeok
            
            placeholders[f'v11_2'] = r.tsize
            placeholders[f'v11_3'] = r.tsizeok
            
            placeholders[f'v12_2'] = r.hsize
            placeholders[f'v12_3'] = r.hsizeok

            # if is_ok(r):
                # placeholders[f'v8_1'] = 'OK'
                # placeholders[f'v8_2'] = 'OK'
            # else:
                # placeholders[f'v8_1'] = 'NG'
                # placeholders[f'v8_2'] = 'NG'

    for r in data_wb:
        if r.row_no == 1:
            placeholders["v4_2"] = f"{r.weight:.1f}"
            placeholders["v4_3"] = r.judgement
        if r.row_no == 2:
            placeholders["v13_2"] = f"{r.weight:.1f}"
            placeholders["v13_3"] = r.judgement

    for r in data_ho:
        if r.row_no == 1:
            rmap = {'OUT': 7, 'MID': 8, 'IN': 9}
            for index, v in enumerate(["p1", "p2", "p3", "avg", "rgrade"], start=1):
                idx = rmap.get(r.r_type, None)
                if idx:
                    placeholders[f'v{idx}_{index}']  = getattr(r, v) 
        if r.row_no == 2:
            rmap = {'OUT': 16, 'MID': 17, 'IN': 18}
            for index, v in enumerate(["p1", "p2", "p3", "avg", "rgrade"], start=1):
                idx = rmap.get(r.r_type, None)
                if idx:
                    placeholders[f'v{idx}_{index}']  = getattr(r, v)

    for r in rotates:
        if r.row_no == 1:
            placeholders["v6_1"] = r.speed_spec
            placeholders["v6_2"] = r.speed_test
            placeholders["v6_3"] = r.speedok
        if r.row_no == 2:
            placeholders["v15_1"] = r.speed_spec
            placeholders["v15_2"] = r.speed_test
            placeholders["v15_3"] = r.speedok

    # hide_con(placeholders, "v5_1", "26:32")
    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_centering_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)

    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    # Prepare placeholders

    # placeholders = {}
    placeholders = clear_values(20, 3)
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    
    pprint(placeholders)
    pprint(manual_size_records) 


    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    record_count = 0
    for i,r in enumerate(data_ms_records, start=1):
        record_count = i
        if i == 1:
            inspect_date = r.created_at
        placeholders[f'v{i}_2'] = r.censize
        placeholders[f'v{i}_3'] = r.censizeok

    if record_count <= 10:
        hide_con(placeholders, "v11_2", "47:93")

    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def generate_t8_values(lot_no, code):
    """
    Fetch dimension records from manualSize and DataMs models
    and generate placeholder values for Standard, Actual, and Judgement.
    Supports two row_no entries per lot.
    """
    # Fetch standard values from manualSize (limit to 2 rows)
    placeholders = clear_values(8,10)
    
    manual_size_records = Manualsize.objects.filter(lotno=lot_no)
    # pprint(f"manual_size = {manual_size_records}")
    dimens = AllProductDimensionForInsProcess.objects.filter(ProductCode=code)
    for m in dimens:  # Changed from manual_size_records to dimens
        if m.Size_Name == "Thickness":
            for i in range(1, 9):
                placeholders[f'v{i}_1'] = f'{m.Std:.2f} +{m.TolUp:.2f} {m.TolUn:.2f}'
                # pprint("set std")
    # Fetch actual and judgement values from DataMs ordered by row_no (limit to 2 rows)
    data_ms_records = DataMs.objects.filter(lot_no=lot_no).order_by('row_no')

    # Prepare placeholders

    # placeholders = {}
    # for i in range(1,7):
        # for j in range(1,4):
            # placeholders[f'v{i}_{j}'] = 0
    

    # Ensure that we map each manualSize entry to a corresponding DataMs entry
    inspect_date = None
    for i,r in enumerate(data_ms_records, start=1):
        if i == 1:
            inspect_date = r.created_at

        placeholders[f'v{i}_2'] = r.tpoint1
        placeholders[f'v{i}_3'] = r.tpoint2
        placeholders[f'v{i}_4'] = r.tpoint3
        placeholders[f'v{i}_5'] = r.tpoint4
        placeholders[f'v{i}_10'] = r.tdiff

    placeholders['inspect_date'] = inspect_date.strftime('%Y/%m/%d') if inspect_date else "-"
    return placeholders

def merge_sheet_data_with_data(sheet_data, data):
    """
    Merge `sheet_data` with `data`.

    :param sheet_data: Dictionary containing the sheet-specific data.
    :param data: Dictionary containing general data.
    :return: A merged dictionary combining both `sheet_data` and `data`.
    """
    # Merge dictionaries using unpacking
    merged_data = {**data, **sheet_data}

    return merged_data

def create_coi_file(lot_no, sheets, user, md):
    pprint("---- create_coi_file ---")
    pprint(md)
    qa1 = User.objects.get(pk=md['qa1'])
    qa2 = User.objects.get(pk=md['qa2'])

    accept = specialAccept = False
    if md['acceptStatus'] == "accepted":
        accept = True
    if md['acceptStatus'] == "special_accepted":
        specialAccept = True


    pprint(qa1)
    pprint(qa2)
    
    results = queryFromMaster(lot_no)
    first_result = results[0] if results else None
    code  = first_result.PRO1 if first_result else None 
    sheet_data = {}
    for sheet_name in sheets:
        if sheet_name == 'hardness_out':
            sheet_data[sheet_name] = generate_hardness_out_values(lot_no, code)
        elif sheet_name == 'hardness_out_in':
            sheet_data[sheet_name] = generate_hardness_out_in_values(lot_no, code)
        elif sheet_name == 'hardness_out_in_4':
            sheet_data[sheet_name] = generate_hardness_out_in_values(lot_no, code)
        elif sheet_name == 'hardness_both_size':
            sheet_data[sheet_name] = generate_hardness_both_size_values(lot_no, first_result, code)
        elif sheet_name == 'dimension':
            sheet_data[sheet_name] = generate_dimension_values(lot_no, code)

        elif sheet_name == 'dimension_weight_warp':
            sheet_data[sheet_name] = generate_dimension_weight_warp_values(lot_no, code)
        elif sheet_name == 'dimension_app':
            sheet_data[sheet_name] = generate_dimension_app_values(lot_no, code)
        elif sheet_name == 'dimension_app_drawing':
            sheet_data[sheet_name] = generate_dimension_app_drawing_values(lot_no, code)
        elif sheet_name == 'dimension_bal_weight':
            sheet_data[sheet_name] = generate_dimension_bal_weight_values(lot_no, first_result, code)
        elif sheet_name == 'centering':
            sheet_data[sheet_name] = generate_centering_values(lot_no, code)
        elif sheet_name == 'thickness_8_point':
            sheet_data[sheet_name] = generate_t8_values(lot_no, code)
        elif sheet_name == 'dim_bal_app_hard':
            sheet_data[sheet_name] = generate_dim_bal_app_hard_values(lot_no, first_result, code)
        elif sheet_name == 'dim_bal_app_hard_stamp':
            sheet_data[sheet_name] = generate_dim_bal_app_hard_values(lot_no, first_result, code)
        elif sheet_name == 'dim_bal_app_rot_hard':
            sheet_data[sheet_name] = generate_dim_bal_app_rot_hard_values(lot_no, first_result, code)
    
    converted_data = convert_sheet_data(sheet_data)

    print(f"sheet_data \n {sheet_data}") 
    print(f"converted_data \n {converted_data}")

    # results = queryFromMaster(lot_no)
    # first_result = results[0] if results else None
    try:
        pcs = int(first_result.PRO5) - int(first_result.PRO27)
    except:
        pcs = 0
    
    # first_result.PRO10 = 12321.1232
    # first_result.PRO11 = 1.5
    # first_result.PRO12 = 3.5

    if first_result:
        # size_str = f"{first_result.PRO10}x{first_result.PRO11}x{first_result.PRO12}";
        try:
            size_str = f"{float(first_result.PRO10):.2f}x{float(first_result.PRO11):.2f}x{float(first_result.PRO12):.2f}"
        except (ValueError, TypeError):
            size_str = "-"
        
        spec = f"{first_result.PRO13} {first_result.PRO14} {first_result.PRO15} {first_result.PRO16} {first_result.PRO17} {first_result.PRO18}"
    else:
        size_str = ""
        spec = ""

    mgt_code = first_result.PRO1 if first_result else "-"

    mks_code = convert_mgt_to_mks(mgt_code)
    code = f"{mks_code}（MKSコード）　　{mgt_code}（参照）"
    '''
    mks_map = MksCodeMap.objects.filter(mgt_code=mgt_code).first()
    if mks_map:
        code = f"{mks_map.mks_code}（MKSコード）　　{mgt_code}（参照）"
    else:
        code = f"{mgt_code}（参照）" or "-"
    '''
    # first_result.PRO_TOOL = None
    def get_tool_no(code_number: str) -> str:
        tool_map = {
            "547870587D": "A中 内径195",
            "547870585K": "仕上用",
            "547870586C": "粗",
            "547870586F": "A粗 内径195",
            "547870586H": "B粗",
            "547870587C": "チュウケンヨウ"
        }
        pprint(f"code_number {code_number}")        
        return tool_map.get(code_number, None)
    tn = get_tool_no(mgt_code)
    if tn is None:
        tn = first_result.PRO_TOOL if first_result and first_result.PRO_TOOL else "-"
# Helper function
    def safe_float(value):
        try:
            return float(value)
        except (TypeError, ValueError):
            return 0

    # MP45 or fallback MP34
    mp45_value = getattr(first_result, "MP45", None)
    mp45_or_34 = mp45_value if mp45_value not in (None, "") else getattr(first_result, "MP34", "")

    # MP49 or fallback MP39
    mp49_value = getattr(first_result, "MP49", None)
    mp49_or_39 = mp49_value if mp49_value not in (None, "") else getattr(first_result, "MP39", None)

    # Build first part (always present)
    part1 = (
        f"Hardness: {getattr(first_result, 'ob_Condition', '')}, "
        f"{mp45_or_34} "
        f"{safe_float(getattr(first_result, 'MI18', None)):.2f} - "
        f"{safe_float(getattr(first_result, 'MI19', None)):.2f} "
        f"({getattr(first_result, 'MI16', '')} {getattr(first_result, 'MI17', '')})"
    )

    # Build second part only if mp49_or_39 exists
    if mp49_or_39 not in (None, ""):
        part2 = (
            f" {mp49_or_39}: "
            f"{safe_float(getattr(first_result, 'MI22', None)):.2f} - "
            f"{safe_float(getattr(first_result, 'MI23', None)):.2f} "
            f"({getattr(first_result, 'MI20', '')} {getattr(first_result, 'MI21', '')})"
        )
    else:
        part2 = ""  # exclude second part entirely

    std_value = part1 + part2
    data = {
        # "code": first_result.PRO1 if first_result else "-",
        "code": code,
        "customer": f"{first_result.Japanese_CustomerName + ' 御中     ' if first_result and first_result.Japanese_CustomerName != '' else ''}{first_result.PRO1C if first_result else '-'}",
        # "inspect_date": inspect_date.strftime('%Y/%m/%d') if inspect_date else "-",
        "lot_no": lot_no,
        "size": size_str,
        "lot_size": pcs,
        "spec": spec,
        "std": std_value,
        # "hardness_out.acc": True,  # Hide rows 24 to 28 if the prefix is "0"
        # "hardness_out.spe_acc": False,  # Hide rows 24 to 28 if the prefix is "0"
        "acc": accept,  # Hide rows 24 to 28 if the prefix is "0"
        "spe_acc": specialAccept,  # Hide rows 24 to 28 if the prefix is "0"
        "tool": tn,
        # "hardness_out.qa1": f"{qa1.first_name} {qa1.last_name}",
        # "hardness_out.qa2": f"{qa2.first_name} {qa2.last_name}",
        "qa1": f"{qa1.first_name} {qa1.last_name}",
        "qa2": f"{qa2.first_name} {qa2.last_name}",
        "sign1": qa1.profile.signed_picture,
        "sign2": qa2.profile.signed_picture,
        "pos1": qa1.profile.get_position_display(),
        "pos2": qa2.profile.get_position_display()
    }
    merged_data = merge_sheet_data_with_data(converted_data, data)
    pprint(f"---- merged_data ---")
    pprint(merged_data)

    template_file = get_coi_template_file(md.get("export_template"))
    output_file = gen_xlsx(
        template_file=template_file,
        selected_sheets=sheets,  # Replace with your actual sheet names
        prefix_filename=f"{settings.BASE_DIR}/media/coi_{lot_no}_",
        data=merged_data
    )
    report = Report.objects.create(
        name=lot_no,
        created_by=user,
        file=None  # Leave this as None or assign a file if required
    )
    output_file_path = Path(output_file)  # Convert to a Path object for convenience
    with open(output_file_path, "rb") as f:
        report.file.save(output_file_path.name, File(f), save=True)

    pprint(f"outputfile = {output_file}")
    return report

def get_fields(model):
    # model_fields = {f.name: f for f in model._meta.get_fields()}
    # fields = list(model_fields.values())
    # return fields
    fields = [f for f in model._meta.get_fields() if not f.auto_created]
    return fields

def filter_by_lot_no(lot_no):
    models = [Data, DataMs, DataRl, DataWb, LotSummary, LotSummaryRl, LotSummaryWb, PressCal, RotateData ]  # List of models to process
    results = {}
    fields = {}

    for model in models:

        model_fields = [f.name for f in model._meta.get_fields()]
        # Check if "id" and "row_no" are in the model's fields
        order_fields = []
        if "id" in model_fields:
            order_fields.append("id")
        if "row_no" in model_fields:
            order_fields.append("row_no")
        # Dynamically filter and order results
        model_name = model.__name__
        if order_fields:
            results[model_name] = model.objects.filter(lot_no=lot_no).order_by(*order_fields)
        else:
            results[model_name] = model.objects.filter(lot_no=lot_no)  # No 
        
        fields[model_name] = get_fields(model)

    return results, fields

@login_required
def coi_view(request):
    pprint(f"xxxx method = xxx {request.method}")
    users = User.objects.all()
    export_template = "japanese"
    qa2_profile = UserProfile.objects.filter(qa2_default=True).select_related('user').first()
    qa2_default_user_id = qa2_profile.user.id if qa2_profile else ''

    if request.method == "POST":
        pprint(request.POST)
        exports = request.POST.getlist("exports")  # Retrieve the list of selected values
        pprint(f"Selected Export Options: {exports}")
        export_template = request.POST.get("export_template", "japanese")
        
        if 'export' in request.POST:

            data = {
                "customer": "Tum Coder",
                "inspect_date": "2025-01-15",
                "lot_no": "12345",
                "staff_name":  "Tum 8888",
                "man_name":  "Tum 999",
                "size": "Large",
                "lot_size": "10 pcs",
                "spec": "Spec-A",
                "hardness_out.d1_act": "10",
                "hardness_out.d2_act": "0[24:28]",  # Hide rows 24 to 28 if the prefix is "0"
                "hardness_out.acc": True,  # Hide rows 24 to 28 if the prefix is "0"
                "hardness_out.spe_acc": False,  # Hide rows 24 to 28 if the prefix is "0"
                "dimension_app.d1_act": "33",
                "dimension_app.d2_act": "0[26:32]",  # Hide rows 24 to 28 if the prefix is "0"
                "dimension_app.acc": True,  # Hide rows 24 to 28 if the prefix is "0"
                "dimension_app.spe_acc": True,  # Hide rows 24 to 28 if the prefix is "0"
            }
            output_file = gen_xlsx(
                template_file=get_coi_template_file(export_template),
                selected_sheets=exports,  # Replace with your actual sheet names
                prefix_filename="/app/media/coi",
                data=data
            )
            report = Report.objects.create(
                name=request.POST.get('lot_no','Untitled'),
                created_by=request.user,
                file=None  # Leave this as None or assign a file if required
            )
            output_file_path = Path(output_file)  # Convert to a Path object for convenience
            with open(output_file_path, "rb") as f:
                report.file.save(output_file_path.name, File(f), save=True)

            pprint(f"outputfile = {output_file}")

        if 'search_lot' in request.POST:
            lot_no = request.POST.get('lot_no', None)
            lot_no = lot_no.strip()
            if lot_no:
                results = queryFromMaster(lot_no)
                first_result = results[0] if results else None
                selected_templates = None
                try:
                    pcs = int(first_result.PRO5) - int(first_result.PRO27)
                except:
                    pcs = 0
                code = "-"
                if first_result:
                    size_str = f"{first_result.PRO10}x{first_result.PRO11}x{first_result.PRO12}";
                    spec = f"{first_result.PRO13} {first_result.PRO14} {first_result.PRO15} {first_result.PRO16} {first_result.PRO17} {first_result.PRO18}"
                    mapping = get_customer_template_mapping(first_result)
                    selected_templates = mapping.template_names if mapping else []
                    export_template = mapping.export_template if mapping else export_template

                    mgt_code = first_result.PRO1 if first_result else "-"
                    mks_code = convert_mgt_to_mks(mgt_code)
                    code = f"{mks_code}（MKSコード）　　{mgt_code}（参照）"
                    
                    '''
                    mks_map = MksCodeMap.objects.filter(mgt_code=mgt_code).first()
                    if mks_map:
                        code = f"{mks_map.mks_code}（MKSコード）　　{mgt_code}（参照）"
                    else:
                        code = f"{mgt_code}（参照）" or "-"
                    '''
                else:
                    size_str = ""
                    spec = ""
                results, fields = filter_by_lot_no(lot_no)
                # results1 = Data.objects.filter(lot_no=lot_no).order_by("id", "row_no")
                # fields1 =  get_fields(Data)

                # results2 = DataMs.objects.filter(lot_no=lot_no).order_by("id", "row_no")
                # fields2 =  get_fields(DataMs)




                return render(request, 'report/coi.html', {'result': first_result,
                                                           'pcs':pcs,
                                                           'size_str': size_str,
                                                           'lot_no': lot_no,
                                                           'spec': spec, 'users': users, 'SHEET_NAMES': SHEET_NAMES,
                                                           'results': results, 'fields': fields, 'selected_templates': selected_templates, 'code': code,
                                                           'export_template': export_template,
                                                           'current_user_id': request.user.id,
                                                           'qa2_default_user_id': qa2_default_user_id})

        messages.success(request, "Request Sent")
        return redirect(request.path_info)
    return render(request, 'report/coi.html', {'SHEET_NAMES': SHEET_NAMES, 'users': users,
                                               'export_template': export_template,
                                               'current_user_id': request.user.id,
                                               'qa2_default_user_id': qa2_default_user_id})


@csrf_exempt  # Disable CSRF for API requests (ensure this is secure in production)
@login_required
def gen_report_view(request):
    if request.method == "POST":
        # try:
            # Parse JSON data from the request body
            data = json.loads(request.body)
            lot_no = data.get("lot_no").strip()
            exports = data.get("exports")
            qa1 = data.get('qa1')
            qa2 = data.get('qa2')
            export_template = data.get("export_template")
            print(f"data = {data}")

            if not lot_no:
                return HttpResponseBadRequest("Missing 'lot_no' in request data")

            if not export_template:
                results = queryFromMaster(lot_no)
                first_result = results[0] if results else None
                mapping = get_customer_template_mapping(first_result)
                export_template = mapping.export_template if mapping else "japanese"

            # Call the `create_coi_file` function with the provided lot_no
            report = create_coi_file(lot_no, exports, request.user, {'qa1': qa1, 'qa2': qa2, \
                    'acceptStatus': data.get('acceptStatus'), 'export_template': export_template})

            # Return a success response with the report details
            return JsonResponse({
                "message": "Report generated successfully",
                "report_id": report.id,
                "file_url": report.file.url if report.file else None,
            })

        # except json.JSONDecodeError:
            # return HttpResponseBadRequest("Invalid JSON data")
        # except Exception as e:
            # pprint(e)
            # return JsonResponse({"error": str(e)}, status=500)
    else:
        return HttpResponseBadRequest("Only POST requests are allowed")



class CustomerTemplateCRUDView(ConfigurableCRUDView):
    model = CustomerTemplateMapping
    list_template_name = 'report/customer_template_list.html'
    detail_template_name = 'legacy/datacrud_detail.html'
    form_template_name = 'report/customer_template_form.html'
    confirm_delete_template_name = 'legacy/datacrud_confirm_delete.html'
    filterset_class = CustomerTemplateFilter

    page_title = "Customer Template Mapping"

    # URL name mappings
    list_url_name = 'report:customer_templates-list'
    create_url_name = 'report:customer_templates-create'
    update_url_name = 'report:customer_templates-update'
    delete_url_name = 'report:customer_templates-delete'
    config_fields = ["id", "customer_name", "export_template", "template_names", "created_at"] 
    config_field_orders = ["id", "customer_name", "export_template", "template_names", "created_at",  "created_by"]
    # config_readonly_fields = ["lot_no"]
    config_edit_fields = None
    ordering = ["-created_at", "-id",]
    form_class = CustomerTemplateMappingForm


    def get_list_view(self):
        class ListViewClass(FilterView, ListView):
            model = self.model
            template_name = self.list_template_name
            paginate_by = self.paginate_by
            filterset_class = self.filterset_class
            ordering = self.ordering

            def get_context_data(inner_self, **kwargs):
                context = super().get_context_data(**kwargs)
                fields = self.get_fields()
                context.update({
                    'fields': [f for f in fields],
                    'sheet_names': SHEET_NAMES,

                    # 'fields': [field for field in self.model._meta.get_fields()],
                    'page_title': self.page_title,
                    'list_url': self.list_url_name,
                    'create_url': self.create_url_name,
                    'update_url': self.update_url_name,
                    'delete_url': self.delete_url_name,
                    'bs': self.get_breadcrumbs('list'),
                })
                return context

        return ListViewClass



class ProductDrawingCRUDView(ConfigurableCRUDView):
    model = ProductDrawing
    list_template_name = 'legacy/datacrud_list.html'
    detail_template_name = 'legacy/datacrud_detail.html'
    form_template_name = 'report/productdrawing_form.html'
    confirm_delete_template_name = 'legacy/datacrud_confirm_delete.html'
    filterset_class = ProductDrawingFilter

    page_title = "Product Drawing"

    # URL name mappings
    list_url_name = 'report:product_drawings-list'
    create_url_name = 'report:product_drawings-create'
    update_url_name = 'report:product_drawings-update'
    delete_url_name = 'report:product_drawings-delete'
    config_fields = ["id", "code_no", "code_no_mks", "lot_no", "drawing", "description", "created_at"] 
    #config_field_orders = ["id", "customer_name", "template_names", "created_at",  "created_by"]
    # config_readonly_fields = ["lot_no"]
    config_edit_fields = None
    ordering = ["-created_at", "-id",]
    form_class = ProductDrawingForm


class MksCodeMapCRUDView(ConfigurableCRUDView):
    model = MksCodeMap
    page_title = "MKS Code Mapping"
    filterset_class = MksCodeMapFilter

    list_template_name = "legacy/datacrud_list.html"
    detail_template_name = "legacy/datacrud_detail.html"
    form_template_name = 'legacy/datacrud_form.html'
    confirm_delete_template_name = "legacy/datacrud_confirm_delete.html"

    config_fields = ["id", "mgt_code", "mks_code", "created_at"]
    config_edit_fields = ["mgt_code", "mks_code"]

    list_url_name = "report:code_maps-list"
    create_url_name = "report:code_maps-create"
    update_url_name = "report:code_maps-update"
    delete_url_name = "report:code_maps-delete"

    ordering = ["-created_at", "-id"]