Audit Trail - JV

CREATE TABLE AuditTrail 

(
    id INT PRIMARY KEY IDENTITY(1,1),
    AuditTrailNo NVARCHAR(8),---generated by the system
    DocTypeCode NVARCHAR(5),---for eg. V, , DNCN1
    yearDocNo NVARCHAR(12),----for eg.YearVoucherNo
    Operationcode NVARCHAR(2),-----for Eg. Modify, Delete, recreated
    operationDate DATETIME,-----On what date it was Modified or delete
    username NVARCHAR(50),----who deleted/Modify
    Notes NVARCHAR(250)---user will mention reason for delete
);

CREATE TABLE AuditTrail1 (
    id INT PRIMARY KEY IDENTITY(1,1),
    AuditTrailNo NVARCHAR(8),--for joining purpose
    displayname NVARCHAR(100),----what will be visible to the user
    oldvalue NVARCHAR(500),
    newvalue NVARCHAR(500),
    updatedBy NVARCHAR(1),---- ind will be U and S (user and System)
);
-----used only for modify

CREATE TABLE AuditTrailMetaData 
(
    id INT PRIMARY KEY IDENTITY(1,1),
    tableName NVARCHAR(50),--- will be the table name
    propertyname NVARCHAR(50),---name which has been used in the Property
    displayname NVARCHAR(100),-- will be the name which is visible to the user for eg Voucher Amount
    ind NVARCHAR(3),---->pending to add description
    coulmnname NVARCHAR(50),---->pending to add description
    internalcolumn NVARCHAR(50)---> for deleting purpose
);


CREATE TABLE AuditTrailDocType 
(
    id INT PRIMARY KEY IDENTITY(1,1),
    docType NVARCHAR(4),--- for eg B (for Bill), V(for Voucher)
    docTypeName NVARCHAR(50),---for eg Bill, Voucer
);

---FOR PARTYMST 
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('I', 'Invoice ')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('B', 'Bill')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('D', 'Debit/Credit Note')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('V', 'Voucher')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('J', 'Journal Voucher ')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('A', 'Charts of Account')
INSERT INTO AuditTrailDocType (docType, docTypeName)
VALUES ('P', 'PartyMst')


---JV
INSERT INTO AuditTrailMetaData (tablename, propertyname, displayname, ind, internalcolumn)
VALUES 
('journalvoucher', 'UnitCode', 'Unit Code', 'MJV', 'unitcode'),
('journalvoucher', 'dated', 'JV Date', 'MJV', 'dated'),
('journalvoucher', 'debitamount', 'Debit Amount', 'MJV', 'debit'),
('journalvoucher', 'creditamount', 'Credit Amount', 'MJV', 'credit'),
('journalvoucher', 'jvtypename', 'JV Type', 'MJV', 'jvtype'),
('journalvoucher', 'divisioncode', 'Division Code', 'MJV', 'divisionid'),
('journalvoucher1', 'drcrname', 'Debit/Credit Type', 'LJV', 'drcr'),
('journalvoucher1', 'refno', 'Ref No', 'LJV', 'refno'),
('journalvoucher1', 'refdate', 'Ref Date', 'LJV', 'refdate'),
('journalvoucher1', 'amount', 'Amount', 'LJV', 'amount'),
('journalvoucher1', 'mainacnamecode', 'Account Head', 'LJV', 'mainac'),
('journalvoucher1', 'subacnamecode', 'Sub Account Head', 'LJV', 'subac'),
('journalvoucher1', 'refdocname', 'Ref Doc', 'LJV', 'refdoc');

INSERT INTO sysparameters (shortname, name, syscondition, dated, isactive, docno, status) VALUES ('AuditTrail', 'Enable Audit Trail', 'Y', null, 'Y', null, null)

ALTER TABLE Audittrail1 ADD docId INT

