List of public pages created with Protopage

Home

News

MSNBC - Top Stories

This Week in Tech

PopSugar

FOXNews.com

Fresh Air

CNN.com International Edition

ESPN.com

Bookmarks

Bookmarks

Bookmarks

Plain sticky notes

Sticky note

Click 'edit' on the Bookmarks widget to add all of your favorite sites to your page for quick access

Notes

Plain sticky notes

Sticky note

Sticky note

Click here to type

Room 101 / SQL

Rich sticky notes

Rich text note

Absolutely, Alan. Here's a comprehensive schema for a Training Management System with 19 well-structured tables, each including:

This system supports trainees, trainers, courses, assessments, feedback, scheduling, and performance tracking — ideal for healthcare, corporate, or educational environments.

🧩 1. Trainees

FieldDescription
TraineeId (PK)Unique identifier for each trainee
FullNameFull name of the trainee
EmailContact email
PhoneMobile or landline
DateOfBirthUsed for age-based eligibility
GenderOptional demographic field
RegistrationDateWhen the trainee joined

🧩 2. Trainers

FieldDescription
TrainerId (PK)Unique identifier for each trainer
FullNameTrainer’s full name
EmailContact email
SpecialtyArea of expertise (e.g. CPR, Mental Health)
CertificationLevelTrainer’s qualification tier
HireDateWhen the trainer was onboarded

🧩 3. Courses

FieldDescription
CourseId (PK)Unique identifier for each course
TitleCourse name
DescriptionSummary of course content
DurationHoursTotal hours required
LevelBeginner, Intermediate, Advanced
TrainerId (FK)Linked trainer who delivers the course

🧩 4. Sessions

FieldDescription
SessionId (PK)Unique session identifier
CourseId (FK)Linked course
TrainerId (FK)Trainer conducting the session
ScheduledDateDate of the session
StartTimeStart time
EndTimeEnd time
LocationPhysical or virtual venue

🧩 5. Enrollments

FieldDescription
EnrollmentId (PK)Unique enrollment record
TraineeId (FK)Who enrolled
CourseId (FK)Which course
EnrollmentDateWhen they signed up
StatusActive, Completed, Dropped

🧩 6. Assessments

FieldDescription
AssessmentId (PK)Unique test or quiz
CourseId (FK)Related course
TitleName of the assessment
TypeQuiz, Practical, Written
MaxScoreTotal possible score

🧩 7. AssessmentResults

FieldDescription
ResultId (PK)Unique result record
AssessmentId (FK)Which test
TraineeId (FK)Who took it
ScoreAchieved score
DateTakenWhen it was completed
PassedBoolean flag for pass/fail

🧩 8. Feedback

FieldDescription
FeedbackId (PK)Unique feedback entry
TraineeId (FK)Who gave the feedback
CourseId (FK)About which course
Rating1–5 scale
CommentsFree text feedback
SubmittedDateTimestamp of submission

🧩 9. Attendance

FieldDescription
AttendanceId (PK)Unique attendance record
SessionId (FK)Which session
TraineeId (FK)Who attended
StatusPresent, Absent, Late
TimestampWhen attendance was logged

🧩 10. Certificates

FieldDescription
CertificateId (PK)Unique certificate
TraineeId (FK)Recipient
CourseId (FK)Course completed
IssueDateDate of certification
ExpiryDateIf applicable

🧩 11. TrainerReviews

FieldDescription
ReviewId (PK)Unique review
TrainerId (FK)Who was reviewed
TraineeId (FK)Who gave the review
Rating1–5 scale
CommentsOptional feedback
DateSubmittedTimestamp

🧩 12. CourseMaterials

FieldDescription
MaterialId (PK)Unique file or link
CourseId (FK)Related course
TitleName of the material
TypePDF, Video, Link
URLLocation of the resource

🧩 13. Notifications

FieldDescription
NotificationId (PK)Unique alert
RecipientIdTrainee or Trainer
MessageContent of the notification
SentDateWhen it was sent
ReadStatusRead or Unread

🧩 14. Roles

FieldDescription
RoleId (PK)Unique role
RoleNameAdmin, Trainer, Trainee
DescriptionRole permissions summary

🧩 15. Users

FieldDescription
UserId (PK)Unique login identity
UsernameLogin name
PasswordHashEncrypted password
RoleId (FK)Linked role
EmailContact email
IsActiveBoolean flag

🧩 16. ScheduleConflicts

FieldDescription
ConflictId (PK)Unique conflict record
SessionId (FK)Conflicting session
TrainerId (FK)Who’s affected
DescriptionNature of the conflict
ResolutionStatusPending, Resolved

🧩 17. CoursePrerequisites

FieldDescription
PrerequisiteId (PK)Unique rule
CourseId (FK)Target course
RequiredCourseId (FK)Must complete this first

🧩 18. TraineeProgress

FieldDescription
ProgressId (PK)Unique progress record
TraineeId (FK)Who’s progressing
CourseId (FK)Which course
CompletionPercent0–100%
LastUpdatedTimestamp

🧩 19. SystemLogs

FieldDescription
LogId (PK)Unique system event
UserId (FK)Who triggered it
ActionLogin, Update, Delete, etc.
TimestampWhen it occurred
DetailsOptional metadata

Rich text note

🧱 1. Trainees

sql
CREATE TABLE Trainees (    TraineeId INT PRIMARY KEY IDENTITY,    FullName NVARCHAR(100),         -- Full name of the trainee    Email NVARCHAR(100),            -- Contact email    Phone NVARCHAR(20),             -- Mobile or landline    DateOfBirth DATE,               -- Used for age-based eligibility    Gender NVARCHAR(10),            -- Optional demographic field    RegistrationDate DATE           -- When the trainee joined);

🧱 2. Trainers

sql
CREATE TABLE Trainers (    TrainerId INT PRIMARY KEY IDENTITY,    FullName NVARCHAR(100),         -- Trainer’s full name    Email NVARCHAR(100),            -- Contact email    Specialty NVARCHAR(50),         -- Area of expertise    CertificationLevel NVARCHAR(50),-- Qualification tier    HireDate DATE                   -- When the trainer was onboarded);

🧱 3. Courses

sql
CREATE TABLE Courses (    CourseId INT PRIMARY KEY IDENTITY,    Title NVARCHAR(100),            -- Course name    Description NVARCHAR(500),      -- Summary of course content    DurationHours INT,              -- Total hours required    Level NVARCHAR(20),             -- Beginner, Intermediate, Advanced    TrainerId INT FOREIGN KEY REFERENCES Trainers(TrainerId) -- Assigned trainer);

🧱 4. Sessions

sql
CREATE TABLE Sessions (    SessionId INT PRIMARY KEY IDENTITY,    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    TrainerId INT FOREIGN KEY REFERENCES Trainers(TrainerId),    ScheduledDate DATE,    StartTime TIME,    EndTime TIME,    Location NVARCHAR(100)          -- Physical or virtual venue);

🧱 5. Enrollments

sql
CREATE TABLE Enrollments (    EnrollmentId INT PRIMARY KEY IDENTITY,    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    EnrollmentDate DATE,    Status NVARCHAR(20)             -- Active, Completed, Dropped);

🧱 6. Assessments

sql
CREATE TABLE Assessments (    AssessmentId INT PRIMARY KEY IDENTITY,    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    Title NVARCHAR(100),    Type NVARCHAR(20),              -- Quiz, Practical, Written    MaxScore INT);

🧱 7. AssessmentResults

sql
CREATE TABLE AssessmentResults (    ResultId INT PRIMARY KEY IDENTITY,    AssessmentId INT FOREIGN KEY REFERENCES Assessments(AssessmentId),    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    Score INT,    DateTaken DATE,    Passed BIT);

🧱 8. Feedback

