Skip to content
Laravel

Users Management

Introduction

In OTAS, user management interacts with multiple database tables. Before we delve into these tables, it's important to note that the system employs group-based permissions. Users can belong to multiple groups, each with distinct permissions:

  • Administrators: Users in the administrator group possess full system access and capabilities.

  • Employees: Stored in the users table, employees have specific permissions tailored to their roles.

  • Agents: Agents are also recorded in the users table, identified by group 5. Users in this group are considered main agents and can manage sub-agents under their account.

  • Sub-Agents: These users are categorized in the users table under group 20. Each sub-agent is linked to a main agent, together forming a single organizational entity. Commissions are calculated based on the main agent's commission percentage.

  • Students: Identified by group 4 in the users table, students currently do not have backend portal access in OTAS.

Users Management

The diagram provided outlines the database structure for user management in OTAS, detailing the specific tables employed to manage all user-related functionalities within the system.

Tables

Let's examine the tables integral to user management in OTAS:

  • oc_users: This is the central table storing all user types, including administrators, employees, managers, CL users, students, agents, sub-agents, among others.

  • oc_users_groups: This table lists all the groups available in the system. Each group entry includes permissions, stored as a JSON column.

  • oc_users_main_agents: This table records the IDs of main agents within the system, along with additional configuration details for each.

  • oc_users_sub_agents: It maps main agents to their sub-agents in a one-to-many relationship and includes specific permissions for each sub-agent.

  • group_user: A junction table facilitating a many-to-many relationship between the oc_users and oc_users_groups tables.

  • spotlayerteam_institutionsprograms_students: This table contains detailed student information and their association with oc_users.

oc_users

The oc_users table is a comprehensive component of the OTAS user management system, featuring a variety of columns to store user-specific information. Here's a concise summary of each column and its purpose:

  • id: The primary key for user identification, automatically incremented.
  • student_name: Optional field for the name of users identified as students (group id 4).
  • name: Mandatory field for the user's name; for students, this holds their email.
  • email: Unique and mandatory field for the user's email address.
  • group_id: Previously used for a one-to-many relationship; now deprecated due to the shift to a many-to-many relationship model.
  • ip_address: Records the last IP address used by the user for login or registration.
  • permissions: A deprecated and unused column.
  • password: Holds encrypted user passwords; random for students as they cannot log in.
  • is_activated: Boolean flag for user account activation status.
  • activation_code: A deprecated and unused column.
  • persist_code: Utilized for 'remember me' functionality.
  • reset_password_code: Employed for the password reset process.
  • last_seen: Timestamp for the user's most recent login.
  • activated_at: Date of user account activation.
  • is_banned: Indicates whether the user is banned.
  • is_banned_at: Date the user was banned.
  • is_banned_reason: Explanation for the user's ban.
  • created_at: Timestamp for user account creation.
  • updated_at: Timestamp for the last update to the user's record.
  • updated_by: Identifier for the user who last updated the record.
  • created_by: Identifier for the user who created the record.
  • agent_commision: A deprecated column previously used for agent commissions.
  • cl_creator_ids: text column contains group ids, when an application got created by a user in one of these groups current user listed in CL users list in that application.
  • cl_school_ids: text column contains institutions ids, when an application created for a program within an institution in this institution ids, the current user listed in CL users list in that application.
  • cl_country_ids: same as cl_creator_ids, and cl_school_ids but for country of the student nationality.
  • title: Title used for main agents (group id: 5).
  • organization: Title used for sub-agents (group id: 20).
  • phone: User's contact number.
  • history: JSON column tracking changes to the user's data.
  • cl_history: json column contains all changes occured in the cl management, like changes in cl_creator_ids, cl_school_ids, and cl_country_ids.
  • platform: Platform used by the student during registration, such as Google or Facebook.
  • platform_id: Identifier for the user on the registration platform.
  • device_token: Authentication token for mobile API use; null for standard users.
  • deleted_at: Soft deletion timestamp; null if the record is active.
  • deleted_by: Identifier for the user who performed the deletion.
  • pr_manager_id: Reference to the public relations manager for users in the PR group (group id: 45).
  • sid: A deprecated identifier for students in the S'aide system.

This table structure allows for detailed user account management, including security features, historical tracking, and multiple deprecated fields from past system iterations.

oc_users_groups

The oc_users_groups table is designed to manage the user groups within OTAS, each with a distinct set of responsibilities and permissions. Below is an outline of its structure:

  • id: Serves as the unique identifier for each group, which is auto-incremented.
  • name: Displays the human-readable name of the group.
  • code: Shows a simplified, lower-case representation of the group's name, using hyphens to separate words.
  • permissions: A JSON-encoded column detailing the permissions assigned to the group, including rights to read, create, update, and delete (R-C-U-D) where applicable.
  • created_at: Records the date and time when the group was originally created.
  • updated_at: Logs the date and time of the most recent modification to the group's record.

This table ensures that different user groups have the appropriate permissions to perform their functions within the system effectively and securely.

oc_users_main_agents

