jdescopingtool/DOCUMENTATION/Architecture/CoreProject.md

Core Project

The JdeScoping.Core project contains business logic and data access. It has no ASP.NET dependencies and is fully testable.

Project Structure

JdeScoping.Core/
├── Models/
│   ├── Search.cs                 # Search request + status
│   ├── SearchCriteria.cs         # Search parameters
│   ├── SearchStatus.cs           # Status enum
│   ├── WorkOrder.cs              # Work order entity
│   ├── Lot.cs                    # Lot entity
│   ├── Item.cs                   # Item entity
│   └── DataUpdate.cs             # Sync tracking
├── Interfaces/
│   ├── ISearchRepository.cs      # Local SQL Server cache
│   ├── IJdeDataSource.cs         # JDE data access (interface)
│   ├── ICmsDataSource.cs         # CMS data access (interface)
│   ├── ISearchService.cs         # Search execution
│   └── IExcelExportService.cs    # Excel generation
├── Repositories/
│   ├── SearchRepository.cs       # Dapper against SQL Server
│   ├── Jde/
│   │   ├── JdeOracleDataSource.cs    # Production: Oracle connection
│   │   └── JdeFileDataSource.cs      # Development: File-based data
│   └── Cms/
│       ├── CmsOracleDataSource.cs    # Production: Oracle connection
│       └── CmsFileDataSource.cs      # Development: File-based data
├── Services/
│   ├── SearchService.cs          # Search execution logic
│   ├── ExcelExportService.cs     # ClosedXML generation
│   └── DataSyncOrchestrator.cs   # Sync orchestration logic
└── Auth/
    ├── IAuthService.cs               # Authentication interface
    ├── LdapAuthService.cs            # Production: Real LDAP server
    └── FakeAuthService.cs            # Development: Accepts any credentials

Repository Pattern

Repositories use Dapper for data access. Connections are created per-query and disposed after use.

public class SearchRepository : ISearchRepository
{
    private readonly string _connectionString;

    public SearchRepository(IConfiguration config)
    {
        _connectionString = config.GetConnectionString("SqlServer");
    }

    public async Task<Search> GetByIdAsync(int id)
    {
        using var connection = new SqlConnection(_connectionString);
        return await connection.QuerySingleOrDefaultAsync<Search>(
            "SELECT * FROM Search WHERE Id = @Id", new { Id = id });
    }

    public async Task<int> CreateAsync(Search search)
    {
        using var connection = new SqlConnection(_connectionString);
        return await connection.ExecuteScalarAsync<int>(
            @"INSERT INTO Search (UserId, Criteria, Status, CreatedAt)
              VALUES (@UserId, @Criteria, @Status, @CreatedAt);
              SELECT SCOPE_IDENTITY();", search);
    }
}

Data Source Pattern (JDE/CMS)

JDE and CMS data access uses an interface with two implementations: production (Oracle) and development (file-based). This allows development without Oracle connectivity.

Interface Definition

public interface IJdeDataSource
{
    Task<IEnumerable<WorkOrder>> GetWorkOrdersAsync(DateTime since);
    Task<IEnumerable<Lot>> GetLotsAsync(DateTime since);
    Task<IEnumerable<Item>> GetItemsAsync();
    // ... other data retrieval methods
}

public interface ICmsDataSource
{
    Task<IEnumerable<LotUsage>> GetLotUsageAsync(DateTime since);
    // ... other CMS data methods
}

Production Implementation (Oracle)

public class JdeOracleDataSource : IJdeDataSource
{
    private readonly string _connectionString;

    public JdeOracleDataSource(IConfiguration config)
    {
        _connectionString = config.GetConnectionString("JdeOracle");
    }

    public async Task<IEnumerable<WorkOrder>> GetWorkOrdersAsync(DateTime since)
    {
        using var connection = new OracleConnection(_connectionString);
        return await connection.QueryAsync<WorkOrder>(
            "SELECT * FROM F4801 WHERE UPMJ >= :Since", new { Since = since });
    }
}

Development Implementation (File-based)

public class JdeFileDataSource : IJdeDataSource
{
    private readonly string _dataDirectory;

