QR-Code Jobwork

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

---for roll back logic
CREATE TABLE ScannedSubTrn (
    id INT PRIMARY KEY IDENTITY(1,1),
    trnid NVARCHAR(7) ,
    subtrnid NVARCHAR(3) ,
    qty NUMERIC(12,3),
    -- ind NVARCHAR(1) ,
    yeardocno NVARCHAR(12) ,
    isrolledback NVARCHAR(1)
);

var query = @"WITH A (grn1id,itemid,qty,brandid,stage, value, storecode,transportercode,modecode,trnid) AS
(
    SELECT G1.id AS grn1id ,G1.itemid,G1.qty,I1.brandid,I1.stage,
    CAST(ROUND(I1.rate * G1.qty,2) AS NUMERIC (11,2)) AS value, G1.storecode,
    G.transportercode, T.modecode,G1.TrnId
    FROM GRN1 G1 
    LEFT JOIN indent1 I1 ON G1.indent1id = I1.id
    LEFT JOIN Grn G ON G1.yearGrnno = G.yearGrnno
    LEFT JOIN transporter T ON G.transportercode = T.transportercode  
    WHERE G1.yeargrnno = @yeargrnno
)
INSERT INTO jobwork1 (jobno,inout,anexno,anexdate,value,qty,refdate,itemid,stage,itemstate,weight,storecode,yearjobno,brandid,grn1id,transporter,mode,plandt,TrnId,QtyPerBox,NoOfPackets) 
(SELECT @jobno,@inout,@anexno,@anexdate,value,qty,@refdate,itemid,stage, @itemstate,qty AS weight,storecode,
@itemstate,qty AS weight,storecode,@yearjobno,brandid,grn1id,transportercode,modecode,@plandt,TrnId,@QtyPerBox,@NoOfPackets
FROM A)"
+ "SELECT CAST(SCOPE_IDENTITY() AS int)";


var updateQrSubTrnQty = @"WITH A(TrnId) AS 
        (SELECT TrnId FROM Grn1 WHERE yeargrnno = @yeargrnno)
        UPDATE QrSubTrn SET BalanceQty = 0 FROM A
        WHERE QrSubTrn.TrnId = A.TrnId";
        
var ScannedSubTrnInsert = @"WITH A (trnid,subtrnid, qty) AS 
        (
            SELECT Q.TrnId, subtrnid, balanceqty AS qty
            FROM Grn1 G1
            LEFT JOIN QrSubtrn Q ON G1.trnid = Q.TrnId
            WHERE yeargrnno = @yeargrnno 
        )
        INSERT INTO ScannedSubTrn (trnid, subtrnid, qty, yeardocno, isrolledback)
        SELECT trnid, subtrnid, qty, @yearjobno AS yeardocno, 'N' AS isrolledback
        FROM A";

public string RefTrnId { get; set; } = string.Empty;

// Update GetGrnDetails to include QR fields
public async Task<JobWork1> GetGrnDetails(int grn1Id, string dbname)
{
    var query = @"SELECT G1.grnno,G1.itemid,I0.shortname AS itemshortname,I1.brandid,B.brandname,
    '1' AS Itemstate, 'Good' AS Itemstatename,
    G1.qty AS grnqty,
    G1.storecode,S.storename, I1.stage AS stagecode,ST.stagename,G1.yeargrnno,
    G.transportercode, T.transportername,G1.TrnId
    FROM grn1 G1
    LEFT JOIN Grn G ON G1.yearGrnno = G.yearGrnno
    LEFT JOIN Inventorymst I0 ON G1.itemid=I0.itemid
    LEFT JOIN Indent1 I1 ON G1.indent1id = I1.id
    LEFT JOIN brandmst B ON I1.brandid = B.brandid
    LEFT JOIN storemst S ON G1.storecode = S.storecode
    LEFT JOIN stages ST ON I1.stage = ST.stage
    LEFT JOIN transporter T ON G.transportercode = T.transportercode
    WHERE G1.id = @grn1id ";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var data = await connection.QuerySingleOrDefaultAsync<JobWork1>(query, new { grn1Id });
    return data;
}

