Simplify CHS - Member Mst Link To Service Group [Import]

Changes in MemberMstLinkToServiceGroupIndex

• In Member Link to Service Group Index need to add Import Button

Changes in LinkToServiceGroupBulkUpdate

• Need to add LinkToServiceGroupBulkUpdate razor page
• Use LinkToServiceGroupIndex query for prefilled data
• Use existing LinkToServiceGroup model class

Download Model

Query

SELECT M.id,COALESCE(M.servicegroupcode,'999999') AS groupcode,
CONCAT(LTRIM(
CONCAT_WS('/',
NULLIF(LTRIM(RTRIM(Wing)), ''),
NULLIF(LTRIM(RTRIM(Bldg)), ''),
NULLIF(LTRIM(RTRIM(Flatno)), '')) + ' '),Name) AS membername,
M.memberid,COALESCE(SG.groupname, 'NOT LINKED') AS groupname
FROM MemberMst M
LEFT JOIN ServiceGroupMst SG ON COALESCE(M.servicegroupcode,'999999') = SG.groupcode
WHERE M.typeofmember IN ('01','02','11') AND ceased = '31-MAR-49'  AND Suspended ='N'
ORDER BY M.servicegroupcode, M.id DESC

Reading XL error codes

Error Codes

Sysgen XL

Queries for error code

var tempcreation = @"CREATE TABLE #linktoservicegroup
(
    id INT PRIMARY KEY IDENTITY(1,1),
    memberid NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    groupcode NVARCHAR(6) COLLATE DATABASE_DEFAULT,
    errorcode NVARCHAR(200) COLLATE DATABASE_DEFAULT
)";

var inserttemp = @"INSERT INTO #linktoservicegroup(memberid,groupcode)
VALUES(@memberid,@groupcode)";

/* -------------------- ERROR VALIDATIONS -------------------- */


// Error Code A - Member ID must exist in member master
var errorcodeA = @"UPDATE #linktoservicegroup SET errorcode = COALESCE(RTRIM(errorcode),'') + ' A'
WHERE memberid NOT EXISTS (SELECT memberid FROM membermst 
WHERE typeofmember IN ('01','02','11') AND ceased = '31-MAR-49'  AND Suspended ='N')";

// Error Code B - Service Group Code must exist in service group master
var errorcodeB = @"UPDATE #linktoservicegroup SET errorcode = COALESCE(RTRIM(errorcode),'') + ' B'
WHERE groupcode NOT EXISTS (SELECT groupcode FROM servicegroupmst WHERE isactive = 'Y')";

// Error Code C - Duplicate Member ID within Excel
var errorcodeC = @"WITH A(memberid) AS
(
    SELECT memberid FROM #linktoservicegroup
    GROUP BY memberid
    HAVING COUNT(memberid) > 1
)
UPDATE #linktoservicegroup SET errorcode = COALESCE(RTRIM(errorcode),'') + ' C'
FROM A WHERE #linktoservicegroup.memberid = A.memberid";

// Query to fetch data after validations
var data = @"SELECT L.memberid, COALESCE(M.name,'Invalid Member Name') AS membername, L.groupcode,
COALESCE(SG.groupname, 'NOT LINKED') AS groupname, L.errorcode
FROM #linktoservicegroup L
LEFT JOIN membermst M ON L.memberid = M.memberid
LEFT JOIN servicegroupmst SG ON L.groupcode = SG.groupcode
ORDER BY L.id ASC;";

var memberMstUpdate = @"UPDATE membermst SET servicegroupcode = L.groupcode
FROM #linktoservicegroup L WHERE membermst.memberid = L.memberid;";

New Classes and Methods

• LinkToServiceGroupBulkUpdate
• LinkToServiceGroupBulkUpdate

public class LinkToServiceGroup
{
    public string ErrorCode { get; set; } = string.Empty; // New Column
}

Updated Test Cases for Link Member to Service Group Bulk Update Functionality

1. UI/Frontend Test Cases

Navigation & Access Control

• TC-UI-001: Verify Import button is visible on MemberMstLinkToServiceGroupIndex page for users with MemberMstServices role
• TC-UI-002: Verify Import button is hidden for users without MemberMstServices role
• TC-UI-003: Verify navigation to /LinkToServiceGroupBulkUpdate page when Import button is clicked
• TC-UI-004: Verify access denied redirect when user lacks permissions

Import Page Display

• TC-UI-005: Verify Upload button is enabled (no record count check needed)
• TC-UI-006: Verify file upload component accepts only .xlsx files
• TC-UI-007: Verify “LinkToServiceGroupImport.xlsx” filename requirement message

