# Excel Pajak

Employee tax data extraction and Excel report generation tool for Indonesian payroll processing.

## Overview

.NET 10.0 console application that extracts employee tax data from PostgreSQL multi-schema databases and generates Excel reports using NPOI library.

## Prerequisites

- .NET 10.0 SDK or later
- PostgreSQL database
- Valid connection string in configuration

## Project Structure

```
excel_pajak/
├── Models/               # Data models (EmployeeInfo, EmployeePayrollInfo)
├── Services/             # Static services (DatabaseService, ExcelService)
├── Examples/             # Code examples
├── appsettings.json      # Application configuration
└── excel_pajak.csproj    # Main project

excel_pajak_test/         # MSTest unit tests
```

## Configuration

Edit [`excel_pajak/appsettings.json`](excel_pajak/appsettings.json) to configure:

```json
{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=payroll;Username=your_user;Password=your_password"
  },
  "ExcelSettings": {
    "TemplatePath": "simulasi_perhitungan_ak.xlsx",
    "OutputDirectory": "Output"
  }
}
```

## Building

```bash
# Build entire solution
dotnet build

# Build specific project
dotnet build excel_pajak
dotnet build excel_pajak_test
```

## Running

```bash
# Run with default settings
dotnet run --project excel_pajak

# Run with command-line arguments
dotnet run --project excel_pajak -- --schema=company_2024 --year=2024

# Run specific schema and year
dotnet run --project excel_pajak -- --schema=company_2025 --year=2025
```

### Command-Line Arguments

| Argument | Description | Required |
|----------|-------------|----------|
| `--schema` | Database schema name | Yes |
| `--year` | Tax year (4 digits) | Yes |
| `--help` | Show help information | No |

## Testing

```bash
# Run all tests
dotnet test

# Run specific test class
dotnet test --filter "FullyQualifiedName~DatabaseServiceTests"

# Run specific test method
dotnet test --filter "FullyQualifiedName~DatabaseServiceTests.ExecuteScalar_NullConnectionString_ThrowsArgumentException"

# Run with detailed output
dotnet test --logger "console;verbosity=detailed"
```

## Workflow

### 1. Configuration Phase
- Load connection string from `appsettings.json`
- Validate schema name and year parameters
- Initialize DatabaseService with connection string

### 2. Data Extraction Phase
- Connect to PostgreSQL database
- Execute query to extract employee tax data
- Query uses `json_agg(row_to_json(t))` for JSON aggregation
- Placeholder replacement: `{schema}` in query template

### 3. Excel Generation Phase
- Load template Excel file (`simulasi_perhitungan_ak.xlsx`)
- Write employee data to specific cells
- Optionally refresh formulas
- Save to Output directory with naming convention: `{EmployeeNumber}_{Year}.xlsx`

### 4. Output Phase
- Generate Excel files in `Output/` directory
- Each file named after employee number and year
- Example: `AA001_2025.xlsx`

## Key Services

### DatabaseService
- Static service for PostgreSQL operations
- Synchronous database operations using Npgsql
- Query execution with schema placeholders

### ExcelService
- Excel file manipulation using NPOI
- Template loading and data writing
- Formula refresh support
- File overwrite control

### ConfigurationService
- Manages application settings
- Loads configuration from appsettings.json

## Architecture

- **Pattern**: Static service classes with Initialize()
- **Database**: Synchronous operations (no async)
- **Serialization**: System.Text.Json with snake_case
- **Validation**: Regex-based schema/year validation

## Anti-Patterns Avoided

- No async database operations (synchronous by design)
- No connection pooling in services
- Static state requires explicit Initialize() before use

## Troubleshooting

### Connection Issues
- Verify PostgreSQL server is running
- Check connection string in appsettings.json
- Ensure database user has appropriate permissions

### Schema Not Found
- Verify schema name exists in database
- Schema names should match pattern: `^[a-zA-Z0-9_-]+$`

### Excel Output Issues
- Ensure Output directory exists
- Check write permissions
- Verify template file exists

## License

Internal use only.