sql
CREATE TABLE Feedback (    FeedbackId INT PRIMARY KEY IDENTITY,    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    Rating INT,                     -- 1–5 scale    Comments NVARCHAR(500),    SubmittedDate DATETIME);

🧱 9. Attendance

sql
CREATE TABLE Attendance (    AttendanceId INT PRIMARY KEY IDENTITY,    SessionId INT FOREIGN KEY REFERENCES Sessions(SessionId),    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    Status NVARCHAR(20),            -- Present, Absent, Late    Timestamp DATETIME);

🧱 10. Certificates

sql
CREATE TABLE Certificates (    CertificateId INT PRIMARY KEY IDENTITY,    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    IssueDate DATE,    ExpiryDate DATE);

🧱 11. TrainerReviews

sql
CREATE TABLE TrainerReviews (    ReviewId INT PRIMARY KEY IDENTITY,    TrainerId INT FOREIGN KEY REFERENCES Trainers(TrainerId),    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    Rating INT,    Comments NVARCHAR(500),    DateSubmitted DATETIME);

🧱 12. CourseMaterials

sql
CREATE TABLE CourseMaterials (    MaterialId INT PRIMARY KEY IDENTITY,    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    Title NVARCHAR(100),    Type NVARCHAR(20),              -- PDF, Video, Link    URL NVARCHAR(200));

🧱 13. Notifications

sql
CREATE TABLE Notifications (    NotificationId INT PRIMARY KEY IDENTITY,    RecipientId INT,                -- Could be Trainee or Trainer    Message NVARCHAR(500),    SentDate DATETIME,    ReadStatus BIT);

🧱 14. Roles

sql
CREATE TABLE Roles (    RoleId INT PRIMARY KEY IDENTITY,    RoleName NVARCHAR(50),          -- Admin, Trainer, Trainee    Description NVARCHAR(200));

🧱 15. Users

sql
CREATE TABLE Users (    UserId INT PRIMARY KEY IDENTITY,    Username NVARCHAR(50),    PasswordHash NVARCHAR(200),    RoleId INT FOREIGN KEY REFERENCES Roles(RoleId),    Email NVARCHAR(100),    IsActive BIT);

🧱 16. ScheduleConflicts

sql
CREATE TABLE ScheduleConflicts (    ConflictId INT PRIMARY KEY IDENTITY,    SessionId INT FOREIGN KEY REFERENCES Sessions(SessionId),    TrainerId INT FOREIGN KEY REFERENCES Trainers(TrainerId),    Description NVARCHAR(500),    ResolutionStatus NVARCHAR(20)   -- Pending, Resolved);

🧱 17. CoursePrerequisites

sql
CREATE TABLE CoursePrerequisites (    PrerequisiteId INT PRIMARY KEY IDENTITY,    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    RequiredCourseId INT FOREIGN KEY REFERENCES Courses(CourseId));

🧱 18. TraineeProgress

sql
CREATE TABLE TraineeProgress (    ProgressId INT PRIMARY KEY IDENTITY,    TraineeId INT FOREIGN KEY REFERENCES Trainees(TraineeId),    CourseId INT FOREIGN KEY REFERENCES Courses(CourseId),    CompletionPercent INT,    LastUpdated DATETIME);

🧱 19. SystemLogs

sql
CREATE TABLE SystemLogs (    LogId INT PRIMARY KEY IDENTITY,    UserId INT FOREIGN KEY REFERENCES Users(UserId),    Action NVARCHAR(50),            -- Login, Update, Delete    Timestamp DATETIME,    Details NVARCHAR(500));

Aviva Studios - TrainIT (Turin)

Bookmarks

Bookmarks

Rich sticky notes

TrainIT

Trainit System Database Schema

This document outlines the proposed table structure for your Trainit System, ensuring that all educational data, learning environments, and facilities are linked back to a specific business entity.

1. Business Table

2. Users Table

3. Courses Table

4. Modules Table

5. Lessons Table

6. Enrollments Table

7. Instructors Table

8. InstructorCourseAssignments Table

9. Assessments Table

10. Grades Table

11. Certifications Table

12. StudentCertifications Table

13. Facilities Table

14. Rooms Table

15. Equipment Table

16. RoomEquipment Table

17. Schedules Table

18. Announcements Table

19. SupportTickets Table

Erasmus

Richard J Gilbert has been assigned this project - he has till 2026 week 52 to complete it - see if you can beat him with a Q Core Subscription Service.
Charging a Business £101 for the use of TrainIT

Expand the functionality with a NVQ Administration System  - Project Kevin

Feasibility Study

Feasibility Study for a Trainee Programmer to Complete Project Erasmus (based on the Trainit System)

To assess the feasibility of a trainee programmer completing "Project Erasmus" (assuming this means building a functional application leveraging the proposed Trainit System database), we need to consider several key aspects:

1. Project Scope Definition (Critical)

2. Trainee Programmer's Skill Set

3. Available Resources & Support

4. Technical Complexity

5. Risk Assessment & Mitigation

Conclusion:

Completing "Project Erasmus" with a trainee programmer is feasible, but only if the following conditions are met:

  1. Strictly defined, incremental scope (MVP first).
  2. Dedicated, patient, and knowledgeable mentorship.
  3. Realistic timeframes that account for the learning curve.
  4. A supportive learning environment that encourages asking questions and learning from mistakes.

Without these elements, even a talented trainee will likely struggle. It's more of an educational journey for the trainee rather than a purely production-focused delivery timeline. The existing database schema provides a solid foundation, removing the need for the trainee to design the core data model from scratch, which is a significant advantage.

NVQ Administstration

NVQ Administration System Database Schema

This document outlines the proposed table structure for an NVQ Administration System, designed to manage qualifications, candidates, assessors, assessments, and scheduling, with all data linked back to a specific business entity.

1. Business Table

2. Users Table

3. Qualifications Table

4. Units Table

5. LearningOutcomes Table

6. Candidates Table

7. Assessors Table

8. InternalVerifiers Table

9. CandidateEnrollments Table

10. UnitRegistrations Table

11. Assessments Table

12. AssessmentRecords Table

13. Evidence Table

14. Portfolios Table

15. Certificates Table

16. Schedules Table (For Administrator/Assessor Scheduling)

17. Rooms Table (For physical locations used in scheduling)

18. Equipment Table (For specific assessment/admin equipment)

19. ScheduleRoomAllocations Table

20. ScheduleEquipmentRequirements Table

21. Appeals Table

22. QualityAssuranceReviews Table

23. Fees Table

Functional Specification

Trainit System Functional Specification

This document details the functional requirements for the Trainit System, derived from the provided database schema. It describes how users will interact with the system to manage educational content, learning environments, and facilities.

1. System Goals

2. User Roles and Permissions

The system will support the following primary user roles, each with distinct functionalities and access levels:

3. Core Functional Modules

Based on the database schema, the system will provide the following major functional modules:

3.1. User Management

3.2. Course and Content Management

3.3. Enrollment and Progress Tracking

3.4. Assessment and Grading

3.5. Instructor Management and Assignment

3.6. Certification Management

3.7. Facilities and Equipment Management

3.8. Scheduling

3.9. Communication and Support

4. Non-Functional Requirements

This functional specification provides a detailed roadmap for developing the Trainit System, ensuring all aspects of education management, learning environments, and facilities are covered, all while maintaining the crucial link to the Business entity.

Q Core Subscription Services

using System;
using System.Collections.Generic;
using System.Linq; // For LINQ operations like .FirstOrDefault(), .Any()

