165 lines
4.2 KiB
Markdown
165 lines
4.2 KiB
Markdown
# 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.
|