File Upload & Processing

• TC-UI-008: Verify error message when no file is selected and Upload is clicked
• TC-UI-009: Verify error message for incorrect filename (not LinkToServiceGroupImport.xlsx)
• TC-UI-010: Verify processing indicator appears during upload
• TC-UI-011: Verify successful redirect to index page after successful import
• TC-UI-012: Verify Excel download trigger after successful import

2. API Controller Test Cases

Download Model Endpoint

• TC-API-001: Verify LinkToServiceGroupDownloadModel returns valid Excel file
• TC-API-002: Verify Excel contains correct headers: Member ID, Member Name, Service Group Name, Service Group Code
• TC-API-003: Verify Instructions sheet contains all validation rules
• TC-API-004: Verify file is saved in correct path with random filename

Import Processing Endpoint

• TC-API-005: Verify LinkToServiceGroupBulkUpdate returns “1” for incorrect column format
• TC-API-006: Verify LinkToServiceGroupBulkUpdate returns “2” for empty Excel file
• TC-API-007: Verify successful import returns Excel filename with results

3. Repository Test Cases

Import Method - Temp Table Creation

• TC-REPO-001: Verify #linktoservicegroup temp table is created successfully
• TC-REPO-002: Verify temp table has correct schema (id, memberid, groupcode, errorcode)
• TC-REPO-003: Verify data insertion into temp table works correctly with PadLeft(6, ‘0’)

Validation Queries - Error Code Z1-Z3 (Controller)

• TC-REPO-004: Verify Z1 error for missing mandatory fields (Member ID, Service Group Code)
• TC-REPO-005: Verify Z2 error for Member ID not exactly 6 digits
• TC-REPO-006: Verify Z3 error for Service Group Code not exactly 6 digits

Validation Queries - Error Code A-C (Repository)

• TC-REPO-007: Verify A error for invalid Member ID (not in member master)
• TC-REPO-008: Verify B error for invalid Service Group Code (not in service group master)
• TC-REPO-009: Verify C error for duplicate Member ID within Excel

Successful Import Processing

• TC-REPO-010: Verify UPDATE statement correctly links members to service groups
• TC-REPO-011: Verify CHS log entry is created with correct details
• TC-REPO-012: Verify return tuple contains success message and processed data

4. Integration Test Cases

End-to-End Import Flow

• TC-INT-001: Complete successful import with valid Excel file
• TC-INT-002: Complete import with mixed valid/invalid records
• TC-INT-003: Verify Excel download contains error codes for failed records
• TC-INT-004: Verify successful records update member service group codes
• TC-INT-005: Verify LinkToServiceGroupIndex reflects updated service groups

Database Integration

• TC-INT-006: Verify MemberMst table servicegroupcode column is updated correctly
• TC-INT-007: Verify CHS log table is updated correctly

5. Error Handling & Edge Cases

File Processing Errors

• TC-ERR-001: Verify handling of corrupted Excel files
• TC-ERR-002: Verify handling of Excel files with wrong sheet structure
• TC-ERR-003: Verify handling of Excel files with missing columns
• TC-ERR-004: Verify handling of Excel files with extra columns

Data Validation Edge Cases

• TC-ERR-005: Verify handling of empty strings vs null values
• TC-ERR-006: Verify case sensitivity in validation
• TC-ERR-007: Verify duplicate member IDs within Excel are properly flagged

7. Business Logic Test Cases

Member Validation

• TC-BIZ-001: Verify valid Member IDs are accepted
• TC-BIZ-002: Verify invalid member IDs are rejected
• TC-BIZ-003: Verify member type filtering (01, 02, 11) works correctly

Service Group Validation

• TC-BIZ-004: Verify valid Service Group Codes are accepted
• TC-BIZ-005: Verify invalid service group codes are rejected
• TC-BIZ-006: Verify inactive service groups are rejected

Update Logic

• TC-BIZ-007: Verify service group code is correctly updated in member master
• TC-BIZ-008: Verify existing service group links can be changed
• TC-BIZ-009: Verify members can be unlinked by setting empty group code

8. Regression Test Cases

Existing Functionality

• TC-REG-001: Verify MemberMstLinkToServiceGroupIndex still works after changes
• TC-REG-002: Verify MemberMstLinkToServiceGroupModify still works after changes
• TC-REG-003: Verify LinkToServiceGroupIndex query still returns correct data
• TC-REG-004: Verify single member service group updates still work

Total Test Cases: 44

This documentation provides a comprehensive guide for implementing the bulk update functionality for linking members to service groups, following the same patterns as the AccountMstController bulk import functionality.