namespace QCoreSubscriptionProgram
{
// --- Introduction Narrative ---
/*
* This C# program simulates the core logic for a "Q Core" subscription program,
* specifically focusing on the business registration process and the associated
* initial payment. It's designed to be a self-contained example that you can
* easily copy and paste into a C# console application project.
*
* The program defines simplified data models for 'Business', 'SubscriptionPlan',
* 'BusinessSubscription', and 'Payment' to represent the key entities involved
* in a subscription system. For a real-world application, these models would
* typically interact with a database (e.g., PostgreSQL using Entity Framework Core)
* rather than in-memory lists.
*
* The `QCoreSubscriptionService` class contains the main business logic:
* 1. Initializing predefined subscription plans (like "Q Core" with its £101 fee).
* 2. Validating input during business registration.
* 3. Creating a new business record.
* 4. Associating the business with a chosen subscription plan.
* 5. Recording the initial payment for the registration.
*
* The `Main` method in the `Program` class orchestrates a demonstration,
* showing successful registrations for different plans, and also showcasing
* scenarios where registration might fail due to invalid input or duplicate entries.
* Output to the console will provide step-by-step feedback on each operation.
*/

// --- 1. Data Models (Simplified representations of database tables) ---

// Corresponds to a simplified 'Business' table
public class Business
{
public Guid BusinessId { get; set; }
public string Name { get; set; }
public string ContactEmail { get; set; }
public DateTime CreatedAt { get; set; }
public bool IsActive { get; set; } // Indicates if the business is active in the system
}

// Corresponds to a simplified 'SubscriptionPlans' table
public class SubscriptionPlan
{
public Guid PlanId { get; set; }
public string PlanName { get; set; }
public string Description { get; set; }
public decimal PerRegistrationFee { get; set; } // The £101 fee for "Q Core"
public bool IsActive { get; set; }
}

// Corresponds to a simplified 'BusinessSubscriptions' table
public class BusinessSubscription
{
public Guid SubscriptionId { get; set; }
public Guid BusinessId { get; set; } // Foreign Key to Business
public Guid PlanId { get; set; } // Foreign Key to SubscriptionPlans
public DateTime StartDate { get; set; }
public string Status { get; set; } // e.g., "Active", "Cancelled", "PendingPayment"
}

// Corresponds to a simplified 'Fees' or 'Payments' table
public class Payment
{
public Guid PaymentId { get; set; }
public Guid BusinessId { get; set; } // Foreign Key to Business
public Guid SubscriptionId { get; set; } // Foreign Key to BusinessSubscriptions
public string FeeType { get; set; } // e.g., "Business Registration Fee"
public decimal Amount { get; set; }
public DateTime PaymentDate { get; set; }
public string Status { get; set; } // e.g., "Paid", "Pending", "Failed"
}

// --- 2. Subscription Service (Core Business Logic) ---

public class QCoreSubscriptionService
{
// Simulate in-memory "database tables" for demonstration
private readonly List<Business> _businesses;
private readonly List<SubscriptionPlan> _subscriptionPlans;
private readonly List<BusinessSubscription> _businessSubscriptions;
private readonly List<Payment> _payments;

public QCoreSubscriptionService()
{
_businesses = new List<Business>();
_subscriptionPlans = new List<SubscriptionPlan>();
_businessSubscriptions = new List<BusinessSubscription>();
_payments = new List<Payment>();

// Initialize with the "Q Core" plan
InitializeSubscriptionPlans();
}

private void InitializeSubscriptionPlans()
{
// Define the "Q Core" plan as specified
_subscriptionPlans.Add(new SubscriptionPlan
{
PlanId = Guid.NewGuid(),
PlanName = "Q Core",
Description = "Standard subscription for NVQ Administration System.",
PerRegistrationFee = 101.00m, // £101
IsActive = true
});

// Add other plans like "Erasmus Business Plan", "Kevin Basic Plan" if needed
_subscriptionPlans.Add(new SubscriptionPlan
{
PlanId = Guid.NewGuid(),
PlanName = "Erasmus Business Plan",
Description = "Premium plan for Erasmus program participants.",
PerRegistrationFee = 150.00m,
IsActive = true
});

_subscriptionPlans.Add(new SubscriptionPlan
{
PlanId = Guid.NewGuid(),
PlanName = "Kevin Basic Plan",
Description = "Basic plan for Kevin's users.",
PerRegistrationFee = 80.00m,
IsActive = true
});
}

/// <summary>
/// Registers a new business, subscribes it to a specified plan (e.g., "Q Core"),
/// and records the initial registration payment.
/// </summary>
/// <param name="businessName">The name of the new business.</param>
/// <param name="contactEmail">The contact email for the business.</param>
/// <param name="planName">The name of the subscription plan (e.g., "Q Core").</param>
/// <returns>True if registration and subscription are successful, false otherwise.</returns>
public bool RegisterBusinessAndSubscribe(string businessName, string contactEmail, string planName)
{
// --- Step 1: Validate input ---
if (string.IsNullOrWhiteSpace(businessName) || string.IsNullOrWhiteSpace(contactEmail) || string.IsNullOrWhiteSpace(planName))
{
Console.WriteLine("Error: Business name, contact email, and plan name cannot be empty.");
return false;
}

// Check if business already exists (by email for simplicity)
if (_businesses.Any(b => b.ContactEmail.Equals(contactEmail, StringComparison.OrdinalIgnoreCase)))
{
Console.WriteLine($"Error: A business with email '{contactEmail}' already exists.");
return false;
}

// --- Step 2: Retrieve the Subscription Plan ---
var selectedPlan = _subscriptionPlans.FirstOrDefault(p => p.PlanName.Equals(planName, StringComparison.OrdinalIgnoreCase) && p.IsActive);
if (selectedPlan == null)
{
Console.WriteLine($"Error: Subscription plan '{planName}' not found or is inactive.");
return false;
}

// --- Step 3: Create the New Business Record ---
var newBusiness = new Business
{
BusinessId = Guid.NewGuid(),
Name = businessName,
ContactEmail = contactEmail,
CreatedAt = DateTime.UtcNow,
IsActive = true
};
_businesses.Add(newBusiness);
Console.WriteLine($"Business '{newBusiness.Name}' (ID: {newBusiness.BusinessId}) registered successfully.");

// --- Step 4: Create the Business Subscription Record ---
var newSubscription = new BusinessSubscription
{
SubscriptionId = Guid.NewGuid(),
BusinessId = newBusiness.BusinessId,
PlanId = selectedPlan.PlanId,
StartDate = DateTime.UtcNow,
Status = "Active" // Or "PendingPayment" if payment is async
};
_businessSubscriptions.Add(newSubscription);
Console.WriteLine($"Business subscribed to '{selectedPlan.PlanName}' (Subscription ID: {newSubscription.SubscriptionId}).");

// --- Step 5: Record the Payment for Registration ---
var registrationPayment = new Payment
{
PaymentId = Guid.NewGuid(),
BusinessId = newBusiness.BusinessId,
SubscriptionId = newSubscription.SubscriptionId,
FeeType = $"Initial Registration Fee - {selectedPlan.PlanName}",
Amount = selectedPlan.PerRegistrationFee,
PaymentDate = DateTime.UtcNow,
Status = "Paid" // Assuming immediate payment for this demo
};
_payments.Add(registrationPayment);
Console.WriteLine($"Payment of £{registrationPayment.Amount:F2} recorded for registration (Payment ID: {registrationPayment.PaymentId}).");

Console.WriteLine("------------------------------------------");
return true;
}

// --- Utility Methods to List Data (for verification) ---

public void ListAllBusinesses()
{
Console.WriteLine("\n--- Registered Businesses ---");
if (!_businesses.Any())
{
Console.WriteLine("No businesses registered yet.");
return;
}
foreach (var business in _businesses)
{
Console.WriteLine($"ID: {business.BusinessId}\n Name: {business.Name}\n Email: {business.ContactEmail}\n Active: {business.IsActive}");
}
}

public void ListAllSubscriptions()
{
Console.WriteLine("\n--- Business Subscriptions ---");
if (!_businessSubscriptions.Any())
{
Console.WriteLine("No subscriptions active yet.");
return;
}
foreach (var sub in _businessSubscriptions)
{
var business = _businesses.FirstOrDefault(b => b.BusinessId == sub.BusinessId);
var plan = _subscriptionPlans.FirstOrDefault(p => p.PlanId == sub.PlanId);
Console.WriteLine($"ID: {sub.SubscriptionId}\n Business: {business?.Name ?? "N/A"}\n Plan: {plan?.PlanName ?? "N/A"}\n Status: {sub.Status}\n Start Date: {sub.StartDate}");
}
}

public void ListAllPayments()
{
Console.WriteLine("\n--- Recorded Payments ---");
if (!_payments.Any())
{
Console.WriteLine("No payments recorded yet.");
return;
}
foreach (var payment in _payments)
{
var business = _businesses.FirstOrDefault(b => b.BusinessId == payment.BusinessId);
Console.WriteLine($"ID: {payment.PaymentId}\n Business: {business?.Name ?? "N/A"}\n Type: {payment.FeeType}\n Amount: £{payment.Amount:F2}\n Status: {payment.Status}\n Date: {payment.PaymentDate}");
}
}
}

// --- 3. Main Program (Demonstration) ---

class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting Q Core Subscription Program Demonstration...");
var service = new QCoreSubscriptionService();

// --- Scenario 1: Successful "Q Core" Business Registration ---
Console.WriteLine("\n--- Attempting to register Business A with Q Core plan ---");
service.RegisterBusinessAndSubscribe("Business A", "business.a@example.com", "Q Core");

// --- Scenario 2: Successful "Erasmus Business Plan" Registration ---
Console.WriteLine("\n--- Attempting to register Business B with Erasmus Business Plan ---");
service.RegisterBusinessAndSubscribe("Business B", "business.b@example.com", "Erasmus Business Plan");

// --- Scenario 3: Attempt to register with a non-existent plan ---
Console.WriteLine("\n--- Attempting to register Business C with NonExistent Plan ---");
service.RegisterBusinessAndSubscribe("Business C", "business.c@example.com", "NonExistent Plan");

// --- Scenario 4: Attempt to re-register an existing business ---
Console.WriteLine("\n--- Attempting to re-register Business A ---");
service.RegisterBusinessAndSubscribe("Business A Duplicate", "business.a@example.com", "Q Core");

// --- Listing all data to verify ---
service.ListAllBusinesses();
service.ListAllSubscriptions();
service.ListAllPayments();

Console.WriteLine("\nDemonstration complete. Press any key to exit.");
Console.ReadKey();
}
}
}

