QR-Code IIRS

ALTER TABLE Mirs1 ADD TrnId NVARCHAR(7),
QtyPerBox NUMERIC (12,3),
NoOfPackets INT;

[HttpPost("{dbname},{yearLabel},{userName}")]
public ActionResult Iirs1Create(Iirs1 _Iirs1, string dbname, string yearLabel, string userName)
{
    if (ModelState.IsValid)
    {
        var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");
        var isQrEnable = "N";
        if (enableQr.IsActive == "Y")
        {
            var maxTrnId = _IIirsRepository.GetMaxTrnId(dbname);
            int trnIdLength = string.IsNullOrEmpty(maxTrnId) ? 6 : maxTrnId.Length;
            var nextTrnId = _IUtilityMethodsRepository.GetNextDocno(maxTrnId, trnIdLength);

            _Iirs1.TrnId = $"M{nextTrnId}";
            _Iirs1.NoOfPackets = (int)(_Iirs1.Qty1 / _Iirs1.QtyPerBox);
            isQrEnable = "Y";
        }
        else
        {
            _Iirs1.QtyPerBox = 0;
            _Iirs1.NoOfPackets = 0;
        }
        _IIirsRepository.Iirs1Create(_Iirs1, dbname, isQrEnable);
        _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, _Iirs1.IirsNo, "IIRS", "Add line", yearLabel, "");

    }

    return Ok(_Iirs1);
}

// Insert Query add trnid,qtyperbox,noofpackets
var query = @"INSERT INTO mirs1 (jobno, dated, inout, condition, itemid, stage, 
qty1, shiftid, machineid, processid, notes,
yearjobno, brandid, storecode,qcok,trnid,qtyperbox,noofpackets) 
VALUES (@jobno, @dated, @inout, @condition, @itemid, @stage, @qty1, @shiftid, @machineid,
@processid, TRIM(@notes), @yearjobno, @brandid, @storecode,@qcok,@trnid,@qtyperbox,@noofpackets); 
" + "SELECT CAST(SCOPE_IDENTITY() as int)";

// If is QrEnable is Y then execute this query
var insertQrSubTrn = @"WITH A(subtrnid) AS
(
    SELECT TOP (CAST(@noofpacket AS INT))
    ROW_NUMBER() OVER (ORDER BY name) AS subtrnid
    FROM master.dbo.spt_values
),
B(trnid, subtrnid, balanceqty) AS
(
    SELECT @mtrnid AS trnid, FORMAT(subtrnid,'000') AS subtrnid,
    ROUND(@qty1/CAST(@noofpacket AS INT),3) AS balanceqty
    FROM A
)
INSERT INTO QrSubTrn (trnid, subtrnid, actualqty, balanceqty, isprinted)
SELECT trnid, subtrnid, balanceqty, balanceqty, @isprinted
FROM B
ORDER BY CONCAT(trnid, subtrnid) ASC";

if (isQrEnable == "Y")
{
    connection.Execute(insertQrSubTrn, new
    {
        mtrnid = _Iirs1.TrnId,
        noofpacket = _Iirs1.NoOfPackets,
        qty1 = _Iirs1.Qty1,
        isprinted = "N"
    });
}

public decimal GetItemStgPkg(string itemid, string dbname)
{
    var query = @"SELECT Stdpkg AS qtyperbox
    FROM inventorymst
    WHERE itemid = @itemid ";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = connection.QuerySingleOrDefault<decimal>(query, new { itemid });
    return data;
}

// Main Level
if (_Iirs.IirsTypeCode == "O" && enableQr == "Y")
{
    scannedSubTrnCnt = await _IIirsService.GetScannedSubTrnCnt(0, _Iirs.YearIirsNo, _PostLogin.dbname);
}

// Line Level
var scannedSubTrnCnt = 1; // dummy value
if (_Iirs.IirsTypeCode == "O" && enableQr == "Y")
{
    scannedSubTrnCnt = await _IIirsService.GetScannedSubTrnCnt(_Iirs1.Id, _Iirs.YearIirsNo, _PostLogin.dbname);
}