--------Audit Trail Index------------------------------
SELECT AuditTrailNo, docTypeName,
        CONCAT(SUBSTRING(yeardocno, 1, 4), '-', SUBSTRING(yeardocno, 5, 2), '/', RIGHT(yeardocno, 6)) AS yeardocNo, 
        IIF(Operationcode = 'RC', 'Re-Created', 
        IIF(Operationcode = 'MM', 'Modify Main', 
            IIF(Operationcode = 'ML', 'Modify Line', 
                IIF(Operationcode = 'DM', 'Delete Main', 'Delete Line')
            )
        )
        ) AS operationname, operationDate, username, A.id
        FROM AuditTrail A 
        LEFT JOIN AuditTrailDocType AD ON A.DocTypeCode = AD.docType
        ORDER BY A.id DESC

--------------Audit Trail Details-----------------------
SELECT AuditTrailNo, docTypeName,
        CONCAT(SUBSTRING(yeardocno, 1, 4), '-', SUBSTRING(yeardocno, 5, 2), '/', RIGHT(yeardocno, 6)) AS yeardocNo, 
        IIF(Operationcode = 'RC', 'Re-Created', 
        IIF(Operationcode = 'MM', 'Modify Main', 
            IIF(Operationcode = 'ML', 'Modify Line', 
                IIF(Operationcode = 'DM', 'Delete Main', 'Delete Line')
            )
        )
        ) AS operationname, operationDate, username, A.id, A.Notes
        FROM AuditTrail A 
        LEFT JOIN AuditTrailDocType AD ON A.DocTypeCode = AD.docType
        WHERE A.id = @id

------------------Audit Trail1 Index----------------------------------
SELECT AuditTrailNo, oldvalue, newvalue, displayname, 
        IIF(updatedby = 'U', 'user', 'System') AS updatedby, id
        FROM 
        AuditTrail1
        WHERE audittrailNo = @audittrailno
        ORDER BY id DESC

//------------------------------------HOW TO ADD IN CREATE FEATURE----------------------------------------

        [HttpPost("{yearLabel},{dbname},{userName}")]
        public ActionResult JournalVoucherCreate(JournalVoucher _JournalVoucher, string yearLabel, string dbname, string userName)
        {
            if (ModelState.IsValid)
            {
                var maxJvNo = _IJournalVoucherRepository.GetMaxJvNo(dbname, yearLabel);
                int jvNoLength = string.IsNullOrEmpty(maxJvNo) ? 6 : maxJvNo.Length;
                var nextJvNo = _IUtilityMethodsRepository.GetNextDocno(maxJvNo, jvNoLength);
                _JournalVoucher.JvNo = nextJvNo;

                _JournalVoucher.YearJvNo = $"{yearLabel.Replace("-", "")}{_JournalVoucher.JvNo}";

                _IJournalVoucherRepository.JournalVoucherCreate(_JournalVoucher, dbname);

                var auditTrailSysparameter = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "AuditTrail");
                if(auditTrailSysparameter.IsActive == "Y")
                {          
                    //--------------------AUDIT TRAIL BLOCK-------------------------------------------
                    var yearDocNoCnt = _IAuditTrailRepository.GetYearDocNoCnt(_JournalVoucher.YearJvNo, dbname, "J");

                    if(yearDocNoCnt > 0)
                    {
                        var nextAuditTrailNo = _AuditTrailController.GetNextAuditTrailNo(dbname);

                        // assigning values for main level audit trail create
                        AuditTrail _AuditTrail = new()
                        {
                            AuditTrailNo = nextAuditTrailNo,
                            DocTypeCode = "J",
                            YearDocNo = _JournalVoucher.YearJvNo,
                            UserName = userName,
                            OperationCode = "RC",
                            Notes = $"Re-created by {userName}",
                        };
                        
                        _IAuditTrailRepository.AuditTrailCreate(dbname, _AuditTrail);// for creating main level
                        _IAuditTrailRepository.AuditTrail1DynamicCreate(dbname, _AuditTrail.YearDocNo, "journalvoucher", _AuditTrail.AuditTrailNo, "YearJvNo", data.Id);// for creating line level
                    }
                }

                _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, _JournalVoucher.JvNo, "JV", "Add", yearLabel, "");

            }
            return Ok(_JournalVoucher);
        }

