Quality Control - [QC Entry]

QC_Main (Header)
   ↓
QC_Line (Item Group Level)
   ↓
QC_Parameter (Parameter Level – only for system/mix mode)

CREATE TABLE QualityControl
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    qcno NVARCHAR(6),
    qcDate DATETIME,
    yearqcno NVARCHAR(12),
    qcStage NVARCHAR(3), -- IQC (Incoming), PQC (In process), FQC (Final)
    refDoc NVARCHAR(20), -- GRN (Currently restricted)
    refNo NVARCHAR(6),
    refDate DATETIME,
    mode NVARCHAR(1), -- S (System), M (Manual), X (Mix)
    qcStatus NVARCHAR(1), -- O (Open), C (Closed), P (Partial)
    checkedBy NVARCHAR(25),
    approvedBy NVARCHAR(25)
);

CREATE TABLE QualityControl1
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    qcno NVARCHAR(6),
    yearqcno NVARCHAR(12),
    itemgrpid NVARCHAR(3),
    testplancode NVARCHAR(6), -- (0 for Manual)
    reportNo NVARCHAR(20),
    reportDate DATETIME,
    itemQcStatus NVARCHAR(1),  -- Pending (N), P (Pass), F (Fail)
    remarks NVARCHAR(150)
);

CREATE TABLE QualityControl2
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    qcno NVARCHAR(6),
    yearqcno NVARCHAR(12),
    itemgrpid NVARCHAR(3),
    parameterCode NVARCHAR(6),
    actualValue DECIMAL(7,3),
    result NVARCHAR(1) -- Pending (N), P (Pass), F (Fail)
);

public int GetRefDocExistCnt(string refNo, DateTime? refDate, string dbname)
{
    var query = @"SELECT COUNT(*) FROM Grn 
    WHERE grnNo = @refNo AND CONVERT(DATE, dated) = @refDate";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var count = connection.QuerySingleOrDefault<int>(query, new { refNo, refDate = refDate?.Date });
    return count;
}

public int GetDuplicateQcCount(string refNo, DateTime? refDate, string refDoc, int id, string dbname)
{
    var query = @"SELECT COUNT(*) FROM QualityControl
    WHERE refNo = @refNo AND CONVERT(DATE, refdate) = @refDate
    AND refDoc = @refDoc AND Id != @id";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var count = connection.QuerySingleOrDefault<int>(query, new { refNo, refDate = refDate?.Date, refDoc, id });
    return count;
}

public int GetRefDocLineCount(string refNo, DateTime? refDate, string dbname)
{
    var query = @"SELECT COUNT(*) FROM Grn1 G1
    LEFT JOIN Grn G ON G1.yeargrnno = G.yeargrnno
    WHERE G.grnno = @refNo AND CONVERT(DATE, dated) = @refDate
    AND COALESCE(trnid, '') != ''";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var count = connection.QuerySingleOrDefault<int>(query, new { refNo, refDate = refDate?.Date });
    return count;
}

public string GetItemGroupsPlanStatus(string refNo, DateTime? refDate, string qcStageCode, string dbname)
{
    var query = @"WITH A (itemgroupcode, hastestplancnt, recordcnt) AS
    (
        SELECT DISTINCT I.itemgroupid AS ItemGroupCode, IIF(TP.testPlanCode IS NULL, 0, 1) AS hastestplancnt, 1 AS recordcnt
        FROM Grn1 G1
        LEFT JOIN Grn G ON G1.yeargrnno = G.yeargrnno
        LEFT JOIN inventorymst I ON G1.itemid = I.itemid
        LEFT JOIN TestPlanMst TP ON I.itemgroupid = TP.itemgroupid AND TP.qcStage = @qcStageCode AND TP.isactive = 'Y'
        WHERE G.grnno = @refNo AND CONVERT(DATE, dated) = @refDate
        AND COALESCE(trnid, '') != ''
    ),
    B (hastestplancnt, recordcnt) AS
    (
        SELECT SUM(hastestplancnt) AS hastestplancnt,
        SUM(recordcnt) AS recordcnt FROM A
    )
    SELECT IIF(hastestplancnt = 0, 'M',
    IIF(hastestplancnt = recordcnt, 'S', 'X')) AS ind FROM B";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = connection.QuerySingleOrDefault<string>(query, new { refNo, refDate = refDate?.Date, qcStageCode });
    return data;
}