Design Specification

Trainit System Design Specification

This document outlines the technical design for the Trainit System, detailing the architectural choices, technology stack, and implementation approach for the functional requirements previously specified. It explicitly addresses the "Add," "Amend" (Update), "List" (Read), and "Delete" (CRUD) operations for key entities.

1. Architecture Overview

The Trainit System will adopt a Client-Server Architecture with a clear separation of concerns, likely following a Model-View-Controller (MVC) or Model-View-ViewModel (MVVM) pattern on the backend and a component-based approach on the frontend.

graph TD    A[Web Browser (Client)] -->|HTTP/HTTPS| B[Backend API (Server)]    B -->|Database Driver/ORM| C[Database]    B -->|API Endpoints| D[Business Logic]    D -->|Data Access Layer| C    B -->|Authentication/Authorization| E[Security Module]    E -->|User Data| C

2. Technology Stack (Suggested)

These are suggestions; actual choices may vary based on team expertise and specific project requirements.

3. Data Flow

  1. User Interaction: User interacts with the frontend application (e.g., clicks a button, fills a form).

  2. Frontend Request: The frontend sends an HTTP request (GET, POST, PUT, DELETE) to the appropriate backend API endpoint.

  3. Backend Processing:

    • Authentication & Authorization: The backend verifies the user's identity and permissions based on their role and the requested action.

    • Input Validation: Request data is validated against defined schemas to prevent invalid input.

    • Business Logic: Core logic is executed (e.g., calculating grades, checking room availability).

    • Database Interaction: The backend uses its ORM/ODM to perform CRUD operations on the database.

  4. Database Response: The database returns data to the backend.

  5. Backend Response: The backend formats the data (usually JSON) and sends an HTTP response back to the frontend.

  6. Frontend Update: The frontend receives the response and updates the UI accordingly (e.g., displays new data, shows a success message, handles errors).

4. Core Functional Modules - CRUD Implementation Details

Each core functional module will implement standard CRUD operations.

4.1. User Management (Relevant Table: Users)

4.2. Course and Content Management (Relevant Tables: Courses, Modules, Lessons)

4.3. Enrollment and Progress Tracking (Relevant Table: Enrollments)

4.4. Assessment and Grading (Relevant Tables: Assessments, Grades)

4.5. Facilities and Equipment Management (Relevant Tables: Facilities, Rooms, Equipment, RoomEquipment)

4.6. Scheduling (Relevant Table: Schedules)

4.7. Communication and Support (Relevant Tables: Announcements, SupportTickets)

5. API Design Principles

6. User Interface (UI) Considerations

7. Error Handling

8. Security Considerations (Reinforcement)

This design specification provides a detailed blueprint for the development of the Trainit System, focusing on a modular and scalable approach to implement all required functionalities, including comprehensive CRUD operations for each major data entity.

Amend Ticket Processing

// This code demonstrates how to connect to a SQL Server database
// and insert or update data in the SupportTickets table.
// It assumes you are using the System.Data.SqlClient namespace.
// For modern .NET, you might use Microsoft.Data.SqlClient.

using System;
using System.Data;
using System.Data.SqlClient;

public class SupportTicketManager
{
    // Make sure to replace this with your actual database connection string.
    // The connection string includes the server name, database name, and authentication details.
    private static string connectionString = "Server=myServerName;Database=myDatabaseName;User Id=myUsername;Password=myPassword;";

