Compare commits
1 Commits
feat/restr
...
feat/recre
| Author | SHA1 | Date | |
|---|---|---|---|
|
59cc102c5a |
@@ -1,210 +0,0 @@
|
||||
# Struktur Tabel Retribusi PBG yang Dioptimalkan
|
||||
|
||||
## Ringkasan Optimasi
|
||||
|
||||
Struktur tabel baru ini **lebih sederhana**, **fokus pada perhitungan**, dan **menghilangkan redundansi** dari struktur sebelumnya.
|
||||
|
||||
## Perbandingan Struktur
|
||||
|
||||
### SEBELUM (Kompleks)
|
||||
|
||||
- `building_functions` - 8 kolom + relationship kompleks
|
||||
- `building_function_parameters` - 12 kolom dengan mismatch model/migration
|
||||
- `retribution_formulas` - Menyimpan formula sebagai string
|
||||
- `retribution_proposals` - 15+ kolom dengan banyak redundansi
|
||||
- `floor_height_indices` - OK, tidak berubah
|
||||
|
||||
### SESUDAH (Sederhana)
|
||||
|
||||
- `building_types` - **7 kolom**, hierarki sederhana
|
||||
- `retribution_indices` - **6 kolom**, parameter calculation saja
|
||||
- `height_indices` - **3 kolom**, sama seperti sebelumnya
|
||||
- `retribution_configs` - **5 kolom**, konfigurasi global
|
||||
- `retribution_calculations` - **8 kolom**, hasil perhitungan saja
|
||||
|
||||
---
|
||||
|
||||
## Detail Struktur Tabel Baru
|
||||
|
||||
### 1. `building_types`
|
||||
|
||||
**Fungsi:** Menyimpan jenis fungsi bangunan dengan hierarki sederhana
|
||||
|
||||
| Kolom | Tipe | Keterangan |
|
||||
| ------------- | ------------ | ---------------------------------- |
|
||||
| `id` | bigint | Primary key |
|
||||
| `code` | varchar(10) | Kode unik (UMKM, KEAGAMAAN, dll) |
|
||||
| `name` | varchar(100) | Nama fungsi bangunan |
|
||||
| `parent_id` | bigint | ID parent (untuk hierarki) |
|
||||
| `level` | tinyint | Level hierarki (1=parent, 2=child) |
|
||||
| `coefficient` | decimal(8,4) | **Koefisien untuk perhitungan** |
|
||||
| `is_free` | boolean | **Apakah gratis (keagamaan, MBR)** |
|
||||
|
||||
### 2. `retribution_indices`
|
||||
|
||||
**Fungsi:** Menyimpan parameter indeks untuk perhitungan (1:1 dengan building_types)
|
||||
|
||||
| Kolom | Tipe | Keterangan |
|
||||
| ----------------------- | ------------ | ---------------------------------- |
|
||||
| `id` | bigint | Primary key |
|
||||
| `building_type_id` | bigint | FK ke building_types |
|
||||
| `ip_permanent` | decimal(8,4) | **Indeks Permanensi** |
|
||||
| `ip_complexity` | decimal(8,4) | **Indeks Kompleksitas** |
|
||||
| `locality_index` | decimal(8,4) | **Indeks Lokalitas** |
|
||||
| `infrastructure_factor` | decimal(8,4) | **Faktor prasarana (default 50%)** |
|
||||
|
||||
### 3. `height_indices`
|
||||
|
||||
**Fungsi:** Indeks ketinggian per lantai (sama seperti sebelumnya)
|
||||
|
||||
| Kolom | Tipe | Keterangan |
|
||||
| -------------- | ------------ | ---------------------------------------- |
|
||||
| `id` | bigint | Primary key |
|
||||
| `floor_number` | tinyint | Nomor lantai (1,2,3,4,5,6) |
|
||||
| `height_index` | decimal(8,6) | **IP Ketinggian (1.0, 1.09, 1.12, dst)** |
|
||||
|
||||
### 4. `retribution_configs`
|
||||
|
||||
**Fungsi:** Konfigurasi global untuk perhitungan (menggantikan hard-coded values)
|
||||
|
||||
| Kolom | Tipe | Keterangan |
|
||||
| ------------- | ------------- | --------------------- |
|
||||
| `id` | bigint | Primary key |
|
||||
| `key` | varchar(50) | Kunci konfigurasi |
|
||||
| `value` | decimal(15,2) | **Nilai konfigurasi** |
|
||||
| `description` | varchar(200) | Deskripsi |
|
||||
|
||||
**Data yang disimpan:**
|
||||
|
||||
- `BASE_VALUE` = 70350 (nilai dasar)
|
||||
- `INFRASTRUCTURE_MULTIPLIER` = 0.5 (50% prasarana)
|
||||
- `HEIGHT_MULTIPLIER` = 0.5 (pengali indeks ketinggian)
|
||||
|
||||
### 5. `retribution_calculations`
|
||||
|
||||
**Fungsi:** Hasil perhitungan retribusi (history)
|
||||
|
||||
| Kolom | Tipe | Keterangan |
|
||||
| -------------------- | ------------- | -------------------------------- |
|
||||
| `id` | bigint | Primary key |
|
||||
| `calculation_id` | varchar(20) | ID unik perhitungan |
|
||||
| `building_type_id` | bigint | FK ke building_types |
|
||||
| `floor_number` | tinyint | Lantai yang dipilih |
|
||||
| `building_area` | decimal(12,2) | **Luas bangunan input** |
|
||||
| `retribution_amount` | decimal(15,2) | **Hasil perhitungan** |
|
||||
| `calculation_detail` | json | **Detail breakdown perhitungan** |
|
||||
| `calculated_at` | timestamp | Waktu perhitungan |
|
||||
|
||||
---
|
||||
|
||||
## Formula Perhitungan
|
||||
|
||||
### Formula Excel yang Diimplementasikan:
|
||||
|
||||
```
|
||||
H13 = coefficient * (ip_permanent + ip_complexity + (0.5 * height_index))
|
||||
|
||||
Main Calculation = building_area * (locality_index * BASE_VALUE * H13)
|
||||
|
||||
Infrastructure = INFRASTRUCTURE_MULTIPLIER * Main Calculation
|
||||
|
||||
Total Retribution = Main Calculation + Infrastructure
|
||||
```
|
||||
|
||||
### Implementasi dalam Service:
|
||||
|
||||
```php
|
||||
// Step 1: Calculate H13 coefficient
|
||||
$h13 = $buildingType->coefficient * (
|
||||
$indices->ip_permanent +
|
||||
$indices->ip_complexity +
|
||||
(0.5 * $heightIndex)
|
||||
);
|
||||
|
||||
// Step 2: Main calculation
|
||||
$mainCalculation = $buildingArea * ($indices->locality_index * $baseValue * $h13);
|
||||
|
||||
// Step 3: Infrastructure (50% additional)
|
||||
$infrastructureCalculation = 0.5 * $mainCalculation;
|
||||
|
||||
// Step 4: Total
|
||||
$totalRetribution = $mainCalculation + $infrastructureCalculation;
|
||||
```
|
||||
|
||||
---
|
||||
|
||||
## Keuntungan Struktur Baru
|
||||
|
||||
### ✅ **Simplicity**
|
||||
|
||||
- **5 tabel** vs 8+ tabel sebelumnya
|
||||
- **Kolom minimal** hanya yang diperlukan untuk perhitungan
|
||||
- **No redundant data** seperti ip_ketinggian di proposals
|
||||
|
||||
### ✅ **Performance**
|
||||
|
||||
- **Proper indexes** untuk query yang sering digunakan
|
||||
- **Normalized structure** mengurangi storage
|
||||
- **Cached configs** untuk values yang jarang berubah
|
||||
|
||||
### ✅ **Maintainability**
|
||||
|
||||
- **Clear separation** antara master data dan calculation results
|
||||
- **Configurable values** tidak hard-coded lagi
|
||||
- **Single responsibility** setiap tabel punya tujuan jelas
|
||||
|
||||
### ✅ **Flexibility**
|
||||
|
||||
- **Easy to extend** untuk fungsi bangunan baru
|
||||
- **Configurable formulas** lewat RetributionConfig
|
||||
- **Audit trail** lewat calculation history
|
||||
|
||||
### ✅ **Data Integrity**
|
||||
|
||||
- **Proper constraints** untuk validasi data
|
||||
- **Foreign key relationships** yang benar
|
||||
- **No model-migration mismatch**
|
||||
|
||||
---
|
||||
|
||||
## Migration Guide
|
||||
|
||||
### Langkah Implementasi:
|
||||
|
||||
1. **Run Migration:** `php artisan migrate` untuk tabel baru
|
||||
2. **Seed Data:** Data master berdasarkan Excel akan otomatis ter-seed
|
||||
3. **Update Code:** Ganti penggunaan model lama dengan model baru
|
||||
4. **Test Calculation:** Verifikasi hasil perhitungan sama dengan Excel
|
||||
5. **Deploy:** Struktur siap production
|
||||
|
||||
### Data Migration (Optional):
|
||||
|
||||
Jika ada data existing di tabel lama yang perlu dipindahkan, buat script migration untuk transfer data dari struktur lama ke struktur baru.
|
||||
|
||||
---
|
||||
|
||||
## Usage Example
|
||||
|
||||
```php
|
||||
// Initialize service
|
||||
$calculator = new RetributionCalculatorService();
|
||||
|
||||
// Calculate retribution
|
||||
$result = $calculator->calculate(
|
||||
buildingTypeId: 8, // UMKM
|
||||
floorNumber: 2, // 2 lantai
|
||||
buildingArea: 100.50, // 100.5 m2
|
||||
saveResult: true // Simpan ke database
|
||||
);
|
||||
|
||||
// Result structure
|
||||
[
|
||||
'building_type' => [...],
|
||||
'total_retribution' => 31658.25,
|
||||
'formatted_amount' => 'Rp 31,658.25',
|
||||
'calculation_steps' => [...],
|
||||
'calculation_id' => 'RTB-20250130140530-123'
|
||||
]
|
||||
```
|
||||
|
||||
Struktur ini **jauh lebih clean**, **mudah dipahami**, dan **optimal untuk perhitungan retribusi PBG**!
|
||||
@@ -1,467 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Console\Commands;
|
||||
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\RetributionCalculation;
|
||||
use App\Models\BuildingType;
|
||||
use App\Services\RetributionCalculatorService;
|
||||
use Illuminate\Console\Command;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
|
||||
class AssignSpatialPlanningsToCalculation extends Command
|
||||
{
|
||||
/**
|
||||
* The name and signature of the console command.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $signature = 'spatial-planning:assign-calculations
|
||||
{--force : Force assign even if already has calculation}
|
||||
{--recalculate : Recalculate existing calculations with new values}
|
||||
{--chunk=100 : Process in chunks}';
|
||||
|
||||
/**
|
||||
* The console command description.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $description = 'Assign retribution calculations to spatial plannings (supports recalculate for existing calculations)';
|
||||
|
||||
protected $calculatorService;
|
||||
|
||||
public function __construct(RetributionCalculatorService $calculatorService)
|
||||
{
|
||||
parent::__construct();
|
||||
$this->calculatorService = $calculatorService;
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute the console command.
|
||||
*/
|
||||
public function handle()
|
||||
{
|
||||
$this->info('🏗️ Starting spatial planning calculation assignment...');
|
||||
|
||||
// Get processing options
|
||||
$force = $this->option('force');
|
||||
$recalculate = $this->option('recalculate');
|
||||
$chunkSize = (int) $this->option('chunk');
|
||||
|
||||
// Get spatial plannings query
|
||||
$query = SpatialPlanning::query();
|
||||
|
||||
if ($recalculate) {
|
||||
// Recalculate mode: only process those WITH active calculations
|
||||
$query->whereHas('retributionCalculations', function ($q) {
|
||||
$q->where('is_active', true);
|
||||
});
|
||||
$this->info('🔄 Recalculate mode: Processing spatial plannings with existing calculations');
|
||||
} elseif (!$force) {
|
||||
// Normal mode: only process those without active calculations
|
||||
$query->whereDoesntHave('retributionCalculations', function ($q) {
|
||||
$q->where('is_active', true);
|
||||
});
|
||||
$this->info('➕ Normal mode: Processing spatial plannings without calculations');
|
||||
} else {
|
||||
// Force mode: process all
|
||||
$this->info('🔥 Force mode: Processing ALL spatial plannings');
|
||||
}
|
||||
|
||||
$totalRecords = $query->count();
|
||||
|
||||
if ($totalRecords === 0) {
|
||||
$this->warn('No spatial plannings found to process.');
|
||||
return 0;
|
||||
}
|
||||
|
||||
$this->info("Found {$totalRecords} spatial planning(s) to process");
|
||||
|
||||
if (!$this->confirm('Do you want to continue?')) {
|
||||
$this->info('Operation cancelled.');
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Process in chunks
|
||||
$processed = 0;
|
||||
$errors = 0;
|
||||
$reused = 0;
|
||||
$created = 0;
|
||||
$buildingTypeStats = [];
|
||||
|
||||
$progressBar = $this->output->createProgressBar($totalRecords);
|
||||
$progressBar->start();
|
||||
|
||||
$recalculated = 0;
|
||||
|
||||
$query->chunk($chunkSize, function ($spatialPlannings) use (&$processed, &$errors, &$reused, &$created, &$recalculated, &$buildingTypeStats, $progressBar, $recalculate) {
|
||||
foreach ($spatialPlannings as $spatialPlanning) {
|
||||
try {
|
||||
$result = $this->assignCalculationToSpatialPlanning($spatialPlanning, $recalculate);
|
||||
|
||||
if ($result['reused']) {
|
||||
$reused++;
|
||||
} elseif (isset($result['recalculated']) && $result['recalculated']) {
|
||||
$recalculated++;
|
||||
} else {
|
||||
$created++;
|
||||
}
|
||||
|
||||
// Track building type statistics
|
||||
$buildingTypeName = $result['building_type_name'] ?? 'Unknown';
|
||||
if (!isset($buildingTypeStats[$buildingTypeName])) {
|
||||
$buildingTypeStats[$buildingTypeName] = 0;
|
||||
}
|
||||
$buildingTypeStats[$buildingTypeName]++;
|
||||
|
||||
$processed++;
|
||||
} catch (\Exception $e) {
|
||||
$errors++;
|
||||
$this->error("Error processing ID {$spatialPlanning->id}: " . $e->getMessage());
|
||||
}
|
||||
|
||||
$progressBar->advance();
|
||||
}
|
||||
});
|
||||
|
||||
$progressBar->finish();
|
||||
|
||||
// Show summary
|
||||
$this->newLine(2);
|
||||
$this->info('✅ Assignment completed!');
|
||||
|
||||
if ($recalculate) {
|
||||
$this->table(
|
||||
['Metric', 'Count'],
|
||||
[
|
||||
['Total Processed', $processed],
|
||||
['Recalculated (Changed)', $recalculated],
|
||||
['Unchanged', $reused],
|
||||
['Errors', $errors],
|
||||
]
|
||||
);
|
||||
} else {
|
||||
$this->table(
|
||||
['Metric', 'Count'],
|
||||
[
|
||||
['Total Processed', $processed],
|
||||
['Calculations Created', $created],
|
||||
['Calculations Reused', $reused],
|
||||
['Errors', $errors],
|
||||
]
|
||||
);
|
||||
}
|
||||
|
||||
// Show building type statistics
|
||||
if (!empty($buildingTypeStats)) {
|
||||
$this->newLine();
|
||||
$this->info('📊 Building Type Distribution:');
|
||||
$statsRows = [];
|
||||
arsort($buildingTypeStats); // Sort by count descending
|
||||
foreach ($buildingTypeStats as $typeName => $count) {
|
||||
$percentage = round(($count / $processed) * 100, 1);
|
||||
$statsRows[] = [$typeName, $count, $percentage . '%'];
|
||||
}
|
||||
$this->table(['Building Type', 'Count', 'Percentage'], $statsRows);
|
||||
}
|
||||
|
||||
return 0;
|
||||
}
|
||||
|
||||
/**
|
||||
* Assign calculation to a spatial planning
|
||||
*/
|
||||
private function assignCalculationToSpatialPlanning(SpatialPlanning $spatialPlanning, bool $recalculate = false): array
|
||||
{
|
||||
// 1. Detect building type
|
||||
$buildingType = $this->detectBuildingType($spatialPlanning->building_function);
|
||||
|
||||
// 2. Get calculation parameters (round to 2 decimal places)
|
||||
$floorNumber = $spatialPlanning->number_of_floors ?: 1;
|
||||
$buildingArea = round($spatialPlanning->getCalculationArea(), 2);
|
||||
|
||||
if ($buildingArea <= 0) {
|
||||
throw new \Exception("Invalid building area: {$buildingArea}");
|
||||
}
|
||||
|
||||
$reused = false;
|
||||
$isRecalculated = false;
|
||||
|
||||
if ($recalculate) {
|
||||
// Recalculate mode: Always create new calculation
|
||||
$calculationResult = $this->performCalculation($spatialPlanning, $buildingType);
|
||||
|
||||
// Check if spatial planning has existing active calculation
|
||||
$currentActiveCalculation = $spatialPlanning->activeRetributionCalculation;
|
||||
|
||||
if ($currentActiveCalculation) {
|
||||
$oldAmount = $currentActiveCalculation->retributionCalculation->retribution_amount;
|
||||
$oldArea = $currentActiveCalculation->retributionCalculation->building_area;
|
||||
$newAmount = $calculationResult['amount'];
|
||||
|
||||
// Check if there's a significant difference (more than 1 rupiah)
|
||||
if (abs($oldAmount - $newAmount) > 1) {
|
||||
// Create new calculation
|
||||
$calculation = RetributionCalculation::create([
|
||||
'building_type_id' => $buildingType->id,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_area' => $buildingArea,
|
||||
'retribution_amount' => $calculationResult['amount'],
|
||||
'calculation_detail' => $calculationResult['detail'],
|
||||
]);
|
||||
|
||||
// Assign new calculation
|
||||
$spatialPlanning->assignRetributionCalculation(
|
||||
$calculation,
|
||||
"Recalculated: Area {$oldArea}→{$buildingArea}, Amount {$oldAmount}→{$newAmount}"
|
||||
);
|
||||
|
||||
$isRecalculated = true;
|
||||
} else {
|
||||
// No significant difference, keep existing
|
||||
$calculation = $currentActiveCalculation->retributionCalculation;
|
||||
$reused = true;
|
||||
}
|
||||
} else {
|
||||
// No existing calculation, create new
|
||||
$calculation = RetributionCalculation::create([
|
||||
'building_type_id' => $buildingType->id,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_area' => $buildingArea,
|
||||
'retribution_amount' => $calculationResult['amount'],
|
||||
'calculation_detail' => $calculationResult['detail'],
|
||||
]);
|
||||
|
||||
$spatialPlanning->assignRetributionCalculation(
|
||||
$calculation,
|
||||
'Recalculated (new calculation)'
|
||||
);
|
||||
}
|
||||
} else {
|
||||
// Normal mode: Check if calculation already exists with same parameters
|
||||
$existingCalculation = RetributionCalculation::where([
|
||||
'building_type_id' => $buildingType->id,
|
||||
'floor_number' => $floorNumber,
|
||||
])
|
||||
->whereBetween('building_area', [
|
||||
$buildingArea * 0.99, // 1% tolerance
|
||||
$buildingArea * 1.01
|
||||
])
|
||||
->first();
|
||||
|
||||
if ($existingCalculation) {
|
||||
// Reuse existing calculation
|
||||
$calculation = $existingCalculation;
|
||||
$reused = true;
|
||||
} else {
|
||||
// Create new calculation
|
||||
$calculationResult = $this->performCalculation($spatialPlanning, $buildingType);
|
||||
|
||||
$calculation = RetributionCalculation::create([
|
||||
'building_type_id' => $buildingType->id,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_area' => $buildingArea,
|
||||
'retribution_amount' => $calculationResult['amount'],
|
||||
'calculation_detail' => $calculationResult['detail'],
|
||||
]);
|
||||
}
|
||||
|
||||
// Assign to spatial planning
|
||||
$spatialPlanning->assignRetributionCalculation(
|
||||
$calculation,
|
||||
$reused ? 'Auto-assigned (reused calculation)' : 'Auto-assigned (new calculation)'
|
||||
);
|
||||
}
|
||||
|
||||
return [
|
||||
'calculation' => $calculation,
|
||||
'reused' => $reused,
|
||||
'recalculated' => $isRecalculated,
|
||||
'building_type_name' => $buildingType->name,
|
||||
'building_type_code' => $buildingType->code,
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Detect building type based on building function using database
|
||||
*/
|
||||
private function detectBuildingType(string $buildingFunction = null): BuildingType
|
||||
{
|
||||
$function = strtolower($buildingFunction ?? '');
|
||||
|
||||
// Mapping building functions to building type codes from database
|
||||
$mappings = [
|
||||
// Religious
|
||||
'masjid' => 'KEAGAMAAN',
|
||||
'gereja' => 'KEAGAMAAN',
|
||||
'vihara' => 'KEAGAMAAN',
|
||||
'pura' => 'KEAGAMAAN',
|
||||
'keagamaan' => 'KEAGAMAAN',
|
||||
'religious' => 'KEAGAMAAN',
|
||||
|
||||
// Residential/Housing
|
||||
'rumah' => 'HUN_SEDH', // Default to simple housing
|
||||
'perumahan' => 'HUN_SEDH',
|
||||
'hunian' => 'HUN_SEDH',
|
||||
'residential' => 'HUN_SEDH',
|
||||
'tinggal' => 'HUN_SEDH',
|
||||
'mbr' => 'MBR', // Specifically for MBR
|
||||
'masyarakat berpenghasilan rendah' => 'MBR',
|
||||
|
||||
// Commercial/Business - default to UMKM
|
||||
'toko' => 'UMKM',
|
||||
'warung' => 'UMKM',
|
||||
'perdagangan' => 'UMKM',
|
||||
'dagang' => 'UMKM',
|
||||
'usaha' => 'UMKM',
|
||||
'komersial' => 'UMKM',
|
||||
'commercial' => 'UMKM',
|
||||
'pasar' => 'UMKM',
|
||||
'kios' => 'UMKM',
|
||||
|
||||
// Large commercial
|
||||
'mall' => 'USH_BESAR',
|
||||
'plaza' => 'USH_BESAR',
|
||||
'supermarket' => 'USH_BESAR',
|
||||
'department' => 'USH_BESAR',
|
||||
'hotel' => 'USH_BESAR',
|
||||
'resort' => 'USH_BESAR',
|
||||
|
||||
// Office
|
||||
'kantor' => 'UMKM', // Can be UMKM or USH_BESAR depending on size
|
||||
'perkantoran' => 'UMKM',
|
||||
'office' => 'UMKM',
|
||||
|
||||
// Industry (usually big business)
|
||||
'industri' => 'USH_BESAR',
|
||||
'pabrik' => 'USH_BESAR',
|
||||
'gudang' => 'USH_BESAR',
|
||||
'warehouse' => 'USH_BESAR',
|
||||
'manufacturing' => 'USH_BESAR',
|
||||
|
||||
// Social/Cultural
|
||||
'sekolah' => 'SOSBUDAYA',
|
||||
'pendidikan' => 'SOSBUDAYA',
|
||||
'universitas' => 'SOSBUDAYA',
|
||||
'kampus' => 'SOSBUDAYA',
|
||||
'rumah sakit' => 'SOSBUDAYA',
|
||||
'klinik' => 'SOSBUDAYA',
|
||||
'kesehatan' => 'SOSBUDAYA',
|
||||
'puskesmas' => 'SOSBUDAYA',
|
||||
'museum' => 'SOSBUDAYA',
|
||||
'perpustakaan' => 'SOSBUDAYA',
|
||||
'gedung olahraga' => 'SOSBUDAYA',
|
||||
|
||||
// Mixed use
|
||||
'campuran' => 'CAMP_KECIL', // Default to small mixed
|
||||
'mixed' => 'CAMP_KECIL',
|
||||
];
|
||||
|
||||
// Try to match building function
|
||||
$detectedCode = null;
|
||||
foreach ($mappings as $keyword => $code) {
|
||||
if (str_contains($function, $keyword)) {
|
||||
$detectedCode = $code;
|
||||
break;
|
||||
}
|
||||
}
|
||||
|
||||
// Find building type in database by code
|
||||
if ($detectedCode) {
|
||||
$buildingType = BuildingType::where('code', $detectedCode)
|
||||
->whereHas('indices') // Only types with indices
|
||||
->first();
|
||||
|
||||
if ($buildingType) {
|
||||
return $buildingType;
|
||||
}
|
||||
}
|
||||
|
||||
// Default to "UMKM" type if not detected (most common business type)
|
||||
$defaultType = BuildingType::where('code', 'UMKM')
|
||||
->whereHas('indices')
|
||||
->first();
|
||||
|
||||
if ($defaultType) {
|
||||
return $defaultType;
|
||||
}
|
||||
|
||||
// Fallback to any available type with indices
|
||||
$fallbackType = BuildingType::whereHas('indices')
|
||||
->where('is_active', true)
|
||||
->first();
|
||||
|
||||
if (!$fallbackType) {
|
||||
throw new \Exception('No building types with indices found in database. Please run: php artisan db:seed --class=RetributionDataSeeder');
|
||||
}
|
||||
|
||||
return $fallbackType;
|
||||
}
|
||||
|
||||
/**
|
||||
* Perform calculation using RetributionCalculatorService
|
||||
*/
|
||||
private function performCalculation(SpatialPlanning $spatialPlanning, BuildingType $buildingType): array
|
||||
{
|
||||
// Round area to 2 decimal places to match database storage format
|
||||
$buildingArea = round($spatialPlanning->getCalculationArea(), 2);
|
||||
$floorNumber = $spatialPlanning->number_of_floors ?: 1;
|
||||
|
||||
try {
|
||||
// Use the same calculation service as TestRetributionCalculation
|
||||
$result = $this->calculatorService->calculate(
|
||||
$buildingType->id,
|
||||
$floorNumber,
|
||||
$buildingArea,
|
||||
false // Don't save to database, we'll handle that separately
|
||||
);
|
||||
|
||||
return [
|
||||
'amount' => $result['total_retribution'],
|
||||
'detail' => [
|
||||
'building_type_id' => $buildingType->id,
|
||||
'building_type_name' => $buildingType->name,
|
||||
'building_type_code' => $buildingType->code,
|
||||
'coefficient' => $result['indices']['coefficient'],
|
||||
'ip_permanent' => $result['indices']['ip_permanent'],
|
||||
'ip_complexity' => $result['indices']['ip_complexity'],
|
||||
'locality_index' => $result['indices']['locality_index'],
|
||||
'height_index' => $result['input_parameters']['height_index'],
|
||||
'infrastructure_factor' => $result['indices']['infrastructure_factor'],
|
||||
'building_area' => $buildingArea,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_function' => $spatialPlanning->building_function,
|
||||
'calculation_steps' => $result['calculation_detail'],
|
||||
'base_value' => $result['input_parameters']['base_value'],
|
||||
'is_free' => $buildingType->is_free,
|
||||
'calculation_date' => now()->toDateTimeString(),
|
||||
'total' => $result['total_retribution'],
|
||||
]
|
||||
];
|
||||
|
||||
} catch (\Exception $e) {
|
||||
// Fallback to basic calculation if service fails
|
||||
$this->warn("Calculation service failed for {$spatialPlanning->name}: {$e->getMessage()}. Using fallback calculation.");
|
||||
|
||||
// Basic fallback calculation
|
||||
$totalAmount = $buildingType->is_free ? 0 : ($buildingArea * 50000);
|
||||
|
||||
return [
|
||||
'amount' => $totalAmount,
|
||||
'detail' => [
|
||||
'building_type_id' => $buildingType->id,
|
||||
'building_type_name' => $buildingType->name,
|
||||
'building_type_code' => $buildingType->code,
|
||||
'building_area' => $buildingArea,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_function' => $spatialPlanning->building_function,
|
||||
'calculation_method' => 'fallback',
|
||||
'error_message' => $e->getMessage(),
|
||||
'is_free' => $buildingType->is_free,
|
||||
'calculation_date' => now()->toDateTimeString(),
|
||||
'total' => $totalAmount,
|
||||
]
|
||||
];
|
||||
}
|
||||
}
|
||||
}
|
||||
288
app/Console/Commands/CalculateRetributionProposalsCommand.php
Normal file
288
app/Console/Commands/CalculateRetributionProposalsCommand.php
Normal file
@@ -0,0 +1,288 @@
|
||||
<?php
|
||||
|
||||
namespace App\Console\Commands;
|
||||
|
||||
use Illuminate\Console\Command;
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\RetributionProposal;
|
||||
use App\Services\RetributionProposalService;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Exception;
|
||||
|
||||
class CalculateRetributionProposalsCommand extends Command
|
||||
{
|
||||
/**
|
||||
* The name and signature of the console command.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $signature = 'retribution:calculate-proposals
|
||||
{--force : Force recalculate existing proposals}
|
||||
{--limit= : Limit number of spatial plannings to process}
|
||||
{--skip-existing : Skip spatial plannings that already have proposals}
|
||||
{--dry-run : Show what would be processed without actually creating proposals}';
|
||||
|
||||
/**
|
||||
* The console command description.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $description = 'Calculate retribution proposals for all spatial plannings';
|
||||
|
||||
protected RetributionProposalService $proposalService;
|
||||
|
||||
public function __construct(RetributionProposalService $proposalService)
|
||||
{
|
||||
parent::__construct();
|
||||
$this->proposalService = $proposalService;
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute the console command.
|
||||
*/
|
||||
public function handle()
|
||||
{
|
||||
$this->info('🧮 Starting Retribution Proposal Calculation...');
|
||||
$this->newLine();
|
||||
|
||||
try {
|
||||
// Get processing options
|
||||
$force = $this->option('force');
|
||||
$limit = $this->option('limit') ? (int) $this->option('limit') : null;
|
||||
$skipExisting = $this->option('skip-existing');
|
||||
$dryRun = $this->option('dry-run');
|
||||
|
||||
// Build query for spatial plannings
|
||||
$query = SpatialPlanning::query();
|
||||
|
||||
if ($skipExisting && !$force) {
|
||||
$query->whereDoesntHave('retributionProposals');
|
||||
}
|
||||
|
||||
if ($limit) {
|
||||
$query->limit($limit);
|
||||
}
|
||||
|
||||
$spatialPlannings = $query->get();
|
||||
$totalCount = $spatialPlannings->count();
|
||||
|
||||
if ($totalCount === 0) {
|
||||
$this->warn('No spatial plannings found to process.');
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Show processing summary
|
||||
$this->info("📊 PROCESSING SUMMARY:");
|
||||
$this->info(" Total Spatial Plannings: {$totalCount}");
|
||||
$this->info(" Force Recalculate: " . ($force ? 'Yes' : 'No'));
|
||||
$this->info(" Skip Existing: " . ($skipExisting ? 'Yes' : 'No'));
|
||||
$this->info(" Dry Run: " . ($dryRun ? 'Yes' : 'No'));
|
||||
$this->newLine();
|
||||
|
||||
if ($dryRun) {
|
||||
$this->showDryRunPreview($spatialPlannings);
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Confirm processing
|
||||
if (!$this->confirm("Process {$totalCount} spatial plannings?")) {
|
||||
$this->info('Operation cancelled.');
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Process spatial plannings
|
||||
$this->processRetributionCalculations($spatialPlannings, $force);
|
||||
|
||||
return 0;
|
||||
|
||||
} catch (Exception $e) {
|
||||
$this->error('Error during retribution calculation: ' . $e->getMessage());
|
||||
return 1;
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Show dry run preview
|
||||
*/
|
||||
private function showDryRunPreview($spatialPlannings)
|
||||
{
|
||||
$this->info('🔍 DRY RUN PREVIEW:');
|
||||
$this->newLine();
|
||||
|
||||
$processable = 0;
|
||||
$withProposals = 0;
|
||||
$withoutFunction = 0;
|
||||
$withoutArea = 0;
|
||||
|
||||
foreach ($spatialPlannings as $spatial) {
|
||||
$hasProposals = $spatial->retributionProposals()->exists();
|
||||
$buildingFunction = $this->proposalService->detectBuildingFunction($spatial->getBuildingFunctionText());
|
||||
$area = $spatial->getCalculationArea();
|
||||
|
||||
if ($hasProposals) {
|
||||
$withProposals++;
|
||||
}
|
||||
|
||||
if (!$buildingFunction) {
|
||||
$withoutFunction++;
|
||||
}
|
||||
|
||||
if ($area <= 0) {
|
||||
$withoutArea++;
|
||||
}
|
||||
|
||||
if ($buildingFunction && $area > 0) {
|
||||
$processable++;
|
||||
}
|
||||
}
|
||||
|
||||
$this->table(
|
||||
['Status', 'Count', 'Description'],
|
||||
[
|
||||
['✅ Processable', $processable, 'Can create retribution proposals'],
|
||||
['🔄 With Existing Proposals', $withProposals, 'Already have proposals (will skip unless --force)'],
|
||||
['❌ Missing Building Function', $withoutFunction, 'Cannot detect building function'],
|
||||
['❌ Missing Area', $withoutArea, 'Area is zero or missing'],
|
||||
]
|
||||
);
|
||||
|
||||
$this->newLine();
|
||||
$this->info("💡 To process: php artisan retribution:calculate-proposals");
|
||||
$this->info("💡 To force recalculate: php artisan retribution:calculate-proposals --force");
|
||||
}
|
||||
|
||||
/**
|
||||
* Process retribution calculations
|
||||
*/
|
||||
private function processRetributionCalculations($spatialPlannings, $force)
|
||||
{
|
||||
$progressBar = $this->output->createProgressBar($spatialPlannings->count());
|
||||
$progressBar->start();
|
||||
|
||||
$stats = [
|
||||
'processed' => 0,
|
||||
'skipped' => 0,
|
||||
'errors' => 0,
|
||||
'created_proposals' => 0
|
||||
];
|
||||
|
||||
foreach ($spatialPlannings as $spatial) {
|
||||
try {
|
||||
$result = $this->processSingleSpatialPlanning($spatial, $force);
|
||||
|
||||
if ($result['status'] === 'processed') {
|
||||
$stats['processed']++;
|
||||
$stats['created_proposals'] += $result['proposals_created'];
|
||||
} elseif ($result['status'] === 'skipped') {
|
||||
$stats['skipped']++;
|
||||
} else {
|
||||
$stats['errors']++;
|
||||
}
|
||||
|
||||
} catch (Exception $e) {
|
||||
$stats['errors']++;
|
||||
$this->newLine();
|
||||
$this->error("Error processing spatial planning ID {$spatial->id}: " . $e->getMessage());
|
||||
}
|
||||
|
||||
$progressBar->advance();
|
||||
}
|
||||
|
||||
$progressBar->finish();
|
||||
$this->newLine(2);
|
||||
|
||||
// Show final statistics
|
||||
$this->showFinalStatistics($stats);
|
||||
}
|
||||
|
||||
/**
|
||||
* Process single spatial planning
|
||||
*/
|
||||
private function processSingleSpatialPlanning(SpatialPlanning $spatial, $force)
|
||||
{
|
||||
// Check if already has proposals
|
||||
if (!$force && $spatial->retributionProposals()->exists()) {
|
||||
return ['status' => 'skipped', 'reason' => 'already_has_proposals'];
|
||||
}
|
||||
|
||||
// Detect building function
|
||||
$buildingFunction = $this->proposalService->detectBuildingFunction($spatial->getBuildingFunctionText());
|
||||
if (!$buildingFunction) {
|
||||
return ['status' => 'error', 'reason' => 'no_building_function'];
|
||||
}
|
||||
|
||||
// Get area
|
||||
$totalArea = $spatial->getCalculationArea();
|
||||
if ($totalArea <= 0) {
|
||||
return ['status' => 'error', 'reason' => 'no_area'];
|
||||
}
|
||||
|
||||
// Get number of floors
|
||||
$numberOfFloors = max(1, $spatial->number_of_floors ?? 1);
|
||||
|
||||
// Delete existing proposals if force mode
|
||||
if ($force) {
|
||||
$spatial->retributionProposals()->delete();
|
||||
}
|
||||
|
||||
$proposalsCreated = 0;
|
||||
|
||||
// Create proposals for each floor
|
||||
for ($floor = 1; $floor <= $numberOfFloors; $floor++) {
|
||||
// Calculate floor area (distribute total area across floors)
|
||||
$floorArea = $totalArea / $numberOfFloors;
|
||||
|
||||
// Create retribution proposal
|
||||
$proposal = $this->proposalService->createProposalForSpatialPlanning(
|
||||
$spatial,
|
||||
$buildingFunction->id,
|
||||
$floor,
|
||||
$floorArea,
|
||||
$totalArea,
|
||||
"Auto-generated from spatial planning calculation"
|
||||
);
|
||||
|
||||
if ($proposal) {
|
||||
$proposalsCreated++;
|
||||
}
|
||||
}
|
||||
|
||||
return [
|
||||
'status' => 'processed',
|
||||
'proposals_created' => $proposalsCreated
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Show final statistics
|
||||
*/
|
||||
private function showFinalStatistics($stats)
|
||||
{
|
||||
$this->info('✅ CALCULATION COMPLETED!');
|
||||
$this->newLine();
|
||||
|
||||
$this->table(
|
||||
['Metric', 'Count'],
|
||||
[
|
||||
['Processed Successfully', $stats['processed']],
|
||||
['Skipped (Already Exists)', $stats['skipped']],
|
||||
['Errors', $stats['errors']],
|
||||
['Total Proposals Created', $stats['created_proposals']],
|
||||
]
|
||||
);
|
||||
|
||||
if ($stats['errors'] > 0) {
|
||||
$this->newLine();
|
||||
$this->warn("⚠️ {$stats['errors']} spatial plannings had errors:");
|
||||
$this->warn(" • Missing building function detection");
|
||||
$this->warn(" • Missing or zero area");
|
||||
$this->warn(" • Other calculation errors");
|
||||
}
|
||||
|
||||
if ($stats['processed'] > 0) {
|
||||
$this->newLine();
|
||||
$this->info("🎉 Successfully created {$stats['created_proposals']} retribution proposals!");
|
||||
$this->info("💡 You can view them using: php artisan retribution:list-proposals");
|
||||
}
|
||||
}
|
||||
}
|
||||
@@ -0,0 +1,321 @@
|
||||
<?php
|
||||
|
||||
namespace App\Console\Commands;
|
||||
|
||||
use Illuminate\Console\Command;
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\RetributionProposal;
|
||||
use App\Services\RetributionProposalService;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Exception;
|
||||
|
||||
class ProcessSpatialPlanningRetributionCommand extends Command
|
||||
{
|
||||
/**
|
||||
* The name and signature of the console command.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $signature = 'spatial:process-retribution
|
||||
{--all : Process all spatial plannings}
|
||||
{--new-only : Process only spatial plannings without retribution proposals}
|
||||
{--force : Force recalculate existing retribution proposals}';
|
||||
|
||||
/**
|
||||
* The console command description.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $description = 'Process and save retribution calculations for spatial plannings';
|
||||
|
||||
protected RetributionProposalService $proposalService;
|
||||
|
||||
public function __construct(RetributionProposalService $proposalService)
|
||||
{
|
||||
parent::__construct();
|
||||
$this->proposalService = $proposalService;
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute the console command.
|
||||
*/
|
||||
public function handle()
|
||||
{
|
||||
$this->info('🏗️ Processing Spatial Planning Retribution Calculations...');
|
||||
$this->newLine();
|
||||
|
||||
try {
|
||||
// Get options
|
||||
$processAll = $this->option('all');
|
||||
$newOnly = $this->option('new-only');
|
||||
$force = $this->option('force');
|
||||
|
||||
// Build query
|
||||
$query = SpatialPlanning::query();
|
||||
|
||||
if ($newOnly || (!$processAll && !$force)) {
|
||||
$query->whereDoesntHave('retributionProposals');
|
||||
$this->info('📋 Mode: Processing only spatial plannings WITHOUT retribution proposals');
|
||||
} elseif ($processAll) {
|
||||
$this->info('📋 Mode: Processing ALL spatial plannings');
|
||||
}
|
||||
|
||||
$spatialPlannings = $query->get();
|
||||
$totalCount = $spatialPlannings->count();
|
||||
|
||||
if ($totalCount === 0) {
|
||||
$this->warn('❌ No spatial plannings found to process.');
|
||||
$this->info('💡 Try running: php artisan spatial:init first');
|
||||
return 0;
|
||||
}
|
||||
|
||||
$this->info("📊 Found {$totalCount} spatial plannings to process");
|
||||
$this->newLine();
|
||||
|
||||
// Show preview of what will be processed
|
||||
$this->showProcessingPreview($spatialPlannings);
|
||||
|
||||
// Confirm processing
|
||||
if (!$this->confirm("Process {$totalCount} spatial plannings and create retribution proposals?")) {
|
||||
$this->info('Operation cancelled.');
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Process all spatial plannings
|
||||
$this->processAllSpatialPlannings($spatialPlannings, $force);
|
||||
|
||||
return 0;
|
||||
|
||||
} catch (Exception $e) {
|
||||
$this->error('❌ Error during processing: ' . $e->getMessage());
|
||||
return 1;
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Show processing preview
|
||||
*/
|
||||
private function showProcessingPreview($spatialPlannings)
|
||||
{
|
||||
$this->info('🔍 PREVIEW:');
|
||||
|
||||
$canProcess = 0;
|
||||
$cannotProcess = 0;
|
||||
$hasExisting = 0;
|
||||
|
||||
$sampleData = [];
|
||||
$errorReasons = [];
|
||||
|
||||
foreach ($spatialPlannings->take(5) as $spatial) {
|
||||
$buildingFunction = $this->proposalService->detectBuildingFunction($spatial->getBuildingFunctionText());
|
||||
$area = $spatial->getCalculationArea();
|
||||
$floors = max(1, $spatial->number_of_floors ?? 1);
|
||||
$hasProposals = $spatial->retributionProposals()->exists();
|
||||
|
||||
if ($hasProposals) {
|
||||
$hasExisting++;
|
||||
}
|
||||
|
||||
if ($buildingFunction && $area > 0) {
|
||||
$canProcess++;
|
||||
$sampleData[] = [
|
||||
'ID' => $spatial->id,
|
||||
'Name' => substr($spatial->name ?? 'N/A', 0, 30),
|
||||
'Function' => $buildingFunction->name ?? 'N/A',
|
||||
'Area' => number_format($area, 2),
|
||||
'Floors' => $floors,
|
||||
'Status' => $hasProposals ? '🔄 Has Proposals' : '✅ Ready'
|
||||
];
|
||||
} else {
|
||||
$cannotProcess++;
|
||||
if (!$buildingFunction) {
|
||||
$errorReasons[] = 'Missing building function';
|
||||
}
|
||||
if ($area <= 0) {
|
||||
$errorReasons[] = 'Missing/zero area';
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
// Show sample data table
|
||||
if (!empty($sampleData)) {
|
||||
$this->table(
|
||||
['ID', 'Name', 'Function', 'Area (m²)', 'Floors', 'Status'],
|
||||
$sampleData
|
||||
);
|
||||
}
|
||||
|
||||
// Show summary
|
||||
$this->newLine();
|
||||
$this->info("📈 SUMMARY:");
|
||||
$this->info(" ✅ Can Process: {$canProcess}");
|
||||
$this->info(" ❌ Cannot Process: {$cannotProcess}");
|
||||
$this->info(" 🔄 Has Existing Proposals: {$hasExisting}");
|
||||
|
||||
if ($cannotProcess > 0) {
|
||||
$this->warn(" ⚠️ Common Issues: " . implode(', ', array_unique($errorReasons)));
|
||||
}
|
||||
|
||||
$this->newLine();
|
||||
}
|
||||
|
||||
/**
|
||||
* Process all spatial plannings
|
||||
*/
|
||||
private function processAllSpatialPlannings($spatialPlannings, $force)
|
||||
{
|
||||
$this->info('🚀 Starting processing...');
|
||||
$this->newLine();
|
||||
|
||||
$progressBar = $this->output->createProgressBar($spatialPlannings->count());
|
||||
$progressBar->start();
|
||||
|
||||
$stats = [
|
||||
'processed' => 0,
|
||||
'skipped' => 0,
|
||||
'errors' => 0,
|
||||
'total_proposals' => 0,
|
||||
'total_amount' => 0
|
||||
];
|
||||
|
||||
$errors = [];
|
||||
|
||||
foreach ($spatialPlannings as $spatial) {
|
||||
try {
|
||||
$result = $this->processSingleSpatialPlanning($spatial, $force);
|
||||
|
||||
if ($result['success']) {
|
||||
$stats['processed']++;
|
||||
$stats['total_proposals'] += $result['proposals_count'];
|
||||
$stats['total_amount'] += $result['total_amount'];
|
||||
} elseif ($result['skipped']) {
|
||||
$stats['skipped']++;
|
||||
} else {
|
||||
$stats['errors']++;
|
||||
$errors[] = "ID {$spatial->id}: " . $result['error'];
|
||||
}
|
||||
|
||||
} catch (Exception $e) {
|
||||
$stats['errors']++;
|
||||
$errors[] = "ID {$spatial->id}: " . $e->getMessage();
|
||||
}
|
||||
|
||||
$progressBar->advance();
|
||||
}
|
||||
|
||||
$progressBar->finish();
|
||||
$this->newLine(2);
|
||||
|
||||
// Show final results
|
||||
$this->showFinalResults($stats, $errors);
|
||||
}
|
||||
|
||||
/**
|
||||
* Process single spatial planning
|
||||
*/
|
||||
private function processSingleSpatialPlanning(SpatialPlanning $spatial, $force)
|
||||
{
|
||||
// Check if already has proposals
|
||||
if (!$force && $spatial->retributionProposals()->exists()) {
|
||||
return ['success' => false, 'skipped' => true];
|
||||
}
|
||||
|
||||
// Detect building function
|
||||
$buildingFunction = $this->proposalService->detectBuildingFunction($spatial->getBuildingFunctionText());
|
||||
if (!$buildingFunction) {
|
||||
return ['success' => false, 'skipped' => false, 'error' => 'Cannot detect building function from: ' . $spatial->getBuildingFunctionText()];
|
||||
}
|
||||
|
||||
// Get area
|
||||
$totalArea = $spatial->getCalculationArea();
|
||||
if ($totalArea <= 0) {
|
||||
return ['success' => false, 'skipped' => false, 'error' => 'Area is zero or missing'];
|
||||
}
|
||||
|
||||
// Get number of floors
|
||||
$numberOfFloors = max(1, $spatial->number_of_floors ?? 1);
|
||||
|
||||
// Delete existing proposals if force mode
|
||||
if ($force) {
|
||||
$spatial->retributionProposals()->delete();
|
||||
}
|
||||
|
||||
$proposalsCount = 0;
|
||||
$totalAmount = 0;
|
||||
|
||||
// Create single proposal for the spatial planning (not per floor to prevent duplicates)
|
||||
// Use the highest floor for IP ketinggian calculation (worst case scenario)
|
||||
$highestFloor = $numberOfFloors;
|
||||
|
||||
$proposal = $this->proposalService->createProposalForSpatialPlanning(
|
||||
$spatial,
|
||||
$buildingFunction->id,
|
||||
$highestFloor, // Use highest floor for calculation
|
||||
$totalArea, // Use total area, not divided by floors
|
||||
$totalArea,
|
||||
"Auto-calculated from spatial planning data (floors: {$numberOfFloors})"
|
||||
);
|
||||
|
||||
if ($proposal) {
|
||||
$proposalsCount = 1;
|
||||
$totalAmount = $proposal->total_retribution_amount;
|
||||
}
|
||||
|
||||
return [
|
||||
'success' => true,
|
||||
'skipped' => false,
|
||||
'proposals_count' => $proposalsCount,
|
||||
'total_amount' => $totalAmount
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Show final results
|
||||
*/
|
||||
private function showFinalResults($stats, $errors)
|
||||
{
|
||||
$this->info('🎉 PROCESSING COMPLETED!');
|
||||
$this->newLine();
|
||||
|
||||
// Main statistics table
|
||||
$this->table(
|
||||
['Metric', 'Count'],
|
||||
[
|
||||
['✅ Successfully Processed', $stats['processed']],
|
||||
['⏭️ Skipped (Already Has Proposals)', $stats['skipped']],
|
||||
['❌ Errors', $stats['errors']],
|
||||
['📄 Total Proposals Created', $stats['total_proposals']],
|
||||
['💰 Total Retribution Amount', 'Rp ' . number_format($stats['total_amount'], 2)],
|
||||
]
|
||||
);
|
||||
|
||||
// Show success message
|
||||
if ($stats['processed'] > 0) {
|
||||
$this->newLine();
|
||||
$this->info("🎊 SUCCESS!");
|
||||
$this->info(" 📊 Created {$stats['total_proposals']} retribution proposals");
|
||||
$this->info(" 💵 Total calculated amount: Rp " . number_format($stats['total_amount'], 2));
|
||||
$this->info(" 📋 Processed {$stats['processed']} spatial plannings");
|
||||
}
|
||||
|
||||
// Show errors if any
|
||||
if ($stats['errors'] > 0) {
|
||||
$this->newLine();
|
||||
$this->warn("⚠️ ERRORS ENCOUNTERED:");
|
||||
foreach (array_slice($errors, 0, 10) as $error) {
|
||||
$this->warn(" • {$error}");
|
||||
}
|
||||
|
||||
if (count($errors) > 10) {
|
||||
$this->warn(" ... and " . (count($errors) - 10) . " more errors");
|
||||
}
|
||||
}
|
||||
|
||||
// Show next steps
|
||||
$this->newLine();
|
||||
$this->info("📋 NEXT STEPS:");
|
||||
$this->info(" • View proposals: php artisan spatial:check-constraints");
|
||||
$this->info(" • Check database: SELECT COUNT(*) FROM retribution_proposals;");
|
||||
$this->info(" • Access via API: GET /api/retribution-proposals");
|
||||
}
|
||||
}
|
||||
125
app/Console/Commands/TestExcelFormulaCommand.php
Normal file
125
app/Console/Commands/TestExcelFormulaCommand.php
Normal file
@@ -0,0 +1,125 @@
|
||||
<?php
|
||||
|
||||
namespace App\Console\Commands;
|
||||
|
||||
use Illuminate\Console\Command;
|
||||
use App\Services\DynamicRetributionCalculationService;
|
||||
|
||||
class TestExcelFormulaCommand extends Command
|
||||
{
|
||||
/**
|
||||
* The name and signature of the console command.
|
||||
*/
|
||||
protected $signature = 'test:excel-formula';
|
||||
|
||||
/**
|
||||
* The console command description.
|
||||
*/
|
||||
protected $description = 'Test Excel formula implementation for PBG retribution calculation';
|
||||
|
||||
/**
|
||||
* Execute the console command.
|
||||
*/
|
||||
public function handle()
|
||||
{
|
||||
$service = new DynamicRetributionCalculationService();
|
||||
|
||||
$this->info('=== TESTING RUNDOWN 4 IMPLEMENTATION ===');
|
||||
$this->info('Formula: =ROUNDDOWN(($E13*($F13+$G13+(0.5*I$3))),4)');
|
||||
$this->newLine();
|
||||
|
||||
// Test 1: Hunian Sederhana
|
||||
$this->info('1. HUNIAN SEDERHANA (1 Lantai, 100 m²)');
|
||||
|
||||
$result1 = $service->calculateRetribution(
|
||||
buildingFunctionId: 10, // HUNIAN_SEDERHANA
|
||||
floorLevel: 1,
|
||||
luasBangunan: 100,
|
||||
indeksLokasi: 'sedang',
|
||||
includeInfrastructure: true
|
||||
);
|
||||
|
||||
if ($result1['success']) {
|
||||
$rundown4 = $result1['calculation_breakdown']['rundown_4_calculation'];
|
||||
$retribution = $result1['calculation_breakdown']['retribution_calculation'];
|
||||
|
||||
$this->info(' ✓ Before ROUNDDOWN: ' . $rundown4['before_rounddown']);
|
||||
$this->info(' ✓ After ROUNDDOWN(4): ' . $rundown4['after_rounddown']);
|
||||
$this->info(' ✓ Basic Retribution: Rp ' . number_format($retribution['basic_amount']));
|
||||
$this->info(' ✓ Infrastructure: Rp ' . number_format($retribution['infrastructure_amount']));
|
||||
$this->info(' 💰 TOTAL: Rp ' . number_format($retribution['total_amount']));
|
||||
} else {
|
||||
$this->error(' ❌ Error: ' . $result1['error']);
|
||||
}
|
||||
$this->newLine();
|
||||
|
||||
// Test 2: Usaha Besar
|
||||
$this->info('2. USAHA BESAR (3 Lantai, 200 m²)');
|
||||
|
||||
$result2 = $service->calculateRetribution(
|
||||
buildingFunctionId: 9, // USAHA_BESAR
|
||||
floorLevel: 3,
|
||||
luasBangunan: 200,
|
||||
indeksLokasi: 'tinggi',
|
||||
includeInfrastructure: true
|
||||
);
|
||||
|
||||
if ($result2['success']) {
|
||||
$rundown4 = $result2['calculation_breakdown']['rundown_4_calculation'];
|
||||
$retribution = $result2['calculation_breakdown']['retribution_calculation'];
|
||||
|
||||
$this->info(' ✓ Rundown 4 Result: ' . $rundown4['after_rounddown']);
|
||||
$this->info(' ✓ Basic: Rp ' . number_format($retribution['basic_amount']));
|
||||
$this->info(' ✓ Infrastructure: Rp ' . number_format($retribution['infrastructure_amount']));
|
||||
$this->info(' 💰 TOTAL: Rp ' . number_format($retribution['total_amount']));
|
||||
}
|
||||
$this->newLine();
|
||||
|
||||
// Test 3: Keagamaan (Free)
|
||||
$this->info('3. KEAGAMAAN (2 Lantai, 150 m²) - BEBAS RETRIBUSI');
|
||||
|
||||
$result3 = $service->calculateRetribution(
|
||||
buildingFunctionId: 1, // KEAGAMAAN
|
||||
floorLevel: 2,
|
||||
luasBangunan: 150,
|
||||
indeksLokasi: 'sedang',
|
||||
includeInfrastructure: true
|
||||
);
|
||||
|
||||
if ($result3['success']) {
|
||||
$this->info(' ✓ Rundown 4 Result: ' . $result3['results']['h5_rundown4']);
|
||||
$this->info(' 💸 RETRIBUSI BEBAS: Rp ' . number_format($result3['results']['total_retribution']));
|
||||
}
|
||||
$this->newLine();
|
||||
|
||||
// Test 4: Multi-Floor Calculation
|
||||
$this->info('4. CAMPURAN BESAR - Multi Lantai (1-4 Lantai, 300 m²)');
|
||||
|
||||
$result4 = $service->calculateMultiFloorRetribution(
|
||||
buildingFunctionId: 7, // CAMPURAN_BESAR
|
||||
floors: [1, 2, 3, 4],
|
||||
luasBangunan: 300,
|
||||
indeksLokasi: 'tinggi'
|
||||
);
|
||||
|
||||
if ($result4['success']) {
|
||||
$this->info(' ✓ Per Floor Calculations:');
|
||||
foreach ($result4['floor_details'] as $floor => $detail) {
|
||||
$this->info(" Lantai {$floor}: H5={$detail['h5_rundown4']}, Total=Rp " . number_format($detail['total_retribution']));
|
||||
}
|
||||
$this->info(' 💰 TOTAL SEMUA LANTAI: Rp ' . number_format($result4['total_retribution']));
|
||||
}
|
||||
$this->newLine();
|
||||
|
||||
$this->info('=== RUNDOWN 4 VERIFICATION ===');
|
||||
$this->info('✅ Formula: =ROUNDDOWN(($E13*($F13+$G13+(0.5*I$3))),4)');
|
||||
$this->info('✅ ROUNDDOWN function with 4 decimal precision');
|
||||
$this->info('✅ Tarif Dasar: Rp 7.035.000');
|
||||
$this->info('✅ Infrastructure calculation: O3 * basic');
|
||||
$this->info('✅ Code simplified - unused formulas removed');
|
||||
$this->newLine();
|
||||
$this->info('🎉 RUNDOWN 4 Implementation is CLEAN and VERIFIED!');
|
||||
|
||||
return Command::SUCCESS;
|
||||
}
|
||||
}
|
||||
@@ -1,265 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Console\Commands;
|
||||
|
||||
use Illuminate\Console\Command;
|
||||
use App\Services\RetributionCalculatorService;
|
||||
use App\Models\BuildingType;
|
||||
|
||||
class TestRetributionCalculation extends Command
|
||||
{
|
||||
/**
|
||||
* The name and signature of the console command.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $signature = 'retribution:test
|
||||
{--area= : Luas bangunan dalam m2}
|
||||
{--floor= : Jumlah lantai (1-6)}
|
||||
{--type= : ID atau kode building type}
|
||||
{--all : Test semua building types}';
|
||||
|
||||
/**
|
||||
* The console command description.
|
||||
*
|
||||
* @var string
|
||||
*/
|
||||
protected $description = 'Test perhitungan retribusi PBG dengan input luas bangunan dan tinggi lantai';
|
||||
|
||||
protected $calculatorService;
|
||||
|
||||
public function __construct(RetributionCalculatorService $calculatorService)
|
||||
{
|
||||
parent::__construct();
|
||||
$this->calculatorService = $calculatorService;
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute the console command.
|
||||
*/
|
||||
public function handle()
|
||||
{
|
||||
$this->info('🏢 SISTEM TEST PERHITUNGAN RETRIBUSI PBG');
|
||||
$this->info('=' . str_repeat('=', 50));
|
||||
|
||||
// Test all building types if --all flag is used
|
||||
if ($this->option('all')) {
|
||||
return $this->testAllBuildingTypes();
|
||||
}
|
||||
|
||||
// Get input parameters
|
||||
$area = $this->getArea();
|
||||
$floor = $this->getFloor();
|
||||
$buildingTypeId = $this->getBuildingType();
|
||||
|
||||
if (!$area || !$floor || !$buildingTypeId) {
|
||||
$this->error('❌ Parameter tidak lengkap!');
|
||||
return 1;
|
||||
}
|
||||
|
||||
// Perform calculation
|
||||
$this->performCalculation($buildingTypeId, $floor, $area);
|
||||
|
||||
return 0;
|
||||
}
|
||||
|
||||
protected function getArea()
|
||||
{
|
||||
$area = $this->option('area');
|
||||
|
||||
if (!$area) {
|
||||
$area = $this->ask('📐 Masukkan luas bangunan (m²)');
|
||||
}
|
||||
|
||||
if (!is_numeric($area) || $area <= 0) {
|
||||
$this->error('❌ Luas bangunan harus berupa angka positif!');
|
||||
return null;
|
||||
}
|
||||
|
||||
return (float) $area;
|
||||
}
|
||||
|
||||
protected function getFloor()
|
||||
{
|
||||
$floor = $this->option('floor');
|
||||
|
||||
if (!$floor) {
|
||||
$floor = $this->ask('🏗️ Masukkan jumlah lantai (1-6)');
|
||||
}
|
||||
|
||||
if (!is_numeric($floor) || $floor < 1 || $floor > 6) {
|
||||
$this->error('❌ Jumlah lantai harus antara 1-6!');
|
||||
return null;
|
||||
}
|
||||
|
||||
return (int) $floor;
|
||||
}
|
||||
|
||||
protected function getBuildingType()
|
||||
{
|
||||
$type = $this->option('type');
|
||||
|
||||
if (!$type) {
|
||||
$this->showBuildingTypes();
|
||||
$type = $this->ask('🏢 Masukkan ID atau kode building type');
|
||||
}
|
||||
|
||||
// Try to find by ID first, then by code
|
||||
$buildingType = null;
|
||||
|
||||
if (is_numeric($type)) {
|
||||
$buildingType = BuildingType::find($type);
|
||||
} else {
|
||||
$buildingType = BuildingType::where('code', strtoupper($type))->first();
|
||||
}
|
||||
|
||||
if (!$buildingType) {
|
||||
$this->error('❌ Building type tidak ditemukan!');
|
||||
return null;
|
||||
}
|
||||
|
||||
return $buildingType->id;
|
||||
}
|
||||
|
||||
protected function showBuildingTypes()
|
||||
{
|
||||
$this->info('📋 DAFTAR BUILDING TYPES:');
|
||||
$this->line('');
|
||||
|
||||
$buildingTypes = BuildingType::with('indices')
|
||||
->whereHas('indices') // Only types that have indices
|
||||
->get();
|
||||
|
||||
$headers = ['ID', 'Kode', 'Nama', 'Coefficient', 'Free'];
|
||||
$rows = [];
|
||||
|
||||
foreach ($buildingTypes as $type) {
|
||||
$rows[] = [
|
||||
$type->id,
|
||||
$type->code,
|
||||
$type->name,
|
||||
$type->indices ? number_format($type->indices->coefficient, 4) : 'N/A',
|
||||
$type->is_free ? '✅' : '❌'
|
||||
];
|
||||
}
|
||||
|
||||
$this->table($headers, $rows);
|
||||
$this->line('');
|
||||
}
|
||||
|
||||
protected function performCalculation($buildingTypeId, $floor, $area)
|
||||
{
|
||||
try {
|
||||
// Round area to 2 decimal places to match database storage format
|
||||
$roundedArea = round($area, 2);
|
||||
$result = $this->calculatorService->calculate($buildingTypeId, $floor, $roundedArea, false);
|
||||
|
||||
$this->displayResults($result, $roundedArea, $floor);
|
||||
|
||||
} catch (\Exception $e) {
|
||||
$this->error('❌ Error: ' . $e->getMessage());
|
||||
return 1;
|
||||
}
|
||||
}
|
||||
|
||||
protected function displayResults($result, $area, $floor)
|
||||
{
|
||||
$this->info('');
|
||||
$this->info('📊 HASIL PERHITUNGAN RETRIBUSI');
|
||||
$this->info('=' . str_repeat('=', 40));
|
||||
|
||||
// Building info
|
||||
$this->line('🏢 <fg=cyan>Building Type:</> ' . $result['building_type']['name']);
|
||||
$this->line('📐 <fg=cyan>Luas Bangunan:</> ' . number_format($area, 0) . ' m²');
|
||||
$this->line('🏗️ <fg=cyan>Jumlah Lantai:</> ' . $floor);
|
||||
|
||||
if (isset($result['building_type']['is_free']) && $result['building_type']['is_free']) {
|
||||
$this->line('');
|
||||
$this->info('🎉 GRATIS - Building type ini tidak dikenakan retribusi');
|
||||
$this->line('💰 <fg=green>Total Retribusi: Rp 0</fg=green>');
|
||||
return;
|
||||
}
|
||||
|
||||
$this->line('');
|
||||
|
||||
// Parameters
|
||||
$this->info('📋 PARAMETER PERHITUNGAN:');
|
||||
$indices = $result['indices'];
|
||||
$this->line('• Coefficient: ' . number_format($indices['coefficient'], 4));
|
||||
$this->line('• IP Permanent: ' . number_format($indices['ip_permanent'], 4));
|
||||
$this->line('• IP Complexity: ' . number_format($indices['ip_complexity'], 4));
|
||||
$this->line('• Locality Index: ' . number_format($indices['locality_index'], 4));
|
||||
$this->line('• Height Index: ' . number_format($result['input_parameters']['height_index'], 4));
|
||||
|
||||
$this->line('');
|
||||
|
||||
// Calculation steps
|
||||
$this->info('🔢 LANGKAH PERHITUNGAN:');
|
||||
$detail = $result['calculation_detail'];
|
||||
$this->line('1. H5 Raw: ' . number_format($detail['h5_raw'], 6));
|
||||
$this->line('2. H5 Rounded: ' . number_format($detail['h5'], 4));
|
||||
$this->line('3. Main Calculation: Rp ' . number_format($detail['main'], 2));
|
||||
$this->line('4. Infrastructure (50%): Rp ' . number_format($detail['infrastructure'], 2));
|
||||
|
||||
$this->line('');
|
||||
|
||||
// Final result
|
||||
$this->info('💰 <fg=green>TOTAL RETRIBUSI: ' . $result['formatted_amount'] . '</fg=green>');
|
||||
$this->line('📈 <fg=yellow>Per m²: Rp ' . number_format($result['total_retribution'] / $area, 2) . '</fg=yellow>');
|
||||
}
|
||||
|
||||
protected function testAllBuildingTypes()
|
||||
{
|
||||
$area = round($this->option('area') ?: 100, 2);
|
||||
$floor = $this->option('floor') ?: 2;
|
||||
|
||||
$this->info("🧪 TESTING SEMUA BUILDING TYPES");
|
||||
$this->info("📐 Luas: {$area} m² | 🏗️ Lantai: {$floor}");
|
||||
$this->info('=' . str_repeat('=', 60));
|
||||
|
||||
$buildingTypes = BuildingType::with('indices')
|
||||
->whereHas('indices') // Only types that have indices
|
||||
->orderBy('level')
|
||||
->orderBy('name')
|
||||
->get();
|
||||
|
||||
$headers = ['Kode', 'Nama', 'Coefficient', 'Total Retribusi', 'Per m²'];
|
||||
$rows = [];
|
||||
|
||||
foreach ($buildingTypes as $type) {
|
||||
try {
|
||||
$result = $this->calculatorService->calculate($type->id, $floor, $area, false);
|
||||
|
||||
if ($type->is_free) {
|
||||
$rows[] = [
|
||||
$type->code,
|
||||
$type->name,
|
||||
'FREE',
|
||||
'Rp 0',
|
||||
'Rp 0'
|
||||
];
|
||||
} else {
|
||||
$rows[] = [
|
||||
$type->code,
|
||||
$type->name,
|
||||
number_format($result['indices']['coefficient'], 4),
|
||||
'Rp ' . number_format($result['total_retribution'], 0),
|
||||
'Rp ' . number_format($result['total_retribution'] / $area, 0)
|
||||
];
|
||||
}
|
||||
} catch (\Exception $e) {
|
||||
$rows[] = [
|
||||
$type->code,
|
||||
$type->name,
|
||||
'ERROR',
|
||||
$e->getMessage(),
|
||||
'-'
|
||||
];
|
||||
}
|
||||
}
|
||||
|
||||
$this->table($headers, $rows);
|
||||
|
||||
return 0;
|
||||
}
|
||||
}
|
||||
295
app/Http/Controllers/RetributionProposalController.php
Normal file
295
app/Http/Controllers/RetributionProposalController.php
Normal file
@@ -0,0 +1,295 @@
|
||||
<?php
|
||||
|
||||
namespace App\Http\Controllers;
|
||||
|
||||
use App\Models\RetributionProposal;
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Services\RetributionProposalService;
|
||||
use Illuminate\Http\Request;
|
||||
use Illuminate\Http\JsonResponse;
|
||||
|
||||
class RetributionProposalController extends Controller
|
||||
{
|
||||
protected RetributionProposalService $proposalService;
|
||||
|
||||
public function __construct(RetributionProposalService $proposalService)
|
||||
{
|
||||
$this->proposalService = $proposalService;
|
||||
}
|
||||
|
||||
/**
|
||||
* Display a listing of retribution proposals
|
||||
*/
|
||||
public function index(Request $request): JsonResponse
|
||||
{
|
||||
$query = RetributionProposal::with(['spatialPlanning', 'buildingFunction', 'retributionFormula']);
|
||||
|
||||
// Filtering
|
||||
if ($request->has('building_function_id')) {
|
||||
$query->where('building_function_id', $request->building_function_id);
|
||||
}
|
||||
|
||||
if ($request->has('spatial_planning_id')) {
|
||||
$query->where('spatial_planning_id', $request->spatial_planning_id);
|
||||
}
|
||||
|
||||
if ($request->has('floor_number')) {
|
||||
$query->where('floor_number', $request->floor_number);
|
||||
}
|
||||
|
||||
if ($request->has('has_spatial_planning')) {
|
||||
if ($request->boolean('has_spatial_planning')) {
|
||||
$query->whereNotNull('spatial_planning_id');
|
||||
} else {
|
||||
$query->whereNull('spatial_planning_id');
|
||||
}
|
||||
}
|
||||
|
||||
// Search
|
||||
if ($request->has('search')) {
|
||||
$search = $request->search;
|
||||
$query->where(function ($q) use ($search) {
|
||||
$q->where('proposal_number', 'like', "%{$search}%")
|
||||
->orWhere('notes', 'like', "%{$search}%")
|
||||
->orWhereHas('spatialPlanning', function ($sq) use ($search) {
|
||||
$sq->where('name', 'like', "%{$search}%");
|
||||
})
|
||||
->orWhereHas('buildingFunction', function ($bq) use ($search) {
|
||||
$bq->where('name', 'like', "%{$search}%");
|
||||
});
|
||||
});
|
||||
}
|
||||
|
||||
// Sorting
|
||||
$sortBy = $request->get('sort_by', 'created_at');
|
||||
$sortOrder = $request->get('sort_order', 'desc');
|
||||
$query->orderBy($sortBy, $sortOrder);
|
||||
|
||||
// Pagination
|
||||
$perPage = $request->get('per_page', 15);
|
||||
$proposals = $query->paginate($perPage);
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'data' => $proposals,
|
||||
'meta' => [
|
||||
'total_amount' => RetributionProposal::sum('total_retribution_amount'),
|
||||
'total_proposals' => RetributionProposal::count(),
|
||||
'formatted_total_amount' => 'Rp ' . number_format(RetributionProposal::sum('total_retribution_amount'), 0, ',', '.')
|
||||
]
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Store a new retribution proposal
|
||||
*/
|
||||
public function store(Request $request): JsonResponse
|
||||
{
|
||||
$request->validate([
|
||||
'spatial_planning_id' => 'nullable|exists:spatial_plannings,id',
|
||||
'building_function_id' => 'required|exists:building_functions,id',
|
||||
'floor_number' => 'required|integer|min:1|max:10',
|
||||
'floor_area' => 'required|numeric|min:0.01',
|
||||
'total_building_area' => 'nullable|numeric|min:0.01',
|
||||
'notes' => 'nullable|string|max:1000'
|
||||
]);
|
||||
|
||||
try {
|
||||
if ($request->spatial_planning_id) {
|
||||
$spatialPlanning = SpatialPlanning::findOrFail($request->spatial_planning_id);
|
||||
$proposal = $this->proposalService->createProposalForSpatialPlanning(
|
||||
$spatialPlanning,
|
||||
$request->building_function_id,
|
||||
$request->floor_number,
|
||||
$request->floor_area,
|
||||
$request->total_building_area,
|
||||
$request->notes
|
||||
);
|
||||
} else {
|
||||
$proposal = $this->proposalService->createStandaloneProposal(
|
||||
$request->building_function_id,
|
||||
$request->floor_number,
|
||||
$request->floor_area,
|
||||
$request->total_building_area,
|
||||
$request->notes
|
||||
);
|
||||
}
|
||||
|
||||
$proposal->load(['spatialPlanning', 'buildingFunction', 'retributionFormula']);
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'message' => 'Retribution proposal created successfully',
|
||||
'data' => $proposal
|
||||
], 201);
|
||||
|
||||
} catch (\Exception $e) {
|
||||
return response()->json([
|
||||
'success' => false,
|
||||
'message' => 'Failed to create retribution proposal',
|
||||
'error' => $e->getMessage()
|
||||
], 400);
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Display the specified retribution proposal
|
||||
*/
|
||||
public function show(int $id): JsonResponse
|
||||
{
|
||||
$proposal = RetributionProposal::with(['spatialPlanning', 'buildingFunction', 'retributionFormula'])
|
||||
->findOrFail($id);
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'data' => $proposal
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Update the specified retribution proposal
|
||||
*/
|
||||
public function update(Request $request, int $id): JsonResponse
|
||||
{
|
||||
$proposal = RetributionProposal::findOrFail($id);
|
||||
|
||||
$request->validate([
|
||||
'notes' => 'nullable|string|max:1000'
|
||||
]);
|
||||
|
||||
$proposal->update($request->only(['notes']));
|
||||
|
||||
$proposal->load(['spatialPlanning', 'buildingFunction', 'retributionFormula']);
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'message' => 'Retribution proposal updated successfully',
|
||||
'data' => $proposal
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Remove the specified retribution proposal
|
||||
*/
|
||||
public function destroy(int $id): JsonResponse
|
||||
{
|
||||
$proposal = RetributionProposal::findOrFail($id);
|
||||
$proposal->delete();
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'message' => 'Retribution proposal deleted successfully'
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get retribution proposal statistics
|
||||
*/
|
||||
public function statistics(): JsonResponse
|
||||
{
|
||||
$stats = $this->proposalService->getStatistics();
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'data' => $stats
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get total sum of all retribution proposals
|
||||
*/
|
||||
public function totalSum(): JsonResponse
|
||||
{
|
||||
$totalAmount = RetributionProposal::sum('total_retribution_amount');
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'data' => [
|
||||
'total_amount' => $totalAmount,
|
||||
'formatted_total_amount' => 'Rp ' . number_format($totalAmount, 0, ',', '.'),
|
||||
'total_proposals' => RetributionProposal::count()
|
||||
]
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get building functions list
|
||||
*/
|
||||
public function buildingFunctions(): JsonResponse
|
||||
{
|
||||
$buildingFunctions = BuildingFunction::whereNotNull('parent_id')
|
||||
->with(['parameter'])
|
||||
->orderBy('name')
|
||||
->get();
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'data' => $buildingFunctions
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Auto-detect building function and create proposal
|
||||
*/
|
||||
public function autoCreateProposal(Request $request): JsonResponse
|
||||
{
|
||||
$request->validate([
|
||||
'spatial_planning_id' => 'nullable|exists:spatial_plannings,id',
|
||||
'building_function_text' => 'required|string',
|
||||
'floor_number' => 'required|integer|min:1|max:10',
|
||||
'floor_area' => 'required|numeric|min:0.01',
|
||||
'total_building_area' => 'nullable|numeric|min:0.01',
|
||||
'notes' => 'nullable|string|max:1000'
|
||||
]);
|
||||
|
||||
try {
|
||||
// Auto-detect building function
|
||||
$buildingFunction = $this->proposalService->detectBuildingFunction($request->building_function_text);
|
||||
|
||||
if (!$buildingFunction) {
|
||||
return response()->json([
|
||||
'success' => false,
|
||||
'message' => 'Could not detect building function from text',
|
||||
'suggestion' => 'Please specify building function manually'
|
||||
], 400);
|
||||
}
|
||||
|
||||
if ($request->spatial_planning_id) {
|
||||
$spatialPlanning = SpatialPlanning::findOrFail($request->spatial_planning_id);
|
||||
$proposal = $this->proposalService->createProposalForSpatialPlanning(
|
||||
$spatialPlanning,
|
||||
$buildingFunction->id,
|
||||
$request->floor_number,
|
||||
$request->floor_area,
|
||||
$request->total_building_area,
|
||||
$request->notes
|
||||
);
|
||||
} else {
|
||||
$proposal = $this->proposalService->createStandaloneProposal(
|
||||
$buildingFunction->id,
|
||||
$request->floor_number,
|
||||
$request->floor_area,
|
||||
$request->total_building_area,
|
||||
$request->notes
|
||||
);
|
||||
}
|
||||
|
||||
$proposal->load(['spatialPlanning', 'buildingFunction', 'retributionFormula']);
|
||||
|
||||
return response()->json([
|
||||
'success' => true,
|
||||
'message' => 'Retribution proposal created successfully with auto-detected building function',
|
||||
'data' => $proposal,
|
||||
'detected_building_function' => $buildingFunction->name
|
||||
], 201);
|
||||
|
||||
} catch (\Exception $e) {
|
||||
return response()->json([
|
||||
'success' => false,
|
||||
'message' => 'Failed to create retribution proposal',
|
||||
'error' => $e->getMessage()
|
||||
], 400);
|
||||
}
|
||||
}
|
||||
}
|
||||
167
app/Models/BuildingFunction.php
Normal file
167
app/Models/BuildingFunction.php
Normal file
@@ -0,0 +1,167 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
use Illuminate\Database\Eloquent\Relations\HasOne;
|
||||
use Illuminate\Database\Eloquent\Relations\HasMany;
|
||||
|
||||
class BuildingFunction extends Model
|
||||
{
|
||||
protected $table = 'building_functions';
|
||||
|
||||
protected $fillable = [
|
||||
'code',
|
||||
'name',
|
||||
'description',
|
||||
'parent_id',
|
||||
'level',
|
||||
'sort_order',
|
||||
'base_tariff'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'level' => 'integer',
|
||||
'sort_order' => 'integer',
|
||||
'base_tariff' => 'decimal:2'
|
||||
];
|
||||
|
||||
/**
|
||||
* Parent relationship (self-referencing)
|
||||
*/
|
||||
public function parent(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class, 'parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Children relationship (self-referencing)
|
||||
*/
|
||||
public function children(): HasMany
|
||||
{
|
||||
return $this->hasMany(BuildingFunction::class, 'parent_id')
|
||||
->orderBy('sort_order');
|
||||
}
|
||||
|
||||
/**
|
||||
* Parameters relationship (1:1)
|
||||
*/
|
||||
public function parameter(): HasOne
|
||||
{
|
||||
return $this->hasOne(BuildingFunctionParameter::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Formulas relationship (1:n) - multiple formulas for different floor numbers
|
||||
*/
|
||||
public function formulas(): HasMany
|
||||
{
|
||||
return $this->hasMany(RetributionFormula::class)->orderBy('floor_number');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get appropriate formula based on floor number
|
||||
*/
|
||||
public function getFormulaForFloor(int $floorNumber): ?RetributionFormula
|
||||
{
|
||||
return $this->formulas()
|
||||
->where('floor_number', $floorNumber)
|
||||
->first();
|
||||
}
|
||||
|
||||
/**
|
||||
* Retribution proposals relationship (1:n)
|
||||
*/
|
||||
public function retributionProposals(): HasMany
|
||||
{
|
||||
return $this->hasMany(RetributionProposal::class, 'building_function_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Parent functions only
|
||||
*/
|
||||
public function scopeParents($query)
|
||||
{
|
||||
return $query->whereNull('parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Children functions only
|
||||
*/
|
||||
public function scopeChildren($query)
|
||||
{
|
||||
return $query->whereNotNull('parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: By level
|
||||
*/
|
||||
public function scopeByLevel($query, int $level)
|
||||
{
|
||||
return $query->where('level', $level);
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if building function has complete setup (parameters + at least one formula)
|
||||
*/
|
||||
public function hasCompleteSetup(): bool
|
||||
{
|
||||
return $this->parameter()->exists() && $this->formulas()->exists();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get building function with all related data
|
||||
*/
|
||||
public function getCompleteData(): array
|
||||
{
|
||||
return [
|
||||
'id' => $this->id,
|
||||
'code' => $this->code,
|
||||
'name' => $this->name,
|
||||
'description' => $this->description,
|
||||
'level' => $this->level,
|
||||
'sort_order' => $this->sort_order,
|
||||
'base_tariff' => $this->base_tariff,
|
||||
'parameters' => $this->parameter?->getParametersArray(),
|
||||
'formulas' => $this->formulas->map(function ($formula) {
|
||||
return [
|
||||
'id' => $formula->id,
|
||||
'name' => $formula->name,
|
||||
'expression' => $formula->formula_expression,
|
||||
'floor_number' => $formula->floor_number,
|
||||
'floor_description' => $this->getFloorDescription($formula->floor_number)
|
||||
];
|
||||
})->toArray(),
|
||||
'has_complete_setup' => $this->hasCompleteSetup()
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Get floor description
|
||||
*/
|
||||
public function getFloorDescription(?int $floorNumber): string
|
||||
{
|
||||
if ($floorNumber === null || $floorNumber === 0) {
|
||||
return 'Semua lantai';
|
||||
}
|
||||
|
||||
return "Lantai {$floorNumber}";
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this is a parent function
|
||||
*/
|
||||
public function isParent(): bool
|
||||
{
|
||||
return $this->parent_id === null;
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this is a child function
|
||||
*/
|
||||
public function isChild(): bool
|
||||
{
|
||||
return $this->parent_id !== null;
|
||||
}
|
||||
}
|
||||
110
app/Models/BuildingFunctionFormulaConfig.php
Normal file
110
app/Models/BuildingFunctionFormulaConfig.php
Normal file
@@ -0,0 +1,110 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Factories\HasFactory;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class BuildingFunctionFormulaConfig extends Model
|
||||
{
|
||||
use HasFactory;
|
||||
|
||||
protected $fillable = [
|
||||
'building_function_id',
|
||||
'formula_id',
|
||||
'floor_level',
|
||||
'min_floor',
|
||||
'max_floor',
|
||||
'multiplier',
|
||||
'is_active',
|
||||
'priority',
|
||||
'notes',
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'multiplier' => 'decimal:4',
|
||||
'is_active' => 'boolean',
|
||||
'min_floor' => 'integer',
|
||||
'max_floor' => 'integer',
|
||||
'floor_level' => 'integer',
|
||||
'priority' => 'integer',
|
||||
];
|
||||
|
||||
/**
|
||||
* Relationship to BuildingFunction
|
||||
*/
|
||||
public function buildingFunction()
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class, 'building_function_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship to MasterFormula
|
||||
*/
|
||||
public function formula()
|
||||
{
|
||||
return $this->belongsTo(MasterFormula::class, 'formula_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope untuk konfigurasi aktif
|
||||
*/
|
||||
public function scopeActive($query)
|
||||
{
|
||||
return $query->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope untuk mencari berdasarkan building function dan floor level
|
||||
*/
|
||||
public function scopeForBuildingAndFloor($query, $buildingFunctionId, $floorLevel)
|
||||
{
|
||||
return $query->where('building_function_id', $buildingFunctionId)
|
||||
->where(function($q) use ($floorLevel) {
|
||||
$q->where('floor_level', $floorLevel)
|
||||
->orWhere('floor_level', 0) // 0 = berlaku untuk semua lantai
|
||||
->orWhere(function($qq) use ($floorLevel) {
|
||||
$qq->whereNotNull('min_floor')
|
||||
->whereNotNull('max_floor')
|
||||
->where('min_floor', '<=', $floorLevel)
|
||||
->where('max_floor', '>=', $floorLevel);
|
||||
});
|
||||
})
|
||||
->orderBy('priority', 'desc')
|
||||
->orderBy('floor_level', 'desc'); // Prioritas: spesifik floor > range > semua lantai
|
||||
}
|
||||
|
||||
/**
|
||||
* Method untuk mendapatkan formula yang tepat untuk building function dan floor tertentu
|
||||
*/
|
||||
public static function getFormulaForBuildingAndFloor($buildingFunctionId, $floorLevel)
|
||||
{
|
||||
return self::active()
|
||||
->forBuildingAndFloor($buildingFunctionId, $floorLevel)
|
||||
->with('formula')
|
||||
->first();
|
||||
}
|
||||
|
||||
/**
|
||||
* Method untuk check apakah floor level masuk dalam range
|
||||
*/
|
||||
public function isFloorInRange($floorLevel)
|
||||
{
|
||||
// Jika floor_level = 0, berlaku untuk semua lantai
|
||||
if ($this->floor_level == 0) {
|
||||
return true;
|
||||
}
|
||||
|
||||
// Jika ada specific floor level
|
||||
if ($this->floor_level == $floorLevel) {
|
||||
return true;
|
||||
}
|
||||
|
||||
// Jika ada range min-max
|
||||
if (!is_null($this->min_floor) && !is_null($this->max_floor)) {
|
||||
return $floorLevel >= $this->min_floor && $floorLevel <= $this->max_floor;
|
||||
}
|
||||
|
||||
return false;
|
||||
}
|
||||
}
|
||||
134
app/Models/BuildingFunctionParameter.php
Normal file
134
app/Models/BuildingFunctionParameter.php
Normal file
@@ -0,0 +1,134 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
|
||||
class BuildingFunctionParameter extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'building_function_id',
|
||||
'fungsi_bangunan',
|
||||
'ip_permanen',
|
||||
'ip_kompleksitas',
|
||||
'indeks_lokalitas',
|
||||
'asumsi_prasarana',
|
||||
'koefisien_dasar',
|
||||
'faktor_penyesuaian',
|
||||
'custom_parameters',
|
||||
'parameter_notes'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'fungsi_bangunan' => 'decimal:6',
|
||||
'ip_permanen' => 'decimal:6',
|
||||
'ip_kompleksitas' => 'decimal:6',
|
||||
'indeks_lokalitas' => 'decimal:6',
|
||||
'asumsi_prasarana' => 'decimal:6',
|
||||
'koefisien_dasar' => 'decimal:6',
|
||||
'faktor_penyesuaian' => 'decimal:6',
|
||||
'custom_parameters' => 'array'
|
||||
];
|
||||
|
||||
/**
|
||||
* Building function relationship (1:1)
|
||||
*/
|
||||
public function buildingFunction(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Active parameters only
|
||||
*/
|
||||
public function scopeActive($query)
|
||||
{
|
||||
return $query->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all parameter values as array
|
||||
*/
|
||||
public function getParametersArray(): array
|
||||
{
|
||||
return [
|
||||
'fungsi_bangunan' => $this->fungsi_bangunan,
|
||||
'ip_permanen' => $this->ip_permanen,
|
||||
'ip_kompleksitas' => $this->ip_kompleksitas,
|
||||
'indeks_lokalitas' => $this->indeks_lokalitas,
|
||||
'asumsi_prasarana' => $this->asumsi_prasarana,
|
||||
'koefisien_dasar' => $this->koefisien_dasar,
|
||||
'faktor_penyesuaian' => $this->faktor_penyesuaian,
|
||||
'custom_parameters' => $this->custom_parameters
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Get formatted parameters for display
|
||||
*/
|
||||
public function getFormattedParameters(): array
|
||||
{
|
||||
return [
|
||||
'Fungsi Bangunan' => $this->fungsi_bangunan,
|
||||
'IP Permanen' => $this->ip_permanen,
|
||||
'IP Kompleksitas' => $this->ip_kompleksitas,
|
||||
'Indeks Lokalitas' => $this->indeks_lokalitas,
|
||||
'Asumsi Prasarana' => $this->asumsi_prasarana,
|
||||
'Koefisien Dasar' => $this->koefisien_dasar,
|
||||
'Faktor Penyesuaian' => $this->faktor_penyesuaian
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculate floor result using the main formula
|
||||
*/
|
||||
public function calculateFloorResult(float $ipKetinggian): float
|
||||
{
|
||||
return $this->fungsi_bangunan * (
|
||||
$this->ip_permanen +
|
||||
$this->ip_kompleksitas +
|
||||
(0.5 * $ipKetinggian)
|
||||
);
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculate full retribution for given building area and floor result
|
||||
*/
|
||||
public function calculateRetribution(float $luasBangunan, float $floorResult): float
|
||||
{
|
||||
$baseValue = 70350; // Base retribution value
|
||||
$mainCalculation = 1 * $luasBangunan * ($this->indeks_lokalitas * $baseValue * $floorResult * 1);
|
||||
$additionalCalculation = 0.5 * $mainCalculation;
|
||||
|
||||
return $mainCalculation + $additionalCalculation;
|
||||
}
|
||||
|
||||
/**
|
||||
* Apply custom parameters if available
|
||||
*/
|
||||
public function getParameterValue(string $key, $default = null)
|
||||
{
|
||||
// First check if it's a standard parameter
|
||||
if (property_exists($this, $key) && $this->$key !== null) {
|
||||
return $this->$key;
|
||||
}
|
||||
|
||||
// Then check custom parameters
|
||||
if ($this->custom_parameters && is_array($this->custom_parameters)) {
|
||||
return $this->custom_parameters[$key] ?? $default;
|
||||
}
|
||||
|
||||
return $default;
|
||||
}
|
||||
|
||||
/**
|
||||
* Set custom parameter
|
||||
*/
|
||||
public function setCustomParameter(string $key, $value): void
|
||||
{
|
||||
$customParams = $this->custom_parameters ?? [];
|
||||
$customParams[$key] = $value;
|
||||
$this->custom_parameters = $customParams;
|
||||
}
|
||||
}
|
||||
@@ -1,131 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
use Illuminate\Database\Eloquent\Relations\HasMany;
|
||||
use Illuminate\Database\Eloquent\Relations\HasOne;
|
||||
|
||||
class BuildingType extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'code',
|
||||
'name',
|
||||
'parent_id',
|
||||
'level',
|
||||
'is_free',
|
||||
'is_active'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'level' => 'integer',
|
||||
'is_free' => 'boolean',
|
||||
'is_active' => 'boolean'
|
||||
];
|
||||
|
||||
/**
|
||||
* Parent relationship
|
||||
*/
|
||||
public function parent(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingType::class, 'parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Children relationship
|
||||
*/
|
||||
public function children(): HasMany
|
||||
{
|
||||
return $this->hasMany(BuildingType::class, 'parent_id')
|
||||
->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Retribution indices relationship
|
||||
*/
|
||||
public function indices(): HasOne
|
||||
{
|
||||
return $this->hasOne(RetributionIndex::class, 'building_type_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculations relationship
|
||||
*/
|
||||
public function calculations(): HasMany
|
||||
{
|
||||
return $this->hasMany(RetributionCalculation::class, 'building_type_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Active only
|
||||
*/
|
||||
public function scopeActive($query)
|
||||
{
|
||||
return $query->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Parents only
|
||||
*/
|
||||
public function scopeParents($query)
|
||||
{
|
||||
return $query->whereNull('parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Children only
|
||||
*/
|
||||
public function scopeChildren($query)
|
||||
{
|
||||
return $query->whereNotNull('parent_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Non-free types
|
||||
*/
|
||||
public function scopeChargeable($query)
|
||||
{
|
||||
return $query->where('is_free', false);
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if building type is free
|
||||
*/
|
||||
public function isFree(): bool
|
||||
{
|
||||
return $this->is_free;
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this is a parent type
|
||||
*/
|
||||
public function isParent(): bool
|
||||
{
|
||||
return $this->parent_id === null;
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this is a child type
|
||||
*/
|
||||
public function isChild(): bool
|
||||
{
|
||||
return $this->parent_id !== null;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get complete data for calculation
|
||||
*/
|
||||
public function getCalculationData(): array
|
||||
{
|
||||
return [
|
||||
'id' => $this->id,
|
||||
'code' => $this->code,
|
||||
'name' => $this->name,
|
||||
'coefficient' => $this->coefficient,
|
||||
'is_free' => $this->is_free,
|
||||
'indices' => $this->indices?->toArray(),
|
||||
'parent' => $this->parent?->only(['id', 'code', 'name'])
|
||||
];
|
||||
}
|
||||
}
|
||||
@@ -1,64 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
use Illuminate\Database\Eloquent\Relations\MorphTo;
|
||||
|
||||
class CalculableRetribution extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'retribution_calculation_id',
|
||||
'calculable_id',
|
||||
'calculable_type',
|
||||
'is_active',
|
||||
'assigned_at',
|
||||
'notes',
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'is_active' => 'boolean',
|
||||
'assigned_at' => 'timestamp',
|
||||
];
|
||||
|
||||
/**
|
||||
* Get the owning calculable model (polymorphic)
|
||||
*/
|
||||
public function calculable(): MorphTo
|
||||
{
|
||||
return $this->morphTo();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get the retribution calculation
|
||||
*/
|
||||
public function retributionCalculation(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(RetributionCalculation::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Only active assignments
|
||||
*/
|
||||
public function scopeActive($query)
|
||||
{
|
||||
return $query->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Only inactive assignments
|
||||
*/
|
||||
public function scopeInactive($query)
|
||||
{
|
||||
return $query->where('is_active', false);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: For specific calculable type
|
||||
*/
|
||||
public function scopeForType($query, string $type)
|
||||
{
|
||||
return $query->where('calculable_type', $type);
|
||||
}
|
||||
}
|
||||
@@ -4,42 +4,35 @@ namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class HeightIndex extends Model
|
||||
class FloorHeightIndex extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'floor_number',
|
||||
'height_index'
|
||||
'ip_ketinggian',
|
||||
'description'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'floor_number' => 'integer',
|
||||
'height_index' => 'decimal:6'
|
||||
'ip_ketinggian' => 'decimal:6'
|
||||
];
|
||||
|
||||
/**
|
||||
* Get height index by floor number
|
||||
* Get IP ketinggian by floor number
|
||||
*/
|
||||
public static function getByFloor(int $floorNumber): ?HeightIndex
|
||||
public static function getIpKetinggianByFloor(int $floorNumber): float
|
||||
{
|
||||
return self::where('floor_number', $floorNumber)->first();
|
||||
$index = self::where('floor_number', $floorNumber)->first();
|
||||
return $index ? (float) $index->ip_ketinggian : 1.0;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get height index value by floor number
|
||||
*/
|
||||
public static function getHeightIndexByFloor(int $floorNumber): float
|
||||
{
|
||||
$index = self::getByFloor($floorNumber);
|
||||
return $index ? (float) $index->height_index : 1.0;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all height indices as array
|
||||
* Get all IP ketinggian mapping as array
|
||||
*/
|
||||
public static function getAllMapping(): array
|
||||
{
|
||||
return self::orderBy('floor_number')
|
||||
->pluck('height_index', 'floor_number')
|
||||
->pluck('ip_ketinggian', 'floor_number')
|
||||
->toArray();
|
||||
}
|
||||
|
||||
@@ -52,4 +45,12 @@ class HeightIndex extends Model
|
||||
->pluck('floor_number')
|
||||
->toArray();
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: By floor number
|
||||
*/
|
||||
public function scopeByFloor($query, int $floorNumber)
|
||||
{
|
||||
return $query->where('floor_number', $floorNumber);
|
||||
}
|
||||
}
|
||||
32
app/Models/FormulaParameter.php
Normal file
32
app/Models/FormulaParameter.php
Normal file
@@ -0,0 +1,32 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Factories\HasFactory;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class FormulaParameter extends Model
|
||||
{
|
||||
use HasFactory;
|
||||
|
||||
protected $fillable = [
|
||||
'formula_id',
|
||||
'parameter_id',
|
||||
];
|
||||
|
||||
/**
|
||||
* Relationship to MasterFormula
|
||||
*/
|
||||
public function formula()
|
||||
{
|
||||
return $this->belongsTo(MasterFormula::class, 'formula_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship to MasterParameter
|
||||
*/
|
||||
public function parameter()
|
||||
{
|
||||
return $this->belongsTo(MasterParameter::class, 'parameter_id');
|
||||
}
|
||||
}
|
||||
81
app/Models/MasterFormula.php
Normal file
81
app/Models/MasterFormula.php
Normal file
@@ -0,0 +1,81 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Factories\HasFactory;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class MasterFormula extends Model
|
||||
{
|
||||
use HasFactory;
|
||||
|
||||
protected $fillable = [
|
||||
'formula_code',
|
||||
'formula_name',
|
||||
'formula_expression',
|
||||
'formula_category',
|
||||
'description',
|
||||
'usage_notes',
|
||||
];
|
||||
|
||||
/**
|
||||
* Relationship to FormulaParameter (Many-to-Many dengan MasterParameter)
|
||||
*/
|
||||
public function formulaParameters()
|
||||
{
|
||||
return $this->hasMany(FormulaParameter::class, 'formula_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship to MasterParameter through FormulaParameter
|
||||
*/
|
||||
public function parameters()
|
||||
{
|
||||
return $this->belongsToMany(MasterParameter::class, 'formula_parameters', 'formula_id', 'parameter_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship to BuildingFunctionFormulaConfig
|
||||
*/
|
||||
public function buildingConfigs()
|
||||
{
|
||||
return $this->hasMany(BuildingFunctionFormulaConfig::class, 'formula_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope untuk mencari berdasarkan kategori
|
||||
*/
|
||||
public function scopeByCategory($query, $category)
|
||||
{
|
||||
return $query->where('formula_category', $category);
|
||||
}
|
||||
|
||||
/**
|
||||
* Method untuk mendapatkan parameter yang diperlukan formula ini
|
||||
*/
|
||||
public function getRequiredParameters()
|
||||
{
|
||||
return $this->parameters()->get();
|
||||
}
|
||||
|
||||
/**
|
||||
* Method untuk evaluate formula dengan parameter values
|
||||
*/
|
||||
public function evaluateFormula($parameterValues = [])
|
||||
{
|
||||
$expression = $this->formula_expression;
|
||||
|
||||
// Replace parameter codes dengan nilai actual
|
||||
foreach ($parameterValues as $parameterCode => $value) {
|
||||
$expression = str_replace('{' . $parameterCode . '}', $value, $expression);
|
||||
}
|
||||
|
||||
// Evaluasi mathematical expression (hati-hati dengan security!)
|
||||
// Untuk production, gunakan library yang aman seperti SymfonyExpressionLanguage
|
||||
try {
|
||||
return eval("return $expression;");
|
||||
} catch (Exception $e) {
|
||||
throw new \Exception("Error evaluating formula: " . $e->getMessage());
|
||||
}
|
||||
}
|
||||
}
|
||||
55
app/Models/MasterParameter.php
Normal file
55
app/Models/MasterParameter.php
Normal file
@@ -0,0 +1,55 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Factories\HasFactory;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class MasterParameter extends Model
|
||||
{
|
||||
use HasFactory;
|
||||
|
||||
protected $fillable = [
|
||||
'parameter_code',
|
||||
'parameter_name',
|
||||
'default_value',
|
||||
'unit',
|
||||
'description',
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'default_value' => 'decimal:6',
|
||||
];
|
||||
|
||||
/**
|
||||
* Relationship to FormulaParameter
|
||||
*/
|
||||
public function formulaParameters()
|
||||
{
|
||||
return $this->hasMany(FormulaParameter::class, 'parameter_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship to MasterFormula through FormulaParameter
|
||||
*/
|
||||
public function formulas()
|
||||
{
|
||||
return $this->belongsToMany(MasterFormula::class, 'formula_parameters', 'parameter_id', 'formula_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope untuk mencari berdasarkan parameter code
|
||||
*/
|
||||
public function scopeByCode($query, $code)
|
||||
{
|
||||
return $query->where('parameter_code', $code);
|
||||
}
|
||||
|
||||
/**
|
||||
* Method untuk mendapatkan nilai parameter dengan fallback ke default
|
||||
*/
|
||||
public function getValue($customValue = null)
|
||||
{
|
||||
return $customValue !== null ? $customValue : $this->default_value;
|
||||
}
|
||||
}
|
||||
@@ -1,139 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
use Illuminate\Database\Eloquent\Relations\HasMany;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class RetributionCalculation extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'calculation_id',
|
||||
'building_type_id',
|
||||
'floor_number',
|
||||
'building_area',
|
||||
'retribution_amount',
|
||||
'calculation_detail',
|
||||
'calculated_at',
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'building_area' => 'decimal:2',
|
||||
'retribution_amount' => 'decimal:2',
|
||||
'calculation_detail' => 'array',
|
||||
'calculated_at' => 'timestamp',
|
||||
'floor_number' => 'integer',
|
||||
];
|
||||
|
||||
/**
|
||||
* Get the building type
|
||||
*/
|
||||
public function buildingType(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingType::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all calculable assignments
|
||||
*/
|
||||
public function calculableRetributions(): HasMany
|
||||
{
|
||||
return $this->hasMany(CalculableRetribution::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get active assignments only
|
||||
*/
|
||||
public function activeAssignments(): HasMany
|
||||
{
|
||||
return $this->hasMany(CalculableRetribution::class)->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Generate unique calculation ID
|
||||
*/
|
||||
public static function generateCalculationId(): string
|
||||
{
|
||||
return 'CALC-' . date('Ymd') . '-' . str_pad(mt_rand(1, 9999), 4, '0', STR_PAD_LEFT);
|
||||
}
|
||||
|
||||
/**
|
||||
* Boot method to auto-generate calculation_id
|
||||
*/
|
||||
protected static function boot()
|
||||
{
|
||||
parent::boot();
|
||||
|
||||
static::creating(function ($model) {
|
||||
if (empty($model->calculation_id)) {
|
||||
$model->calculation_id = self::generateCalculationId();
|
||||
}
|
||||
if (empty($model->calculated_at)) {
|
||||
$model->calculated_at = now();
|
||||
}
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if calculation is being used
|
||||
*/
|
||||
public function isInUse(): bool
|
||||
{
|
||||
return $this->activeAssignments()->exists();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation summary
|
||||
*/
|
||||
public function getSummary(): array
|
||||
{
|
||||
return [
|
||||
'calculation_id' => $this->calculation_id,
|
||||
'building_type' => $this->buildingType->name ?? 'Unknown',
|
||||
'floor_number' => $this->floor_number,
|
||||
'building_area' => $this->building_area,
|
||||
'retribution_amount' => $this->retribution_amount,
|
||||
'calculated_at' => $this->calculated_at->format('Y-m-d H:i:s'),
|
||||
'in_use' => $this->isInUse(),
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Create new calculation
|
||||
*/
|
||||
public static function createCalculation(
|
||||
int $buildingTypeId,
|
||||
int $floorNumber,
|
||||
float $buildingArea,
|
||||
float $retributionAmount,
|
||||
array $calculationDetail
|
||||
): self {
|
||||
return self::create([
|
||||
'calculation_id' => self::generateCalculationId(),
|
||||
'building_type_id' => $buildingTypeId,
|
||||
'floor_number' => $floorNumber,
|
||||
'building_area' => $buildingArea,
|
||||
'retribution_amount' => $retributionAmount,
|
||||
'calculation_detail' => $calculationDetail,
|
||||
'calculated_at' => Carbon::now()
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get formatted retribution amount
|
||||
*/
|
||||
public function getFormattedAmount(): string
|
||||
{
|
||||
return 'Rp ' . number_format($this->retribution_amount, 2, ',', '.');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation breakdown
|
||||
*/
|
||||
public function getCalculationBreakdown(): array
|
||||
{
|
||||
return $this->calculation_detail ?? [];
|
||||
}
|
||||
}
|
||||
@@ -1,50 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
class RetributionConfig extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'key',
|
||||
'value',
|
||||
'description',
|
||||
'is_active'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'value' => 'decimal:2',
|
||||
'is_active' => 'boolean'
|
||||
];
|
||||
|
||||
/**
|
||||
* Get config value by key
|
||||
*/
|
||||
public static function getValue(string $key, float $default = 0.0): float
|
||||
{
|
||||
$config = self::where('key', $key)->where('is_active', true)->first();
|
||||
return $config ? (float) $config->value : $default;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all active configs as array
|
||||
*/
|
||||
public static function getAllActive(): array
|
||||
{
|
||||
return self::where('is_active', true)
|
||||
->pluck('value', 'key')
|
||||
->toArray();
|
||||
}
|
||||
|
||||
/**
|
||||
* Update config value
|
||||
*/
|
||||
public static function updateValue(string $key, float $value): bool
|
||||
{
|
||||
return self::updateOrCreate(
|
||||
['key' => $key],
|
||||
['value' => $value, 'is_active' => true]
|
||||
);
|
||||
}
|
||||
}
|
||||
227
app/Models/RetributionFormula.php
Normal file
227
app/Models/RetributionFormula.php
Normal file
@@ -0,0 +1,227 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
use Illuminate\Database\Eloquent\Relations\HasMany;
|
||||
|
||||
class RetributionFormula extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'building_function_id',
|
||||
'name',
|
||||
'floor_number',
|
||||
'formula_expression'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'floor_number' => 'integer'
|
||||
];
|
||||
|
||||
/**
|
||||
* Building function relationship (n:1)
|
||||
*/
|
||||
public function buildingFunction(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Retribution proposals relationship (1:n)
|
||||
*/
|
||||
public function retributionProposals(): HasMany
|
||||
{
|
||||
return $this->hasMany(RetributionProposal::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: By floor number
|
||||
*/
|
||||
public function scopeByFloor($query, int $floorNumber)
|
||||
{
|
||||
return $query->where('floor_number', $floorNumber);
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute formula calculation with parameters and IP ketinggian
|
||||
*/
|
||||
public function calculate(float $luasBangunan, array $parameters, float $ipKetinggian): float
|
||||
{
|
||||
// Extract parameters
|
||||
$fungsi_bangunan = $parameters['fungsi_bangunan'] ?? 0;
|
||||
$ip_permanen = $parameters['ip_permanen'] ?? 0;
|
||||
$ip_kompleksitas = $parameters['ip_kompleksitas'] ?? 0;
|
||||
$indeks_lokalitas = $parameters['indeks_lokalitas'] ?? 0;
|
||||
|
||||
// Calculate H13 (floor coefficient) using Excel formula
|
||||
$h13 = $fungsi_bangunan * ($ip_permanen + $ip_kompleksitas + (0.5 * $ipKetinggian));
|
||||
|
||||
// Calculate full retribution using Excel formula
|
||||
$baseValue = 70350;
|
||||
$additionalFactor = 0.5;
|
||||
|
||||
// Main calculation: (1*D13*(N13*70350*H13*1))
|
||||
$mainCalculation = 1 * $luasBangunan * ($indeks_lokalitas * $baseValue * $h13 * 1);
|
||||
|
||||
// Additional calculation: ($O$3*(1*D13*(N13*70350*H13*1)))
|
||||
$additionalCalculation = $additionalFactor * $mainCalculation;
|
||||
|
||||
// Total: main + additional
|
||||
return $mainCalculation + $additionalCalculation;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get formula with parameter placeholders replaced for display
|
||||
*/
|
||||
public function getDisplayFormula(array $parameters = []): string
|
||||
{
|
||||
$formula = $this->formula_expression;
|
||||
|
||||
if (!empty($parameters)) {
|
||||
foreach ($parameters as $key => $value) {
|
||||
$formula = str_replace($key, "({$key}={$value})", $formula);
|
||||
}
|
||||
}
|
||||
|
||||
return $formula;
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this formula applies to given floor number
|
||||
*/
|
||||
public function appliesTo(int $floorNumber): bool
|
||||
{
|
||||
// If floor_number is 0, formula applies to all floors
|
||||
if ($this->floor_number === 0) {
|
||||
return true;
|
||||
}
|
||||
|
||||
// Otherwise, exact match required
|
||||
return $this->floor_number == $floorNumber;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get human readable floor description
|
||||
*/
|
||||
public function getFloorDescription(): string
|
||||
{
|
||||
if ($this->floor_number === 0) {
|
||||
return 'Semua lantai';
|
||||
}
|
||||
|
||||
return "Lantai {$this->floor_number}";
|
||||
}
|
||||
|
||||
/**
|
||||
* Get default formula expression
|
||||
*/
|
||||
public static function getDefaultFormula(): string
|
||||
{
|
||||
return '(fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian)))';
|
||||
}
|
||||
|
||||
/**
|
||||
* Validate formula expression syntax
|
||||
*/
|
||||
public function validateFormula(): bool
|
||||
{
|
||||
// Basic validation - check if formula contains required variables
|
||||
$requiredVariables = ['fungsi_bangunan', 'ip_permanen', 'ip_kompleksitas', 'ip_ketinggian'];
|
||||
|
||||
foreach ($requiredVariables as $variable) {
|
||||
if (strpos($this->formula_expression, $variable) === false) {
|
||||
return false;
|
||||
}
|
||||
}
|
||||
|
||||
return true;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation breakdown for debugging
|
||||
*/
|
||||
public function getCalculationBreakdown(float $luasBangunan, array $parameters, float $ipKetinggian): array
|
||||
{
|
||||
// Extract parameters
|
||||
$fungsi_bangunan = $parameters['fungsi_bangunan'] ?? 0;
|
||||
$ip_permanen = $parameters['ip_permanen'] ?? 0;
|
||||
$ip_kompleksitas = $parameters['ip_kompleksitas'] ?? 0;
|
||||
$indeks_lokalitas = $parameters['indeks_lokalitas'] ?? 0;
|
||||
|
||||
// Calculate H13 (floor coefficient)
|
||||
$h13 = $fungsi_bangunan * ($ip_permanen + $ip_kompleksitas + (0.5 * $ipKetinggian));
|
||||
|
||||
// Calculate components
|
||||
$baseValue = 70350;
|
||||
$additionalFactor = 0.5;
|
||||
$mainCalculation = 1 * $luasBangunan * ($indeks_lokalitas * $baseValue * $h13 * 1);
|
||||
$additionalCalculation = $additionalFactor * $mainCalculation;
|
||||
$totalCalculation = $mainCalculation + $additionalCalculation;
|
||||
|
||||
return [
|
||||
'input_parameters' => [
|
||||
'luas_bangunan' => $luasBangunan,
|
||||
'fungsi_bangunan' => $fungsi_bangunan,
|
||||
'ip_permanen' => $ip_permanen,
|
||||
'ip_kompleksitas' => $ip_kompleksitas,
|
||||
'ip_ketinggian' => $ipKetinggian,
|
||||
'indeks_lokalitas' => $indeks_lokalitas,
|
||||
'base_value' => $baseValue,
|
||||
'additional_factor' => $additionalFactor
|
||||
],
|
||||
'calculation_steps' => [
|
||||
'h13_calculation' => [
|
||||
'formula' => 'fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian))',
|
||||
'calculation' => "{$fungsi_bangunan} * ({$ip_permanen} + {$ip_kompleksitas} + (0.5 * {$ipKetinggian}))",
|
||||
'result' => $h13
|
||||
],
|
||||
'main_calculation' => [
|
||||
'formula' => '1 * luas_bangunan * (indeks_lokalitas * base_value * h13 * 1)',
|
||||
'calculation' => "1 * {$luasBangunan} * ({$indeks_lokalitas} * {$baseValue} * {$h13} * 1)",
|
||||
'result' => $mainCalculation
|
||||
],
|
||||
'additional_calculation' => [
|
||||
'formula' => 'additional_factor * main_calculation',
|
||||
'calculation' => "{$additionalFactor} * {$mainCalculation}",
|
||||
'result' => $additionalCalculation
|
||||
],
|
||||
'total_calculation' => [
|
||||
'formula' => 'main_calculation + additional_calculation',
|
||||
'calculation' => "{$mainCalculation} + {$additionalCalculation}",
|
||||
'result' => $totalCalculation
|
||||
]
|
||||
],
|
||||
'formatted_results' => [
|
||||
'h13' => number_format($h13, 6),
|
||||
'main_calculation' => 'Rp ' . number_format($mainCalculation, 2),
|
||||
'additional_calculation' => 'Rp ' . number_format($additionalCalculation, 2),
|
||||
'total_calculation' => 'Rp ' . number_format($totalCalculation, 2)
|
||||
]
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if this formula has been used in any proposals
|
||||
*/
|
||||
public function hasProposals(): bool
|
||||
{
|
||||
return $this->retributionProposals()->exists();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get total amount calculated using this formula
|
||||
*/
|
||||
public function getTotalCalculatedAmount(): float
|
||||
{
|
||||
return $this->retributionProposals()->sum('total_retribution_amount');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get count of proposals using this formula
|
||||
*/
|
||||
public function getProposalCount(): int
|
||||
{
|
||||
return $this->retributionProposals()->count();
|
||||
}
|
||||
}
|
||||
@@ -1,57 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
|
||||
class RetributionIndex extends Model
|
||||
{
|
||||
protected $fillable = [
|
||||
'building_type_id',
|
||||
'coefficient',
|
||||
'ip_permanent',
|
||||
'ip_complexity',
|
||||
'locality_index',
|
||||
'infrastructure_factor',
|
||||
'is_active'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'coefficient' => 'decimal:4',
|
||||
'ip_permanent' => 'decimal:4',
|
||||
'ip_complexity' => 'decimal:4',
|
||||
'locality_index' => 'decimal:4',
|
||||
'infrastructure_factor' => 'decimal:4',
|
||||
'is_active' => 'boolean'
|
||||
];
|
||||
|
||||
/**
|
||||
* Building type relationship
|
||||
*/
|
||||
public function buildingType(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingType::class, 'building_type_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Active only
|
||||
*/
|
||||
public function scopeActive($query)
|
||||
{
|
||||
return $query->where('is_active', true);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all indices as array
|
||||
*/
|
||||
public function getIndicesArray(): array
|
||||
{
|
||||
return [
|
||||
'ip_permanent' => $this->ip_permanent,
|
||||
'ip_complexity' => $this->ip_complexity,
|
||||
'locality_index' => $this->locality_index,
|
||||
'infrastructure_factor' => $this->infrastructure_factor
|
||||
];
|
||||
}
|
||||
}
|
||||
187
app/Models/RetributionProposal.php
Normal file
187
app/Models/RetributionProposal.php
Normal file
@@ -0,0 +1,187 @@
|
||||
<?php
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use Illuminate\Database\Eloquent\Factories\HasFactory;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
|
||||
class RetributionProposal extends Model
|
||||
{
|
||||
use HasFactory;
|
||||
|
||||
protected $fillable = [
|
||||
'spatial_planning_id',
|
||||
'building_function_id',
|
||||
'retribution_formula_id',
|
||||
'proposal_number',
|
||||
'floor_number',
|
||||
'floor_area',
|
||||
'total_building_area',
|
||||
'ip_ketinggian',
|
||||
'floor_retribution_amount',
|
||||
'total_retribution_amount',
|
||||
'calculation_parameters',
|
||||
'calculation_breakdown',
|
||||
'notes',
|
||||
'calculated_at'
|
||||
];
|
||||
|
||||
protected $casts = [
|
||||
'floor_area' => 'decimal:6',
|
||||
'total_building_area' => 'decimal:6',
|
||||
'ip_ketinggian' => 'decimal:6',
|
||||
'floor_retribution_amount' => 'decimal:2',
|
||||
'total_retribution_amount' => 'decimal:2',
|
||||
'calculation_parameters' => 'array',
|
||||
'calculation_breakdown' => 'array',
|
||||
'calculated_at' => 'datetime'
|
||||
];
|
||||
|
||||
|
||||
|
||||
/**
|
||||
* Relationship with SpatialPlanning
|
||||
*/
|
||||
public function spatialPlanning(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(SpatialPlanning::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship with BuildingFunction
|
||||
*/
|
||||
public function buildingFunction(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Relationship with RetributionFormula
|
||||
*/
|
||||
public function retributionFormula(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(RetributionFormula::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Generate proposal number
|
||||
*/
|
||||
public static function generateProposalNumber(): string
|
||||
{
|
||||
$year = now()->format('Y');
|
||||
$month = now()->format('m');
|
||||
|
||||
// Use max ID + 1 to avoid duplicates when records are deleted
|
||||
$maxId = static::whereYear('created_at', now()->year)
|
||||
->whereMonth('created_at', now()->month)
|
||||
->max('id') ?? 0;
|
||||
$nextNumber = $maxId + 1;
|
||||
|
||||
// Fallback: if still duplicate, use timestamp
|
||||
$proposalNumber = sprintf('RP-%s%s-%04d', $year, $month, $nextNumber);
|
||||
|
||||
// Check if exists and increment until unique
|
||||
$counter = $nextNumber;
|
||||
while (static::where('proposal_number', $proposalNumber)->exists()) {
|
||||
$counter++;
|
||||
$proposalNumber = sprintf('RP-%s%s-%04d', $year, $month, $counter);
|
||||
}
|
||||
|
||||
return $proposalNumber;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get formatted floor retribution amount
|
||||
*/
|
||||
public function getFormattedFloorAmountAttribute(): string
|
||||
{
|
||||
return 'Rp ' . number_format($this->floor_retribution_amount, 0, ',', '.');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get formatted total retribution amount
|
||||
*/
|
||||
public function getFormattedTotalAmountAttribute(): string
|
||||
{
|
||||
return 'Rp ' . number_format($this->total_retribution_amount, 0, ',', '.');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation breakdown for specific parameter
|
||||
*/
|
||||
public function getCalculationBreakdownFor(string $parameter)
|
||||
{
|
||||
return $this->calculation_breakdown[$parameter] ?? null;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get parameter value
|
||||
*/
|
||||
public function getParameterValue(string $parameter)
|
||||
{
|
||||
return $this->calculation_parameters[$parameter] ?? null;
|
||||
}
|
||||
|
||||
|
||||
|
||||
|
||||
|
||||
/**
|
||||
* Scope for filtering by building function
|
||||
*/
|
||||
public function scopeByBuildingFunction($query, int $buildingFunctionId)
|
||||
{
|
||||
return $query->where('building_function_id', $buildingFunctionId);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope for filtering by spatial planning
|
||||
*/
|
||||
public function scopeBySpatialPlanning($query, int $spatialPlanningId)
|
||||
{
|
||||
return $query->where('spatial_planning_id', $spatialPlanningId);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get total retribution amount for multiple proposals
|
||||
*/
|
||||
public static function getTotalAmount($proposals = null): float
|
||||
{
|
||||
if ($proposals) {
|
||||
return $proposals->sum('total_retribution_amount');
|
||||
}
|
||||
|
||||
return static::sum('total_retribution_amount');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get basic statistics
|
||||
*/
|
||||
public static function getBasicStats(): array
|
||||
{
|
||||
return [
|
||||
'total_count' => static::count(),
|
||||
'total_amount' => static::sum('total_retribution_amount'),
|
||||
'average_amount' => static::avg('total_retribution_amount'),
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Boot method to generate proposal number
|
||||
*/
|
||||
protected static function boot()
|
||||
{
|
||||
parent::boot();
|
||||
|
||||
static::creating(function ($model) {
|
||||
if (empty($model->proposal_number)) {
|
||||
$model->proposal_number = static::generateProposalNumber();
|
||||
}
|
||||
|
||||
if (empty($model->calculated_at)) {
|
||||
$model->calculated_at = now();
|
||||
}
|
||||
});
|
||||
}
|
||||
}
|
||||
@@ -2,9 +2,10 @@
|
||||
|
||||
namespace App\Models;
|
||||
|
||||
use App\Traits\HasRetributionCalculation;
|
||||
use Illuminate\Database\Eloquent\Model;
|
||||
|
||||
use Illuminate\Database\Eloquent\Relations\HasOne;
|
||||
use Illuminate\Database\Eloquent\Relations\HasMany;
|
||||
use Illuminate\Database\Eloquent\Relations\BelongsTo;
|
||||
|
||||
/**
|
||||
* Class SpatialPlanning
|
||||
@@ -25,7 +26,6 @@ use Illuminate\Database\Eloquent\Model;
|
||||
*/
|
||||
class SpatialPlanning extends Model
|
||||
{
|
||||
use HasRetributionCalculation;
|
||||
|
||||
protected $perPage = 20;
|
||||
|
||||
@@ -44,7 +44,45 @@ class SpatialPlanning extends Model
|
||||
'date' => 'date'
|
||||
];
|
||||
|
||||
/**
|
||||
* Retribution proposals relationship (1:many)
|
||||
*/
|
||||
public function retributionProposals(): HasMany
|
||||
{
|
||||
return $this->hasMany(RetributionProposal::class);
|
||||
}
|
||||
|
||||
/**
|
||||
* Building function relationship (if building_function becomes FK in future)
|
||||
*/
|
||||
public function buildingFunctionRelation(): BelongsTo
|
||||
{
|
||||
return $this->belongsTo(BuildingFunction::class, 'building_function_id');
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if spatial planning has retribution proposals
|
||||
*/
|
||||
public function hasRetributionProposals(): bool
|
||||
{
|
||||
return $this->retributionProposals()->exists();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get latest retribution proposal
|
||||
*/
|
||||
public function getLatestRetributionProposal()
|
||||
{
|
||||
return $this->retributionProposals()->latest()->first();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all retribution proposals
|
||||
*/
|
||||
public function getAllRetributionProposals()
|
||||
{
|
||||
return $this->retributionProposals()->get();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get building function text for detection
|
||||
@@ -62,7 +100,21 @@ class SpatialPlanning extends Model
|
||||
return (float) ($this->area ?? $this->land_area ?? 0);
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: Without retribution proposals
|
||||
*/
|
||||
public function scopeWithoutRetributionProposals($query)
|
||||
{
|
||||
return $query->whereDoesntHave('retributionProposals');
|
||||
}
|
||||
|
||||
/**
|
||||
* Scope: With retribution proposals
|
||||
*/
|
||||
public function scopeWithRetributionProposals($query)
|
||||
{
|
||||
return $query->whereHas('retributionProposals');
|
||||
}
|
||||
|
||||
|
||||
}
|
||||
|
||||
180
app/Services/DynamicRetributionCalculationService.php
Normal file
180
app/Services/DynamicRetributionCalculationService.php
Normal file
@@ -0,0 +1,180 @@
|
||||
<?php
|
||||
|
||||
namespace App\Services;
|
||||
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\MasterParameter;
|
||||
|
||||
/**
|
||||
* Retribution Calculation Service - Rundown 4 Implementation
|
||||
*
|
||||
* Implementasi rumus Excel Rundown 4:
|
||||
* =ROUNDDOWN(($E13*($F13+$G13+(0.5*I$3))),4)
|
||||
* Retribusi = (1*D5*(N5*7035000*H5*1))+($O$3*(1*D5*(N5*7035000*H5*1)))
|
||||
*/
|
||||
class DynamicRetributionCalculationService
|
||||
{
|
||||
/**
|
||||
* Calculate retribution using Rundown 4 formula
|
||||
*/
|
||||
public function calculateRetribution($buildingFunctionId, $floorLevel, $luasBangunan, $indeksLokasi = 'sedang', $includeInfrastructure = true)
|
||||
{
|
||||
try {
|
||||
// Get parameters for this building function
|
||||
$parameters = $this->getParameterValues($buildingFunctionId, $floorLevel, $indeksLokasi);
|
||||
|
||||
// Step 1: Calculate Rundown 4 per floor value
|
||||
$h5 = $this->calculateRundown4($parameters);
|
||||
|
||||
// Step 2: Calculate basic retribution
|
||||
$basicRetribution = $parameters['koefisien_dasar'] * $luasBangunan *
|
||||
($parameters['indeks_lokalitas'] * $parameters['tarif_dasar'] * $h5 * $parameters['koefisien_dasar']);
|
||||
|
||||
// Step 3: Calculate infrastructure (optional)
|
||||
$infrastructureAmount = 0;
|
||||
$totalRetribution = $basicRetribution;
|
||||
|
||||
if ($includeInfrastructure) {
|
||||
$infrastructureAmount = $parameters['asumsi_prasarana'] * $basicRetribution;
|
||||
$totalRetribution = $basicRetribution + $infrastructureAmount;
|
||||
}
|
||||
|
||||
return [
|
||||
'success' => true,
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'floor_level' => $floorLevel,
|
||||
'luas_bangunan' => $luasBangunan,
|
||||
'indeks_lokalitas_type' => $indeksLokasi,
|
||||
'calculation_breakdown' => [
|
||||
'rundown_4_calculation' => [
|
||||
'excel_formula' => '=ROUNDDOWN(($E13*($F13+$G13+(0.5*I$3))),4)',
|
||||
'fungsi_bangunan' => $parameters['fungsi'],
|
||||
'ip_permanen' => $parameters['ip_permanen'],
|
||||
'ip_kompleksitas' => $parameters['ip_kompleksitas'],
|
||||
'multiplier_ketinggian' => $parameters['multiplier_ketinggian'],
|
||||
'ip_ketinggian' => $parameters['ip_ketinggian'],
|
||||
'before_rounddown' => $parameters['fungsi'] * ($parameters['ip_permanen'] + $parameters['ip_kompleksitas'] + ($parameters['multiplier_ketinggian'] * $parameters['ip_ketinggian'])),
|
||||
'after_rounddown' => $h5,
|
||||
'precision' => 4
|
||||
],
|
||||
'retribution_calculation' => [
|
||||
'basic_formula' => '1 * D5 * (N5 * 7035000 * H5 * 1)',
|
||||
'infrastructure_formula' => 'O3 * basic_retribution',
|
||||
'basic_amount' => $basicRetribution,
|
||||
'infrastructure_amount' => $infrastructureAmount,
|
||||
'total_amount' => $totalRetribution
|
||||
]
|
||||
],
|
||||
'results' => [
|
||||
'h5_rundown4' => $h5,
|
||||
'basic_retribution' => $basicRetribution,
|
||||
'infrastructure_amount' => $infrastructureAmount,
|
||||
'total_retribution' => $totalRetribution
|
||||
]
|
||||
];
|
||||
|
||||
} catch (\Exception $e) {
|
||||
return [
|
||||
'success' => false,
|
||||
'error' => $e->getMessage(),
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'floor_level' => $floorLevel
|
||||
];
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculate multiple floors retribution
|
||||
*/
|
||||
public function calculateMultiFloorRetribution($buildingFunctionId, array $floors, $luasBangunan, $indeksLokasi = 'sedang')
|
||||
{
|
||||
$floorDetails = [];
|
||||
$totalRetribution = 0;
|
||||
|
||||
foreach ($floors as $floor) {
|
||||
$result = $this->calculateRetribution($buildingFunctionId, $floor, $luasBangunan, $indeksLokasi, true);
|
||||
|
||||
if ($result['success']) {
|
||||
$floorDetails[$floor] = $result['results'];
|
||||
$totalRetribution += $result['results']['total_retribution'];
|
||||
}
|
||||
}
|
||||
|
||||
return [
|
||||
'success' => true,
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'floors' => $floors,
|
||||
'luas_bangunan' => $luasBangunan,
|
||||
'indeks_lokalitas_type' => $indeksLokasi,
|
||||
'floor_details' => $floorDetails,
|
||||
'total_retribution' => $totalRetribution,
|
||||
'average_per_floor' => count($floors) > 0 ? $totalRetribution / count($floors) : 0
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculate Rundown 4 per-floor value with ROUNDDOWN
|
||||
* Formula: =ROUNDDOWN(($E13*($F13+$G13+(0.5*I$3))),4)
|
||||
*/
|
||||
private function calculateRundown4($parameters)
|
||||
{
|
||||
$calculation = $parameters['fungsi'] *
|
||||
($parameters['ip_permanen'] + $parameters['ip_kompleksitas'] +
|
||||
($parameters['multiplier_ketinggian'] * $parameters['ip_ketinggian']));
|
||||
|
||||
// Apply ROUNDDOWN with 4 decimal places
|
||||
return floor($calculation * 10000) / 10000;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get parameter values for calculation
|
||||
*/
|
||||
private function getParameterValues($buildingFunctionId, $floorLevel, $indeksLokasi)
|
||||
{
|
||||
$buildingFunction = BuildingFunction::find($buildingFunctionId);
|
||||
if (!$buildingFunction) {
|
||||
throw new \Exception("Building function not found: {$buildingFunctionId}");
|
||||
}
|
||||
|
||||
$functionCode = strtolower($buildingFunction->code);
|
||||
|
||||
$lokalitasMapping = [
|
||||
'rendah' => 'indeks_lokalitas_rendah',
|
||||
'sedang' => 'indeks_lokalitas_sedang',
|
||||
'tinggi' => 'indeks_lokalitas_tinggi'
|
||||
];
|
||||
|
||||
$lokalitasParamCode = $lokalitasMapping[$indeksLokasi] ?? 'indeks_lokalitas_sedang';
|
||||
|
||||
$parameterCodes = [
|
||||
'tarif_dasar',
|
||||
'koefisien_dasar',
|
||||
'multiplier_ketinggian',
|
||||
'asumsi_prasarana',
|
||||
$lokalitasParamCode,
|
||||
"ip_ketinggian_{$floorLevel}",
|
||||
"fungsi_{$functionCode}",
|
||||
"ip_permanen_{$functionCode}",
|
||||
"ip_kompleksitas_{$functionCode}"
|
||||
];
|
||||
|
||||
$parameters = MasterParameter::whereIn('parameter_code', $parameterCodes)->get();
|
||||
|
||||
$values = [];
|
||||
foreach ($parameters as $param) {
|
||||
$values[$param->parameter_code] = $param->default_value;
|
||||
}
|
||||
|
||||
return [
|
||||
'tarif_dasar' => $values['tarif_dasar'] ?? 7035000,
|
||||
'koefisien_dasar' => $values['koefisien_dasar'] ?? 1,
|
||||
'multiplier_ketinggian' => $values['multiplier_ketinggian'] ?? 0.5,
|
||||
'asumsi_prasarana' => $values['asumsi_prasarana'] ?? 0.5,
|
||||
'indeks_lokalitas' => $values[$lokalitasParamCode] ?? 0.004,
|
||||
'ip_ketinggian' => $values["ip_ketinggian_{$floorLevel}"] ?? 1.0,
|
||||
'fungsi' => $values["fungsi_{$functionCode}"] ?? 0,
|
||||
'ip_permanen' => $values["ip_permanen_{$functionCode}"] ?? 0,
|
||||
'ip_kompleksitas' => $values["ip_kompleksitas_{$functionCode}"] ?? 0
|
||||
];
|
||||
}
|
||||
}
|
||||
@@ -1,254 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Services;
|
||||
|
||||
use App\Models\BuildingType;
|
||||
use App\Models\HeightIndex;
|
||||
use App\Models\RetributionConfig;
|
||||
use App\Models\RetributionCalculation;
|
||||
|
||||
class RetributionCalculatorService
|
||||
{
|
||||
/**
|
||||
* Calculate retribution for given parameters
|
||||
*/
|
||||
public function calculate(
|
||||
int $buildingTypeId,
|
||||
int $floorNumber,
|
||||
float $buildingArea,
|
||||
bool $saveResult = true,
|
||||
bool $excelCompatibleMode = false
|
||||
): array {
|
||||
// Get building type with indices
|
||||
$buildingType = BuildingType::with('indices')->findOrFail($buildingTypeId);
|
||||
|
||||
// Check if building type is free
|
||||
if ($buildingType->isFree()) {
|
||||
return $this->createFreeResult($buildingType, $floorNumber, $buildingArea, $saveResult);
|
||||
}
|
||||
|
||||
// Get height index
|
||||
$heightIndex = HeightIndex::getHeightIndexByFloor($floorNumber);
|
||||
|
||||
// Get configuration values
|
||||
$baseValue = RetributionConfig::getValue('BASE_VALUE', 70350);
|
||||
$infrastructureMultiplier = RetributionConfig::getValue('INFRASTRUCTURE_MULTIPLIER', 0.5);
|
||||
$heightMultiplier = RetributionConfig::getValue('HEIGHT_MULTIPLIER', 0.5);
|
||||
|
||||
// Get indices
|
||||
$indices = $buildingType->indices;
|
||||
if (!$indices) {
|
||||
throw new \Exception("Indices not found for building type: {$buildingType->name}");
|
||||
}
|
||||
|
||||
// Calculate using Excel formula
|
||||
$result = $this->executeCalculation(
|
||||
$buildingType,
|
||||
$indices,
|
||||
$heightIndex,
|
||||
$baseValue,
|
||||
$infrastructureMultiplier,
|
||||
$heightMultiplier,
|
||||
$floorNumber,
|
||||
$buildingArea,
|
||||
$excelCompatibleMode
|
||||
);
|
||||
|
||||
// Save result if requested
|
||||
if ($saveResult) {
|
||||
$calculation = RetributionCalculation::createCalculation(
|
||||
$buildingTypeId,
|
||||
$floorNumber,
|
||||
$buildingArea,
|
||||
$result['total_retribution'],
|
||||
$result['calculation_detail']
|
||||
);
|
||||
$result['calculation_id'] = $calculation->calculation_id;
|
||||
}
|
||||
|
||||
return $result;
|
||||
}
|
||||
|
||||
/**
|
||||
* Execute the main calculation logic
|
||||
*/
|
||||
protected function executeCalculation(
|
||||
BuildingType $buildingType,
|
||||
$indices,
|
||||
float $heightIndex,
|
||||
float $baseValue,
|
||||
float $infrastructureMultiplier,
|
||||
float $heightMultiplier,
|
||||
int $floorNumber,
|
||||
float $buildingArea,
|
||||
bool $excelCompatibleMode = false
|
||||
): array {
|
||||
// Step 1: Calculate H5 coefficient (Excel formula: RUNDOWN(($E5*($F5+$G5+(0.5*H$3))),4))
|
||||
// H5 = coefficient * (ip_permanent + ip_complexity + (height_multiplier * height_index))
|
||||
$h5Raw = $indices->coefficient * (
|
||||
$indices->ip_permanent +
|
||||
$indices->ip_complexity +
|
||||
($heightMultiplier * $heightIndex)
|
||||
);
|
||||
|
||||
// Apply RUNDOWN (floor to 4 decimal places)
|
||||
$h5 = floor($h5Raw * 10000) / 10000;
|
||||
|
||||
// Step 2: Main calculation (Excel: 1*D5*(N5*base_value*H5*1))
|
||||
// Main = building_area * locality_index * base_value * h5
|
||||
$mainCalculation = $buildingArea * $indices->locality_index * $baseValue * $h5;
|
||||
|
||||
// Step 3: Infrastructure calculation (Excel: O3*(1*D5*(N5*base_value*H5*1)))
|
||||
// Additional = infrastructure_multiplier * main_calculation
|
||||
$infrastructureCalculation = $infrastructureMultiplier * $mainCalculation;
|
||||
|
||||
// Step 4: Total retribution (Main + Infrastructure)
|
||||
if ($excelCompatibleMode) {
|
||||
// Try to match Excel exactly - round intermediate calculations
|
||||
$mainCalculation = round($mainCalculation, 0);
|
||||
$infrastructureCalculation = round($infrastructureCalculation, 0);
|
||||
$totalRetribution = $mainCalculation + $infrastructureCalculation;
|
||||
} else {
|
||||
// Apply standard rounding to match Excel results more closely
|
||||
$totalRetribution = round($mainCalculation + $infrastructureCalculation, 0);
|
||||
}
|
||||
|
||||
return [
|
||||
'building_type' => [
|
||||
'id' => $buildingType->id,
|
||||
'code' => $buildingType->code,
|
||||
'name' => $buildingType->name,
|
||||
'is_free' => $buildingType->is_free
|
||||
],
|
||||
'input_parameters' => [
|
||||
'building_area' => $buildingArea,
|
||||
'floor_number' => $floorNumber,
|
||||
'height_index' => $heightIndex,
|
||||
'base_value' => $baseValue,
|
||||
'infrastructure_multiplier' => $infrastructureMultiplier,
|
||||
'height_multiplier' => $heightMultiplier
|
||||
],
|
||||
'indices' => [
|
||||
'coefficient' => $indices->coefficient,
|
||||
'ip_permanent' => $indices->ip_permanent,
|
||||
'ip_complexity' => $indices->ip_complexity,
|
||||
'locality_index' => $indices->locality_index,
|
||||
'infrastructure_factor' => $indices->infrastructure_factor
|
||||
],
|
||||
'calculation_steps' => [
|
||||
'h5_coefficient' => [
|
||||
'formula' => 'RUNDOWN((coefficient * (ip_permanent + ip_complexity + (height_multiplier * height_index))), 4)',
|
||||
'calculation' => "RUNDOWN(({$indices->coefficient} * ({$indices->ip_permanent} + {$indices->ip_complexity} + ({$heightMultiplier} * {$heightIndex}))), 4)",
|
||||
'raw_result' => $h5Raw,
|
||||
'result' => $h5
|
||||
],
|
||||
'main_calculation' => [
|
||||
'formula' => 'building_area * locality_index * base_value * h5',
|
||||
'calculation' => "{$buildingArea} * {$indices->locality_index} * {$baseValue} * {$h5}",
|
||||
'result' => $mainCalculation
|
||||
],
|
||||
'infrastructure_calculation' => [
|
||||
'formula' => 'infrastructure_multiplier * main_calculation',
|
||||
'calculation' => "{$infrastructureMultiplier} * {$mainCalculation}",
|
||||
'result' => $infrastructureCalculation
|
||||
],
|
||||
'total_calculation' => [
|
||||
'formula' => 'main_calculation + infrastructure_calculation',
|
||||
'calculation' => "{$mainCalculation} + {$infrastructureCalculation}",
|
||||
'result' => $totalRetribution
|
||||
]
|
||||
],
|
||||
'total_retribution' => $totalRetribution,
|
||||
'formatted_amount' => 'Rp ' . number_format($totalRetribution, 2, ',', '.'),
|
||||
'calculation_detail' => [
|
||||
'h5_raw' => $h5Raw,
|
||||
'h5' => $h5,
|
||||
'main' => $mainCalculation,
|
||||
'infrastructure' => $infrastructureCalculation,
|
||||
'total' => $totalRetribution
|
||||
]
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Create result for free building types
|
||||
*/
|
||||
protected function createFreeResult(
|
||||
BuildingType $buildingType,
|
||||
int $floorNumber,
|
||||
float $buildingArea,
|
||||
bool $saveResult
|
||||
): array {
|
||||
$result = [
|
||||
'building_type' => [
|
||||
'id' => $buildingType->id,
|
||||
'code' => $buildingType->code,
|
||||
'name' => $buildingType->name,
|
||||
'is_free' => true
|
||||
],
|
||||
'input_parameters' => [
|
||||
'building_area' => $buildingArea,
|
||||
'floor_number' => $floorNumber
|
||||
],
|
||||
'total_retribution' => 0.0,
|
||||
'formatted_amount' => 'Rp 0 (Gratis)',
|
||||
'calculation_detail' => [
|
||||
'reason' => 'Building type is free of charge',
|
||||
'total' => 0.0
|
||||
]
|
||||
];
|
||||
|
||||
if ($saveResult) {
|
||||
$calculation = RetributionCalculation::createCalculation(
|
||||
$buildingType->id,
|
||||
$floorNumber,
|
||||
$buildingArea,
|
||||
0.0,
|
||||
$result['calculation_detail']
|
||||
);
|
||||
$result['calculation_id'] = $calculation->calculation_id;
|
||||
}
|
||||
|
||||
return $result;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation by ID
|
||||
*/
|
||||
public function getCalculationById(string $calculationId): ?RetributionCalculation
|
||||
{
|
||||
return RetributionCalculation::with('buildingType')
|
||||
->where('calculation_id', $calculationId)
|
||||
->first();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all available building types for calculation
|
||||
*/
|
||||
public function getAvailableBuildingTypes(): array
|
||||
{
|
||||
return BuildingType::with('indices')
|
||||
->active()
|
||||
->children() // Only child types can be used for calculation
|
||||
->get()
|
||||
->map(function ($type) {
|
||||
return [
|
||||
'id' => $type->id,
|
||||
'code' => $type->code,
|
||||
'name' => $type->name,
|
||||
'is_free' => $type->is_free,
|
||||
'has_indices' => $type->indices !== null,
|
||||
'coefficient' => $type->indices ? $type->indices->coefficient : null
|
||||
];
|
||||
})
|
||||
->toArray();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get all available floor numbers
|
||||
*/
|
||||
public function getAvailableFloors(): array
|
||||
{
|
||||
return HeightIndex::getAvailableFloors();
|
||||
}
|
||||
}
|
||||
294
app/Services/RetributionProposalService.php
Normal file
294
app/Services/RetributionProposalService.php
Normal file
@@ -0,0 +1,294 @@
|
||||
<?php
|
||||
|
||||
namespace App\Services;
|
||||
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\RetributionProposal;
|
||||
use App\Models\RetributionFormula;
|
||||
use App\Models\FloorHeightIndex;
|
||||
use App\Models\BuildingFunctionParameter;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class RetributionProposalService
|
||||
{
|
||||
/**
|
||||
* Create retribution proposal for spatial planning
|
||||
*/
|
||||
public function createProposalForSpatialPlanning(
|
||||
SpatialPlanning $spatialPlanning,
|
||||
int $buildingFunctionId,
|
||||
int $floorNumber,
|
||||
float $floorArea,
|
||||
float $totalBuildingArea = null,
|
||||
string $notes = null
|
||||
): RetributionProposal {
|
||||
// Get building function and its parameters
|
||||
$buildingFunction = BuildingFunction::with('parameter')->findOrFail($buildingFunctionId);
|
||||
$parameters = $buildingFunction->parameter;
|
||||
|
||||
if (!$parameters) {
|
||||
throw new \Exception("Building function parameters not found for ID: {$buildingFunctionId}");
|
||||
}
|
||||
|
||||
// Get floor height index
|
||||
$floorHeightIndex = FloorHeightIndex::where('floor_number', $floorNumber)->first();
|
||||
if (!$floorHeightIndex) {
|
||||
throw new \Exception("Floor height index not found for floor: {$floorNumber}");
|
||||
}
|
||||
|
||||
// Get retribution formula
|
||||
$retributionFormula = RetributionFormula::where('building_function_id', $buildingFunctionId)
|
||||
->where('floor_number', $floorNumber)
|
||||
->first();
|
||||
|
||||
if (!$retributionFormula) {
|
||||
throw new \Exception("Retribution formula not found for building function ID: {$buildingFunctionId}, floor: {$floorNumber}");
|
||||
}
|
||||
|
||||
// Calculate retribution using Excel formula
|
||||
$calculationResult = $this->calculateRetribution(
|
||||
$floorArea,
|
||||
$parameters,
|
||||
$floorHeightIndex->ip_ketinggian
|
||||
);
|
||||
|
||||
// Create retribution proposal
|
||||
return RetributionProposal::create([
|
||||
'spatial_planning_id' => $spatialPlanning->id,
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'retribution_formula_id' => $retributionFormula->id,
|
||||
'floor_number' => $floorNumber,
|
||||
'floor_area' => $floorArea,
|
||||
'total_building_area' => $totalBuildingArea ?? $floorArea,
|
||||
'ip_ketinggian' => $floorHeightIndex->ip_ketinggian,
|
||||
'floor_retribution_amount' => $calculationResult['total_retribution'],
|
||||
'total_retribution_amount' => $calculationResult['total_retribution'],
|
||||
'calculation_parameters' => $calculationResult['parameters'],
|
||||
'calculation_breakdown' => $calculationResult['breakdown'],
|
||||
'notes' => $notes,
|
||||
'calculated_at' => Carbon::now()
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Create proposal without spatial planning
|
||||
*/
|
||||
public function createStandaloneProposal(
|
||||
int $buildingFunctionId,
|
||||
int $floorNumber,
|
||||
float $floorArea,
|
||||
float $totalBuildingArea = null,
|
||||
string $notes = null
|
||||
): RetributionProposal {
|
||||
// Get building function and its parameters
|
||||
$buildingFunction = BuildingFunction::with('parameter')->findOrFail($buildingFunctionId);
|
||||
$parameters = $buildingFunction->parameter;
|
||||
|
||||
if (!$parameters) {
|
||||
throw new \Exception("Building function parameters not found for ID: {$buildingFunctionId}");
|
||||
}
|
||||
|
||||
// Get floor height index
|
||||
$floorHeightIndex = FloorHeightIndex::where('floor_number', $floorNumber)->first();
|
||||
if (!$floorHeightIndex) {
|
||||
throw new \Exception("Floor height index not found for floor: {$floorNumber}");
|
||||
}
|
||||
|
||||
// Get retribution formula
|
||||
$retributionFormula = RetributionFormula::where('building_function_id', $buildingFunctionId)
|
||||
->where('floor_number', $floorNumber)
|
||||
->first();
|
||||
|
||||
if (!$retributionFormula) {
|
||||
throw new \Exception("Retribution formula not found for building function ID: {$buildingFunctionId}, floor: {$floorNumber}");
|
||||
}
|
||||
|
||||
// Calculate retribution using Excel formula
|
||||
$calculationResult = $this->calculateRetribution(
|
||||
$floorArea,
|
||||
$parameters,
|
||||
$floorHeightIndex->ip_ketinggian
|
||||
);
|
||||
|
||||
// Create retribution proposal
|
||||
return RetributionProposal::create([
|
||||
'spatial_planning_id' => null,
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'retribution_formula_id' => $retributionFormula->id,
|
||||
'floor_number' => $floorNumber,
|
||||
'floor_area' => $floorArea,
|
||||
'total_building_area' => $totalBuildingArea ?? $floorArea,
|
||||
'ip_ketinggian' => $floorHeightIndex->ip_ketinggian,
|
||||
'floor_retribution_amount' => $calculationResult['total_retribution'],
|
||||
'total_retribution_amount' => $calculationResult['total_retribution'],
|
||||
'calculation_parameters' => $calculationResult['parameters'],
|
||||
'calculation_breakdown' => $calculationResult['breakdown'],
|
||||
'notes' => $notes,
|
||||
'calculated_at' => Carbon::now()
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Calculate retribution using Excel formula
|
||||
*/
|
||||
protected function calculateRetribution(
|
||||
float $floorArea,
|
||||
BuildingFunctionParameter $parameters,
|
||||
float $ipKetinggian
|
||||
): array {
|
||||
// Excel formula parameters
|
||||
$fungsi_bangunan = $parameters->fungsi_bangunan;
|
||||
$ip_permanen = $parameters->ip_permanen;
|
||||
$ip_kompleksitas = $parameters->ip_kompleksitas;
|
||||
$indeks_lokalitas = $parameters->indeks_lokalitas;
|
||||
$base_value = 70350;
|
||||
$additional_factor = 0.5;
|
||||
|
||||
// Step 1: Calculate H13 (floor coefficient)
|
||||
$h13 = $fungsi_bangunan * ($ip_permanen + $ip_kompleksitas + (0.5 * $ipKetinggian));
|
||||
|
||||
// Step 2: Main calculation
|
||||
$main_calculation = 1 * $floorArea * ($indeks_lokalitas * $base_value * $h13 * 1);
|
||||
|
||||
// Step 3: Additional (50%)
|
||||
$additional_calculation = $additional_factor * $main_calculation;
|
||||
|
||||
// Step 4: Total
|
||||
$total_retribution = $main_calculation + $additional_calculation;
|
||||
|
||||
return [
|
||||
'total_retribution' => $total_retribution,
|
||||
'parameters' => [
|
||||
'fungsi_bangunan' => $fungsi_bangunan,
|
||||
'ip_permanen' => $ip_permanen,
|
||||
'ip_kompleksitas' => $ip_kompleksitas,
|
||||
'ip_ketinggian' => $ipKetinggian,
|
||||
'indeks_lokalitas' => $indeks_lokalitas,
|
||||
'base_value' => $base_value,
|
||||
'additional_factor' => $additional_factor,
|
||||
'floor_area' => $floorArea
|
||||
],
|
||||
'breakdown' => [
|
||||
'h13_calculation' => [
|
||||
'formula' => 'fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian))',
|
||||
'calculation' => "{$fungsi_bangunan} * ({$ip_permanen} + {$ip_kompleksitas} + (0.5 * {$ipKetinggian}))",
|
||||
'result' => $h13
|
||||
],
|
||||
'main_calculation' => [
|
||||
'formula' => '1 * floor_area * (indeks_lokalitas * base_value * h13 * 1)',
|
||||
'calculation' => "1 * {$floorArea} * ({$indeks_lokalitas} * {$base_value} * {$h13} * 1)",
|
||||
'result' => $main_calculation
|
||||
],
|
||||
'additional_calculation' => [
|
||||
'formula' => 'additional_factor * main_calculation',
|
||||
'calculation' => "{$additional_factor} * {$main_calculation}",
|
||||
'result' => $additional_calculation
|
||||
],
|
||||
'total_calculation' => [
|
||||
'formula' => 'main_calculation + additional_calculation',
|
||||
'calculation' => "{$main_calculation} + {$additional_calculation}",
|
||||
'result' => $total_retribution
|
||||
]
|
||||
]
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Get proposal statistics
|
||||
*/
|
||||
public function getStatistics(): array
|
||||
{
|
||||
return [
|
||||
'total_proposals' => RetributionProposal::count(),
|
||||
'total_amount' => RetributionProposal::sum('total_retribution_amount'),
|
||||
'average_amount' => RetributionProposal::avg('total_retribution_amount'),
|
||||
'proposals_with_spatial_planning' => RetributionProposal::whereNotNull('spatial_planning_id')->count(),
|
||||
'proposals_without_spatial_planning' => RetributionProposal::whereNull('spatial_planning_id')->count(),
|
||||
'by_building_function' => RetributionProposal::with('buildingFunction')
|
||||
->selectRaw('building_function_id, COUNT(*) as count, SUM(total_retribution_amount) as total_amount')
|
||||
->groupBy('building_function_id')
|
||||
->orderBy('total_amount', 'desc')
|
||||
->get()
|
||||
->map(function ($item) {
|
||||
return [
|
||||
'building_function_id' => $item->building_function_id,
|
||||
'building_function_name' => $item->buildingFunction->name ?? 'Unknown',
|
||||
'count' => $item->count,
|
||||
'total_amount' => $item->total_amount,
|
||||
'formatted_amount' => 'Rp ' . number_format($item->total_amount, 0, ',', '.')
|
||||
];
|
||||
})->toArray()
|
||||
];
|
||||
}
|
||||
|
||||
/**
|
||||
* Detect building function from text
|
||||
*/
|
||||
public function detectBuildingFunction(string $text): ?BuildingFunction
|
||||
{
|
||||
$text = strtolower($text);
|
||||
|
||||
// Detection patterns - order matters (more specific first)
|
||||
$patterns = [
|
||||
'HUNIAN_TIDAK_SEDERHANA' => [
|
||||
'hunian mewah', 'villa', 'apartemen', 'kondominium', 'townhouse'
|
||||
],
|
||||
'HUNIAN_SEDERHANA' => [
|
||||
'hunian sederhana', 'hunian', 'rumah', 'perumahan', 'residential', 'fungsi hunian'
|
||||
],
|
||||
'USAHA_BESAR' => [
|
||||
'usaha besar', 'pabrik', 'industri', 'mall', 'hotel', 'restoran', 'supermarket',
|
||||
'plaza', 'gedung perkantoran', 'non-mikro', 'non mikro'
|
||||
],
|
||||
'USAHA_KECIL' => [
|
||||
'usaha kecil', 'umkm', 'warung', 'toko', 'mikro', 'kios'
|
||||
],
|
||||
'CAMPURAN_BESAR' => [
|
||||
'campuran besar', 'mixed use besar'
|
||||
],
|
||||
'CAMPURAN_KECIL' => [
|
||||
'campuran kecil', 'ruko', 'mixed use', 'campuran'
|
||||
],
|
||||
'SOSIAL_BUDAYA' => [
|
||||
'sekolah', 'rumah sakit', 'masjid', 'gereja', 'puskesmas', 'klinik',
|
||||
'universitas', 'perpustakaan', 'museum'
|
||||
],
|
||||
'AGAMA' => [
|
||||
'tempat ibadah', 'masjid', 'mushola', 'gereja', 'pura', 'vihara', 'klenteng'
|
||||
]
|
||||
];
|
||||
|
||||
// Try to find exact matches first
|
||||
foreach ($patterns as $functionCode => $keywords) {
|
||||
foreach ($keywords as $keyword) {
|
||||
if (strpos($text, $keyword) !== false) {
|
||||
$buildingFunction = BuildingFunction::where('code', $functionCode)->first();
|
||||
if ($buildingFunction) {
|
||||
return $buildingFunction;
|
||||
}
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
// Debug: Log what we're trying to match
|
||||
\Illuminate\Support\Facades\Log::info("Building function detection failed for text: '{$text}'");
|
||||
|
||||
// If no exact match, try to find by name similarity
|
||||
$allFunctions = BuildingFunction::whereNotNull('parent_id')->get(); // Only child functions
|
||||
|
||||
foreach ($allFunctions as $function) {
|
||||
$functionName = strtolower($function->name);
|
||||
|
||||
// Check if any word from the function name appears in the text
|
||||
$words = explode(' ', $functionName);
|
||||
foreach ($words as $word) {
|
||||
if (strlen($word) > 3 && strpos($text, $word) !== false) {
|
||||
return $function;
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
return null;
|
||||
}
|
||||
}
|
||||
@@ -6,8 +6,7 @@ use App\Models\BigdataResume;
|
||||
use App\Models\DataSetting;
|
||||
use App\Models\ImportDatasource;
|
||||
use App\Models\PbgTaskGoogleSheet;
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\RetributionCalculation;
|
||||
use App\Models\RetributionProposal;
|
||||
use Carbon\Carbon;
|
||||
use Exception;
|
||||
use Google\Client as Google_Client;
|
||||
@@ -238,7 +237,7 @@ class ServiceGoogleSheet
|
||||
$result = [];
|
||||
|
||||
foreach ($sections as $key => $identifier) {
|
||||
$values = $this->get_values_from_section($identifier, [10, 11], 2);
|
||||
$values = $this->get_values_from_section(2, $identifier, [10, 11]);
|
||||
|
||||
if (!empty($values)) {
|
||||
$result[$key] = [
|
||||
@@ -277,8 +276,8 @@ class ServiceGoogleSheet
|
||||
'process_in_technical_office_count' => $this->convertToInteger($result['PROSES_DINAS_TEKNIS']['total'] ?? null) ?? 0,
|
||||
'process_in_technical_office_sum' => $this->convertToDecimal($result['PROSES_DINAS_TEKNIS']['nominal'] ?? null) ?? 0,
|
||||
// TATA RUANG
|
||||
'spatial_count' => $this->getSpatialPlanningWithCalculationCount(),
|
||||
'spatial_sum' => $this->getSpatialPlanningCalculationSum()
|
||||
'spatial_count' => RetributionProposal::count(),
|
||||
'spatial_sum' => RetributionProposal::sum('total_retribution_amount'),
|
||||
]);
|
||||
|
||||
// Save data settings
|
||||
@@ -371,12 +370,12 @@ class ServiceGoogleSheet
|
||||
|
||||
/**
|
||||
* Get specific values from a row that contains a specific text/section identifier
|
||||
* @param int $no_sheet Sheet number (0-based)
|
||||
* @param string $section_identifier Text to search for in the row
|
||||
* @param array $column_indices Array of column indices to extract values from
|
||||
* @param int $no_sheet Sheet number (0-based)
|
||||
* @return array Array of values from specified columns, or empty array if section not found
|
||||
*/
|
||||
private function get_values_from_section(string $section_identifier, array $column_indices = [], int $no_sheet = 1) {
|
||||
private function get_values_from_section($no_sheet = 1, $section_identifier, $column_indices = []) {
|
||||
try {
|
||||
$sheet_data = $this->get_data_by_sheet($no_sheet);
|
||||
|
||||
@@ -470,48 +469,6 @@ class ServiceGoogleSheet
|
||||
return is_numeric($value) ? (float) number_format((float) $value, 2, '.', '') : null;
|
||||
}
|
||||
|
||||
/**
|
||||
* Get count of spatial plannings that have active retribution calculations
|
||||
*/
|
||||
public function getSpatialPlanningWithCalculationCount(): int
|
||||
{
|
||||
try {
|
||||
return SpatialPlanning::whereHas('retributionCalculations', function ($query) {
|
||||
$query->where('is_active', true);
|
||||
})->count();
|
||||
} catch (\Exception $e) {
|
||||
Log::error("Error getting spatial planning with calculation count", ['error' => $e->getMessage()]);
|
||||
return 0;
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* Get total sum of retribution amounts for spatial plannings with active calculations
|
||||
*/
|
||||
public function getSpatialPlanningCalculationSum(): float
|
||||
{
|
||||
try {
|
||||
// Get all spatial plannings that have active calculations
|
||||
$spatialPlannings = SpatialPlanning::whereHas('retributionCalculations', function ($query) {
|
||||
$query->where('is_active', true);
|
||||
})->with(['retributionCalculations.retributionCalculation'])
|
||||
->get();
|
||||
|
||||
$totalSum = 0;
|
||||
foreach ($spatialPlannings as $spatialPlanning) {
|
||||
$activeCalculation = $spatialPlanning->activeRetributionCalculation;
|
||||
if ($activeCalculation && $activeCalculation->retributionCalculation) {
|
||||
$totalSum += $activeCalculation->retributionCalculation->retribution_amount;
|
||||
}
|
||||
}
|
||||
|
||||
return (float) $totalSum;
|
||||
} catch (\Exception $e) {
|
||||
Log::error("Error getting spatial planning calculation sum", ['error' => $e->getMessage()]);
|
||||
return 0.0;
|
||||
}
|
||||
}
|
||||
|
||||
private function convertToDate($dateString)
|
||||
{
|
||||
try {
|
||||
|
||||
@@ -1,79 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace App\Traits;
|
||||
|
||||
use App\Models\RetributionCalculation;
|
||||
use App\Models\CalculableRetribution;
|
||||
use Illuminate\Database\Eloquent\Relations\MorphMany;
|
||||
use Illuminate\Database\Eloquent\Relations\MorphOne;
|
||||
|
||||
trait HasRetributionCalculation
|
||||
{
|
||||
/**
|
||||
* Get all retribution calculations for this model (polymorphic many-to-many)
|
||||
*/
|
||||
public function retributionCalculations(): MorphMany
|
||||
{
|
||||
return $this->morphMany(CalculableRetribution::class, 'calculable');
|
||||
}
|
||||
|
||||
/**
|
||||
* Get active retribution calculation
|
||||
*/
|
||||
public function activeRetributionCalculation(): MorphOne
|
||||
{
|
||||
return $this->morphOne(CalculableRetribution::class, 'calculable')
|
||||
->where('is_active', true)
|
||||
->latest('assigned_at');
|
||||
}
|
||||
|
||||
/**
|
||||
* Assign calculation to this model
|
||||
*/
|
||||
public function assignRetributionCalculation(RetributionCalculation $calculation, string $notes = null): CalculableRetribution
|
||||
{
|
||||
// Deactivate previous active calculation
|
||||
$this->retributionCalculations()
|
||||
->where('is_active', true)
|
||||
->update(['is_active' => false]);
|
||||
|
||||
// Create new assignment
|
||||
return $this->retributionCalculations()->create([
|
||||
'retribution_calculation_id' => $calculation->id,
|
||||
'is_active' => true,
|
||||
'assigned_at' => now(),
|
||||
'notes' => $notes,
|
||||
]);
|
||||
}
|
||||
|
||||
/**
|
||||
* Get current retribution amount
|
||||
*/
|
||||
public function getCurrentRetributionAmount(): float
|
||||
{
|
||||
$activeCalculation = $this->activeRetributionCalculation;
|
||||
|
||||
return $activeCalculation
|
||||
? $activeCalculation->retributionCalculation->retribution_amount
|
||||
: 0;
|
||||
}
|
||||
|
||||
/**
|
||||
* Check if has active calculation
|
||||
*/
|
||||
public function hasActiveRetributionCalculation(): bool
|
||||
{
|
||||
return $this->activeRetributionCalculation()->exists();
|
||||
}
|
||||
|
||||
/**
|
||||
* Get calculation history
|
||||
*/
|
||||
public function getRetributionCalculationHistory()
|
||||
{
|
||||
return $this->retributionCalculations()
|
||||
->with('retributionCalculation')
|
||||
->orderBy('assigned_at', 'desc')
|
||||
->get();
|
||||
}
|
||||
}
|
||||
@@ -0,0 +1,35 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
Schema::create('master_parameters', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->string('parameter_code', 255)->unique()->comment('Kode unik parameter');
|
||||
$table->string('parameter_name', 255)->comment('Nama parameter');
|
||||
$table->decimal('default_value', 15, 6)->default(0.000000)->comment('Nilai default parameter');
|
||||
$table->string('unit', 50)->nullable()->comment('Satuan parameter');
|
||||
$table->text('description')->nullable()->comment('Deskripsi parameter');
|
||||
$table->timestamps();
|
||||
|
||||
// Add indexes for better performance
|
||||
$table->index('parameter_name', 'idx_master_parameters_name');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('master_parameters');
|
||||
}
|
||||
};
|
||||
@@ -0,0 +1,36 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
Schema::create('master_formulas', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->string('formula_code', 255)->unique()->comment('Kode unik formula');
|
||||
$table->string('formula_name', 255)->comment('Nama formula');
|
||||
$table->text('formula_expression')->comment('Formula matematika');
|
||||
$table->string('formula_category', 255)->nullable()->comment('Kategori formula');
|
||||
$table->text('description')->nullable()->comment('Deskripsi formula');
|
||||
$table->text('usage_notes')->nullable()->comment('Catatan penggunaan');
|
||||
$table->timestamps();
|
||||
|
||||
// Add indexes for better performance
|
||||
$table->index('formula_category', 'idx_master_formulas_category');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('master_formulas');
|
||||
}
|
||||
};
|
||||
@@ -0,0 +1,36 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
Schema::create('formula_parameters', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->foreignId('formula_id')->constrained('master_formulas')->onDelete('cascade')->comment('ID formula');
|
||||
$table->foreignId('parameter_id')->constrained('master_parameters')->onDelete('cascade')->comment('ID parameter');
|
||||
$table->timestamps();
|
||||
|
||||
// Composite unique constraint to prevent duplicate relations
|
||||
$table->unique(['formula_id', 'parameter_id'], 'unique_formula_parameter');
|
||||
|
||||
// Indexes for better performance
|
||||
$table->index('formula_id', 'idx_formula_parameters_formula');
|
||||
$table->index('parameter_id', 'idx_formula_parameters_parameter');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('formula_parameters');
|
||||
}
|
||||
};
|
||||
@@ -0,0 +1,45 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
Schema::create('building_function_formula_configs', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->foreignId('building_function_id')->constrained('building_functions')->onDelete('cascade')->comment('ID fungsi bangunan');
|
||||
$table->foreignId('formula_id')->constrained('master_formulas')->onDelete('cascade')->comment('ID formula yang digunakan');
|
||||
$table->integer('floor_level')->comment('Level lantai (1, 2, 3, dst. 0 untuk semua lantai)');
|
||||
$table->integer('min_floor')->nullable()->comment('Minimum lantai yang berlaku (opsional)');
|
||||
$table->integer('max_floor')->nullable()->comment('Maximum lantai yang berlaku (opsional)');
|
||||
$table->decimal('multiplier', 8, 4)->default(1.0000)->comment('Pengali khusus untuk lantai ini');
|
||||
$table->boolean('is_active')->default(true)->comment('Status aktif konfigurasi');
|
||||
$table->integer('priority')->default(0)->comment('Prioritas jika ada konflik (angka lebih besar = prioritas lebih tinggi)');
|
||||
$table->text('notes')->nullable()->comment('Catatan konfigurasi');
|
||||
$table->timestamps();
|
||||
|
||||
// Composite unique constraint untuk mencegah duplikasi konfigurasi
|
||||
$table->unique(['building_function_id', 'formula_id', 'floor_level'], 'unique_building_formula_floor');
|
||||
|
||||
// Indexes untuk performa
|
||||
$table->index(['building_function_id', 'floor_level'], 'idx_building_floor');
|
||||
$table->index(['building_function_id', 'is_active'], 'idx_building_active');
|
||||
$table->index(['floor_level', 'is_active'], 'idx_floor_active');
|
||||
$table->index('priority', 'idx_priority');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('building_function_formula_configs');
|
||||
}
|
||||
};
|
||||
@@ -1,95 +0,0 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
// 1. Tabel Fungsi Bangunan (Simplified)
|
||||
Schema::create('building_types', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->string('code', 10)->unique()->comment('Kode fungsi bangunan');
|
||||
$table->string('name', 100)->comment('Nama fungsi bangunan');
|
||||
$table->unsignedBigInteger('parent_id')->nullable()->comment('Parent ID untuk hierarki');
|
||||
$table->tinyInteger('level')->default(1)->comment('Level hierarki (1=parent, 2=child)');
|
||||
$table->boolean('is_free')->default(false)->comment('Apakah gratis (keagamaan, MBR)');
|
||||
$table->boolean('is_active')->default(true);
|
||||
$table->timestamps();
|
||||
|
||||
$table->index(['parent_id', 'level']);
|
||||
$table->index('is_active');
|
||||
$table->foreign('parent_id')->references('id')->on('building_types')->onDelete('cascade');
|
||||
});
|
||||
|
||||
// 2. Tabel Parameter Indeks (Simplified)
|
||||
Schema::create('retribution_indices', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->unsignedBigInteger('building_type_id');
|
||||
$table->decimal('coefficient', 8, 4)->comment('Koefisien fungsi bangunan');
|
||||
$table->decimal('ip_permanent', 8, 4)->comment('Indeks Permanensi');
|
||||
$table->decimal('ip_complexity', 8, 4)->comment('Indeks Kompleksitas');
|
||||
$table->decimal('locality_index', 8, 4)->comment('Indeks Lokalitas');
|
||||
$table->decimal('infrastructure_factor', 8, 4)->default(0.5)->comment('Faktor prasarana (default 50%)');
|
||||
$table->boolean('is_active')->default(true);
|
||||
$table->timestamps();
|
||||
|
||||
$table->unique('building_type_id');
|
||||
$table->foreign('building_type_id')->references('id')->on('building_types')->onDelete('cascade');
|
||||
});
|
||||
|
||||
// 3. Tabel Indeks Ketinggian (Simplified)
|
||||
Schema::create('height_indices', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->tinyInteger('floor_number')->unique()->comment('Nomor lantai');
|
||||
$table->decimal('height_index', 8, 6)->comment('Indeks ketinggian');
|
||||
$table->timestamps();
|
||||
|
||||
$table->index('floor_number');
|
||||
});
|
||||
|
||||
// 4. Tabel Konfigurasi Global
|
||||
Schema::create('retribution_configs', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->string('key', 50)->unique()->comment('Kunci konfigurasi');
|
||||
$table->decimal('value', 15, 2)->comment('Nilai konfigurasi');
|
||||
$table->string('description', 200)->comment('Deskripsi konfigurasi');
|
||||
$table->boolean('is_active')->default(true);
|
||||
$table->timestamps();
|
||||
});
|
||||
|
||||
// 5. Tabel Hasil Perhitungan (Simplified)
|
||||
Schema::create('retribution_calculations', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->string('calculation_id', 20)->unique()->comment('ID unik perhitungan');
|
||||
$table->unsignedBigInteger('building_type_id');
|
||||
$table->tinyInteger('floor_number');
|
||||
$table->decimal('building_area', 12, 2)->comment('Luas bangunan (m2)');
|
||||
$table->decimal('retribution_amount', 15, 2)->comment('Jumlah retribusi');
|
||||
$table->json('calculation_detail')->comment('Detail perhitungan');
|
||||
$table->timestamp('calculated_at');
|
||||
$table->timestamps();
|
||||
|
||||
$table->index(['building_type_id', 'floor_number']);
|
||||
$table->index('calculated_at');
|
||||
$table->foreign('building_type_id')->references('id')->on('building_types');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('retribution_calculations');
|
||||
Schema::dropIfExists('retribution_configs');
|
||||
Schema::dropIfExists('height_indices');
|
||||
Schema::dropIfExists('retribution_indices');
|
||||
Schema::dropIfExists('building_types');
|
||||
}
|
||||
};
|
||||
@@ -1,42 +0,0 @@
|
||||
<?php
|
||||
|
||||
use Illuminate\Database\Migrations\Migration;
|
||||
use Illuminate\Database\Schema\Blueprint;
|
||||
use Illuminate\Support\Facades\Schema;
|
||||
|
||||
return new class extends Migration
|
||||
{
|
||||
/**
|
||||
* Run the migrations.
|
||||
*/
|
||||
public function up(): void
|
||||
{
|
||||
Schema::create('calculable_retributions', function (Blueprint $table) {
|
||||
$table->id();
|
||||
$table->unsignedBigInteger('retribution_calculation_id');
|
||||
$table->morphs('calculable'); // calculable_id & calculable_type (automatically creates index)
|
||||
$table->boolean('is_active')->default(true)->comment('Status aktif calculation');
|
||||
$table->timestamp('assigned_at')->useCurrent()->comment('Kapan calculation di-assign');
|
||||
$table->text('notes')->nullable()->comment('Catatan assignment');
|
||||
$table->timestamps();
|
||||
|
||||
// Additional indexes for better performance
|
||||
$table->index('is_active');
|
||||
$table->index('assigned_at');
|
||||
|
||||
// Foreign key constraint
|
||||
$table->foreign('retribution_calculation_id')
|
||||
->references('id')
|
||||
->on('retribution_calculations')
|
||||
->onDelete('cascade');
|
||||
});
|
||||
}
|
||||
|
||||
/**
|
||||
* Reverse the migrations.
|
||||
*/
|
||||
public function down(): void
|
||||
{
|
||||
Schema::dropIfExists('calculable_retributions');
|
||||
}
|
||||
};
|
||||
216
database/seeders/BuildingFunctionFormulaConfigSeeder.php
Normal file
216
database/seeders/BuildingFunctionFormulaConfigSeeder.php
Normal file
@@ -0,0 +1,216 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\BuildingFunctionFormulaConfig;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\MasterFormula;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class BuildingFunctionFormulaConfigSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$now = Carbon::now();
|
||||
|
||||
// Mapping Building Function Codes to IDs (sesuai dengan BuildingFunctionSeeder)
|
||||
$buildingFunctionMapping = [
|
||||
'AGAMA' => 1, // Fungsi Keagamaan
|
||||
'SOSIAL_BUDAYA' => 2, // Fungsi Sosial Budaya
|
||||
'CAMPURAN_KECIL' => 6, // Campuran Kecil
|
||||
'CAMPURAN_BESAR' => 7, // Campuran Besar
|
||||
'USAHA_KECIL' => 8, // UMKM
|
||||
'USAHA_BESAR' => 9, // Usaha Besar (Non-Mikro)
|
||||
'HUNIAN_SEDERHANA' => 10, // Hunian Sederhana <100
|
||||
'HUNIAN_TIDAK_SEDERHANA' => 11, // Hunian Tidak Sederhana >100
|
||||
];
|
||||
|
||||
// Get Formula IDs (akan dibuat di MasterFormulaSeeder)
|
||||
$formulaMapping = [
|
||||
'BEBAS' => 'RETRIBUSI_BEBAS',
|
||||
'DASAR' => 'RETRIBUSI_DASAR',
|
||||
'LENGKAP' => 'RETRIBUSI_DENGAN_PRASARANA'
|
||||
];
|
||||
|
||||
// Configurations per Building Function dan Floor Level
|
||||
$configs = [];
|
||||
|
||||
// 1. FUNGSI KEAGAMAAN - Bebas Retribusi (semua lantai)
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['AGAMA'],
|
||||
'formula_code' => 'RETRIBUSI_BEBAS',
|
||||
'floor_level' => 0, // Berlaku untuk semua lantai
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => 'Bangunan keagamaan bebas retribusi untuk semua lantai'
|
||||
];
|
||||
|
||||
// 2. FUNGSI SOSIAL BUDAYA - Formula dengan parameter khusus
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['SOSIAL_BUDAYA'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Sosial budaya lantai {$floor} - menggunakan parameter khusus sosial budaya"
|
||||
];
|
||||
}
|
||||
|
||||
// 3. CAMPURAN KECIL - Formula per lantai
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['CAMPURAN_KECIL'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Campuran kecil lantai {$floor} - parameter fungsi 0.6"
|
||||
];
|
||||
}
|
||||
|
||||
// 4. CAMPURAN BESAR - Formula per lantai
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['CAMPURAN_BESAR'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Campuran besar lantai {$floor} - parameter fungsi 0.8"
|
||||
];
|
||||
}
|
||||
|
||||
// 5. UMKM - Formula per lantai
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['USAHA_KECIL'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "UMKM lantai {$floor} - parameter fungsi 0.5"
|
||||
];
|
||||
}
|
||||
|
||||
// 6. USAHA BESAR - Formula per lantai
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['USAHA_BESAR'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Usaha besar lantai {$floor} - parameter fungsi 0.7"
|
||||
];
|
||||
}
|
||||
|
||||
// 7. HUNIAN SEDERHANA - Formula khusus dengan IP Kompleksitas 0.3
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['HUNIAN_SEDERHANA'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Hunian sederhana lantai {$floor} - parameter fungsi 0.15, IP kompleksitas 0.3"
|
||||
];
|
||||
}
|
||||
|
||||
// 8. HUNIAN TIDAK SEDERHANA - Formula standar
|
||||
for ($floor = 1; $floor <= 6; $floor++) {
|
||||
$configs[] = [
|
||||
'building_function_id' => $buildingFunctionMapping['HUNIAN_TIDAK_SEDERHANA'],
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'floor_level' => $floor,
|
||||
'min_floor' => null,
|
||||
'max_floor' => null,
|
||||
'multiplier' => 1.0000,
|
||||
'is_active' => true,
|
||||
'priority' => 1,
|
||||
'notes' => "Hunian tidak sederhana lantai {$floor} - parameter fungsi 0.17"
|
||||
];
|
||||
}
|
||||
|
||||
// Insert configurations
|
||||
foreach ($configs as $config) {
|
||||
// Get formula ID
|
||||
$formula = DB::table('master_formulas')
|
||||
->where('formula_code', $config['formula_code'])
|
||||
->first();
|
||||
|
||||
if ($formula) {
|
||||
DB::table('building_function_formula_configs')->updateOrInsert(
|
||||
[
|
||||
'building_function_id' => $config['building_function_id'],
|
||||
'formula_id' => $formula->id,
|
||||
'floor_level' => $config['floor_level']
|
||||
],
|
||||
[
|
||||
'min_floor' => $config['min_floor'],
|
||||
'max_floor' => $config['max_floor'],
|
||||
'multiplier' => $config['multiplier'],
|
||||
'is_active' => $config['is_active'],
|
||||
'priority' => $config['priority'],
|
||||
'notes' => $config['notes'],
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
]
|
||||
);
|
||||
}
|
||||
}
|
||||
|
||||
$this->command->info('Building Function Formula Configurations seeded successfully!');
|
||||
$this->command->info('=== CONFIGURATION SUMMARY ===');
|
||||
$this->command->info('Total configurations created: ' . count($configs));
|
||||
|
||||
// Summary per building function
|
||||
$summary = [];
|
||||
foreach ($configs as $config) {
|
||||
$buildingFunctionId = $config['building_function_id'];
|
||||
if (!isset($summary[$buildingFunctionId])) {
|
||||
$summary[$buildingFunctionId] = 0;
|
||||
}
|
||||
$summary[$buildingFunctionId]++;
|
||||
}
|
||||
|
||||
foreach ($buildingFunctionMapping as $code => $id) {
|
||||
$count = $summary[$id] ?? 0;
|
||||
$this->command->info("{$code}: {$count} configurations");
|
||||
}
|
||||
|
||||
$this->command->info('=== KEY FEATURES ===');
|
||||
$this->command->info('✓ Fungsi Keagamaan: Bebas retribusi semua lantai');
|
||||
$this->command->info('✓ Setiap fungsi bangunan memiliki parameter berbeda');
|
||||
$this->command->info('✓ Setiap lantai memiliki IP ketinggian berbeda');
|
||||
$this->command->info('✓ Hunian sederhana: IP kompleksitas khusus 0.3');
|
||||
$this->command->info('✓ Formula dapat disesuaikan per fungsi dan lantai');
|
||||
}
|
||||
}
|
||||
148
database/seeders/BuildingFunctionParameterSeeder.php
Normal file
148
database/seeders/BuildingFunctionParameterSeeder.php
Normal file
@@ -0,0 +1,148 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\BuildingFunctionParameter;
|
||||
|
||||
class BuildingFunctionParameterSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$parameters = [
|
||||
// Fungsi Keagamaan
|
||||
[
|
||||
'building_function_code' => 'AGAMA',
|
||||
'fungsi_bangunan' => 0,
|
||||
'ip_permanen' => 0,
|
||||
'ip_kompleksitas' => 0,
|
||||
'indeks_lokalitas' => 0,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk fungsi keagamaan'
|
||||
],
|
||||
|
||||
// Fungsi Sosial Budaya
|
||||
[
|
||||
'building_function_code' => 'SOSIAL_BUDAYA',
|
||||
'fungsi_bangunan' => 0.3,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.3,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk fungsi sosial budaya'
|
||||
],
|
||||
|
||||
// Fungsi Campuran Kecil
|
||||
[
|
||||
'building_function_code' => 'CAMPURAN_KECIL',
|
||||
'fungsi_bangunan' => 0.6,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.5,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk fungsi campuran kecil'
|
||||
],
|
||||
|
||||
// Fungsi Campuran Besar
|
||||
[
|
||||
'building_function_code' => 'CAMPURAN_BESAR',
|
||||
'fungsi_bangunan' => 0.8,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.5,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk fungsi campuran besar'
|
||||
],
|
||||
|
||||
// UMKM (Usaha Kecil)
|
||||
[
|
||||
'building_function_code' => 'USAHA_KECIL',
|
||||
'fungsi_bangunan' => 0.5,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.4,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk UMKM'
|
||||
],
|
||||
|
||||
// Usaha Besar (Non-Mikro)
|
||||
[
|
||||
'building_function_code' => 'USAHA_BESAR',
|
||||
'fungsi_bangunan' => 0.7,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.5,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk usaha besar (non-mikro)'
|
||||
],
|
||||
|
||||
// Hunian Sederhana < 100 m2
|
||||
[
|
||||
'building_function_code' => 'HUNIAN_KECIL',
|
||||
'fungsi_bangunan' => 0.15,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.3,
|
||||
'indeks_lokalitas' => 0.4,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk hunian sederhana < 100 m2'
|
||||
],
|
||||
|
||||
// Hunian Sederhana > 100 m2
|
||||
[
|
||||
'building_function_code' => 'HUNIAN_BESAR',
|
||||
'fungsi_bangunan' => 0.17,
|
||||
'ip_permanen' => 0.4,
|
||||
'ip_kompleksitas' => 0.6,
|
||||
'indeks_lokalitas' => 0.4,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk hunian sederhana > 100 m2'
|
||||
],
|
||||
|
||||
// MBR (Masyarakat Berpenghasilan Rendah)
|
||||
[
|
||||
'building_function_code' => 'HUNIAN_MBR',
|
||||
'fungsi_bangunan' => 0,
|
||||
'ip_permanen' => 0,
|
||||
'ip_kompleksitas' => 0,
|
||||
'indeks_lokalitas' => 0,
|
||||
'asumsi_prasarana' => 0.5,
|
||||
'is_active' => true,
|
||||
'notes' => 'Parameter untuk MBR (tarif lebih rendah)'
|
||||
]
|
||||
];
|
||||
|
||||
foreach ($parameters as $parameterData) {
|
||||
$buildingFunction = BuildingFunction::where('code', $parameterData['building_function_code'])->first();
|
||||
|
||||
if ($buildingFunction) {
|
||||
// Remove building_function_code from parameter data
|
||||
$parameterData['building_function_id'] = $buildingFunction->id;
|
||||
unset($parameterData['building_function_code']);
|
||||
|
||||
BuildingFunctionParameter::updateOrCreate(
|
||||
['building_function_id' => $buildingFunction->id],
|
||||
$parameterData
|
||||
);
|
||||
|
||||
$this->command->info("Created/Updated parameter for: {$buildingFunction->name}");
|
||||
} else {
|
||||
$this->command->warn("Building function not found: {$parameterData['building_function_code']}");
|
||||
}
|
||||
}
|
||||
|
||||
$this->command->info('Building Function Parameters seeding completed!');
|
||||
}
|
||||
}
|
||||
156
database/seeders/BuildingFunctionSeeder.php
Normal file
156
database/seeders/BuildingFunctionSeeder.php
Normal file
@@ -0,0 +1,156 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\BuildingFunction;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class BuildingFunctionSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$now = Carbon::now();
|
||||
|
||||
// 1. Building Functions Data - Fixed structure with proper parent-child relationships
|
||||
$buildingFunctions = [
|
||||
// Parent Categories
|
||||
['id' => 1, 'code' => 'AGAMA', 'name' => 'Bangunan Keagamaan', 'parent_id' => null, 'level' => 0, 'sort_order' => 1, 'base_tariff' => 0.00],
|
||||
['id' => 2, 'code' => 'SOSIAL_BUDAYA', 'name' => 'Bangunan Sosial Budaya', 'parent_id' => null, 'level' => 0, 'sort_order' => 2, 'base_tariff' => 30000.00],
|
||||
['id' => 3, 'code' => 'CAMPURAN', 'name' => 'Bangunan Campuran', 'parent_id' => null, 'level' => 0, 'sort_order' => 3, 'base_tariff' => 70000.00],
|
||||
['id' => 4, 'code' => 'USAHA', 'name' => 'Bangunan Usaha', 'parent_id' => null, 'level' => 0, 'sort_order' => 4, 'base_tariff' => 60000.00],
|
||||
['id' => 5, 'code' => 'HUNIAN', 'name' => 'Bangunan Hunian', 'parent_id' => null, 'level' => 0, 'sort_order' => 5, 'base_tariff' => 40000.00],
|
||||
|
||||
// Sub Categories - Campuran
|
||||
['id' => 6, 'code' => 'CAMPURAN_KECIL', 'name' => 'Bangunan Campuran Kecil', 'parent_id' => 3, 'level' => 1, 'sort_order' => 1, 'base_tariff' => 60000.00],
|
||||
['id' => 7, 'code' => 'CAMPURAN_BESAR', 'name' => 'Bangunan Campuran Besar', 'parent_id' => 3, 'level' => 1, 'sort_order' => 2, 'base_tariff' => 80000.00],
|
||||
|
||||
// Sub Categories - Usaha
|
||||
['id' => 8, 'code' => 'USAHA_KECIL', 'name' => 'Bangunan Usaha Kecil (UMKM)', 'parent_id' => 4, 'level' => 1, 'sort_order' => 1, 'base_tariff' => 50000.00],
|
||||
['id' => 9, 'code' => 'USAHA_BESAR', 'name' => 'Bangunan Usaha Besar', 'parent_id' => 4, 'level' => 1, 'sort_order' => 2, 'base_tariff' => 70000.00],
|
||||
|
||||
// Sub Categories - Hunian
|
||||
['id' => 10, 'code' => 'HUNIAN_SEDERHANA', 'name' => 'Hunian Sederhana', 'parent_id' => 5, 'level' => 1, 'sort_order' => 1, 'base_tariff' => 35000.00],
|
||||
['id' => 11, 'code' => 'HUNIAN_TIDAK_SEDERHANA', 'name' => 'Hunian Tidak Sederhana', 'parent_id' => 5, 'level' => 1, 'sort_order' => 2, 'base_tariff' => 45000.00],
|
||||
['id' => 12, 'code' => 'MBR', 'name' => 'Hunian MBR (Masyarakat Berpenghasilan Rendah)', 'parent_id' => 5, 'level' => 1, 'sort_order' => 3, 'base_tariff' => 0.00],
|
||||
];
|
||||
|
||||
foreach ($buildingFunctions as $function) {
|
||||
DB::table('building_functions')->updateOrInsert(
|
||||
['id' => $function['id']],
|
||||
array_merge($function, [
|
||||
'description' => 'Deskripsi untuk ' . $function['name'],
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
])
|
||||
);
|
||||
}
|
||||
|
||||
// 2. Building Function Parameters
|
||||
$parameters = [
|
||||
// AGAMA - No charge
|
||||
['building_function_id' => 1, 'fungsi_bangunan' => 0, 'ip_permanen' => 0, 'ip_kompleksitas' => 0, 'indeks_lokalitas' => 0, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// SOSIAL_BUDAYA
|
||||
['building_function_id' => 2, 'fungsi_bangunan' => 0.3, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.3, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// CAMPURAN_KECIL
|
||||
['building_function_id' => 6, 'fungsi_bangunan' => 0.6, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.5, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// CAMPURAN_BESAR
|
||||
['building_function_id' => 7, 'fungsi_bangunan' => 0.8, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.5, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// USAHA_KECIL
|
||||
['building_function_id' => 8, 'fungsi_bangunan' => 0.5, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.4, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// USAHA_BESAR
|
||||
['building_function_id' => 9, 'fungsi_bangunan' => 0.7, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.5, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// HUNIAN_SEDERHANA
|
||||
['building_function_id' => 10, 'fungsi_bangunan' => 0.15, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.3, 'indeks_lokalitas' => 0.4, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// HUNIAN_TIDAK_SEDERHANA
|
||||
['building_function_id' => 11, 'fungsi_bangunan' => 0.17, 'ip_permanen' => 0.4, 'ip_kompleksitas' => 0.6, 'indeks_lokalitas' => 0.4, 'asumsi_prasarana' => 0.5],
|
||||
|
||||
// MBR - No charge
|
||||
['building_function_id' => 12, 'fungsi_bangunan' => 0, 'ip_permanen' => 0, 'ip_kompleksitas' => 0, 'indeks_lokalitas' => 0, 'asumsi_prasarana' => 0.5],
|
||||
];
|
||||
|
||||
foreach ($parameters as $param) {
|
||||
DB::table('building_function_parameters')->updateOrInsert(
|
||||
['building_function_id' => $param['building_function_id']],
|
||||
array_merge($param, [
|
||||
'koefisien_dasar' => 1.0,
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
])
|
||||
);
|
||||
}
|
||||
|
||||
// 3. Floor Height Indices (IP Ketinggian per lantai)
|
||||
$floorHeightIndices = [
|
||||
['floor_number' => 1, 'ip_ketinggian' => 1.000000, 'description' => 'IP ketinggian untuk lantai 1'],
|
||||
['floor_number' => 2, 'ip_ketinggian' => 1.090000, 'description' => 'IP ketinggian untuk lantai 2'],
|
||||
['floor_number' => 3, 'ip_ketinggian' => 1.120000, 'description' => 'IP ketinggian untuk lantai 3'],
|
||||
['floor_number' => 4, 'ip_ketinggian' => 1.350000, 'description' => 'IP ketinggian untuk lantai 4'],
|
||||
['floor_number' => 5, 'ip_ketinggian' => 1.620000, 'description' => 'IP ketinggian untuk lantai 5'],
|
||||
['floor_number' => 6, 'ip_ketinggian' => 1.197000, 'description' => 'IP ketinggian untuk lantai 6'],
|
||||
];
|
||||
|
||||
foreach ($floorHeightIndices as $heightIndex) {
|
||||
DB::table('floor_height_indices')->updateOrInsert(
|
||||
['floor_number' => $heightIndex['floor_number']],
|
||||
array_merge($heightIndex, [
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
])
|
||||
);
|
||||
}
|
||||
|
||||
// Formula Templates removed - not used in current system
|
||||
|
||||
// 5. Retribution Formulas per Floor (for child building functions only)
|
||||
$childBuildingFunctionIds = [1, 2, 6, 7, 8, 9, 10, 11, 12]; // All leaf nodes
|
||||
$formulaExpression = '(1 * luas_bangunan * (indeks_lokalitas * 70350 * ((fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian)))) * 1)) + (0.5 * (1 * luas_bangunan * (indeks_lokalitas * 70350 * ((fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian)))) * 1)))';
|
||||
|
||||
foreach ($childBuildingFunctionIds as $buildingFunctionId) {
|
||||
$buildingFunction = DB::table('building_functions')->where('id', $buildingFunctionId)->first();
|
||||
|
||||
if ($buildingFunction) {
|
||||
// Create formulas for floors 1-5
|
||||
for ($floorNumber = 1; $floorNumber <= 5; $floorNumber++) {
|
||||
DB::table('retribution_formulas')->updateOrInsert(
|
||||
[
|
||||
'building_function_id' => $buildingFunctionId,
|
||||
'floor_number' => $floorNumber
|
||||
],
|
||||
[
|
||||
'name' => "{$buildingFunction->name} - Lantai {$floorNumber}",
|
||||
'formula_expression' => $formulaExpression,
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
]
|
||||
);
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
$this->command->info('Building Function data seeded successfully!');
|
||||
$this->command->info('Building Function Parameters seeded successfully!');
|
||||
$this->command->info('Floor Height Indices seeded successfully!');
|
||||
$this->command->info('Retribution Formulas with IP Ketinggian seeded successfully!');
|
||||
|
||||
// Display summary
|
||||
$this->command->info('=== SUMMARY ===');
|
||||
$this->command->info('Parent Functions: ' . DB::table('building_functions')->whereNull('parent_id')->count());
|
||||
$this->command->info('Child Functions: ' . DB::table('building_functions')->whereNotNull('parent_id')->count());
|
||||
$this->command->info('Parameters: ' . DB::table('building_function_parameters')->count());
|
||||
$this->command->info('Floor Height Indices: ' . DB::table('floor_height_indices')->count());
|
||||
$this->command->info('Retribution Formulas: ' . DB::table('retribution_formulas')->count());
|
||||
}
|
||||
}
|
||||
@@ -50,7 +50,11 @@ class DatabaseSeeder extends Seeder
|
||||
MenuSeeder::class,
|
||||
UsersRoleMenuSeeder::class,
|
||||
GlobalSettingSeeder::class,
|
||||
RetributionDataSeeder::class,
|
||||
BuildingFunctionSeeder::class,
|
||||
BuildingFunctionParameterSeeder::class,
|
||||
FloorHeightIndexSeeder::class,
|
||||
RetributionFormulaSeeder::class,
|
||||
RetributionProposalSeeder::class,
|
||||
]);
|
||||
}
|
||||
}
|
||||
|
||||
62
database/seeders/FloorHeightIndexSeeder.php
Normal file
62
database/seeders/FloorHeightIndexSeeder.php
Normal file
@@ -0,0 +1,62 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\FloorHeightIndex;
|
||||
|
||||
class FloorHeightIndexSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$indices = [
|
||||
[
|
||||
'floor_number' => 1,
|
||||
'ip_ketinggian' => 1.000000,
|
||||
'description' => 'IP ketinggian untuk lantai 1'
|
||||
],
|
||||
[
|
||||
'floor_number' => 2,
|
||||
'ip_ketinggian' => 1.090000,
|
||||
'description' => 'IP ketinggian untuk lantai 2'
|
||||
],
|
||||
[
|
||||
'floor_number' => 3,
|
||||
'ip_ketinggian' => 1.120000,
|
||||
'description' => 'IP ketinggian untuk lantai 3'
|
||||
],
|
||||
[
|
||||
'floor_number' => 4,
|
||||
'ip_ketinggian' => 1.350000,
|
||||
'description' => 'IP ketinggian untuk lantai 4'
|
||||
],
|
||||
[
|
||||
'floor_number' => 5,
|
||||
'ip_ketinggian' => 1.620000,
|
||||
'description' => 'IP ketinggian untuk lantai 5'
|
||||
],
|
||||
[
|
||||
'floor_number' => 6,
|
||||
'ip_ketinggian' => 1.197000,
|
||||
'description' => 'IP ketinggian untuk lantai 6'
|
||||
]
|
||||
];
|
||||
|
||||
foreach ($indices as $index) {
|
||||
FloorHeightIndex::updateOrCreate(
|
||||
['floor_number' => $index['floor_number']],
|
||||
$index
|
||||
);
|
||||
}
|
||||
|
||||
$this->command->info('Floor Height Index seeding completed!');
|
||||
$this->command->info('IP Ketinggian values:');
|
||||
foreach ($indices as $index) {
|
||||
$this->command->info("Lantai {$index['floor_number']}: {$index['ip_ketinggian']}");
|
||||
}
|
||||
}
|
||||
}
|
||||
106
database/seeders/MasterFormulaSeeder.php
Normal file
106
database/seeders/MasterFormulaSeeder.php
Normal file
@@ -0,0 +1,106 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\MasterFormula;
|
||||
use App\Models\MasterParameter;
|
||||
use App\Models\FormulaParameter;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class MasterFormulaSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$now = Carbon::now();
|
||||
|
||||
// 1. Master Formulas
|
||||
$formulas = [
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_DASAR',
|
||||
'formula_name' => 'Formula Retribusi Dasar',
|
||||
'formula_expression' => '{koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_bangunan} * ({ip_permanen} + {ip_kompleksitas} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})',
|
||||
'formula_category' => 'basic',
|
||||
'description' => 'Formula dasar untuk perhitungan retribusi PBG tanpa prasarana',
|
||||
'usage_notes' => 'Menggunakan rumus: 1*D5*(N5*tarif_dasar*H5*1) dimana H5=E5*(F5+G5+(0.5*H3))'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_DENGAN_PRASARANA',
|
||||
'formula_name' => 'Formula Retribusi dengan Prasarana',
|
||||
'formula_expression' => '({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_bangunan} * ({ip_permanen} + {ip_kompleksitas} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})) + ({asumsi_prasarana} * ({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_bangunan} * ({ip_permanen} + {ip_kompleksitas} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})))',
|
||||
'formula_category' => 'complete',
|
||||
'description' => 'Formula lengkap retribusi PBG termasuk prasarana',
|
||||
'usage_notes' => 'Formula utama: (dasar) + (prasarana * dasar). Sesuai rumus Excel yang diberikan'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_BEBAS',
|
||||
'formula_name' => 'Formula Retribusi Bebas',
|
||||
'formula_expression' => '0',
|
||||
'formula_category' => 'free',
|
||||
'description' => 'Formula untuk bangunan yang bebas retribusi',
|
||||
'usage_notes' => 'Digunakan untuk bangunan keagamaan dan MBR'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_PER_LANTAI',
|
||||
'formula_name' => 'Formula Perhitungan per Lantai (H5)',
|
||||
'formula_expression' => '{fungsi_bangunan} * ({ip_permanen} + {ip_kompleksitas} + ({multiplier_ketinggian} * {ip_ketinggian}))',
|
||||
'formula_category' => 'component',
|
||||
'description' => 'Formula untuk menghitung nilai per lantai (H5 dalam Excel)',
|
||||
'usage_notes' => 'Rumus: E5*(F5+G5+(0.5*H3)) - komponen perhitungan per lantai'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_HUNIAN_SEDERHANA',
|
||||
'formula_name' => 'Formula Retribusi Hunian Sederhana',
|
||||
'formula_expression' => '({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_hunian_sederhana} * ({ip_permanen_hunian_sederhana} + {ip_kompleksitas_hunian_sederhana} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})) + ({asumsi_prasarana} * ({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_hunian_sederhana} * ({ip_permanen_hunian_sederhana} + {ip_kompleksitas_hunian_sederhana} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})))',
|
||||
'formula_category' => 'residential',
|
||||
'description' => 'Formula khusus untuk hunian sederhana dengan IP kompleksitas 0.3',
|
||||
'usage_notes' => 'Menggunakan parameter khusus hunian sederhana'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_HUNIAN_TIDAK_SEDERHANA',
|
||||
'formula_name' => 'Formula Retribusi Hunian Tidak Sederhana',
|
||||
'formula_expression' => '({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_hunian_tidak_sederhana} * ({ip_permanen_hunian_tidak_sederhana} + {ip_kompleksitas_hunian_tidak_sederhana} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})) + ({asumsi_prasarana} * ({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_hunian_tidak_sederhana} * ({ip_permanen_hunian_tidak_sederhana} + {ip_kompleksitas_hunian_tidak_sederhana} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})))',
|
||||
'formula_category' => 'residential',
|
||||
'description' => 'Formula khusus untuk hunian tidak sederhana',
|
||||
'usage_notes' => 'Menggunakan parameter khusus hunian tidak sederhana'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_SOSIAL_BUDAYA',
|
||||
'formula_name' => 'Formula Retribusi Sosial Budaya',
|
||||
'formula_expression' => '({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_sosial_budaya} * ({ip_permanen_sosial_budaya} + {ip_kompleksitas_sosial_budaya} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})) + ({asumsi_prasarana} * ({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_sosial_budaya} * ({ip_permanen_sosial_budaya} + {ip_kompleksitas_sosial_budaya} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})))',
|
||||
'formula_category' => 'social',
|
||||
'description' => 'Formula khusus untuk bangunan sosial budaya',
|
||||
'usage_notes' => 'Menggunakan parameter fungsi sosial budaya'
|
||||
],
|
||||
[
|
||||
'formula_code' => 'RETRIBUSI_USAHA',
|
||||
'formula_name' => 'Formula Retribusi Usaha',
|
||||
'formula_expression' => '({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_usaha_besar} * ({ip_permanen_usaha_besar} + {ip_kompleksitas_usaha_besar} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})) + ({asumsi_prasarana} * ({koefisien_dasar} * {luas_bangunan} * ({indeks_lokalitas} * {tarif_dasar} * ({fungsi_usaha_besar} * ({ip_permanen_usaha_besar} + {ip_kompleksitas_usaha_besar} + ({multiplier_ketinggian} * {ip_ketinggian}))) * {koefisien_dasar})))',
|
||||
'formula_category' => 'commercial',
|
||||
'description' => 'Formula khusus untuk bangunan usaha',
|
||||
'usage_notes' => 'Menggunakan parameter fungsi usaha besar'
|
||||
]
|
||||
];
|
||||
|
||||
foreach ($formulas as $formula) {
|
||||
DB::table('master_formulas')->updateOrInsert(
|
||||
['formula_code' => $formula['formula_code']],
|
||||
array_merge($formula, [
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
])
|
||||
);
|
||||
}
|
||||
|
||||
$this->command->info('Master Formulas seeded successfully!');
|
||||
$this->command->info('=== FORMULA SUMMARY ===');
|
||||
foreach ($formulas as $formula) {
|
||||
$this->command->info($formula['formula_code'] . ': ' . $formula['formula_name']);
|
||||
}
|
||||
}
|
||||
}
|
||||
387
database/seeders/MasterParameterSeeder.php
Normal file
387
database/seeders/MasterParameterSeeder.php
Normal file
@@ -0,0 +1,387 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\MasterParameter;
|
||||
use App\Models\BuildingFunction;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class MasterParameterSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
$now = Carbon::now();
|
||||
|
||||
// 1. General Parameters (Parameter Umum)
|
||||
$generalParameters = [
|
||||
[
|
||||
'parameter_code' => 'luas_bangunan',
|
||||
'parameter_name' => 'Luas Bangunan',
|
||||
'default_value' => 0.000000,
|
||||
'unit' => 'm²',
|
||||
'description' => 'Luas total bangunan dalam meter persegi'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'tarif_dasar',
|
||||
'parameter_name' => 'Tarif Dasar',
|
||||
'default_value' => 7035000.000000,
|
||||
'unit' => 'Rupiah',
|
||||
'description' => 'Tarif dasar retribusi per meter persegi (7.035.000)'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'koefisien_dasar',
|
||||
'parameter_name' => 'Koefisien Dasar',
|
||||
'default_value' => 1.000000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Koefisien dasar perhitungan'
|
||||
]
|
||||
];
|
||||
|
||||
// 2. IP Ketinggian Parameters per Lantai (Data dari tabel)
|
||||
$floorHeightParameters = [
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_1',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 1',
|
||||
'default_value' => 1.000000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 1'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_2',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 2',
|
||||
'default_value' => 1.090000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 2'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_3',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 3',
|
||||
'default_value' => 1.120000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 3'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_4',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 4',
|
||||
'default_value' => 1.135000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 4'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_5',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 5',
|
||||
'default_value' => 1.162000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 5'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_ketinggian_6',
|
||||
'parameter_name' => 'IP Ketinggian Lantai 6',
|
||||
'default_value' => 1.197000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks ketinggian untuk lantai 6'
|
||||
]
|
||||
];
|
||||
|
||||
// 3. Building Function Specific Parameters (Data dari tabel)
|
||||
$buildingFunctionParameters = [
|
||||
// FUNGSI KEAGAMAAN - Bebas retribusi
|
||||
[
|
||||
'parameter_code' => 'fungsi_keagamaan',
|
||||
'parameter_name' => 'Fungsi Bangunan Keagamaan',
|
||||
'default_value' => 0.000000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan keagamaan (bebas retribusi)'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_keagamaan',
|
||||
'parameter_name' => 'IP Permanen Keagamaan',
|
||||
'default_value' => 0.000000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan keagamaan'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_keagamaan',
|
||||
'parameter_name' => 'IP Kompleksitas Keagamaan',
|
||||
'default_value' => 0.000000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan keagamaan'
|
||||
],
|
||||
|
||||
// FUNGSI SOSIAL BUDAYA - 0.3
|
||||
[
|
||||
'parameter_code' => 'fungsi_sosial_budaya',
|
||||
'parameter_name' => 'Fungsi Bangunan Sosial Budaya',
|
||||
'default_value' => 0.300000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan sosial budaya'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_sosial_budaya',
|
||||
'parameter_name' => 'IP Permanen Sosial Budaya',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan sosial budaya'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_sosial_budaya',
|
||||
'parameter_name' => 'IP Kompleksitas Sosial Budaya',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan sosial budaya'
|
||||
],
|
||||
|
||||
// CAMPURAN KECIL - 0.6
|
||||
[
|
||||
'parameter_code' => 'fungsi_campuran_kecil',
|
||||
'parameter_name' => 'Fungsi Bangunan Campuran Kecil',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan campuran kecil'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_campuran_kecil',
|
||||
'parameter_name' => 'IP Permanen Campuran Kecil',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan campuran kecil'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_campuran_kecil',
|
||||
'parameter_name' => 'IP Kompleksitas Campuran Kecil',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan campuran kecil'
|
||||
],
|
||||
|
||||
// CAMPURAN BESAR - 0.8
|
||||
[
|
||||
'parameter_code' => 'fungsi_campuran_besar',
|
||||
'parameter_name' => 'Fungsi Bangunan Campuran Besar',
|
||||
'default_value' => 0.800000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan campuran besar'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_campuran_besar',
|
||||
'parameter_name' => 'IP Permanen Campuran Besar',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan campuran besar'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_campuran_besar',
|
||||
'parameter_name' => 'IP Kompleksitas Campuran Besar',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan campuran besar'
|
||||
],
|
||||
|
||||
// UMKM - 0.5
|
||||
[
|
||||
'parameter_code' => 'fungsi_umkm',
|
||||
'parameter_name' => 'Fungsi Bangunan UMKM',
|
||||
'default_value' => 0.500000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan UMKM'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_umkm',
|
||||
'parameter_name' => 'IP Permanen UMKM',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan UMKM'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_umkm',
|
||||
'parameter_name' => 'IP Kompleksitas UMKM',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan UMKM'
|
||||
],
|
||||
|
||||
// USAHA BESAR - 0.7
|
||||
[
|
||||
'parameter_code' => 'fungsi_usaha_besar',
|
||||
'parameter_name' => 'Fungsi Bangunan Usaha Besar',
|
||||
'default_value' => 0.700000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi bangunan usaha besar'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_usaha_besar',
|
||||
'parameter_name' => 'IP Permanen Usaha Besar',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk bangunan usaha besar'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_usaha_besar',
|
||||
'parameter_name' => 'IP Kompleksitas Usaha Besar',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk bangunan usaha besar'
|
||||
],
|
||||
|
||||
// HUNIAN SEDERHANA - 0.15
|
||||
[
|
||||
'parameter_code' => 'fungsi_hunian_sederhana',
|
||||
'parameter_name' => 'Fungsi Hunian Sederhana',
|
||||
'default_value' => 0.150000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi hunian sederhana'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_hunian_sederhana',
|
||||
'parameter_name' => 'IP Permanen Hunian Sederhana',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk hunian sederhana'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_hunian_sederhana',
|
||||
'parameter_name' => 'IP Kompleksitas Hunian Sederhana',
|
||||
'default_value' => 0.300000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk hunian sederhana (0.3)'
|
||||
],
|
||||
|
||||
// HUNIAN TIDAK SEDERHANA - 0.17
|
||||
[
|
||||
'parameter_code' => 'fungsi_hunian_tidak_sederhana',
|
||||
'parameter_name' => 'Fungsi Hunian Tidak Sederhana',
|
||||
'default_value' => 0.170000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Parameter fungsi hunian tidak sederhana'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_permanen_hunian_tidak_sederhana',
|
||||
'parameter_name' => 'IP Permanen Hunian Tidak Sederhana',
|
||||
'default_value' => 0.400000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Permanen untuk hunian tidak sederhana'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'ip_kompleksitas_hunian_tidak_sederhana',
|
||||
'parameter_name' => 'IP Kompleksitas Hunian Tidak Sederhana',
|
||||
'default_value' => 0.600000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'IP Kompleksitas untuk hunian tidak sederhana'
|
||||
]
|
||||
];
|
||||
|
||||
// 4. Indeks Lokalitas Parameters
|
||||
$localityParameters = [
|
||||
[
|
||||
'parameter_code' => 'indeks_lokalitas_03',
|
||||
'parameter_name' => 'Indeks Lokalitas 0.3%',
|
||||
'default_value' => 0.003000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks lokalitas 0.3% (untuk bangunan tertentu)'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'indeks_lokalitas_04',
|
||||
'parameter_name' => 'Indeks Lokalitas 0.4%',
|
||||
'default_value' => 0.004000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks lokalitas 0.4% (untuk hunian sederhana dan tidak sederhana)'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'indeks_lokalitas_05',
|
||||
'parameter_name' => 'Indeks Lokalitas 0.5%',
|
||||
'default_value' => 0.005000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Indeks lokalitas 0.5% (untuk bangunan komersial)'
|
||||
]
|
||||
];
|
||||
|
||||
// 5. Multiplier Parameters
|
||||
$multiplierParameters = [
|
||||
[
|
||||
'parameter_code' => 'multiplier_prasarana',
|
||||
'parameter_name' => 'Multiplier Prasarana',
|
||||
'default_value' => 0.500000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Multiplier untuk perhitungan prasarana (50%)'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'multiplier_ketinggian',
|
||||
'parameter_name' => 'Multiplier Ketinggian',
|
||||
'default_value' => 0.500000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Multiplier untuk indeks ketinggian dalam formula (0.5)'
|
||||
]
|
||||
];
|
||||
|
||||
// 4. Additional Parameters (Parameter Tambahan)
|
||||
$additionalParameters = [
|
||||
[
|
||||
'parameter_code' => 'asumsi_prasarana',
|
||||
'parameter_name' => 'Asumsi Prasarana',
|
||||
'default_value' => 0.500000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Persentase asumsi prasarana dalam perhitungan retribusi'
|
||||
],
|
||||
[
|
||||
'parameter_code' => 'multiplier_ketinggian',
|
||||
'parameter_name' => 'Multiplier Ketinggian',
|
||||
'default_value' => 0.500000,
|
||||
'unit' => 'decimal',
|
||||
'description' => 'Multiplier untuk perhitungan ketinggian (0.5 dalam rumus Excel)'
|
||||
]
|
||||
];
|
||||
|
||||
// Combine all parameters
|
||||
$allParameters = array_merge(
|
||||
$generalParameters,
|
||||
$floorHeightParameters,
|
||||
$buildingFunctionParameters,
|
||||
$localityParameters,
|
||||
$multiplierParameters,
|
||||
$additionalParameters
|
||||
);
|
||||
|
||||
// Insert parameters
|
||||
foreach ($allParameters as $param) {
|
||||
DB::table('master_parameters')->updateOrInsert(
|
||||
['parameter_code' => $param['parameter_code']],
|
||||
array_merge($param, [
|
||||
'created_at' => $now,
|
||||
'updated_at' => $now
|
||||
])
|
||||
);
|
||||
}
|
||||
|
||||
// Summary output
|
||||
$this->command->info('Master Parameters seeded successfully!');
|
||||
$this->command->info('=== SUMMARY ===');
|
||||
$this->command->info('General Parameters: ' . count($generalParameters));
|
||||
$this->command->info('Floor Height Parameters: ' . count($floorHeightParameters));
|
||||
$this->command->info('Building Function Parameters: ' . count($buildingFunctionParameters));
|
||||
$this->command->info('Locality Parameters: ' . count($localityParameters));
|
||||
$this->command->info('Multiplier Parameters: ' . count($multiplierParameters));
|
||||
$this->command->info('Additional Parameters: ' . count($additionalParameters));
|
||||
$this->command->info('Total Parameters: ' . count($allParameters));
|
||||
|
||||
// Display parameter mapping
|
||||
$this->command->info('=== BUILDING FUNCTION PARAMETER MAPPING ===');
|
||||
$this->command->info('KEAGAMAAN: Fungsi=0.000, IP_Permanen=0.000, IP_Kompleksitas=0.000');
|
||||
$this->command->info('SOSIAL_BUDAYA: Fungsi=0.300, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
$this->command->info('CAMPURAN_KECIL: Fungsi=0.600, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
$this->command->info('CAMPURAN_BESAR: Fungsi=0.800, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
$this->command->info('UMKM: Fungsi=0.500, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
$this->command->info('USAHA_BESAR: Fungsi=0.700, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
$this->command->info('HUNIAN_SEDERHANA: Fungsi=0.150, IP_Permanen=0.400, IP_Kompleksitas=0.300');
|
||||
$this->command->info('HUNIAN_TIDAK_SEDERHANA: Fungsi=0.170, IP_Permanen=0.400, IP_Kompleksitas=0.600');
|
||||
|
||||
$this->command->info('=== IP KETINGGIAN PER LANTAI ===');
|
||||
$this->command->info('Lantai 1: 1.000, Lantai 2: 1.090, Lantai 3: 1.120');
|
||||
$this->command->info('Lantai 4: 1.135, Lantai 5: 1.162, Lantai 6: 1.197');
|
||||
}
|
||||
}
|
||||
@@ -1,71 +0,0 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use Illuminate\Support\Facades\DB;
|
||||
|
||||
class RetributionDataSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
// Seed Building Types berdasarkan Excel (without coefficient)
|
||||
DB::table('building_types')->insert([
|
||||
// Parent Functions
|
||||
['id' => 1, 'code' => 'KEAGAMAAN', 'name' => 'Fungsi Keagamaan', 'parent_id' => null, 'level' => 1, 'is_free' => true, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 2, 'code' => 'SOSBUDAYA', 'name' => 'Fungsi Sosial Budaya', 'parent_id' => null, 'level' => 1, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 3, 'code' => 'CAMPURAN', 'name' => 'Fungsi Campuran (lebih dari 1)', 'parent_id' => null, 'level' => 1, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 4, 'code' => 'USAHA', 'name' => 'Fungsi Usaha', 'parent_id' => null, 'level' => 1, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 5, 'code' => 'HUNIAN', 'name' => 'Fungsi Hunian', 'parent_id' => null, 'level' => 1, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
|
||||
// Child Functions
|
||||
['id' => 6, 'code' => 'CAMP_KECIL', 'name' => 'Campuran Kecil', 'parent_id' => 3, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 7, 'code' => 'CAMP_BESAR', 'name' => 'Campuran Besar', 'parent_id' => 3, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 8, 'code' => 'UMKM', 'name' => 'Fungsi Usaha (UMKM)', 'parent_id' => 4, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 9, 'code' => 'USH_BESAR', 'name' => 'Usaha Besar (Non-Mikro)', 'parent_id' => 4, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 10, 'code' => 'HUN_SEDH', 'name' => 'Hunian Sederhana <100', 'parent_id' => 5, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 11, 'code' => 'HUN_TSEDH', 'name' => 'Hunian Tidak Sederhana >100', 'parent_id' => 5, 'level' => 2, 'is_free' => false, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['id' => 12, 'code' => 'MBR', 'name' => 'Rumah Tinggal MBR', 'parent_id' => 5, 'level' => 2, 'is_free' => true, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
]);
|
||||
|
||||
// Seed Retribution Indices berdasarkan Excel (with coefficient moved here)
|
||||
DB::table('retribution_indices')->insert([
|
||||
['building_type_id' => 1, 'coefficient' => 0.0000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.0000, 'locality_index' => 0.0000, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Keagamaan
|
||||
['building_type_id' => 2, 'coefficient' => 0.3000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0030, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Sosial Budaya
|
||||
['building_type_id' => 6, 'coefficient' => 0.6000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0050, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Campuran Kecil
|
||||
['building_type_id' => 7, 'coefficient' => 0.8000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0050, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Campuran Besar
|
||||
['building_type_id' => 8, 'coefficient' => 0.5000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0040, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // UMKM
|
||||
['building_type_id' => 9, 'coefficient' => 0.7000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0050, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Usaha Besar
|
||||
['building_type_id' => 10, 'coefficient' => 0.1500, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.3000, 'locality_index' => 0.0040, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Hunian Sederhana
|
||||
['building_type_id' => 11, 'coefficient' => 0.1700, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.6000, 'locality_index' => 0.0040, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // Hunian Tidak Sederhana
|
||||
['building_type_id' => 12, 'coefficient' => 0.0000, 'ip_permanent' => 0.4000, 'ip_complexity' => 0.0000, 'locality_index' => 0.0000, 'infrastructure_factor' => 0.5000, 'is_active' => true, 'created_at' => now(), 'updated_at' => now()], // MBR
|
||||
]);
|
||||
|
||||
// Seed Height Indices berdasarkan Excel
|
||||
DB::table('height_indices')->insert([
|
||||
['floor_number' => 1, 'height_index' => 1.0000, 'created_at' => now(), 'updated_at' => now()],
|
||||
['floor_number' => 2, 'height_index' => 1.0900, 'created_at' => now(), 'updated_at' => now()],
|
||||
['floor_number' => 3, 'height_index' => 1.1200, 'created_at' => now(), 'updated_at' => now()],
|
||||
['floor_number' => 4, 'height_index' => 1.1350, 'created_at' => now(), 'updated_at' => now()],
|
||||
['floor_number' => 5, 'height_index' => 1.1620, 'created_at' => now(), 'updated_at' => now()],
|
||||
['floor_number' => 6, 'height_index' => 1.1970, 'created_at' => now(), 'updated_at' => now()],
|
||||
]);
|
||||
|
||||
// Seed Retribution Configs
|
||||
DB::table('retribution_configs')->insert([
|
||||
['key' => 'BASE_VALUE', 'value' => 7035000.00, 'description' => 'Nilai dasar perhitungan retribusi', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['key' => 'INFRASTRUCTURE_MULTIPLIER', 'value' => 0.50, 'description' => 'Pengali asumsi prasarana (50%)', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
['key' => 'HEIGHT_MULTIPLIER', 'value' => 0.50, 'description' => 'Pengali indeks ketinggian dalam formula', 'is_active' => true, 'created_at' => now(), 'updated_at' => now()],
|
||||
]);
|
||||
|
||||
$this->command->info('✅ Retribution data seeded successfully!');
|
||||
$this->command->info('📊 Building Types: 12 records');
|
||||
$this->command->info('📊 Retribution Indices: 9 records');
|
||||
$this->command->info('📊 Height Indices: 6 records');
|
||||
$this->command->info('📊 Retribution Configs: 3 records');
|
||||
}
|
||||
}
|
||||
74
database/seeders/RetributionFormulaSeeder.php
Normal file
74
database/seeders/RetributionFormulaSeeder.php
Normal file
@@ -0,0 +1,74 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\RetributionFormula;
|
||||
|
||||
class RetributionFormulaSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
// Get building function codes from the actual seeded data (child functions only)
|
||||
$buildingFunctionCodes = [
|
||||
'AGAMA',
|
||||
'SOSIAL_BUDAYA',
|
||||
'CAMPURAN_KECIL',
|
||||
'CAMPURAN_BESAR',
|
||||
'USAHA_KECIL',
|
||||
'USAHA_BESAR',
|
||||
'HUNIAN_SEDERHANA',
|
||||
'HUNIAN_TIDAK_SEDERHANA',
|
||||
'MBR'
|
||||
];
|
||||
|
||||
// Formula lengkap retribusi dengan perhitungan per lantai
|
||||
// Bagian 1: Perhitungan per lantai
|
||||
$floorCalculationFormula = "(fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian)))";
|
||||
|
||||
// Bagian 2: Formula retribusi lengkap
|
||||
$mainFormula = "(1 * luas_bangunan * (indeks_lokalitas * 70350 * ({$floorCalculationFormula}) * 1))";
|
||||
$additionalFormula = "(0.5 * (1 * luas_bangunan * (indeks_lokalitas * 70350 * ({$floorCalculationFormula}) * 1)))";
|
||||
$fullFormulaExpression = "{$mainFormula} + {$additionalFormula}";
|
||||
|
||||
// Buat formula untuk 5 lantai pertama saja
|
||||
for ($floorNumber = 1; $floorNumber <= 5; $floorNumber++) {
|
||||
foreach ($buildingFunctionCodes as $code) {
|
||||
$buildingFunction = BuildingFunction::where('code', $code)->first();
|
||||
|
||||
if ($buildingFunction) {
|
||||
$formulaName = "{$buildingFunction->name} - Lantai {$floorNumber}";
|
||||
|
||||
RetributionFormula::updateOrCreate(
|
||||
[
|
||||
'building_function_id' => $buildingFunction->id,
|
||||
'floor_number' => $floorNumber
|
||||
],
|
||||
[
|
||||
'name' => $formulaName,
|
||||
'formula_expression' => $fullFormulaExpression,
|
||||
'description' => "Formula retribusi untuk {$buildingFunction->name} dengan {$floorNumber} lantai. Formula: {$fullFormulaExpression}",
|
||||
'floor_number' => $floorNumber,
|
||||
'luas_bangunan_rate' => $buildingFunction->base_tariff ?? 50000,
|
||||
'is_active' => true
|
||||
]
|
||||
);
|
||||
|
||||
$this->command->info("Created formula for: {$formulaName}");
|
||||
} else {
|
||||
$this->command->warn("Building function not found: {$code}");
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
$this->command->info('Retribution Formulas seeding completed for 5 floors!');
|
||||
$this->command->info("Building functions processed: " . implode(', ', $buildingFunctionCodes));
|
||||
$this->command->info("Floor calculation formula: {$floorCalculationFormula}");
|
||||
$this->command->info("Full retribution formula: {$fullFormulaExpression}");
|
||||
}
|
||||
}
|
||||
159
database/seeders/RetributionProposalSeeder.php
Normal file
159
database/seeders/RetributionProposalSeeder.php
Normal file
@@ -0,0 +1,159 @@
|
||||
<?php
|
||||
|
||||
namespace Database\Seeders;
|
||||
|
||||
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
|
||||
use Illuminate\Database\Seeder;
|
||||
use App\Models\RetributionProposal;
|
||||
use App\Models\SpatialPlanning;
|
||||
use App\Models\BuildingFunction;
|
||||
use App\Models\RetributionFormula;
|
||||
use App\Models\FloorHeightIndex;
|
||||
use App\Models\BuildingFunctionParameter;
|
||||
use Carbon\Carbon;
|
||||
|
||||
class RetributionProposalSeeder extends Seeder
|
||||
{
|
||||
/**
|
||||
* Run the database seeds.
|
||||
*/
|
||||
public function run(): void
|
||||
{
|
||||
// Get some sample data
|
||||
$spatialPlannings = SpatialPlanning::take(5)->get();
|
||||
$buildingFunctions = BuildingFunction::whereNotNull('parent_id')->get(); // Only child functions
|
||||
|
||||
if ($spatialPlannings->isEmpty() || $buildingFunctions->isEmpty()) {
|
||||
$this->command->warn('No spatial plannings or building functions found. Please seed them first.');
|
||||
return;
|
||||
}
|
||||
|
||||
$sampleProposals = [
|
||||
[
|
||||
'spatial_planning_id' => $spatialPlannings->first()?->id,
|
||||
'building_function_code' => 'HUNIAN_SEDERHANA',
|
||||
'floor_number' => 2,
|
||||
'floor_area' => 45666,
|
||||
'total_building_area' => 91332, // 2 floors
|
||||
'notes' => 'Sample calculation for Hunian Sederhana'
|
||||
],
|
||||
[
|
||||
'spatial_planning_id' => $spatialPlannings->skip(1)->first()?->id,
|
||||
'building_function_code' => 'USAHA_KECIL',
|
||||
'floor_number' => 1,
|
||||
'floor_area' => 150,
|
||||
'total_building_area' => 150,
|
||||
'notes' => 'Sample calculation for UMKM'
|
||||
],
|
||||
[
|
||||
'spatial_planning_id' => null, // Testing nullable spatial_planning_id
|
||||
'building_function_code' => 'CAMPURAN_KECIL',
|
||||
'floor_number' => 3,
|
||||
'floor_area' => 200,
|
||||
'total_building_area' => 600,
|
||||
'notes' => 'Sample calculation without spatial planning link'
|
||||
]
|
||||
];
|
||||
|
||||
foreach ($sampleProposals as $proposalData) {
|
||||
$buildingFunction = BuildingFunction::where('code', $proposalData['building_function_code'])->first();
|
||||
|
||||
if (!$buildingFunction) {
|
||||
$this->command->warn("Building function not found: {$proposalData['building_function_code']}");
|
||||
continue;
|
||||
}
|
||||
|
||||
// Get parameters
|
||||
$parameters = BuildingFunctionParameter::where('building_function_id', $buildingFunction->id)->first();
|
||||
$floorHeightIndex = FloorHeightIndex::where('floor_number', $proposalData['floor_number'])->first();
|
||||
$retributionFormula = RetributionFormula::where('building_function_id', $buildingFunction->id)
|
||||
->where('floor_number', $proposalData['floor_number'])
|
||||
->first();
|
||||
|
||||
if (!$parameters || !$floorHeightIndex || !$retributionFormula) {
|
||||
$this->command->warn("Missing data for building function: {$buildingFunction->name}");
|
||||
continue;
|
||||
}
|
||||
|
||||
// Calculate retribution using the Excel formula
|
||||
$floorArea = $proposalData['floor_area'];
|
||||
$fungsi_bangunan = $parameters->fungsi_bangunan;
|
||||
$ip_permanen = $parameters->ip_permanen;
|
||||
$ip_kompleksitas = $parameters->ip_kompleksitas;
|
||||
$ip_ketinggian = $floorHeightIndex->ip_ketinggian;
|
||||
$indeks_lokalitas = $parameters->indeks_lokalitas;
|
||||
$base_value = 70350;
|
||||
$additional_factor = 0.5;
|
||||
|
||||
// Step 1: Calculate H13 (floor coefficient)
|
||||
$h13 = $fungsi_bangunan * ($ip_permanen + $ip_kompleksitas + (0.5 * $ip_ketinggian));
|
||||
|
||||
// Step 2: Main calculation
|
||||
$main_calculation = 1 * $floorArea * ($indeks_lokalitas * $base_value * $h13 * 1);
|
||||
|
||||
// Step 3: Additional (50%)
|
||||
$additional_calculation = $additional_factor * $main_calculation;
|
||||
|
||||
// Step 4: Total
|
||||
$total_retribution = $main_calculation + $additional_calculation;
|
||||
|
||||
// Prepare calculation parameters and breakdown
|
||||
$calculationParameters = [
|
||||
'fungsi_bangunan' => $fungsi_bangunan,
|
||||
'ip_permanen' => $ip_permanen,
|
||||
'ip_kompleksitas' => $ip_kompleksitas,
|
||||
'ip_ketinggian' => $ip_ketinggian,
|
||||
'indeks_lokalitas' => $indeks_lokalitas,
|
||||
'base_value' => $base_value,
|
||||
'additional_factor' => $additional_factor,
|
||||
'floor_area' => $floorArea
|
||||
];
|
||||
|
||||
$calculationBreakdown = [
|
||||
'h13_calculation' => [
|
||||
'formula' => 'fungsi_bangunan * (ip_permanen + ip_kompleksitas + (0.5 * ip_ketinggian))',
|
||||
'calculation' => "{$fungsi_bangunan} * ({$ip_permanen} + {$ip_kompleksitas} + (0.5 * {$ip_ketinggian}))",
|
||||
'result' => $h13
|
||||
],
|
||||
'main_calculation' => [
|
||||
'formula' => '1 * floor_area * (indeks_lokalitas * base_value * h13 * 1)',
|
||||
'calculation' => "1 * {$floorArea} * ({$indeks_lokalitas} * {$base_value} * {$h13} * 1)",
|
||||
'result' => $main_calculation
|
||||
],
|
||||
'additional_calculation' => [
|
||||
'formula' => 'additional_factor * main_calculation',
|
||||
'calculation' => "{$additional_factor} * {$main_calculation}",
|
||||
'result' => $additional_calculation
|
||||
],
|
||||
'total_calculation' => [
|
||||
'formula' => 'main_calculation + additional_calculation',
|
||||
'calculation' => "{$main_calculation} + {$additional_calculation}",
|
||||
'result' => $total_retribution
|
||||
]
|
||||
];
|
||||
|
||||
// Create the proposal
|
||||
RetributionProposal::create([
|
||||
'spatial_planning_id' => $proposalData['spatial_planning_id'],
|
||||
'building_function_id' => $buildingFunction->id,
|
||||
'retribution_formula_id' => $retributionFormula->id,
|
||||
'floor_number' => $proposalData['floor_number'],
|
||||
'floor_area' => $proposalData['floor_area'],
|
||||
'total_building_area' => $proposalData['total_building_area'],
|
||||
'ip_ketinggian' => $ip_ketinggian,
|
||||
'floor_retribution_amount' => $total_retribution,
|
||||
'total_retribution_amount' => $total_retribution, // For single floor, same as floor amount
|
||||
'calculation_parameters' => $calculationParameters,
|
||||
'calculation_breakdown' => $calculationBreakdown,
|
||||
'notes' => $proposalData['notes'],
|
||||
'calculated_at' => Carbon::now()
|
||||
]);
|
||||
|
||||
$this->command->info("Created retribution proposal for: {$buildingFunction->name} - Floor {$proposalData['floor_number']}");
|
||||
$this->command->info(" Amount: Rp " . number_format($total_retribution, 0, ',', '.'));
|
||||
}
|
||||
|
||||
$this->command->info('Retribution Proposal seeding completed!');
|
||||
$this->command->info('Total proposals created: ' . RetributionProposal::count());
|
||||
}
|
||||
}
|
||||
@@ -28,7 +28,7 @@ use App\Http\Controllers\Api\UmkmController;
|
||||
use App\Http\Controllers\Api\TourismController;
|
||||
use App\Http\Controllers\Api\SpatialPlanningController;
|
||||
use App\Http\Controllers\Api\ChatbotController;
|
||||
|
||||
use App\Http\Controllers\RetributionProposalController;
|
||||
use Illuminate\Support\Facades\Route;
|
||||
|
||||
Route::post('/login', [UsersController::class, 'login'])->name('api.user.login');
|
||||
@@ -194,5 +194,16 @@ Route::group(['middleware' => 'auth:sanctum'], function (){
|
||||
Route::get('/growth','index')->name('api.growth');
|
||||
});
|
||||
|
||||
// TODO: Implement new retribution calculation API endpoints using the new schema
|
||||
// Retribution Proposal API
|
||||
Route::controller(RetributionProposalController::class)->group(function(){
|
||||
Route::get('/retribution-proposals', 'index')->name('api.retribution-proposals.index');
|
||||
Route::post('/retribution-proposals', 'store')->name('api.retribution-proposals.store');
|
||||
Route::get('/retribution-proposals/statistics', 'statistics')->name('api.retribution-proposals.statistics');
|
||||
Route::get('/retribution-proposals/total-sum', 'totalSum')->name('api.retribution-proposals.total-sum');
|
||||
Route::get('/retribution-proposals/building-functions', 'buildingFunctions')->name('api.retribution-proposals.building-functions');
|
||||
Route::post('/retribution-proposals/auto-create', 'autoCreateProposal')->name('api.retribution-proposals.auto-create');
|
||||
Route::get('/retribution-proposals/{id}', 'show')->name('api.retribution-proposals.show');
|
||||
Route::put('/retribution-proposals/{id}', 'update')->name('api.retribution-proposals.update');
|
||||
Route::delete('/retribution-proposals/{id}', 'destroy')->name('api.retribution-proposals.destroy');
|
||||
});
|
||||
});
|
||||
Reference in New Issue
Block a user