// Update JobWork1Create INSERT to include RefTrnId
public JobWork1 JobWork1Create(JobWork1 _JobWork1, string dbname, string isQrEnable)
{
    var query = @"INSERT INTO jobwork1 (jobno,inout,anexno,anexdate,value,qty,refno,refdate,itemid,stage,
    itemstate,weight,notes,Specialnote,storecode,yearjobno,grn1id,brandid,planno,targetqty,
    targetweight,boxes,mode,transporter,lrno,vehicledetails,plandt,TrnId,RefTrnId,QtyPerBox,NoOfPackets)
    VALUES (@jobno,@inout,UPPER(@AnexNo),@AnexDate,@value,@qty,@refno,@refdate,@itemid,@stage,
    @itemstate,@weight,TRIM(@notes),TRIM(@Specialnote),@storecode,@yearjobno,@grn1id,@brandid,@planno,
    @targetqty,@targetweight,@boxes,@mode,@transporter,@lrno,TRIM(@vehicledetails),@plandt,@TrnId,@RefTrnId,@QtyPerBox,@NoOfPackets)"
    + "SELECT CAST(SCOPE_IDENTITY() AS int)";

    // ... existing code ...
    // Note: No QrSubTrn creation for Part 2 (In Stock through GRN)

    return _JobWork1;
}

// Add to JobWork1ValidateCreateModify for GRN creation (InOutTypeCode == "1")
- No additional validations are needed since they are already handled in the GRN process before insertion into the table.

// Add to JobWork1Create method for GRN creation
if (_JobWork1.InOutTypeCode == "1" && _JobWork1.Grn1Id > 0)
{
    var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");
    var isQrEnable = "N";
    if (enableQr.IsActive == "Y")
    {
        var maxTrnId = _IJobWorkRepository.GetMaxTrnId(dbname);
        int trnIdLength = string.IsNullOrEmpty(maxTrnId) ? 6 : maxTrnId.Length;
        var nextTrnId = _IUtilityMethodsRepository.GetNextDocno(maxTrnId, trnIdLength);

        _JobWork1.TrnId = $"J{nextTrnId}";
        _JobWork1.RefTrnId = _JobWork1.TrnId; // This should be the G-TrnId from GRN
        // Note: QtyPerBox and NoOfPackets are already set from GRN data
        isQrEnable = "Y"; // Set to Y but QrSubTrn won't be created
    }

    _IJobWorkRepository.JobWork1Create(_JobWork1, dbname, isQrEnable);
}

@if(!string.IsNullOrEmpty(_JobWork1.TrnId))
{
    <tr>
        <th>Qty Per Box</th>
        <td>@_JobWork1.QtyPerBox</td>
    </tr>
    <tr>
        <th>No Of Packets</th>
        <td>@_JobWork1.NoOfPackets</td>
    </tr>
    <tr>
        <th>Trn Id</th>
        <td>@_JobWork1.TrnId</td>

        <tr>
            <th>Ref Trn Id</th>
            <td>@_JobWork1.RefTrnId</td>
        </tr>
}

// Add to JobWork1 class in ErpCrystal_MFG.Models/JobWork.cs
public string TrnId { get; set; } = string.Empty; // J-TrnId for JobWork
public string RefTrnId { get; set; } = string.Empty; // G-TrnId for GRN reference
public decimal QtyPerBox { get; set; }
public int NoOfPackets { get; set; }

// Update JobWork1Create to accept isQrEnable parameter
public JobWork1 JobWork1Create(JobWork1 _JobWork1, string dbname, string isQrEnable)
{
    // ... existing INSERT logic ...
    // Add TrnId, QtyPerBox, NoOfPackets to INSERT statement
    var query = @"INSERT INTO jobwork1 (jobno,inout,anexno,anexdate,value,qty,refno,refdate,itemid,stage,
    itemstate,weight,notes,Specialnote,storecode,yearjobno,grn1id,brandid,planno,targetqty,
    targetweight,boxes,mode,transporter,lrno,vehicledetails,plandt,TrnId,QtyPerBox,NoOfPackets)
    VALUES (@jobno,@inout,UPPER(@AnexNo),@AnexDate,@value,@qty,@refno,@refdate,@itemid,@stage,
    @itemstate,@weight,TRIM(@notes),TRIM(@Specialnote),@storecode,@yearjobno,@grn1id,@brandid,@planno,
    @targetqty,@targetweight,@boxes,@mode,@transporter,@lrno,TRIM(@vehicledetails),@plandt,@TrnId,@QtyPerBox,@NoOfPackets)";

    // ... existing code ...

    // Add QR Sub-Transaction creation
    if (isQrEnable == "Y" && _JobWork1.InOutTypeCode == "2")
    {
        QrSubTrnCreate(_JobWork1.TrnId, dbname);
    }

    return _JobWork1;
}