    public static void InsertNewTicket(int businessId, int raisedByUserId, string subject, string description, string status, string priority)
    {
        // SQL query to insert a new ticket.
        // We use parameters (@parameterName) to prevent SQL injection attacks.
        // We do not insert a value for ticket_id, assuming it is an IDENTITY column.
        // created_at and updated_at are set to the current date and time.
        // assigned_to_user_id and resolved_at are set to NULL by default.
        string sqlQuery = @"
            INSERT INTO SupportTickets (
                business_id, 
                raised_by_user_id, 
                subject, 
                description, 
                status, 
                priority, 
                created_at, 
                updated_at
            ) VALUES (
                @business_id, 
                @raised_by_user_id, 
                @subject, 
                @description, 
                @status, 
                @priority, 
                GETDATE(), 
                GETDATE()
            );";

        // Use a 'using' statement to ensure the connection is properly closed and disposed of.
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Use a 'using' statement for the command object as well.
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                // Add parameters and their values to the command.
                // This is the safest way to handle user input in SQL.
                command.Parameters.AddWithValue("@business_id", businessId);
                command.Parameters.AddWithValue("@raised_by_user_id", raisedByUserId);
                command.Parameters.AddWithValue("@subject", subject);
                command.Parameters.AddWithValue("@description", description);
                command.Parameters.AddWithValue("@status", status);
                command.Parameters.AddWithValue("@priority", priority);

                try
                {
                    // Open the database connection.
                    connection.Open();

                    // Execute the command.
                    // ExecuteNonQuery is used for commands that do not return data (like INSERT, UPDATE, DELETE).
                    int rowsAffected = command.ExecuteNonQuery();

                    Console.WriteLine($"{rowsAffected} row(s) inserted successfully.");
                }
                catch (SqlException ex)
                {
                    // Handle and log any SQL-related errors.
                    Console.WriteLine("An SQL error occurred: " + ex.Message);
                }
                catch (Exception ex)
                {
                    // Handle any other general exceptions.
                    Console.WriteLine("An unexpected error occurred: " + ex.Message);
                }
            }
        }
    }

    /// <summary>
    /// Updates an existing support ticket with new values.
    /// Note: The ticket_id must exist in the database.
    /// </summary>
    public static void UpdateTicket(int ticketId, int assignedToUserId, string status, string priority, DateTime? resolvedAt = null)
    {
        // SQL query to update an existing ticket.
        // We use parameters to safely update the record.
        // The updated_at field is set to the current date and time.
        string sqlQuery = @"
            UPDATE SupportTickets
            SET
                assigned_to_user_id = @assigned_to_user_id,
                status = @status,
                priority = @priority,
                updated_at = GETDATE(),
                resolved_at = @resolved_at
            WHERE
                ticket_id = @ticket_id;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                // Add parameters for the update command.
                command.Parameters.AddWithValue("@ticket_id", ticketId);
                command.Parameters.AddWithValue("@assigned_to_user_id", assignedToUserId);
                command.Parameters.AddWithValue("@status", status);
                command.Parameters.AddWithValue("@priority", priority);

                // Handle the nullable DateTime parameter.
                if (resolvedAt.HasValue)
                {
                    command.Parameters.AddWithValue("@resolved_at", resolvedAt.Value);
                }
                else
                {
                    command.Parameters.AddWithValue("@resolved_at", DBNull.Value);
                }

                try
                {
                    connection.Open();
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) updated successfully.");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("An SQL error occurred: " + ex.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("An unexpected error occurred: " + ex.Message);
                }
            }
        }
    }

    // Example usage of the InsertNewTicket and UpdateTicket methods
    public static void Main(string[] args)
    {
        // First, insert a new ticket.
        InsertNewTicket(
            businessId: 101, 
            raisedByUserId: 202, 
            subject: "Printer Malfunction", 
            description: "The office printer is not printing properly and is making a strange noise.", 
            status: "Open", 
            priority: "High"
        );
        
        // This is a placeholder since we don't have the new ticket_id from the INSERT operation.
        // In a real application, you would get the new ID after inserting.
        int existingTicketId = 1; 

        // Then, update the ticket.
        UpdateTicket(
            ticketId: existingTicketId,
            assignedToUserId: 303,
            status: "In Progress",
            priority: "High"
        );
    }
}

Pseudo Code

Here is the pseudo-code for typical SQL operations:

SQL Pseudo-code for CRUD Operations

Let's assume we're working with a table named Courses (from your Trainit schema) with simplified fields: course_id, business_id, course_name, description.

1. Add (Create)

Purpose: To insert a new record into a table.

FUNCTION AddCourse(courseName, description, businessId)    // SQL INSERT statement to add a new row to the Courses table    SQL_QUERY = "INSERT INTO Courses (business_id, course_name, description)                 VALUES (:businessId, :courseName, :description);"    // Execute the query with provided parameters    EXECUTE SQL_QUERY WITH PARAMS {        businessId: businessId,        courseName: courseName,        description: description    }    // Check for success or failure    IF query_successful THEN        RETURN "Course added successfully."    ELSE        RETURN "Error adding course."    END IFEND FUNCTION

2. Amend (Update)

Purpose: To modify existing records in a table.

FUNCTION AmendCourse(courseId, newCourseName, newDescription, businessId)    // SQL UPDATE statement to modify an existing row    // The WHERE clause ensures only the specific course for the given business is updated    SQL_QUERY = "UPDATE Courses                 SET course_name = :newCourseName,                     description = :newDescription                 WHERE course_id = :courseId AND business_id = :businessId;"    // Execute the query with provided parameters    EXECUTE SQL_QUERY WITH PARAMS {        newCourseName: newCourseName,        newDescription: newDescription,        courseId: courseId,        businessId: businessId    }    // Check for success or failure    IF rows_affected > 0 THEN        RETURN "Course updated successfully."    ELSE        RETURN "Course not found or no changes made."    END IFEND FUNCTION

3. List (Read/Retrieve)

Purpose: To fetch one or more records from a table.

FUNCTION ListCourses(businessId, filterByActiveStatus = TRUE, searchKeyword = NULL)    // SQL SELECT statement to retrieve courses    // Filters by business_id (mandatory) and optional active status or search keyword    SQL_QUERY = "SELECT course_id, course_name, description, is_active                 FROM Courses                 WHERE business_id = :businessId"    // Add optional filters    IF filterByActiveStatus IS NOT NULL THEN        SQL_QUERY = SQL_QUERY + " AND is_active = :filterByActiveStatus"    END IF    IF searchKeyword IS NOT NOT NULL THEN        SQL_QUERY = SQL_QUERY + " AND (course_name LIKE '%' || :searchKeyword || '%' OR description LIKE '%' || :searchKeyword || '%')"    END IF    SQL_QUERY = SQL_QUERY + " ORDER BY course_name ASC;" // Order results    // Execute the query    RESULTS = EXECUTE SQL_QUERY WITH PARAMS {        businessId: businessId,        filterByActiveStatus: filterByActiveStatus,        searchKeyword: searchKeyword    }    // Check if results were found    IF RESULTS.count > 0 THEN        RETURN RESULTS    ELSE        RETURN "No courses found."    END IFEND FUNCTIONFUNCTION GetSingleCourse(courseId, businessId)    // SQL SELECT statement to retrieve a single course by its ID    SQL_QUERY = "SELECT course_id, course_name, description, is_active                 FROM Courses                 WHERE course_id = :courseId AND business_id = :businessId LIMIT 1;"    // Execute the query    RESULT = EXECUTE SQL_QUERY WITH PARAMS {        courseId: courseId,        businessId: businessId    }    IF RESULT IS NOT NULL THEN        RETURN RESULT    ELSE        RETURN "Course not found."    END IFEND FUNCTION

4. Delete (Soft Delete Example)

Purpose: To remove records from a table. This example shows a soft delete, which is generally recommended for auditing and data integrity.

FUNCTION DeleteCourseSoft(courseId, businessId)    // SQL UPDATE statement to mark a course as inactive (soft delete)    // Ensures only the course belonging to the specified business is affected    SQL_QUERY = "UPDATE Courses                 SET is_active = FALSE                 WHERE course_id = :courseId AND business_id = :businessId;"    // Execute the query    EXECUTE SQL_QUERY WITH PARAMS {        courseId: courseId,        businessId: businessId    }    // Check for success or failure    IF rows_affected > 0 THEN        RETURN "Course successfully soft-deleted (marked inactive)."    ELSE        RETURN "Course not found or could not be soft-deleted."    END IFEND FUNCTION// Hard Delete (Use with Caution!)FUNCTION DeleteCourseHard(courseId, businessId)    // SQL DELETE statement to permanently remove a course    // WARNING: This will also remove all related data (modules, lessons, enrollments, etc.)    // if cascading deletes are configured in the database schema.    SQL_QUERY = "DELETE FROM Courses                 WHERE course_id = :courseId AND business_id = :businessId;"    // Execute the query    EXECUTE SQL_QUERY WITH PARAMS {        courseId: courseId,        businessId: businessId    }    // Check for success or failure    IF rows_affected > 0 THEN        RETURN "Course permanently deleted."    ELSE        RETURN "Course not found or could not be deleted."    END IFEND FUNCTION

This pseudo-code demonstrates the fundamental SQL operations that your backend would perform to manage the data within the Trainit System, aligning with the principles outlined in your Design Specification Canvas.