--- to check record exist or not for delete feature 
--Paramteres : Yeardocno - eg. PartyId, YearVoucherNo etc..., docTypeCode = P for PartyMst, V for Voucher, OperationCode = D(for Delete)
SELECT COUNT(*) FROM audittrail
WHERE yeardocno = @yeardocno AND 
doctypecode = @doctypecode AND operationCode = 'DM'

-- to insert at main level
-- Paramteres : 
-- auditrailNo - getting from getnextaudittrailno, 
-- docTypeCode = P for PartyMst, V for Voucher, 
-- OperationCode = RC(Re-Created),
-- OperationDate = DateTime.Now 
-- UserName - coming from postlogin
-- Notes = $"Recreated by {_AuditChange.UserName}"

INSERT INTO AuditTrail(AuditTrailNo,DocTypeCode,yearDocNo, OperationCode, operationDate, username, Notes) 
        VALUES (@AuditTrailNo,@DocTypeCode, @yearDocNo, @Operationcode, @operationDate, @username, @Notes)

        "DECLARE 
		@columns NVARCHAR(MAX),
		@castedColumns NVARCHAR(MAX),
		@query NVARCHAR(MAX);

	    -- Step 1: Build column list dynamically based on tablename
	    SELECT 
		@columns = STRING_AGG(internalcolumn, ', '),
		@castedColumns = STRING_AGG('CAST(' + internalcolumn + ' AS NVARCHAR(MAX)) AS ' + internalcolumn, ', ')
	    FROM AuditTrailMetaData
	    WHERE tablename = @tableName;

	    -- Step 2: Build the dynamic SQL
	    SET @query = 'WITH A (dynamicdocno, ' + @columns + ') AS
        (
		    SELECT  ' + QUOTENAME(@tableYearDocNameId) + ' AS dynamicdocno, '  + @castedColumns + '
		    FROM {tableName}
	    ),
	    B(internalcolumn, value) AS 
        (
		    SELECT internalcolumn, value
		    FROM A
		    UNPIVOT (value FOR internalcolumn IN (' + @columns + ')) AS U
		    WHERE dynamicdocno = @yearDocNoId
	    )
	    INSERT INTO audittrail1 (audittrailno, displayname, oldvalue, updatedby, docid)
	    SELECT @audittrailno, M.displayname, B.value, @updatedby, @docid
	    FROM B
	    JOIN AuditTrailMetaData M ON M.internalcolumn = B.internalcolumn 
	    AND M.tablename = @tableName;';

        -- Execute the dynamic SQL
        EXEC sp_executesql @query, 
        N'@yearDocNoId NVARCHAR(MAX), @audittrailno NVARCHAR(MAX), @tableName NVARCHAR(MAX), @updatedby NVARCHAR(10)', 
        @yearDocNoId, @audittrailno, @tableName, 'U', @docid";

        [HttpPut("{dbname},{yearlabel},{username}")]
        public ActionResult JournalVoucher1Modify(JournalVoucher1 _JournalVoucher1, string dbname, string yearlabel, string username)
        {
            if (ModelState.IsValid)
            {
                // picking old values for audit trail 
                JournalVoucher1 _LineOldValues = _IJournalVoucherRepository.JournalVoucher1Details(_JournalVoucher1.Id, dbname).Result;

                _JournalVoucher1.RefNo = !string.IsNullOrEmpty(_JournalVoucher1.RefNo) ? _JournalVoucher1.RefNo.PadLeft(6, '0') : "";
                _IJournalVoucherRepository.JournalVoucher1Modify(_JournalVoucher1, dbname);

                JournalVoucher _MainOldValues = new();
                AuditTrail _AuditTrail = new();

                var auditTrailSysparameter = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "AuditTrail");
                if(auditTrailSysparameter.IsActive == "Y")
                {
                    //------------------------AUDIT TRAIL BLOCK------------------------------------------------------------------------
                    // need to call to fetch new values else it will not consider combo values
                    JournalVoucher1 _LineNewValues = _IJournalVoucherRepository.JournalVoucher1Details(_JournalVoucher1.Id, dbname).Result;

                    //calling detect changes for audit trail to detect changes made
                    var detectedChanges = _AuditTrailController.DetectChanges(_LineOldValues, _LineNewValues, dbname, "Journalvoucher1");

                    if (detectedChanges.Count != 0)//only going forward if change is done
                    {
                        //assigning the values 
                        _AuditTrail.DocTypeCode = "J";
                        _AuditTrail.YearDocNo = _JournalVoucher1.YearJvNo;
                        _AuditTrail.UserName = username;
                        _AuditTrail.OperationCode = "ML";
                        _AuditTrail.Notes = _JournalVoucher1.AuditTrailNotes;
                        _AuditTrail.AuditTrailNo = _AuditTrailController.GetNextAuditTrailNo(dbname);
                    
                        _IAuditTrailRepository.AuditTrailCreate(dbname, _AuditTrail);
                        _IAuditTrailRepository.AuditTrail1Create(detectedChanges1, dbname, _AuditTrail.AuditTrailNo, "S", _JournalVoucher1.Id);
                    }
                    // calling main level details to get values before updating
                    _MainOldValues = _IJournalVoucherRepository.JournalVoucherDetails(_JournalVoucher1.MainId, dbname).Result;
                }

                _IJournalVoucherRepository.UpdateAmount(_JournalVoucher1.YearJvNo, dbname);

                if(auditTrailSysparameter.IsActive == "Y")
                {
                    // calling main level details to get values after updating
                    JournalVoucher _MainNewValues = _IJournalVoucherRepository.JournalVoucherDetails(_JournalVoucher1.MainId, dbname).Result;

                    var detectedChanges1 = _AuditTrailController.DetectChanges(_MainOldValues, _MainNewValues, dbname, "Journalvoucher");

                    if (detectedChanges1.Count != 0)
                    {
                        _IAuditTrailRepository.AuditTrail1Create(detectedChanges1, dbname, _AuditTrail.AuditTrailNo, "S", _JournalVoucher1.Id);
                    }  
                }

                _IUtilityMethodsRepository.InsertMFGLog(dbname, username, _JournalVoucher1.JvNo, "JV", "Edit Line", yearlabel, "");
            }

            return Ok();
        }

        [HttpDelete("{id},{mainId},{dbname},{userName},{yearLabel},{deleteReason}")]
        public void JournalVoucher1Delete(int id, int mainId, string dbname, string userName, string yearLabel, string deleteReason)
        {
            var data = _IJournalVoucherRepository.JournalVoucherDetails(mainId, dbname).Result;

            var auditTrailSysparameter = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "AuditTrail");
            AuditTrail _AuditTrail = new();
            if(auditTrailSysparameter.IsActive == "Y")
            {
      
                _AuditTrail.AuditTrailNo = _AuditTrailController.GetNextAuditTrailNo(dbname);
                //assigning the values 
                _AuditTrail.DocTypeCode = "J";
                _AuditTrail.YearDocNo = data.YearJvNo;
                _AuditTrail.UserName = userName;
                _AuditTrail.OperationCode = "DL";
                _AuditTrail.Notes = deleteReason;

                // for creating main level audit trail
                _IAuditTrailRepository.AuditTrailCreate(dbname, _AuditTrail);

                // for inserting records from line level jv
                _IAuditTrailRepository.AuditTrail1DynamicCreate(dbname, $"{id}", "journalvoucher1", _AuditTrail.AuditTrailNo, "id", id);

            }
            
            _IJournalVoucherRepository.JournalVoucher1Delete(id, dbname);

            _IJournalVoucherRepository.UpdateAmount(data.YearJvNo, dbname);

            if(auditTrailSysparameter.IsActive == "Y")
            {
                // calling main level details to get values after updating
                JournalVoucher _jvDataAfterUpdate = _IJournalVoucherRepository.JournalVoucherDetails(mainId, dbname).Result;

                var detectedChanges1 = _AuditTrailController.DetectChanges(data, _jvDataAfterUpdate, dbname, "Journalvoucher");

                if (detectedChanges1.Count != 0)
                {
                    _IAuditTrailRepository.AuditTrail1Create(detectedChanges1, dbname, _AuditTrail.AuditTrailNo, "S", id);
                }  

            }

            _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, data.JvNo, "JV", "Delete Line", yearLabel, deleteReason);
        }

        public List<AuditTrail1> DetectChanges<T>(T original, T updated, string dbName, string tableName)
        {
            var changes = new List<AuditTrail1>();

            // Fetch the property names that should be tracked from AuditTrailInfo table
            var auditTrailProperties = _IAuditTrailRepository.AuditTrailMetaData(dbName, tableName); // Ensure it's a List<string>

            foreach (var propertyName in auditTrailProperties)  // Loop through properties from DB
            {
                var property = typeof(T).GetProperty(propertyName.PropertyName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

                var oldValue = property.GetValue(original);
                var newValue = property.GetValue(updated);

                if (!Equals(oldValue, newValue)) // Log only if values are different
                {
                    changes.Add(new AuditTrail1
                    {
                        DisplayName = propertyName.DisplayName,
                        OldValue = oldValue,
                        NewValue = newValue
                    });
                }
            }

            return changes;
        }

INSERT INTO AuditTrail1(audittrailno, displayname, oldvalue, newvalue, updatedby) 
        VALUES (@audittrailno, @displayname, @oldvalue, @newvalue, @updatedby)

        -- foreach (var dataLine in detectedChangesList)
        -- {
        --     connection.Query<AuditTrail1>(insertAuditTrail1,
        --     new
        --     {
        --         auditTrailNo,
        --         dataLine.DisplayName,
        --         dataLine.OldValue,
        --         dataLine.NewValue,
        --         updatedBy

        --     });
        -- }

        [HttpDelete("{id},{dbname},{userName},{yearLabel},{deleteReason}")]
        public void JournalVoucherDelete(int id, string dbname, string userName, string yearLabel, string deleteReason)
        {
            var data = _IJournalVoucherRepository.JournalVoucherDetails(id, dbname).Result;

            var auditTrailSysparameter = _IUtilityMethodsRepository.GetSysParameterValue(dbname, "AuditTrail");
            if(auditTrailSysparameter.IsActive == "Y")
            {
                //-------------------AUDIT TRAIL BLOCK--------------------------------------------------------
                AuditTrail _AuditTrail = new();
     
                _AuditTrail.AuditTrailNo = _AuditTrailController.GetNextAuditTrailNo(dbname);
                //assigning the values 
                _AuditTrail.DocTypeCode = "J";
                _AuditTrail.YearDocNo = data.YearJvNo;
                _AuditTrail.UserName = userName;
                _AuditTrail.OperationCode = "DM";
                _AuditTrail.Notes = deleteReason;

                // for creating main level audit trail
                _IAuditTrailRepository.AuditTrailCreate(dbname, _AuditTrail);

                // for inserting records from main level jv
                _IAuditTrailRepository.AuditTrail1DynamicCreate(dbname, _AuditTrail.YearDocNo, "journalvoucher", _AuditTrail.AuditTrailNo, "YearJvNo", id);

                // checking line level records are there or not 
                var journalVoucher1Cnt = _IUtilityMethodsRepository.GetRecordCount(dbname, data.YearJvNo, "journalvoucher1", "yearjvno");

                if(journalVoucher1Cnt > 0)
                {
                    // for inserting records from line level jv
                    _IAuditTrailRepository.AuditTrail1DynamicCreate(dbname, _AuditTrail.YearDocNo, "journalvoucher1", _AuditTrail.AuditTrailNo, "YearJvNo", id);
                }

            }

            _IJournalVoucherRepository.JournalVoucherDelete(data.YearJvNo, dbname);
            _IUtilityMethodsRepository.InsertMFGLog(dbname, userName, data.JvNo, "JV", "Delete", yearLabel, deleteReason);
        }