School Management

// === School Management System Tables in ServiceNow with Admin Role Assignments ===
var appMenuName = "School Management";
var appOrder = 100;
var moduleOrder = 50;
var adminRole = "admin"; // Role to be assigned

// === STEP 1: CREATE APPLICATION MENU ===
var appCheck = new GlideRecord('sys_app_application');
appCheck.addQuery('title', appMenuName);
appCheck.query();

var appSysId;
if (appCheck.next()) {
    gs.info("Application menu already exists: " + appMenuName);
    appSysId = appCheck.getValue('sys_id');
} else {
    var appMenu = new GlideRecord('sys_app_application');
    appMenu.initialize();
    appMenu.title = appMenuName;
    appMenu.active = true;
    appMenu.order = appOrder;
    appSysId = appMenu.insert();
    gs.info("Application menu created: " + appMenuName);
}

function setListViewOrder(tableName, fields) {
    var listView = new GlideRecord('sys_ui_list');
    listView.addQuery('name', tableName);
    listView.query();

    var listViewSysId;
    if (!listView.next()) {
        listView.initialize();
        listView.name = tableName;
        listView.view = "Default";
        listViewSysId = listView.insert();
    } else {
        listViewSysId = listView.sys_id;
    }

    for (var i = 0; i < fields.length; i++) {
        var listElement = new GlideRecord('sys_ui_list_element');
        listElement.addQuery('list_id', listViewSysId);
        listElement.addQuery('name', fields[i].columnName);
        listElement.query();

        if (!listElement.next()) { // Check if element already exists before inserting
            var newListElement = new GlideRecord('sys_ui_list_element');
            newListElement.initialize();
            newListElement.list_id = listViewSysId;
            newListElement.name = fields[i].columnName;
            newListElement.order = i * 100; // Ordering incrementally
            newListElement.insert();
        } else {
            gs.info("List element already exists: " + fields[i].columnName + " for table: " + tableName);
        }
    }
    gs.info("List view configured for: " + tableName);
}

function setFormViewOrder(tableName, fields) {
    var formView = new GlideRecord('sys_ui_form');
    formView.addQuery('name', tableName);
    formView.addQuery('view', 'Default'); // Ensure we are checking for the default view
    formView.query();

    var formViewSysId;
    if (!formView.next()) {
        formView.initialize();
        formView.name = tableName;
        formView.view = "Default";
        formViewSysId = formView.insert();
    } else {
        formViewSysId = formView.sys_id;
    }

    // Get the default section for the form view. Usually, it's named "NULL" or empty.
    var formSection = new GlideRecord('sys_ui_section');
    formSection.addQuery('form', formViewSysId);
    formSection.addQuery('view', 'Default'); // Ensure we are checking for the default view
    formSection.query();
    formSection.next(); // Assuming default section always exists or gets created with form view.

    var sectionSysId = formSection.sys_id;

    for (var i = 0; i < fields.length; i++) {
        var formField = new GlideRecord('sys_ui_element');
        formField.addQuery('section', sectionSysId);
        formField.addQuery('name', fields[i].columnName);
        formField.query();

        if (!formField.next()) { // Check if form element already exists before inserting
            var newFormField = new GlideRecord('sys_ui_element');
            newFormField.initialize();
            newFormField.section = sectionSysId;
            newFormField.name = fields[i].columnName;
            newFormField.position = i * 100; // Ordering incrementally
            newFormField.insert();
        } else {
            gs.info("Form element already exists: " + fields[i].columnName + " for table: " + tableName);
        }
    }
    gs.info("Form view configured for: " + tableName);
}


// === TABLE CREATION FUNCTION ===
function createTable(tableName, tableLabel, fields) {
    var tableCheck = new GlideRecord('sys_db_object');
    tableCheck.addQuery('name', tableName);
    tableCheck.query();

    var tableSysId;
    if (tableCheck.next()) {
        gs.info("Table already exists: " + tableName);
        tableSysId = tableCheck.getValue('sys_id');
    } else {
        var table = new GlideRecord('sys_db_object');
        table.initialize();
        table.name = tableName;
        table.label = tableLabel;
        table.is_extendable = true;
        tableSysId = table.insert();

        if (tableSysId) {
            gs.info("Table created: " + tableName);
            for (var i = 0; i < fields.length; i++) {
                // Corrected: Use fields[i] instead of field
                var field = fields[i];
                // If columnType is "reference", ensure referenceTable is set or use ""
                var referenceTable = (field.columnType === "reference") ? (field.referenceTable || "") : "";
                addColumn(tableName, field.columnName, field.columnType, field.columnLabel, referenceTable);
            }
            assignAdminRole(tableName); // Assign admin role

             // Set List and Form View Order
            setListViewOrder(tableName, fields);
            setFormViewOrder(tableName, fields);
        } else {
            gs.error("Failed to create table: " + tableName);
        }
    }

    createModule(tableLabel, tableName);
}