C# Blazor Azure - SQL Table Example

// 1. Project Setup:
// - Create a new Azure Functions project (e.g., 'TrainitBackend')
// - Choose HTTP Trigger template.
// - Install NuGet Packages:
// - Npgsql (for PostgreSQL connectivity)
// - BCrypt.Net-Core (for password hashing)
// - Microsoft.Extensions.Configuration.Json (for local settings)

// 2. local.settings.json (in your TrainitBackend project root)
// - This file holds your database connection string for local development.
// - Replace placeholders with your actual PostgreSQL database credentials.
/*
{
"IsEncrypted": false,
"Values": {
"AzureWebJobsStorage": "UseDevelopmentStorage=true",
"FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
// Replace with your PostgreSQL connection string
"PostgreSqlConnectionString": "Host=localhost;Port=5432;Database=trainit_db;Username=your_db_user;Password=your_db_password;"
}
}
*/

// 3. UserModels.cs (Define your data models/DTOs)
// - Create a new folder named 'Models' and add this file inside.
using System;
using System.Text.Json.Serialization;

namespace TrainitBackend.Models
{
// Represents the User entity from the database
public class User
{
public Guid UserId { get; set; }
public Guid BusinessId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
// password_hash is for internal use, not exposed in general GET responses
[JsonIgnore] // Exclude from JSON serialization by default
public string PasswordHash { get; set; }
public string UserType { get; set; }
public DateTime RegistrationDate { get; set; }
public DateTime? LastLogin { get; set; } // Nullable
public bool IsActive { get; set; }
public DateTime? UpdatedAt { get; set; } // Nullable
}

// DTO for creating a new user (request body)
public class UserCreateRequest
{
public Guid BusinessId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Password { get; set; } // Plain password for creation
public string UserType { get; set; }
}

// DTO for updating an existing user (request body)
public class UserUpdateRequest
{
// All fields are optional as it's a partial update
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Password { get; set; } // New plain password if changing
public string UserType { get; set; }
public bool? IsActive { get; set; } // Nullable boolean for soft delete/reactivation
}
}


// 4. UserRepository.cs (Data Access Layer)
// - Create a new folder named 'Repositories' and add this file inside.
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Npgsql; // PostgreSQL client
using Microsoft.Extensions.Logging;
using TrainitBackend.Models;
using BCrypt.Net; // For password hashing

namespace TrainitBackend.Repositories
{
public class UserRepository
{
private readonly string _connectionString;
private readonly ILogger<UserRepository> _logger;

public UserRepository(string connectionString, ILogger<UserRepository> logger)
{
_connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
_logger = logger ?? throw new ArgumentNullException(nameof(logger));
}

// --- ADD (Create) User ---
public async Task<User> AddUserAsync(UserCreateRequest request, Guid businessId)
{
if (request == null) throw new ArgumentNullException(nameof(request));

// Hash the password
string passwordHash = BCrypt.Net.BCrypt.HashPassword(request.Password);

// Generate a new UserId (UUID)
Guid newUserId = Guid.NewGuid();

await using (var conn = new NpgsqlConnection(_connectionString))
{
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand(
@"INSERT INTO Users (user_id, business_id, first_name, last_name, email, password_hash, user_type, registration_date, is_active)
VALUES (@userId, @businessId, @firstName, @lastName, @email, @passwordHash, @userType, NOW(), TRUE)
RETURNING user_id, first_name, last_name, email, user_type, registration_date, is_active;", conn))
{
cmd.Parameters.AddWithValue("userId", newUserId);
cmd.Parameters.AddWithValue("businessId", businessId);
cmd.Parameters.AddWithValue("firstName", request.FirstName);
cmd.Parameters.AddWithValue("lastName", request.LastName);
cmd.Parameters.AddWithValue("email", request.Email);
cmd.Parameters.AddWithValue("passwordHash", passwordHash);
cmd.Parameters.AddWithValue("userType", request.UserType);

await using (var reader = await cmd.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
return new User
{
UserId = reader.GetGuid(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
UserType = reader.GetString(4),
RegistrationDate = reader.GetDateTime(5),
IsActive = reader.GetBoolean(6),
BusinessId = businessId // Set explicitly from input
};
}
}
}
}
return null; // Should not happen if RETURNING clause is used
}

// --- AMEND (Update) User ---
public async Task<User> AmendUserAsync(Guid userId, Guid businessId, UserUpdateRequest request)
{
if (request == null) throw new ArgumentNullException(nameof(request));

var updates = new List<string>();
var parameters = new List<NpgsqlParameter>();
int paramIndex = 0;

if (request.FirstName != null) { updates.Add($"first_name = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", request.FirstName)); }
if (request.LastName != null) { updates.Add($"last_name = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", request.LastName)); }
if (request.Email != null) { updates.Add($"email = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", request.Email)); }
if (request.Password != null)
{
string passwordHash = BCrypt.Net.BCrypt.HashPassword(request.Password);
updates.Add($"password_hash = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", passwordHash));
}
if (request.UserType != null) { updates.Add($"user_type = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", request.UserType)); }
if (request.IsActive.HasValue) { updates.Add($"is_active = @p{paramIndex}"); parameters.Add(new NpgsqlParameter($"p{paramIndex++}", request.IsActive.Value)); }

// Always update updated_at timestamp
updates.Add("updated_at = NOW()");

if (updates.Count == 0) return null; // No fields to update

string updateQuery = $"UPDATE Users SET {string.Join(", ", updates)} WHERE user_id = @userId AND business_id = @businessId RETURNING user_id, first_name, last_name, email, user_type, is_active, updated_at;";

await using (var conn = new NpgsqlConnection(_connectionString))
{
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand(updateQuery, conn))
{
cmd.Parameters.AddRange(parameters.ToArray());
cmd.Parameters.AddWithValue("userId", userId);
cmd.Parameters.AddWithValue("businessId", businessId);

await using (var reader = await cmd.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
return new User
{
UserId = reader.GetGuid(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
UserType = reader.GetString(4),
IsActive = reader.GetBoolean(5),
UpdatedAt = reader.GetDateTime(6),
BusinessId = businessId // Set explicitly from input
};
}
}
}
}
return null; // User not found or not updated
}

// --- LIST (Read) Users ---
public async Task<IEnumerable<User>> GetUsersAsync(Guid businessId, string userType = null, bool? isActive = null, string searchKeyword = null)
{
var users = new List<User>();
var queryBuilder = new System.Text.StringBuilder(
"SELECT user_id, business_id, first_name, last_name, email, user_type, registration_date, last_login, is_active, updated_at FROM Users WHERE business_id = @businessId");
var parameters = new List<NpgsqlParameter>
{
new NpgsqlParameter("businessId", businessId)
};

if (userType != null)
{
queryBuilder.Append(" AND user_type = @userType");
parameters.Add(new NpgsqlParameter("userType", userType));
}

if (isActive.HasValue)
{
queryBuilder.Append(" AND is_active = @isActive");
parameters.Add(new NpgsqlParameter("isActive", isActive.Value));
}

if (searchKeyword != null)
{
queryBuilder.Append(" AND (first_name ILIKE @searchKeyword OR last_name ILIKE @searchKeyword OR email ILIKE @searchKeyword)");
parameters.Add(new NpgsqlParameter("searchKeyword", $"%{searchKeyword}%"));
}

queryBuilder.Append(" ORDER BY last_name, first_name ASC;");

await using (var conn = new NpgsqlConnection(_connectionString))
{
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand(queryBuilder.ToString(), conn))
{
cmd.Parameters.AddRange(parameters.ToArray());

await using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
users.Add(new User
{
UserId = reader.GetGuid(0),
BusinessId = reader.GetGuid(1),
FirstName = reader.GetString(2),
LastName = reader.GetString(3),
Email = reader.GetString(4),
UserType = reader.GetString(5),
RegistrationDate = reader.GetDateTime(6),
LastLogin = reader.IsDBNull(7) ? (DateTime?)null : reader.GetDateTime(7),
IsActive = reader.GetBoolean(8),
UpdatedAt = reader.IsDBNull(9) ? (DateTime?)null : reader.GetDateTime(9)
});
}
}
}
}
return users;
}