public int GetTestPlanLineCount(string refNo, DateTime? refDate, string qcStageCode, string dbname)
{
    var query = @"WITH A (itemgroupid, lineCnt, recordcnt) AS
    (
        SELECT DISTINCT
        I.itemgroupid,
        IIF
        (
            EXISTS
            (
            SELECT 1
            FROM TestPlanMst1 TP1
            WHERE TP1.testPlanCode = TP.testPlanCode
            ),
            1,
            0
        ) AS lineCnt,
        1 AS recordcnt
        FROM Grn1 G1
        LEFT JOIN Grn G ON G1.yeargrnno = G.yeargrnno
        LEFT JOIN inventorymst I ON G1.itemid = I.itemid
        LEFT JOIN TestPlanMst TP ON I.itemgroupid = TP.itemgroupid AND TP.qcStage = @qcStageCode AND TP.isactive = 'Y'
        WHERE G.grnno = @refNo AND CONVERT(DATE, dated) = @refDate
        AND COALESCE(trnid, '') != ''
        AND TP.testplancode IS NOT NULL
    ),
    B (lineCnt, recordcnt) AS
    (
        SELECT SUM(lineCnt) AS lineCnt,
        SUM(recordcnt) AS recordcnt FROM A
    )
    SELECT IIF(lineCnt < recordCnt, 0, 1) AS cnt FROM B";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = connection.QuerySingleOrDefault<int>(query, new { refNo, refDate = refDate?.Date, qcStageCode });
    return data;
}

public List<QualityControl1> GetRefDocItemGroups(string refNo, DateTime? refDate, string qcStageCode, string dbname)
{
    var query = @"SELECT DISTINCT I.itemgroupid AS ItemGroupCode,
    COALESCE(TP.testPlanCode, '0') AS testPlanCode
    FROM Grn1 G1
    LEFT JOIN Grn G ON G1.yeargrnno = G.yeargrnno
    LEFT JOIN inventorymst I ON G1.itemid = I.itemid
    LEFT JOIN TestPlanMst TP ON I.itemgroupid = TP.itemgroupid AND TP.qcStage = @qcStageCode AND TP.isactive = 'Y'
    WHERE  G.grnno = @refNo AND CONVERT(DATE, dated) = @refDate
    AND COALESCE(trnid, '') != ''";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = connection.Query<QualityControl1>(query, new { refNo, refDate = refDate?.Date, qcStageCode });
    return data.ToList();
}