// Repository Query
public int GetScannedSubTrnCnt(int id, string yearIirsNo, string dbname)
{
    string? query;
    if (id == 0)
    {
        query = @"SELECT COUNT(*) FROM Mirs1 M1
        WHERE NOT EXISTS (SELECT trnId FROM ScannedSubTrn S
        WHERE docType = 'M' AND S.yeardocno = @yearIirsNo
        AND S.isrolledback = 'N' AND M1.id = S.docId)
        AND yearjobno = @yearIirsNo";
    }
    else
    {
        query = @"SELECT COUNT(*) FROM ScannedSubTrn
        WHERE docType = 'M' AND yeardocno = @yearIirsNo AND docId = @id
        AND isrolledback = 'N'";
    }

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = connection.QuerySingleOrDefault<int>(query, new { id, yearIirsNo });
    return data;
}

[HttpDelete("{id},{dbname},{userName},{yearLabel}")]
public void IirsDelete(int id, string dbname, string userName,string yearLabel,[FromBody] DeleteRequest req)
{
    var data = _IIirsRepository.GetIirsInfo(id, dbname);
    var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");

    if (enableQr.IsActive == "Y" && data.IirsTypeCode == "I")
    {
        _IQrPrintRepository.SubTrnDelete(dbname, data.YearIirsNo, "0", "M");
    }

    if (enableQr.IsActive == "Y" && data.IirsTypeCode == "O")
    {
        _IIirsRepository.RollBack(0, data.YearIirsNo, dbname);
    }
    _IIirsRepository.IirsDelete(data.YearIirsNo, dbname);
    _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, data.IirsNo, "IIRS", "Delete", yearLabel, req.DeleteReason);
}

[HttpDelete("{id},{dbname},{userName},{yearLabel}")]
public void Iirs1Delete(int id, string dbname, string userName, string yearLabel,[FromBody] DeleteRequest req)
{
    var data = _IIirsRepository.GetIirs1Info(id, dbname);
    var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");
    if (enableQr.IsActive == "Y" && !string.IsNullOrEmpty(data.TrnId))
    {
        _IQrPrintRepository.SubTrnDelete(dbname, "0", data.TrnId.Replace("-", ""), "M");
    }

    if (enableQr.IsActive == "Y" && string.IsNullOrEmpty(data.TrnId))
    {
        _IIirsRepository.RollBack(id, "0", dbname);
    }
    _IIirsRepository.Iirs1Delete(id, dbname);
    _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, data.IirsNo, "IIRS", "Delete line", yearLabel, req.DeleteReason);

}

public void RollBack(int docId, string yearIirsNo, string dbname)
{
    var IdWhereClause = docId == 0 ? "AND yeardocno = @yearIirsNo" : "AND docid = @docId";

    var query = $@"WITH A (trnid, subtrnid, qty) AS
    (
        SELECT S.trnid, S.subtrnid, SUM(S.qty) AS qty
        FROM ScannedSubTrn S
        WHERE docType = 'M'
        AND S.isrolledback = 'N'
        {IdWhereClause}
        GROUP BY S.trnid, S.subtrnid
    )
    UPDATE QrSubTrn SET balanceqty = balanceqty + qty FROM A 
    WHERE QrSubTrn.trnid+QrSubTrn.subtrnid = A.trnid+A.subtrnid";

    var query1 = $@"UPDATE ScannedSubTrn SET isrolledback = 'Y'
    WHERE docType = 'M'
    {IdWhereClause}";

    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Execute(query, new { docId, yearIirsNo });
    connection.Execute(query1, new { docId, yearIirsNo });
} 