// Separate method for QR Sub-Transaction creation (following GRN pattern)
public void QrSubTrnCreate(string trnId, string dbname)
{
    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 J1.trnid, FORMAT(subtrnid,'000') AS subtrnid,
        ROUND(qty/CAST(@noofpacket AS INT),3) AS balanceqty
        FROM A
        LEFT JOIN JobWork1 J1 ON 1 = 1
        WHERE trnid = @jtrnid
    )
    INSERT INTO QrSubTrn (trnid, subtrnid, actualqty, balanceqty, isprinted)
    SELECT trnid, subtrnid, balanceqty, balanceqty, @isprinted
    FROM B
    ORDER BY CONCAT(trnid, subtrnid) ASC";

    using var connection = _DapperContext.SetClientConnection(dbname);
    var jobWork1Data = GetJobWork1Data(trnId, dbname);
    connection.Execute(insertQrSubTrn, new
    {
        jtrnid = trnId,
        noofpacket = jobWork1Data.NoOfPackets,
        isprinted = "N"
    });
}

// Helper method to get JobWork1 data by TrnId
public JobWork1 GetJobWork1Info(string trnId, string dbname)
{
    var query = @"SELECT TrnId,NoOfPackets FROM JobWork1 WHERE TrnId = @TrnId";
    using var connection = _DapperContext.SetClientConnection(dbname);
    return connection.QuerySingleOrDefault<JobWork1>(query, new { TrnId = trnId });
}

// Add to JobWork1ValidateCreateModify for manual creation (InOutTypeCode == "2")
if (_JobWork1.InOutTypeCode == "2" && _JobWork1.Grn1Id == 0)
{
    var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");
    if (enableQr.IsActive == "Y")
    {
        // Fetch QtyPerBox from Inventory (similar to GRN)
        var itemData = await _IJobWorkRepository.GetItemInfo(dbname, _JobWork1.ItemId);
        _JobWork1.QtyPerBox = itemData.QtyPerBox;

        if(_JobWork1.QtyPerBox < 1 || _JobWork1.QtyPerBox > 999999999.999m)
        {
            ModelState.AddModelError("QtyPerBox", "Qty Per Box needs to be filled and must be between 1 to 999999999.999");
        }
        else
        {
            var pendingPackets = _JobWork1.ActualQty % _JobWork1.QtyPerBox;
            if (pendingPackets != 0)
            {
                ModelState.AddModelError("QtyPerBox", "Qty must be an exact multiple of Qty per box");
            }
            else
            {
                var noOfPackets = _JobWork1.ActualQty / _JobWork1.QtyPerBox;
                if (noOfPackets > 300 || noOfPackets < 1)
                {
                    ModelState.AddModelError("QtyPerBox", "No of packets must be between 1 to 300");
                }
            }
        }
    }
}

// Add to JobWork1Create method for manual creation
if (_JobWork1.InOutTypeCode == "2" && _JobWork1.Grn1Id == 0)
{
    var enableQr = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "EnableQr");
    var isQrEnable = "N";
    if (enableQr.IsActive == "Y")
    {
        var maxTrnId = _IGrnRepository.GetMaxTrnId(dbname);
        int trnIdLength = string.IsNullOrEmpty(maxTrnId) ? 6 : maxTrnId.Length;
        var nextTrnId = _IUtilityMethodsRepository.GetNextDocno(maxTrnId, trnIdLength);

        _JobWork1.TrnId = $"J{nextTrnId}";
        _JobWork1.NoOfPackets = (int)(_JobWork1.ActualQty / _JobWork1.QtyPerBox);
        isQrEnable = "Y";
    }

    _IJobWorkRepository.JobWork1Create(_JobWork1, dbname, isQrEnable);
}

@if(enableQRSysParameter == "Y" && _JobWork1.InOutTypeCode == "2")
{
    <MudNumericField Label="Qty Per Box" @bind-Value="_JobWork1.QtyPerBox"
    For="@(() => _JobWork1.QtyPerBox)"/>
}