// --- DELETE (Soft Delete) User ---
public async Task<User> DeleteUserSoftAsync(Guid userId, Guid businessId)
{
await using (var conn = new NpgsqlConnection(_connectionString))
{
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand(
@"UPDATE Users SET is_active = FALSE, updated_at = NOW()
WHERE user_id = @userId AND business_id = @businessId
RETURNING user_id, first_name, last_name, email, is_active, updated_at;", conn))
{
cmd.Parameters.AddWithValue("userId", userId);
cmd.Parameters.AddWithValue("businessId", businessId);

await using (var reader = await cmd.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
return new User
{
UserId = reader.GetGuid(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
IsActive = reader.GetBoolean(4),
UpdatedAt = reader.GetDateTime(5),
BusinessId = businessId // Set explicitly from input
};
}
}
}
}
return null; // User not found or not updated
}
}
}

// 5. Functions.cs (HTTP Trigger Functions)
// - This file will contain your Azure Functions.
using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json; // Used for Newtonsoft.Json for deserialization in Azure Functions. Blazor uses System.Text.Json.
using TrainitBackend.Models;
using TrainitBackend.Repositories;
using Microsoft.Extensions.Configuration; // To get connection string

namespace TrainitBackend
{
public class UserApiFunctions
{
private readonly UserRepository _userRepository;
private readonly ILogger<UserApiFunctions> _logger;
private readonly IConfiguration _configuration; // To access app settings

// Constructor for dependency injection
// This is how Azure Functions injects ILogger and IConfiguration
public UserApiFunctions(ILogger<UserApiFunctions> logger, IConfiguration configuration)
{
_logger = logger;
_configuration = configuration;
string connectionString = _configuration.GetValue<string>("PostgreSqlConnectionString");
_userRepository = new UserRepository(connectionString, logger);
}

// --- 1. Add (Create) User ---
[FunctionName("CreateUser")]
public async Task<IActionResult> CreateUser(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = "users")] HttpRequest req)
{
_logger.LogInformation("C# HTTP trigger function processed a request to create a user.");

string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
UserCreateRequest data = JsonConvert.DeserializeObject<UserCreateRequest>(requestBody);

// Basic validation
if (data == null || string.IsNullOrEmpty(data.FirstName) || string.IsNullOrEmpty(data.LastName) ||
string.IsNullOrEmpty(data.Email) || string.IsNullOrEmpty(data.Password) || string.IsNullOrEmpty(data.UserType) ||
data.BusinessId == Guid.Empty)
{
return new BadRequestObjectResult("Please provide all required user details: BusinessId, FirstName, LastName, Email, Password, UserType.");
}

// More robust email validation (can use System.ComponentModel.DataAnnotations)
try
{
var addr = new System.Net.Mail.MailAddress(data.Email);
}
catch
{
return new BadRequestObjectResult("Invalid email format.");
}

try
{
var newUser = await _userRepository.AddUserAsync(data, data.BusinessId);
if (newUser == null)
{
// This case might be hit if a unique constraint (e.g., email) fails
return new ConflictObjectResult("Email already exists for this business or another conflict occurred.");
}
return new CreatedResult($"/api/users/{newUser.UserId}", newUser); // 201 Created
}
catch (Npgsql.PostgresException pgEx)
{
_logger.LogError(pgEx, "Database error creating user.");
if (pgEx.SqlState == "23505") // Unique violation
{
return new ConflictObjectResult("Email already exists for this business.");
}
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error creating user.");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
}

// --- 2. Amend (Update) User ---
[FunctionName("AmendUser")]
public async Task<IActionResult> AmendUser(
[HttpTrigger(AuthorizationLevel.Function, "put", Route = "users/{userId}")] HttpRequest req,
string userId)
{
_logger.LogInformation($"C# HTTP trigger function processed a request to update user {userId}.");

if (!Guid.TryParse(userId, out Guid userGuid))
{
return new BadRequestObjectResult("Invalid user ID format.");
}

string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
UserUpdateRequest data = JsonConvert.DeserializeObject<UserUpdateRequest>(requestBody);

// Assuming business_id comes from a header or authenticated context in a real app.
// For this demo, let's assume it's also in the request body (less secure for production).
// Or, you could fetch the user first and use their business_id to ensure scope.
// For now, let's enforce a `businessId` in the query parameters for simplicity of demo
// in this function to ensure data isolation.
if (!req.Query.ContainsKey("business_id") || !Guid.TryParse(req.Query["business_id"], out Guid businessId))
{
return new BadRequestObjectResult("Business ID is required as a query parameter for update.");
}

if (data == null ||
(data.FirstName == null && data.LastName == null && data.Email == null && data.Password == null && data.UserType == null && data.IsActive == null))
{
return new BadRequestObjectResult("Please provide at least one field to update.");
}

// Email format validation if email is provided
if (data.Email != null)
{
try { var addr = new System.Net.Mail.MailAddress(data.Email); }
catch { return new BadRequestObjectResult("Invalid email format."); }
}

try
{
var updatedUser = await _userRepository.AmendUserAsync(userGuid, businessId, data);
if (updatedUser == null)
{
return new NotFoundObjectResult($"User with ID {userId} not found or not associated with business ID {businessId}.");
}
return new OkObjectResult(updatedUser); // 200 OK
}
catch (Npgsql.PostgresException pgEx)
{
_logger.LogError(pgEx, "Database error updating user.");
if (pgEx.SqlState == "23505") // Unique violation
{
return new ConflictObjectResult("Email already exists for another user in this business.");
}
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error updating user.");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
}

// --- 3. List (Read) Users ---
[FunctionName("ListUsers")]
public async Task<IActionResult> ListUsers(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = "users")] HttpRequest req)
{
_logger.LogInformation("C# HTTP trigger function processed a request to list users.");

// Business ID is mandatory to scope the request
if (!req.Query.ContainsKey("business_id") || !Guid.TryParse(req.Query["business_id"], out Guid businessId))
{
return new BadRequestObjectResult("Business ID is required as a query parameter.");
}

string userType = req.Query["user_type"];
bool? isActive = null;
if (req.Query.ContainsKey("is_active"))
{
if (bool.TryParse(req.Query["is_active"], out bool parsedIsActive))
{
isActive = parsedIsActive;
}
else
{
return new BadRequestObjectResult("Invalid value for is_active. Must be 'true' or 'false'.");
}
}
string searchKeyword = req.Query["search"];

try
{
var users = await _userRepository.GetUsersAsync(businessId, userType, isActive, searchKeyword);
return new OkObjectResult(users); // 200 OK
}
catch (Exception ex)
{
_logger.LogError(ex, "Error listing users.");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
}

// --- 4. Delete (Soft Delete) User ---
[FunctionName("DeleteUser")]
public async Task<IActionResult> DeleteUser(
[HttpTrigger(AuthorizationLevel.Function, "delete", Route = "users/{userId}")] HttpRequest req,
string userId)
{
_logger.LogInformation($"C# HTTP trigger function processed a request to soft-delete user {userId}.");

if (!Guid.TryParse(userId, out Guid userGuid))
{
return new BadRequestObjectResult("Invalid user ID format.");
}

// Assuming business_id comes from query parameter for simplicity in this demo.
if (!req.Query.ContainsKey("business_id") || !Guid.TryParse(req.Query["business_id"], out Guid businessId))
{
return new BadRequestObjectResult("Business ID is required as a query parameter for delete.");
}

try
{
var deletedUser = await _userRepository.DeleteUserSoftAsync(userGuid, businessId);
if (deletedUser == null)
{
return new NotFoundObjectResult($"User with ID {userId} not found or not associated with business ID {businessId}.");
}
return new OkObjectResult($"User {deletedUser.FirstName} {deletedUser.LastName} (ID: {deletedUser.UserId}) soft-deleted successfully.");
}
catch (Exception ex)
{
_logger.LogError(ex, "Error soft-deleting user.");
return new StatusCodeResult(StatusCodes.Status500InternalServerError);
}
}
}
}