    public JdeFileDataSource(IConfiguration config)
    {
        _dataDirectory = config["DataSource:FileDirectory"] ?? "DevData";
    }

    public async Task<IEnumerable<WorkOrder>> GetWorkOrdersAsync(DateTime since)
    {
        var filePath = Path.Combine(_dataDirectory, "workorders.json");
        var json = await File.ReadAllTextAsync(filePath);
        var allOrders = JsonSerializer.Deserialize<List<WorkOrder>>(json);
        return allOrders.Where(wo => wo.UpdateDate >= since);
    }
}

Registration by Environment

// In Program.cs
if (builder.Environment.IsDevelopment())
{
    builder.Services.AddScoped<IJdeDataSource, JdeFileDataSource>();
    builder.Services.AddScoped<ICmsDataSource, CmsFileDataSource>();
}
else
{
    builder.Services.AddScoped<IJdeDataSource, JdeOracleDataSource>();
    builder.Services.AddScoped<ICmsDataSource, CmsOracleDataSource>();
}

This pattern enables:

• Development without Oracle database access
• Testing with predictable data sets
• Easy switching between implementations via configuration

Authentication Pattern

Authentication uses the same interface pattern with production and development implementations.

Interface Definition

public interface IAuthService
{
    Task<AuthResult> AuthenticateAsync(string username, string password);
    Task<bool> IsInGroupAsync(string username, string groupName);
}

public class AuthResult
{
    public bool Success { get; set; }
    public string DisplayName { get; set; }
    public string Email { get; set; }
    public string ErrorMessage { get; set; }
}

Production Implementation (LDAP)

public class LdapAuthService : IAuthService
{
    private readonly LdapOptions _options;

    public LdapAuthService(IOptions<LdapOptions> options)
    {
        _options = options.Value;
    }

    public async Task<AuthResult> AuthenticateAsync(string username, string password)
    {
        using var connection = new LdapConnection(_options.Url);
        try
        {
            connection.Bind(new NetworkCredential(username, password));
            // Retrieve user details from directory
            return new AuthResult { Success = true, DisplayName = "..." };
        }
        catch (LdapException)
        {
            return new AuthResult { Success = false, ErrorMessage = "Invalid credentials" };
        }
    }
}

Development Implementation (Fake)

public class FakeAuthService : IAuthService
{
    private readonly AuthOptions _options;

    public FakeAuthService(IOptions<AuthOptions> options)
    {
        _options = options.Value;
    }

    public Task<AuthResult> AuthenticateAsync(string username, string password)
    {
        // Accept any non-empty credentials in development
        if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password))
        {
            return Task.FromResult(new AuthResult
            {
                Success = false,
                ErrorMessage = "Username and password required"
            });
        }

        return Task.FromResult(new AuthResult
        {
            Success = true,
            DisplayName = username,
            Email = $"{username}@dev.local"
        });
    }

    public Task<bool> IsInGroupAsync(string username, string groupName)
    {
        // Always return true in development
        return Task.FromResult(true);
    }
}

Registration by Configuration

// In Program.cs
var authOptions = builder.Configuration.GetSection("Auth").Get<AuthOptions>();

if (authOptions?.UseFakeAuth == true)
{
    builder.Services.AddScoped<IAuthService, FakeAuthService>();
}
else
{
    builder.Services.AddScoped<IAuthService, LdapAuthService>();
}

Porting Strategy

The legacy Dapper queries in LotFinderDB*.cs, JDE*.cs, and CMS*.cs port with minimal changes:

• Update namespaces (System.Data.SqlClient to Microsoft.Data.SqlClient)
• Adapt CMS queries from Sybase to Oracle syntax
• Use async/await consistently

Related Documentation

• Solution Structure
• Data Flow
• Testing