public async Task<Tuple<List<QualityControl2>, string>> QualityControl2BulkUpdate(int qc1Id, List<QualityControl2> listData, string dbname, string yearLabel, string userName, string itemGroupCode)
{
    var tempCreate = @"CREATE TABLE #temp
    (
        Id INT,
        parametercode NVARCHAR(6) COLLATE DATABASE_DEFAULT,
        parametername NVARCHAR(100) COLLATE DATABASE_DEFAULT,
        parametertypecode NVARCHAR(1) COLLATE DATABASE_DEFAULT,
        parametertypename NVARCHAR(10) COLLATE DATABASE_DEFAULT,
        value NVARCHAR(10) COLLATE DATABASE_DEFAULT,
        actualvalue NUMERIC(7,3),
        result NVARCHAR(1) COLLATE DATABASE_DEFAULT
    )";

    var tempinsertion = @"INSERT INTO #temp (Id,parametercode,parametername,parametertypecode,parametertypename,value,actualvalue) 
    VALUES (@Id,@parametercode,@parametername,@parametertypecode,@parametertypename,@value,0)";

    var errorCodeA = @"WITH A (LineCnt) AS 
    (
        SELECT COUNT(*) AS LineCnt
        FROM #temp
    ),
    B (LineCnt) AS 
    (
        SELECT COUNT(*) AS LineCnt
        FROM QualityControl2 Q2
        LEFT JOIN QualityControl1 Q1 ON Q2.yearqcno = Q1.yearqcno AND Q2.itemgrpid = Q1.itemgrpid
        WHERE Q1.id = @qc1Id
    ),
    C (LineCnt) AS 
    (
        SELECT (LineCnt * -1) AS LineCnt FROM A
        UNION ALL
        SELECT LineCnt FROM B

    )
    SELECT SUM(LineCnt) FROM C";

    var errorCodeB = @"WITH A (id) AS 
    (
        SELECT Q2.id
        FROM QualityControl2 Q2
        LEFT JOIN QualityControl1 Q1 ON Q2.yearqcno = Q1.yearqcno AND Q2.itemgrpid = Q1.itemgrpid
        WHERE Q1.id = @qc1Id
    ),
    B (id) AS 
    (
        SELECT id FROM A WHERE NOT EXISTS (SELECT id FROM #temp WHERE A.id = #temp.id)
    )
    SELECT COUNT(*) FROM B";

    var updateQc2TempSystemStatus = @"WITH A (result, actualvalue, id) AS
    (
        SELECT IIF(Z.value BETWEEN Q.minvalue AND Q.maxvalue, 'P', 'F') AS Result,
        Z.value AS actualvalue, Z.id
        FROM #temp Z
        LEFT JOIN QualityParameter Q ON Z.parametercode = Q.parametercode
        WHERE Z.parametertypecode = 'S'
    )
    UPDATE #temp SET actualvalue = A.actualvalue, result = A.result
    FROM A
    WHERE #temp.id = A.id";

    var updateQc2TempUserStatus = @"WITH A (result, id) AS
    (
        SELECT Z.value AS result, Z.id
        FROM #temp Z
        WHERE Z.parametertypecode = 'U'
    )
    UPDATE #temp SET result = A.result
    FROM A
    WHERE #temp.id = A.id";

    var updateQc2Status = @"UPDATE QualityControl2 SET result = Z.result,
    actualvalue = Z.actualvalue
    FROM #temp Z
    WHERE QualityControl2.id = Z.id";

    var updateQc1Status = @"WITH A (itemqcstatus) AS
    (
        SELECT IIF(SUM(IIF(Q2.result != 'P', 1, 0)) = 0, 'P', 'F') AS itemqcstatus
        FROM QualityControl2 Q2
        LEFT JOIN QualityControl1 Q1 ON Q2.yearqcno = Q1.yearqcno AND Q2.itemgrpid = Q1.itemgrpid
        WHERE Q1.id = @qc1Id
    )
    UPDATE QualityControl1 SET itemqcstatus = A.itemqcstatus
    FROM A
    WHERE QualityControl1.id = @qc1Id";

    var updateQcStatus = @"WITH A (qcStatus) AS
    (
        SELECT IIF(SUM(IIF(Q1.itemqcstatus = 'N', 1, 0)) = 0, 'C', 'P') AS qcStatus
        FROM QualityControl1 Q1
        WHERE Q1.YearQcNo = @YearQcNo
    )
    UPDATE QualityControl SET qcStatus = A.qcStatus
    FROM A
    WHERE QualityControl.yearqcno = @YearQcNo";

    var finalSelect = @"SELECT id, parametercode, parametername, parametertypecode, parametertypename, value,
    actualvalue, IIF(result = 'P', 'Pass', 'Fail') AS resultname
    FROM #temp
    ORDER BY Id ASC";

    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Open();

    connection.Execute(tempCreate);

    connection.Execute(tempinsertion, listData.Select(item => new
    {
        item.Id,
        item.ParameterCode,
        item.ParameterName,
        item.ParameterTypeCode,
        item.ParameterTypeName,
        item.Value
    }));

    var errorcodeACnt = connection.QuerySingleOrDefault<int>(errorCodeA, new { qc1Id });
    var errorcodeBCnt = connection.QuerySingleOrDefault<int>(errorCodeB, new { qc1Id });

    string msg;
    string errorCodeMsg = string.Empty;
    if (errorcodeACnt != 0)
    {
        errorCodeMsg = "Model has been altered. Records are missing. Please re-download the model.";
    }
    else if (errorcodeBCnt != 0)
    {
        errorCodeMsg = "Model has been altered. Records are not matching. Please re-download the model.";
    }
    if (errorcodeACnt == 0 && errorcodeBCnt == 0)
    {
        connection.Execute(updateQc2TempSystemStatus);
        connection.Execute(updateQc2TempUserStatus);
        connection.Execute(updateQc2Status);
        connection.Execute(updateQc1Status, new { qc1Id });
        var data = QualityControl1Details(qc1Id, dbname).Result;
        connection.Execute(updateQcStatus, new { data.YearQcNo });
        _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, "", "QualityControl", "Bulk Update", yearLabel, $"Updated Parameters for Item Group : {itemGroupCode}");
        msg = "Bulk update Succeeded.";
    }
    else
    {
        msg = $"{errorCodeMsg} Bulk update failed due to validation errors.";
    }

    var resultData = await connection.QueryAsync<QualityControl2>(finalSelect);
    var resultList = resultData.ToList();

    return new Tuple<List<QualityControl2>, string>(resultList, msg);
}

IF Manual → user input
IF System:
    IF any FAIL → F
    ELSE IF all PASS → P
    ELSE → N

IF all QC1 = PASS → C
IF all QC1 = Pending → O
ELSE → P

IF ANY sample fails → Item Group FAIL

