QR-Code Reports

/////import query

        var createtemp = @"CREATE TABLE #temp
        (
            trnid  NVARCHAR(7) COLLATE DATABASE_DEFAULT,
            subtrnid NVARCHAR(3) COLLATE DATABASE_DEFAULT
        )";

        var inserttempquery = @"INSERT INTO #temp (trnid, subtrnid) VALUES (@trnid, @subtrnid)";

        var finalinsertquery = @"INSERT INTO QrSubTrnComparison (trnid, subtrnid, inventorytype, qty, qty1)
        SELECT DISTINCT trnid,subtrnid,'P' AS inventoryype, 0 AS qty, 0 AS qty1 FROM #temp";

        // need to discuss short close part in details
        var trnIdList = @"WITH A(trnid, subtrnid,balanceqty) AS
        (
            SELECT trnid,subtrnid, balanceqty FROM QrSubTrn
            WHERE ABS(balanceqty) > 0
        )";

        // Updated bal. for existing records
        var updateqtyquery = $@"{trnIdList}
        UPDATE QrSubTrnComparison SET qty = B.balanceqty FROM B
        WHERE QrSubTrnComparison.trnid = B.trnid AND QrSubTrnComparison.subtrnid = B.subtrnid";

        // Insert those records which is in System but not in comparison table
        var insertSystemRecords = $@"{trnIdList},
        C(trnid, subtrnid, balanceqty) AS
        (
            SELECT trnid, subtrnid, balanceqty FROM B
            WHERE NOT EXISTS (SELECT trnid, subtrnid FROM QrSubTrnComparison D
            WHERE B.trnid = D.trnid AND B.subtrnid = D.subtrnid )
        )
        INSERT INTO QrSubTrnComparison (trnid, subtrnid, inventorytype, qty, qty1)
        SELECT trnid, subtrnid, 'S' AS inventoryType, balanceqty FROM C";

WITH A (trnid, id, inventorytype) AS 
(
    -- Physical Stocks
    SELECT trnid, id, inventorytype 
    FROM QrSubTrnComparison
    WHERE inventorytype = 'P'

    UNION ALL 

    -- Added by System but trnid exists in Physical Stock
    SELECT trnid, id, inventorytype  
    FROM QrSubTrnComparison 
    WHERE inventorytype = 'S' 
      AND trnid IN (
            SELECT trnid 
            FROM QrSubTrnComparison 
            WHERE inventorytype = 'P'
      )
),
B (trnid) AS
(
    SELECT DISTINCT trnid FROM A
),
C (trnid, rowno) AS
(
    SELECT trnid,
           ROW_NUMBER() OVER (ORDER BY trnid) AS rowno
    FROM B
),
D (trnid, id, inventorytype, rowno, newrowno) AS
(
    SELECT A.trnid,A.id,A.inventorytype,
        C.rowno,
        IIF(A.inventorytype = 'P', C.rowno, C.rowno + 0.1) AS newrowno
    FROM A
    LEFT JOIN C ON A.trnid = C.trnid
),
E (maxrowno) AS 
(
    SELECT MAX(rowno) + 1 FROM C
),
F (trnid, id, inventorytype, newrowno) AS
(
    SELECT trnid,id,inventorytype,
    newrowno
    FROM D

    UNION ALL

    SELECT DS.trnid,DS.id,DS.inventorytype,
    E.maxrowno AS newrowno
    FROM QrSubTrnComparison DS
    LEFT JOIN E ON 1 = 1
    LEFT JOIN D ON DS.trnid = D.trnid
    WHERE DS.inventorytype = 'S'
    AND D.trnid IS NULL
),

/* =========================================================
   Document & Item Resolution using QRSubTrn
   ========================================================= */
G(trnid,itemid,docno,docdate,qty) AS
(
    /* ---------------- GRN ---------------- */
    SELECT QS.trnid,G1.itemid,G.grnno AS docno,G.dated AS docdate,
        SUM(QS.balanceqty) AS qty
    FROM QRSubTrn QS
    LEFT JOIN Grn1 G1 ON QS.trnid = G1.trnid
    LEFT JOIN Grn  G  ON G1.yeargrnno = G.yeargrnno
    WHERE LEFT(QS.trnid,1) = 'G'
    GROUP BY QS.trnid, G1.itemid, G.grnno, G.dated

    UNION ALL

    /* ---------------- MIRS ---------------- */
    SELECT QS.trnid,M1.itemid,M.jobno AS docno,M1.dated AS docdate,
    SUM(QS.balanceqty) AS qty
    FROM QRSubTrn QS
    LEFT JOIN Mirs1 M1 ON QS.trnid = M1.trnid
    LEFT JOIN Mirs  M  ON M1.yearjobno = M.yearjobno
    WHERE LEFT(QS.trnid,1) = 'M'
      AND M.isauth = 'Y'
    GROUP BY QS.trnid, M1.itemid, M.jobno, M1.dated

    UNION ALL

    /* ---------------- JOBWORK ---------------- */
    SELECT QS.trnid,J1.itemid,J.jobno AS docno,J1.anexdate AS docdate,
    SUM(QS.balanceqty) AS qty
    FROM QRSubTrn QS
    LEFT JOIN Jobwork1 J1 ON QS.trnid = J1.trnid
    LEFT JOIN Jobwork  J  ON J1.yearjobno = J.yearjobno
    WHERE LEFT(QS.trnid,1) = 'J'
    GROUP BY QS.trnid, J1.itemid, J.jobno, J1.anexdate

    UNION ALL

    /* ---------------- STOCK ADJUSTMENT ---------------- */
    SELECT QS.trnid,Q.itemid,Q.trnid AS docno,Q.dated AS docdate,
    SUM(QS.balanceqty) AS qty
    FROM QRSubTrn QS
    LEFT JOIN QRStock Q ON QS.trnid = Q.trnid
    WHERE LEFT(QS.trnid,1) = 'S'
    GROUP BY QS.trnid, Q.itemid, Q.trnid, Q.dated
),

/* =========================================================
   Final Presentation Layer
   ========================================================= */
H(itemname,trnid,docno,docdate,qty) AS
(
    SELECT I.itemname,CONCAT_WS('-', LEFT(F.trnid,1), RIGHT(F.trnid,6)) AS trnid,
    G.docno,G.docdate,G.qty
    FROM F
    LEFT JOIN G        ON F.trnid = G.trnid
    LEFT JOIN DprItem I ON G.itemid = I.itemid
)

SELECT itemname,trnid,docno,docdate,qty
FROM H
ORDER BY trnid;";