public async Task<Tuple<List<Iirs1>, string>> Iirs1ImportIssueQr
(int mainId, List<Iirs1> listdata, string dbname, string machineCode, string shiftCode, string processCode, string userName, string yearLabel)
{
    var tempcreate = @"CREATE TABLE #temp
    (
        id INT PRIMARY KEY IDENTITY(1,1),
        qrdescription NVARCHAR (500)  COLLATE DATABASE_DEFAULT,
        trnid NVARCHAR (7) COLLATE DATABASE_DEFAULT,
        subtrnid NVARCHAR (3) COLLATE DATABASE_DEFAULT,
        qty NUMERIC(12,3),
        errorcode NVARCHAR(50) COLLATE DATABASE_DEFAULT,
        itemid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
        stagecode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
        brandid NVARCHAR(3) COLLATE DATABASE_DEFAULT
    )";

    var tempinsert = @"INSERT INTO #temp(QrDescription, qty, trnid, subtrnid)
    VALUES(@QrDescription, @qty, @trnid, @subtrnid)";

    var updateQty = @"UPDATE #temp SET qty = Q.balanceqty
    FROM QrSubTrn Q
    WHERE #temp.trnid = Q.trnid AND #temp.subtrnid = Q.subtrnid AND COALESCE(#temp.qty, 0) = 0";

    var updateOtherFields = @"WITH A(trnid , itemid, stagecode, brandid) AS
    (
        SELECT G1.TrnId, G1.ItemId,
        I1.stage AS stagecode, I1.brandid
        FROM #temp Z
        LEFT JOIN Grn1 G1 ON Z.TrnId = G1.TrnId
        LEFT JOIN indent1 I1 ON G1.indent1id = I1.id
        WHERE LEFT(Z.trnid,1) = 'G'

        UNION ALL 

        SELECT J1.TrnId, J1.ItemId,
        J1.stage AS stagecode, J1.brandid
        FROM #temp Z
        LEFT JOIN Jobwork1 J1 ON Z.TrnId = J1.TrnId
        WHERE LEFT(Z.trnid,1) = 'J'

        UNION ALL 

        SELECT M1.TrnId, M1.ItemId,
        M1.stage AS stagecode, M1.brandid
        FROM #temp Z
        LEFT JOIN Mirs1 M1 ON Z.TrnId = M1.TrnId
        WHERE LEFT(Z.trnid,1) = 'M'

        UNION ALL 

        SELECT S.TrnId, S.ItemId,
        S.stagecode, S.brandid
        FROM #temp Z
        LEFT JOIN QrStock S ON Z.TrnId = S.TrnId
        WHERE LEFT(Z.trnid,1) = 'S'
    )
    UPDATE #temp SET itemid = A.itemid,
    stagecode = A.stagecode, brandid = A.brandid
    FROM A
    WHERE #temp.trnid = A.trnid";

    // Error Code A: Only GRN, JobWork, Mirs, and Stock transactions
    var errorcodeA = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(errorcode),'')+ 'A'
    WHERE LEFT(trnid, 1) NOT IN ('G','J','M','S')";

    // Error Code B: Invalid Transaction ID or Sub-Transaction ID
    var errorcodeB = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' B' 
    WHERE NOT EXISTS (SELECT trnid + subtrnid FROM QrSubTrn 
    WHERE #temp.trnid + #temp.subtrnid = QrSubTrn.trnid + QrSubTrn.subtrnid)";

    // Error Code C: SubTrn ID has zero balance
    var errorcodeC = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' C'
    WHERE EXISTS (SELECT trnid + subtrnid FROM QrSubTrn 
    WHERE #temp.trnid + #temp.subtrnid = QrSubTrn.trnid + QrSubTrn.subtrnid
    AND QrSubTrn.balanceqty = 0)";

    // Error code D: Trnid and subtrnid must be unique
    var errorcodeD = @"WITH A(trnid, subtrnid) AS 
    (
        SELECT trnid, subtrnid
        FROM #temp
        GROUP BY trnid,subtrnid
        HAVING COUNT(*) > 1
    )
    UPDATE #temp
    SET errorcode = COALESCE(RTRIM(errorcode), '') + ' D'
    FROM A
    WHERE #temp.trnid = A.trnid AND #temp.subtrnid = A.subtrnid";

    // Error Code E: Issue Qty must be between 1 and 999999.999
    var errorcodeE = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' E'
    FROM #temp 
    WHERE qty NOT BETWEEN 1 AND 999999.999";

    // Error Code F: Issue Qty cannot be more than balance Qty
    var errorcodeF = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' F'
    FROM QrSubTrn Q 
    WHERE #temp.trnid = Q.trnid AND #temp.subtrnid = Q.subtrnid
    AND #temp.qty > Q.balanceqty";

    // Error Code G: Iirs Unit and Transaction Document Unit is not matching
    var errorcodeG = @"WITH A(id, unitcode) AS
    (
        SELECT Z.id, G.unitcode
        FROM #temp Z
        LEFT JOIN Grn1 G1 ON Z.TrnId = G1.TrnId
        LEFT JOIN Grn G ON G1.yeargrnno = G.yeargrnno
        WHERE LEFT(Z.trnid,1) = 'G'

        UNION ALL 

        SELECT Z.id, J.Unitcode
        FROM #temp Z
        LEFT JOIN Jobwork1 J1 ON Z.TrnId = J1.TrnId
        LEFT JOIN jobwork J ON J1.yearjobno = J.yearjobno 
        WHERE LEFT(Z.trnid,1) = 'J'

        UNION ALL 

        SELECT Z.id, M.Unitcode
        FROM #temp Z
        LEFT JOIN Mirs1 M1 ON Z.TrnId = M1.TrnId
        LEFT JOIN mirs M ON M1.yearjobno = M.yearjobno
        WHERE LEFT(Z.trnid,1) = 'M'

        UNION ALL 

        SELECT Z.id, S.unitcode
        FROM #temp Z
        LEFT JOIN QrStock S ON Z.TrnId = S.TrnId
        WHERE LEFT(Z.trnid,1) = 'S'
    )
    UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' G'
    FROM A
    WHERE A.unitcode != @unitcode AND #temp.id = A.id AND COALESCE(A.unitcode,'') != ''";

    // Error Code H: Rejected Stock Items cannot be issued.
    var errorcodeH = @"UPDATE #temp SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' H'
    WHERE EXISTS (SELECT S.TrnId FROM QrStock S WHERE #temp.TrnId = S.TrnId
    AND S.itemstate != 'G') 
    AND LEFT(#temp.trnid,1) = 'S'";

    var cnterrorcode = @"SELECT COUNT(errorcode) AS CNT FROM #temp 
    WHERE COALESCE(errorcode,'') != ''";

    var data = @"SELECT Z.QrDescription, Z.qty AS qty1,
    COALESCE(Z.errorcode,'') AS errorcode
    FROM #temp Z
    ORDER BY Z.id ASC";

    var newTempCreate = @"CREATE TABLE #NewMirs 
    (
        mirsid INT,
        itemid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
        brandid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
        stagecode NVARCHAR(2) COLLATE DATABASE_DEFAULT
    )";

    var finalinsert = @"WITH A (itemid, stagecode, brandid, qty1) AS 
    (
        SELECT itemid, stagecode, brandid, SUM(qty) AS qty1
        FROM #temp
        GROUP by itemid, stagecode, brandid
    )
    INSERT INTO Mirs1 (jobno, dated, inout, condition,
    itemid, stage, shiftid, machineid, processid, notes, qty1, yearjobno,
    brandid, storecode, qcok, qtyperbox, noofpackets, trnid)
    OUTPUT INSERTED.Id, INSERTED.itemid, INSERTED.brandid, INSERTED.stage
    INTO #NewMirs
    SELECT @jobno, @dated, @inout, @condition,
    itemid, stagecode, @shiftid, @machineid, @processid, @notes, qty1, @yearjobno,
    brandid, @storecode, @qcok, @qtyperbox, @noofpackets, @trnid
    FROM A"; 

    // var updateRemovedColumns = @"UPDATE mirs1 SET mkrckrno = @mkrckrno, qty2 = @qty2,
    // rmbatchcode = @rmbatchcode, locked = @locked, partyid = @partyid
    // FROM #temp
    // WHERE Mirs1.yearjobno = @yearjobno"; 

    // var insertScannedSubTrn = @"WITH A(trnid, subtrnid, qty,docid) AS 
    // (
    //     SELECT Z.trnid, Z.subtrnid, Z.qty, M1.id AS docid FROM #temp Z
    //     LEFT JOIN Mirs1 M1 ON Z.itemid = M1.itemid AND Z.stagecode = M1.Stage AND Z.brandid = M1.brandid
    //     WHERE NOT EXISTS (SELECT docId FROM ScannedSubTrn S
    //     WHERE M1.id = S.docid AND S.doctype = 'M' AND S.yeardocno = @yearjobno)
    //     AND M1.id IS NOT NULL AND M1.yearjobno = @yearjobno
    // )
    // INSERT INTO ScannedSubTrn (docType, yeardocno, trnid, subtrnid, qty, isrolledback, docid)
    // SELECT 'M', @yearjobno, trnid, subtrnid, qty, 'N', docid FROM A";

    var insertScannedSubTrn = @"INSERT INTO ScannedSubTrn (docType, yeardocno, trnid, subtrnid, qty, isrolledback, docid)
    SELECT 'M', @yearjobno, trnid, subtrnid, qty, 'N',  N.MirsId 
    FROM #temp T
    JOIN #NewMirs N ON N.itemid = T.itemid AND N.brandid = T.brandid AND N.stagecode = T.stagecode;";

    var updateQrSubTrnQty = @"WITH A(trnid, subtrnid, qty) AS 
    (
        SELECT trnid, subtrnid, qty FROM #temp
    )
    UPDATE QrSubTrn SET BalanceQty = BalanceQty - A.qty FROM A
    WHERE QrSubTrn.TrnId + QrSubTrn.SubTrnId = A.TrnId + A.subtrnid";

    using var connection = _DapperContext.SetClientConnection(dbname);
    connection.Open();
    connection.Execute(tempcreate);
    foreach (var dataLine in listdata)
    {
        connection.Execute(tempinsert, new
        {
            dataLine.QrDescription, 
            qty = dataLine.QrIssueQty,
            trnid = dataLine.QrDescription.Trim().Substring(0, 7),
            subtrnid = dataLine.QrDescription.Trim().Substring(8, 3)
        });
    }

    connection.Execute(updateQty);
    connection.Execute(updateOtherFields);

    connection.Execute(errorcodeA);
    connection.Execute(errorcodeB);
    connection.Execute(errorcodeC);
    connection.Execute(errorcodeD);
    connection.Execute(errorcodeE);
    connection.Execute(errorcodeF);
    var iirsInfo = GetIirsInfo(mainId, dbname);
    connection.Execute(errorcodeG, new { iirsInfo.UnitCode });
    connection.Execute(errorcodeH);

    var errorcount = connection.QuerySingleOrDefault<int>(cnterrorcode);
    string? msg;
    if (errorcount == 0)
    {
        connection.Execute(newTempCreate);
        connection.Execute(finalinsert , 
        new 
        { 
            jobno = iirsInfo.IirsNo,
            dated = DateTime.Now.Date,
            inout = iirsInfo.IirsTypeCode,
            condition = "G",
            shiftid = shiftCode,
            machineid = machineCode,
            processid = processCode,
            notes = "",
            yearjobno = iirsInfo.YearIirsNo,
            storecode = iirsInfo.MainLocation,
            qcok = "N",
            qtyperbox = 0, 
            noofpackets = 0,
            trnid = ""
        });

        // string? nullValue = null;

        // connection.Execute(updateRemovedColumns  , 
        // new 
        // { 
        //     mkrckrno = nullValue,
        //     qty2 = 0,
        //     rmbatchcode = nullValue,
        //     locked = nullValue,
        //     partyid = nullValue,
        //     yearjobno = iirsInfo.YearIirsNo,
        // });

        connection.Execute(insertScannedSubTrn, new { yearjobno = iirsInfo.YearIirsNo });
        connection.Execute(updateQrSubTrnQty);

        _IUtilityMethodsRepository.InsertMFGLog(dbname, yearLabel, iirsInfo.IirsNo, "IIRS", "Issue QR Import", userName, "");
        msg = "Import Successfully";
    }
    else
    {
        msg = "Import could not succeeded";
    }

    var dataQuery = await connection.QueryAsync<Iirs1>(data);
    var data1 = dataQuery.ToList();

    return new Tuple<List<Iirs1>, string>(data1, msg);
}