using System.ComponentModel.DataAnnotations;

namespace ErpCrystal_MFG.Models;

public class QualityControl
{
    public int Id { get; set; }
    public string QcNo { get; set; } = string.Empty;
    public DateTime? QcDate { get; set; } = DateTime.Now;
    public string YearQcNo { get; set; } = string.Empty;
    [Required(ErrorMessage = "QC Stage needs to be selected")]
    public string QcStageCode { get; set; } = string.Empty;
    public string QcStageName { get; set; } = string.Empty;
    [Required(ErrorMessage = "Ref Doc needs to be selected")]
    public string RefDoc { get; set; } = string.Empty;
    [Required(ErrorMessage = "Ref No needs to be filled")]
    [StringLength(6, ErrorMessage = "Ref No can be upto 6 digits only")]
    public string RefNo { get; set; } = string.Empty;
    public DateTime? RefDate { get; set; } = DateTime.Now;
    [Required(ErrorMessage = "QC Mode needs to be selected")]
    public string QcModeCode { get; set; } = string.Empty;
    public string QcModeName { get; set; } = string.Empty;
    public string QcStatusCode { get; set; } = string.Empty;
    public string QcStatusName { get; set; } = string.Empty;
    public string CheckedBy { get; set; } = string.Empty;
    public string ApprovedBy { get; set; } = string.Empty;
    public string DeleteReason { get; set; } = string.Empty;
}

public class QualityControl1
{
    public int Id { get; set; }
    public string QcNo { get; set; } = string.Empty;
    public string YearQcNo { get; set; } = string.Empty;
    public string ItemGroupCode { get; set; } = string.Empty;
    public string ItemGroupName { get; set; } = string.Empty;
    public string TestPlanCode { get; set; } = string.Empty;
    public string TestPlanName { get; set; } = string.Empty;
    public int MainId { get; set; }
    [Required(ErrorMessage = "Report No needs to be filled")]
    [StringLength(20, ErrorMessage = "Report No can be upto 20 characters only")]
    public string ReportNo { get; set; } = string.Empty;
    public DateTime? ReportDate { get; set; } = DateTime.Now;
    public string ItemQcStatusCode { get; set; } = string.Empty;
    public string ItemQcStatusName { get; set; } = string.Empty;
    [StringLength(150, ErrorMessage = "QA Remark can be upto 150 characters only")]
    public string Remarks { get; set; } = string.Empty;
}

public class QualityControl2
{
    public int Id { get; set; }
    public string QcNo { get; set; } = string.Empty;
    public string YearQcNo { get; set; } = string.Empty;
    public string ItemGroupCode { get; set; } = string.Empty;
    public string ItemGroupName { get; set; } = string.Empty;
    public string ParameterCode { get; set; } = string.Empty;
    public string ParameterName { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
    public decimal ActualValue { get; set; }
    public string ResultCode { get; set; } = string.Empty;
    public string ResultName { get; set; } = string.Empty;
    public string ParameterTypeCode { get; set; } = string.Empty;
    public string ParameterTypeName { get; set; } = string.Empty;
    public string ErrorCode { get; set; } = string.Empty;
}

public class QualityControlPrint
{
    public string YearQcNo { get; set; } = string.Empty;
    public string QcNo { get; set; } = string.Empty;
    public DateTime? QcDate { get; set; } = DateTime.Now;
    public string QcStageName { get; set; } = string.Empty;
    public string RefDoc { get; set; } = string.Empty;
    public string RefNo { get; set; } = string.Empty;
    public DateTime? RefDate { get; set; } = DateTime.Now;
    public string QcModeName { get; set; } = string.Empty;
    public string QcStatusName { get; set; } = string.Empty;
    public string PdfFileName { get; set; } = string.Empty;
    // QC 1
    public string ItemGroupName { get; set; } = string.Empty;
    public string TestPlanName { get; set; } = string.Empty;
    public string ReportNo { get; set; } = string.Empty;
    public DateTime? ReportDate { get; set; } = DateTime.Now;
    public string ItemQcStatusName { get; set; } = string.Empty;
    public string Remarks { get; set; } = string.Empty;
    // QC 2
    public string ParameterName { get; set; } = string.Empty;
    public string ParameterTypeCode { get; set; } = string.Empty;
    public string ParameterTypeName { get; set; } = string.Empty;
    public string ActualValue { get; set; } = string.Empty;
    public string ResultName { get; set; } = string.Empty;
    public string CheckedBy { get; set; } = string.Empty;
    public string ApprovedBy { get; set; } = string.Empty;
}