// 6. Program.cs (Azure Function Host Configuration)
// - This file configures dependency injection for your functions.
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Configuration; // Added for IConfiguration

namespace TrainitBackend
{
public class Program
{
public static void Main(string[] args)
{
var host = new HostBuilder()
.ConfigureFunctionsWorkerDefaults()
.ConfigureAppConfiguration(config =>
{
config.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
})
.ConfigureServices(s =>
{
// Register UserRepository as a scoped service
// The connection string is retrieved from configuration
s.AddSingleton<Repositories.UserRepository>(provider =>
{
var configuration = provider.GetRequiredService<IConfiguration>();
var logger = provider.GetRequiredService<ILogger<Repositories.UserRepository>>();
var connectionString = configuration.GetValue<string>("PostgreSqlConnectionString");
return new Repositories.UserRepository(connectionString, logger);
});
// Other services if needed
})
.Build();

host.Run();
}
}
}

19 Tickets

CREATE TABLE SupportTickets ( ticket_id INT PRIMARY KEY, business_id INT, raised_by_user_id INT, assigned_to_user_id INT NULL, subject VARCHAR(255), description TEXT, status VARCHAR(50) CHECK (status IN ('Open', 'In Progress', 'Resolved', 'Closed')), priority VARCHAR(50) CHECK (priority IN ('Low', 'Medium', 'High', 'Urgent')), created_at DATETIME, updated_at DATETIME, resolved_at DATETIME NULL, FOREIGN KEY (business_id) REFERENCES Business(business_id), FOREIGN KEY (raised_by_user_id) REFERENCES Users(user_id), FOREIGN KEY (assigned_to_user_id) REFERENCES Users(user_id));

CREATE TABLE SupportTickets (
ticket_id INT PRIMARY KEY,
business_id INT,
raised_by_user_id INT,
assigned_to_user_id INT NULL,
subject VARCHAR(255),
description TEXT,
status VARCHAR(50) CHECK (status IN ('Open', 'In Progress', 'Resolved', 'Closed')),
priority VARCHAR(50) CHECK (priority IN ('Low', 'Medium', 'High', 'Urgent')),
created_at DATETIME,
updated_at DATETIME,
resolved_at DATETIME NULL,
FOREIGN KEY (business_id) REFERENCES Business(business_id),
FOREIGN KEY (raised_by_user_id) REFERENCES Users(user_id),
FOREIGN KEY (assigned_to_user_id) REFERENCES Users(user_id)


// This code demonstrates how to connect to a SQL Server database// and insert data into the SupportTickets table.// It assumes you are using the System.Data.SqlClient namespace.// For modern .NET, you might use Microsoft.Data.SqlClient.using System;using System.Data;using System.Data.SqlClient;public class SupportTicketManager{ // Make sure to replace this with your actual database connection string. // The connection string includes the server name, database name, and authentication details. private static string connectionString = "Server=myServerName;Database=myDatabaseName;User Id=myUsername;Password=myPassword;"; public static void InsertNewTicket(int businessId, int raisedByUserId, string subject, string description, string status, string priority) { // SQL query to insert a new ticket. // We use parameters (@parameterName) to prevent SQL injection attacks. // We do not insert a value for ticket_id, assuming it is an IDENTITY column. // created_at and updated_at are set to the current date and time. // assigned_to_user_id and resolved_at are set to NULL by default. string sqlQuery = @" INSERT INTO SupportTickets ( business_id, raised_by_user_id, subject, description, status, priority, created_at, updated_at ) VALUES ( @business_id, @raised_by_user_id, @subject, @description, @status, @priority, GETDATE(), GETDATE() );"; // Use a 'using' statement to ensure the connection is properly closed and disposed of. using (SqlConnection connection = new SqlConnection(connectionString)) { // Use a 'using' statement for the command object as well. using (SqlCommand command = new SqlCommand(sqlQuery, connection)) { // Add parameters and their values to the command. // This is the safest way to handle user input in SQL. command.Parameters.AddWithValue("@business_id", businessId); command.Parameters.AddWithValue("@raised_by_user_id", raisedByUserId); command.Parameters.AddWithValue("@subject", subject); command.Parameters.AddWithValue("@description", description); command.Parameters.AddWithValue("@status", status); command.Parameters.AddWithValue("@priority", priority); try { // Open the database connection. connection.Open(); // Execute the command. // ExecuteNonQuery is used for commands that do not return data (like INSERT, UPDATE, DELETE). int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine($"{rowsAffected} row(s) inserted successfully."); } catch (SqlException ex) { // Handle and log any SQL-related errors. Console.WriteLine("An SQL error occurred: " + ex.Message); } catch (Exception ex) { // Handle any other general exceptions. Console.WriteLine("An unexpected error occurred: " + ex.Message); } } } } // Example usage of the InsertNewTicket method public static void Main(string[] args) { InsertNewTicket( businessId: 101, raisedByUserId: 202, subject: "Printer Malfunction", description: "The office printer is not printing properly and is making a strange noise.", status: "Open", priority: "High" ); }}

Studio 54

Medicare

WWWW Intransition

Bookmarks

Bookmarks

ROBS GMG

Bookmarks

Bookmarks

Sodexo

Bookmarks

Bookmarks

Rich sticky notes

Rich text note

Nimrod 1.0 12/12/2023

Bookmarks

Bookmarks

Premier Care

Bookmarks

Bookmarks

Magna Carta

Bookmarks

Bookmarks

Rich sticky notes

Greater Manchester

  • Altrincham Golf Course and Driving Range
  • Ashton-on-Mersey Golf Club
  • Ashton-under-Lyne Golf Club
  • Avro Golf Club
  • Breightmet Golf Club
  • Brookdale Golf Club
  • Bury Golf Club
  • Chorlton-cum-Hardy Golf Club
  • Crompton and Royton Golf Club
  • Davyhulme Park Golf Club
  • Didsbury Golf Club
  • Fairfield Golf Club
  • Flixton Golf Club
  • Gatley Golf Club
  • Great Lever and Farnworth Golf Club
  • Haigh Hall Golf Club
  • Heaton Moor Golf Club
  • Heaton Park Golf Club
  • Houldsworth Golf Club
  • Manchester Golf Club (Hopwood)
  • Northenden Golf Club
  • North Manchester Golf Club
  • Oldham Golf Club
  • Pike Fold Golf Club
  • Prestwich Golf Club
  • Reddish Vale Golf Club
  • Regent Park Golf Club (Bolton)
  • Rochdale Golf Club
  • Romiley Golf Club
  • Sale Golf Club
  • Swinton Park Golf Club
  • Turton Golf Club
  • Whitefield Golf Club (Manchester)
  • Withington Golf Club
  • Worsley Golf Club

    • Sifters Christas Vouchers

    Bookmarks

    Bookmarks

    Red Octopus Home Improvements

    Bookmarks

    Bookmarks

    Richard

    Rich sticky notes

    18 June 2025

    Number 10 Downing Street

    Bookmarks

    Bookmarks

    101 SQL Tables

    Bookmarks

    Bookmarks