Welcome Page Changes

ALTER TABLE WelcomeInfo ADD docname NVARCHAR(100);

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000082', 'Automated Notifications keep all users informed with real-time alerts on updates, approvals, and pending actions.', 'A', '2025-11-17', 'ALL', NULL, 'Automated Notifications');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000083', 'Smart Dashboard provides a quick overview of key business metrics with customizable widgets for better insights.', 'A', '2025-11-17', 'ALL', NULL, 'Smart Dashboard');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000084', 'User Access Control ensures secure operations by assigning role-based permissions to every module.', 'A', '2025-11-17', 'ALL', NULL, 'User Access Control');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000085', 'Workflow Automation streamlines repetitive tasks with predefined rules for faster processing.', 'A', '2025-11-17', 'ALL', NULL, 'Workflow Automation');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000086', 'Advanced Search helps instantly find records using multiple filters, keywords, and sorting options.', 'A', '2025-11-17', 'ALL', NULL, 'Advanced Search');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000087', 'Multi-Device Access allows users to work from mobile, tablet, or desktop with seamless synchronization.', 'A', '2025-11-17', 'ALL', NULL, 'Multi-Device Access');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000088', 'Version Control maintains historical changes of documents for easy tracking and rollback.', 'A', '2025-11-17', 'ALL', NULL, 'Version Control');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000089', 'Data Export lets users download reports in PDF, Excel, and CSV formats instantly.', 'A', '2025-11-17', 'ALL', NULL, 'Data Export');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000090', 'Audit Trails record every user activity to maintain transparency and compliance.', 'A', '2025-11-17', 'ALL', NULL, 'Audit Trails');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000091', 'Real-time Sync ensures all modules update instantly across departments with zero delay.', 'A', '2025-11-17', 'ALL', NULL, 'Real-time Sync');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000092', 'Bulk Upload supports importing large datasets with validation and error reporting.', 'A', '2025-11-17', 'ALL', NULL, 'Bulk Upload');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000093', 'Interactive Reports visualizes business data with charts and graphs for decision-making.', 'A', '2025-11-17', 'ALL', NULL, 'Interactive Reports');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000094', 'Integrated Helpdesk assists users with ticketing, tracking, and resolution workflows.', 'A', '2025-11-17', 'ALL', NULL, 'Integrated Helpdesk');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000095', 'Calendar Planner manages meetings, reminders, and events in a unified schedule.', 'A', '2025-11-17', 'ALL', NULL, 'Calendar Planner');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000096', 'Custom Fields allow organizations to tailor forms according to business needs.', 'A', '2025-11-17', 'ALL', NULL, 'Custom Fields');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000097', 'Email Integration sends automated messages, invoices, and alerts directly from the system.', 'A', '2025-11-17', 'ALL', NULL, 'Email Integration');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000098', 'Data Backup ensures all business information is safely stored and recoverable.', 'A', '2025-11-17', 'ALL', NULL, 'Data Backup');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000099', 'Inventory Alerts notify when stock reaches minimum or reorder levels.', 'A', '2025-11-17', 'ALL', NULL, 'Inventory Alerts');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000100', 'Performance Analyzer tracks employee productivity and operational efficiency.', 'A', '2025-11-17', 'ALL', NULL, 'Performance Analyzer');

INSERT INTO WelcomeInfo (docno, notes, type, updatedon, menu, doclink, docname) VALUES
('000101', 'Smart Tags categorize documents and records for quick grouping and accessibility.', 'A', '2025-11-17', 'ALL', NULL, 'Smart Tags');

public async Task<IEnumerable<Welcome>> GetAddOnFeatures()
{
    var selectAllQuery = @"SELECT id, docno, 'Add-On Features' AS typename, notes AS description, menu, doclink, updatedon, type, docname AS DocName
    FROM WelcomeInfo
    WHERE type = @type AND menu IN ('ALL', 'MFG')
    ORDER BY id";

    using var commonConnection = _DapperContext.SetCommonConnection();

    var addOnFeatures = await commonConnection.QueryAsync<Welcome>(selectAllQuery, new { type = "A" });
    return addOnFeatures.ToList();
}

public async Task<Welcome> GetFeaturedAddOn()
{
    var query = @"WITH A(id, rowno, updatedon, docno)AS
    (
        SELECT id, ROW_NUMBER() OVER (ORDER BY docno) AS rowno, updatedon,docno FROM WelcomeInfo
        WHERE type = @type AND menu IN ('ALL', 'MFG')
    ),
    B (maxdate) AS
    (
        SELECT MAX(updatedon) FROM A
    ),
    C(rowno)AS
    (
        SELECT rowno + 1 FROM A
        LEFT JOIN B ON 1 = 1
        WHERE A.updatedon = B.maxdate
    ),
    D(rowno)AS
    (
        SELECT COALESCE(A.rowno,1) FROM C
        LEFT JOIN A ON C.rowno = A.rowno
    ),
    E(id)AS
    (
        SELECT A.id FROM A
        INNER JOIN D ON A.rowno = D.rowno
    )
    UPDATE WelcomeInfo SET updatedon = FORMAT(GETDATE(),'dd-MMM-yy')  FROM E
    WHERE WelcomeInfo.id = E.id";

    var featuredAddOnQuery = @"SELECT id, docno, 'Add-On Features' AS typename, notes AS description, menu, doclink, updatedon, type, docname AS DocName
    FROM WelcomeInfo
    WHERE updatedon = @dated AND Type = @type AND menu IN ('ALL', 'MFG')";

    using var commonconnection = _DapperContext.SetCommonConnection();
    var cntAddOn = GetTypeCount(DateTime.Now.Date, "A");
    if (cntAddOn == 0)
    {
        commonconnection.Execute(query, new { type = "A" });
    }

    var featuredAddOnData = commonconnection.QueryFirstOrDefault<Welcome>(featuredAddOnQuery, new { dated = DateTime.Now.ToString("dd-MMM-yy"), type = "A" });

    return featuredAddOnData;
}

public class Welcome
{
    [StringLength(100, ErrorMessage = "Doc Name can be upto 100 characters only")]
    public string DocName { get; set; } = string.Empty;
}