listdata = listdata.Where(x => x.Qty != "D").ToList();

    public async Task<Tuple<List<JobWork1Import>, string>> JobWork1OutChallanImportXl(List<JobWork1Import> listdata, string dbname, string userName, string yearLabel,JobWork1Import _JobWork1Import)
    {
        var tempcreation = @"CREATE TABLE #jobwork
        (
            id INT PRIMARY KEY IDENTITY(1,1),
            QrDescription NVARCHAR(500) COLLATE DATABASE_DEFAULT,
            qty NUMERIC(10,3),
            weight NUMERIC(10,3),
            value NUMERIC(12,2),
            trnid NVARCHAR(7) COLLATE DATABASE_DEFAULT,
            subtrnid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
            itemid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
            errorcode NVARCHAR(20) COLLATE DATABASE_DEFAULT,
            stage NVARCHAR(2) COLLATE DATABASE_DEFAULT,
            storecode NVARCHAR(2) COLLATE DATABASE_DEFAULT,
            brandid NVARCHAR(3) COLLATE DATABASE_DEFAULT,
            grn1id INT,
            updateWeight NVARCHAR(1) COLLATE DATABASE_DEFAULT
        )";

        var inserttemp = @"INSERT INTO #jobwork(QrDescription, qty, weight, value, trnid, subtrnid, updateweight)
        VALUES(@QrDescription, @qty, @weight, @value, @trnid, @subtrnid, @updateweight)";

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

        var updateWeight = @"UPDATE #jobwork
        SET weight = qty WHERE updateweight = 'Y'";

        // for preventing null values
        // var updateValue = @"UPDATE #jobwork
        // SET value = IIF(COALESCE(value, 0) = 0, 0, value)";

        var updateItemId = @"WITH A(trnid , itemid, stage, storecode, brandid, grn1id) AS
        (
            SELECT J1.TrnId, J1.ItemId, J1.stage, J1.storecode, J1.brandid, 0 AS grn1id
            FROM JobWork1 J1
            LEFT JOIN #jobwork ON J1.TrnId = #jobwork.TrnId

            UNION ALL

            SELECT G1.TrnId, G1.ItemId, I1.stage, G1.storecode, I1.brandid, G1.id AS grn1id
            FROM Grn1 G1
            LEFT JOIN Indent1 I1 ON G1.indent1id = I1.id
            LEFT JOIN #jobwork ON G1.TrnId = #jobwork.TrnId
            
            UNION ALL 
            
            SELECT Q.TrnId, Q.ItemId, 
            Q.stagecode, Q.storecode, Q.brandid, 0 AS grn1id
            FROM QRStock Q
            LEFT JOIN #jobwork ON Q.TrnId = #jobwork.TrnId  
            
            ---Need to add for IIRS once it is added
        )
        UPDATE #jobwork SET itemid = A.itemid, stage = A.stage, storecode = A.storecode, 
        brandid = A.brandid, grn1id = A.grn1id
        FROM A
        WHERE #jobwork.trnid = A.trnid";

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

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

        // Error Code C: Transaction ID / SubTrn ID has zero balance
        var errorcodeC = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' C'
        FROM #jobwork JW
        INNER JOIN QrSubTrn Q ON JW.trnid = Q.trnid AND JW.subtrnid = Q.subtrnid
        WHERE Q.balanceqty = 0";

        // Error Code D: Entered Qty must be less than the available balance Qty
        var errorcodeD = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' D'
        FROM #jobwork JW
        LEFT JOIN QrSubTrn Q ON JW.trnid = Q.trnid AND JW.subtrnid = Q.subtrnid
        WHERE JW.qty > Q.balanceqty";
        
        // Error Code E: weight must be between **0** and **9,999,999.99
        var errorcodeE = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' E'
        FROM #jobwork
        WHERE (weight NOT BETWEEN 0 AND 999999.999)";
        
        // Error code F: Trnid and subtrnid must be unique
        var errorcodeF = @"WITH A(trnid, subtrnid) AS
        (
            SELECT trnid, subtrnid
            FROM #jobwork
            GROUP BY trnid,subtrnid
            HAVING COUNT(*) > 1
        )
        UPDATE #jobwork
        SET errorcode = COALESCE(RTRIM(errorcode), '') + ' F'
        FROM A
        WHERE #jobwork.trnid = A.trnid AND #jobwork.subtrnid = A.subtrnid";

        // Error code G: HSN Code for this item is empty.
        var errorcodeG = @"WITH A(tempid) AS
        (
            SELECT J.id AS tempid
            FROM #jobwork J
            LEFT JOIN InventoryMst I ON J.itemid = I.itemid
            WHERE COALESCE(hsncode, '') = ''
        )
        UPDATE #jobwork
        SET errorcode = COALESCE(RTRIM(errorcode), '') + ' G'
        FROM A
        WHERE #jobwork.id = A.tempid";

        // Error Code H: Issue Qty, weight must be between **1** and **9,999,999.99
        var errorcodeH = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' H'
        FROM #jobwork
        WHERE (qty NOT BETWEEN 1 AND 999999.999)";

        // Error Code I: value must be a number between **00.1** and **9,999,999.99
        var errorcodeI = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' I'
        FROM #jobwork
        WHERE value NOT BETWEEN 0.01 AND 999999999.99";
        
        // errorcode J : Jtrnid series already exists 
        var errorcodeJ = @"UPDATE #jobwork SET errorcode = COALESCE(RTRIM(ERRORCODE), '') + ' J'
        FROM #jobwork
        WHERE EXISTS (SELECT trnid FROM jobwork1 J1 WHERE #jobwork.trnid = J1.trnid AND yearjobno = @yearjobno AND inout = '0' )";

        var cnterror = @"SELECT COUNT(Errorcode) AS CNT FROM #jobwork
        WHERE COALESCE(ERRORCODE,'') != ''";

        var data = @"SELECT JW.QrDescription, JW.qty,JW.weight,JW.Value ,JW.trnid AS jobworktrnid, JW.subtrnid,
        COALESCE(JW.errorcode,'') AS errorcode
        FROM #jobwork JW
        ORDER BY JW.id ASC";

        var scannedsubtrninsert = @"INSERT INTO ScannedSubTrn (trnid, subtrnid, qty, yeardocno, isrolledback)
        SELECT trnid, subtrnid, qty, @yearjobno AS yeardocno, 'N' AS isrolledback
        FROM #jobwork";

        // var trnidquery = @"WITH A (trnid, itemid )AS
        // (
        //     SELECT DISTINCT trnid,itemid FROM #jobwork
        // ),
        // B(rowno,trnid,itemid)AS
        // (
        //     SELECT ROW_NUMBER() OVER (ORDER BY trnid,itemid)AS Rowno,trnid,itemid FROM A
        // ),
        // C(trnid,itemid,rowno)AS
        // (
        //     SELECT trnid,itemid,FORMAT(COALESCE(@maxtrnid,'000000') + B.rowno,'000000')AS rowno FROM B
        // )
        // UPDATE #jobwork SET jobworktrnid= C.rowno FROM C 
        // WHERE #jobwork.trnid=C.trnid AND #jobwork.Itemid = C.itemid";


        var jobworkinsert = @"WITH A (trnid,itemid,stage,storecode,brandid,grn1id,
        totalvalue,totalqty,totalweight,rn) AS
        (
            SELECT trnid,itemid,stage,storecode,brandid,
            grn1id,SUM(value) AS totalvalue,
            SUM(qty) AS totalqty,SUM(weight) AS totalweight,ROW_NUMBER() OVER (ORDER BY trnid) AS rn
            FROM #jobwork
            GROUP BY trnid,itemid,stage,storecode,brandid,grn1id
        ),
        B(trnid,itemid,totalvalue,stage,storecode,brandid,grn1id,
        totalqty,totalweight,rn) AS
        (
            SELECT A.trnid,A.itemid,A.totalvalue,A.stage,A.storecode,A.brandid,
            A.grn1id,A.totalqty,A.totalweight,rn
            FROM A
        )
        INSERT INTO JobWork1 
        (
            jobno, inout, anexno, anexdate,value, qty, itemid, stage, itemstate,
            weight, notes, yearjobno,storecode, brandid, grn1id,
            transporter, mode, lrno, vehicledetails,
            plandt, TrnId, Boxes,noofpackets,qtyperbox
        )
        SELECT @jobno,'0' AS inout ,@anexno,@anexdate,B.totalvalue,B.totalqty,B.itemid,B.stage,'1' AS itemstate ,
        B.totalweight,IIF(B.rn = 1, @notes, ''),@yearjobno,B.storecode,B.brandid,B.grn1id,@transporter,
        @mode,@lrno,@vehicledetails,@plandt,trnid,@boxes,@noofpackets,@qtyperbox
        FROM B;";

        //RemoveColumns
        var removeColumns = @"UPDATE jobwork1 SET coid = @coid, grnno = @grnno, instock = @instock, 
        tariffcode = @tariffcode, ismanualvalue = @ismanualvalue, yeargrnno = @yeargrnno, reversed = @reversed 
        WHERE yearjobno = @yearjobno";

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

        var lognotes = @"WITH A (trnid) AS
        (
            SELECT DISTINCT trnid FROM #jobwork WHERE COALESCE(errorcode,'') = ''
        )
        SELECT STRING_AGG(trnid,' ') AS notes FROM A";

        

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

        connection.Execute(inserttemp, 
            listdata.Select(dataLine => new
            {
                dataLine.QrDescription,
                SubTrnId = dataLine.QrDescription.Trim().Substring(8, 3),
                TrnId = dataLine.QrDescription.Trim()[..7],
                qty = dataLine.Qty,
                weight = dataLine.Weight,
                value = dataLine.Value,
                updateWeight = dataLine.UpdateWeight
            })
        );

        connection.Execute(updateQty);
        connection.Execute(updateItemId);
        
        var jobWork = GetJobWorkInfo(_JobWork1Import.MainId,dbname);
        var balanceType = jobWork.BalanceType;

        connection.Execute(updateWeight);
        // connection.Execute(updateValue);


        connection.Execute(errorcodeA);
        connection.Execute(errorcodeB);
        connection.Execute(errorcodeC);
        connection.Execute(errorcodeD);
        connection.Execute(errorcodeE);
        connection.Execute(errorcodeF);
        connection.Execute(errorcodeG);
        connection.Execute(errorcodeH);
        connection.Execute(errorcodeI);
        connection.Execute(errorcodeJ, new{yearjobno = _JobWork1Import.YearJobNo});

        var errorcount = connection.QuerySingleOrDefault<int>(cnterror);
        string? msg;
        msg = "Import could not succeed because of errors in input";
        if (errorcount == 0)
        {
            connection.Execute(scannedsubtrninsert, new { yearjobno =  _JobWork1Import.YearJobNo }); 
            
            connection.Execute(jobworkinsert,
            new
            {
                yearjobno = _JobWork1Import.YearJobNo,
                anexno = _JobWork1Import.AnnexNo,
                anexdate = _JobWork1Import.AnnexureDate,
                transporter = _JobWork1Import.TransporterCode,
                mode = _JobWork1Import.TransportModeCode,
                lrno = _JobWork1Import.LrNo,
                vehicledetails = _JobWork1Import.VehicleDetails,
                notes = _JobWork1Import.Notes,
                plandt = DateTime.Now,
                jobno = _JobWork1Import.JobNo,
                boxes = 0,
                noofpackets = 0, 
                qtyperbox = 0
            });
            connection.Execute(removeColumns, new
            {
                coid = "01",
                grnno = "",
                instock = "N",
                tariffcode = "",
                ismanualvalue = "Y",
                yeargrnno = "",
                reversed = "N",
                _JobWork1Import.YearJobNo
            });
            connection.Execute(updateQrSubTrnQty);

            var notes = connection.QuerySingleOrDefault<string>(lognotes);
            _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, "000000", "JobWork", "Out Challan Import", yearLabel, notes);
            msg = "JobWork Out Challan import succeeded.";
        }
        var finaldata = connection.Query<JobWork1Import>(data);
        var data1 = finaldata.ToList();

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