The oc_users_main_agents table holds the ids of users in Agent group (group 5) and some extra configuration to their account.

  • id: Serves as the unique identifier, which is auto-incremented.
  • user_id: The user id who is the agent.
  • max_sub_count: The max sub agents account he can create under his account.
  • is_main: A boolean determine if a user is main agent and he can create sub agents accounts.
  • is_inherit: A boolean determine if a user is inherit or not.
  • created_at: the date of which the operation created.
  • updated_at: the date of last update operation done to the record.
  • countries: json list of countries ids the user allowed to create applications in institutions in that countries only.
  • universities: json list of universities ids the user allowed to create applications in.

oc_users_sub_agents

The oc_users_sub_agents table is structured to maintain the relationships between main agents and their sub-agents, along with the specific permissions assigned to the sub-agents. Here's an overview of its columns:

  • id: Acts as the primary key, automatically incremented for each new record.
  • user_id: References the ID of the user who is designated as a sub-agent (belonging to group 20).
  • main_id: Stores the ID of the main agent associated with the sub-agent.
  • can_search: A boolean flag that allows a sub-agent to access the search functionality.
  • can_app_create: A boolean flag that permits a sub-agent to access the application creation interface.
  • can_student_create: A boolean flag that enables a sub-agent to access the student creation page.
  • can_view_commission: A boolean flag that allows a sub-agent to view commission-related information.
  • can_view_org_students: A boolean flag that enables a sub-agent to view the organization's student records.
  • can_view_org_applications: A boolean flag that allows a sub-agent to view application details within the organization.
  • created_at: Timestamp marking when the sub-agent record was created.
  • updated_at: Timestamp of the most recent update to the sub-agent's record.

This table plays a crucial role in managing the hierarchy within the agent structure of OTAS, clearly defining what actions sub-agents are allowed to perform and ensuring that these permissions align with the system's governance.

group_user

The group_user table is a junction or associative table that establishes a many-to-many relationship between users and groups within OTAS. It maps users to the groups they belong to. Here's a brief outline of its columns:

  • group_id: Stores the identifier of the group.
  • user_id: Holds the identifier of the user.

Each record in the group_user table represents a link between a specific user and a specific group, meaning that a single user can be associated with multiple groups and a single group can contain multiple users. This structure is essential for systems where users may have roles in multiple groups, each with different permissions and access levels.

students

The spotlayerteam_institutionsprograms_students table is structured to store comprehensive data about students. Here's a summary of each column and its purpose:

  • id: Unique identifier for the student record.
  • name: Student's first name.
  • surname: Student's last name.
  • passport_no: Student's passport number.
  • email: Student's email address.
  • nationality: ID of the student's nationality, linking to the spotlayerteam_institutionsprograms_country table.
  • country: Deprecated column, formerly used for country information.
  • country_code: ID of the country code for the student's phone number.
  • phone: Student's phone number.
  • birthdate: Student's date of birth.
  • gender: Student's gender, either male or female.
  • marital: Student's marital status, can be Single or Married.
  • father_name: Name of the student's father.
  • father_number: Contact number of the student's father.
  • mother_name: Name of the student's mother.
  • address_abroad: Student's address outside of Turkey.
  • address_inside: Student's address within Turkey.
  • agent_id: ID of the agent who created the student record, if applicable.
  • user_id: ID of the OTAS employee who created the student record, sourced from the oc_users table.
  • created_at: Timestamp for when the student record was created.
  • updated_at: Timestamp for the last update made to the student record.
  • deleted_at: Timestamp for soft deletion, if applicable.
  • created_by: ID of the user who created the student record.
  • updated_by: ID of the user who last updated the student record.
  • visa_status: Status indicating if the student requires a visa to come to Turkey.
  • source: Origin of the student's registration, such as otas-plugin, agent-plugin, or direct site registration.
  • year_id: Academic year the student is applying for.
  • possible_transfer: Boolean indicating if the student is undecided and might transfer to another institution after the first year.
  • sid: Deprecated student ID from the S'aide system.
  • passport_issue_date: Date of passport issuance.
  • passport_expiry_date: Date of passport expiration.
  • visa_country: Nullable field for the ID of the country if the student needs a visa to come to Turkey.
  • is_transfer: Boolean indicating whether the student has transferred from another institution.
  • has_blue_card: Boolean indicating if the student possesses a blue card.
  • has_tc_number: Boolean indicating if the student has a Turkish residence card.
  • tc_number: Residence card number if it exists.
  • awaiting_payment_status: Deprecated column, not in use.
  • student_await_status: Deprecated column, not in use.
  • extra_data: Nullable JSON column for any additional student data.
  • residence_country: ID of the student's current country of residence.
  • aff_code: Affiliate code used if the student applied using an affiliate code from a system member.
  • deleted_by: ID of the user who performed a deletion on the student record, if applicable.
  • user_agent: Browser user agent used by the student during application.
  • session_key: the session key generated by october cms to manage upload files process.
  • started_creation: Timestamp indicating when the process of inputting the student's data began.

This table captures detailed personal, contact, and application-related information about students, providing a robust data set for managing student profiles within OTAS.