<?php
namespace App\Services\Exporters_Development;

use DB;

class ExportersDevelopmentService
{
    public function getExportersDevelopmentProgrammeData($date_range)
    {
        $result = DB::table('kpi_indicator as ki')
                    ->join('kpi_achievement as ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                    ->join('kpi_achievement_detail as kad', 'ka.kpi_achievement_det_id', '=', 'kad.kpi_achievement_det_id')
                    ->select('ka.division','ka.section','ka.unit', 'kad.date',
                              'kad.programme', 
                              'kad.no_of_participants', 'kad.pmu_remarks',
                              'kad.pmu_remarks', 'kad.unit_remarks')
                    ->where('ki.indicator', 'Programme')
                    ->where('ki.Component_Achievement', 'development')
                    ->where(function ($query) use ($date_range) {
                        $query->whereBetween(
                            DB::raw("
                                CASE 
                                    WHEN kad.start_date IS NOT NULL THEN kad.start_date
                                    WHEN kad.approved_date IS NOT NULL THEN kad.approved_date
                                    ELSE NULL
                                END
                            "), [$date_range->start_date, $date_range->end_date]
                        )
                        ->orWhere(function ($q) use ($date_range) {
                            $q->whereNull('kad.start_date')
                            ->whereNull('kad.approved_date')
                            ->whereBetween('kad.year', [$date_range->year_from, $date_range->year_to])
                            ->whereIn('kad.quarter', array_unique(array_merge($date_range->quarter_from,
                            $date_range->quarter_to)));
                        });
                    })
                    ->get();
        return $result;
    }

    public function getExportersDevelopmentExportSalesData($date_range)
    {
        $result = DB::table('kpi_indicator as ki')
                    ->join('kpi_achievement as ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                    ->join('kpi_achievement_detail as kad', 'ka.kpi_achievement_det_id', '=', 'kad.kpi_achievement_det_id')
                    ->select('kad.year', 'kad.quarter','kad.division',
                              'kad.section', 'ka.unit', 
                              'kad.activity_name',
                              'kad.actual_sales_generated', 'kad.potential_sales_generated',
                              'kad.total_sales_generated')
                    ->where('ki.indicator', 'Export Sales')
                    ->where('ki.Component_Achievement', 'development')
                    ->where(function ($query) use ($date_range) {
                        $query->whereBetween(
                            DB::raw("
                                CASE 
                                    WHEN kad.start_date IS NOT NULL THEN kad.start_date
                                    WHEN kad.approved_date IS NOT NULL THEN kad.approved_date
                                    ELSE NULL
                                END
                            "), [$date_range->start_date, $date_range->end_date]
                        )
                        ->orWhere(function ($q) use ($date_range) {
                            $q->whereNull('kad.start_date')
                            ->whereNull('kad.approved_date')
                            ->whereBetween('kad.year', [$date_range->year_from, $date_range->year_to])
                            ->whereIn('kad.quarter', array_unique(array_merge($date_range->quarter_from,
                            $date_range->quarter_to)));
                        });
                    })
                    ->get();
        return $result;
    }

    public function getExportersDevelopmentCompaniesAssistedData($date_range)
    {
        $result = DB::table('kpi_indicator as ki')
                    ->join('kpi_achievement as ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                    ->join('kpi_achievement_detail as kad', 'ka.kpi_achievement_det_id', '=', 'kad.kpi_achievement_det_id')
                    ->select('ka.division','ka.section','ka.unit',
                            'kad.programme','kad.sector',
                              'kad.name_of_approved_participants', 'kad.sector', 
                              'kad.registration_number')
                    ->where('ki.indicator', 'Companies Assisted')
                    ->where('ki.Component_Achievement', 'development')
                    ->where(function ($query) use ($date_range) {
                        $query->whereBetween(
                            DB::raw("
                                CASE 
                                    WHEN kad.start_date IS NOT NULL THEN kad.start_date
                                    WHEN kad.approved_date IS NOT NULL THEN kad.approved_date
                                    ELSE NULL
                                END
                            "), [$date_range->start_date, $date_range->end_date]
                        )
                        ->orWhere(function ($q) use ($date_range) {
                            $q->whereNull('kad.start_date')
                            ->whereNull('kad.approved_date')
                            ->whereBetween('kad.year', [$date_range->year_from, $date_range->year_to])
                            ->whereIn('kad.quarter', array_unique(array_merge($date_range->quarter_from,
                            $date_range->quarter_to)));
                        });
                    })
                    ->get();
        return $result;
    }
}