var scannedsubtrninsert = @"INSERT INTO ScannedSubTrn (trnid, subtrnid, qty, yeardocno, isrolledback)
SELECT trnid, subtrnid, qty, @yearjobno AS yeardocno, 'N' AS isrolledback
FROM ##JobWork";

    public void JobWork1OutChallanRollback(string yearJobNo, string dbname, string trnId)
    {
        var trnIdWhereClause = trnId == "0" ? "" : "AND trnid = @trnId";
        var rollbackQuery = $@"
            WITH A (trnid, subtrnid, qty) AS
            (
                SELECT S.trnid, S.subtrnid, S.qty AS qty
                FROM ScannedSubTrn S
                WHERE S.YearDocNo = @yearJobNo AND S.IsRolledBack = 'N'
                {trnIdWhereClause}
            )
            UPDATE QrSubTrn
            SET BalanceQty = BalanceQty + A.qty
            FROM A
            WHERE QrSubTrn.TrnId + QrSubTrn.SubTrnId = A.TrnId + A.SubTrnId";

            var updateSubTrn = $@"UPDATE ScannedSubTrn
            SET IsRolledBack = 'Y'
            WHERE YearDocNo = @yearJobNo {trnIdWhereClause}";

        using var connection = _DapperContext.SetClientConnection(dbname);
        connection.Execute(rollbackQuery, new { yearJobNo, trnId });
        connection.Execute(updateSubTrn, new{yearJobNo,trnId});
    }