// === MODULE CREATION FUNCTION ===
function createModule(moduleLabel, tableName) {
    var moduleCheck = new GlideRecord('sys_app_module');
    moduleCheck.addQuery('title', moduleLabel);
    moduleCheck.addQuery('application', appSysId); // Ensure module is under the created application
    moduleCheck.query();

    if (!moduleCheck.next()) {
        var module = new GlideRecord('sys_app_module');
        module.initialize();
        module.title = moduleLabel;
        module.application = appSysId;
        module.link_type = "LIST";
        module.name = tableName;
        module.order = moduleOrder;
        module.insert();
        gs.info("Module created: " + moduleLabel);
    } else {
        gs.info("Module already exists: " + moduleLabel + " under Application: " + appMenuName);
    }
}

// === COLUMN ADDITION FUNCTION (FIXED REFERENCE HANDLING) ===
function addColumn(tableName, columnName, columnType, columnLabel, referenceTable) {
    var column = new GlideRecord('sys_dictionary');
    column.initialize();
    column.name = tableName;
    column.column_label = columnLabel;
    column.column_name = columnName;
    column.internal_type = columnType;
    column.mandatory = false;

    // If it's a reference field, set the reference table properly
    if (columnType === "reference" && referenceTable) {
        column.reference = referenceTable;
    }

    var columnSysId = column.insert();
    if (columnSysId) {
        gs.info("Column created: " + columnName + " in " + tableName);
    } else {
        gs.error("Failed to create column: " + columnName + " in " + tableName);
    }
}

// === ASSIGN ADMIN ROLE FUNCTION ===
function assignAdminRole(tableName) {
    var aclTypes = ["read", "write", "create", "delete"];

    for (var i = 0; i < aclTypes.length; i++) {
        var aclCheck = new GlideRecord('sys_security_acl');
        aclCheck.addQuery('name', tableName);
        aclCheck.addQuery('operation', aclTypes[i]);
        aclCheck.addQuery('role', adminRole);
        aclCheck.query();

        if (!aclCheck.next()) {
            var acl = new GlideRecord('sys_security_acl');
            acl.initialize();
            acl.name = tableName;
            acl.operation = aclTypes[i];
            acl.role = adminRole;
            acl.insert();
            gs.info("Assigned " + aclTypes[i] + " permission to admin role for table: " + tableName);
        } else {
            gs.info("Admin role already has " + aclTypes[i] + " access for: " + tableName);
        }
    }
}

// === CREATE TABLES ===
createTable("u_students", "Students", [
    { columnName: "u_first_name", columnType: "string", columnLabel: "First Name" },
    { columnName: "u_last_name", columnType: "string", columnLabel: "Last Name" },
    { columnName: "u_date_of_birth", columnType: "glide_date", columnLabel: "Date of Birth" },
    { columnName: "u_gender", columnType: "choice", columnLabel: "Gender" },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_phone", columnType: "string", columnLabel: "Phone" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_admission_date", columnType: "glide_date", columnLabel: "Admission Date" }
]);

createTable("u_teachers", "Teachers", [
    { columnName: "u_first_name", columnType: "string", columnLabel: "First Name" },
    { columnName: "u_last_name", columnType: "string", columnLabel: "Last Name" },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_phone", columnType: "string", columnLabel: "Phone" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_hire_date", columnType: "glide_date", columnLabel: "Hire Date" },
    { columnName: "u_subject_id", columnType: "reference", columnLabel: "Subject", referenceTable: "u_subjects" }
]);

createTable("u_subjects", "Subjects", [
    { columnName: "u_subject_name", columnType: "string", columnLabel: "Subject Name" },
    { columnName: "u_description", columnType: "string", columnLabel: "Description" }
]);

createTable("u_classes", "Classes", [
    { columnName: "u_class_name", columnType: "string", columnLabel: "Class Name" },
    { columnName: "u_section", columnType: "string", columnLabel: "Section" },
    { columnName: "u_teacher_id", columnType: "reference", columnLabel: "Teacher", referenceTable: "u_teachers" }
]);

createTable("u_enrollments", "Enrollments", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_enroll_date", columnType: "glide_date", columnLabel: "Enroll Date" }
]);

createTable("u_attendance", "Attendance", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_attendance_date", columnType: "glide_date", columnLabel: "Attendance Date" },
    { columnName: "u_status", columnType: "choice", columnLabel: "Status" }
]);

createTable("u_exams", "Exams", [
    { columnName: "u_exam_name", columnType: "string", columnLabel: "Exam Name" },
    { columnName: "u_subject_id", columnType: "reference", columnLabel: "Subject", referenceTable: "u_subjects" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_exam_date", columnType: "glide_date", columnLabel: "Exam Date" }
]);

createTable("u_exam_results", "Exam Results", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_exam_id", columnType: "reference", columnLabel: "Exam", referenceTable: "u_exams" },
    { columnName: "u_score", columnType: "decimal", columnLabel: "Score" },
    { columnName: "u_grade", columnType: "string", columnLabel: "Grade" },
    { columnName: "u_remarks", columnType: "string", columnLabel: "Remarks" }
]);

createTable("u_fees", "Fees", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_due_date", columnType: "glide_date", columnLabel: "Due Date" },
    { columnName: "u_paid_date", columnType: "glide_date", columnLabel: "Paid Date" },
    { columnName: "u_status", columnType: "choice", columnLabel: "Status" }
]);

gs.info("School Management System setup completed with admin role assignments.");