787 lines
24 KiB
PHP
787 lines
24 KiB
PHP
<?php
|
||
|
||
namespace Database\Seeders;
|
||
|
||
use App\Models\Report;
|
||
use Illuminate\Database\Seeder;
|
||
|
||
class ReportsSeeder extends Seeder
|
||
{
|
||
/**
|
||
* Run the database seeds.
|
||
*/
|
||
public function run(): void
|
||
{
|
||
// Clear existing reports (cascade will delete all related records)
|
||
Report::truncate();
|
||
|
||
$this->seedActiveContractsReport();
|
||
$this->seedFieldJobsCompletedReport();
|
||
$this->seedDecisionsCountReport();
|
||
$this->seedSegmentActivityCountsReport();
|
||
$this->seedActionsDecisionsCountReport();
|
||
$this->seedActivitiesPerPeriodReport();
|
||
}
|
||
|
||
protected function seedActiveContractsReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'active-contracts',
|
||
'name' => 'Aktivne pogodbe',
|
||
'description' => 'Pogodbe, ki so aktivne na izbrani dan, z možnostjo filtriranja po stranki.',
|
||
'category' => 'contracts',
|
||
'enabled' => true,
|
||
'order' => 1,
|
||
]);
|
||
|
||
// Entities (joins)
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\Contract',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
]);
|
||
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\ClientCase',
|
||
'join_type' => 'join',
|
||
'join_first' => 'contracts.client_case_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'client_cases.id',
|
||
'order' => 1,
|
||
]);
|
||
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\Client',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'client_cases.client_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'clients.id',
|
||
'order' => 2,
|
||
]);
|
||
|
||
$report->entities()->createMany([
|
||
[
|
||
'model_class' => 'App\\Models\\Person\\Person',
|
||
'alias' => 'client_people',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'clients.person_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'client_people.id',
|
||
'order' => 3,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Person\\Person',
|
||
'alias' => 'subject_people',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'client_cases.person_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'subject_people.id',
|
||
'order' => 4,
|
||
],
|
||
]);
|
||
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\Account',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'contracts.id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'accounts.contract_id',
|
||
'order' => 5,
|
||
]);
|
||
|
||
// Columns
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'contract_reference',
|
||
'label' => 'Pogodba',
|
||
'type' => 'string',
|
||
'expression' => 'contracts.reference',
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'client_name',
|
||
'label' => 'Stranka',
|
||
'type' => 'string',
|
||
'expression' => 'client_people.full_name',
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'key' => 'person_name',
|
||
'label' => 'Zadeva (oseba)',
|
||
'type' => 'string',
|
||
'expression' => 'subject_people.full_name',
|
||
'order' => 2,
|
||
],
|
||
[
|
||
'key' => 'start_date',
|
||
'label' => 'Začetek',
|
||
'type' => 'date',
|
||
'expression' => 'contracts.start_date',
|
||
'order' => 3,
|
||
],
|
||
[
|
||
'key' => 'end_date',
|
||
'label' => 'Konec',
|
||
'type' => 'date',
|
||
'expression' => 'contracts.end_date',
|
||
'order' => 4,
|
||
],
|
||
[
|
||
'key' => 'balance_amount',
|
||
'label' => 'Saldo',
|
||
'type' => 'currency',
|
||
'expression' => 'CAST(accounts.balance_amount AS FLOAT)',
|
||
'order' => 5,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->create([
|
||
'key' => 'client_uuid',
|
||
'label' => 'Stranka',
|
||
'type' => 'select:client',
|
||
'nullable' => true,
|
||
'order' => 0,
|
||
]);
|
||
|
||
// Conditions - Active as of today
|
||
$asOf = 'CURRENT_DATE';
|
||
|
||
// start_date <= as_of (or null)
|
||
$report->conditions()->create([
|
||
'column' => 'contracts.start_date',
|
||
'operator' => '<=',
|
||
'value_type' => 'expression',
|
||
'value' => $asOf,
|
||
'logical_operator' => 'OR',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
]);
|
||
|
||
$report->conditions()->create([
|
||
'column' => 'contracts.start_date',
|
||
'operator' => 'IS NULL',
|
||
'value_type' => 'static',
|
||
'logical_operator' => 'OR',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
]);
|
||
|
||
// end_date >= as_of (or null)
|
||
$report->conditions()->create([
|
||
'column' => 'contracts.end_date',
|
||
'operator' => '>=',
|
||
'value_type' => 'expression',
|
||
'value' => $asOf,
|
||
'logical_operator' => 'OR',
|
||
'group_id' => 2,
|
||
'order' => 0,
|
||
]);
|
||
|
||
$report->conditions()->create([
|
||
'column' => 'contracts.end_date',
|
||
'operator' => 'IS NULL',
|
||
'value_type' => 'static',
|
||
'logical_operator' => 'OR',
|
||
'group_id' => 2,
|
||
'order' => 1,
|
||
]);
|
||
|
||
// client_uuid filter condition
|
||
$report->conditions()->create([
|
||
'column' => 'clients.uuid',
|
||
'operator' => '=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'client_uuid',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 3,
|
||
'order' => 0,
|
||
]);
|
||
|
||
// Orders
|
||
$report->orders()->create([
|
||
'column' => 'contracts.start_date',
|
||
'direction' => 'ASC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
|
||
protected function seedFieldJobsCompletedReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'field-jobs-completed',
|
||
'name' => 'Zaključeni tereni',
|
||
'description' => 'Pregled zaključenih terenov po datumu in uporabniku.',
|
||
'category' => 'field',
|
||
'enabled' => true,
|
||
'order' => 2,
|
||
]);
|
||
|
||
// Base entity
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\FieldJob',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
]);
|
||
|
||
// Join contracts table
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\Contract',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'field_jobs.contract_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'contracts.id',
|
||
'order' => 1,
|
||
]);
|
||
|
||
// Join users table
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\User',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'field_jobs.assigned_user_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'users.id',
|
||
'order' => 2,
|
||
]);
|
||
|
||
// Columns
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'id',
|
||
'label' => '#',
|
||
'type' => 'number',
|
||
'expression' => 'field_jobs.id',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'contract_reference',
|
||
'label' => 'Pogodba',
|
||
'type' => 'string',
|
||
'expression' => 'contracts.reference',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'key' => 'assigned_user_name',
|
||
'label' => 'Terenski',
|
||
'type' => 'string',
|
||
'expression' => 'users.name',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 2,
|
||
],
|
||
[
|
||
'key' => 'completed_at',
|
||
'label' => 'Zaključeno',
|
||
'type' => 'date',
|
||
'expression' => 'field_jobs.completed_at',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 3,
|
||
],
|
||
[
|
||
'key' => 'notes',
|
||
'label' => 'Opombe',
|
||
'type' => 'string',
|
||
'expression' => 'field_jobs.notes',
|
||
'sortable' => false,
|
||
'visible' => true,
|
||
'order' => 4,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->createMany([
|
||
[
|
||
'key' => 'from',
|
||
'label' => 'Od',
|
||
'type' => 'date',
|
||
'nullable' => false,
|
||
'default_value' => now()->startOfMonth()->toDateString(),
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'to',
|
||
'label' => 'Do',
|
||
'type' => 'date',
|
||
'nullable' => false,
|
||
'default_value' => now()->toDateString(),
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'key' => 'user_id',
|
||
'label' => 'Uporabnik',
|
||
'type' => 'select:user',
|
||
'nullable' => true,
|
||
'order' => 2,
|
||
],
|
||
]);
|
||
|
||
// Conditions
|
||
$report->conditions()->createMany([
|
||
[
|
||
'column' => 'field_jobs.cancelled_at',
|
||
'operator' => 'IS NULL',
|
||
'value_type' => 'static',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'field_jobs.completed_at',
|
||
'operator' => 'BETWEEN',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'from,to',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'field_jobs.assigned_user_id',
|
||
'operator' => '=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'user_id',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 2,
|
||
'enabled' => true,
|
||
],
|
||
]);
|
||
|
||
// Order
|
||
$report->orders()->create([
|
||
'column' => 'field_jobs.completed_at',
|
||
'direction' => 'DESC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
|
||
protected function seedDecisionsCountReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'decisions-counts',
|
||
'name' => 'Odločitve – štetje',
|
||
'description' => 'Število aktivnosti po odločitvah v izbranem obdobju.',
|
||
'category' => 'activities',
|
||
'enabled' => true,
|
||
'order' => 3,
|
||
]);
|
||
|
||
// Entities
|
||
$report->entities()->createMany([
|
||
[
|
||
'model_class' => 'App\\Models\\Activity',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Decision',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'activities.decision_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'decisions.id',
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Columns
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'decision_name',
|
||
'label' => 'Odločitev',
|
||
'type' => 'string',
|
||
'expression' => "COALESCE(decisions.name, '—')",
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'activities_count',
|
||
'label' => 'Št. aktivnosti',
|
||
'type' => 'number',
|
||
'expression' => 'COUNT(*)',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->createMany([
|
||
[
|
||
'key' => 'from',
|
||
'label' => 'Od',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'to',
|
||
'label' => 'Do',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Conditions
|
||
$report->conditions()->createMany([
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '>=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'from',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '<=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'to',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
'enabled' => true,
|
||
],
|
||
]);
|
||
|
||
// Order
|
||
$report->orders()->create([
|
||
'column' => 'activities_count',
|
||
'direction' => 'DESC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
|
||
protected function seedSegmentActivityCountsReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'segment-activity-counts',
|
||
'name' => 'Aktivnosti po segmentih',
|
||
'description' => 'Število aktivnosti po segmentih v izbranem obdobju (glede na segment dejanja).',
|
||
'category' => 'activities',
|
||
'enabled' => true,
|
||
'order' => 4,
|
||
]);
|
||
|
||
// Entities
|
||
$report->entities()->createMany([
|
||
[
|
||
'model_class' => 'App\\Models\\Activity',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Action',
|
||
'join_type' => 'join',
|
||
'join_first' => 'activities.action_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'actions.id',
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Segment',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'actions.segment_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'segments.id',
|
||
'order' => 2,
|
||
],
|
||
]);
|
||
|
||
// Columns
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'segment_name',
|
||
'label' => 'Segment',
|
||
'type' => 'string',
|
||
'expression' => "COALESCE(segments.name, 'Brez segmenta')",
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'activities_count',
|
||
'label' => 'Št. aktivnosti',
|
||
'type' => 'number',
|
||
'expression' => 'COUNT(*)',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->createMany([
|
||
[
|
||
'key' => 'from',
|
||
'label' => 'Od',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'to',
|
||
'label' => 'Do',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Conditions
|
||
$report->conditions()->createMany([
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '>=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'from',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '<=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'to',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
'enabled' => true,
|
||
],
|
||
]);
|
||
|
||
// Order
|
||
$report->orders()->create([
|
||
'column' => 'activities_count',
|
||
'direction' => 'DESC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
|
||
protected function seedActionsDecisionsCountReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'actions-decisions-counts',
|
||
'name' => 'Dejanja / Odločitve – štetje',
|
||
'description' => 'Število aktivnosti po dejanjih in odločitvah v obdobju.',
|
||
'category' => 'activities',
|
||
'enabled' => true,
|
||
'order' => 5,
|
||
]);
|
||
|
||
// Entities
|
||
$report->entities()->createMany([
|
||
[
|
||
'model_class' => 'App\\Models\\Activity',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Action',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'activities.action_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'actions.id',
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'model_class' => 'App\\Models\\Decision',
|
||
'join_type' => 'leftJoin',
|
||
'join_first' => 'activities.decision_id',
|
||
'join_operator' => '=',
|
||
'join_second' => 'decisions.id',
|
||
'order' => 2,
|
||
],
|
||
]);
|
||
|
||
// Columns
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'action_name',
|
||
'label' => 'Dejanje',
|
||
'type' => 'string',
|
||
'expression' => "COALESCE(actions.name, '—')",
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'decision_name',
|
||
'label' => 'Odločitev',
|
||
'type' => 'string',
|
||
'expression' => "COALESCE(decisions.name, '—')",
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'key' => 'activities_count',
|
||
'label' => 'Št. aktivnosti',
|
||
'type' => 'number',
|
||
'expression' => 'COUNT(*)',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 2,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->createMany([
|
||
[
|
||
'key' => 'from',
|
||
'label' => 'Od',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'to',
|
||
'label' => 'Do',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Conditions
|
||
$report->conditions()->createMany([
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '>=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'from',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '<=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'to',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
'enabled' => true,
|
||
],
|
||
]);
|
||
|
||
// Order
|
||
$report->orders()->create([
|
||
'column' => 'activities_count',
|
||
'direction' => 'DESC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
|
||
protected function seedActivitiesPerPeriodReport(): void
|
||
{
|
||
$report = Report::create([
|
||
'slug' => 'activities-per-period',
|
||
'name' => 'Aktivnosti po obdobjih',
|
||
'description' => 'Seštevek aktivnosti po dneh/tednih/mesecih v obdobju.',
|
||
'category' => 'activities',
|
||
'enabled' => true,
|
||
'order' => 6,
|
||
]);
|
||
|
||
// Base entity
|
||
$report->entities()->create([
|
||
'model_class' => 'App\\Models\\Activity',
|
||
'join_type' => 'base',
|
||
'order' => 0,
|
||
]);
|
||
|
||
// Columns (simplified - period grouping handled in ReportQueryBuilder or controller)
|
||
$report->columns()->createMany([
|
||
[
|
||
'key' => 'period',
|
||
'label' => 'Obdobje',
|
||
'type' => 'string',
|
||
'expression' => 'DATE(activities.created_at)',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'activities_count',
|
||
'label' => 'Št. aktivnosti',
|
||
'type' => 'number',
|
||
'expression' => 'COUNT(*)',
|
||
'sortable' => true,
|
||
'visible' => true,
|
||
'order' => 1,
|
||
],
|
||
]);
|
||
|
||
// Filters
|
||
$report->filters()->createMany([
|
||
[
|
||
'key' => 'from',
|
||
'label' => 'Od',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 0,
|
||
],
|
||
[
|
||
'key' => 'to',
|
||
'label' => 'Do',
|
||
'type' => 'date',
|
||
'nullable' => true,
|
||
'order' => 1,
|
||
],
|
||
[
|
||
'key' => 'period',
|
||
'label' => 'Obdobje (day/week/month)',
|
||
'type' => 'string',
|
||
'nullable' => false,
|
||
'default_value' => 'day',
|
||
'order' => 2,
|
||
],
|
||
]);
|
||
|
||
// Conditions
|
||
$report->conditions()->createMany([
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '>=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'from',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 0,
|
||
'enabled' => true,
|
||
],
|
||
[
|
||
'column' => 'activities.created_at',
|
||
'operator' => '<=',
|
||
'value_type' => 'filter',
|
||
'filter_key' => 'to',
|
||
'logical_operator' => 'AND',
|
||
'group_id' => 1,
|
||
'order' => 1,
|
||
'enabled' => true,
|
||
],
|
||
]);
|
||
|
||
// Order
|
||
$report->orders()->create([
|
||
'column' => 'period',
|
||
'direction' => 'ASC',
|
||
'order' => 0,
|
||
]);
|
||
}
|
||
}
|